Changeset 1520 for pykota/trunk/initscripts/postgresql/pykota-postgresql.sql

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.

Files:

• pykota/trunk/initscripts/postgresql/pykota-postgresql.sql (modified) (4 diffs)

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

@@ -20 +20 @@
 --
 -- $Log$
+-- Revision 1.10  2004/06/03 23:14:09  jalet
+-- 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.
+--
 -- Revision 1.9  2004/05/13 11:15:29  jalet
 -- Added hostname field in job history
@@ -121 +127 @@
                         userid INT4,
                         printerid INT4,
-                        hostname TEXT,
                         pagecounter INT4 DEFAULT 0,
+                        jobsizebytes INT8,
                         jobsize INT4,
                         jobprice FLOAT,
@@ -130 +136 @@
                         copies INT4,
                         options TEXT,
+                        hostname TEXT,
                         jobdate TIMESTAMP DEFAULT now(),
                         CONSTRAINT checkUserPQuota FOREIGN KEY (userid, printerid) REFERENCES userpquota(userid, printerid));
 CREATE INDEX jobhistory_p_id_ix ON jobhistory (printerid);
 CREATE INDEX jobhistory_pd_id_ix ON jobhistory (printerid, jobdate);
+CREATE INDEX jobhistory_hostname_ix ON jobhistory (hostname);
                         
 --
@@ -159 +167 @@
                            printerid INT4 REFERENCES printers(id),
                            PRIMARY KEY (groupid, printerid));
+--
+-- Create the table for payments
+-- 
+CREATE TABLE payments (id SERIAL PRIMARY KEY NOT NULL,
+                       userid INT4 REFERENCES users(id),
+                       amount FLOAT,
+                       date TIMESTAMP DEFAULT now());
+CREATE INDEX payments_date_ix ON payments (date);
 
 --                        
 -- Set some ACLs                        
 --
-REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory FROM public;                        
-REVOKE ALL ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq FROM public;
+REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments FROM public;                        
+REVOKE 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;
 
-GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory TO pykotaadmin;
-GRANT SELECT, UPDATE ON users_id_seq, groups_id_seq, printers_id_seq, userpquota_id_seq, grouppquota_id_seq, jobhistory_id_seq TO pykotaadmin;
-GRANT SELECT ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory TO pykotauser;
+GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments TO pykotaadmin;
+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 TO pykotaadmin;
+GRANT SELECT ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments TO pykotauser;