[ipfire.org.git] / src / backend / fireinfo.py

#!/usr/bin/python

import datetime
import iso3166
import json
import jsonschema
import logging
import re

from . import hwdata
from . import util
from .misc import Object
from .decorators import *

N_ = lambda x: x

CPU_VENDORS = {
	"AMDisbetter!" : "AMD",
	"AuthenticAMD" : "AMD",
	"CentaurHauls" : "VIA",
	"CyrixInstead" : "Cyrix",
	"GenuineIntel" : "Intel",
	"TransmetaCPU" : "Transmeta",
	"GenuineTMx86" : "Transmeta",
	"Geode by NSC" : "NSC",
	"NexGenDriven" : "NexGen",
	"RiseRiseRise" : "Rise",
	"SiS SiS SiS"  : "SiS",
	"SiS SiS SiS " : "SiS",
	"UMC UMC UMC " : "UMC",
	"VIA VIA VIA " : "VIA",
	"Vortex86 SoC" : "Vortex86",
}

CPU_STRINGS = (
	### AMD ###
	# APU
	(r"AMD (Sempron)\(tm\) (\d+) APU with Radeon\(tm\) R\d+", r"AMD \1 \2 APU"),
	(r"AMD ([\w\-]+) APU with Radeon\(tm\) HD Graphics", r"AMD \1 APU"),
	(r"AMD ([\w\-]+) Radeon R\d+, \d+ Compute Cores \d+C\+\d+G", r"AMD \1 APU"),
	# Athlon
	(r"AMD Athlon.* II X2 ([a-z0-9]+).*", r"AMD Athlon X2 \1"),
	(r"AMD Athlon\(tm\) 64 Processor (\w+)", r"AMD Athlon64 \1"),
	(r"AMD Athlon\(tm\) 64 X2 Dual Core Processor (\w+)", r"AMD Athlon64 X2 \1"),
	(r"(AMD Athlon).*(XP).*", r"\1 \2"),
	(r"(AMD Phenom).* ([0-9]+) .*", r"\1 \2"),
	(r"(AMD Phenom).*", r"\1"),
	(r"(AMD Sempron).*", r"\1"),
	# Geode
	(r"Geode\(TM\) Integrated Processor by AMD PCS", r"AMD Geode"),
	(r"(Geode).*", r"\1"),
	# Mobile
	(r"Mobile AMD (Athlon|Sempron)\(tm\) Processor (\d+\+?)", r"AMD \1-M \2"),

	# Intel
	(r"Intel\(R\) (Atom|Celeron).*CPU\s*([A-Z0-9]+) .*", r"Intel \1 \2"),
	(r"(Intel).*(Celeron).*", r"\1 \2"),
	(r"Intel\(R\)? Core\(TM\)?2 Duo *CPU .* ([A-Z0-9]+) .*", r"Intel C2D \1"),
	(r"Intel\(R\)? Core\(TM\)?2 Duo CPU (\w+)", r"Intel C2D \1"),
	(r"Intel\(R\)? Core\(TM\)?2 CPU .* ([A-Z0-9]+) .*", r"Intel C2 \1"),
	(r"Intel\(R\)? Core\(TM\)?2 Quad *CPU .* ([A-Z0-9]+) .*", r"Intel C2Q \1"),
	(r"Intel\(R\)? Core\(TM\)? (i[753]\-\w+) CPU", r"Intel Core \1"),
	(r"Intel\(R\)? Xeon\(R\)? CPU (\w+) (0|v\d+)", r"Intel Xeon \1 \2"),
	(r"Intel\(R\)? Xeon\(R\)? CPU\s+(\w+)", r"Intel Xeon \1"),
	(r"(Intel).*(Xeon).*", r"\1 \2"),
	(r"Intel.* Pentium.* (D|4) .*", r"Intel Pentium \1"),
	(r"Intel.* Pentium.* Dual .* ([A-Z0-9]+) .*", r"Intel Pentium Dual \1"),
	(r"Pentium.* Dual-Core .* ([A-Z0-9]+) .*", r"Intel Pentium Dual \1"),
	(r"(Pentium I{2,3}).*", r"Intel \1"),
	(r"(Celeron \(Coppermine\))", r"Intel Celeron"),

	# NSC
	(r"Geode\(TM\) Integrated Processor by National Semi", r"NSC Geode"),

	# VIA
	(r"(VIA \w*).*", r"\1"),

	# Qemu
	(r"QEMU Virtual CPU version .*", r"QEMU CPU"),

	# ARM
	(r"Feroceon .*", r"ARM Feroceon"),
)

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", "null"],
				},
				"flags" : {
					"type" : "array",
					"items" : {
						"type"    : "string",
						"pattern" : r"^.{,24}$",
					},
				},
				"model" : {
					"type" : "integer",
				},
				"model_string" : {
					"type" : ["string", "null"],
					"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",
			],
		},

		# 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",
				],
			},
		},

		# Network
		"network" : {
			"type" : "object",
			"properties" : {
				"blue" : {
					"type" : "boolean",
				},
				"green" : {
					"type" : "boolean",
				},
				"orange" : {
					"type" : "boolean",
				},
				"red" : {
					"type" : "boolean",
				},
			},
			"additionalProperties" : False,
		},

		# System
		"system" : {
			"type" : "object",
			"properties" : {
				"kernel_release" : {
					"type"    : "string",
					"pattern" : r"^.{,40}$",
				},
				"language" : {
					"type"    : "string",
					"pattern" : r"^[a-z]{2}(\.utf8)?$",
				},
				"memory" : {
					"type" : "integer",
				},
				"model" : {
					"type"    : ["string", "null"],
					"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",
			],
		},

		# Hypervisor
		"hypervisor" : {
			"type" : "object",
			"properties" : {
				"vendor" : {
					"type"    : "string",
					"pattern" : r"^.{,40}$",
				},
			},
			"additionalProperties" : False,
			"required" : [
				"vendor",
			],
		},

		# Error - BogoMIPS
		"bogomips" : {
			"type" : "number",
		},
	},
	"additionalProperties" : False,
	"required" : [
		"cpu",
		"devices",
		"network",
		"system",
	],
}

class Network(Object):
	def init(self, blob):
		self.blob = blob

	def __iter__(self):
		ret = []

		for zone in ("red", "green", "orange", "blue"):
			if self.has_zone(zone):
				ret.append(zone)

		return iter(ret)

	def has_zone(self, name):
		return self.blob.get(name, False)

	@property
	def has_red(self):
		return self.has_zone("red")

	@property
	def has_green(self):
		return self.has_zone("green")

	@property
	def has_orange(self):
		return self.has_zone("orange")

	@property
	def has_blue(self):
		return self.has_zone("blue")


class Processor(Object):
	def init(self, blob):
		self.blob = blob

	def __str__(self):
		s = []

		if self.model_string and not self.model_string.startswith(self.vendor):
			s.append(self.vendor)
			s.append("-")

		s.append(self.model_string or "Generic")

		if self.core_count > 1:
			s.append("x%s" % self.core_count)

		return " ".join(s)

	@property
	def arch(self):
		return self.blob.get("arch")

	@property
	def vendor(self):
		vendor = self.blob.get("vendor")

		try:
			return CPU_VENDORS[vendor]
		except KeyError:
			return vendor

	@property
	def family(self):
		return self.blob.get("family")

	@property
	def model(self):
		return self.blob.get("model")

	@property
	def stepping(self):
		return self.blob.get("stepping")

	@property
	def model_string(self):
		return self.blob.get("model_string")

	@property
	def flags(self):
		return self.blob.get("flags")

	def has_flag(self, flag):
		return flag in self.flags

	def uses_ht(self):
		if self.vendor == "Intel" and self.family == 6 and self.model in (15, 55, 76, 77):
			return False

		return self.has_flag("ht")

	@property
	def core_count(self):
		return self.blob.get("count", 1)

	@property
	def count(self):
		if self.uses_ht():
			return self.core_count // 2

		return self.core_count

	@property
	def clock_speed(self):
		return self.blob.get("speed", 0)

	def format_clock_speed(self):
		if not self.clock_speed:
			return

		if self.clock_speed < 1000:
			return "%dMHz" % self.clock_speed

		return "%.2fGHz" % round(self.clock_speed / 1000, 2)

	@property
	def bogomips(self):
		return self.__bogomips

	@property
	def capabilities(self):
		caps = [
			("64bit",  self.has_flag("lm")),
			("aes",    self.has_flag("aes")),
			("nx",     self.has_flag("nx")),
			("pae",    self.has_flag("pae") or self.has_flag("lpae")),
			("rdrand", self.has_flag("rdrand")),
		]

		# If the system is already running in a virtual environment,
		# we cannot correctly detect if the CPU supports svm or vmx
		if self.has_flag("hypervisor"):
			caps.append(("virt", None))
		else:
			caps.append(("virt", self.has_flag("vmx") or self.has_flag("svm")))

		return caps

	def format_model(self):
		s = self.model_string or ""

		# Remove everything after the @: Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz
		s, sep, rest = s.partition("@")

		for pattern, repl in CPU_STRINGS:
			if re.match(pattern, s) is None:
				continue

			s = re.sub(pattern, repl, s)
			break

		# Otherwise remove the symbols
		for i in ("C", "R", "TM", "tm"):
			s = s.replace("(%s)" % i, "")

		# Replace too long strings with shorter ones
		pairs = (
			("Quad-Core Processor", ""),
			("Dual-Core Processor", ""),
			("Processor", "CPU"),
			("processor", "CPU"),
		)
		for k, v in pairs:
			s = s.replace(k, v)

		# Remove too many spaces
		s = " ".join((e for e in s.split() if e))

		return s

	@property
	def friendly_string(self):
		s = []

		model = self.format_model()
		if model:
			s.append(model)

		clock_speed = self.format_clock_speed()
		if clock_speed:
			s.append("@ %s" % clock_speed)

		if self.count > 1:
			s.append("x%s" % self.count)

		return " ".join(s)


class Device(Object):
	classid2name = {
		"pci" : {
			"00" : N_("Unclassified"),
			"01" : N_("Mass storage"),
			"02" : N_("Network"),
			"03" : N_("Display"),
			"04" : N_("Multimedia"),
			"05" : N_("Memory controller"),
			"06" : N_("Bridge"),
			"07" : N_("Communication"),
			"08" : N_("Generic system peripheral"),
			"09" : N_("Input device"),
			"0a" : N_("Docking station"),
			"0b" : N_("Processor"),
			"0c" : N_("Serial bus"),
			"0d" : N_("Wireless"),
			"0e" : N_("Intelligent controller"),
			"0f" : N_("Satellite communications controller"),
			"10" : N_("Encryption"),
			"11" : N_("Signal processing controller"),
			"ff" : N_("Unassigned class"),
		},

		"usb" : {
			"00" : N_("Unclassified"),
			"01" : N_("Multimedia"),
			"02" : N_("Communication"),
			"03" : N_("Input device"),
			"05" : N_("Generic system peripheral"),
			"06" : N_("Image"),
			"07" : N_("Printer"),
			"08" : N_("Mass storage"),
			"09" : N_("Hub"),
			"0a" : N_("Communication"),
			"0b" : N_("Smart card"),
			"0d" : N_("Encryption"),
			"0e" : N_("Display"),
			"0f" : N_("Personal Healthcare"),
			"dc" : N_("Diagnostic Device"),
			"e0" : N_("Wireless"),
			"ef" : N_("Unclassified"),
			"fe" : N_("Unclassified"),
			"ff" : N_("Unclassified"),
		}
	}

	def init(self, blob):
		self.blob = blob

	def __repr__(self):
		return "<%s vendor=%s model=%s>" % (self.__class__.__name__,
			self.vendor_string, self.model_string)

	def __eq__(self, other):
		if isinstance(other, self.__class__):
			return self.blob == other.blob

		return NotImplemented

	def __lt__(self, other):
		if isinstance(other, self.__class__):
			return self.cls < other.cls or \
				self.vendor_string < other.vendor_string or \
				self.vendor < other.vendor or \
				self.model_string < other.model_string or \
				self.model < other.model

		return NotImplemented

	def is_showable(self):
		if self.driver in ("usb", "pcieport", "hub"):
			return False

		return True

	@property
	def subsystem(self):
		return self.blob.get("subsystem")

	@property
	def model(self):
		return self.blob.get("model")

	@lazy_property
	def model_string(self):
		return self.fireinfo.get_model_string(self.subsystem, self.vendor, self.model)

	@property
	def vendor(self):
		return self.blob.get("vendor")

	@lazy_property
	def vendor_string(self):
		return self.fireinfo.get_vendor_string(self.subsystem, self.vendor)

	@property
	def driver(self):
		return self.blob.get("driver")

	@lazy_property
	def cls(self):
		classid = self.blob.get("deviceclass")

		if self.subsystem == "pci":
			classid = classid[:-4]
			if len(classid) == 1:
				classid = "0%s" % classid

		elif self.subsystem == "usb" and classid:
			classid = classid.split("/")[0]
			classid = "%02x" % int(classid)

		try:
			return self.classid2name[self.subsystem][classid]
		except KeyError:
			return "N/A"


class System(Object):
	def init(self, blob):
		self.blob = blob

	@property
	def language(self):
		return self.blob.get("language")

	@property
	def vendor(self):
		return self.blob.get("vendor")

	@property
	def model(self):
		return self.blob.get("model")

	@property
	def release(self):
		return self.blob.get("release")

	@property
	def storage(self):
		return self.blob.get("storage_size", 0)

	def is_virtual(self):
		return self.blob.get("virtual", False)


class Hypervisor(Object):
	def init(self, blob):
		self.blob = blob

	def __str__(self):
		return self.vendor

	@property
	def vendor(self):
		return self.blob.get("vendor")


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

	def __repr__(self):
		return "<%s %s>" % (self.__class__.__name__, self.profile_id)

	def is_showable(self):
		return True if self.blob else False

	@property
	def public_id(self):
		"""
			An alias for the profile ID
		"""
		return self.profile_id

	# Location

	@property
	def location(self):
		return self.country_code

	@property
	def location_string(self):
		return self.backend.get_country_name(self.location) or self.location

	# Devices

	@lazy_property
	def devices(self):
		return [Device(self.backend, blob) for blob in self.blob.get("devices", [])]

	# System

	@lazy_property
	def system(self):
		return System(self.backend, self.blob.get("system", {}))

	# Processor

	@property
	def processor(self):
		return Processor(self.backend, self.blob.get("cpu", {}))

	# Memory

	@property
	def memory(self):
		return self.blob.get("memory")

	@property
	def friendly_memory(self):
		return util.format_size(self.memory or 0)

	# Virtual

	def is_virtual(self):
		return self.system.is_virtual()

	@property
	def hypervisor(self):
		return Hypervisor(self.backend, self.blob.get("hypervisor"))

	# Network

	@lazy_property
	def network(self):
		return Network(self.backend, self.blob.get("network", {}))


class Fireinfo(Object):
	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(self, query, *args, **kwargs):
		res = self.db.get(query, *args, **kwargs)

		if res:
			return Profile(self.backend, **res)

	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
				""",
			)

		return res.count if res else 0

	def get_profile_histogram(self):
		today = datetime.date.today()

		t1 = datetime.date(year=today.year - 10, month=today.month, day=1)
		t2 = datetime.date(year=today.year, month=today.month, day=1)

		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)

		return { row.date : row.count for row in res }

	# Profiles

	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,
			)

		return self._get_profile("""
			SELECT
				*
			FROM
				fireinfo
			WHERE
				profile_id = %s
			AND
				expired_at IS NULL
			""", profile_id,
		)

	# Handle profile

	def handle_profile(self, profile_id, blob, country_code=None, asn=None, when=None):
		private_id = blob.get("private_id", None)
		assert private_id

		now = datetime.datetime.utcnow()

		# Fetch the profile version
		version = blob.get("profile_version")

		# Extract the profile
		profile = blob.get("profile")

		if profile:
			# Validate the profile
			self._validate(profile_id, version, profile)

			# Pre-process the profile
			profile = self._preprocess(profile)

		# Fetch the previous profile
		prev = self.get_profile(profile_id)

		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

			# 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

			# 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)

		# Serialise the profile
		if profile:
			profile = json.dumps(profile)

		# Store the new profile
		self.db.execute("""
			INSERT INTO
				fireinfo
			(
				profile_id,
				private_id,
				version,
				blob,
				country_code,
				asn
			)
			VALUES
			(
				%s,
				%s,
				%s,
				%s,
				%s,
				%s
			)
			""", profile_id, private_id, version, profile, country_code, asn,
		)

	def _validate(self, profile_id, version, blob):
		"""
			Validate the profile
		"""
		if not version == 0:
			raise ValueError("Unsupported profile version")

		# Validate the blob
		try:
			return jsonschema.validate(blob, schema=PROFILE_SCHEMA)

		# Raise a ValueError instead which is easier to handle later on
		except jsonschema.exceptions.ValidationError as e:
			raise ValueError("%s" % e) from e

	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"])

		return blob

	def _filter_release(self, release):
		"""
			Removes the arch part
		"""
		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

		return " ".join(r)

	# Data outputs

	def get_random_profile(self, when=None):
		if when:
			return self._get_profile("""
				SELECT
					*
				FROM
					fireinfo
				WHERE
					created_at <= %s
				AND
					(
						expired_at IS NULL
					OR
						expired_at > %s
					)
				AND
					blob IS NOT NULL
				ORDER BY
					RANDOM()
				LIMIT
					1
				""", when, when,
			)

		return self._get_profile("""
			SELECT
				*
			FROM
				fireinfo
			WHERE
				expired_at IS NULL
			AND
				blob IS NOT NULL
			ORDER BY
				RANDOM()
			LIMIT
				1
		""")

	def get_active_profiles(self, when=None):
		if when:
			raise NotImplementedError

		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.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 }

	def get_asn_map(self, when=None):
		if when:
			res = self.db.query("""
				SELECT
					asn,
					fireinfo_percentage(
						COUNT(*), SUM(COUNT(*)) OVER ()
					) AS p,
					COUNT(*) AS c
				FROM
					fireinfo
				WHERE
					created_at <= %s
				AND
					(
						expired_at IS NULL
					OR
						expired_at > %s
					)
				AND
					asn IS NOT NULL
				GROUP BY
					asn
			""", when, when)
		else:
			res = self.db.query("""
				SELECT
					asn,
					fireinfo_percentage(
						COUNT(*), SUM(COUNT(*)) OVER ()
					) AS p,
					COUNT(*) AS c
				FROM
					fireinfo
				WHERE
					expired_at IS NULL
				AND
					asn IS NOT NULL
				GROUP BY
					asn
			""")

		return { self.backend.location.get_as(row.asn) : (row.c, row.p) for row in res }

	@property
	def cpu_vendors(self):
		res = self.db.query("""
			SELECT DISTINCT
				blob->'cpu'->'vendor' AS vendor
			FROM
				fireinfo
			WHERE
				blob->'cpu'->'vendor' IS NOT NULL
			""",
		)

		return sorted((CPU_VENDORS.get(row.vendor, row.vendor) for row in res))

	def get_cpu_vendors_map(self, when=None):
		if when:
			raise NotImplementedError

		else:
			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

		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 = {}

		for row in res:
			try:
				result[row.arch][row.flag] = row.p
			except KeyError:
				result[row.arch] = { row.flag : row.p }

		return result

	def get_average_memory_amount(self, when=None):
		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):
		if when:
			raise NotImplementedError

		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):
		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):
		if when:
			raise NotImplementedError

		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):
		if when:
			raise NotImplementedError

		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):
		if when:
			raise NotImplementedError

		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(),
		"usb" : hwdata.USB(),
	}

	def get_vendor_string(self, subsystem, vendor_id):
		try:
			cls = self.subsystem2class[subsystem]
		except KeyError:
			return ""

		return cls.get_vendor(vendor_id) or ""

	def get_model_string(self, subsystem, vendor_id, model_id):
		try:
			cls = self.subsystem2class[subsystem]
		except KeyError:
			return ""

		return cls.get_device(vendor_id, model_id) or ""

	def get_vendor_list(self, when=None):
		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) or row.vendor

			# Drop if vendor could not be determined
			if vendor is None:
				continue

			try:
				vendors[vendor].append((row.subsystem, row.vendor))
			except KeyError:
				vendors[vendor] = [(row.subsystem, row.vendor)]

		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):
		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,
			)