From 2c0340b044248cab40cc21191630a9880be58bcd Mon Sep 17 00:00:00 2001
From: Marco van Wieringen <marco.van.wieringen@bareos.com>
Date: Mon, 10 Nov 2014 23:11:41 +0100
Subject: [PATCH] Tweak queries a bit.

Create a second query for finding the last full backup when no specific
pool is specified as then we don't need to JOIN the JobMedia and Media
table into the query as we don't select on the Media's PoolId.

Also drop the JOIN on Client as we don't select any column from that
table anyway and the query is also kind of clumbsy by specifying twice
the ClientId instead of using a single WHERE on the ClientId and use
an AND Client.ClientId = Job.ClientId.
---
 src/cats/sql_cmds.c   |   17 ++++++++++--
 src/dird/ua_restore.c |   66 ++++++++++++++++++++++++++++++++++---------------
 2 files changed, 60 insertions(+), 23 deletions(-)

diff --git a/src/cats/sql_cmds.c b/src/cats/sql_cmds.c
index c3b94e7..9d8942a 100644
--- a/src/cats/sql_cmds.c
+++ b/src/cats/sql_cmds.c
@@ -133,8 +133,8 @@ const char *uar_del_temp1 = "DROP TABLE temp1";
 
 const char *uar_last_full =
    "INSERT INTO temp1 SELECT Job.JobId,JobTdate "
-   "FROM Client,Job,JobMedia,Media,FileSet WHERE Client.ClientId=%s "
-   "AND Job.ClientId=%s "
+   "FROM Job,JobMedia,Media,FileSet "
+   "WHERE Job.ClientId=%s "
    "AND Job.StartTime < '%s' "
    "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' "
    "AND JobMedia.JobId=Job.JobId "
@@ -145,11 +145,22 @@ const char *uar_last_full =
    "%s"
    "ORDER BY Job.JobTDate DESC LIMIT 1";
 
+const char *uar_last_full_no_pool =
+   "INSERT INTO temp1 SELECT Job.JobId,JobTdate "
+   "FROM Job,FileSet "
+   "WHERE Job.ClientId=%s "
+   "AND Job.StartTime < '%s' "
+   "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' "
+   "AND Job.FileSetId=FileSet.FileSetId "
+   "AND FileSet.FileSet='%s' "
+   "ORDER BY Job.JobTDate DESC LIMIT 1";
+
 const char *uar_full =
    "INSERT INTO temp SELECT Job.JobId,Job.JobTDate,"
    "Job.ClientId,Job.Level,Job.JobFiles,Job.JobBytes,"
    "StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime "
-   "FROM temp1,Job,JobMedia,Media WHERE temp1.JobId=Job.JobId "
+   "FROM temp1,Job,JobMedia,Media "
+   "WHERE temp1.JobId=Job.JobId "
    "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' "
    "AND Media.Enabled=1 "
    "AND JobMedia.JobId=Job.JobId "
diff --git a/src/dird/ua_restore.c b/src/dird/ua_restore.c
index fbb9826..d69543e 100644
--- a/src/dird/ua_restore.c
+++ b/src/dird/ua_restore.c
@@ -1287,8 +1287,7 @@ static bool build_directory_tree(UAContext *ua, RESTORE_CTX *rx)
 }
 
 /*
- * This routine is used to get the current backup or a backup
- *   before the specified date.
+ * This routine is used to get the current backup or a backup before the specified date.
  */
 static bool select_backups_before_date(UAContext *ua, RESTORE_CTX *rx, char *date)
 {
@@ -1356,7 +1355,9 @@ static bool select_backups_before_date(UAContext *ua, RESTORE_CTX *rx, char *dat
       }
    }
 
-   /* If Pool specified, add PoolId specification */
+   /*
+    * If Pool specified, add PoolId specification
+    */
    pool_select[0] = 0;
    if (rx->pool) {
       POOL_DBR pr;
@@ -1370,23 +1371,35 @@ static bool select_backups_before_date(UAContext *ua, RESTORE_CTX *rx, char *dat
       }
    }
 
-   /* Find JobId of last Full backup for this client, fileset */
-   edit_int64(cr.ClientId, ed1);
-   Mmsg(rx->query, uar_last_full, ed1, ed1, date, fsr.FileSet,
-         pool_select);
-   if (!db_sql_query(ua->db, rx->query)) {
-      ua->error_msg("%s\n", db_strerror(ua->db));
-      goto bail_out;
+   /*
+    * Find JobId of last Full backup for this client, fileset
+    */
+   if (pool_select[0]) {
+      edit_int64(cr.ClientId, ed1);
+      Mmsg(rx->query, uar_last_full, ed1, date, fsr.FileSet, pool_select);
+      if (!db_sql_query(ua->db, rx->query)) {
+         ua->error_msg("%s\n", db_strerror(ua->db));
+         goto bail_out;
+      }
+   } else {
+      edit_int64(cr.ClientId, ed1);
+      Mmsg(rx->query, uar_last_full_no_pool, ed1, date, fsr.FileSet);
+      if (!db_sql_query(ua->db, rx->query)) {
+         ua->error_msg("%s\n", db_strerror(ua->db));
+         goto bail_out;
+      }
    }
 
-   /* Find all Volumes used by that JobId */
+   /*
+    * Find all Volumes used by that JobId
+    */
    if (!db_sql_query(ua->db, uar_full)) {
       ua->error_msg("%s\n", db_strerror(ua->db));
       goto bail_out;
    }
 
-   /* Note, this is needed because I don't seem to get the callback
-    * from the call just above.
+   /*
+    * Note, this is needed because I don't seem to get the callback from the call just above.
     */
    rx->JobTDate = 0;
    if (!db_sql_query(ua->db, uar_sel_all_temp1, last_full_handler, (void *)rx)) {
@@ -1397,13 +1410,18 @@ static bool select_backups_before_date(UAContext *ua, RESTORE_CTX *rx, char *dat
       goto bail_out;
    }
 
-   /* Now find most recent Differental Job after Full save, if any */
+   /*
+    * Now find most recent Differental Job after Full save, if any
+    */
    Mmsg(rx->query, uar_dif, edit_uint64(rx->JobTDate, ed1), date,
         edit_int64(cr.ClientId, ed2), fsr.FileSet, pool_select);
    if (!db_sql_query(ua->db, rx->query)) {
       ua->warning_msg("%s\n", db_strerror(ua->db));
    }
-   /* Now update JobTDate to look into Differental, if any */
+
+   /*
+    * Now update JobTDate to look into Differental, if any
+    */
    rx->JobTDate = 0;
    if (!db_sql_query(ua->db, uar_sel_all_temp, last_full_handler, (void *)rx)) {
       ua->warning_msg("%s\n", db_strerror(ua->db));
@@ -1413,14 +1431,18 @@ static bool select_backups_before_date(UAContext *ua, RESTORE_CTX *rx, char *dat
       goto bail_out;
    }
 
-   /* Now find all Incremental Jobs after Full/dif save */
+   /*
+    * Now find all Incremental Jobs after Full/dif save
+    */
    Mmsg(rx->query, uar_inc, edit_uint64(rx->JobTDate, ed1), date,
         edit_int64(cr.ClientId, ed2), fsr.FileSet, pool_select);
    if (!db_sql_query(ua->db, rx->query)) {
       ua->warning_msg("%s\n", db_strerror(ua->db));
    }
 
-   /* Get the JobIds from that list */
+   /*
+    * Get the JobIds from that list
+    */
    rx->last_jobid[0] = rx->JobIds[0] = 0;
 
    if (!db_sql_query(ua->db, uar_sel_jobid_temp, jobid_handler, (void *)rx)) {
@@ -1429,14 +1451,18 @@ static bool select_backups_before_date(UAContext *ua, RESTORE_CTX *rx, char *dat
 
    if (rx->JobIds[0] != 0) {
       if (find_arg(ua, NT_("copies")) > 0) {
-         /* Display a list of all copies */
+         /*
+          * Display a list of all copies
+          */
          db_list_copies_records(ua->jcr, ua->db, 0, rx->JobIds,
                                 prtit, ua, HORZ_LIST);
       }
-      /* Display a list of Jobs selected for this restore */
+
+      /*
+       * Display a list of Jobs selected for this restore
+       */
       db_list_sql_query(ua->jcr, ua->db, uar_list_temp, prtit, ua, true, HORZ_LIST);
       ok = true;
-
    } else {
       ua->warning_msg(_("No jobs found.\n"));
    }
-- 
1.7.3.2