Changeset 2638
- Timestamp:
- 02/02/06 19:30:32 (18 years ago)
- Location:
- pykota/trunk
- Files:
-
- 7 modified
-
checkdeps.py (modified) (1 diff)
-
conf/pykota.conf.sample (modified) (2 diffs)
-
conf/pykotadmin.conf.sample (modified) (1 diff)
-
initscripts/mysql/pykota-mysql.sql (modified) (4 diffs)
-
pykota/storages/sql.py (modified) (1 diff)
-
README (modified) (2 diffs)
-
setup.py (modified) (2 diffs)
Legend:
- Unmodified
- Added
- Removed
-
pykota/trunk/checkdeps.py
r2629 r2638 70 70 modulestocheck = [ ("Python-PygreSQL", "pg", "PygreSQL is mandatory if you want to use PostgreSQL as the quota database backend.\nSee http://www.pygresql.org"), 71 71 ("Python-SQLite", "pysqlite2", "Python-SQLite is mandatory if you want to use SQLite as the quota database backend.\nSee http://www.pysqlite.org"), 72 ("MySQL-Python", "MySQLdb", "MySQL-Python is mandatory if you want to use MySQL as the quota database backend.\nSee http://sourceforge.net/projects/mysql-python"), 72 73 ("Python-egenix-mxDateTime", "mx.DateTime", "eGenix' mxDateTime is mandatory for PyKota to work.\nSee http://www.egenix.com"), 73 74 ("Python-LDAP", "ldap", "Python-LDAP is mandatory if you plan to use an LDAP\ndirectory as the quota database backend.\nSee http://python-ldap.sf.net"), -
pykota/trunk/conf/pykota.conf.sample
r2635 r2638 49 49 # Storage backend for quotas 50 50 # only PGStorage (PostgreSQL), LDAPStorage (OpenLDAP or else), 51 # and SQLiteStorage (SQLite 3) are supported.52 # 53 # MySQL and BerkeleyDB areplanned.51 # MySQLStorage (MySQL) and SQLiteStorage (SQLite 3) are supported. 52 # 53 # BerkeleyDB is planned. 54 54 55 55 #################################################################### … … 60 60 #storagename: /etc/pykota/pykota.db 61 61 62 #################################################################### 63 # PostgreSQL : comment this section out if you use another backend #64 #################################################################### 62 ############################################################################ 63 # PostgreSQL or MySQL: comment this section out if you use another backend # 64 ############################################################################ 65 65 storagebackend: pgstorage 66 # storagebackend: mysqlstorage 66 67 67 68 # Quota Storage Server hostname (and optional port) -
pykota/trunk/conf/pykotadmin.conf.sample
r2622 r2638 60 60 # For SQLite3, you can comment out all the lines in this file. 61 61 62 # PostgreSQL's Quota Storage administrator's name and password62 # PostgreSQL's or MySQL's Quota Storage administrator's name and password 63 63 # Please comment these lines out and see further below if you use LDAP. 64 64 storageadmin: pykotaadmin -
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 -
pykota/trunk/pykota/storages/sql.py
r2622 r2638 226 226 userpquota = StorageUserPQuota(self, user, printer) 227 227 if printer.Exists and user.Exists : 228 result = self.doSearch("SELECT * FROM userpquota WHERE userid=%s AND printerid=%s " % (self.doQuote(user.ident), self.doQuote(printer.ident)))228 result = self.doSearch("SELECT * FROM userpquota WHERE userid=%s AND printerid=%s;" % (self.doQuote(user.ident), self.doQuote(printer.ident))) 229 229 if result : 230 230 fields = result[0] -
pykota/trunk/README
r2622 r2638 62 62 An SQL script to create the database is included. 63 63 64 - Supports MySQL as the database backend. 65 An SQL script to create the database is included. 66 64 67 - The print jobs history can be stored in any database 65 68 of your choice (or in plain text files) if needed. … … 383 386 as the database backend (http://www.sqlite.org). You also need the 384 387 PySQLite module v2.0.5 or higher (http://www.pysqlite.org) 388 or 389 - MySQL 4.0 or higher and the MySQL-python bindings. 390 IMPORTANT: If you are using MySQL 4.1, then MySQL-python must be 391 version 1.2 or greater. Version 1.0 does not work correctly with 4.1. 385 392 386 393 -
pykota/trunk/setup.py
r2622 r2638 68 68 data_files.append((os.path.join(docdir, "postgresql"), ["initscripts/postgresql/README.postgresql"])) 69 69 data_files.append((os.path.join(docdir, "ldap"), ["initscripts/ldap/README.ldap"])) 70 data_files.append((os.path.join(docdir, "mysql"), ["initscripts/mysql/README.mysql"])) 70 71 71 72 directory = os.sep.join(["share", "man", "man1"]) … … 105 106 data_files.append((ldapdirectory, ["initscripts/ldap/README.ldap", "initscripts/ldap/pykota.schema", "initscripts/ldap/pykota-sample.ldif"])) 106 107 108 mysqldirectory = os.sep.join([directory, "mysql"]) 109 data_files.append((mysqldirectory, ["initscripts/mysql/README.mysql", "initscripts/mysql/pykota-mysql.sql"])) 110 107 111 os.umask(022) 108 112 setup(name = "pykota", version = __version__,
