root / pykota / trunk / initscripts / postgresql / pykota-postgresql.sql @ 2954

Revision 2954, 7.6 kB (checked in by jerome, 16 years ago)

Ensures that the databases are created with UTF-8 encoding, and that the
client tells that we will always use UTF-8 when sending datas to the server
or retrieving them.

  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
Line 
1--
2-- PyKota - Print Quotas for CUPS and LPRng
3--
4-- (c) 2003, 2004, 2005, 2006 Jerome Alet <alet@librelogiciel.com>
5-- This program is free software; you can redistribute it and/or modify
6-- it under the terms of the GNU General Public License as published by
7-- the Free Software Foundation; either version 2 of the License, or
8-- (at your option) any later version.
9--
10-- This program is distributed in the hope that it will be useful,
11-- but WITHOUT ANY WARRANTY; without even the implied warranty of
12-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13-- GNU General Public License for more details.
14--
15-- You should have received a copy of the GNU General Public License
16-- along with this program; if not, write to the Free Software
17-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
18--
19-- $Id$
20--
21--
22
23--
24-- PyKota Database creation script for PostgreSQL
25--
26-- Launch this as PostgreSQL administrator with \i
27--
28
29
30--
31-- Create the print quota database
32--
33CREATE DATABASE pykota WITH ENCODING='UTF-8';
34
35--
36-- Create the print quota database users
37-- NOTE: Change the password values to the passwords you would like.
38--
39CREATE USER pykotauser WITH UNENCRYPTED PASSWORD 'readonlypw' NOCREATEDB NOCREATEUSER;
40CREATE USER pykotaadmin WITH UNENCRYPTED PASSWORD 'readwritepw' NOCREATEDB NOCREATEUSER;
41
42--
43-- Now connect to the new database
44--
45\connect pykota
46
47--
48-- Create the users table
49--
50CREATE TABLE users(id SERIAL PRIMARY KEY NOT NULL,
51                   username TEXT UNIQUE NOT NULL,
52                   email TEXT, 
53                   balance FLOAT DEFAULT 0.0,
54                   lifetimepaid FLOAT DEFAULT 0.0,
55                   limitby TEXT DEFAULT 'quota',
56                   description TEXT,
57                   overcharge FLOAT NOT NULL DEFAULT 1.0);
58                   
59--
60-- Create the groups table
61--
62CREATE TABLE groups(id SERIAL PRIMARY KEY NOT NULL,
63                    groupname TEXT UNIQUE NOT NULL,
64                    description TEXT,
65                    limitby TEXT DEFAULT 'quota');
66                   
67--
68-- Create the printers table
69--
70CREATE TABLE printers(id SERIAL PRIMARY KEY NOT NULL,
71                      printername TEXT UNIQUE NOT NULL,
72                      description TEXT,
73                      priceperpage FLOAT DEFAULT 0.0,
74                      priceperjob FLOAT DEFAULT 0.0,
75                      passthrough BOOLEAN DEFAULT FALSE,
76                      maxjobsize INT4);
77                   
78--
79-- Create the print quota table for users
80--
81CREATE TABLE userpquota(id SERIAL PRIMARY KEY NOT NULL,
82                        userid INT4 REFERENCES users(id),
83                        printerid INT4 REFERENCES printers(id),
84                        lifepagecounter INT4 DEFAULT 0,
85                        pagecounter INT4 DEFAULT 0,
86                        softlimit INT4,
87                        hardlimit INT4,
88                        datelimit TIMESTAMP,
89                        maxjobsize INT4,
90                        warncount INT4 DEFAULT 0); 
91CREATE INDEX userpquota_u_id_ix ON userpquota (userid);
92CREATE INDEX userpquota_p_id_ix ON userpquota (printerid);
93CREATE UNIQUE INDEX userpquota_up_id_ix ON userpquota (userid, printerid);
94                       
95--
96-- Create the job history table
97--
98CREATE TABLE jobhistory(id SERIAL PRIMARY KEY NOT NULL,
99                        jobid TEXT,
100                        userid INT4,
101                        printerid INT4,
102                        pagecounter INT4 DEFAULT 0,
103                        jobsizebytes INT8,
104                        jobsize INT4,
105                        jobprice FLOAT,
106                        action TEXT,
107                        filename TEXT,
108                        title TEXT,
109                        copies INT4,
110                        options TEXT,
111                        hostname TEXT,
112                        md5sum TEXT,
113                        pages TEXT,
114                        billingcode TEXT,
115                        precomputedjobsize INT4,
116                        precomputedjobprice FLOAT,
117                        jobdate TIMESTAMP DEFAULT now(),
118                        CONSTRAINT checkUserPQuota FOREIGN KEY (userid, printerid) REFERENCES userpquota(userid, printerid));
119CREATE INDEX jobhistory_u_id_ix ON jobhistory (userid);
120CREATE INDEX jobhistory_p_id_ix ON jobhistory (printerid);
121CREATE INDEX jobhistory_pd_id_ix ON jobhistory (printerid, jobdate);
122CREATE INDEX jobhistory_hostname_ix ON jobhistory (hostname);
123                       
124--
125-- Create the print quota table for groups
126--
127CREATE TABLE grouppquota(id SERIAL PRIMARY KEY NOT NULL,
128                         groupid INT4 REFERENCES groups(id),
129                         printerid INT4 REFERENCES printers(id),
130                         softlimit INT4,
131                         hardlimit INT4,
132                         maxjobsize INT4,
133                         datelimit TIMESTAMP);
134CREATE INDEX grouppquota_g_id_ix ON grouppquota (groupid);
135CREATE INDEX grouppquota_p_id_ix ON grouppquota (printerid);
136CREATE UNIQUE INDEX grouppquota_up_id_ix ON grouppquota (groupid, printerid);
137                       
138--                         
139-- Create the groups/members relationship
140--
141CREATE TABLE groupsmembers(groupid INT4 REFERENCES groups(id),
142                           userid INT4 REFERENCES users(id),
143                           PRIMARY KEY (groupid, userid));
144                           
145--                         
146-- Create the printer groups relationship
147--
148CREATE TABLE printergroupsmembers(groupid INT4 REFERENCES printers(id),
149                           printerid INT4 REFERENCES printers(id),
150                           PRIMARY KEY (groupid, printerid));
151--
152-- Create the table for payments
153--
154CREATE TABLE payments (id SERIAL PRIMARY KEY NOT NULL,
155                       userid INT4 REFERENCES users(id),
156                       amount FLOAT,
157                       description TEXT,
158                       date TIMESTAMP DEFAULT now());
159CREATE INDEX payments_date_ix ON payments (date);
160
161--
162-- Create the table for coefficients wrt paper sizes and the like
163--
164CREATE TABLE coefficients (id SERIAL PRIMARY KEY NOT NULL, 
165                           printerid INTEGER NOT NULL REFERENCES printers(id), 
166                           label TEXT NOT NULL, 
167                           coefficient FLOAT DEFAULT 1.0, 
168                           CONSTRAINT coeffconstraint UNIQUE (printerid, label));
169
170--
171-- Create the table for the billing codes
172--
173CREATE TABLE billingcodes (id SERIAL PRIMARY KEY NOT NULL,
174                           billingcode TEXT UNIQUE NOT NULL,
175                           description TEXT,
176                           balance FLOAT DEFAULT 0.0,
177                           pagecounter INT4 DEFAULT 0);
178
179--                       
180-- Set some ACLs                       
181--
182REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients, billingcodes FROM public;
183REVOKE ALL ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq, payments_id_seq, coefficients_id_seq, billingcodes_id_seq FROM public;
184
185GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients, billingcodes TO pykotaadmin;
186GRANT SELECT, UPDATE ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq, payments_id_seq, coefficients_id_seq, billingcodes_id_seq TO pykotaadmin;
187GRANT SELECT ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients, billingcodes TO pykotauser;
Note: See TracBrowser for help on using the browser.