From d2099de12f8fef038820ceca64928cb7c68d7128 Mon Sep 17 00:00:00 2001 From: Eric Bollengier Date: Wed, 16 Sep 2009 20:40:52 +0200 Subject: [PATCH] update db_get_file_list() to use StartTime instead of FileIndex --- bacula/src/cats/sql_cmds.c | 46 ++++++++++++++++++++++++++++++++++++++ bacula/src/cats/sql_cmds.h | 1 + bacula/src/cats/sql_get.c | 22 ++++++------------ 3 files changed, 54 insertions(+), 15 deletions(-) diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index 4ff0c6e9e4..4276fc4897 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -450,6 +450,52 @@ 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 */ +const char *select_recent_version[4] = { + /* MySQL */ + "SELECT max(StartTime), Job.JobId, FileIndex, PathId, FilenameId, LStat, MD5 " + "FROM " + "(SELECT JobId, PathId, FilenameId, FileIndex, LStat, MD5 FROM File WHERE JobId IN (%s) " + "UNION ALL " + "SELECT BaseFiles.JobId, PathId, FilenameId, FileIndex, LStat, MD5 " + "FROM BaseFiles JOIN File USING (FileId) " + "WHERE BaseFiles.JobId IN (%s) " + ") AS T JOIN Job USING (JobId) " + "GROUP BY PathId, FilenameId ", + + /* Postgresql */ + "SELECT DISTINCT ON (FilenameId, PathId) StartTime, JobId, FileId, FileIndex, PathId, FilenameId, LStat, MD5 " + "FROM " + "(SELECT JobId, PathId, FilenameId, FileIndex, LStat, MD5 FROM File WHERE JobId IN (%s) " + "UNION ALL " + "SELECT BaseFiles.JobId, PathId, FilenameId, FileIndex, LStat, MD5 " + "FROM BaseFiles JOIN File USING (FileId) " + "WHERE BaseFiles.JobId IN (%s) " + ") AS T JOIN Job USING (JobId) " + "ORDER BY FilenameId, PathId, StartTime DESC ", + + /* SQLite */ + "SELECT max(StartTime), JobId, FileId, FileIndex, PathId, FilenameId, LStat " + "FROM " + "(SELECT JobId, PathId, FilenameId, FileIndex, LStat, MD5 FROM File WHERE JobId IN (%s) " + "UNION ALL " + "SELECT BaseFiles.JobId, PathId, FilenameId, FileIndex, LStat, MD5 " + "FROM BaseFiles JOIN File USING (FileId) " + "WHERE BaseFiles.JobId IN (%s) " + ") AS T JOIN Job USING (JobId) " + "GROUP BY PathId, FilenameId ", + + /* SQLite3 */ + "SELECT max(StartTime), JobId, FileId, FileIndex, PathId, FilenameId, LStat " + "(SELECT JobId, PathId, FilenameId, FileIndex, LStat, MD5 FROM File WHERE JobId IN (%s) " + "UNION ALL " + "SELECT BaseFiles.JobId, PathId, FilenameId, FileIndex, LStat, MD5 " + "FROM BaseFiles JOIN File USING (FileId) " + "WHERE BaseFiles.JobId IN (%s) " + ") AS T JOIN Job USING (JobId) " + "GROUP BY PathId, FilenameId " +}; + /* ====== ua_prune.c */ /* List of SQL commands to create temp table and indicies */ diff --git a/bacula/src/cats/sql_cmds.h b/bacula/src/cats/sql_cmds.h index a5d8054e48..6f1abaefaa 100644 --- a/bacula/src/cats/sql_cmds.h +++ b/bacula/src/cats/sql_cmds.h @@ -73,6 +73,7 @@ extern const char CATS_IMP_EXP *uar_jobids_fileindex; extern const char CATS_IMP_EXP *uar_jobid_fileindex_from_table; extern const char CATS_IMP_EXP *uar_sel_jobid_temp; +extern const char CATS_IMP_EXP *select_recent_version[4]; extern const char CATS_IMP_EXP *create_deltabs[4]; extern const char CATS_IMP_EXP *uar_file[4]; diff --git a/bacula/src/cats/sql_get.c b/bacula/src/cats/sql_get.c index 2e32882baf..577f79d3a3 100644 --- a/bacula/src/cats/sql_get.c +++ b/bacula/src/cats/sql_get.c @@ -1067,25 +1067,17 @@ 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[db_type], jobids, jobids); Mmsg(buf, - "SELECT Path.Path, Filename.Name, File.FileIndex, File.JobId, " - "File.LStat, File.MD5 " - "FROM ( " - "SELECT max(FileId) as FileId, PathId, FilenameId " - "FROM (SELECT FileId, PathId, FilenameId FROM File WHERE JobId IN (%s) " - "UNION ALL " /* we already sort after */ - "SELECT File.FileId, PathId, FilenameId " - "FROM BaseFiles JOIN File USING (FileId) " - "WHERE BaseFiles.JobId IN (%s) " - ") AS F " - "GROUP BY PathId, FilenameId " - ") AS Temp " + "SELECT Path.Path, Filename.Name, FileIndex, JobId, " + "LStat, MD5 " + "FROM ( %s ) AS TEMP " "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) " "JOIN Path ON (Path.PathId = Temp.PathId) " - "JOIN File ON (File.FileId = Temp.FileId) " -"WHERE File.FileIndex > 0 ORDER BY JobId, FileIndex ASC",/* Return sorted by JobId, */ +"WHERE FileIndex > 0 ORDER BY JobId, FileIndex ASC",/* Return sorted by JobId, */ /* FileIndex for restore code */ - jobids, jobids); + buf2.c_str()); #else /* * I am not sure that this works the same as the code in ua_restore.c but it -- 2.39.5