1 -- --------------------------------------------------
3 -- --------------------------------------------------
7 DROP FUNCTION IF EXISTS base64_decode_lstat |
8 CREATE FUNCTION base64_decode_lstat (field INTEGER, input BLOB)
14 DECLARE first_char BINARY(1);
15 DECLARE accum_value BIGINT UNSIGNED DEFAULT 0;
17 -- The number of fields can vary, so we need 2 calls to SUBSTRING_INDEX
18 SET input = SUBSTRING_INDEX(SUBSTRING_INDEX(input, ' ', field),
21 WHILE LENGTH(input) > 0 DO
22 SET first_char = SUBSTRING(input, 1, 1);
23 SET input = SUBSTRING(input, 2);
25 SET accum_value = (accum_value << 6) +
26 INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
35 -- --------------------------------------------------
37 -- --------------------------------------------------
39 ALTER TABLE Status ADD COLUMN severity int;
40 UPDATE Status SET severity = 15;
41 UPDATE Status SET severity = 100 where JobStatus = 'f';
42 UPDATE Status SET severity = 90 where JobStatus = 'A';
43 UPDATE Status SET severity = 10 where JobStatus = 'T';
44 UPDATE Status SET severity = 20 where jobStatus = 'e';
45 UPDATE Status SET severity = 25 where jobStatus = 'E';
48 -- New tables for bresto (same as brestore)
50 CREATE TABLE brestore_knownjobid
52 JobId integer NOT NULL,
53 CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId)
56 CREATE TABLE brestore_pathhierarchy
58 PathId integer NOT NULL,
59 PPathId integer NOT NULL,
60 CONSTRAINT brestore_pathhierarchy_pkey PRIMARY KEY (PathId)
63 CREATE INDEX brestore_pathhierarchy_ppathid
64 ON brestore_pathhierarchy (PPathId);
66 CREATE TABLE brestore_pathvisibility
68 PathId integer NOT NULL,
69 JobId integer NOT NULL,
72 CONSTRAINT brestore_pathvisibility_pkey PRIMARY KEY (JobId, PathId)
75 CREATE INDEX brestore_pathvisibility_jobid
76 ON brestore_pathvisibility (JobId);
79 CREATE TABLE bweb_user
81 userid serial not null,
82 username text not null,
83 use_acl boolean default false,
84 enabled boolean default true,
85 comment text default '',
86 passwd text default '',
90 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username(255));
92 CREATE TABLE bweb_role
94 roleid serial not null,
95 rolename text not null,
96 comment text default '',
99 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename(255));
100 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
101 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
102 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
104 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
105 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
106 INSERT INTO bweb_role (rolename) VALUES ('r_storage_mgnt');
107 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
108 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
109 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
111 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
112 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
113 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
114 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
115 INSERT INTO bweb_role (rolename) VALUES ('r_view_group');
116 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
118 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
119 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
120 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
122 CREATE TABLE bweb_role_member
124 roleid integer not null,
125 userid integer not null,
126 primary key (roleid, userid)
129 CREATE TABLE bweb_client_group_acl
131 client_group_id integer not null,
132 userid integer not null,
133 primary key (client_group_id, userid)
137 -- --------------------------------------------------
139 -- --------------------------------------------------
141 -- Manage Client groups in bweb
142 -- Works with postgresql and mysql5
144 CREATE TABLE client_group
146 client_group_id serial not null,
147 client_group_name text not null,
148 comment text default '',
149 primary key (client_group_id)
152 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name(255));
154 CREATE TABLE client_group_member
156 client_group_id integer not null,
157 ClientId integer not null,
158 primary key (client_group_id, clientid)
161 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
164 -- -- creer un nouveau group
166 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
168 -- -- affecter une machine a un group
170 -- INSERT INTO client_group_member (client_group_id, clientid)
171 -- (SELECT client_group_id,
172 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
174 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
177 -- -- modifier l'affectation d'une machine
179 -- DELETE FROM client_group_member
180 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
182 -- -- supprimer un groupe
184 -- DELETE FROM client_group_member
185 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
188 -- -- afficher tous les clients du group SIGMA
190 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
191 -- JOIN client_group using (client_group_id)
192 -- WHERE client_group_name = 'SIGMA';
194 -- -- afficher tous les groups
196 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
198 -- -- afficher tous les job du group SIGMA hier
200 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
201 -- FROM Job JOIN Client USING(ClientId)
202 -- JOIN client_group_member USING (ClientId)
203 -- JOIN client_group USING (client_group_id)
204 -- WHERE client_group_name = 'SIGMA'
205 -- AND Job.StartTime > '2007-03-20';
207 -- -- donne des stats
209 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
210 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
211 -- JobStatus AS jobstatus, client_group_name
212 -- FROM Job JOIN Client USING(ClientId)
213 -- JOIN client_group_member USING (ClientId)
214 -- JOIN client_group USING (client_group_id)
215 -- WHERE Job.StartTime > '2007-03-20'
216 -- GROUP BY JobStatus, client_group_name