1 |
-- Plase note that the flisfyr database must be created with iso-8859-1 encoding like this
|
2 |
-- CREATE DATABASE flisfyr ENCODING 'LATIN1';
|
3 |
|
4 |
|
5 |
CREATE TABLE customer
|
6 |
(
|
7 |
id serial,
|
8 |
name character varying(100) NOT NULL,
|
9 |
address character varying(255) NOT NULL,
|
10 |
phonenr character varying(30) NOT NULL,
|
11 |
contactperson character varying(100) NOT NULL,
|
12 |
PRIMARY KEY (id)
|
13 |
) WITHOUT OIDS;
|
14 |
|
15 |
|
16 |
CREATE TABLE installation
|
17 |
(
|
18 |
id serial,
|
19 |
description character varying(100),
|
20 |
address character varying(100) NOT NULL,
|
21 |
customerid integer NOT NULL,
|
22 |
furnacetype character varying(100) NOT NULL,
|
23 |
installationphonenr character varying(30) NOT NULL,
|
24 |
imei character(16) NOT NULL,
|
25 |
updaterate integer NOT NULL,
|
26 |
commerror boolean NOT NULL DEFAULT false,
|
27 |
PRIMARY KEY (id),
|
28 |
FOREIGN KEY (customerid) REFERENCES customer (id) ON UPDATE CASCADE ON DELETE CASCADE
|
29 |
) WITHOUT OIDS;
|
30 |
|
31 |
|
32 |
CREATE TABLE logtable
|
33 |
(
|
34 |
id serial,
|
35 |
logtime timestamp without time zone NOT NULL,
|
36 |
temperature smallint NOT NULL,
|
37 |
flamedetector boolean NOT NULL,
|
38 |
solidfuelempty boolean NOT NULL,
|
39 |
conveyorerror boolean NOT NULL,
|
40 |
powerfailure boolean NOT NULL,
|
41 |
messagenr integer NOT NULL,
|
42 |
installationnr integer NOT NULL,
|
43 |
PRIMARY KEY (id),
|
44 |
FOREIGN KEY (installationnr) REFERENCES installation (id) ON UPDATE CASCADE ON DELETE CASCADE
|
45 |
)
|
46 |
WITHOUT OIDS;
|
47 |
|
48 |
CREATE TABLE command
|
49 |
(
|
50 |
id serial NOT NULL,
|
51 |
created timestamp without time zone NOT NULL,
|
52 |
executed timestamp without time zone,
|
53 |
commandid smallint NOT NULL,
|
54 |
installationid integer,
|
55 |
CONSTRAINT command_pkey PRIMARY KEY (id),
|
56 |
CONSTRAINT command_installationid_fkey FOREIGN KEY (installationid)
|
57 |
REFERENCES installation (id)
|
58 |
ON UPDATE CASCADE ON DELETE CASCADE
|
59 |
)
|
60 |
WITHOUT OIDS;
|
61 |
|
62 |
CREATE TABLE config
|
63 |
(
|
64 |
name character varying(50) NOT NULL,
|
65 |
value text NOT NULL,
|
66 |
CONSTRAINT config_pkey PRIMARY KEY (name)
|
67 |
)
|
68 |
WITHOUT OIDS;
|
69 |
|
70 |
CREATE TABLE users
|
71 |
(
|
72 |
id serial,
|
73 |
realname character varying(100) NOT NULL,
|
74 |
username character varying(30) NOT NULL,
|
75 |
"password" character(32) NOT NULL,
|
76 |
enabled boolean NOT NULL DEFAULT true,
|
77 |
useradmin boolean NOT NULL DEFAULT false,
|
78 |
PRIMARY KEY (id)
|
79 |
) WITHOUT OIDS;
|
80 |
|
81 |
CREATE INDEX installation_customer_idx ON installation (customerid);
|
82 |
CREATE UNIQUE INDEX installation_phonenr_idx ON installation (installationphonenr);
|
83 |
CREATE INDEX logtable_installationnr_idx ON logtable (installationnr);
|
84 |
CREATE UNIQUE INDEX users_username_idx ON users (username);
|
85 |
CREATE INDEX logtable_messagenr_idx ON logtable(messagenr);
|
86 |
|
87 |
CREATE ROLE serrenab LOGIN PASSWORD 'furnacemonitor';
|
88 |
|
89 |
GRANT select,insert,update,delete ON users TO serrenab;
|
90 |
GRANT select,insert,update,delete ON config TO serrenab;
|
91 |
GRANT select,insert,update,delete ON command TO serrenab;
|
92 |
GRANT select,insert,update,delete ON installation TO serrenab;
|
93 |
GRANT select,insert,update,delete ON customer TO serrenab;
|
94 |
GRANT select,insert,update,delete ON logtable TO serrenab;
|
95 |
|
96 |
-- these sequences are auto created by postgresql
|
97 |
GRANT ALL ON customer_id_seq TO serrenab;
|
98 |
GRANT ALL ON command_id_seq TO serrenab;
|
99 |
GRANT ALL ON installation_id_seq TO serrenab;
|
100 |
GRANT ALL ON logtable_id_seq TO serrenab;
|
101 |
GRANT ALL ON users_id_seq TO serrenab;
|
102 |
|
103 |
REVOKE create ON SCHEMA public FROM public;
|
104 |
|
105 |
INSERT INTO users (realname,username,password,enabled,useradmin) VALUES ('Admin User','admin',md5('admin'),true,true); |