webapp/backend/fireinfo.py

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