+ "AND Filename.FilenameId=File.FilenameId"
+};
+
+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",
+
+ /* 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[] = {
+ /* Mysql */
+ "SHOW VARIABLES LIKE 'max_connections'",
+
+ /* Postgresql */
+ "SHOW max_connections",
+
+ /* 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"
+};
+
+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 */
+ "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 */
+ sql_bvfs_list_files_default,
+
+ /* SQLite3 */
+ sql_bvfs_list_files_default,
+
+ /* Ingres (TODO) */
+ 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 */
+ "~"
+};
projects / bacula / bacula / blobdiff