I have a little stand alone perl script that does several "REPLACE INTO CategoryAlternates" statements. But the REPLACE
doesn't actually replace the old records, instead duplicates are
inserted. For example if there is already a record where CategoryID=100 and LinkID=15, I can insert an identical record into the table (either by hand or with my script). I think this is because of the multiple indexes on CategoryID and LinkID, but I'm no sql pro...

Do the indexes on that table need to be changed to prevent duplicate records?

I have the same problem when I use REPLACE INTO Links. I end up with multiple records with the same URL and CategoryID.

Am I missing something here?

Brian

Hello!


No. Not at all. More you think in this direction, more you will understand what all I have been speaking about in the other thread.

This is exactaly what I am requesting Alex to develop this area in a good way. This feature is so important and necessary for all the webmasters. If people need not to use it, its fine. They may not argue against it, but its so necessary.

You can see that I am also stuck since three months. I am banging my head on the wall. I took a very long time to realize that this kind of export-import area is just not a simple modification. Beleive me. So many issues involved.

What I propose is the following :


It is therefore, Links, Category, Users and Validate should be able to be imported and exported and imported. This is a feature which is missing and is important to any other development. The best is that all the sub_routines are there. They have been designed. They simply needs to be given shape to and make a user/webmaster friendly featues.

A very very clever design exist in the editor.cgi!!! To use this concept and simply make few CASE more and get many other sub_routines may be not a big problem at all. But the advantage it brings it worth a millions of thanks by all of us.

Conceptually speaking, in terms of design value, DBSQL.pm and editor.cgi are the BEST programmed scripts of all the other. Editor.cgi is not as complicated but the result it can offer, the application of its intellectual value it can offer if soooooo great. The only problem is editor.cgi has not yet fully been developed in regards to situations like yours and mine.

What will you do after the imports. Never be able to get it out in the correct way!!! Once the Category names are converted into IDs then they will remain. What if a table is corrupt? And thats of Category. Say, you took a Links Table out. What to do with table Category_Alternates? Then when you import it back, the Ids may have changed!!!

Now think of a real life situation and say that you need to rebuild your database entirely. How? What is Categories have been deleted in between? So you may have CategoryID 231 and 234. When you rebuild a new category table, it will give a new number after the import through the auto_increment from the great MySQL. Then what will happen to the CatIDs in the Links? Can any one tell me how to handle such a situation with the current level of import-export possibilities.

Now, at the moment editor.cgi only simply dumps into the database. It does check for duplicates and thats one small good part of it.

Thats what is necessary for your case and not to work on any other scripts trying to modify. A modification in the editor.cgi can help you tremendously.

Hence a proper system is necessary which is well thought. Alex said in the other thread with the title "I am tired of Links SQL" that Send me your database and I will help you to import it into the Links MySQL database. But a one time solution is not helpful. It could be general and broad so that all the Links SQL users are fully benefited from a problem of one user and that it helps everyone as a community. For e.g. Mr. Pataki brought a very very clever idea of Multiple editors in the admin. Thats so impressive. I liked the idea vcery very much. Alex did it. It works very good, or it will. Such are the features that are shining silver features for a potential buyer to go for the product. Such suggestions helps the entire community of Links SQL users. More and more such featurtes developes more and more attractive it becomes in the showcase and more and more are ther chances of a produst for it to sell. I would be more happy if more users join, so that there is further and strong development. But I would be very unhappy if Alex blocks the development of some areas of Links SQL for certain reasons. I have full faith and trust in the future development that when Alex thinks of this problems, I am sure that the result will be a.........

------------------
rajani













[This message has been edited by rajani (edited October 21, 1999).]

Hi Brian,


So you need to make sure when you do a REPLACE that you pass in either a PRIMARY KEY value or a UNIQUE index value. On the Links table, that means making sure that you set the ID. On CategoryAlternates, there isn't a UNIQUE index so you can't use replace.

What you would need to do is add:

UNIQUE unindx (LinkID, CategoryID)

as an index. Otherwise, you'll need to do a SELECT then INSERT/UPDATE. The reason I don't use that to start with, is because that does not allow quick lookups for CategoryID by itself, something that needs to be done.

Cheers,

Alex

Thanks for the reply Alex! I understand now.