3 -- CREATE INDEX fireinfo_search ON fireinfo USING gin (blob) WHERE expired_at IS NULL;
4 -- CREATE UNIQUE INDEX fireinfo_current ON fireinfo USING btree (profile_id) WHERE expired_at IS NULL
6 -- CREATE INDEX fireinfo_releases_current ON fireinfo USING hash((blob->'system'->'release')) WHERE expired_at IS NULL;
7 -- CREATE INDEX fireinfo_releases ON fireinfo USING hash((blob->'system'->'release'));
9 -- CREATE INDEX fireinfo_arches_current ON fireinfo USING hash((blob->'cpu'->'arch')) WHERE blob->'cpu'->'arch' IS NOT NULL AND expired_at IS NULL;
10 -- CREATE INDEX fireinfo_arches ON fireinfo USING hash((blob->'cpu'->'arch')) WHERE blob->'cpu'->'arch' IS NOT NULL;
12 -- CREATE INDEX fireinfo_cpu_vendors ON fireinfo USING hash((blob->'cpu'->'vendor')) WHERE blob->'cpu'->'vendor' IS NOT NULL;
14 -- 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;
18 TRUNCATE TABLE fireinfo
;
25 p.public_id
AS profile_id
,
26 p.time_created
AS created_at
,
29 WHEN p.time_valid
<= CURRENT_TIMESTAMP THEN p.time_valid
35 -- Empty the profile if we don't have any data
36 CASE WHEN profile_arches.arch_id
IS NULL THEN NULL
38 -- Otherwise do some hard work...
41 jsonb_build_object('cpu',
44 'bogomips', profile_processors.bogomips
,
45 'speed', profile_processors.clock_speed
,
47 'vendor', processors.vendor
,
48 'model', processors.model
,
49 'model_string', processors.model_string
,
50 'stepping', processors.stepping
,
51 'flags', processors.flags
,
52 'family', processors.
family,
53 'count', processors.core_count
58 ||
jsonb_build_object('system',
60 'kernel', kernels.
name,
61 'language', profile_languages.
language,
62 'memory', profile_memory.amount
,
63 'release', releases.
name,
64 'root_size', profile_storage.amount
,
65 'vendor', systems.vendor
,
66 'model', systems.model
,
67 'virtual', CASE WHEN hypervisors.
id IS NULL THEN FALSE ELSE TRUE END
73 WHEN hypervisors.
id IS NULL THEN jsonb_build_object()
77 json_build_object('vendor', hypervisors.
name)
82 ||
jsonb_build_object('devices', devices.devices
)
85 ||
jsonb_build_object('networks',
87 'green', profile_networks.has_green
,
88 'blue', profile_networks.has_blue
,
89 'orange', profile_networks.has_orange
,
90 'red', profile_networks.has_red
95 p.time_updated
AS last_updated_at
,
96 p.private_id
AS private_id
,
97 locations.
location AS country_code
99 FROM fireinfo_profiles p
102 fireinfo_profiles_locations locations
ON p.
id = locations.profile_id
105 fireinfo_profiles_arches profile_arches
ON p.
id = profile_arches.profile_id
108 fireinfo_arches arches
ON profile_arches.arch_id
= arches.
id
113 profile_devices.profile_id
AS profile_id
,
116 'deviceclass', devices.deviceclass
,
117 'subsystem', devices.subsystem
,
118 'vendor', devices.vendor
,
119 'model', devices.model
,
120 'sub_vendor', devices.sub_vendor
,
121 'sub_model', devices.sub_model
,
122 'driver', devices.driver
126 fireinfo_profiles_devices profile_devices
128 fireinfo_devices devices
ON profile_devices.device_id
= devices.
id
130 profile_devices.profile_id
131 ) devices
ON p.
id = devices.profile_id
134 fireinfo_profiles_processors profile_processors
ON p.
id = profile_processors.profile_id
137 fireinfo_processors processors
ON profile_processors.processor_id
= processors.
id
140 fireinfo_profiles_kernels profile_kernels
ON p.
id = profile_kernels.profile_id
143 fireinfo_kernels kernels
ON profile_kernels.kernel_id
= kernels.
id
146 fireinfo_profiles_languages profile_languages
ON p.
id = profile_languages.profile_id
149 fireinfo_profiles_memory profile_memory
ON p.
id = profile_memory.profile_id
152 fireinfo_profiles_releases profile_releases
ON p.
id = profile_releases.profile_id
155 fireinfo_releases releases
ON profile_releases.release_id
= releases.
id
158 fireinfo_profiles_storage profile_storage
ON p.
id = profile_storage.profile_id
161 fireinfo_profiles_systems profile_systems
ON p.
id = profile_systems.profile_id
164 fireinfo_systems systems
ON profile_systems.system_id
= systems.
id
167 fireinfo_profiles_virtual profile_virtual
ON p.
id = profile_virtual.profile_id
170 fireinfo_hypervisors hypervisors
ON profile_virtual.hypervisor_id
= hypervisors.
id
173 fireinfo_profiles_networks profile_networks
ON p.
id = profile_networks.profile_id
176 -- XXX TO FIND A PROFILE WITH DATA
177 -- profile_processors.profile_id IS NOT NULL
179 -- XXX TO FIND A VIRTUAL PROFILE
180 --profile_virtual.hypervisor_id IS NOT NULL