From f16b4c89f42a9e904088c77dcf9cab81572a594e Mon Sep 17 00:00:00 2001 From: Eric Bollengier Date: Thu, 17 Sep 2009 15:54:45 +0200 Subject: [PATCH] Restrict Join using JobTDate to a selection of JobIds --- bacula/src/cats/sql_cmds.c | 37 +++++++++++++++++++++++-------------- bacula/src/cats/sql_get.c | 1 + 2 files changed, 24 insertions(+), 14 deletions(-) diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index afa5276822..9f7687cd8e 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -467,21 +467,24 @@ const char *select_recent_version_with_basejob[4] = { "FROM Job, File, ( " "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId " "FROM ( " - "SELECT JobTDate, PathId, FilenameId " /* Get all files files */ - "FROM File JOIN Job USING (JobId) " /* from selected backup */ + "SELECT JobTDate, PathId, FilenameId " + "FROM File JOIN Job USING (JobId) " "WHERE JobId IN (%s) " - "UNION ALL " - "SELECT JobTDate, PathId, FilenameId " /* Get all files from */ - "FROM BaseFiles " /* BaseJob */ + "UNION ALL " + "SELECT JobTDate, PathId, FilenameId " + "FROM BaseFiles " "JOIN File USING (FileId) " "JOIN Job ON (BaseJobId = Job.JobId) " "WHERE BaseFiles.JobId IN (%s) " - ") AS tmp GROUP BY PathId, FilenameId " /* Use Max(JobTDate) to find */ - ") AS T1 " /* the latest version */ -"WHERE Job.JobId IN (%s) " - "AND Job.JobTDate = T1.JobTDate " /* Join on JobTDate to get the orginal */ - "AND File.PathId = T1.PathId " /* Job/File record */ - "AND File.FilenameId = T1.PathId ", + ") AS tmp GROUP BY PathId, FilenameId " + ") AS T1 " +"WHERE (Job.JobId IN ( + SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) + OR Job.JobId IN (%s)) " + "AND Job.JobId = File.JobId " + "AND Job.JobTDate = T1.JobTDate " + "AND File.PathId = T1.PathId " + "AND File.FilenameId = T1.PathId " /* Postgresql */ /* The DISTINCT ON () permits to avoid extra join */ "SELECT DISTINCT ON (FilenameId, PathId) StartTime, JobId, FileId, " @@ -514,7 +517,10 @@ const char *select_recent_version_with_basejob[4] = { "WHERE BaseFiles.JobId IN (%s) " ") AS tmp GROUP BY PathId, FilenameId " ") AS T1 " -"WHERE Job.JobId IN (%s) " +"WHERE (Job.JobId IN ( + SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) + OR Job.JobId IN (%s)) " + "AND Job.JobId = File.JobId " "AND Job.JobTDate = T1.JobTDate " "AND File.PathId = T1.PathId " "AND File.FilenameId = T1.PathId ", @@ -528,7 +534,7 @@ const char *select_recent_version_with_basejob[4] = { "SELECT JobTDate, PathId, FilenameId " "FROM File JOIN Job USING (JobId) " "WHERE JobId IN (%s) " - "UNION ALL " + "UNION ALL " "SELECT JobTDate, PathId, FilenameId " "FROM BaseFiles " "JOIN File USING (FileId) " @@ -536,7 +542,10 @@ const char *select_recent_version_with_basejob[4] = { "WHERE BaseFiles.JobId IN (%s) " ") AS tmp GROUP BY PathId, FilenameId " ") AS T1 " -"WHERE Job.JobId IN (%s) " +"WHERE (Job.JobId IN ( + SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) + OR Job.JobId IN (%s)) " + "AND Job.JobId = File.JobId " "AND Job.JobTDate = T1.JobTDate " "AND File.PathId = T1.PathId " "AND File.FilenameId = T1.PathId " diff --git a/bacula/src/cats/sql_get.c b/bacula/src/cats/sql_get.c index b0e795e4f8..1e5b569a15 100644 --- a/bacula/src/cats/sql_get.c +++ b/bacula/src/cats/sql_get.c @@ -1079,6 +1079,7 @@ bool db_get_file_list(JCR *jcr, B_DB *mdb, char *jobids, "ORDER BY Temp.JobId, FileIndex ASC",/* Return sorted by JobId, */ /* FileIndex for restore code */ buf2.c_str()); + Dmsg1(0, "sql=%s\n", buf.c_str()); #else /* * I am not sure that this works the same as the code in ua_restore.c but it -- 2.39.5