Means it's an optional parameter and you use LOCAL or FOREIGN or ALIEN The brackets just group the set, and usually mean "optional".

Sorry about that..... programmer shorthand.

Oh!! One more thing... the "Links.Table" is the way MySQL represents DATABASE.tablename, so if you were importing into the standard main "Links" table that was in a database called "Links" it would be:

...TABLE Links.Links ....

To import into the Validate table (as long as it had all the fields) you would use:

...TABLE Links.Validate

The '.' links the database name to the table name, so you can even use this to copy across databases using the INSERT INTO command:

Code:

INSERT INTO Database1.Tablename1 (comma,list,of,fields) SELECT (comma,list,of,fields) FROM Database2.Tablename2 ORDER BY fieldname

Once you get there, do a search for the LOAD command, and you'll see all the available options and syntax.

I thought it was useful to point out, since it does bypass all the Links features, but it inserts the data right into the database. It doesn't do any error checking (other than minor field type) and you do need to REINDEX the database after using it.

I'm using it successfully on a number of databases and applications, and it's a general solution (albiet a technical one) for some sticky problems.

I have had a problem that keeps popping up in regards to importing links, and I was hoping you might be able to answer whether the LOAD command will fix this.

If you have 100 Categories, and delete #3, export the data and re-import, # 4 becomes # 3, # 5 bevcomes # 4, etc. Will LOAD Keep the ID Number from the file on Import, or will it re-number the order, thus screwing up all the category links.

The way the auto increment field works is that you can insert any value into the field, as long as it's non-zero and greater than the last loaded value.

So, if you wanted to re-import the links, you need to include the ID field. If you export the database, and re-import with the ID field IN ASCENDING ORDER you should keep the ID values.

If you CHANGE a field to auto-increment, it re-numbers starting at 1

I could be wrong, but this is how it's apparantly worked for me -- since when I imported DMOZ I have categories with 227504 or something crazy like that.

And the re-numbering by changing a field to Autoincrement is something by experience.

I have set up and dumped literally dozens of databases and hundreds of tables trying to figure things out.

To specifically answer your question:

Quote:

If you have 100 Categories, and delete #3, export the data and re-import, # 4 becomes # 3, # 5 bevcomes # 4, etc. Will LOAD Keep the ID Number from the file on Import, or will it re-number the order, thus screwing up all the category links.

The answer should be YES it will keep the fields, as long as you are loading into a NEW database (one that has been fully emptied).

Logicially, this would have to be so, since most people trying to backup/restore a database would not want values of any field arbitrarily changed.

The key is that values must ASCEND so links must be exported/imported in numerical order _AND_ the database must have been emptied to reset the counter to 0

Actually, that's been my experience, the "official" word from the docs is:

Quote:

An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. See section 20.4.29 mysql_insert_id(). If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused. If you delete all rows in the table, the sequence starts over. Note: There can be only one AUTO_INCREMENT column per table, and it must be indexed. To make MySQL compatible with some ODBC applications, you can find the last inserted row with the following query: SELECT * FROM tbl_name WHERE auto_col IS NULL

What this is saying, is you can insert any integer value except '0' directly into the auto_increment field. If you try to enter a value of '0' or NULL the next-highest value is taken. Values _can_ be reused, but are implied (at least) to be unique.

What I haven't seen in the docs is what happens if you try to insert a duplicate key value -- do you get an error message and a fail? Do you get a successful unsertion? Or does it increment the highest value by one, and continue (but by doing that screw up all subsequent values).

Thanks for the reply. I figured out that empty the database myself- been having a lot of fun importing and exporting What I missed is how to turn off auto-increment (sp?) or turn on "force the new ID number..."

I mean if you have been working with a database for a while, you are gonna have holes in the category numbers (right?) So if I export that, my ID numbers will go 1, 2, 3, 5, 6, 9, 10....

So I want to FORCE it to import those same numbers, not 1, 2, 3, 4

OR, is there a way to compact the database BEFORE you export, to get numbers back to the 1, 2, 3, 4 progression (that also updates all the Links?)

Also, for those who care, if you change $CATID and $CATCNT in Parse_RDF.pl to the last Category.ID number, you can Import a second DMOZ set into Links (I wanted Arts/Animation and Arts/Comics, but not Arts/Music...) That got VERY messy!

You should ask them. They may have some restriction on loading files from disk.

I have hit a few ISP's who felt this was a "security risk" for some unknown reason... since you can pipe the same thing in via the interactive interface, and there is no additional risk of a file, as long as you already have an account on the ISP....

Thanks PUGDOG, Can you help me to make it the interactive way. Actually i m not sure with the fields like Escape character, Records, Delimiter etc etc. Please tell me what do i have to add or remove in the space provided against these feilds For a look at my database you can visit my post at http://gossamer-threads.com/perl/forum/showflat.pl?Cat=&lookup=1&Number=113564

Gossamer Threads is a Vancouver-based company with over 23
years experience in web technology. From development to hosting, we
partner with leading organizations around the globe and help to build
their web presences, strategies and infrastructures.