diff options
Diffstat (limited to 'scripts/minimaws/lib/dbaccess.py')
-rw-r--r-- | scripts/minimaws/lib/dbaccess.py | 203 |
1 files changed, 176 insertions, 27 deletions
diff --git a/scripts/minimaws/lib/dbaccess.py b/scripts/minimaws/lib/dbaccess.py index 59b22dfad5e..d453fe3d941 100644 --- a/scripts/minimaws/lib/dbaccess.py +++ b/scripts/minimaws/lib/dbaccess.py @@ -11,6 +11,124 @@ if sys.version_info >= (3, 4): class SchemaQueries(object): + CREATE_FEATURETYPE = \ + 'CREATE TABLE featuretype (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' name TEXT NOT NULL,\n' \ + ' UNIQUE (name ASC))' + CREATE_SOURCEFILE = \ + 'CREATE TABLE sourcefile (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' filename TEXT NOT NULL,\n' \ + ' UNIQUE (filename ASC))' + CREATE_MACHINE = \ + 'CREATE TABLE machine (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' shortname TEXT NOT NULL,\n' \ + ' description TEXT NOT NULL,\n' \ + ' sourcefile INTEGER NOT NULL,\n' \ + ' isdevice INTEGER NOT NULL,\n' \ + ' runnable INTEGER NOT NULL,\n' \ + ' UNIQUE (shortname ASC),\n' \ + ' UNIQUE (description ASC),\n' \ + ' FOREIGN KEY (sourcefile) REFERENCES sourcefile (id))' + CREATE_SYSTEM = \ + 'CREATE TABLE system (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' year TEXT NOT NULL,\n' \ + ' manufacturer TEXT NOT NULL,\n' \ + ' FOREIGN KEY (id) REFERENCES machine (id))' + CREATE_CLONEOF = \ + 'CREATE TABLE cloneof (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' parent TEXT NOT NULL,\n' \ + ' FOREIGN KEY (id) REFERENCES machine (id))' + CREATE_ROMOF = \ + 'CREATE TABLE romof (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' parent TEXT NOT NULL,\n' \ + ' FOREIGN KEY (id) REFERENCES machine (id))' + CREATE_BIOSSET = \ + 'CREATE TABLE biosset (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' machine INTEGER NOT NULL,\n' \ + ' name TEXT NOT NULL,\n' \ + ' description TEXT NOT NULL,\n' \ + ' UNIQUE (machine ASC, name ASC),\n' \ + ' FOREIGN KEY (machine) REFERENCES machine (id))' + CREATE_BIOSSETDEFAULT = \ + 'CREATE TABLE biossetdefault (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' FOREIGN KEY (id) REFERENCES biosset (id))' + CREATE_DEVICEREFERENCE = \ + 'CREATE TABLE devicereference (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' machine INTEGER NOT NULL,\n' \ + ' device INTEGER NOT NULL,\n' \ + ' UNIQUE (machine ASC, device ASC),\n' \ + ' FOREIGN KEY (machine) REFERENCES machine (id),\n' \ + ' FOREIGN KEY (device) REFERENCES machine (id))' + CREATE_DIPSWITCH = \ + 'CREATE TABLE dipswitch (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' machine INTEGER NOT NULL,\n' \ + ' isconfig INTEGER NOT NULL,\n' \ + ' name TEXT NOT NULL,\n' \ + ' tag TEXT NOT NULL,\n' \ + ' mask INTEGER NOT NULL,\n' \ + ' --UNIQUE (machine ASC, tag ASC, mask ASC), not necessarily true, need to expose port conditions\n' \ + ' FOREIGN KEY (machine) REFERENCES machine (id))' + CREATE_DIPLOCATION = \ + 'CREATE TABLE diplocation (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' dipswitch INTEGER NOT NULL,\n' \ + ' bit INTEGER NOT NULL,\n' \ + ' name TEXT NOT NULL,\n' \ + ' num INTEGER NOT NULL,\n' \ + ' inverted INTEGER NOT NULL,\n' \ + ' UNIQUE (dipswitch ASC, bit ASC),\n' \ + ' FOREIGN KEY (dipswitch) REFERENCES dipswitch (id))' + CREATE_DIPVALUE = \ + 'CREATE TABLE dipvalue (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' dipswitch INTEGER NOT NULL,\n' \ + ' name TEXT NOT NULL,\n' \ + ' value INTEGER NOT NULL,\n' \ + ' isdefault INTEGER NOT NULL,\n' \ + ' FOREIGN KEY (dipswitch) REFERENCES dipswitch (id))' + CREATE_FEATURE = \ + 'CREATE TABLE feature (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' machine INTEGER NOT NULL,\n' \ + ' featuretype INTEGER NOT NULL,\n' \ + ' status INTEGER NOT NULL,\n' \ + ' overall INTEGER NOT NULL,\n' \ + ' UNIQUE (machine ASC, featuretype ASC),\n' \ + ' FOREIGN KEY (machine) REFERENCES machine (id),\n' \ + ' FOREIGN KEY (featuretype) REFERENCES featuretype (id))' + CREATE_SLOT = \ + 'CREATE TABLE slot (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' machine INTEGER NOT NULL,\n' \ + ' name TEXT NOT NULL,\n' \ + ' UNIQUE (machine ASC, name ASC),\n' \ + ' FOREIGN KEY (machine) REFERENCES machine (id))' + CREATE_SLOTOPTION = \ + 'CREATE TABLE slotoption (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' slot INTEGER NOT NULL,\n' \ + ' device INTEGER NOT NULL,\n' \ + ' name TEXT NOT NULL,\n' \ + ' UNIQUE (slot ASC, name ASC),\n' \ + ' FOREIGN KEY (slot) REFERENCES slot (id),\n' \ + ' FOREIGN KEY (device) REFERENCES machine (id))' + CREATE_SLOTDEFAULT = \ + 'CREATE TABLE slotdefault (\n' \ + ' id INTEGER PRIMARY KEY,\n' \ + ' slotoption INTEGER NOT NULL,\n' \ + ' FOREIGN KEY (id) REFERENCES slot (id),\n' \ + ' FOREIGN KEY (slotoption) REFERENCES slotoption (id))' + CREATE_TEMPORARY_DEVICEREFERENCE = 'CREATE TEMPORARY TABLE temp_devicereference (id INTEGER PRIMARY KEY, machine INTEGER NOT NULL, device TEXT NOT NULL, UNIQUE (machine, device))' CREATE_TEMPORARY_SLOTOPTION = 'CREATE TEMPORARY TABLE temp_slotoption (id INTEGER PRIMARY KEY, slot INTEGER NOT NULL, device TEXT NOT NULL, name TEXT NOT NULL)' CREATE_TEMPORARY_SLOTDEFAULT = 'CREATE TEMPORARY TABLE temp_slotdefault (id INTEGER PRIMARY KEY, slotoption INTEGER NOT NULL)' @@ -47,6 +165,51 @@ class SchemaQueries(object): DROP_DIPSWITCH_MACHINE_ISCONFIG = 'DROP INDEX IF EXISTS dipswitch_machine_isconfig' + CREATE_TABLES = ( + CREATE_FEATURETYPE, + CREATE_SOURCEFILE, + CREATE_MACHINE, + CREATE_SYSTEM, + CREATE_CLONEOF, + CREATE_ROMOF, + CREATE_BIOSSET, + CREATE_BIOSSETDEFAULT, + CREATE_DEVICEREFERENCE, + CREATE_DIPSWITCH, + CREATE_DIPLOCATION, + CREATE_DIPVALUE, + CREATE_FEATURE, + CREATE_SLOT, + CREATE_SLOTOPTION, + CREATE_SLOTDEFAULT) + + CREATE_TEMPORARY_TABLES = ( + CREATE_TEMPORARY_DEVICEREFERENCE, + CREATE_TEMPORARY_SLOTOPTION, + CREATE_TEMPORARY_SLOTDEFAULT) + + CREATE_INDEXES = ( + INDEX_MACHINE_ISDEVICE_SHORTNAME, + INDEX_MACHINE_ISDEVICE_DESCRIPTION, + INDEX_MACHINE_RUNNABLE_SHORTNAME, + INDEX_MACHINE_RUNNABLE_DESCRIPTION, + INDEX_SYSTEM_YEAR, + INDEX_SYSTEM_MANUFACTURER, + INDEX_ROMOF_PARENT, + INDEX_CLONEOF_PARENT, + INDEX_DIPSWITCH_MACHINE_ISCONFIG) + + DROP_INDEXES = ( + DROP_MACHINE_ISDEVICE_SHORTNAME, + DROP_MACHINE_ISDEVICE_DESCRIPTION, + DROP_MACHINE_RUNNABLE_SHORTNAME, + DROP_MACHINE_RUNNABLE_DESCRIPTION, + DROP_SYSTEM_YEAR, + DROP_SYSTEM_MANUFACTURER, + DROP_ROMOF_PARENT, + DROP_CLONEOF_PARENT, + DROP_DIPSWITCH_MACHINE_ISCONFIG) + class UpdateQueries(object): ADD_FEATURETYPE = 'INSERT OR IGNORE INTO featuretype (name) VALUES (?)' @@ -362,6 +525,7 @@ class UpdateConnection(object): def __init__(self, database, **kwargs): super(UpdateConnection, self).__init__(**kwargs) self.dbconn = sqlite3.connect(database) + self.dbconn.execute('PRAGMA page_size = 4096') self.dbconn.execute('PRAGMA foreign_keys = ON') def commit(self): @@ -377,10 +541,16 @@ class UpdateConnection(object): return UpdateCursor(self.dbconn) def prepare_for_load(self): - self.drop_indexes() - self.dbconn.execute(SchemaQueries.CREATE_TEMPORARY_DEVICEREFERENCE) - self.dbconn.execute(SchemaQueries.CREATE_TEMPORARY_SLOTOPTION) - self.dbconn.execute(SchemaQueries.CREATE_TEMPORARY_SLOTDEFAULT) + # here be dragons - this is a poor man's DROP ALL TABLES etc. + self.dbconn.execute('PRAGMA writable_schema = 1') + self.dbconn.execute('delete from sqlite_master where type in (\'table\', \'index\', \'trigger\')') + self.dbconn.execute('PRAGMA writable_schema = 0') + for query in SchemaQueries.DROP_INDEXES: + self.dbconn.execute(query) + for query in SchemaQueries.CREATE_TABLES: + self.dbconn.execute(query) + for query in SchemaQueries.CREATE_TEMPORARY_TABLES: + self.dbconn.execute(query) self.dbconn.commit() def finalise_load(self): @@ -393,27 +563,6 @@ class UpdateConnection(object): self.dbconn.execute(UpdateQueries.FINALISE_SLOTDEFAULTS) self.dbconn.commit() self.dbconn.execute(SchemaQueries.DROP_TEMPORARY_SLOTDEFAULT) - self.create_indexes() + for query in SchemaQueries.CREATE_INDEXES: + self.dbconn.execute(query) self.dbconn.commit() - - def drop_indexes(self): - self.dbconn.execute(SchemaQueries.DROP_MACHINE_ISDEVICE_SHORTNAME) - self.dbconn.execute(SchemaQueries.DROP_MACHINE_ISDEVICE_DESCRIPTION) - self.dbconn.execute(SchemaQueries.DROP_MACHINE_RUNNABLE_SHORTNAME) - self.dbconn.execute(SchemaQueries.DROP_MACHINE_RUNNABLE_DESCRIPTION) - self.dbconn.execute(SchemaQueries.DROP_SYSTEM_YEAR) - self.dbconn.execute(SchemaQueries.DROP_SYSTEM_MANUFACTURER) - self.dbconn.execute(SchemaQueries.DROP_ROMOF_PARENT) - self.dbconn.execute(SchemaQueries.DROP_CLONEOF_PARENT) - self.dbconn.execute(SchemaQueries.DROP_DIPSWITCH_MACHINE_ISCONFIG) - - def create_indexes(self): - self.dbconn.execute(SchemaQueries.INDEX_MACHINE_ISDEVICE_SHORTNAME) - self.dbconn.execute(SchemaQueries.INDEX_MACHINE_ISDEVICE_DESCRIPTION) - self.dbconn.execute(SchemaQueries.INDEX_MACHINE_RUNNABLE_SHORTNAME) - self.dbconn.execute(SchemaQueries.INDEX_MACHINE_RUNNABLE_DESCRIPTION) - self.dbconn.execute(SchemaQueries.INDEX_SYSTEM_YEAR) - self.dbconn.execute(SchemaQueries.INDEX_SYSTEM_MANUFACTURER) - self.dbconn.execute(SchemaQueries.INDEX_ROMOF_PARENT) - self.dbconn.execute(SchemaQueries.INDEX_CLONEOF_PARENT) - self.dbconn.execute(SchemaQueries.INDEX_DIPSWITCH_MACHINE_ISCONFIG) |