Show
Ignore:
Timestamp:
06/04/04 01:14:11 (20 years ago)
Author:
jalet
Message:

Now stores the job's size in bytes in the database.
Preliminary work on payments storage : database schemas are OK now,
but no code to store payments yet.
Removed schema picture, not relevant anymore.

Location:
pykota/trunk/initscripts/postgresql
Files:
1 removed
2 modified

Legend:

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

    r1473 r1520  
    2020-- 
    2121-- $Log$ 
     22-- Revision 1.10  2004/06/03 23:14:09  jalet 
     23-- Now stores the job's size in bytes in the database. 
     24-- Preliminary work on payments storage : database schemas are OK now, 
     25-- but no code to store payments yet. 
     26-- Removed schema picture, not relevant anymore. 
     27-- 
    2228-- Revision 1.9  2004/05/13 11:15:29  jalet 
    2329-- Added hostname field in job history 
     
    121127                        userid INT4, 
    122128                        printerid INT4, 
    123                         hostname TEXT, 
    124129                        pagecounter INT4 DEFAULT 0, 
     130                        jobsizebytes INT8, 
    125131                        jobsize INT4, 
    126132                        jobprice FLOAT, 
     
    130136                        copies INT4, 
    131137                        options TEXT, 
     138                        hostname TEXT, 
    132139                        jobdate TIMESTAMP DEFAULT now(), 
    133140                        CONSTRAINT checkUserPQuota FOREIGN KEY (userid, printerid) REFERENCES userpquota(userid, printerid)); 
    134141CREATE INDEX jobhistory_p_id_ix ON jobhistory (printerid); 
    135142CREATE INDEX jobhistory_pd_id_ix ON jobhistory (printerid, jobdate); 
     143CREATE INDEX jobhistory_hostname_ix ON jobhistory (hostname); 
    136144                         
    137145-- 
     
    159167                           printerid INT4 REFERENCES printers(id), 
    160168                           PRIMARY KEY (groupid, printerid)); 
     169-- 
     170-- Create the table for payments 
     171--  
     172CREATE TABLE payments (id SERIAL PRIMARY KEY NOT NULL, 
     173                       userid INT4 REFERENCES users(id), 
     174                       amount FLOAT, 
     175                       date TIMESTAMP DEFAULT now()); 
     176CREATE INDEX payments_date_ix ON payments (date); 
    161177 
    162178--                         
    163179-- Set some ACLs                         
    164180-- 
    165 REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory FROM public;                         
    166 REVOKE ALL ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq FROM public; 
     181REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments FROM public;                         
     182REVOKE ALL ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq, payments_id_seq FROM public; 
    167183 
    168 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory TO pykotaadmin; 
    169 GRANT SELECT, UPDATE ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq TO pykotaadmin; 
    170 GRANT SELECT ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory TO pykotauser; 
     184GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments TO pykotaadmin; 
     185GRANT SELECT, UPDATE ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq, payments_id_seq TO pykotaadmin; 
     186GRANT SELECT ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments TO pykotauser; 
    171187 
  • pykota/trunk/initscripts/postgresql/upgrade-to-1.19.sql

    r1473 r1520  
    2020-- 
    2121-- $Log$ 
     22-- Revision 1.2  2004/06/03 23:14:10  jalet 
     23-- Now stores the job's size in bytes in the database. 
     24-- Preliminary work on payments storage : database schemas are OK now, 
     25-- but no code to store payments yet. 
     26-- Removed schema picture, not relevant anymore. 
     27-- 
    2228-- Revision 1.1  2004/05/13 11:15:29  jalet 
    2329-- Added hostname field in job history 
     
    3945-- Modify the old database schema 
    4046-- 
     47ALTER TABLE jobhistory ADD COLUMN jobsizebytes INT8; 
    4148ALTER TABLE jobhistory ADD COLUMN hostname TEXT; 
     49CREATE INDEX jobhistory_hostname_ix ON jobhistory (hostname); 
    4250 
     51CREATE TABLE payments (id SERIAL PRIMARY KEY NOT NULL, 
     52                       userid INT4 REFERENCES users(id), 
     53                       amount FLOAT, 
     54                       date TIMESTAMP DEFAULT now()); 
     55CREATE INDEX payments_date_ix ON payments (date); 
     56 
     57REVOKE ALL ON payments FROM public;                         
     58REVOKE ALL ON payments_id_seq FROM public; 
     59GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON payments TO pykotaadmin; 
     60GRANT SELECT, UPDATE ON payments_id_seq TO pykotaadmin; 
     61GRANT SELECT ON payments TO pykotauser;