1 -- --------------------------------------------------
3 -- --------------------------------------------------
5 ALTER TABLE Status ADD COLUMN severity int;
6 UPDATE status SET severity = 15;
7 UPDATE status SET severity = 100 where jobstatus = 'f';
8 UPDATE status SET severity = 90 where jobstatus = 'A';
9 UPDATE status SET severity = 10 where jobstatus = 'T';
11 -- New tables for bresto (same as brestore)
13 CREATE TABLE brestore_knownjobid
15 JobId integer NOT NULL,
16 CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId)
19 CREATE TABLE brestore_pathhierarchy
21 PathId integer NOT NULL,
22 PPathId integer NOT NULL,
23 CONSTRAINT brestore_pathhierarchy_pkey PRIMARY KEY (PathId)
26 CREATE INDEX brestore_pathhierarchy_ppathid
27 ON brestore_pathhierarchy (PPathId);
29 CREATE TABLE brestore_pathvisibility
31 PathId integer NOT NULL,
32 JobId integer NOT NULL,
35 CONSTRAINT brestore_pathvisibility_pkey PRIMARY KEY (JobId, PathId)
38 CREATE INDEX brestore_pathvisibility_jobid
39 ON brestore_pathvisibility (JobId);
44 CREATE FUNCTION concat (text, text) RETURNS text AS '
48 IF $1 is not null THEN
56 CREATE AGGREGATE group_concat(
64 CREATE TABLE bweb_user
66 userid serial not null,
67 username text not null,
68 use_acl boolean default false,
69 enabled boolean default true,
70 comment text default '',
71 passwd text default '',
75 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username);
77 CREATE TABLE bweb_role
79 roleid serial not null,
80 rolename text not null,
81 -- comment text default '',
84 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename);
86 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
87 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
88 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
90 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
91 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
92 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
93 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
94 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
96 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
97 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
98 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
99 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
100 INSERT INTO bweb_role (rolename) VALUES ('r_view_group');
101 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
103 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
104 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
105 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
107 CREATE TABLE bweb_role_member
109 roleid integer not null,
110 userid integer not null,
111 primary key (roleid, userid)
114 CREATE TABLE bweb_client_group_acl
116 client_group_id integer not null,
117 userid integer not null,
118 primary key (client_group_id, userid)
121 -- --------------------------------------------------
123 -- --------------------------------------------------
126 -- Manage Client groups in bweb
127 -- Works with postgresql and mysql5
129 CREATE TABLE client_group
131 client_group_id serial not null,
132 client_group_name text not null,
133 primary key (client_group_id)
136 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name);
138 CREATE TABLE client_group_member
140 client_group_id integer not null,
141 clientid integer not null,
142 primary key (client_group_id, clientid)
145 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
149 -- --------------------------------------------------
150 -- End of upgrade from 2.0
151 -- --------------------------------------------------
153 -- -- creer un nouveau group
155 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
157 -- -- affecter une machine a un group
159 -- INSERT INTO client_group_member (client_group_id, clientid)
160 -- (SELECT client_group_id,
161 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
163 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
166 -- -- modifier l'affectation d'une machine
168 -- DELETE FROM client_group_member
169 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
171 -- -- supprimer un groupe
173 -- DELETE FROM client_group_member
174 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
177 -- -- afficher tous les clients du group SIGMA
179 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
180 -- JOIN client_group using (client_group_id)
181 -- WHERE client_group_name = 'SIGMA';
183 -- -- afficher tous les groups
185 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
187 -- -- afficher tous les job du group SIGMA hier
189 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
190 -- FROM Job JOIN Client USING(ClientId)
191 -- JOIN client_group_member USING (ClientId)
192 -- JOIN client_group USING (client_group_id)
193 -- WHERE client_group_name = 'SIGMA'
194 -- AND Job.StartTime > '2007-03-20';
196 -- -- donne des stats
198 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
199 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
200 -- JobStatus AS jobstatus, client_group_name
201 -- FROM Job JOIN Client USING(ClientId)
202 -- JOIN client_group_member USING (ClientId)
203 -- JOIN client_group USING (client_group_id)
204 -- WHERE Job.StartTime > '2007-03-20'
205 -- GROUP BY JobStatus, client_group_name
209 CREATE PROCEDURAL LANGUAGE plpgsql;
212 CREATE FUNCTION SEC_TO_TIME(timestamp with time zone)
213 RETURNS timestamp with time zone AS $$
214 select date_trunc('second', $1);
217 CREATE FUNCTION SEC_TO_TIME(bigint)
218 RETURNS interval AS $$
219 select date_trunc('second', $1 * interval '1 second');
222 CREATE FUNCTION UNIX_TIMESTAMP(timestamp with time zone)
223 RETURNS double precision AS $$
224 select date_part('epoch', $1);
227 CREATE FUNCTION SEC_TO_INT(interval)
228 RETURNS double precision AS $$
229 select extract(epoch from $1);
232 CREATE OR REPLACE FUNCTION base64_decode_lstat(int4, varchar) RETURNS int8 AS $$
239 size := split_part($2, ' ', $1);
240 b64 := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
242 FOR i IN 1..length(size) LOOP
243 val := val + (strpos(b64, substr(size, i, 1))-1) * (64^(length(size)-i));
247 $$ language 'plpgsql';