Changeset 3413 for pykota/trunk/initscripts/mysql
- Timestamp:
- 09/27/08 22:02:37 (16 years ago)
- Location:
- pykota/trunk/initscripts/mysql
- Files:
-
- 2 modified
-
pykota-mysql.sql (modified) (14 diffs)
-
README.mysql (modified) (2 diffs)
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) -
pykota/trunk/initscripts/mysql/README.mysql
r3279 r3413 25 25 DataBase using MySQL as a backend. 26 26 27 - edit the pykota-mysql.sql file and modify the passwords at 27 - edit the pykota-mysql.sql file and modify the passwords at 28 28 the end of the 'GRANT USAGE' lines. 29 29 30 30 - As MySQL's administrative user, usually root, 31 31 do the following : 32 32 33 33 # mysql <pykota-mysql.sql 34 34 35 35 - If no error message appear, your MySQL PyKota storage 36 36 is now ready for use. 37 37 38 38 - In ~pykota/pykota.conf, ensure that the following lines 39 39 are present and unique inside the [global] section : 40 40 41 41 storagebackend : mysqlstorage 42 42 storageserver : localhost … … 44 44 storageuser : pykotauser 45 45 storageuserpw : readonlypw 46 46 47 47 NB : replace 'readonlypw' with the password you chose 48 48 for user 'pykotauser' when you modified pykota-mysql.sql 49 49 50 50 - In ~pykota/pykotadmin.conf ensure that the following lines 51 51 are present and unique inside the [global] section : 52 52 53 53 storageadmin : pykotaadmin 54 54 storageadminpw : readwritepw 55 55 56 56 NB : replace 'readwritepw' with the password you chose 57 57 for user 'pykotaadmin' when you modified pykota-mysql.sql 58 59 - IMPORTANT : if your database is not located on the print server, 60 replace 'localhost' in the lines above with your database server's 58 59 - IMPORTANT : if your database is not located on the print server, 60 replace 'localhost' in the lines above with your database server's 61 61 hostname or IP address. If your database server doesn't listen on 62 62 its standard port, add the port number at the end of the hostname 63 63 with a colon to separate the hostname from the port. 64 64 See pykota/conf/pykota.conf.sample for examples. 65 65 66 66 ============================================================
