Show
Ignore:
Timestamp:
06/15/05 22:46:47 (19 years ago)
Author:
jerome
Message:

Big database changes.
Severity : From now on, existing datas have to be migrated
using the appropriate script if needed. LDAP users will need
to use the new schema. Beginning tomorrow, the code will be in
experimental quality instead of stable quality.

Location:
pykota/trunk/initscripts/postgresql
Files:
1 added
1 modified

Legend:

Unmodified
Added
Removed
  • pykota/trunk/initscripts/postgresql/pykota-postgresql.sql

    r2303 r2317  
    5353                   lifetimepaid FLOAT DEFAULT 0.0, 
    5454                   limitby TEXT DEFAULT 'quota', 
     55                   description TEXT, 
    5556                   overcharge FLOAT NOT NULL DEFAULT 1.0); 
    5657                    
     
    6061CREATE TABLE groups(id SERIAL PRIMARY KEY NOT NULL, 
    6162                    groupname TEXT UNIQUE NOT NULL, 
     63                    description TEXT, 
    6264                    limitby TEXT DEFAULT 'quota'); 
    6365                     
     
    6971                      description TEXT, 
    7072                      priceperpage FLOAT DEFAULT 0.0, 
    71                       priceperjob FLOAT DEFAULT 0.0); 
     73                      priceperjob FLOAT DEFAULT 0.0, 
     74                      passthrough BOOLEAN DEFAULT 'f', 
     75                      maxjobsize INT4); 
    7276                     
    7377-- 
     
    8286                        hardlimit INT4, 
    8387                        datelimit TIMESTAMP, 
     88                        maxjobsize INT4, 
     89                        temporarydenied BOOLEAN DEFAULT 'f', 
    8490                        warncount INT4 DEFAULT 0);  
    8591CREATE INDEX userpquota_u_id_ix ON userpquota (userid); 
     
    107113                        pages TEXT, 
    108114                        billingcode TEXT, 
     115                        precomputedjobsize INT4, 
     116                        precomputedjobprice FLOAT, 
    109117                        jobdate TIMESTAMP DEFAULT now(), 
    110118                        CONSTRAINT checkUserPQuota FOREIGN KEY (userid, printerid) REFERENCES userpquota(userid, printerid)); 
     
    122130                         softlimit INT4, 
    123131                         hardlimit INT4, 
     132                         maxjobsize INT4, 
    124133                         datelimit TIMESTAMP); 
    125134CREATE INDEX grouppquota_g_id_ix ON grouppquota (groupid); 
     
    158167                           CONSTRAINT coeffconstraint UNIQUE (printerid, label)); 
    159168 
     169--  
     170-- Create the table for the billing codes 
     171-- 
     172CREATE TABLE billingcodes (id SERIAL PRIMARY KEY NOT NULL, 
     173                           label TEXT UNIQUE NOT NULL, 
     174                           balance FLOAT DEFAULT 0.0, 
     175                           pagecounter INT4 DEFAULT 0); 
     176 
    160177--                         
    161178-- Set some ACLs                         
    162179-- 
    163 REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients FROM public;                         
    164 REVOKE ALL ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq, payments_id_seq, coefficients_id_seq FROM public; 
     180REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients, billingcodes FROM public; 
     181REVOKE ALL ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq, payments_id_seq, coefficients_id_seq, billingcodes_id_seq FROM public; 
    165182 
    166 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients TO pykotaadmin; 
    167 GRANT SELECT, UPDATE ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq, payments_id_seq, coefficients_id_seq TO pykotaadmin; 
    168 GRANT SELECT ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients TO pykotauser; 
     183GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients, billingcodes TO pykotaadmin; 
     184GRANT SELECT, UPDATE ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq, payments_id_seq, coefficients_id_seq, billingcodes_id_seq TO pykotaadmin; 
     185GRANT SELECT ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients, billingcodes TO pykotauser; 
    169186