add a tool to easily permit the upgrade of a database version to an other

[allan-simon]

When we update the database schema of a cppcms application, we may want to have an easy and safe way to

1. check that the current application as the last database schema
2. if not to be able to execute one or several script to update it without harming the data already in it

basically it should be an external tool that can work like this

 cppcmsskel_upgradedb  path/to/application/db  path/to/update/folder 

for example

 cppcmsskel_upgradedb  /opt/tatowiki/data/tatowiki.db   ~/tatowiki/app/sql/updates 

updates containing

1. a last_version.txt containing the last version number in it
2. a list of scripts from_X_to_Y.sql permitting to upgrade from the version X to the version Y of the database , so that the script will first compare the version in the application database, and then will execute it for example from_13_to_15.sql and from_15_to_16.sql

[eventhorizonpl]

Hi,

I need such tool and I'm willing to work on it. I think that it should work like Doctrine migration tool.

Let's say that we have files in app/sql/ :
20130415110214_down.sql
20130415110214_up.sql
20130415170210_down.sql
20130415170210_up.sql
20130416090314_down.sql
20130416090314_up.sql

If we execute cppcmsskel_upgradedb with empty database, than:

• migration_versions table is created

CREATE TABLE IF NOT EXISTS migration_versions (
version varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

• 20130415110214_up.sql, 20130415170210_up.sql, 20130416090314_up.sql scripts are executed
  we perform
  INSERT INTO migration_versions (version) VALUES ('20130415110214');
  for each migration file.

If we want to migrate database to version 20130415170210, than 20130416090314_down.sql is executed and we perform
DELETE FROM migration_versions WHERE version = '20130416090314';

If we execute cppcmsskel_upgradedb with existng database, than:

• we check what migrations was applied, we apply missing migrations versions

Versioning with timestamps is a lot better scheme if many peoples works on application.

What do you think about that?

I'm mainly interested in postresql and mysql if it comes to SQL databases - I think that we need some configuration support for that. Do you have any code for it or should I start looking at code and start coding? :)

Best regards,
Michal

[allan-simon]

for the last point you're refering to do something like solving issue #36 ?

for the rest I'm gonna check doctrine, but yep certainly something like that :)

[eventhorizonpl]

Yes, we need to solve issue #36 before this. I'll take a look at it and try to write something.

Doctrine is a PHP ORM. For version 2.x there is a separate migration tool http://docs.doctrine-project.org/projects/doctrine-migrations/en/latest/index.html .

What I would like to have for cppcms is a simple tool that can:

• migrate database from one version to another (up and down)
• works for two different databases (MySQL, PostgreSQL) and I guess your requirement will be SQLite

From my POV most simple and clean method to achieve this will be use of separate scripts named after this convention {timestamp}{myslq,pgsql,sqlite}{down,up}.sql
20130415110214_mysql_down.sql
20130415110214_mysql_up.sql
20130415110214_pgsql_down.sql
20130415110214_pgsql_up.sql

Other way to achieve this is to create some migration file format

postgresql:
up:
CREATE *;
down:
DELETE *;

But I'm a huge fan of first solution here.

The other problem that I would like your opinion on is how to connect to database from cppcmsskel_upgradedb (I assume that you want this to be writen in python like other cppcmsskel tools). So we have a two options here:

• we can use some database connect drivers (and add additional dependencies)
• we can run raw scripts from system command line i.e. psql -U user database < migration_file.sql

The second solution is simple and will not add additional dependencies, so I think that it will be better.

What do you think about it?

Best regards,
Michal