Koha Database Structure Meeting
Hi all, Paul and I have done some cleaning of the kohastructure.sql file for the 3.0 release lately (see the following commitdiffs: http://git.koha.org/cgi-bin/gitweb.cgi?p=Koha;a=commitdiff;h=d60d540d6db5825... http://git.koha.org/cgi-bin/gitweb.cgi?p=Koha;a=commitdiff;h=b40c26cffedf10a... ). There are still a few remaining issues to clear up so we've decided to hold a meeting strictly about the Koha SQL definition for 3.0. The meeting's open to anyone interested in database design, feel free to join us on August 31st at 16:00 GMT. Here's the time in your area: http://tinyurl.com/2bteuw We'll be meeting on IRC at irc.katipo.co.nz on channel #koha, though you can also reach us via the freenode relay on #koha at freenode. I've put up a meeting agenda, please feel free to add to it: http://wiki.koha.org/doku.php?id=meetingnotes07aug31 Cheers, -- Joshua Ferraro SUPPORT FOR OPEN-SOURCE SOFTWARE President, Technology migration, training, maintenance, support LibLime Featuring Koha Open-Source ILS jmf@liblime.com |Full Demos at http://liblime.com/koha |1(888)KohaILS
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
Paul POULAIN <paul.poulain@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/
MJ Ray <mjr@phonecoop.coop> wrote: [...]
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?
So are there no changes which couldn't be done with SQL statements and MySQL 5 functions? Regards, -- 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/
MJ Ray a écrit :
Paul POULAIN <paul.poulain@free.fr> wrote:
Let me know what you think of this (during the chat or on the list) 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.
that's how it works atm.
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:
You're right, but we want : - to have a single authoritative file - "automatic" updates From 2 pick 1 i'm afraid : if we have a single authoritative SQL file, then we can't have an updatedatabase. If we have an updatedatabase and an authoritative SQL file, then we have 2 authoritative places. the alternative could be to have kohastructure.30.sql, then 3.00.00.001.sql, 3.00.00.002.sql, ... and apply all of them. But we would not have a single entry point. And that would let open the question of having a non SQL-only update. With nested SQL queries, we can do very interesting & powerfull things, so I agree that we could never need to do Perl hacking.
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.
In fact it's what is done now, except the SQL is embeeded in the updatedatabase script. If you want to move them outside & load updates/*.sql files, I won't object (except that the load must be ordered, so we will have to check carefully what we do)
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.
You're probably right, I can't remember why I did that.
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?
++
- should we use perl-calculated updates every time, or only as a last resort?
I think if we go SQL, we can't mix, unless doing complex (and probably ugly) things) -- 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
participants (3)
-
Joshua M. Ferraro -
MJ Ray -
Paul POULAIN