src/backend/fireinfo.py

   1 #!/usr/bin/python
   2
   3 import datetime
   4 import iso3166
   5 import json
   6 import jsonschema
   7 import logging
   8 import re
   9
  10 from . import hwdata
  11 from . import util
  12 from .misc import Object
  13 from .decorators import *
  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 PROFILE_SCHEMA = {
  86         "$schema"     : "https://json-schema.org/draft/2020-12/schema",
  87         "$id"         : "https://fireinfo.ipfire.org/profile.schema.json",
  88         "title"       : "Fireinfo Profile",
  89         "description" : "Fireinfo Profile",
  90         "type"        : "object",
  91
  92         # Properties
  93         "properties" : {
  94                 # Processor
  95                 "cpu" : {
  96                         "type" : "object",
  97                         "properties" : {
  98                                 "arch" : {
  99                                         "type"    : "string",
 100                                         "pattern" : r"^[a-z0-9\_]{,8}$",
 101                                 },
 102                                 "count" : {
 103                                         "type" : "integer",
 104                                 },
 105                                 "family" : {
 106                                         "type" : ["integer", "null"],
 107                                 },
 108                                 "flags" : {
 109                                         "type" : "array",
 110                                         "items" : {
 111                                                 "type"    : "string",
 112                                                 "pattern" : r"^.{,24}$",
 113                                         },
 114                                 },
 115                                 "model" : {
 116                                         "type" : "integer",
 117                                 },
 118                                 "model_string" : {
 119                                         "type" : ["string", "null"],
 120                                         "pattern" : r"^.{,80}$",
 121                                 },
 122                                 "speed" : {
 123                                         "type" : "number",
 124                                 },
 125                                 "stepping" : {
 126                                         "type" : "integer",
 127                                 },
 128                                 "vendor" : {
 129                                         "type"    : "string",
 130                                         "pattern" : r"^.{,80}$",
 131                                 },
 132                         },
 133                         "additionalProperties" : False,
 134                         "required" : [
 135                                 "arch",
 136                                 "count",
 137                                 "family",
 138                                 "flags",
 139                                 "model",
 140                                 "model_string",
 141                                 "speed",
 142                                 "stepping",
 143                                 "vendor",
 144                         ],
 145                 },
 146
 147                 # Devices
 148                 "devices" : {
 149                         "type" : "array",
 150                         "items" : {
 151                                 "type" : "object",
 152                                 "properties" : {
 153                                         "deviceclass" : {
 154                                                 "type"    : ["string", "null"],
 155                                                 "pattern" : r"^.{,20}$",
 156                                         },
 157                                         "driver" : {
 158                                                 "type"    : ["string", "null"],
 159                                                 "pattern" : r"^.{,24}$",
 160                                         },
 161                                         "model" : {
 162                                                 "type"    : "string",
 163                                                 "pattern" : r"^[a-z0-9]{4}$",
 164                                         },
 165                                         "sub_model" : {
 166                                                 "type"    : ["string", "null"],
 167                                                 "pattern" : r"^[a-z0-9]{4}$",
 168                                         },
 169                                         "sub_vendor" : {
 170                                                 "type"    : ["string", "null"],
 171                                                 "pattern" : r"^[a-z0-9]{4}$",
 172                                         },
 173                                         "subsystem" : {
 174                                                 "type"    : "string",
 175                                                 "pattern" : r"^[a-z]{3}$",
 176                                         },
 177                                         "vendor" : {
 178                                                 "type"    : "string",
 179                                                 "pattern" : r"^[a-z0-9]{4}$",
 180                                         },
 181                                 },
 182                                 "additionalProperties" : False,
 183                                 "required" : [
 184                                         "deviceclass",
 185                                         "driver",
 186                                         "model",
 187                                         "subsystem",
 188                                         "vendor",
 189                                 ],
 190                         },
 191                 },
 192
 193                 # Network
 194                 "network" : {
 195                         "type" : "object",
 196                         "properties" : {
 197                                 "blue" : {
 198                                         "type" : "boolean",
 199                                 },
 200                                 "green" : {
 201                                         "type" : "boolean",
 202                                 },
 203                                 "orange" : {
 204                                         "type" : "boolean",
 205                                 },
 206                                 "red" : {
 207                                         "type" : "boolean",
 208                                 },
 209                         },
 210                         "additionalProperties" : False,
 211                 },
 212
 213                 # System
 214                 "system" : {
 215                         "type" : "object",
 216                         "properties" : {
 217                                 "kernel_release" : {
 218                                         "type"    : "string",
 219                                         "pattern" : r"^.{,40}$",
 220                                 },
 221                                 "language" : {
 222                                         "type"    : "string",
 223                                         "pattern" : r"^[a-z]{2}(\.utf8)?$",
 224                                 },
 225                                 "memory" : {
 226                                         "type" : "integer",
 227                                 },
 228                                 "model" : {
 229                                         "type"    : ["string", "null"],
 230                                         "pattern" : r"^.{,80}$",
 231                                 },
 232                                 "release" : {
 233                                         "type"    : "string",
 234                                         "pattern" : r"^.{,80}$",
 235                                 },
 236                                 "root_size" : {
 237                                         "type" : "number",
 238                                 },
 239                                 "vendor" : {
 240                                         "type"    : "string",
 241                                         "pattern" : r"^.{,80}$",
 242                                 },
 243                                 "virtual" : {
 244                                         "type" : "boolean"
 245                                 },
 246                         },
 247                         "additionalProperties" : False,
 248                         "required" : [
 249                                 "kernel_release",
 250                                 "language",
 251                                 "memory",
 252                                 "model",
 253                                 "release",
 254                                 "root_size",
 255                                 "vendor",
 256                                 "virtual",
 257                         ],
 258                 },
 259
 260                 # Hypervisor
 261                 "hypervisor" : {
 262                         "type" : "object",
 263                         "properties" : {
 264                                 "vendor" : {
 265                                         "type"    : "string",
 266                                         "pattern" : r"^.{,40}$",
 267                                 },
 268                         },
 269                         "additionalProperties" : False,
 270                         "required" : [
 271                                 "vendor",
 272                         ],
 273                 },
 274
 275                 # Error - BogoMIPS
 276                 "bogomips" : {
 277                         "type" : "number",
 278                 },
 279         },
 280         "additionalProperties" : False,
 281         "required" : [
 282                 "cpu",
 283                 "devices",
 284                 "network",
 285                 "system",
 286         ],
 287 }
 288
 289 class Network(Object):
 290         def init(self, blob):
 291                 self.blob = blob
 292
 293         def __iter__(self):
 294                 ret = []
 295
 296                 for zone in ("red", "green", "orange", "blue"):
 297                         if self.has_zone(zone):
 298                                 ret.append(zone)
 299
 300                 return iter(ret)
 301
 302         def has_zone(self, name):
 303                 return self.blob.get(name, False)
 304
 305         @property
 306         def has_red(self):
 307                 return self.has_zone("red")
 308
 309         @property
 310         def has_green(self):
 311                 return self.has_zone("green")
 312
 313         @property
 314         def has_orange(self):
 315                 return self.has_zone("orange")
 316
 317         @property
 318         def has_blue(self):
 319                 return self.has_zone("blue")
 320
 321
 322 class Processor(Object):
 323         def init(self, blob):
 324                 self.blob = blob
 325
 326         def __str__(self):
 327                 s = []
 328
 329                 if self.model_string and not self.model_string.startswith(self.vendor):
 330                         s.append(self.vendor)
 331                         s.append("-")
 332
 333                 s.append(self.model_string or "Generic")
 334
 335                 if self.core_count > 1:
 336                         s.append("x%s" % self.core_count)
 337
 338                 return " ".join(s)
 339
 340         @property
 341         def arch(self):
 342                 return self.blob.get("arch")
 343
 344         @property
 345         def vendor(self):
 346                 vendor = self.blob.get("vendor")
 347
 348                 try:
 349                         return CPU_VENDORS[vendor]
 350                 except KeyError:
 351                         return vendor
 352
 353         @property
 354         def family(self):
 355                 return self.blob.get("family")
 356
 357         @property
 358         def model(self):
 359                 return self.blob.get("model")
 360
 361         @property
 362         def stepping(self):
 363                 return self.blob.get("stepping")
 364
 365         @property
 366         def model_string(self):
 367                 return self.blob.get("model_string")
 368
 369         @property
 370         def flags(self):
 371                 return self.blob.get("flags")
 372
 373         def has_flag(self, flag):
 374                 return flag in self.flags
 375
 376         def uses_ht(self):
 377                 if self.vendor == "Intel" and self.family == 6 and self.model in (15, 55, 76, 77):
 378                         return False
 379
 380                 return self.has_flag("ht")
 381
 382         @property
 383         def core_count(self):
 384                 return self.blob.get("count", 1)
 385
 386         @property
 387         def count(self):
 388                 if self.uses_ht():
 389                         return self.core_count // 2
 390
 391                 return self.core_count
 392
 393         @property
 394         def clock_speed(self):
 395                 return self.blob.get("speed", 0)
 396
 397         def format_clock_speed(self):
 398                 if not self.clock_speed:
 399                         return
 400
 401                 if self.clock_speed < 1000:
 402                         return "%dMHz" % self.clock_speed
 403
 404                 return "%.2fGHz" % round(self.clock_speed / 1000, 2)
 405
 406         @property
 407         def bogomips(self):
 408                 return self.__bogomips
 409
 410         @property
 411         def capabilities(self):
 412                 caps = [
 413                         ("64bit",  self.has_flag("lm")),
 414                         ("aes",    self.has_flag("aes")),
 415                         ("nx",     self.has_flag("nx")),
 416                         ("pae",    self.has_flag("pae") or self.has_flag("lpae")),
 417                         ("rdrand", self.has_flag("rdrand")),
 418                 ]
 419
 420                 # If the system is already running in a virtual environment,
 421                 # we cannot correctly detect if the CPU supports svm or vmx
 422                 if self.has_flag("hypervisor"):
 423                         caps.append(("virt", None))
 424                 else:
 425                         caps.append(("virt", self.has_flag("vmx") or self.has_flag("svm")))
 426
 427                 return caps
 428
 429         def format_model(self):
 430                 s = self.model_string or ""
 431
 432                 # Remove everything after the @: Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz
 433                 s, sep, rest = s.partition("@")
 434
 435                 for pattern, repl in CPU_STRINGS:
 436                         if re.match(pattern, s) is None:
 437                                 continue
 438
 439                         s = re.sub(pattern, repl, s)
 440                         break
 441
 442                 # Otherwise remove the symbols
 443                 for i in ("C", "R", "TM", "tm"):
 444                         s = s.replace("(%s)" % i, "")
 445
 446                 # Replace too long strings with shorter ones
 447                 pairs = (
 448                         ("Quad-Core Processor", ""),
 449                         ("Dual-Core Processor", ""),
 450                         ("Processor", "CPU"),
 451                         ("processor", "CPU"),
 452                 )
 453                 for k, v in pairs:
 454                         s = s.replace(k, v)
 455
 456                 # Remove too many spaces
 457                 s = " ".join((e for e in s.split() if e))
 458
 459                 return s
 460
 461         @property
 462         def friendly_string(self):
 463                 s = []
 464
 465                 model = self.format_model()
 466                 if model:
 467                         s.append(model)
 468
 469                 clock_speed = self.format_clock_speed()
 470                 if clock_speed:
 471                         s.append("@ %s" % clock_speed)
 472
 473                 if self.count > 1:
 474                         s.append("x%s" % self.count)
 475
 476                 return " ".join(s)
 477
 478
 479 class Device(Object):
 480         classid2name = {
 481                 "pci" : {
 482                         "00" : N_("Unclassified"),
 483                         "01" : N_("Mass storage"),
 484                         "02" : N_("Network"),
 485                         "03" : N_("Display"),
 486                         "04" : N_("Multimedia"),
 487                         "05" : N_("Memory controller"),
 488                         "06" : N_("Bridge"),
 489                         "07" : N_("Communication"),
 490                         "08" : N_("Generic system peripheral"),
 491                         "09" : N_("Input device"),
 492                         "0a" : N_("Docking station"),
 493                         "0b" : N_("Processor"),
 494                         "0c" : N_("Serial bus"),
 495                         "0d" : N_("Wireless"),
 496                         "0e" : N_("Intelligent controller"),
 497                         "0f" : N_("Satellite communications controller"),
 498                         "10" : N_("Encryption"),
 499                         "11" : N_("Signal processing controller"),
 500                         "ff" : N_("Unassigned class"),
 501                 },
 502
 503                 "usb" : {
 504                         "00" : N_("Unclassified"),
 505                         "01" : N_("Multimedia"),
 506                         "02" : N_("Communication"),
 507                         "03" : N_("Input device"),
 508                         "05" : N_("Generic system peripheral"),
 509                         "06" : N_("Image"),
 510                         "07" : N_("Printer"),
 511                         "08" : N_("Mass storage"),
 512                         "09" : N_("Hub"),
 513                         "0a" : N_("Communication"),
 514                         "0b" : N_("Smart card"),
 515                         "0d" : N_("Encryption"),
 516                         "0e" : N_("Display"),
 517                         "0f" : N_("Personal Healthcare"),
 518                         "dc" : N_("Diagnostic Device"),
 519                         "e0" : N_("Wireless"),
 520                         "ef" : N_("Unclassified"),
 521                         "fe" : N_("Unclassified"),
 522                         "ff" : N_("Unclassified"),
 523                 }
 524         }
 525
 526         def init(self, blob):
 527                 self.blob = blob
 528
 529         def __repr__(self):
 530                 return "<%s vendor=%s model=%s>" % (self.__class__.__name__,
 531                         self.vendor_string, self.model_string)
 532
 533         def __eq__(self, other):
 534                 if isinstance(other, self.__class__):
 535                         return self.blob == other.blob
 536
 537                 return NotImplemented
 538
 539         def __lt__(self, other):
 540                 if isinstance(other, self.__class__):
 541                         return self.cls < other.cls or \
 542                                 self.vendor_string < other.vendor_string or \
 543                                 self.vendor < other.vendor or \
 544                                 self.model_string < other.model_string or \
 545                                 self.model < other.model
 546
 547                 return NotImplemented
 548
 549         def is_showable(self):
 550                 if self.driver in ("usb", "pcieport", "hub"):
 551                         return False
 552
 553                 return True
 554
 555         @property
 556         def subsystem(self):
 557                 return self.blob.get("subsystem")
 558
 559         @property
 560         def model(self):
 561                 return self.blob.get("model")
 562
 563         @lazy_property
 564         def model_string(self):
 565                 return self.fireinfo.get_model_string(self.subsystem, self.vendor, self.model)
 566
 567         @property
 568         def vendor(self):
 569                 return self.blob.get("vendor")
 570
 571         @lazy_property
 572         def vendor_string(self):
 573                 return self.fireinfo.get_vendor_string(self.subsystem, self.vendor)
 574
 575         @property
 576         def driver(self):
 577                 return self.blob.get("driver")
 578
 579         @lazy_property
 580         def cls(self):
 581                 classid = self.blob.get("deviceclass")
 582
 583                 if self.subsystem == "pci":
 584                         classid = classid[:-4]
 585                         if len(classid) == 1:
 586                                 classid = "0%s" % classid
 587
 588                 elif self.subsystem == "usb" and classid:
 589                         classid = classid.split("/")[0]
 590                         classid = "%02x" % int(classid)
 591
 592                 try:
 593                         return self.classid2name[self.subsystem][classid]
 594                 except KeyError:
 595                         return "N/A"
 596
 597
 598 class System(Object):
 599         def init(self, blob):
 600                 self.blob = blob
 601
 602         @property
 603         def language(self):
 604                 return self.blob.get("language")
 605
 606         @property
 607         def vendor(self):
 608                 return self.blob.get("vendor")
 609
 610         @property
 611         def model(self):
 612                 return self.blob.get("model")
 613
 614         @property
 615         def release(self):
 616                 return self.blob.get("release")
 617
 618         @property
 619         def storage(self):
 620                 return self.blob.get("storage_size", 0)
 621
 622         def is_virtual(self):
 623                 return self.blob.get("virtual", False)
 624
 625
 626 class Hypervisor(Object):
 627         def init(self, blob):
 628                 self.blob = blob
 629
 630         def __str__(self):
 631                 return self.vendor
 632
 633         @property
 634         def vendor(self):
 635                 return self.blob.get("vendor")
 636
 637
 638 class Profile(Object):
 639         def init(self, profile_id, private_id, created_at, expired_at, version, blob,
 640                         last_updated_at, country_code, **kwargs):
 641                 self.profile_id      = profile_id
 642                 self.private_id      = private_id
 643                 self.created_at      = created_at
 644                 self.expired_at      = expired_at
 645                 self.version         = version
 646                 self.blob            = blob
 647                 self.last_updated_at = last_updated_at
 648                 self.country_code    = country_code
 649
 650         def __repr__(self):
 651                 return "<%s %s>" % (self.__class__.__name__, self.profile_id)
 652
 653         def is_showable(self):
 654                 return True if self.blob else False
 655
 656         @property
 657         def public_id(self):
 658                 """
 659                         An alias for the profile ID
 660                 """
 661                 return self.profile_id
 662
 663         # Location
 664
 665         @property
 666         def location(self):
 667                 return self.country_code
 668
 669         @property
 670         def location_string(self):
 671                 return self.backend.get_country_name(self.location) or self.location
 672
 673         # Devices
 674
 675         @lazy_property
 676         def devices(self):
 677                 return [Device(self.backend, blob) for blob in self.blob.get("devices", [])]
 678
 679         # System
 680
 681         @lazy_property
 682         def system(self):
 683                 return System(self.backend, self.blob.get("system", {}))
 684
 685         # Processor
 686
 687         @property
 688         def processor(self):
 689                 return Processor(self.backend, self.blob.get("cpu", {}))
 690
 691         # Memory
 692
 693         @property
 694         def memory(self):
 695                 return self.blob.get("memory")
 696
 697         @property
 698         def friendly_memory(self):
 699                 return util.format_size(self.memory or 0)
 700
 701         # Virtual
 702
 703         def is_virtual(self):
 704                 return self.system.is_virtual()
 705
 706         @property
 707         def hypervisor(self):
 708                 return Hypervisor(self.backend, self.blob.get("hypervisor"))
 709
 710         # Network
 711
 712         @lazy_property
 713         def network(self):
 714                 return Network(self.backend, self.blob.get("network", {}))
 715
 716
 717 class Fireinfo(Object):
 718         async def expire(self):
 719                 """
 720                         Called to expire any profiles that have not been updated in a fortnight
 721                 """
 722                 self.db.execute("UPDATE fireinfo SET expired_at = CURRENT_TIMESTAMP \
 723                         WHERE last_updated_at <= CURRENT_TIMESTAMP - %s", datetime.timedelta(days=14))
 724
 725         def _get_profile(self, query, *args, **kwargs):
 726                 res = self.db.get(query, *args, **kwargs)
 727
 728                 if res:
 729                         return Profile(self.backend, **res)
 730
 731         def get_profile_count(self, when=None):
 732                 if when:
 733                         res = self.db.get("""
 734                                 SELECT
 735                                         COUNT(*) AS count
 736                                 FROM
 737                                         fireinfo
 738                                 WHERE
 739                                         created_at <= %s
 740                                 AND
 741                                         (
 742                                                 expired_at IS NULL
 743                                         OR
 744                                                 expired_at > %s
 745                                         )
 746                         """)
 747                 else:
 748                         res = self.db.get("""
 749                                 SELECT
 750                                         COUNT(*) AS count
 751                                 FROM
 752                                         fireinfo
 753                                 WHERE
 754                                         expired_at IS NULL
 755                                 """,
 756                         )
 757
 758                 return res.count if res else 0
 759
 760         def get_profile_histogram(self):
 761                 today = datetime.date.today()
 762
 763                 t1 = datetime.date(year=today.year - 10, month=today.month, day=1)
 764                 t2 = datetime.date(year=today.year, month=today.month, day=1)
 765
 766                 res = self.db.query("""
 767                         SELECT
 768                                 date,
 769                                 COUNT(*) AS count
 770                         FROM
 771                                 generate_series(%s, %s, INTERVAL '1 month') date
 772                         JOIN
 773                                 fireinfo ON date >= created_at
 774                                         AND (expired_at IS NULL OR expired_at > date)
 775                         GROUP BY
 776                                 date
 777                 """, t1, t2)
 778
 779                 return { row.date : row.count for row in res }
 780
 781         # Profiles
 782
 783         def get_profile(self, profile_id, when=None):
 784                 if when:
 785                         return self._get_profile("""
 786                                 SELECT
 787                                         *
 788                                 FROM
 789                                         fireinfo
 790                                 WHERE
 791                                         profile_id = %s
 792                                 AND
 793                                         %s BETWEEN created_at AND expired_at
 794                                 """, profile_id,
 795                         )
 796
 797                 return self._get_profile("""
 798                         SELECT
 799                                 *
 800                         FROM
 801                                 fireinfo
 802                         WHERE
 803                                 profile_id = %s
 804                         AND
 805                                 expired_at IS NULL
 806                         """, profile_id,
 807                 )
 808
 809         # Handle profile
 810
 811         def handle_profile(self, profile_id, blob, country_code=None, asn=None, when=None):
 812                 private_id = blob.get("private_id", None)
 813                 assert private_id
 814
 815                 now = datetime.datetime.utcnow()
 816
 817                 # Fetch the profile version
 818                 version = blob.get("profile_version")
 819
 820                 # Extract the profile
 821                 profile = blob.get("profile")
 822
 823                 if profile:
 824                         # Validate the profile
 825                         self._validate(profile_id, version, profile)
 826
 827                         # Pre-process the profile
 828                         profile = self._preprocess(profile)
 829
 830                 # Fetch the previous profile
 831                 prev = self.get_profile(profile_id)
 832
 833                 if prev:
 834                         # Check if the private ID matches
 835                         if not prev.private_id == private_id:
 836                                 logging.error("Private ID for profile %s does not match" % profile_id)
 837                                 return False
 838
 839                         # Check when the last update was
 840                         elif now - prev.last_updated_at < datetime.timedelta(hours=6):
 841                                 logging.warning("Profile %s has been updated too soon" % profile_id)
 842                                 return False
 843
 844                         # Check if the profile has changed
 845                         elif prev.version == version and prev.blob == blob:
 846                                 logging.debug("Profile %s has not changed" % profile_id)
 847
 848                                 # Update the timestamp
 849                                 self.db.execute("UPDATE fireinfo SET last_updated_at = CURRENT_TIMESTAMP \
 850                                         WHERE profile_id = %s AND expired_at IS NULL", profile_id)
 851
 852                                 return True
 853
 854                         # Delete the previous profile
 855                         self.db.execute("UPDATE fireinfo SET expired_at = CURRENT_TIMESTAMP \
 856                                 WHERE profile_id = %s AND expired_at IS NULL", profile_id)
 857
 858                 # Serialise the profile
 859                 if profile:
 860                         profile = json.dumps(profile)
 861
 862                 # Store the new profile
 863                 self.db.execute("""
 864                         INSERT INTO
 865                                 fireinfo
 866                         (
 867                                 profile_id,
 868                                 private_id,
 869                                 version,
 870                                 blob,
 871                                 country_code,
 872                                 asn
 873                         )
 874                         VALUES
 875                         (
 876                                 %s,
 877                                 %s,
 878                                 %s,
 879                                 %s,
 880                                 %s,
 881                                 %s
 882                         )
 883                         """, profile_id, private_id, version, profile, country_code, asn,
 884                 )
 885
 886         def _validate(self, profile_id, version, blob):
 887                 """
 888                         Validate the profile
 889                 """
 890                 if not version == 0:
 891                         raise ValueError("Unsupported profile version")
 892
 893                 # Validate the blob
 894                 try:
 895                         return jsonschema.validate(blob, schema=PROFILE_SCHEMA)
 896
 897                 # Raise a ValueError instead which is easier to handle later on
 898                 except jsonschema.exceptions.ValidationError as e:
 899                         raise ValueError("%s" % e) from e
 900
 901         def _preprocess(self, blob):
 902                 """
 903                         Modifies the profile before storing it
 904                 """
 905                 # Remove the architecture from the release string
 906                 blob["system"]["release"]= self._filter_release(blob["system"]["release"])
 907
 908                 return blob
 909
 910         def _filter_release(self, release):
 911                 """
 912                         Removes the arch part
 913                 """
 914                 r = [e for e in release.split() if e]
 915
 916                 for s in ("(x86_64)", "(aarch64)", "(i586)", "(armv6l)", "(armv5tel)", "(riscv64)"):
 917                         try:
 918                                 r.remove(s)
 919                                 break
 920                         except ValueError:
 921                                 pass
 922
 923                 return " ".join(r)
 924
 925         # Data outputs
 926
 927         def get_random_profile(self, when=None):
 928                 if when:
 929                         return self._get_profile("""
 930                                 SELECT
 931                                         *
 932                                 FROM
 933                                         fireinfo
 934                                 WHERE
 935                                         created_at <= %s
 936                                 AND
 937                                         (
 938                                                 expired_at IS NULL
 939                                         OR
 940                                                 expired_at > %s
 941                                         )
 942                                 AND
 943                                         blob IS NOT NULL
 944                                 ORDER BY
 945                                         RANDOM()
 946                                 LIMIT
 947                                         1
 948                                 """, when, when,
 949                         )
 950
 951                 return self._get_profile("""
 952                         SELECT
 953                                 *
 954                         FROM
 955                                 fireinfo
 956                         WHERE
 957                                 expired_at IS NULL
 958                         AND
 959                                 blob IS NOT NULL
 960                         ORDER BY
 961                                 RANDOM()
 962                         LIMIT
 963                                 1
 964                 """)
 965
 966         def get_active_profiles(self, when=None):
 967                 if when:
 968                         raise NotImplementedError
 969
 970                 else:
 971                         res = self.db.get("""
 972                                 SELECT
 973                                         COUNT(*) AS total_profiles,
 974                                         COUNT(*) FILTER (WHERE blob IS NOT NULL) AS active_profiles
 975                                 FROM
 976                                         fireinfo
 977                                 WHERE
 978                                         expired_at IS NULL
 979                         """)
 980
 981                 if res:
 982                         return res.active_profiles, res.total_profiles
 983
 984         def get_geo_location_map(self, when=None):
 985                 if when:
 986                         res = self.db.query("""
 987                                 SELECT
 988                                         country_code,
 989                                         fireinfo_percentage(
 990                                                 COUNT(*), SUM(COUNT(*)) OVER ()
 991                                         ) AS p
 992                                 FROM
 993                                         fireinfo
 994                                 WHERE
 995                                         created_at <= %s
 996                                 AND
 997                                         (
 998                                                 expired_at IS NULL
 999                                         OR
1000                                                 expired_at > %s
1001                                         )
1002                                 AND
1003                                         country_code IS NOT NULL
1004                                 GROUP BY
1005                                         country_code
1006                         """, when, when)
1007                 else:
1008                         res = self.db.query("""
1009                                 SELECT
1010                                         country_code,
1011                                         fireinfo_percentage(
1012                                                 COUNT(*), SUM(COUNT(*)) OVER ()
1013                                         ) AS p
1014                                 FROM
1015                                         fireinfo
1016                                 WHERE
1017                                         expired_at IS NULL
1018                                 AND
1019                                         country_code IS NOT NULL
1020                                 GROUP BY
1021                                         country_code
1022                         """)
1023
1024                 return { row.country_code : row.p for row in res }
1025
1026         def get_asn_map(self, when=None):
1027                 if when:
1028                         res = self.db.query("""
1029                                 SELECT
1030                                         asn,
1031                                         fireinfo_percentage(
1032                                                 COUNT(*), SUM(COUNT(*)) OVER ()
1033                                         ) AS p,
1034                                         COUNT(*) AS c
1035                                 FROM
1036                                         fireinfo
1037                                 WHERE
1038                                         created_at <= %s
1039                                 AND
1040                                         (
1041                                                 expired_at IS NULL
1042                                         OR
1043                                                 expired_at > %s
1044                                         )
1045                                 AND
1046                                         asn IS NOT NULL
1047                                 GROUP BY
1048                                         asn
1049                         """, when, when)
1050                 else:
1051                         res = self.db.query("""
1052                                 SELECT
1053                                         asn,
1054                                         fireinfo_percentage(
1055                                                 COUNT(*), SUM(COUNT(*)) OVER ()
1056                                         ) AS p,
1057                                         COUNT(*) AS c
1058                                 FROM
1059                                         fireinfo
1060                                 WHERE
1061                                         expired_at IS NULL
1062                                 AND
1063                                         asn IS NOT NULL
1064                                 GROUP BY
1065                                         asn
1066                         """)
1067
1068                 return { self.backend.location.get_as(row.asn) : (row.c, row.p) for row in res }
1069
1070         @property
1071         def cpu_vendors(self):
1072                 res = self.db.query("""
1073                         SELECT DISTINCT
1074                                 blob->'cpu'->'vendor' AS vendor
1075                         FROM
1076                                 fireinfo
1077                         WHERE
1078                                 blob->'cpu'->'vendor' IS NOT NULL
1079                         """,
1080                 )
1081
1082                 return sorted((CPU_VENDORS.get(row.vendor, row.vendor) for row in res))
1083
1084         def get_cpu_vendors_map(self, when=None):
1085                 if when:
1086                         raise NotImplementedError
1087
1088                 else:
1089                         res = self.db.query("""
1090                                 SELECT
1091                                         blob->'cpu'->'vendor' AS vendor,
1092                                         fireinfo_percentage(
1093                                                 COUNT(*), SUM(COUNT(*)) OVER ()
1094                                         ) AS p
1095                                 FROM
1096                                         fireinfo
1097                                 WHERE
1098                                         expired_at IS NULL
1099                                 AND
1100                                         blob IS NOT NULL
1101                                 AND
1102                                         blob->'cpu'->'vendor' IS NOT NULL
1103                                 GROUP BY
1104                                         blob->'cpu'->'vendor'
1105                         """)
1106
1107                 return { CPU_VENDORS.get(row.vendor, row.vendor) : row.p for row in res }
1108
1109         def get_cpu_flags_map(self, when=None):
1110                 if when:
1111                         raise NotImplementedError
1112
1113                 else:
1114                         res = self.db.query("""
1115                                 WITH arch_flags AS (
1116                                         SELECT
1117                                                 ROW_NUMBER() OVER (PARTITION BY blob->'cpu'->'arch') AS id,
1118                                                 blob->'cpu'->'arch' AS arch,
1119                                                 blob->'cpu'->'flags' AS flags
1120                                         FROM
1121                                                 fireinfo
1122                                         WHERE
1123                                                 expired_at IS NULL
1124                                         AND
1125                                                 blob->'cpu'->'arch' IS NOT NULL
1126                                         AND
1127                                                 blob->'cpu'->'flags' IS NOT NULL
1128
1129                                         -- Filter out virtual systems
1130                                         AND
1131                                                 CAST((blob->'system'->'virtual') AS boolean) IS FALSE
1132                                 )
1133
1134                                 SELECT
1135                                         arch,
1136                                         flag,
1137                                         fireinfo_percentage(
1138                                                 COUNT(*),
1139                                                 (
1140                                                         SELECT
1141                                                                 MAX(id)
1142                                                         FROM
1143                                                                 arch_flags __arch_flags
1144                                                         WHERE
1145                                                                 arch_flags.arch = __arch_flags.arch
1146                                                 )
1147                                         ) AS p
1148                                 FROM
1149                                         arch_flags, jsonb_array_elements(arch_flags.flags) AS flag
1150                                 GROUP BY
1151                                         arch, flag
1152                         """)
1153
1154                 result = {}
1155
1156                 for row in res:
1157                         try:
1158                                 result[row.arch][row.flag] = row.p
1159                         except KeyError:
1160                                 result[row.arch] = { row.flag : row.p }
1161
1162                 return result
1163
1164         def get_average_memory_amount(self, when=None):
1165                 if when:
1166                         res = self.db.get("""
1167                                 SELECT
1168                                         AVG(
1169                                                 CAST(blob->'system'->'memory' AS numeric)
1170                                         ) AS memory
1171                                 FROM
1172                                         fireinfo
1173                                 WHERE
1174                                         created_at <= %s
1175                                 AND
1176                                         (
1177                                                 expired_at IS NULL
1178                                         OR
1179                                                 expired_at > %s
1180                                         )
1181                         """, when)
1182                 else:
1183                         res = self.db.get("""
1184                                 SELECT
1185                                         AVG(
1186                                                 CAST(blob->'system'->'memory' AS numeric)
1187                                         ) AS memory
1188                                 FROM
1189                                         fireinfo
1190                                 WHERE
1191                                         expired_at IS NULL
1192                         """,)
1193
1194                 return res.memory if res else 0
1195
1196         def get_arch_map(self, when=None):
1197                 if when:
1198                         raise NotImplementedError
1199
1200                 else:
1201                         res = self.db.query("""
1202                                 SELECT
1203                                         blob->'cpu'->'arch' AS arch,
1204                                         fireinfo_percentage(
1205                                                 COUNT(*), SUM(COUNT(*)) OVER ()
1206                                         ) AS p
1207                                 FROM
1208                                         fireinfo
1209                                 WHERE
1210                                         expired_at IS NULL
1211                                 AND
1212                                         blob->'cpu'->'arch' IS NOT NULL
1213                                 GROUP BY
1214                                         blob->'cpu'->'arch'
1215                         """)
1216
1217                 return { row.arch : row.p for row in res }
1218
1219         # Virtual
1220
1221         def get_hypervisor_map(self, when=None):
1222                 if when:
1223                         raise NotImplementedError
1224                 else:
1225                         res = self.db.query("""
1226                                 SELECT
1227                                         blob->'hypervisor'->'vendor' AS vendor,
1228                                         fireinfo_percentage(
1229                                                 COUNT(*), SUM(COUNT(*)) OVER ()
1230                                         ) AS p
1231                                 FROM
1232                                         fireinfo
1233                                 WHERE
1234                                         expired_at IS NULL
1235                                 AND
1236                                         CAST((blob->'system'->'virtual') AS boolean) IS TRUE
1237                                 AND
1238                                         blob->'hypervisor'->'vendor' IS NOT NULL
1239                                 GROUP BY
1240                                         blob->'hypervisor'->'vendor'
1241                         """)
1242
1243                 return { row.vendor : row.p for row in res }
1244
1245         def get_virtual_ratio(self, when=None):
1246                 if when:
1247                         raise NotImplementedError
1248
1249                 else:
1250                         res = self.db.get("""
1251                                 SELECT
1252                                         fireinfo_percentage(
1253                                                 COUNT(*) FILTER (
1254                                                         WHERE CAST((blob->'system'->'virtual') AS boolean) IS TRUE
1255                                                 ),
1256                                                 COUNT(*)
1257                                         ) AS p
1258                                 FROM
1259                                         fireinfo
1260                                 WHERE
1261                                         expired_at IS NULL
1262                                 AND
1263                                         blob IS NOT NULL
1264                         """)
1265
1266                 return res.p if res else 0
1267
1268         # Releases
1269
1270         def get_releases_map(self, when=None):
1271                 if when:
1272                         raise NotImplementedError
1273
1274                 else:
1275                         res = self.db.query("""
1276                                 SELECT
1277                                         blob->'system'->'release' AS release,
1278                                         fireinfo_percentage(
1279                                                 COUNT(*), SUM(COUNT(*)) OVER ()
1280                                         ) AS p
1281                                 FROM
1282                                         fireinfo
1283                                 WHERE
1284                                         expired_at IS NULL
1285                                 AND
1286                                         blob IS NOT NULL
1287                                 AND
1288                                         blob->'system'->'release' IS NOT NULL
1289                                 GROUP BY
1290                                         blob->'system'->'release'
1291                         """)
1292
1293                 return { row.release : row.p for row in res }
1294
1295         # Kernels
1296
1297         def get_kernels_map(self, when=None):
1298                 if when:
1299                         raise NotImplementedError
1300
1301                 else:
1302                         res = self.db.query("""
1303                                 SELECT
1304                                         blob->'system'->'kernel' AS kernel,
1305                                         fireinfo_percentage(
1306                                                 COUNT(*), SUM(COUNT(*)) OVER ()
1307                                         ) AS p
1308                                 FROM
1309                                         fireinfo
1310                                 WHERE
1311                                         expired_at IS NULL
1312                                 AND
1313                                         blob IS NOT NULL
1314                                 AND
1315                                         blob->'system'->'kernel' IS NOT NULL
1316                                 GROUP BY
1317                                         blob->'system'->'kernel'
1318                         """)
1319
1320                 return { row.kernel : row.p for row in res }
1321
1322         subsystem2class = {
1323                 "pci" : hwdata.PCI(),
1324                 "usb" : hwdata.USB(),
1325         }
1326
1327         def get_vendor_string(self, subsystem, vendor_id):
1328                 try:
1329                         cls = self.subsystem2class[subsystem]
1330                 except KeyError:
1331                         return ""
1332
1333                 return cls.get_vendor(vendor_id) or ""
1334
1335         def get_model_string(self, subsystem, vendor_id, model_id):
1336                 try:
1337                         cls = self.subsystem2class[subsystem]
1338                 except KeyError:
1339                         return ""
1340
1341                 return cls.get_device(vendor_id, model_id) or ""
1342
1343         def get_vendor_list(self, when=None):
1344                 if when:
1345                         raise NotImplementedError
1346
1347                 else:
1348                         res = self.db.query("""
1349                                 WITH devices AS (
1350                                         SELECT
1351                                                 jsonb_array_elements(blob->'devices') AS device
1352                                         FROM
1353                                                 fireinfo
1354                                         WHERE
1355                                                 expired_at IS NULL
1356                                         AND
1357                                                 blob IS NOT NULL
1358                                         AND
1359                                                 blob->'devices' IS NOT NULL
1360                                         AND
1361                                                 jsonb_typeof(blob->'devices') = 'array'
1362                                 )
1363
1364                                 SELECT
1365                                         devices.device->'subsystem' AS subsystem,
1366                                         devices.device->'vendor' AS vendor
1367                                 FROM
1368                                         devices
1369                                 WHERE
1370                                         devices.device->'subsystem' IS NOT NULL
1371                                 AND
1372                                         devices.device->'vendor' IS NOT NULL
1373                                 AND
1374                                         NOT devices.device->>'driver' = 'usb'
1375                                 GROUP BY
1376                                         subsystem, vendor
1377                         """)
1378
1379                 vendors = {}
1380
1381                 for row in res:
1382                         vendor = self.get_vendor_string(row.subsystem, row.vendor) or row.vendor
1383
1384                         # Drop if vendor could not be determined
1385                         if vendor is None:
1386                                 continue
1387
1388                         try:
1389                                 vendors[vendor].append((row.subsystem, row.vendor))
1390                         except KeyError:
1391                                 vendors[vendor] = [(row.subsystem, row.vendor)]
1392
1393                 return vendors
1394
1395         def _get_devices(self, query, *args, **kwargs):
1396                 res = self.db.query(query, *args, **kwargs)
1397
1398                 return [Device(self.backend, blob) for blob in res]
1399
1400         def get_devices_by_vendor(self, subsystem, vendor, when=None):
1401                 if when:
1402                         raise NotImplementedError
1403
1404                 else:
1405                         return self._get_devices("""
1406                                 WITH devices AS (
1407                                         SELECT
1408                                                 jsonb_array_elements(blob->'devices') AS device
1409                                         FROM
1410                                                 fireinfo
1411                                         WHERE
1412                                                 expired_at IS NULL
1413                                         AND
1414                                                 blob IS NOT NULL
1415                                         AND
1416                                                 blob->'devices' IS NOT NULL
1417                                         AND
1418                                                 jsonb_typeof(blob->'devices') = 'array'
1419                                 )
1420
1421                                 SELECT
1422                                         device.deviceclass,
1423                                         device.subsystem,
1424                                         device.vendor,
1425                                         device.model,
1426                                         device.driver
1427                                 FROM
1428                                         devices,
1429                                         jsonb_to_record(devices.device) AS device(
1430                                                 deviceclass text,
1431                                                 subsystem   text,
1432                                                 vendor      text,
1433                                                 sub_vendor  text,
1434                                                 model       text,
1435                                                 sub_model   text,
1436                                                 driver      text
1437                                         )
1438                                 WHERE
1439                                         devices.device->>'subsystem' = %s
1440                                 AND
1441                                         devices.device->>'vendor' = %s
1442                                 AND
1443                                         NOT devices.device->>'driver' = 'usb'
1444                                 GROUP BY
1445                                         device.deviceclass,
1446                                         device.subsystem,
1447                                         device.vendor,
1448                                         device.model,
1449                                         device.driver
1450                                 """, subsystem, vendor,
1451                         )
1452
1453         def get_devices_by_driver(self, driver, when=None):
1454                 if when:
1455                         raise NotImplementedError
1456
1457                 else:
1458                         return self._get_devices("""
1459                                 WITH devices AS (
1460                                         SELECT
1461                                                 jsonb_array_elements(blob->'devices') AS device
1462                                         FROM
1463                                                 fireinfo
1464                                         WHERE
1465                                                 expired_at IS NULL
1466                                         AND
1467                                                 blob IS NOT NULL
1468                                         AND
1469                                                 blob->'devices' IS NOT NULL
1470                                         AND
1471                                                 jsonb_typeof(blob->'devices') = 'array'
1472                                 )
1473
1474                                 SELECT
1475                                         device.deviceclass,
1476                                         device.subsystem,
1477                                         device.vendor,
1478                                         device.model,
1479                                         device.driver
1480                                 FROM
1481                                         devices,
1482                                         jsonb_to_record(devices.device) AS device(
1483                                                 deviceclass text,
1484                                                 subsystem   text,
1485                                                 vendor      text,
1486                                                 sub_vendor  text,
1487                                                 model       text,
1488                                                 sub_model   text,
1489                                                 driver      text
1490                                         )
1491                                 WHERE
1492                                         devices.device->>'driver' = '%s'
1493                                 GROUP BY
1494                                         device.deviceclass,
1495                                         device.subsystem,
1496                                         device.vendor,
1497                                         device.model,
1498                                         device.driver
1499                                 """, driver,
1500                         )