[Halld-offline] CCDB database update and columnType is NULL fix

[Dmitry Romanov]

Hi, 
Next version of CCDB has an SQL schema update. It is backward compatible which means that 0.08 and 0.09 tags work fine with the updated database. 
But CCDB trunk requires the new database schema and will not work with old one.  


1. HallD users:
Regular users are not affected to the change. The only affected users are
those who use current trunk AND their own old databases (sqlite or mysql).

hallddb.jlab.org database has been updated. SQLite now is generated by cronjob and located at:
/group/halld/Software/calib/ccdb_sqlite/



2. How to migrate:
There are two sql patches to update sqlite and MySQL added:
$CCDB_HOME/sql/update_0.09_1.00.mysql.sql
$CCDB_HOME/sql/update_0.09_1.00.sqlite.sql

To patch a database run it like:
sqlite3 <your_db>.sqlite < $CCDB_HOME/sql/update_0.09_1.00.sqlite.sql



3. columnType is NULL
This fix has already been done for HallD hallddb.jlab.org database and thus for update SQLite database. 

Description:
CCDB 0.09 defines columnType to be 'double' if no column type is specified. 
CCDB 0.08 puts NULL for such columns. So data added by CCDB_0.08 may have columnType=NULL
It is not critical for constants readout but should be fixed. 

Fix:
To fix it, run next mysql commands(for example by: mysql -u ccdb_user -h localhost ccdb):
SET SQL_SAFE_UPDATES=0;
UPDATE `ccdb`.`columns` SET `columnType`='double' WHERE `columnType` is NULL;
SET SQL_SAFE_UPDATES=1;

Regards,
Dmitry