Don't hardcode temporary table creation as some backend have a somewhat different...

diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c
index 7ed398e2ca59c274e8c0a8a6f02b4e88634704c6..6ee166d7c70fb907814c93c216859ae079230dfc 100644 (file)
--- a/bacula/src/cats/sql_cmds.c
+++ b/bacula/src/cats/sql_cmds.c
@@ -742,6 +742,49 @@ const char *create_deltabs[5] = {
    "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
 };
 
+/* ======= ua_purge.c */
+
+/* Select the first available Copy Job that must be upgraded to a Backup job when the original backup job is expired. */
+
+const char *uap_upgrade_copies_oldest_job[5] = {
+   /* Mysql */
+   "CREATE TEMPORARY TABLE cpy_tmp AS "
+   "SELECT MIN(JobId) AS JobId FROM Job "
+   "WHERE Type='%c' AND (PriorJobId IN (%s) OR PriorJobId IN ( "
+   "SELECT PriorJobId FROM Job "
+   "WHERE JobId IN (%s) AND Type='B' ))"
+   "GROUP BY PriorJobId",
+   /* Postgresql */
+   "CREATE TEMPORARY TABLE cpy_tmp AS "
+   "SELECT MIN(JobId) AS JobId FROM Job "
+   "WHERE Type='%c' AND (PriorJobId IN (%s) OR PriorJobId IN ( "
+   "SELECT PriorJobId FROM Job "
+   "WHERE JobId IN (%s) AND Type='B' ))"
+   "GROUP BY PriorJobId",
+   /* SQLite */
+   "CREATE TEMPORARY TABLE cpy_tmp AS "
+   "SELECT MIN(JobId) AS JobId FROM Job "
+   "WHERE Type='%c' AND (PriorJobId IN (%s) OR PriorJobId IN ( "
+   "SELECT PriorJobId FROM Job "
+   "WHERE JobId IN (%s) AND Type='B' ))"
+   "GROUP BY PriorJobId",
+   /* SQLite3 */
+   "CREATE TEMPORARY TABLE cpy_tmp AS "
+   "SELECT MIN(JobId) AS JobId FROM Job "
+   "WHERE Type='%c' AND (PriorJobId IN (%s) OR PriorJobId IN ( "
+   "SELECT PriorJobId FROM Job "
+   "WHERE JobId IN (%s) AND Type='B' ))"
+   "GROUP BY PriorJobId",
+   /* Ingres */
+   "DECLARE GLOBAL TEMPORARY TABLE cpy_tmp AS "
+   "SELECT MIN(JobId) AS JobId FROM Job "
+   "WHERE Type='%c' AND (PriorJobId IN (%s) OR PriorJobId IN ( "
+   "SELECT PriorJobId FROM Job "
+   "WHERE JobId IN (%s) AND Type='B' ))"
+   "GROUP BY PriorJobId "
+   "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
+};
+
 /* ======= ua_restore.c */
 
 /* List Jobs where a particular file is saved */

diff --git a/bacula/src/cats/sql_cmds.h b/bacula/src/cats/sql_cmds.h
index e984fe6eb0a6281524be193fe049e8c77370d077..5b9b6cb882b389ed9f7d82b1d498aae4ab0d958c 100644 (file)
--- a/bacula/src/cats/sql_cmds.h
+++ b/bacula/src/cats/sql_cmds.h
@@ -78,6 +78,7 @@ extern const char CATS_IMP_EXP *select_recent_version_with_basejob[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];
+extern const char CATS_IMP_EXP *uap_upgrade_copies_oldest_job[5];
 
 extern const char CATS_IMP_EXP *uar_file[5];
 extern const char CATS_IMP_EXP *uar_create_temp[5];

diff --git a/bacula/src/dird/ua_purge.c b/bacula/src/dird/ua_purge.c
index 7d1dd4621d23f650cd2ea0e8df02360c76c5ab31..d4243686c9106945024ea79e60afa241ff896f92 100644 (file)
--- a/bacula/src/dird/ua_purge.c
+++ b/bacula/src/dird/ua_purge.c
@@ -387,21 +387,8 @@ void upgrade_copies(UAContext *ua, char *jobs)
    POOL_MEM query(PM_MESSAGE);
    
    db_lock(ua->db);
-   /* Do it in two times for mysql */
-   Mmsg(query, "CREATE TEMPORARY TABLE cpy_tmp AS "
-                  "SELECT MIN(JobId) AS JobId FROM Job "     /* Choose the oldest job */
-                   "WHERE Type='%c' "
-                     "AND ( PriorJobId IN (%s) "
-                         "OR "
-                          " PriorJobId IN ( "
-                             "SELECT PriorJobId "
-                               "FROM Job "
-                              "WHERE JobId IN (%s) "
-                               " AND Type='B' "
-                            ") "
-                         ") "
-                   "GROUP BY PriorJobId ",           /* one result per copy */
-        JT_JOB_COPY, jobs, jobs);
+
+   Mmsg(query, uap_upgrade_copies_oldest_job[db_type], JT_JOB_COPY, jobs, jobs);
    db_sql_query(ua->db, query.c_str(), NULL, (void *)NULL);
    Dmsg1(050, "Upgrade copies Log sql=%s\n", query.c_str());