--- trunk/docs/Database.txt	2007/11/27 08:16:08	63
+++ trunk/docs/Database.txt	2007/12/11 22:10:17	244
@@ -1,3 +1,7 @@
+-- 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, 
@@ -12,13 +16,13 @@
 CREATE TABLE installation
 (
    id serial, 
+   description character varying(100), 
    address character varying(100) NOT NULL, 
    customerid integer NOT NULL, 
-   boilertype character varying(100) NOT NULL, 
-   installationphonenr character varying(50) NOT NULL, 
+   furnacetype character varying(100) NOT NULL, 
+   installationphonenr character varying(30) 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
@@ -67,10 +71,10 @@
 (
    id serial, 
    realname character varying(100) NOT NULL, 
-   username character(30) NOT NULL, 
+   username character varying(30) NOT NULL,   
    "password" character(32) NOT NULL, 
    enabled boolean NOT NULL DEFAULT true, 
-   useradmin boolean NOT NULL, 
+   useradmin boolean NOT NULL DEFAULT false, 
     PRIMARY KEY (id)
 ) WITHOUT OIDS;
 
@@ -78,3 +82,24 @@
 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);
\ No newline at end of file