Project

General

Profile

Feature #1992

Access ConfigDB as a local file

Added by Nebrensky, Henry almost 4 years ago. Updated almost 3 years ago.

Status:
Open
Priority:
Normal
Assignee:
Nebrensky, Henry
Category:
Database
Start date:
25 November 2019
Due date:
31 December 2019 (over 3 years late)
% Done:

0%

Estimated time:

Description

I'd like to be able to query the ConfigDB (read-only) without requiring internet access.

Thought was to take an SQL dump, read it into say SQLite, and query it there.
Will also need to find a human-readable version of the schema.

#2

Updated by Nebrensky, Henry almost 4 years ago

A copy of the SQL dumped from postgres is in /srv/calibration_data/cdb on heplnm072.

#4

Updated by Nebrensky, Henry almost 3 years ago

(Finally recovered history from host after Covid and power cut outage)

sqlite3 ConfigDB_Oct2019.sqlite creates a blank native SQLite file. Then at SQLite prompt

.read cdb_pg_dump-Oct2019.sql
.quit

should fill it out and save it. Instead (on 8GB server) get

sqlite> .read cdb_pg_dump-Oct2019.sql
Error: near line 5: near "SET": syntax error
Error: near line 6: near "SET": syntax error
Error: near line 7: near "SET": syntax error
...
Error: near line 89871: near "MICE": syntax error
Error: near line 89881: near "1999": syntax error
Error: out of memory

so PostGres -> SQLite not as direct as claimed.

Full:

sqlite> .read cdb_pg_dump-Oct2019.sql
Error: near line 5: near "SET": syntax error
Error: near line 6: near "SET": syntax error
Error: near line 7: near "SET": syntax error
Error: near line 8: near "SET": syntax error
Error: near line 9: near "SET": syntax error
Error: near line 15: near "EXTENSION": syntax error
Error: near line 22: near "COMMENT": syntax error
Error: near line 25: near "SET": syntax error
Error: near line 27: near "SET": syntax error
Error: near line 29: near "SET": syntax error
Error: near line 35: near "(": syntax error
Error: near line 48: near "OWNER": syntax error
Error: near line 62: near "OWNER": syntax error
Error: near line 68: near "SEQUENCE": syntax error
Error: near line 76: near "OWNER": syntax error
Error: near line 82: near "SEQUENCE": syntax error
Error: near line 100: near "OWNER": syntax error
Error: near line 117: near "OWNER": syntax error
Error: near line 130: near "OWNER": syntax error
Error: near line 145: near "OWNER": syntax error
Error: near line 151: near "SEQUENCE": syntax error
Error: near line 159: near "OWNER": syntax error
Error: near line 173: near "OWNER": syntax error
Error: near line 187: near "OWNER": syntax error
Error: near line 193: near "SEQUENCE": syntax error
Error: near line 201: near "OWNER": syntax error
Error: near line 207: near "SEQUENCE": syntax error
Error: near line 222: near "OWNER": syntax error
Error: near line 238: near "OWNER": syntax error
Error: near line 253: near "OWNER": syntax error
Error: near line 259: near "SEQUENCE": syntax error
Error: near line 267: near "OWNER": syntax error
Error: near line 273: near "SEQUENCE": syntax error
Error: near line 294: near "OWNER": syntax error
Error: near line 310: near "OWNER": syntax error
Error: near line 316: near "SEQUENCE": syntax error
Error: near line 324: near "OWNER": syntax error
Error: near line 330: near "SEQUENCE": syntax error
Error: near line 351: near "OWNER": syntax error
Error: near line 369: near "OWNER": syntax error
Error: near line 382: near "OWNER": syntax error
Error: near line 388: near "SEQUENCE": syntax error
Error: near line 396: near "OWNER": syntax error
Error: near line 402: near "SEQUENCE": syntax error
Error: near line 422: near "OWNER": syntax error
Error: near line 441: near "OWNER": syntax error
Error: near line 455: near "OWNER": syntax error
Error: near line 469: near "OWNER": syntax error
Error: near line 484: near "OWNER": syntax error
Error: near line 490: near "SEQUENCE": syntax error
Error: near line 498: near "OWNER": syntax error
Error: near line 504: near "SEQUENCE": syntax error
Error: near line 519: near "OWNER": syntax error
Error: near line 525: near "SEQUENCE": syntax error
Error: near line 533: near "OWNER": syntax error
Error: near line 539: near "SEQUENCE": syntax error
Error: near line 554: near "OWNER": syntax error
Error: near line 568: near "OWNER": syntax error
Error: near line 584: near "OWNER": syntax error
Error: near line 590: near "SEQUENCE": syntax error
Error: near line 598: near "OWNER": syntax error
Error: near line 604: near "SEQUENCE": syntax error
Error: near line 622: near "OWNER": syntax error
Error: near line 628: near "SEQUENCE": syntax error
Error: near line 636: near "OWNER": syntax error
Error: near line 642: near "SEQUENCE": syntax error
Error: near line 668: near "OWNER": syntax error
Error: near line 685: near "OWNER": syntax error
Error: near line 699: near "OWNER": syntax error
Error: near line 711: near "OWNER": syntax error
Error: near line 725: near "OWNER": syntax error
Error: near line 738: near "OWNER": syntax error
Error: near line 753: near "OWNER": syntax error
Error: near line 759: near "SEQUENCE": syntax error
Error: near line 767: near "OWNER": syntax error
Error: near line 792: near "OWNER": syntax error
Error: near line 816: near "OWNER": syntax error
Error: near line 829: near "OWNER": syntax error
Error: near line 840: near "OWNER": syntax error
Error: near line 854: near "OWNER": syntax error
Error: near line 873: near "OWNER": syntax error
Error: near line 886: near "OWNER": syntax error
Error: near line 900: near "OWNER": syntax error
Error: near line 914: near "OWNER": syntax error
Error: near line 932: near "OWNER": syntax error
Error: near line 945: near "OWNER": syntax error
Error: near line 959: near "OWNER": syntax error
Error: near line 972: near "OWNER": syntax error
Error: near line 984: near "OWNER": syntax error
Error: near line 990: near "absorbertag": syntax error
Error: near line 997: near "beamlinetag": syntax error
Error: near line 1004: near "cablingdetector": syntax error
Error: near line 1011: near "calibrationdetector": syntax error
Error: near line 1018: near "coolingchannel": syntax error
Error: near line 1025: near "coolingchannelrun": syntax error
Error: near line 1032: near "coolingchanneltag": syntax error
Error: near line 1039: near "geometry": syntax error
Error: near line 1046: near "geometrycorrections": syntax error
Error: near line 1053: near "COPY": syntax error
Error: near line 1054: near "7928": syntax error
Error: near line 7320: near "1": syntax error
Error: near line 7338: near "COPY": syntax error
Error: near line 7339: near "1": syntax error
Error: near line 7359: near "2010": syntax error
Error: near line 7401: near "2010": syntax error
Error: near line 7411: near "2015": syntax error
Error: near line 7428: near "COPY": syntax error
Error: near line 7429: near "1": syntax error
Error: near line 11266: near "1": syntax error
Error: near line 11438: near "COPY": syntax error
Error: near line 11439: near "1": syntax error
Error: near line 11605: near "2": syntax error
Error: near line 11869: near "1": syntax error
Error: near line 11904: near "COPY": syntax error
Error: near line 11905: unrecognized token: "\" 
Error: near line 11913: near "1": syntax error
Error: near line 12059: near "COPY": syntax error
Error: near line 12060: near "TRACKER": syntax error
Error: near line 20260: unrecognized token: "\" 
Error: near line 27981: near "2": syntax error
Error: near line 29748: near "COPY": syntax error
Error: near line 29749: near "1": syntax error
Error: near line 73465: near "1": syntax error
Error: near line 75502: near "1": syntax error
Error: near line 87016: near "1": syntax error
Error: near line 87573: near "5000": syntax error
Error: near line 89670: near "COPY": syntax error
Error: near line 89671: near "1": syntax error
Error: near line 89870: near "COPY": syntax error
Error: near line 89871: near "MICE": syntax error
Error: near line 89881: near "1999": syntax error
Error: out of memory

Also available in: Atom PDF