From 278a2971f5bef80ae990613c5297b16eea126d35 Mon Sep 17 00:00:00 2001 From: Michael Tremer Date: Tue, 24 Oct 2023 09:29:29 +0000 Subject: [PATCH] fireinfo: Refactor all the things Signed-off-by: Michael Tremer --- migrate.sql | 188 ++ src/backend/fireinfo.py | 2406 +++++++++--------------- src/backend/releases.py | 19 +- src/templates/fireinfo/admin.html | 24 +- src/templates/fireinfo/driver.html | 2 +- src/templates/fireinfo/index.html | 50 +- src/templates/fireinfo/processors.html | 24 +- src/templates/fireinfo/profile.html | 46 +- src/templates/fireinfo/releases.html | 8 +- src/templates/fireinfo/vendors.html | 16 +- src/web/fireinfo.py | 140 +- src/web/iuse.py | 2 +- 12 files changed, 1223 insertions(+), 1702 deletions(-) create mode 100644 migrate.sql diff --git a/migrate.sql b/migrate.sql new file mode 100644 index 00000000..78a41788 --- /dev/null +++ b/migrate.sql @@ -0,0 +1,188 @@ +START TRANSACTION; + +-- CREATE INDEX fireinfo_search ON fireinfo USING gin (blob) WHERE expired_at IS NULL; +-- CREATE UNIQUE INDEX fireinfo_current ON fireinfo USING btree (profile_id) WHERE expired_at IS NULL + +-- CREATE INDEX fireinfo_releases_current ON fireinfo USING hash((blob->'system'->'release')) WHERE expired_at IS NULL; +-- CREATE INDEX fireinfo_releases ON fireinfo USING hash((blob->'system'->'release')); + +-- CREATE INDEX fireinfo_arches_current ON fireinfo USING hash((blob->'cpu'->'arch')) WHERE blob->'cpu'->'arch' IS NOT NULL AND expired_at IS NULL; +-- CREATE INDEX fireinfo_arches ON fireinfo USING hash((blob->'cpu'->'arch')) WHERE blob->'cpu'->'arch' IS NOT NULL; + +-- CREATE INDEX fireinfo_cpu_vendors ON fireinfo USING hash((blob->'cpu'->'vendor')) WHERE blob->'cpu'->'vendor' IS NOT NULL; + +-- CREATE INDEX fireinfo_hypervisor_vendors_current ON fireinfo USING hash((blob->'hypervisor'->'vendor')) WHERE expired_at IS NULL AND CAST((blob->'system'->'virtual') AS boolean) IS TRUE; + +-- XXX virtual index + +TRUNCATE TABLE fireinfo; + +--EXPLAIN + +INSERT INTO fireinfo + +SELECT + p.public_id AS profile_id, + p.time_created AS created_at, + ( + CASE + WHEN p.time_valid <= CURRENT_TIMESTAMP THEN p.time_valid + ELSE NULL + END + ) AS expired_at, + 0 AS version, + ( + -- Empty the profile if we don't have any data + CASE WHEN profile_arches.arch_id IS NULL THEN NULL + + -- Otherwise do some hard work... + ELSE + -- CPU + jsonb_build_object('cpu', + jsonb_build_object( + 'arch', arches.name, + 'bogomips', profile_processors.bogomips, + 'speed', profile_processors.clock_speed, + + 'vendor', processors.vendor, + 'model', processors.model, + 'model_string', processors.model_string, + 'stepping', processors.stepping, + 'flags', processors.flags, + 'family', processors.family, + 'count', processors.core_count + ) + ) + + -- System + || jsonb_build_object('system', + jsonb_build_object( + 'kernel', kernels.name, + 'language', profile_languages.language, + 'memory', profile_memory.amount, + 'release', releases.name, + 'root_size', profile_storage.amount, + 'vendor', systems.vendor, + 'model', systems.model, + 'virtual', CASE WHEN hypervisors.id IS NULL THEN FALSE ELSE TRUE END + ) + ) + + -- Hypervisor + || CASE + WHEN hypervisors.id IS NULL THEN jsonb_build_object() + ELSE + jsonb_build_object( + 'hypervisor', + json_build_object('vendor', hypervisors.name) + ) + END + + -- Devices + || jsonb_build_object('devices', devices.devices) + + -- Networks + || jsonb_build_object('networks', + jsonb_build_object( + 'green', profile_networks.has_green, + 'blue', profile_networks.has_blue, + 'orange', profile_networks.has_orange, + 'red', profile_networks.has_red + ) + ) + END + ) AS blob, + p.time_updated AS last_updated_at, + p.private_id AS private_id, + locations.location AS country_code + +FROM fireinfo_profiles p + +LEFT JOIN + fireinfo_profiles_locations locations ON p.id = locations.profile_id + +LEFT JOIN + fireinfo_profiles_arches profile_arches ON p.id = profile_arches.profile_id + +LEFT JOIN + fireinfo_arches arches ON profile_arches.arch_id = arches.id + +LEFT JOIN + ( + SELECT + profile_devices.profile_id AS profile_id, + jsonb_agg( + jsonb_build_object( + 'deviceclass', devices.deviceclass, + 'subsystem', devices.subsystem, + 'vendor', devices.vendor, + 'model', devices.model, + 'sub_vendor', devices.sub_vendor, + 'sub_model', devices.sub_model, + 'driver', devices.driver + ) + ) AS devices + FROM + fireinfo_profiles_devices profile_devices + LEFT JOIN + fireinfo_devices devices ON profile_devices.device_id = devices.id + GROUP BY + profile_devices.profile_id + ) devices ON p.id = devices.profile_id + +LEFT JOIN + fireinfo_profiles_processors profile_processors ON p.id = profile_processors.profile_id + +LEFT JOIN + fireinfo_processors processors ON profile_processors.processor_id = processors.id + +LEFT JOIN + fireinfo_profiles_kernels profile_kernels ON p.id = profile_kernels.profile_id + +LEFT JOIN + fireinfo_kernels kernels ON profile_kernels.kernel_id = kernels.id + +LEFT JOIN + fireinfo_profiles_languages profile_languages ON p.id = profile_languages.profile_id + +LEFT JOIN + fireinfo_profiles_memory profile_memory ON p.id = profile_memory.profile_id + +LEFT JOIN + fireinfo_profiles_releases profile_releases ON p.id = profile_releases.profile_id + +LEFT JOIN + fireinfo_releases releases ON profile_releases.release_id = releases.id + +LEFT JOIN + fireinfo_profiles_storage profile_storage ON p.id = profile_storage.profile_id + +LEFT JOIN + fireinfo_profiles_systems profile_systems ON p.id = profile_systems.profile_id + +LEFT JOIN + fireinfo_systems systems ON profile_systems.system_id = systems.id + +LEFT JOIN + fireinfo_profiles_virtual profile_virtual ON p.id = profile_virtual.profile_id + +LEFT JOIN + fireinfo_hypervisors hypervisors ON profile_virtual.hypervisor_id = hypervisors.id + +LEFT JOIN + fireinfo_profiles_networks profile_networks ON p.id = profile_networks.profile_id + +--WHERE +-- XXX TO FIND A PROFILE WITH DATA +-- profile_processors.profile_id IS NOT NULL + +-- XXX TO FIND A VIRTUAL PROFILE +--profile_virtual.hypervisor_id IS NOT NULL + +--ORDER BY +-- time_created DESC + +--LIMIT 1 +; + +COMMIT; diff --git a/src/backend/fireinfo.py b/src/backend/fireinfo.py index f2273507..260572ae 100644 --- a/src/backend/fireinfo.py +++ b/src/backend/fireinfo.py @@ -2,13 +2,15 @@ import datetime import iso3166 +import json +import jsonschema import logging import re -from . import database from . import hwdata from . import util from .misc import Object +from .decorators import * N_ = lambda x: x @@ -80,31 +82,213 @@ CPU_STRINGS = ( (r"Feroceon .*", r"ARM Feroceon"), ) -IGNORED_DEVICES = ["usb",] - -class ProfileDict(object): - def __init__(self, data): - self._data = data +PROFILE_SCHEMA = { + "$schema" : "https://json-schema.org/draft/2020-12/schema", + "$id" : "https://fireinfo.ipfire.org/profile.schema.json", + "title" : "Fireinfo Profile", + "description" : "Fireinfo Profile", + "type" : "object", + # Properties + "properties" : { + # Processor + "cpu" : { + "type" : "object", + "properties" : { + "arch" : { + "type" : "string", + "pattern" : r"^[a-z0-9\_]{,8}$", + }, + "count" : { + "type" : "integer", + }, + "family" : { + "type" : "integer", + }, + "flags" : { + "type" : "array", + "items" : { + "type" : "string", + "pattern" : r"^.{,24}$", + }, + }, + "model" : { + "type" : "integer", + }, + "model_string" : { + "type" : "string", + "pattern" : r"^.{,80}$", + }, + "speed" : { + "type" : "number", + }, + "stepping" : { + "type" : "integer", + }, + "vendor" : { + "type" : "string", + "pattern" : r"^.{,80}$", + }, + }, + "additionalProperties" : False, + "required" : [ + "arch", + "count", + "family", + "flags", + "model", + "model_string", + "speed", + "stepping", + "vendor", + ], + }, -class ProfileNetwork(ProfileDict): - def __eq__(self, other): - if other is None: - return False + # Devices + "devices" : { + "type" : "array", + "items" : { + "type" : "object", + "properties" : { + "deviceclass" : { + "type" : ["string", "null"], + "pattern" : r"^.{,20}$", + }, + "driver" : { + "type" : ["string", "null"], + "pattern" : r"^.{,24}$", + }, + "model" : { + "type" : "string", + "pattern" : r"^[a-z0-9]{4}$", + }, + "sub_model" : { + "type" : ["string", "null"], + "pattern" : r"^[a-z0-9]{4}$", + }, + "sub_vendor" : { + "type" : ["string", "null"], + "pattern" : r"^[a-z0-9]{4}$", + }, + "subsystem" : { + "type" : "string", + "pattern" : r"^[a-z]{3}$", + }, + "vendor" : { + "type" : "string", + "pattern" : r"^[a-z0-9]{4}$", + }, + }, + "additionalProperties" : False, + "required" : [ + "deviceclass", + "driver", + "model", + "subsystem", + "vendor", + ], + }, + }, - if not self.has_red == other.has_red: - return False + # Network + "network" : { + "type" : "object", + "properties" : { + "blue" : { + "type" : "boolean", + }, + "green" : { + "type" : "boolean", + }, + "orange" : { + "type" : "boolean", + }, + "red" : { + "type" : "boolean", + }, + }, + "additionalProperties" : False, + }, - if not self.has_green == other.has_green: - return False + # System + "system" : { + "type" : "object", + "properties" : { + "kernel_release" : { + "type" : "string", + "pattern" : r"^.{,40}$", + }, + "language" : { + "type" : "string", + "pattern" : r"^[a-z]{2}$", + }, + "memory" : { + "type" : "integer", + }, + "model" : { + "type" : "string", + "pattern" : r"^.{,80}$", + }, + "release" : { + "type" : "string", + "pattern" : r"^.{,80}$", + }, + "root_size" : { + "type" : "number", + }, + "vendor" : { + "type" : "string", + "pattern" : r"^.{,80}$", + }, + "virtual" : { + "type" : "boolean" + }, + }, + "additionalProperties" : False, + "required" : [ + "kernel_release", + "language", + "memory", + "model", + "release", + "root_size", + "vendor", + "virtual", + ], + }, - if not self.has_orange == other.has_orange: - return False + # Hypervisor + "hypervisor" : { + "type" : "object", + "properties" : { + "vendor" : { + "type" : "string", + "pattern" : r"^.{,40}$", + }, + }, + "additionalProperties" : False, + "required" : [ + "vendor", + ], + }, - if not self.has_blue == other.has_blue: - return False + # Error - BogoMIPS + "bogomips" : { + "type" : "number", + }, + }, + "additionalProperties" : False, + "required" : [ + "cpu", + "devices", + "network", + "system", + ], +} - return True +class Network(Object): + def init(self, blob): + self.blob = blob def __iter__(self): ret = [] @@ -116,33 +300,28 @@ class ProfileNetwork(ProfileDict): return iter(ret) def has_zone(self, name): - return self._data.get("has_%s" % name) + return self.blob.get(name, False) @property def has_red(self): - return self._data.get("has_red", False) + return self.has_zone("red") @property def has_green(self): - return self._data.get("has_green", False) + return self.has_zone("green") @property def has_orange(self): - return self._data.get("has_orange", False) + return self.has_zone("orange") @property def has_blue(self): - return self._data.get("has_blue", False) + return self.has_zone("blue") class Processor(Object): - def __init__(self, backend, id, data=None, clock_speed=None, bogomips=None): - Object.__init__(self, backend) - - self.id = id - self.__data = data - self.__clock_speed = clock_speed - self.__bogomips = bogomips + def init(self, blob): + self.blob = blob def __str__(self): s = [] @@ -158,43 +337,34 @@ class Processor(Object): return " ".join(s) - @property - def data(self): - if self.__data is None: - self.__data = self.db.get("SELECT * FROM fireinfo_processors \ - WHERE id = %s", self.id) - - return self.__data - @property def vendor(self): + vendor = self.blob.get("vendor") + try: - return CPU_VENDORS[self.data.vendor] + return CPU_VENDORS[vendor] except KeyError: - return self.data.vendor + return vendor @property def family(self): - return self.data.family + return self.blob.get("family") @property def model(self): - return self.data.model + return self.blob.get("model") @property def stepping(self): - return self.data.stepping + return self.blob.get("stepping") @property def model_string(self): - if self.data.model_string: - s = self.data.model_string.split() - - return " ".join((e for e in s if e)) + return self.blob.get("model_string") @property def flags(self): - return self.data.flags + return self.blob.get("flags") def has_flag(self, flag): return flag in self.flags @@ -207,7 +377,7 @@ class Processor(Object): @property def core_count(self): - return self.data.core_count + return self.blob.get("count", 1) @property def count(self): @@ -349,11 +519,8 @@ class Device(Object): } } - def __init__(self, backend, id, data=None): - Object.__init__(self, backend) - - self.id = id - self.__data = data + def init(self, blob): + self.blob = blob def __repr__(self): return "<%s vendor=%s model=%s>" % (self.__class__.__name__, @@ -361,7 +528,9 @@ class Device(Object): def __eq__(self, other): if isinstance(other, self.__class__): - return self.id == other.id + return self.blob == other.blob + + return NotImplemented def __lt__(self, other): if isinstance(other, self.__class__): @@ -371,53 +540,41 @@ class Device(Object): self.model_string < other.model_string or \ self.model < other.model - @property - def data(self): - if self.__data is None: - assert self.id - - self.__data = self.db.get("SELECT * FROM fireinfo_devices \ - WHERE id = %s", self.id) - - return self.__data + return NotImplemented def is_showable(self): - if self.driver in IGNORED_DEVICES: - return False - - if self.driver in ("pcieport", "hub"): + if self.driver in ("usb", "pcieport", "hub"): return False return True @property def subsystem(self): - return self.data.subsystem + return self.blob.get("subsystem") @property def model(self): - return self.data.model + return self.blob.get("model") - @property + @lazy_property def model_string(self): - return self.fireinfo.get_model_string(self.subsystem, - self.vendor, self.model) + return self.fireinfo.get_model_string(self.subsystem, self.vendor, self.model) @property def vendor(self): - return self.data.vendor + return self.blob.get("vendor") - @property + @lazy_property def vendor_string(self): return self.fireinfo.get_vendor_string(self.subsystem, self.vendor) @property def driver(self): - return self.data.driver + return self.blob.get("driver") - @property + @lazy_property def cls(self): - classid = self.data.deviceclass + classid = self.blob.get("deviceclass") if self.subsystem == "pci": classid = classid[:-4] @@ -433,145 +590,81 @@ class Device(Object): except KeyError: return "N/A" - @property - def percentage(self): - return self.data.get("percentage", None) - - -class Profile(Object): - def __init__(self, backend, id, data=None): - Object.__init__(self, backend) - - self.id = id - self.__data = data - def __repr__(self): - return "<%s %s>" % (self.__class__.__name__, self.public_id) - - def __cmp__(self, other): - return cmp(self.id, other.id) - - def is_showable(self): - if self.arch_id: - return True - - return False +class System(Object): + def init(self, blob): + self.blob = blob @property - def data(self): - if self.__data is None: - self.__data = self.db.get("SELECT * FROM fireinfo_profiles \ - WHERE id = %s", self.id) - - return self.__data + def arch(self): + return self.blob.get("arch") @property - def public_id(self): - return self.data.public_id + def language(self): + return self.blob.get("language") @property - def private_id(self): - raise NotImplementedError + def vendor(self): + return self.blob.get("vendor") @property - def time_created(self): - return self.data.time_created + def model(self): + return self.blob.get("model") @property - def time_updated(self): - return self.data.time_updated - - def updated(self, profile_parser=None, country_code=None, when=None): - valid = self.settings.get_int("fireinfo_profile_days_valid", 14) - - self.db.execute("UPDATE fireinfo_profiles \ - SET \ - time_updated = then_or_now(%s), \ - time_valid = then_or_now(%s) + INTERVAL '%s days', \ - updates = updates + 1 \ - WHERE id = %s", when, when, valid, self.id) - - if profile_parser: - self.set_processor_speeds( - profile_parser.processor_clock_speed, - profile_parser.processor_bogomips, - ) - - if country_code: - self.set_country_code(country_code) - - self.log_profile_update() - - def log_profile_update(self): - # Log that an update was performed for this profile id - self.db.execute("INSERT INTO fireinfo_profiles_log(public_id) \ - VALUES(%s)", self.public_id) - - def expired(self, when=None): - self.db.execute("UPDATE fireinfo_profiles \ - SET time_valid = then_or_now(%s) WHERE id = %s", when, self.id) + def release(self): + return self.blob.get("release") - def parse(self, parser): - # Processor - self.processor = parser.processor - self.set_processor_speeds(parser.processor_clock_speed, parser.processor_bogomips) + @property + def storage(self): + return self.blob.get("storage_size", 0) - # All devices - self.devices = parser.devices + def is_virtual(self): + return self.blob.get("virtual", False) - # System - self.system_id = parser.system_id - # Memory - self.memory = parser.memory +class Hypervisor(Object): + def init(self, blob): + self.blob = blob - # Storage - self.storage = parser.storage + def __str__(self): + return self.vendor - # Kernel - self.kernel_id = parser.kernel_id + @property + def vendor(self): + return self.blob.get("vendor") - # Arch - self.arch_id = parser.arch_id - # Release - self.release_id = parser.release_id +class Profile(Object): + def init(self, profile_id, private_id, created_at, expired_at, version, blob, + last_updated_at, country_code, **kwargs): + self.profile_id = profile_id + self.private_id = private_id + self.created_at = created_at + self.expired_at = expired_at + self.version = version + self.blob = blob + self.last_updated_at = last_updated_at + self.country_code = country_code - # Language - self.language = parser.language + def __repr__(self): + return "<%s %s>" % (self.__class__.__name__, self.profile_id) - # Virtual - if parser.virtual: - self.hypervisor_id = parser.hypervisor_id + def is_showable(self): + return True if self.blob else False - # Network - self.network = parser.network + @property + def public_id(self): + """ + An alias for the profile ID + """ + return self.profile_id # Location @property def location(self): - if not hasattr(self, "_location"): - res = self.db.get("SELECT location FROM fireinfo_profiles_locations \ - WHERE profile_id = %s", self.id) - - if res: - self._location = res.location - else: - self._location = None - - return self._location - - def set_country_code(self, country_code): - if self.location == country_code: - return - - self.db.execute("DELETE FROM fireinfo_profiles_locations \ - WHERE profile_id = %s", self.id) - self.db.execute("INSERT INTO fireinfo_profiles_locations(profile_id, location) \ - VALUES(%s, %s)", self.id, country_code) - - self._location = country_code + return self.country_code @property def location_string(self): @@ -579,1384 +672,597 @@ class Profile(Object): # Devices - @property - def device_ids(self): - if not hasattr(self, "_device_ids"): - res = self.db.query("SELECT device_id FROM fireinfo_profiles_devices \ - WHERE profile_id = %s", self.id) - - self._device_ids = sorted([r.device_id for r in res]) - - return self._device_ids - - def get_devices(self): - if not hasattr(self, "_devices"): - res = self.db.query("SELECT * FROM fireinfo_devices \ - LEFT JOIN fireinfo_profiles_devices ON \ - fireinfo_devices.id = fireinfo_profiles_devices.device_id \ - WHERE fireinfo_profiles_devices.profile_id = %s", self.id) - - self._devices = [] - for row in res: - device = Device(self.backend, row.id, row) - self._devices.append(device) - - return self._devices - - def set_devices(self, devices): - device_ids = [d.id for d in devices] - - self.db.execute("DELETE FROM fireinfo_profiles_devices WHERE profile_id = %s", self.id) - self.db.executemany("INSERT INTO fireinfo_profiles_devices(profile_id, device_id) \ - VALUES(%s, %s)", ((self.id, d) for d in device_ids)) - - self._devices = devices - self._device_ids = device_ids - - devices = property(get_devices, set_devices) - - def count_device(self, subsystem, vendor, model): - counter = 0 - - for dev in self.devices: - if dev.subsystem == subsystem and dev.vendor == vendor and dev.model == model: - counter += 1 - - return counter + @lazy_property + def devices(self): + return [Device(self.backend, blob) for blob in self.blob.get("devices", [])] # System - def get_system_id(self): - if not hasattr(self, "_system_id"): - res = self.db.get("SELECT system_id AS id FROM fireinfo_profiles_systems \ - WHERE profile_id = %s", self.id) - - if res: - self._system_id = res.id - else: - self._system_id = None - - return self._system_id - - def set_system_id(self, system_id): - self.db.execute("DELETE FROM fireinfo_profiles_systems WHERE profile_id = %s", self.id) - - if system_id: - self.db.execute("INSERT INTO fireinfo_profiles_systems(profile_id, system_id) \ - VALUES(%s, %s)", self.id, system_id) - - self._system_id = None - if hasattr(self, "_system"): - del self._system - - system_id = property(get_system_id, set_system_id) - - @property + @lazy_property def system(self): - if not hasattr(self, "_system"): - res = self.db.get("SELECT fireinfo_systems.vendor AS vendor, fireinfo_systems.model AS model \ - FROM fireinfo_profiles_systems \ - LEFT JOIN fireinfo_systems ON fireinfo_profiles_systems.system_id = fireinfo_systems.id \ - WHERE fireinfo_profiles_systems.profile_id = %s", self.id) - - if res: - self._system = (res.vendor, res.model) - else: - self._system = (None, None) - - return self._system - - @property - def system_vendor(self): - try: - v, m = self.system - return v - except TypeError: - pass - - @property - def system_model(self): - try: - v, m = self.system - return m - except TypeError: - pass - - @property - def appliance_id(self): - if not hasattr(self, "_appliance_id"): - appliances = ( - ("fountainnetworks-duo-box", self._appliance_test_fountainnetworks_duo_box), - ("fountainnetworks-prime", self._appliance_test_fountainnetworks_prime), - ("lightningwirelabs-eco-plus", self._appliance_test_lightningwirelabs_eco_plus), - ("lightningwirelabs-eco", self._appliance_test_lightningwirelabs_eco), - ) + return System(self.backend, self.blob.get("system", {})) - self._appliance_id = None - for name, test_func in appliances: - if not test_func(): - continue - - self._appliance_id = name - break - - return self._appliance_id + # Processor @property - def appliance(self): - if self.appliance_id == "fountainnetworks-duo-box": - return "Fountain Networks - IPFire Duo Box" - - elif self.appliance_id == "fountainnetworks-prime": - return "Fountain Networks - IPFire Prime Box" - - elif self.appliance_id == "lightningwirelabs-eco-plus": - return "Lightning Wire Labs - IPFire Eco Plus Appliance" - - elif self.appliance_id == "lightningwirelabs-eco": - return "Lightning Wire Labs - IPFire Eco Appliance" - - def _appliance_test_fountainnetworks_duo_box(self): - if not self.processor.vendor == "Intel": - return False - - if not self.processor.model_string == "Intel(R) Celeron(R) 2957U @ 1.40GHz": - return False - - if not self.count_device("pci", "10ec", "8168") == 2: - return False - - # WiFi module - #if self.count_device("usb", "148f", "5572") < 1: - # return False - - return True - - def _appliance_test_fountainnetworks_prime(self): - if not self.system in (("SECO", None), ("SECO", "0949")): - return False - - # Must have a wireless device - if self.count_device("usb", "148f", "5572") < 1: - return False - - return True - - def _appliance_test_lightningwirelabs_eco(self): - if not self.system == ("MSI", "MS-9877"): - return False - - # Must have four Intel network adapters - network_adapters_count = self.count_device("pci", "8086", "10d3") - if not network_adapters_count == 4: - return False - - return True - - def _appliance_test_lightningwirelabs_eco_plus(self): - if not self.system_vendor == "ASUS": - return False - - if not self.system_model.startswith("P9A-I/2550"): - return False - - # Must have four Intel network adapters - network_adapters_count = self.count_device("pci", "8086", "1f41") - if not network_adapters_count == 4: - return False - - return True - - # Processors - - @property - def processor_id(self): - if hasattr(self, "_processor"): - return self._processor.id - - if not hasattr(self, "_processor_id"): - res = self.db.get("SELECT processor_id FROM fireinfo_profiles_processors \ - WHERE profile_id = %s", self.id) - - if res: - self._processor_id = res.processor_id - else: - self._processor_id = None - - return self._processor_id - - def get_processor(self): - if not self.processor_id: - return - - if not hasattr(self, "_processor"): - res = self.db.get("SELECT * FROM fireinfo_profiles_processors \ - WHERE profile_id = %s", self.id) - - if res: - self._processor = self.fireinfo.get_processor_by_id(res.processor_id, - clock_speed=res.clock_speed, bogomips=res.bogomips) - else: - self._processor = None - - return self._processor - - def set_processor(self, processor): - self.db.execute("DELETE FROM fireinfo_profiles_processors \ - WHERE profile_id = %s", self.id) - - if processor: - self.db.execute("INSERT INTO fireinfo_profiles_processors(profile_id, processor_id) \ - VALUES(%s, %s)", self.id, processor.id) - - self._processor = processor - - processor = property(get_processor, set_processor) - - def set_processor_speeds(self, clock_speed, bogomips): - self.db.execute("UPDATE fireinfo_profiles_processors \ - SET clock_speed = %s, bogomips = %s WHERE profile_id = %s", - clock_speed, bogomips, self.id) - - # Compat - @property - def cpu(self): - return self.processor + def processor(self): + return Processor(self.backend, self.blob.get("cpu", {})) # Memory - def get_memory(self): - if not hasattr(self, "_memory"): - res = self.db.get("SELECT amount FROM fireinfo_profiles_memory \ - WHERE profile_id = %s", self.id) - - if res: - self._memory = res.amount * 1024 - else: - self._memory = None - - return self._memory - - def set_memory(self, amount): - if self.memory == amount: - return - - amount /= 1024 - - self.db.execute("DELETE FROM fireinfo_profiles_memory WHERE profile_id = %s", self.id) - if amount: - self.db.execute("INSERT INTO fireinfo_profiles_memory(profile_id, amount) \ - VALUES(%s, %s)", self.id, amount) - - self._memory = amount * 1024 - - memory = property(get_memory, set_memory) + @property + def memory(self): + return self.blob.get("memory") @property def friendly_memory(self): return util.format_size(self.memory or 0) - # Storage - - def get_storage(self): - if not hasattr(self, "_storage"): - res = self.db.get("SELECT amount FROM fireinfo_profiles_storage \ - WHERE profile_id = %s", self.id) - - if res: - self._storage = res.amount * 1024 - else: - self._storage = None - - return self._storage - - def set_storage(self, amount): - if self.storage == amount: - return - - amount /= 1024 - - self.db.execute("DELETE FROM fireinfo_profiles_storage WHERE profile_id = %s", self.id) - if amount: - self.db.execute("INSERT INTO fireinfo_profiles_storage(profile_id, amount) \ - VALUES(%s, %s)", self.id, amount) - - self._storage = amount * 1024 - - storage = property(get_storage, set_storage) - - @property - def friendly_storage(self): - return util.format_size(self.storage) - - # Kernel - - def get_kernel_id(self): - if not hasattr(self, "_kernel_id"): - res = self.db.get("SELECT fireinfo_profiles_kernels.kernel_id AS id FROM fireinfo_profiles \ - LEFT JOIN fireinfo_profiles_kernels ON fireinfo_profiles.id = fireinfo_profiles_kernels.profile_id \ - WHERE fireinfo_profiles.id = %s", self.id) - - if res: - self._kernel_id = res.id - else: - self._kernel_id = None - - return self._kernel_id - - def set_kernel_id(self, kernel_id): - if self.kernel_id == kernel_id: - return - - self.db.execute("DELETE FROM fireinfo_profiles_kernels WHERE profile_id = %s", self.id) - if kernel_id: - self.db.execute("INSERT INTO fireinfo_profiles_kernels(profile_id, kernel_id) \ - VALUES(%s, %s)", self.id, kernel_id) - - self._kernel_id = kernel_id - if hasattr(self, "_kernel"): - del self._kernel - - kernel_id = property(get_kernel_id, set_kernel_id) - - @property - def kernel(self): - if not hasattr(self, "_kernel"): - res = self.db.get("SELECT fireinfo_kernels.name AS name FROM fireinfo_profiles \ - LEFT JOIN fireinfo_profiles_kernels ON fireinfo_profiles.id = fireinfo_profiles_kernels.profile_id \ - LEFT JOIN fireinfo_kernels ON fireinfo_kernels.id = fireinfo_profiles_kernels.kernel_id \ - WHERE fireinfo_profiles.id = %s", self.id) - - if res: - self._kernel = res.name - else: - self._kernel = None - - return self._kernel - - # Arch - - def get_arch_id(self): - if not hasattr(self, "_arch_id"): - res = self.db.get("SELECT fireinfo_profiles_arches.arch_id AS id FROM fireinfo_profiles \ - LEFT JOIN fireinfo_profiles_arches ON fireinfo_profiles.id = fireinfo_profiles_arches.profile_id \ - WHERE fireinfo_profiles.id = %s", self.id) - - if res: - self._arch_id = res.id - else: - self._arch_id = None - - return self._arch_id - - def set_arch_id(self, arch_id): - if self.arch_id == arch_id: - return - - self.db.execute("DELETE FROM fireinfo_profiles_arches WHERE profile_id = %s", self.id) - if arch_id: - self.db.execute("INSERT INTO fireinfo_profiles_arches(profile_id, arch_id) \ - VALUES(%s, %s)", self.id, arch_id) - - self._arch_id = None - if hasattr(self, "_arch"): - del self._arch - - arch_id = property(get_arch_id, set_arch_id) - - @property - def arch(self): - if not hasattr(self, "_arch"): - res = self.db.get("SELECT fireinfo_arches.name AS name FROM fireinfo_profiles \ - LEFT JOIN fireinfo_profiles_arches ON fireinfo_profiles.id = fireinfo_profiles_arches.profile_id \ - LEFT JOIN fireinfo_arches ON fireinfo_arches.id = fireinfo_profiles_arches.arch_id \ - WHERE fireinfo_profiles.id = %s", self.id) - - if res: - self._arch = res.name - else: - self._arch = None - - return self._arch - - # Release - - def get_release_id(self): - if not hasattr(self, "_release_id"): - res = self.db.get("SELECT fireinfo_profiles_releases.release_id AS id FROM fireinfo_profiles \ - LEFT JOIN fireinfo_profiles_releases ON fireinfo_profiles.id = fireinfo_profiles_releases.profile_id \ - WHERE fireinfo_profiles.id = %s", self.id) - - if res: - self._release_id = res.id - else: - self._release_id = None - - return self._release_id - - def set_release_id(self, release_id): - if self.release_id == release_id: - return - - self.db.execute("DELETE FROM fireinfo_profiles_releases WHERE profile_id = %s", self.id) - if release_id: - self.db.execute("INSERT INTO fireinfo_profiles_releases(profile_id, release_id) \ - VALUES(%s, %s)", self.id, release_id) - - self._release_id = release_id - if hasattr(self, "_release"): - del self._release - - release_id = property(get_release_id, set_release_id) - - @property - def release(self): - if not hasattr(self, "_release"): - res = self.db.get("SELECT fireinfo_releases.name AS name FROM fireinfo_profiles \ - LEFT JOIN fireinfo_profiles_releases ON fireinfo_profiles.id = fireinfo_profiles_releases.profile_id \ - LEFT JOIN fireinfo_releases ON fireinfo_profiles_releases.release_id = fireinfo_releases.id \ - WHERE fireinfo_profiles.id = %s", self.id) - - if res: - self._release = self._format_release(res.name) - else: - self._release = None - - return self._release - - @staticmethod - def _format_release(r): - if not r: - return r - - # Remove the development header - r = r.replace("Development Build: ", "") - - pairs = ( - ("-beta", " - Beta "), - ("-rc", " - Release Candidate "), - ("core", "Core Update "), - ("beta", "Beta "), - ) - - for k, v in pairs: - r = r.replace(k, v) - - return r - - @property - def release_short(self): - pairs = ( - (r"Release Candidate (\d+)", r"RC\1"), - ) - - s = self.release - for pattern, repl in pairs: - if re.search(pattern, s) is None: - continue - - s = re.sub(pattern, repl, s) - - return s - # Virtual - @property - def virtual(self): - if not hasattr(self, "_virtual"): - res = self.db.get("SELECT 1 FROM fireinfo_profiles_virtual \ - WHERE profile_id = %s", self.id) - - if res: - self._virtual = True - else: - self._virtual = False - - return self._virtual - - def get_hypervisor_id(self): - if not hasattr(self, "_hypervisor_id"): - res = self.db.get("SELECT fireinfo_profiles_virtual.hypervisor_id AS id FROM fireinfo_profiles \ - LEFT JOIN fireinfo_profiles_virtual ON fireinfo_profiles.id = fireinfo_profiles_virtual.profile_id \ - WHERE fireinfo_profiles.id = %s", self.id) - - if res: - self._hypervisor_id = res.id - else: - self._hypervisor_id = None - - return self._hypervisor_id - - def set_hypervisor_id(self, hypervisor_id): - self.db.execute("DELETE FROM fireinfo_profiles_virtual WHERE profile_id = %s", self.id) - self.db.execute("INSERT INTO fireinfo_profiles_virtual(profile_id, hypervisor_id) \ - VALUES(%s, %s)", self.id, hypervisor_id) - - self._hypervisor_id = hypervisor_id - - hypervisor_id = property(get_hypervisor_id, set_hypervisor_id) + def is_virtual(self): + return self.system.is_virtual() @property def hypervisor(self): - if not hasattr(self, "_hypervisor"): - res = self.db.get("SELECT fireinfo_hypervisors.name AS hypervisor FROM fireinfo_profiles \ - LEFT JOIN fireinfo_profiles_virtual ON fireinfo_profiles.id = fireinfo_profiles_virtual.profile_id \ - LEFT JOIN fireinfo_hypervisors ON fireinfo_profiles_virtual.hypervisor_id = fireinfo_hypervisors.id \ - WHERE fireinfo_profiles.id = %s", self.id) - - if res: - self._hypervisor = res.hypervisor - else: - self._hypervisor = None - - return self._hypervisor - - # Language - - def get_language(self): - if not hasattr(self, "_language"): - res = self.db.get("SELECT language FROM fireinfo_profiles_languages \ - WHERE profile_id = %s", self.id) - - if res: - self._language = res.language - else: - self._language = None - - return self._language - - def set_language(self, language): - self.db.execute("DELETE FROM fireinfo_profiles_languages WHERE profile_id = %s", self.id) - - if language: - self.db.execute("INSERT INTO fireinfo_profiles_languages(profile_id, language) \ - VALUES(%s, %s)", self.id, language) - - self._language = language - - language = property(get_language, set_language) + return Hypervisor(self.backend, self.blob.get("hypervisor")) # Network - def get_network(self): - if not hasattr(self, "_network"): - res = self.db.get("SELECT * FROM fireinfo_profiles_networks \ - WHERE profile_id = %s", self.id) - - if not res: - res = {} - - self._network = ProfileNetwork(res) - - return self._network - - def set_network(self, network): - self.db.execute("DELETE FROM fireinfo_profiles_networks WHERE profile_id = %s", self.id) - - if network: - self.db.execute("INSERT INTO fireinfo_profiles_networks(profile_id, \ - has_red, has_green, has_orange, has_blue) VALUES(%s, %s, %s, %s, %s)", - self.id, network.has_red, network.has_green, network.has_orange, network.has_blue) - - self._network = network - - network = property(get_network, set_network) - - -class ProfileData(Object): - def __init__(self, backend, id, data=None, profile=None): - Object.__init__(self, backend) - - self.id = id - self._data = data - self._profile = profile - - @property - def data(self): - if self._data is None: - self._data = self.db.get("SELECT * FROM fireinfo_profile_data \ - WHERE id = %s", self.id) - - return self._data - - @property - def profile(self): - if not self._profile: - self._profile = self.fireinfo.get_profile_by_id(self.profile_id) - - return self._profile - - @property - def profile_id(self): - return self.data.profile_id - - -class ProfileParserError(Exception): - pass - - -class ProfileParser(Object): - __device_args = ( - "subsystem", - "vendor", - "model", - "sub_vendor", - "sub_model", - "driver", - "deviceclass", - ) - - __processor_args = ( - "vendor", - "model_string", - "family", - "model", - "stepping", - "core_count", - "flags", - ) - - def __init__(self, backend, public_id, blob=None): - Object.__init__(self, backend) - - self.public_id = public_id - self.private_id = None - self.devices = [] - self.processor = None - self.processor_clock_speed = None - self.processor_bogomips = None - self.system_id = None - self.memory = None - self.storage = None - self.kernel = None - self.kernel_id = None - self.arch = None - self.arch_id = None - self.release = None - self.release_id = None - self.language = None - self.virtual = None - self.hypervisor_id = None - self.network = None - - self.__parse_blob(blob) - - def equals(self, other): - if not self.processor_id == other.processor_id: - return False - - if not self.device_ids == other.device_ids: - return False - - if not self.system_id == other.system_id: - return False - - if not self.memory == other.memory: - return False - - if not self.storage == other.storage: - return False - - if not self.kernel_id == other.kernel_id: - return False - - if not self.arch_id == other.arch_id: - return False - - if not self.release_id == other.release_id: - return False - - if not self.language == other.language: - return False - - if not self.virtual == other.virtual: - return False - - if other.virtual: - if not self.hypervisor_id == other.hypervisor_id: - return False - - if not self.network == other.network: - return False - - return True - - def __parse_blob(self, blob): - _profile = blob.get("profile", {}) - self.private_id = blob.get("private_id") - - # Do not try to parse an empty profile - if not _profile: - return - - # Processor - _processor = _profile.get("cpu", {}) - self.__parse_processor(_processor) - - # Find devices - _devices = _profile.get("devices", []) - self.__parse_devices(_devices) - - # System - _system = _profile.get("system") - if _system: - self.__parse_system(_system) - - # Memory (convert to bytes) - memory = _system.get("memory", None) - if memory: - self.memory = memory * 1024 - - # Storage size (convert to bytes) - storage = _system.get("root_size", None) - if storage: - self.storage = storage * 1024 - - # Kernel - kernel = _system.get("kernel_release", None) - if kernel: - self.__parse_kernel(kernel) - - # Release - release = _system.get("release", None) - if release: - self.__parse_release(release) - - # Language - language = _system.get("language", None) - if language: - self.__parse_language(language) - - # Virtual - self.virtual = _system.get("virtual", False) - if self.virtual: - hypervisor = _profile.get("hypervisor") - self.__parse_hypervisor(hypervisor) - - # Network - _network = _profile.get("network") - if _network: - self.__parse_network(_network) - - @property - def device_ids(self): - return sorted([d.id for d in self.devices]) - - def __parse_devices(self, _devices): - self.devices = [] - - for _device in _devices: - args = {} - for arg in self.__device_args: - args[arg] = _device.get(arg, None) - - # Skip if the subsystem is not set - if not args.get("subsystem", None): - continue - - # Find the device or create a new one. - device = self.fireinfo.get_device(**args) - if not device: - device = self.fireinfo.create_device(**args) - - self.devices.append(device) - - def __parse_system(self, system): - vendor = system.get("vendor", None) - if not vendor: - vendor = None - - model = system.get("model", None) - if not model: - model = None - - self.system_id = self.fireinfo.get_system(vendor, model) - if not self.system_id: - self.system_id = self.fireinfo.create_system(vendor, model) - - @property - def processor_id(self): - if not self.processor: - return - - return self.processor.id - - def __parse_processor(self, _processor): - args = {} - for arg in self.__processor_args: - if arg == "core_count": - _arg = "count" - else: - _arg = arg - - args[arg] = _processor.get(_arg, None) - - self.processor = self.fireinfo.get_processor(**args) - if not self.processor: - self.processor = self.fireinfo.create_processor(**args) - - self.processor_clock_speed = _processor.get("speed", None) - self.processor_bogomips = _processor.get("bogomips", None) - - arch = _processor.get("arch", None) - if arch: - self.__parse_arch(arch) - - def __parse_kernel(self, kernel): - self.kernel_id = self.fireinfo.get_kernel(kernel) - if not self.kernel_id: - self.kernel_id = self.fireinfo.create_kernel(kernel) - assert self.kernel_id - - self.kernel = kernel - - def __parse_arch(self, arch): - self.arch_id = self.fireinfo.get_arch(arch) - if not self.arch_id: - self.arch_id = self.fireinfo.create_arch(arch) - - self.arch = arch - - def __parse_release(self, release): - # Remove the arch bit - if release: - r = [e for e in release.split() if e] - for s in ("(x86_64)", "(aarch64)", "(i586)", "(armv6l)", "(armv5tel)", "(riscv64)"): - try: - r.remove(s) - break - except ValueError: - pass - - release = " ".join(r) - - self.release_id = self.fireinfo.get_release(release) - if not self.release_id: - self.release_id = self.fireinfo.create_release(release) - assert self.release_id - - self.release = release - - def __parse_language(self, language): - self.language = language - self.language, delim, rest = self.language.partition(".") - self.language, delim, rest = self.language.partition("_") - - def __parse_hypervisor(self, hypervisor): - vendor = hypervisor.get("vendor", "other") - - if vendor in ("other", "unknown"): - self.hypervisor_id = None - return - - self.hypervisor_id = self.fireinfo.get_hypervisor(vendor) - if not self.hypervisor_id: - self.hypervisor_id = self.fireinfo.create_hypervisor(vendor) - - def __parse_network(self, network): - self.network = ProfileNetwork({ - "has_red" : network.get("red", False), - "has_green" : network.get("green", False), - "has_orange" : network.get("orange", False), - "has_blue" : network.get("blue", False), - }) + @lazy_property + def network(self): + return Network(self.backend, self.blob.get("network", {})) class Fireinfo(Object): - def get_profile_count(self, when=None): - res = self.db.get("SELECT COUNT(*) AS count FROM fireinfo_profiles \ - WHERE then_or_now(%s) BETWEEN time_created AND time_valid", when) - - if res: - return res.count - - def get_total_updates_count(self, when=None): - res = self.db.get("SELECT COUNT(*) + SUM(updates) AS count \ - FROM fireinfo_profiles WHERE time_created <= then_or_now(%s)", when) - - if res: - return res.count - - # Parser - - def parse_profile(self, public_id, blob): - return ProfileParser(self.backend, public_id, blob) - - # Profiles - - def profile_exists(self, public_id): - res = self.db.get("SELECT id FROM fireinfo_profiles \ - WHERE public_id = %s LIMIT 1", public_id) - - if res: - return True - - return False - - def profile_rate_limit_active(self, public_id, when=None): - res = self.db.get("SELECT COUNT(*) AS count FROM fireinfo_profiles_log \ - WHERE public_id = %s AND ts >= then_or_now(%s) - INTERVAL '60 minutes'", - public_id, when) - - if res and res.count >= 10: - return True - - return False - - def is_private_id_change_permitted(self, public_id, private_id, when=None): - # Check if a profile exists with a different private id that is still valid - res = self.db.get("SELECT 1 FROM fireinfo_profiles \ - WHERE public_id = %s AND NOT private_id = %s \ - AND time_valid >= then_or_now(%s) LIMIT 1", public_id, private_id, when) - - if res: - return False - - return True - - def get_profile(self, public_id, private_id=None, when=None): - res = self.db.get("SELECT * FROM fireinfo_profiles \ - WHERE public_id = %s AND \ - (CASE WHEN %s IS NULL THEN TRUE ELSE private_id = %s END) AND \ - then_or_now(%s) BETWEEN time_created AND time_valid \ - ORDER BY time_updated DESC LIMIT 1", - public_id, private_id, private_id, when) - - if res: - return Profile(self.backend, res.id, res) + async def expire(self): + """ + Called to expire any profiles that have not been updated in a fortnight + """ + self.db.execute("UPDATE fireinfo SET expired_at = CURRENT_TIMESTAMP \ + WHERE last_updated_at <= CURRENT_TIMESTAMP - %s", datetime.timedelta(days=14)) - def get_profile_with_data(self, public_id, when=None): - res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \ - SELECT * FROM profiles JOIN fireinfo_profiles ON profiles.id = fireinfo_profiles.id \ - WHERE public_id = %s ORDER BY time_updated DESC LIMIT 1", when, public_id) + def _get_profile(self, query, *args, **kwargs): + res = self.db.get(query, *args, **kwargs) if res: - return Profile(self.backend, res.id, res) + return Profile(self.backend, **res) - def get_profiles(self, public_id): - res = self.db.query("SELECT * FROM fireinfo_profiles \ - WHERE public_id = %s ORDER BY time_created DESC", public_id) - - profiles = [] - for row in res: - profile = Profile(self.backend, row.id, row) - profiles.append(profile) - - return profiles - - def create_profile(self, public_id, private_id, when=None): - valid = self.settings.get_int("fireinfo_profile_days_valid", 14) - - res = self.db.get("INSERT INTO fireinfo_profiles(public_id, private_id, \ - time_created, time_updated, time_valid) VALUES(%s, %s, then_or_now(%s), \ - then_or_now(%s), then_or_now(%s) + INTERVAL '%s days') RETURNING id", - public_id, private_id, when, when, when, valid) - - if res: - p = Profile(self.backend, res.id) - p.log_profile_update() - - return p - - # Devices - - def create_device(self, subsystem, vendor, model, sub_vendor=None, sub_model=None, - driver=None, deviceclass=None): - res = self.db.get("INSERT INTO fireinfo_devices(subsystem, vendor, model, \ - sub_vendor, sub_model, driver, deviceclass) VALUES(%s, %s, %s, %s, %s, %s, %s) \ - RETURNING id", subsystem, vendor, model, sub_vendor, sub_model, driver, deviceclass) - - if res: - return Device(self.backend, res.id) - - def get_device(self, subsystem, vendor, model, sub_vendor=None, sub_model=None, - driver=None, deviceclass=None): - res = self.db.get("SELECT * FROM fireinfo_devices \ - WHERE subsystem = %s AND vendor = %s AND model = %s \ - AND sub_vendor IS NOT DISTINCT FROM %s \ - AND sub_model IS NOT DISTINCT FROM %s \ - AND driver IS NOT DISTINCT FROM %s \ - AND deviceclass IS NOT DISTINCT FROM %s \ - LIMIT 1", subsystem, vendor, model, sub_vendor, - sub_model, driver, deviceclass) - - if res: - return Device(self.backend, res.id, res) - - # System - - def create_system(self, vendor, model): - res = self.db.get("INSERT INTO fireinfo_systems(vendor, model) \ - VALUES(%s, %s) RETURNING id", vendor, model) - - if res: - return res.id - - def get_system(self, vendor, model): - res = self.db.get("SELECT id FROM fireinfo_systems WHERE vendor IS NOT DISTINCT FROM %s \ - AND model IS NOT DISTINCT FROM %s LIMIT 1", vendor, model) - - if res: - return res.id - - # Processors - - def create_processor(self, vendor, model_string, family, model, stepping, core_count, flags=None): - res = self.db.get("INSERT INTO fireinfo_processors(vendor, model_string, \ - family, model, stepping, core_count, flags) VALUES(%s, %s, %s, %s, %s, %s, %s) \ - RETURNING id", vendor or None, model_string or None, family, model, stepping, core_count, flags) - - if res: - return Processor(self.backend, res.id) - - def get_processor_by_id(self, processor_id, **kwargs): - res = self.db.get("SELECT * FROM fireinfo_processors \ - WHERE id = %s", processor_id) - - if res: - return Processor(self.backend, res.id, data=res, **kwargs) - - def get_processor(self, vendor, model_string, family, model, stepping, core_count, flags=None): - if flags is None: - flags = [] - - res = self.db.get("SELECT * FROM fireinfo_processors \ - WHERE vendor IS NOT DISTINCT FROM %s AND model_string IS NOT DISTINCT FROM %s \ - AND family IS NOT DISTINCT FROM %s AND model IS NOT DISTINCT FROM %s \ - AND stepping IS NOT DISTINCT FROM %s AND core_count = %s \ - AND flags <@ %s AND flags @> %s", vendor or None, model_string or None, - family, model, stepping, core_count, flags, flags) - - if res: - return Processor(self.backend, res.id, res) - - # Kernel - - def create_kernel(self, kernel): - res = self.db.get("INSERT INTO fireinfo_kernels(name) VALUES(%s) \ - RETURNING id", kernel) - - if res: - return res.id - - def get_kernel(self, kernel): - res = self.db.get("SELECT id FROM fireinfo_kernels WHERE name = %s", kernel) - - if res: - return res.id - - # Arch + def get_profile_count(self, when=None): + if when: + res = self.db.get(""" + SELECT + COUNT(*) AS count + FROM + fireinfo + WHERE + created_at <= %s + AND + ( + expired_at IS NULL + OR + expired_at > %s + ) + """) + else: + res = self.db.get(""" + SELECT + COUNT(*) AS count + FROM + fireinfo + WHERE + expired_at IS NULL + """, + ) - def create_arch(self, arch): - res = self.db.get("INSERT INTO fireinfo_arches(name) VALUES(%s) \ - RETURNING id", arch) + return res.count if res else 0 - if res: - return res.id + def get_profile_histogram(self): + today = datetime.date.today() - def get_arch(self, arch): - res = self.db.get("SELECT id FROM fireinfo_arches WHERE name = %s", arch) + t1 = datetime.date(year=today.year - 10, month=today.month, day=1) + t2 = datetime.date(year=today.year, month=today.month, day=1) - if res: - return res.id + res = self.db.query(""" + SELECT + date, + COUNT(*) AS count + FROM + generate_series(%s, %s, INTERVAL '1 month') date + JOIN + fireinfo ON date >= created_at + AND (expired_at IS NULL OR expired_at > date) + GROUP BY + date + """, t1, t2) - # Release + return { row.date : row.count for row in res } - def create_release(self, release): - res = self.db.get("INSERT INTO fireinfo_releases(name) VALUES(%s) \ - RETURNING id", release) + # Profiles - if res: - return res.id + def get_profile(self, profile_id, when=None): + if when: + return self._get_profile(""" + SELECT + * + FROM + fireinfo + WHERE + profile_id = %s + AND + %s BETWEEN created_at AND expired_at + """, profile_id, + ) - def get_release(self, release): - res = self.db.get("SELECT id FROM fireinfo_releases WHERE name = %s", release) + return self._get_profile(""" + SELECT + * + FROM + fireinfo + WHERE + profile_id = %s + AND + expired_at IS NULL + """, profile_id, + ) - if res: - return res.id + # Handle profile - def get_release_penetration(self, release, when=None): - res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \ - SELECT COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS penetration FROM profiles \ - LEFT JOIN fireinfo_profiles_releases ON profiles.id = fireinfo_profiles_releases.profile_id \ - WHERE fireinfo_profiles_releases.release_id = %s", when, release.fireinfo_id) + def handle_profile(self, profile_id, blob, country_code=None, when=None): + private_id = blob.get("private_id", None) + assert private_id - if res: - return res.penetration + now = datetime.datetime.utcnow() - def get_random_country_penetration(self): - res = self.db.get("SELECT * FROM fireinfo_country_percentages \ - ORDER BY RANDOM() LIMIT 1") + # Fetch the profile version + version = blob.get("profile_version") - if res: - return database.Row({ - "country" : iso3166.countries.get(res.location), - "percentage" : res.count, - }) + # Extract the profile + profile = blob.get("profile") - # Hypervisor + # Validate the profile + self._validate(profile_id, version, profile) - def create_hypervisor(self, hypervisor): - res = self.db.get("INSERT INTO fireinfo_hypervisors(name) VALUES(%s) \ - RETURNING id", hypervisor) + # Pre-process the profile + profile = self._preprocess(profile) - if res: - return res.id + # Fetch the previous profile + prev = self.get_profile(profile_id) - def get_hypervisor(self, hypervisor): - res = self.db.get("SELECT id FROM fireinfo_hypervisors WHERE name = %s", - hypervisor) + if prev: + # Check if the private ID matches + if not prev.private_id == private_id: + logging.error("Private ID for profile %s does not match" % profile_id) + return False - if res: - return res.id + # Check when the last update was + elif now - prev.last_updated_at < datetime.timedelta(hours=6): + logging.warning("Profile %s has been updated too soon" % profile_id) + return False - # Handle profile + # Check if the profile has changed + elif prev.version == version and prev.blob == blob: + logging.debug("Profile %s has not changed" % profile_id) + + # Update the timestamp + self.db.execute("UPDATE fireinfo SET last_updated_at = CURRENT_TIMESTAMP \ + WHERE profile_id = %s AND expired_at IS NULL", profile_id) + + return True + + # Delete the previous profile + self.db.execute("UPDATE fireinfo SET expired_at = CURRENT_TIMESTAMP \ + WHERE profile_id = %s AND expired_at IS NULL", profile_id) + + # Store the new profile + self.db.execute(""" + INSERT INTO + fireinfo + ( + profile_id, + private_id, + version, + blob, + country_code + ) + VALUES + ( + %s, + %s, + %s, + %s, + %s + ) + """, profile_id, private_id, version, json.dumps(profile), country_code, + ) - def handle_profile(self, *args, **kwargs): - self.db.execute("START TRANSACTION") + def _validate(self, profile_id, version, blob): + """ + Validate the profile + """ + if not version == 0: + raise ValueError("Unsupported profile version") - # Wrap all the handling of the profile in a huge transaction. + # Validate the blob try: - self._handle_profile(*args, **kwargs) - - except: - self.db.execute("ROLLBACK") - raise - - else: - self.db.execute("COMMIT") - - def _handle_profile(self, public_id, profile_blob, country_code=None, when=None): - private_id = profile_blob.get("private_id", None) - assert private_id - - # Check if the profile already exists in the database. - profile = self.fireinfo.get_profile(public_id, private_id=private_id, when=when) + return jsonschema.validate(blob, schema=PROFILE_SCHEMA) - # Check if the update can actually be updated - if profile and self.fireinfo.profile_rate_limit_active(public_id, when=when): - logging.warning("There were too many updates for this profile in the last hour: %s" % public_id) - return - - elif not self.is_private_id_change_permitted(public_id, private_id, when=when): - logging.warning("Changing private id is not permitted for profile: %s" % public_id) - return - - # Parse the profile - profile_parser = self.parse_profile(public_id, profile_blob) + # Raise a ValueError instead which is easier to handle later on + except jsonschema.exceptions.ValidationError as e: + raise ValueError("%s" % e) from e - # If a profile exists, check if it matches and if so, just update the - # timestamp. - if profile: - # Check if the profile has changed. If so, update the data. - if profile_parser.equals(profile): - profile.updated(profile_parser, country_code=country_code, when=when) - return + def _preprocess(self, blob): + """ + Modifies the profile before storing it + """ + # Remove the architecture from the release string + blob["system"]["release"]= self._filter_release(blob["system"]["release"]) - # If it does not match, we assume that it is expired and - # create a new profile. - profile.expired(when=when) + return blob - # Replace the old profile with a new one - profile = self.fireinfo.create_profile(public_id, private_id, when=when) - profile.parse(profile_parser) + def _filter_release(self, release): + """ + Removes the arch part + """ + r = [e for e in release.split() if e] - if country_code: - profile.set_country_code(country_code) + for s in ("(x86_64)", "(aarch64)", "(i586)", "(armv6l)", "(armv5tel)", "(riscv64)"): + try: + r.remove(s) + break + except ValueError: + pass - return profile + return " ".join(r) # Data outputs def get_random_profile(self, when=None): - # Check if the architecture exists so that we pick a profile with some data - res = self.db.get("SELECT public_id FROM fireinfo_profiles \ - LEFT JOIN fireinfo_profiles_arches ON fireinfo_profiles.id = fireinfo_profiles_arches.profile_id \ - WHERE fireinfo_profiles_arches.profile_id IS NOT NULL \ - AND then_or_now(%s) BETWEEN time_created AND time_valid ORDER BY RANDOM() LIMIT 1", when) + if when: + return self._get_profile(""" + SELECT + * + FROM + fireinfo + WHERE + created_at <= %s + AND + ( + expired_at IS NULL + OR + expired_at > %s + ) + ORDER BY + RANDOM() + LIMIT + 1 + """, when, when, + ) - if res: - return res.public_id + return self._get_profile(""" + SELECT + * + FROM + fireinfo + WHERE + expired_at IS NULL + ORDER BY + RANDOM() + LIMIT + 1 + """) def get_active_profiles(self, when=None): - res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_at(%s) AS id) \ - SELECT COUNT(*) AS with_data, (SELECT COUNT(*) FROM profiles) AS count FROM profiles \ - LEFT JOIN fireinfo_profiles_releases ON profiles.id = fireinfo_profiles_releases.profile_id \ - WHERE fireinfo_profiles_releases.profile_id IS NOT NULL", when) + if when: + raise NotImplementedError - if res: - return res.with_data, res.count - - def get_archive_size(self, when=None): - res = self.db.get("SELECT COUNT(*) AS count FROM fireinfo_profiles \ - WHERE time_created <= then_or_now(%s)", when) + else: + res = self.db.get(""" + SELECT + COUNT(*) AS total_profiles, + COUNT(*) FILTER (WHERE blob IS NOT NULL) AS active_profiles + FROM + fireinfo + WHERE + expired_at IS NULL + """) if res: - return res.count - - def get_geo_location_map(self, when=None, minimum_percentage=0): - res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_at(%s) AS id) \ - SELECT location, COUNT(location)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \ - LEFT JOIN fireinfo_profiles_locations ON profiles.id = fireinfo_profiles_locations.profile_id \ - WHERE fireinfo_profiles_locations.location IS NOT NULL GROUP BY location \ - HAVING COUNT(location)::float / (SELECT COUNT(*) FROM profiles) >= %s ORDER BY count DESC", - when, minimum_percentage) - - return list(((r.location, r.count) for r in res)) + return res.active_profiles, res.total_profiles + + def get_geo_location_map(self, when=None): + if when: + res = self.db.query(""" + SELECT + country_code, + fireinfo_percentage( + COUNT(*), SUM(COUNT(*)) OVER () + ) AS p + FROM + fireinfo + WHERE + created_at <= %s + AND + ( + expired_at IS NULL + OR + expired_at > %s + ) + AND + country_code IS NOT NULL + GROUP BY + country_code + """, when, when) + else: + res = self.db.query(""" + SELECT + country_code, + fireinfo_percentage( + COUNT(*), SUM(COUNT(*)) OVER () + ) AS p + FROM + fireinfo + WHERE + expired_at IS NULL + AND + country_code IS NOT NULL + GROUP BY + country_code + """) + + return { row.country_code : row.p for row in res } @property def cpu_vendors(self): - res = self.db.query("SELECT DISTINCT vendor FROM fireinfo_processors ORDER BY vendor") + res = self.db.query(""" + SELECT DISTINCT + blob->'cpu'->'vendor' AS vendor + FROM + fireinfo + WHERE + blob->'cpu'->'vendor' IS NOT NULL + """, + ) - return (CPU_VENDORS.get(r.vendor, r.vendor) for r in res) + return sorted((CPU_VENDORS.get(row.vendor, row.vendor) for row in res)) def get_cpu_vendors_map(self, when=None): - res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \ - SELECT COALESCE(vendor, %s) AS vendor, COUNT(vendor)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \ - LEFT JOIN fireinfo_profiles_processors ON profiles.id = fireinfo_profiles_processors.profile_id \ - LEFT JOIN fireinfo_processors ON fireinfo_profiles_processors.processor_id = fireinfo_processors.id \ - WHERE NOT fireinfo_profiles_processors.processor_id IS NULL GROUP BY vendor ORDER BY count DESC", when, "Unknown") - - return ((CPU_VENDORS.get(r.vendor, r.vendor), r.count) for r in res) - - def get_cpu_clock_speeds(self, when=None): - res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \ - SELECT AVG(fireinfo_profiles_processors.clock_speed) AS avg, \ - STDDEV(fireinfo_profiles_processors.clock_speed) AS stddev, \ - MIN(fireinfo_profiles_processors.clock_speed) AS min, \ - MAX(fireinfo_profiles_processors.clock_speed) AS max FROM profiles \ - LEFT JOIN fireinfo_profiles_processors ON profiles.id = fireinfo_profiles_processors.profile_id \ - WHERE NOT fireinfo_profiles_processors.processor_id IS NULL \ - AND fireinfo_profiles_processors.clock_speed > 0 \ - AND fireinfo_profiles_processors.clock_speed < fireinfo_profiles_processors.bogomips \ - AND fireinfo_profiles_processors.bogomips <= %s", when, 10000) + if when: + raise NotImplementedError - if res: - return (res.avg or 0, res.stddev or 0, res.min or 0, res.max or 0) - - def get_cpus_with_platform_and_flag(self, platform, flag, when=None): - res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \ - processors AS (SELECT fireinfo_processors.id AS id, fireinfo_processors.flags AS flags FROM profiles \ - LEFT JOIN fireinfo_profiles_processors ON profiles.id = fireinfo_profiles_processors.profile_id \ - LEFT JOIN fireinfo_processors ON fireinfo_profiles_processors.processor_id = fireinfo_processors.id \ - LEFT JOIN fireinfo_profiles_arches ON profiles.id = fireinfo_profiles_arches.profile_id \ - LEFT JOIN fireinfo_arches ON fireinfo_profiles_arches.arch_id = fireinfo_arches.id \ - WHERE NOT fireinfo_profiles_processors.processor_id IS NULL \ - AND fireinfo_arches.platform = %s AND NOT 'hypervisor' = ANY(fireinfo_processors.flags)) \ - SELECT (COUNT(*)::float / (SELECT NULLIF(COUNT(*), 0) FROM processors)) AS count FROM processors \ - WHERE %s = ANY(processors.flags)", when, platform, flag) - - return res.count or 0 - - def get_common_cpu_flags_by_platform(self, platform, when=None): - if platform == "arm": - flags = ( - "lpae", "neon", "thumb", "thumbee", "vfpv3", "vfpv4", - ) - elif platform == "x86": - flags = ( - "aes", "avx", "avx2", "lm", "mmx", "mmxext", "nx", "pae", - "pni", "popcnt", "sse", "sse2", "rdrand", "ssse3", "sse4a", - "sse4_1", "sse4_2", "pclmulqdq", "rdseed", - ) else: - return + res = self.db.query(""" + SELECT + blob->'cpu'->'vendor' AS vendor, + fireinfo_percentage( + COUNT(*), SUM(COUNT(*)) OVER () + ) AS p + FROM + fireinfo + WHERE + expired_at IS NULL + AND + blob IS NOT NULL + AND + blob->'cpu'->'vendor' IS NOT NULL + GROUP BY + blob->'cpu'->'vendor' + """) + + return { CPU_VENDORS.get(row.vendor, row.vendor) : row.p for row in res } + + def get_cpu_flags_map(self, when=None): + if when: + raise NotImplementedError - ret = [] - for flag in flags: - ret.append((flag, self.get_cpus_with_platform_and_flag(platform, flag, when=when))) + else: + res = self.db.query(""" + WITH arch_flags AS ( + SELECT + ROW_NUMBER() OVER (PARTITION BY blob->'cpu'->'arch') AS id, + blob->'cpu'->'arch' AS arch, + blob->'cpu'->'flags' AS flags + FROM + fireinfo + WHERE + expired_at IS NULL + AND + blob->'cpu'->'arch' IS NOT NULL + AND + blob->'cpu'->'flags' IS NOT NULL + + -- Filter out virtual systems + AND + CAST((blob->'system'->'virtual') AS boolean) IS FALSE + ) + + SELECT + arch, + flag, + fireinfo_percentage( + COUNT(*), + ( + SELECT + MAX(id) + FROM + arch_flags __arch_flags + WHERE + arch_flags.arch = __arch_flags.arch + ) + ) AS p + FROM + arch_flags, jsonb_array_elements(arch_flags.flags) AS flag + GROUP BY + arch, flag + """) + + result = {} - # Add virtual CPU flag "virt" for virtualization support - if platform == "x86": - ret.append(("virt", - self.get_cpus_with_platform_and_flag(platform, "vmx", when=when) + \ - self.get_cpus_with_platform_and_flag(platform, "svm", when=when))) + for row in res: + try: + result[row.arch][row.flag] = row.p + except KeyError: + result[row.arch] = { row.flag : row.p } - return sorted(ret, key=lambda x: x[1], reverse=True) + return result def get_average_memory_amount(self, when=None): - res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \ - SELECT AVG(fireinfo_profiles_memory.amount) AS avg FROM profiles \ - LEFT JOIN fireinfo_profiles_memory ON profiles.id = fireinfo_profiles_memory.profile_id", when) - - if res: - return res.avg or 0 + if when: + res = self.db.get(""" + SELECT + AVG( + CAST(blob->'system'->'memory' AS numeric) + ) AS memory + FROM + fireinfo + WHERE + created_at <= %s + AND + ( + expired_at IS NULL + OR + expired_at > %s + ) + """, when) + else: + res = self.db.get(""" + SELECT + AVG( + CAST(blob->'system'->'memory' AS numeric) + ) AS memory + FROM + fireinfo + WHERE + expired_at IS NULL + """,) + + return res.memory if res else 0 def get_arch_map(self, when=None): - res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \ - SELECT fireinfo_arches.name AS arch, COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count \ - FROM profiles \ - LEFT JOIN fireinfo_profiles_arches ON profiles.id = fireinfo_profiles_arches.profile_id \ - LEFT JOIN fireinfo_arches ON fireinfo_profiles_arches.arch_id = fireinfo_arches.id \ - WHERE NOT fireinfo_profiles_arches.profile_id IS NULL \ - GROUP BY fireinfo_arches.id ORDER BY count DESC", when) + if when: + raise NotImplementedError - return ((r.arch, r.count) for r in res) + else: + res = self.db.query(""" + SELECT + blob->'cpu'->'arch' AS arch, + fireinfo_percentage( + COUNT(*), SUM(COUNT(*)) OVER () + ) AS p + FROM + fireinfo + WHERE + expired_at IS NULL + AND + blob->'cpu'->'arch' IS NOT NULL + GROUP BY + blob->'cpu'->'arch' + """) + + return { row.arch : row.p for row in res } # Virtual def get_hypervisor_map(self, when=None): - res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \ - virtual_profiles AS (SELECT profiles.id AS profile_id, fireinfo_profiles_virtual.hypervisor_id FROM profiles \ - LEFT JOIN fireinfo_profiles_virtual ON profiles.id = fireinfo_profiles_virtual.profile_id \ - WHERE fireinfo_profiles_virtual.profile_id IS NOT NULL) \ - SELECT COALESCE(fireinfo_hypervisors.name, %s) AS name, \ - COUNT(*)::float / (SELECT COUNT(*) FROM virtual_profiles) AS count FROM virtual_profiles \ - LEFT JOIN fireinfo_hypervisors ON virtual_profiles.hypervisor_id = fireinfo_hypervisors.id \ - GROUP BY fireinfo_hypervisors.name ORDER BY count DESC", when, "unknown") - - return ((r.name, r.count) for r in res) + if when: + raise NotImplementedError + else: + res = self.db.query(""" + SELECT + blob->'hypervisor'->'vendor' AS vendor, + fireinfo_percentage( + COUNT(*), SUM(COUNT(*)) OVER () + ) AS p + FROM + fireinfo + WHERE + expired_at IS NULL + AND + CAST((blob->'system'->'virtual') AS boolean) IS TRUE + AND + blob->'hypervisor'->'vendor' IS NOT NULL + GROUP BY + blob->'hypervisor'->'vendor' + """) + + return { row.vendor : row.p for row in res } def get_virtual_ratio(self, when=None): - res = self.db.get("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \ - SELECT COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \ - LEFT JOIN fireinfo_profiles_virtual ON profiles.id = fireinfo_profiles_virtual.profile_id \ - WHERE fireinfo_profiles_virtual.profile_id IS NOT NULL", when) + if when: + raise NotImplementedError - if res: - return res.count + else: + res = self.db.get(""" + SELECT + fireinfo_percentage( + COUNT(*) FILTER ( + WHERE CAST((blob->'system'->'virtual') AS boolean) IS TRUE + ), + COUNT(*) + ) AS p + FROM + fireinfo + WHERE + expired_at IS NULL + AND + blob IS NOT NULL + """) + + return res.p if res else 0 # Releases def get_releases_map(self, when=None): - res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \ - SELECT fireinfo_releases.name, COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \ - LEFT JOIN fireinfo_profiles_releases ON profiles.id = fireinfo_profiles_releases.profile_id \ - LEFT JOIN fireinfo_releases ON fireinfo_profiles_releases.release_id = fireinfo_releases.id \ - GROUP BY fireinfo_releases.name ORDER BY count DESC", when) + if when: + raise NotImplementedError - return ((r.name, r.count) for r in res) + else: + res = self.db.query(""" + SELECT + blob->'system'->'release' AS release, + fireinfo_percentage( + COUNT(*), SUM(COUNT(*)) OVER () + ) AS p + FROM + fireinfo + WHERE + expired_at IS NULL + AND + blob IS NOT NULL + AND + blob->'system'->'release' IS NOT NULL + GROUP BY + blob->'system'->'release' + """) + + return { row.release : row.p for row in res } + + # Kernels def get_kernels_map(self, when=None): - res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \ - SELECT fireinfo_kernels.name, COUNT(*)::float / (SELECT COUNT(*) FROM profiles) AS count FROM profiles \ - LEFT JOIN fireinfo_profiles_kernels ON profiles.id = fireinfo_profiles_kernels.profile_id \ - LEFT JOIN fireinfo_kernels ON fireinfo_profiles_kernels.kernel_id = fireinfo_kernels.id \ - GROUP BY fireinfo_kernels.name ORDER BY count DESC", when) - - return ((r.name, r.count) for r in res) - - def _process_devices(self, devices): - result = [] - - for dev in devices: - dev = Device(self.backend, dev.get("id", None), dev) - result.append(dev) - - return result - - def get_driver_map(self, driver, when=None): - res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \ - devices AS (SELECT * FROM profiles \ - LEFT JOIN fireinfo_profiles_devices ON profiles.id = fireinfo_profiles_devices.profile_id \ - LEFT JOIN fireinfo_devices ON fireinfo_profiles_devices.device_id = fireinfo_devices.id \ - WHERE driver = %s) \ - SELECT subsystem, model, vendor, driver, deviceclass, \ - COUNT(*)::float / (SELECT COUNT(*) FROM devices) AS percentage FROM devices \ - GROUP BY subsystem, model, vendor, driver, deviceclass \ - ORDER BY percentage DESC", when, driver) + if when: + raise NotImplementedError - return self._process_devices(res) + else: + res = self.db.query(""" + SELECT + blob->'system'->'kernel' AS kernel, + fireinfo_percentage( + COUNT(*), SUM(COUNT(*)) OVER () + ) AS p + FROM + fireinfo + WHERE + expired_at IS NULL + AND + blob IS NOT NULL + AND + blob->'system'->'kernel' IS NOT NULL + GROUP BY + blob->'system'->'kernel' + """) + + return { row.kernel : row.p for row in res } subsystem2class = { "pci" : hwdata.PCI(), @@ -1980,15 +1286,45 @@ class Fireinfo(Object): return cls.get_device(vendor_id, model_id) or "" def get_vendor_list(self, when=None): - res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id) \ - SELECT DISTINCT fireinfo_devices.subsystem AS subsystem, fireinfo_devices.vendor AS vendor FROM profiles \ - LEFT JOIN fireinfo_profiles_devices ON profiles.id = fireinfo_profiles_devices.profile_id \ - LEFT JOIN fireinfo_devices ON fireinfo_profiles_devices.device_id = fireinfo_devices.id \ - WHERE NOT fireinfo_devices.driver = ANY(%s)", when, IGNORED_DEVICES) + if when: + raise NotImplementedError + + else: + res = self.db.query(""" + WITH devices AS ( + SELECT + jsonb_array_elements(blob->'devices') AS device + FROM + fireinfo + WHERE + expired_at IS NULL + AND + blob IS NOT NULL + AND + blob->'devices' IS NOT NULL + AND + jsonb_typeof(blob->'devices') = 'array' + ) + + SELECT + devices.device->'subsystem' AS subsystem, + devices.device->'vendor' AS vendor + FROM + devices + WHERE + devices.device->'subsystem' IS NOT NULL + AND + devices.device->'vendor' IS NOT NULL + AND + NOT devices.device->>'driver' = 'usb' + GROUP BY + subsystem, vendor + """) vendors = {} + for row in res: - vendor = self.get_vendor_string(row.subsystem, row.vendor) + vendor = self.get_vendor_string(row.subsystem, row.vendor) or row.vendor # Drop if vendor could not be determined if vendor is None: @@ -1999,17 +1335,111 @@ class Fireinfo(Object): except KeyError: vendors[vendor] = [(row.subsystem, row.vendor)] - vendors = list(vendors.items()) - return sorted(vendors) + return vendors + + def _get_devices(self, query, *args, **kwargs): + res = self.db.query(query, *args, **kwargs) + + return [Device(self.backend, blob) for blob in res] def get_devices_by_vendor(self, subsystem, vendor, when=None): - res = self.db.query("WITH profiles AS (SELECT fireinfo_profiles_with_data_at(%s) AS id), \ - devices AS (SELECT * FROM profiles \ - LEFT JOIN fireinfo_profiles_devices ON profiles.id = fireinfo_profiles_devices.profile_id \ - LEFT JOIN fireinfo_devices ON fireinfo_profiles_devices.device_id = fireinfo_devices.id \ - WHERE NOT fireinfo_devices.driver = ANY(%s)), \ - vendor_devices AS (SELECT * FROM devices WHERE devices.subsystem = %s AND devices.vendor = %s) \ - SELECT subsystem, model, vendor, driver, deviceclass FROM vendor_devices \ - GROUP BY subsystem, model, vendor, driver, deviceclass", when, IGNORED_DEVICES, subsystem, vendor) - - return self._process_devices(res) + if when: + raise NotImplementedError + + else: + return self._get_devices(""" + WITH devices AS ( + SELECT + jsonb_array_elements(blob->'devices') AS device + FROM + fireinfo + WHERE + expired_at IS NULL + AND + blob IS NOT NULL + AND + blob->'devices' IS NOT NULL + AND + jsonb_typeof(blob->'devices') = 'array' + ) + + SELECT + device.deviceclass, + device.subsystem, + device.vendor, + device.model, + device.driver + FROM + devices, + jsonb_to_record(devices.device) AS device( + deviceclass text, + subsystem text, + vendor text, + sub_vendor text, + model text, + sub_model text, + driver text + ) + WHERE + devices.device->>'subsystem' = %s + AND + devices.device->>'vendor' = %s + AND + NOT devices.device->>'driver' = 'usb' + GROUP BY + device.deviceclass, + device.subsystem, + device.vendor, + device.model, + device.driver + """, subsystem, vendor, + ) + + def get_devices_by_driver(self, driver, when=None): + if when: + raise NotImplementedError + + else: + return self._get_devices(""" + WITH devices AS ( + SELECT + jsonb_array_elements(blob->'devices') AS device + FROM + fireinfo + WHERE + expired_at IS NULL + AND + blob IS NOT NULL + AND + blob->'devices' IS NOT NULL + AND + jsonb_typeof(blob->'devices') = 'array' + ) + + SELECT + device.deviceclass, + device.subsystem, + device.vendor, + device.model, + device.driver + FROM + devices, + jsonb_to_record(devices.device) AS device( + deviceclass text, + subsystem text, + vendor text, + sub_vendor text, + model text, + sub_model text, + driver text + ) + WHERE + devices.device->>'driver' = '%s' + GROUP BY + device.deviceclass, + device.subsystem, + device.vendor, + device.model, + device.driver + """, driver, + ) diff --git a/src/backend/releases.py b/src/backend/releases.py index e399613e..bed051d0 100644 --- a/src/backend/releases.py +++ b/src/backend/releases.py @@ -250,16 +250,6 @@ class Release(Object): if self.__data.blog_id: return self.backend.blog.get_by_id(self.__data.blog_id) - @property - def fireinfo_id(self): - name = self.sname.replace("ipfire-", "IPFire ").replace("-", " - ") - - res = self.db.get("SELECT id FROM fireinfo_releases \ - WHERE name = %s", name) - - if res: - return res.id - @property def stable(self): return self.__data.stable @@ -371,10 +361,13 @@ class Release(Object): # Fireinfo Stuff - @property - def penetration(self): + def get_usage(self, when=None): + name = self.sname.replace("ipfire-", "IPFire ").replace("-", " - ") + # Get penetration from fireinfo - return self.backend.fireinfo.get_release_penetration(self) + releases = self.backend.fireinfo.get_releases_map(when=when) + + return releases.get(name, 0) class Releases(Object): diff --git a/src/templates/fireinfo/admin.html b/src/templates/fireinfo/admin.html index d30811a1..b33989df 100644 --- a/src/templates/fireinfo/admin.html +++ b/src/templates/fireinfo/admin.html @@ -9,7 +9,7 @@

{{ "{:,d}".format(total) }}

-

{{ _("Total amount of profiles") }}

+

{{ _("Total Profiles") }}

@@ -22,4 +22,26 @@ + + {% if histogram %} +
+
+ + + + + + + {% for date in sorted(histogram, reverse=True) %} + + + + + {% end %} +
{{ _("Date") }}{{ _("Total Profiles") }}
{{ format_date(date) }} + {{ histogram[date] }} +
+
+
+ {% end %} {% end block %} diff --git a/src/templates/fireinfo/driver.html b/src/templates/fireinfo/driver.html index c9f4bf2e..c0366594 100644 --- a/src/templates/fireinfo/driver.html +++ b/src/templates/fireinfo/driver.html @@ -14,6 +14,6 @@
- {% module FireinfoDeviceTable(driver_map) %} + {% module FireinfoDeviceTable(devices) %}
{% end block %} diff --git a/src/templates/fireinfo/index.html b/src/templates/fireinfo/index.html index 72d3ac9a..e283a46e 100644 --- a/src/templates/fireinfo/index.html +++ b/src/templates/fireinfo/index.html @@ -34,10 +34,12 @@
{% if latest_release %} + {% set usage = latest_release.get_usage(when=when) %} +

- {{ "%.2f%%" % (latest_release.penetration * 100) }} + {{ "%.2f%%" % (usage * 100) }}

@@ -62,25 +64,29 @@

{{ _("Locations") }}

- {% for country_code, percentage in locations %} + {% for cc in sorted(locations, key=lambda cc: locations[cc], reverse=True) %}
- {% if percentage >= 0.01 %} + {% if locations[cc] >= 0.01 %}
- - {{ format_country_name(country_code) }} + + {{ format_country_name(cc) }}
- {% module ProgressBar(percentage, "success") %} + {% module ProgressBar(locations[cc], "success") %}
{% end %}
{% end %} -

- IPFire_ - {{_("is also running in these countries: %s") % locale.list([(format_country_name(c) or c) for c, p in locations if p < 0.01]) }} -

+ {% set other_countries = [cc for cc in locations if locations[cc] < 0.01] %} + + {% if other_countries %} +

+ IPFire_ + {{_("is also running in these countries: %s") % locale.list([(format_country_name(cc) or cc) for cc in other_countries]) }} +

+ {% end %}
@@ -91,12 +97,12 @@

{{ _("CPU Vendors") }}

- {% for name, percentage in cpu_vendors %} + {% for vendor in sorted(cpu_vendors, key=lambda v: cpu_vendors[v], reverse=True) %}
-
{{ name }}
+
{{ vendor }}
- {% module ProgressBar(percentage, "success") %} + {% module ProgressBar(cpu_vendors[vendor], "success") %}
{% end %} @@ -109,12 +115,12 @@

{{ _("Architectures") }}

- {% for name, percentage in arches %} + {% for arch in sorted(arches, key=lambda a: arches[a], reverse=True) %}
-
{{ name }}
+
{{ arch }}
- {% module ProgressBar(percentage, "success") %} + {% module ProgressBar(arches[arch], "success") %}
{% end %} @@ -145,21 +151,21 @@

{{ _("Top Hypervisors") }}

- {% for name, percentage in hypervisors %} + {% for vendor in sorted(hypervisors, key=lambda v: hypervisors[v], reverse=True) %}
- {% if percentage >= 0.01 %} + {% if hypervisors[vendor] >= 0.01 %}
- {% if name == "unknown" %} + {% if vendor == "unknown" %} {{ _("Unknown") }} - {% elif name == "VMWare" %} + {% elif vendor == "VMWare" %} VMware {% else %} - {{ name }} + {{ vendor }} {% end %}
- {% module ProgressBar(percentage, "success") %} + {% module ProgressBar(hypervisors[vendor], "success") %}
{% end %}
diff --git a/src/templates/fireinfo/processors.html b/src/templates/fireinfo/processors.html index 56dddbcc..ec8bdd27 100644 --- a/src/templates/fireinfo/processors.html +++ b/src/templates/fireinfo/processors.html @@ -3,6 +3,8 @@ {% block title %}{{ _("Processors") }}{% end block %} {% block container %} + {% set map = backend.fireinfo.get_cpu_flags_map(when=when) %} +
@@ -33,11 +35,13 @@
- {% for platform in flags %} -

{{ platform }}

+ {% for arch in sorted(map) %} +

{{ arch }}

- {% for flag, percentage in flags[platform] %} + {% for flag in sorted(map[arch], key=lambda f: map[arch][f], reverse=True) %} + {% set p = map[arch][flag] %} +
{% if flag == "aes" %} {{ _("AES Instruction Set") }} @@ -101,14 +105,14 @@
- {% if percentage >= 0.95 %} - {% module ProgressBar(percentage, "success") %} - {% elif percentage >= 0.5 %} - {% module ProgressBar(percentage, "warning") %} - {% elif percentage >= 0.1 %} - {% module ProgressBar(percentage, "info") %} + {% if p >= 0.95 %} + {% module ProgressBar(p, "success") %} + {% elif p >= 0.5 %} + {% module ProgressBar(p, "warning") %} + {% elif p >= 0.1 %} + {% module ProgressBar(p, "info") %} {% else %} - {% module ProgressBar(percentage, "danger") %} + {% module ProgressBar(p, "danger") %} {% end %}
{% end %} diff --git a/src/templates/fireinfo/profile.html b/src/templates/fireinfo/profile.html index c984294d..09bdc264 100644 --- a/src/templates/fireinfo/profile.html +++ b/src/templates/fireinfo/profile.html @@ -15,36 +15,16 @@
-
-
- {% if profile.appliance_id %} -
-
- {{ _("This is a") }} -
{{ profile.appliance }}
-
- -
- - {{ _("Go to Lightning Wire Labs") }} - -
-
- {% end %} -
-
- -
- {{ _("Running %s") % profile.release }} + {{ _("Running %s") % profile.system.release }}
- {{ _("Last update %s") % locale.format_date(profile.time_updated) }} + {{ _("Last update %s") % locale.format_date(profile.last_updated_at) }}
@@ -64,7 +44,7 @@
- {% if profile.virtual %} + {% if profile.is_virtual() %}
{{ _("Hypervisor") }}
{% if profile.hypervisor == "VMWare" %} @@ -75,19 +55,19 @@ {{ profile.hypervisor }} {% end %}
- {% elif not profile.appliance_id and profile.system %} + {% elif profile.system %}
{{ _("System") }}
- {% if profile.system_vendor %} - {{ profile.system_vendor }} + {% if profile.system.vendor %} + {{ profile.system.vendor }} {% end %} - {% if profile.system_vendor and profile.system_model %} + {% if profile.system.vendor and profile.system.model %} ‐ {% end %} - {% if profile.system_model %} - {{ profile.system_model }} + {% if profile.system.model %} + {{ profile.system.model }} {% end %}
{% end %} @@ -128,10 +108,10 @@ {% end %} - {% if profile.storage %} + {% if profile.system.storage %}
{{ _("Storage") }}
- {{ format_size(profile.storage) }} + {{ format_size(profile.system.storage) }}
{% end %} @@ -142,10 +122,10 @@ {% end %} - {% if profile.language %} + {% if profile.system.language %}
{{ _("Language") }}
- {{ format_language_name(profile.language) }} + {{ format_language_name(profile.system.language) }}
{% end %}
diff --git a/src/templates/fireinfo/releases.html b/src/templates/fireinfo/releases.html index 243878af..d47fea61 100644 --- a/src/templates/fireinfo/releases.html +++ b/src/templates/fireinfo/releases.html @@ -33,24 +33,24 @@
- {% for name, percentage in releases %} + {% for name in sorted(releases, key=lambda n: releases[n], reverse=True) %}
{{ name.replace("core", "Core Update ") }}
- {% module ProgressBar(percentage, "primary") %} + {% module ProgressBar(releases[name], "primary") %}
{% end %}

{{ _("Kernels") }}

- {% for name, percentage in kernels %} + {% for name in sorted(kernels, key=lambda n: kernels[n], reverse=True) %}
{{ name }}
- {% module ProgressBar(percentage, "info") %} + {% module ProgressBar(kernels[name], "info") %}
{% end %} diff --git a/src/templates/fireinfo/vendors.html b/src/templates/fireinfo/vendors.html index 5e66ed74..77b6bd4e 100644 --- a/src/templates/fireinfo/vendors.html +++ b/src/templates/fireinfo/vendors.html @@ -17,15 +17,13 @@
- {% for vendor, subsystems in vendors %} - {% if vendor %} -
{{ vendor }}
-
- {% for subsystem, vendor_id in sorted(subsystems) %} - {{ subsystem.upper() }} - {% end %} -
- {% end %} + {% for vendor in sorted(vendors) %} +
{{ vendor }}
+
+ {% for subsystem, vendor_id in sorted(vendors[vendor]) %} + {{ subsystem.upper() }} + {% end %} +
{% end %}
diff --git a/src/web/fireinfo.py b/src/web/fireinfo.py index 976b9e1d..8b0f8e8e 100644 --- a/src/web/fireinfo.py +++ b/src/web/fireinfo.py @@ -1,13 +1,8 @@ #!/usr/bin/python -import datetime -import logging -import re import json import tornado.web -from .. import fireinfo - from . import base from . import ui_modules @@ -17,100 +12,11 @@ class BaseHandler(base.BaseHandler): return self.get_argument_date("when", None) -MIN_PROFILE_VERSION = 0 -MAX_PROFILE_VERSION = 0 - -class Profile(dict): - def __getattr__(self, key): - try: - return self[key] - except KeyError: - raise AttributeError(key) - - def __setattr__(self, key, val): - self[key] = val - - class ProfileSendHandler(BaseHandler): def check_xsrf_cookie(self): # This cookie is not required here. pass - def prepare(self): - # Create an empty profile. - self.profile = Profile() - - def __check_attributes(self, profile): - """ - Check for attributes that must be provided, - """ - attributes = ( - "private_id", - "profile_version", - "public_id", - ) - for attr in attributes: - if attr not in profile: - raise tornado.web.HTTPError(400, "Profile lacks '%s' attribute: %s" % (attr, profile)) - - def __check_valid_ids(self, profile): - """ - Check if IDs contain valid data. - """ - for id in ("public_id", "private_id"): - if re.match(r"^([a-f0-9]{40})$", "%s" % profile[id]) is None: - raise tornado.web.HTTPError(400, "ID '%s' has wrong format: %s" % (id, profile)) - - def __check_equal_ids(self, profile): - """ - Check if public_id and private_id are equal. - """ - if profile.public_id == profile.private_id: - raise tornado.web.HTTPError(400, "Public and private IDs are equal: %s" % profile) - - def __check_matching_ids(self, profile): - """ - Check if a profile with the given public_id is already in the - database. If so we need to check if the private_id matches. - """ - p = self.profiles.find_one({ "public_id" : profile["public_id"]}) - if not p: - return - - p = Profile(p) - if p.private_id != profile.private_id: - raise tornado.web.HTTPError(400, "Mismatch of private_id: %s" % profile) - - def __check_profile_version(self, profile): - """ - Check if this version of the server software does support the - received profile. - """ - version = profile.profile_version - - if version < MIN_PROFILE_VERSION or version > MAX_PROFILE_VERSION: - raise tornado.web.HTTPError(400, - "Profile version is not supported: %s" % version) - - def check_profile_blob(self, profile): - """ - This method checks if the blob is sane. - """ - checks = ( - self.__check_attributes, - self.__check_valid_ids, - self.__check_equal_ids, - self.__check_profile_version, - # These checks require at least one database query and should be done - # at last. - self.__check_matching_ids, - ) - - for check in checks: - check(profile) - - # If we got here, everything is okay and we can go on... - def get_profile_blob(self): profile = self.get_argument("profile", None) @@ -118,22 +24,14 @@ class ProfileSendHandler(BaseHandler): if not profile: raise tornado.web.HTTPError(400, "No profile received") - # Try to decode the profile. + # Try to decode the profile try: return json.loads(profile) except json.decoder.JSONDecodeError as e: raise tornado.web.HTTPError(400, "Profile could not be decoded: %s" % e) - # The GET method is only allowed in debugging mode. - def get(self, public_id): - if not self.application.settings["debug"]: - raise tornado.web.HTTPError(405) - - return self.post(public_id) - def post(self, public_id): profile_blob = self.get_profile_blob() - #self.check_profile_blob(profile_blob) # Handle the profile. with self.db.transaction(): @@ -141,8 +39,8 @@ class ProfileSendHandler(BaseHandler): self.fireinfo.handle_profile(public_id, profile_blob, country_code=self.current_country_code) - except fireinfo.ProfileParserError as e: - raise tornado.web.HTTPError(400, "Could not parse profile: %s" % e) + except ValueError as e: + raise tornado.web.HTTPError(400, "Could not process profile: %s" % e) self.finish("Your profile was successfully saved to the database.") @@ -150,13 +48,17 @@ class ProfileSendHandler(BaseHandler): class IndexHandler(BaseHandler): def get(self): data = { + "when" : self.when, + # Release "latest_release" : self.backend.releases.get_latest(), # Hardware "arches" : self.fireinfo.get_arch_map(when=self.when), "cpu_vendors" : self.fireinfo.get_cpu_vendors_map(when=self.when), - "memory_avg" : self.backend.fireinfo.get_average_memory_amount(when=self.when), + + # Memory + "memory_avg" : self.backend.fireinfo.get_average_memory_amount(when=self.when), # Virtualization "hypervisors" : self.fireinfo.get_hypervisor_map(when=self.when), @@ -171,8 +73,9 @@ class IndexHandler(BaseHandler): class DriverDetail(BaseHandler): def get(self, driver): - self.render("fireinfo/driver.html", driver=driver, - driver_map=self.fireinfo.get_driver_map(driver, when=self.when)) + devices = self.fireinfo.get_devices_by_driver(driver, when=self.when) + + self.render("fireinfo/driver.html", driver=driver, devices=devices) class ProfileHandler(BaseHandler): @@ -187,11 +90,11 @@ class ProfileHandler(BaseHandler): class RandomProfileHandler(BaseHandler): def get(self): - profile_id = self.fireinfo.get_random_profile(when=self.when) - if profile_id is None: - raise tornado.web.HTTPError(404) + profile = self.fireinfo.get_random_profile(when=self.when) + if not profile: + raise tornado.web.HTTPError(404, "Could not find a random profile") - self.redirect("/profile/%s" % profile_id) + self.redirect("/profile/%s" % profile.profile_id) class ReleasesHandler(BaseHandler): @@ -206,13 +109,7 @@ class ReleasesHandler(BaseHandler): class ProcessorsHandler(BaseHandler): def get(self): - flags = {} - - for platform in ("arm", "x86"): - flags[platform] = \ - self.fireinfo.get_common_cpu_flags_by_platform(platform, when=self.when) - - return self.render("fireinfo/processors.html", flags=flags) + return self.render("fireinfo/processors.html", when=self.when) class VendorsHandler(BaseHandler): @@ -273,4 +170,7 @@ class AdminIndexHandler(BaseHandler): def get(self): with_data, total = self.backend.fireinfo.get_active_profiles() - self.render("fireinfo/admin.html", with_data=with_data, total=total) + histogram = self.backend.fireinfo.get_profile_histogram() + + self.render("fireinfo/admin.html", with_data=with_data, total=total, + histogram=histogram) diff --git a/src/web/iuse.py b/src/web/iuse.py index c7dc3190..faa6d406 100644 --- a/src/web/iuse.py +++ b/src/web/iuse.py @@ -19,7 +19,7 @@ class ImageHandler(base.BaseHandler): def get(self, profile_id, image_id): when = self.get_argument_date("when", None) - profile = self.fireinfo.get_profile_with_data(profile_id, when=when) + profile = self.fireinfo.get_profile(profile_id, when=when) if not profile: raise tornado.web.HTTPError(404, "Profile '%s' was not found." % profile_id) -- 2.47.3