[dev] DB Migration questions

[Jan Schneider]

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

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

I think we can't cover all eventualities with this approach, but the  
most straight forward.
We can still provide all (traditional) upgrade scripts from the stable  
versions, so that people can first run them to get to latest stable  
scheme version. This is not different from today's upgrade scripts. If  
people upgrade from say 1.1 to 2.0, but 1.2 required a db upgrade,  
they first have to upgrade the db to 1.2 before going to 2.0.
We should ignore the very unlikely case that we'll change the db in a  
H3 app after the H4 version has already been released.

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

This seems a bit fragile to me. I think explicitly providing a schema  
version with the migration script is much safer than assuming some  
schema version from the mere existence of a table.

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

Makes sense, this would only be for cosmetical purposes anyway. Even  
with a bunch of upgrade scripts, the admin still only has to run a  
single script. Or no script at all.
We could actually easily detect from the config overview whether an  
application has an outdated db schema (like we show the outdated  
configuration already), and have them updating their schema with a  
single click.

Jan.

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