Show
Ignore:
Timestamp:
09/27/08 22:02:37 (16 years ago)
Author:
jerome
Message:

Removed unnecessary spaces at EOL.

Location:
pykota/trunk/initscripts/postgresql
Files:
9 modified

Legend:

Unmodified
Added
Removed

  • pykota/trunk/initscripts/postgresql/pykota-postgresql.sql

    r3279 r3413  
    77-- the Free Software Foundation, either version 3 of the License, or 
    88-- (at your option) any later version. 
    9 --  
     9-- 
    1010-- This program is distributed in the hope that it will be useful, 
    1111-- but WITHOUT ANY WARRANTY; without even the implied warranty of 
    1212-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
    1313-- GNU General Public License for more details. 
    14 --  
     14-- 
    1515-- You should have received a copy of the GNU General Public License 
    1616-- along with this program.  If not, see <http://www.gnu.org/licenses/>. 
    … …  
    3535-- Create the print quota database users 
    3636-- NOTE: Change the password values to the passwords you would like. 
    37 --  
     37-- 
    3838CREATE USER pykotauser WITH UNENCRYPTED PASSWORD 'readonlypw' NOCREATEDB NOCREATEUSER; 
    3939CREATE USER pykotaadmin WITH UNENCRYPTED PASSWORD 'readwritepw' NOCREATEDB NOCREATEUSER; 
    4040 
    41 --  
     41-- 
    4242-- Now connect to the new database 
    43 --  
     43-- 
    4444\connect pykota 
    4545 
    … …  
    4949CREATE TABLE users(id SERIAL PRIMARY KEY NOT NULL, 
    5050                   username TEXT UNIQUE NOT NULL, 
    51                    email TEXT,  
     51                   email TEXT, 
    5252                   balance FLOAT DEFAULT 0.0, 
    5353                   lifetimepaid FLOAT DEFAULT 0.0, 
    … …  
    5555                   description TEXT, 
    5656                   overcharge FLOAT NOT NULL DEFAULT 1.0); 
    57                     
     57 
    5858-- 
    5959-- Create the groups table 
    … …  
    6363                    description TEXT, 
    6464                    limitby TEXT DEFAULT 'quota'); 
    65                      
     65 
    6666-- 
    6767-- Create the printers table 
    … …  
    7474                      passthrough BOOLEAN DEFAULT FALSE, 
    7575                      maxjobsize INT4); 
    76                      
     76 
    7777-- 
    7878-- Create the print quota table for users 
    … …  
    8787                        datelimit TIMESTAMP, 
    8888                        maxjobsize INT4, 
    89                         warncount INT4 DEFAULT 0);  
     89                        warncount INT4 DEFAULT 0); 
    9090CREATE INDEX userpquota_u_id_ix ON userpquota (userid); 
    9191CREATE INDEX userpquota_p_id_ix ON userpquota (printerid); 
    9292CREATE UNIQUE INDEX userpquota_up_id_ix ON userpquota (userid, printerid); 
    93                          
     93 
    9494-- 
    9595-- Create the job history table 
    … …  
    120120CREATE INDEX jobhistory_pd_id_ix ON jobhistory (printerid, jobdate); 
    121121CREATE INDEX jobhistory_hostname_ix ON jobhistory (hostname); 
    122                          
     122 
    123123-- 
    124124-- Create the print quota table for groups 
    … …  
    134134CREATE INDEX grouppquota_p_id_ix ON grouppquota (printerid); 
    135135CREATE UNIQUE INDEX grouppquota_up_id_ix ON grouppquota (groupid, printerid); 
    136                          
    137 --                          
     136 
     137-- 
    138138-- Create the groups/members relationship 
    139139-- 
    … …  
    141141                           userid INT4 REFERENCES users(id), 
    142142                           PRIMARY KEY (groupid, userid)); 
    143                             
    144 --                          
     143 
     144-- 
    145145-- Create the printer groups relationship 
    146146-- 
    … …  
    150150-- 
    151151-- Create the table for payments 
    152 --  
     152-- 
    153153CREATE TABLE payments (id SERIAL PRIMARY KEY NOT NULL, 
    154154                       userid INT4 REFERENCES users(id), 
    … …  
    158158CREATE INDEX payments_date_ix ON payments (date); 
    159159 
    160 --  
     160-- 
    161161-- Create the table for coefficients wrt paper sizes and the like 
    162162-- 
    163 CREATE TABLE coefficients (id SERIAL PRIMARY KEY NOT NULL,  
    164                            printerid INTEGER NOT NULL REFERENCES printers(id),  
    165                            label TEXT NOT NULL,  
    166                            coefficient FLOAT DEFAULT 1.0,  
     163CREATE TABLE coefficients (id SERIAL PRIMARY KEY NOT NULL, 
     164                           printerid INTEGER NOT NULL REFERENCES printers(id), 
     165                           label TEXT NOT NULL, 
     166                           coefficient FLOAT DEFAULT 1.0, 
    167167                           CONSTRAINT coeffconstraint UNIQUE (printerid, label)); 
    168168 
    169 --  
     169-- 
    170170-- Create the table for the billing codes 
    171171-- 
    … …  
    176176                           pagecounter INT4 DEFAULT 0); 
    177177 
    178 --                         
    179 -- Set some ACLs                         
     178-- 
     179-- Set some ACLs 
    180180-- 
    181181REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients, billingcodes FROM public; 

  • pykota/trunk/initscripts/postgresql/README.postgresql

    r3279 r3413  
    3131        - edit the pykota-postgresql.sql file and modify the passwords on 
    3232          the 'CREATE USER' lines. 
    33            
     33 
    3434        - Launch the psql frontend and connect to the 
    3535          database template1 as a PostgreSQL administrator 
    3636          (i.e. the postgres user) : 
    37            
     37 
    3838          as user 'root' : 
    39            
     39 
    4040            # su - postgres 
    41              
    42           you're now user 'postgres', then continue with :    
    43            
     41 
     42          you're now user 'postgres', then continue with : 
     43 
    4444            $ psql -f pykota-postgresql.sql template1 
    45              
    46           or depending on your settings you may prefer :   
    47            
     45 
     46          or depending on your settings you may prefer : 
     47 
    4848            $ psql -h localhost -U postgres -f pykota-postgresql.sql template1 
    49            
    50         - If no error message appears, your PostgreSQL PyKota storage  
     49 
     50        - If no error message appears, your PostgreSQL PyKota storage 
    5151          is now ready for use. NB : a lot of NOTICE messages might appear, 
    5252          this is perfectly normal. 
    53            
     53 
    5454        - In ~pykota/pykota.conf, ensure that the following lines 
    5555          are present and unique inside the [global] section : 
    56          
     56 
    5757            storagebackend : pgstorage 
    5858            storageserver : localhost 
    … …  
    6060            storageuser : pykotauser 
    6161            storageuserpw : readonlypw 
    62              
     62 
    6363            NB : replace 'readonlypw' with the password you chose 
    6464            for user 'pykotauser' when you modified pykota-postgresql.sql 
    65              
     65 
    6666        - In ~pykota/pykotadmin.conf ensure that the following lines 
    6767          are present and unique inside the [global] section : 
    68          
     68 
    6969            storageadmin : pykotaadmin 
    7070            storageadminpw : readwritepw 
    71              
     71 
    7272            NB : replace 'readwritepw' with the password you chose 
    7373            for user 'pykotaadmin' when you modified pykota-postgresql.sql 
    74          
    75         - IMPORTANT : if your database is not located on the print server,  
    76           replace 'localhost' in the lines above with your database server's  
     74 
     75        - IMPORTANT : if your database is not located on the print server, 
     76          replace 'localhost' in the lines above with your database server's 
    7777          hostname or IP address. If your database server doesn't listen on 
    7878          its standard port, add the port number at the end of the hostname 
    7979          with a colon to separate the hostname from the port. 
    8080          See pykota/conf/pykota.conf.sample for examples. 
    81            
     81 
    8282============================================================ 
    8383 
    … …  
    8989  Some scripts to upgrade old PyKota databases to the new 
    9090  database schema are included. You may have to run all 
    91   of them or only some of them, depending on your  
     91  of them or only some of them, depending on your 
    9292  version of PyKota. Usually running them several times, for 
    9393  example if you used several different development versions 
    9494  of PyKota, won't do any harm. 
    95    
     95 
    9696  If you're connected as user 'root', you have to be user 'postgres' 
    9797  instead : 
    98            
     98 
    9999            # su - postgres 
    100              
     100 
    101101  You're now user 'postgres', then continue the upgrade by following 
    102102  the instructions below, depending on the version you actually use : 
    103    
     103 
    104104  * An SQL script to upgrade a 1.22 PyKota Storage DataBase to 
    105105    1.23 is included. Launch it this way on the Quota Storage Server : 
    106      
     106 
    107107        $ psql -U postgres pykota 
    108108        pykota=# \i upgrade-to-1.23.sql 
    109109        pykota=# \q 
    110110        $ 
    111          
     111 
    112112    This script adds many fields to existing tables, and a table to 
    113113    manage billing codes. 
    114      
     114 
    115115  * An SQL script to upgrade a 1.20 PyKota Storage DataBase to 
    116116    1.21 is included. Launch it this way on the Quota Storage Server : 
    117      
     117 
    118118        $ psql -U postgres pykota 
    119119        pykota=# \i upgrade-to-1.21.sql 
    120120        pykota=# \q 
    121121        $ 
    122          
     122 
    123123    This script adds many fields to existing tables, and also adds 
    124124    some tables in indexes. 
    125      
     125 
    126126  * An SQL script to upgrade a 1.18 PyKota Storage DataBase to 
    127127    1.19 is included. Launch it this way on the Quota Storage Server : 
    128      
     128 
    129129        $ psql -U postgres pykota 
    130130        pykota=# \i upgrade-to-1.19.sql 
    131131        pykota=# \q 
    132132        $ 
    133          
     133 
    134134    This script adds a field to the print job history. The field that 
    135135    is added is : hostname 
    136      
     136 
    137137  * An SQL script to upgrade a pre-1.16 PyKota Storage DataBase to 
    138138    1.16 is included. Launch it this way on the Quota Storage Server : 
    139      
     139 
    140140        $ psql -U postgres pykota 
    141141        pykota=# \i upgrade-to-1.16.sql 
    142142        pykota=# \q 
    143143        $ 
    144          
     144 
    145145    This script adds some fields to the print job history, so that 
    146146    more complete information will be known. The fields that 
    147     are added are : jobprice, filename, title, copies and print  
     147    are added are : jobprice, filename, title, copies and print 
    148148    command options. Also some indexes are created to speed things up. 
    149    
    150 What is below is for historical reasons only, real people don't use   
     149 
     150What is below is for historical reasons only, real people don't use 
    151151such old beasts, and YOU SHOULDN'T EITHER ! 
    152152 
    153153  * An SQL script to upgrade a pre-1.14 PyKota Storage DataBase to 
    154154    1.14 is included. Launch it this way on the Quota Storage Server : 
    155      
     155 
    156156        $ psql -U postgres pykota 
    157157        pykota=# \i upgrade-to-1.14.sql 
    158158        pykota=# \q 
    159159        $ 
    160          
    161     This script adds an "email" field for each user. This field, if not     
     160 
     161    This script adds an "email" field for each user. This field, if not 
    162162    NULL, is used as the email address of the user when sending warning 
    163163    messages in case he is over quota. 
    164      
    165   * An sql script to upgrade a pre-1.01 PyKota Quota Storage DataBase to  
    166     1.01 is still included for historical reasons. PLEASE DON'T USE IT !  
    167    
    168   * If you are already running an old version of PyKota (<1.03) and  
    169     want to upgrade to 1.03, YOU HAVE TO RUN the  
    170     upgrade-from-before-1.03.py shell script. NOT LAUNCHING THIS SCRIPT  
    171     WILL CAUSE PYKOTA TO STOP WORKING ! PLEASE NEVER RUN THIS SCRIPT IF  
    172     YOUR EXISTING VERSION OF PYKOTA IS ALREADY 1.03 OR HIGHER.  
     164 
     165  * An sql script to upgrade a pre-1.01 PyKota Quota Storage DataBase to 
     166    1.01 is still included for historical reasons. PLEASE DON'T USE IT ! 
     167 
     168  * If you are already running an old version of PyKota (<1.03) and 
     169    want to upgrade to 1.03, YOU HAVE TO RUN the 
     170    upgrade-from-before-1.03.py shell script. NOT LAUNCHING THIS SCRIPT 
     171    WILL CAUSE PYKOTA TO STOP WORKING ! PLEASE NEVER RUN THIS SCRIPT IF 
     172    YOUR EXISTING VERSION OF PYKOTA IS ALREADY 1.03 OR HIGHER. 

  • pykota/trunk/initscripts/postgresql/upgrade-from-before-1.03.py

    r3259 r3413  
    88# the Free Software Foundation, either version 3 of the License, or 
    99# (at your option) any later version. 
    10 #  
     10# 
    1111# This program is distributed in the hope that it will be useful, 
    1212# but WITHOUT ANY WARRANTY; without even the implied warranty of 
    1313# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
    1414# GNU General Public License for more details. 
    15 #  
     15# 
    1616# You should have received a copy of the GNU General Public License 
    1717# along with this program.  If not, see <http://www.gnu.org/licenses/>. 
    … …  
    2626try : 
    2727    import pg 
    28 except ImportError :     
     28except ImportError : 
    2929    sys.stderr.write("The PyGreSQL Python module doesn't seem to be available. ABORTED.\n") 
    3030    sys.exit(-1) 
    … …  
    3232def dump_old_database() : 
    3333    """Dumps the existing PyKota database to a file, to avoir loosing data. 
    34      
     34 
    3535       Returns 1 if dump is successfull, 0 if it isn't. 
    36     """    
     36    """ 
    3737    pipeinput = os.popen("pg_dump -C -D -N -U postgres -f pykota-dump.sql pykota") 
    3838    dummy = pipeinput.read() 
    … …  
    4444    dummy = pipeinput.read() 
    4545    return (pipeinput.close() is None) or 0 
    46      
     46 
    4747def restore_original_database() : 
    4848    """Creates the empty database.""" 
    … …  
    5050    dummy = pipeinput.read() 
    5151    return (pipeinput.close() is None) or 0 
    52      
     52 
    5353def open_database(dbname="pykota") : 
    5454    """Returns the database object or None if we can't connect to it.""" 
    5555    try : 
    5656        pykotadb = pg.connect(host="localhost", port=5432, dbname=dbname, user="postgres") 
    57     except pg.error, msg :      
     57    except pg.error, msg : 
    5858        sys.stderr.write("%s\n" % msg) 
    5959        sys.stderr.write("Unable to connect to the local PostgreSQL server.\nPlease modify the open_database() method in %s\nto connect to the correct PostgreSQL server\nand relaunch the script.\n" % sys.argv[0]) 
    60         return  
    61     else :     
     60        return 
     61    else : 
    6262        return pykotadb 
    63          
    64          
     63 
     64 
    6565def doQuote(field) : 
    6666    """Quotes a field for use as a string in SQL queries.""" 
    6767    if type(field) == type(0) : # TODO : do something safer 
    6868        typ = "decimal" 
    69     else :     
     69    else : 
    7070        typ = "text" 
    7171    return pg._quote(field, typ) 
    72          
     72 
    7373def main() : 
    7474    """Does the work.""" 
    75      
     75 
    7676    # First we make a dump of the old database 
    7777    print "Dumping old database for safety...", 
    … …  
    8080        return -1 
    8181    print "Done." 
    82          
    83     # Second we try to connect to it     
    84     print "Extracting datas from old database...",  
     82 
     83    # Second we try to connect to it 
     84    print "Extracting datas from old database...", 
    8585    db = open_database() 
    8686    if db is None : 
    8787        sys.stderr.write("Impossible to connect to old PyKota database. ABORTED.\nAre you sure you are upgrading an existing installation ?\n") 
    8888        return -1 
    89      
     89 
    9090    # Third we extract datas 
    9191    oldprinters = db.query("SELECT * FROM printers ORDER BY id;") 
    9292    oldusers = db.query("SELECT * FROM users ORDER BY id;") 
    9393    oldquotas = db.query("SELECT * FROM userpquota ORDER BY printerid, userid;") 
    94      
     94 
    9595    # Fourth close the database 
    9696    db.close() 
    9797    print "Done." 
    98      
     98 
    9999    # Fifth we delete the old database ! 
    100100    answer = raw_input("The old database will be deleted for the upgrade to take place.\nAre you sure you want to continue (y/N) ? ") 
    … …  
    112112        db.close() 
    113113        return -1 
    114     else :     
     114    else : 
    115115        db.close() 
    116          
     116 
    117117    # Sixth we create the new database 
    118118    print "Creating the new database...", 
    … …  
    121121        return -1 
    122122    print "Done." 
    123      
     123 
    124124    # Seventh we restore old data 
    125125    print "Restoring old datas..." 
    … …  
    131131            sys.stderr.write("Shit ! A double-error occured !!!\nPlease report problem to your database administrator.\n") 
    132132            sys.stderr.write("And file a bug report to alet@librelogiciel.com\n") 
    133         else :     
     133        else : 
    134134            print "Done." 
    135135        return -1 
    136     db.query("BEGIN;")     
     136    db.query("BEGIN;") 
    137137    try : 
    138138        newprinters = {} 
    … …  
    141141            newid = db.query("SELECT id FROM printers WHERE printername='%s';" % oldprinter["printername"]).dictresult()[0]["id"] 
    142142            newprinters[oldprinter["id"]] = newid 
    143         newusers = {}     
    144         for olduser in oldusers.dictresult() :     
     143        newusers = {} 
     144        for olduser in oldusers.dictresult() : 
    145145            db.query("INSERT INTO users (username) VALUES (%s);" % doQuote(olduser["username"])) 
    146146            newid = db.query("SELECT id FROM users WHERE username='%s';" % olduser["username"]).dictresult()[0]["id"] 
    147147            newusers[olduser["id"]] = newid 
    148         for oldquota in oldquotas.dictresult() :    
    149             db.query("INSERT INTO userpquota (userid, printerid, pagecounter, lifepagecounter, softlimit, hardlimit, datelimit) VALUES (%s, %s, %s, %s, %s, %s, %s);" %  
     148        for oldquota in oldquotas.dictresult() : 
     149            db.query("INSERT INTO userpquota (userid, printerid, pagecounter, lifepagecounter, softlimit, hardlimit, datelimit) VALUES (%s, %s, %s, %s, %s, %s, %s);" % 
    150150                                              (doQuote(newusers[oldquota["userid"]]) , doQuote(newprinters[oldquota["printerid"]]), doQuote(oldquota["pagecounter"]), doQuote(oldquota["lifepagecounter"]), doQuote(oldquota["softlimit"]), doQuote(oldquota["hardlimit"]), doQuote(oldquota["datelimit"]))) 
    151     except pg.error, msg :     
     151    except pg.error, msg : 
    152152        sys.stderr.write("ERROR : %s\nABORTED.\n" % msg) 
    153153        db.query("ROLLBACK;") 
    154         db.close()     
     154        db.close() 
    155155        return -1 
    156     except :     
     156    except : 
    157157        sys.stderr.write("Unknown error ! ABORTED.\n") 
    158158        db.query("ROLLBACK;") 
    159         db.close()     
     159        db.close() 
    160160        return -1 
    161     else :     
     161    else : 
    162162        db.query("COMMIT;") 
    163163        db.close() 
    … …  
    165165    print "NB : Last job on each printer was lost. This is normal !" 
    166166    return 0 
    167      
     167 
    168168if __name__ == "__main__" : 
    169169    sys.exit(main()) 

  • pykota/trunk/initscripts/postgresql/upgrade-to-1.14.sql

    r3259 r3413  
    77-- the Free Software Foundation, either version 3 of the License, or 
    88-- (at your option) any later version. 
    9 --  
     9-- 
    1010-- This program is distributed in the hope that it will be useful, 
    1111-- but WITHOUT ANY WARRANTY; without even the implied warranty of 
    1212-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
    1313-- GNU General Public License for more details. 
    14 --  
     14-- 
    1515-- You should have received a copy of the GNU General Public License 
    1616-- along with this program.  If not, see <http://www.gnu.org/licenses/>. 
    … …  
    1919-- 
    2020-- 
    21                          
    22 --                          
     21 
     22-- 
    2323-- WARNING : YOU NEED A RECENT VERSION OF POSTGRESQL FOR THE ALTER COLUMN STATEMENT TO WORK ! 
    2424-- 
    2525 
    26 --                          
     26-- 
    2727-- Modify the old database schema 
    2828-- 

  • pykota/trunk/initscripts/postgresql/upgrade-to-1.16.sql

    r3259 r3413  
    77-- the Free Software Foundation, either version 3 of the License, or 
    88-- (at your option) any later version. 
    9 --  
     9-- 
    1010-- This program is distributed in the hope that it will be useful, 
    1111-- but WITHOUT ANY WARRANTY; without even the implied warranty of 
    1212-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
    1313-- GNU General Public License for more details. 
    14 --  
     14-- 
    1515-- You should have received a copy of the GNU General Public License 
    1616-- along with this program.  If not, see <http://www.gnu.org/licenses/>. 
    17 --  
     17-- 
    1818-- $Id$ 
    1919-- 
    … …  
    2222-- This script has to be used if you already 
    2323-- have a pre-1.16 version of PyKota to upgrade 
    24 -- your database schema.  
     24-- your database schema. 
    2525-- 
    2626-- YOU DON'T NEED TO USE IT IF YOU'VE JUST INSTALLED PYKOTA 
    2727-- 
    28                          
    29 --                          
     28 
     29-- 
    3030-- WARNING : YOU NEED A RECENT VERSION OF POSTGRESQL FOR THE ALTER COLUMN STATEMENT TO WORK ! 
    3131-- 
    3232 
    33 --                          
     33-- 
    3434-- Modify the old database schema 
    3535-- 
    … …  
    4848ALTER TABLE jobhistory ADD CONSTRAINT checkUserPQuota FOREIGN KEY (userid, printerid) REFERENCES userpquota (userid, printerid); 
    4949 
    50 --  
     50-- 
    5151-- Add new tables 
    5252-- 
    53 --                          
     53-- 
    5454-- Create the printer groups relationship 
    5555-- 
    … …  
    5858                           PRIMARY KEY (groupid, printerid)); 
    5959 
    60 --                          
     60-- 
    6161-- Now add some indexes 
    6262-- 
    … …  
    6666CREATE UNIQUE INDEX grouppquota_up_id_ix ON grouppquota (groupid, printerid); 
    6767 
    68 --  
     68-- 
    6969-- And now sets some ACLs 
    70 --  
    71 REVOKE ALL ON printergroupsmembers FROM public;                         
     70-- 
     71REVOKE ALL ON printergroupsmembers FROM public; 
    7272GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON printergroupsmembers TO pykotaadmin; 
    7373GRANT SELECT ON printergroupsmembers TO pykotauser; 

  • pykota/trunk/initscripts/postgresql/upgrade-to-1.19.sql

    r3259 r3413  
    77-- the Free Software Foundation, either version 3 of the License, or 
    88-- (at your option) any later version. 
    9 --  
     9-- 
    1010-- This program is distributed in the hope that it will be useful, 
    1111-- but WITHOUT ANY WARRANTY; without even the implied warranty of 
    1212-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
    1313-- GNU General Public License for more details. 
    14 --  
     14-- 
    1515-- You should have received a copy of the GNU General Public License 
    1616-- along with this program.  If not, see <http://www.gnu.org/licenses/>. 
    … …  
    2222-- This script has to be used if you already 
    2323-- have a pre-1.19 version of PyKota to upgrade 
    24 -- your database schema.  
     24-- your database schema. 
    2525-- 
    2626-- YOU DON'T NEED TO USE IT IF YOU'VE JUST INSTALLED PYKOTA 
    2727-- 
    28                          
    29 --                          
     28 
     29-- 
    3030-- WARNING : YOU NEED A RECENT VERSION OF POSTGRESQL FOR THE ALTER COLUMN STATEMENT TO WORK ! 
    3131-- 
    3232 
    33 --                          
     33-- 
    3434-- Modify the old database schema 
    3535-- 
    … …  
    4545CREATE INDEX payments_date_ix ON payments (date); 
    4646 
    47 REVOKE ALL ON payments FROM public;                         
     47REVOKE ALL ON payments FROM public; 
    4848REVOKE ALL ON payments_id_seq FROM public; 
    4949GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON payments TO pykotaadmin; 

  • pykota/trunk/initscripts/postgresql/upgrade-to-1.21.sql

    r3259 r3413  
    77-- the Free Software Foundation, either version 3 of the License, or 
    88-- (at your option) any later version. 
    9 --  
     9-- 
    1010-- This program is distributed in the hope that it will be useful, 
    1111-- but WITHOUT ANY WARRANTY; without even the implied warranty of 
    1212-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
    1313-- GNU General Public License for more details. 
    14 --  
     14-- 
    1515-- You should have received a copy of the GNU General Public License 
    1616-- along with this program.  If not, see <http://www.gnu.org/licenses/>. 
    … …  
    2222-- This script has to be used if you already 
    2323-- have a pre-1.21 version of PyKota to upgrade 
    24 -- your database schema.  
     24-- your database schema. 
    2525-- 
    2626-- YOU DON'T NEED TO USE IT IF YOU'VE JUST INSTALLED PYKOTA 
    2727-- 
    28                          
    29 --                          
     28 
     29-- 
    3030-- Modify the old database schema 
    3131-- 
    … …  
    5151CREATE INDEX jobhistory_u_id_ix ON jobhistory (userid); 
    5252 
    53 --  
     53-- 
    5454-- Create the table for coefficients wrt paper sizes and the like 
    5555-- 
    56 CREATE 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,  
     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, 
    6060                           CONSTRAINT coeffconstraint UNIQUE (printerid, label)); 
    61                             
    62 REVOKE ALL ON coefficients FROM public;                         
     61 
     62REVOKE ALL ON coefficients FROM public; 
    6363REVOKE ALL ON coefficients_id_seq FROM public; 
    6464GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON coefficients TO pykotaadmin; 

  • pykota/trunk/initscripts/postgresql/upgrade-to-1.23.sql

    r3259 r3413  
    77-- the Free Software Foundation, either version 3 of the License, or 
    88-- (at your option) any later version. 
    9 --  
     9-- 
    1010-- This program is distributed in the hope that it will be useful, 
    1111-- but WITHOUT ANY WARRANTY; without even the implied warranty of 
    1212-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
    1313-- GNU General Public License for more details. 
    14 --  
     14-- 
    1515-- You should have received a copy of the GNU General Public License 
    1616-- along with this program.  If not, see <http://www.gnu.org/licenses/>. 
    … …  
    2222-- This script has to be used if you already 
    2323-- have a pre-1.23alpha30 version of PyKota to upgrade 
    24 -- your database schema.  
     24-- your database schema. 
    2525-- 
    2626-- YOU DON'T NEED TO USE IT IF YOU'VE JUST INSTALLED PYKOTA 
    2727-- 
    28                          
    29 --                          
     28 
     29-- 
    3030-- Modify the old database schema 
    3131-- 
    … …  
    4949UPDATE printers SET passthrough=FALSE; 
    5050 
    51 --  
     51-- 
    5252-- Create the table for the billing codes 
    5353-- 
    … …  
    5959ALTER TABLE billingcodes RENAME COLUMN label TO billingcode; 
    6060 
    61 REVOKE ALL ON billingcodes FROM public;                         
     61REVOKE ALL ON billingcodes FROM public; 
    6262REVOKE ALL ON billingcodes_id_seq FROM public; 
    6363GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON billingcodes TO pykotaadmin; 

  • pykota/trunk/initscripts/postgresql/VERYOLDpykota-upgrade-postgresql.sql

    r3259 r3413  
    77-- the Free Software Foundation, either version 3 of the License, or 
    88-- (at your option) any later version. 
    9 --  
     9-- 
    1010-- This program is distributed in the hope that it will be useful, 
    1111-- but WITHOUT ANY WARRANTY; without even the implied warranty of 
    1212-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the 
    1313-- GNU General Public License for more details. 
    14 --  
     14-- 
    1515-- You should have received a copy of the GNU General Public License 
    1616-- along with this program.  If not, see <http://www.gnu.org/licenses/>. 
    … …  
    2626-- the normal script instead. 
    2727-- 
    28                          
    29 --                          
     28 
     29-- 
    3030-- WARNING : YOU NEED A RECENT VERSION OF POSTGRESQL FOR THE DROP COLUMN STATEMENT TO WORK ! 
    3131-- 
    3232 
    33 --                          
     33-- 
    3434-- Modify the old database schema 
    3535-- 
    … …  
    3737ALTER TABLE grouppquota DROP COLUMN pagecounter; 
    3838 
    39 --                          
     39-- 
    4040-- Create the groups/members relationship 
    4141-- 
    … …  
    4343                           userid INT4 REFERENCES users(id), 
    4444                           PRIMARY KEY (groupid, userid)); 
    45                             
    46 --                         
    47 -- Set some ACLs                         
     45 
    4846-- 
    49 REVOKE ALL ON groupsmembers FROM public;                         
     47-- Set some ACLs 
     48-- 
     49REVOKE ALL ON groupsmembers FROM public; 
    5050GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON groupsmembers TO pykotaadmin;