From 343fe78bb4eb571fcdcefbdeb5e717de8b9ec8d7 Mon Sep 17 00:00:00 2001 From: Eric Bollengier Date: Thu, 17 Sep 2009 15:13:53 +0200 Subject: [PATCH] Fix and document new queries --- bacula/src/cats/sql_cmds.c | 42 +++++++++++++++++++++++------------- bacula/src/cats/sql_create.c | 4 ++-- bacula/src/cats/sql_get.c | 3 ++- 3 files changed, 31 insertions(+), 18 deletions(-) diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index e58c6a5e1e..18c5362bd1 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -450,30 +450,39 @@ const char *uar_jobid_fileindex_from_table = #include "bacula.h" #include "cats.h" -/* Get the list of the last recent version with a given jobid list */ +/* Get the list of the last recent version with a given jobid list + * This is a tricky part because with SQL the result of + * + * SELECT MAX(A), B, C, D FROM... GROUP BY (B,C) + * + * doesn't give the good result (for D). + * + * With PostgreSQL, we can use DISTINCT ON(), but with Mysql or Sqlite, + * we need an extra join using JobTDate. + */ const char *select_recent_version_with_basejob[4] = { /* MySQL */ "SELECT FileId, Job.JobId, FileIndex, File.PathId, File.FilenameId, LStat, MD5 " "FROM Job, File, ( " "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId " "FROM ( " - "SELECT JobTDate, PathId, FilenameId " - "FROM File JOIN Job USING (JobId) " + "SELECT JobTDate, PathId, FilenameId " /* Get all files files */ + "FROM File JOIN Job USING (JobId) " /* from selected backup */ "WHERE JobId IN (%s) " "UNION ALL " - "SELECT JobTDate, PathId, FilenameId " - "FROM BaseFiles " + "SELECT JobTDate, PathId, FilenameId " /* Get all files from */ + "FROM BaseFiles " /* BaseJob */ "JOIN File USING (FileId) " "JOIN Job ON (BaseJobId = Job.JobId) " "WHERE BaseFiles.JobId IN (%s) " - ") AS temp GROUP BY PathId, FilenameId " - ") AS T1 " + ") AS temp GROUP BY PathId, FilenameId " /* Use Max(JobTDate) to find */ + ") AS T1 " /* the latest version */ "WHERE Job.JobId IN (%s) " - "AND Job.JobTDate = T1.JobTDate " - "AND File.PathId = T1.PathId " + "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 ", - /* Postgresql */ + /* Postgresql */ /* The DISTINCT ON () permits to avoid extra join */ "SELECT DISTINCT ON (FilenameId, PathId) StartTime, JobId, FileId, " "FileIndex, PathId, FilenameId, LStat, MD5 " "FROM " @@ -487,7 +496,7 @@ const char *select_recent_version_with_basejob[4] = { ") AS T JOIN Job USING (JobId) " "ORDER BY FilenameId, PathId, StartTime DESC ", - /* SQLite */ + /* SQLite */ /* See Mysql section for doc */ "SELECT FileId, Job.JobId, FileIndex, File.PathId, File.FilenameId, LStat, MD5 " "FROM Job, File, ( " "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId " @@ -508,7 +517,7 @@ const char *select_recent_version_with_basejob[4] = { "AND File.PathId = T1.PathId " "AND File.FilenameId = T1.PathId ", - /* SQLite3 */ + /* SQLite3 */ /* See Mysql section for doc */ "SELECT FileId, Job.JobId, FileIndex, File.PathId, File.FilenameId, LStat, MD5 " "FROM Job, File, ( " "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId " @@ -534,7 +543,8 @@ const char *select_recent_version_with_basejob[4] = { const char *select_recent_version[4] = { /* MySQL */ "SELECT j1.JobId AS JobId, f1.FileId AS FileId, f1.FileIndex AS FileIndex, " - "f1.PathId AS PathId, f1.FilenameId AS FilenameId, f1.LStat AS LStat " + "f1.PathId AS PathId, f1.FilenameId AS FilenameId, " + "f1.LStat AS LStat, f1.MD5 AS MD5 " "FROM ( " /* Choose the last version for each Path/Filename */ "SELECT max(JobTDate) AS JobTDate, PathId, FilenameId " "FROM File JOIN Job USING (JobId) " @@ -556,7 +566,8 @@ const char *select_recent_version[4] = { /* SQLite */ "SELECT j1.JobId AS JobId, f1.FileId AS FileId, f1.FileIndex AS FileIndex, " - "f1.PathId AS PathId, f1.FilenameId AS FilenameId, f1.LStat AS LStat " + "f1.PathId AS PathId, f1.FilenameId AS FilenameId, " + "f1.LStat AS LStat, f1.MD5 AS MD5 " "FROM ( " "SELECT max(JobTDate) AS JobTDate, PathId, FilenameId " "FROM File JOIN Job USING (JobId) " @@ -571,7 +582,8 @@ const char *select_recent_version[4] = { /* SQLite3 */ "SELECT j1.JobId AS JobId, f1.FileId AS FileId, f1.FileIndex AS FileIndex, " - "f1.PathId AS PathId, f1.FilenameId AS FilenameId, f1.LStat AS LStat " + "f1.PathId AS PathId, f1.FilenameId AS FilenameId, " + "f1.LStat AS LStat, f1.MD5 AS MD5 " "FROM ( " "SELECT max(JobTDate) AS JobTDate, PathId, FilenameId " "FROM File JOIN Job USING (JobId) " diff --git a/bacula/src/cats/sql_create.c b/bacula/src/cats/sql_create.c index 035ebc8dde..71f21f9332 100644 --- a/bacula/src/cats/sql_create.c +++ b/bacula/src/cats/sql_create.c @@ -1232,8 +1232,7 @@ bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids) if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) { goto bail_out; } - - Mmsg(buf, select_recent_version[db_type], jobids); + Mmsg(buf, select_recent_version[db_type], jobids, jobids); Mmsg(mdb->cmd, "CREATE TEMPORARY TABLE new_basefile%lld AS " //"CREATE TABLE new_basefile%lld AS " @@ -1245,6 +1244,7 @@ bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids) "JOIN Path ON (Path.PathId = Temp.PathId) " "WHERE Temp.FileIndex > 0", (uint64_t)jcr->JobId, buf.c_str()); + ret = db_sql_query(mdb, mdb->cmd, NULL, NULL); bail_out: db_unlock(mdb); diff --git a/bacula/src/cats/sql_get.c b/bacula/src/cats/sql_get.c index 73c631b6ed..6da66a75bd 100644 --- a/bacula/src/cats/sql_get.c +++ b/bacula/src/cats/sql_get.c @@ -1068,7 +1068,8 @@ bool db_get_file_list(JCR *jcr, B_DB *mdb, char *jobids, #define new_db_get_file_list #ifdef new_db_get_file_list POOL_MEM buf2(PM_MESSAGE); - Mmsg(buf2, select_recent_version_with_basejob[db_type], jobids, jobids); + Mmsg(buf2, select_recent_version_with_basejob[db_type], + jobids, jobids, jobids); Mmsg(buf, "SELECT Path.Path, Filename.Name, FileIndex, JobId, " "LStat, MD5 " -- 2.39.5