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

Revision 2146, 8.6 kB (checked in by jerome, 19 years ago)

It seems that $Log$ is not implemented or doesn't work for some reason

  • 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 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., 59 Temple Place, Suite 330, Boston, MA 02111-1307, USA.
18--
19-- $Id$
20--
21-- $Log$
22-- Revision 1.16  2005/02/13 22:02:29  jalet
23-- Big database structure changes. Upgrade script is now included as well as
24-- the new LDAP schema.
25-- Introduction of the -o | --overcharge command line option to edpykota.
26-- The output of repykota is more complete, but doesn't fit in 80 columns anymore.
27-- Introduction of the new 'maxdenybanners' directive.
28--
29-- Revision 1.15  2005/01/23 10:58:22  jalet
30-- Added a few indexes for the database
31--
32-- Revision 1.14  2005/01/17 08:44:24  jalet
33-- Modified copyright years
34--
35-- Revision 1.13  2005/01/10 23:23:25  jalet
36-- Added the billing code to the history
37--
38-- Revision 1.12  2004/12/23 18:40:18  jalet
39-- Added the coefficient table, and many columns to existing tables
40--
41-- Revision 1.11  2004/06/20 16:15:21  jalet
42-- Added "description" attribute for printers
43--
44-- Revision 1.10  2004/06/03 23:14:09  jalet
45-- Now stores the job's size in bytes in the database.
46-- Preliminary work on payments storage : database schemas are OK now,
47-- but no code to store payments yet.
48-- Removed schema picture, not relevant anymore.
49--
50-- Revision 1.9  2004/05/13 11:15:29  jalet
51-- Added hostname field in job history
52--
53-- Revision 1.8  2004/01/08 14:10:32  jalet
54-- Copyright year changed.
55--
56-- Revision 1.7  2003/12/27 16:49:25  uid67467
57-- Should be ok now.
58--
59-- Revision 1.6  2003/11/23 19:01:36  jalet
60-- Job price added to history
61--
62-- Revision 1.5  2003/11/21 14:28:45  jalet
63-- More complete job history.
64--
65-- Revision 1.4  2003/07/16 21:53:07  jalet
66-- Really big modifications wrt new configuration file's location and content.
67--
68-- Revision 1.3  2003/07/09 20:17:07  jalet
69-- Email field added to PostgreSQL schema
70--
71-- Revision 1.2  2003/06/10 16:37:54  jalet
72-- Deletion of the second user which is not needed anymore.
73-- Added a debug configuration field in /etc/pykota.conf
74-- All queries can now be sent to the logger in debug mode, this will
75-- greatly help improve performance when time for this will come.
76--
77-- Revision 1.1  2003/06/05 07:12:31  jalet
78-- Reorganization of directories
79--
80--
81--
82
83--
84-- PyKota Database creation script for PostgreSQL
85--
86-- Launch this as PostgreSQL administrator with \i
87--
88
89
90--
91-- Create the print quota database
92--
93CREATE DATABASE pykota;
94
95--
96-- Create the print quota database users
97--
98CREATE USER pykotaadmin;
99CREATE USER pykotauser;
100
101--
102-- Now connect to the new database
103--
104\connect pykota
105
106--
107-- Create the users table
108--
109CREATE TABLE users(id SERIAL PRIMARY KEY NOT NULL,
110                   username TEXT UNIQUE NOT NULL,
111                   email TEXT, 
112                   balance FLOAT DEFAULT 0.0,
113                   lifetimepaid FLOAT DEFAULT 0.0,
114                   limitby TEXT DEFAULT 'quota',
115                   overcharge FLOAT NOT NULL DEFAULT 1.0);
116                   
117--
118-- Create the groups table
119--
120CREATE TABLE groups(id SERIAL PRIMARY KEY NOT NULL,
121                    groupname TEXT UNIQUE NOT NULL,
122                    limitby TEXT DEFAULT 'quota');
123                   
124--
125-- Create the printers table
126--
127CREATE TABLE printers(id SERIAL PRIMARY KEY NOT NULL,
128                      printername TEXT UNIQUE NOT NULL,
129                      description TEXT,
130                      priceperpage FLOAT DEFAULT 0.0,
131                      priceperjob FLOAT DEFAULT 0.0);
132                   
133--
134-- Create the print quota table for users
135--
136CREATE TABLE userpquota(id SERIAL PRIMARY KEY NOT NULL,
137                        userid INT4 REFERENCES users(id),
138                        printerid INT4 REFERENCES printers(id),
139                        lifepagecounter INT4 DEFAULT 0,
140                        pagecounter INT4 DEFAULT 0,
141                        softlimit INT4,
142                        hardlimit INT4,
143                        datelimit TIMESTAMP,
144                        warncount INT4 DEFAULT 0); 
145CREATE INDEX userpquota_u_id_ix ON userpquota (userid);
146CREATE INDEX userpquota_p_id_ix ON userpquota (printerid);
147CREATE UNIQUE INDEX userpquota_up_id_ix ON userpquota (userid, printerid);
148                       
149--
150-- Create the job history table
151--
152CREATE TABLE jobhistory(id SERIAL PRIMARY KEY NOT NULL,
153                        jobid TEXT,
154                        userid INT4,
155                        printerid INT4,
156                        pagecounter INT4 DEFAULT 0,
157                        jobsizebytes INT8,
158                        jobsize INT4,
159                        jobprice FLOAT,
160                        action TEXT,
161                        filename TEXT,
162                        title TEXT,
163                        copies INT4,
164                        options TEXT,
165                        hostname TEXT,
166                        md5sum TEXT,
167                        pages TEXT,
168                        billingcode TEXT,
169                        jobdate TIMESTAMP DEFAULT now(),
170                        CONSTRAINT checkUserPQuota FOREIGN KEY (userid, printerid) REFERENCES userpquota(userid, printerid));
171CREATE INDEX jobhistory_u_id_ix ON jobhistory (userid);
172CREATE INDEX jobhistory_p_id_ix ON jobhistory (printerid);
173CREATE INDEX jobhistory_pd_id_ix ON jobhistory (printerid, jobdate);
174CREATE INDEX jobhistory_hostname_ix ON jobhistory (hostname);
175                       
176--
177-- Create the print quota table for groups
178--
179CREATE TABLE grouppquota(id SERIAL PRIMARY KEY NOT NULL,
180                         groupid INT4 REFERENCES groups(id),
181                         printerid INT4 REFERENCES printers(id),
182                         softlimit INT4,
183                         hardlimit INT4,
184                         datelimit TIMESTAMP);
185CREATE INDEX grouppquota_g_id_ix ON grouppquota (groupid);
186CREATE INDEX grouppquota_p_id_ix ON grouppquota (printerid);
187CREATE UNIQUE INDEX grouppquota_up_id_ix ON grouppquota (groupid, printerid);
188                       
189--                         
190-- Create the groups/members relationship
191--
192CREATE TABLE groupsmembers(groupid INT4 REFERENCES groups(id),
193                           userid INT4 REFERENCES users(id),
194                           PRIMARY KEY (groupid, userid));
195                           
196--                         
197-- Create the printer groups relationship
198--
199CREATE TABLE printergroupsmembers(groupid INT4 REFERENCES printers(id),
200                           printerid INT4 REFERENCES printers(id),
201                           PRIMARY KEY (groupid, printerid));
202--
203-- Create the table for payments
204--
205CREATE TABLE payments (id SERIAL PRIMARY KEY NOT NULL,
206                       userid INT4 REFERENCES users(id),
207                       amount FLOAT,
208                       date TIMESTAMP DEFAULT now());
209CREATE INDEX payments_date_ix ON payments (date);
210
211--
212-- Create the table for coefficients wrt paper sizes and the like
213--
214CREATE TABLE coefficients (id SERIAL PRIMARY KEY NOT NULL, 
215                           printerid INTEGER NOT NULL REFERENCES printers(id), 
216                           label TEXT NOT NULL, 
217                           coefficient FLOAT DEFAULT 1.0, 
218                           CONSTRAINT coeffconstraint UNIQUE (printerid, label));
219
220--                       
221-- Set some ACLs                       
222--
223REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients FROM public;                       
224REVOKE 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 FROM public;
225
226GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients TO pykotaadmin;
227GRANT 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 TO pykotaadmin;
228GRANT SELECT ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients TO pykotauser;
Note: See TracBrowser for help on using the browser.