2 -- --------------------------------------------------
4 -- --------------------------------------------------
6 CREATE FUNCTION concat (text, text) RETURNS text AS '
10 IF $1 is not null THEN
18 CREATE AGGREGATE group_concat(
26 CREATE TABLE bweb_user
28 userid serial not null,
29 username text not null,
30 use_acl boolean default false,
31 enabled boolean default true,
32 comment text default '',
33 passwd text default '',
36 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username);
38 CREATE TABLE bweb_role
40 roleid serial not null,
41 rolename text not null,
42 -- comment text default '',
45 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename);
47 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
48 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
49 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
51 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
52 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
53 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
54 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
55 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
57 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
58 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
59 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
60 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
61 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
63 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
64 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
65 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
67 CREATE TABLE bweb_role_member
69 roleid integer not null,
70 userid integer not null,
71 primary key (roleid, userid)
74 CREATE TABLE bweb_client_group_acl
76 client_group_id integer not null,
77 userid integer not null,
78 primary key (client_group_id, userid)
81 -- --------------------------------------------------
83 -- --------------------------------------------------
86 -- Manage Client groups in bweb
87 -- Works with postgresql and mysql5
89 CREATE TABLE client_group
91 client_group_id serial not null,
92 client_group_name text not null,
93 primary key (client_group_id)
96 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name);
98 CREATE TABLE client_group_member
100 client_group_id integer not null,
101 clientid integer not null,
102 primary key (client_group_id, clientid)
105 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
109 -- --------------------------------------------------
110 -- End of upgrade from 2.0
111 -- --------------------------------------------------
113 -- -- creer un nouveau group
115 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
117 -- -- affecter une machine a un group
119 -- INSERT INTO client_group_member (client_group_id, clientid)
120 -- (SELECT client_group_id,
121 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
123 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
126 -- -- modifier l'affectation d'une machine
128 -- DELETE FROM client_group_member
129 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
131 -- -- supprimer un groupe
133 -- DELETE FROM client_group_member
134 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
137 -- -- afficher tous les clients du group SIGMA
139 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
140 -- JOIN client_group using (client_group_id)
141 -- WHERE client_group_name = 'SIGMA';
143 -- -- afficher tous les groups
145 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
147 -- -- afficher tous les job du group SIGMA hier
149 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
150 -- FROM Job JOIN Client USING(ClientId)
151 -- JOIN client_group_member USING (ClientId)
152 -- JOIN client_group USING (client_group_id)
153 -- WHERE client_group_name = 'SIGMA'
154 -- AND Job.StartTime > '2007-03-20';
156 -- -- donne des stats
158 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
159 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
160 -- JobStatus AS jobstatus, client_group_name
161 -- FROM Job JOIN Client USING(ClientId)
162 -- JOIN client_group_member USING (ClientId)
163 -- JOIN client_group USING (client_group_id)
164 -- WHERE Job.StartTime > '2007-03-20'
165 -- GROUP BY JobStatus, client_group_name
169 CREATE PROCEDURAL LANGUAGE plpgsql;
172 CREATE FUNCTION SEC_TO_TIME(timestamp with time zone)
173 RETURNS timestamp with time zone AS $$
174 select date_trunc('second', $1);
177 CREATE FUNCTION SEC_TO_TIME(bigint)
178 RETURNS interval AS $$
179 select date_trunc('second', $1 * interval '1 second');
182 CREATE FUNCTION UNIX_TIMESTAMP(timestamp with time zone)
183 RETURNS double precision AS $$
184 select date_part('epoch', $1);
187 CREATE FUNCTION SEC_TO_INT(interval)
188 RETURNS double precision AS $$
189 select extract(epoch from $1);
192 CREATE OR REPLACE FUNCTION base64_decode_lstat(int4, varchar) RETURNS int8 AS $$
199 size := split_part($2, ' ', $1);
200 b64 := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
202 FOR i IN 1..length(size) LOOP
203 val := val + (strpos(b64, substr(size, i, 1))-1) * (64^(length(size)-i));
207 $$ language 'plpgsql';