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';
11 -- New tables for bresto (same as brestore)
13 CREATE TABLE brestore_knownjobid
15 JobId integer NOT NULL,
16 CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId)
19 CREATE TABLE brestore_pathhierarchy
21 PathId integer NOT NULL,
22 PPathId integer NOT NULL,
23 CONSTRAINT brestore_pathhierarchy_pkey PRIMARY KEY (PathId)
26 CREATE INDEX brestore_pathhierarchy_ppathid
27 ON brestore_pathhierarchy (PPathId);
29 CREATE TABLE brestore_pathvisibility
31 PathId integer NOT NULL,
32 JobId integer NOT NULL,
35 CONSTRAINT brestore_pathvisibility_pkey PRIMARY KEY (JobId, PathId)
38 CREATE INDEX brestore_pathvisibility_jobid
39 ON brestore_pathvisibility (JobId);
42 CREATE TABLE bweb_user
44 userid serial not null,
45 username text not null,
46 use_acl boolean default false,
47 enabled boolean default true,
48 comment text default '',
49 passwd text default '',
53 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username(255));
55 CREATE TABLE bweb_role
57 roleid serial not null,
58 rolename text not null,
59 comment text default '',
62 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename(255));
63 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
64 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
65 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
67 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
68 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
69 INSERT INTO bweb_role (rolename) VALUES ('r_storage_mgnt');
70 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
71 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
72 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
74 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
75 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
76 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
77 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
78 INSERT INTO bweb_role (rolename) VALUES ('r_view_group');
79 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
81 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
82 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
83 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
85 CREATE TABLE bweb_role_member
87 roleid integer not null,
88 userid integer not null,
89 primary key (roleid, userid)
92 CREATE TABLE bweb_client_group_acl
94 client_group_id integer not null,
95 userid integer not null,
96 primary key (client_group_id, userid)
100 -- --------------------------------------------------
102 -- --------------------------------------------------
104 -- Manage Client groups in bweb
105 -- Works with postgresql and mysql5
107 CREATE TABLE client_group
109 client_group_id serial not null,
110 client_group_name text not null,
111 comment text default '',
112 primary key (client_group_id)
115 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name(255));
117 CREATE TABLE client_group_member
119 client_group_id integer not null,
120 ClientId integer not null,
121 primary key (client_group_id, clientid)
124 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
127 -- -- creer un nouveau group
129 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
131 -- -- affecter une machine a un group
133 -- INSERT INTO client_group_member (client_group_id, clientid)
134 -- (SELECT client_group_id,
135 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
137 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
140 -- -- modifier l'affectation d'une machine
142 -- DELETE FROM client_group_member
143 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
145 -- -- supprimer un groupe
147 -- DELETE FROM client_group_member
148 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
151 -- -- afficher tous les clients du group SIGMA
153 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
154 -- JOIN client_group using (client_group_id)
155 -- WHERE client_group_name = 'SIGMA';
157 -- -- afficher tous les groups
159 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
161 -- -- afficher tous les job du group SIGMA hier
163 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
164 -- FROM Job JOIN Client USING(ClientId)
165 -- JOIN client_group_member USING (ClientId)
166 -- JOIN client_group USING (client_group_id)
167 -- WHERE client_group_name = 'SIGMA'
168 -- AND Job.StartTime > '2007-03-20';
170 -- -- donne des stats
172 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
173 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
174 -- JobStatus AS jobstatus, client_group_name
175 -- FROM Job JOIN Client USING(ClientId)
176 -- JOIN client_group_member USING (ClientId)
177 -- JOIN client_group USING (client_group_id)
178 -- WHERE Job.StartTime > '2007-03-20'
179 -- GROUP BY JobStatus, client_group_name