" AND Job.FileSetId=FileSet.FileSetId"
" ORDER BY Job.StartTime";
-
/* ====== ua_prune.c */
-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_JobMedia = "DELETE FROM JobMedia WHERE JobId=%s";
-const char *cnt_JobMedia = "SELECT count(*) FROM JobMedia WHERE MediaId=%s";
-
const char *sel_JobMedia =
"SELECT DISTINCT JobMedia.JobId FROM JobMedia,Job "
"WHERE MediaId=%s AND Job.JobId=JobMedia.JobId "
"AND Job.JobTDate<%s";
-/* Count Select JobIds for File deletion */
-const char *count_select_job =
- "SELECT count(*) from Job "
- "WHERE JobTDate<%s "
- "AND ClientId=%s "
- "AND PurgedFiles=0";
-
-
-/* Select JobIds for File deletion. */
-const char *select_job =
- "SELECT DISTINCT JobId from Job "
- "WHERE JobTDate<%s "
- "AND ClientId=%s "
- "AND PurgedFiles=0";
-
/* Delete temp tables and indexes */
const char *drop_deltabs[] = {
"DROP TABLE DelCandidates",
"WHERE Job.ClientId=%s "
"GROUP BY FileSet";
-/* Find MediaType used by this Job */
-const char *uar_mediatype =
- "SELECT MediaType FROM JobMedia,Media WHERE JobMedia.JobId=%s "
- "AND JobMedia.MediaId=Media.MediaId";
-
/*
* Find JobId, FileIndex for a given path/file and date
* for use when inserting individual files into the tree.
/* 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";
+ "SELECT JobId,FileIndex FROM %s";
/* Get the list of the last recent version per Delta with a given jobid list
* This is a tricky part because with SQL the result of
*/
const char *select_recent_version_with_basejob_default =
"SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, "
- "File.FilenameId AS FilenameId, LStat, MD5, MarkId, "
+ "File.FilenameId AS FilenameId, LStat, MD5, DeltaSeq, "
"Job.JobTDate AS JobTDate "
"FROM Job, File, ( "
"SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId "
/* Postgresql */ /* The DISTINCT ON () permits to avoid extra join */
"SELECT DISTINCT ON (FilenameId, PathId) JobTDate, JobId, FileId, "
- "FileIndex, PathId, FilenameId, LStat, MD5, MarkId "
+ "FileIndex, PathId, FilenameId, LStat, MD5, DeltaSeq "
"FROM "
- "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5, MarkId "
+ "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5, DeltaSeq "
"FROM File WHERE JobId IN (%s) "
"UNION ALL "
"SELECT File.FileId, File.JobId, PathId, FilenameId, "
- "File.FileIndex, LStat, MD5, MarkId "
+ "File.FileIndex, LStat, MD5, DeltaSeq "
"FROM BaseFiles JOIN File USING (FileId) "
"WHERE BaseFiles.JobId IN (%s) "
") AS T JOIN Job USING (JobId) "
*/
const char *select_recent_version_with_basejob_and_delta_default =
"SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, "
- "File.FilenameId AS FilenameId, LStat, MD5, File.MarkId AS MarkId, "
+ "File.FilenameId AS FilenameId, LStat, MD5, File.DeltaSeq AS DeltaSeq, "
"Job.JobTDate AS JobTDate "
"FROM Job, File, ( "
- "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId, MarkId "
+ "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId, DeltaSeq "
"FROM ( "
- "SELECT JobTDate, PathId, FilenameId, MarkId " /* Get all normal files */
+ "SELECT JobTDate, PathId, FilenameId, DeltaSeq " /*Get all normal files*/
"FROM File JOIN Job USING (JobId) " /* from selected backup */
"WHERE File.JobId IN (%s) "
"UNION ALL "
- "SELECT JobTDate, PathId, FilenameId, MarkId " /* Get all files from */
+ "SELECT JobTDate, PathId, FilenameId, DeltaSeq " /*Get all files from */
"FROM BaseFiles " /* BaseJob */
"JOIN File USING (FileId) "
"JOIN Job ON (BaseJobId = Job.JobId) "
"WHERE BaseFiles.JobId IN (%s) " /* Use Max(JobTDate) to find */
") AS tmp "
- "GROUP BY PathId, FilenameId, MarkId " /* the latest file version */
+ "GROUP BY PathId, FilenameId, DeltaSeq " /* the latest file version */
") AS T1 "
"WHERE (Job.JobId IN ( " /* Security, we force JobId to be valid */
"SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) "
select_recent_version_with_basejob_and_delta_default,
/* Postgresql */ /* The DISTINCT ON () permits to avoid extra join */
- "SELECT DISTINCT ON (FilenameId, PathId, MarkId) JobTDate, JobId, FileId, "
- "FileIndex, PathId, FilenameId, LStat, MD5, MarkId "
+ "SELECT DISTINCT ON (FilenameId, PathId, DeltaSeq) JobTDate, JobId, FileId, "
+ "FileIndex, PathId, FilenameId, LStat, MD5, DeltaSeq "
"FROM "
- "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5, MarkId "
+ "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5,DeltaSeq "
"FROM File WHERE JobId IN (%s) "
"UNION ALL "
"SELECT File.FileId, File.JobId, PathId, FilenameId, "
- "File.FileIndex, LStat, MD5, MarkId "
+ "File.FileIndex, LStat, MD5, DeltaSeq "
"FROM BaseFiles JOIN File USING (FileId) "
"WHERE BaseFiles.JobId IN (%s) "
") AS T JOIN Job USING (JobId) "
- "ORDER BY FilenameId, PathId, MarkId, JobTDate DESC ",
+ "ORDER BY FilenameId, PathId, DeltaSeq, JobTDate DESC ",
/* SQLite3 */
select_recent_version_with_basejob_and_delta_default,
"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 ( "
"WHERE FileIndex > 0)",
/* SQLite3 */
- "SELECT 0",
+ "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', PathId, T1.FilenameId, Filename.Name, "
- "File.JobId, LStat, FileId "
+"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 ( "
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)",
+ "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) ",
+ "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",
+ "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)"
+ "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)",
+ "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)",
+ "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",
+ "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)"
+ "SELECT DISTINCT b.Name FROM batch b "
+ "WHERE NOT EXISTS (SELECT Name FROM Filename f WHERE f.Name = b.Name)"
};
const char *match_query[] = {
"~",
/* SQLite3 */
- "MATCH",
+ "LIKE", /* MATCH doesn't seems to work anymore... */
/* Ingres */
"~"