Vu9E7hh6

1--- THIS SCRIPT GENERATES THE django-managed tables etc.
2
3-- run when setting up the docker/codeship images for testing etc.
4CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
5GRANT ALL ON DATABASE energydata_px TO rav;
6GRANT ALL PRIVILEGES ON DATABASE energydata_px TO rav;
7
8-- generate the required Django-managed table that this app is dependent on, for running tests against
9CREATE TABLE devices_devicemodel (
10    id SERIAL PRIMARY KEY,
11    code character varying(8) NOT NULL UNIQUE,
12    label character varying(32) NOT NULL,
13    channels_count smallint NOT NULL CHECK (channels_count >= 0),
14    switches_count smallint NOT NULL CHECK (switches_count >= 0),
15    communications character varying(8) NOT NULL,
16    is_rogowski boolean NOT NULL,
17    modbus smallint NOT NULL
18);
19
20INSERT INTO 
21    devices_devicemodel (id, code, label, channels_count, switches_count, communications, is_rogowski, modbus)
22VALUES
23    (1, '6M', 'Auditor 6M', 6, 0, '3g', FALSE, 0),
24    (2, '6W', 'Auditor 6W', 6, 0, 'wifi', FALSE, 0),
25    (3, '3M', 'Auditor 3M', 3, 0, '3g', FALSE, 0),
26    (4, '6W+3SW', 'Auditor 6W+3SW', 6, 3, 'wifi', FALSE, 0),
27    (5, '6M+3SW', 'Auditor 6M+3SW', 6, 3, '3g', FALSE, 0),
28    (6, '3RM', 'Auditor 3R (Rogowski)', 3, 0, '3g', TRUE, 0),
29    (7, '6M+One', 'Auditor 6M+One (Modbus)', 6, 2, '3g', FALSE, 1),
30    (8, '3RM+3SW', 'Auditor 3R (Rogowski) + Switches', 3, 3, '3g', TRUE, 0),
31    (9, '6M4', 'Auditor 6M (4G)', 6, 0, '4g', FALSE, 0),
32    (10, '6M4+3SW', 'Auditor 6M (4G) + Switches', 6, 3, '4g', FALSE, 0)
33ON CONFLICT DO NOTHING;
34
35CREATE TABLE IF NOT EXISTS devices_device (
36    id character varying(16) PRIMARY KEY,
37    model_type_id integer REFERENCES devices_devicemodel(id) DEFERRABLE INITIALLY DEFERRED,
38    label character varying(64) NOT NULL,
39    short_energy_reporting_interval integer NOT NULL CHECK (short_energy_reporting_interval >= 0),
40    production_date timestamp with time zone,
41    first_heard_at timestamp with time zone,
42    last_heard_at timestamp with time zone,
43    latest_se timestamp with time zone,
44    jove_first_le timestamp with time zone,
45    first_le timestamp with time zone,
46    latest_le timestamp with time zone,
47    latest_modbus timestamp with time zone,
48    latest_status timestamp with time zone,
49    firmware_version character varying(9),
50    rssi integer NOT NULL,
51    apn character varying(32),
52    imsi character varying(24),
53    mac_address character varying(24),
54    sim_id character varying(24),
55    internal_temperature integer,
56    grid_phases integer CHECK (grid_phases >= 0),
57    phase_groupings jsonb,
58    CONSTRAINT grid_phases_phase_groupings_type_check CHECK (
59        grid_phases <> 0 OR (jsonb_typeof(phase_groupings) = 'array'::text AND jsonb_array_length(phase_groupings) > 0)
60    )
61);
62
63CREATE TABLE devices_channelcategory (
64    id integer PRIMARY KEY,
65    label character varying(32) NOT NULL,
66    description character varying(512) NOT NULL
67);
68
69INSERT INTO 
70    devices_channelcategory (id, label, description)
71VALUES
72    (-1, 'Not set', 'No category has been set for this channel'),
73    (0, 'Not connected', 'The channel is present, but not connected to a circuit on this device'),
74    (1, 'Grid connect', 'The channel tracks imports and exports to the electricity grid.'),
75    (2, 'Lighting & Power', ''),
76    (3, 'Pool pump', ''),
77    (4, 'Air conditioning', ''),
78    (5, 'Solar generation', 'The channel tracks the total amount of power generated by a solar installation (before consumption/export)'),
79    (6, 'Hot water', ''),
80    (7, 'Electric vehicle', 'The channel tracks consumption for charging/exports from the battery of an electric vehicle'),
81    (8, 'Battery',	'The channel tracks charging + exports to/from a premises-based electricity storage system')
82ON CONFLICT DO NOTHING;
83
84CREATE TABLE devices_devicechannel (
85    id character varying(16) PRIMARY KEY,
86    label character varying(64) NOT NULL,
87    ct_rating integer NOT NULL CHECK (ct_rating >= 0),
88    category_id integer NOT NULL REFERENCES devices_channelcategory(id) DEFERRABLE INITIALLY DEFERRED,
89    device_id character varying(16) NOT NULL REFERENCES devices_device(id) DEFERRABLE INITIALLY DEFERRED
90);
91
92CREATE TABLE devices_devicegroup (
93    id SERIAL PRIMARY KEY,
94    label character varying(64) NOT NULL UNIQUE,
95    keywords character varying(32)[] NOT NULL,
96    internal_notes text,
97    api_key character varying(36) NOT NULL UNIQUE,
98    is_mercury_ready boolean NOT NULL,
99    is_admin boolean NOT NULL,
100    can_modify_switch_state boolean NOT NULL,
101    request_limit_per_day integer,
102    request_limit_per_second integer,
103    autoscale_limits boolean NOT NULL,
104    jove_api_key character varying(36) UNIQUE,
105    technical_contact character varying(254)
106);
107
108CREATE TABLE devices_devicegroup_authorized_devices (
109    id SERIAL PRIMARY KEY,
110    devicegroup_id integer NOT NULL REFERENCES devices_devicegroup(id) DEFERRABLE INITIALLY DEFERRED,
111    device_id character varying(16) NOT NULL REFERENCES devices_device(id) DEFERRABLE INITIALLY DEFERRED,
112    CONSTRAINT devices_devicegroup_autho_devicegroup_id_device_id_5e3efccd_uniq UNIQUE (devicegroup_id, device_id)
113);
114
115CREATE TABLE devices_deviceswitch (
116    id character varying(16) PRIMARY KEY,
117    state integer NOT NULL CHECK (state >= 0),
118    label character varying(64) NOT NULL,
119    closed_state_label character varying(16) NOT NULL,
120    open_state_label character varying(16) NOT NULL,
121    device_id character varying(16) NOT NULL REFERENCES devices_device(id) DEFERRABLE INITIALLY DEFERRED,
122    contactor_type integer NOT NULL CHECK (contactor_type >= 0)
123);
124
125CREATE TABLE IF NOT EXISTS auth_user
126(
127    id serial primary key ,
128    password character varying(128) NOT NULL,
129    last_login timestamp with time zone,
130    is_superuser boolean NOT NULL,
131    username character varying(150) NOT NULL,
132    first_name character varying(30) NOT NULL,
133    last_name character varying(150) NOT NULL,
134    email character varying(254) NOT NULL,
135    is_staff boolean NOT NULL,
136    is_active boolean NOT NULL,
137    date_joined timestamp with time zone NOT NULL,
138    CONSTRAINT auth_user_username_key UNIQUE (username)
139);
140
141INSERT INTO auth_user VALUES (1, '', NULL, TRUE, 'server', '', '', '', TRUE, TRUE, current_timestamp)
142ON CONFLICT DO NOTHING;
143
144CREATE TABLE IF NOT EXISTS fleet_fleetdevice
145(
146    id serial primary key,
147    status character varying(32) COLLATE pg_catalog."default",
148    status_updated_at timestamp with time zone,
149    subscription_expires timestamp with time zone,
150    subscription_plan character varying(32),
151    warranty_expires timestamp with time zone,
152    device_id character varying(16) NOT NULL REFERENCES devices_device(id) DEFERRABLE INITIALLY DEFERRED
153);
154
155CREATE TABLE IF NOT EXISTS fleet_fleetdevicenote
156(
157    id serial primary key,
158    created_at timestamp with time zone NOT NULL,
159    updated_at timestamp with time zone NOT NULL,
160    summary character varying(64)NOT NULL,
161    detail text NOT NULL,
162    is_internal boolean NOT NULL,
163    support_ticket_link character varying(200),
164    device_id integer  NOT NULL REFERENCES fleet_fleetdevice(id) DEFERRABLE INITIALLY DEFERRED,
165    user_id integer NOT NULL REFERENCES auth_user(id),
166    resolved_at timestamp with time zone
167);
168
169GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO rav;
170GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO rav;