X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fsql_cmds.c;h=88afd3e024719478f321bc1921712c8c8018c3c2;hb=638e67a8c6628604327df2bb2f57cffb61a08016;hp=10bdbbdf35c97a3ee5bf67f34763e9b8e792f8fc;hpb=0daea3f3059b21be6e659b98b5ed0b38b57934d2;p=bacula%2Fbacula

diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c
index 10bdbbdf35..88afd3e024 100644
--- a/bacula/src/cats/sql_cmds.c
+++ b/bacula/src/cats/sql_cmds.c
@@ -26,12 +26,10 @@
    Switzerland, email:ftf@fsfeurope.org.
 */
 /*
- *
- *  This file contains all the SQL commands issued by the Director
+ *  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$
  */
 /*
  * Note, PostgreSQL imposes some constraints on using DISTINCT and GROUP BY
@@ -40,7 +38,6 @@
  *  because all the ORDER BY expressions must appear in the SELECT list!
  */
 
-
 #include "bacula.h"
 #include "cats.h"
 
@@ -430,26 +427,6 @@ const char *uar_jobids_fileindex =
 const char *uar_jobid_fileindex_from_table = 
    "SELECT JobId,FileIndex from %s";
 
-
-/*
- *
- *  This file contains all the SQL commands issued by the Director
- *
- *     Kern Sibbald, July MMII
- *
- *   Version $Id$
- */
-/*
- * 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"
-
 /* Get the list of the last recent version with a given jobid list 
  * This is a tricky part because with SQL the result of 
  *
@@ -650,6 +627,79 @@ const char *select_recent_version[5] = {
       "AND j1.JobId = f1.JobId"
 };
 
+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  */
@@ -688,7 +738,52 @@ const char *create_deltabs[5] = {
    "PurgedFiles SMALLINT, "
    "FileSetId INTEGER, "
    "JobFiles INTEGER, "
-   "JobStatus char(1))"
+   "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"
 };
 
@@ -801,14 +896,14 @@ const char *uar_create_temp[5] = {
    "JobId INTEGER NOT NULL,"
    "JobTDate BIGINT,"
    "ClientId INTEGER,"
-   "Level CHAR,"
+   "Level CHAR(1),"
    "JobFiles INTEGER,"
    "JobBytes BIGINT,"
-   "StartTime VARCHAR(256),"
-   "VolumeName VARCHAR(256),"
+   "StartTime TIMESTAMP WITHOUT TIME ZONE,"
+   "VolumeName VARBYTE(128),"
    "StartFile INTEGER,"
    "VolSessionId INTEGER,"
-   "VolSessionTime INTEGER)"
+   "VolSessionTime INTEGER) "
    "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
 };
 
@@ -832,7 +927,7 @@ const char *uar_create_temp1[5] = {
    /* Ingres */
    "DECLARE GLOBAL TEMPORARY TABLE temp1 ("
    "JobId INTEGER NOT NULL,"
-   "JobTDate BIGINT)"
+   "JobTDate BIGINT) "
    "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
    };
 
@@ -904,7 +999,7 @@ const char *sql_media_order_most_recently_written[5] = {
    /* SQLite3 */
    "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId",
    /* Ingres */
-   "ORDER BY IFNULL(LastWritten, '') DESC,MediaId"
+   "ORDER BY IFNULL(LastWritten, '1970-01-01 00:00:00') DESC,MediaId"
 };
 
 const char *sql_get_max_connections[5] = {