SQLite-form of the CCDB database

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.

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