webapp/backend/fireinfo.py

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