2 -- --------------------------------------------------
4 -- --------------------------------------------------
7 -- New tables for bresto (same as brestore)
9 CREATE TABLE brestore_knownjobid
11 JobId integer NOT NULL,
12 CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId)
15 CREATE TABLE brestore_pathhierarchy
17 PathId integer NOT NULL,
18 PPathId integer NOT NULL,
19 CONSTRAINT brestore_pathhierarchy_pkey PRIMARY KEY (PathId)
22 CREATE INDEX brestore_pathhierarchy_ppathid
23 ON brestore_pathhierarchy (PPathId);
25 CREATE TABLE brestore_pathvisibility
27 PathId integer NOT NULL,
28 JobId integer NOT NULL,
31 CONSTRAINT brestore_pathvisibility_pkey PRIMARY KEY (JobId, PathId)
34 CREATE INDEX brestore_pathvisibility_jobid
35 ON brestore_pathvisibility (JobId);
40 CREATE FUNCTION concat (text, text) RETURNS text AS '
44 IF $1 is not null THEN
52 CREATE AGGREGATE group_concat(
60 CREATE TABLE bweb_user
62 userid serial not null,
63 username text not null,
64 use_acl boolean default false,
65 enabled boolean default true,
66 comment text default '',
67 passwd text default '',
71 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username);
73 CREATE TABLE bweb_role
75 roleid serial not null,
76 rolename text not null,
77 -- comment text default '',
80 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename);
82 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
83 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
84 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
86 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
87 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
88 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
89 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
90 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
92 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
93 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
94 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
95 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
96 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
98 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
99 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
100 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
102 CREATE TABLE bweb_role_member
104 roleid integer not null,
105 userid integer not null,
106 primary key (roleid, userid)
109 CREATE TABLE bweb_client_group_acl
111 client_group_id integer not null,
112 userid integer not null,
113 primary key (client_group_id, userid)
116 -- --------------------------------------------------
118 -- --------------------------------------------------
121 -- Manage Client groups in bweb
122 -- Works with postgresql and mysql5
124 CREATE TABLE client_group
126 client_group_id serial not null,
127 client_group_name text not null,
128 primary key (client_group_id)
131 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name);
133 CREATE TABLE client_group_member
135 client_group_id integer not null,
136 clientid integer not null,
137 primary key (client_group_id, clientid)
140 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
144 -- --------------------------------------------------
145 -- End of upgrade from 2.0
146 -- --------------------------------------------------
148 -- -- creer un nouveau group
150 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
152 -- -- affecter une machine a un group
154 -- INSERT INTO client_group_member (client_group_id, clientid)
155 -- (SELECT client_group_id,
156 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
158 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
161 -- -- modifier l'affectation d'une machine
163 -- DELETE FROM client_group_member
164 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
166 -- -- supprimer un groupe
168 -- DELETE FROM client_group_member
169 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
172 -- -- afficher tous les clients du group SIGMA
174 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
175 -- JOIN client_group using (client_group_id)
176 -- WHERE client_group_name = 'SIGMA';
178 -- -- afficher tous les groups
180 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
182 -- -- afficher tous les job du group SIGMA hier
184 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
185 -- FROM Job JOIN Client USING(ClientId)
186 -- JOIN client_group_member USING (ClientId)
187 -- JOIN client_group USING (client_group_id)
188 -- WHERE client_group_name = 'SIGMA'
189 -- AND Job.StartTime > '2007-03-20';
191 -- -- donne des stats
193 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
194 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
195 -- JobStatus AS jobstatus, client_group_name
196 -- FROM Job JOIN Client USING(ClientId)
197 -- JOIN client_group_member USING (ClientId)
198 -- JOIN client_group USING (client_group_id)
199 -- WHERE Job.StartTime > '2007-03-20'
200 -- GROUP BY JobStatus, client_group_name
204 CREATE PROCEDURAL LANGUAGE plpgsql;
207 CREATE FUNCTION SEC_TO_TIME(timestamp with time zone)
208 RETURNS timestamp with time zone AS $$
209 select date_trunc('second', $1);
212 CREATE FUNCTION SEC_TO_TIME(bigint)
213 RETURNS interval AS $$
214 select date_trunc('second', $1 * interval '1 second');
217 CREATE FUNCTION UNIX_TIMESTAMP(timestamp with time zone)
218 RETURNS double precision AS $$
219 select date_part('epoch', $1);
222 CREATE FUNCTION SEC_TO_INT(interval)
223 RETURNS double precision AS $$
224 select extract(epoch from $1);
227 CREATE OR REPLACE FUNCTION base64_decode_lstat(int4, varchar) RETURNS int8 AS $$
234 size := split_part($2, ' ', $1);
235 b64 := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
237 FOR i IN 1..length(size) LOOP
238 val := val + (strpos(b64, substr(size, i, 1))-1) * (64^(length(size)-i));
242 $$ language 'plpgsql';