"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 ( "
"~",
/* SQLite3 */
- "MATCH",
+ "LIKE", /* MATCH doesn't seems to work anymore... */
/* Ingres */
"~"