diff options
Diffstat (limited to 'docs/release/scripts/minimaws/lib/dbaccess.py')
-rw-r--r-- | docs/release/scripts/minimaws/lib/dbaccess.py | 606 |
1 files changed, 606 insertions, 0 deletions
diff --git a/docs/release/scripts/minimaws/lib/dbaccess.py b/docs/release/scripts/minimaws/lib/dbaccess.py new file mode 100644 index 00000000000..57de6aeb236 --- /dev/null +++ b/docs/release/scripts/minimaws/lib/dbaccess.py @@ -0,0 +1,606 @@ +#!/usr/bin/python +## +## license:BSD-3-Clause +## copyright-holders:Vas Crabb + +import sqlite3 +import sys + +if sys.version_info >= (3, 4): + import urllib.request + + +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_RAMOPTION = \ + 'CREATE TABLE ramoption (\n' \ + ' machine INTEGER NOT NULL,\n' \ + ' size INTEGER NOT NULL,\n' \ + ' name TEXT NOT NULL,\n' \ + ' PRIMARY KEY (machine ASC, size ASC),\n' \ + ' FOREIGN KEY (machine) REFERENCES machine (id))' + CREATE_RAMDEFAULT = \ + 'CREATE TABLE ramdefault (\n' \ + ' machine INTEGER PRIMARY KEY,\n' \ + ' size INTEGER NOT NULL,\n' \ + ' FOREIGN KEY (machine) REFERENCES machine (id),\n' \ + ' FOREIGN KEY (machine, size) REFERENCES ramoption (machine, size))' + + 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)' + + DROP_TEMPORARY_DEVICEREFERENCE = 'DROP TABLE IF EXISTS temp_devicereference' + DROP_TEMPORARY_SLOTOPTION = 'DROP TABLE IF EXISTS temp_slotoption' + DROP_TEMPORARY_SLOTDEFAULT = 'DROP TABLE IF EXISTS temp_slotdefault' + + INDEX_MACHINE_ISDEVICE_SHORTNAME = 'CREATE INDEX machine_isdevice_shortname ON machine (isdevice ASC, shortname ASC)' + INDEX_MACHINE_ISDEVICE_DESCRIPTION = 'CREATE INDEX machine_isdevice_description ON machine (isdevice ASC, description ASC)' + INDEX_MACHINE_RUNNABLE_SHORTNAME = 'CREATE INDEX machine_runnable_shortname ON machine (runnable ASC, shortname ASC)' + INDEX_MACHINE_RUNNABLE_DESCRIPTION = 'CREATE INDEX machine_runnable_description ON machine (runnable ASC, description ASC)' + + INDEX_SYSTEM_YEAR = 'CREATE INDEX system_year ON system (year ASC)' + INDEX_SYSTEM_MANUFACTURER = 'CREATE INDEX system_manufacturer ON system (manufacturer ASC)' + + INDEX_ROMOF_PARENT = 'CREATE INDEX romof_parent ON romof (parent ASC)' + + INDEX_CLONEOF_PARENT = 'CREATE INDEX cloneof_parent ON cloneof (parent ASC)' + + INDEX_DIPSWITCH_MACHINE_ISCONFIG = 'CREATE INDEX dipswitch_machine_isconfig ON dipswitch (machine ASC, isconfig ASC)' + + DROP_MACHINE_ISDEVICE_SHORTNAME = 'DROP INDEX IF EXISTS machine_isdevice_shortname' + DROP_MACHINE_ISDEVICE_DESCRIPTION = 'DROP INDEX IF EXISTS machine_isdevice_description' + DROP_MACHINE_RUNNABLE_SHORTNAME = 'DROP INDEX IF EXISTS machine_runnable_shortname' + DROP_MACHINE_RUNNABLE_DESCRIPTION = 'DROP INDEX IF EXISTS machine_runnable_description' + + DROP_SYSTEM_YEAR = 'DROP INDEX IF EXISTS system_year' + DROP_SYSTEM_MANUFACTURER = 'DROP INDEX IF EXISTS system_manufacturer' + + DROP_ROMOF_PARENT = 'DROP INDEX IF EXISTS romof_parent' + + DROP_CLONEOF_PARENT = 'DROP INDEX IF EXISTS cloneof_parent' + + 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_RAMOPTION, + CREATE_RAMDEFAULT) + + 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 (?)' + ADD_SOURCEFILE = 'INSERT OR IGNORE INTO sourcefile (filename) VALUES (?)' + ADD_MACHINE = 'INSERT INTO machine (shortname, description, sourcefile, isdevice, runnable) SELECT ?, ?, id, ?, ? FROM sourcefile WHERE filename = ?' + ADD_SYSTEM = 'INSERT INTO system (id, year, manufacturer) VALUES (?, ?, ?)' + ADD_CLONEOF = 'INSERT INTO cloneof (id, parent) VALUES (?, ?)' + ADD_ROMOF = 'INSERT INTO romof (id, parent) VALUES (?, ?)' + ADD_BIOSSET = 'INSERT INTO biosset (machine, name, description) VALUES (?, ?, ?)' + ADD_BIOSSETDEFAULT = 'INSERT INTO biossetdefault (id) VALUES (?)' + ADD_DIPSWITCH = 'INSERT INTO dipswitch (machine, isconfig, name, tag, mask) VALUES (?, ?, ?, ?, ?)' + ADD_DIPLOCATION = 'INSERT INTO diplocation (dipswitch, bit, name, num, inverted) VALUES (?, ?, ?, ?, ?)' + ADD_DIPVALUE = 'INSERT INTO dipvalue (dipswitch, name, value, isdefault) VALUES (?, ?, ?, ?)' + ADD_FEATURE = 'INSERT INTO feature (machine, featuretype, status, overall) SELECT ?, id, ?, ? FROM featuretype WHERE name = ?' + ADD_SLOT = 'INSERT INTO slot (machine, name) VALUES (?, ?)' + ADD_RAMOPTION = 'INSERT INTO ramoption (machine, size, name) VALUES (?, ?, ?)' + ADD_RAMDEFAULT = 'INSERT INTO ramdefault (machine, size) VALUES (?, ?)' + + ADD_TEMPORARY_DEVICEREFERENCE = 'INSERT OR IGNORE INTO temp_devicereference (machine, device) VALUES (?, ?)' + ADD_TEMPORARY_SLOTOPTION = 'INSERT INTO temp_slotoption (slot, device, name) VALUES (?, ?, ?)' + ADD_TEMPORARY_SLOTDEFAULT = 'INSERT INTO temp_slotdefault (id, slotoption) VALUES (?, ?)' + + FINALISE_DEVICEREFERENCES = 'INSERT INTO devicereference (id, machine, device) SELECT temp_devicereference.id, temp_devicereference.machine, machine.id FROM temp_devicereference LEFT JOIN machine ON temp_devicereference.device = machine.shortname' + FINALISE_SLOTOPTIONS = 'INSERT INTO slotoption (id, slot, device, name) SELECT temp_slotoption.id, temp_slotoption.slot, machine.id, temp_slotoption.name FROM temp_slotoption LEFT JOIN machine ON temp_slotoption.device = machine.shortname' + FINALISE_SLOTDEFAULTS = 'INSERT INTO slotdefault (id, slotoption) SELECT id, slotoption FROM temp_slotdefault' + + +class QueryCursor(object): + def __init__(self, dbconn, **kwargs): + super(QueryCursor, self).__init__(**kwargs) + self.dbcurs = dbconn.cursor() + + def close(self): + self.dbcurs.close() + + def is_glob(self, *patterns): + for pattern in patterns: + if any(ch in pattern for ch in '?*['): + return True + return False + + def count_systems(self, pattern): + if pattern is not None: + return self.dbcurs.execute( + 'SELECT COUNT(*) ' \ + 'FROM machine WHERE isdevice = 0 AND shortname GLOB ? ', + (pattern, )) + else: + return self.dbcurs.execute( + 'SELECT COUNT(*) ' \ + 'FROM machine WHERE isdevice = 0 ') + + def listfull(self, pattern): + if pattern is not None: + return self.dbcurs.execute( + 'SELECT shortname, description ' \ + 'FROM machine WHERE isdevice = 0 AND shortname GLOB ? ' \ + 'ORDER BY shortname ASC', + (pattern, )) + else: + return self.dbcurs.execute( + 'SELECT shortname, description ' \ + 'FROM machine WHERE isdevice = 0 ' \ + 'ORDER BY shortname ASC') + + def listsource(self, pattern): + if pattern is not None: + return self.dbcurs.execute( + 'SELECT machine.shortname, sourcefile.filename ' \ + 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id ' \ + 'WHERE machine.isdevice = 0 AND machine.shortname GLOB ? ' \ + 'ORDER BY machine.shortname ASC', + (pattern, )) + else: + return self.dbcurs.execute( + 'SELECT machine.shortname, sourcefile.filename ' \ + 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id ' \ + 'WHERE machine.isdevice = 0 ORDER BY machine.shortname ASC') + + def listclones(self, pattern): + if pattern is not None: + return self.dbcurs.execute( + 'SELECT machine.shortname, cloneof.parent ' \ + 'FROM machine JOIN cloneof ON machine.id = cloneof.id ' \ + 'WHERE machine.shortname GLOB ? OR cloneof.parent GLOB ? ' \ + 'ORDER BY machine.shortname ASC', + (pattern, pattern)) + else: + return self.dbcurs.execute( + 'SELECT machine.shortname, cloneof.parent ' \ + 'FROM machine JOIN cloneof ON machine.id = cloneof.id ' \ + 'ORDER BY machine.shortname ASC') + + def listbrothers(self, pattern): + if pattern is not None: + return self.dbcurs.execute( + 'SELECT sourcefile.filename, machine.shortname, cloneof.parent ' \ + 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id LEFT JOIN cloneof ON machine.id = cloneof.id ' \ + 'WHERE machine.isdevice = 0 AND sourcefile.id IN (SELECT sourcefile FROM machine WHERE shortname GLOB ?)' \ + 'ORDER BY machine.shortname ASC', + (pattern, )) + else: + return self.dbcurs.execute( + 'SELECT sourcefile.filename, machine.shortname, cloneof.parent ' \ + 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id LEFT JOIN cloneof ON machine.id = cloneof.id ' \ + 'WHERE machine.isdevice = 0 ' \ + 'ORDER BY machine.shortname ASC') + + def listaffected(self, *patterns): + if 1 == len(patterns): + return self.dbcurs.execute( + 'SELECT shortname, description ' \ + 'FROM machine ' \ + 'WHERE id IN (SELECT machine FROM devicereference WHERE device IN (SELECT id FROM machine WHERE sourcefile IN (SELECT id FROM sourcefile WHERE filename GLOB ?))) AND runnable = 1 ' \ + 'ORDER BY shortname ASC', + patterns) + elif self.is_glob(*patterns): + return self.dbcurs.execute( + 'SELECT shortname, description ' \ + 'FROM machine ' \ + 'WHERE id IN (SELECT machine FROM devicereference WHERE device IN (SELECT id FROM machine WHERE sourcefile IN (SELECT id FROM sourcefile WHERE filename GLOB ?' + (' OR filename GLOB ?' * (len(patterns) - 1)) + '))) AND runnable = 1 ' \ + 'ORDER BY shortname ASC', + patterns) + else: + return self.dbcurs.execute( + 'SELECT shortname, description ' \ + 'FROM machine ' \ + 'WHERE id IN (SELECT machine FROM devicereference WHERE device IN (SELECT id FROM machine WHERE sourcefile IN (SELECT id FROM sourcefile WHERE filename IN (?' + (', ?' * (len(patterns) - 1)) + ')))) AND runnable = 1 ' \ + 'ORDER BY shortname ASC', + patterns) + + def get_machine_id(self, machine): + return (self.dbcurs.execute('SELECT id FROM machine WHERE shortname = ?', (machine, )).fetchone() or (None, ))[0] + + def get_machine_info(self, machine): + return self.dbcurs.execute( + 'SELECT machine.id AS id, machine.description AS description, machine.isdevice AS isdevice, machine.runnable AS runnable, sourcefile.filename AS sourcefile, system.year AS year, system.manufacturer AS manufacturer, cloneof.parent AS cloneof, romof.parent AS romof ' \ + 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id LEFT JOIN system ON machine.id = system.id LEFT JOIN cloneof ON system.id = cloneof.id LEFT JOIN romof ON system.id = romof.id ' \ + 'WHERE machine.shortname = ?', + (machine, )) + + def get_biossets(self, machine): + return self.dbcurs.execute( + 'SELECT biosset.name AS name, biosset.description AS description, COUNT(biossetdefault.id) AS isdefault ' \ + 'FROM biosset LEFT JOIN biossetdefault USING (id) ' \ + 'WHERE biosset.machine = ? ' \ + 'GROUP BY biosset.id', + (machine, )) + + def get_devices_referenced(self, machine): + return self.dbcurs.execute( + 'SELECT machine.shortname AS shortname, machine.description AS description, sourcefile.filename AS sourcefile ' \ + 'FROM devicereference LEFT JOIN machine ON devicereference.device = machine.id LEFT JOIN sourcefile ON machine.sourcefile = sourcefile.id ' \ + 'WHERE devicereference.machine = ?', + (machine, )) + + def get_device_references(self, device): + return self.dbcurs.execute( + 'SELECT machine.shortname AS shortname, machine.description AS description, sourcefile.filename AS sourcefile ' \ + 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id ' \ + 'WHERE machine.id IN (SELECT machine FROM devicereference WHERE device = ?)', + (device, )) + + def get_compatible_slots(self, device): + return self.dbcurs.execute( + 'SELECT machine.shortname AS shortname, machine.description AS description, slot.name AS slot, slotoption.name AS slotoption, sourcefile.filename AS sourcefile ' \ + 'FROM slotoption JOIN slot ON slotoption.slot = slot.id JOIN machine on slot.machine = machine.id JOIN sourcefile ON machine.sourcefile = sourcefile.id ' + 'WHERE slotoption.device = ?', + (device, )) + + def get_sourcefile_id(self, filename): + return (self.dbcurs.execute('SELECT id FROM sourcefile WHERE filename = ?', (filename, )).fetchone() or (None, ))[0] + + def get_sourcefile_machines(self, id): + return self.dbcurs.execute( + 'SELECT machine.shortname AS shortname, machine.description AS description, machine.isdevice AS isdevice, machine.runnable AS runnable, sourcefile.filename AS sourcefile, system.year AS year, system.manufacturer AS manufacturer, cloneof.parent AS cloneof, romof.parent AS romof ' \ + 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id LEFT JOIN system ON machine.id = system.id LEFT JOIN cloneof ON system.id = cloneof.id LEFT JOIN romof ON system.id = romof.id ' \ + 'WHERE machine.sourcefile = ?', + (id, )) + + def get_sourcefiles(self, pattern): + if pattern is not None: + return self.dbcurs.execute( + 'SELECT sourcefile.filename AS filename, COUNT(machine.id) AS machines ' \ + 'FROM sourcefile LEFT JOIN machine ON sourcefile.id = machine.sourcefile ' \ + 'WHERE sourcefile.filename GLOB ?' \ + 'GROUP BY sourcefile.id ', + (pattern, )) + else: + return self.dbcurs.execute( + 'SELECT sourcefile.filename AS filename, COUNT(machine.id) AS machines ' \ + 'FROM sourcefile LEFT JOIN machine ON sourcefile.id = machine.sourcefile ' \ + 'GROUP BY sourcefile.id') + + def count_sourcefiles(self, pattern): + if pattern is not None: + return self.dbcurs.execute('SELECT COUNT(*) FROM sourcefile WHERE filename GLOB ?', (pattern, )).fetchone()[0] + else: + return self.dbcurs.execute('SELECT COUNT(*) FROM sourcefile').fetchone()[0] + + def count_slots(self, machine): + return self.dbcurs.execute( + 'SELECT COUNT(*) FROM slot WHERE machine = ?', (machine, )).fetchone()[0] + + def get_feature_flags(self, machine): + return self.dbcurs.execute( + 'SELECT featuretype.name AS featuretype, feature.status AS status, feature.overall AS overall ' \ + 'FROM feature JOIN featuretype ON feature.featuretype = featuretype.id ' \ + 'WHERE feature.machine = ?', + (machine, )) + + def get_slot_defaults(self, machine): + return self.dbcurs.execute( + 'SELECT slot.name AS name, slotoption.name AS option ' \ + 'FROM slot JOIN slotdefault ON slot.id = slotdefault.id JOIN slotoption ON slotdefault.slotoption = slotoption.id ' \ + 'WHERE slot.machine = ?', + (machine, )) + + def get_slot_options(self, machine): + return self.dbcurs.execute( + 'SELECT slot.name AS slot, slotoption.name AS option, machine.shortname AS shortname, machine.description AS description ' \ + 'FROM slot JOIN slotoption ON slot.id = slotoption.slot JOIN machine ON slotoption.device = machine.id ' \ + 'WHERE slot.machine = ?', + (machine, )) + + def get_ram_options(self, machine): + return self.dbcurs.execute( + 'SELECT ramoption.name AS name, ramoption.size AS size, COUNT(ramdefault.machine) AS isdefault ' \ + 'FROM ramoption LEFT JOIN ramdefault USING (machine, size) WHERE ramoption.machine = ? ' \ + 'GROUP BY ramoption.machine, ramoption.size ' \ + 'ORDER BY ramoption.size', + (machine, )) + + +class UpdateCursor(object): + def __init__(self, dbconn, **kwargs): + super(UpdateCursor, self).__init__(**kwargs) + self.dbcurs = dbconn.cursor() + + def close(self): + self.dbcurs.close() + + def add_featuretype(self, name): + self.dbcurs.execute(UpdateQueries.ADD_FEATURETYPE, (name, )) + + def add_sourcefile(self, filename): + self.dbcurs.execute(UpdateQueries.ADD_SOURCEFILE, (filename, )) + + def add_machine(self, shortname, description, sourcefile, isdevice, runnable): + self.dbcurs.execute(UpdateQueries.ADD_MACHINE, (shortname, description, isdevice, runnable, sourcefile)) + return self.dbcurs.lastrowid + + def add_system(self, machine, year, manufacturer): + self.dbcurs.execute(UpdateQueries.ADD_SYSTEM, (machine, year, manufacturer)) + return self.dbcurs.lastrowid + + def add_cloneof(self, machine, parent): + self.dbcurs.execute(UpdateQueries.ADD_CLONEOF, (machine, parent)) + return self.dbcurs.lastrowid + + def add_romof(self, machine, parent): + self.dbcurs.execute(UpdateQueries.ADD_ROMOF, (machine, parent)) + return self.dbcurs.lastrowid + + def add_biosset(self, machine, name, description): + self.dbcurs.execute(UpdateQueries.ADD_BIOSSET, (machine, name, description)) + return self.dbcurs.lastrowid + + def add_biossetdefault(self, biosset): + self.dbcurs.execute(UpdateQueries.ADD_BIOSSETDEFAULT, (biosset, )) + return self.dbcurs.lastrowid + + def add_devicereference(self, machine, device): + self.dbcurs.execute(UpdateQueries.ADD_TEMPORARY_DEVICEREFERENCE, (machine, device)) + + def add_dipswitch(self, machine, isconfig, name, tag, mask): + self.dbcurs.execute(UpdateQueries.ADD_DIPSWITCH, (machine, isconfig, name, tag, mask)) + return self.dbcurs.lastrowid + + def add_diplocation(self, dipswitch, bit, name, num, inverted): + self.dbcurs.execute(UpdateQueries.ADD_DIPLOCATION, (dipswitch, bit, name, num, inverted)) + return self.dbcurs.lastrowid + + def add_dipvalue(self, dipswitch, name, value, isdefault): + self.dbcurs.execute(UpdateQueries.ADD_DIPVALUE, (dipswitch, name, value, isdefault)) + return self.dbcurs.lastrowid + + def add_feature(self, machine, featuretype, status, overall): + self.dbcurs.execute(UpdateQueries.ADD_FEATURE, (machine, status, overall, featuretype)) + return self.dbcurs.lastrowid + + def add_slot(self, machine, name): + self.dbcurs.execute(UpdateQueries.ADD_SLOT, (machine, name)) + return self.dbcurs.lastrowid + + def add_slotoption(self, slot, device, name): + self.dbcurs.execute(UpdateQueries.ADD_TEMPORARY_SLOTOPTION, (slot, device, name)) + return self.dbcurs.lastrowid + + def add_slotdefault(self, slot, slotoption): + self.dbcurs.execute(UpdateQueries.ADD_TEMPORARY_SLOTDEFAULT, (slot, slotoption)) + return self.dbcurs.lastrowid + + def add_ramoption(self, machine, name, size): + self.dbcurs.execute(UpdateQueries.ADD_RAMOPTION, (machine, size, name)) + return self.dbcurs.lastrowid + + def add_ramdefault(self, machine, size): + self.dbcurs.execute(UpdateQueries.ADD_RAMDEFAULT, (machine, size)) + return self.dbcurs.lastrowid + + +class QueryConnection(object): + def __init__(self, database, **kwargs): + super(QueryConnection, self).__init__(**kwargs) + if sys.version_info >= (3, 4): + self.dbconn = sqlite3.connect('file:' + urllib.request.pathname2url(database) + '?mode=ro', uri=True) + else: + self.dbconn = sqlite3.connect(database) + self.dbconn.row_factory = sqlite3.Row + self.dbconn.execute('PRAGMA foreign_keys = ON') + + def close(self): + self.dbconn.close() + + def cursor(self): + return QueryCursor(self.dbconn) + + +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): + self.dbconn.commit() + + def rollback(self): + self.dbconn.rollback() + + def close(self): + self.dbconn.close() + + def cursor(self): + return UpdateCursor(self.dbconn) + + def prepare_for_load(self): + # here be dragons - this is a poor man's DROP ALL TABLES etc. + self.dbconn.execute('PRAGMA foreign_keys = OFF') + for query in self.dbconn.execute('SELECT \'DROP INDEX \' || name FROM sqlite_master WHERE type = \'index\' AND NOT name GLOB \'sqlite_autoindex_*\'').fetchall(): + self.dbconn.execute(query[0]) + for query in self.dbconn.execute('SELECT \'DROP TABLE \' || name FROM sqlite_master WHERE type = \'table\'').fetchall(): + self.dbconn.execute(query[0]) + self.dbconn.execute('PRAGMA foreign_keys = ON') + + # this is where the sanity starts + 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): + self.dbconn.execute(UpdateQueries.FINALISE_DEVICEREFERENCES) + self.dbconn.commit() + self.dbconn.execute(SchemaQueries.DROP_TEMPORARY_DEVICEREFERENCE) + self.dbconn.execute(UpdateQueries.FINALISE_SLOTOPTIONS) + self.dbconn.commit() + self.dbconn.execute(SchemaQueries.DROP_TEMPORARY_SLOTOPTION) + self.dbconn.execute(UpdateQueries.FINALISE_SLOTDEFAULTS) + self.dbconn.commit() + self.dbconn.execute(SchemaQueries.DROP_TEMPORARY_SLOTDEFAULT) + for query in SchemaQueries.CREATE_INDEXES: + self.dbconn.execute(query) + self.dbconn.commit() |