EVE Technology Lab

It would be great if CCP could provide the SQL Server patch scripts used update their databases with the changes for each release. That way it would be far simpler to apply the changes to our copies by just modifying their SQL to MySQL or PostgreSQL syntax and voila done. No need to do a diff to figure out what changed and then craft the corresponding SQL by hand or resort to dropping / recreating the the database every time to reload it from scratch.

If you have referential integrity constraints then it's troublesome. With MySQL you can of course turn off foreign key checks and then re-enable them when completed but unfortunately there's no built-in way to validate the foreign keys afterwards to ensure that all is well. You have to do those checks manually or hope for the best. CCP does delete ids occasionally and they sometimes remap a few too. With PostgreSQL it's not as easy to get that messed up to begin with unless you go through the trouble of explicitly dropping your foreign key constraints - then upon recreating them it'll tell you that something is amiss. That's a pain in the butt to deal with.

Fortunately, you provide a conversion from SQL Server to MySQL. I use that to load up a MySQL database, extract the data for the tables and columns that I care about into tab delimited files and then update my database load scripts with the results - both MySQL and PostgreSQL. That's not too bad. But to apply the changes to existing databases is tedious. With MySQL I use the foreign key disable / enable feature and just hope all is well afterwards admittedly. With PostgreSQL, I have to load the database tables into temp tables and issue updates joining on the primary key for each one.

All of this could be avoided and sped up by just having patches with only the data and structure that's changed being affected. Especially helpful for killboard or other distributed application sites where admins want to apply the latest changes - as quickly as possible - and without screwing up relationships in the dynamic portions of their databases. A patch would also presumably remap ids if that occurred instead of leaving you with a messed up database where some of the static ids have changed and your other custom tables still point to the old ids some of which are now non-existent or mean something else entirely.