bat: Use BVFS on bRestore view

[bacula/bacula] / bacula / src / cats / sql_cmds.c

diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c
index dbcdb6d55924bd6b2c3430f35630820933ff4046..5340b2dfc41d55680746cb87a9bce93ca8a810a4 100644 (file)
--- a/bacula/src/cats/sql_cmds.c
+++ b/bacula/src/cats/sql_cmds.c
@@ -6,7 +6,7 @@
    The main author of Bacula is Kern Sibbald, with contributions from
    many others, a complete list can be found in the file AUTHORS.
    This program is Free Software; you can redistribute it and/or
-   modify it under the terms of version two of the GNU General Public
+   modify it under the terms of version three of the GNU Affero General Public
    License as published by the Free Software Foundation and included
    in the file LICENSE.
 
@@ -15,7 +15,7 @@
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
    General Public License for more details.
 
-   You should have received a copy of the GNU General Public License
+   You should have received a copy of the GNU Affero General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
    02110-1301, USA.
@@ -63,7 +63,9 @@ const char *fill_jobhisto =
            "PurgedFiles, HasBase, Reviewed, Comment "
           "FROM Job "
          "WHERE JobStatus IN ('T','W','f','A','E') "
-           "AND JobId NOT IN (SELECT JobId FROM JobHisto) "
+           "AND NOT EXISTS "
+                "(SELECT JobHisto.JobId "
+                   "FROM JobHisto WHERE JobHisto.Jobid=Job.JobId) "
            "AND JobTDate < %s ";
 
 /* For ua_update.c */
@@ -119,91 +121,6 @@ const char *drop_deltabs[] = {
 
 const char *create_delindex = "CREATE INDEX DelInx1 ON DelCandidates (JobId)";
 
-/* Fill candidates table with all Jobs subject to being deleted.
- *  This is used for pruning Jobs (first the files, then the Jobs).
- */
-const char *insert_delcand =
-   "INSERT INTO DelCandidates "
-   "SELECT JobId,PurgedFiles,FileSetId,JobFiles,JobStatus FROM Job "
-   "WHERE Type='%c' "
-   "AND JobTDate<%s "
-   "AND ClientId=%s";
-
-/*
- * Select Jobs from the DelCandidates table that have a
- * more recent backup -- i.e. are not the only backup.
- * This is the list of Jobs to delete for a Backup Job.
- * At the same time, we select "orphanned" jobs
- * (i.e. no files, ...) for deletion.
- */
-
-/* Faster way */
-const char *select_backup_del =
-   "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles "
-   "FROM Job,DelCandidates "
-   "WHERE (Job.JobId=DelCandidates.JobId AND ((DelCandidates.JobFiles=0) OR "
-   "(DelCandidates.JobStatus NOT IN ('T','W')))) OR "
-   "(Job.JobTDate>%s "
-   "AND Job.ClientId=%s "
-   "AND Job.Level='F' AND Job.JobStatus IN ('T','W') AND Job.Type IN ('B','M') "
-   "AND Job.FileSetId=DelCandidates.FileSetId)";
-
-/* Select Jobs from the DelCandidates table that have a
- * more recent InitCatalog -- i.e. are not the only InitCatalog
- * This is the list of Jobs to delete for a Verify Job.
- */
-const char *select_verify_del =
-   "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles "
-   "FROM Job,DelCandidates "
-   "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR "
-   "(Job.JobTDate>%s "
-   "AND Job.ClientId=%s "
-   "AND Job.Type='V' AND Job.Level='V' AND Job.JobStatus IN ('T','W') "
-   "AND Job.FileSetId=DelCandidates.FileSetId)";
-
-
-/* Select Jobs from the DelCandidates table.
- * This is the list of Jobs to delete for a Restore Job.
- */
-const char *select_restore_del =
-   "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles "
-   "FROM Job,DelCandidates "
-   "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR "
-   "(Job.JobTDate>%s "
-   "AND Job.ClientId=%s "
-   "AND Job.Type='R')";
-
-/* Select Jobs from the DelCandidates table.
- * This is the list of Jobs to delete for an Admin Job.
- */
-const char *select_admin_del =
-   "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles "
-   "FROM Job,DelCandidates "
-   "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR "
-   "(Job.JobTDate>%s "
-   "AND Job.ClientId=%s "
-   "AND Job.Type='D')";
-
-/*
- * Select Jobs from the DelCandidates table.
- * This is the list of Jobs to delete for an Migrate Job.
- */
-const char *select_migrate_del =
-   "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles "
-   "FROM Job,DelCandidates "
-   "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR "
-   "(Job.JobTDate>%s "
-   "AND Job.ClientId=%s "
-   "AND Job.Type='g')";
-
-const char *select_copy_del =
-   "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles "
-   "FROM Job,DelCandidates "
-   "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR "
-   "(Job.JobTDate>%s "
-   "AND Job.ClientId=%s "
-   "AND Job.Type='c')";
-
 /* ======= ua_restore.c */
 const char *uar_count_files =
    "SELECT JobFiles FROM Job WHERE JobId=%s";
@@ -552,8 +469,11 @@ const char *select_recent_version[5] = {
       "AND j1.JobId = f1.JobId"
 };
 
+/* We don't create this table as TEMPORARY because MySQL MyISAM 
+ * 5.0 and 5.1 are unable to run further queries in this mode
+ */
 const char *create_temp_accurate_jobids_default =
- "CREATE TEMPORARY TABLE btemp3%s AS "
+ "CREATE TABLE btemp3%s AS "
     "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
       "FROM Job JOIN FileSet USING (FileSetId) "
      "WHERE ClientId = %s "