trunk/docs/Database.txt

-- Plase note that the flisfyr database must be created with iso-8859-1 encoding like this
-- CREATE DATABASE flisfyr ENCODING 'LATIN1';


CREATE TABLE customer
(
   id serial, 
   name character varying(100) NOT NULL, 
   address character varying(255) NOT NULL, 
   phonenr character varying(30) NOT NULL, 
   contactperson character varying(100) NOT NULL, 
    PRIMARY KEY (id)
) WITHOUT OIDS;


CREATE TABLE installation
(
   id serial, 
   description character varying(100), 
   address character varying(100) NOT NULL, 
   customerid integer NOT NULL, 
   furnacetype character varying(100) NOT NULL, 
   installationphonenr character varying(30) NOT NULL, 
   imei character(16) NOT NULL, 
   updaterate integer NOT NULL, 
   commerror boolean NOT NULL DEFAULT false, 
    PRIMARY KEY (id), 
    FOREIGN KEY (customerid) REFERENCES customer (id)    ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT OIDS;


CREATE TABLE logtable
(
   id serial, 
   logtime timestamp without time zone NOT NULL, 
   temperature smallint NOT NULL, 
   flamedetector boolean NOT NULL, 
   solidfuelempty boolean NOT NULL, 
   conveyorerror boolean NOT NULL, 
   powerfailure boolean NOT NULL, 
   messagenr integer NOT NULL, 
   installationnr integer NOT NULL, 
    PRIMARY KEY (id), 
    FOREIGN KEY (installationnr) REFERENCES installation (id)    ON UPDATE CASCADE ON DELETE CASCADE
) 
WITHOUT OIDS;

CREATE TABLE command
(
  id serial NOT NULL,
  created timestamp without time zone NOT NULL,
  executed timestamp without time zone,
  commandid smallint NOT NULL,
  installationid integer,
  CONSTRAINT command_pkey PRIMARY KEY (id),
  CONSTRAINT command_installationid_fkey FOREIGN KEY (installationid)
      REFERENCES installation (id) 
      ON UPDATE CASCADE ON DELETE CASCADE
) 
WITHOUT OIDS;

CREATE TABLE config
(
  name character varying(50) NOT NULL,
  value text NOT NULL,
  CONSTRAINT config_pkey PRIMARY KEY (name)
) 
WITHOUT OIDS;

CREATE TABLE users
(
   id serial, 
   realname character varying(100) NOT NULL, 
   username character varying(30) NOT NULL,   
   "password" character(32) NOT NULL, 
   enabled boolean NOT NULL DEFAULT true, 
   useradmin boolean NOT NULL DEFAULT false, 
    PRIMARY KEY (id)
) WITHOUT OIDS;

CREATE INDEX installation_customer_idx ON installation (customerid);
CREATE UNIQUE INDEX installation_phonenr_idx ON installation (installationphonenr);
CREATE INDEX logtable_installationnr_idx ON logtable (installationnr);
CREATE UNIQUE INDEX users_username_idx ON users (username);
CREATE INDEX logtable_messagenr_idx ON logtable(messagenr);

CREATE ROLE serrenab LOGIN PASSWORD 'furnacemonitor';

GRANT select,insert,update,delete ON users TO serrenab;
GRANT select,insert,update,delete ON config TO serrenab;
GRANT select,insert,update,delete ON command TO serrenab;
GRANT select,insert,update,delete ON installation TO serrenab;
GRANT select,insert,update,delete ON customer TO serrenab;
GRANT select,insert,update,delete ON logtable TO serrenab;

-- these sequences are auto created by postgresql
GRANT ALL ON customer_id_seq  TO serrenab;
GRANT ALL ON command_id_seq TO serrenab;
GRANT ALL ON installation_id_seq TO serrenab;
GRANT ALL ON logtable_id_seq TO serrenab;
GRANT ALL ON users_id_seq TO serrenab;

REVOKE create ON SCHEMA public FROM public;

INSERT INTO users (realname,username,password,enabled,useradmin) VALUES ('Admin User','admin',md5('admin'),true,true);
1	torben	91	-- 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	torben	61	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	torben	221	description character varying(100),
20	torben	61	address character varying(100) NOT NULL,
21			customerid integer NOT NULL,
22	torben	97	furnacetype character varying(100) NOT NULL,
23	torben	244	installationphonenr character varying(30) NOT NULL,
24	torben	61	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	torben	102	username character varying(30) NOT NULL,
75	torben	61	"password" character(32) NOT NULL,
76	torben	63	enabled boolean NOT NULL DEFAULT true,
77	torben	76	useradmin boolean NOT NULL DEFAULT false,
78	torben	61	PRIMARY KEY (id)
79			) WITHOUT OIDS;
80
81			CREATE INDEX installation_customer_idx ON installation (customerid);
82	torben	62	CREATE UNIQUE INDEX installation_phonenr_idx ON installation (installationphonenr);
83	torben	61	CREATE INDEX logtable_installationnr_idx ON logtable (installationnr);
84			CREATE UNIQUE INDEX users_username_idx ON users (username);
85	torben	199	CREATE INDEX logtable_messagenr_idx ON logtable(messagenr);
86	torben	76
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	torben	89	GRANT select,insert,update,delete ON logtable TO serrenab;
95
96	torben	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	torben	89	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);