1 |
CREATE TABLE customer
|
2 |
(
|
3 |
id serial,
|
4 |
name character varying(100) NOT NULL,
|
5 |
address character varying(255) NOT NULL,
|
6 |
phonenr character varying(30) NOT NULL,
|
7 |
contactperson character varying(100) NOT NULL,
|
8 |
PRIMARY KEY (id)
|
9 |
) WITHOUT OIDS;
|
10 |
|
11 |
|
12 |
CREATE TABLE installation
|
13 |
(
|
14 |
id serial,
|
15 |
address character varying(100) NOT NULL,
|
16 |
customerid integer NOT NULL,
|
17 |
boilertype character varying(100) NOT NULL,
|
18 |
installationphonenr character varying(50) NOT NULL,
|
19 |
imei character(16) NOT NULL,
|
20 |
updaterate integer NOT NULL,
|
21 |
description text,
|
22 |
commerror boolean NOT NULL DEFAULT false,
|
23 |
PRIMARY KEY (id),
|
24 |
FOREIGN KEY (customerid) REFERENCES customer (id) ON UPDATE CASCADE ON DELETE CASCADE
|
25 |
) WITHOUT OIDS;
|
26 |
|
27 |
|
28 |
CREATE TABLE logtable
|
29 |
(
|
30 |
id serial,
|
31 |
logtime timestamp without time zone NOT NULL,
|
32 |
temperature smallint NOT NULL,
|
33 |
flamedetector boolean NOT NULL,
|
34 |
solidfuelempty boolean NOT NULL,
|
35 |
conveyorerror boolean NOT NULL,
|
36 |
powerfailure boolean NOT NULL,
|
37 |
messagenr integer NOT NULL,
|
38 |
installationnr integer NOT NULL,
|
39 |
PRIMARY KEY (id),
|
40 |
FOREIGN KEY (installationnr) REFERENCES installation (id) ON UPDATE CASCADE ON DELETE CASCADE
|
41 |
)
|
42 |
WITHOUT OIDS;
|
43 |
|
44 |
CREATE TABLE command
|
45 |
(
|
46 |
id serial NOT NULL,
|
47 |
created timestamp without time zone NOT NULL,
|
48 |
executed timestamp without time zone,
|
49 |
commandid smallint NOT NULL,
|
50 |
installationid integer,
|
51 |
CONSTRAINT command_pkey PRIMARY KEY (id),
|
52 |
CONSTRAINT command_installationid_fkey FOREIGN KEY (installationid)
|
53 |
REFERENCES installation (id)
|
54 |
ON UPDATE CASCADE ON DELETE CASCADE
|
55 |
)
|
56 |
WITHOUT OIDS;
|
57 |
|
58 |
CREATE TABLE config
|
59 |
(
|
60 |
name character varying(50) NOT NULL,
|
61 |
value text NOT NULL,
|
62 |
CONSTRAINT config_pkey PRIMARY KEY (name)
|
63 |
)
|
64 |
WITHOUT OIDS;
|
65 |
|
66 |
CREATE TABLE users
|
67 |
(
|
68 |
id serial,
|
69 |
realname character varying(100) NOT NULL,
|
70 |
username character(30) NOT NULL,
|
71 |
"password" character(32) NOT NULL,
|
72 |
enabled boolean NOT NULL DEFAULT true,
|
73 |
useradmin boolean NOT NULL DEFAULT false,
|
74 |
PRIMARY KEY (id)
|
75 |
) WITHOUT OIDS;
|
76 |
|
77 |
CREATE INDEX installation_customer_idx ON installation (customerid);
|
78 |
CREATE UNIQUE INDEX installation_phonenr_idx ON installation (installationphonenr);
|
79 |
CREATE INDEX logtable_installationnr_idx ON logtable (installationnr);
|
80 |
CREATE UNIQUE INDEX users_username_idx ON users (username);
|
81 |
|
82 |
CREATE ROLE serrenab LOGIN PASSWORD 'furnacemonitor';
|
83 |
|
84 |
GRANT select,insert,update,delete ON users TO serrenab;
|
85 |
GRANT select,insert,update,delete ON config TO serrenab;
|
86 |
GRANT select,insert,update,delete ON command TO serrenab;
|
87 |
GRANT select,insert,update,delete ON installation TO serrenab;
|
88 |
GRANT select,insert,update,delete ON customer TO serrenab;
|
89 |
GRANT select,insert,update,delete ON logtable TO serrenab; |