X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fdird%2Fsql_cmds.c;h=e0f97192e5d83226697b8509a634f97af21418c5;hb=4f7896c56213a88d17777db9041a9643ae541c5f;hp=5970758fe6572593c020e0c5c55934d300a7d5f3;hpb=544ab3d6540a48ab4363138bee972a13c89594c0;p=bacula%2Fbacula diff --git a/bacula/src/dird/sql_cmds.c b/bacula/src/dird/sql_cmds.c index 5970758fe6..e0f97192e5 100644 --- a/bacula/src/dird/sql_cmds.c +++ b/bacula/src/dird/sql_cmds.c @@ -6,24 +6,18 @@ * * Version $Id$ */ - /* - Copyright (C) 2002-2004 Kern Sibbald and John Walker + 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 as - published by the Free Software Foundation; either version 2 of - the License, or (at your option) any later version. + 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 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., 59 Temple Place - Suite 330, Boston, - MA 02111-1307, USA. + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + the file LICENSE for additional details. */ @@ -35,23 +29,25 @@ 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" -" FROM Client,Job,JobMedia,Media" -" WHERE Client.Name='%s'" -" AND Client.ClientId=Job.ClientId" -" AND JobStatus='T'" -" AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId" -" ORDER BY Job.StartTime"; + "SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime," + "JobFiles,JobBytes,VolumeName,MediaType,FileSet" + " 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 JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId" + " AND Job.FileSetId=FileSet.FileSetId" + " ORDER BY Job.StartTime"; /* ====== ua_prune.c */ -const char *cnt_File = "SELECT count(*) FROM File WHERE JobId=%s"; 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"; @@ -72,15 +68,14 @@ const char *drop_deltabs[] = { /* List of SQL commands to create temp table and indicies */ const char *create_deltabs[] = { - "CREATE TABLE DelCandidates (" -#ifdef HAVE_MYSQL + "CREATE TEMPORARY TABLE DelCandidates (" +#if defined(HAVE_MYSQL) "JobId INTEGER UNSIGNED NOT NULL, " "PurgedFiles TINYINT, " "FileSetId INTEGER UNSIGNED, " "JobFiles INTEGER UNSIGNED, " "JobStatus BINARY(1))", -#else -#ifdef HAVE_POSTGRESQL +#elif defined(HAVE_POSTGRESQL) "JobId INTEGER NOT NULL, " "PurgedFiles SMALLINT, " "FileSetId INTEGER, " @@ -92,7 +87,6 @@ const char *create_deltabs[] = { "FileSetId INTEGER UNSIGNED, " "JobFiles INTEGER UNSIGNED, " "JobStatus CHAR)", -#endif #endif "CREATE INDEX DelInx1 ON DelCandidates (JobId)", NULL}; @@ -114,15 +108,13 @@ const char *insert_delcand = * (i.e. no files, ...) for deletion. */ const char *select_backup_del = - "SELECT DISTINCT DelCandidates.JobId " + "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " "FROM Job,DelCandidates " "WHERE (Job.JobTDate<%s AND ((DelCandidates.JobFiles=0) OR " "(DelCandidates.JobStatus!='T'))) OR " "(Job.JobTDate>%s " "AND Job.ClientId=%s " - "AND Job.Type='B' " - "AND Job.Level='F' " - "AND Job.JobStatus='T' " + "AND Job.Level='F' AND Job.JobStatus='T' AND Job.Type='B' " "AND Job.FileSetId=DelCandidates.FileSetId)"; /* Select Jobs from the DelCandidates table that have a @@ -130,14 +122,12 @@ const char *select_backup_del = * This is the list of Jobs to delete for a Verify Job. */ const char *select_verify_del = - "SELECT DISTINCT DelCandidates.JobId " + "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " "FROM Job,DelCandidates " "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus!='T') 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='T' " "AND Job.FileSetId=DelCandidates.FileSetId)"; @@ -145,7 +135,7 @@ const char *select_verify_del = * This is the list of Jobs to delete for a Restore Job. */ const char *select_restore_del = - "SELECT DISTINCT DelCandidates.JobId " + "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " "FROM Job,DelCandidates " "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus!='T') OR " "(Job.JobTDate>%s " @@ -156,7 +146,7 @@ const char *select_restore_del = * This is the list of Jobs to delete for an Admin Job. */ const char *select_admin_del = - "SELECT DISTINCT DelCandidates.JobId " + "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " "FROM Job,DelCandidates " "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus!='T') OR " "(Job.JobTDate>%s " @@ -179,21 +169,23 @@ const char *uar_list_jobs = /* MYSQL IS NOT STANDARD SQL !!!!! */ /* List Jobs where a particular file is saved */ const char *uar_file = - "SELECT Job.JobId as JobId, Client.Name as Client, " + "SELECT Job.JobId as JobId," "CONCAT(Path.Path,Filename.Name) as Name, " - "StartTime,Type as JobType,JobFiles,JobBytes " - "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId " - "AND JobStatus='T' AND Job.JobId=File.JobId " + "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, Client.Name as Client, " + "SELECT Job.JobId as JobId," "Path.Path||Filename.Name as Name, " - "StartTime,Type as JobType,JobFiles,JobBytes " - "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId " - "AND JobStatus='T' AND Job.JobId=File.JobId " + "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 @@ -213,7 +205,7 @@ const char *uar_del_temp = "DROP TABLE temp"; const char *uar_del_temp1 = "DROP TABLE temp1"; const char *uar_create_temp = - "CREATE TABLE temp (" + "CREATE TEMPORARY TABLE temp (" #ifdef HAVE_POSTGRESQL "JobId INTEGER NOT NULL," "JobTDate BIGINT," @@ -241,7 +233,7 @@ const char *uar_create_temp = #endif const char *uar_create_temp1 = - "CREATE TABLE temp1 (" + "CREATE TEMPORARY TABLE temp1 (" #ifdef HAVE_POSTGRESQL "JobId INTEGER NOT NULL," "JobTDate BIGINT)"; @@ -255,7 +247,7 @@ const char *uar_last_full = "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 Level='F' AND JobStatus='T' AND Type='B' " "AND JobMedia.JobId=Job.JobId " "AND JobMedia.MediaId=Media.MediaId " "AND Job.FileSetId=FileSet.FileSetId " @@ -268,7 +260,7 @@ 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 Level='F' AND JobStatus='T' AND Type='B' " "AND JobMedia.JobId=Job.JobId " "AND JobMedia.MediaId=Media.MediaId"; @@ -282,7 +274,7 @@ const char *uar_dif = "AND Job.ClientId=%s " "AND JobMedia.JobId=Job.JobId " "AND JobMedia.MediaId=Media.MediaId " - "AND Job.Level='D' AND JobStatus='T' " + "AND Job.Level='D' AND JobStatus='T' AND Type='B' " "AND Job.FileSetId=FileSet.FileSetId " "AND FileSet.FileSet='%s' " "%s" @@ -298,15 +290,25 @@ const char *uar_inc = "AND Job.ClientId=%s " "AND JobMedia.JobId=Job.JobId " "AND JobMedia.MediaId=Media.MediaId " - "AND Job.Level='I' AND JobStatus='T' " + "AND Job.Level='I' AND JobStatus='T' 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" + " 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 const char *uar_sel_jobid_temp = "SELECT JobId FROM temp ORDER BY StartTime ASC"; @@ -326,7 +328,7 @@ const char *uar_sel_fileset = /* Find MediaType used by this Job */ const char *uar_mediatype = - "SELECT MediaType FROM JobMedia,Media WHERE JobMedia.JobId=%s" + "SELECT MediaType FROM JobMedia,Media WHERE JobMedia.JobId=%s " "AND JobMedia.MediaId=Media.MediaId"; /* @@ -358,15 +360,36 @@ const char *uar_jobids_fileindex = "AND Filename.FilenameId=File.FilenameId " "ORDER BY Job.StartTime DESC LIMIT 1"; -/* Query to get all files in a directory -- no recursing */ +/* 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. + * I.e. the same file will be restored multiple times, once + * for each time it was backed up. + */ + +#ifdef HAVE_POSTGRESQL const char *uar_jobid_fileindex_from_dir = "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client " "WHERE Job.JobId IN (%s) " - "WHERE Job.JobId=File.JobId " + "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"; +#else +const char *uar_jobid_fileindex_from_dir = + "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 "; +#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";