/[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 62 by torben, Mon Nov 26 22:32:14 2007 UTC revision 97 by torben, Thu Nov 29 09:51:51 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 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
# Line 69  CREATE TABLE users Line 73  CREATE TABLE users
73     realname character varying(100) NOT NULL,     realname character varying(100) NOT NULL,
74     username character(30) NOT NULL,     username character(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    
# 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    
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);
Legend:
Removed from v.62  
changed lines
  Added in v.97
  ViewVC Help
Powered by ViewVC 1.1.20