From 6a3673a83780c9a65e01586b29f85a2ee8fd8649 Mon Sep 17 00:00:00 2001
From: Eric Bollengier <eric@eb.homelinux.org>
Date: Fri, 16 Nov 2007 19:37:01 +0000
Subject: [PATCH] ebl  Sql optimizations

git-svn-id: https://bacula.svn.sourceforge.net/svnroot/bacula/trunk@5940 91ce42f0-d328-0410-95d8-f526ca767f89
---
 gui/bweb/cgi/bresto.pl | 42 ++++++++----------------------------------
 1 file changed, 8 insertions(+), 34 deletions(-)

diff --git a/gui/bweb/cgi/bresto.pl b/gui/bweb/cgi/bresto.pl
index eee5613999..d28d42eb72 100755
--- a/gui/bweb/cgi/bresto.pl
+++ b/gui/bweb/cgi/bresto.pl
@@ -867,6 +867,9 @@ my $q="
       push @union, "(SELECT JobId, FileIndex FROM File WHERE FileId IN ($fileid))";
     }
 
+    # using this is not good because the sql engine doesn't know 
+    # what LIKE will use. It will be better to get Path% in perl
+    # but it doesn't work with accents... :(
     foreach my $dirid (@dirid) {
       push @union, "
   (SELECT File.JobId, File.FileIndex, File.FilenameId, File.PathId
@@ -880,11 +883,9 @@ my $q="
 
     my $u = join(" UNION ", @union);
 
-    $bvfs->dbh_do("CREATE TEMP TABLE btemp (JobId integer, FileIndex integer, FilenameId integer, PathId integer)");
-    $bvfs->dbh_do("INSERT INTO btemp $u");
+    $bvfs->dbh_do("CREATE TEMP TABLE btemp AS ($u)");
     # TODO: remove FilenameId et PathId
-    $bvfs->dbh_do("CREATE TABLE b2$$ (JobId integer, FileIndex integer, FilenameId integer, PathId integer)");
-    $bvfs->dbh_do("INSERT INTO b2$$ (
+    $bvfs->dbh_do("CREATE TABLE b2$$ AS (
 SELECT btemp.JobId, btemp.FileIndex, btemp.FilenameId, btemp.PathId
   FROM btemp,
        (SELECT max(JobId) as JobId, PathId, FilenameId
@@ -903,35 +904,8 @@ SELECT btemp.JobId, btemp.FileIndex, btemp.FilenameId, btemp.PathId
 __END__ 
 
 CREATE VIEW files AS 
- SELECT path || name,pathid,filenameid,fileid,jobid 
+ SELECT path || name AS name,pathid,filenameid,fileid,jobid 
    FROM File JOIN FileName USING (FilenameId) JOIN Path USING (PathId);
 
-
-
-CREATE temp TABLE bresto (JobId integer, FileIndex integer, FilenameId integer, PathId integer);
-INSERT INTO bresto 
-(SELECT JobId, FileIndex, FilenameId, PathId FROM File WHERE FileId IN (5716))
-UNION
-(SELECT File.JobId, File.FileIndex, FilenameId, PathId
-    FROM Path JOIN File USING (PathId)
-   WHERE Path.Path LIKE 
-        (SELECT Path || '%' FROM Path WHERE PathId = 1)
-     AND File.JobId IN (1));
-
-CREATE TABLE bresto2 (JobId integer, FileIndex integer, FilenameId integer, PathId integer);
-
-INSERT INTO bresto2 ( SELECT bresto.JobId, bresto.FileIndex, bresto.FilenameId, bresto.PathId
-  FROM bresto,
-       (SELECT max(JobId) as JobId, PathId, FilenameId
-          FROM bresto
-      GROUP BY PathId, FilenameId
-      ORDER BY JobId DESC) AS a
-  WHERE a.JobId = bresto.JobId and a.PathId=bresto.PathId and a.FilenameId = bresto.FilenameId);
-
-DROP TABLE bresto;
-
-# attention, je sais pas si on peut se fier au jobid pour selection la meilleur version...
-
-
-# dirid = 844
-# fileid = 5716
+SELECT 'drop table ' || tablename || ';' 
+  FROM pg_tables WHERE tablename ~ '^b[0-9]'
-- 
2.39.5