[Koha-devel] database analysis (diff between 22+updatedatabase and kohastructure.sql)

[Paul POULAIN]

I have reviewed all the diffs between my ipt3 database, that is a koha 
2.2 + updatedatabase and the head (3.0) kohastructure.sql

There are lots of varchar/char differences
===============================================
http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes.html says : 
  Before MySQL 5.0.3, VARCHAR columns with a length less than four are 
changed to CHAR.

OK, my database was on mySQL 4.1 previously, thus the differences. I 
discard them, it's meaningless anyway

There are a lot of NOT NULL default '' vs NOT NULL
==================================================
(or NOT NULL default 0 vs NOT NULL)
http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html says :
  If the column cannot take NULL as the value, MySQL defines the column 
with an explicit DEFAULT clause, using the implicit default value for 
the column data type. Implicit defaults are defined as follows:
For numeric types, the default is 0, with the exception that for integer 
types declared with the AUTO_INCREMENT attribute, the default is the 
next value in the sequence.
For date and time types other than TIMESTAMP, the default is the 
appropriate “zero” value for the type. For the first TIMESTAMP column in 
a table, the default value is the current date and time. See Section 
10.3, “Date and Time Types”.
For string types other than ENUM, the default value is the empty string. 
For ENUM, the default is the first enumeration value.

So the differences as meaningless, I discard them as well.



(hereafter, when I write x VS Y, I mean kohastructure.sql VS mydatabase)

action_logs : text vs mediumtext :
==================================
just differ on the length the record can contain, but even text is 
enough (http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html)

biblioitems
============
- volumedesc is text VS char(255). Harmless, text is better (although 
more space consuming)
- some columns (the new ones) are not at the same place in the table. 
harmless
- marc longblob vs blob : marc column is deprecated & could be dropped 
soon if I don't mind. howeber, longblob is better

browser
========
don't exist VS exist : this table is speficically created & filled by a 
specific script, I'll explain on request what it is for

categorytable
==============
text VS mediumtext : harmless, anyway, text is too large imho

charges
========
don't exist VS exist : kohastructure is right. we can drop it.

deletedbiblioitems
====================
dewey 8,6 VS char(30) : kohastructure must be modified to char(30), as 
in biblioitems.

deleteditems
================
barcode char(9) VS char(30) : kohastructure must be modified to 
char(30), char(9) is too small for some libraries

items
=================
homebranch : char(4) VS char(10) : this one is a major diff, that can 
cause bugs. ***MUST*** be changed in kohastructure, or contraints will 
fail.

itemsprices
=============
don't exist VS exist : kohastructure is right. we can drop it.

labels
==============
- batch_id exist VS don't exist : kohastructure probably right.
- ENGINE=myISAM VS ENGINE=InnoDB : MUST be changed in kohastructure

labels_conf :
===============
- lot of differences : my structure has to be updated (however, ipt 
don't use label facilities, so they don't care ;-) )
- ENGINE=myISAM VS ENGINE=InnoDB : MUST be changed in kohastructure

labels_templates :
===================
table has to be added on my side

letters & many other tables
==========
mediumtext VS text : harmless, both are acceptable. mediumtext maybe a 
little bit better although

marc_biblio
============
can be dropped on my side, I agree

mediatypetable :
=================
exist VS don't exist : i'm right imho, kohastructure should drop this table

sessionqueries
===============
don't exist VS exist : kohastructure is right. we can drop it.

subcategorytable
=================
exist VS don't exist : i'm right imho, kohastructure should drop this table

subscription
==============
firstacquidate : NULL default NULL vs NOT NULL. hdl will confirm, but I 
think NOT NULL is better here

subscriptionhistory
==============
histstartdate : NULL default NULL vs NOT NULL. hdl will confirm, but I 
think NOT NULL is better here

users/websites / z3950queue/ z3950queries
===============
don't exist VS exist : kohastructure is right. we can drop them

Foreign keys
==============
  on my side most constraints are repeated, I don't know why, but it's 
stupid




As a conclusion : I should do that test again on another 
22+updatedatabase config, to be sure, but it seems there are no so many 
diffs between kohastructure.sql & 22+updatedatabase. I'll let you know 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