src/backend/fireinfo.py

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