X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fdird%2Fsql_cmds.c;h=e0f97192e5d83226697b8509a634f97af21418c5;hb=4f7896c56213a88d17777db9041a9643ae541c5f;hp=f5384140c3cea1cc18acb6b9aa2b10f669c4b74a;hpb=16415680c8b2c30cddd68400f906d410b4d344f0;p=bacula%2Fbacula

diff --git a/bacula/src/dird/sql_cmds.c b/bacula/src/dird/sql_cmds.c
index f5384140c3..e0f97192e5 100644
--- a/bacula/src/dird/sql_cmds.c
+++ b/bacula/src/dird/sql_cmds.c
@@ -7,7 +7,7 @@
  *   Version $Id$
  */
 /*
-   Copyright (C) 2002-2005 Kern Sibbald
+   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
@@ -30,12 +30,14 @@ 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"
+   "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 JobStatus='T' AND Type='B'" 
    " AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId"
+   " AND Job.FileSetId=FileSet.FileSetId"
    " ORDER BY Job.StartTime";
 
 
@@ -45,6 +47,7 @@ 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";
@@ -65,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, "
@@ -85,7 +87,6 @@ const char *create_deltabs[] = {
       "FileSetId INTEGER UNSIGNED, "
       "JobFiles INTEGER UNSIGNED, "
       "JobStatus CHAR)",
-#endif
 #endif
    "CREATE INDEX DelInx1 ON DelCandidates (JobId)",
    NULL};
@@ -107,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
@@ -123,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)";
 
 
@@ -138,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 "
@@ -149,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 "
@@ -191,7 +188,7 @@ const char *uar_file =
    "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
+#endif
 
 
 /*
@@ -208,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,"
@@ -236,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)";
@@ -250,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 "
@@ -263,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";
 
@@ -277,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"
@@ -293,7 +290,7 @@ 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";
@@ -393,3 +390,6 @@ const char *uar_jobid_fileindex_from_dir =
    "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";