3 userid serial not null,
4 username text not null,
5 use_acl boolean default false,
6 enabled boolean default true,
7 comment text default '',
8 passwd text default '',
11 CREATE UNIQUE INDEX bweb_user_idx on bweb_user (username(255));
13 CREATE TABLE bweb_role
15 roleid serial not null,
16 rolename text not null,
17 -- comment text default '',
20 CREATE UNIQUE INDEX bweb_role_idx on bweb_role (rolename(255));
21 INSERT INTO bweb_role (rolename) VALUES ('r_user_mgnt');
22 INSERT INTO bweb_role (rolename) VALUES ('r_group_mgnt');
23 INSERT INTO bweb_role (rolename) VALUES ('r_configure');
25 INSERT INTO bweb_role (rolename) VALUES ('r_autochanger_mgnt');
26 INSERT INTO bweb_role (rolename) VALUES ('r_location_mgnt');
27 INSERT INTO bweb_role (rolename) VALUES ('r_delete_job');
28 INSERT INTO bweb_role (rolename) VALUES ('r_prune');
29 INSERT INTO bweb_role (rolename) VALUES ('r_purge');
31 INSERT INTO bweb_role (rolename) VALUES ('r_view_job');
32 INSERT INTO bweb_role (rolename) VALUES ('r_view_log');
33 INSERT INTO bweb_role (rolename) VALUES ('r_view_stat');
34 INSERT INTO bweb_role (rolename) VALUES ('r_view_media');
35 INSERT INTO bweb_role (rolename) VALUES ('r_view_running_job');
37 INSERT INTO bweb_role (rolename) VALUES ('r_run_job');
38 INSERT INTO bweb_role (rolename) VALUES ('r_cancel_job');
39 INSERT INTO bweb_role (rolename) VALUES ('r_client_status');
41 CREATE TABLE bweb_role_member
43 roleid integer not null,
44 userid integer not null,
45 primary key (roleid, userid)
48 CREATE TABLE bweb_client_group_acl
50 client_group_id integer not null,
51 userid integer not null,
52 primary key (client_group_id, userid)
56 -- Manage Client groups in bweb
57 -- Works with postgresql and mysql5
59 CREATE TABLE client_group
61 client_group_id serial not null,
62 client_group_name text not null,
63 primary key (client_group_id)
66 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name(255));
68 CREATE TABLE client_group_member
70 client_group_id integer not null,
71 clientid integer not null,
72 primary key (client_group_id, clientid)
75 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
78 -- -- creer un nouveau group
80 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
82 -- -- affecter une machine a un group
84 -- INSERT INTO client_group_member (client_group_id, clientid)
85 -- (SELECT client_group_id,
86 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
88 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
91 -- -- modifier l'affectation d'une machine
93 -- DELETE FROM client_group_member
94 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
96 -- -- supprimer un groupe
98 -- DELETE FROM client_group_member
99 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
102 -- -- afficher tous les clients du group SIGMA
104 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
105 -- JOIN client_group using (client_group_id)
106 -- WHERE client_group_name = 'SIGMA';
108 -- -- afficher tous les groups
110 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
112 -- -- afficher tous les job du group SIGMA hier
114 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
115 -- FROM Job JOIN Client USING(ClientId)
116 -- JOIN client_group_member USING (ClientId)
117 -- JOIN client_group USING (client_group_id)
118 -- WHERE client_group_name = 'SIGMA'
119 -- AND Job.StartTime > '2007-03-20';
121 -- -- donne des stats
123 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
124 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
125 -- JobStatus AS jobstatus, client_group_name
126 -- FROM Job JOIN Client USING(ClientId)
127 -- JOIN client_group_member USING (ClientId)
128 -- JOIN client_group USING (client_group_id)
129 -- WHERE Job.StartTime > '2007-03-20'
130 -- GROUP BY JobStatus, client_group_name