From faf2eb804180807fbebc0c20a0b47ccb43c383e5 Mon Sep 17 00:00:00 2001 From: Marco van Wieringen Date: Fri, 16 Apr 2010 10:07:09 +0200 Subject: [PATCH] Seperate some basejob queries into sql_cmds.c so the code is cleaner and we can specify the Ingres specific definition of a temporary table. --- bacula/src/cats/sql_cmds.c | 75 +++++++++++++++++++++++++++++++++++- bacula/src/cats/sql_cmds.h | 2 + bacula/src/cats/sql_create.c | 45 +--------------------- 3 files changed, 76 insertions(+), 46 deletions(-) diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index 7cb83313e5..c6edd8bc66 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -26,12 +26,10 @@ Switzerland, email:ftf@fsfeurope.org. */ /* - * * This file contains all the SQL commands that are either issued by the * Director or which are database backend specific. * * Kern Sibbald, July MMII - * */ /* * Note, PostgreSQL imposes some constraints on using DISTINCT and GROUP BY @@ -629,6 +627,79 @@ const char *select_recent_version[5] = { "AND j1.JobId = f1.JobId" }; +const char *create_temp_basefile[5] = { + /* Mysql */ + "CREATE TEMPORARY TABLE basefile%lld (" + "Path BLOB NOT NULL," + "Name BLOB NOT NULL)", + /* Postgresql */ + "CREATE TEMPORARY TABLE basefile%lld (" + "Path TEXT," + "Name TEXT)", + /* SQLite */ + "CREATE TEMPORARY TABLE basefile%lld (" + "Path TEXT," + "Name TEXT)", + /* SQLite3 */ + "CREATE TEMPORARY TABLE basefile%lld (" + "Path TEXT," + "Name TEXT)", + /* Ingres */ + "DECLARE GLOBAL TEMPORARY TABLE basefile%lld (" + "Path TEXT NOT NULL," + "Name TEXT NOT NULL)" + "ON COMMIT PRESERVE ROWS WITH NORECOVERY" +}; + +const char *create_temp_new_basefile[5] = { + /* Mysql */ + "CREATE TEMPORARY TABLE new_basefile%lld AS " + "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) " + "WHERE Temp.FileIndex > 0", + /* Postgresql */ + "CREATE TEMPORARY TABLE new_basefile%lld AS " + "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) " + "WHERE Temp.FileIndex > 0", + /* SQLite */ + "CREATE TEMPORARY TABLE new_basefile%lld AS " + "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) " + "WHERE Temp.FileIndex > 0", + /* SQLite3 */ + "CREATE TEMPORARY TABLE new_basefile%lld AS " + "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) " + "WHERE Temp.FileIndex > 0", + /* Ingres */ + "DECLARE GLOBAL TEMPORARY TABLE new_basefile%lld AS " + "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) " + "WHERE Temp.FileIndex > 0" + "ON COMMIT PRESERVE ROWS WITH NORECOVERY", +}; + /* ====== 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 a1614fd9ba..e984fe6eb0 100644 --- a/bacula/src/cats/sql_cmds.h +++ b/bacula/src/cats/sql_cmds.h @@ -75,6 +75,8 @@ extern const char CATS_IMP_EXP *uar_sel_jobid_temp; extern const char CATS_IMP_EXP *select_recent_version[5]; extern const char CATS_IMP_EXP *select_recent_version_with_basejob[5]; +extern const char CATS_IMP_EXP *create_temp_basefile[5]; +extern const char CATS_IMP_EXP *create_temp_new_basefile[5]; extern const char CATS_IMP_EXP *create_deltabs[5]; extern const char CATS_IMP_EXP *uar_file[5]; diff --git a/bacula/src/cats/sql_create.c b/bacula/src/cats/sql_create.c index 44e016c465..cf8b1ad284 100644 --- a/bacula/src/cats/sql_create.c +++ b/bacula/src/cats/sql_create.c @@ -1083,38 +1083,6 @@ bool db_write_batch_file_records(JCR *jcr) #endif /* ! HAVE_BATCH_FILE_INSERT */ - -/* List of SQL commands to create temp table and indicies */ -const char *create_temp_basefile[5] = { - /* MySQL */ - "CREATE TEMPORARY TABLE basefile%lld (" -// "CREATE TABLE basefile%lld (" - "Path BLOB NOT NULL," - "Name BLOB NOT NULL)", - - /* Postgresql */ - "CREATE TEMPORARY TABLE basefile%lld (" -// "CREATE TABLE basefile%lld (" - "Path TEXT," - "Name TEXT)", - - /* SQLite */ - "CREATE TEMPORARY TABLE basefile%lld (" - "Path TEXT," - "Name TEXT)", - - /* SQLite3 */ - "CREATE TEMPORARY TABLE basefile%lld (" - "Path TEXT," - "Name TEXT)", - - /* Ingres */ - "DECLARE GLOBAL TEMPORARY TABLE basefile%lld (" - "Path TEXT NOT NULL," - "Name TEXT NOT NULL)" - "ON COMMIT PRESERVE ROWS WITH NORECOVERY" -}; - /** * Create file attributes record, or base file attributes record */ @@ -1238,17 +1206,7 @@ bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids) goto bail_out; } 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 " - "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) " - "WHERE Temp.FileIndex > 0", - (uint64_t)jcr->JobId, buf.c_str()); + Mmsg(mdb->cmd, create_temp_new_basefile[db_type], (uint64_t)jcr->JobId, buf.c_str()); ret = db_sql_query(mdb, mdb->cmd, NULL, NULL); bail_out: @@ -1256,7 +1214,6 @@ bail_out: return ret; } - /** * Create Restore Object record in B_DB * -- 2.39.5