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

Revision 952, 5.8 kB (checked in by jalet, 22 years ago)

Preliminary support for LPRng added BUT STILL UNTESTED.

  • 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 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.14  2003/04/23 22:13:56  jalet
23-- Preliminary support for LPRng added BUT STILL UNTESTED.
24--
25-- Revision 1.13  2003/04/15 11:30:57  jalet
26-- More work done on money print charging.
27-- Minor bugs corrected.
28-- All tools now access to the storage as priviledged users, repykota excepted.
29--
30-- Revision 1.12  2003/04/14 20:01:02  jalet
31-- Typo
32--
33-- Revision 1.11  2003/04/14 19:58:59  jalet
34-- New database schema for users' account balance
35--
36-- Revision 1.10  2003/04/10 21:47:20  jalet
37-- Job history added. Upgrade script neutralized for now !
38--
39-- Revision 1.9  2003/04/09 20:11:29  jalet
40-- Added a field to save the action taken for this job (Allow, Deny)
41--
42-- Revision 1.8  2003/04/09 20:09:34  jalet
43-- New table to keep job history
44--
45-- Revision 1.7  2003/04/08 20:38:08  jalet
46-- The last job Id is saved now for each printer, this will probably
47-- allow other accounting methods in the future.
48--
49-- Revision 1.6  2003/03/29 13:45:27  jalet
50-- GPL paragraphs were incorrectly (from memory) copied into the sources.
51-- Two README files were added.
52-- Upgrade script for PostgreSQL pre 1.01 schema was added.
53--
54-- Revision 1.5  2003/03/12 19:06:08  jalet
55-- Initial support for groups added.
56--
57-- Revision 1.4  2003/02/27 08:40:14  jalet
58-- DATETIME is not supported anymore in PostgreSQL 7.3 it seems, but
59-- TIMESTAMP is.
60--
61-- Revision 1.3  2003/02/26 20:34:22  jalet
62-- Default value for printer page counter set to 0
63--
64-- Revision 1.2  2003/02/08 22:12:09  jalet
65-- Life time counter for users and groups added.
66--
67-- Revision 1.1  2003/02/05 21:28:17  jalet
68-- Initial import into CVS
69--
70--
71--
72
73--
74-- PyKota Database creation script for PostgreSQL
75--
76-- Launch this as PostgreSQL administrator with \i
77--
78
79
80--
81-- Create the print quota database
82--
83CREATE DATABASE pykota;
84
85--
86-- Create the print quota database users
87--
88CREATE USER pykotauser;
89CREATE USER pykotaadmin;
90
91--
92-- Now connect to the new database
93--
94\connect pykota
95
96--
97-- Create the users table
98--
99CREATE TABLE users(id SERIAL PRIMARY KEY NOT NULL,
100                   username TEXT UNIQUE NOT NULL,
101                   balance FLOAT DEFAULT 0.0,
102                   lifetimepaid FLOAT DEFAULT 0.0,
103                   limitby TEXT DEFAULT 'quota');
104                   
105--
106-- Create the groups table
107--
108CREATE TABLE groups(id SERIAL PRIMARY KEY NOT NULL,
109                    groupname TEXT UNIQUE NOT NULL,
110                    limitby TEXT DEFAULT 'quota');
111                   
112--
113-- Create the printers table
114--
115CREATE TABLE printers(id SERIAL PRIMARY KEY NOT NULL,
116                      printername TEXT UNIQUE NOT NULL,
117                      priceperpage FLOAT DEFAULT 0.0,
118                      priceperjob FLOAT DEFAULT 0.0);
119                   
120--
121-- Create the print quota table for users
122--
123CREATE TABLE userpquota(id SERIAL PRIMARY KEY NOT NULL,
124                        userid INT4 REFERENCES users(id),
125                        printerid INT4 REFERENCES printers(id),
126                        lifepagecounter INT4 DEFAULT 0,
127                        pagecounter INT4 DEFAULT 0,
128                        softlimit INT4,
129                        hardlimit INT4,
130                        datelimit TIMESTAMP);
131                       
132--
133-- Create the job history table
134--
135CREATE TABLE jobhistory(id SERIAL PRIMARY KEY NOT NULL,
136                        jobid TEXT,
137                        userid INT4 REFERENCES users(id),
138                        printerid INT4 REFERENCES printers(id),
139                        pagecounter INT4 DEFAULT 0,
140                        jobsize INT4,
141                        action TEXT,
142                        jobdate TIMESTAMP DEFAULT now());
143                       
144--
145-- Create the print quota table for groups
146--
147CREATE TABLE grouppquota(id SERIAL PRIMARY KEY NOT NULL,
148                         groupid INT4 REFERENCES groups(id),
149                         printerid INT4 REFERENCES printers(id),
150                         softlimit INT4,
151                         hardlimit INT4,
152                         datelimit TIMESTAMP);
153                       
154--                         
155-- Create the groups/members relationship
156--
157CREATE TABLE groupsmembers(groupid INT4 REFERENCES groups(id),
158                           userid INT4 REFERENCES users(id),
159                           PRIMARY KEY (groupid, userid));
160
161--                       
162-- Set some ACLs                       
163--
164REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, jobhistory FROM public;                       
165GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, jobhistory TO pykotaadmin;
166GRANT SELECT, UPDATE ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq TO pykotaadmin;
167GRANT SELECT, UPDATE ON printers, userpquota, grouppquota TO pykotauser;
168GRANT SELECT ON users, groups, groupsmembers TO pykotauser;
169GRANT SELECT, INSERT, UPDATE ON jobhistory TO pykotauser;
170GRANT SELECT, UPDATE ON jobhistory_id_seq TO pykotauser;
Note: See TracBrowser for help on using the browser.