- Add index file to JobId field of File records for PostgreSQL.

[bacula/bacula] / bacula / src / dird / sql_cmds.c

diff --git a/bacula/src/dird/sql_cmds.c b/bacula/src/dird/sql_cmds.c
index 042d3764bde0dd85b3242e0132f19cfbe8690893..10768d312d3c70fa34fd2360dd8ac3b4e7ffaf79 100644 (file)
--- a/bacula/src/dird/sql_cmds.c
+++ b/bacula/src/dird/sql_cmds.c
@@ -33,6 +33,18 @@
 /* For ua_cmds.c */
 const char *list_pool = "SELECT * FROM Pool WHERE PoolId=%u";
 
+/* For ua_dotcmds.c */
+const char *client_backups =
+"SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,"
+"JobFiles,JobBytes,VolumeName"
+" 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";
+
+
 /* ====== ua_prune.c */
 
 const char *cnt_File     = "SELECT count(*) FROM File WHERE JobId=%u";
@@ -102,48 +114,59 @@ const char *insert_delcand =
  * (i.e. no files, ...) for deletion.
  */
 const char *select_backup_del =
-   "SELECT DelCandidates.JobId "
+   "SELECT DISTINCT DelCandidates.JobId "
    "FROM Job,DelCandidates "
-   "WHERE (JobTDate<%s AND ((DelCandidates.JobFiles=0) OR "
+   "WHERE (Job.JobTDate<%s AND ((DelCandidates.JobFiles=0) OR "
    "(DelCandidates.JobStatus!='T'))) OR "
    "(Job.JobTDate>%s "
    "AND Job.ClientId=%u "
    "AND Job.Type='B' "
    "AND Job.Level='F' "
    "AND Job.JobStatus='T' "
-   "AND Job.FileSetId=DelCandidates.FileSetId) "
-   "GROUP BY DelCandidates.JobId";
+   "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 DelCandidates.JobId "
+   "SELECT DISTINCT DelCandidates.JobId "
    "FROM Job,DelCandidates "
-   "WHERE Job.JobTDate>%s "
+   "WHERE (Job.JobTdate<%s AND DelCandidates.JobStatus!='T') OR "
+   "(Job.JobTDate>%s "
    "AND Job.ClientId=%u "
    "AND Job.Type='V' "
    "AND Job.Level='V' "
    "AND Job.JobStatus='T' "
-   "AND Job.FileSetId=DelCandidates.FileSetId "
-   "GROUP BY DelCandidates.JobId";
+   "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 DelCandidates.JobId "
+   "SELECT DISTINCT DelCandidates.JobId "
    "FROM Job,DelCandidates "
-   "WHERE Job.JobTDate>%s "
+   "WHERE (Job.JobTdate<%s AND delCandidates.JobStatus!='T') OR "
+   "(Job.JobTDate>%s "
    "AND Job.ClientId=%u "   
-   "AND Job.Type='R' "
-   "GROUP BY DelCandidates.JobId";
+   "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 "
+   "FROM Job,DelCandidates "
+   "WHERE (Job.JobTdate<%s AND delCandidates.JobStatus!='T') OR "
+   "(Job.JobTDate>%s "
+   "AND Job.ClientId=%u "   
+   "AND Job.Type='D')";
 
 
 /* ======= ua_restore.c */
+const char *uar_count_files =
+   "SELECT JobFiles FROM Job WHERE JobId=%u";
 
 /* List last 20 Jobs */
 const char *uar_list_jobs = 
@@ -162,7 +185,7 @@ const char *uar_file =
    "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId "
    "AND JobStatus='T' AND Job.JobId=File.JobId "
    "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId "
-   "AND Filename.Name='%s' LIMIT 20";
+   "AND Filename.Name='%s' ORDER BY StartTime DESC LIMIT 20";
 #else
 /* List Jobs where a particular file is saved */
 const char *uar_file = 
@@ -172,7 +195,7 @@ const char *uar_file =
    "FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId "
    "AND JobStatus='T' AND Job.JobId=File.JobId "
    "AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId "
-   "AND Filename.Name='%s' LIMIT 20";
+   "AND Filename.Name='%s' ORDER BY StartTime DESC LIMIT 20";
 #endif
 
 
@@ -282,7 +305,7 @@ const char *uar_list_temp =
    "ORDER BY StartTime ASC";
 
 
-const char *uar_sel_jobid_temp = "SELECT JobId FROM temp";
+const char *uar_sel_jobid_temp = "SELECT JobId FROM temp ORDER BY StartTime ASC";
 
 const char *uar_sel_all_temp1 = "SELECT * FROM temp1";
 
@@ -313,6 +336,7 @@ const char *uar_jobid_fileindex =
    "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 "
    "ORDER BY Job.StartTime DESC LIMIT 1";