root / pykota / trunk / initscripts / postgresql / upgrade-to-1.21.sql @ 3501

Revision 3481, 2.5 kB (checked in by jerome, 16 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.21 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-- Modify the old database schema
31--
32ALTER TABLE users DROP COLUMN coefficient;
33ALTER TABLE users ADD COLUMN overcharge FLOAT;
34ALTER TABLE users ALTER COLUMN overcharge SET DEFAULT 1.0;
35UPDATE users SET overcharge=1.0;
36ALTER TABLE users ALTER COLUMN overcharge SET NOT NULL;
37
38ALTER TABLE userpquota DROP COLUMN warned;
39ALTER TABLE userpquota ADD COLUMN warncount INT4;
40ALTER TABLE userpquota ALTER COLUMN warncount SET DEFAULT 0;
41CREATE INDEX userpquota_u_id_ix ON userpquota (userid);
42CREATE INDEX userpquota_p_id_ix ON userpquota (printerid);
43UPDATE userpquota SET warncount=0;
44
45CREATE INDEX grouppquota_g_id_ix ON grouppquota (groupid);
46CREATE INDEX grouppquota_p_id_ix ON grouppquota (printerid);
47
48ALTER TABLE jobhistory ADD COLUMN md5sum TEXT;
49ALTER TABLE jobhistory ADD COLUMN pages TEXT;
50ALTER TABLE jobhistory ADD COLUMN billingcode TEXT;
51CREATE INDEX jobhistory_u_id_ix ON jobhistory (userid);
52
53--
54-- Create the table for coefficients wrt paper sizes and the like
55--
56CREATE TABLE coefficients (id SERIAL PRIMARY KEY NOT NULL,
57                           printerid INTEGER NOT NULL REFERENCES printers(id),
58                           label TEXT NOT NULL,
59                           coefficient FLOAT DEFAULT 1.0,
60                           CONSTRAINT coeffconstraint UNIQUE (printerid, label));
61
62REVOKE ALL ON coefficients FROM public;
63REVOKE ALL ON coefficients_id_seq FROM public;
64GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON coefficients TO pykotaadmin;
65GRANT SELECT, UPDATE ON coefficients_id_seq TO pykotaadmin;
66GRANT SELECT ON coefficients TO pykotauser;
Note: See TracBrowser for help on using the browser.