[imp] Upgrading DB schemas

[R Phillips]

On 03/01/12 15:16, Michael J Rubinsky wrote:
> 
> Quoting R Phillips <R.I.Phillips at bath.ac.uk>:
> 
>> On 03/01/12 14:50, Michael J Rubinsky wrote:
>>>
>>> Quoting R Phillips <R.I.Phillips at bath.ac.uk>:
>>>
>>>> When upgrading minor versions, say from the Webmail groupware edition
>>>> from 4.0.3 to 4.0.5 there are some DB scheme changes.
>>>>
>>>> Is it possible to see whether those changes are significant, in that an
>>>> upgraded test install of horde/imp could be run against the same
>>>> database as a current live version?
>>>>
>>>> It's probably best to copy the existing database and run the test
>>>> version against the copy, but if the changes are limited to additional
>>>> database indexes/new columns then it would be possible without
>>>> affecting
>>>> the live version.  Other than actually taking a copy and comparing
>>>> after
>>>> upgrade, is there an easy way of comparing the old/new schema?
>>>
>>>
>>> If information in docs/UPGRADING or docs/CHANGES is not enough, you can
>>> look at the migration scripts to see what is being changed. e.g.,
>>> turba/migration contains the scripts that build all the tables and apply
>>> all the updates to the tables for Turba.
>>
>>
>> I did have a look at the migration folders, but was a bit puzzled.
>>
>> To take specifics, I'm asked to upgrade the DB schema for
>> Horde_sessionhandler.
>>
>> Looking at the script in migration folder I see:
>>
>>     public function up()
>>     {
>>         if (!in_array('horde_sessionhandler', $this->tables())) {
>>             $t = $this->createTable('horde_sessionhandler',
>> array('autoincrementKey' => array('session_id')));
>>
>> ... snippped
>>
>>
>> which implies to me a create table, and not an upgrade so I'm slighly
>> puzzled.
> 
> The migrations are additive, so each new version will still contain all
> migrations. The highest number migration is the most recent change.
> 
>> The changes refer to a change in the SQL from REPLACE to an
>> INSERT/UPDATE, but not a schema change as far as I can see.
> 
> Ah, I assumed you were talking about schema changes. It sounds to me
> that the change you are referring to is a change in the code/logic that
> writes to the database. No other real way of seeing those changes other
> than reading the CHANGES and looking at the code.



Indeed I was referring to schema changes.  However, my mistake was the
table schema that needed to be updated, it wasn't Horde_Sessionhandler,
but the one above in the admin/config list; Horde_Prefs... whoops.  Now
this all makes a lot more sense.  I can see the second migration file
and I assume that the <tablename>_schema_info table contains the prefix
number of the migration file in question...

All makes more sense.  And in this case the text field (which I assume
gets converted to a longtext with MySQL in my case, is changed to
"binary" (assume a 'blob' here).

Mystery solved - and the "down" "up" methods make more sense to me to
now, downgrade and upgrade.


>> I suppose it's good practice not to test on a live database... so I'll
>> do that instead!
> 
> Always a good idea.
> 
>