From 3daad5bb1eff9e0601330a46f24f61bd4cf4eb53 Mon Sep 17 00:00:00 2001
From: Eric Bollengier <eric@eb.homelinux.org>
Date: Fri, 1 Jun 2007 13:13:37 +0000
Subject: [PATCH] ebl  need to have 2 differents sql files

git-svn-id: https://bacula.svn.sourceforge.net/svnroot/bacula/trunk@4958 91ce42f0-d328-0410-95d8-f526ca767f89
---
 .../script/{bweb_group.sql => bweb-mysql.sql} |  0
 gui/bweb/script/bweb-postgresql.sql           | 82 +++++++++++++++++++
 2 files changed, 82 insertions(+)
 rename gui/bweb/script/{bweb_group.sql => bweb-mysql.sql} (100%)

diff --git a/gui/bweb/script/bweb_group.sql b/gui/bweb/script/bweb-mysql.sql
similarity index 100%
rename from gui/bweb/script/bweb_group.sql
rename to gui/bweb/script/bweb-mysql.sql
diff --git a/gui/bweb/script/bweb-postgresql.sql b/gui/bweb/script/bweb-postgresql.sql
index 626333a5fa..804712f25d 100644
--- a/gui/bweb/script/bweb-postgresql.sql
+++ b/gui/bweb/script/bweb-postgresql.sql
@@ -1,3 +1,83 @@
+BEGIN;
+-- Manage Client groups in bweb
+-- Works with postgresql and mysql5 
+
+CREATE TABLE client_group
+(
+    client_group_id             serial	  not null,
+    client_group_name	        text	  not null,
+    primary key (client_group_id)
+);
+
+CREATE UNIQUE INDEX client_group_idx on client_group (client_group_name);
+
+CREATE TABLE client_group_member
+(
+    client_group_id 		    integer	  not null,
+    clientid          integer     not null,
+    primary key (client_group_id, clientid)
+);
+
+CREATE INDEX client_group_member_idx on client_group_member (client_group_id);
+
+COMMIT;
+
+--   -- creer un nouveau group
+--   
+--   INSERT INTO client_group (client_group_name) VALUES ('SIGMA');
+--   
+--   -- affecter une machine a un group
+--   
+--   INSERT INTO client_group_member (client_group_id, clientid) 
+--          (SELECT client_group_id, 
+--                 (SELECT Clientid FROM Client WHERE Name = 'slps0003-fd')
+--             FROM client_group 
+--            WHERE client_group_name IN ('SIGMA', 'EXPLOITATION', 'MUTUALISE'));
+--        
+--   
+--   -- modifier l'affectation d'une machine
+--   
+--   DELETE FROM client_group_member 
+--         WHERE clientid = (SELECT ClientId FROM Client WHERE Name = 'slps0003-fd')
+--   
+--   -- supprimer un groupe
+--   
+--   DELETE FROM client_group_member 
+--         WHERE client_group_id = (SELECT client_group_id FROM client_group WHERE client_group_name = 'EXPLOIT')
+--   
+--   
+--   -- afficher tous les clients du group SIGMA
+--   
+--   SELECT Name FROM Client JOIN client_group_member using (clientid) 
+--                           JOIN client_group using (client_group_id)
+--    WHERE client_group_name = 'SIGMA';
+--   
+--   -- afficher tous les groups
+--   
+--   SELECT client_group_name FROM client_group ORDER BY client_group_name;
+--   
+--   -- afficher tous les job du group SIGMA hier
+--   
+--   SELECT JobId, Job.Name, Client.Name, JobStatus, JobErrors
+--     FROM Job JOIN Client              USING(ClientId) 
+--              JOIN client_group_member USING (ClientId)
+--              JOIN client_group        USING (client_group_id)
+--     WHERE client_group_name = 'SIGMA'
+--       AND Job.StartTime > '2007-03-20'; 
+--   
+--   -- donne des stats
+--   
+--   SELECT count(1) AS nb, sum(JobFiles) AS files,
+--          sum(JobBytes) AS size, sum(JobErrors) AS joberrors,
+--          JobStatus AS jobstatus, client_group_name
+--     FROM Job JOIN Client              USING(ClientId) 
+--              JOIN client_group_member USING (ClientId)
+--              JOIN client_group        USING (client_group_id)
+--     WHERE Job.StartTime > '2007-03-20'
+--     GROUP BY JobStatus, client_group_name
+--   
+--   
+
 CREATE PROCEDURAL LANGUAGE plpgsql;
 BEGIN;
 
@@ -39,3 +119,5 @@ END;
 $$ language 'plpgsql';
 
 COMMIT;
+
+
-- 
2.39.5