SQLite-form of the CCDB database

From GlueXWiki
Revision as of 20:23, 31 March 2015 by Marki (Talk | contribs) (Text replacement - "/halldweb1.jlab.org/" to "/halldweb.jlab.org/")

Jump to: navigation, search

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 files

An SQLite database is created at midnight. The latest version is available for download here . 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.

The database is created by a cron job runing nightly from the "gluex" account on jlabl1.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.

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 never
Weekly 7th, 14th, 21st, 28th after one month
Daily every day after one week

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 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