1 -- --------------------------------------------------
3 -- --------------------------------------------------
6 -- Manage Client groups in bweb
7 -- Works with postgresql and mysql5
9 CREATE TABLE client_group
11 client_group_id serial not null,
12 client_group_name text not null,
13 primary key (client_group_id)
16 CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name);
18 CREATE TABLE client_group_member
20 client_group_id integer not null,
21 clientid integer not null,
22 primary key (client_group_id, clientid)
25 CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
29 -- --------------------------------------------------
30 -- End of upgrade from 2.0
31 -- --------------------------------------------------
33 -- -- creer un nouveau group
35 -- INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
37 -- -- affecter une machine a un group
39 -- INSERT INTO client_group_member (client_group_id, clientid)
40 -- (SELECT client_group_id,
41 -- (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
43 -- WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
46 -- -- modifier l'affectation d'une machine
48 -- DELETE FROM client_group_member
49 -- WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
51 -- -- supprimer un groupe
53 -- DELETE FROM client_group_member
54 -- WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
57 -- -- afficher tous les clients du group SIGMA
59 -- SELECT Name FROM Client JOIN client_group_member using (clientid)
60 -- JOIN client_group using (client_group_id)
61 -- WHERE client_group_name = 'SIGMA';
63 -- -- afficher tous les groups
65 -- SELECT client_group_name FROM client_group ORDER BY client_group_name;
67 -- -- afficher tous les job du group SIGMA hier
69 -- SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
70 -- FROM Job JOIN Client USING(ClientId)
71 -- JOIN client_group_member USING (ClientId)
72 -- JOIN client_group USING (client_group_id)
73 -- WHERE client_group_name = 'SIGMA'
74 -- AND Job.StartTime > '2007-03-20';
78 -- SELECT count(1) AS nb, sum(JobFiles) AS files,
79 -- sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
80 -- JobStatus AS jobstatus, client_group_name
81 -- FROM Job JOIN Client USING(ClientId)
82 -- JOIN client_group_member USING (ClientId)
83 -- JOIN client_group USING (client_group_id)
84 -- WHERE Job.StartTime > '2007-03-20'
85 -- GROUP BY JobStatus, client_group_name
89 CREATE PROCEDURAL LANGUAGE plpgsql;
92 CREATE FUNCTION SEC_TO_TIME(timestamp with time zone)
93 RETURNS timestamp with time zone AS $$
94 select date_trunc('second', $1);
97 CREATE FUNCTION SEC_TO_TIME(bigint)
98 RETURNS interval AS $$
99 select date_trunc('second', $1 * interval '1 second');
102 CREATE FUNCTION UNIX_TIMESTAMP(timestamp with time zone)
103 RETURNS double precision AS $$
104 select date_part('epoch', $1);
107 CREATE FUNCTION SEC_TO_INT(interval)
108 RETURNS double precision AS $$
109 select extract(epoch from $1);
112 CREATE OR REPLACE FUNCTION base64_decode_lstat(int4, varchar) RETURNS int8 AS $$
119 size := split_part($2, ' ', $1);
120 b64 := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
122 FOR i IN 1..length(size) LOOP
123 val := val + (strpos(b64, substr(size, i, 1))-1) * (64^(length(size)-i));
127 $$ language 'plpgsql';