1#!/usr/bin/python 2## 3## license:BSD-3-Clause 4## copyright-holders:Vas Crabb 5 6import sqlite3 7import sys 8 9if sys.version_info >= (3, 4): 10 import urllib.request 11 12 13class SchemaQueries(object): 14 # software lists 15 CREATE_SOFTWAREINFOTYPE = \ 16 'CREATE TABLE softwareinfotype (\n' \ 17 ' id INTEGER PRIMARY KEY,\n' \ 18 ' name TEXT NOT NULL,\n' \ 19 ' UNIQUE (name ASC))' 20 CREATE_SOFTWARESHAREDFEATTYPE = \ 21 'CREATE TABLE softwaresharedfeattype (\n' \ 22 ' id INTEGER PRIMARY KEY,\n' \ 23 ' name TEXT NOT NULL,\n' \ 24 ' UNIQUE (name ASC))' 25 CREATE_SOFTWAREPARTFEATURETYPE = \ 26 'CREATE TABLE softwarepartfeaturetype (\n' \ 27 ' id INTEGER PRIMARY KEY,\n' \ 28 ' name TEXT NOT NULL,\n' \ 29 ' UNIQUE (name ASC))' 30 CREATE_SOFTWARELIST = \ 31 'CREATE TABLE softwarelist (\n' \ 32 ' id INTEGER PRIMARY KEY,\n' \ 33 ' shortname TEXT NOT NULL,\n' \ 34 ' description TEXT NOT NULL,\n' \ 35 ' UNIQUE (shortname ASC))' 36 CREATE_SOFTWARE = \ 37 'CREATE TABLE software (\n' \ 38 ' id INTEGER PRIMARY KEY,\n' \ 39 ' softwarelist INTEGER NOT NULL,\n' \ 40 ' shortname TEXT NOT NULL,\n' \ 41 ' supported INTEGER NOT NULL,\n' \ 42 ' description TEXT NOT NULL,\n' \ 43 ' year TEXT NOT NULL,\n' \ 44 ' publisher TEXT NOT NULL,\n' \ 45 ' UNIQUE (softwarelist ASC, shortname ASC),\n' \ 46 ' FOREIGN KEY (softwarelist) REFERENCES softwarelist (id))' 47 CREATE_SOFTWARECLONEOF = \ 48 'CREATE TABLE softwarecloneof (\n' \ 49 ' id INTEGER PRIMARY KEY,\n' \ 50 ' parent INTEGER NOT NULL,\n' \ 51 ' FOREIGN KEY (id) REFERENCES software (id),\n' \ 52 ' FOREIGN KEY (parent) REFERENCES software (id))' 53 CREATE_SOFTWAREINFO = \ 54 'CREATE TABLE softwareinfo (\n' \ 55 ' id INTEGER PRIMARY KEY,\n' \ 56 ' software INTEGER NOT NULL,\n' \ 57 ' infotype INTEGER NOT NULL,\n' \ 58 ' value TEXT NOT NULL,\n' \ 59 ' FOREIGN KEY (software) REFERENCES software (id),\n' \ 60 ' FOREIGN KEY (infotype) REFERENCES softwareinfotype (id))' 61 CREATE_SOFTWARESHAREDFEAT = \ 62 'CREATE TABLE softwaresharedfeat (\n' \ 63 ' id INTEGER PRIMARY KEY,\n' \ 64 ' software INTEGER NOT NULL,\n' \ 65 ' sharedfeattype INTEGER NOT NULL,\n' \ 66 ' value TEXT NOT NULL,\n' \ 67 ' UNIQUE (software ASC, sharedfeattype ASC),\n' \ 68 ' FOREIGN KEY (software) REFERENCES software (id),\n' \ 69 ' FOREIGN KEY (sharedfeattype) REFERENCES softwaresharedfeattype (id))' 70 CREATE_SOFTWAREPART = \ 71 'CREATE TABLE softwarepart (\n' \ 72 ' id INTEGER PRIMARY KEY,\n' \ 73 ' software INTEGER NOT NULL,\n' \ 74 ' shortname TEXT NOT NULL,\n' \ 75 ' interface TEXT NOT NULL,\n' \ 76 ' UNIQUE (software ASC, shortname ASC))' 77 CREATE_SOFTWAREPARTFEATURE = \ 78 'CREATE TABLE softwarepartfeature (\n' \ 79 ' id INTEGER PRIMARY KEY,\n' \ 80 ' part INTEGER NOT NULL,\n' \ 81 ' featuretype INTEGER NOT NULL,\n' \ 82 ' value TEXT NOT NULL,\n' \ 83 ' UNIQUE (part ASC, featuretype ASC),\n' \ 84 ' FOREIGN KEY (part) REFERENCES softwarepart (id),\n' \ 85 ' FOREIGN KEY (featuretype) REFERENCES softwarepartfeaturetype (id))' 86 87 # machines 88 CREATE_FEATURETYPE = \ 89 'CREATE TABLE featuretype (\n' \ 90 ' id INTEGER PRIMARY KEY,\n' \ 91 ' name TEXT NOT NULL,\n' \ 92 ' UNIQUE (name ASC))' 93 CREATE_MACHINESOFTWARELISTSTATUSTYPE = \ 94 'CREATE TABLE machinesoftwareliststatustype (\n' \ 95 ' id INTEGER PRIMARY KEY,\n' \ 96 ' value TEXT NOT NULL,\n' \ 97 ' UNIQUE (value ASC))' 98 CREATE_SOURCEFILE = \ 99 'CREATE TABLE sourcefile (\n' \ 100 ' id INTEGER PRIMARY KEY,\n' \ 101 ' filename TEXT NOT NULL,\n' \ 102 ' UNIQUE (filename ASC))' 103 CREATE_MACHINE = \ 104 'CREATE TABLE machine (\n' \ 105 ' id INTEGER PRIMARY KEY,\n' \ 106 ' shortname TEXT NOT NULL,\n' \ 107 ' description TEXT NOT NULL,\n' \ 108 ' sourcefile INTEGER NOT NULL,\n' \ 109 ' isdevice INTEGER NOT NULL,\n' \ 110 ' runnable INTEGER NOT NULL,\n' \ 111 ' UNIQUE (shortname ASC),\n' \ 112 ' UNIQUE (description ASC),\n' \ 113 ' FOREIGN KEY (sourcefile) REFERENCES sourcefile (id))' 114 CREATE_SYSTEM = \ 115 'CREATE TABLE system (\n' \ 116 ' id INTEGER PRIMARY KEY,\n' \ 117 ' year TEXT NOT NULL,\n' \ 118 ' manufacturer TEXT NOT NULL,\n' \ 119 ' FOREIGN KEY (id) REFERENCES machine (id))' 120 CREATE_CLONEOF = \ 121 'CREATE TABLE cloneof (\n' \ 122 ' id INTEGER PRIMARY KEY,\n' \ 123 ' parent TEXT NOT NULL,\n' \ 124 ' FOREIGN KEY (id) REFERENCES machine (id))' 125 CREATE_ROMOF = \ 126 'CREATE TABLE romof (\n' \ 127 ' id INTEGER PRIMARY KEY,\n' \ 128 ' parent TEXT NOT NULL,\n' \ 129 ' FOREIGN KEY (id) REFERENCES machine (id))' 130 CREATE_BIOSSET = \ 131 'CREATE TABLE biosset (\n' \ 132 ' id INTEGER PRIMARY KEY,\n' \ 133 ' machine INTEGER NOT NULL,\n' \ 134 ' name TEXT NOT NULL,\n' \ 135 ' description TEXT NOT NULL,\n' \ 136 ' UNIQUE (machine ASC, name ASC),\n' \ 137 ' FOREIGN KEY (machine) REFERENCES machine (id))' 138 CREATE_BIOSSETDEFAULT = \ 139 'CREATE TABLE biossetdefault (\n' \ 140 ' id INTEGER PRIMARY KEY,\n' \ 141 ' FOREIGN KEY (id) REFERENCES biosset (id))' 142 CREATE_DEVICEREFERENCE = \ 143 'CREATE TABLE devicereference (\n' \ 144 ' id INTEGER PRIMARY KEY,\n' \ 145 ' machine INTEGER NOT NULL,\n' \ 146 ' device INTEGER NOT NULL,\n' \ 147 ' UNIQUE (machine ASC, device ASC),\n' \ 148 ' FOREIGN KEY (machine) REFERENCES machine (id),\n' \ 149 ' FOREIGN KEY (device) REFERENCES machine (id))' 150 CREATE_DIPSWITCH = \ 151 'CREATE TABLE dipswitch (\n' \ 152 ' id INTEGER PRIMARY KEY,\n' \ 153 ' machine INTEGER NOT NULL,\n' \ 154 ' isconfig INTEGER NOT NULL,\n' \ 155 ' name TEXT NOT NULL,\n' \ 156 ' tag TEXT NOT NULL,\n' \ 157 ' mask INTEGER NOT NULL,\n' \ 158 ' --UNIQUE (machine ASC, tag ASC, mask ASC), not necessarily true, need to expose port conditions\n' \ 159 ' FOREIGN KEY (machine) REFERENCES machine (id))' 160 CREATE_DIPLOCATION = \ 161 'CREATE TABLE diplocation (\n' \ 162 ' id INTEGER PRIMARY KEY,\n' \ 163 ' dipswitch INTEGER NOT NULL,\n' \ 164 ' bit INTEGER NOT NULL,\n' \ 165 ' name TEXT NOT NULL,\n' \ 166 ' num INTEGER NOT NULL,\n' \ 167 ' inverted INTEGER NOT NULL,\n' \ 168 ' UNIQUE (dipswitch ASC, bit ASC),\n' \ 169 ' FOREIGN KEY (dipswitch) REFERENCES dipswitch (id))' 170 CREATE_DIPVALUE = \ 171 'CREATE TABLE dipvalue (\n' \ 172 ' id INTEGER PRIMARY KEY,\n' \ 173 ' dipswitch INTEGER NOT NULL,\n' \ 174 ' name TEXT NOT NULL,\n' \ 175 ' value INTEGER NOT NULL,\n' \ 176 ' isdefault INTEGER NOT NULL,\n' \ 177 ' FOREIGN KEY (dipswitch) REFERENCES dipswitch (id))' 178 CREATE_FEATURE = \ 179 'CREATE TABLE feature (\n' \ 180 ' id INTEGER PRIMARY KEY,\n' \ 181 ' machine INTEGER NOT NULL,\n' \ 182 ' featuretype INTEGER NOT NULL,\n' \ 183 ' status INTEGER NOT NULL,\n' \ 184 ' overall INTEGER NOT NULL,\n' \ 185 ' UNIQUE (machine ASC, featuretype ASC),\n' \ 186 ' FOREIGN KEY (machine) REFERENCES machine (id),\n' \ 187 ' FOREIGN KEY (featuretype) REFERENCES featuretype (id))' 188 CREATE_SLOT = \ 189 'CREATE TABLE slot (\n' \ 190 ' id INTEGER PRIMARY KEY,\n' \ 191 ' machine INTEGER NOT NULL,\n' \ 192 ' name TEXT NOT NULL,\n' \ 193 ' UNIQUE (machine ASC, name ASC),\n' \ 194 ' FOREIGN KEY (machine) REFERENCES machine (id))' 195 CREATE_SLOTOPTION = \ 196 'CREATE TABLE slotoption (\n' \ 197 ' id INTEGER PRIMARY KEY,\n' \ 198 ' slot INTEGER NOT NULL,\n' \ 199 ' device INTEGER NOT NULL,\n' \ 200 ' name TEXT NOT NULL,\n' \ 201 ' UNIQUE (slot ASC, name ASC),\n' \ 202 ' FOREIGN KEY (slot) REFERENCES slot (id),\n' \ 203 ' FOREIGN KEY (device) REFERENCES machine (id))' 204 CREATE_SLOTDEFAULT = \ 205 'CREATE TABLE slotdefault (\n' \ 206 ' id INTEGER PRIMARY KEY,\n' \ 207 ' slotoption INTEGER NOT NULL,\n' \ 208 ' FOREIGN KEY (id) REFERENCES slot (id),\n' \ 209 ' FOREIGN KEY (slotoption) REFERENCES slotoption (id))' 210 CREATE_RAMOPTION = \ 211 'CREATE TABLE ramoption (\n' \ 212 ' machine INTEGER NOT NULL,\n' \ 213 ' size INTEGER NOT NULL,\n' \ 214 ' name TEXT NOT NULL,\n' \ 215 ' PRIMARY KEY (machine ASC, size ASC),\n' \ 216 ' FOREIGN KEY (machine) REFERENCES machine (id))' 217 CREATE_RAMDEFAULT = \ 218 'CREATE TABLE ramdefault (\n' \ 219 ' machine INTEGER PRIMARY KEY,\n' \ 220 ' size INTEGER NOT NULL,\n' \ 221 ' FOREIGN KEY (machine) REFERENCES machine (id),\n' \ 222 ' FOREIGN KEY (machine, size) REFERENCES ramoption (machine, size))' 223 CREATE_MACHINESOFTWARELIST = \ 224 'CREATE TABLE machinesoftwarelist (\n' \ 225 ' id INTEGER PRIMARY KEY,\n' \ 226 ' machine INTEGER NOT NULL,\n' \ 227 ' softwarelist INTEGER NOT NULL,\n' \ 228 ' tag TEXT NOT NULL,\n' \ 229 ' status INTEGER NOT NULL,\n' \ 230 ' UNIQUE (machine ASC, tag ASC),\n' \ 231 ' FOREIGN KEY (machine) REFERENCES machine (id),\n' \ 232 ' FOREIGN KEY (softwarelist) REFERENCES softwarelist (id),' \ 233 ' FOREIGN KEY (status) REFERENCES machinesoftwareliststatustype (id))' 234 235 # media 236 CREATE_ROM = \ 237 'CREATE TABLE rom (\n' \ 238 ' id INTEGER PRIMARY KEY,\n' \ 239 ' crc INTEGER NOT NULL,\n' \ 240 ' sha1 TEXT NOT NULL,\n' \ 241 ' UNIQUE (crc ASC, sha1 ASC))' 242 CREATE_ROMDUMP = \ 243 'CREATE TABLE romdump (\n' \ 244 ' machine INTEGER NOT NULL,\n' \ 245 ' rom INTEGER NOT NULL,\n' \ 246 ' name TEXT NOT NULL,\n' \ 247 ' bad INTEGER NOT NULL,\n' \ 248 ' FOREIGN KEY (machine) REFERENCES machine (id),\n' \ 249 ' FOREIGN KEY (rom) REFERENCES rom (id),\n' \ 250 ' UNIQUE (machine, rom, name))' 251 CREATE_SOFTWAREROMDUMP = \ 252 'CREATE TABLE softwareromdump (\n' \ 253 ' part INTEGER NOT NULL,\n' \ 254 ' rom INTEGER NOT NULL,\n' \ 255 ' name TEXT NOT NULL,\n' \ 256 ' bad INTEGER NOT NULL,\n' \ 257 ' FOREIGN KEY (part) REFERENCES softwarepart (id),\n' \ 258 ' FOREIGN KEY (rom) REFERENCES rom (id),\n' \ 259 ' UNIQUE (part, rom, name))' 260 CREATE_DISK = \ 261 'CREATE TABLE disk (\n' \ 262 ' id INTEGER PRIMARY KEY,\n' \ 263 ' sha1 TEXT NOT NULL,\n' \ 264 ' UNIQUE (sha1 ASC))' 265 CREATE_DISKDUMP = \ 266 'CREATE TABLE diskdump (\n' \ 267 ' machine INTEGER NOT NULL,\n' \ 268 ' disk INTEGER NOT NULL,\n' \ 269 ' name TEXT NOT NULL,\n' \ 270 ' bad INTEGER NOT NULL,\n' \ 271 ' FOREIGN KEY (machine) REFERENCES machine (id),\n' \ 272 ' FOREIGN KEY (disk) REFERENCES disk (id),\n' \ 273 ' UNIQUE (machine, disk, name))' 274 CREATE_SOFTWAREDISKDUMP = \ 275 'CREATE TABLE softwarediskdump (\n' \ 276 ' part INTEGER NOT NULL,\n' \ 277 ' disk INTEGER NOT NULL,\n' \ 278 ' name TEXT NOT NULL,\n' \ 279 ' bad INTEGER NOT NULL,\n' \ 280 ' FOREIGN KEY (part) REFERENCES softwarepart (id),\n' \ 281 ' FOREIGN KEY (disk) REFERENCES disk (id),\n' \ 282 ' UNIQUE (part, disk, name))' 283 284 # create temporary tables used during loading 285 CREATE_TEMPORARY_SOFTWARECLONEOF = 'CREATE TEMPORARY TABLE temp_softwarecloneof (id INTEGER PRIMARY KEY, parent TEXT NOT NULL)' 286 CREATE_TEMPORARY_DEVICEREFERENCE = 'CREATE TEMPORARY TABLE temp_devicereference (id INTEGER PRIMARY KEY, machine INTEGER NOT NULL, device TEXT NOT NULL, UNIQUE (machine, device))' 287 CREATE_TEMPORARY_SLOTOPTION = 'CREATE TEMPORARY TABLE temp_slotoption (id INTEGER PRIMARY KEY, slot INTEGER NOT NULL, device TEXT NOT NULL, name TEXT NOT NULL)' 288 CREATE_TEMPORARY_SLOTDEFAULT = 'CREATE TEMPORARY TABLE temp_slotdefault (id INTEGER PRIMARY KEY, slotoption INTEGER NOT NULL)' 289 290 # drop temporary tables used during loading 291 DROP_TEMPORARY_SOFTWARECLONEOF = 'DROP TABLE IF EXISTS temp_softwarecloneof' 292 DROP_TEMPORARY_DEVICEREFERENCE = 'DROP TABLE IF EXISTS temp_devicereference' 293 DROP_TEMPORARY_SLOTOPTION = 'DROP TABLE IF EXISTS temp_slotoption' 294 DROP_TEMPORARY_SLOTDEFAULT = 'DROP TABLE IF EXISTS temp_slotdefault' 295 296 INDEX_SOFTWARE_SOFTWARELIST_SUPPORTED = 'CREATE INDEX software_softwarelist_supported ON software (softwarelist ASC, supported ASC)' 297 INDEX_SOFTWARE_SOFTWARELIST_YEAR = 'CREATE INDEX software_softwarelist_year ON software (softwarelist ASC, year ASC)' 298 INDEX_SOFTWARE_SOFTWARELIST_PUBLISHER = 'CREATE INDEX software_softwarelist_publisher ON software (softwarelist ASC, publisher ASC)' 299 INDEX_SOFTWARE_SHORTNAME_SOFTWARELIST = 'CREATE INDEX software_shortname_softwarelist ON software (shortname ASC, softwarelist ASC)' 300 INDEX_SOFTWARE_YEAR_SOFTWARELIST = 'CREATE INDEX software_year_softwarelist ON software (year ASC, softwarelist ASC)' 301 INDEX_SOFTWARE_PUBLISHER_SOFTWARELIST = 'CREATE INDEX software_publisher_softwarelist ON software (publisher ASC, softwarelist ASC)' 302 303 INDEX_SOFTWARECLONEOF_PARENT = 'CREATE INDEX softwarecloneof_parent ON softwarecloneof (parent ASC)' 304 305 INDEX_SOFTWAREINFO_SOFTWARE_INFOTYPE = 'CREATE INDEX softwareinfo_software_infotype ON softwareinfo (software ASC, infotype ASC)' 306 INDEX_SOFTWAREINFO_INFOTYPE_VALUE_SOFTWARE = 'CREATE INDEX softwareinfo_infotype_value_software ON softwareinfo (infotype ASC, value ASC, software ASC)' 307 308 INDEX_SOFTWARESHAREDFEAT_SHAREDFEATTYPE_VALUE_SOFTWARE = 'CREATE INDEX softwaresharedfeat_sharedfeattype_value_software ON softwaresharedfeat (sharedfeattype ASC, value ASC, software ASC)' 309 310 INDEX_SOFTWAREPART_INTERFACE_SOFTWARE = 'CREATE INDEX softwarepart_interface_software ON softwarepart (interface ASC, software ASC)' 311 312 INDEX_SOFTWAREPARTFEATURE_FEATURETYPE_VALUE_PART = 'CREATE INDEX softwarepartfeature_featuretype_value_part ON softwarepartfeature (featuretype ASC, value ASC, part ASC)' 313 314 INDEX_MACHINE_ISDEVICE_SHORTNAME = 'CREATE INDEX machine_isdevice_shortname ON machine (isdevice ASC, shortname ASC)' 315 INDEX_MACHINE_ISDEVICE_DESCRIPTION = 'CREATE INDEX machine_isdevice_description ON machine (isdevice ASC, description ASC)' 316 INDEX_MACHINE_RUNNABLE_SHORTNAME = 'CREATE INDEX machine_runnable_shortname ON machine (runnable ASC, shortname ASC)' 317 INDEX_MACHINE_RUNNABLE_DESCRIPTION = 'CREATE INDEX machine_runnable_description ON machine (runnable ASC, description ASC)' 318 319 INDEX_SYSTEM_YEAR = 'CREATE INDEX system_year ON system (year ASC)' 320 INDEX_SYSTEM_MANUFACTURER = 'CREATE INDEX system_manufacturer ON system (manufacturer ASC)' 321 322 INDEX_ROMOF_PARENT = 'CREATE INDEX romof_parent ON romof (parent ASC)' 323 324 INDEX_CLONEOF_PARENT = 'CREATE INDEX cloneof_parent ON cloneof (parent ASC)' 325 326 INDEX_DIPSWITCH_MACHINE_ISCONFIG = 'CREATE INDEX dipswitch_machine_isconfig ON dipswitch (machine ASC, isconfig ASC)' 327 328 INDEX_MACHINESOFTWARELIST_SOFTWARELIST_MACHINE = 'CREATE INDEX machinesoftwarelist_softwarelist_machine ON machinesoftwarelist (softwarelist ASC, machine ASC)' 329 330 INDEX_ROMDUMP_ROM = 'CREATE INDEX romdump_rom ON romdump (rom ASC)' 331 INDEX_ROMDUMP_MACHINE_BAD = 'CREATE INDEX romdump_machine_bad ON romdump (machine ASC, bad ASC)' 332 333 INDEX_SOFTWAREROMDUMP_ROM = 'CREATE INDEX softwareromdump_rom ON softwareromdump (rom ASC)' 334 INDEX_SOFTWAREROMDUMP_PART_BAD = 'CREATE INDEX softwareromdump_part_bad ON softwareromdump (part ASC, bad ASC)' 335 336 INDEX_DISKDUMP_DISK = 'CREATE INDEX diskdump_disk ON diskdump (disk ASC)' 337 INDEX_DISKDUMP_MACHINE_BAD = 'CREATE INDEX diskdump_machine_bad ON diskdump (machine ASC, bad ASC)' 338 339 INDEX_SOFTWAREDISKDUMP_DISK = 'CREATE INDEX softwarediskdump_disk ON softwarediskdump (disk ASC)' 340 INDEX_SOFTWAREDISKDUMP_PART_BAD = 'CREATE INDEX softwarediskdump_part_bad ON softwarediskdump (part ASC, bad ASC)' 341 342 DROP_SOFTWARE_SOFTWARELIST_SUPPORTED = 'DROP INDEX IF EXISTS software_softwarelist_supported' 343 DROP_SOFTWARE_SOFTWARELIST_YEAR = 'DROP INDEX IF EXISTS software_softwarelist_year' 344 DROP_SOFTWARE_SOFTWARELIST_PUBLISHER = 'DROP INDEX IF EXISTS software_softwarelist_publisher' 345 DROP_SOFTWARE_SHORTNAME_SOFTWARELIST = 'DROP INDEX IF EXISTS software_shortname_softwarelist' 346 DROP_SOFTWARE_YEAR_SOFTWARELIST = 'DROP INDEX IF EXISTS software_year_softwarelist' 347 DROP_SOFTWARE_PUBLISHER_SOFTWARELIST = 'DROP INDEX IF EXISTS software_publisher_softwarelist' 348 349 DROP_SOFTWARECLONEOF_PARENT = 'DROP INDEX IF EXISTS softwarecloneof_parent' 350 351 DROP_SOFTWAREINFO_SOFTWARE_INFOTYPE = 'DROP INDEX IF EXISTS softwareinfo_software_infotype' 352 DROP_SOFTWAREINFO_INFOTYPE_VALUE_SOFTWARE = 'DROP INDEX IF EXISTS softwareinfo_infotype_value_software' 353 354 DROP_SOFTWARESHAREDFEAT_SHAREDFEATTYPE_VALUE_SOFTWARE = 'DROP INDEX IF EXISTS softwaresharedfeat_sharedfeattype_value_software' 355 356 DROP_SOFTWAREPART_INTERFACE_SOFTWARE = 'DROP INDEX IF EXISTS softwarepart_interface_software' 357 358 DROP_SOFTWAREPARTFEATURE_FEATURETYPE_VALUE_PART = 'DROP INDEX IF EXISTS softwarepartfeature_featuretype_value_part' 359 360 DROP_MACHINE_ISDEVICE_SHORTNAME = 'DROP INDEX IF EXISTS machine_isdevice_shortname' 361 DROP_MACHINE_ISDEVICE_DESCRIPTION = 'DROP INDEX IF EXISTS machine_isdevice_description' 362 DROP_MACHINE_RUNNABLE_SHORTNAME = 'DROP INDEX IF EXISTS machine_runnable_shortname' 363 DROP_MACHINE_RUNNABLE_DESCRIPTION = 'DROP INDEX IF EXISTS machine_runnable_description' 364 365 DROP_SYSTEM_YEAR = 'DROP INDEX IF EXISTS system_year' 366 DROP_SYSTEM_MANUFACTURER = 'DROP INDEX IF EXISTS system_manufacturer' 367 368 DROP_ROMOF_PARENT = 'DROP INDEX IF EXISTS romof_parent' 369 370 DROP_CLONEOF_PARENT = 'DROP INDEX IF EXISTS cloneof_parent' 371 372 DROP_DIPSWITCH_MACHINE_ISCONFIG = 'DROP INDEX IF EXISTS dipswitch_machine_isconfig' 373 374 DROP_MACHINESOFTWARELIST_SOFTWARELIST_MACHINE = 'DROP INDEX IF EXISTS machinesoftwarelist_softwarelist_machine' 375 376 DROP_ROMDUMP_ROM = 'DROP INDEX IF EXISTS romdump_rom' 377 DROP_ROMDUMP_MACHINE_BAD = 'DROP INDEX IF EXISTS romdump_machine_bad' 378 379 DROP_SOFTWAREROMDUMP_ROM = 'DROP INDEX IF EXISTS softwareromdump_rom' 380 DROP_SOFTWAREROMDUMP_PART_BAD = 'DROP INDEX IF EXISTS softwareromdump_part_bad' 381 382 DROP_DISKDUMP_DISK = 'DROP INDEX IF EXISTS diskdump_disk' 383 DROP_DISKDUMP_MACHINE_BAD = 'DROP INDEX IF EXISTS diskdump_machine_bad' 384 385 DROP_SOFTWAREDISKDUMP_DISK = 'DROP INDEX IF EXISTS softwarediskdump_disk' 386 DROP_SOFTWAREDISKDUMP_PART_BAD = 'DROP INDEX IF EXISTS softwarediskdump_part_bad' 387 388 CREATE_TABLES = ( 389 CREATE_SOFTWAREINFOTYPE, 390 CREATE_SOFTWARESHAREDFEATTYPE, 391 CREATE_SOFTWAREPARTFEATURETYPE, 392 CREATE_SOFTWARELIST, 393 CREATE_SOFTWARE, 394 CREATE_SOFTWARECLONEOF, 395 CREATE_SOFTWAREINFO, 396 CREATE_SOFTWARESHAREDFEAT, 397 CREATE_SOFTWAREPART, 398 CREATE_SOFTWAREPARTFEATURE, 399 CREATE_FEATURETYPE, 400 CREATE_MACHINESOFTWARELISTSTATUSTYPE, 401 CREATE_SOURCEFILE, 402 CREATE_MACHINE, 403 CREATE_SYSTEM, 404 CREATE_CLONEOF, 405 CREATE_ROMOF, 406 CREATE_BIOSSET, 407 CREATE_BIOSSETDEFAULT, 408 CREATE_DEVICEREFERENCE, 409 CREATE_DIPSWITCH, 410 CREATE_DIPLOCATION, 411 CREATE_DIPVALUE, 412 CREATE_FEATURE, 413 CREATE_SLOT, 414 CREATE_SLOTOPTION, 415 CREATE_SLOTDEFAULT, 416 CREATE_RAMOPTION, 417 CREATE_RAMDEFAULT, 418 CREATE_MACHINESOFTWARELIST, 419 CREATE_ROM, 420 CREATE_ROMDUMP, 421 CREATE_SOFTWAREROMDUMP, 422 CREATE_DISK, 423 CREATE_DISKDUMP, 424 CREATE_SOFTWAREDISKDUMP) 425 426 CREATE_TEMPORARY_TABLES = ( 427 CREATE_TEMPORARY_SOFTWARECLONEOF, 428 CREATE_TEMPORARY_DEVICEREFERENCE, 429 CREATE_TEMPORARY_SLOTOPTION, 430 CREATE_TEMPORARY_SLOTDEFAULT) 431 432 CREATE_INDEXES = ( 433 INDEX_SOFTWARE_SOFTWARELIST_SUPPORTED, 434 INDEX_SOFTWARE_SOFTWARELIST_YEAR, 435 INDEX_SOFTWARE_SOFTWARELIST_PUBLISHER, 436 INDEX_SOFTWARE_SHORTNAME_SOFTWARELIST, 437 INDEX_SOFTWARE_YEAR_SOFTWARELIST, 438 INDEX_SOFTWARE_PUBLISHER_SOFTWARELIST, 439 INDEX_SOFTWARECLONEOF_PARENT, 440 INDEX_SOFTWAREINFO_SOFTWARE_INFOTYPE, 441 INDEX_SOFTWAREINFO_INFOTYPE_VALUE_SOFTWARE, 442 INDEX_SOFTWARESHAREDFEAT_SHAREDFEATTYPE_VALUE_SOFTWARE, 443 INDEX_SOFTWAREPART_INTERFACE_SOFTWARE, 444 INDEX_SOFTWAREPARTFEATURE_FEATURETYPE_VALUE_PART, 445 INDEX_MACHINE_ISDEVICE_SHORTNAME, 446 INDEX_MACHINE_ISDEVICE_DESCRIPTION, 447 INDEX_MACHINE_RUNNABLE_SHORTNAME, 448 INDEX_MACHINE_RUNNABLE_DESCRIPTION, 449 INDEX_SYSTEM_YEAR, 450 INDEX_SYSTEM_MANUFACTURER, 451 INDEX_ROMOF_PARENT, 452 INDEX_CLONEOF_PARENT, 453 INDEX_DIPSWITCH_MACHINE_ISCONFIG, 454 INDEX_MACHINESOFTWARELIST_SOFTWARELIST_MACHINE, 455 INDEX_ROMDUMP_ROM, 456 INDEX_ROMDUMP_MACHINE_BAD, 457 INDEX_SOFTWAREROMDUMP_ROM, 458 INDEX_SOFTWAREROMDUMP_PART_BAD, 459 INDEX_DISKDUMP_DISK, 460 INDEX_DISKDUMP_MACHINE_BAD, 461 INDEX_SOFTWAREDISKDUMP_DISK, 462 INDEX_SOFTWAREDISKDUMP_PART_BAD) 463 464 DROP_INDEXES = ( 465 DROP_SOFTWARE_SOFTWARELIST_SUPPORTED, 466 DROP_SOFTWARE_SOFTWARELIST_YEAR, 467 DROP_SOFTWARE_SOFTWARELIST_PUBLISHER, 468 DROP_SOFTWARE_SHORTNAME_SOFTWARELIST, 469 DROP_SOFTWARE_YEAR_SOFTWARELIST, 470 DROP_SOFTWARE_PUBLISHER_SOFTWARELIST, 471 DROP_SOFTWARECLONEOF_PARENT, 472 DROP_SOFTWAREINFO_SOFTWARE_INFOTYPE, 473 DROP_SOFTWAREINFO_INFOTYPE_VALUE_SOFTWARE, 474 DROP_SOFTWARESHAREDFEAT_SHAREDFEATTYPE_VALUE_SOFTWARE, 475 DROP_SOFTWAREPART_INTERFACE_SOFTWARE, 476 DROP_SOFTWAREPARTFEATURE_FEATURETYPE_VALUE_PART, 477 DROP_MACHINE_ISDEVICE_SHORTNAME, 478 DROP_MACHINE_ISDEVICE_DESCRIPTION, 479 DROP_MACHINE_RUNNABLE_SHORTNAME, 480 DROP_MACHINE_RUNNABLE_DESCRIPTION, 481 DROP_SYSTEM_YEAR, 482 DROP_SYSTEM_MANUFACTURER, 483 DROP_ROMOF_PARENT, 484 DROP_CLONEOF_PARENT, 485 DROP_DIPSWITCH_MACHINE_ISCONFIG, 486 DROP_MACHINESOFTWARELIST_SOFTWARELIST_MACHINE, 487 DROP_ROMDUMP_ROM, 488 DROP_ROMDUMP_MACHINE_BAD, 489 DROP_SOFTWAREROMDUMP_ROM, 490 DROP_SOFTWAREROMDUMP_PART_BAD, 491 DROP_DISKDUMP_DISK, 492 DROP_DISKDUMP_MACHINE_BAD, 493 DROP_SOFTWAREDISKDUMP_DISK, 494 DROP_SOFTWAREDISKDUMP_PART_BAD) 495 496 497class UpdateQueries(object): 498 # software lists 499 ADD_SOFTWAREINFOTYPE = 'INSERT OR IGNORE INTO softwareinfotype (name) VALUES (?)' 500 ADD_SOFTWARESHAREDFEATTYPE = 'INSERT OR IGNORE INTO softwaresharedfeattype (name) VALUES (?)' 501 ADD_SOFTWAREPARTFEATURETYPE = 'INSERT OR IGNORE INTO softwarepartfeaturetype (name) VALUES(?)' 502 ADD_SOFTWARELIST = 'INSERT INTO softwarelist (shortname, description) VALUES (?, ?)' 503 ADD_SOFTWARE = 'INSERT INTO software (softwarelist, shortname, supported, description, year, publisher) VALUES (?, ?, ?, ?, ?, ?)' 504 ADD_SOFTWAREINFO = 'INSERT INTO softwareinfo (software, infotype, value) SELECT ?, id, ? FROM softwareinfotype WHERE name = ?' 505 ADD_SOFTWARESHAREDFEAT = 'INSERT INTO softwaresharedfeat (software, sharedfeattype, value) SELECT ?, id, ? FROM softwaresharedfeattype WHERE name = ?' 506 ADD_SOFTWAREPART = 'INSERT INTO softwarepart (software, shortname, interface) VALUES (?, ?, ?)' 507 ADD_SOFTWAREPARTFEATURE = 'INSERT INTO softwarepartfeature (part, featuretype, value) SELECT ?, id, ? FROM softwarepartfeaturetype WHERE name = ?' 508 509 # machines 510 ADD_FEATURETYPE = 'INSERT OR IGNORE INTO featuretype (name) VALUES (?)' 511 ADD_MACHINESOFTWARELISTSTATUSTYPE = 'INSERT OR IGNORE INTO machinesoftwareliststatustype (value) VALUES (?)' 512 ADD_SOURCEFILE = 'INSERT OR IGNORE INTO sourcefile (filename) VALUES (?)' 513 ADD_MACHINE = 'INSERT INTO machine (shortname, description, sourcefile, isdevice, runnable) SELECT ?, ?, id, ?, ? FROM sourcefile WHERE filename = ?' 514 ADD_SYSTEM = 'INSERT INTO system (id, year, manufacturer) VALUES (?, ?, ?)' 515 ADD_CLONEOF = 'INSERT INTO cloneof (id, parent) VALUES (?, ?)' 516 ADD_ROMOF = 'INSERT INTO romof (id, parent) VALUES (?, ?)' 517 ADD_BIOSSET = 'INSERT INTO biosset (machine, name, description) VALUES (?, ?, ?)' 518 ADD_BIOSSETDEFAULT = 'INSERT INTO biossetdefault (id) VALUES (?)' 519 ADD_DIPSWITCH = 'INSERT INTO dipswitch (machine, isconfig, name, tag, mask) VALUES (?, ?, ?, ?, ?)' 520 ADD_DIPLOCATION = 'INSERT INTO diplocation (dipswitch, bit, name, num, inverted) VALUES (?, ?, ?, ?, ?)' 521 ADD_DIPVALUE = 'INSERT INTO dipvalue (dipswitch, name, value, isdefault) VALUES (?, ?, ?, ?)' 522 ADD_FEATURE = 'INSERT INTO feature (machine, featuretype, status, overall) SELECT ?, id, ?, ? FROM featuretype WHERE name = ?' 523 ADD_SLOT = 'INSERT INTO slot (machine, name) VALUES (?, ?)' 524 ADD_RAMOPTION = 'INSERT INTO ramoption (machine, size, name) VALUES (?, ?, ?)' 525 ADD_RAMDEFAULT = 'INSERT INTO ramdefault (machine, size) VALUES (?, ?)' 526 ADD_MACHINESOFTWARELIST = 'INSERT INTO machinesoftwarelist (machine, softwarelist, tag, status) SELECT ?, softwarelist.id, ?, machinesoftwareliststatustype.id FROM softwarelist JOIN machinesoftwareliststatustype WHERE softwarelist.shortname = ? AND machinesoftwareliststatustype.value = ?' 527 528 # media 529 ADD_ROM = 'INSERT OR IGNORE INTO rom (crc, sha1) VALUES (?, ?)' 530 ADD_ROMDUMP = 'INSERT OR IGNORE INTO romdump (machine, rom, name, bad) SELECT ?, id, ?, ? FROM rom WHERE crc = ? AND sha1 = ?' 531 ADD_SOFTWAREROMDUMP = 'INSERT OR IGNORE INTO softwareromdump (part, rom, name, bad) SELECT ?, id, ?, ? FROM rom WHERE crc = ? AND sha1 = ?' 532 ADD_DISK = 'INSERT OR IGNORE INTO disk (sha1) VALUES (?)' 533 ADD_DISKDUMP = 'INSERT OR IGNORE INTO diskdump (machine, disk, name, bad) SELECT ?, id, ?, ? FROM disk WHERE sha1 = ?' 534 ADD_SOFTWAREDISKDUMP = 'INSERT OR IGNORE INTO softwarediskdump (part, disk, name, bad) SELECT ?, id, ?, ? FROM disk WHERE sha1 = ?' 535 536 # temporary records used during loading 537 ADD_TEMPORARY_SOFTWARECLONEOF = 'INSERT INTO temp_softwarecloneof (id, parent) VALUES (?, ?)' 538 ADD_TEMPORARY_DEVICEREFERENCE = 'INSERT OR IGNORE INTO temp_devicereference (machine, device) VALUES (?, ?)' 539 ADD_TEMPORARY_SLOTOPTION = 'INSERT INTO temp_slotoption (slot, device, name) VALUES (?, ?, ?)' 540 ADD_TEMPORARY_SLOTDEFAULT = 'INSERT INTO temp_slotdefault (id, slotoption) VALUES (?, ?)' 541 542 # convert temporary tables to final form 543 FINALISE_SOFTWARECLONEOFS = \ 544 'INSERT INTO softwarecloneof (id, parent) ' \ 545 'SELECT temp_softwarecloneof.id AS id, parent.id AS parent ' \ 546 'FROM temp_softwarecloneof LEFT JOIN software AS clone ON temp_softwarecloneof.id = clone.id LEFT JOIN software AS parent ON clone.softwarelist = parent.softwarelist AND temp_softwarecloneof.parent = parent.shortname' 547 FINALISE_DEVICEREFERENCES = \ 548 'INSERT INTO devicereference (id, machine, device) ' \ 549 'SELECT temp_devicereference.id, temp_devicereference.machine, machine.id ' \ 550 'FROM temp_devicereference LEFT JOIN machine ON temp_devicereference.device = machine.shortname' 551 FINALISE_SLOTOPTIONS = \ 552 'INSERT INTO slotoption (id, slot, device, name) ' \ 553 'SELECT temp_slotoption.id, temp_slotoption.slot, machine.id, temp_slotoption.name ' \ 554 'FROM temp_slotoption LEFT JOIN machine ON temp_slotoption.device = machine.shortname' 555 FINALISE_SLOTDEFAULTS = \ 556 'INSERT INTO slotdefault (id, slotoption) ' \ 557 'SELECT id, slotoption ' \ 558 'FROM temp_slotdefault' 559 560 561class QueryCursor(object): 562 def __init__(self, dbconn, **kwargs): 563 super(QueryCursor, self).__init__(**kwargs) 564 self.dbcurs = dbconn.cursor() 565 566 def close(self): 567 self.dbcurs.close() 568 569 def is_glob(self, *patterns): 570 for pattern in patterns: 571 if any(ch in pattern for ch in '?*['): 572 return True 573 return False 574 575 def count_systems(self, pattern): 576 if pattern is not None: 577 return self.dbcurs.execute( 578 'SELECT COUNT(*) ' \ 579 'FROM machine WHERE isdevice = 0 AND shortname GLOB ? ', 580 (pattern, )) 581 else: 582 return self.dbcurs.execute( 583 'SELECT COUNT(*) ' \ 584 'FROM machine WHERE isdevice = 0 ') 585 586 def listfull(self, pattern): 587 if pattern is not None: 588 return self.dbcurs.execute( 589 'SELECT shortname, description ' \ 590 'FROM machine WHERE isdevice = 0 AND shortname GLOB ? ' \ 591 'ORDER BY shortname ASC', 592 (pattern, )) 593 else: 594 return self.dbcurs.execute( 595 'SELECT shortname, description ' \ 596 'FROM machine WHERE isdevice = 0 ' \ 597 'ORDER BY shortname ASC') 598 599 def listsource(self, pattern): 600 if pattern is not None: 601 return self.dbcurs.execute( 602 'SELECT machine.shortname, sourcefile.filename ' \ 603 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id ' \ 604 'WHERE machine.isdevice = 0 AND machine.shortname GLOB ? ' \ 605 'ORDER BY machine.shortname ASC', 606 (pattern, )) 607 else: 608 return self.dbcurs.execute( 609 'SELECT machine.shortname, sourcefile.filename ' \ 610 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id ' \ 611 'WHERE machine.isdevice = 0 ORDER BY machine.shortname ASC') 612 613 def listclones(self, pattern): 614 if pattern is not None: 615 return self.dbcurs.execute( 616 'SELECT machine.shortname, cloneof.parent ' \ 617 'FROM machine JOIN cloneof ON machine.id = cloneof.id ' \ 618 'WHERE machine.shortname GLOB ? OR cloneof.parent GLOB ? ' \ 619 'ORDER BY machine.shortname ASC', 620 (pattern, pattern)) 621 else: 622 return self.dbcurs.execute( 623 'SELECT machine.shortname, cloneof.parent ' \ 624 'FROM machine JOIN cloneof ON machine.id = cloneof.id ' \ 625 'ORDER BY machine.shortname ASC') 626 627 def listbrothers(self, pattern): 628 if pattern is not None: 629 return self.dbcurs.execute( 630 'SELECT sourcefile.filename, machine.shortname, cloneof.parent ' \ 631 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id LEFT JOIN cloneof ON machine.id = cloneof.id ' \ 632 'WHERE machine.isdevice = 0 AND sourcefile.id IN (SELECT sourcefile FROM machine WHERE shortname GLOB ?)' \ 633 'ORDER BY machine.shortname ASC', 634 (pattern, )) 635 else: 636 return self.dbcurs.execute( 637 'SELECT sourcefile.filename, machine.shortname, cloneof.parent ' \ 638 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id LEFT JOIN cloneof ON machine.id = cloneof.id ' \ 639 'WHERE machine.isdevice = 0 ' \ 640 'ORDER BY machine.shortname ASC') 641 642 def listaffected(self, *patterns): 643 if 1 == len(patterns): 644 return self.dbcurs.execute( 645 'SELECT shortname, description ' \ 646 'FROM machine ' \ 647 '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 ' \ 648 'ORDER BY shortname ASC', 649 patterns) 650 elif self.is_glob(*patterns): 651 return self.dbcurs.execute( 652 'SELECT shortname, description ' \ 653 'FROM machine ' \ 654 '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 ' \ 655 'ORDER BY shortname ASC', 656 patterns) 657 else: 658 return self.dbcurs.execute( 659 'SELECT shortname, description ' \ 660 'FROM machine ' \ 661 '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 ' \ 662 'ORDER BY shortname ASC', 663 patterns) 664 665 def get_machine_id(self, machine): 666 return (self.dbcurs.execute('SELECT id FROM machine WHERE shortname = ?', (machine, )).fetchone() or (None, ))[0] 667 668 def get_machine_details(self, machine): 669 return self.dbcurs.execute( 670 '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 ' \ 671 '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 ' \ 672 'WHERE machine.shortname = ?', 673 (machine, )) 674 675 def get_biossets(self, machine): 676 return self.dbcurs.execute( 677 'SELECT biosset.name AS name, biosset.description AS description, COUNT(biossetdefault.id) AS isdefault ' \ 678 'FROM biosset LEFT JOIN biossetdefault USING (id) ' \ 679 'WHERE biosset.machine = ? ' \ 680 'GROUP BY biosset.id', 681 (machine, )) 682 683 def get_devices_referenced(self, machine): 684 return self.dbcurs.execute( 685 'SELECT machine.shortname AS shortname, machine.description AS description, sourcefile.filename AS sourcefile ' \ 686 'FROM devicereference LEFT JOIN machine ON devicereference.device = machine.id LEFT JOIN sourcefile ON machine.sourcefile = sourcefile.id ' \ 687 'WHERE devicereference.machine = ?', 688 (machine, )) 689 690 def get_device_references(self, device): 691 return self.dbcurs.execute( 692 'SELECT machine.shortname AS shortname, machine.description AS description, sourcefile.filename AS sourcefile ' \ 693 'FROM machine JOIN sourcefile ON machine.sourcefile = sourcefile.id ' \ 694 'WHERE machine.id IN (SELECT machine FROM devicereference WHERE device = ?)', 695 (device, )) 696 697 def get_compatible_slots(self, device): 698 return self.dbcurs.execute( 699 'SELECT machine.shortname AS shortname, machine.description AS description, slot.name AS slot, slotoption.name AS slotoption, sourcefile.filename AS sourcefile ' \ 700 'FROM slotoption JOIN slot ON slotoption.slot = slot.id JOIN machine on slot.machine = machine.id JOIN sourcefile ON machine.sourcefile = sourcefile.id ' 701 'WHERE slotoption.device = ?', 702 (device, )) 703 704 def get_sourcefile_id(self, filename): 705 return (self.dbcurs.execute('SELECT id FROM sourcefile WHERE filename = ?', (filename, )).fetchone() or (None, ))[0] 706 707 def get_sourcefile_machines(self, id): 708 return self.dbcurs.execute( 709 '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 ' \ 710 '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 ' \ 711 'WHERE machine.sourcefile = ?', 712 (id, )) 713 714 def get_sourcefiles(self, pattern): 715 if pattern is not None: 716 return self.dbcurs.execute( 717 'SELECT sourcefile.filename AS filename, COUNT(machine.id) AS machines ' \ 718 'FROM sourcefile LEFT JOIN machine ON sourcefile.id = machine.sourcefile ' \ 719 'WHERE sourcefile.filename GLOB ?' \ 720 'GROUP BY sourcefile.id ', 721 (pattern, )) 722 else: 723 return self.dbcurs.execute( 724 'SELECT sourcefile.filename AS filename, COUNT(machine.id) AS machines ' \ 725 'FROM sourcefile LEFT JOIN machine ON sourcefile.id = machine.sourcefile ' \ 726 'GROUP BY sourcefile.id') 727 728 def count_sourcefiles(self, pattern): 729 if pattern is not None: 730 return self.dbcurs.execute('SELECT COUNT(*) FROM sourcefile WHERE filename GLOB ?', (pattern, )).fetchone()[0] 731 else: 732 return self.dbcurs.execute('SELECT COUNT(*) FROM sourcefile').fetchone()[0] 733 734 def get_clones(self, shortname): 735 return self.dbcurs.execute( 736 'SELECT machine.shortname AS shortname, machine.description AS description, system.year AS year, system.manufacturer AS manufacturer ' \ 737 'FROM cloneof JOIN machine ON cloneof.id = machine.id LEFT JOIN system ON machine.id = system.id ' \ 738 'WHERE cloneof.parent = ?', 739 (shortname, )) 740 741 def count_slots(self, machine): 742 return self.dbcurs.execute( 743 'SELECT COUNT(*) FROM slot WHERE machine = ?', (machine, )).fetchone()[0] 744 745 def get_feature_flags(self, machine): 746 return self.dbcurs.execute( 747 'SELECT featuretype.name AS featuretype, feature.status AS status, feature.overall AS overall ' \ 748 'FROM feature JOIN featuretype ON feature.featuretype = featuretype.id ' \ 749 'WHERE feature.machine = ?', 750 (machine, )) 751 752 def get_slot_defaults(self, machine): 753 return self.dbcurs.execute( 754 'SELECT slot.name AS name, slotoption.name AS option ' \ 755 'FROM slot JOIN slotdefault ON slot.id = slotdefault.id JOIN slotoption ON slotdefault.slotoption = slotoption.id ' \ 756 'WHERE slot.machine = ?', 757 (machine, )) 758 759 def get_slot_options(self, machine): 760 return self.dbcurs.execute( 761 'SELECT slot.name AS slot, slotoption.name AS option, machine.shortname AS shortname, machine.description AS description ' \ 762 'FROM slot JOIN slotoption ON slot.id = slotoption.slot JOIN machine ON slotoption.device = machine.id ' \ 763 'WHERE slot.machine = ?', 764 (machine, )) 765 766 def get_ram_options(self, machine): 767 return self.dbcurs.execute( 768 'SELECT ramoption.name AS name, ramoption.size AS size, COUNT(ramdefault.machine) AS isdefault ' \ 769 'FROM ramoption LEFT JOIN ramdefault USING (machine, size) WHERE ramoption.machine = ? ' \ 770 'GROUP BY ramoption.machine, ramoption.size ' \ 771 'ORDER BY ramoption.size', 772 (machine, )) 773 774 def get_machine_softwarelists(self, machine): 775 return self.dbcurs.execute( 776 'SELECT machinesoftwarelist.tag AS tag, machinesoftwareliststatustype.value AS status, 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 ' \ 777 'FROM machinesoftwarelist LEFT JOIN machinesoftwareliststatustype ON machinesoftwarelist.status = machinesoftwareliststatustype.id LEFT JOIN softwarelist ON machinesoftwarelist.softwarelist = softwarelist.id LEFT JOIN software ON softwarelist.id = software.softwarelist ' \ 778 'WHERE machinesoftwarelist.machine = ? ' \ 779 'GROUP BY machinesoftwarelist.id', 780 (machine, )) 781 782 def get_softwarelist_id(self, shortname): 783 return (self.dbcurs.execute('SELECT id FROM softwarelist WHERE shortname = ?', (shortname, )).fetchone() or (None, ))[0] 784 785 def get_softwarelist_details(self, shortname, pattern): 786 if pattern is not None: 787 return self.dbcurs.execute( 788 '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 ' \ 789 'FROM softwarelist LEFT JOIN software ON softwarelist.id = software.softwarelist ' \ 790 'WHERE softwarelist.shortname = ? AND software.shortname GLOB ? ' \ 791 'GROUP BY softwarelist.id', 792 (shortname, pattern)) 793 else: 794 return self.dbcurs.execute( 795 '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 ' \ 796 'FROM softwarelist LEFT JOIN software ON softwarelist.id = software.softwarelist ' \ 797 'WHERE softwarelist.shortname = ? ' \ 798 'GROUP BY softwarelist.id', 799 (shortname, )) 800 801 def get_softwarelist_software(self, id, pattern): 802 if pattern is not None: 803 return self.dbcurs.execute( 804 'SELECT software.shortname AS shortname, software.description AS description, software.year AS year, software.publisher AS publisher, software.supported AS supported, COUNT(DISTINCT softwarepart.id) AS parts, COUNT(softwareromdump.rom) + COUNT(softwarediskdump.disk) AS baddumps, parent.shortname AS parent, parent.description AS parentdescription, softwarelist.shortname AS parentsoftwarelist, softwarelist.description AS parentsoftwarelistdescription ' \ 805 'FROM software JOIN softwarepart ON software.id = softwarepart.software LEFT JOIN softwareromdump ON softwarepart.id = softwareromdump.part AND softwareromdump.bad = 1 LEFT JOIN softwarediskdump ON softwarepart.id = softwarediskdump.part AND softwarediskdump.bad = 1 LEFT JOIN softwarecloneof ON software.id = softwarecloneof.id LEFT JOIN software AS parent ON softwarecloneof.parent = parent.id LEFT JOIN softwarelist ON parent.softwarelist = softwarelist.id ' \ 806 'WHERE software.softwarelist = ? AND software.shortname GLOB ? ' \ 807 'GROUP BY software.id', 808 (id, pattern)) 809 else: 810 return self.dbcurs.execute( 811 'SELECT software.shortname AS shortname, software.description AS description, software.year AS year, software.publisher AS publisher, software.supported AS supported, COUNT(DISTINCT softwarepart.id) AS parts, COUNT(softwareromdump.rom) + COUNT(softwarediskdump.disk) AS baddumps, parent.shortname AS parent, parent.description AS parentdescription, softwarelist.shortname AS parentsoftwarelist, softwarelist.description AS parentsoftwarelistdescription ' \ 812 'FROM software JOIN softwarepart ON software.id = softwarepart.software LEFT JOIN softwareromdump ON softwarepart.id = softwareromdump.part AND softwareromdump.bad = 1 LEFT JOIN softwarediskdump ON softwarepart.id = softwarediskdump.part AND softwarediskdump.bad = 1 LEFT JOIN softwarecloneof ON software.id = softwarecloneof.id LEFT JOIN software AS parent ON softwarecloneof.parent = parent.id LEFT JOIN softwarelist ON parent.softwarelist = softwarelist.id ' \ 813 'WHERE software.softwarelist = ? ' \ 814 'GROUP BY software.id', 815 (id, )) 816 817 def get_softwarelist_machines(self, id): 818 return self.dbcurs.execute( 819 'SELECT machine.shortname AS shortname, machine.description AS description, system.year AS year, system.manufacturer AS manufacturer, machinesoftwareliststatustype.value AS status ' \ 820 'FROM (SELECT DISTINCT machine, status FROM machinesoftwarelist WHERE softwarelist = ?) AS softwarelists LEFT JOIN machine ON softwarelists.machine = machine.id LEFT JOIN system ON machine.id = system.id LEFT JOIN machinesoftwareliststatustype ON softwarelists.status = machinesoftwareliststatustype.id', 821 (id, )) 822 823 def get_softwarelists(self, pattern): 824 if pattern is not None: 825 return self.dbcurs.execute( 826 '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 ' \ 827 'FROM softwarelist LEFT JOIN software ON softwarelist.id = software.softwarelist ' \ 828 'WHERE softwarelist.shortname GLOB ? ' \ 829 'GROUP BY softwarelist.id', 830 (pattern, )) 831 else: 832 return self.dbcurs.execute( 833 '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 ' \ 834 'FROM softwarelist LEFT JOIN software ON softwarelist.id = software.softwarelist ' \ 835 'GROUP BY softwarelist.id') 836 837 def get_software_details(self, softwarelist, software): 838 return self.dbcurs.execute( 839 '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, parent.shortname AS parent, parent.description AS parentdescription, parentsoftwarelist.shortname AS parentsoftwarelist, parentsoftwarelist.description AS parentsoftwarelistdescription ' \ 840 'FROM software LEFT JOIN softwarelist ON software.softwarelist = softwarelist.id LEFT JOIN softwarecloneof ON software.id = softwarecloneof.id LEFT JOIN software AS parent ON softwarecloneof.parent = parent.id LEFT JOIN softwarelist AS parentsoftwarelist ON parent.softwarelist = parentsoftwarelist.id ' \ 841 'WHERE software.softwarelist = (SELECT id FROM softwarelist WHERE shortname = ?) AND software.shortname = ?', 842 (softwarelist, software)) 843 844 def get_software_clones(self, software): 845 return self.dbcurs.execute( 846 'SELECT software.shortname AS shortname, software.description AS description, software.year AS year, software.publisher AS publisher, software.supported AS supported, softwarelist.shortname AS softwarelist, softwarelist.description AS softwarelistdescription ' \ 847 'FROM softwarecloneof LEFT JOIN software ON softwarecloneof.id = software.id LEFT JOIN softwarelist ON software.softwarelist = softwarelist.id ' \ 848 'WHERE softwarecloneof.parent = ?', 849 (software, )) 850 851 def get_software_info(self, software): 852 return self.dbcurs.execute( 853 'SELECT softwareinfotype.name AS name, softwareinfo.value AS value ' \ 854 'FROM softwareinfo JOIN softwareinfotype ON softwareinfo.infotype = softwareinfotype.id ' \ 855 'WHERE softwareinfo.software = ? ' \ 856 'ORDER BY softwareinfotype.name ASC, softwareinfo.value ASC', 857 (software, )) 858 859 def get_software_parts(self, software): 860 return self.dbcurs.execute( 861 'SELECT softwarepart.id AS id, softwarepart.shortname AS shortname, softwarepart.interface AS interface, softwarepartfeature.value AS part_id ' \ 862 'FROM softwarepart LEFT JOIN softwarepartfeature ON softwarepart.id = softwarepartfeature.part AND softwarepartfeature.featuretype = (SELECT id FROM softwarepartfeaturetype WHERE name = \'part_id\') ' \ 863 'WHERE softwarepart.software = ?', 864 (software, )) 865 866 def get_softwarepart_features(self, part): 867 return self.dbcurs.execute( 868 'SELECT softwarepartfeaturetype.name AS name, softwarepartfeature.value AS value ' \ 869 'FROM softwarepartfeature LEFT JOIN softwarepartfeaturetype ON softwarepartfeature.featuretype = softwarepartfeaturetype.id ' \ 870 'WHERE softwarepartfeature.part = ? ' 871 'ORDER BY softwarepartfeaturetype.name ASC', 872 (part, )) 873 874 def get_rom_dumps(self, crc, sha1): 875 return self.dbcurs.execute( 876 'SELECT machine.shortname AS shortname, machine.description AS description, romdump.name AS label, romdump.bad AS bad ' \ 877 'FROM romdump LEFT JOIN machine ON romdump.machine = machine.id ' \ 878 'WHERE romdump.rom = (SELECT id FROM rom WHERE crc = ? AND sha1 = ?)', 879 (crc, sha1)) 880 881 def get_software_rom_dumps(self, crc, sha1): 882 return self.dbcurs.execute( 883 '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 ' \ 884 '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 ' \ 885 'WHERE softwareromdump.rom = (SELECT id FROM rom WHERE crc = ? AND sha1 = ?)', 886 (crc, sha1)) 887 888 def get_disk_dumps(self, sha1): 889 return self.dbcurs.execute( 890 'SELECT machine.shortname AS shortname, machine.description AS description, diskdump.name AS label, diskdump.bad AS bad ' \ 891 'FROM diskdump LEFT JOIN machine ON diskdump.machine = machine.id ' \ 892 'WHERE diskdump.disk = (SELECT id FROM disk WHERE sha1 = ?)', 893 (sha1, )) 894 895 def get_software_disk_dumps(self, sha1): 896 return self.dbcurs.execute( 897 '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 ' \ 898 '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 ' \ 899 'WHERE softwarediskdump.disk = (SELECT id FROM disk WHERE sha1 = ?)', 900 (sha1, )) 901 902 903class UpdateCursor(object): 904 def __init__(self, dbconn, **kwargs): 905 super(UpdateCursor, self).__init__(**kwargs) 906 self.dbcurs = dbconn.cursor() 907 908 def close(self): 909 self.dbcurs.close() 910 911 def add_softwarelist(self, shortname, description): 912 self.dbcurs.execute(UpdateQueries.ADD_SOFTWARELIST, (shortname, description)) 913 return self.dbcurs.lastrowid 914 915 def add_softwareinfotype(self, name): 916 self.dbcurs.execute(UpdateQueries.ADD_SOFTWAREINFOTYPE, (name, )) 917 918 def add_softwaresharedfeattype(self, name): 919 self.dbcurs.execute(UpdateQueries.ADD_SOFTWARESHAREDFEATTYPE, (name, )) 920 921 def add_softwarepartfeaturetype(self, name): 922 self.dbcurs.execute(UpdateQueries.ADD_SOFTWAREPARTFEATURETYPE, (name, )) 923 924 def add_software(self, softwarelist, shortname, supported, description, year, publisher): 925 self.dbcurs.execute(UpdateQueries.ADD_SOFTWARE, (softwarelist, shortname, supported, description, year, publisher)) 926 return self.dbcurs.lastrowid 927 928 def add_softwarecloneof(self, software, parent): 929 self.dbcurs.execute(UpdateQueries.ADD_TEMPORARY_SOFTWARECLONEOF, (software, parent)) 930 return self.dbcurs.lastrowid 931 932 def add_softwareinfo(self, software, infotype, value): 933 self.dbcurs.execute(UpdateQueries.ADD_SOFTWAREINFO, (software, value, infotype)) 934 return self.dbcurs.lastrowid 935 936 def add_softwaresharedfeat(self, software, sharedfeattype, value): 937 self.dbcurs.execute(UpdateQueries.ADD_SOFTWARESHAREDFEAT, (software, value, sharedfeattype)) 938 return self.dbcurs.lastrowid 939 940 def add_softwarepart(self, software, shortname, interface): 941 self.dbcurs.execute(UpdateQueries.ADD_SOFTWAREPART, (software, shortname, interface)) 942 return self.dbcurs.lastrowid 943 944 def add_softwarepartfeature(self, part, featuretype, value): 945 self.dbcurs.execute(UpdateQueries.ADD_SOFTWAREPARTFEATURE, (part, value, featuretype)) 946 return self.dbcurs.lastrowid 947 948 def add_featuretype(self, name): 949 self.dbcurs.execute(UpdateQueries.ADD_FEATURETYPE, (name, )) 950 951 def add_sourcefile(self, filename): 952 self.dbcurs.execute(UpdateQueries.ADD_SOURCEFILE, (filename, )) 953 954 def add_machine(self, shortname, description, sourcefile, isdevice, runnable): 955 self.dbcurs.execute(UpdateQueries.ADD_MACHINE, (shortname, description, isdevice, runnable, sourcefile)) 956 return self.dbcurs.lastrowid 957 958 def add_system(self, machine, year, manufacturer): 959 self.dbcurs.execute(UpdateQueries.ADD_SYSTEM, (machine, year, manufacturer)) 960 return self.dbcurs.lastrowid 961 962 def add_cloneof(self, machine, parent): 963 self.dbcurs.execute(UpdateQueries.ADD_CLONEOF, (machine, parent)) 964 return self.dbcurs.lastrowid 965 966 def add_romof(self, machine, parent): 967 self.dbcurs.execute(UpdateQueries.ADD_ROMOF, (machine, parent)) 968 return self.dbcurs.lastrowid 969 970 def add_biosset(self, machine, name, description): 971 self.dbcurs.execute(UpdateQueries.ADD_BIOSSET, (machine, name, description)) 972 return self.dbcurs.lastrowid 973 974 def add_biossetdefault(self, biosset): 975 self.dbcurs.execute(UpdateQueries.ADD_BIOSSETDEFAULT, (biosset, )) 976 return self.dbcurs.lastrowid 977 978 def add_devicereference(self, machine, device): 979 self.dbcurs.execute(UpdateQueries.ADD_TEMPORARY_DEVICEREFERENCE, (machine, device)) 980 981 def add_dipswitch(self, machine, isconfig, name, tag, mask): 982 self.dbcurs.execute(UpdateQueries.ADD_DIPSWITCH, (machine, isconfig, name, tag, mask)) 983 return self.dbcurs.lastrowid 984 985 def add_diplocation(self, dipswitch, bit, name, num, inverted): 986 self.dbcurs.execute(UpdateQueries.ADD_DIPLOCATION, (dipswitch, bit, name, num, inverted)) 987 return self.dbcurs.lastrowid 988 989 def add_dipvalue(self, dipswitch, name, value, isdefault): 990 self.dbcurs.execute(UpdateQueries.ADD_DIPVALUE, (dipswitch, name, value, isdefault)) 991 return self.dbcurs.lastrowid 992 993 def add_feature(self, machine, featuretype, status, overall): 994 self.dbcurs.execute(UpdateQueries.ADD_FEATURE, (machine, status, overall, featuretype)) 995 return self.dbcurs.lastrowid 996 997 def add_slot(self, machine, name): 998 self.dbcurs.execute(UpdateQueries.ADD_SLOT, (machine, name)) 999 return self.dbcurs.lastrowid 1000 1001 def add_slotoption(self, slot, device, name): 1002 self.dbcurs.execute(UpdateQueries.ADD_TEMPORARY_SLOTOPTION, (slot, device, name)) 1003 return self.dbcurs.lastrowid 1004 1005 def add_slotdefault(self, slot, slotoption): 1006 self.dbcurs.execute(UpdateQueries.ADD_TEMPORARY_SLOTDEFAULT, (slot, slotoption)) 1007 return self.dbcurs.lastrowid 1008 1009 def add_ramoption(self, machine, name, size): 1010 self.dbcurs.execute(UpdateQueries.ADD_RAMOPTION, (machine, size, name)) 1011 return self.dbcurs.lastrowid 1012 1013 def add_ramdefault(self, machine, size): 1014 self.dbcurs.execute(UpdateQueries.ADD_RAMDEFAULT, (machine, size)) 1015 return self.dbcurs.lastrowid 1016 1017 def add_machinesoftwarelist(self, machine, softwarelist, tag, status): 1018 self.dbcurs.execute(UpdateQueries.ADD_MACHINESOFTWARELISTSTATUSTYPE, (status, )) 1019 self.dbcurs.execute(UpdateQueries.ADD_MACHINESOFTWARELIST, (machine, tag, softwarelist, status)) 1020 return self.dbcurs.lastrowid 1021 1022 def add_rom(self, crc, sha1): 1023 self.dbcurs.execute(UpdateQueries.ADD_ROM, (crc, sha1)) 1024 return self.dbcurs.lastrowid 1025 1026 def add_romdump(self, machine, name, crc, sha1, bad): 1027 self.dbcurs.execute(UpdateQueries.ADD_ROMDUMP, (machine, name, 1 if bad else 0, crc, sha1)) 1028 return self.dbcurs.lastrowid 1029 1030 def add_softwareromdump(self, part, name, crc, sha1, bad): 1031 self.dbcurs.execute(UpdateQueries.ADD_SOFTWAREROMDUMP, (part, name, 1 if bad else 0, crc, sha1)) 1032 return self.dbcurs.lastrowid 1033 1034 def add_disk(self, sha1): 1035 self.dbcurs.execute(UpdateQueries.ADD_DISK, (sha1, )) 1036 return self.dbcurs.lastrowid 1037 1038 def add_diskdump(self, machine, name, sha1, bad): 1039 self.dbcurs.execute(UpdateQueries.ADD_DISKDUMP, (machine, name, 1 if bad else 0, sha1)) 1040 return self.dbcurs.lastrowid 1041 1042 def add_softwarediskdump(self, part, name, sha1, bad): 1043 self.dbcurs.execute(UpdateQueries.ADD_SOFTWAREDISKDUMP, (part, name, 1 if bad else 0, sha1)) 1044 return self.dbcurs.lastrowid 1045 1046 1047class QueryConnection(object): 1048 def __init__(self, database, **kwargs): 1049 super(QueryConnection, self).__init__(**kwargs) 1050 if sys.version_info >= (3, 4): 1051 self.dbconn = sqlite3.connect('file:' + urllib.request.pathname2url(database) + '?mode=ro', uri=True, check_same_thread=False) 1052 else: 1053 self.dbconn = sqlite3.connect(database, check_same_thread=False) 1054 self.dbconn.row_factory = sqlite3.Row 1055 self.dbconn.execute('PRAGMA foreign_keys = ON') 1056 1057 def close(self): 1058 self.dbconn.close() 1059 1060 def cursor(self): 1061 return QueryCursor(self.dbconn) 1062 1063 1064class UpdateConnection(object): 1065 def __init__(self, database, **kwargs): 1066 super(UpdateConnection, self).__init__(**kwargs) 1067 self.dbconn = sqlite3.connect(database) 1068 self.dbconn.execute('PRAGMA page_size = 4096') 1069 self.dbconn.execute('PRAGMA foreign_keys = ON') 1070 1071 def commit(self): 1072 self.dbconn.commit() 1073 1074 def rollback(self): 1075 self.dbconn.rollback() 1076 1077 def close(self): 1078 self.dbconn.close() 1079 1080 def cursor(self): 1081 return UpdateCursor(self.dbconn) 1082 1083 def prepare_for_load(self): 1084 # here be dragons - this is a poor man's DROP ALL TABLES etc. 1085 self.dbconn.execute('PRAGMA foreign_keys = OFF') 1086 for query in self.dbconn.execute('SELECT \'DROP VIEW \' || name FROM sqlite_master WHERE type = \'view\'').fetchall(): 1087 self.dbconn.execute(query[0]) 1088 for query in self.dbconn.execute('SELECT \'DROP INDEX \' || name FROM sqlite_master WHERE type = \'index\' AND NOT name GLOB \'sqlite_autoindex_*\'').fetchall(): 1089 self.dbconn.execute(query[0]) 1090 for query in self.dbconn.execute('SELECT \'DROP TABLE \' || name FROM sqlite_master WHERE type = \'table\'').fetchall(): 1091 self.dbconn.execute(query[0]) 1092 self.dbconn.execute('PRAGMA foreign_keys = ON') 1093 1094 # this is where the sanity starts 1095 for query in SchemaQueries.DROP_INDEXES: 1096 self.dbconn.execute(query) 1097 for query in SchemaQueries.CREATE_TABLES: 1098 self.dbconn.execute(query) 1099 for query in SchemaQueries.CREATE_TEMPORARY_TABLES: 1100 self.dbconn.execute(query) 1101 self.dbconn.commit() 1102 1103 def finalise_load(self): 1104 self.dbconn.execute(UpdateQueries.FINALISE_SOFTWARECLONEOFS) 1105 self.dbconn.commit() 1106 self.dbconn.execute(SchemaQueries.DROP_TEMPORARY_SOFTWARECLONEOF) 1107 self.dbconn.execute(UpdateQueries.FINALISE_DEVICEREFERENCES) 1108 self.dbconn.commit() 1109 self.dbconn.execute(SchemaQueries.DROP_TEMPORARY_DEVICEREFERENCE) 1110 self.dbconn.execute(UpdateQueries.FINALISE_SLOTOPTIONS) 1111 self.dbconn.commit() 1112 self.dbconn.execute(SchemaQueries.DROP_TEMPORARY_SLOTOPTION) 1113 self.dbconn.execute(UpdateQueries.FINALISE_SLOTDEFAULTS) 1114 self.dbconn.commit() 1115 self.dbconn.execute(SchemaQueries.DROP_TEMPORARY_SLOTDEFAULT) 1116 for query in SchemaQueries.CREATE_INDEXES: 1117 self.dbconn.execute(query) 1118 self.dbconn.commit() 1119