]>
Commit | Line | Data |
---|---|---|
278a2971 MT |
1 | START TRANSACTION; |
2 | ||
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 | |
5 | ||
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')); | |
8 | ||
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; | |
11 | ||
12 | -- CREATE INDEX fireinfo_cpu_vendors ON fireinfo USING hash((blob->'cpu'->'vendor')) WHERE blob->'cpu'->'vendor' IS NOT NULL; | |
13 | ||
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; | |
15 | ||
16 | -- XXX virtual index | |
17 | ||
18 | TRUNCATE TABLE fireinfo; | |
19 | ||
20 | --EXPLAIN | |
21 | ||
22 | INSERT INTO fireinfo | |
23 | ||
24 | SELECT | |
25 | p.public_id AS profile_id, | |
26 | p.time_created AS created_at, | |
27 | ( | |
28 | CASE | |
29 | WHEN p.time_valid <= CURRENT_TIMESTAMP THEN p.time_valid | |
30 | ELSE NULL | |
31 | END | |
32 | ) AS expired_at, | |
33 | 0 AS version, | |
34 | ( | |
35 | -- Empty the profile if we don't have any data | |
36 | CASE WHEN profile_arches.arch_id IS NULL THEN NULL | |
37 | ||
38 | -- Otherwise do some hard work... | |
39 | ELSE | |
40 | -- CPU | |
41 | jsonb_build_object('cpu', | |
42 | jsonb_build_object( | |
43 | 'arch', arches.name, | |
44 | 'bogomips', profile_processors.bogomips, | |
45 | 'speed', profile_processors.clock_speed, | |
46 | ||
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 | |
54 | ) | |
55 | ) | |
56 | ||
57 | -- System | |
58 | || jsonb_build_object('system', | |
59 | jsonb_build_object( | |
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 | |
68 | ) | |
69 | ) | |
70 | ||
71 | -- Hypervisor | |
72 | || CASE | |
73 | WHEN hypervisors.id IS NULL THEN jsonb_build_object() | |
74 | ELSE | |
75 | jsonb_build_object( | |
76 | 'hypervisor', | |
77 | json_build_object('vendor', hypervisors.name) | |
78 | ) | |
79 | END | |
80 | ||
81 | -- Devices | |
82 | || jsonb_build_object('devices', devices.devices) | |
83 | ||
84 | -- Networks | |
85 | || jsonb_build_object('networks', | |
86 | jsonb_build_object( | |
87 | 'green', profile_networks.has_green, | |
88 | 'blue', profile_networks.has_blue, | |
89 | 'orange', profile_networks.has_orange, | |
90 | 'red', profile_networks.has_red | |
91 | ) | |
92 | ) | |
93 | END | |
94 | ) AS blob, | |
95 | p.time_updated AS last_updated_at, | |
96 | p.private_id AS private_id, | |
97 | locations.location AS country_code | |
98 | ||
99 | FROM fireinfo_profiles p | |
100 | ||
101 | LEFT JOIN | |
102 | fireinfo_profiles_locations locations ON p.id = locations.profile_id | |
103 | ||
104 | LEFT JOIN | |
105 | fireinfo_profiles_arches profile_arches ON p.id = profile_arches.profile_id | |
106 | ||
107 | LEFT JOIN | |
108 | fireinfo_arches arches ON profile_arches.arch_id = arches.id | |
109 | ||
110 | LEFT JOIN | |
111 | ( | |
112 | SELECT | |
113 | profile_devices.profile_id AS profile_id, | |
114 | jsonb_agg( | |
115 | jsonb_build_object( | |
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 | |
123 | ) | |
124 | ) AS devices | |
125 | FROM | |
126 | fireinfo_profiles_devices profile_devices | |
127 | LEFT JOIN | |
128 | fireinfo_devices devices ON profile_devices.device_id = devices.id | |
129 | GROUP BY | |
130 | profile_devices.profile_id | |
131 | ) devices ON p.id = devices.profile_id | |
132 | ||
133 | LEFT JOIN | |
134 | fireinfo_profiles_processors profile_processors ON p.id = profile_processors.profile_id | |
135 | ||
136 | LEFT JOIN | |
137 | fireinfo_processors processors ON profile_processors.processor_id = processors.id | |
138 | ||
139 | LEFT JOIN | |
140 | fireinfo_profiles_kernels profile_kernels ON p.id = profile_kernels.profile_id | |
141 | ||
142 | LEFT JOIN | |
143 | fireinfo_kernels kernels ON profile_kernels.kernel_id = kernels.id | |
144 | ||
145 | LEFT JOIN | |
146 | fireinfo_profiles_languages profile_languages ON p.id = profile_languages.profile_id | |
147 | ||
148 | LEFT JOIN | |
149 | fireinfo_profiles_memory profile_memory ON p.id = profile_memory.profile_id | |
150 | ||
151 | LEFT JOIN | |
152 | fireinfo_profiles_releases profile_releases ON p.id = profile_releases.profile_id | |
153 | ||
154 | LEFT JOIN | |
155 | fireinfo_releases releases ON profile_releases.release_id = releases.id | |
156 | ||
157 | LEFT JOIN | |
158 | fireinfo_profiles_storage profile_storage ON p.id = profile_storage.profile_id | |
159 | ||
160 | LEFT JOIN | |
161 | fireinfo_profiles_systems profile_systems ON p.id = profile_systems.profile_id | |
162 | ||
163 | LEFT JOIN | |
164 | fireinfo_systems systems ON profile_systems.system_id = systems.id | |
165 | ||
166 | LEFT JOIN | |
167 | fireinfo_profiles_virtual profile_virtual ON p.id = profile_virtual.profile_id | |
168 | ||
169 | LEFT JOIN | |
170 | fireinfo_hypervisors hypervisors ON profile_virtual.hypervisor_id = hypervisors.id | |
171 | ||
172 | LEFT JOIN | |
173 | fireinfo_profiles_networks profile_networks ON p.id = profile_networks.profile_id | |
174 | ||
175 | --WHERE | |
176 | -- XXX TO FIND A PROFILE WITH DATA | |
177 | -- profile_processors.profile_id IS NOT NULL | |
178 | ||
179 | -- XXX TO FIND A VIRTUAL PROFILE | |
180 | --profile_virtual.hypervisor_id IS NOT NULL | |
181 | ||
182 | --ORDER BY | |
183 | -- time_created DESC | |
184 | ||
185 | --LIMIT 1 | |
186 | ; | |
187 | ||
188 | COMMIT; |