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 |
CREATE TABLE customer |
6 |
( |
( |
7 |
id serial, |
id serial, |
16 |
CREATE TABLE installation |
CREATE TABLE installation |
17 |
( |
( |
18 |
id serial, |
id serial, |
19 |
|
description text, |
20 |
address character varying(100) NOT NULL, |
address character varying(100) NOT NULL, |
21 |
customerid integer NOT NULL, |
customerid integer NOT NULL, |
22 |
boilertype character varying(100) NOT NULL, |
furnacetype character varying(100) NOT NULL, |
23 |
installationphonenr character varying(50) NOT NULL, |
installationphonenr character varying(50) NOT NULL, |
24 |
imei character(16) NOT NULL, |
imei character(16) NOT NULL, |
25 |
updaterate integer NOT NULL, |
updaterate integer NOT NULL, |
|
description text, |
|
26 |
commerror boolean NOT NULL DEFAULT false, |
commerror boolean NOT NULL DEFAULT false, |
27 |
PRIMARY KEY (id), |
PRIMARY KEY (id), |
28 |
FOREIGN KEY (customerid) REFERENCES customer (id) ON UPDATE CASCADE ON DELETE CASCADE |
FOREIGN KEY (customerid) REFERENCES customer (id) ON UPDATE CASCADE ON DELETE CASCADE |
71 |
( |
( |
72 |
id serial, |
id serial, |
73 |
realname character varying(100) NOT NULL, |
realname character varying(100) NOT NULL, |
74 |
username character(30) NOT NULL, |
username character varying(30) NOT NULL, |
75 |
"password" character(32) NOT NULL, |
"password" character(32) NOT NULL, |
76 |
enabled boolean NOT NULL, |
enabled boolean NOT NULL DEFAULT true, |
77 |
useradmin boolean NOT NULL, |
useradmin boolean NOT NULL DEFAULT false, |
78 |
PRIMARY KEY (id) |
PRIMARY KEY (id) |
79 |
) WITHOUT OIDS; |
) WITHOUT OIDS; |
80 |
|
|
81 |
CREATE INDEX installation_customer_idx ON installation (customerid); |
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); |
CREATE INDEX logtable_installationnr_idx ON logtable (installationnr); |
84 |
CREATE UNIQUE INDEX users_username_idx ON users (username); |
CREATE UNIQUE INDEX users_username_idx ON users (username); |
85 |
|
|
86 |
|
CREATE ROLE serrenab LOGIN PASSWORD 'furnacemonitor'; |
87 |
|
|
88 |
|
GRANT select,insert,update,delete ON users TO serrenab; |
89 |
|
GRANT select,insert,update,delete ON config TO serrenab; |
90 |
|
GRANT select,insert,update,delete ON command TO serrenab; |
91 |
|
GRANT select,insert,update,delete ON installation TO serrenab; |
92 |
|
GRANT select,insert,update,delete ON customer TO serrenab; |
93 |
|
GRANT select,insert,update,delete ON logtable TO serrenab; |
94 |
|
|
95 |
|
-- these sequences are auto created by postgresql |
96 |
|
GRANT ALL ON customer_id_seq TO serrenab; |
97 |
|
GRANT ALL ON command_id_seq TO serrenab; |
98 |
|
GRANT ALL ON installation_id_seq TO serrenab; |
99 |
|
GRANT ALL ON logtable_id_seq TO serrenab; |
100 |
|
GRANT ALL ON users_id_seq TO serrenab; |
101 |
|
|
102 |
|
REVOKE create ON SCHEMA public FROM public; |
103 |
|
|
104 |
|
INSERT INTO users (realname,username,password,enabled,useradmin) VALUES ('Admin User','admin',md5('admin'),true,true); |