trunk/docs/Database.txt

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, 
   address character varying(100) NOT NULL, 
   customerid integer NOT NULL, 
   boilertype character varying(100) NOT NULL, 
   installationphonenr character varying(50) NOT NULL, 
   imei character(16) NOT NULL, 
   updaterate integer NOT NULL, 
   description text, 
   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(30) NOT NULL, 
   "password" character(32) NOT NULL, 
   enabled boolean NOT NULL DEFAULT true, 
   useradmin boolean NOT NULL, 
    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);
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,
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);