X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fsql_cmds.c;h=5340b2dfc41d55680746cb87a9bce93ca8a810a4;hb=cd0826785c0dc9a1cf1fccaeffe7f94e7a2647cc;hp=85ab546a1e68e038e4af202ce3846915a5221962;hpb=ac2f2b4330c458ff97d96c6c6a8602f0ad8dcaa7;p=bacula%2Fbacula diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index 85ab546a1e..5340b2dfc4 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -6,7 +6,7 @@ The main author of Bacula is Kern Sibbald, with contributions from many others, a complete list can be found in the file AUTHORS. This program is Free Software; you can redistribute it and/or - modify it under the terms of version two of the GNU General Public + modify it under the terms of version three of the GNU Affero General Public License as published by the Free Software Foundation and included in the file LICENSE. @@ -15,7 +15,7 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. - You should have received a copy of the GNU General Public License + You should have received a copy of the GNU Affero General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. @@ -26,12 +26,10 @@ Switzerland, email:ftf@fsfeurope.org. */ /* - * - * This file contains all the SQL commands issued by the Director + * This file contains all the SQL commands that are either issued by the + * Director or which are database backend specific. * * Kern Sibbald, July MMII - * - * Version $Id$ */ /* * Note, PostgreSQL imposes some constraints on using DISTINCT and GROUP BY @@ -40,7 +38,6 @@ * because all the ORDER BY expressions must appear in the SELECT list! */ - #include "bacula.h" #include "cats.h" @@ -66,7 +63,9 @@ const char *fill_jobhisto = "PurgedFiles, HasBase, Reviewed, Comment " "FROM Job " "WHERE JobStatus IN ('T','W','f','A','E') " - "AND JobId NOT IN (SELECT JobId FROM JobHisto) " + "AND NOT EXISTS " + "(SELECT JobHisto.JobId " + "FROM JobHisto WHERE JobHisto.Jobid=Job.JobId) " "AND JobTDate < %s "; /* For ua_update.c */ @@ -122,166 +121,6 @@ const char *drop_deltabs[] = { const char *create_delindex = "CREATE INDEX DelInx1 ON DelCandidates (JobId)"; -/* Fill candidates table with all Jobs subject to being deleted. - * This is used for pruning Jobs (first the files, then the Jobs). - */ -const char *insert_delcand = - "INSERT INTO DelCandidates " - "SELECT JobId,PurgedFiles,FileSetId,JobFiles,JobStatus FROM Job " - "WHERE Type='%c' " - "AND JobTDate<%s " - "AND ClientId=%s"; - -/* - * Select Jobs from the DelCandidates table that have a - * more recent backup -- i.e. are not the only backup. - * This is the list of Jobs to delete for a Backup Job. - * At the same time, we select "orphanned" jobs - * (i.e. no files, ...) for deletion. - */ -#ifdef old_way -const char *select_backup_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobTDate<%s AND ((DelCandidates.JobFiles=0) OR " - "(DelCandidates.JobStatus NOT IN ('T','W')))) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Level='F' AND Job.JobStatus IN ('T','W') AND Job.Type IN ('B','M') " - "AND Job.FileSetId=DelCandidates.FileSetId)"; - -/* Select Jobs from the DelCandidates table that have a - * more recent InitCatalog -- i.e. are not the only InitCatalog - * This is the list of Jobs to delete for a Verify Job. - */ -const char *select_verify_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus NOT IN ('T','W')) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Type='V' AND Job.Level='V' AND Job.JobStatus IN ('T','W') " - "AND Job.FileSetId=DelCandidates.FileSetId)"; - - -/* Select Jobs from the DelCandidates table. - * This is the list of Jobs to delete for a Restore Job. - */ -const char *select_restore_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus NOT IN ('T','W')) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Type='R')"; - -/* Select Jobs from the DelCandidates table. - * This is the list of Jobs to delete for an Admin Job. - */ -const char *select_admin_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus NOT IN ('T','W')) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Type='D')"; - -/* - * Select Jobs from the DelCandidates table. - * This is the list of Jobs to delete for an Migrate Job. - */ -const char *select_migrate_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus NOT IN ('T','W')) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Type='g')"; - -/* - * Select Jobs from the DelCandidates table. - * This is the list of Jobs to delete for an Copy Job. - */ -const char *select_copy_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus NOT IN ('T','W')) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Type='C')"; - - -#else -/* Faster way */ -const char *select_backup_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobId=DelCandidates.JobId AND ((DelCandidates.JobFiles=0) OR " - "(DelCandidates.JobStatus NOT IN ('T','W')))) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Level='F' AND Job.JobStatus IN ('T','W') AND Job.Type IN ('B','M') " - "AND Job.FileSetId=DelCandidates.FileSetId)"; - -/* Select Jobs from the DelCandidates table that have a - * more recent InitCatalog -- i.e. are not the only InitCatalog - * This is the list of Jobs to delete for a Verify Job. - */ -const char *select_verify_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Type='V' AND Job.Level='V' AND Job.JobStatus IN ('T','W') " - "AND Job.FileSetId=DelCandidates.FileSetId)"; - - -/* Select Jobs from the DelCandidates table. - * This is the list of Jobs to delete for a Restore Job. - */ -const char *select_restore_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Type='R')"; - -/* Select Jobs from the DelCandidates table. - * This is the list of Jobs to delete for an Admin Job. - */ -const char *select_admin_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Type='D')"; - -/* - * Select Jobs from the DelCandidates table. - * This is the list of Jobs to delete for an Migrate Job. - */ -const char *select_migrate_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Type='g')"; - -const char *select_copy_del = - "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " - "FROM Job,DelCandidates " - "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR " - "(Job.JobTDate>%s " - "AND Job.ClientId=%s " - "AND Job.Type='C')"; - - -#endif - /* ======= ua_restore.c */ const char *uar_count_files = "SELECT JobFiles FROM Job WHERE JobId=%s"; @@ -430,26 +269,6 @@ const char *uar_jobids_fileindex = const char *uar_jobid_fileindex_from_table = "SELECT JobId,FileIndex from %s"; - -/* - * - * This file contains all the SQL commands issued by the Director - * - * Kern Sibbald, July MMII - * - * Version $Id$ - */ -/* - * Note, PostgreSQL imposes some constraints on using DISTINCT and GROUP BY - * for example, the following is illegal in PostgreSQL: - * SELECT DISTINCT JobId FROM temp ORDER BY StartTime ASC; - * because all the ORDER BY expressions must appear in the SELECT list! - */ - - -#include "bacula.h" -#include "cats.h" - /* Get the list of the last recent version with a given jobid list * This is a tricky part because with SQL the result of * @@ -460,7 +279,7 @@ const char *uar_jobid_fileindex_from_table = * 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] = { +const char *select_recent_version_with_basejob[5] = { /* MySQL */ "SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, " "File.FilenameId AS FilenameId, LStat, MD5 " @@ -542,6 +361,31 @@ const char *select_recent_version_with_basejob[4] = { "WHERE BaseFiles.JobId IN (%s) " ") AS tmp GROUP BY PathId, FilenameId " ") AS T1 " +"WHERE (Job.JobId IN ( " + "SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) " + "OR Job.JobId IN (%s)) " + "AND T1.JobTDate = Job.JobTDate " + "AND Job.JobId = File.JobId " + "AND T1.PathId = File.PathId " + "AND T1.FilenameId = File.FilenameId", + + /* Ingres */ /* See Mysql section for doc */ +"SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, " + "File.FilenameId AS FilenameId, 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 (%s) " + "UNION ALL " + "SELECT JobTDate, PathId, FilenameId " + "FROM BaseFiles " + "JOIN File USING (FileId) " + "JOIN Job ON (BaseJobId = Job.JobId) " + "WHERE BaseFiles.JobId IN (%s) " + ") AS tmp GROUP BY PathId, FilenameId " + ") AS T1 " "WHERE (Job.JobId IN ( " "SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) " "OR Job.JobId IN (%s)) " @@ -552,7 +396,7 @@ const char *select_recent_version_with_basejob[4] = { }; /* Get the list of the last recent version with a given BaseJob jobid list */ -const char *select_recent_version[4] = { +const char *select_recent_version[5] = { /* MySQL */ "SELECT j1.JobId AS JobId, f1.FileId AS FileId, f1.FileIndex AS FileIndex, " "f1.PathId AS PathId, f1.FilenameId AS FilenameId, " @@ -602,6 +446,22 @@ const char *select_recent_version[4] = { "WHERE File.JobId IN (%s) " "GROUP BY PathId, FilenameId " ") AS t1, Job AS j1, File AS f1 " + "WHERE t1.JobTDate = j1.JobTDate " + "AND j1.JobId IN (%s) " + "AND t1.FilenameId = f1.FilenameId " + "AND t1.PathId = f1.PathId " + "AND j1.JobId = f1.JobId", + + /* Ingres */ + "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.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) " + "WHERE File.JobId IN (%s) " + "GROUP BY PathId, FilenameId " + ") AS t1, Job AS j1, File AS f1 " "WHERE t1.JobTDate = j1.JobTDate " "AND j1.JobId IN (%s) " "AND t1.FilenameId = f1.FilenameId " @@ -609,10 +469,117 @@ const char *select_recent_version[4] = { "AND j1.JobId = f1.JobId" }; +/* We don't create this table as TEMPORARY because MySQL MyISAM + * 5.0 and 5.1 are unable to run further queries in this mode + */ +const char *create_temp_accurate_jobids_default = + "CREATE TABLE btemp3%s AS " + "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles " + "FROM Job JOIN FileSet USING (FileSetId) " + "WHERE ClientId = %s " + "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' " + "AND StartTime<'%s' " + "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) " + "ORDER BY Job.JobTDate DESC LIMIT 1"; + +const char *create_temp_accurate_jobids[5] = { + /* Mysql */ + create_temp_accurate_jobids_default, + /* Postgresql */ + create_temp_accurate_jobids_default, + /* SQLite */ + create_temp_accurate_jobids_default, + /* SQLite3 */ + create_temp_accurate_jobids_default, + /* Ingres */ + "DECLARE GLOBAL TEMPORARY TABLE btemp3%s AS " + "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles " + "FROM Job JOIN FileSet USING (FileSetId) " + "WHERE ClientId = %s " + "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' " + "AND StartTime<'%s' " + "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) " + "ORDER BY Job.JobTDate DESC FETCH FIRST 1 ROW ONLY " + "ON COMMIT PRESERVE ROWS WITH NORECOVERY" +}; + +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 VARBYTE(32000) NOT NULL," + "Name VARBYTE(32000) 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 */ -const char *create_deltabs[4] = { +const char *create_deltabs[5] = { /* MySQL */ "CREATE TEMPORARY TABLE DelCandidates (" "JobId INTEGER UNSIGNED NOT NULL, " @@ -640,12 +607,66 @@ const char *create_deltabs[4] = { "PurgedFiles TINYINT, " "FileSetId INTEGER UNSIGNED, " "JobFiles INTEGER UNSIGNED, " - "JobStatus CHAR)"}; + "JobStatus CHAR)", + /* Ingres */ + "DECLARE GLOBAL TEMPORARY TABLE DelCandidates (" + "JobId INTEGER NOT NULL, " + "PurgedFiles SMALLINT, " + "FileSetId INTEGER, " + "JobFiles INTEGER, " + "JobStatus CHAR(1)) " + "ON COMMIT PRESERVE ROWS WITH NORECOVERY" +}; + +/* ======= ua_purge.c */ + +/* Select the first available Copy Job that must be upgraded to a Backup job when the original backup job is expired. */ + +const char *uap_upgrade_copies_oldest_job_default = +"CREATE TEMPORARY TABLE cpy_tmp AS " + "SELECT MIN(JobId) AS JobId FROM Job " /* Choose the oldest job */ + "WHERE Type='%c' " /* JT_JOB_COPY */ + "AND ( PriorJobId IN (%s) " /* JobId selection */ + "OR " + " PriorJobId IN ( " + "SELECT PriorJobId " + "FROM Job " + "WHERE JobId IN (%s) " /* JobId selection */ + " AND Type='B' " + ") " + ") " + "GROUP BY PriorJobId "; /* one result per copy */ + +const char *uap_upgrade_copies_oldest_job[5] = { + /* Mysql */ + uap_upgrade_copies_oldest_job_default, + /* Postgresql */ + uap_upgrade_copies_oldest_job_default, + /* SQLite */ + uap_upgrade_copies_oldest_job_default, + /* SQLite3 */ + uap_upgrade_copies_oldest_job_default, + /* Ingres */ + "DECLARE GLOBAL TEMPORARY TABLE cpy_tmp AS " + "SELECT MIN(JobId) AS JobId FROM Job " /* Choose the oldest job */ + "WHERE Type='%c' " /* JT_JOB_COPY */ + "AND ( PriorJobId IN (%s) " /* JobId selection */ + "OR " + " PriorJobId IN ( " + "SELECT PriorJobId " + "FROM Job " + "WHERE JobId IN (%s) " /* JobId selection */ + " AND Type='B' " + ") " + ") " + "GROUP BY PriorJobId " /* one result per copy */ + "ON COMMIT PRESERVE ROWS WITH NORECOVERY" +}; /* ======= ua_restore.c */ /* List Jobs where a particular file is saved */ -const char *uar_file[4] = { +const char *uar_file[5] = { /* Mysql */ "SELECT Job.JobId as JobId," "CONCAT(Path.Path,Filename.Name) as Name, " @@ -681,9 +702,19 @@ const char *uar_file[4] = { "AND Client.ClientId=Job.ClientId " "AND Job.JobId=File.JobId AND File.FileIndex > 0 " "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId " - "AND Filename.Name='%s' ORDER BY StartTime DESC LIMIT 20"}; + "AND Filename.Name='%s' ORDER BY StartTime DESC LIMIT 20", + /* Ingres */ + "SELECT Job.JobId as JobId," + "Path.Path||Filename.Name as Name, " + "StartTime,Type as JobType,JobStatus,JobFiles,JobBytes " + "FROM Client,Job,File,Filename,Path WHERE Client.Name='%s' " + "AND Client.ClientId=Job.ClientId " + "AND Job.JobId=File.JobId AND File.FileIndex > 0 " + "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId " + "AND Filename.Name='%s' ORDER BY StartTime DESC FETCH FIRST 20 ROWS ONLY" +}; -const char *uar_create_temp[4] = { +const char *uar_create_temp[5] = { /* Mysql */ "CREATE TEMPORARY TABLE temp (" "JobId INTEGER UNSIGNED NOT NULL," @@ -735,9 +766,24 @@ const char *uar_create_temp[4] = { "VolumeName TEXT," "StartFile INTEGER UNSIGNED," "VolSessionId INTEGER UNSIGNED," - "VolSessionTime INTEGER UNSIGNED)"}; + "VolSessionTime INTEGER UNSIGNED)", + /* Ingres */ + "DECLARE GLOBAL TEMPORARY TABLE temp (" + "JobId INTEGER NOT NULL," + "JobTDate BIGINT," + "ClientId INTEGER," + "Level CHAR(1)," + "JobFiles INTEGER," + "JobBytes BIGINT," + "StartTime TIMESTAMP WITHOUT TIME ZONE," + "VolumeName VARBYTE(128)," + "StartFile INTEGER," + "VolSessionId INTEGER," + "VolSessionTime INTEGER) " + "ON COMMIT PRESERVE ROWS WITH NORECOVERY" +}; -const char *uar_create_temp1[4] = { +const char *uar_create_temp1[5] = { /* Mysql */ "CREATE TEMPORARY TABLE temp1 (" "JobId INTEGER UNSIGNED NOT NULL," @@ -753,7 +799,13 @@ const char *uar_create_temp1[4] = { /* SQLite3 */ "CREATE TEMPORARY TABLE temp1 (" "JobId INTEGER UNSIGNED NOT NULL," - "JobTDate BIGINT UNSIGNED)"}; + "JobTDate BIGINT UNSIGNED)", + /* Ingres */ + "DECLARE GLOBAL TEMPORARY TABLE temp1 (" + "JobId INTEGER NOT NULL," + "JobTDate BIGINT) " + "ON COMMIT PRESERVE ROWS WITH NORECOVERY" + }; /* Query to get all files in a directory -- no recursing * Note, for PostgreSQL since it respects the "Single Value @@ -762,7 +814,7 @@ const char *uar_create_temp1[4] = { * for each time it was backed up. */ -const char *uar_jobid_fileindex_from_dir[4] = { +const char *uar_jobid_fileindex_from_dir[5] = { /* Mysql */ "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " "WHERE Job.JobId IN (%s) " @@ -801,9 +853,32 @@ const char *uar_jobid_fileindex_from_dir[4] = { "AND Job.ClientId=Client.ClientId " "AND Path.PathId=File.Pathid " "AND Filename.FilenameId=File.FilenameId " - "GROUP BY File.FileIndex "}; + "GROUP BY File.FileIndex ", + /* Ingres */ + "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " + "WHERE Job.JobId IN (%s) " + "AND Job.JobId=File.JobId " + "AND Path.Path='%s' " + "AND Client.Name='%s' " + "AND Job.ClientId=Client.ClientId " + "AND Path.PathId=File.Pathid " + "AND Filename.FilenameId=File.FilenameId" +}; + +const char *sql_media_order_most_recently_written[5] = { + /* Mysql */ + "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId", + /* Postgresql */ + "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId", + /* SQLite */ + "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId", + /* SQLite3 */ + "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId", + /* Ingres */ + "ORDER BY IFNULL(LastWritten, '1970-01-01 00:00:00') DESC,MediaId" +}; -const char *sql_get_max_connections[4] = { +const char *sql_get_max_connections[5] = { /* Mysql */ "SHOW VARIABLES LIKE 'max_connections'", /* Postgresql */ @@ -811,11 +886,13 @@ const char *sql_get_max_connections[4] = { /* SQLite */ "SELECT 0", /* SQLite3 */ + "SELECT 0", + /* Ingres (TODO) */ "SELECT 0" }; /* Row number of the max_connections setting */ -const uint32_t sql_get_max_connections_index[4] = { +const uint32_t sql_get_max_connections_index[5] = { /* Mysql */ 1, /* Postgresql */ @@ -823,5 +900,7 @@ const uint32_t sql_get_max_connections_index[4] = { /* SQLite */ 0, /* SQLite3 */ + 0, + /* Ingres (TODO) */ 0 };