Changeset 1520 for pykota/trunk/initscripts/postgresql
- Timestamp:
- 06/04/04 01:14:11 (20 years ago)
- Location:
- pykota/trunk/initscripts/postgresql
- Files:
-
- 1 removed
- 2 modified
-
dbschema.png (deleted)
-
pykota-postgresql.sql (modified) (4 diffs)
-
upgrade-to-1.19.sql (modified) (2 diffs)
Legend:
- Unmodified
- Added
- Removed
-
pykota/trunk/initscripts/postgresql/pykota-postgresql.sql
r1473 r1520 20 20 -- 21 21 -- $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 -- 22 28 -- Revision 1.9 2004/05/13 11:15:29 jalet 23 29 -- Added hostname field in job history … … 121 127 userid INT4, 122 128 printerid INT4, 123 hostname TEXT,124 129 pagecounter INT4 DEFAULT 0, 130 jobsizebytes INT8, 125 131 jobsize INT4, 126 132 jobprice FLOAT, … … 130 136 copies INT4, 131 137 options TEXT, 138 hostname TEXT, 132 139 jobdate TIMESTAMP DEFAULT now(), 133 140 CONSTRAINT checkUserPQuota FOREIGN KEY (userid, printerid) REFERENCES userpquota(userid, printerid)); 134 141 CREATE INDEX jobhistory_p_id_ix ON jobhistory (printerid); 135 142 CREATE INDEX jobhistory_pd_id_ix ON jobhistory (printerid, jobdate); 143 CREATE INDEX jobhistory_hostname_ix ON jobhistory (hostname); 136 144 137 145 -- … … 159 167 printerid INT4 REFERENCES printers(id), 160 168 PRIMARY KEY (groupid, printerid)); 169 -- 170 -- Create the table for payments 171 -- 172 CREATE TABLE payments (id SERIAL PRIMARY KEY NOT NULL, 173 userid INT4 REFERENCES users(id), 174 amount FLOAT, 175 date TIMESTAMP DEFAULT now()); 176 CREATE INDEX payments_date_ix ON payments (date); 161 177 162 178 -- 163 179 -- Set some ACLs 164 180 -- 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;181 REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments FROM public; 182 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; 167 183 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;184 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments TO pykotaadmin; 185 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; 186 GRANT SELECT ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments TO pykotauser; 171 187 -
pykota/trunk/initscripts/postgresql/upgrade-to-1.19.sql
r1473 r1520 20 20 -- 21 21 -- $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 -- 22 28 -- Revision 1.1 2004/05/13 11:15:29 jalet 23 29 -- Added hostname field in job history … … 39 45 -- Modify the old database schema 40 46 -- 47 ALTER TABLE jobhistory ADD COLUMN jobsizebytes INT8; 41 48 ALTER TABLE jobhistory ADD COLUMN hostname TEXT; 49 CREATE INDEX jobhistory_hostname_ix ON jobhistory (hostname); 42 50 51 CREATE TABLE payments (id SERIAL PRIMARY KEY NOT NULL, 52 userid INT4 REFERENCES users(id), 53 amount FLOAT, 54 date TIMESTAMP DEFAULT now()); 55 CREATE INDEX payments_date_ix ON payments (date); 56 57 REVOKE ALL ON payments FROM public; 58 REVOKE ALL ON payments_id_seq FROM public; 59 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON payments TO pykotaadmin; 60 GRANT SELECT, UPDATE ON payments_id_seq TO pykotaadmin; 61 GRANT SELECT ON payments TO pykotauser;
