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