SqlCVS - Managing schema changes

Modifying existing tables in sqlCVS is a multistep process which involves manually executing stuff on the sqlCVS main server (schema.linuxmce.org as of this writing). The current master database is main_sqlcvs_utf8. You *must* remember to perform a new sql2cpp run after re-importing the databases from schema and commit any changes to the database libraries. This will usually require a complete rebuild of all LinuxMCE packages.

On the main sqlCVS server

Add the modification statement to psc_<repo>_schema

For example, to add a new field FK_Text to the CommandGroup table in the local repository, the command is

insert into psc_local_schema set Value= "ALTER TABLE `CommandGroup` ADD `FK_Text` INT(11) DEFAULT NULL AFTER `TemplateParm2`";

Update the psc_<repo>_repset table

After adding the above alter statement to the schema, we need to update table rep_set with the latest PK_psc_local_schema created by the above statement

UPDATE psc_local_repset SET Value=(Select max(PK_psc_local_schema) From psc_local_schema);

Modifying the actual table

Now we modify the actual table, by executing the ALTER TABLE command in the main_sqlcvs_utf8 database

ALTER TABLE `CommandGroup` ADD `FK_Text` INT(11) DEFAULT NULL AFTER `TemplateParm2`;

On the local system

To update the schema on our system, we need to run the update command locally (or do this through webadmin, which is not possible for the 'local' repo)

/usr/pluto/bin/sqlCVS -H schema.linuxmce.org -R 3999 -U anonymous~nopass -r local update

Notes

If anyone knows of a simpler approach, please amend. Thanks.