1 -- --------------------------------------------------
3 -- --------------------------------------------------
5 ALTER TABLE Status ADD COLUMN severity int;
6 UPDATE Status SET severity = 15;
7 UPDATE Status SET severity = 100 where JobStatus = 'f';
8 UPDATE Status SET severity = 90 where JobStatus = 'A';
9 UPDATE Status SET severity = 10 where JobStatus = 'T';
10 UPDATE status SET severity = 20 where jobstatus = 'e';
11 UPDATE status SET severity = 25 where jobstatus = 'E';
14 -- New tables for bresto (same as brestore)
16 CREATE TABLE brestore_knownjobid
18 JobId integer NOT NULL,
19 CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId)
22 CREATE TABLE brestore_pathhierarchy
24 PathId integer NOT NULL,
25 PPathId integer NOT NULL,
26 CONSTRAINT brestore_pathhierarchy_pkey PRIMARY KEY (PathId)
29 CREATE INDEX brestore_pathhierarchy_ppathid
30 ON brestore_pathhierarchy (PPathId);
32 CREATE TABLE brestore_pathvisibility
34 PathId integer NOT NULL,
35 JobId integer NOT NULL,
38 CONSTRAINT brestore_pathvisibility_pkey PRIMARY KEY (JobId, PathId)
41 CREATE INDEX brestore_pathvisibility_jobid
42 ON brestore_pathvisibility (JobId);
45 CREATE TABLE bweb_user
47 userid serial not null,
48 username text not null,
49 use_acl boolean default false,
50 enabled boolean default true,
51 comment text default '',
52 passwd text default '',
56 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username(255));
58 CREATE TABLE bweb_role
60 roleid serial not null,
61 rolename text not null,
62 comment text default '',
65 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename(255));
66 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
67 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
68 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
70 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
71 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
72 INSERT INTO bweb_role (rolename) VALUES ('r_storage_mgnt');
73 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
74 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
75 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
77 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
78 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
79 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
80 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
81 INSERT INTO bweb_role (rolename) VALUES ('r_view_group');
82 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
84 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
85 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
86 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
88 CREATE TABLE bweb_role_member
90 roleid integer not null,
91 userid integer not null,
92 primary key (roleid, userid)
95 CREATE TABLE bweb_client_group_acl
97 client_group_id integer not null,
98 userid integer not null,
99 primary key (client_group_id, userid)
103 -- --------------------------------------------------
105 -- --------------------------------------------------
107 -- Manage Client groups in bweb
108 -- Works with postgresql and mysql5
110 CREATE TABLE client_group
112 client_group_id serial not null,
113 client_group_name text not null,
114 comment text default '',
115 primary key (client_group_id)
118 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name(255));
120 CREATE TABLE client_group_member
122 client_group_id integer not null,
123 ClientId integer not null,
124 primary key (client_group_id, clientid)
127 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
130 -- -- creer un nouveau group
132 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
134 -- -- affecter une machine a un group
136 -- INSERT INTO client_group_member (client_group_id, clientid)
137 -- (SELECT client_group_id,
138 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
140 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
143 -- -- modifier l'affectation d'une machine
145 -- DELETE FROM client_group_member
146 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
148 -- -- supprimer un groupe
150 -- DELETE FROM client_group_member
151 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
154 -- -- afficher tous les clients du group SIGMA
156 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
157 -- JOIN client_group using (client_group_id)
158 -- WHERE client_group_name = 'SIGMA';
160 -- -- afficher tous les groups
162 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
164 -- -- afficher tous les job du group SIGMA hier
166 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
167 -- FROM Job JOIN Client USING(ClientId)
168 -- JOIN client_group_member USING (ClientId)
169 -- JOIN client_group USING (client_group_id)
170 -- WHERE client_group_name = 'SIGMA'
171 -- AND Job.StartTime > '2007-03-20';
173 -- -- donne des stats
175 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
176 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
177 -- JobStatus AS jobstatus, client_group_name
178 -- FROM Job JOIN Client USING(ClientId)
179 -- JOIN client_group_member USING (ClientId)
180 -- JOIN client_group USING (client_group_id)
181 -- WHERE Job.StartTime > '2007-03-20'
182 -- GROUP BY JobStatus, client_group_name