1 -- --------------------------------------------------
3 -- --------------------------------------------------
5 CREATE UNIQUE INDEX location_idx ON Location (Location(255));
7 -- --------------------------------------------------
9 -- --------------------------------------------------
13 DROP FUNCTION IF EXISTS base64_decode_lstat |
14 CREATE FUNCTION base64_decode_lstat (field INTEGER, input BLOB)
20 DECLARE first_char BINARY(1);
21 DECLARE accum_value BIGINT UNSIGNED DEFAULT 0;
23 -- The number of fields can vary, so we need 2 calls to SUBSTRING_INDEX
24 SET input = SUBSTRING_INDEX(SUBSTRING_INDEX(input, ' ', field),
27 WHILE LENGTH(input) > 0 DO
28 SET first_char = SUBSTRING(input, 1, 1);
29 SET input = SUBSTRING(input, 2);
31 SET accum_value = (accum_value << 6) +
32 INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/',
41 -- --------------------------------------------------
43 -- --------------------------------------------------
45 -- New tables for bresto (same as brestore)
47 CREATE TABLE brestore_knownjobid
49 JobId integer NOT NULL,
50 CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId)
53 CREATE TABLE brestore_pathhierarchy
55 PathId integer NOT NULL,
56 PPathId integer NOT NULL,
57 CONSTRAINT brestore_pathhierarchy_pkey PRIMARY KEY (PathId)
60 CREATE INDEX brestore_pathhierarchy_ppathid
61 ON brestore_pathhierarchy (PPathId);
63 CREATE TABLE brestore_pathvisibility
65 PathId integer NOT NULL,
66 JobId integer NOT NULL,
69 CONSTRAINT brestore_pathvisibility_pkey PRIMARY KEY (JobId, PathId)
72 CREATE INDEX brestore_pathvisibility_jobid
73 ON brestore_pathvisibility (JobId);
76 CREATE TABLE bweb_user
78 userid serial not null,
79 username text not null,
80 use_acl boolean default false,
81 enabled boolean default true,
82 comment text default '',
83 passwd text default '',
87 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username(255));
89 CREATE TABLE bweb_role
91 roleid serial not null,
92 rolename text not null,
93 comment text default '',
96 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename(255));
97 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
98 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
99 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
101 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
102 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
103 INSERT INTO bweb_role (rolename) VALUES ('r_storage_mgnt');
104 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
105 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
106 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
108 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
109 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
110 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
111 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
112 INSERT INTO bweb_role (rolename) VALUES ('r_view_group');
113 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
115 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
116 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
117 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
119 CREATE TABLE bweb_role_member
121 roleid integer not null,
122 userid integer not null,
123 primary key (roleid, userid)
126 CREATE TABLE bweb_client_group_acl
128 client_group_id integer not null,
129 userid integer not null,
130 primary key (client_group_id, userid)
134 -- --------------------------------------------------
136 -- --------------------------------------------------
138 -- Manage Client groups in bweb
139 -- Works with postgresql and mysql5
141 CREATE TABLE client_group
143 client_group_id serial not null,
144 client_group_name text not null,
145 comment text default '',
146 primary key (client_group_id)
149 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name(255));
151 CREATE TABLE client_group_member
153 client_group_id integer not null,
154 ClientId integer not null,
155 primary key (client_group_id, clientid)
158 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
161 -- -- creer un nouveau group
163 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
165 -- -- affecter une machine a un group
167 -- INSERT INTO client_group_member (client_group_id, clientid)
168 -- (SELECT client_group_id,
169 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
171 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
174 -- -- modifier l'affectation d'une machine
176 -- DELETE FROM client_group_member
177 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
179 -- -- supprimer un groupe
181 -- DELETE FROM client_group_member
182 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
185 -- -- afficher tous les clients du group SIGMA
187 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
188 -- JOIN client_group using (client_group_id)
189 -- WHERE client_group_name = 'SIGMA';
191 -- -- afficher tous les groups
193 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
195 -- -- afficher tous les job du group SIGMA hier
197 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
198 -- FROM Job JOIN Client USING(ClientId)
199 -- JOIN client_group_member USING (ClientId)
200 -- JOIN client_group USING (client_group_id)
201 -- WHERE client_group_name = 'SIGMA'
202 -- AND Job.StartTime > '2007-03-20';
204 -- -- donne des stats
206 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
207 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
208 -- JobStatus AS jobstatus, client_group_name
209 -- FROM Job JOIN Client USING(ClientId)
210 -- JOIN client_group_member USING (ClientId)
211 -- JOIN client_group USING (client_group_id)
212 -- WHERE Job.StartTime > '2007-03-20'
213 -- GROUP BY JobStatus, client_group_name