summaryrefslogtreecommitdiffstatshomepage
path: root/scripts/minimaws/lib/dbaccess.py
diff options
context:
space:
mode:
Diffstat (limited to 'scripts/minimaws/lib/dbaccess.py')
-rw-r--r--scripts/minimaws/lib/dbaccess.py203
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)