X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fsql_cmds.c;h=b739d6d96691d52c062d8acb1d042aa470fdf70c;hb=57c3794986e8cd011788c1ea3bded6bf3bcfa431;hp=2a22467a9749c4d46ec0959c9ef728c132c4cbef;hpb=07797ede8d82ab51f1fffe10ca79dd4d18fd3f79;p=bacula%2Fbacula diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index 2a22467a97..b739d6d966 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -1,12 +1,12 @@ /* Bacula® - The Network Backup Solution - Copyright (C) 2002-2008 Free Software Foundation Europe e.V. + Copyright (C) 2002-2010 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 + 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. @@ -39,7 +39,6 @@ */ #include "bacula.h" -#include "cats.h" const char *cleanup_created_job = "UPDATE Job SET JobStatus='f', StartTime=SchedTime, EndTime=SchedTime " @@ -84,36 +83,13 @@ const char *client_backups = " AND Job.FileSetId=FileSet.FileSetId" " ORDER BY Job.StartTime"; - /* ====== ua_prune.c */ -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_JobMedia = "DELETE FROM JobMedia WHERE JobId=%s"; -const char *cnt_JobMedia = "SELECT count(*) 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 DISTINCT JobId from Job " - "WHERE JobTDate<%s " - "AND ClientId=%s " - "AND PurgedFiles=0"; - /* Delete temp tables and indexes */ const char *drop_deltabs[] = { "DROP TABLE DelCandidates", @@ -230,6 +206,17 @@ const char *uar_sel_fileset = "AND Job.ClientId=%s AND Client.ClientId=%s " "ORDER BY FileSet.FileSet"; +/* Select all different FileSet for this client + * This query doesn't guarantee that the Id is the latest + * version of the FileSet. Can be used with other queries that + * use Ids to select the FileSet name. (like in accurate) + */ +const char *uar_sel_filesetid = + "SELECT MAX(FileSetId) " + "FROM FileSet JOIN Job USING (FileSetId) " + "WHERE Job.ClientId=%s " + "GROUP BY FileSet"; + /* Find MediaType used by this Job */ const char *uar_mediatype = "SELECT MediaType FROM JobMedia,Media WHERE JobMedia.JobId=%s " @@ -269,7 +256,7 @@ const char *uar_jobids_fileindex = 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 +/* Get the list of the last recent version per Delta 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) @@ -279,10 +266,10 @@ 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[5] = { - /* MySQL */ +const char *select_recent_version_with_basejob_default = "SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, " - "File.FilenameId AS FilenameId, LStat, MD5 " + "File.FilenameId AS FilenameId, LStat, MD5, MarkId, " + "Job.JobTDate AS JobTDate " "FROM Job, File, ( " "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId " "FROM ( " @@ -295,7 +282,8 @@ const char *select_recent_version_with_basejob[5] = { "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 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)) " @@ -303,104 +291,107 @@ const char *select_recent_version_with_basejob[5] = { "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", + "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 " +const char *select_recent_version_with_basejob[] = { + /* MySQL */ + select_recent_version_with_basejob_default, + + /* Postgresql */ /* The DISTINCT ON () permits to avoid extra join */ + "SELECT DISTINCT ON (FilenameId, PathId) JobTDate, JobId, FileId, " + "FileIndex, PathId, FilenameId, LStat, MD5, MarkId " "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 ", + "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5, MarkId " + "FROM File WHERE JobId IN (%s) " + "UNION ALL " + "SELECT File.FileId, File.JobId, PathId, FilenameId, " + "File.FileIndex, LStat, MD5, MarkId " + "FROM BaseFiles JOIN File USING (FileId) " + "WHERE BaseFiles.JobId IN (%s) " + ") AS T JOIN Job USING (JobId) " + "ORDER BY FilenameId, PathId, JobTDate 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 */ + select_recent_version_with_basejob_default, - /* 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 */ + select_recent_version_with_basejob_default +}; - /* Ingres */ /* See Mysql section for doc */ +/* We do the same thing than the previous query, but we include + * all delta parts. If the file has been deleted, we can have irrelevant + * parts. + * + * The code that uses results should control the delta sequence with + * the following rules: + * First Delta = 0 + * Delta = Previous Delta + 1 + * + * If we detect a gap, we can discard further pieces + * If a file starts at 1 instead of 0, the file has been deleted, and further + * pieces are useless. + * + * This control should be reset for each new file + */ +const char *select_recent_version_with_basejob_and_delta_default = "SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, " - "File.FilenameId AS FilenameId, LStat, MD5 " + "File.FilenameId AS FilenameId, LStat, MD5, File.MarkId AS MarkId, " + "Job.JobTDate AS JobTDate " "FROM Job, File, ( " - "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId " + "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId, MarkId " "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 " + "SELECT JobTDate, PathId, FilenameId, MarkId " /* Get all normal files */ + "FROM File JOIN Job USING (JobId) " /* from selected backup */ + "WHERE File.JobId IN (%s) " + "UNION ALL " + "SELECT JobTDate, PathId, FilenameId, MarkId " /* 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, MarkId " /* the latest file version */ ") AS T1 " -"WHERE (Job.JobId IN ( " - "SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) " +"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 " - "AND Job.JobId = File.JobId " + "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" -}; + "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] = { +const char *select_recent_version_with_basejob_and_delta[] = { /* MySQL */ - "SELECT j1.JobId AS JobId, f1.FileId AS FileId, f1.FileIndex AS FileIndex, " + select_recent_version_with_basejob_and_delta_default, + + /* Postgresql */ /* The DISTINCT ON () permits to avoid extra join */ + "SELECT DISTINCT ON (FilenameId, PathId, MarkId) JobTDate, JobId, FileId, " + "FileIndex, PathId, FilenameId, LStat, MD5, MarkId " + "FROM " + "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5, MarkId " + "FROM File WHERE JobId IN (%s) " + "UNION ALL " + "SELECT File.FileId, File.JobId, PathId, FilenameId, " + "File.FileIndex, LStat, MD5, MarkId " + "FROM BaseFiles JOIN File USING (FileId) " + "WHERE BaseFiles.JobId IN (%s) " + ") AS T JOIN Job USING (JobId) " + "ORDER BY FilenameId, PathId, MarkId, JobTDate DESC ", + + /* SQLite3 */ + select_recent_version_with_basejob_and_delta_default, + + /* Ingres */ + select_recent_version_with_basejob_and_delta_default +}; + +/* Get the list of the last recent version with a given BaseJob jobid list + * We don't handle Delta with BaseJobs, they have only Full files + */ +const char *select_recent_version_default = + "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 " + "f1.LStat AS LStat, f1.MD5 AS MD5, j1.JobTDate " "FROM ( " /* Choose the last version for each Path/Filename */ "SELECT max(JobTDate) AS JobTDate, PathId, FilenameId " "FROM File JOIN Job USING (JobId) " @@ -411,64 +402,29 @@ const char *select_recent_version[5] = { "AND j1.JobId IN (%s) " "AND t1.FilenameId = f1.FilenameId " "AND t1.PathId = f1.PathId " - "AND j1.JobId = f1.JobId", + "AND j1.JobId = f1.JobId"; + +const char *select_recent_version[] = { + /* MySQL */ + select_recent_version_default, /* Postgresql */ - "SELECT DISTINCT ON (FilenameId, PathId) StartTime, JobId, FileId, " + "SELECT DISTINCT ON (FilenameId, PathId) JobTDate, 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", + "ORDER BY FilenameId, PathId, JobTDate DESC ", /* 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", + select_recent_version_default, /* 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" + select_recent_version_default }; +/* 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 " @@ -479,15 +435,16 @@ const char *create_temp_accurate_jobids_default = "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) " "ORDER BY Job.JobTDate DESC LIMIT 1"; -const char *create_temp_accurate_jobids[5] = { +const char *create_temp_accurate_jobids[] = { /* 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 " @@ -500,23 +457,22 @@ const char *create_temp_accurate_jobids[5] = { "ON COMMIT PRESERVE ROWS WITH NORECOVERY" }; -const char *create_temp_basefile[5] = { +const char *create_temp_basefile[] = { /* 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," @@ -524,7 +480,7 @@ const char *create_temp_basefile[5] = { "ON COMMIT PRESERVE ROWS WITH NORECOVERY" }; -const char *create_temp_new_basefile[5] = { +const char *create_temp_new_basefile[] = { /* Mysql */ "CREATE TEMPORARY TABLE new_basefile%lld AS " "SELECT Path.Path AS Path, Filename.Name AS Name, Temp.FileIndex AS FileIndex," @@ -534,6 +490,7 @@ const char *create_temp_new_basefile[5] = { "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," @@ -543,15 +500,7 @@ const char *create_temp_new_basefile[5] = { "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," @@ -561,6 +510,7 @@ const char *create_temp_new_basefile[5] = { "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," @@ -576,7 +526,7 @@ const char *create_temp_new_basefile[5] = { /* ====== ua_prune.c */ /* List of SQL commands to create temp table and indicies */ -const char *create_deltabs[5] = { +const char *create_deltabs[] = { /* MySQL */ "CREATE TEMPORARY TABLE DelCandidates (" "JobId INTEGER UNSIGNED NOT NULL, " @@ -584,6 +534,7 @@ const char *create_deltabs[5] = { "FileSetId INTEGER UNSIGNED, " "JobFiles INTEGER UNSIGNED, " "JobStatus BINARY(1))", + /* Postgresql */ "CREATE TEMPORARY TABLE DelCandidates (" "JobId INTEGER NOT NULL, " @@ -591,13 +542,7 @@ const char *create_deltabs[5] = { "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, " @@ -605,6 +550,7 @@ const char *create_deltabs[5] = { "FileSetId INTEGER UNSIGNED, " "JobFiles INTEGER UNSIGNED, " "JobStatus CHAR)", + /* Ingres */ "DECLARE GLOBAL TEMPORARY TABLE DelCandidates (" "JobId INTEGER NOT NULL, " @@ -634,15 +580,16 @@ const char *uap_upgrade_copies_oldest_job_default = ") " "GROUP BY PriorJobId "; /* one result per copy */ -const char *uap_upgrade_copies_oldest_job[5] = { +const char *uap_upgrade_copies_oldest_job[] = { /* 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 */ @@ -663,7 +610,7 @@ const char *uap_upgrade_copies_oldest_job[5] = { /* ======= ua_restore.c */ /* List Jobs where a particular file is saved */ -const char *uar_file[5] = { +const char *uar_file[] = { /* Mysql */ "SELECT Job.JobId as JobId," "CONCAT(Path.Path,Filename.Name) as Name, " @@ -673,6 +620,7 @@ const char *uar_file[5] = { "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, " @@ -682,15 +630,7 @@ const char *uar_file[5] = { "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, " @@ -700,6 +640,7 @@ const char *uar_file[5] = { "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, " @@ -711,7 +652,7 @@ const char *uar_file[5] = { "AND Filename.Name='%s' ORDER BY StartTime DESC FETCH FIRST 20 ROWS ONLY" }; -const char *uar_create_temp[5] = { +const char *uar_create_temp[] = { /* Mysql */ "CREATE TEMPORARY TABLE temp (" "JobId INTEGER UNSIGNED NOT NULL," @@ -725,6 +666,7 @@ const char *uar_create_temp[5] = { "StartFile INTEGER UNSIGNED," "VolSessionId INTEGER UNSIGNED," "VolSessionTime INTEGER UNSIGNED)", + /* Postgresql */ "CREATE TEMPORARY TABLE temp (" "JobId INTEGER NOT NULL," @@ -738,19 +680,7 @@ const char *uar_create_temp[5] = { "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," @@ -764,6 +694,7 @@ const char *uar_create_temp[5] = { "StartFile INTEGER UNSIGNED," "VolSessionId INTEGER UNSIGNED," "VolSessionTime INTEGER UNSIGNED)", + /* Ingres */ "DECLARE GLOBAL TEMPORARY TABLE temp (" "JobId INTEGER NOT NULL," @@ -780,29 +711,28 @@ const char *uar_create_temp[5] = { "ON COMMIT PRESERVE ROWS WITH NORECOVERY" }; -const char *uar_create_temp1[5] = { +const char *uar_create_temp1[] = { /* 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 @@ -811,7 +741,7 @@ const char *uar_create_temp1[5] = { * for each time it was backed up. */ -const char *uar_jobid_fileindex_from_dir[5] = { +const char *uar_jobid_fileindex_from_dir[] = { /* Mysql */ "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " "WHERE Job.JobId IN (%s) " @@ -822,6 +752,7 @@ const char *uar_jobid_fileindex_from_dir[5] = { "AND Path.PathId=File.Pathid " "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) " @@ -831,16 +762,7 @@ const char *uar_jobid_fileindex_from_dir[5] = { "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 " - "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 ", + /* SQLite3 */ "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " "WHERE Job.JobId IN (%s) " @@ -851,6 +773,7 @@ const char *uar_jobid_fileindex_from_dir[5] = { "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) " @@ -862,42 +785,229 @@ const char *uar_jobid_fileindex_from_dir[5] = { "AND Filename.FilenameId=File.FilenameId" }; -const char *sql_media_order_most_recently_written[5] = { +const char *sql_media_order_most_recently_written[] = { /* 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] = { +const char *sql_get_max_connections[] = { /* Mysql */ "SHOW VARIABLES LIKE 'max_connections'", + /* Postgresql */ "SHOW max_connections", - /* SQLite */ - "SELECT 0", + /* SQLite3 */ "SELECT 0", + + /* Ingres (TODO) */ + "SELECT 0" +}; + +/* TODO: Check for corner cases with MySQL and SQLite3 + * The Group By can return strange numbers when having multiple + * version of a file in the same dataset. + */ +const char *sql_bvfs_select[] = { + /* Mysql */ + "CREATE TABLE %s AS ( " + "SELECT JobId, FileIndex, FileId, max(JobTDate) as JobTDate " + "FROM btemp%s " + "GROUP BY PathId, FilenameId " + "HAVING FileIndex > 0)", + + /* Postgresql */ + "CREATE TABLE %s AS ( " + "SELECT JobId, FileIndex, FileId " + "FROM ( " + "SELECT DISTINCT ON (PathId, FilenameId) " + "JobId, FileIndex, FileId " + "FROM btemp%s " + "ORDER BY PathId, FilenameId, JobTDate DESC " + ") AS T " + "WHERE FileIndex > 0)", + + /* SQLite3 */ + "CREATE TABLE %s AS " + "SELECT JobId, FileIndex, FileId, max(JobTDate) as JobTDate " + "FROM btemp%s " + "GROUP BY PathId, FilenameId " + "HAVING FileIndex > 0", + + /* Ingres (TODO) */ "SELECT 0" }; -/* Row number of the max_connections setting */ -const uint32_t sql_get_max_connections_index[5] = { - /* Mysql */ - 1, +const char *sql_bvfs_list_files_default = +"SELECT 'F', T1.PathId, T1.FilenameId, Filename.Name, " + "File.JobId, File.LStat, File.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 */ - 0, + "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 */ - 0, + sql_bvfs_list_files_default, + /* SQLite3 */ - 0, + sql_bvfs_list_files_default, + /* Ingres (TODO) */ - 0 + sql_bvfs_list_files_default +}; + +const char *batch_lock_path_query[] = { + /* Mysql */ + "LOCK TABLES Path write, batch write, Path as p write", + + /* Postgresql */ + "BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE", + + /* SQLite3 */ + "BEGIN", + + /* Ingres */ + "BEGIN" +}; + +const char *batch_lock_filename_query[] = { + /* Mysql */ + "LOCK TABLES Filename write, batch write, Filename as f write", + + /* Postgresql */ + "BEGIN; LOCK TABLE Filename IN SHARE ROW EXCLUSIVE MODE", + + /* SQLite3 */ + "BEGIN", + + /* Ingres */ + "BEGIN" +}; + +const char *batch_unlock_tables_query[] = { + /* Mysql */ + "UNLOCK TABLES", + + /* Postgresql */ + "COMMIT", + + /* SQLite3 */ + "COMMIT", + + /* Ingres */ + "COMMIT" +}; + +const char *batch_fill_path_query[] = { + /* Mysql */ + "INSERT INTO Path (Path) " + "SELECT a.Path FROM " + "(SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS " + "(SELECT Path FROM Path AS p WHERE p.Path = a.Path)", + + /* Postgresql */ + "INSERT INTO Path (Path) " + "SELECT a.Path FROM " + "(SELECT DISTINCT Path FROM batch) AS a " + "WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) ", + + /* SQLite3 */ + "INSERT INTO Path (Path) " + "SELECT DISTINCT Path FROM batch " + "EXCEPT SELECT Path FROM Path", + + /* Ingres */ + "INSERT INTO Path (Path) " + "SELECT DISTINCT b.Path FROM batch b " + "WHERE NOT EXISTS (SELECT Path FROM Path p WHERE p.Path = b.Path)" +}; + +const char *batch_fill_filename_query[] = { + /* Mysql */ + "INSERT INTO Filename (Name) " + "SELECT a.Name FROM " + "(SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS " + "(SELECT Name FROM Filename AS f WHERE f.Name = a.Name)", + + /* Postgresql */ + "INSERT INTO Filename (Name) " + "SELECT a.Name FROM " + "(SELECT DISTINCT Name FROM batch) as a " + "WHERE NOT EXISTS " + "(SELECT Name FROM Filename WHERE Name = a.Name)", + + /* SQLite3 */ + "INSERT INTO Filename (Name) " + "SELECT DISTINCT Name FROM batch " + "EXCEPT SELECT Name FROM Filename", + + /* Ingres */ + "INSERT INTO Filename (Name) " + "SELECT DISTINCT b.Name FROM batch b " + "WHERE NOT EXISTS (SELECT Name FROM Filename f WHERE f.Name = b.Name)" +}; + +const char *match_query[] = { + /* Mysql */ + "MATCH", + + /* Postgresql */ + "~", + + /* SQLite3 */ + "MATCH", + + /* Ingres */ + "~" };