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

Revision 1087, 4.8 kB (checked in by jalet, 21 years ago)

Really big modifications wrt new configuration file's location and content.

  • 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.4  2003/07/16 21:53:07  jalet
23-- Really big modifications wrt new configuration file's location and content.
24--
25-- Revision 1.3  2003/07/09 20:17:07  jalet
26-- Email field added to PostgreSQL schema
27--
28-- Revision 1.2  2003/06/10 16:37:54  jalet
29-- Deletion of the second user which is not needed anymore.
30-- Added a debug configuration field in /etc/pykota.conf
31-- All queries can now be sent to the logger in debug mode, this will
32-- greatly help improve performance when time for this will come.
33--
34-- Revision 1.1  2003/06/05 07:12:31  jalet
35-- Reorganization of directories
36--
37--
38--
39
40--
41-- PyKota Database creation script for PostgreSQL
42--
43-- Launch this as PostgreSQL administrator with \i
44--
45
46
47--
48-- Create the print quota database
49--
50CREATE DATABASE pykota;
51
52--
53-- Create the print quota database users
54--
55CREATE USER pykotaadmin;
56CREATE USER pykotauser;
57
58--
59-- Now connect to the new database
60--
61\connect pykota
62
63--
64-- Create the users table
65--
66CREATE TABLE users(id SERIAL PRIMARY KEY NOT NULL,
67                   username TEXT UNIQUE NOT NULL,
68                   email TEXT, 
69                   balance FLOAT DEFAULT 0.0,
70                   lifetimepaid FLOAT DEFAULT 0.0,
71                   limitby TEXT DEFAULT 'quota');
72                   
73--
74-- Create the groups table
75--
76CREATE TABLE groups(id SERIAL PRIMARY KEY NOT NULL,
77                    groupname TEXT UNIQUE NOT NULL,
78                    limitby TEXT DEFAULT 'quota');
79                   
80--
81-- Create the printers table
82--
83CREATE TABLE printers(id SERIAL PRIMARY KEY NOT NULL,
84                      printername TEXT UNIQUE NOT NULL,
85                      priceperpage FLOAT DEFAULT 0.0,
86                      priceperjob FLOAT DEFAULT 0.0);
87                   
88--
89-- Create the print quota table for users
90--
91CREATE TABLE userpquota(id SERIAL PRIMARY KEY NOT NULL,
92                        userid INT4 REFERENCES users(id),
93                        printerid INT4 REFERENCES printers(id),
94                        lifepagecounter INT4 DEFAULT 0,
95                        pagecounter INT4 DEFAULT 0,
96                        softlimit INT4,
97                        hardlimit INT4,
98                        datelimit TIMESTAMP);
99                       
100--
101-- Create the job history table
102--
103CREATE TABLE jobhistory(id SERIAL PRIMARY KEY NOT NULL,
104                        jobid TEXT,
105                        userid INT4 REFERENCES users(id),
106                        printerid INT4 REFERENCES printers(id),
107                        pagecounter INT4 DEFAULT 0,
108                        jobsize INT4,
109                        action TEXT,
110                        jobdate TIMESTAMP DEFAULT now());
111                       
112--
113-- Create the print quota table for groups
114--
115CREATE TABLE grouppquota(id SERIAL PRIMARY KEY NOT NULL,
116                         groupid INT4 REFERENCES groups(id),
117                         printerid INT4 REFERENCES printers(id),
118                         softlimit INT4,
119                         hardlimit INT4,
120                         datelimit TIMESTAMP);
121                       
122--                         
123-- Create the groups/members relationship
124--
125CREATE TABLE groupsmembers(groupid INT4 REFERENCES groups(id),
126                           userid INT4 REFERENCES users(id),
127                           PRIMARY KEY (groupid, userid));
128
129--                       
130-- Set some ACLs                       
131--
132REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, jobhistory FROM public;                       
133REVOKE ALL ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq FROM public;
134
135GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, jobhistory TO pykotaadmin;
136GRANT SELECT, UPDATE ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq TO pykotaadmin;
137GRANT SELECT ON users, groups, printers, userpquota, grouppquota, groupsmembers, jobhistory TO pykotauser;
Note: See TracBrowser for help on using the browser.