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

#!/usr/bin/python

import datetime
import iso3166
import logging
import re

from . import database
from . import hwdata
from . import util
from .misc import Object

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

IGNORED_DEVICES = ["usb",]

class ProfileDict(object):
	def __init__(self, data):
		self._data = data


class ProfileNetwork(ProfileDict):
	def __eq__(self, other):
		if other is None:
			return False

		if not self.has_red == other.has_red:
			return False

		if not self.has_green == other.has_green:
			return False

		if not self.has_orange == other.has_orange:
			return False

		if not self.has_blue == other.has_blue:
			return False

		return True

	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._data.get("has_%s" % name)

	@property
	def has_red(self):
		return self._data.get("has_red", False)

	@property
	def has_green(self):
		return self._data.get("has_green", False)

	@property
	def has_orange(self):
		return self._data.get("has_orange", False)

	@property
	def has_blue(self):
		return self._data.get("has_blue", False)


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 __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 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):
		try:
			return CPU_VENDORS[self.data.vendor]
		except KeyError:
			return self.data.vendor

	@property
	def family(self):
		return self.data.family

	@property
	def model(self):
		return self.data.model

	@property
	def stepping(self):
		return self.data.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))

	@property
	def flags(self):
		return self.data.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.data.core_count

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

		return self.core_count

	@property
	def clock_speed(self):
		return self.__clock_speed

	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, backend, id, data=None):
		Object.__init__(self, backend)

		self.id = id
		self.__data = data

	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.id == other.id

	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

	@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

	def is_showable(self):
		if self.driver in IGNORED_DEVICES:
			return False

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

		return True

	@property
	def subsystem(self):
		return self.data.subsystem

	@property
	def model(self):
		return self.data.model

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

	@property
	def vendor(self):
		return self.data.vendor

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

	@property
	def driver(self):
		return self.data.driver

	@property
	def cls(self):
		classid = self.data.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"

	@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

	@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

	@property
	def public_id(self):
		return self.data.public_id

	@property
	def private_id(self):
		raise NotImplementedError

	@property
	def time_created(self):
		return self.data.time_created

	@property
	def time_updated(self):
		return self.data.time_updated

	def updated(self, profile_parser=None, location=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 location:
			self.set_location(location)

		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 parse(self, parser):
		# Processor
		self.processor = parser.processor
		self.set_processor_speeds(parser.processor_clock_speed, parser.processor_bogomips)

		# All devices
		self.devices = parser.devices

		# System
		self.system_id = parser.system_id

		# Memory
		self.memory = parser.memory

		# Storage
		self.storage = parser.storage

		# Kernel
		self.kernel_id = parser.kernel_id

		# Arch
		self.arch_id = parser.arch_id

		# Release
		self.release_id = parser.release_id

		# Language
		self.language = parser.language

		# Virtual
		if parser.virtual:
			self.hypervisor_id = parser.hypervisor_id

		# Network
		self.network = parser.network

	# Location

	def get_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_location(self, location):
		if self.location == location:
			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, location)

		self._location = location

	location = property(get_location, set_location)

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

	# 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

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

			self._appliance_id = None
			for name, test_func in appliances:
				if not test_func():
					continue

				self._appliance_id = name
				break

		return self._appliance_id

	@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

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

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

	# 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)", "(i586)", "(armv5tel)"):
				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),
		})


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)

	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)

		if res:
			return Profile(self.backend, res.id, 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 create_arch(self, arch):
		res = self.db.get("INSERT INTO fireinfo_arches(name) VALUES(%s) \
			RETURNING id", arch)

		if res:
			return res.id

	def get_arch(self, arch):
		res = self.db.get("SELECT id FROM fireinfo_arches WHERE name = %s", arch)

		if res:
			return res.id

	# Release

	def create_release(self, release):
		res = self.db.get("INSERT INTO fireinfo_releases(name) VALUES(%s) \
			RETURNING id", release)

		if res:
			return res.id

	def get_release(self, release):
		res = self.db.get("SELECT id FROM fireinfo_releases WHERE name = %s", release)

		if res:
			return res.id

	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)

		if res:
			return res.penetration

	def get_random_country_penetration(self):
		res = self.db.get("SELECT * FROM fireinfo_country_percentages \
			ORDER BY RANDOM() LIMIT 1")

		if res:
			return database.Row({
				"country"    : iso3166.countries.get(res.location),
				"percentage" : res.count,
			})

	# Hypervisor

	def create_hypervisor(self, hypervisor):
		res = self.db.get("INSERT INTO fireinfo_hypervisors(name) VALUES(%s) \
			RETURNING id", hypervisor)

		if res:
			return res.id

	def get_hypervisor(self, hypervisor):
		res = self.db.get("SELECT id FROM fireinfo_hypervisors WHERE name = %s",
			hypervisor)

		if res:
			return res.id

	# Handle profile

	def handle_profile(self, *args, **kwargs):
		self.db.execute("START TRANSACTION")

		# Wrap all the handling of the profile in a huge transaction.
		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, location=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)

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

		# 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, location=location, when=when)
				return

			# If it does not match, we assume that it is expired and
			# create a new profile.
			profile.expired(when=when)

		# Replace the old profile with a new one
		profile = self.fireinfo.create_profile(public_id, private_id, when=when)
		profile.parse(profile_parser)

		if location:
			profile.set_location(location)

		return profile

	# 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 res:
			return res.public_id

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

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

	@property
	def cpu_vendors(self):
		res = self.db.query("SELECT DISTINCT vendor FROM fireinfo_processors ORDER BY vendor")

		return (CPU_VENDORS.get(r.vendor, r.vendor) for r 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 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

		ret = []
		for flag in flags:
			ret.append((flag, self.get_cpus_with_platform_and_flag(platform, flag, when=when)))

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

		return sorted(ret, key=lambda x: x[1], reverse=True)

	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

	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)

		return ((r.arch, r.count) for r 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)

	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 res:
			return res.count

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

		return ((r.name, r.count) for r in res)

	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)

		return self._process_devices(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):
		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)

		vendors = {}
		for row in res:
			vendor = self.get_vendor_string(row.subsystem, 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)]

		vendors = list(vendors.items())
		return sorted(vendors)

	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)