From 8b6b1cea3cb9bd5a2a36c3fb5ed88da5498de8ee Mon Sep 17 00:00:00 2001 From: Marco van Wieringen Date: Fri, 30 Apr 2010 20:05:05 +0200 Subject: [PATCH] Don't hardcode temporary table creation as some backend have a somewhat different SQL syntax. --- bacula/src/cats/sql_cmds.c | 43 ++++++++++++++++++++++++++++++++++++++ bacula/src/cats/sql_cmds.h | 1 + bacula/src/dird/ua_purge.c | 17 ++------------- 3 files changed, 46 insertions(+), 15 deletions(-) diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index 7ed398e2ca..6ee166d7c7 100644 --- 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 e984fe6eb0..5b9b6cb882 100644 --- 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 7d1dd4621d..d4243686c9 100644 --- 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()); -- 2.39.2