[dev] DB Migration questions

[Jan Schneider]

Zitat von Michael M Slusarz <slusarz at horde.org>:

> See, e.g.:
>
> Quoting Michael M Slusarz <slusarz at horde.org>:
>
>> commit e2e084a517dfe3c732b3147fddfb6d2cf481b65a
>> Author: Michael M Slusarz <slusarz at curecanti.org>
>> Date:   Mon May 24 17:19:02 2010 -0600
>>
>>    IMP tables now created via migration script
>>
>> imp/docs/INSTALL                    |   14 ++----
>> imp/migration/1_imp_base_tables.php |   46 ++++++++++++++++
>> imp/scripts/sql/imp.oci8.sql        |   15 -----
>> imp/scripts/sql/imp.sql             |   15 -----
>> imp/scripts/sql/imp.xml             |   97  
>> -----------------------------------
>
> Is this the way we want to create DB's in H4?  This is *much*  
> cleaner and easier than maintaining the scripts/sql directory.  The  
> issue I see is upgrading from H3 though.  How does that happen?
>
> I could see the following happening:
> app/
>   migration/
>     1_upgrade.php  (Upgrade that occurred from App 1.1.x to App 1.2.x)
>     2_upgrade.php  (Upgrade that occurred from App 1.2.x to App 2.0)
>     3_new_install.php  (Create tables)
>
> Tasks 1 (and/or 2) won't be run on a new install, since the tables  
> don't exist.  And these tasks will spit informational errors if the  
> upgrade has already occurred, but that should be ok (right?).

No, the migrations script keeps an schema table that has an  
incrementing schema version that is used to decide which scripts to  
run. A fresh install will run all scripts.
The solution is to create a 1_ script that installs the base tables  
from the stable versions. Then create more scripts for any upgrades  
during development. Users upgrading from a H3 stable version to a H4  
version have *once* provide the starting version when running  
db_migrate. Further runs work without specifying the version number.

Some example:
1_imp_4.php            (installs the same tables like IMP H3 (4.x))
2_upgrade_imp_5.php    (upgrades from 4 to 5)
3_upgrade_imp_5_1.php  (you get the idea)

For a fresh install, just run "db_migrate imp". Since there is no  
schema version in the database, all scripts will be run.

If upgrading from IMP 4 to 5 run "db_migrate imp 1" (or 2?). Since a  
version is provided, the database is not checked for a schema version  
and only the upgrade scripts will be run.

If upgrading from IMP 5.0 to 5.1, again just run "db_migrate imp".  
Since there is a schema version in the db now from the original IMP 5  
install/update, the script figures out to only run the 3rd script.

That's the theory at least, I haven't tested it yet. Chuck, correct me  
if I'm wrong.


There is still another "problem" though. Upgrade scripts in  
development vs. stable versions. I suggest that we do it similarly  
like we do it now. We just have to keep track of schema versions.  
Example:

In dev version:
4_add_some_missing_index.php
5_add_a_new_column.php
6_drop_another_one.php

This should be a single script in stable versions, but the schema  
version must match. I suggest that we even round the number up to the  
next tens. This looks cleaner and could even help for debugging:

10_app_x_to_y.php

Jan.

-- 
Do you need professional PHP or Horde consulting?
http://horde.org/consulting/