Gossamer Forum
Home : Products : Gossamer Links : Discussions :

50,000 records - best way to batch add, manage??

Quote Reply
50,000 records - best way to batch add, manage??
Hi All

Happy New Year!

It looks like we will be starting with about 50,000 records for the db.

Since Excel and other spreadsheet programs have limits up to something like 14,000 records, what would be the best way to work with a db of 50,000 items?

Can groups of, say, 5000 be added or dumped at a time, OR are do we have to find some way to manage the whole glob of data?

Also, what if we wanted a dump of selected categories? Is there a way to get only the category of stuff that we want to work on, OR is it an all-or-nothing kind of thing?

The whole issue is, "How can we work with selected big parts of a big db?"

Many thanks for your help!

------------------------------------------
Quote Reply
Re: [DogTags] 50,000 records - best way to batch add, manage?? In reply to
I'll say this again, right up front, so that any head banging, nail biting or furniture throwing is warned of in advance :)

Links - especially the SQL version, is _not_ meant to be used off-line, broken up, or redigested only to be put back into the database.

That said, if you _ONLY_ want to work on the links off-line, such as add descriptions, change descriptions or keywords, or otherwise make changes to the record without moving those records around, it would be possible to dump the links, work on them, and re-import them _AS_IS_ into the database, overwriting the old data.

The best way to work with Links, is ON-LINE. That's what inexpensive Internet connections are for -- and DSL, Cable, etc.

The next best, would be to run the same OS on your local computer -- windows, Unix, Linux, Solaris, etc, and then simply re-import the database. You'd lose stats, and such, but for a catalog or uni-direction (out going) database, that may not be important.

The problem is that SQL is a RELATIONAL database system, and Links SQL makes extensive use of that relational capacity. The Links Table is related to the Category table via CatLinks, the Users Table is related to the Links table, xxx_Files are related to the Links table, and so on, and so on. When you take a part of this database off-line, you are breaking these relations.

Don't look at the database as a flat-file of stuff you can play with, and put back. It's not. It's a 3-Dimentional data structure, more like Mr Spock's chess board.

Can it be done? Yes. (The backup system does something analogous to this)

Should it be done? No. (for all the reasons above)

Is it easy to do? No. (again, for all the reasons above, and the backup system does a complete dump/import)

Is it something you should consider doing? No. (get a better internet connection and work on-line :))

Does it really make sense to do something like this? No. (it's more expensive than a better internet connection)

Is it something people want to do, and ask to do all the time? Yes. (they think it's cheaper than a better internet connection)

Is it something planned for the future? No. (though I can't speak for Alex, i can't see this sort of "feature" being worked on).

This is a "feature" that would consume loads of time, to create a pretty bad hack, that people would complain about to no end, and it would take man power from the main jobs of improving the whole system, developing new products, etc. The reasons have to do with the RELATIONAL nature of the program and database, and that the power, and features all come from that relational ability. You can't break that up, and expect to put it back together in random fashion.

These are my opinions, and the Magician Alex may have something else to say, or in the works. But, I can't see something like this happening because of the complexity of the problem, and the overall limited value of such a feature in reality.




PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] 50,000 records - best way to batch add, manage?? In reply to
Hi pugdog

I appreciate your thoughtful response. I agree that I do not want to mess with the relational nature of the system. The only thing that I thought would be nice is to do batch stuff like when I used to work with the Links flatfile. The spreadsheet made it easy to create one value in a cell and then propagate it to the other records. It's the repetitive stuff that I was trying to get a handle on.

I guess it would be great to know exactly what happens to the db when a new link or category is added. When a link or category is added, what gets added where? Is there some razzledazzle that occurs that us mysql-newbies are not used to yet?

I appreciate your thoughts on this, as always.

Have a Happy NY!

------------------------------------------
Quote Reply
Re: [DogTags] 50,000 records - best way to batch add, manage?? In reply to
Hello All:

I specifically asked about this twice before I purchased Links SQL - I've attached part of email thread at bottom (see my request for clarification on "And the ability to add/delete catagories/links via a database?". While Jack's response doesn't specifically say "YES", it did give me the impression that it was doable.


I do have a cable modem, but it is much harder to change links through an admin panel interface than it is via a spreadsheet program like Excel.

I guess I'm a little disappoint to hear it isn't possibile OR not a good idea.

I have only about 3,000 links. Many have been pulled from the web and need to be put into different categories. It would be much easier to cutNpaste the category. I'm sure it will take me hours and hours to move each link individually through the admin panel.

DogTags - I'm sure you get tired of responding to questions about this . . . but as you can see - I was very specific in my questioning before I purchased the product.

How about this . . . I could try to make all the changes in a flat file (from Links Ext.) and then do an import into Links SQL. Would that work OK?

Also - Does SQL or MySQL (sorry I'm a newbie with SQL) have a method to make changes directly to a file?

Thanks for your help.

April

guider@guider.com

HERE'S PART OF THE EMAIL THREAD

Hi April,

If you go to the Links SQL admin panel, click on "Database", select "Links" from the first pull-down menu, "Properties" from the second pull-down menu, and click on "Go".

Scroll down to the bottom of the right frame and you will see Import and Export there. The fields have to line up completely though, just do an export first and you'll see what the structure of the database is like.

Jack.




> Jack - Thanks again. Let me clarify one question . . . From before:
>
> "And the ability to add/delete catagories/links via a database?"
>
> What I meant was . . .
> I use Excel as well as a Text Editor extensively to
> add/arrange/remove/modify categories and links. Will I be able to
> import a category flat text database file into the MySQL database under
> its respective table?
>
> Thanks, April
>

A. Lougheed
IndyLinks.com
webmaster@indylinks.com
Quote Reply
Re: [aprillougheed] 50,000 records - best way to batch add, manage?? In reply to
I should have said "PugDog" I'm sure you get tired . . .

rather than "DogTags"

sorry.
A. Lougheed
IndyLinks.com
webmaster@indylinks.com
Quote Reply
Re: [aprillougheed] 50,000 records - best way to batch add, manage?? In reply to
He's saying what I said. It's possible, it's just not a good idea.

But, he's also saying that you _can_ do it.

Follow the instructions he gave, and you'll come to a screen that says:

Quote:


From here you can export your Links to either the screen or to a file on your server. The first line of the export will be a list of the column headers. The last two fields is the Category ID the link is in, and the Category Name. If a link is in more then one category, you will get one row for each occurrence.



and

Quote:




You can either import from a file or you can cut and paste the contents into a textarea box. If you have a large number of records, you should really import from a file. The first row of your input should be the fully qualified column names. You must also include the Category ID and Category Name of the category the link will be imported to.


There are _problems_ with this, if you understand the Links structure, most notably the
"one row for each occurance" of a link in a category. Each row will have the same LinkID,
but different Catgory# and Name. This will cause problems on a re-import and edit, but
it _can_ be done, and if you are careful, and develop a standard operating procedure you
can do it... but remember, you'll loose hits and ratings, etc between the dump and import.

An "intelligent" import/update script would be what you needed, updating only the
specified fields, such as Title,Keywords,Description, and leaving the other fields alone.






PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] 50,000 records - best way to batch add, manage?? In reply to
Hi - Yes, I'm understanding more and more. I see where the category isn't listed as a "property" under links.

Thanks for your help. I'll keep working at it . . .

Smile
A. Lougheed
IndyLinks.com
webmaster@indylinks.com
Quote Reply
Re: [DogTags] 50,000 records - best way to batch add, manage?? In reply to
Hi,

What we have found to be EXTREMELY useful, is if you can get MS Access setup with ODBC. If you download and install MyODBC from the MySQL website, and create an ODBC connection to your database, you can open your live Links SQL database inside Access and make changes.

Please realize that this is of course very dangerous if you don't know what you are doing, as you are making direct changes to the underlying data. You can however, easily add/modify data directly from Access. It works quite well with a fast connection.

We are looking into making a windows app that would work through ODBC to do this. The one tricky part is you need to be able to connect to your MySQL database remotely, this may require setting up a new username/password with your ISP.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] 50,000 records - best way to batch add, manage?? In reply to
What if we start with 50,000 items. Then, we decide to add 10,000 more.

Can the new 10,000 be somehow appended to the existing db, OR must the 50,000 be dumped, the 10,000 added to it, and then the new 60,000 item db uploaded ? I know that we may lose hits, etc.

Thanks very much.

------------------------------------------
Quote Reply
Re: [DogTags] 50,000 records - best way to batch add, manage?? In reply to
You can use the IMPORT utility to IMPORT the new set of records...they will be appended to the table.
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Heckler] 50,000 records - best way to batch add, manage?? In reply to
Thank you very kindly, Heckler!

Happy New Year to All !!

------------------------------------------
Quote Reply
Re: [Alex] 50,000 records - best way to batch add, manage?? In reply to
Any luck with such an app or something else, Alex?

Thanks Smile

------------------------------------------
Quote Reply
Re: [DogTags] 50,000 records - best way to batch add, manage?? In reply to
you don't have access then?

if you don it's really worth a try -quite a good alternative to mysqlman