From 6ccc36819780208553d8b4d541c3c98e4137a7fb Mon Sep 17 00:00:00 2001
From: Eric Bollengier <eric@eb.homelinux.org>
Date: Tue, 15 Jun 2010 19:03:35 +0200
Subject: [PATCH] cleanup sql queries

---
 bacula/src/cats/sql_cmds.c | 91 ++++----------------------------------
 1 file changed, 8 insertions(+), 83 deletions(-)

diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c
index cb8a9d4149..dbcdb6d559 100644
--- a/bacula/src/cats/sql_cmds.c
+++ b/bacula/src/cats/sql_cmds.c
@@ -136,79 +136,7 @@ const char *insert_delcand =
  * At the same time, we select "orphanned" jobs
  * (i.e. no files, ...) for deletion.
  */
-#ifdef old_way
-const char *select_backup_del =
-   "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles "
-   "FROM Job,DelCandidates "
-   "WHERE (Job.JobTDate<%s 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.JobTdate<%s 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.JobTdate<%s 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.JobTdate<%s 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.JobTdate<%s AND DelCandidates.JobStatus NOT IN ('T','W')) OR "
-   "(Job.JobTDate>%s "
-   "AND Job.ClientId=%s "
-   "AND Job.Type='g')";
-
-/*
- * Select Jobs from the DelCandidates table.
- * This is the list of Jobs to delete for an Copy Job.
- */
-const char *select_copy_del =
-   "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles "
-   "FROM Job,DelCandidates "
-   "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus NOT IN ('T','W')) OR "
-   "(Job.JobTDate>%s "
-   "AND Job.ClientId=%s "
-   "AND Job.Type='c')";
-
 
-#else
 /* Faster way */
 const char *select_backup_del =
    "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles "
@@ -276,9 +204,6 @@ const char *select_copy_del =
    "AND Job.ClientId=%s "
    "AND Job.Type='c')";
 
-
-#endif
-
 /* ======= ua_restore.c */
 const char *uar_count_files =
    "SELECT JobFiles FROM Job WHERE JobId=%s";
@@ -628,14 +553,14 @@ const char *select_recent_version[5] = {
 };
 
 const char *create_temp_accurate_jobids_default =
-   "CREATE TEMPORARY TABLE btemp3%s AS "
-   "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
-   "FROM Job JOIN FileSet USING (FileSetId) "
-   "WHERE ClientId = %s "
-   "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' "
-   "AND StartTime<'%s' "
-   "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
-   "ORDER BY Job.JobTDate DESC LIMIT 1";
+ "CREATE TEMPORARY TABLE btemp3%s AS "
+    "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
+      "FROM Job JOIN FileSet USING (FileSetId) "
+     "WHERE ClientId = %s "
+       "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' "
+       "AND StartTime<'%s' "
+       "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
+     "ORDER BY Job.JobTDate DESC LIMIT 1";
 
 const char *create_temp_accurate_jobids[5] = {
    /* Mysql */
-- 
2.39.5