1 |
torben |
61 |
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,
|
73 |
|
|
useradmin boolean NOT NULL,
|
74 |
|
|
PRIMARY KEY (id)
|
75 |
|
|
) WITHOUT OIDS;
|
76 |
|
|
|
77 |
|
|
CREATE INDEX installation_customer_idx ON installation (customerid);
|
78 |
|
|
CREATE INDEX logtable_installationnr_idx ON logtable (installationnr);
|
79 |
|
|
CREATE UNIQUE INDEX users_username_idx ON users (username);
|