[FONT=arial]Since the very beginning of WoW emulation, DB updates have been distributed more or less like they are now; by the way, times have changed and complexity increases day by day, expansion by expansion.[/FONT]
That’s the reason why I spend some time on looking at the way things are organized, and here is my suggestion about DB updates.
Desirable Properties:
[ul]
[li]Idempotence: which is that even if an update is applied 1000 times, it doesn’t break the database.[/li]
[li][FONT=arial]Consistency: which means that anytime an update is launched, it must run from the beginning to the end, and if a query fails, the update should automatically rollback without breaking the DB.[/FONT][/li][li][FONT=arial]Ease: the new system should be easy to understand and to use by early users, maybe by providing some tools, but still efficient and flexible for “power users”.[/FONT][/li][/ul]
[FONT=arial]Proposal:[/FONT]
- Updates should be saved in the /updates folder like they are now, but named more easily, like VERSION.REVISION.sql
[FONT=arial]This way Git can manage conflicts (if two people push a file with the same name) and still developers can easily write updates (they just need to refer to the revision they are using).[/FONT]
[FONT=arial]- DB instead of a “version” table, may manage some data, like [Version, Revision, Date, Changelog] and a row could be added each time an update is executed, so that a small changelog since the first DB Installation is saved.[/FONT]
- A tool could easily grab all updates in the appropriate folder and apply to the DB and this would be much easier both for power users and newcomers. It may even be started by the core itself when it detects that the wrong DB version is present.
Examples:
About Idempotence, many query already work that way, like for example UPDATE or INSERT IGNORE.
While for more complex situations, IF construct may be used:
– Idempotent alteration of a table
IF NOT EXISTS (
SELECT * FROM [information_schema].[columns]
WHERE table_name = ‘table_name’ AND table_schema = ‘db_name’ AND column_name = ‘column_name’
)
BEGIN
ALTER TABLE [db_name].[table_name] ADD column_name int;
END
Consistency could easily be achieved by transactions, even though some DB engines do not support them (yet?).
– This way even if the first UPDATE and DROP queries work, the last will give an error and all these changes won’t be saved
START TRANSACTION;
– Do Something
UPDATE version SET XXX;
DROP TABLE creature_template;
UPDATE creature_template SET entry = 1 WHERE entry =2;
COMMIT;
Hoping this can be a good point where to start, I am looking for some comments and ideas.
I’m sorry for the grammars errors, if something is not clear, say it! /emoticons/default_biggrin.png