src/backend/fireinfo.py

   1 #!/usr/bin/python
   2
   3 import datetime
   4 import iso3166
   5 import logging
   6 import re
   7
   8 from . import database
   9 from . import hwdata
  10 from . import util
  11 from .misc import Object
  12
  13 N_ = lambda x: x
  14
  15 CPU_VENDORS = {
  16         "AMDisbetter!" : "AMD",
  17         "AuthenticAMD" : "AMD",
  18         "CentaurHauls" : "VIA",
  19         "CyrixInstead" : "Cyrix",
  20         "GenuineIntel" : "Intel",
  21         "TransmetaCPU" : "Transmeta",
  22         "GenuineTMx86" : "Transmeta",
  23         "Geode by NSC" : "NSC",
  24         "NexGenDriven" : "NexGen",
  25         "RiseRiseRise" : "Rise",
  26         "SiS SiS SiS"  : "SiS",
  27         "SiS SiS SiS " : "SiS",
  28         "UMC UMC UMC " : "UMC",
  29         "VIA VIA VIA " : "VIA",
  30         "Vortex86 SoC" : "Vortex86",
  31 }
  32
  33 CPU_STRINGS = (
  34         ### AMD ###
  35         # APU
  36         (r"AMD (Sempron)\(tm\) (\d+) APU with Radeon\(tm\) R\d+", r"AMD \1 \2 APU"),
  37         (r"AMD ([\w\-]+) APU with Radeon\(tm\) HD Graphics", r"AMD \1 APU"),
  38         (r"AMD ([\w\-]+) Radeon R\d+, \d+ Compute Cores \d+C\+\d+G", r"AMD \1 APU"),
  39         # Athlon
  40         (r"AMD Athlon.* II X2 ([a-z0-9]+).*", r"AMD Athlon X2 \1"),
  41         (r"AMD Athlon\(tm\) 64 Processor (\w+)", r"AMD Athlon64 \1"),
  42         (r"AMD Athlon\(tm\) 64 X2 Dual Core Processor (\w+)", r"AMD Athlon64 X2 \1"),
  43         (r"(AMD Athlon).*(XP).*", r"\1 \2"),
  44         (r"(AMD Phenom).* ([0-9]+) .*", r"\1 \2"),
  45         (r"(AMD Phenom).*", r"\1"),
  46         (r"(AMD Sempron).*", r"\1"),
  47         # Geode
  48         (r"Geode\(TM\) Integrated Processor by AMD PCS", r"AMD Geode"),
  49         (r"(Geode).*", r"\1"),
  50         # Mobile
  51         (r"Mobile AMD (Athlon|Sempron)\(tm\) Processor (\d+\+?)", r"AMD \1-M \2"),
  52
  53         # Intel
  54         (r"Intel\(R\) (Atom|Celeron).*CPU\s*([A-Z0-9]+) .*", r"Intel \1 \2"),
  55         (r"(Intel).*(Celeron).*", r"\1 \2"),
  56         (r"Intel\(R\)? Core\(TM\)?2 Duo *CPU .* ([A-Z0-9]+) .*", r"Intel C2D \1"),
  57         (r"Intel\(R\)? Core\(TM\)?2 Duo CPU (\w+)", r"Intel C2D \1"),
  58         (r"Intel\(R\)? Core\(TM\)?2 CPU .* ([A-Z0-9]+) .*", r"Intel C2 \1"),
  59         (r"Intel\(R\)? Core\(TM\)?2 Quad *CPU .* ([A-Z0-9]+) .*", r"Intel C2Q \1"),
  60         (r"Intel\(R\)? Core\(TM\)? (i[753]\-\w+) CPU", r"Intel Core \1"),
  61         (r"Intel\(R\)? Xeon\(R\)? CPU (\w+) (0|v\d+)", r"Intel Xeon \1 \2"),
  62         (r"Intel\(R\)? Xeon\(R\)? CPU\s+(\w+)", r"Intel Xeon \1"),
  63         (r"(Intel).*(Xeon).*", r"\1 \2"),
  64         (r"Intel.* Pentium.* (D|4) .*", r"Intel Pentium \1"),
  65         (r"Intel.* Pentium.* Dual .* ([A-Z0-9]+) .*", r"Intel Pentium Dual \1"),
  66         (r"Pentium.* Dual-Core .* ([A-Z0-9]+) .*", r"Intel Pentium Dual \1"),
  67         (r"(Pentium I{2,3}).*", r"Intel \1"),
  68         (r"(Celeron \(Coppermine\))", r"Intel Celeron"),
  69
  70         # NSC
  71         (r"Geode\(TM\) Integrated Processor by National Semi", r"NSC Geode"),
  72
  73         # VIA
  74         (r"(VIA \w*).*", r"\1"),
  75
  76         # Qemu
  77         (r"QEMU Virtual CPU version .*", r"QEMU CPU"),
  78
  79         # ARM
  80         (r"Feroceon .*", r"ARM Feroceon"),
  81 )
  82
  83 IGNORED_DEVICES = ["usb",]
  84
  85 class ProfileDict(object):
  86         def __init__(self, data):
  87                 self._data = data
  88
  89
  90 class ProfileNetwork(ProfileDict):
  91         def __eq__(self, other):
  92                 if other is None:
  93                         return False
  94
  95                 if not self.has_red == other.has_red:
  96                         return False
  97
  98                 if not self.has_green == other.has_green:
  99                         return False
 100
 101                 if not self.has_orange == other.has_orange:
 102                         return False
 103
 104                 if not self.has_blue == other.has_blue:
 105                         return False
 106
 107                 return True
 108
 109         def __iter__(self):
 110                 ret = []
 111
 112                 for zone in ("red", "green", "orange", "blue"):
 113                         if self.has_zone(zone):
 114                                 ret.append(zone)
 115
 116                 return iter(ret)
 117
 118         def has_zone(self, name):
 119                 return self._data.get("has_%s" % name)
 120
 121         @property
 122         def has_red(self):
 123                 return self._data.get("has_red", False)
 124
 125         @property
 126         def has_green(self):
 127                 return self._data.get("has_green", False)
 128
 129         @property
 130         def has_orange(self):
 131                 return self._data.get("has_orange", False)
 132
 133         @property
 134         def has_blue(self):
 135                 return self._data.get("has_blue", False)
 136
 137
 138 class Processor(Object):
 139         def __init__(self, backend, id, data=None, clock_speed=None, bogomips=None):
 140                 Object.__init__(self, backend)
 141
 142                 self.id = id
 143                 self.__data = data
 144                 self.__clock_speed = clock_speed
 145                 self.__bogomips = bogomips
 146
 147         def __str__(self):
 148                 s = []
 149
 150                 if self.model_string and not self.model_string.startswith(self.vendor):
 151                         s.append(self.vendor)
 152                         s.append("-")
 153
 154                 s.append(self.model_string or "Generic")
 155
 156                 if self.core_count > 1:
 157                         s.append("x%s" % self.core_count)
 158
 159                 return " ".join(s)
 160
 161         @property
 162         def data(self):
 163                 if self.__data is None:
 164                         self.__data = self.db.get("SELECT * FROM fireinfo_processors \
 165                                 WHERE id = %s", self.id)
 166
 167                 return self.__data
 168
 169         @property
 170         def vendor(self):
 171                 try:
 172                         return CPU_VENDORS[self.data.vendor]
 173                 except KeyError:
 174                         return self.data.vendor
 175
 176         @property
 177         def family(self):
 178                 return self.data.family
 179
 180         @property
 181         def model(self):
 182                 return self.data.model
 183
 184         @property
 185         def stepping(self):
 186                 return self.data.stepping
 187
 188         @property
 189         def model_string(self):
 190                 if self.data.model_string:
 191                         s = self.data.model_string.split()
 192
 193                         return " ".join((e for e in s if e))
 194
 195         @property
 196         def flags(self):
 197                 return self.data.flags
 198
 199         def has_flag(self, flag):
 200                 return flag in self.flags
 201
 202         def uses_ht(self):
 203                 if self.vendor == "Intel" and self.family == 6 and self.model in (15, 55, 76, 77):
 204                         return False
 205
 206                 return self.has_flag("ht")
 207
 208         @property
 209         def core_count(self):
 210                 return self.data.core_count
 211
 212         @property
 213         def count(self):
 214                 if self.uses_ht():
 215                         return self.core_count // 2
 216
 217                 return self.core_count
 218
 219         @property
 220         def clock_speed(self):
 221                 return self.__clock_speed
 222
 223         def format_clock_speed(self):
 224                 if not self.clock_speed:
 225                         return
 226
 227                 if self.clock_speed < 1000:
 228                         return "%dMHz" % self.clock_speed
 229
 230                 return "%.2fGHz" % round(self.clock_speed / 1000, 2)
 231
 232         @property
 233         def bogomips(self):
 234                 return self.__bogomips
 235
 236         @property
 237         def capabilities(self):
 238                 caps = [
 239                         ("64bit",  self.has_flag("lm")),
 240                         ("aes",    self.has_flag("aes")),
 241                         ("nx",     self.has_flag("nx")),
 242                         ("pae",    self.has_flag("pae") or self.has_flag("lpae")),
 243                         ("rdrand", self.has_flag("rdrand")),
 244                 ]
 245
 246                 # If the system is already running in a virtual environment,
 247                 # we cannot correctly detect if the CPU supports svm or vmx
 248                 if self.has_flag("hypervisor"):
 249                         caps.append(("virt", None))
 250                 else:
 251                         caps.append(("virt", self.has_flag("vmx") or self.has_flag("svm")))
 252
 253                 return caps
 254
 255         def format_model(self):
 256                 s = self.model_string or ""
 257
 258                 # Remove everything after the @: Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz
 259                 s, sep, rest = s.partition("@")
 260
 261                 for pattern, repl in CPU_STRINGS:
 262                         if re.match(pattern, s) is None:
 263                                 continue
 264
 265                         s = re.sub(pattern, repl, s)
 266                         break
 267
 268                 # Otherwise remove the symbols
 269                 for i in ("C", "R", "TM", "tm"):
 270                         s = s.replace("(%s)" % i, "")
 271
 272                 # Replace too long strings with shorter ones
 273                 pairs = (
 274                         ("Quad-Core Processor", ""),
 275                         ("Dual-Core Processor", ""),
 276                         ("Processor", "CPU"),
 277                         ("processor", "CPU"),
 278                 )
 279                 for k, v in pairs:
 280                         s = s.replace(k, v)
 281
 282                 # Remove too many spaces
 283                 s = " ".join((e for e in s.split() if e))
 284
 285                 return s
 286
 287         @property
 288         def friendly_string(self):
 289                 s = []
 290
 291                 model = self.format_model()
 292                 if model:
 293                         s.append(model)
 294
 295                 clock_speed = self.format_clock_speed()
 296                 if clock_speed:
 297                         s.append("@ %s" % clock_speed)
 298
 299                 if self.count > 1:
 300                         s.append("x%s" % self.count)
 301
 302                 return " ".join(s)
 303
 304
 305 class Device(Object):
 306         classid2name = {
 307                 "pci" : {
 308                         "00" : N_("Unclassified"),
 309                         "01" : N_("Mass storage"),
 310                         "02" : N_("Network"),
 311                         "03" : N_("Display"),
 312                         "04" : N_("Multimedia"),
 313                         "05" : N_("Memory controller"),
 314                         "06" : N_("Bridge"),
 315                         "07" : N_("Communication"),
 316                         "08" : N_("Generic system peripheral"),
 317                         "09" : N_("Input device"),
 318                         "0a" : N_("Docking station"),
 319                         "0b" : N_("Processor"),
 320                         "0c" : N_("Serial bus"),
 321                         "0d" : N_("Wireless"),
 322                         "0e" : N_("Intelligent controller"),
 323                         "0f" : N_("Satellite communications controller"),
 324                         "10" : N_("Encryption"),
 325                         "11" : N_("Signal processing controller"),
 326                         "ff" : N_("Unassigned class"),
 327                 },
 328
 329                 "usb" : {
 330                         "00" : N_("Unclassified"),
 331                         "01" : N_("Multimedia"),
 332                         "02" : N_("Communication"),
 333                         "03" : N_("Input device"),
 334                         "05" : N_("Generic system peripheral"),
 335                         "06" : N_("Image"),
 336                         "07" : N_("Printer"),
 337                         "08" : N_("Mass storage"),
 338                         "09" : N_("Hub"),
 339                         "0a" : N_("Communication"),
 340                         "0b" : N_("Smart card"),
 341                         "0d" : N_("Encryption"),
 342                         "0e" : N_("Display"),
 343                         "0f" : N_("Personal Healthcare"),
 344                         "dc" : N_("Diagnostic Device"),
 345                         "e0" : N_("Wireless"),
 346                         "ef" : N_("Unclassified"),
 347                         "fe" : N_("Unclassified"),
 348                         "ff" : N_("Unclassified"),
 349                 }
 350         }
 351
 352         def __init__(self, backend, id, data=None):
 353                 Object.__init__(self, backend)
 354
 355                 self.id = id
 356                 self.__data = data
 357
 358         def __repr__(self):
 359                 return "<%s vendor=%s model=%s>" % (self.__class__.__name__,
 360                         self.vendor_string, self.model_string)
 361
 362         def __eq__(self, other):
 363                 if isinstance(other, self.__class__):
 364                         return self.id == other.id
 365
 366         def __lt__(self, other):
 367                 if isinstance(other, self.__class__):
 368                         return self.cls < other.cls or \
 369                                 self.vendor_string < other.vendor_string or \
 370                                 self.vendor < other.vendor or \
 371                                 self.model_string < other.model_string or \
 372                                 self.model < other.model
 373
 374         @property
 375         def data(self):
 376                 if self.__data is None:
 377                         assert self.id
 378
 379                         self.__data = self.db.get("SELECT * FROM fireinfo_devices \
 380                                 WHERE id = %s", self.id)
 381
 382                 return self.__data
 383
 384         def is_showable(self):
 385                 if self.driver in IGNORED_DEVICES:
 386                         return False
 387
 388                 if self.driver in ("pcieport", "hub"):
 389                         return False
 390
 391                 return True
 392
 393         @property
 394         def subsystem(self):
 395                 return self.data.subsystem
 396
 397         @property
 398         def model(self):
 399                 return self.data.model
 400
 401         @property
 402         def model_string(self):
 403                 return self.fireinfo.get_model_string(self.subsystem,
 404                                 self.vendor, self.model)
 405
 406         @property
 407         def vendor(self):
 408                 return self.data.vendor
 409
 410         @property
 411         def vendor_string(self):
 412                 return self.fireinfo.get_vendor_string(self.subsystem, self.vendor)
 413
 414         @property
 415         def driver(self):
 416                 return self.data.driver
 417
 418         @property
 419         def cls(self):
 420                 classid = self.data.deviceclass
 421
 422                 if self.subsystem == "pci":
 423                         classid = classid[:-4]
 424                         if len(classid) == 1:
 425                                 classid = "0%s" % classid
 426
 427                 elif self.subsystem == "usb" and classid:
 428                         classid = classid.split("/")[0]
 429                         classid = "%02x" % int(classid)
 430
 431                 try:
 432                         return self.classid2name[self.subsystem][classid]
 433                 except KeyError:
 434                         return "N/A"
 435
 436         @property
 437         def percentage(self):
 438                 return self.data.get("percentage", None)
 439
 440
 441 class Profile(Object):
 442         def __init__(self, backend, id, data=None):
 443                 Object.__init__(self, backend)
 444
 445                 self.id = id
 446                 self.__data = data
 447
 448         def __repr__(self):
 449                 return "<%s %s>" % (self.__class__.__name__, self.public_id)
 450
 451         def __cmp__(self, other):
 452                 return cmp(self.id, other.id)
 453
 454         def is_showable(self):
 455                 if self.arch_id:
 456                         return True
 457
 458                 return False
 459
 460         @property
 461         def data(self):
 462                 if self.__data is None:
 463                         self.__data = self.db.get("SELECT * FROM fireinfo_profiles \
 464                                 WHERE id = %s", self.id)
 465
 466                 return self.__data
 467
 468         @property
 469         def public_id(self):
 470                 return self.data.public_id
 471
 472         @property
 473         def private_id(self):
 474                 raise NotImplementedError
 475
 476         @property
 477         def time_created(self):
 478                 return self.data.time_created
 479
 480         @property
 481         def time_updated(self):
 482                 return self.data.time_updated
 483
 484         def updated(self, profile_parser=None, country_code=None, when=None):
 485                 valid = self.settings.get_int("fireinfo_profile_days_valid", 14)
 486
 487                 self.db.execute("UPDATE fireinfo_profiles \
 488                         SET \
 489                                 time_updated = then_or_now(%s), \
 490                                 time_valid = then_or_now(%s) + INTERVAL '%s days', \
 491                                 updates = updates + 1 \
 492                         WHERE id = %s", when, when, valid, self.id)
 493
 494                 if profile_parser:
 495                         self.set_processor_speeds(
 496                                 profile_parser.processor_clock_speed,
 497                                 profile_parser.processor_bogomips,
 498                         )
 499
 500                 if country_code:
 501                         self.set_country_code(country_code)
 502
 503                 self.log_profile_update()
 504
 505         def log_profile_update(self):
 506                 # Log that an update was performed for this profile id
 507                 self.db.execute("INSERT INTO fireinfo_profiles_log(public_id) \
 508                         VALUES(%s)", self.public_id)
 509
 510         def expired(self, when=None):
 511                 self.db.execute("UPDATE fireinfo_profiles \
 512                         SET time_valid = then_or_now(%s) WHERE id = %s", when, self.id)
 513
 514         def parse(self, parser):
 515                 # Processor
 516                 self.processor = parser.processor
 517                 self.set_processor_speeds(parser.processor_clock_speed, parser.processor_bogomips)
 518
 519                 # All devices
 520                 self.devices = parser.devices
 521
 522                 # System
 523                 self.system_id = parser.system_id
 524
 525                 # Memory
 526                 self.memory = parser.memory
 527
 528                 # Storage
 529                 self.storage = parser.storage
 530
 531                 # Kernel
 532                 self.kernel_id = parser.kernel_id
 533
 534                 # Arch
 535                 self.arch_id = parser.arch_id
 536
 537                 # Release
 538                 self.release_id = parser.release_id
 539
 540                 # Language
 541                 self.language = parser.language
 542
 543                 # Virtual
 544                 if parser.virtual:
 545                         self.hypervisor_id = parser.hypervisor_id
 546
 547                 # Network
 548                 self.network = parser.network
 549
 550         # Location
 551
 552         @property
 553         def location(self):
 554                 if not hasattr(self, "_location"):
 555                         res = self.db.get("SELECT location FROM fireinfo_profiles_locations \
 556                                 WHERE profile_id = %s", self.id)
 557
 558                         if res:
 559                                 self._location = res.location
 560                         else:
 561                                 self._location = None
 562
 563                 return self._location
 564
 565         def set_country_code(self, country_code):
 566                 if self.location == country_code:
 567                         return
 568
 569                 self.db.execute("DELETE FROM fireinfo_profiles_locations \
 570                         WHERE profile_id = %s", self.id)
 571                 self.db.execute("INSERT INTO fireinfo_profiles_locations(profile_id, location) \
 572                         VALUES(%s, %s)", self.id, country_code)
 573
 574                 self._location = country_code
 575
 576         @property
 577         def location_string(self):
 578                 return self.backend.get_country_name(self.location) or self.location
 579
 580         # Devices
 581
 582         @property
 583         def device_ids(self):
 584                 if not hasattr(self, "_device_ids"):
 585                         res = self.db.query("SELECT device_id FROM fireinfo_profiles_devices \
 586                                 WHERE profile_id = %s", self.id)
 587
 588                         self._device_ids = sorted([r.device_id for r in res])
 589
 590                 return self._device_ids
 591
 592         def get_devices(self):
 593                 if not hasattr(self, "_devices"):
 594                         res = self.db.query("SELECT * FROM fireinfo_devices \
 595                                 LEFT JOIN fireinfo_profiles_devices ON \
 596                                         fireinfo_devices.id = fireinfo_profiles_devices.device_id \
 597                                 WHERE fireinfo_profiles_devices.profile_id = %s", self.id)
 598
 599                         self._devices = []
 600                         for row in res:
 601                                 device = Device(self.backend, row.id, row)
 602                                 self._devices.append(device)
 603
 604                 return self._devices
 605
 606         def set_devices(self, devices):
 607                 device_ids = [d.id for d in devices]
 608
 609                 self.db.execute("DELETE FROM fireinfo_profiles_devices WHERE profile_id = %s", self.id)
 610                 self.db.executemany("INSERT INTO fireinfo_profiles_devices(profile_id, device_id) \
 611                         VALUES(%s, %s)", ((self.id, d) for d in device_ids))
 612
 613                 self._devices = devices
 614                 self._device_ids = device_ids
 615
 616         devices = property(get_devices, set_devices)
 617
 618         def count_device(self, subsystem, vendor, model):
 619                 counter = 0
 620
 621                 for dev in self.devices:
 622                         if dev.subsystem == subsystem and dev.vendor == vendor and dev.model == model:
 623                                 counter += 1
 624
 625                 return counter
 626
 627         # System
 628
 629         def get_system_id(self):
 630                 if not hasattr(self, "_system_id"):
 631                         res = self.db.get("SELECT system_id AS id FROM fireinfo_profiles_systems \
 632                                 WHERE profile_id = %s", self.id)
 633
 634                         if res:
 635                                 self._system_id = res.id
 636                         else:
 637                                 self._system_id = None
 638
 639                 return self._system_id
 640
 641         def set_system_id(self, system_id):
 642                 self.db.execute("DELETE FROM fireinfo_profiles_systems WHERE profile_id = %s", self.id)
 643
 644                 if system_id:
 645                         self.db.execute("INSERT INTO fireinfo_profiles_systems(profile_id, system_id) \
 646                                 VALUES(%s, %s)", self.id, system_id)
 647
 648                 self._system_id = None
 649                 if hasattr(self, "_system"):
 650                         del self._system
 651
 652         system_id = property(get_system_id, set_system_id)
 653
 654         @property
 655         def system(self):
 656                 if not hasattr(self, "_system"):
 657                         res = self.db.get("SELECT fireinfo_systems.vendor AS vendor, fireinfo_systems.model AS model \
 658                                 FROM fireinfo_profiles_systems \
 659                                 LEFT JOIN fireinfo_systems ON fireinfo_profiles_systems.system_id = fireinfo_systems.id \
 660                                 WHERE fireinfo_profiles_systems.profile_id = %s", self.id)
 661
 662                         if res:
 663                                 self._system = (res.vendor, res.model)
 664                         else:
 665                                 self._system = (None, None)
 666
 667                 return self._system
 668
 669         @property
 670         def system_vendor(self):
 671                 try:
 672                         v, m = self.system
 673                         return v
 674                 except TypeError:
 675                         pass
 676
 677         @property
 678         def system_model(self):
 679                 try:
 680                         v, m = self.system
 681                         return m
 682                 except TypeError:
 683                         pass
 684
 685         @property
 686         def appliance_id(self):
 687                 if not hasattr(self, "_appliance_id"):
 688                         appliances = (
 689                                 ("fountainnetworks-duo-box", self._appliance_test_fountainnetworks_duo_box),
 690                                 ("fountainnetworks-prime", self._appliance_test_fountainnetworks_prime),
 691                                 ("lightningwirelabs-eco-plus", self._appliance_test_lightningwirelabs_eco_plus),
 692                                 ("lightningwirelabs-eco", self._appliance_test_lightningwirelabs_eco),
 693                         )
 694
 695                         self._appliance_id = None
 696                         for name, test_func in appliances:
 697                                 if not test_func():
 698                                         continue
 699
 700                                 self._appliance_id = name
 701                                 break
 702
 703                 return self._appliance_id
 704
 705         @property
 706         def appliance(self):
 707                 if self.appliance_id == "fountainnetworks-duo-box":
 708                         return "Fountain Networks - IPFire Duo Box"
 709
 710                 elif self.appliance_id == "fountainnetworks-prime":
 711                         return "Fountain Networks - IPFire Prime Box"
 712
 713                 elif self.appliance_id == "lightningwirelabs-eco-plus":
 714                         return "Lightning Wire Labs - IPFire Eco Plus Appliance"
 715
 716                 elif self.appliance_id == "lightningwirelabs-eco":
 717                         return "Lightning Wire Labs - IPFire Eco Appliance"
 718
 719         def _appliance_test_fountainnetworks_duo_box(self):
 720                 if not self.processor.vendor == "Intel":
 721                         return False
 722
 723                 if not self.processor.model_string == "Intel(R) Celeron(R) 2957U @ 1.40GHz":
 724                         return False
 725
 726                 if not self.count_device("pci", "10ec", "8168") == 2:
 727                         return False
 728
 729                 # WiFi module
 730                 #if self.count_device("usb", "148f", "5572") < 1:
 731                 #       return False
 732
 733                 return True
 734
 735         def _appliance_test_fountainnetworks_prime(self):
 736                 if not self.system in (("SECO", None), ("SECO", "0949")):
 737                         return False
 738
 739                 # Must have a wireless device
 740                 if self.count_device("usb", "148f", "5572") < 1:
 741                         return False
 742
 743                 return True
 744
 745         def _appliance_test_lightningwirelabs_eco(self):
 746                 if not self.system == ("MSI", "MS-9877"):
 747                         return False
 748
 749                 # Must have four Intel network adapters
 750                 network_adapters_count = self.count_device("pci", "8086", "10d3")
 751                 if not network_adapters_count == 4:
 752                         return False
 753
 754                 return True
 755
 756         def _appliance_test_lightningwirelabs_eco_plus(self):
 757                 if not self.system_vendor == "ASUS":
 758                         return False
 759
 760                 if not self.system_model.startswith("P9A-I/2550"):
 761                         return False
 762
 763                 # Must have four Intel network adapters
 764                 network_adapters_count = self.count_device("pci", "8086", "1f41")
 765                 if not network_adapters_count == 4:
 766                         return False
 767
 768                 return True
 769
 770         # Processors
 771
 772         @property
 773         def processor_id(self):
 774                 if hasattr(self, "_processor"):
 775                         return self._processor.id
 776
 777                 if not hasattr(self, "_processor_id"):
 778                         res = self.db.get("SELECT processor_id FROM fireinfo_profiles_processors \
 779                                 WHERE profile_id = %s", self.id)
 780
 781                         if res:
 782                                 self._processor_id = res.processor_id
 783                         else:
 784                                 self._processor_id = None
 785
 786                 return self._processor_id
 787
 788         def get_processor(self):
 789                 if not self.processor_id:
 790                         return
 791
 792                 if not hasattr(self, "_processor"):
 793                         res = self.db.get("SELECT * FROM fireinfo_profiles_processors \
 794                                 WHERE profile_id = %s", self.id)
 795
 796                         if res:
 797                                 self._processor = self.fireinfo.get_processor_by_id(res.processor_id,
 798                                         clock_speed=res.clock_speed, bogomips=res.bogomips)
 799                         else:
 800                                 self._processor = None
 801
 802                 return self._processor
 803
 804         def set_processor(self, processor):
 805                 self.db.execute("DELETE FROM fireinfo_profiles_processors \
 806                         WHERE profile_id = %s", self.id)
 807
 808                 if processor:
 809                         self.db.execute("INSERT INTO fireinfo_profiles_processors(profile_id, processor_id) \
 810                                 VALUES(%s, %s)", self.id, processor.id)
 811
 812                 self._processor = processor
 813
 814         processor = property(get_processor, set_processor)
 815
 816         def set_processor_speeds(self, clock_speed, bogomips):
 817                 self.db.execute("UPDATE fireinfo_profiles_processors \
 818                         SET clock_speed = %s, bogomips = %s WHERE profile_id = %s",
 819                         clock_speed, bogomips, self.id)
 820
 821         # Compat
 822         @property
 823         def cpu(self):
 824                 return self.processor
 825
 826         # Memory
 827
 828         def get_memory(self):
 829                 if not hasattr(self, "_memory"):
 830                         res = self.db.get("SELECT amount FROM fireinfo_profiles_memory \
 831                                 WHERE profile_id = %s", self.id)
 832
 833                         if res:
 834                                 self._memory = res.amount * 1024
 835                         else:
 836                                 self._memory = None
 837
 838                 return self._memory
 839
 840         def set_memory(self, amount):
 841                 if self.memory == amount:
 842                         return
 843
 844                 amount /= 1024
 845
 846                 self.db.execute("DELETE FROM fireinfo_profiles_memory WHERE profile_id = %s", self.id)
 847                 if amount:
 848                         self.db.execute("INSERT INTO fireinfo_profiles_memory(profile_id, amount) \
 849                                 VALUES(%s, %s)", self.id, amount)
 850
 851                 self._memory = amount * 1024
 852
 853         memory = property(get_memory, set_memory)
 854
 855         @property
 856         def friendly_memory(self):
 857                 return util.format_size(self.memory or 0)
 858
 859         # Storage
 860
 861         def get_storage(self):
 862                 if not hasattr(self, "_storage"):
 863                         res = self.db.get("SELECT amount FROM fireinfo_profiles_storage \
 864                                 WHERE profile_id = %s", self.id)
 865
 866                         if res:
 867                                 self._storage = res.amount * 1024
 868                         else:
 869                                 self._storage = None
 870
 871                 return self._storage
 872
 873         def set_storage(self, amount):
 874                 if self.storage == amount:
 875                         return
 876
 877                 amount /= 1024
 878
 879                 self.db.execute("DELETE FROM fireinfo_profiles_storage WHERE profile_id = %s", self.id)
 880                 if amount:
 881                         self.db.execute("INSERT INTO fireinfo_profiles_storage(profile_id, amount) \
 882                                 VALUES(%s, %s)", self.id, amount)
 883
 884                 self._storage = amount * 1024
 885
 886         storage = property(get_storage, set_storage)
 887
 888         @property
 889         def friendly_storage(self):
 890                 return util.format_size(self.storage)
 891
 892         # Kernel
 893
 894         def get_kernel_id(self):
 895                 if not hasattr(self, "_kernel_id"):
 896                         res = self.db.get("SELECT fireinfo_profiles_kernels.kernel_id AS id FROM fireinfo_profiles \
 897                                 LEFT JOIN fireinfo_profiles_kernels ON fireinfo_profiles.id = fireinfo_profiles_kernels.profile_id \
 898                                 WHERE fireinfo_profiles.id = %s", self.id)
 899
 900                         if res:
 901                                 self._kernel_id = res.id
 902                         else:
 903                                 self._kernel_id = None
 904
 905                 return self._kernel_id
 906
 907         def set_kernel_id(self, kernel_id):
 908                 if self.kernel_id == kernel_id:
 909                         return
 910
 911                 self.db.execute("DELETE FROM fireinfo_profiles_kernels WHERE profile_id = %s", self.id)
 912                 if kernel_id:
 913                         self.db.execute("INSERT INTO fireinfo_profiles_kernels(profile_id, kernel_id) \
 914                                 VALUES(%s, %s)", self.id, kernel_id)
 915
 916                 self._kernel_id = kernel_id
 917                 if hasattr(self, "_kernel"):
 918                         del self._kernel
 919
 920         kernel_id = property(get_kernel_id, set_kernel_id)
 921
 922         @property
 923         def kernel(self):
 924                 if not hasattr(self, "_kernel"):
 925                         res = self.db.get("SELECT fireinfo_kernels.name AS name FROM fireinfo_profiles \
 926                                 LEFT JOIN fireinfo_profiles_kernels ON fireinfo_profiles.id = fireinfo_profiles_kernels.profile_id \
 927                                 LEFT JOIN fireinfo_kernels ON fireinfo_kernels.id = fireinfo_profiles_kernels.kernel_id \
 928                                 WHERE fireinfo_profiles.id = %s", self.id)
 929
 930                         if res:
 931                                 self._kernel = res.name
 932                         else:
 933                                 self._kernel = None
 934
 935                 return self._kernel
 936
 937         # Arch
 938
 939         def get_arch_id(self):
 940                 if not hasattr(self, "_arch_id"):
 941                         res = self.db.get("SELECT fireinfo_profiles_arches.arch_id AS id FROM fireinfo_profiles \
 942                                 LEFT JOIN fireinfo_profiles_arches ON fireinfo_profiles.id = fireinfo_profiles_arches.profile_id \
 943                                 WHERE fireinfo_profiles.id = %s", self.id)
 944
 945                         if res:
 946                                 self._arch_id = res.id
 947                         else:
 948                                 self._arch_id = None
 949
 950                 return self._arch_id
 951
 952         def set_arch_id(self, arch_id):
 953                 if self.arch_id == arch_id:
 954                         return
 955
 956                 self.db.execute("DELETE FROM fireinfo_profiles_arches WHERE profile_id = %s", self.id)
 957                 if arch_id:
 958                         self.db.execute("INSERT INTO fireinfo_profiles_arches(profile_id, arch_id) \
 959                                 VALUES(%s, %s)", self.id, arch_id)
 960
 961                 self._arch_id = None
 962                 if hasattr(self, "_arch"):
 963                         del self._arch
 964
 965         arch_id = property(get_arch_id, set_arch_id)
 966
 967         @property
 968         def arch(self):
 969                 if not hasattr(self, "_arch"):
 970                         res = self.db.get("SELECT fireinfo_arches.name AS name FROM fireinfo_profiles \
 971                                 LEFT JOIN fireinfo_profiles_arches ON fireinfo_profiles.id = fireinfo_profiles_arches.profile_id \
 972                                 LEFT JOIN fireinfo_arches ON fireinfo_arches.id = fireinfo_profiles_arches.arch_id \
 973                                 WHERE fireinfo_profiles.id = %s", self.id)
 974
 975                         if res:
 976                                 self._arch = res.name
 977                         else:
 978                                 self._arch = None
 979
 980                 return self._arch
 981
 982         # Release
 983
 984         def get_release_id(self):
 985                 if not hasattr(self, "_release_id"):
 986                         res = self.db.get("SELECT fireinfo_profiles_releases.release_id AS id FROM fireinfo_profiles \
 987                                 LEFT JOIN fireinfo_profiles_releases ON fireinfo_profiles.id = fireinfo_profiles_releases.profile_id \
 988                                 WHERE fireinfo_profiles.id = %s", self.id)
 989
 990                         if res:
 991                                 self._release_id = res.id
 992                         else:
 993                                 self._release_id = None
 994
 995                 return self._release_id
 996
 997         def set_release_id(self, release_id):
 998                 if self.release_id == release_id:
 999                         return
1000
1001                 self.db.execute("DELETE FROM fireinfo_profiles_releases WHERE profile_id = %s", self.id)
1002                 if release_id:
1003                         self.db.execute("INSERT INTO fireinfo_profiles_releases(profile_id, release_id) \
1004                                 VALUES(%s, %s)", self.id, release_id)
1005
1006                 self._release_id = release_id
1007                 if hasattr(self, "_release"):
1008                         del self._release
1009
1010         release_id = property(get_release_id, set_release_id)
1011
1012         @property
1013         def release(self):
1014                 if not hasattr(self, "_release"):
1015                         res = self.db.get("SELECT fireinfo_releases.name AS name FROM fireinfo_profiles \
1016                                 LEFT JOIN fireinfo_profiles_releases ON fireinfo_profiles.id = fireinfo_profiles_releases.profile_id \
1017                                 LEFT JOIN fireinfo_releases ON fireinfo_profiles_releases.release_id = fireinfo_releases.id \
1018                                 WHERE fireinfo_profiles.id = %s", self.id)
1019
1020                         if res:
1021                                 self._release = self._format_release(res.name)
1022                         else:
1023                                 self._release = None
1024
1025                 return self._release
1026
1027         @staticmethod
1028         def _format_release(r):
1029                 if not r:
1030                         return r
1031
1032                 # Remove the development header
1033                 r = r.replace("Development Build: ", "")
1034
1035                 pairs = (
1036                         ("-beta", " - Beta "),
1037                         ("-rc", " - Release Candidate "),
1038                         ("core", "Core Update "),
1039                         ("beta", "Beta "),
1040                 )
1041
1042                 for k, v in pairs:
1043                         r = r.replace(k, v)
1044
1045                 return r
1046
1047         @property
1048         def release_short(self):
1049                 pairs = (
1050                         (r"Release Candidate (\d+)", r"RC\1"),
1051                 )
1052
1053                 s = self.release
1054                 for pattern, repl in pairs:
1055                         if re.search(pattern, s) is None:
1056                                 continue
1057
1058                         s = re.sub(pattern, repl, s)
1059
1060                 return s
1061
1062         # Virtual
1063
1064         @property
1065         def virtual(self):
1066                 if not hasattr(self, "_virtual"):
1067                         res = self.db.get("SELECT 1 FROM fireinfo_profiles_virtual \
1068                                 WHERE profile_id = %s", self.id)
1069
1070                         if res:
1071                                 self._virtual = True
1072                         else:
1073                                 self._virtual = False
1074
1075                 return self._virtual
1076
1077         def get_hypervisor_id(self):
1078                 if not hasattr(self, "_hypervisor_id"):
1079                         res = self.db.get("SELECT fireinfo_profiles_virtual.hypervisor_id AS id FROM fireinfo_profiles \
1080                                 LEFT JOIN fireinfo_profiles_virtual ON fireinfo_profiles.id = fireinfo_profiles_virtual.profile_id \
1081                                 WHERE fireinfo_profiles.id = %s", self.id)
1082
1083                         if res:
1084                                 self._hypervisor_id = res.id
1085                         else:
1086                                 self._hypervisor_id = None
1087
1088                 return self._hypervisor_id
1089
1090         def set_hypervisor_id(self, hypervisor_id):
1091                 self.db.execute("DELETE FROM fireinfo_profiles_virtual WHERE profile_id = %s", self.id)
1092                 self.db.execute("INSERT INTO fireinfo_profiles_virtual(profile_id, hypervisor_id) \
1093                         VALUES(%s, %s)", self.id, hypervisor_id)
1094
1095                 self._hypervisor_id = hypervisor_id
1096
1097         hypervisor_id = property(get_hypervisor_id, set_hypervisor_id)
1098
1099         @property
1100         def hypervisor(self):
1101                 if not hasattr(self, "_hypervisor"):
1102                         res = self.db.get("SELECT fireinfo_hypervisors.name AS hypervisor FROM fireinfo_profiles \
1103                                 LEFT JOIN fireinfo_profiles_virtual ON fireinfo_profiles.id = fireinfo_profiles_virtual.profile_id \
1104                                 LEFT JOIN fireinfo_hypervisors ON fireinfo_profiles_virtual.hypervisor_id = fireinfo_hypervisors.id \
1105                                 WHERE fireinfo_profiles.id = %s", self.id)
1106
1107                         if res:
1108                                 self._hypervisor = res.hypervisor
1109                         else:
1110                                 self._hypervisor = None
1111
1112                 return self._hypervisor
1113
1114         # Language
1115
1116         def get_language(self):
1117                 if not hasattr(self, "_language"):
1118                         res = self.db.get("SELECT language FROM fireinfo_profiles_languages \
1119                                 WHERE profile_id = %s", self.id)
1120
1121                         if res:
1122                                 self._language = res.language
1123                         else:
1124                                 self._language = None
1125
1126                 return self._language
1127
1128         def set_language(self, language):
1129                 self.db.execute("DELETE FROM fireinfo_profiles_languages WHERE profile_id = %s", self.id)
1130
1131                 if language:
1132                         self.db.execute("INSERT INTO fireinfo_profiles_languages(profile_id, language) \
1133                                 VALUES(%s, %s)", self.id, language)
1134
1135                 self._language = language
1136
1137         language = property(get_language, set_language)
1138
1139         # Network
1140
1141         def get_network(self):
1142                 if not hasattr(self, "_network"):
1143                         res = self.db.get("SELECT * FROM fireinfo_profiles_networks \
1144                                 WHERE profile_id = %s", self.id)
1145
1146                         if not res:
1147                                 res = {}
1148
1149                         self._network = ProfileNetwork(res)
1150
1151                 return self._network
1152
1153         def set_network(self, network):
1154                 self.db.execute("DELETE FROM fireinfo_profiles_networks WHERE profile_id = %s", self.id)
1155
1156                 if network:
1157                         self.db.execute("INSERT INTO fireinfo_profiles_networks(profile_id, \
1158                                 has_red, has_green, has_orange, has_blue) VALUES(%s, %s, %s, %s, %s)",
1159                                 self.id, network.has_red, network.has_green, network.has_orange, network.has_blue)
1160
1161                 self._network = network
1162
1163         network = property(get_network, set_network)
1164
1165
1166 class ProfileData(Object):
1167         def __init__(self, backend, id, data=None, profile=None):
1168                 Object.__init__(self, backend)
1169
1170                 self.id = id
1171                 self._data = data
1172                 self._profile = profile
1173
1174         @property
1175         def data(self):
1176                 if self._data is None:
1177                         self._data = self.db.get("SELECT * FROM fireinfo_profile_data \
1178                                 WHERE id = %s", self.id)
1179
1180                 return self._data
1181
1182         @property
1183         def profile(self):
1184                 if not self._profile:
1185                         self._profile = self.fireinfo.get_profile_by_id(self.profile_id)
1186
1187                 return self._profile
1188
1189         @property
1190         def profile_id(self):
1191                 return self.data.profile_id
1192
1193
1194 class ProfileParserError(Exception):
1195         pass
1196
1197
1198 class ProfileParser(Object):
1199         __device_args = (
1200                 "subsystem",
1201                 "vendor",
1202                 "model",
1203                 "sub_vendor",
1204                 "sub_model",
1205                 "driver",
1206                 "deviceclass",
1207         )
1208
1209         __processor_args = (
1210                 "vendor",
1211                 "model_string",
1212                 "family",
1213                 "model",
1214                 "stepping",
1215                 "core_count",
1216                 "flags",
1217         )
1218
1219         def __init__(self, backend, public_id, blob=None):
1220                 Object.__init__(self, backend)
1221
1222                 self.public_id = public_id
1223                 self.private_id = None
1224                 self.devices = []
1225                 self.processor = None
1226                 self.processor_clock_speed = None
1227                 self.processor_bogomips = None
1228                 self.system_id = None
1229                 self.memory = None
1230                 self.storage = None
1231                 self.kernel = None
1232                 self.kernel_id = None
1233                 self.arch = None
1234                 self.arch_id = None
1235                 self.release = None
1236                 self.release_id = None
1237                 self.language = None
1238                 self.virtual = None
1239                 self.hypervisor_id = None
1240                 self.network = None
1241
1242                 self.__parse_blob(blob)
1243
1244         def equals(self, other):
1245                 if not self.processor_id == other.processor_id:
1246                         return False
1247
1248                 if not self.device_ids == other.device_ids:
1249                         return False
1250
1251                 if not self.system_id == other.system_id:
1252                         return False
1253
1254                 if not self.memory == other.memory:
1255                         return False
1256
1257                 if not self.storage == other.storage:
1258                         return False
1259
1260                 if not self.kernel_id == other.kernel_id:
1261                         return False
1262
1263                 if not self.arch_id == other.arch_id:
1264                         return False
1265
1266                 if not self.release_id == other.release_id:
1267                         return False
1268
1269                 if not self.language == other.language:
1270                         return False
1271
1272                 if not self.virtual == other.virtual:
1273                         return False
1274
1275                 if other.virtual:
1276                         if not self.hypervisor_id == other.hypervisor_id:
1277                                 return False
1278
1279                 if not self.network == other.network:
1280                         return False
1281
1282                 return True
1283
1284         def __parse_blob(self, blob):
1285                 _profile = blob.get("profile", {})
1286                 self.private_id = blob.get("private_id")
1287
1288                 # Do not try to parse an empty profile
1289                 if not _profile:
1290                         return
1291
1292                 # Processor
1293                 _processor = _profile.get("cpu", {})
1294                 self.__parse_processor(_processor)
1295
1296                 # Find devices
1297                 _devices = _profile.get("devices", [])
1298                 self.__parse_devices(_devices)
1299
1300                 # System
1301                 _system = _profile.get("system")
1302                 if _system:
1303                         self.__parse_system(_system)
1304
1305                         # Memory (convert to bytes)
1306                         memory = _system.get("memory", None)
1307                         if memory:
1308                                 self.memory = memory * 1024
1309
1310                         # Storage size (convert to bytes)
1311                         storage = _system.get("root_size", None)
1312                         if storage:
1313                                 self.storage = storage * 1024
1314
1315                         # Kernel
1316                         kernel = _system.get("kernel_release", None)
1317                         if kernel:
1318                                 self.__parse_kernel(kernel)
1319
1320                         # Release
1321                         release = _system.get("release", None)
1322                         if release:
1323                                 self.__parse_release(release)
1324
1325                         # Language
1326                         language = _system.get("language", None)
1327                         if language:
1328                                 self.__parse_language(language)
1329
1330                         # Virtual
1331                         self.virtual = _system.get("virtual", False)
1332                         if self.virtual:
1333                                 hypervisor = _profile.get("hypervisor")
1334                                 self.__parse_hypervisor(hypervisor)
1335
1336                 # Network
1337                 _network = _profile.get("network")
1338                 if _network:
1339                         self.__parse_network(_network)
1340
1341         @property
1342         def device_ids(self):
1343                 return sorted([d.id for d in self.devices])
1344
1345         def __parse_devices(self, _devices):
1346                 self.devices = []
1347
1348                 for _device in _devices:
1349                         args = {}
1350                         for arg in self.__device_args:
1351                                 args[arg] = _device.get(arg, None)
1352
1353                         # Skip if the subsystem is not set
1354                         if not args.get("subsystem", None):
1355                                 continue
1356
1357                         # Find the device or create a new one.
1358                         device = self.fireinfo.get_device(**args)
1359                         if not device:
1360                                 device = self.fireinfo.create_device(**args)
1361
1362                         self.devices.append(device)
1363
1364         def __parse_system(self, system):
1365                 vendor = system.get("vendor", None)
1366                 if not vendor:
1367                         vendor = None
1368
1369                 model = system.get("model", None)
1370                 if not model:
1371                         model = None
1372
1373                 self.system_id = self.fireinfo.get_system(vendor, model)
1374                 if not self.system_id:
1375                         self.system_id = self.fireinfo.create_system(vendor, model)
1376
1377         @property
1378         def processor_id(self):
1379                 if not self.processor:
1380                         return
1381
1382                 return self.processor.id
1383
1384         def __parse_processor(self, _processor):
1385                 args = {}
1386                 for arg in self.__processor_args:
1387                         if arg == "core_count":
1388                                 _arg = "count"
1389                         else:
1390                                 _arg = arg
1391
1392                         args[arg] = _processor.get(_arg, None)
1393
1394                 self.processor = self.fireinfo.get_processor(**args)
1395                 if not self.processor:
1396                         self.processor = self.fireinfo.create_processor(**args)
1397
1398                 self.processor_clock_speed = _processor.get("speed", None)
1399                 self.processor_bogomips = _processor.get("bogomips", None)
1400
1401                 arch = _processor.get("arch", None)
1402                 if arch:
1403                         self.__parse_arch(arch)
1404
1405         def __parse_kernel(self, kernel):
1406                 self.kernel_id = self.fireinfo.get_kernel(kernel)
1407                 if not self.kernel_id:
1408                         self.kernel_id = self.fireinfo.create_kernel(kernel)
1409                         assert self.kernel_id
1410
1411                 self.kernel = kernel
1412
1413         def __parse_arch(self, arch):
1414                 self.arch_id = self.fireinfo.get_arch(arch)
1415                 if not self.arch_id:
1416                         self.arch_id = self.fireinfo.create_arch(arch)
1417
1418                 self.arch = arch
1419
1420         def __parse_release(self, release):
1421                 # Remove the arch bit
1422                 if release:
1423                         r = [e for e in release.split() if e]
1424                         for s in ("(x86_64)", "(aarch64)", "(i586)", "(armv6l)", "(armv5tel)", "(riscv64)"):
1425                                 try:
1426                                         r.remove(s)
1427                                         break
1428                                 except ValueError:
1429                                         pass
1430
1431                         release = " ".join(r)
1432
1433                 self.release_id = self.fireinfo.get_release(release)
1434                 if not self.release_id:
1435                         self.release_id = self.fireinfo.create_release(release)
1436                         assert self.release_id
1437
1438                 self.release = release
1439
1440         def __parse_language(self, language):
1441                 self.language = language
1442                 self.language, delim, rest = self.language.partition(".")
1443                 self.language, delim, rest = self.language.partition("_")
1444
1445         def __parse_hypervisor(self, hypervisor):
1446                 vendor = hypervisor.get("vendor", "other")
1447
1448                 if vendor in ("other", "unknown"):
1449                         self.hypervisor_id = None
1450                         return
1451
1452                 self.hypervisor_id = self.fireinfo.get_hypervisor(vendor)
1453                 if not self.hypervisor_id:
1454                         self.hypervisor_id = self.fireinfo.create_hypervisor(vendor)
1455
1456         def __parse_network(self, network):
1457                 self.network = ProfileNetwork({
1458                         "has_red"    : network.get("red", False),
1459                         "has_green"  : network.get("green", False),
1460                         "has_orange" : network.get("orange", False),
1461                         "has_blue"   : network.get("blue", False),
1462                 })
1463
1464
1465 class Fireinfo(Object):
1466         def get_profile_count(self, when=None):
1467                 res = self.db.get("SELECT COUNT(*) AS count FROM fireinfo_profiles \
1468                         WHERE then_or_now(%s) BETWEEN time_created AND time_valid", when)
1469
1470                 if res:
1471                         return res.count
1472
1473         def get_total_updates_count(self, when=None):
1474                 res = self.db.get("SELECT COUNT(*) + SUM(updates) AS count \
1475                         FROM fireinfo_profiles WHERE time_created <= then_or_now(%s)", when)
1476
1477                 if res:
1478                         return res.count
1479
1480         # Parser
1481
1482         def parse_profile(self, public_id, blob):
1483                 return ProfileParser(self.backend, public_id, blob)
1484
1485         # Profiles
1486
1487         def profile_exists(self, public_id):
1488                 res = self.db.get("SELECT id FROM fireinfo_profiles \
1489                         WHERE public_id = %s LIMIT 1", public_id)
1490
1491                 if res:
1492                         return True
1493
1494                 return False
1495
1496         def profile_rate_limit_active(self, public_id, when=None):
1497                 res = self.db.get("SELECT COUNT(*) AS count FROM fireinfo_profiles_log \
1498                         WHERE public_id = %s AND ts >= then_or_now(%s) - INTERVAL '60 minutes'",
1499                         public_id, when)
1500
1501                 if res and res.count >= 10:
1502                         return True
1503
1504                 return False
1505
1506         def is_private_id_change_permitted(self, public_id, private_id, when=None):
1507                 # Check if a profile exists with a different private id that is still valid
1508                 res = self.db.get("SELECT 1 FROM fireinfo_profiles \
1509                         WHERE public_id = %s AND NOT private_id = %s \
1510                         AND time_valid >= then_or_now(%s) LIMIT 1", public_id, private_id, when)
1511
1512                 if res:
1513                         return False
1514
1515                 return True
1516
1517         def get_profile(self, public_id, private_id=None, when=None):
1518                 res = self.db.get("SELECT * FROM fireinfo_profiles \
1519                         WHERE public_id = %s AND \
1520                                 (CASE WHEN %s IS NULL THEN TRUE ELSE private_id = %s END) AND \
1521                                 then_or_now(%s) BETWEEN time_created AND time_valid \
1522                         ORDER BY time_updated DESC LIMIT 1",
1523                         public_id, private_id, private_id, when)
1524
1525                 if res:
1526                         return Profile(self.backend, res.id, res)
1527
1528         def get_profile_with_data(self, public_id, when=None):
1529                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1530                         SELECT * FROM profiles JOIN fireinfo_profiles ON profiles.id = fireinfo_profiles.id \
1531                                 WHERE public_id = %s ORDER BY time_updated DESC LIMIT 1", when, public_id)
1532
1533                 if res:
1534                         return Profile(self.backend, res.id, res)
1535
1536         def get_profiles(self, public_id):
1537                 res = self.db.query("SELECT * FROM fireinfo_profiles \
1538                         WHERE public_id = %s ORDER BY time_created DESC", public_id)
1539
1540                 profiles = []
1541                 for row in res:
1542                         profile = Profile(self.backend, row.id, row)
1543                         profiles.append(profile)
1544
1545                 return profiles
1546
1547         def create_profile(self, public_id, private_id, when=None):
1548                 valid = self.settings.get_int("fireinfo_profile_days_valid", 14)
1549
1550                 res = self.db.get("INSERT INTO fireinfo_profiles(public_id, private_id, \
1551                         time_created, time_updated, time_valid) VALUES(%s, %s, then_or_now(%s), \
1552                         then_or_now(%s), then_or_now(%s) + INTERVAL '%s days') RETURNING id",
1553                         public_id, private_id, when, when, when, valid)
1554
1555                 if res:
1556                         p = Profile(self.backend, res.id)
1557                         p.log_profile_update()
1558
1559                         return p
1560
1561         # Devices
1562
1563         def create_device(self, subsystem, vendor, model, sub_vendor=None, sub_model=None,
1564                         driver=None, deviceclass=None):
1565                 res = self.db.get("INSERT INTO fireinfo_devices(subsystem, vendor, model, \
1566                                 sub_vendor, sub_model, driver, deviceclass) VALUES(%s, %s, %s, %s, %s, %s, %s) \
1567                                 RETURNING id", subsystem, vendor, model, sub_vendor, sub_model, driver, deviceclass)
1568
1569                 if res:
1570                         return Device(self.backend, res.id)
1571
1572         def get_device(self, subsystem, vendor, model, sub_vendor=None, sub_model=None,
1573                         driver=None, deviceclass=None):
1574                 res = self.db.get("SELECT * FROM fireinfo_devices \
1575                         WHERE subsystem = %s AND vendor = %s AND model = %s \
1576                         AND sub_vendor IS NOT DISTINCT FROM %s \
1577                         AND sub_model IS NOT DISTINCT FROM %s \
1578                         AND driver IS NOT DISTINCT FROM %s \
1579                         AND deviceclass IS NOT DISTINCT FROM %s \
1580                         LIMIT 1", subsystem, vendor, model, sub_vendor,
1581                                 sub_model, driver, deviceclass)
1582
1583                 if res:
1584                         return Device(self.backend, res.id, res)
1585
1586         # System
1587
1588         def create_system(self, vendor, model):
1589                 res = self.db.get("INSERT INTO fireinfo_systems(vendor, model) \
1590                         VALUES(%s, %s) RETURNING id", vendor, model)
1591
1592                 if res:
1593                         return res.id
1594
1595         def get_system(self, vendor, model):
1596                 res = self.db.get("SELECT id FROM fireinfo_systems WHERE vendor IS NOT DISTINCT FROM %s \
1597                         AND model IS NOT DISTINCT FROM %s LIMIT 1", vendor, model)
1598
1599                 if res:
1600                         return res.id
1601
1602         # Processors
1603
1604         def create_processor(self, vendor, model_string, family, model, stepping, core_count, flags=None):
1605                 res = self.db.get("INSERT INTO fireinfo_processors(vendor, model_string, \
1606                         family, model, stepping, core_count, flags) VALUES(%s, %s, %s, %s, %s, %s, %s) \
1607                         RETURNING id", vendor or None, model_string or None, family, model, stepping, core_count, flags)
1608
1609                 if res:
1610                         return Processor(self.backend, res.id)
1611
1612         def get_processor_by_id(self, processor_id, **kwargs):
1613                 res = self.db.get("SELECT * FROM fireinfo_processors \
1614                         WHERE id = %s", processor_id)
1615
1616                 if res:
1617                         return Processor(self.backend, res.id, data=res, **kwargs)
1618
1619         def get_processor(self, vendor, model_string, family, model, stepping, core_count, flags=None):
1620                 if flags is None:
1621                         flags = []
1622
1623                 res = self.db.get("SELECT * FROM fireinfo_processors \
1624                         WHERE vendor IS NOT DISTINCT FROM %s AND model_string IS NOT DISTINCT FROM %s \
1625                         AND family IS NOT DISTINCT FROM %s AND model IS NOT DISTINCT FROM %s \
1626                         AND stepping IS NOT DISTINCT FROM %s AND core_count = %s \
1627                         AND flags <@ %s AND flags @> %s", vendor or None, model_string or None,
1628                         family, model, stepping, core_count, flags, flags)
1629
1630                 if res:
1631                         return Processor(self.backend, res.id, res)
1632
1633         # Kernel
1634
1635         def create_kernel(self, kernel):
1636                 res = self.db.get("INSERT INTO fireinfo_kernels(name) VALUES(%s) \
1637                         RETURNING id", kernel)
1638
1639                 if res:
1640                         return res.id
1641
1642         def get_kernel(self, kernel):
1643                 res = self.db.get("SELECT id FROM fireinfo_kernels WHERE name = %s", kernel)
1644
1645                 if res:
1646                         return res.id
1647
1648         # Arch
1649
1650         def create_arch(self, arch):
1651                 res = self.db.get("INSERT INTO fireinfo_arches(name) VALUES(%s) \
1652                         RETURNING id", arch)
1653
1654                 if res:
1655                         return res.id
1656
1657         def get_arch(self, arch):
1658                 res = self.db.get("SELECT id FROM fireinfo_arches WHERE name = %s", arch)
1659
1660                 if res:
1661                         return res.id
1662
1663         # Release
1664
1665         def create_release(self, release):
1666                 res = self.db.get("INSERT INTO fireinfo_releases(name) VALUES(%s) \
1667                         RETURNING id", release)
1668
1669                 if res:
1670                         return res.id
1671
1672         def get_release(self, release):
1673                 res = self.db.get("SELECT id FROM fireinfo_releases WHERE name = %s", release)
1674
1675                 if res:
1676                         return res.id
1677
1678         def get_release_penetration(self, release, when=None):
1679                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1680                         SELECT COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS penetration FROM profiles \
1681                         LEFT JOIN fireinfo_profiles_releases ON profiles.id = fireinfo_profiles_releases.profile_id \
1682                         WHERE fireinfo_profiles_releases.release_id = %s", when, release.fireinfo_id)
1683
1684                 if res:
1685                         return res.penetration
1686
1687         def get_random_country_penetration(self):
1688                 res = self.db.get("SELECT * FROM fireinfo_country_percentages \
1689                         ORDER BY RANDOM() LIMIT 1")
1690
1691                 if res:
1692                         return database.Row({
1693                                 "country"    : iso3166.countries.get(res.location),
1694                                 "percentage" : res.count,
1695                         })
1696
1697         # Hypervisor
1698
1699         def create_hypervisor(self, hypervisor):
1700                 res = self.db.get("INSERT INTO fireinfo_hypervisors(name) VALUES(%s) \
1701                         RETURNING id", hypervisor)
1702
1703                 if res:
1704                         return res.id
1705
1706         def get_hypervisor(self, hypervisor):
1707                 res = self.db.get("SELECT id FROM fireinfo_hypervisors WHERE name = %s",
1708                         hypervisor)
1709
1710                 if res:
1711                         return res.id
1712
1713         # Handle profile
1714
1715         def handle_profile(self, *args, **kwargs):
1716                 self.db.execute("START TRANSACTION")
1717
1718                 # Wrap all the handling of the profile in a huge transaction.
1719                 try:
1720                         self._handle_profile(*args, **kwargs)
1721
1722                 except:
1723                         self.db.execute("ROLLBACK")
1724                         raise
1725
1726                 else:
1727                         self.db.execute("COMMIT")
1728
1729         def _handle_profile(self, public_id, profile_blob, country_code=None, when=None):
1730                 private_id = profile_blob.get("private_id", None)
1731                 assert private_id
1732
1733                 # Check if the profile already exists in the database.
1734                 profile = self.fireinfo.get_profile(public_id, private_id=private_id, when=when)
1735
1736                 # Check if the update can actually be updated
1737                 if profile and self.fireinfo.profile_rate_limit_active(public_id, when=when):
1738                         logging.warning("There were too many updates for this profile in the last hour: %s" % public_id)
1739                         return
1740
1741                 elif not self.is_private_id_change_permitted(public_id, private_id, when=when):
1742                         logging.warning("Changing private id is not permitted for profile: %s" % public_id)
1743                         return
1744
1745                 # Parse the profile
1746                 profile_parser = self.parse_profile(public_id, profile_blob)
1747
1748                 # If a profile exists, check if it matches and if so, just update the
1749                 # timestamp.
1750                 if profile:
1751                         # Check if the profile has changed. If so, update the data.
1752                         if profile_parser.equals(profile):
1753                                 profile.updated(profile_parser, country_code=country_code, when=when)
1754                                 return
1755
1756                         # If it does not match, we assume that it is expired and
1757                         # create a new profile.
1758                         profile.expired(when=when)
1759
1760                 # Replace the old profile with a new one
1761                 profile = self.fireinfo.create_profile(public_id, private_id, when=when)
1762                 profile.parse(profile_parser)
1763
1764                 if country_code:
1765                         profile.set_country_code(country_code)
1766
1767                 return profile
1768
1769         # Data outputs
1770
1771         def get_random_profile(self, when=None):
1772                 # Check if the architecture exists so that we pick a profile with some data
1773                 res = self.db.get("SELECT public_id FROM fireinfo_profiles \
1774                         LEFT JOIN fireinfo_profiles_arches ON fireinfo_profiles.id = fireinfo_profiles_arches.profile_id \
1775                         WHERE fireinfo_profiles_arches.profile_id IS NOT NULL \
1776                         AND then_or_now(%s) BETWEEN time_created AND time_valid ORDER BY RANDOM() LIMIT 1", when)
1777
1778                 if res:
1779                         return res.public_id
1780
1781         def get_active_profiles(self, when=None):
1782                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_at(%s) AS id) \
1783                         SELECT COUNT(*) AS with_data, (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1784                         LEFT JOIN fireinfo_profiles_releases ON profiles.id = fireinfo_profiles_releases.profile_id \
1785                         WHERE fireinfo_profiles_releases.profile_id IS NOT NULL", when)
1786
1787                 if res:
1788                         return res.with_data, res.count
1789
1790         def get_archive_size(self, when=None):
1791                 res = self.db.get("SELECT COUNT(*) AS count FROM fireinfo_profiles \
1792                         WHERE time_created <= then_or_now(%s)", when)
1793
1794                 if res:
1795                         return res.count
1796
1797         def get_geo_location_map(self, when=None, minimum_percentage=0):
1798                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_at(%s) AS id) \
1799                         SELECT location, COUNT(location)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1800                         LEFT JOIN fireinfo_profiles_locations ON profiles.id = fireinfo_profiles_locations.profile_id \
1801                         WHERE fireinfo_profiles_locations.location IS NOT NULL GROUP BY location \
1802                         HAVING COUNT(location)::float / (SELECT COUNT(*) FROM profiles) >= %s ORDER BY count DESC",
1803                         when, minimum_percentage)
1804
1805                 return list(((r.location, r.count) for r in res))
1806
1807         @property
1808         def cpu_vendors(self):
1809                 res = self.db.query("SELECT DISTINCT vendor FROM fireinfo_processors ORDER BY vendor")
1810
1811                 return (CPU_VENDORS.get(r.vendor, r.vendor) for r in res)
1812
1813         def get_cpu_vendors_map(self, when=None):
1814                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1815                         SELECT COALESCE(vendor, %s) AS vendor, COUNT(vendor)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1816                         LEFT JOIN fireinfo_profiles_processors ON profiles.id = fireinfo_profiles_processors.profile_id \
1817                         LEFT JOIN fireinfo_processors ON fireinfo_profiles_processors.processor_id = fireinfo_processors.id \
1818                         WHERE NOT fireinfo_profiles_processors.processor_id IS NULL GROUP BY vendor ORDER BY count DESC", when, "Unknown")
1819
1820                 return ((CPU_VENDORS.get(r.vendor, r.vendor), r.count) for r in res)
1821
1822         def get_cpu_clock_speeds(self, when=None):
1823                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1824                         SELECT AVG(fireinfo_profiles_processors.clock_speed) AS avg, \
1825                         STDDEV(fireinfo_profiles_processors.clock_speed) AS stddev, \
1826                         MIN(fireinfo_profiles_processors.clock_speed) AS min, \
1827                         MAX(fireinfo_profiles_processors.clock_speed) AS max FROM profiles \
1828                         LEFT JOIN fireinfo_profiles_processors ON profiles.id = fireinfo_profiles_processors.profile_id \
1829                         WHERE NOT fireinfo_profiles_processors.processor_id IS NULL \
1830                         AND fireinfo_profiles_processors.clock_speed > 0 \
1831                         AND fireinfo_profiles_processors.clock_speed < fireinfo_profiles_processors.bogomips \
1832                         AND fireinfo_profiles_processors.bogomips <= %s", when, 10000)
1833
1834                 if res:
1835                         return (res.avg or 0, res.stddev or 0, res.min or 0, res.max or 0)
1836
1837         def get_cpus_with_platform_and_flag(self, platform, flag, when=None):
1838                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \
1839                         processors AS (SELECT fireinfo_processors.id AS id, fireinfo_processors.flags AS flags FROM profiles \
1840                         LEFT JOIN fireinfo_profiles_processors ON profiles.id = fireinfo_profiles_processors.profile_id \
1841                         LEFT JOIN fireinfo_processors ON fireinfo_profiles_processors.processor_id = fireinfo_processors.id \
1842                         LEFT JOIN fireinfo_profiles_arches ON profiles.id = fireinfo_profiles_arches.profile_id \
1843                         LEFT JOIN fireinfo_arches ON fireinfo_profiles_arches.arch_id = fireinfo_arches.id \
1844                         WHERE NOT fireinfo_profiles_processors.processor_id IS NULL \
1845                         AND fireinfo_arches.platform = %s AND NOT 'hypervisor' = ANY(fireinfo_processors.flags)) \
1846                         SELECT (COUNT(*)::float / (SELECT NULLIF(COUNT(*), 0) FROM processors)) AS count FROM processors \
1847                         WHERE %s = ANY(processors.flags)", when, platform, flag)
1848
1849                 return res.count or 0
1850
1851         def get_common_cpu_flags_by_platform(self, platform, when=None):
1852                 if platform == "arm":
1853                         flags = (
1854                                 "lpae", "neon", "thumb", "thumbee", "vfpv3", "vfpv4",
1855                         )
1856                 elif platform == "x86":
1857                         flags = (
1858                                 "aes", "avx", "avx2", "lm", "mmx", "mmxext", "nx", "pae",
1859                                 "pni", "popcnt", "sse", "sse2", "rdrand", "ssse3", "sse4a",
1860                                 "sse4_1", "sse4_2", "pclmulqdq", "rdseed",
1861                         )
1862                 else:
1863                         return
1864
1865                 ret = []
1866                 for flag in flags:
1867                         ret.append((flag, self.get_cpus_with_platform_and_flag(platform, flag, when=when)))
1868
1869                 # Add virtual CPU flag "virt" for virtualization support
1870                 if platform == "x86":
1871                         ret.append(("virt",
1872                                 self.get_cpus_with_platform_and_flag(platform, "vmx", when=when) + \
1873                                 self.get_cpus_with_platform_and_flag(platform, "svm", when=when)))
1874
1875                 return sorted(ret, key=lambda x: x[1], reverse=True)
1876
1877         def get_average_memory_amount(self, when=None):
1878                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1879                         SELECT AVG(fireinfo_profiles_memory.amount) AS avg FROM profiles \
1880                         LEFT JOIN fireinfo_profiles_memory ON profiles.id = fireinfo_profiles_memory.profile_id", when)
1881
1882                 if res:
1883                         return res.avg or 0
1884
1885         def get_arch_map(self, when=None):
1886                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1887                         SELECT fireinfo_arches.name AS arch, COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count \
1888                         FROM profiles \
1889                         LEFT JOIN fireinfo_profiles_arches ON profiles.id = fireinfo_profiles_arches.profile_id \
1890                         LEFT JOIN fireinfo_arches ON fireinfo_profiles_arches.arch_id = fireinfo_arches.id \
1891                         WHERE NOT fireinfo_profiles_arches.profile_id IS NULL \
1892                         GROUP BY fireinfo_arches.id ORDER BY count DESC", when)
1893
1894                 return ((r.arch, r.count) for r in res)
1895
1896         # Virtual
1897
1898         def get_hypervisor_map(self, when=None):
1899                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \
1900                         virtual_profiles AS (SELECT profiles.id AS profile_id, fireinfo_profiles_virtual.hypervisor_id FROM profiles \
1901                                 LEFT JOIN fireinfo_profiles_virtual ON profiles.id = fireinfo_profiles_virtual.profile_id \
1902                                 WHERE fireinfo_profiles_virtual.profile_id IS NOT NULL) \
1903                         SELECT COALESCE(fireinfo_hypervisors.name, %s) AS name, \
1904                                 COUNT(*)::float / (SELECT COUNT(*) FROM virtual_profiles) AS count FROM virtual_profiles \
1905                         LEFT JOIN fireinfo_hypervisors ON virtual_profiles.hypervisor_id = fireinfo_hypervisors.id \
1906                         GROUP BY fireinfo_hypervisors.name ORDER BY count DESC", when, "unknown")
1907
1908                 return ((r.name, r.count) for r in res)
1909
1910         def get_virtual_ratio(self, when=None):
1911                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1912                         SELECT COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1913                         LEFT JOIN fireinfo_profiles_virtual ON profiles.id = fireinfo_profiles_virtual.profile_id \
1914                         WHERE fireinfo_profiles_virtual.profile_id IS NOT NULL", when)
1915
1916                 if res:
1917                         return res.count
1918
1919         # Releases
1920
1921         def get_releases_map(self, when=None):
1922                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1923                         SELECT fireinfo_releases.name, COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1924                         LEFT JOIN fireinfo_profiles_releases ON profiles.id = fireinfo_profiles_releases.profile_id \
1925                         LEFT JOIN fireinfo_releases ON fireinfo_profiles_releases.release_id = fireinfo_releases.id \
1926                         GROUP BY fireinfo_releases.name ORDER BY count DESC", when)
1927
1928                 return ((r.name, r.count) for r in res)
1929
1930         def get_kernels_map(self, when=None):
1931                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1932                         SELECT fireinfo_kernels.name, COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1933                         LEFT JOIN fireinfo_profiles_kernels ON profiles.id = fireinfo_profiles_kernels.profile_id \
1934                         LEFT JOIN fireinfo_kernels ON fireinfo_profiles_kernels.kernel_id = fireinfo_kernels.id \
1935                         GROUP BY fireinfo_kernels.name ORDER BY count DESC", when)
1936
1937                 return ((r.name, r.count) for r in res)
1938
1939         def _process_devices(self, devices):
1940                 result = []
1941
1942                 for dev in devices:
1943                         dev = Device(self.backend, dev.get("id", None), dev)
1944                         result.append(dev)
1945
1946                 return result
1947
1948         def get_driver_map(self, driver, when=None):
1949                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \
1950                         devices AS (SELECT * FROM profiles \
1951                                 LEFT JOIN fireinfo_profiles_devices ON profiles.id = fireinfo_profiles_devices.profile_id \
1952                                 LEFT JOIN fireinfo_devices ON fireinfo_profiles_devices.device_id = fireinfo_devices.id \
1953                                 WHERE driver = %s) \
1954                         SELECT subsystem, model, vendor, driver, deviceclass, \
1955                                 COUNT(*)::float / (SELECT COUNT(*) FROM devices) AS percentage FROM devices \
1956                                 GROUP BY subsystem, model, vendor, driver, deviceclass \
1957                                 ORDER BY percentage DESC", when, driver)
1958
1959                 return self._process_devices(res)
1960
1961         subsystem2class = {
1962                 "pci" : hwdata.PCI(),
1963                 "usb" : hwdata.USB(),
1964         }
1965
1966         def get_vendor_string(self, subsystem, vendor_id):
1967                 try:
1968                         cls = self.subsystem2class[subsystem]
1969                 except KeyError:
1970                         return ""
1971
1972                 return cls.get_vendor(vendor_id) or ""
1973
1974         def get_model_string(self, subsystem, vendor_id, model_id):
1975                 try:
1976                         cls = self.subsystem2class[subsystem]
1977                 except KeyError:
1978                         return ""
1979
1980                 return cls.get_device(vendor_id, model_id) or ""
1981
1982         def get_vendor_list(self, when=None):
1983                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1984                         SELECT DISTINCT fireinfo_devices.subsystem AS subsystem, fireinfo_devices.vendor AS vendor FROM profiles \
1985                                 LEFT JOIN fireinfo_profiles_devices ON profiles.id = fireinfo_profiles_devices.profile_id \
1986                                 LEFT JOIN fireinfo_devices ON fireinfo_profiles_devices.device_id = fireinfo_devices.id \
1987                                 WHERE NOT fireinfo_devices.driver = ANY(%s)", when, IGNORED_DEVICES)
1988
1989                 vendors = {}
1990                 for row in res:
1991                         vendor = self.get_vendor_string(row.subsystem, row.vendor)
1992
1993                         # Drop if vendor could not be determined
1994                         if vendor is None:
1995                                 continue
1996
1997                         try:
1998                                 vendors[vendor].append((row.subsystem, row.vendor))
1999                         except KeyError:
2000                                 vendors[vendor] = [(row.subsystem, row.vendor)]
2001
2002                 vendors = list(vendors.items())
2003                 return sorted(vendors)
2004
2005         def get_devices_by_vendor(self, subsystem, vendor, when=None):
2006                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \
2007                         devices AS (SELECT * FROM profiles \
2008                                 LEFT JOIN fireinfo_profiles_devices ON profiles.id = fireinfo_profiles_devices.profile_id \
2009                                 LEFT JOIN fireinfo_devices ON fireinfo_profiles_devices.device_id = fireinfo_devices.id \
2010                                 WHERE NOT fireinfo_devices.driver = ANY(%s)), \
2011                         vendor_devices AS (SELECT * FROM devices WHERE devices.subsystem = %s AND devices.vendor = %s) \
2012                         SELECT subsystem, model, vendor, driver, deviceclass FROM vendor_devices \
2013                                 GROUP BY subsystem, model, vendor, driver, deviceclass", when, IGNORED_DEVICES, subsystem, vendor)
2014
2015                 return self._process_devices(res)