Difference between revisions of "SQLite-form of the CCDB database"
(→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
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 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