root / pykota / trunk / initscripts / postgresql / upgrade-to-1.16.sql @ 3481

Revision 3481, 2.4 kB (checked in by jerome, 15 years ago)

Changed copyright years.
Copyright years are now dynamic when displayed by a command line tool.

  • 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-2009 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 3 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, see <http://www.gnu.org/licenses/>.
17--
18-- $Id$
19--
20--
21--
22-- This script has to be used if you already
23-- have a pre-1.16 version of PyKota to upgrade
24-- your database schema.
25--
26-- YOU DON'T NEED TO USE IT IF YOU'VE JUST INSTALLED PYKOTA
27--
28
29--
30-- WARNING : YOU NEED A RECENT VERSION OF POSTGRESQL FOR THE ALTER COLUMN STATEMENT TO WORK !
31--
32
33--
34-- Modify the old database schema
35--
36ALTER TABLE jobhistory ADD COLUMN jobprice FLOAT;
37ALTER TABLE jobhistory ADD COLUMN filename TEXT;
38ALTER TABLE jobhistory ADD COLUMN title TEXT;
39ALTER TABLE jobhistory ADD COLUMN copies INT4;
40ALTER TABLE jobhistory ADD COLUMN options TEXT;
41
42--
43-- Remove bad integrity rules
44-- and replace them with a new one
45--
46ALTER TABLE jobhistory DROP CONSTRAINT "$1";
47ALTER TABLE jobhistory DROP CONSTRAINT "$2";
48ALTER TABLE jobhistory ADD CONSTRAINT checkUserPQuota FOREIGN KEY (userid, printerid) REFERENCES userpquota (userid, printerid);
49
50--
51-- Add new tables
52--
53--
54-- Create the printer groups relationship
55--
56CREATE TABLE printergroupsmembers(groupid INT4 REFERENCES printers(id),
57                           printerid INT4 REFERENCES printers(id),
58                           PRIMARY KEY (groupid, printerid));
59
60--
61-- Now add some indexes
62--
63CREATE UNIQUE INDEX userpquota_up_id_ix ON userpquota (userid, printerid);
64CREATE INDEX jobhistory_p_id_ix ON jobhistory (printerid);
65CREATE INDEX jobhistory_pd_id_ix ON jobhistory (printerid, jobdate);
66CREATE UNIQUE INDEX grouppquota_up_id_ix ON grouppquota (groupid, printerid);
67
68--
69-- And now sets some ACLs
70--
71REVOKE ALL ON printergroupsmembers FROM public;
72GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON printergroupsmembers TO pykotaadmin;
73GRANT SELECT ON printergroupsmembers TO pykotauser;
Note: See TracBrowser for help on using the browser.