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.py149
1 files changed, 143 insertions, 6 deletions
diff --git a/scripts/minimaws/lib/dbaccess.py b/scripts/minimaws/lib/dbaccess.py
index d5fe557ff91..80bfabb92a5 100644
--- a/scripts/minimaws/lib/dbaccess.py
+++ b/scripts/minimaws/lib/dbaccess.py
@@ -221,6 +221,15 @@ class SchemaQueries(object):
' FOREIGN KEY (machine) REFERENCES machine (id),\n' \
' FOREIGN KEY (rom) REFERENCES rom (id),\n' \
' UNIQUE (machine, rom, name))'
+ CREATE_SOFTWAREROMDUMP = \
+ 'CREATE TABLE softwareromdump (\n' \
+ ' part INTEGER NOT NULL,\n' \
+ ' rom INTEGER NOT NULL,\n' \
+ ' name TEXT NOT NULL,\n' \
+ ' bad INTEGER NOT NULL,\n' \
+ ' FOREIGN KEY (part) REFERENCES softwarepart (id),\n' \
+ ' FOREIGN KEY (rom) REFERENCES rom (id),\n' \
+ ' UNIQUE (part, rom, name))'
CREATE_DISK = \
'CREATE TABLE disk (\n' \
' id INTEGER PRIMARY KEY,\n' \
@@ -235,6 +244,15 @@ class SchemaQueries(object):
' FOREIGN KEY (machine) REFERENCES machine (id),\n' \
' FOREIGN KEY (disk) REFERENCES disk (id),\n' \
' UNIQUE (machine, disk, name))'
+ CREATE_SOFTWAREDISKDUMP = \
+ 'CREATE TABLE softwarediskdump (\n' \
+ ' part INTEGER NOT NULL,\n' \
+ ' disk INTEGER NOT NULL,\n' \
+ ' name TEXT NOT NULL,\n' \
+ ' bad INTEGER NOT NULL,\n' \
+ ' FOREIGN KEY (part) REFERENCES softwarepart (id),\n' \
+ ' FOREIGN KEY (disk) REFERENCES disk (id),\n' \
+ ' UNIQUE (part, disk, name))'
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)'
@@ -275,8 +293,16 @@ class SchemaQueries(object):
INDEX_SOFTWAREPARTFEATURE_FEATURETYPE_VALUE_PART = 'CREATE INDEX softwarepartfeature_featuretype_value_part ON softwarepartfeature (featuretype ASC, value ASC, part ASC)'
INDEX_ROMDUMP_ROM = 'CREATE INDEX romdump_rom ON romdump (rom ASC)'
+ INDEX_ROMDUMP_MACHINE_BAD = 'CREATE INDEX romdump_machine_bad ON romdump (machine ASC, bad ASC)'
+
+ INDEX_SOFTWAREROMDUMP_ROM = 'CREATE INDEX softwareromdump_rom ON softwareromdump (rom ASC)'
+ INDEX_SOFTWAREROMDUMP_PART_BAD = 'CREATE INDEX softwareromdump_part_bad ON softwareromdump (part ASC, bad ASC)'
INDEX_DISKDUMP_DISK = 'CREATE INDEX diskdump_disk ON diskdump (disk ASC)'
+ INDEX_DISKDUMP_MACHINE_BAD = 'CREATE INDEX diskdump_machine_bad ON diskdump (machine ASC, bad ASC)'
+
+ INDEX_SOFTWAREDISKDUMP_DISK = 'CREATE INDEX softwarediskdump_disk ON softwarediskdump (disk ASC)'
+ INDEX_SOFTWAREDISKDUMP_PART_BAD = 'CREATE INDEX softwarediskdump_part_bad ON softwarediskdump (part ASC, bad ASC)'
DROP_MACHINE_ISDEVICE_SHORTNAME = 'DROP INDEX IF EXISTS machine_isdevice_shortname'
DROP_MACHINE_ISDEVICE_DESCRIPTION = 'DROP INDEX IF EXISTS machine_isdevice_description'
@@ -309,8 +335,16 @@ class SchemaQueries(object):
DROP_SOFTWAREPARTFEATURE_FEATURETYPE_VALUE_PART = 'DROP INDEX IF EXISTS softwarepartfeature_featuretype_value_part'
DROP_ROMDUMP_ROM = 'DROP INDEX IF EXISTS romdump_rom'
+ DROP_ROMDUMP_MACHINE_BAD = 'DROP INDEX IF EXISTS romdump_machine_bad'
+
+ DROP_SOFTWAREROMDUMP_ROM = 'DROP INDEX IF EXISTS softwareromdump_rom'
+ DROP_SOFTWAREROMDUMP_PART_BAD = 'DROP INDEX IF EXISTS softwareromdump_part_bad'
DROP_DISKDUMP_DISK = 'DROP INDEX IF EXISTS diskdump_disk'
+ DROP_DISKDUMP_MACHINE_BAD = 'DROP INDEX IF EXISTS diskdump_machine_bad'
+
+ DROP_SOFTWAREDISKDUMP_DISK = 'DROP INDEX IF EXISTS softwarediskdump_disk'
+ DROP_SOFTWAREDISKDUMP_PART_BAD = 'DROP INDEX IF EXISTS softwarediskdump_part_bad'
CREATE_TABLES = (
CREATE_FEATURETYPE,
@@ -342,8 +376,10 @@ class SchemaQueries(object):
CREATE_SOFTWAREPARTFEATURE,
CREATE_ROM,
CREATE_ROMDUMP,
+ CREATE_SOFTWAREROMDUMP,
CREATE_DISK,
- CREATE_DISKDUMP)
+ CREATE_DISKDUMP,
+ CREATE_SOFTWAREDISKDUMP)
CREATE_TEMPORARY_TABLES = (
CREATE_TEMPORARY_DEVICEREFERENCE,
@@ -372,7 +408,13 @@ class SchemaQueries(object):
INDEX_SOFTWAREPART_INTERFACE_SOFTWARE,
INDEX_SOFTWAREPARTFEATURE_FEATURETYPE_VALUE_PART,
INDEX_ROMDUMP_ROM,
- INDEX_DISKDUMP_DISK)
+ INDEX_ROMDUMP_MACHINE_BAD,
+ INDEX_SOFTWAREROMDUMP_ROM,
+ INDEX_SOFTWAREROMDUMP_PART_BAD,
+ INDEX_DISKDUMP_DISK,
+ INDEX_DISKDUMP_MACHINE_BAD,
+ INDEX_SOFTWAREDISKDUMP_DISK,
+ INDEX_SOFTWAREDISKDUMP_PART_BAD)
DROP_INDEXES = (
DROP_MACHINE_ISDEVICE_SHORTNAME,
@@ -396,7 +438,13 @@ class SchemaQueries(object):
DROP_SOFTWAREPART_INTERFACE_SOFTWARE,
DROP_SOFTWAREPARTFEATURE_FEATURETYPE_VALUE_PART,
DROP_ROMDUMP_ROM,
- DROP_DISKDUMP_DISK)
+ DROP_ROMDUMP_MACHINE_BAD,
+ DROP_SOFTWAREROMDUMP_ROM,
+ DROP_SOFTWAREROMDUMP_PART_BAD,
+ DROP_DISKDUMP_DISK,
+ DROP_DISKDUMP_MACHINE_BAD,
+ DROP_SOFTWAREDISKDUMP_DISK,
+ DROP_SOFTWAREDISKDUMP_PART_BAD)
class UpdateQueries(object):
@@ -426,8 +474,10 @@ class UpdateQueries(object):
ADD_SOFTWAREPARTFEATURE = 'INSERT INTO softwarepartfeature (part, featuretype, value) SELECT ?, id, ? FROM softwarepartfeaturetype WHERE name = ?'
ADD_ROM = 'INSERT OR IGNORE INTO rom (crc, sha1) VALUES (?, ?)'
ADD_ROMDUMP = 'INSERT OR IGNORE INTO romdump (machine, rom, name, bad) SELECT ?, id, ?, ? FROM rom WHERE crc = ? AND sha1 = ?'
+ ADD_SOFTWAREROMDUMP = 'INSERT OR IGNORE INTO softwareromdump (part, rom, name, bad) SELECT ?, id, ?, ? FROM rom WHERE crc = ? AND sha1 = ?'
ADD_DISK = 'INSERT OR IGNORE INTO disk (sha1) VALUES (?)'
ADD_DISKDUMP = 'INSERT OR IGNORE INTO diskdump (machine, disk, name, bad) SELECT ?, id, ?, ? FROM disk WHERE sha1 = ?'
+ ADD_SOFTWAREDISKDUMP = 'INSERT OR IGNORE INTO softwarediskdump (part, disk, name, bad) SELECT ?, id, ?, ? FROM disk WHERE sha1 = ?'
ADD_TEMPORARY_DEVICEREFERENCE = 'INSERT OR IGNORE INTO temp_devicereference (machine, device) VALUES (?, ?)'
ADD_TEMPORARY_SLOTOPTION = 'INSERT INTO temp_slotoption (slot, device, name) VALUES (?, ?, ?)'
@@ -545,7 +595,7 @@ class QueryCursor(object):
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):
+ def get_machine_details(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 ' \
@@ -644,20 +694,85 @@ class QueryCursor(object):
'ORDER BY ramoption.size',
(machine, ))
+ def get_softwarelist_id(self, shortname):
+ return (self.dbcurs.execute('SELECT id FROM softwarelist WHERE shortname = ?', (shortname, )).fetchone() or (None, ))[0]
+
+ def get_softwarelist_details(self, shortname, pattern):
+ if pattern is not None:
+ return self.dbcurs.execute(
+ 'SELECT softwarelist.id AS id, softwarelist.shortname AS shortname, softwarelist.description AS description, COUNT(software.id) AS total, COUNT(CASE software.supported WHEN 0 THEN 1 ELSE NULL END) AS supported, COUNT(CASE software.supported WHEN 1 THEN 1 ELSE NULL END) AS partiallysupported, COUNT(CASE software.supported WHEN 2 THEN 1 ELSE NULL END) AS unsupported ' \
+ 'FROM softwarelist LEFT JOIN software ON softwarelist.id = software.softwarelist ' \
+ 'WHERE softwarelist.shortname = ? AND software.shortname GLOB ? ' \
+ 'GROUP BY softwarelist.id',
+ (shortname, pattern))
+ else:
+ return self.dbcurs.execute(
+ 'SELECT softwarelist.id AS id, softwarelist.shortname AS shortname, softwarelist.description AS description, COUNT(software.id) AS total, COUNT(CASE software.supported WHEN 0 THEN 1 ELSE NULL END) AS supported, COUNT(CASE software.supported WHEN 1 THEN 1 ELSE NULL END) AS partiallysupported, COUNT(CASE software.supported WHEN 2 THEN 1 ELSE NULL END) AS unsupported ' \
+ 'FROM softwarelist LEFT JOIN software ON softwarelist.id = software.softwarelist ' \
+ 'WHERE softwarelist.shortname = ? ' \
+ 'GROUP BY softwarelist.id',
+ (shortname, ))
+
+ def get_softwarelist_software(self, id, pattern):
+ if pattern is not None:
+ return self.dbcurs.execute(
+ 'SELECT software.shortname AS shortname, software.description AS description, software.year AS year, software.publisher AS publisher, software.supported AS supported, COUNT(*) AS parts ' \
+ 'FROM software JOIN softwarepart ON software.id = softwarepart.software ' \
+ 'WHERE software.softwarelist = ? AND software.shortname GLOB ? ' \
+ 'GROUP BY software.id',
+ (id, pattern))
+ else:
+ return self.dbcurs.execute(
+ 'SELECT software.shortname AS shortname, software.description AS description, software.year AS year, software.publisher AS publisher, software.supported AS supported, COUNT(*) AS parts ' \
+ 'FROM software JOIN softwarepart ON software.id = softwarepart.software ' \
+ 'WHERE software.softwarelist = ? ' \
+ 'GROUP BY software.id',
+ (id, ))
+
def get_softwarelists(self, pattern):
if pattern is not None:
return self.dbcurs.execute(
- 'SELECT softwarelist.shortname AS shortname, softwarelist.description AS description, COUNT(*) AS total, COUNT(CASE software.supported WHEN 0 THEN 1 ELSE NULL END) AS supported, COUNT(CASE software.supported WHEN 1 THEN 1 ELSE NULL END) AS partiallysupported, COUNT(CASE software.supported WHEN 2 THEN 1 ELSE NULL END) AS unsupported ' \
+ 'SELECT softwarelist.shortname AS shortname, softwarelist.description AS description, COUNT(software.id) AS total, COUNT(CASE software.supported WHEN 0 THEN 1 ELSE NULL END) AS supported, COUNT(CASE software.supported WHEN 1 THEN 1 ELSE NULL END) AS partiallysupported, COUNT(CASE software.supported WHEN 2 THEN 1 ELSE NULL END) AS unsupported ' \
'FROM softwarelist LEFT JOIN software ON softwarelist.id = software.softwarelist ' \
'WHERE softwarelist.shortname GLOB ? ' \
'GROUP BY softwarelist.id',
(pattern, ))
else:
return self.dbcurs.execute(
- 'SELECT softwarelist.shortname AS shortname, softwarelist.description AS description, COUNT(*) AS total, COUNT(CASE software.supported WHEN 0 THEN 1 ELSE NULL END) AS supported, COUNT(CASE software.supported WHEN 1 THEN 1 ELSE NULL END) AS partiallysupported, COUNT(CASE software.supported WHEN 2 THEN 1 ELSE NULL END) AS unsupported ' \
+ 'SELECT softwarelist.shortname AS shortname, softwarelist.description AS description, COUNT(software.id) AS total, COUNT(CASE software.supported WHEN 0 THEN 1 ELSE NULL END) AS supported, COUNT(CASE software.supported WHEN 1 THEN 1 ELSE NULL END) AS partiallysupported, COUNT(CASE software.supported WHEN 2 THEN 1 ELSE NULL END) AS unsupported ' \
'FROM softwarelist LEFT JOIN software ON softwarelist.id = software.softwarelist ' \
'GROUP BY softwarelist.id')
+ def get_software_details(self, softwarelist, software):
+ return self.dbcurs.execute(
+ 'SELECT software.id AS id, software.shortname AS shortname, software.supported AS supported, software.description AS description, software.year AS year, software.publisher AS publisher, softwarelist.shortname AS softwarelist, softwarelist.description AS softwarelistdescription ' \
+ 'FROM software LEFT JOIN softwarelist ON software.softwarelist = softwarelist.id ' \
+ 'WHERE software.softwarelist = (SELECT id FROM softwarelist WHERE shortname = ?) AND software.shortname = ?',
+ (softwarelist, software))
+
+ def get_software_info(self, software):
+ return self.dbcurs.execute(
+ 'SELECT softwareinfotype.name AS name, softwareinfo.value AS value ' \
+ 'FROM softwareinfo JOIN softwareinfotype ON softwareinfo.infotype = softwareinfotype.id ' \
+ 'WHERE softwareinfo.software = ? ' \
+ 'ORDER BY softwareinfotype.name ASC, softwareinfo.value ASC',
+ (software, ))
+
+ def get_software_parts(self, software):
+ return self.dbcurs.execute(
+ 'SELECT softwarepart.id AS id, softwarepart.shortname AS shortname, softwarepart.interface AS interface, softwarepartfeature.value AS part_id ' \
+ 'FROM softwarepart LEFT JOIN softwarepartfeature ON softwarepart.id = softwarepartfeature.part AND softwarepartfeature.featuretype = (SELECT id FROM softwarepartfeaturetype WHERE name = \'part_id\') ' \
+ 'WHERE softwarepart.software = ?',
+ (software, ))
+
+ def get_softwarepart_features(self, part):
+ return self.dbcurs.execute(
+ 'SELECT softwarepartfeaturetype.name AS name, softwarepartfeature.value AS value ' \
+ 'FROM softwarepartfeature LEFT JOIN softwarepartfeaturetype ON softwarepartfeature.featuretype = softwarepartfeaturetype.id ' \
+ 'WHERE softwarepartfeature.part = ? '
+ 'ORDER BY softwarepartfeaturetype.name ASC',
+ (part, ))
+
def get_rom_dumps(self, crc, sha1):
return self.dbcurs.execute(
'SELECT machine.shortname AS shortname, machine.description AS description, romdump.name AS label, romdump.bad AS bad ' \
@@ -665,6 +780,13 @@ class QueryCursor(object):
'WHERE romdump.rom = (SELECT id FROM rom WHERE crc = ? AND sha1 = ?)',
(crc, sha1))
+ def get_software_rom_dumps(self, crc, sha1):
+ return self.dbcurs.execute(
+ 'SELECT softwarelist.shortname AS softwarelist, softwarelist.description AS softwarelistdescription, software.shortname AS shortname, software.description AS description, softwarepart.shortname AS part, softwarepartfeature.value AS part_id, softwareromdump.name AS label, softwareromdump.bad AS bad ' \
+ 'FROM softwareromdump LEFT JOIN softwarepart ON softwareromdump.part = softwarepart.id LEFT JOIN softwarepartfeature ON softwarepart.id = softwarepartfeature.part AND softwarepartfeature.featuretype = (SELECT id FROM softwarepartfeaturetype WHERE name = \'part_id\') LEFT JOIN software ON softwarepart.software = software.id LEFT JOIN softwarelist ON software.softwarelist = softwarelist.id ' \
+ 'WHERE softwareromdump.rom = (SELECT id FROM rom WHERE crc = ? AND sha1 = ?)',
+ (crc, sha1))
+
def get_disk_dumps(self, sha1):
return self.dbcurs.execute(
'SELECT machine.shortname AS shortname, machine.description AS description, diskdump.name AS label, diskdump.bad AS bad ' \
@@ -672,6 +794,13 @@ class QueryCursor(object):
'WHERE diskdump.disk = (SELECT id FROM disk WHERE sha1 = ?)',
(sha1, ))
+ def get_software_disk_dumps(self, sha1):
+ return self.dbcurs.execute(
+ 'SELECT softwarelist.shortname AS softwarelist, softwarelist.description AS softwarelistdescription, software.shortname AS shortname, software.description AS description, softwarepart.shortname AS part, softwarepartfeature.value AS part_id, softwarediskdump.name AS label, softwarediskdump.bad AS bad ' \
+ 'FROM softwarediskdump LEFT JOIN softwarepart ON softwarediskdump.part = softwarepart.id LEFT JOIN softwarepartfeature ON softwarepart.id = softwarepartfeature.part AND softwarepartfeature.featuretype = (SELECT id FROM softwarepartfeaturetype WHERE name = \'part_id\') LEFT JOIN software ON softwarepart.software = software.id LEFT JOIN softwarelist ON software.softwarelist = softwarelist.id ' \
+ 'WHERE softwarediskdump.disk = (SELECT id FROM disk WHERE sha1 = ?)',
+ (sha1, ))
+
class UpdateCursor(object):
def __init__(self, dbconn, **kwargs):
@@ -791,6 +920,10 @@ class UpdateCursor(object):
self.dbcurs.execute(UpdateQueries.ADD_ROMDUMP, (machine, name, 1 if bad else 0, crc, sha1))
return self.dbcurs.lastrowid
+ def add_softwareromdump(self, part, name, crc, sha1, bad):
+ self.dbcurs.execute(UpdateQueries.ADD_SOFTWAREROMDUMP, (part, name, 1 if bad else 0, crc, sha1))
+ return self.dbcurs.lastrowid
+
def add_disk(self, sha1):
self.dbcurs.execute(UpdateQueries.ADD_DISK, (sha1, ))
return self.dbcurs.lastrowid
@@ -799,6 +932,10 @@ class UpdateCursor(object):
self.dbcurs.execute(UpdateQueries.ADD_DISKDUMP, (machine, name, 1 if bad else 0, sha1))
return self.dbcurs.lastrowid
+ def add_softwarediskdump(self, part, name, sha1, bad):
+ self.dbcurs.execute(UpdateQueries.ADD_SOFTWAREDISKDUMP, (part, name, 1 if bad else 0, sha1))
+ return self.dbcurs.lastrowid
+
class QueryConnection(object):
def __init__(self, database, **kwargs):