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