setup_db.sql (1398B)
1 -- Create tempestas user and database. 2 CREATE ROLE tempestas WITH LOGIN PASSWORD '<password>'; 3 CREATE DATABASE tempestas WITH OWNER tempestas TEMPLATE template0 ENCODING UTF8 LC_COLLATE 'en_CA.UTF-8' LC_CTYPE 'en_CA.UTF-8'; 4 5 -- Create tables. 6 CREATE TABLE sensors ( 7 sensor_id VARCHAR(63) PRIMARY KEY, 8 name VARCHAR(20) NOT NULL 9 ); 10 11 CREATE TABLE sensor_data ( 12 sensor_id VARCHAR(63) NOT NULL, 13 sw_version VARCHAR(63) NOT NULL, 14 reading_time TIMESTAMP NOT NULL, 15 reading_type VARCHAR(63) NOT NULL, 16 reading_value NUMERIC(20,6) NOT NULL 17 ); 18 19 -- Create indexes. 20 CREATE INDEX id_idx ON sensor_data (sensor_id); 21 CREATE INDEX type_idx ON sensor_data (reading_type); 22 CREATE INDEX time_id ON sensor_data (reading_time); 23 24 -- Create stored procedures. 25 CREATE OR REPLACE PROCEDURE save_reading( 26 new_sensor_id VARCHAR(63), 27 new_sw_version VARCHAR(63), 28 new_reading_time TIMESTAMP, 29 new_reading_type VARCHAR(63), 30 new_reading_value NUMERIC(20,6) 31 ) 32 LANGUAGE plpgsql 33 AS $$ 34 BEGIN 35 INSERT INTO sensors(sensor_id, name) VALUES(new_sensor_id, 'Unknown') ON CONFLICT DO NOTHING; 36 37 INSERT INTO sensor_data(sensor_id, sw_version, reading_time, reading_type, reading_value) 38 VALUES(new_sensor_id, new_sw_version, new_reading_time, new_reading_type, new_reading_value); 39 40 COMMIT; 41 END$$; 42 43 -- Grant permissions. 44 GRANT SELECT, INSERT ON sensor_data TO tempestas; 45 GRANT SELECT, INSERT ON sensors TO tempestas;