Cannot read CLAS12's RCDB from SQLite

[baltzell]

Not specific to this repo, just posting here to keep it on our radar. The problem could be Java-specific and in the RCDB Java library, or in our conversion from SQL to SQLite, or with some rogue tables in RCDB, etc.

[baltzell]

An example exception trace from COATJAVA:

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such table: files_have_runs)
      at org.sqlite.DB.newSQLException(DB.java:383)
      at org.sqlite.DB.newSQLException(DB.java:387)
      at org.sqlite.DB.throwex(DB.java:374)
      at org.sqlite.NativeDB.prepare(Native Method)
      at org.sqlite.DB.prepare(DB.java:123)
      at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
      at org.sqlite.Conn.prepareStatement(Conn.java:404)
      at org.sqlite.Conn.prepareStatement(Conn.java:399)
      at org.sqlite.Conn.prepareStatement(Conn.java:383)
      at org.rcdb.SQLiteProvider.connectJDBC(SQLiteProvider.kt:35)
      at org.rcdb.SQLiteProvider.connect(SQLiteProvider.kt:21)
      at org.jlab.detector.calib.utils.RCDBProvider.initialize(RCDBProvider.java:68)
      at org.jlab.detector.calib.utils.RCDBProvider.<init>(RCDBProvider.java:28)
      at org.jlab.detector.calib.utils.ConstantsManager.loadConstantsForRun(ConstantsManager.java:142)
      at org.jlab.detector.calib.utils.ConstantsManager.getConstants(ConstantsManager.java:80)
      at org.jlab.rec.rich.RICHEBEngine.init_CCDB(RICHEBEngine.java:145)
      at org.jlab.rec.rich.RICHEBEngine.init(RICHEBEngine.java:49)
      at org.jlab.clas.reco.EngineProcessor.addEngine(EngineProcessor.java:168)
      at org.jlab.clas.reco.EngineProcessor.addEngine(EngineProcessor.java:187)
      at org.jlab.clas.reco.EngineProcessor.main(EngineProcessor.java:338)

[baltzell]

The printouts from the conversion to SQLite (which I've so far ignored):

memory
Error: near line 26: near "FOREIGN": syntax error
Error: near line 41: near "FOREIGN": syntax error
Error: near line 187493: near "FOREIGN": syntax error
Error: near line 197327: no such table: main.board_configurations_have_runs
Error: near line 197328: no such table: main.board_configurations_have_runs
Error: near line 197332: no such table: main.board_installations_have_runs
Error: near line 197333: no such table: main.board_installations_have_runs
Error: near line 197342: no such table: main.files_have_runs
Error: near line 197343: no such table: main.files_have_runs
/u/home/clas12
sending incremental file list
deleting rcdb_2023-01-27.sqlite
deleting rcdb_2023-01-26.sqlite
./
LATEST
latest.sqlite -> rcdb_2023-02-04.sqlite
rcdb_2023-02-04.sqlite

sent 17,225,317 bytes  received 112 bytes  34,450,858.00 bytes/sec
total size is 137,411,841  speedup is 7.98

[baltzell]

And finally the script that does the conversion to SQLite:

https://github.com/JeffersonLab/clas12-utilities/blob/master/bin/clas12-rcdb-mysql2sqlite.py

[baltzell]

Fixed with #245 in ccdb library.