Difference between revisions of "SQLite-form of the CCDB database"

Revision as of 09:33, 2 May 2014

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 .

The database is created by a cron job run from the "gluex" account on jlabl1.jlab.org. To create the database, the following script is run:

/home/gluex/bin/ccdb_sqlite_create.sh

This is kept in the subversion repository here:

https://halldsvn.jlab.org/repos/trunk/home/gluex/bin

This, in turn calls the following script that comes with CCDB.

$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 based on the current date.

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 -hhalldweb1.jlab.org -uccdb_user ccdb | sqlite3 ccdb.sqlite

In order for this to work, you will need access to the mysql port on the halldweb1 server. NOTE: The Hall-D internal network generally cannot connect to this directly. Issue the following command from a computer like jlabl1.jlab.org