1 -- Manage Client groups in bweb
2 -- Works with postgresql and mysql5
4 CREATE TABLE client_group
6 client_group_id serial not null,
7 client_group_name text not null,
8 primary key (client_group_id)
11 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name(255));
13 CREATE TABLE client_group_member
15 client_group_id integer not null,
16 clientid integer not null,
17 primary key (client_group_id, clientid)
20 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
23 -- -- creer un nouveau group
25 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
27 -- -- affecter une machine a un group
29 -- INSERT INTO client_group_member (client_group_id, clientid)
30 -- (SELECT client_group_id,
31 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
33 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
36 -- -- modifier l'affectation d'une machine
38 -- DELETE FROM client_group_member
39 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
41 -- -- supprimer un groupe
43 -- DELETE FROM client_group_member
44 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
47 -- -- afficher tous les clients du group SIGMA
49 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
50 -- JOIN client_group using (client_group_id)
51 -- WHERE client_group_name = 'SIGMA';
53 -- -- afficher tous les groups
55 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
57 -- -- afficher tous les job du group SIGMA hier
59 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
60 -- FROM Job JOIN Client USING(ClientId)
61 -- JOIN client_group_member USING (ClientId)
62 -- JOIN client_group USING (client_group_id)
63 -- WHERE client_group_name = 'SIGMA'
64 -- AND Job.StartTime > '2007-03-20';
68 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
69 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
70 -- JobStatus AS jobstatus, client_group_name
71 -- FROM Job JOIN Client USING(ClientId)
72 -- JOIN client_group_member USING (ClientId)
73 -- JOIN client_group USING (client_group_id)
74 -- WHERE Job.StartTime > '2007-03-20'
75 -- GROUP BY JobStatus, client_group_name