X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fsql_cmds.c;h=d48d926d708689a128e87e9ce7292f0853e8fde7;hb=9227caa021ac3d1c4f2f1e1ad1a7985ecb3cc268;hp=e4e273025a7f94d01f4f3884de37943de2582058;hpb=09c8c88482741e8f3e906be9159fa68015a9e3ec;p=bacula%2Fbacula diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index e4e273025a..d48d926d70 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -1,41 +1,83 @@ /* - * - * This file contains all the SQL commands issued by the Director + Bacula® - The Network Backup Solution + + Copyright (C) 2002-2008 Free Software Foundation Europe e.V. + + 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 + License as published by the Free Software Foundation and included + in the file LICENSE. + + This program is distributed in the hope that it will be useful, but + WITHOUT ANY WARRANTY; without even the implied warranty of + 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 + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA + 02110-1301, USA. + + Bacula® is a registered trademark of Kern Sibbald. + The licensor of Bacula is the Free Software Foundation Europe + (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich, + 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 - * - * Version $Id$ */ /* - Copyright (C) 2002-2006 Kern Sibbald - - This program is free software; you can redistribute it and/or - modify it under the terms of the GNU General Public License - version 2 as amended with additional clauses defined in the - file LICENSE in the main source directory. - - This program is distributed in the hope that it will be useful, - but WITHOUT ANY WARRANTY; without even the implied warranty of - MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - the file LICENSE for additional details. - + * 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" -/* For ua_cmds.c */ +const char *cleanup_created_job = + "UPDATE Job SET JobStatus='f', StartTime=SchedTime, EndTime=SchedTime " + "WHERE JobStatus = 'C'"; +const char *cleanup_running_job = + "UPDATE Job SET JobStatus='f', EndTime=StartTime WHERE JobStatus = 'R'"; + +/* For sql_update.c db_update_stats */ +const char *fill_jobhisto = + "INSERT INTO JobHisto (" + "JobId, Job, Name, Type, Level, ClientId, JobStatus, " + "SchedTime, StartTime, EndTime, RealEndTime, JobTDate, " + "VolSessionId, VolSessionTime, JobFiles, JobBytes, ReadBytes, " + "JobErrors, JobMissingFiles, PoolId, FileSetId, PriorJobId, " + "PurgedFiles, HasBase, Reviewed, Comment ) " + "SELECT " + "JobId, Job, Name, Type, Level, ClientId, JobStatus, " + "SchedTime, StartTime, EndTime, RealEndTime, JobTDate, " + "VolSessionId, VolSessionTime, JobFiles, JobBytes, ReadBytes, " + "JobErrors, JobMissingFiles, PoolId, FileSetId, PriorJobId, " + "PurgedFiles, HasBase, Reviewed, Comment " + "FROM Job " + "WHERE JobStatus IN ('T','W','f','A','E') " + "AND JobId NOT IN (SELECT JobId FROM JobHisto) " + "AND JobTDate < %s "; + +/* For ua_update.c */ const char *list_pool = "SELECT * FROM Pool WHERE PoolId=%s"; /* For ua_dotcmds.c */ const char *client_backups = "SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime," - "JobFiles,JobBytes,VolumeName,MediaType,FileSet" + "JobFiles,JobBytes,VolumeName,MediaType,FileSet,Media.Enabled as Enabled" " FROM Client,Job,JobMedia,Media,FileSet" " WHERE Client.Name='%s'" " AND FileSet='%s'" " AND Client.ClientId=Job.ClientId" - " AND JobStatus='T' AND Type='B'" + " AND JobStatus IN ('T','W') AND Type='B'" " AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId" " AND Job.FileSetId=FileSet.FileSetId" " ORDER BY Job.StartTime"; @@ -47,14 +89,25 @@ const char *del_File = "DELETE FROM File WHERE JobId=%s"; const char *upd_Purged = "UPDATE Job Set PurgedFiles=1 WHERE JobId=%s"; const char *cnt_DelCand = "SELECT count(*) FROM DelCandidates"; const char *del_Job = "DELETE FROM Job WHERE JobId=%s"; -const char *del_MAC = "DELETE FROM MAC WHERE JobId=%s"; const char *del_JobMedia = "DELETE FROM JobMedia WHERE JobId=%s"; const char *cnt_JobMedia = "SELECT count(*) FROM JobMedia WHERE MediaId=%s"; -const char *sel_JobMedia = "SELECT JobId FROM JobMedia WHERE MediaId=%s"; + +const char *sel_JobMedia = + "SELECT DISTINCT JobMedia.JobId FROM JobMedia,Job " + "WHERE MediaId=%s AND Job.JobId=JobMedia.JobId " + "AND Job.JobTDate<%s"; + +/* Count Select JobIds for File deletion */ +const char *count_select_job = + "SELECT count(*) from Job " + "WHERE JobTDate<%s " + "AND ClientId=%s " + "AND PurgedFiles=0"; + /* Select JobIds for File deletion. */ const char *select_job = - "SELECT JobId from Job " + "SELECT DISTINCT JobId from Job " "WHERE JobTDate<%s " "AND ClientId=%s " "AND PurgedFiles=0"; @@ -62,34 +115,9 @@ const char *select_job = /* Delete temp tables and indexes */ const char *drop_deltabs[] = { "DROP TABLE DelCandidates", - "DROP INDEX DelInx1", NULL}; - -/* List of SQL commands to create temp table and indicies */ -const char *create_deltabs[] = { - "CREATE TEMPORARY TABLE DelCandidates (" -#if defined(HAVE_MYSQL) - "JobId INTEGER UNSIGNED NOT NULL, " - "PurgedFiles TINYINT, " - "FileSetId INTEGER UNSIGNED, " - "JobFiles INTEGER UNSIGNED, " - "JobStatus BINARY(1))", -#elif defined(HAVE_POSTGRESQL) - "JobId INTEGER NOT NULL, " - "PurgedFiles SMALLINT, " - "FileSetId INTEGER, " - "JobFiles INTEGER, " - "JobStatus char(1))", -#else - "JobId INTEGER UNSIGNED NOT NULL, " - "PurgedFiles TINYINT, " - "FileSetId INTEGER UNSIGNED, " - "JobFiles INTEGER UNSIGNED, " - "JobStatus CHAR)", -#endif - "CREATE INDEX DelInx1 ON DelCandidates (JobId)", - NULL}; +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). @@ -108,14 +136,88 @@ const char *insert_delcand = * 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!='T'))) 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.Level='F' AND Job.JobStatus='T' AND Job.Type IN ('B','M') " + "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 @@ -125,10 +227,10 @@ const char *select_backup_del = const char *select_verify_del = "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " "FROM Job,DelCandidates " - "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus!='T') OR " + "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='T' " + "AND Job.Type='V' AND Job.Level='V' AND Job.JobStatus IN ('T','W') " "AND Job.FileSetId=DelCandidates.FileSetId)"; @@ -138,7 +240,7 @@ const char *select_verify_del = const char *select_restore_del = "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " "FROM Job,DelCandidates " - "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus!='T') OR " + "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR " "(Job.JobTDate>%s " "AND Job.ClientId=%s " "AND Job.Type='R')"; @@ -149,7 +251,7 @@ const char *select_restore_del = const char *select_admin_del = "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " "FROM Job,DelCandidates " - "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus!='T') OR " + "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus NOT IN ('T','W')) OR " "(Job.JobTDate>%s " "AND Job.ClientId=%s " "AND Job.Type='D')"; @@ -161,11 +263,22 @@ const char *select_admin_del = const char *select_migrate_del = "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " "FROM Job,DelCandidates " - "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus!='T') OR " + "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"; @@ -174,34 +287,14 @@ const char *uar_count_files = const char *uar_list_jobs = "SELECT JobId,Client.Name as Client,StartTime,Level as " "JobLevel,JobFiles,JobBytes " - "FROM Client,Job WHERE Client.ClientId=Job.ClientId AND JobStatus='T' " + "FROM Client,Job WHERE Client.ClientId=Job.ClientId AND JobStatus IN ('T','W') " "AND Type='B' ORDER BY StartTime DESC LIMIT 20"; -#ifdef HAVE_MYSQL -/* MYSQL IS NOT STANDARD SQL !!!!! */ -/* List Jobs where a particular file is saved */ -const char *uar_file = - "SELECT Job.JobId as JobId," - "CONCAT(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 Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId " - "AND Filename.Name='%s' ORDER BY StartTime DESC LIMIT 20"; -#else -/* List Jobs where a particular file is saved */ -const char *uar_file = - "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 Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId " - "AND Filename.Name='%s' ORDER BY StartTime DESC LIMIT 20"; -#endif - +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) " + " ORDER BY StartTime ASC"; /* * Find all files for a particular JobId and insert them into @@ -210,57 +303,20 @@ const char *uar_file = const char *uar_sel_files = "SELECT Path.Path,Filename.Name,FileIndex,JobId,LStat " "FROM File,Filename,Path " - "WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId " + "WHERE File.JobId IN (%s) AND Filename.FilenameId=File.FilenameId " "AND Path.PathId=File.PathId"; const char *uar_del_temp = "DROP TABLE temp"; const char *uar_del_temp1 = "DROP TABLE temp1"; -const char *uar_create_temp = - "CREATE TEMPORARY TABLE temp (" -#ifdef HAVE_POSTGRESQL - "JobId INTEGER NOT NULL," - "JobTDate BIGINT," - "ClientId INTEGER," - "Level CHAR," - "JobFiles INTEGER," - "JobBytes BIGINT," - "StartTime TEXT," - "VolumeName TEXT," - "StartFile INTEGER," - "VolSessionId INTEGER," - "VolSessionTime INTEGER)"; -#else - "JobId INTEGER UNSIGNED NOT NULL," - "JobTDate BIGINT UNSIGNED," - "ClientId INTEGER UNSIGNED," - "Level CHAR," - "JobFiles INTEGER UNSIGNED," - "JobBytes BIGINT UNSIGNED," - "StartTime TEXT," - "VolumeName TEXT," - "StartFile INTEGER UNSIGNED," - "VolSessionId INTEGER UNSIGNED," - "VolSessionTime INTEGER UNSIGNED)"; -#endif - -const char *uar_create_temp1 = - "CREATE TEMPORARY TABLE temp1 (" -#ifdef HAVE_POSTGRESQL - "JobId INTEGER NOT NULL," - "JobTDate BIGINT)"; -#else - "JobId INTEGER UNSIGNED NOT NULL," - "JobTDate BIGINT UNSIGNED)"; -#endif - const char *uar_last_full = "INSERT INTO temp1 SELECT Job.JobId,JobTdate " "FROM Client,Job,JobMedia,Media,FileSet WHERE Client.ClientId=%s " "AND Job.ClientId=%s " - "AND Job.StartTime<'%s' " - "AND Level='F' AND JobStatus='T' AND Type='B' " + "AND Job.StartTime < '%s' " + "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' " "AND JobMedia.JobId=Job.JobId " + "AND Media.Enabled=1 " "AND JobMedia.MediaId=Media.MediaId " "AND Job.FileSetId=FileSet.FileSetId " "AND FileSet.FileSet='%s' " @@ -272,7 +328,8 @@ const char *uar_full = "Job.ClientId,Job.Level,Job.JobFiles,Job.JobBytes," "StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime " "FROM temp1,Job,JobMedia,Media WHERE temp1.JobId=Job.JobId " - "AND Level='F' AND JobStatus='T' AND Type='B' " + "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' " + "AND Media.Enabled=1 " "AND JobMedia.JobId=Job.JobId " "AND JobMedia.MediaId=Media.MediaId"; @@ -285,8 +342,9 @@ const char *uar_dif = "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' " "AND Job.ClientId=%s " "AND JobMedia.JobId=Job.JobId " + "AND Media.Enabled=1 " "AND JobMedia.MediaId=Media.MediaId " - "AND Job.Level='D' AND JobStatus='T' AND Type='B' " + "AND Job.Level='D' AND JobStatus IN ('T','W') AND Type='B' " "AND Job.FileSetId=FileSet.FileSetId " "AND FileSet.FileSet='%s' " "%s" @@ -300,30 +358,22 @@ const char *uar_inc = "FROM Job,JobMedia,Media,FileSet " "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' " "AND Job.ClientId=%s " + "AND Media.Enabled=1 " "AND JobMedia.JobId=Job.JobId " "AND JobMedia.MediaId=Media.MediaId " - "AND Job.Level='I' AND JobStatus='T' AND Type='B' " + "AND Job.Level='I' AND JobStatus IN ('T','W') AND Type='B' " "AND Job.FileSetId=FileSet.FileSetId " "AND FileSet.FileSet='%s' " "%s"; -#ifdef HAVE_POSTGRESQL -/* Note, the PostgreSQL will have a much uglier looking - * list since it cannot do GROUP BY of different values. - */ const char *uar_list_temp = - "SELECT JobId,Level,JobFiles,JobBytes,StartTime,VolumeName,StartFile" + "SELECT DISTINCT JobId,Level,JobFiles,JobBytes,StartTime,VolumeName" " FROM temp" - " ORDER BY StartTime,StartFile ASC"; -#else -const char *uar_list_temp = - "SELECT JobId,Level,JobFiles,JobBytes,StartTime,VolumeName,StartFile" - " FROM temp" - " GROUP BY JobId ORDER BY StartTime,StartFile ASC"; -#endif + " ORDER BY StartTime ASC"; -const char *uar_sel_jobid_temp = "SELECT JobId FROM temp ORDER BY StartTime ASC"; +const char *uar_sel_jobid_temp = + "SELECT DISTINCT JobId,StartTime FROM temp ORDER BY StartTime ASC"; const char *uar_sel_all_temp1 = "SELECT * FROM temp1"; @@ -348,22 +398,23 @@ const char *uar_mediatype = * for use when inserting individual files into the tree. */ const char *uar_jobid_fileindex = - "SELECT Job.JobId, File.FileIndex FROM Job,File,Path,Filename,Client " + "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " "WHERE Job.JobId=File.JobId " - "AND Job.StartTime<'%s' " + "AND Job.StartTime<='%s' " "AND Path.Path='%s' " "AND Filename.Name='%s' " "AND Client.Name='%s' " "AND Job.ClientId=Client.ClientId " "AND Path.PathId=File.PathId " "AND Filename.FilenameId=File.FilenameId " + "AND JobStatus IN ('T','W') AND Type='B' " "ORDER BY Job.StartTime DESC LIMIT 1"; const char *uar_jobids_fileindex = - "SELECT Job.JobId, File.FileIndex FROM Job,File,Path,Filename,Client " + "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " "WHERE Job.JobId IN (%s) " "AND Job.JobId=File.JobId " - "AND Job.StartTime<'%s' " + "AND Job.StartTime<='%s' " "AND Path.Path='%s' " "AND Filename.Name='%s' " "AND Client.Name='%s' " @@ -372,6 +423,545 @@ const char *uar_jobids_fileindex = "AND Filename.FilenameId=File.FilenameId " "ORDER BY Job.StartTime DESC LIMIT 1"; +/* Query to get list of files from table -- presuably built by an external program */ +const char *uar_jobid_fileindex_from_table = + "SELECT JobId,FileIndex from %s"; + +/* Get the list of the last recent version with a given jobid list + * This is a tricky part because with SQL the result of + * + * SELECT MAX(A), B, C, D FROM... GROUP BY (B,C) + * + * doesn't give the good result (for D). + * + * 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[5] = { + /* MySQL */ +"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 " /* Get all normal files */ + "FROM File JOIN Job USING (JobId) " /* from selected backup */ + "WHERE File.JobId IN (%s) " + "UNION ALL " + "SELECT JobTDate, PathId, FilenameId " /* Get all files from */ + "FROM BaseFiles " /* BaseJob */ + "JOIN File USING (FileId) " + "JOIN Job ON (BaseJobId = Job.JobId) " + "WHERE BaseFiles.JobId IN (%s) " /* Use Max(JobTDate) to find */ + ") AS tmp GROUP BY PathId, FilenameId " /* the latest file version */ + ") AS T1 " +"WHERE (Job.JobId IN ( " /* Security, we force JobId to be valid */ + "SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) " + "OR Job.JobId IN (%s)) " + "AND T1.JobTDate = Job.JobTDate " /* Join on JobTDate to get the orginal */ + "AND Job.JobId = File.JobId " /* Job/File record */ + "AND T1.PathId = File.PathId " + "AND T1.FilenameId = File.FilenameId", + + /* Postgresql */ /* The DISTINCT ON () permits to avoid extra join */ + "SELECT DISTINCT ON (FilenameId, PathId) StartTime, JobId, FileId, " + "FileIndex, PathId, FilenameId, LStat, MD5 " + "FROM " + "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 " + "FROM File WHERE JobId IN (%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) " + ") AS T JOIN Job USING (JobId) " + "ORDER BY FilenameId, PathId, StartTime DESC ", + + /* SQLite */ /* 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)) " + "AND T1.JobTDate = Job.JobTDate " + "AND Job.JobId = File.JobId " + "AND T1.PathId = File.PathId " + "AND T1.FilenameId = File.FilenameId", + + /* SQLite3 */ /* 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)) " + "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)) " + "AND T1.JobTDate = Job.JobTDate " + "AND Job.JobId = File.JobId " + "AND T1.PathId = File.PathId " + "AND T1.FilenameId = File.FilenameId" +}; + +/* Get the list of the last recent version with a given BaseJob jobid list */ +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, " + "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 " + "AND t1.PathId = f1.PathId " + "AND j1.JobId = f1.JobId", + + /* 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 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 ( " + "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 " + "AND t1.PathId = f1.PathId " + "AND j1.JobId = f1.JobId", + + /* SQLite3 */ + "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 ( " + "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 " + "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 " + "AND t1.PathId = f1.PathId " + "AND j1.JobId = f1.JobId" +}; + +const char *create_temp_accurate_jobids_default = + "CREATE 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 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[5] = { + /* MySQL */ + "CREATE TEMPORARY TABLE DelCandidates (" + "JobId INTEGER UNSIGNED NOT NULL, " + "PurgedFiles TINYINT, " + "FileSetId INTEGER UNSIGNED, " + "JobFiles INTEGER UNSIGNED, " + "JobStatus BINARY(1))", + /* Postgresql */ + "CREATE TEMPORARY TABLE DelCandidates (" + "JobId INTEGER NOT NULL, " + "PurgedFiles SMALLINT, " + "FileSetId INTEGER, " + "JobFiles INTEGER, " + "JobStatus char(1))", + /* SQLite */ + "CREATE TEMPORARY TABLE DelCandidates (" + "JobId INTEGER UNSIGNED NOT NULL, " + "PurgedFiles TINYINT, " + "FileSetId INTEGER UNSIGNED, " + "JobFiles INTEGER UNSIGNED, " + "JobStatus CHAR)", + /* SQLite3 */ + "CREATE TEMPORARY TABLE DelCandidates (" + "JobId INTEGER UNSIGNED NOT NULL, " + "PurgedFiles TINYINT, " + "FileSetId INTEGER UNSIGNED, " + "JobFiles INTEGER UNSIGNED, " + "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[5] = { + /* Mysql */ + "SELECT Job.JobId as JobId," + "CONCAT(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 LIMIT 20", + /* Postgresql */ + "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 LIMIT 20", + /* SQLite */ + "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 LIMIT 20", + /* SQLite3 */ + "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 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[5] = { + /* Mysql */ + "CREATE TEMPORARY TABLE temp (" + "JobId INTEGER UNSIGNED NOT NULL," + "JobTDate BIGINT UNSIGNED," + "ClientId INTEGER UNSIGNED," + "Level CHAR," + "JobFiles INTEGER UNSIGNED," + "JobBytes BIGINT UNSIGNED," + "StartTime TEXT," + "VolumeName TEXT," + "StartFile INTEGER UNSIGNED," + "VolSessionId INTEGER UNSIGNED," + "VolSessionTime INTEGER UNSIGNED)", + /* Postgresql */ + "CREATE TEMPORARY TABLE temp (" + "JobId INTEGER NOT NULL," + "JobTDate BIGINT," + "ClientId INTEGER," + "Level CHAR," + "JobFiles INTEGER," + "JobBytes BIGINT," + "StartTime TEXT," + "VolumeName TEXT," + "StartFile INTEGER," + "VolSessionId INTEGER," + "VolSessionTime INTEGER)", + /* SQLite */ + "CREATE TEMPORARY TABLE temp (" + "JobId INTEGER UNSIGNED NOT NULL," + "JobTDate BIGINT UNSIGNED," + "ClientId INTEGER UNSIGNED," + "Level CHAR," + "JobFiles INTEGER UNSIGNED," + "JobBytes BIGINT UNSIGNED," + "StartTime TEXT," + "VolumeName TEXT," + "StartFile INTEGER UNSIGNED," + "VolSessionId INTEGER UNSIGNED," + "VolSessionTime INTEGER UNSIGNED)", + /* SQLite3 */ + "CREATE TEMPORARY TABLE temp (" + "JobId INTEGER UNSIGNED NOT NULL," + "JobTDate BIGINT UNSIGNED," + "ClientId INTEGER UNSIGNED," + "Level CHAR," + "JobFiles INTEGER UNSIGNED," + "JobBytes BIGINT UNSIGNED," + "StartTime TEXT," + "VolumeName TEXT," + "StartFile INTEGER UNSIGNED," + "VolSessionId 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[5] = { + /* Mysql */ + "CREATE TEMPORARY TABLE temp1 (" + "JobId INTEGER UNSIGNED NOT NULL," + "JobTDate BIGINT UNSIGNED)", + /* Postgresql */ + "CREATE TEMPORARY TABLE temp1 (" + "JobId INTEGER NOT NULL," + "JobTDate BIGINT)", + /* SQLite */ + "CREATE TEMPORARY TABLE temp1 (" + "JobId INTEGER UNSIGNED NOT NULL," + "JobTDate BIGINT UNSIGNED)", + /* SQLite3 */ + "CREATE TEMPORARY TABLE temp1 (" + "JobId INTEGER UNSIGNED NOT NULL," + "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 * rule", the results of the SELECT will be unoptimized. @@ -379,8 +969,8 @@ const char *uar_jobids_fileindex = * for each time it was backed up. */ -#ifdef HAVE_POSTGRESQL -const char *uar_jobid_fileindex_from_dir = +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) " "AND Job.JobId=File.JobId " @@ -388,9 +978,18 @@ const char *uar_jobid_fileindex_from_dir = "AND Client.Name='%s' " "AND Job.ClientId=Client.ClientId " "AND Path.PathId=File.Pathid " - "AND Filename.FilenameId=File.FilenameId"; -#else -const char *uar_jobid_fileindex_from_dir = + "AND Filename.FilenameId=File.FilenameId " + "GROUP BY File.FileIndex ", + /* Postgresql */ + "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", + /* SQLite */ "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " "WHERE Job.JobId IN (%s) " "AND Job.JobId=File.JobId " @@ -399,9 +998,64 @@ const char *uar_jobid_fileindex_from_dir = "AND Job.ClientId=Client.ClientId " "AND Path.PathId=File.Pathid " "AND Filename.FilenameId=File.FilenameId " - "GROUP BY File.FileIndex "; -#endif - -/* Query to get list of files from table -- presuably built by an external program */ -const char *uar_jobid_fileindex_from_table = - "SELECT JobId, FileIndex from %s"; + "GROUP BY File.FileIndex ", + /* SQLite3 */ + "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 " + "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[5] = { + /* Mysql */ + "SHOW VARIABLES LIKE 'max_connections'", + /* Postgresql */ + "SHOW max_connections", + /* 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[5] = { + /* Mysql */ + 1, + /* Postgresql */ + 0, + /* SQLite */ + 0, + /* SQLite3 */ + 0, + /* Ingres (TODO) */ + 0 +};