Database schema needs to be dynamic and upgradeable

[Jonathan Hitchcock]

Since the bot is in the early development stage, the schema is by no means rigidly set - we may add or remove fields fairly often in the next few milestones.

In addition, some plugins require tables in the schema, but these tables are only created by the setup script when it calls create_all - if a new plugin is installed, there is no way to create the table just for that plugin as yet.

If we had some sort of schema version number stored in a static table, and methods that could perform simple steps to migrate from one version to the next, it would be easy to step up through all the steps to get to the current version number. In addition, if plugins could detect that their tables had not been created, or were an old version, they could step forward through versions to current.

Something along the lines of:

create table schema_info(
   id int(3) not null auto_increment,
   tablename varchar(100),
   version int(10)
);

If there's no line in the table for a tablename, it needs to be created, if the line has version number 3, then you need to run 3_to_4, 4_to_5 and 5_to_6 to get to current version 6.

Now when a bot owner installs a plugin or updates the source, they run ibid-update, which checks that the schema is up to date and does anything else that the new version requires.

[Michael Gorven (mgorven)]

Moved to milestone. https://blueprints.launchpad.net/ibid/+spec/database-upgrades