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

Revision 3259, 2.5 kB (checked in by jerome, 17 years ago)

Began work on moving from GNU GPL v2 or later to GNU GPL v3 or later.
TODO : Also move command line tools and library.
TODO : Change documentation from SGML DocBook? to XML DocBook? to
TODO : accomodate the GNU GPL v3 text available in DocBook? XML
TODO : from www.gnu.org

  • 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, 2004, 2005, 2006, 2007 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.