[Koha-devel] Database Structure [update strategy : proposal]

[MJ Ray]

Paul POULAIN <paul.poulain at free.fr> wrote:
> Let me know what you think of this (during the chat or on the list)

Well, we certainly did that.  The full meeting log is
http://koha.org/cgi-bin/logs.pl?user=&channel=%23koha&action=&text=&user-ddl=&channel-ddl=&action-ddl=&startdate=2007083108%3A50&enddate=2007083110%3A51&saveas=&search=Search
and I expect Joshua will be along soon with a complaint about us
squabbling, erm, I mean short summary of the meeting.

Key points from this email:

> what is interesting with that behaviour is that you just have to install 
> a new version, and the librarian will automatically be redirected to the 
> update page before login, and see what has been done. [...]

This should be possible by comparing the version stored in the
database with the $C4::Context::VERSION value, however it happens.

> After that, kohastructure.sql is the authoritative file for 3.0 structure
> kohastructure.sql + updatedatabase applied is the authoritative 
> structure for 3.X version.

That would mean a new developer starting at release 3.X could not
check kohastructure.sql to see the database layout.  I seem to recall
that I struggled with 2.0 until you sent me a working koha.sql file.
I was also asking back then "why updatedatabase instead of SQL?" and
didn't get a clear answer:

http://lists.gnu.org/archive/html/koha-devel/2003-07/msg00012.html
  From: MJ Ray
  Subject: Re: [Koha-devel] Systempreferences.pl
  Date: Tue Jul 1 15:46:06 2003

Having to run updatedatabase and check the result has been one of the
most awkward bits of koha development and debugging.  It was
introduced back in 1.2 and it seems to have been causing problems
right from the start.

We should update kohastructure.sql to be the authoritative file in
each release.  The lack of One Right Place even confused this guy:

http://lists.gnu.org/archive/html/koha-devel/2003-04/msg00532.html
  From: paul POULAIN
  Subject: [Koha-devel] table itemtypesearchgroups ?
  Date: Thu Apr 24 08:39:07 2003

;-)

> So, now, the updater/updatedatabase contains 2 parts :
> - The 1st part contains what has to be done to migrate from 2.2 to 3.0

I think that should probably stay as it is, now that it's done.

> - The 2nd part deals with the future, that isolates what has to be done 
> to move from X.Y.Z.T to X'.Y'.Z'.T'

I strongly feel that this should be done with a sequence of SQL files,
each one updating X.Y.Z.T to X.Y.Z.T+1 - one version at a time.  These
could be loaded by the webinstaller with similar routines to the
current data files.  Seems easier and more portable than calling
system() on another script to me.

Looking at the 3.0 series changes in updatedatabase, the only thing
that isn't possible is DropAllForeignKeys - but why is that there?
We should know what foreign keys we were using and DROP them one by one.
If we run DropAllForeignKeys on a table, we will also DROP any foreign
keys installed by a contrib.

paul, hdl and ryan (+ anyone else?) all made the point that one day,
we may want to make a change that isn't possible with SQL alone.  That
raises a few questions:

- are such fundamental changes a good idea without a version update?

- have we ever made a change which couldn't be done with SQL statements
and MySQL 5 functions?

- should we use perl-calculated updates every time, or only as a last
resort?

Thanks,
-- 
MJ Ray - see/vidu http://mjr.towers.org.uk/email.html
Experienced webmaster-developers for hire http://www.ttllp.co.uk/
Also: statistician, sysadmin, online shop builder, workers co-op.
Writing on koha, debian, sat TV, Kewstoke http://mjr.towers.org.uk/