From 76595a1cfb3049246a1167105726afb454e37fc6 Mon Sep 17 00:00:00 2001
From: Eric Bollengier <eric@eb.homelinux.org>
Date: Thu, 3 Feb 2011 14:38:06 +0100
Subject: [PATCH] Adapt bvfs for SQLite3

---
 bacula/src/cats/bvfs.c     | 30 +++++++++++++++---------------
 bacula/src/cats/sql_cmds.c | 11 ++++++++++-
 2 files changed, 25 insertions(+), 16 deletions(-)

diff --git a/bacula/src/cats/bvfs.c b/bacula/src/cats/bvfs.c
index 7a6588155f..a16e188ef4 100644
--- a/bacula/src/cats/bvfs.c
+++ b/bacula/src/cats/bvfs.c
@@ -616,11 +616,11 @@ void Bvfs::ls_special_dirs()
 
    POOL_MEM query;
    Mmsg(query, 
-"((SELECT PPathId AS PathId, '..' AS Path "
+"(SELECT PPathId AS PathId, '..' AS Path "
     "FROM  PathHierarchy "
-   "WHERE  PathId = %s) "
+   "WHERE  PathId = %s "
 "UNION "
- "(SELECT %s AS PathId, '.' AS Path))",
+ "SELECT %s AS PathId, '.' AS Path)",
         edit_uint64(pwd_id, ed1), ed1);
 
    POOL_MEM query2;
@@ -685,7 +685,7 @@ bool Bvfs::ls_dirs()
       "JOIN PathVisibility AS PathVisibility1 "
         "ON (PathHierarchy1.PathId = PathVisibility1.PathId) "
       "WHERE PathHierarchy1.PPathId = %s "
-      "AND PathVisibility1.jobid IN (%s) "
+      "AND PathVisibility1.JobId IN (%s) "
            "%s "
      ") AS listpath1 "
    "JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) "
@@ -853,12 +853,12 @@ bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink,
       return false;
    }
 
-   Mmsg(query, "CREATE TEMPORARY TABLE btemp%s AS ", output_table);
+   Mmsg(query, "CREATE TABLE btemp%s AS ", output_table);
 
    if (*fileid) {               /* Select files with their direct id */
       init=true;
-      Mmsg(tmp,"(SELECT JobId, JobTDate, FileIndex, FilenameId, PathId, FileId "
-                  "FROM File JOIN Job USING (JobId) WHERE FileId IN (%s))",
+      Mmsg(tmp,"SELECT JobId, JobTDate, FileIndex, FilenameId, PathId, FileId "
+                  "FROM File JOIN Job USING (JobId) WHERE FileId IN (%s)",
            fileid);
       pm_strcat(query, tmp.c_str());
    }
@@ -899,10 +899,10 @@ bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink,
          query.strcat(" UNION ");
       }
 
-      Mmsg(tmp, "(SELECT JobId, JobTDate, File.FileIndex, File.FilenameId, "
+      Mmsg(tmp, "SELECT JobId, JobTDate, File.FileIndex, File.FilenameId, "
                         "File.PathId, FileId "
                    "FROM Path JOIN File USING (PathId) JOIN Job USING (JobId) "
-                  "WHERE Path.Path LIKE '%s' AND File.JobId IN (%s)) ", 
+                  "WHERE Path.Path LIKE '%s' AND File.JobId IN (%s) ", 
            tmp2.c_str(), jobids); 
       query.strcat(tmp.c_str());
       init = true;
@@ -910,13 +910,13 @@ bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink,
       query.strcat(" UNION ");
 
       /* A directory can have files from a BaseJob */
-      Mmsg(tmp, "(SELECT File.JobId, JobTDate, BaseFiles.FileIndex, "
+      Mmsg(tmp, "SELECT File.JobId, JobTDate, BaseFiles.FileIndex, "
                         "File.FilenameId, File.PathId, BaseFiles.FileId "
                    "FROM BaseFiles "
                         "JOIN File USING (FileId) "
                         "JOIN Job ON (BaseFiles.JobId = Job.JobId) "
                         "JOIN Path USING (PathId) "
-                  "WHERE Path.Path LIKE '%s' AND BaseFiles.JobId IN (%s)) ", 
+                  "WHERE Path.Path LIKE '%s' AND BaseFiles.JobId IN (%s) ", 
            tmp2.c_str(), jobids); 
       query.strcat(tmp.c_str());
    }
@@ -934,11 +934,11 @@ bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink,
                query.strcat(" UNION ");
             }
          } else {               /* end last job, start new one */
-            tmp.strcat(")) UNION ");
+            tmp.strcat(") UNION ");
             query.strcat(tmp.c_str());
          }
-         Mmsg(tmp, "(SELECT JobId, JobTDate, FileIndex, FilenameId, "
-                           "PathId, FileId "
+         Mmsg(tmp,   "SELECT JobId, JobTDate, FileIndex, FilenameId, "
+                            "PathId, FileId "
                        "FROM File JOIN Job USING (JobId) WHERE JobId = %lld " 
                         "AND FileIndex IN (%lld", jobid, id);
          prev_jobid = jobid;
@@ -950,7 +950,7 @@ bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink,
    }
 
    if (prev_jobid != 0) {       /* end last job */
-      tmp.strcat(")) ");
+      tmp.strcat(") ");
       query.strcat(tmp.c_str());
       init = true;
    }
diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c
index 26ff05276c..b739d6d966 100644
--- a/bacula/src/cats/sql_cmds.c
+++ b/bacula/src/cats/sql_cmds.c
@@ -813,6 +813,10 @@ const char *sql_get_max_connections[] = {
    "SELECT 0"
 };
 
+/* TODO: Check for corner cases with MySQL and SQLite3
+ *  The Group By can return strange numbers when having multiple
+ *  version of a file in the same dataset.
+ */
 const char *sql_bvfs_select[] = {
    /* Mysql */
    "CREATE TABLE %s AS ( "
@@ -833,7 +837,12 @@ const char *sql_bvfs_select[] = {
           "WHERE FileIndex > 0)",
 
    /* SQLite3 */
-   "SELECT 0",
+   "CREATE TABLE %s AS "
+      "SELECT JobId, FileIndex, FileId, max(JobTDate) as JobTDate "
+        "FROM btemp%s "
+      "GROUP BY PathId, FilenameId "
+   "HAVING FileIndex > 0",
+   
 
    /* Ingres (TODO) */
    "SELECT 0"
-- 
2.39.5