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

Revision 887, 4.2 kB (checked in by jalet, 22 years ago)

The last job Id is saved now for each printer, this will probably
allow other accounting methods in the future.

  • Property svn:eol-style set to native
  • Property svn:keywords set to Author Date Id Revision
Line 
1--
2-- PyKota - Print Quotas for CUPS
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.7  2003/04/08 20:38:08  jalet
23-- The last job Id is saved now for each printer, this will probably
24-- allow other accounting methods in the future.
25--
26-- Revision 1.6  2003/03/29 13:45:27  jalet
27-- GPL paragraphs were incorrectly (from memory) copied into the sources.
28-- Two README files were added.
29-- Upgrade script for PostgreSQL pre 1.01 schema was added.
30--
31-- Revision 1.5  2003/03/12 19:06:08  jalet
32-- Initial support for groups added.
33--
34-- Revision 1.4  2003/02/27 08:40:14  jalet
35-- DATETIME is not supported anymore in PostgreSQL 7.3 it seems, but
36-- TIMESTAMP is.
37--
38-- Revision 1.3  2003/02/26 20:34:22  jalet
39-- Default value for printer page counter set to 0
40--
41-- Revision 1.2  2003/02/08 22:12:09  jalet
42-- Life time counter for users and groups added.
43--
44-- Revision 1.1  2003/02/05 21:28:17  jalet
45-- Initial import into CVS
46--
47--
48--
49
50--
51-- PyKota Database creation script for PostgreSQL
52--
53-- Launch this as PostgreSQL administrator with \i
54--
55
56
57--
58-- Create the print quota database
59--
60CREATE DATABASE pykota;
61
62--
63-- Create the print quota database users
64--
65CREATE USER pykotauser;
66CREATE USER pykotaadmin;
67
68--
69-- Now connect to the new database
70--
71\connect pykota
72
73--
74-- Create the users table
75--
76CREATE TABLE users(id SERIAL PRIMARY KEY NOT NULL,
77                   username TEXT UNIQUE NOT NULL);
78                   
79--
80-- Create the groups table
81--
82CREATE TABLE groups(id SERIAL PRIMARY KEY NOT NULL,
83                    groupname TEXT UNIQUE NOT NULL);
84                   
85--
86-- Create the printers table
87--
88CREATE TABLE printers(id SERIAL PRIMARY KEY NOT NULL,
89                      printername TEXT UNIQUE NOT NULL,
90                      lastjobid TEXT,
91                      lastusername TEXT,
92                      pagecounter INT4 DEFAULT 0);
93                   
94--
95-- Create the print quota table for users
96--
97CREATE TABLE userpquota(id SERIAL PRIMARY KEY NOT NULL,
98                        userid INT4 REFERENCES users(id),
99                        printerid INT4 REFERENCES printers(id),
100                        lifepagecounter INT4 DEFAULT 0,
101                        pagecounter INT4 DEFAULT 0,
102                        softlimit INT4,
103                        hardlimit INT4,
104                        datelimit TIMESTAMP);
105                       
106--
107-- Create the print quota table for groups
108--
109CREATE TABLE grouppquota(id SERIAL PRIMARY KEY NOT NULL,
110                         groupid INT4 REFERENCES groups(id),
111                         printerid INT4 REFERENCES printers(id),
112                         softlimit INT4,
113                         hardlimit INT4,
114                         datelimit TIMESTAMP);
115                       
116--                         
117-- Create the groups/members relationship
118--
119CREATE TABLE groupsmembers(groupid INT4 REFERENCES groups(id),
120                           userid INT4 REFERENCES users(id),
121                           PRIMARY KEY (groupid, userid));
122
123--                       
124-- Set some ACLs                       
125--
126REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers FROM public;                       
127GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers TO pykotaadmin;
128GRANT SELECT, UPDATE ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq TO pykotaadmin;
129GRANT SELECT, UPDATE ON printers, userpquota, grouppquota TO pykotauser;
130GRANT SELECT ON users, groups, groupsmembers TO pykotauser;
Note: See TracBrowser for help on using the browser.