events.sql
1 DROP TABLE IF EXISTS seed_event_time_series; 2 3 CREATE TEMP TABLE seed_event_time_series AS 4 WITH aligned_current_time AS ( 5 SELECT to_timestamp(floor(extract(epoch FROM now()) / 300) * 300) AS window_center_time 6 ) 7 SELECT generate_series( 8 aligned_current_time.window_center_time - interval '24 hours', 9 aligned_current_time.window_center_time + interval '24 hours', 10 interval '5 minutes' 11 ) AS event_time 12 FROM aligned_current_time; 13 14 WITH seed_node_definitions AS ( 15 SELECT 16 'urn:pulumi:dev::arctic-rover::microvisor:index:Node::arctic-rover.01'::text AS event_source, 17 NULL::int AS node_number, 18 NULL::text AS node_id, 19 'arctic'::text AS node_group 20 21 UNION ALL 22 23 SELECT 24 format( 25 'urn:pulumi:dev::arctic-rover::microvisor:index:Node::toronto-transit-commission-subway.%s', 26 lpad(toronto_node.node_number::text, 2, '0') 27 ) AS event_source, 28 toronto_node.node_number, 29 lpad(toronto_node.node_number::text, 2, '0') AS node_id, 30 'toronto'::text AS node_group 31 FROM generate_series(1, 20) AS toronto_node(node_number) 32 ), 33 seed_event_definitions AS ( 34 SELECT 35 seed_node_definitions.event_source, 36 seed_node_definitions.node_number, 37 seed_node_definitions.node_id, 38 seed_node_definitions.node_group, 39 arctic_event.event_type, 40 arctic_event.event_sequence 41 FROM seed_node_definitions 42 CROSS JOIN ( 43 VALUES 44 ('status.v1', 0), 45 ('sensors.wind_speed.v1', 1), 46 ('sensors.wind_direction.v1', 2), 47 ('sensors.solar_radiation.v1', 3), 48 ('sensors.soil.v1', 4), 49 ('sensors.temperature_and_humidity.v1', 5) 50 ) AS arctic_event(event_type, event_sequence) 51 WHERE seed_node_definitions.node_group = 'arctic' 52 53 UNION ALL 54 55 SELECT 56 seed_node_definitions.event_source, 57 seed_node_definitions.node_number, 58 seed_node_definitions.node_id, 59 seed_node_definitions.node_group, 60 'sensors.temperature_and_humidity.v1'::text AS event_type, 61 0 AS event_sequence 62 FROM seed_node_definitions 63 WHERE seed_node_definitions.node_group = 'toronto' 64 65 UNION ALL 66 67 SELECT 68 seed_node_definitions.event_source, 69 seed_node_definitions.node_number, 70 seed_node_definitions.node_id, 71 seed_node_definitions.node_group, 72 'sensors.wind_speed.v1'::text AS event_type, 73 1 AS event_sequence 74 FROM seed_node_definitions 75 WHERE seed_node_definitions.node_group = 'toronto' 76 AND seed_node_definitions.node_number IN (19, 20) 77 78 UNION ALL 79 80 SELECT 81 seed_node_definitions.event_source, 82 seed_node_definitions.node_number, 83 seed_node_definitions.node_id, 84 seed_node_definitions.node_group, 85 'sensors.wind_direction.v1'::text AS event_type, 86 2 AS event_sequence 87 FROM seed_node_definitions 88 WHERE seed_node_definitions.node_group = 'toronto' 89 AND seed_node_definitions.node_number IN (19, 20) 90 ), 91 seed_event_rows AS ( 92 SELECT 93 seed_event_time_series.event_time, 94 seed_event_definitions.event_source, 95 seed_event_definitions.node_number, 96 seed_event_definitions.node_group, 97 seed_event_definitions.event_type, 98 seed_event_definitions.event_sequence, 99 CASE 100 WHEN seed_event_definitions.node_group = 'arctic' THEN format( 101 '%s-%s-%s', 102 seed_event_definitions.event_type, 103 to_char(seed_event_time_series.event_time AT TIME ZONE 'UTC', 'YYYYMMDD"T"HH24MISS"Z"'), 104 seed_event_definitions.event_sequence 105 ) 106 ELSE format( 107 '%s-%s-%s-%s', 108 seed_event_definitions.event_type, 109 to_char(seed_event_time_series.event_time AT TIME ZONE 'UTC', 'YYYYMMDD"T"HH24MISS"Z"'), 110 seed_event_definitions.node_id, 111 seed_event_definitions.event_sequence 112 ) 113 END AS cloud_event_id 114 FROM seed_event_time_series 115 CROSS JOIN seed_event_definitions 116 ) 117 INSERT INTO events (name, source, type, specversion, datacontenttype, time, data) 118 SELECT 119 seed_event_rows.cloud_event_id, 120 seed_event_rows.event_source, 121 seed_event_rows.event_type, 122 1.0::float8 AS specversion, 123 'application/json'::text AS datacontenttype, 124 seed_event_rows.event_time, 125 CASE 126 WHEN seed_event_rows.event_type = 'status.v1' THEN jsonb_build_object( 127 'memory_heap', 133000 + floor(450 * (1 + sin(extract(epoch FROM seed_event_rows.event_time) / 1800.0)))::int, 128 'chip_model', 'ESP32-S3', 129 'chip_cores', 2, 130 'chip_revision', 2, 131 'ipv4_address', '192.168.1.100', 132 'wifi_rssi', -40 + floor(10 * sin(extract(epoch FROM seed_event_rows.event_time) / 2400.0))::int, 133 'uptime_seconds', greatest(0, extract(epoch FROM (seed_event_rows.event_time - (SELECT min(event_time) FROM seed_event_time_series)))::bigint) 134 ) 135 WHEN seed_event_rows.event_type = 'sensors.wind_speed.v1' 136 AND seed_event_rows.node_group = 'arctic' THEN jsonb_build_object( 137 'read_ok', true, 138 'wind_speed_kilometers_per_hour', round((0.6 + 2.8 * abs(sin(extract(epoch FROM seed_event_rows.event_time) / 2200.0)))::numeric, 2) 139 ) 140 WHEN seed_event_rows.event_type = 'sensors.wind_direction.v1' 141 AND seed_event_rows.node_group = 'arctic' THEN jsonb_build_object( 142 'read_ok', true, 143 'wind_direction_angle', round(mod((extract(epoch FROM seed_event_rows.event_time) / 45.0), 360.0)::numeric, 2), 144 'wind_direction_slice', floor(mod((extract(epoch FROM seed_event_rows.event_time) / 45.0), 360.0) / 22.5)::int 145 ) 146 WHEN seed_event_rows.event_type = 'sensors.solar_radiation.v1' THEN jsonb_build_object( 147 'read_ok', true, 148 'solar_radiation_watts_per_square_meter', floor(greatest(0, 350 * sin(extract(epoch FROM seed_event_rows.event_time) / 14400.0)))::int 149 ) 150 WHEN seed_event_rows.event_type = 'sensors.soil.v1' THEN jsonb_build_object( 151 'range_ok', true, 152 'read_ok', true, 153 'first_slave_id', 53, 154 'last_slave_id', 72, 155 'instance_count', 20, 156 'temperature_celsius', round((-3.3 + 0.6 * sin(extract(epoch FROM seed_event_rows.event_time) / 3600.0))::numeric, 2), 157 'moisture_percent', round((5.5 + 0.7 * sin(extract(epoch FROM seed_event_rows.event_time) / 4200.0))::numeric, 2), 158 'conductivity', floor(5 + 3 * sin(extract(epoch FROM seed_event_rows.event_time) / 3000.0))::int, 159 'salinity', floor(5 + 3 * cos(extract(epoch FROM seed_event_rows.event_time) / 3000.0))::int, 160 'tds', floor(4 + 3 * sin(extract(epoch FROM seed_event_rows.event_time) / 2500.0))::int, 161 'instances', ( 162 SELECT jsonb_agg( 163 jsonb_build_object( 164 'instance_index', soil_instance.instance_index, 165 'slave_id', 53 + soil_instance.instance_index, 166 'read_ok', true, 167 'temperature_celsius', round((-3.4 + 0.5 * sin((extract(epoch FROM seed_event_rows.event_time) / 3600.0) + soil_instance.instance_index))::numeric, 2), 168 'moisture_percent', round((5.5 + 0.8 * sin((extract(epoch FROM seed_event_rows.event_time) / 4800.0) + soil_instance.instance_index))::numeric, 2), 169 'conductivity', greatest(0, least(9, floor(5 + 4 * sin((extract(epoch FROM seed_event_rows.event_time) / 3000.0) + soil_instance.instance_index))::int)), 170 'salinity', greatest(0, least(9, floor(5 + 4 * cos((extract(epoch FROM seed_event_rows.event_time) / 3000.0) + soil_instance.instance_index))::int)), 171 'tds', greatest(0, least(9, floor(4 + 4 * sin((extract(epoch FROM seed_event_rows.event_time) / 2700.0) + soil_instance.instance_index))::int)) 172 ) 173 ) 174 FROM generate_series(0, 19) AS soil_instance(instance_index) 175 ) 176 ) 177 WHEN seed_event_rows.event_type = 'sensors.temperature_and_humidity.v1' 178 AND seed_event_rows.node_group = 'arctic' THEN jsonb_build_object( 179 'init_ok', true, 180 'instances', jsonb_build_array( 181 jsonb_build_object( 182 'instance_index', 0, 183 'begin_ok', true, 184 'read_ok', true, 185 'temperature_celsius', round((-4.4 + 0.5 * sin(extract(epoch FROM seed_event_rows.event_time) / 3300.0))::numeric, 2), 186 'relative_humidity_percent', round((75 + 8 * sin(extract(epoch FROM seed_event_rows.event_time) / 2800.0))::numeric, 2) 187 ), 188 jsonb_build_object( 189 'instance_index', 1, 190 'begin_ok', true, 191 'read_ok', true, 192 'temperature_celsius', round((-4.5 + 0.5 * cos(extract(epoch FROM seed_event_rows.event_time) / 3300.0))::numeric, 2), 193 'relative_humidity_percent', round((73 + 8 * cos(extract(epoch FROM seed_event_rows.event_time) / 2800.0))::numeric, 2) 194 ) 195 ), 196 'instance_count', 2, 197 'successful_reads', 2, 198 'read_ok', true 199 ) 200 WHEN seed_event_rows.event_type = 'sensors.temperature_and_humidity.v1' THEN jsonb_build_object( 201 'init_ok', true, 202 'instances', jsonb_build_array( 203 jsonb_build_object( 204 'instance_index', 0, 205 'begin_ok', true, 206 'read_ok', true, 207 'temperature_celsius', round((-2.8 + 0.8 * sin((extract(epoch FROM seed_event_rows.event_time) / 3300.0) + seed_event_rows.node_number))::numeric, 2), 208 'relative_humidity_percent', round((61 + 9 * cos((extract(epoch FROM seed_event_rows.event_time) / 2800.0) + seed_event_rows.node_number))::numeric, 2) 209 ) 210 ), 211 'instance_count', 1, 212 'successful_reads', 1, 213 'read_ok', true 214 ) 215 WHEN seed_event_rows.event_type = 'sensors.wind_speed.v1' THEN jsonb_build_object( 216 'read_ok', true, 217 'wind_speed_kilometers_per_hour', round((1.1 + 3.2 * abs(sin((extract(epoch FROM seed_event_rows.event_time) / 2600.0) + seed_event_rows.node_number)))::numeric, 2) 218 ) 219 WHEN seed_event_rows.event_type = 'sensors.wind_direction.v1' THEN jsonb_build_object( 220 'read_ok', true, 221 'wind_direction_angle', round(mod((extract(epoch FROM seed_event_rows.event_time) / 55.0) + (seed_event_rows.node_number * 12), 360.0)::numeric, 2), 222 'wind_direction_slice', floor(mod((extract(epoch FROM seed_event_rows.event_time) / 55.0) + (seed_event_rows.node_number * 12), 360.0) / 22.5)::int 223 ) 224 END AS data 225 FROM seed_event_rows;