Develop CCDB

Development hints

Tools:

• Running $CCDB_HOME/python/ccdbcmd.py is the same as running ccdb command. This might be helpful for debugging, IDEs setup, etc:

  # same as > ccdb
  python3 $CCDB_HOME/python/ccdbcmd.py
  

• Running $CCDB_HOME/python/ccdbweb.py will start web-site or GUI for CCDB with your local setup

Tests organization

Setting up unit tests:

Unit tests look for two environment variables:

1. CCDB_TEST_MYSQL_CONNECTION
   if not found mysql://ccdb_user@localhost/ccdb_test is used
2. CCDB_TEST_SQLITE_CONNECTION
   if not found sqlite:///$CCDB_HOME/sql/ccdb.sqlite is used

It is assumed that for unit tests on MySQL database, the schema with name ccdb_test and a user with name ccdb_user are used.

To create a user

mysql -u root -p -e "CREATE USER ccdb_user@'localhost';"

To give a permission for the user

mysql -u root -p -e "GRANT ALL PRIVILEGES ON ccdb_test.* TO 'ccdb_user'@'localhost';"

There is a helpful script that allows to re/create the test database contents

> ccdb db init

MySQL and SQLite databases

• The main true source of MySQL database schema is MySQL-Workbench project: ccdb_eer_diagram.mwb. Every change of database layout is first made here, then propagated to scripts and SQLite.
• Python ccdb library has SQL scripts embedded in (python/ccdb/sql)[https://github.com/JeffersonLab/ccdb/tree/v2-main/python/ccdb/sql) directory. After ccdb_eer_diagram.mwb change they must synchronized there too (not ideal, make it better?)
• There are SQL scripts in sql directory, but they are obsolete and will gone with future versions

Upgrade CCDB database schema

To check current schema:

# Make sure your CCDB_CONNECTION set to the correct db or use -c flag
ccdb db

# or
ccdb -c mysql://[email protected]/ccdb db

To update (NEVER DO ON PRODUCTION SERVER WITHOUT PRIOR PROPER CHECKS):

ccdb db update
# follow the promt

Copy MySQL to local machine

If you have a local running MySQL and you would like to copy experiment running CCDB version to your local machine: (If local MySQL is not yet created, follow MySQL installation instruction)

# 1. Make a dump of production MySQL database: 
mysqldump -u ccdb_user -h hallddb.jlab.org ccdb > dump.mysql.sql

# 2. Import the database:
mysql -u ccdb_user -h localhost ccdb < dump.mysql.sql

Convert MySQL to SQLite

Newer, simpler to use projects for conversion of MySQL to SQLite are there such as python mysql-to-sqlite3

CCDB repository ships an old mysql2sqlite script (Authors: @esperlu, @artemyk, @gkuenning, @dumblob) that is used for conversion of HallD CCDB instance. The script takes MySQL flavored SQL file and converts it to SQLite flavored syntax. Taking HallD CCDB database as an example, here are the complete steps to convert the database:

# use standard mysqldump to read MySQL database into a file with SQL queries. 
mysqldump --skip-tz-utc -u ccdb_user -h hallddb.jlab.org ccdb > dump.mysql.sql

# convert MySQL flavored SQL to SQLite flavored SQL via mysql2sqlite 
./mysql2sqlite dump.mysql.sql > dump.sqlite3.sql

# Create DB out of SQL file
sqlite3 ccdb.sqlite3.db < backup.sql

mysqldump converts times to UTC by default (see #100), without --skip-tz-utc it would lead to a shift in time between mysql and sqlite (at least when using the scripts above)