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

[Paul POULAIN]

Hello guys,

I wanted to share my thought about DB structure handling, following the 
july discussion [koha 3 updatedatabase and databasestructure.sql]

In Koha 2.0/2.2, the database management was done through :
- koha.sql, that contained a 2.0 sql version of the database
- updater/updatedatabase script, that was a huge file that contained all 
database evolutions. What made the script heavy was that it was checking 
all updates before doing them. For example "does the table XXX exist ? 
NO => create it. Does the field YYY exist ? NO => create it"
It is very hard sometimes to check that something is done or not.

Another bad point about updatedatabase is that all actions were grouped 
by type, not by time. I explain : all table creations are in the same 
area, all the field updates too, all field content too... So, it's 
almost impossible to know when things are done. This was not a major 
problem, as the script take care of wether it is already done or not.


In Koha 3.0, I proposed to add a C4::Context->KOHAVERSION (1) management 
and it was accepted (with enthusiasm ?)
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
- 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'

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.
What is interesting too is that from a developper pov, if I make an 
update, other developpers should, when updating their repository, be 
automatically redirected as well, they don't have to check 
updatedatabase changes.


What kind of strategy for 3.0 release ?
I think 2 cases have to be distinguished :
- fresh install
- update from 2.2

fresh install :
The authoritative database is and must be kohastructure.sql

update from 2.2 :
we need a tool to move from 2.2 DB to 3.0 DB
updatedatabase does the stuff.
but I think we should split updatedatabase in 2 parts to get something 
more easy to read :
- update22to30
- updatedatabase (from 3.0 to ...)

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

When a 4.0 is about to be released, we could dump kohastructure.sql to 
4.0 and clean updatedatabase again (& having a update30to40 for 3.0 to 
4.0 updates)

I agree if you say my main idea is that kohastructure.sql is updated 
only on X version change.

Let me know what you think of this (during the chat or on the list)

(1) slef, I agree with your Context->VERSION suggestion, i'll do it asap
-- 
Paul POULAIN et Henri Damien LAURENT
Consultants indépendants en logiciels libres et bibliothéconomie 
(http://www.koha-fr.org)
Tel : 04 91 31 45 19