1 -- Require > 7.4, else use createlang command
2 CREATE PROCEDURAL LANGUAGE plpgsql;
4 -- --------------------------------------------------
6 -- --------------------------------------------------
10 -- PG 8.4 drops implicit cast from double to bigint
11 CREATE FUNCTION SEC_TO_TIME(double precision)
12 RETURNS interval AS $$
13 select date_trunc('second', $1 * interval '1 second');
18 CREATE UNIQUE INDEX location_idx ON Location (Location);
20 -- --------------------------------------------------
22 -- --------------------------------------------------
24 -- New tables for bresto (same as brestore)
26 CREATE TABLE brestore_knownjobid
28 JobId integer NOT NULL,
29 CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId)
32 CREATE TABLE brestore_pathhierarchy
34 PathId integer NOT NULL,
35 PPathId integer NOT NULL,
36 CONSTRAINT brestore_pathhierarchy_pkey PRIMARY KEY (PathId)
39 CREATE INDEX brestore_pathhierarchy_ppathid
40 ON brestore_pathhierarchy (PPathId);
42 CREATE TABLE brestore_pathvisibility
44 PathId integer NOT NULL,
45 JobId integer NOT NULL,
48 CONSTRAINT brestore_pathvisibility_pkey PRIMARY KEY (JobId, PathId)
51 CREATE INDEX brestore_pathvisibility_jobid
52 ON brestore_pathvisibility (JobId);
57 CREATE FUNCTION concat (text, text) RETURNS text AS '
61 IF $1 is not null THEN
69 CREATE AGGREGATE group_concat(
77 CREATE TABLE bweb_user
79 userid serial not null,
80 username text not null,
81 use_acl boolean default false,
82 enabled boolean default true,
83 comment text default '',
84 passwd text default '',
88 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username);
90 CREATE TABLE bweb_role
92 roleid serial not null,
93 rolename text not null,
94 comment text default '',
97 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename);
99 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
100 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
101 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
103 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
104 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
105 INSERT INTO bweb_role (rolename) VALUES ('r_storage_mgnt');
106 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
107 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
108 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
110 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
111 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
112 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
113 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
114 INSERT INTO bweb_role (rolename) VALUES ('r_view_group');
115 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
117 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
118 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
119 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
121 CREATE TABLE bweb_role_member
123 roleid integer not null,
124 userid integer not null,
125 primary key (roleid, userid)
128 CREATE TABLE bweb_client_group_acl
130 client_group_id integer not null,
131 userid integer not null,
132 primary key (client_group_id, userid)
135 -- --------------------------------------------------
137 -- --------------------------------------------------
140 -- Manage Client groups in bweb
141 -- Works with postgresql and mysql5
143 CREATE TABLE client_group
145 client_group_id serial not null,
146 client_group_name text not null,
147 comment text default '',
148 primary key (client_group_id)
151 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name);
153 CREATE TABLE client_group_member
155 client_group_id integer not null,
156 clientid integer not null,
157 primary key (client_group_id, clientid)
160 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
164 -- --------------------------------------------------
165 -- End of upgrade from 2.0
166 -- --------------------------------------------------
168 -- -- creer un nouveau group
170 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
172 -- -- affecter une machine a un group
174 -- INSERT INTO client_group_member (client_group_id, clientid)
175 -- (SELECT client_group_id,
176 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
178 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
181 -- -- modifier l'affectation d'une machine
183 -- DELETE FROM client_group_member
184 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
186 -- -- supprimer un groupe
188 -- DELETE FROM client_group_member
189 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
192 -- -- afficher tous les clients du group SIGMA
194 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
195 -- JOIN client_group using (client_group_id)
196 -- WHERE client_group_name = 'SIGMA';
198 -- -- afficher tous les groups
200 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
202 -- -- afficher tous les job du group SIGMA hier
204 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
205 -- FROM Job JOIN Client USING(ClientId)
206 -- JOIN client_group_member USING (ClientId)
207 -- JOIN client_group USING (client_group_id)
208 -- WHERE client_group_name = 'SIGMA'
209 -- AND Job.StartTime > '2007-03-20';
211 -- -- donne des stats
213 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
214 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
215 -- JobStatus AS jobstatus, client_group_name
216 -- FROM Job JOIN Client USING(ClientId)
217 -- JOIN client_group_member USING (ClientId)
218 -- JOIN client_group USING (client_group_id)
219 -- WHERE Job.StartTime > '2007-03-20'
220 -- GROUP BY JobStatus, client_group_name
226 CREATE FUNCTION SEC_TO_TIME(timestamp with time zone)
227 RETURNS timestamp with time zone AS $$
228 select date_trunc('second', $1);
231 CREATE FUNCTION SEC_TO_TIME(bigint)
232 RETURNS interval AS $$
233 select date_trunc('second', $1 * interval '1 second');
236 CREATE FUNCTION UNIX_TIMESTAMP(timestamp with time zone)
237 RETURNS double precision AS $$
238 select date_part('epoch', $1);
241 CREATE FUNCTION SEC_TO_INT(interval)
242 RETURNS double precision AS $$
243 select extract(epoch from $1);
246 CREATE OR REPLACE FUNCTION base64_decode_lstat(int4, varchar) RETURNS int8
247 IMMUTABLE STRICT AS $$
255 size := split_part($2, ' ', $1);
256 b64 := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
260 val := val + (strpos(b64, substr(size, i, 1))-1) * (64^(len-i));
264 $$ language 'plpgsql';