diff options
author | 2019-12-15 21:38:54 +1100 | |
---|---|---|
committer | 2019-12-15 21:38:54 +1100 | |
commit | 202d06ffb0d9a55c484dbc71c657e24b9323efe2 (patch) | |
tree | b74c60ee1e06263d16f69d7bd7ca6516e316acbe /scripts/minimaws/lib/dbaccess.py | |
parent | 601e0668badf61b00ebd86aed995b3e3cf5c17e1 (diff) |
minimaws: fill in software list pages, load software list ROM information
Diffstat (limited to 'scripts/minimaws/lib/dbaccess.py')
-rw-r--r-- | scripts/minimaws/lib/dbaccess.py | 149 |
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): |