Changeset 3413 for pykota/trunk/initscripts/mysql/pykota-mysql.sql
- Timestamp:
- 09/27/08 22:02:37 (16 years ago)
- Files:
-
- 1 modified
Legend:
- Unmodified
- Added
- Removed
-
pykota/trunk/initscripts/mysql/pykota-mysql.sql
r3279 r3413 7 7 -- the Free Software Foundation, either version 3 of the License, or 8 8 -- (at your option) any later version. 9 -- 9 -- 10 10 -- This program is distributed in the hope that it will be useful, 11 11 -- but WITHOUT ANY WARRANTY; without even the implied warranty of 12 12 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13 13 -- GNU General Public License for more details. 14 -- 14 -- 15 15 -- You should have received a copy of the GNU General Public License 16 16 -- along with this program. If not, see <http://www.gnu.org/licenses/>. … … 35 35 -- Create the print quota database users 36 36 -- NOTE: Change the "IDENTIFIED BY" strings to the passwords you would like. 37 -- 37 -- 38 38 GRANT USAGE ON *.* TO 'pykotauser'@'localhost' IDENTIFIED BY 'readonlypw'; 39 39 GRANT USAGE ON *.* TO 'pykotaadmin'@'localhost' IDENTIFIED BY 'readwritepw'; 40 40 41 -- 41 -- 42 42 -- If necessary activate the lines below (and keep the preceding ones 43 43 -- activated at the same time) … … 46 46 -- GRANT USAGE ON *.* TO 'pykotaadmin'@'%' IDENTIFIED BY 'readwritepw'; 47 47 48 -- 48 -- 49 49 -- Now connect to the new database 50 -- 50 -- 51 51 USE pykota; 52 52 … … 56 56 CREATE TABLE users (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT, 57 57 username VARCHAR(255) UNIQUE NOT NULL, 58 email TEXT, 58 email TEXT, 59 59 balance FLOAT DEFAULT 0.0, 60 60 lifetimepaid FLOAT DEFAULT 0.0, … … 62 62 description TEXT, 63 63 overcharge FLOAT NOT NULL DEFAULT 1.0) TYPE=INNODB; 64 64 65 65 -- 66 66 -- Create the groups table … … 70 70 description TEXT, 71 71 limitby VARCHAR(30) DEFAULT 'quota') TYPE=INNODB; 72 72 73 73 -- 74 74 -- Create the printers table … … 81 81 passthrough ENUM('t','f') DEFAULT 'f', 82 82 maxjobsize INT4) TYPE=INNODB; 83 83 84 84 -- 85 85 -- Create the print quota table for users 86 86 -- 87 87 CREATE TABLE userpquota (id INT8 PRIMARY KEY NOT NULL AUTO_INCREMENT, 88 userid INT4, 89 printerid INT4, 88 userid INT4, 89 printerid INT4, 90 90 lifepagecounter INT4 DEFAULT 0, 91 91 pagecounter INT4 DEFAULT 0, … … 94 94 datelimit DATETIME, 95 95 maxjobsize INT4, 96 warncount INT4 DEFAULT 0, 96 warncount INT4 DEFAULT 0, 97 97 INDEX (userid), 98 98 FOREIGN KEY (userid) REFERENCES users(id), 99 99 INDEX (printerid), 100 FOREIGN KEY (printerid) REFERENCES printers(id)) 100 FOREIGN KEY (printerid) REFERENCES printers(id)) 101 101 TYPE=INNODB; 102 102 CREATE UNIQUE INDEX userpquota_up_id_ix ON userpquota (userid, printerid); 103 103 104 104 -- 105 105 -- Create the job history table … … 132 132 CREATE INDEX jobhistory_pd_id_ix ON jobhistory (printerid, jobdate); 133 133 CREATE INDEX jobhistory_hostname_ix ON jobhistory (hostname); 134 134 135 135 -- 136 136 -- Create the print quota table for groups 137 137 -- 138 138 CREATE TABLE grouppquota(id INT8 PRIMARY KEY NOT NULL AUTO_INCREMENT, 139 groupid INT4, 139 groupid INT4, 140 140 printerid INT4, 141 141 softlimit INT4, … … 149 149 TYPE=INNODB; 150 150 CREATE UNIQUE INDEX grouppquota_up_id_ix ON grouppquota (groupid, printerid); 151 152 -- 151 152 -- 153 153 -- Create the groups/members relationship 154 154 -- … … 160 160 FOREIGN KEY (userid) REFERENCES users(id), 161 161 PRIMARY KEY (groupid, userid)) TYPE=INNODB; 162 163 -- 162 163 -- 164 164 -- Create the printer groups relationship 165 165 -- … … 173 173 -- 174 174 -- Create the table for payments 175 -- 175 -- 176 176 CREATE TABLE payments (id INT4 PRIMARY KEY NOT NULL AUTO_INCREMENT, 177 177 userid INT4, … … 195 195 ) TYPE=INNODB; 196 196 197 -- 197 -- 198 198 -- Create the table for the billing codes 199 199 -- … … 203 203 balance FLOAT DEFAULT 0.0, 204 204 pagecounter INT4 DEFAULT 0) TYPE=INNODB; 205 -- 206 -- Set some ACLs 205 -- 206 -- Set some ACLs 207 207 -- 208 208 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON `pykota`.* TO 'pykotaadmin'@'localhost'; 209 209 GRANT SELECT ON `pykota`.* TO 'pykotauser'@'localhost'; 210 210 211 -- 211 -- 212 212 -- If necessary activate the lines below (and keep the preceding ones 213 213 -- activated at the same time)