Difference between revisions of "SQLite-form of the CCDB database"
(→Using the SQLite File at JLab) |
|||
(18 intermediate revisions by 3 users not shown) | |||
Line 1: | Line 1: | ||
− | + | == 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 | + | 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 [[#Archive of SQLite Files|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: | ||
+ | |||
+ | {| border | ||
+ | |- | ||
+ | ! 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 |
Latest revision as of 10:34, 12 February 2020
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 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