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.vendor == "Intel" and self.family == 6 and self.model in (15, 55, 76, 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-duo-box", self._appliance_test_fountainnetworks_duo_box),
 689                                 ("fountainnetworks-prime", self._appliance_test_fountainnetworks_prime),
 690                                 ("lightningwirelabs-eco-plus", self._appliance_test_lightningwirelabs_eco_plus),
 691                                 ("lightningwirelabs-eco", self._appliance_test_lightningwirelabs_eco),
 692                         )
 693
 694                         self._appliance_id = None
 695                         for name, test_func in appliances:
 696                                 if not test_func():
 697                                         continue
 698
 699                                 self._appliance_id = name
 700                                 break
 701
 702                 return self._appliance_id
 703
 704         @property
 705         def appliance(self):
 706                 if self.appliance_id == "fountainnetworks-duo-box":
 707                         return "Fountain Networks - IPFire Duo Box"
 708
 709                 elif self.appliance_id == "fountainnetworks-prime":
 710                         return "Fountain Networks - IPFire Prime Box"
 711
 712                 elif self.appliance_id == "lightningwirelabs-eco-plus":
 713                         return "Lightning Wire Labs - IPFire Eco Plus Appliance"
 714
 715                 elif self.appliance_id == "lightningwirelabs-eco":
 716                         return "Lightning Wire Labs - IPFire Eco Appliance"
 717
 718         def _appliance_test_fountainnetworks_duo_box(self):
 719                 if not self.processor.vendor == "Intel":
 720                         return False
 721
 722                 if not self.processor.model_string == "Intel(R) Celeron(R) 2957U @ 1.40GHz":
 723                         return False
 724
 725                 if not self.count_device("pci", "10ec", "8168") == 2:
 726                         return False
 727
 728                 # WiFi module
 729                 if self.count_device("usb", "148f", "5572") < 1:
 730                         return False
 731
 732                 return True
 733
 734         def _appliance_test_fountainnetworks_prime(self):
 735                 if not self.system in (("SECO", None), ("SECO", "0949")):
 736                         return False
 737
 738                 # Must have a wireless device
 739                 if self.count_device("usb", "148f", "5572") < 1:
 740                         return False
 741
 742                 return True
 743
 744         def _appliance_test_lightningwirelabs_eco(self):
 745                 if not self.system == ("MSI", "MS-9877"):
 746                         return False
 747
 748                 # Must have four Intel network adapters
 749                 network_adapters_count = self.count_device("pci", "8086", "10d3")
 750                 if not network_adapters_count == 4:
 751                         return False
 752
 753                 return True
 754
 755         def _appliance_test_lightningwirelabs_eco_plus(self):
 756                 if not self.system_vendor == "ASUS":
 757                         return False
 758
 759                 if not self.system_model.startswith("P9A-I/2550"):
 760                         return False
 761
 762                 # Must have four Intel network adapters
 763                 network_adapters_count = self.count_device("pci", "8086", "1f41")
 764                 if not network_adapters_count == 4:
 765                         return False
 766
 767                 return True
 768
 769         # Processors
 770
 771         @property
 772         def processor_id(self):
 773                 if hasattr(self, "_processor"):
 774                         return self._processor.id
 775
 776                 if not hasattr(self, "_processor_id"):
 777                         res = self.db.get("SELECT processor_id FROM fireinfo_profiles_processors \
 778                                 WHERE profile_id = %s", self.id)
 779
 780                         if res:
 781                                 self._processor_id = res.processor_id
 782                         else:
 783                                 self._processor_id = None
 784
 785                 return self._processor_id
 786
 787         def get_processor(self):
 788                 if not self.processor_id:
 789                         return
 790
 791                 if not hasattr(self, "_processor"):
 792                         res = self.db.get("SELECT * FROM fireinfo_profiles_processors \
 793                                 WHERE profile_id = %s", self.id)
 794
 795                         if res:
 796                                 self._processor = self.fireinfo.get_processor_by_id(res.processor_id,
 797                                         clock_speed=res.clock_speed, bogomips=res.bogomips)
 798                         else:
 799                                 self._processor = None
 800
 801                 return self._processor
 802
 803         def set_processor(self, processor):
 804                 self.db.execute("DELETE FROM fireinfo_profiles_processors \
 805                         WHERE profile_id = %s", self.id)
 806
 807                 if processor:
 808                         self.db.execute("INSERT INTO fireinfo_profiles_processors(profile_id, processor_id) \
 809                                 VALUES(%s, %s)", self.id, processor.id)
 810
 811                 self._processor = processor
 812
 813         processor = property(get_processor, set_processor)
 814
 815         def set_processor_speeds(self, clock_speed, bogomips):
 816                 self.db.execute("UPDATE fireinfo_profiles_processors \
 817                         SET clock_speed = %s, bogomips = %s WHERE profile_id = %s",
 818                         clock_speed, bogomips, self.id)
 819
 820         # Compat
 821         @property
 822         def cpu(self):
 823                 return self.processor
 824
 825         # Memory
 826
 827         def get_memory(self):
 828                 if not hasattr(self, "_memory"):
 829                         res = self.db.get("SELECT amount FROM fireinfo_profiles_memory \
 830                                 WHERE profile_id = %s", self.id)
 831
 832                         if res:
 833                                 self._memory = res.amount * 1024
 834                         else:
 835                                 self._memory = None
 836
 837                 return self._memory
 838
 839         def set_memory(self, amount):
 840                 if self.memory == amount:
 841                         return
 842
 843                 amount /= 1024
 844
 845                 self.db.execute("DELETE FROM fireinfo_profiles_memory WHERE profile_id = %s", self.id)
 846                 if amount:
 847                         self.db.execute("INSERT INTO fireinfo_profiles_memory(profile_id, amount) \
 848                                 VALUES(%s, %s)", self.id, amount)
 849
 850                 self._memory = amount * 1024
 851
 852         memory = property(get_memory, set_memory)
 853
 854         @property
 855         def friendly_memory(self):
 856                 return util.format_size(self.memory or 0)
 857
 858         # Storage
 859
 860         def get_storage(self):
 861                 if not hasattr(self, "_storage"):
 862                         res = self.db.get("SELECT amount FROM fireinfo_profiles_storage \
 863                                 WHERE profile_id = %s", self.id)
 864
 865                         if res:
 866                                 self._storage = res.amount * 1024
 867                         else:
 868                                 self._storage = None
 869
 870                 return self._storage
 871
 872         def set_storage(self, amount):
 873                 if self.storage == amount:
 874                         return
 875
 876                 amount /= 1024
 877
 878                 self.db.execute("DELETE FROM fireinfo_profiles_storage WHERE profile_id = %s", self.id)
 879                 if amount:
 880                         self.db.execute("INSERT INTO fireinfo_profiles_storage(profile_id, amount) \
 881                                 VALUES(%s, %s)", self.id, amount)
 882
 883                 self._storage = amount * 1024
 884
 885         storage = property(get_storage, set_storage)
 886
 887         @property
 888         def friendly_storage(self):
 889                 return util.format_size(self.storage)
 890
 891         # Kernel
 892
 893         def get_kernel_id(self):
 894                 if not hasattr(self, "_kernel_id"):
 895                         res = self.db.get("SELECT fireinfo_profiles_kernels.kernel_id AS id FROM fireinfo_profiles \
 896                                 LEFT JOIN fireinfo_profiles_kernels ON fireinfo_profiles.id = fireinfo_profiles_kernels.profile_id \
 897                                 WHERE fireinfo_profiles.id = %s", self.id)
 898
 899                         if res:
 900                                 self._kernel_id = res.id
 901                         else:
 902                                 self._kernel_id = None
 903
 904                 return self._kernel_id
 905
 906         def set_kernel_id(self, kernel_id):
 907                 if self.kernel_id == kernel_id:
 908                         return
 909
 910                 self.db.execute("DELETE FROM fireinfo_profiles_kernels WHERE profile_id = %s", self.id)
 911                 if kernel_id:
 912                         self.db.execute("INSERT INTO fireinfo_profiles_kernels(profile_id, kernel_id) \
 913                                 VALUES(%s, %s)", self.id, kernel_id)
 914
 915                 self._kernel_id = kernel_id
 916                 if hasattr(self, "_kernel"):
 917                         del self._kernel
 918
 919         kernel_id = property(get_kernel_id, set_kernel_id)
 920
 921         @property
 922         def kernel(self):
 923                 if not hasattr(self, "_kernel"):
 924                         res = self.db.get("SELECT fireinfo_kernels.name AS name FROM fireinfo_profiles \
 925                                 LEFT JOIN fireinfo_profiles_kernels ON fireinfo_profiles.id = fireinfo_profiles_kernels.profile_id \
 926                                 LEFT JOIN fireinfo_kernels ON fireinfo_kernels.id = fireinfo_profiles_kernels.kernel_id \
 927                                 WHERE fireinfo_profiles.id = %s", self.id)
 928
 929                         if res:
 930                                 self._kernel = res.name
 931                         else:
 932                                 self._kernel = None
 933
 934                 return self._kernel
 935
 936         # Arch
 937
 938         def get_arch_id(self):
 939                 if not hasattr(self, "_arch_id"):
 940                         res = self.db.get("SELECT fireinfo_profiles_arches.arch_id AS id FROM fireinfo_profiles \
 941                                 LEFT JOIN fireinfo_profiles_arches ON fireinfo_profiles.id = fireinfo_profiles_arches.profile_id \
 942                                 WHERE fireinfo_profiles.id = %s", self.id)
 943
 944                         if res:
 945                                 self._arch_id = res.id
 946                         else:
 947                                 self._arch_id = None
 948
 949                 return self._arch_id
 950
 951         def set_arch_id(self, arch_id):
 952                 if self.arch_id == arch_id:
 953                         return
 954
 955                 self.db.execute("DELETE FROM fireinfo_profiles_arches WHERE profile_id = %s", self.id)
 956                 if arch_id:
 957                         self.db.execute("INSERT INTO fireinfo_profiles_arches(profile_id, arch_id) \
 958                                 VALUES(%s, %s)", self.id, arch_id)
 959
 960                 self._arch_id = None
 961                 if hasattr(self, "_arch"):
 962                         del self._arch
 963
 964         arch_id = property(get_arch_id, set_arch_id)
 965
 966         @property
 967         def arch(self):
 968                 if not hasattr(self, "_arch"):
 969                         res = self.db.get("SELECT fireinfo_arches.name AS name FROM fireinfo_profiles \
 970                                 LEFT JOIN fireinfo_profiles_arches ON fireinfo_profiles.id = fireinfo_profiles_arches.profile_id \
 971                                 LEFT JOIN fireinfo_arches ON fireinfo_arches.id = fireinfo_profiles_arches.arch_id \
 972                                 WHERE fireinfo_profiles.id = %s", self.id)
 973
 974                         if res:
 975                                 self._arch = res.name
 976                         else:
 977                                 self._arch = None
 978
 979                 return self._arch
 980
 981         # Release
 982
 983         def get_release_id(self):
 984                 if not hasattr(self, "_release_id"):
 985                         res = self.db.get("SELECT fireinfo_profiles_releases.release_id AS id FROM fireinfo_profiles \
 986                                 LEFT JOIN fireinfo_profiles_releases ON fireinfo_profiles.id = fireinfo_profiles_releases.profile_id \
 987                                 WHERE fireinfo_profiles.id = %s", self.id)
 988
 989                         if res:
 990                                 self._release_id = res.id
 991                         else:
 992                                 self._release_id = None
 993
 994                 return self._release_id
 995
 996         def set_release_id(self, release_id):
 997                 if self.release_id == release_id:
 998                         return
 999
1000                 self.db.execute("DELETE FROM fireinfo_profiles_releases WHERE profile_id = %s", self.id)
1001                 if release_id:
1002                         self.db.execute("INSERT INTO fireinfo_profiles_releases(profile_id, release_id) \
1003                                 VALUES(%s, %s)", self.id, release_id)
1004
1005                 self._release_id = release_id
1006                 if hasattr(self, "_release"):
1007                         del self._release
1008
1009         release_id = property(get_release_id, set_release_id)
1010
1011         @property
1012         def release(self):
1013                 if not hasattr(self, "_release"):
1014                         res = self.db.get("SELECT fireinfo_releases.name AS name FROM fireinfo_profiles \
1015                                 LEFT JOIN fireinfo_profiles_releases ON fireinfo_profiles.id = fireinfo_profiles_releases.profile_id \
1016                                 LEFT JOIN fireinfo_releases ON fireinfo_profiles_releases.release_id = fireinfo_releases.id \
1017                                 WHERE fireinfo_profiles.id = %s", self.id)
1018
1019                         if res:
1020                                 self._release = self._format_release(res.name)
1021                         else:
1022                                 self._release = None
1023
1024                 return self._release
1025
1026         @staticmethod
1027         def _format_release(r):
1028                 if not r:
1029                         return r
1030
1031                 # Remove the development header
1032                 r = r.replace("Development Build: ", "")
1033
1034                 pairs = (
1035                         ("-beta", " - Beta "),
1036                         ("-rc", " - Release Candidate "),
1037                         ("rc", "Release Candidate "),
1038                         ("core", "Core Update "),
1039                         ("beta", "Beta "),
1040                 )
1041
1042                 for k, v in pairs:
1043                         r = r.replace(k, v)
1044
1045                 return r
1046
1047         @property
1048         def release_short(self):
1049                 pairs = (
1050                         (r"Release Candidate (\d+)", r"RC\1"),
1051                 )
1052
1053                 s = self.release
1054                 for pattern, repl in pairs:
1055                         if re.search(pattern, s) is None:
1056                                 continue
1057
1058                         s = re.sub(pattern, repl, s)
1059
1060                 return s
1061
1062         # Virtual
1063
1064         @property
1065         def virtual(self):
1066                 if not hasattr(self, "_virtual"):
1067                         res = self.db.get("SELECT 1 FROM fireinfo_profiles_virtual \
1068                                 WHERE profile_id = %s", self.id)
1069
1070                         if res:
1071                                 self._virtual = True
1072                         else:
1073                                 self._virtual = False
1074
1075                 return self._virtual
1076
1077         def get_hypervisor_id(self):
1078                 if not hasattr(self, "_hypervisor_id"):
1079                         res = self.db.get("SELECT fireinfo_profiles_virtual.hypervisor_id AS id FROM fireinfo_profiles \
1080                                 LEFT JOIN fireinfo_profiles_virtual ON fireinfo_profiles.id = fireinfo_profiles_virtual.profile_id \
1081                                 WHERE fireinfo_profiles.id = %s", self.id)
1082
1083                         if res:
1084                                 self._hypervisor_id = res.id
1085                         else:
1086                                 self._hypervisor_id = None
1087
1088                 return self._hypervisor_id
1089
1090         def set_hypervisor_id(self, hypervisor_id):
1091                 self.db.execute("DELETE FROM fireinfo_profiles_virtual WHERE profile_id = %s", self.id)
1092                 self.db.execute("INSERT INTO fireinfo_profiles_virtual(profile_id, hypervisor_id) \
1093                         VALUES(%s, %s)", self.id, hypervisor_id)
1094
1095                 self._hypervisor_id = hypervisor_id
1096
1097         hypervisor_id = property(get_hypervisor_id, set_hypervisor_id)
1098
1099         @property
1100         def hypervisor(self):
1101                 if not hasattr(self, "_hypervisor"):
1102                         res = self.db.get("SELECT fireinfo_hypervisors.name AS hypervisor FROM fireinfo_profiles \
1103                                 LEFT JOIN fireinfo_profiles_virtual ON fireinfo_profiles.id = fireinfo_profiles_virtual.profile_id \
1104                                 LEFT JOIN fireinfo_hypervisors ON fireinfo_profiles_virtual.hypervisor_id = fireinfo_hypervisors.id \
1105                                 WHERE fireinfo_profiles.id = %s", self.id)
1106
1107                         if res:
1108                                 self._hypervisor = res.hypervisor
1109                         else:
1110                                 self._hypervisor = None
1111
1112                 return self._hypervisor
1113
1114         # Language
1115
1116         def get_language(self):
1117                 if not hasattr(self, "_language"):
1118                         res = self.db.get("SELECT language FROM fireinfo_profiles_languages \
1119                                 WHERE profile_id = %s", self.id)
1120
1121                         if res:
1122                                 self._language = res.language
1123                         else:
1124                                 self._language = None
1125
1126                 return self._language
1127
1128         def set_language(self, language):
1129                 self.db.execute("DELETE FROM fireinfo_profiles_languages WHERE profile_id = %s", self.id)
1130
1131                 if language:
1132                         self.db.execute("INSERT INTO fireinfo_profiles_languages(profile_id, language) \
1133                                 VALUES(%s, %s)", self.id, language)
1134
1135                 self._language = language
1136
1137         language = property(get_language, set_language)
1138
1139         # Network
1140
1141         def get_network(self):
1142                 if not hasattr(self, "_network"):
1143                         res = self.db.get("SELECT * FROM fireinfo_profiles_networks \
1144                                 WHERE profile_id = %s", self.id)
1145
1146                         if not res:
1147                                 res = {}
1148
1149                         self._network = ProfileNetwork(res)
1150
1151                 return self._network
1152
1153         def set_network(self, network):
1154                 self.db.execute("DELETE FROM fireinfo_profiles_networks WHERE profile_id = %s", self.id)
1155
1156                 if network:
1157                         self.db.execute("INSERT INTO fireinfo_profiles_networks(profile_id, \
1158                                 has_red, has_green, has_orange, has_blue) VALUES(%s, %s, %s, %s, %s)",
1159                                 self.id, network.has_red, network.has_green, network.has_orange, network.has_blue)
1160
1161                 self._network = network
1162
1163         network = property(get_network, set_network)
1164
1165
1166 class ProfileData(Object):
1167         def __init__(self, backend, id, data=None, profile=None):
1168                 Object.__init__(self, backend)
1169
1170                 self.id = id
1171                 self._data = data
1172                 self._profile = profile
1173
1174         @property
1175         def data(self):
1176                 if self._data is None:
1177                         self._data = self.db.get("SELECT * FROM fireinfo_profile_data \
1178                                 WHERE id = %s", self.id)
1179
1180                 return self._data
1181
1182         @property
1183         def profile(self):
1184                 if not self._profile:
1185                         self._profile = self.fireinfo.get_profile_by_id(self.profile_id)
1186
1187                 return self._profile
1188
1189         @property
1190         def profile_id(self):
1191                 return self.data.profile_id
1192
1193
1194 class ProfileParserError(Exception):
1195         pass
1196
1197
1198 class ProfileParser(Object):
1199         __device_args = (
1200                 "subsystem",
1201                 "vendor",
1202                 "model",
1203                 "sub_vendor",
1204                 "sub_model",
1205                 "driver",
1206                 "deviceclass",
1207         )
1208
1209         __processor_args = (
1210                 "vendor",
1211                 "model_string",
1212                 "family",
1213                 "model",
1214                 "stepping",
1215                 "core_count",
1216                 "flags",
1217         )
1218
1219         __processor_args_mandatory = (
1220                 "vendor",
1221                 "model_string",
1222         )
1223
1224         def __init__(self, backend, public_id, blob=None):
1225                 Object.__init__(self, backend)
1226
1227                 self.public_id = public_id
1228                 self.private_id = None
1229                 self.devices = []
1230                 self.processor = None
1231                 self.processor_clock_speed = None
1232                 self.processor_bogomips = None
1233                 self.system_id = None
1234                 self.memory = None
1235                 self.storage = None
1236                 self.kernel = None
1237                 self.kernel_id = None
1238                 self.arch = None
1239                 self.arch_id = None
1240                 self.release = None
1241                 self.release_id = None
1242                 self.language = None
1243                 self.virtual = None
1244                 self.hypervisor_id = None
1245                 self.network = None
1246
1247                 self.__parse_blob(blob)
1248
1249         def equals(self, other):
1250                 if not self.processor_id == other.processor_id:
1251                         return False
1252
1253                 if not self.device_ids == other.device_ids:
1254                         return False
1255
1256                 if not self.system_id == other.system_id:
1257                         return False
1258
1259                 if not self.memory == other.memory:
1260                         return False
1261
1262                 if not self.storage == other.storage:
1263                         return False
1264
1265                 if not self.kernel_id == other.kernel_id:
1266                         return False
1267
1268                 if not self.arch_id == other.arch_id:
1269                         return False
1270
1271                 if not self.release_id == other.release_id:
1272                         return False
1273
1274                 if not self.language == other.language:
1275                         return False
1276
1277                 if not self.virtual == other.virtual:
1278                         return False
1279
1280                 if other.virtual:
1281                         if not self.hypervisor_id == other.hypervisor_id:
1282                                 return False
1283
1284                 if not self.network == other.network:
1285                         return False
1286
1287                 return True
1288
1289         def __parse_blob(self, blob):
1290                 _profile = blob.get("profile", {})
1291                 self.private_id = blob.get("private_id")
1292
1293                 # Do not try to parse an empty profile
1294                 if not _profile:
1295                         return
1296
1297                 # Processor
1298                 _processor = _profile.get("cpu", {})
1299                 self.__parse_processor(_processor)
1300
1301                 # Find devices
1302                 _devices = _profile.get("devices", [])
1303                 self.__parse_devices(_devices)
1304
1305                 # System
1306                 _system = _profile.get("system")
1307                 if _system:
1308                         self.__parse_system(_system)
1309
1310                         # Memory (convert to bytes)
1311                         memory = _system.get("memory", None)
1312                         if memory:
1313                                 self.memory = memory * 1024
1314
1315                         # Storage size (convert to bytes)
1316                         storage = _system.get("root_size", None)
1317                         if storage:
1318                                 self.storage = storage * 1024
1319
1320                         # Kernel
1321                         kernel = _system.get("kernel_release", None)
1322                         if kernel:
1323                                 self.__parse_kernel(kernel)
1324
1325                         # Release
1326                         release = _system.get("release", None)
1327                         if release:
1328                                 self.__parse_release(release)
1329
1330                         # Language
1331                         language = _system.get("language", None)
1332                         if language:
1333                                 self.__parse_language(language)
1334
1335                         # Virtual
1336                         self.virtual = _system.get("virtual", False)
1337                         if self.virtual:
1338                                 hypervisor = _profile.get("hypervisor")
1339                                 self.__parse_hypervisor(hypervisor)
1340
1341                 # Network
1342                 _network = _profile.get("network")
1343                 if _network:
1344                         self.__parse_network(_network)
1345
1346         @property
1347         def device_ids(self):
1348                 return sorted([d.id for d in self.devices])
1349
1350         def __parse_devices(self, _devices):
1351                 self.devices = []
1352
1353                 for _device in _devices:
1354                         args = {}
1355                         for arg in self.__device_args:
1356                                 args[arg] = _device.get(arg, None)
1357
1358                         # Skip if the subsystem is not set
1359                         if not args.get("subsystem", None):
1360                                 continue
1361
1362                         # Find the device or create a new one.
1363                         device = self.fireinfo.get_device(**args)
1364                         if not device:
1365                                 device = self.fireinfo.create_device(**args)
1366
1367                         self.devices.append(device)
1368
1369         def __parse_system(self, system):
1370                 vendor = system.get("vendor", None)
1371                 if not vendor:
1372                         vendor = None
1373
1374                 model = system.get("model", None)
1375                 if not model:
1376                         model = None
1377
1378                 self.system_id = self.fireinfo.get_system(vendor, model)
1379                 if not self.system_id:
1380                         self.system_id = self.fireinfo.create_system(vendor, model)
1381
1382         @property
1383         def processor_id(self):
1384                 if not self.processor:
1385                         return
1386
1387                 return self.processor.id
1388
1389         def __parse_processor(self, _processor):
1390                 args = {}
1391                 for arg in self.__processor_args:
1392                         if arg == "core_count":
1393                                 _arg = "count"
1394                         else:
1395                                 _arg = arg
1396
1397                         args[arg] = _processor.get(_arg, None)
1398
1399                 for arg in self.__processor_args_mandatory:
1400                         if not args.get(arg, None):
1401                                 raise ProfileParserError, "Mandatory argument missing: %s" % arg
1402
1403                 self.processor = self.fireinfo.get_processor(**args)
1404                 if not self.processor:
1405                         self.processor = self.fireinfo.create_processor(**args)
1406
1407                 self.processor_clock_speed = _processor.get("speed", None)
1408                 self.processor_bogomips = _processor.get("bogomips", None)
1409
1410                 arch = _processor.get("arch", None)
1411                 if arch:
1412                         self.__parse_arch(arch)
1413
1414         def __parse_kernel(self, kernel):
1415                 self.kernel_id = self.fireinfo.get_kernel(kernel)
1416                 if not self.kernel_id:
1417                         self.kernel_id = self.fireinfo.create_kernel(kernel)
1418                         assert self.kernel_id
1419
1420                 self.kernel = kernel
1421
1422         def __parse_arch(self, arch):
1423                 self.arch_id = self.fireinfo.get_arch(arch)
1424                 if not self.arch_id:
1425                         self.arch_id = self.fireinfo.create_arch(arch)
1426
1427                 self.arch = arch
1428
1429         def __parse_release(self, release):
1430                 # Remove the arch bit
1431                 if release:
1432                         r = [e for e in release.split() if e]
1433                         for s in ("(x86_64)", "(i586)", "(armv5tel)"):
1434                                 try:
1435                                         r.remove(s)
1436                                         break
1437                                 except ValueError:
1438                                         pass
1439
1440                         release = " ".join(r)
1441
1442                 self.release_id = self.fireinfo.get_release(release)
1443                 if not self.release_id:
1444                         self.release_id = self.fireinfo.create_release(release)
1445                         assert self.release_id
1446
1447                 self.release = release
1448
1449         def __parse_language(self, language):
1450                 self.language = language
1451                 self.language, delim, rest = self.language.partition(".")
1452                 self.language, delim, rest = self.language.partition("_")
1453
1454         def __parse_hypervisor(self, hypervisor):
1455                 vendor = hypervisor.get("vendor", "other")
1456
1457                 if vendor in ("other", "unknown"):
1458                         self.hypervisor_id = None
1459                         return
1460
1461                 self.hypervisor_id = self.fireinfo.get_hypervisor(vendor)
1462                 if not self.hypervisor_id:
1463                         self.hypervisor_id = self.fireinfo.create_hypervisor(vendor)
1464
1465         def __parse_network(self, network):
1466                 self.network = ProfileNetwork({
1467                         "has_red"    : network.get("red", False),
1468                         "has_green"  : network.get("green", False),
1469                         "has_orange" : network.get("orange", False),
1470                         "has_blue"   : network.get("blue", False),
1471                 })
1472
1473
1474 class Fireinfo(Object):
1475         def get_profile_count(self, when=None):
1476                 res = self.db.get("SELECT COUNT(*) AS count FROM fireinfo_profiles \
1477                         WHERE then_or_now(%s) BETWEEN time_created AND time_valid", when)
1478
1479                 if res:
1480                         return res.count
1481
1482         def get_total_updates_count(self, when=None):
1483                 res = self.db.get("SELECT COUNT(*) + SUM(updates) AS count \
1484                         FROM fireinfo_profiles WHERE time_created <= then_or_now(%s)", when)
1485
1486                 if res:
1487                         return res.count
1488
1489         # Parser
1490
1491         def parse_profile(self, public_id, blob):
1492                 return ProfileParser(self.backend, public_id, blob)
1493
1494         # Profiles
1495
1496         def profile_exists(self, public_id):
1497                 res = self.db.get("SELECT id FROM fireinfo_profiles \
1498                         WHERE public_id = %s LIMIT 1", public_id)
1499
1500                 if res:
1501                         return True
1502
1503                 return False
1504
1505         def profile_rate_limit_active(self, public_id, when=None):
1506                 # Remove all outdated entries
1507                 self.db.execute("DELETE FROM fireinfo_profiles_log \
1508                         WHERE ts <= then_or_now(%s) - INTERVAL '60 minutes'", when)
1509
1510                 res = self.db.get("SELECT COUNT(*) AS count FROM fireinfo_profiles_log \
1511                         WHERE public_id = %s", public_id)
1512
1513                 if res and res.count >= 10:
1514                         return True
1515
1516                 return False
1517
1518         def is_private_id_change_permitted(self, public_id, private_id, when=None):
1519                 # Check if a profile exists with a different private id that is still valid
1520                 res = self.db.get("SELECT 1 FROM fireinfo_profiles \
1521                         WHERE public_id = %s AND NOT private_id = %s \
1522                         AND time_valid >= then_or_now(%s) LIMIT 1", public_id, private_id, when)
1523
1524                 if res:
1525                         return False
1526
1527                 return True
1528
1529         def get_profile(self, public_id, private_id=None, when=None):
1530                 res = self.db.get("SELECT * FROM fireinfo_profiles \
1531                         WHERE public_id = %s AND \
1532                                 (CASE WHEN %s IS NULL THEN TRUE ELSE private_id = %s END) AND \
1533                                 then_or_now(%s) BETWEEN time_created AND time_valid \
1534                         ORDER BY time_updated DESC LIMIT 1",
1535                         public_id, private_id, private_id, when)
1536
1537                 if res:
1538                         return Profile(self.backend, res.id, res)
1539
1540         def get_profile_with_data(self, public_id, when=None):
1541                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1542                         SELECT * FROM profiles JOIN fireinfo_profiles ON profiles.id = fireinfo_profiles.id \
1543                                 WHERE public_id = %s ORDER BY time_updated DESC LIMIT 1", when, public_id)
1544
1545                 if res:
1546                         return Profile(self.backend, res.id, res)
1547
1548         def get_profiles(self, public_id):
1549                 res = self.db.query("SELECT * FROM fireinfo_profiles \
1550                         WHERE public_id = %s ORDER BY time_created DESC", public_id)
1551
1552                 profiles = []
1553                 for row in res:
1554                         profile = Profile(self.backend, row.id, row)
1555                         profiles.append(profile)
1556
1557                 return profiles
1558
1559         def create_profile(self, public_id, private_id, when=None):
1560                 valid = self.settings.get_int("fireinfo_profile_days_valid", 14)
1561
1562                 res = self.db.get("INSERT INTO fireinfo_profiles(public_id, private_id, \
1563                         time_created, time_updated, time_valid) VALUES(%s, %s, then_or_now(%s), \
1564                         then_or_now(%s), then_or_now(%s) + INTERVAL '%s days') RETURNING id",
1565                         public_id, private_id, when, when, when, valid)
1566
1567                 if res:
1568                         p = Profile(self.backend, res.id)
1569                         p.log_profile_update()
1570
1571                         return p
1572
1573         # Devices
1574
1575         def create_device(self, subsystem, vendor, model, sub_vendor=None, sub_model=None,
1576                         driver=None, deviceclass=None):
1577                 res = self.db.get("INSERT INTO fireinfo_devices(subsystem, vendor, model, \
1578                                 sub_vendor, sub_model, driver, deviceclass) VALUES(%s, %s, %s, %s, %s, %s, %s) \
1579                                 RETURNING id", subsystem, vendor, model, sub_vendor, sub_model, driver, deviceclass)
1580
1581                 if res:
1582                         return Device(self.backend, res.id)
1583
1584         def get_device(self, subsystem, vendor, model, sub_vendor=None, sub_model=None,
1585                         driver=None, deviceclass=None):
1586                 res = self.db.get("SELECT * FROM fireinfo_devices \
1587                         WHERE subsystem = %s AND vendor = %s AND model = %s \
1588                         AND sub_vendor IS NOT DISTINCT FROM %s \
1589                         AND sub_model IS NOT DISTINCT FROM %s \
1590                         AND driver IS NOT DISTINCT FROM %s \
1591                         AND deviceclass IS NOT DISTINCT FROM %s \
1592                         LIMIT 1", subsystem, vendor, model, sub_vendor,
1593                                 sub_model, driver, deviceclass)
1594
1595                 if res:
1596                         return Device(self.backend, res.id, res)
1597
1598         # System
1599
1600         def create_system(self, vendor, model):
1601                 res = self.db.get("INSERT INTO fireinfo_systems(vendor, model) \
1602                         VALUES(%s, %s) RETURNING id", vendor, model)
1603
1604                 if res:
1605                         return res.id
1606
1607         def get_system(self, vendor, model):
1608                 res = self.db.get("SELECT id FROM fireinfo_systems WHERE vendor IS NOT DISTINCT FROM %s \
1609                         AND model IS NOT DISTINCT FROM %s LIMIT 1", vendor, model)
1610
1611                 if res:
1612                         return res.id
1613
1614         # Processors
1615
1616         def create_processor(self, vendor, model_string, family, model, stepping, core_count, flags=None):
1617                 res = self.db.get("INSERT INTO fireinfo_processors(vendor, model_string, \
1618                         family, model, stepping, core_count, flags) VALUES(%s, %s, %s, %s, %s, %s, %s) \
1619                         RETURNING id", vendor, model_string, family, model, stepping, core_count, flags)
1620
1621                 if res:
1622                         return Processor(self.backend, res.id)
1623
1624         def get_processor_by_id(self, processor_id, **kwargs):
1625                 res = self.db.get("SELECT * FROM fireinfo_processors \
1626                         WHERE id = %s", processor_id)
1627
1628                 if res:
1629                         return Processor(self.backend, res.id, data=res, **kwargs)
1630
1631         def get_processor(self, vendor, model_string, family, model, stepping, core_count, flags=None):
1632                 if flags is None:
1633                         flags = []
1634
1635                 res = self.db.get("SELECT * FROM fireinfo_processors \
1636                         WHERE vendor = %s AND model_string = %s \
1637                         AND family IS NOT DISTINCT FROM %s AND model IS NOT DISTINCT FROM %s \
1638                         AND stepping IS NOT DISTINCT FROM %s AND core_count = %s \
1639                         AND flags <@ %s AND flags @> %s", vendor, model_string, family, model,
1640                         stepping, core_count, flags, flags)
1641
1642                 if res:
1643                         return Processor(self.backend, res.id, res)
1644
1645         # Kernel
1646
1647         def create_kernel(self, kernel):
1648                 res = self.db.get("INSERT INTO fireinfo_kernels(name) VALUES(%s) \
1649                         RETURNING id", kernel)
1650
1651                 if res:
1652                         return res.id
1653
1654         def get_kernel(self, kernel):
1655                 res = self.db.get("SELECT id FROM fireinfo_kernels WHERE name = %s", kernel)
1656
1657                 if res:
1658                         return res.id
1659
1660         # Arch
1661
1662         def create_arch(self, arch):
1663                 res = self.db.get("INSERT INTO fireinfo_arches(name) VALUES(%s) \
1664                         RETURNING id", arch)
1665
1666                 if res:
1667                         return res.id
1668
1669         def get_arch(self, arch):
1670                 res = self.db.get("SELECT id FROM fireinfo_arches WHERE name = %s", arch)
1671
1672                 if res:
1673                         return res.id
1674
1675         # Release
1676
1677         def create_release(self, release):
1678                 res = self.db.get("INSERT INTO fireinfo_releases(name) VALUES(%s) \
1679                         RETURNING id", release)
1680
1681                 if res:
1682                         return res.id
1683
1684         def get_release(self, release):
1685                 res = self.db.get("SELECT id FROM fireinfo_releases WHERE name = %s", release)
1686
1687                 if res:
1688                         return res.id
1689
1690         # Hypervisor
1691
1692         def create_hypervisor(self, hypervisor):
1693                 res = self.db.get("INSERT INTO fireinfo_hypervisors(name) VALUES(%s) \
1694                         RETURNING id", hypervisor)
1695
1696                 if res:
1697                         return res.id
1698
1699         def get_hypervisor(self, hypervisor):
1700                 res = self.db.get("SELECT id FROM fireinfo_hypervisors WHERE name = %s",
1701                         hypervisor)
1702
1703                 if res:
1704                         return res.id
1705
1706         # Handle profile
1707
1708         def handle_profile(self, *args, **kwargs):
1709                 self.db.execute("START TRANSACTION")
1710
1711                 # Wrap all the handling of the profile in a huge transaction.
1712                 try:
1713                         self._handle_profile(*args, **kwargs)
1714
1715                 except:
1716                         self.db.execute("ROLLBACK")
1717                         raise
1718
1719                 else:
1720                         self.db.execute("COMMIT")
1721
1722         def _handle_profile(self, public_id, profile_blob, location=None, when=None):
1723                 private_id = profile_blob.get("private_id", None)
1724                 assert private_id
1725
1726                 # Check if the profile already exists in the database.
1727                 profile = self.fireinfo.get_profile(public_id, private_id=private_id, when=when)
1728
1729                 # Check if the update can actually be updated
1730                 if profile and self.fireinfo.profile_rate_limit_active(public_id, when=when):
1731                         logging.warning("There were too many updates for this profile in the last hour: %s" % public_id)
1732                         return
1733
1734                 elif not self.is_private_id_change_permitted(public_id, private_id, when=when):
1735                         logging.warning("Changing private id is not permitted for profile: %s" % public_id)
1736                         return
1737
1738                 # Parse the profile
1739                 profile_parser = self.parse_profile(public_id, profile_blob)
1740
1741                 # If a profile exists, check if it matches and if so, just update the
1742                 # timestamp.
1743                 if profile:
1744                         # Check if the profile has changed. If so, update the data.
1745                         if profile_parser.equals(profile):
1746                                 profile.updated(profile_parser, location=location, when=when)
1747                                 return
1748
1749                         # If it does not match, we assume that it is expired and
1750                         # create a new profile.
1751                         profile.expired(when=when)
1752
1753                 # Replace the old profile with a new one
1754                 profile = self.fireinfo.create_profile(public_id, private_id, when=when)
1755                 profile.parse(profile_parser)
1756
1757                 if location:
1758                         profile.set_location(location)
1759
1760                 return profile
1761
1762         # Data outputs
1763
1764         def get_random_profile(self, when=None):
1765                 # Check if the architecture exists so that we pick a profile with some data
1766                 res = self.db.get("SELECT public_id FROM fireinfo_profiles \
1767                         LEFT JOIN fireinfo_profiles_arches ON fireinfo_profiles.id = fireinfo_profiles_arches.profile_id \
1768                         WHERE fireinfo_profiles_arches.profile_id IS NOT NULL \
1769                         AND then_or_now(%s) BETWEEN time_created AND time_valid ORDER BY RANDOM() LIMIT 1", when)
1770
1771                 if res:
1772                         return res.public_id
1773
1774         def get_active_profiles(self, when=None):
1775                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_at(%s) AS id) \
1776                         SELECT COUNT(*) AS with_data, (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1777                         LEFT JOIN fireinfo_profiles_releases ON profiles.id = fireinfo_profiles_releases.profile_id \
1778                         WHERE fireinfo_profiles_releases.profile_id IS NOT NULL", when)
1779
1780                 if res:
1781                         return res.with_data, res.count
1782
1783         def get_archive_size(self, when=None):
1784                 res = self.db.get("SELECT COUNT(*) AS count FROM fireinfo_profiles \
1785                         WHERE time_created <= then_or_now(%s)", when)
1786
1787                 if res:
1788                         return res.count
1789
1790         def get_geo_location_map(self, when=None):
1791                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_at(%s) AS id) \
1792                         SELECT location, COUNT(location)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1793                         LEFT JOIN fireinfo_profiles_locations ON profiles.id = fireinfo_profiles_locations.profile_id \
1794                         WHERE fireinfo_profiles_locations.location IS NOT NULL GROUP BY location ORDER BY count DESC", when)
1795
1796                 return ((r.location, r.count) for r in res)
1797
1798         def get_language_map(self, when=None):
1799                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1800                         SELECT language, COUNT(language)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1801                         LEFT JOIN fireinfo_profiles_languages ON profiles.id = fireinfo_profiles_languages.profile_id \
1802                         WHERE fireinfo_profiles_languages.language IS NOT NULL GROUP BY language ORDER BY count DESC", when)
1803
1804                 return ((r.language, r.count) for r in res)
1805
1806         @property
1807         def cpu_vendors(self):
1808                 res = self.db.query("SELECT DISTINCT vendor FROM fireinfo_processors ORDER BY vendor")
1809
1810                 return (CPU_VENDORS.get(r.vendor, r.vendor) for r in res)
1811
1812         def get_cpu_vendors_map(self, when=None):
1813                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1814                         SELECT vendor, COUNT(vendor)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1815                         LEFT JOIN fireinfo_profiles_processors ON profiles.id = fireinfo_profiles_processors.profile_id \
1816                         LEFT JOIN fireinfo_processors ON fireinfo_profiles_processors.processor_id = fireinfo_processors.id \
1817                         WHERE NOT fireinfo_profiles_processors.processor_id IS NULL GROUP BY vendor ORDER BY count DESC", when)
1818
1819                 return ((CPU_VENDORS.get(r.vendor, r.vendor), r.count) for r in res)
1820
1821         def get_cpu_clock_speeds(self, when=None):
1822                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1823                         SELECT AVG(fireinfo_profiles_processors.clock_speed) AS avg, \
1824                         STDDEV(fireinfo_profiles_processors.clock_speed) AS stddev, \
1825                         MIN(fireinfo_profiles_processors.clock_speed) AS min, \
1826                         MAX(fireinfo_profiles_processors.clock_speed) AS max FROM profiles \
1827                         LEFT JOIN fireinfo_profiles_processors ON profiles.id = fireinfo_profiles_processors.profile_id \
1828                         WHERE NOT fireinfo_profiles_processors.processor_id IS NULL \
1829                         AND fireinfo_profiles_processors.clock_speed > 0 \
1830                         AND fireinfo_profiles_processors.clock_speed < fireinfo_profiles_processors.bogomips \
1831                         AND fireinfo_profiles_processors.bogomips <= %s", when, 10000)
1832
1833                 if res:
1834                         return (res.avg or 0, res.stddev or 0, res.min or 0, res.max or 0)
1835
1836         def get_cpus_with_platform_and_flag(self, platform, flag, when=None):
1837                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \
1838                         processors AS (SELECT fireinfo_processors.id AS id, fireinfo_processors.flags AS flags FROM profiles \
1839                         LEFT JOIN fireinfo_profiles_processors ON profiles.id = fireinfo_profiles_processors.profile_id \
1840                         LEFT JOIN fireinfo_processors ON fireinfo_profiles_processors.processor_id = fireinfo_processors.id \
1841                         LEFT JOIN fireinfo_profiles_arches ON profiles.id = fireinfo_profiles_arches.profile_id \
1842                         LEFT JOIN fireinfo_arches ON fireinfo_profiles_arches.arch_id = fireinfo_arches.id \
1843                         WHERE NOT fireinfo_profiles_processors.processor_id IS NULL \
1844                         AND fireinfo_arches.platform = %s AND NOT 'hypervisor' = ANY(fireinfo_processors.flags)) \
1845                         SELECT (COUNT(*)::float / (SELECT NULLIF(COUNT(*), 0) FROM processors)) AS count FROM processors \
1846                         WHERE %s = ANY(processors.flags)", when, platform, flag)
1847
1848                 return res.count or 0
1849
1850         def get_common_cpu_flags_by_platform(self, platform, when=None):
1851                 if platform == "arm":
1852                         flags = (
1853                                 "lpae", "neon", "thumb", "thumb2", "thumbee", "vfpv3", "vfpv4",
1854                         )
1855                 elif platform == "x86":
1856                         flags = (
1857                                 "aes", "avx", "avx2", "lm", "mmx", "mmxext", "nx", "pae",
1858                                 "pni", "popcnt", "sse", "sse2", "rdrand", "ssse3", "sse4a",
1859                                 "sse4_1", "sse4_2"
1860                         )
1861                 else:
1862                         return
1863
1864                 ret = []
1865                 for flag in flags:
1866                         ret.append((flag, self.get_cpus_with_platform_and_flag(platform, flag, when=when)))
1867
1868                 # Add virtual CPU flag "virt" for virtualization support
1869                 if platform == "x86":
1870                         ret.append(("virt",
1871                                 self.get_cpus_with_platform_and_flag(platform, "vmx", when=when) + \
1872                                 self.get_cpus_with_platform_and_flag(platform, "svm", when=when)))
1873
1874                 return sorted(ret, key=lambda x: x[1], reverse=True)
1875
1876         def get_common_memory_amounts(self, when=None):
1877                 amounts = (128, 256, 512, 1024, 2 * 1024, 4 * 1024, 8 * 1024, 16 * 1024, 32 * 1024, 64 * 1024)
1878
1879                 ret = []
1880                 for amount in amounts:
1881                         ret.append((amount, self.get_memory_amount(amount * 1024 * 0.95, when=when)))
1882
1883                 return ret
1884
1885         def get_memory_amount(self, greather_than, when=None):
1886                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1887                         SELECT COUNT(*)::float / (SELECT COUNT(*) FROM profiles \
1888                         LEFT JOIN fireinfo_profiles_memory ON profiles.id = fireinfo_profiles_memory.profile_id \
1889                         WHERE NOT fireinfo_profiles_memory.amount IS NULL) AS percentage FROM profiles \
1890                         LEFT JOIN fireinfo_profiles_memory ON profiles.id = fireinfo_profiles_memory.profile_id \
1891                         WHERE fireinfo_profiles_memory.amount >= %s", when, greather_than)
1892
1893                 if res:
1894                         return res.percentage
1895
1896         def get_memory_amounts(self, when=None):
1897                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1898                         SELECT AVG(fireinfo_profiles_memory.amount) AS avg, \
1899                         STDDEV(fireinfo_profiles_memory.amount) AS stddev, \
1900                         MIN(fireinfo_profiles_memory.amount) AS min, \
1901                         MAX(fireinfo_profiles_memory.amount) AS max FROM profiles \
1902                         LEFT JOIN fireinfo_profiles_memory ON profiles.id = fireinfo_profiles_memory.profile_id", when)
1903
1904                 if res:
1905                         return (res.avg or 0, res.stddev or 0, res.min or 0, res.max or 0)
1906
1907         def get_arch_map(self, when=None):
1908                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1909                         SELECT fireinfo_arches.name AS arch, COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count \
1910                         FROM profiles \
1911                         LEFT JOIN fireinfo_profiles_arches ON profiles.id = fireinfo_profiles_arches.profile_id \
1912                         LEFT JOIN fireinfo_arches ON fireinfo_profiles_arches.arch_id = fireinfo_arches.id \
1913                         WHERE NOT fireinfo_profiles_arches.profile_id IS NULL \
1914                         GROUP BY fireinfo_arches.id ORDER BY count DESC", when)
1915
1916                 return ((r.arch, r.count) for r in res)
1917
1918         # Virtual
1919
1920         def get_hypervisor_map(self, when=None):
1921                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \
1922                         virtual_profiles AS (SELECT profiles.id AS profile_id, fireinfo_profiles_virtual.hypervisor_id FROM profiles \
1923                                 LEFT JOIN fireinfo_profiles_virtual ON profiles.id = fireinfo_profiles_virtual.profile_id \
1924                                 WHERE fireinfo_profiles_virtual.profile_id IS NOT NULL) \
1925                         SELECT COALESCE(fireinfo_hypervisors.name, %s) AS name, \
1926                                 COUNT(*)::float / (SELECT COUNT(*) FROM virtual_profiles) AS count FROM virtual_profiles \
1927                         LEFT JOIN fireinfo_hypervisors ON virtual_profiles.hypervisor_id = fireinfo_hypervisors.id \
1928                         GROUP BY fireinfo_hypervisors.name ORDER BY count DESC", when, "unknown")
1929
1930                 return ((r.name, r.count) for r in res)
1931
1932         def get_virtual_ratio(self, when=None):
1933                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1934                         SELECT COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1935                         LEFT JOIN fireinfo_profiles_virtual ON profiles.id = fireinfo_profiles_virtual.profile_id \
1936                         WHERE fireinfo_profiles_virtual.profile_id IS NOT NULL", when)
1937
1938                 if res:
1939                         return res.count
1940
1941         # Releases
1942
1943         def get_releases_map(self, when=None):
1944                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1945                         SELECT fireinfo_releases.name, COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1946                         LEFT JOIN fireinfo_profiles_releases ON profiles.id = fireinfo_profiles_releases.profile_id \
1947                         LEFT JOIN fireinfo_releases ON fireinfo_profiles_releases.release_id = fireinfo_releases.id \
1948                         GROUP BY fireinfo_releases.name ORDER BY count DESC", when)
1949
1950                 return ((r.name, r.count) for r in res)
1951
1952         def get_kernels_map(self, when=None):
1953                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
1954                         SELECT fireinfo_kernels.name, COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \
1955                         LEFT JOIN fireinfo_profiles_kernels ON profiles.id = fireinfo_profiles_kernels.profile_id \
1956                         LEFT JOIN fireinfo_kernels ON fireinfo_profiles_kernels.kernel_id = fireinfo_kernels.id \
1957                         GROUP BY fireinfo_kernels.name ORDER BY count DESC", when)
1958
1959                 return ((r.name, r.count) for r in res)
1960
1961         def _process_devices(self, devices):
1962                 result = []
1963
1964                 for dev in devices:
1965                         dev = Device(self.backend, dev.get("id", None), dev)
1966                         result.append(dev)
1967
1968                 return result
1969
1970         def get_driver_map(self, driver, when=None):
1971                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \
1972                         devices AS (SELECT * FROM profiles \
1973                                 LEFT JOIN fireinfo_profiles_devices ON profiles.id = fireinfo_profiles_devices.profile_id \
1974                                 LEFT JOIN fireinfo_devices ON fireinfo_profiles_devices.device_id = fireinfo_devices.id \
1975                                 WHERE driver = %s) \
1976                         SELECT subsystem, model, vendor, driver, deviceclass, \
1977                                 COUNT(*)::float / (SELECT COUNT(*) FROM devices) AS percentage FROM devices \
1978                                 GROUP BY subsystem, model, vendor, driver, deviceclass \
1979                                 ORDER BY percentage DESC", when, driver)
1980
1981                 return self._process_devices(res)
1982
1983         subsystem2class = {
1984                 "pci" : hwdata.PCI(),
1985                 "usb" : hwdata.USB(),
1986         }
1987
1988         def get_vendor_string(self, subsystem, vendor_id):
1989                 try:
1990                         cls = self.subsystem2class[subsystem]
1991                 except KeyError:
1992                         return
1993
1994                 return cls.get_vendor(vendor_id)
1995
1996         def get_model_string(self, subsystem, vendor_id, model_id):
1997                 try:
1998                         cls = self.subsystem2class[subsystem]
1999                 except KeyError:
2000                         return
2001
2002                 return cls.get_device(vendor_id, model_id)
2003
2004         def get_vendor_list(self, when=None):
2005                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
2006                         SELECT DISTINCT fireinfo_devices.subsystem AS subsystem, fireinfo_devices.vendor AS vendor FROM profiles \
2007                                 LEFT JOIN fireinfo_profiles_devices ON profiles.id = fireinfo_profiles_devices.profile_id \
2008                                 LEFT JOIN fireinfo_devices ON fireinfo_profiles_devices.device_id = fireinfo_devices.id \
2009                                 WHERE NOT fireinfo_devices.driver = ANY(%s)", when, IGNORED_DEVICES)
2010
2011                 vendors = {}
2012                 for row in res:
2013                         vendor = self.get_vendor_string(row.subsystem, row.vendor)
2014
2015                         # Drop if vendor could not be determined
2016                         if vendor is None:
2017                                 continue
2018
2019                         try:
2020                                 vendors[vendor].append((row.subsystem, row.vendor))
2021                         except KeyError:
2022                                 vendors[vendor] = [(row.subsystem, row.vendor)]
2023
2024                 vendors = vendors.items()
2025                 return sorted(vendors)
2026
2027         def get_devices_by_vendor(self, subsystem, vendor, when=None):
2028                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \
2029                         devices AS (SELECT * FROM profiles \
2030                                 LEFT JOIN fireinfo_profiles_devices ON profiles.id = fireinfo_profiles_devices.profile_id \
2031                                 LEFT JOIN fireinfo_devices ON fireinfo_profiles_devices.device_id = fireinfo_devices.id \
2032                                 WHERE NOT fireinfo_devices.driver = ANY(%s)), \
2033                         vendor_devices AS (SELECT * FROM devices WHERE devices.subsystem = %s AND devices.vendor = %s) \
2034                         SELECT subsystem, model, vendor, driver, deviceclass FROM vendor_devices \
2035                                 GROUP BY subsystem, model, vendor, driver, deviceclass", when, IGNORED_DEVICES, subsystem, vendor)
2036
2037                 return self._process_devices(res)
2038
2039         def get_device_percentage(self, subsystem, vendor, model, when=None):
2040                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \
2041                         devices AS (SELECT * FROM profiles \
2042                                 LEFT JOIN fireinfo_profiles_devices ON profiles.id = fireinfo_profiles_devices.profile_id \
2043                                 LEFT JOIN fireinfo_devices ON fireinfo_profiles_devices.device_id = fireinfo_devices.id) \
2044                         SELECT COUNT(*)::float / (SELECT COUNT(*) FROM devices) AS percentage FROM devices \
2045                                 WHERE devices.subsystem = %s AND devices.vendor = %s AND devices.model = %s",
2046                         when, subsystem, vendor, model)
2047
2048                 if res:
2049                         return res.percentage
2050
2051         def get_device_in_profile(self, subsystem, vendor, model, limit=10, when=None):
2052                 res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \
2053                         profiles_with_device AS (SELECT DISTINCT fireinfo_profiles.public_id FROM profiles \
2054                                 LEFT JOIN fireinfo_profiles ON profiles.id = fireinfo_profiles.id \
2055                                 LEFT JOIN fireinfo_profiles_devices ON profiles.id = fireinfo_profiles_devices.profile_id \
2056                                 LEFT JOIN fireinfo_devices ON fireinfo_profiles_devices.device_id = fireinfo_devices.id \
2057                                 WHERE fireinfo_devices.subsystem = %s AND fireinfo_devices.vendor = %s \
2058                                 AND fireinfo_devices.model = %s) \
2059                         SELECT * FROM profiles_with_device ORDER BY RANDOM() LIMIT %s",
2060                         when, subsystem, vendor, model, limit)
2061
2062                 return (r.public_id for r in res)
2063
2064         def get_network_zones_map(self, when=None):
2065                 res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \
2066                         SELECT COUNT(NULLIF(has_red, FALSE))::float / (SELECT COUNT(*) FROM profiles) AS has_red, \
2067                                 COUNT(NULLIF(has_green, FALSE))::float / (SELECT COUNT(*) FROM profiles) AS has_green, \
2068                                 COUNT(NULLIF(has_orange, FALSE))::float / (SELECT COUNT(*) FROM profiles) AS has_orange, \
2069                                 COUNT(NULLIF(has_blue, FALSE))::float / (SELECT COUNT(*) FROM profiles) AS has_blue FROM profiles \
2070                                 LEFT JOIN fireinfo_profiles_networks ON profiles.id = fireinfo_profiles_networks.profile_id \
2071                                 WHERE fireinfo_profiles_networks.profile_id IS NOT NULL", when)
2072
2073                 return res