From 8d1d139df977d07575451b928dd2b4bec4999ed9 Mon Sep 17 00:00:00 2001 From: Eric Bollengier Date: Wed, 16 Sep 2009 21:22:18 +0200 Subject: [PATCH] fix sql --- bacula/src/cats/sql_cmds.c | 54 +++++++++++++++++++++++++++--------- bacula/src/cats/sql_cmds.h | 1 + bacula/src/cats/sql_create.c | 20 ++++++------- bacula/src/cats/sql_get.c | 3 +- 4 files changed, 53 insertions(+), 25 deletions(-) diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index 4276fc4897..bd7c5bf5e3 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -293,7 +293,7 @@ const char *uar_list_jobs = "FROM Client,Job WHERE Client.ClientId=Job.ClientId AND JobStatus IN ('T','W') " "AND Type='B' ORDER BY StartTime DESC LIMIT 20"; -const char *uar_print_jobs = +const char *uar_print_jobs = "SELECT DISTINCT JobId,Level,JobFiles,JobBytes,StartTime,VolumeName" " FROM Job JOIN JobMedia USING (JobId) JOIN Media USING (MediaId) " " WHERE JobId IN (%s) " @@ -451,51 +451,79 @@ const char *uar_jobid_fileindex_from_table = #include "cats.h" /* Get the list of the last recent version with a given jobid list */ -const char *select_recent_version[4] = { +const char *select_recent_version_with_basejob[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) " + "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 FROM File WHERE JobId IN (%s) " "UNION ALL " - "SELECT BaseFiles.JobId, PathId, FilenameId, FileIndex, LStat, MD5 " + "SELECT File.FileId, BaseFiles.JobId, PathId, FilenameId, File.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 " + "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) " + "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 FROM File WHERE JobId IN (%s) " "UNION ALL " - "SELECT BaseFiles.JobId, PathId, FilenameId, FileIndex, LStat, MD5 " + "SELECT File.FileId, BaseFiles.JobId, PathId, FilenameId, File.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 " + "SELECT max(StartTime), JobId, FileIndex, PathId, FilenameId, LStat, MD5 " "FROM " - "(SELECT JobId, PathId, FilenameId, FileIndex, LStat, MD5 FROM File WHERE JobId IN (%s) " + "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 FROM File WHERE JobId IN (%s) " "UNION ALL " - "SELECT BaseFiles.JobId, PathId, FilenameId, FileIndex, LStat, MD5 " + "SELECT File.FileId, BaseFiles.JobId, PathId, FilenameId, File.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) " + "SELECT max(StartTime), JobId, FileIndex, PathId, FilenameId, LStat, MD5 " + "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 FROM File WHERE JobId IN (%s) " "UNION ALL " - "SELECT BaseFiles.JobId, PathId, FilenameId, FileIndex, LStat, MD5 " + "SELECT File.FileId, BaseFiles.JobId, PathId, FilenameId, File.FileIndex, LStat, MD5 " "FROM BaseFiles JOIN File USING (FileId) " "WHERE BaseFiles.JobId IN (%s) " ") AS T JOIN Job USING (JobId) " "GROUP BY PathId, FilenameId " }; +/* Get the list of the last recent version with a given BaseJob jobid list */ +const char *select_recent_version[4] = { + /* MySQL */ + "SELECT max(StartTime), JobId, FileId, FileIndex, PathId, FilenameId, LStat, MD5 " + "FROM File JOIN Job USING (JobId) " + "WHERE JobId IN (%s) " + "GROUP BY PathId, FilenameId", + + /* Postgresql */ + "SELECT DISTINCT ON (FilenameId, PathId) StartTime, JobId, FileId, FileIndex, PathId, FilenameId, LStat, MD5 " + "FROM File JOIN Job USING (JobId) " + "WHERE JobId IN (%s) " + "ORDER BY FilenameId, PathId, StartTime DESC", + + /* SQLite */ + "SELECT max(StartTime), JobId, FileId, FileIndex, PathId, FilenameId, LStat, MD5 " + "FROM File JOIN Job USING (JobId) " + "WHERE JobId IN (%s) " + "GROUP BY PathId, FilenameId", + + /* SQLite3 */ + "SELECT max(StartTime), JobId, FileId, FileIndex, PathId, FilenameId, LStat, MD5 " + "FROM File JOIN Job USING (JobId) " + "WHERE JobId IN (%s) " + "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 6f1abaefaa..5755cc8c29 100644 --- a/bacula/src/cats/sql_cmds.h +++ b/bacula/src/cats/sql_cmds.h @@ -74,6 +74,7 @@ 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 *select_recent_version_with_basejob[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_create.c b/bacula/src/cats/sql_create.c index 65cfb80b4d..f6b182f902 100644 --- a/bacula/src/cats/sql_create.c +++ b/bacula/src/cats/sql_create.c @@ -1217,6 +1217,7 @@ bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb) */ bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids) { + POOL_MEM buf; bool ret=false; db_lock(mdb); @@ -1231,22 +1232,19 @@ bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids) goto bail_out; } + Mmsg(buf, select_recent_version[db_type], jobids); + Mmsg(mdb->cmd, "CREATE TEMPORARY TABLE new_basefile%lld AS ( " //"CREATE TABLE new_basefile%lld AS ( " - "SELECT Path.Path AS Path, Filename.Name AS Name, File.FileIndex AS FileIndex," - "File.JobId AS JobId, File.LStat AS LStat, File.FileId AS FileId, " - "File.MD5 AS MD5 " - "FROM ( " - "SELECT max(FileId) as FileId, PathId, FilenameId " - "FROM (SELECT FileId, PathId, FilenameId FROM File WHERE JobId IN (%s)) AS F " - "GROUP BY PathId, FilenameId " - ") AS Temp " + "SELECT Path.Path AS Path, Filename.Name AS Name, Temp.FileIndex AS FileIndex," + "Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId, " + "Temp.MD5 AS 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)", - (uint64_t)jcr->JobId, jobids); + "WHERE Temp.FileIndex > 0)", + (uint64_t)jcr->JobId, buf.c_str()); ret = QUERY_DB(jcr, mdb, mdb->cmd); bail_out: db_unlock(mdb); diff --git a/bacula/src/cats/sql_get.c b/bacula/src/cats/sql_get.c index 577f79d3a3..ffadb55be7 100644 --- a/bacula/src/cats/sql_get.c +++ b/bacula/src/cats/sql_get.c @@ -1068,7 +1068,7 @@ 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(buf2, select_recent_version_with_basejob[db_type], jobids, jobids); Mmsg(buf, "SELECT Path.Path, Filename.Name, FileIndex, JobId, " "LStat, MD5 " @@ -1216,6 +1216,7 @@ bool db_get_base_file_list(JCR *jcr, B_DB *mdb, return db_sql_query(mdb, buf.c_str(), result_handler, ctx); } + bool db_get_base_jobid(JCR *jcr, B_DB *mdb, JOB_DBR *jr, JobId_t *jobid) { char date[MAX_TIME_LENGTH]; -- 2.39.5