Edited in 14/09/2014: Moved to the wiki
I don’t think this rule is a good idea. Any time you use a DELETE, it has the potential to really screw something up. Those “import errors” are primary key constraints and they’re there to prevent us from breaking something.
For instance, look at the following example. The DELETE statements before the INSERT statement would have caused me to lose a smart_script I had already set on that NPC.
UPDATE creature_template SET AIName=‘SmartAI’ WHERE entry=25379;
DELETE FROM smart_scripts WHERE entryorguid=25379 AND source_type=0;
DELETE FROM smart_scripts WHERE entryorguid=2537900 AND source_type=9;
INSERT INTO smart_scripts (entryorguid,source_type,id,link,event_type,event_phase_mask,event_chance,event_flags,event_param1,event_param2,event_param3,event_param4,action_type,action_param1,action_param2,action_param3,action_param4,action_param5,action_param6,target_type,target_param1,target_param2,target_param3,target_x,target_y,target_z,target_o,comment) VALUES
(25379,0,1,0,1,0,100,0,5000,5000,300000,300000,80,2537900,0,0,0,0,0,1,0,0,0,0,0,0,0,‘Warden Nork Bloodfrenzy - OOC - Run script’)-- partial quote from [ WIP ] Quest 11711 “Coward Delivery… Under 30 Minutes or it’s Free”
That’s just an example without errors. A typo in a DELETE statement could be catastrophic.
I come from a background with a lot of database experience, but very little C++ experience and no experience with TrinityCore. I’m new here and I don’t want to piss anyone off, so please don’t flame me. I also know that you’ve been working with these rules successfully for a long time. I’m just sharing an opinion based on my own negative experiences.
My rule is to never use a DELETE statement unless absolutely necessary. Of course, if I submit a bug fix, I’ll follow your rules.
Thank you.
Yeah, we discussed that internally (many years ago, since we follow this rule) and we still think deleting before inserting is better than not doing it: people are usually bad at following the rule “only import each sql once” so by deleting before inserting we cut down a lot of “OMG SQL DOESNT WORK PLZ HELP”. Also, most people got custom stuff on the db, it could easly conflict with our inserts.
Personally, I’d prefer if we didn’t have to write deletes. Few months ago, someone did a commit changing a lot of script_texts: “DELETE FROM script_texts WHERE id BETWEEN -10000 AND -100000;” (the values were different but the point is that max was less than min), it ended up deleting 90% of the table (https://github.com/TrinityCore/TrinityCore/commit/13d3a675b11aaa4c4e733a0353daca7ae2da6ed6).