1 -- Require > 7.4, else use createlang command
2 CREATE PROCEDURAL LANGUAGE plpgsql;
4 -- --------------------------------------------------
6 -- --------------------------------------------------
8 ALTER TABLE Status ADD COLUMN severity int;
9 UPDATE status SET severity = 15;
10 UPDATE status SET severity = 100 where jobstatus = 'f';
11 UPDATE status SET severity = 90 where jobstatus = 'A';
12 UPDATE status SET severity = 10 where jobstatus = 'T';
13 UPDATE status SET severity = 20 where jobstatus = 'e';
14 UPDATE status SET severity = 25 where jobstatus = 'E';
16 -- New tables for bresto (same as brestore)
18 CREATE TABLE brestore_knownjobid
20 JobId integer NOT NULL,
21 CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId)
24 CREATE TABLE brestore_pathhierarchy
26 PathId integer NOT NULL,
27 PPathId integer NOT NULL,
28 CONSTRAINT brestore_pathhierarchy_pkey PRIMARY KEY (PathId)
31 CREATE INDEX brestore_pathhierarchy_ppathid
32 ON brestore_pathhierarchy (PPathId);
34 CREATE TABLE brestore_pathvisibility
36 PathId integer NOT NULL,
37 JobId integer NOT NULL,
40 CONSTRAINT brestore_pathvisibility_pkey PRIMARY KEY (JobId, PathId)
43 CREATE INDEX brestore_pathvisibility_jobid
44 ON brestore_pathvisibility (JobId);
49 CREATE FUNCTION concat (text, text) RETURNS text AS '
53 IF $1 is not null THEN
61 CREATE AGGREGATE group_concat(
69 CREATE TABLE bweb_user
71 userid serial not null,
72 username text not null,
73 use_acl boolean default false,
74 enabled boolean default true,
75 comment text default '',
76 passwd text default '',
80 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username);
82 CREATE TABLE bweb_role
84 roleid serial not null,
85 rolename text not null,
86 comment text default '',
89 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename);
91 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
92 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
93 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
95 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
96 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
97 INSERT INTO bweb_role (rolename) VALUES ('r_storage_mgnt');
98 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
99 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
100 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
102 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
103 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
104 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
105 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
106 INSERT INTO bweb_role (rolename) VALUES ('r_view_group');
107 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
109 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
110 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
111 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
113 CREATE TABLE bweb_role_member
115 roleid integer not null,
116 userid integer not null,
117 primary key (roleid, userid)
120 CREATE TABLE bweb_client_group_acl
122 client_group_id integer not null,
123 userid integer not null,
124 primary key (client_group_id, userid)
127 -- --------------------------------------------------
129 -- --------------------------------------------------
132 -- Manage Client groups in bweb
133 -- Works with postgresql and mysql5
135 CREATE TABLE client_group
137 client_group_id serial not null,
138 client_group_name text not null,
139 comment text default '',
140 primary key (client_group_id)
143 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name);
145 CREATE TABLE client_group_member
147 client_group_id integer not null,
148 clientid integer not null,
149 primary key (client_group_id, clientid)
152 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
156 -- --------------------------------------------------
157 -- End of upgrade from 2.0
158 -- --------------------------------------------------
160 -- -- creer un nouveau group
162 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
164 -- -- affecter une machine a un group
166 -- INSERT INTO client_group_member (client_group_id, clientid)
167 -- (SELECT client_group_id,
168 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
170 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
173 -- -- modifier l'affectation d'une machine
175 -- DELETE FROM client_group_member
176 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
178 -- -- supprimer un groupe
180 -- DELETE FROM client_group_member
181 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
184 -- -- afficher tous les clients du group SIGMA
186 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
187 -- JOIN client_group using (client_group_id)
188 -- WHERE client_group_name = 'SIGMA';
190 -- -- afficher tous les groups
192 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
194 -- -- afficher tous les job du group SIGMA hier
196 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
197 -- FROM Job JOIN Client USING(ClientId)
198 -- JOIN client_group_member USING (ClientId)
199 -- JOIN client_group USING (client_group_id)
200 -- WHERE client_group_name = 'SIGMA'
201 -- AND Job.StartTime > '2007-03-20';
203 -- -- donne des stats
205 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
206 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
207 -- JobStatus AS jobstatus, client_group_name
208 -- FROM Job JOIN Client USING(ClientId)
209 -- JOIN client_group_member USING (ClientId)
210 -- JOIN client_group USING (client_group_id)
211 -- WHERE Job.StartTime > '2007-03-20'
212 -- GROUP BY JobStatus, client_group_name
218 CREATE FUNCTION SEC_TO_TIME(timestamp with time zone)
219 RETURNS timestamp with time zone AS $$
220 select date_trunc('second', $1);
223 CREATE FUNCTION SEC_TO_TIME(bigint)
224 RETURNS interval AS $$
225 select date_trunc('second', $1 * interval '1 second');
228 CREATE FUNCTION UNIX_TIMESTAMP(timestamp with time zone)
229 RETURNS double precision AS $$
230 select date_part('epoch', $1);
233 CREATE FUNCTION SEC_TO_INT(interval)
234 RETURNS double precision AS $$
235 select extract(epoch from $1);
238 CREATE OR REPLACE FUNCTION base64_decode_lstat(int4, varchar) RETURNS int8
239 IMMUTABLE STRICT AS $$
247 size := split_part($2, ' ', $1);
248 b64 := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
252 val := val + (strpos(b64, substr(size, i, 1))-1) * (64^(len-i));
256 $$ language 'plpgsql';