SQLite-form of the CCDB database
Contents
Overview
The CCDB can be accessed as an SQLite file. Full history and read/write access is supported. However note that there is no current automatic mechanism for backporting changes from a modified SQLite database to the main MySQL database.
By its nature SQLite requires no network connection and no additional software is required.
Legal forms of the CCDB_CONNECTION URL are:
sqlite:///relative/path/to/file.db sqlite:////absolute/path/to/file.db
Note that three "/"s indicate a relative path, four "/"s an absolute path.
For example, sqlite database is /home/user/ccdb.sqlite then to use it with the CCDB software,
setenv CCDB_CONNECTION sqlite:////home/user/ccdb.sqlite
or supply the URL directly to CCDB (interactive version shown),
ccdb -i -c sqlite:////home/user/ccdb.sqlite
To use it with JANA,
setenv JANA_CALIB_URL sqlite:////home/user/ccdb.sqlite
(n.b. to use the MySQL DB set JANA_CALIB_URL and CCDB_CONNECTION to mysql://ccdb_user@hallddb.jlab.org/ccdb)
Nightly generated SQLite file
An new SQLite database is created at midnight. The latest version is available for download from the web at
https://halldweb.jlab.org/dist/ccdb.sqlite .
This file is on the disk at JLab at
/group/halld/www/halldweb/html/dist/ccdb.sqlite .
Older versions of the CCDB can be found at JLab in /group/halld/Software/calib/ccdb_sqlite . See the section below on archiving old versions for more details on what is stored there.
The database is created by a cron job runing nightly from the "gluex" account on jlabl5.jlab.org. The following script is used:
/home/gluex/bin/ccdb_sqlite_create.sh
This script is kept in the subversion repository here:
https://halldsvn.jlab.org/repos/trunk/home/gluex/bin
The cron script in turn invokes a script that comes with CCDB with the following command:
$CCDB_HOME/scripts/mysql2sqlite/mysql2sqlite.sh -hhalldweb1 -uccdb_user ccdb | sqlite3 $sqlite_file
where the $sqlite_file variable is set in the ccdb_sqlite_create.sh script. The setting is based on the current date.
Using the SQLite File at JLab
To use the file with your JANA-based program at JLab
setenv JANA_CALIB_URL sqlite:////group/halld/www/halldweb/html/dist/ccdb.sqlite
for C-shell and
export JANA_CALIB_URL=sqlite:////group/halld/www/halldweb/html/dist/ccdb.sqlite
for Bourne shell. Using the SQLite form of the CCDB is important when you run multiple farm or grid jobs. Otherwise the MySQL database server gets overwhelmed (too many connections).
Archive of SQLite Files
The older versions kept at JLab in /group/halld/Software/calib/ccdb_sqlite are classified and automatically deleted as follows:
Classification | Day of the Month | Deletion Time |
---|---|---|
Monthly | 7th | one year after last access |
Weekly | 7th, 14th, 21st, 28th | one month after last access |
Daily | every day | one week after last access |
Generating an SQLite file from the MySQL DB
To create your own SQLite file from the MySQL DB, do the following:
$CCDB_HOME/scripts/mysql2sqlite/mysql2sqlite.sh -hhallddb.jlab.org -uccdb_user ccdb | sqlite3 ccdb.sqlite
In order for this to work, you must specify an output file on a non-lustre filesystem and will need access to the mysql port on the hallddb server. NOTE: The Hall-D internal network generally cannot connect to this directly. Issue the following command from a computer like jlabl1.jlab.org