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';
10 UPDATE status SET severity = 20 where jobstatus = 'e';
11 UPDATE status SET severity = 25 where jobstatus = 'E';
13 -- New tables for bresto (same as brestore)
15 CREATE TABLE brestore_knownjobid
17 JobId integer NOT NULL,
18 CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId)
21 CREATE TABLE brestore_pathhierarchy
23 PathId integer NOT NULL,
24 PPathId integer NOT NULL,
25 CONSTRAINT brestore_pathhierarchy_pkey PRIMARY KEY (PathId)
28 CREATE INDEX brestore_pathhierarchy_ppathid
29 ON brestore_pathhierarchy (PPathId);
31 CREATE TABLE brestore_pathvisibility
33 PathId integer NOT NULL,
34 JobId integer NOT NULL,
37 CONSTRAINT brestore_pathvisibility_pkey PRIMARY KEY (JobId, PathId)
40 CREATE INDEX brestore_pathvisibility_jobid
41 ON brestore_pathvisibility (JobId);
46 CREATE FUNCTION concat (text, text) RETURNS text AS '
50 IF $1 is not null THEN
58 CREATE AGGREGATE group_concat(
66 CREATE TABLE bweb_user
68 userid serial not null,
69 username text not null,
70 use_acl boolean default false,
71 enabled boolean default true,
72 comment text default '',
73 passwd text default '',
77 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username);
79 CREATE TABLE bweb_role
81 roleid serial not null,
82 rolename text not null,
83 comment text default '',
86 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename);
88 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
89 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
90 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
92 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
93 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
94 INSERT INTO bweb_role (rolename) VALUES ('r_storage_mgnt');
95 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
96 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
97 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
99 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
100 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
101 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
102 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
103 INSERT INTO bweb_role (rolename) VALUES ('r_view_group');
104 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
106 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
107 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
108 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
110 CREATE TABLE bweb_role_member
112 roleid integer not null,
113 userid integer not null,
114 primary key (roleid, userid)
117 CREATE TABLE bweb_client_group_acl
119 client_group_id integer not null,
120 userid integer not null,
121 primary key (client_group_id, userid)
124 -- --------------------------------------------------
126 -- --------------------------------------------------
129 -- Manage Client groups in bweb
130 -- Works with postgresql and mysql5
132 CREATE TABLE client_group
134 client_group_id serial not null,
135 client_group_name text not null,
136 comment text default '',
137 primary key (client_group_id)
140 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name);
142 CREATE TABLE client_group_member
144 client_group_id integer not null,
145 clientid integer not null,
146 primary key (client_group_id, clientid)
149 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
153 -- --------------------------------------------------
154 -- End of upgrade from 2.0
155 -- --------------------------------------------------
157 -- -- creer un nouveau group
159 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
161 -- -- affecter une machine a un group
163 -- INSERT INTO client_group_member (client_group_id, clientid)
164 -- (SELECT client_group_id,
165 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
167 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
170 -- -- modifier l'affectation d'une machine
172 -- DELETE FROM client_group_member
173 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
175 -- -- supprimer un groupe
177 -- DELETE FROM client_group_member
178 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
181 -- -- afficher tous les clients du group SIGMA
183 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
184 -- JOIN client_group using (client_group_id)
185 -- WHERE client_group_name = 'SIGMA';
187 -- -- afficher tous les groups
189 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
191 -- -- afficher tous les job du group SIGMA hier
193 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
194 -- FROM Job JOIN Client USING(ClientId)
195 -- JOIN client_group_member USING (ClientId)
196 -- JOIN client_group USING (client_group_id)
197 -- WHERE client_group_name = 'SIGMA'
198 -- AND Job.StartTime > '2007-03-20';
200 -- -- donne des stats
202 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
203 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
204 -- JobStatus AS jobstatus, client_group_name
205 -- FROM Job JOIN Client USING(ClientId)
206 -- JOIN client_group_member USING (ClientId)
207 -- JOIN client_group USING (client_group_id)
208 -- WHERE Job.StartTime > '2007-03-20'
209 -- GROUP BY JobStatus, client_group_name
213 CREATE PROCEDURAL LANGUAGE plpgsql;
216 CREATE FUNCTION SEC_TO_TIME(timestamp with time zone)
217 RETURNS timestamp with time zone AS $$
218 select date_trunc('second', $1);
221 CREATE FUNCTION SEC_TO_TIME(bigint)
222 RETURNS interval AS $$
223 select date_trunc('second', $1 * interval '1 second');
226 CREATE FUNCTION UNIX_TIMESTAMP(timestamp with time zone)
227 RETURNS double precision AS $$
228 select date_part('epoch', $1);
231 CREATE FUNCTION SEC_TO_INT(interval)
232 RETURNS double precision AS $$
233 select extract(epoch from $1);
236 CREATE OR REPLACE FUNCTION base64_decode_lstat(int4, varchar) RETURNS int8 AS $$
243 size := split_part($2, ' ', $1);
244 b64 := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
246 FOR i IN 1..length(size) LOOP
247 val := val + (strpos(b64, substr(size, i, 1))-1) * (64^(length(size)-i));
251 $$ language 'plpgsql';