From 39a148b66cb0b9dcf5e9e7547f9bdda2ac1bbc55 Mon Sep 17 00:00:00 2001
From: Marco van Wieringen <mvw@planets.elm.net>
Date: Tue, 1 Jun 2010 21:23:56 +0200
Subject: [PATCH] Create table with jobids as a temporary table and move the
 sql definition to sql_cmds.c. We use a default query for all databases but
 Ingres.

---
 bacula/src/cats/sql_cmds.c | 33 ++++++++++++++++++++++++++++++++-
 bacula/src/cats/sql_cmds.h |  1 +
 bacula/src/cats/sql_get.c  | 10 +---------
 3 files changed, 34 insertions(+), 10 deletions(-)

diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c
index 88afd3e024..d48d926d70 100644
--- a/bacula/src/cats/sql_cmds.c
+++ b/bacula/src/cats/sql_cmds.c
@@ -627,6 +627,37 @@ const char *select_recent_version[5] = {
       "AND j1.JobId = f1.JobId"
 };
 
+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";
+
+const char *create_temp_accurate_jobids[5] = {
+   /* Mysql */
+   create_temp_accurate_jobids_default,
+   /* Postgresql */
+   create_temp_accurate_jobids_default,
+   /* SQLite */
+   create_temp_accurate_jobids_default,
+   /* SQLite3 */
+   create_temp_accurate_jobids_default,
+   /* Ingres */
+   "DECLARE GLOBAL 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 FETCH FIRST 1 ROW ONLY "
+   "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
+};
+
 const char *create_temp_basefile[5] = {
    /* Mysql */
    "CREATE TEMPORARY TABLE basefile%lld ("
@@ -697,7 +728,7 @@ const char *create_temp_new_basefile[5] = {
    "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
    "JOIN Path ON (Path.PathId = Temp.PathId) "
    "WHERE Temp.FileIndex > 0 "
-   "ON COMMIT PRESERVE ROWS WITH NORECOVERY",
+   "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
 };
 
 /* ====== ua_prune.c */
diff --git a/bacula/src/cats/sql_cmds.h b/bacula/src/cats/sql_cmds.h
index 5b9b6cb882..4c525da28a 100644
--- a/bacula/src/cats/sql_cmds.h
+++ b/bacula/src/cats/sql_cmds.h
@@ -75,6 +75,7 @@ extern const char CATS_IMP_EXP *uar_sel_jobid_temp;
 
 extern const char CATS_IMP_EXP *select_recent_version[5];
 extern const char CATS_IMP_EXP *select_recent_version_with_basejob[5];
+extern const char CATS_IMP_EXP *create_temp_accurate_jobids[5];
 extern const char CATS_IMP_EXP *create_temp_basefile[5];
 extern const char CATS_IMP_EXP *create_temp_new_basefile[5];
 extern const char CATS_IMP_EXP *create_deltabs[5];
diff --git a/bacula/src/cats/sql_get.c b/bacula/src/cats/sql_get.c
index e44dc47d4e..04f72849c3 100644
--- a/bacula/src/cats/sql_get.c
+++ b/bacula/src/cats/sql_get.c
@@ -1172,15 +1172,7 @@ bool db_accurate_get_jobids(JCR *jcr, B_DB *mdb,
    jobids->count = 0;
 
    /* First, find the last good Full backup for this job/client/fileset */
-   Mmsg(query, 
-"CREATE 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",
+   Mmsg(query, create_temp_accurate_jobids[db_type], 
         edit_uint64(jcr->JobId, jobid),
         edit_uint64(jr->ClientId, clientid),
         date,
-- 
2.39.5