migrate.sql

   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;