summaryrefslogtreecommitdiffstatshomepage
path: root/scripts/minimaws
diff options
context:
space:
mode:
author Vas Crabb <vas@vastheman.com>2017-08-04 18:26:40 +1000
committer Vas Crabb <vas@vastheman.com>2017-08-04 18:26:40 +1000
commit3eb9ddfa1691ce147861a2648e34dc7478f8f3b8 (patch)
treeb7ee6e04fec169fb763b1e2d15bfeaf92aac030d /scripts/minimaws
parentd96933356cda8f865a80245f17dd79a527d267b2 (diff)
minimaws: remove need for explicit schema step
Diffstat (limited to 'scripts/minimaws')
-rw-r--r--scripts/minimaws/lib/dbaccess.py203
-rw-r--r--scripts/minimaws/schema.sql119
2 files changed, 176 insertions, 146 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)
diff --git a/scripts/minimaws/schema.sql b/scripts/minimaws/schema.sql
deleted file mode 100644
index 63e13c6c364..00000000000
--- a/scripts/minimaws/schema.sql
+++ /dev/null
@@ -1,119 +0,0 @@
-PRAGMA page_size = 4096;
-PRAGMA foreign_keys = ON;
-
-CREATE TABLE featuretype (
- id INTEGER PRIMARY KEY,
- name TEXT NOT NULL,
- UNIQUE (name ASC));
-
-CREATE TABLE sourcefile (
- id INTEGER PRIMARY KEY,
- filename TEXT NOT NULL,
- UNIQUE (filename ASC));
-
-CREATE TABLE machine (
- id INTEGER PRIMARY KEY,
- shortname TEXT NOT NULL,
- description TEXT NOT NULL,
- sourcefile INTEGER NOT NULL,
- isdevice INTEGER NOT NULL,
- runnable INTEGER NOT NULL,
- UNIQUE (shortname ASC),
- UNIQUE (description ASC),
- FOREIGN KEY (sourcefile) REFERENCES sourcefile (id));
-
-CREATE TABLE system (
- id INTEGER PRIMARY KEY,
- year TEXT NOT NULL,
- manufacturer TEXT NOT NULL,
- FOREIGN KEY (id) REFERENCES machine (id));
-
-CREATE TABLE cloneof (
- id INTEGER PRIMARY KEY,
- parent TEXT NOT NULL,
- FOREIGN KEY (id) REFERENCES machine (id));
-
-CREATE TABLE romof (
- id INTEGER PRIMARY KEY,
- parent TEXT NOT NULL,
- FOREIGN KEY (id) REFERENCES machine (id));
-
-CREATE TABLE biosset (
- id INTEGER PRIMARY KEY,
- machine INTEGER NOT NULL,
- name TEXT NOT NULL,
- description TEXT NOT NULL,
- UNIQUE (machine ASC, name ASC),
- FOREIGN KEY (machine) REFERENCES machine (id));
-
-CREATE TABLE biossetdefault (
- id INTEGER PRIMARY KEY,
- FOREIGN KEY (id) REFERENCES biosset (id));
-
-CREATE TABLE devicereference (
- id INTEGER PRIMARY KEY,
- machine INTEGER NOT NULL,
- device INTEGER NOT NULL,
- UNIQUE (machine ASC, device ASC),
- FOREIGN KEY (machine) REFERENCES machine (id),
- FOREIGN KEY (device) REFERENCES machine (id));
-
-CREATE TABLE dipswitch (
- id INTEGER PRIMARY KEY,
- machine INTEGER NOT NULL,
- isconfig INTEGER NOT NULL,
- name TEXT NOT NULL,
- tag TEXT NOT NULL,
- mask INTEGER NOT NULL,
- --UNIQUE (machine ASC, tag ASC, mask ASC), not necessarily true, need to expose port conditions
- FOREIGN KEY (machine) REFERENCES machine (id));
-
-CREATE TABLE diplocation (
- id INTEGER PRIMARY KEY,
- dipswitch INTEGER NOT NULL,
- bit INTEGER NOT NULL,
- name TEXT NOT NULL,
- num INTEGER NOT NULL,
- inverted INTEGER NOT NULL,
- UNIQUE (dipswitch ASC, bit ASC),
- FOREIGN KEY (dipswitch) REFERENCES dipswitch (id));
-
-CREATE TABLE dipvalue (
- id INTEGER PRIMARY KEY,
- dipswitch INTEGER NOT NULL,
- name TEXT NOT NULL,
- value INTEGER NOT NULL,
- isdefault INTEGER NOT NULL,
- FOREIGN KEY (dipswitch) REFERENCES dipswitch (id));
-
-CREATE TABLE feature (
- id INTEGER PRIMARY KEY,
- machine INTEGER NOT NULL,
- featuretype INTEGER NOT NULL,
- status INTEGER NOT NULL,
- overall INTEGER NOT NULL,
- UNIQUE (machine ASC, featuretype ASC),
- FOREIGN KEY (machine) REFERENCES machine (id),
- FOREIGN KEY (featuretype) REFERENCES featuretype (id));
-
-CREATE TABLE slot (
- id INTEGER PRIMARY KEY,
- machine INTEGER NOT NULL,
- name TEXT NOT NULL,
- UNIQUE (machine ASC, name ASC),
- FOREIGN KEY (machine) REFERENCES machine (id));
-
-CREATE TABLE slotoption (
- id INTEGER PRIMARY KEY,
- slot INTEGER NOT NULL,
- device INTEGER NOT NULL,
- name TEXT NOT NULL,
- UNIQUE (slot ASC, name ASC),
- FOREIGN KEY (slot) REFERENCES slot (id),
- FOREIGN KEY (device) REFERENCES machine (id));
-
-CREATE TABLE slotdefault (
- id INTEGER PRIMARY KEY,
- slotoption INTEGER NOT NULL,
- FOREIGN KEY (id) REFERENCES slot (id),
- FOREIGN KEY (slotoption) REFERENCES slotoption (id));