[dev] DB Migration questions

[Michael M Slusarz]

Quoting Jan Schneider <jan at horde.org>:

> Zitat von Michael M Slusarz <slusarz at horde.org>:
>
>> 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)

The problem with this - what version of IMP 4 do you create the  
initial script?  The current version of IMP H3 stable?  But what  
happens if something changes in the future, and a version of IMP H3  
stable requires a DB upgrade (granted, there is supposed to be no DB  
changes between versions, but worst-case scenario...)

> 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.

Couldn't this easily be handled by some checks in the migration  
scripts though?  i.e. the migration scripts are run (db_migrate imp),  
if no schema table is found we attempt to run ALL scripts, but table  
creation scripts could easily add a table exists check and skip the  
creation if the table already exists.  This prevents users from having  
to know what exact migrate version number they need to enter.

> 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

I agree it is cleaner, but I don't think it is terrible if we have a  
bunch of small scripts either.  It actually makes things clearer, and  
might be easier to debug for an admin (if the DB upgrade fails in  
schema update 5, for example, that might be easier than trying to  
figure out where it failed within jumbo script 10).  It will be easier  
to backout changes also if using the smaller developer scripts.  In  
other words, I don't see a pressing need to create a release version  
of the schema scripts - it seems like it is just going to add more  
complexity when building a release.

michael

-- 
___________________________________
Michael Slusarz [slusarz at horde.org]