From f5e1f15c94a4828f6d0bb6fc3042d99d6e2fe415 Mon Sep 17 00:00:00 2001 From: Eric Bollengier Date: Thu, 17 Sep 2009 14:46:22 +0200 Subject: [PATCH] Use new query for Mysql and Sqlite for basejob + accurate --- bacula/src/cats/sql_cmds.c | 87 +++++++++++++++++++++++++------------- 1 file changed, 57 insertions(+), 30 deletions(-) diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index b4efefa061..89e5e3b66a 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -453,17 +453,26 @@ const char *uar_jobid_fileindex_from_table = /* Get the list of the last recent version with a given jobid list */ const char *select_recent_version_with_basejob[4] = { /* MySQL */ - "SELECT max(FileId), JobId, FileIndex, PathId, FilenameId, LStat, MD5 " - "FROM " - "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 " - "FROM File WHERE JobId IN (%s) " - "UNION ALL " - "SELECT File.FileId, File.JobId, PathId, FilenameId, " - "File.FileIndex, LStat, MD5 " - "FROM BaseFiles JOIN File USING (FileId) " +"SELECT FileId, Job.JobId, FileIndex, File.PathId, File.FilenameId, LStat, MD5 " +"FROM ( " + "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId " + "FROM ( " + "SELECT JobTDate, PathId, FilenameId " + "FROM File JOIN Job USING (JobId) " + "WHERE JobId IN (%s) " + "UNION ALL " + "SELECT JobTDate, PathId, FilenameId " + "FROM BaseFiles " + "JOIN File USING (FileId) " + "JOIN Job ON (BaseJobId = Job.JobId) " "WHERE BaseFiles.JobId IN (%s) " - ") AS T " - "GROUP BY PathId, FilenameId ", + ") AS temp GROUP BY PathId, FilenameId " + ") AS T1 " + "JOIN Job, File " +"WHERE Job.JobId IN (%s) " + "AND Job.JobTDate = T1.JobTDate " + "AND File.PathId = T1.PathId " + "AND File.FilenameId = T1.PathId ", /* Postgresql */ "SELECT DISTINCT ON (FilenameId, PathId) StartTime, JobId, FileId, " @@ -480,30 +489,48 @@ const char *select_recent_version_with_basejob[4] = { "ORDER BY FilenameId, PathId, StartTime DESC ", /* SQLite */ - "SELECT max(FileId), JobId, FileIndex, PathId, FilenameId, LStat, MD5 " - "FROM " - "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 " - "FROM File WHERE JobId IN (%s) " - "UNION ALL " - "SELECT File.FileId, File.JobId, PathId, FilenameId, " - "File.FileIndex, LStat, MD5 " - "FROM BaseFiles JOIN File USING (FileId) " +"SELECT FileId, Job.JobId, FileIndex, File.PathId, File.FilenameId, LStat, MD5 " +"FROM ( " + "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId " + "FROM ( " + "SELECT JobTDate, PathId, FilenameId " + "FROM File JOIN Job USING (JobId) " + "WHERE JobId IN (%s) " + "UNION ALL " + "SELECT JobTDate, PathId, FilenameId " + "FROM BaseFiles " + "JOIN File USING (FileId) " + "JOIN Job ON (BaseJobId = Job.JobId) " "WHERE BaseFiles.JobId IN (%s) " - ") AS T " - "GROUP BY PathId, FilenameId ", + ") AS temp GROUP BY PathId, FilenameId " + ") AS T1 " + "JOIN Job, File " +"WHERE Job.JobId IN (%s) " + "AND Job.JobTDate = T1.JobTDate " + "AND File.PathId = T1.PathId " + "AND File.FilenameId = T1.PathId ", /* SQLite3 */ - "SELECT max(FileId), JobId, FileIndex, PathId, FilenameId, LStat, MD5 " - "FROM " - "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 " - "FROM File WHERE JobId IN (%s) " - "UNION ALL " - "SELECT File.FileId, File.JobId, PathId, FilenameId, " - "File.FileIndex, LStat, MD5 " - "FROM BaseFiles JOIN File USING (FileId) " +"SELECT FileId, Job.JobId, FileIndex, File.PathId, File.FilenameId, LStat, MD5 " +"FROM ( " + "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId " + "FROM ( " + "SELECT JobTDate, PathId, FilenameId " + "FROM File JOIN Job USING (JobId) " + "WHERE JobId IN (%s) " + "UNION ALL " + "SELECT JobTDate, PathId, FilenameId " + "FROM BaseFiles " + "JOIN File USING (FileId) " + "JOIN Job ON (BaseJobId = Job.JobId) " "WHERE BaseFiles.JobId IN (%s) " - ") AS T " - "GROUP BY PathId, FilenameId " + ") AS temp GROUP BY PathId, FilenameId " + ") AS T1 " + "JOIN Job, File " +"WHERE Job.JobId IN (%s) " + "AND Job.JobTDate = T1.JobTDate " + "AND File.PathId = T1.PathId " + "AND File.FilenameId = T1.PathId " }; /* Get the list of the last recent version with a given BaseJob jobid list */ -- 2.39.5