From 5b6c5197ccabfd5ab97c2de41a806c04a265ae78 Mon Sep 17 00:00:00 2001 From: Eric Bollengier Date: Mon, 15 Nov 2010 23:04:53 +0100 Subject: [PATCH] bvfs: support basejobs and migration for PostgreSQL --- bacula/src/cats/bvfs.c | 112 +++++++++++++---------------------- bacula/src/cats/sql_cmds.c | 69 +++++++++++++++++++-- bacula/src/cats/sql_cmds.h | 1 + bacula/src/dird/ua_dotcmds.c | 25 +++++--- 4 files changed, 123 insertions(+), 84 deletions(-) diff --git a/bacula/src/cats/bvfs.c b/bacula/src/cats/bvfs.c index caeee64fa0..9dd5dd06f5 100644 --- a/bacula/src/cats/bvfs.c +++ b/bacula/src/cats/bvfs.c @@ -706,11 +706,29 @@ bool Bvfs::ls_dirs() return nb_record == limit; } +void build_ls_files_query(B_DB *db, POOL_MEM &query, + const char *JobId, const char *PathId, + const char *filter, int64_t limit, int64_t offset) +{ + if (db_type == SQL_TYPE_POSTGRESQL) { + Mmsg(query, sql_bvfs_list_files[db_type], + JobId, PathId, JobId, PathId, + filter, limit, offset); + } else { + Mmsg(query, sql_bvfs_list_files[db_type], + JobId, PathId, JobId, PathId, + limit, offset, filter, JobId, JobId); + } +} + /* Returns true if we have files to read */ bool Bvfs::ls_files() { + POOL_MEM query; + POOL_MEM filter; + char pathid[50]; + Dmsg1(dbglevel, "ls_files(%lld)\n", (uint64_t)pwd_id); - char ed1[50]; if (*jobids == 0) { return false; } @@ -719,76 +737,15 @@ bool Bvfs::ls_files() ch_dir(get_root()); } - POOL_MEM filter; + edit_uint64(pwd_id, pathid); if (*pattern) { Mmsg(filter, " AND Filename.Name %s '%s' ", SQL_MATCH, pattern); } - /* TODO: Use JobTDate instead of FileId to determine the latest version */ - -/* Postgresql - SELECT DISTINCT ON (FilenameId) 'F', PathId, T.FilenameId, - Filename.Name, JobId, LStat, FileId - FROM - (SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 - FROM File WHERE JobId IN (7) AND PathId = 9 - UNION ALL - SELECT File.FileId, File.JobId, PathId, FilenameId, - File.FileIndex, LStat, MD5 - FROM BaseFiles JOIN File USING (FileId) - WHERE BaseFiles.JobId IN (7) AND File.PathId = 9 - ) AS T JOIN Job USING (JobId) JOIN Filename USING (FilenameId) - ORDER BY FilenameId, StartTime DESC - -Mysql -SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, - File.FilenameId AS FilenameId, Filename.Name, LStat, MD5 -FROM Job, File, ( - SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId - FROM ( - SELECT JobTDate, PathId, FilenameId - FROM File JOIN Job USING (JobId) - WHERE File.JobId IN (7) AND PathId = 9 - UNION ALL - SELECT JobTDate, PathId, FilenameId - FROM BaseFiles - JOIN File USING (FileId) - JOIN Job ON (BaseJobId = Job.JobId) - WHERE BaseFiles.JobId IN (7) AND PathId = 9 - ) AS tmp GROUP BY PathId, FilenameId - ) AS T1 JOIN Filename USING (FilenameId) -WHERE (Job.JobId IN ( - SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (7)) - OR Job.JobId IN (7)) - AND T1.JobTDate = Job.JobTDate - AND Job.JobId = File.JobId - AND T1.PathId = File.PathId - AND T1.FilenameId = File.FilenameId + build_ls_files_query(db, query, + jobids, pathid, filter.c_str(), + limit, offset); - -*/ - - POOL_MEM query; - Mmsg(query, // 1 2 3 4 -"SELECT 'F', File.PathId, File.FilenameId, listfiles.Name, File.JobId, " - "File.LStat, listfiles.id " -"FROM File, ( " - "SELECT Filename.Name as Name, max(File.FileId) as id " - "FROM File, Filename " - "WHERE File.FilenameId = Filename.FilenameId " - "AND Filename.Name != '' " - "AND File.PathId = %s " - "AND File.JobId IN (%s) " - "%s " - "GROUP BY Filename.Name " - "ORDER BY Filename.Name LIMIT %d OFFSET %d " - ") AS listfiles " -"WHERE File.FileId = listfiles.id", - edit_uint64(pwd_id, ed1), - jobids, - filter.c_str(), - limit, - offset); Dmsg1(dbglevel_sql, "q=%s\n", query.c_str()); db_lock(db); @@ -889,8 +846,9 @@ bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink, if (*fileid) { init=true; - Mmsg(tmp, "(SELECT JobId, FileIndex, FilenameId, PathId, FileId " - "FROM File WHERE FileId IN (%s))", fileid); + Mmsg(tmp,"(SELECT JobId, JobTDate, FileIndex, FilenameId, PathId, FileId " + "FROM File JOIN Job USING (JobId) WHERE FileId IN (%s))", + fileid); pm_strcat(query, tmp.c_str()); } @@ -928,9 +886,10 @@ bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink, if (init) { query.strcat(" UNION "); } - Mmsg(tmp, "(SELECT File.JobId, File.FileIndex, File.FilenameId, " + /* TODO: Add basejobs here */ + Mmsg(tmp, "(SELECT JobId, JobTDate, File.FileIndex, File.FilenameId, " "File.PathId, FileId " - "FROM Path JOIN File USING (PathId) " + "FROM Path JOIN File USING (PathId) JOIN Job USING (JobId) " "WHERE Path.Path LIKE '%s' AND File.JobId IN (%s)) ", tmp2.c_str(), jobids); query.strcat(tmp.c_str()); @@ -953,8 +912,9 @@ bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink, tmp.strcat(")) UNION "); query.strcat(tmp.c_str()); } - Mmsg(tmp, "(SELECT JobId, FileIndex, FilenameId, PathId, FileId " - "FROM File WHERE JobId = %lld " + Mmsg(tmp, "(SELECT JobId, JobTDate, FileIndex, FilenameId, " + "PathId, FileId " + "FROM File JOIN Job USING (JobId) WHERE JobId = %lld " "AND FileIndex IN (%lld", jobid, id); prev_jobid = jobid; @@ -980,11 +940,19 @@ bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink, /* TODO: handle basejob and SQLite3 */ Mmsg(query, sql_bvfs_select[db_type], output_table, output_table); + /* TODO: handle jobid filter */ Dmsg1(dbglevel_sql, "q=%s\n", query.c_str()); if (!db_sql_query(db, query.c_str(), NULL, NULL)) { Dmsg0(dbglevel, "Can't execute q\n"); goto bail_out; } + + /* MySQL need it */ + if (db_type == SQL_TYPE_MYSQL) { + Mmsg(query, "CREATE INDEX idx_%s ON b2%s (JobId)", + output_table, output_table); + } + ret = true; bail_out: diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index 48c27152bb..e94298c7a5 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -919,7 +919,7 @@ const uint32_t sql_get_max_connections_index[5] = { const char *sql_bvfs_select[5] = { /* Mysql */ "CREATE TABLE %s AS ( " - "SELECT max(JobId) as JobId, FileIndex, FileId " + "SELECT JobId, FileIndex, FileId, max(JobTDate) as JobTDate " "FROM btemp%s " "GROUP BY PathId, FilenameId " "HAVING FileIndex > 0)", @@ -927,9 +927,10 @@ const char *sql_bvfs_select[5] = { "CREATE TABLE %s AS ( " "SELECT JobId, FileIndex, FileId " "FROM ( " - "SELECT DISTINCT ON (PathId, FilenameId) JobId, FileIndex, FileId " - "FROM btemp%s " - "ORDER BY PathId, FilenameId, JobId DESC " + "SELECT DISTINCT ON (PathId, FilenameId) " + "JobId, FileIndex, FileId " + "FROM btemp%s " + "ORDER BY PathId, FilenameId, JobTDate DESC " ") AS T " "WHERE FileIndex > 0)", /* SQLite */ @@ -939,3 +940,63 @@ const char *sql_bvfs_select[5] = { /* Ingres (TODO) */ "SELECT 0" }; + +const char *sql_bvfs_list_files_default = +"SELECT 'F', PathId, T1.FilenameId, Filename.Name, " + "File.JobId, LStat, FileId " +"FROM Job, File, ( " + "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId " + "FROM ( " + "SELECT JobTDate, PathId, FilenameId " + "FROM File JOIN Job USING (JobId) " + "WHERE File.JobId IN (%s) AND PathId = %s " + "UNION ALL " + "SELECT JobTDate, PathId, FilenameId " + "FROM BaseFiles " + "JOIN File USING (FileId) " + "JOIN Job ON (BaseJobId = Job.JobId) " + "WHERE BaseFiles.JobId IN (%s) AND PathId = %s " + ") AS tmp GROUP BY PathId, FilenameId LIMIT %lld OFFSET %lld" + ") AS T1 JOIN Filename USING (FilenameId) " +"WHERE T1.JobTDate = Job.JobTDate " + "AND Job.JobId = File.JobId " + "AND T1.PathId = File.PathId " + "AND T1.FilenameId = File.FilenameId " + "AND Filename.Name != '' " + " %s " /* AND Name LIKE '' */ + "AND (Job.JobId IN ( " + "SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) " + "OR Job.JobId IN (%s)) "; + +const char *sql_bvfs_list_files[] = { + /* Mysql */ +/* JobId PathId JobId PathId Limit Offset AND? Filename? JobId JobId*/ + sql_bvfs_list_files_default, + +/* JobId PathId JobId PathId WHERE? Filename? Limit Offset*/ + /* Postgresql */ + "SELECT DISTINCT ON (FilenameId) 'F', PathId, T.FilenameId, " + "Filename.Name, JobId, LStat, FileId " + "FROM " + "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 " + "FROM File WHERE JobId IN (%s) AND PathId = %s " + "UNION ALL " + "SELECT File.FileId, File.JobId, PathId, FilenameId, " + "File.FileIndex, LStat, MD5 " + "FROM BaseFiles JOIN File USING (FileId) " + "WHERE BaseFiles.JobId IN (%s) AND File.PathId = %s " + ") AS T JOIN Job USING (JobId) JOIN Filename USING (FilenameId) " + " WHERE Filename.Name != '' " + " %s " /* AND Name LIKE '' */ + "ORDER BY FilenameId, StartTime DESC LIMIT %lld OFFSET %lld", + + /* SQLite */ + sql_bvfs_list_files_default, + + /* SQLite3 */ + sql_bvfs_list_files_default, + + /* Ingres (TODO) */ + sql_bvfs_list_files_default +}; + diff --git a/bacula/src/cats/sql_cmds.h b/bacula/src/cats/sql_cmds.h index 1446d800e0..51d7b4c594 100644 --- a/bacula/src/cats/sql_cmds.h +++ b/bacula/src/cats/sql_cmds.h @@ -83,3 +83,4 @@ extern const char CATS_IMP_EXP *sql_media_order_most_recently_written[5]; extern const char CATS_IMP_EXP *sql_get_max_connections[5]; extern const uint32_t CATS_IMP_EXP sql_get_max_connections_index[5]; extern const char *sql_bvfs_select[5]; +extern const char *sql_bvfs_list_files[]; diff --git a/bacula/src/dird/ua_dotcmds.c b/bacula/src/dird/ua_dotcmds.c index 1677435338..72645b1c91 100644 --- a/bacula/src/dird/ua_dotcmds.c +++ b/bacula/src/dird/ua_dotcmds.c @@ -269,6 +269,7 @@ static bool bvfs_parse_arg_version(UAContext *ua, static bool bvfs_parse_arg(UAContext *ua, DBId_t *pathid, char **path, char **jobid, + char **username, int *limit, int *offset) { *pathid=0; @@ -276,6 +277,7 @@ static bool bvfs_parse_arg(UAContext *ua, *offset=0; *path=NULL; *jobid=NULL; + *username=NULL; for (int i=1; iargc; i++) { if (strcasecmp(ua->argk[i], NT_("pathid")) == 0) { @@ -287,6 +289,10 @@ static bool bvfs_parse_arg(UAContext *ua, if (strcasecmp(ua->argk[i], NT_("path")) == 0) { *path = ua->argv[i]; } + + if (strcasecmp(ua->argk[i], NT_("username")) == 0) { + *username = ua->argv[i]; + } if (strcasecmp(ua->argk[i], NT_("jobid")) == 0) { if (is_a_number_list(ua->argv[i])) { @@ -337,12 +343,12 @@ static bool dot_bvfs_restore(UAContext *ua, const char *cmd) { DBId_t pathid=0; int limit=2000, offset=0, i; - char *path=NULL, *jobid=NULL; + char *path=NULL, *jobid=NULL, *username=NULL; char *empty = (char *)""; char *fileid, *dirid, *hardlink, *id; id = fileid = dirid = hardlink = empty; - if (!bvfs_parse_arg(ua, &pathid, &path, &jobid, + if (!bvfs_parse_arg(ua, &pathid, &path, &jobid, &username, &limit, &offset)) { ua->error_msg("Can't find jobid, pathid or path argument\n"); @@ -350,6 +356,7 @@ static bool dot_bvfs_restore(UAContext *ua, const char *cmd) } Bvfs fs(ua->jcr, ua->db); + fs.set_username(username); fs.set_jobids(jobid); if ((i = find_arg_with_value(ua, "fileid")) >= 0) { @@ -378,11 +385,11 @@ static bool dot_bvfs_lsfiles(UAContext *ua, const char *cmd) { DBId_t pathid=0; int limit=2000, offset=0; - char *path=NULL, *jobid=NULL; + char *path=NULL, *jobid=NULL, *username=NULL; char *pattern=NULL; int i; - if (!bvfs_parse_arg(ua, &pathid, &path, &jobid, + if (!bvfs_parse_arg(ua, &pathid, &path, &jobid, &username, &limit, &offset)) { ua->error_msg("Can't find jobid, pathid or path argument\n"); @@ -393,6 +400,7 @@ static bool dot_bvfs_lsfiles(UAContext *ua, const char *cmd) } Bvfs fs(ua->jcr, ua->db); + fs.set_username(username); fs.set_jobids(jobid); fs.set_handler(bvfs_result_handler, ua); fs.set_limit(limit); @@ -421,9 +429,9 @@ static bool dot_bvfs_lsdirs(UAContext *ua, const char *cmd) { DBId_t pathid=0; int limit=2000, offset=0; - char *path=NULL, *jobid=NULL; + char *path=NULL, *jobid=NULL, *username=NULL; - if (!bvfs_parse_arg(ua, &pathid, &path, &jobid, + if (!bvfs_parse_arg(ua, &pathid, &path, &jobid, &username, &limit, &offset)) { ua->error_msg("Can't find jobid, pathid or path argument\n"); @@ -431,6 +439,7 @@ static bool dot_bvfs_lsdirs(UAContext *ua, const char *cmd) } Bvfs fs(ua->jcr, ua->db); + fs.set_username(username); fs.set_jobids(jobid); fs.set_limit(limit); fs.set_handler(bvfs_result_handler, ua); @@ -457,9 +466,9 @@ static bool dot_bvfs_versions(UAContext *ua, const char *cmd) { DBId_t pathid=0, fnid=0; int limit=2000, offset=0; - char *path=NULL, *jobid=NULL, *client=NULL; + char *path=NULL, *jobid=NULL, *client=NULL, *username=NULL; bool copies=false, versions=false; - if (!bvfs_parse_arg(ua, &pathid, &path, &jobid, + if (!bvfs_parse_arg(ua, &pathid, &path, &jobid, &username, &limit, &offset)) { ua->error_msg("Can't find jobid, pathid or path argument\n"); -- 2.39.5