1 -- Require > 7.4, else use createlang command
2 CREATE PROCEDURAL LANGUAGE plpgsql;
4 -- --------------------------------------------------
6 -- --------------------------------------------------
8 -- New tables for bresto (same as brestore)
10 CREATE TABLE brestore_knownjobid
12 JobId integer NOT NULL,
13 CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId)
16 CREATE TABLE brestore_pathhierarchy
18 PathId integer NOT NULL,
19 PPathId integer NOT NULL,
20 CONSTRAINT brestore_pathhierarchy_pkey PRIMARY KEY (PathId)
23 CREATE INDEX brestore_pathhierarchy_ppathid
24 ON brestore_pathhierarchy (PPathId);
26 CREATE TABLE brestore_pathvisibility
28 PathId integer NOT NULL,
29 JobId integer NOT NULL,
32 CONSTRAINT brestore_pathvisibility_pkey PRIMARY KEY (JobId, PathId)
35 CREATE INDEX brestore_pathvisibility_jobid
36 ON brestore_pathvisibility (JobId);
41 CREATE FUNCTION concat (text, text) RETURNS text AS '
45 IF $1 is not null THEN
53 CREATE AGGREGATE group_concat(
61 CREATE TABLE bweb_user
63 userid serial not null,
64 username text not null,
65 use_acl boolean default false,
66 enabled boolean default true,
67 comment text default '',
68 passwd text default '',
72 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username);
74 CREATE TABLE bweb_role
76 roleid serial not null,
77 rolename text not null,
78 comment text default '',
81 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename);
83 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
84 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
85 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
87 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
88 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
89 INSERT INTO bweb_role (rolename) VALUES ('r_storage_mgnt');
90 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
91 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
92 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
94 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
95 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
96 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
97 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
98 INSERT INTO bweb_role (rolename) VALUES ('r_view_group');
99 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
101 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
102 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
103 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
105 CREATE TABLE bweb_role_member
107 roleid integer not null,
108 userid integer not null,
109 primary key (roleid, userid)
112 CREATE TABLE bweb_client_group_acl
114 client_group_id integer not null,
115 userid integer not null,
116 primary key (client_group_id, userid)
119 -- --------------------------------------------------
121 -- --------------------------------------------------
124 -- Manage Client groups in bweb
125 -- Works with postgresql and mysql5
127 CREATE TABLE client_group
129 client_group_id serial not null,
130 client_group_name text not null,
131 comment text default '',
132 primary key (client_group_id)
135 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name);
137 CREATE TABLE client_group_member
139 client_group_id integer not null,
140 clientid integer not null,
141 primary key (client_group_id, clientid)
144 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
148 -- --------------------------------------------------
149 -- End of upgrade from 2.0
150 -- --------------------------------------------------
152 -- -- creer un nouveau group
154 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
156 -- -- affecter une machine a un group
158 -- INSERT INTO client_group_member (client_group_id, clientid)
159 -- (SELECT client_group_id,
160 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
162 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
165 -- -- modifier l'affectation d'une machine
167 -- DELETE FROM client_group_member
168 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
170 -- -- supprimer un groupe
172 -- DELETE FROM client_group_member
173 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
176 -- -- afficher tous les clients du group SIGMA
178 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
179 -- JOIN client_group using (client_group_id)
180 -- WHERE client_group_name = 'SIGMA';
182 -- -- afficher tous les groups
184 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
186 -- -- afficher tous les job du group SIGMA hier
188 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
189 -- FROM Job JOIN Client USING(ClientId)
190 -- JOIN client_group_member USING (ClientId)
191 -- JOIN client_group USING (client_group_id)
192 -- WHERE client_group_name = 'SIGMA'
193 -- AND Job.StartTime > '2007-03-20';
195 -- -- donne des stats
197 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
198 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
199 -- JobStatus AS jobstatus, client_group_name
200 -- FROM Job JOIN Client USING(ClientId)
201 -- JOIN client_group_member USING (ClientId)
202 -- JOIN client_group USING (client_group_id)
203 -- WHERE Job.StartTime > '2007-03-20'
204 -- GROUP BY JobStatus, client_group_name
210 CREATE FUNCTION SEC_TO_TIME(timestamp with time zone)
211 RETURNS timestamp with time zone AS $$
212 select date_trunc('second', $1);
215 CREATE FUNCTION SEC_TO_TIME(bigint)
216 RETURNS interval AS $$
217 select date_trunc('second', $1 * interval '1 second');
220 CREATE FUNCTION UNIX_TIMESTAMP(timestamp with time zone)
221 RETURNS double precision AS $$
222 select date_part('epoch', $1);
225 CREATE FUNCTION SEC_TO_INT(interval)
226 RETURNS double precision AS $$
227 select extract(epoch from $1);
230 CREATE OR REPLACE FUNCTION base64_decode_lstat(int4, varchar) RETURNS int8
231 IMMUTABLE STRICT AS $$
239 size := split_part($2, ' ', $1);
240 b64 := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
244 val := val + (strpos(b64, substr(size, i, 1))-1) * (64^(len-i));
248 $$ language 'plpgsql';