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

From GlueXWiki
Jump to: navigation, search
(Using the SQLite File at JLab)
 
(16 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.
 
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.
  
Line 8: Line 10:
 
  sqlite:////absolute/path/to/file.db
 
  sqlite:////absolute/path/to/file.db
  
Note that three /'s indicate a relative path, four /'s an absolute path.  
+
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,
 
For example, sqlite database is /home/user/ccdb.sqlite then to use it with the CCDB software,
Line 22: Line 24:
 
  setenv JANA_CALIB_URL sqlite:////home/user/ccdb.sqlite
 
  setenv JANA_CALIB_URL sqlite:////home/user/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 .
+
 
 +
''(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

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