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

From GlueXWiki
Jump to: navigation, search
(Nightly generated SQLite files)
Line 29: Line 29:
  
 
== Nightly generated SQLite files ==
 
== Nightly generated SQLite files ==
An SQLite database is created at midnight. The latest version is available for download [https://halldweb1.jlab.org/dist/ccdb.sqlite here] . Older versions of the CCDB can be found at JLab in /group/halld/Software/calib/ccdb_sqlite .
+
An SQLite database is created at midnight. The latest version is available for download [https://halldweb1.jlab.org/dist/ccdb.sqlite here] . Older versions of the CCDB can be found at JLab in /group/halld/Software/calib/ccdb_sqlite . See the section on this archive below for more detail
  
 
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:
 
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:
Line 45: Line 45:
 
where the ''$sqlite_file'' variable is set in the ''ccdb_sqlite_create.sh'' script based on the current date.
 
where the ''$sqlite_file'' variable is set in the ''ccdb_sqlite_create.sh'' script 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:
 +
 +
{| border
 +
|-
 +
! 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 ==
 
== Generating an SQLite file from the MySQL DB ==

Revision as of 20:09, 20 September 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 . See the section on this archive below for more detail

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.

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