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

From GlueXWiki
Jump to: navigation, search
(Using the SQLite File at JLab)
 
(13 intermediate revisions by 2 users not shown)
Line 28: Line 28:
  
  
== Nightly generated SQLite files ==
+
== Nightly generated SQLite file ==
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 .
+
  
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:
+
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
 
  /home/gluex/bin/ccdb_sqlite_create.sh
  
This is kept in the subversion repository here:
+
This script is kept in the subversion repository here:
  
 
  https://halldsvn.jlab.org/repos/trunk/home/gluex/bin
 
  https://halldsvn.jlab.org/repos/trunk/home/gluex/bin
  
This, in turn calls the following script that comes with CCDB.
+
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
 
  $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.
+
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 ==
 
== Generating an SQLite file from the MySQL DB ==
Line 50: Line 85:
 
To create your own SQLite file from the MySQL DB, do the following:
 
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
+
  $CCDB_HOME/scripts/mysql2sqlite/mysql2sqlite.sh -hhallddb.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
+
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