From 7fdb814d74a6e7b277ee59297971fde09c8f097c Mon Sep 17 00:00:00 2001 From: Eric Bollengier Date: Sat, 1 May 2010 10:05:37 +0200 Subject: [PATCH] Rework sql queries for update copies --- bacula/src/cats/sql_cmds.c | 60 ++++++++++++++++++++------------------ bacula/src/dird/ua_purge.c | 3 +- 2 files changed, 33 insertions(+), 30 deletions(-) diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index 6ee166d7c7..88afd3e024 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -746,42 +746,44 @@ const char *create_deltabs[5] = { /* 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_default = +"CREATE TEMPORARY TABLE cpy_tmp AS " + "SELECT MIN(JobId) AS JobId FROM Job " /* Choose the oldest job */ + "WHERE Type='%c' " /* JT_JOB_COPY */ + "AND ( PriorJobId IN (%s) " /* JobId selection */ + "OR " + " PriorJobId IN ( " + "SELECT PriorJobId " + "FROM Job " + "WHERE JobId IN (%s) " /* JobId selection */ + " AND Type='B' " + ") " + ") " + "GROUP BY PriorJobId "; /* one result per copy */ + 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", + uap_upgrade_copies_oldest_job_default, /* 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", + uap_upgrade_copies_oldest_job_default, /* 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", + uap_upgrade_copies_oldest_job_default, /* 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", + uap_upgrade_copies_oldest_job_default, /* 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 " + "SELECT MIN(JobId) AS JobId FROM Job " /* Choose the oldest job */ + "WHERE Type='%c' " /* JT_JOB_COPY */ + "AND ( PriorJobId IN (%s) " /* JobId selection */ + "OR " + " PriorJobId IN ( " + "SELECT PriorJobId " + "FROM Job " + "WHERE JobId IN (%s) " /* JobId selection */ + " AND Type='B' " + ") " + ") " + "GROUP BY PriorJobId " /* one result per copy */ "ON COMMIT PRESERVE ROWS WITH NORECOVERY" }; diff --git a/bacula/src/dird/ua_purge.c b/bacula/src/dird/ua_purge.c index d4243686c9..539586d443 100644 --- a/bacula/src/dird/ua_purge.c +++ b/bacula/src/dird/ua_purge.c @@ -388,12 +388,13 @@ void upgrade_copies(UAContext *ua, char *jobs) db_lock(ua->db); + /* Do it in two times for mysql */ 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()); /* Now upgrade first copy to Backup */ - Mmsg(query, "UPDATE Job SET Type='B' " /* JT_JOB_COPY => JT_BACKUP */ + Mmsg(query, "UPDATE Job SET Type='B' " /* JT_JOB_COPY => JT_BACKUP */ "WHERE JobId IN ( SELECT JobId FROM cpy_tmp )"); db_sql_query(ua->db, query.c_str(), NULL, (void *)NULL); -- 2.39.5