Changeset 2638 for pykota/trunk/initscripts
- Timestamp:
- 02/02/06 19:30:32 (18 years ago)
- Files:
-
- 1 modified
-
pykota/trunk/initscripts/mysql/pykota-mysql.sql (modified) (4 diffs)
Legend:
- Unmodified
- Added
- Removed
-
pykota/trunk/initscripts/mysql/pykota-mysql.sql
r2622 r2638 31 31 -- Create the print quota database 32 32 -- 33 CREATE DATABASE pykota; 33 34 34 35 -- 35 36 -- Create the print quota database users 36 37 -- 37 -- TODO : CREATE USER pykotaadmin;38 -- TODO : CREATE USER pykotauser;38 GRANT USAGE ON *.* TO 'pykotauser'@'localhost' IDENTIFIED BY 'readonly'; 39 GRANT USAGE ON *.* TO 'pykotaadmin'@'localhost' IDENTIFIED BY 'readwrite'; 39 40 40 41 -- 41 42 -- Now connect to the new database 42 43 -- 43 \u pykota 44 use pykota; 44 45 45 46 -- 46 47 -- Create the users table 47 48 -- 48 CREATE TABLE users (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT,49 CREATE TABLE users (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT, 49 50 username VARCHAR(255) UNIQUE NOT NULL, 50 51 email TEXT, 51 52 balance FLOAT DEFAULT 0.0, 52 53 lifetimepaid FLOAT DEFAULT 0.0, 53 limitby VARCHAR(30) DEFAULT 'quota'); 54 limitby VARCHAR(30) DEFAULT 'quota', 55 description TEXT, 56 overcharge FLOAT NOT NULL DEFAULT 1.0) TYPE=INNODB; 54 57 55 58 -- 56 59 -- Create the groups table 57 60 -- 58 CREATE TABLE groups (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT,61 CREATE TABLE groups (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT, 59 62 groupname VARCHAR(255) UNIQUE NOT NULL, 60 limitby VARCHAR(30) DEFAULT 'quota'); 63 description TEXT, 64 limitby VARCHAR(30) DEFAULT 'quota') TYPE=INNODB; 61 65 62 66 -- 63 67 -- Create the printers table 64 68 -- 65 CREATE TABLE printers (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT,69 CREATE TABLE printers (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT, 66 70 printername VARCHAR(255) UNIQUE NOT NULL, 67 71 description TEXT, 68 72 priceperpage FLOAT DEFAULT 0.0, 69 priceperjob FLOAT DEFAULT 0.0); 73 priceperjob FLOAT DEFAULT 0.0, 74 passthrough ENUM('t','f') DEFAULT 'f', 75 maxjobsize INT4) TYPE=INNODB; 70 76 71 77 -- 72 78 -- Create the print quota table for users 73 79 -- 74 CREATE TABLE userpquota (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT,75 userid INT4 REFERENCES users(id),76 printerid INT4 REFERENCES printers(id),80 CREATE TABLE userpquota (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT, 81 userid INT4, 82 printerid INT4, 77 83 lifepagecounter INT4 DEFAULT 0, 78 84 pagecounter INT4 DEFAULT 0, 79 85 softlimit INT4, 80 86 hardlimit INT4, 81 datelimit TIMESTAMP); 87 datelimit TIMESTAMP, 88 maxjobsize INT4, 89 warncount INT4 DEFAULT 0, 90 FOREIGN KEY (userid) REFERENCES users(id), 91 FOREIGN KEY (printerid) REFERENCES printers(id)) 92 TYPE=INNODB; 93 CREATE INDEX userpquota_u_id_ix ON userpquota (userid); 94 CREATE INDEX userpquota_p_id_ix ON userpquota (printerid); 82 95 CREATE UNIQUE INDEX userpquota_up_id_ix ON userpquota (userid, printerid); 83 96 … … 99 112 options TEXT, 100 113 hostname VARCHAR(255), 101 jobdate TIMESTAMP, 102 CONSTRAINT checkUserPQuota FOREIGN KEY (userid, printerid) 103 REFERENCES userpquota(userid, printerid)); 114 md5sum TEXT, 115 pages TEXT, 116 billingcode TEXT, 117 precomputedjobsize INT4, 118 precomputedjobprice FLOAT, 119 jobdate TIMESTAMP DEFAULT, 120 CONSTRAINT checkUserPQuota FOREIGN KEY (userid, printerid) REFERENCES userpquota(userid, printerid)) TYPE=INNODB; 121 CREATE INDEX jobhistory_u_id_ix ON jobhistory (userid); 104 122 CREATE INDEX jobhistory_p_id_ix ON jobhistory (printerid); 105 123 CREATE INDEX jobhistory_pd_id_ix ON jobhistory (printerid, jobdate); … … 110 128 -- 111 129 CREATE TABLE grouppquota(id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT, 112 groupid INT4 REFERENCES groups(id),113 printerid INT4 REFERENCES printers(id),130 groupid INT4, 131 printerid INT4, 114 132 softlimit INT4, 115 133 hardlimit INT4, 116 datelimit TIMESTAMP); 134 maxjobsize INT4, 135 datelimit TIMESTAMP, 136 FOREIGN KEY (groupid) REFERENCES groups(id), 137 FOREIGN KEY (printerid) REFERENCES printers(id)) 138 TYPE=INNODB; 139 CREATE INDEX grouppquota_g_id_ix ON grouppquota (groupid); 140 CREATE INDEX grouppquota_p_id_ix ON grouppquota (printerid); 117 141 CREATE UNIQUE INDEX grouppquota_up_id_ix ON grouppquota (groupid, printerid); 118 142 … … 120 144 -- Create the groups/members relationship 121 145 -- 122 CREATE TABLE groupsmembers(groupid INT4 REFERENCES groups(id), 123 userid INT4 REFERENCES users(id), 124 PRIMARY KEY (groupid, userid)); 146 CREATE TABLE groupsmembers(groupid INT4, 147 userid INT4, 148 FOREIGN KEY (groupid) REFERENCES groups(id), 149 FOREIGN KEY (userid) REFERENCES users(id), 150 PRIMARY KEY (groupid, userid)) TYPE=INNODB; 125 151 126 152 -- 127 153 -- Create the printer groups relationship 128 154 -- 129 CREATE TABLE printergroupsmembers(groupid INT4 REFERENCES printers(id), 130 printerid INT4 REFERENCES printers(id), 131 PRIMARY KEY (groupid, printerid)); 155 CREATE TABLE printergroupsmembers(groupid INT4, 156 printerid INT4, 157 FOREIGN KEY (groupid) REFERENCES groups(id), 158 FOREIGN KEY (printerid) REFERENCES printers(id), 159 PRIMARY KEY (groupid, printerid)) TYPE=INNODB; 132 160 -- 133 161 -- Create the table for payments 134 162 -- 135 163 CREATE TABLE payments (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT, 136 userid INT4 REFERENCES users(id),164 userid INT4, 137 165 amount FLOAT, 138 date TIMESTAMP DEFAULT now()); 166 description TEXT, 167 date TIMESTAMP DEFAULT now(), 168 FOREIGN KEY (userid) REFERENCES users(id)) TYPE=INNODB; 139 169 CREATE INDEX payments_date_ix ON payments (date); 140 170 171 -- 172 -- Create the table for coefficients wrt paper sizes and the like 173 -- 174 CREATE TABLE coefficients (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT, 175 printerid INT4 NOT NULL, 176 label VARCHAR(255) NOT NULL, 177 coefficient FLOAT DEFAULT 1.0, 178 FOREIGN KEY (printerid) REFERENCES printers(id), 179 CONSTRAINT coeffconstraint UNIQUE (printerid, label)); 180 181 -- 182 -- Create the table for the billing codes 183 -- 184 CREATE TABLE billingcodes (id INT4 PRIMARY KEY NOT NULL, 185 billingcode VARCHAR(255) UNIQUE NOT NULL, 186 description TEXT, 187 balance FLOAT DEFAULT 0.0, 188 pagecounter INT4 DEFAULT 0) TYPE=INNODB; 141 189 -- 142 190 -- Set some ACLs 143 191 -- 144 -- TODO : REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments FROM public; 145 -- TODO : 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;192 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON `pykota`.* TO 'pykotaadmin'@'localhost'; 193 GRANT SELECT ON `pykota`.* TO 'pykotauser'@'localhost'; 146 194 147 -- TODO : GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments TO pykotaadmin;148 -- TODO : 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;149 -- TODO : GRANT SELECT ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments TO pykotauser;150
