- Timestamp:
- 09/27/08 22:02:37 (16 years ago)
- Files:
-
- 1 modified
Legend:
- Unmodified
- Added
- Removed
-
pykota/trunk/initscripts/postgresql/pykota-postgresql.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 password values to the passwords you would like. 37 -- 37 -- 38 38 CREATE USER pykotauser WITH UNENCRYPTED PASSWORD 'readonlypw' NOCREATEDB NOCREATEUSER; 39 39 CREATE USER pykotaadmin WITH UNENCRYPTED PASSWORD 'readwritepw' NOCREATEDB NOCREATEUSER; 40 40 41 -- 41 -- 42 42 -- Now connect to the new database 43 -- 43 -- 44 44 \connect pykota 45 45 … … 49 49 CREATE TABLE users(id SERIAL PRIMARY KEY NOT NULL, 50 50 username TEXT UNIQUE NOT NULL, 51 email TEXT, 51 email TEXT, 52 52 balance FLOAT DEFAULT 0.0, 53 53 lifetimepaid FLOAT DEFAULT 0.0, … … 55 55 description TEXT, 56 56 overcharge FLOAT NOT NULL DEFAULT 1.0); 57 57 58 58 -- 59 59 -- Create the groups table … … 63 63 description TEXT, 64 64 limitby TEXT DEFAULT 'quota'); 65 65 66 66 -- 67 67 -- Create the printers table … … 74 74 passthrough BOOLEAN DEFAULT FALSE, 75 75 maxjobsize INT4); 76 76 77 77 -- 78 78 -- Create the print quota table for users … … 87 87 datelimit TIMESTAMP, 88 88 maxjobsize INT4, 89 warncount INT4 DEFAULT 0); 89 warncount INT4 DEFAULT 0); 90 90 CREATE INDEX userpquota_u_id_ix ON userpquota (userid); 91 91 CREATE INDEX userpquota_p_id_ix ON userpquota (printerid); 92 92 CREATE UNIQUE INDEX userpquota_up_id_ix ON userpquota (userid, printerid); 93 93 94 94 -- 95 95 -- Create the job history table … … 120 120 CREATE INDEX jobhistory_pd_id_ix ON jobhistory (printerid, jobdate); 121 121 CREATE INDEX jobhistory_hostname_ix ON jobhistory (hostname); 122 122 123 123 -- 124 124 -- Create the print quota table for groups … … 134 134 CREATE INDEX grouppquota_p_id_ix ON grouppquota (printerid); 135 135 CREATE UNIQUE INDEX grouppquota_up_id_ix ON grouppquota (groupid, printerid); 136 137 -- 136 137 -- 138 138 -- Create the groups/members relationship 139 139 -- … … 141 141 userid INT4 REFERENCES users(id), 142 142 PRIMARY KEY (groupid, userid)); 143 144 -- 143 144 -- 145 145 -- Create the printer groups relationship 146 146 -- … … 150 150 -- 151 151 -- Create the table for payments 152 -- 152 -- 153 153 CREATE TABLE payments (id SERIAL PRIMARY KEY NOT NULL, 154 154 userid INT4 REFERENCES users(id), … … 158 158 CREATE INDEX payments_date_ix ON payments (date); 159 159 160 -- 160 -- 161 161 -- Create the table for coefficients wrt paper sizes and the like 162 162 -- 163 CREATE TABLE coefficients (id SERIAL PRIMARY KEY NOT NULL, 164 printerid INTEGER NOT NULL REFERENCES printers(id), 165 label TEXT NOT NULL, 166 coefficient FLOAT DEFAULT 1.0, 163 CREATE TABLE coefficients (id SERIAL PRIMARY KEY NOT NULL, 164 printerid INTEGER NOT NULL REFERENCES printers(id), 165 label TEXT NOT NULL, 166 coefficient FLOAT DEFAULT 1.0, 167 167 CONSTRAINT coeffconstraint UNIQUE (printerid, label)); 168 168 169 -- 169 -- 170 170 -- Create the table for the billing codes 171 171 -- … … 176 176 pagecounter INT4 DEFAULT 0); 177 177 178 -- 179 -- Set some ACLs 178 -- 179 -- Set some ACLs 180 180 -- 181 181 REVOKE ALL ON users, groups, printers, userpquota, grouppquota, groupsmembers, printergroupsmembers, jobhistory, payments, coefficients, billingcodes FROM public;
