-
Notifications
You must be signed in to change notification settings - Fork 16
Develop CCDB
-
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
Setting up unit tests:
Unit tests look for two environment variables:
-
CCDB_TEST_MYSQL_CONNECTION
if not foundmysql://ccdb_user@localhost/ccdb_test
is used -
CCDB_TEST_SQLITE_CONNECTION
if not foundsqlite:///$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
- 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. Afterccdb_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
To check current schema:
# Make sure your CCDB_CONNECTION set to the correct db or use -c flag
ccdb db
# or
ccdb -c mysql://ccdb_user@hallddb.jlab.org/ccdb db
To update (NEVER DO ON PRODUCTION SERVER WITHOUT PRIOR PROPER CHECKS):
ccdb db update
# follow the promt
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
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)