Database upgrade approach, will this be too complicated ?

[nemerle]

Proposed approach:

./unoff_server
#[Error] Your main database version ( v11 ) is inconsistent with the server version ( v39 ),
#upgrade your database using '-U' option
./unoff_server -U
#[Notice] Database upgrade in progress
#...
#[Notice] v12_to_v13 - Assigning default age to all the shrubs in the Northern Forest - remember to adjust using your favourite SQL editor
#[Notice] v30_to_v31 - New attribute added to all items, consult unoff-base-data scripts for additional info
#[Success] Database updated.

1. Add a simple administrative table to the database, storing DbVersion integer.
2. Check DbVersion on server startup, and inform user about any inconsistencies.
3. With each new DbVersion provide either a sql script ( Db_1_to_2.sql ), or create a semi-automated set of procedures that can be executed on server startup.
4. When upgrade procedure is being done, run such scripts/procedures in order. Simple template of such a function

void databaseUpdate_v1_to_v2(sqlite3 *db) {
// start transaction
// check db version
// do whatever, add columns, tables, recalculate things etc,
// update db version
// if everything is ok, commit transaction
}
void register_db_upgraders() {
  register_upgrade_function(1,2,databaseUpdate_v1_to_v2);
}

[themuntdregger]

1. Good idea - Is there any advantage to incorporating a db_version field into the existing GAME_DATA_TABLE, as opposed to having a new table just to hold version data ?
2. Good idea - Yes, database inconsistencies have previously resulted in some hard to find bugs, so improving the robustness of our code seems sensible.
3. Good idea - Automation would save the hassle and problems of having to edit the database structure directly
4. Yes. The ability to specify the order in which upgrade actions are undertaken would be critical.

Query - We have a function called create_default_database in the database_functions.c module, who's purpose is to create a default database structure at outset. Could this be replaced by an upgrade script ?

Query - How would we supply the upgrade script to the server, ie would it be a plain text file or some other means ?

[nemerle]

ad 1. Yes, adding db_version to GAME_DATA_TABLE makes perfect sense ad 4. Assuming we do upgrades using properly registered in-server functions, ordering should be auto-magical :smile:

ad Query 1: Yup, just leave it in it as-is, and after running it, just run the whole upgrade process ad Query 2: Simplest approach, add db_version_upgrades.c to the project, and store each upgrade snippet as a standalone function.

Expanding answer to Query 2 : Having those upgrade procedures as a plain old 'c' functions instead of standalone SQL files, will allow for some pretty decent error checking and performing complex operations on existing database.

As an example: if we find the need to move few columns from one of the tables to another, using 'c' function would allow us to perform the necessary steps to preserve the existing data. In case we need to 'split' existing data ( like say supporting two different eye colors per-character ), we can transform existing data appropriately ( like copying existing eye-color, into eye-color-left, eye-color-right )

[themuntdregger]

Sounds good to me :+1: Lets do it