/[H9]/trunk/docs/Database.txt
ViewVC logotype

Diff of /trunk/docs/Database.txt

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 63 by torben, Tue Nov 27 08:16:08 2007 UTC revision 221 by torben, Tue Dec 11 07:48:55 2007 UTC
# Line 1  Line 1 
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,
# Line 12  CREATE TABLE customer Line 16  CREATE TABLE customer
16  CREATE TABLE installation  CREATE TABLE installation
17  (  (
18     id serial,     id serial,
19       description character varying(100),
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
# Line 67  CREATE TABLE users Line 71  CREATE TABLE users
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 DEFAULT true,     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    
# Line 78  CREATE INDEX installation_customer_idx O Line 82  CREATE INDEX installation_customer_idx O
82  CREATE UNIQUE INDEX installation_phonenr_idx ON installation (installationphonenr);  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    CREATE INDEX logtable_messagenr_idx ON logtable(messagenr);
86    
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    GRANT select,insert,update,delete ON logtable TO serrenab;
95    
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    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);
Legend:
Removed from v.63  
changed lines
  Added in v.221
  ViewVC Help
Powered by ViewVC 1.1.20