1/* autogenerated file by ./create_queryfiles.sh */
2
3const char *BareosDbPostgresql::query_definitions[] = {
4/* 0001_get_restore_objects */
5"SELECT JobId, ObjectLength, ObjectFullLength, ObjectIndex, ObjectType, ObjectCompression, FileIndex, ObjectName, RestoreObject, PluginName "
6  "FROM RestoreObject "
7 "WHERE JobId IN (%s) "
8   "AND ObjectType = %d "
9 "ORDER BY ObjectIndex ASC "
10,
11
12/* 0002_cleanup_created_job */
13"UPDATE Job "
14   "SET JobStatus = 'f', StartTime = SchedTime, EndTime = SchedTime "
15 "WHERE JobStatus = 'C' "
16,
17
18/* 0003_cleanup_running_job */
19"UPDATE Job "
20   "SET JobStatus = 'f', EndTime = StartTime "
21 "WHERE JobStatus = 'R' "
22,
23
24/* 0004_fill_jobhisto */
25"INSERT INTO JobHisto ( "
26	       "JobId, Job, Name, Type, Level, ClientId, JobStatus, SchedTime, StartTime, EndTime, RealEndTime, JobTDate, "
27	       "VolSessionId, VolSessionTime, JobFiles, JobBytes, ReadBytes, JobErrors, JobMissingFiles, PoolId, FileSetId, "
28	       "PriorJobId, PurgedFiles, "
29	       "HasBase, Reviewed, Comment "
30       ") "
31"SELECT JobId, Job, Name, Type, Level, ClientId, JobStatus, SchedTime, StartTime, EndTime, RealEndTime, JobTDate, VolSessionId, "
32       "VolSessionTime, JobFiles, JobBytes, ReadBytes, JobErrors, JobMissingFiles, PoolId, FileSetId, PriorJobId, PurgedFiles, "
33       "HasBase, "
34       "Reviewed, Comment "
35  "FROM Job "
36 "WHERE JobStatus IN ('T','W','f','A','E') "
37   "AND NOT EXISTS ( "
38		"SELECT JobHisto.JobId "
39		  "FROM JobHisto "
40		 "WHERE JobHisto.Jobid = Job.JobId "
41       ") "
42   "AND JobTDate       < %s "
43,
44
45/* 0005_list_pool */
46"SELECT * "
47  "FROM Pool "
48 "WHERE PoolId = %s "
49,
50
51/* 0006_list_jobs */
52"SELECT DISTINCT Job.JobId,Job.Name, Client.Name as Client, "
53"Job.StartTime,Job.Type,Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus "
54"FROM Job "
55"LEFT JOIN Client ON Client.ClientId=Job.ClientId "
56"LEFT JOIN JobMedia ON JobMedia.JobId=Job.JobId "
57"LEFT JOIN Media ON JobMedia.MediaId=Media.MediaId "
58"LEFT JOIN FileSet ON FileSet.FileSetId=Job.FileSetId "
59"WHERE Job.JobId > 0 "
60"%s "
61"ORDER BY StartTime%s; "
62,
63
64/* 0007_list_jobs_long */
65"SELECT DISTINCT Job.JobId, Job.Job, Job.Name, Job.PurgedFiles, Job.Type, Job.Level, "
66"Job.ClientId, Client.Name as Client, Job.JobStatus, Job.SchedTime, Job.StartTime, "
67"Job.EndTime, Job.RealEndTime, Job.JobTDate, Job.VolSessionId, Job.VolSessionTime, "
68"Job.JobFiles, Job.JobBytes, Job.JobErrors, Job.JobMissingFiles, Job.PoolId, "
69"Pool.Name as PoolName, Job.PriorJobId, Job.FileSetId, FileSet.FileSet "
70"FROM Job "
71"LEFT JOIN Client ON Client.ClientId=Job.ClientId "
72"LEFT JOIN Pool ON Pool.PoolId=Job.PoolId "
73"LEFT JOIN JobMedia ON JobMedia.JobId=Job.JobId "
74"LEFT JOIN Media ON JobMedia.MediaId=Media.MediaId "
75"LEFT JOIN FileSet ON FileSet.FileSetId=Job.FileSetId "
76"WHERE Job.JobId > 0 "
77"%s "
78"ORDER BY StartTime%s; "
79,
80
81/* 0008_list_jobs_count */
82"SELECT DISTINCT COUNT(DISTINCT Job.JobId) as count "
83"FROM Job "
84"LEFT JOIN Client ON Client.ClientId=Job.ClientId "
85"LEFT JOIN JobMedia ON JobMedia.JobId=Job.JobId "
86"LEFT JOIN Media ON JobMedia.MediaId=Media.MediaId "
87"LEFT JOIN FileSet ON FileSet.FileSetId=Job.FileSetId "
88"WHERE Job.JobId > 0 "
89"%s "
90"%s; "
91,
92
93/* 0009_list_jobs_last */
94"SELECT DISTINCT Job.JobId,Job.Name, Client.Name as Client, "
95"Job.StartTime,Job.Type,Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus "
96"FROM Job "
97"LEFT JOIN Client ON Client.ClientId=Job.ClientId "
98"LEFT JOIN JobMedia ON JobMedia.JobId=Job.JobId "
99"LEFT JOIN Media ON JobMedia.MediaId=Media.MediaId "
100"LEFT JOIN FileSet ON FileSet.FileSetId=Job.FileSetId "
101"INNER JOIN ( "
102   "SELECT MAX(Job.JobId) as MaxJobId "
103   "FROM Job "
104   "LEFT JOIN Client ON Client.ClientId=Job.ClientId "
105   "LEFT JOIN Pool ON Pool.PoolId=Job.PoolId "
106   "LEFT JOIN JobMedia ON JobMedia.JobId=Job.JobId "
107   "LEFT JOIN Media ON JobMedia.MediaId=Media.MediaId "
108   "LEFT JOIN FileSet ON FileSet.FileSetId=Job.FileSetId "
109   "WHERE Job.JobId > 0 "
110   "%s "
111   "GROUP BY Job.Name "
112") LastJob "
113"ON Job.JobId = LastJob.MaxJobId "
114"ORDER BY StartTime%s; "
115,
116
117/* 0010_list_jobs_long_last */
118"SELECT DISTINCT Job.JobId, Job.Job, Job.Name, Job.PurgedFiles, Job.Type, Job.Level, "
119"Job.ClientId, Client.Name as Client, Job.JobStatus, Job.SchedTime, Job.StartTime, "
120"Job.EndTime, Job.RealEndTime, Job.JobTDate, Job.VolSessionId, Job.VolSessionTime, "
121"Job.JobFiles, Job.JobBytes, Job.JobErrors, Job.JobMissingFiles, Job.PoolId, "
122"Pool.Name as PoolName, Job.PriorJobId, Job.FileSetId, FileSet.FileSet "
123"FROM Job "
124"LEFT JOIN Client ON Client.ClientId=Job.ClientId "
125"LEFT JOIN Pool ON Pool.PoolId=Job.PoolId "
126"LEFT JOIN JobMedia ON JobMedia.JobId=Job.JobId "
127"LEFT JOIN Media ON JobMedia.MediaId=Media.MediaId "
128"LEFT JOIN FileSet ON FileSet.FileSetId=Job.FileSetId "
129"INNER JOIN ( "
130   "SELECT MAX(Job.JobId) as MaxJobId "
131   "FROM Job "
132   "LEFT JOIN Client ON Client.ClientId=Job.ClientId "
133   "LEFT JOIN Pool ON Pool.PoolId=Job.PoolId "
134   "LEFT JOIN JobMedia ON JobMedia.JobId=Job.JobId "
135   "LEFT JOIN Media ON JobMedia.MediaId=Media.MediaId "
136   "LEFT JOIN FileSet ON FileSet.FileSetId=Job.FileSetId "
137   "WHERE Job.JobId > 0 "
138   "%s "
139   "GROUP BY Job.Name "
140") LastJob "
141"ON Job.JobId = LastJob.MaxJobId "
142"ORDER BY StartTime%s; "
143,
144
145/* 0011_sel_JobMedia */
146"SELECT DISTINCT JobMedia.JobId "
147  "FROM JobMedia, Job "
148 "WHERE MediaId      = %s "
149   "AND Job.JobId    = JobMedia.JobId "
150   "AND Job.JobTDate < %s "
151,
152
153/* 0012_drop_deltabs.postgresql */
154"DROP TABLE IF EXISTS DelCandidates "
155,
156
157/* 0013_create_deltabs.postgresql */
158"CREATE TEMPORARY TABLE "
159       "DelCandidates ( JobId INTEGER NOT NULL, PurgedFiles SMALLINT, FileSetId INTEGER, JobFiles INTEGER, JobStatus char(1)) "
160,
161
162/* 0014_create_delindex */
163"CREATE INDEX DelInx1 ON DelCandidates (JobId) "
164,
165
166/* 0015_uar_count_files */
167"SELECT JobFiles "
168  "FROM Job "
169 "WHERE JobId = %s "
170,
171
172/* 0016_uar_list_jobs */
173"SELECT JobId, Client.Name as Client, StartTime, Level as JobLevel, JobFiles, JobBytes "
174  "FROM Client, Job "
175 "WHERE Client.ClientId = Job.ClientId "
176   "AND JobStatus IN ('T','W') "
177   "AND Type            = 'B' "
178 "ORDER BY StartTime DESC LIMIT 20 "
179,
180
181/* 0017_uar_print_jobs */
182"SELECT DISTINCT JobId, Level, JobFiles, JobBytes, StartTime, VolumeName "
183  "FROM Job "
184  "JOIN JobMedia "
185 "USING (JobId) "
186  "JOIN Media "
187 "USING (MediaId) "
188 "WHERE JobId IN (%s) "
189 "ORDER BY StartTime ASC "
190,
191
192/* 0018_uar_del_temp */
193"DROP TABLE temp "
194,
195
196/* 0019_uar_del_temp1 */
197"DROP TABLE temp1 "
198,
199
200/* 0020_uar_last_full */
201"INSERT INTO temp1 "
202"SELECT Job.JobId, JobTdate "
203  "FROM Job, JobMedia, Media, FileSet "
204 "WHERE Job.ClientId     = %s "
205   "AND Job.StartTime    < '%s' "
206   "AND Level            = 'F' "
207   "AND JobStatus IN ('T','W') "
208   "AND Type             = 'B' "
209   "AND JobMedia.JobId   = Job.JobId "
210   "AND Media.Enabled    = 1 "
211   "AND JobMedia.MediaId = Media.MediaId "
212   "AND Job.FileSetId    = FileSet.FileSetId "
213   "AND FileSet.FileSet  = '%s' %s "
214 "ORDER BY Job.JobTDate DESC LIMIT 1 "
215,
216
217/* 0021_uar_last_full_no_pool */
218"INSERT INTO temp1 "
219"SELECT Job.JobId, JobTdate "
220  "FROM Job, FileSet "
221 "WHERE Job.ClientId    = %s "
222   "AND Job.StartTime   < '%s' "
223   "AND Level           = 'F' "
224   "AND JobStatus IN ('T','W') "
225   "AND Type            = 'B' "
226   "AND Job.FileSetId   = FileSet.FileSetId "
227   "AND FileSet.FileSet = '%s' "
228 "ORDER BY Job.JobTDate DESC LIMIT 1 "
229,
230
231/* 0022_uar_full */
232"INSERT INTO temp "
233"SELECT Job.JobId, Job.JobTDate, Job.ClientId, Job.Level, Job.JobFiles, Job.JobBytes, StartTime, VolumeName, JobMedia.StartFile, "
234       "VolSessionId, VolSessionTime "
235  "FROM temp1, Job, JobMedia, Media "
236 "WHERE temp1.JobId      = Job.JobId "
237   "AND Level            = 'F' "
238   "AND JobStatus IN ('T','W') "
239   "AND Type             = 'B' "
240   "AND Media.Enabled    = 1 "
241   "AND JobMedia.JobId   = Job.JobId "
242   "AND JobMedia.MediaId = Media.MediaId "
243,
244
245/* 0023_uar_dif */
246"INSERT INTO temp "
247"SELECT Job.JobId, Job.JobTDate, Job.ClientId, Job.Level, Job.JobFiles, Job.JobBytes, Job.StartTime, Media.VolumeName, "
248       "JobMedia.StartFile, Job.VolSessionId, Job.VolSessionTime "
249  "FROM Job, JobMedia, Media, FileSet "
250 "WHERE Job.JobTDate     > %s "
251   "AND Job.StartTime    < '%s' "
252   "AND Job.ClientId     = %s "
253   "AND JobMedia.JobId   = Job.JobId "
254   "AND Media.Enabled    = 1 "
255   "AND JobMedia.MediaId = Media.MediaId "
256   "AND Job.Level        = 'D' "
257   "AND JobStatus IN ('T','W') "
258   "AND Type             = 'B' "
259   "AND Job.FileSetId    = FileSet.FileSetId "
260   "AND FileSet.FileSet  = '%s' %s "
261 "ORDER BY Job.JobTDate DESC LIMIT 1 "
262,
263
264/* 0024_uar_inc */
265"INSERT INTO temp "
266"SELECT Job.JobId, Job.JobTDate, Job.ClientId, Job.Level, Job.JobFiles, Job.JobBytes, Job.StartTime, Media.VolumeName, "
267       "JobMedia.StartFile, Job.VolSessionId, Job.VolSessionTime "
268  "FROM Job, JobMedia, Media, FileSet "
269 "WHERE Job.JobTDate     > %s "
270   "AND Job.StartTime    < '%s' "
271   "AND Job.ClientId     = %s "
272   "AND Media.Enabled    = 1 "
273   "AND JobMedia.JobId   = Job.JobId "
274   "AND JobMedia.MediaId = Media.MediaId "
275   "AND Job.Level        = 'I' "
276   "AND JobStatus IN ('T','W') "
277   "AND Type             = 'B' "
278   "AND Job.FileSetId    = FileSet.FileSetId "
279   "AND FileSet.FileSet  = '%s' %s "
280,
281
282/* 0025_uar_list_jobs_by_idlist */
283"SELECT DISTINCT Job.JobId, Job.Level, Job.JobFiles, Job.JobBytes, Job.StartTime, Media.VolumeName "
284  "FROM Job, JobMedia, Media "
285 "WHERE Job.JobId IN (%s) "
286   "AND JobMedia.JobId   = Job.JobId "
287   "AND Media.Enabled    = 1 "
288   "AND JobMedia.MediaId = Media.MediaId "
289 "ORDER BY StartTime ASC "
290,
291
292/* 0026_uar_sel_jobid_temp */
293"SELECT DISTINCT JobId, StartTime "
294  "FROM temp "
295 "ORDER BY StartTime ASC "
296,
297
298/* 0027_uar_sel_all_temp1 */
299"SELECT * "
300  "FROM temp1 "
301,
302
303/* 0028_uar_sel_all_temp */
304"SELECT * "
305  "FROM temp "
306,
307
308/* 0029_uar_sel_fileset */
309"SELECT DISTINCT FileSet.FileSet "
310  "FROM Job, Client, FileSet "
311 "WHERE Job.FileSetId   = FileSet.FileSetId "
312   "AND Job.ClientId    = %s "
313   "AND Client.ClientId = %s "
314 "ORDER BY FileSet.FileSet "
315,
316
317/* 0030_uar_sel_filesetid */
318"SELECT MAX(FileSet.FileSetId) "
319  "FROM FileSet "
320  "JOIN Job "
321 "USING (FileSetId) "
322 "WHERE Job.ClientId = %s "
323 "GROUP BY FileSet "
324,
325
326/* 0031_uar_jobid_fileindex */
327"SELECT Job.JobId, "
328       "File.FileIndex "
329"FROM Job, "
330     "File, "
331     "Path, "
332     "Client "
333"WHERE Job.JobId=File.JobId "
334  "AND Job.StartTime<='%s' "
335  "AND Path.Path='%s' "
336  "AND File.Name='%s' "
337  "AND Client.Name='%s' "
338  "AND Job.ClientId=Client.ClientId "
339  "AND Path.PathId=File.PathId "
340  "AND JobStatus IN ('T', "
341                    "'W') "
342  "AND TYPE='B' "
343"ORDER BY Job.StartTime DESC "
344"LIMIT 1 "
345,
346
347/* 0032_uar_jobids_fileindex */
348"SELECT Job.JobId, "
349       "File.FileIndex "
350"FROM Job, "
351     "File, "
352     "Path, "
353     "Client "
354"WHERE Job.JobId IN (%s) "
355  "AND Job.JobId=File.JobId "
356  "AND Job.StartTime<='%s' "
357  "AND Path.Path='%s' "
358  "AND File.Name='%s' "
359  "AND Client.Name='%s' "
360  "AND Job.ClientId=Client.ClientId "
361  "AND Path.PathId=File.PathId "
362"ORDER BY Job.StartTime DESC "
363"LIMIT 1 "
364,
365
366/* 0033_uar_jobid_fileindex_from_table */
367"SELECT JobId, FileIndex "
368  "FROM %s "
369 "ORDER BY JobId, FileIndex ASC "
370,
371
372/* 0034_select_recent_version_with_basejob.postgresql */
373"SELECT DISTINCT ON (Name, "
374                    "PathId) JobTDate, "
375                   "JobId, "
376                   "FileId, "
377                   "FileIndex, "
378                   "PathId, "
379                   "FileName AS Name, "
380                   "LStat, "
381                   "MD5, "
382                   "DeltaSeq, "
383                   "Fhinfo, "
384                   "Fhnode "
385"FROM "
386  "(SELECT FileId, "
387          "JobId, "
388          "PathId, "
389          "Name AS FileName, "
390          "FileIndex, "
391          "LStat, "
392          "MD5, "
393          "DeltaSeq, "
394          "Fhinfo, "
395          "Fhnode "
396   "FROM File "
397   "WHERE JobId IN (%s) "
398     "UNION ALL "
399     "SELECT File.FileId, "
400            "File.JobId, "
401            "PathId, "
402            "File.Name AS FileName, "
403            "File.FileIndex, "
404            "LStat, "
405            "MD5, "
406            "DeltaSeq, "
407            "Fhinfo, "
408            "Fhnode "
409     "FROM BaseFiles "
410     "JOIN File USING (FileId) WHERE BaseFiles.JobId IN (%s) ) AS T "
411"JOIN Job USING (JobId) "
412"ORDER BY Name, "
413         "PathId, "
414         "JobTDate DESC "
415,
416
417/* 0035_select_recent_version_with_basejob_and_delta.postgresql */
418"SELECT DISTINCT ON (Name, "
419                    "PathId, "
420                    "DeltaSeq) JobTDate, "
421                   "JobId, "
422                   "FileId, "
423                   "FileIndex, "
424                   "PathId, "
425                   "Filename AS Name, "
426                   "LStat, "
427                   "MD5, "
428                   "DeltaSeq, "
429                   "Fhinfo, "
430                   "Fhnode "
431"FROM "
432  "(SELECT FileId, "
433          "JobId, "
434          "PathId, "
435          "Name AS FileName, "
436          "FileIndex, "
437          "LStat, "
438          "MD5, "
439          "DeltaSeq, "
440          "Fhinfo, "
441          "Fhnode "
442   "FROM File "
443   "WHERE JobId IN (%s) "
444     "UNION ALL "
445     "SELECT File.FileId, "
446            "File.JobId, "
447            "PathId, "
448            "File.Name AS FileName, "
449            "File.FileIndex, "
450            "LStat, "
451            "MD5, "
452            "DeltaSeq, "
453            "Fhinfo, "
454            "Fhnode "
455     "FROM BaseFiles "
456     "JOIN File USING (FileId) WHERE BaseFiles.JobId IN (%s) ) AS T "
457"JOIN Job USING (JobId) "
458"ORDER BY Name, "
459         "PathId, "
460         "DeltaSeq, "
461         "JobTDate DESC "
462,
463
464/* 0036_select_recent_version.postgresql */
465"SELECT DISTINCT ON (File.Name, "
466                    "PathId) JobTDate, "
467                   "JobId, "
468                   "FileId, "
469                   "FileIndex, "
470                   "PathId, "
471                   "File.Name, "
472                   "LStat, "
473                   "MD5, "
474                   "Fhinfo, "
475                   "Fhnode "
476"FROM File "
477"JOIN Job USING (JobId) "
478"WHERE JobId IN (%s) "
479"ORDER BY File.Name, "
480         "PathId, "
481         "JobTDate DESC "
482,
483
484/* 0037_create_temp_accurate_jobids */
485"CREATE TABLE btemp3%s AS "
486"SELECT JobId, "
487       "StartTime, "
488       "EndTime, "
489       "JobTDate, "
490       "PurgedFiles "
491"FROM Job "
492"JOIN FileSet USING (FileSetId) "
493"WHERE ClientId = %s "
494  "AND Level = 'F' "
495  "AND JobStatus IN ('T', "
496                    "'W') "
497  "AND Type = 'B' "
498  "AND StartTime < '%s' "
499  "AND FileSet.FileSetId = %s "
500"ORDER BY Job.JobId DESC, "
501         "Job.JobTDate DESC "
502"LIMIT 1 "
503,
504
505/* 0038_create_temp_basefile */
506"CREATE TEMPORARY TABLE basefile%lld ( Path TEXT, Name TEXT) "
507,
508
509/* 0039_create_temp_new_basefile */
510"CREATE "
511"TEMPORARY TABLE new_basefile%lld AS "
512"SELECT Path.Path AS Path, "
513       "Temp.Name AS Name, "
514       "Temp.FileIndex AS FileIndex, "
515       "Temp.JobId AS JobId, "
516       "Temp.LStat AS LStat, "
517       "Temp.FileId AS FileId, "
518       "Temp.MD5 AS MD5, "
519       "Temp.Fhinfo AS Fhinfo, "
520       "Temp.Fhnode AS Fhnode "
521"FROM (%s) AS Temp "
522"JOIN Path ON (Path.PathId = Temp.PathId) "
523"WHERE Temp.FileIndex > 0 "
524,
525
526/* 0040_uap_upgrade_copies_oldest_job */
527"CREATE TEMPORARY TABLE cpy_tmp AS "
528"SELECT MIN(JobId) AS JobId "
529  "FROM Job "
530 "WHERE Type = '%c' "
531   "AND ( "
532          "PriorJobId IN (%s) "
533       "OR PriorJobId IN ( "
534         "SELECT PriorJobId "
535           "FROM Job "
536          "WHERE JobId IN (%s) "
537            "AND Type = 'B' "
538          ") "
539       ") "
540 "GROUP BY PriorJobId "
541,
542
543/* 0041_uar_file */
544"SELECT Job.JobId AS JobId, "
545       "Path.Path||File.Name AS Name, "
546       "StartTime, "
547       "TYPE AS JobType, "
548               "JobStatus, "
549               "JobFiles, "
550               "JobBytes "
551"FROM Client, "
552     "Job, "
553     "File, "
554     "Path "
555"WHERE Client.Name='%s' "
556  "AND Client.ClientId=Job.ClientId "
557  "AND Job.JobId=File.JobId "
558  "AND File.FileIndex > 0 "
559  "AND Path.PathId=File.PathId "
560  "AND File.Name='%s' "
561"ORDER BY StartTime DESC "
562"LIMIT 20 "
563,
564
565/* 0042_uar_create_temp.postgresql */
566"CREATE TEMPORARY TABLE "
567       "temp ( "
568          "JobId INTEGER NOT NULL, JobTDate BIGINT, ClientId INTEGER, Level CHAR, JobFiles INTEGER, JobBytes BIGINT, StartTime "
569          "TEXT, VolumeName TEXT, StartFile INTEGER, VolSessionId INTEGER, VolSessionTime INTEGER "
570       ") "
571,
572
573/* 0043_uar_create_temp1.postgresql */
574"CREATE TEMPORARY TABLE temp1 ( JobId INTEGER NOT NULL, JobTDate BIGINT) "
575,
576
577/* 0044_uar_jobid_fileindex_from_dir.postgresql */
578"SELECT Job.JobId, "
579       "File.FileIndex "
580"FROM Job, "
581     "File, "
582     "Path, "
583     "Client "
584"WHERE Job.JobId IN (%s) "
585  "AND Job.JobId=File.JobId "
586  "AND Path.Path='%s' "
587  "AND Client.Name='%s' "
588  "AND Job.ClientId=Client.ClientId "
589  "AND Path.PathId=File.Pathid "
590,
591
592/* 0045_sql_media_order_most_recently_written */
593"ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId "
594,
595
596/* 0046_sql_get_max_connections.postgresql */
597"SHOW max_connections "
598,
599
600/* 0047_bvfs_select.postgresql */
601"CREATE TABLE %s AS "
602  "(SELECT JobId, "
603          "FileIndex, "
604          "FileId "
605   "FROM "
606     "(SELECT DISTINCT ON (PathId, "
607                          "Name) JobId, "
608                         "FileIndex, "
609                         "FileId "
610      "FROM btemp%s "
611      "ORDER BY PathId, "
612               "Name, "
613               "JobTDate DESC) AS T "
614   "WHERE FileIndex > 0) "
615,
616
617/* 0048_bvfs_list_files.postgresql */
618"SELECT TYPE, "
619       "PathId, "
620       "Name, "
621       "JobId, "
622       "LStat, "
623       "FileId "
624"FROM ( "
625   "SELECT DISTINCT ON (Name) 'F' AS TYPE, "
626                       "PathId, "
627                       "FileName AS Name, "
628                       "JobId, "
629                       "LStat, "
630                       "FileId, "
631                       "FileIndex "
632   "FROM ( "
633      "SELECT FileId, "
634             "JobId, "
635             "PathId, "
636             "Name AS FileName, "
637             "FileIndex, "
638             "LStat, "
639             "MD5 "
640      "FROM File "
641      "WHERE JobId IN (%s) "
642        "AND PathId = %s "
643        "UNION ALL "
644        "SELECT File.FileId, "
645               "File.JobId, "
646               "PathId, "
647               "File.Name AS FileName, "
648               "File.FileIndex, "
649               "LStat, "
650               "MD5 "
651        "FROM BaseFiles "
652        "JOIN File USING (FileId) WHERE BaseFiles.JobId IN (%s) "
653        "AND File.PathId = %s "
654   ") AS T "
655   "JOIN Job USING (JobId) "
656   "WHERE "
657      "FileName != '' "
658      "%s "
659   "ORDER BY FileName, "
660            "StartTime DESC "
661") AS A "
662"WHERE A.FileIndex > 0 "
663"LIMIT %lld "
664"OFFSET %lld "
665,
666
667/* 0049_batch_lock_path_query.postgresql */
668"BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE "
669,
670
671/* 0050_batch_lock_filename_query.postgresql */
672"BEGIN; LOCK TABLE Filename IN SHARE ROW EXCLUSIVE MODE "
673,
674
675/* 0051_batch_unlock_tables_query */
676"COMMIT "
677,
678
679/* 0052_batch_fill_path_query.postgresql */
680"INSERT INTO Path (Path) "
681"SELECT a.Path "
682  "FROM ( "
683      "SELECT DISTINCT Path "
684        "FROM batch "
685       ") AS a "
686 "WHERE NOT EXISTS ( "
687      "SELECT Path "
688        "FROM Path "
689       "WHERE Path = a.Path "
690       ") "
691,
692
693/* 0053_batch_fill_filename_query.postgresql */
694"INSERT INTO Filename (Name) "
695"SELECT a.Name "
696  "FROM ( "
697      "SELECT DISTINCT Name "
698        "FROM batch "
699       ") as a "
700 "WHERE NOT EXISTS ( "
701      "SELECT Name "
702        "FROM Filename "
703       "WHERE Name = a.Name "
704       ") "
705,
706
707/* 0054_match_query.postgresql */
708 "AND Path2.Path ~ '%s' "
709,
710
711/* 0055_match_query2.postgresql */
712 "AND Filename.Name ~ '%s' "
713,
714
715/* 0056_insert_counter_values */
716"INSERT INTO Counters (Counter, MinValue, MaxValue, CurrentValue, WrapCounter) "
717"VALUES ('%s','%d','%d','%d','%s') "
718,
719
720/* 0057_select_counter_values */
721"SELECT MinValue, MaxValue, CurrentValue, WrapCounter "
722  "FROM Counters "
723 "WHERE Counter = '%s' "
724,
725
726/* 0058_update_counter_values */
727"UPDATE Counters "
728   "SET MinValue = %d, MaxValue = %d, CurrentValue = %d, WrapCounter = '%s' "
729 "WHERE Counter  = '%s' "
730,
731
732/* 0059_get_quota_jobbytes.postgresql */
733"SELECT SUM(JobBytes) "
734  "FROM Job "
735 "WHERE ClientId  =  %s "
736   "AND JobId     != %s "
737   "AND SchedTime >  TIMESTAMP '%s' "
738,
739
740/* 0060_get_quota_jobbytes_nofailed.postgresql */
741"SELECT SUM(JobBytes) "
742  "FROM Job "
743 "WHERE ClientId  =  %s "
744   "AND Job.JobId != %s "
745   "AND SchedTime >  TIMESTAMP '%s' "
746   "AND JobStatus NOT IN ('E','f','A') "
747,
748
749/* 0061_uar_sel_jobid_copies */
750"SELECT DISTINCT JobId, StartTime "
751  "FROM Job "
752 "WHERE Type = 'C' "
753   "AND (PriorJobId IN (%s)) "
754 "ORDER BY StartTime ASC "
755,
756
757/* 0062_get_jobstatus_details */
758"SELECT DISTINCT JobStatus, JobStatusLong, Severity, "
759       "CASE "
760       "WHEN JobStatus in ('T') THEN '0' "
761       "WHEN JobStatus in ('W') THEN '1' "
762       "WHEN JobStatus in ('e', 'f', 'A', 'E') THEN '2' "
763       "END as ExitLevel, "
764       "CASE "
765       "WHEN JobStatus in ('T') THEN 'Ok' "
766       "WHEN JobStatus in ('W') THEN 'Warning' "
767       "WHEN JobStatus in ('e', 'f', 'A', 'E') THEN 'Error' "
768       "END as ExitStatus "
769  "FROM Status %s "
770 "ORDER BY JobStatus "
771,
772
773/* 0063_bvfs_versions_6 */
774"SELECT 'V', File.PathId, File.Name, File.JobId, File.LStat, File.FileId, File.Md5, Media.VolumeName, Media.InChanger "
775"FROM File, Job, Client, JobMedia, Media "
776"WHERE File.Name = '%s' "
777  "AND File.PathId = %s "
778  "AND File.JobId = Job.JobId "
779  "AND Job.JobId = JobMedia.JobId "
780  "AND File.FileIndex >= JobMedia.FirstIndex "
781  "AND File.FileIndex <= JobMedia.LastIndex "
782  "AND JobMedia.MediaId = Media.MediaId "
783  "AND Job.ClientId = Client.ClientId "
784  "AND Client.Name = '%s' "
785  "%s "
786"ORDER BY File.FileId "
787"LIMIT %d "
788"OFFSET %d "
789,
790
791/* 0064_bvfs_lsdirs_4 */
792"%s "
793"UNION "
794"%s "
795"ORDER BY Path ASC,JobId DESC "
796"LIMIT %d "
797"OFFSET %d "
798,
799
800/* 0065_bvfs_clear_cache_0 */
801"BEGIN; "
802   "UPDATE Job SET HasCache=0; "
803   "TRUNCATE PathHierarchy; "
804   "TRUNCATE PathVisibility; "
805"COMMIT; "
806,
807
808/* 0066_bvfs_update_path_visibility_3 */
809"INSERT INTO PathVisibility (PathId, JobId) "
810"SELECT a.PathId,%s "
811"FROM ( "
812   "SELECT DISTINCT h.PPathId AS PathId "
813   "FROM PathHierarchy AS h "
814   "JOIN PathVisibility AS p ON (h.PathId=p.PathId) "
815   "WHERE p.JobId=%s "
816") AS a "
817"LEFT JOIN PathVisibility AS b "
818"ON (b.JobId=%s AND a.PathId = b.PathId) "
819"WHERE b.PathId IS NULL "
820,
821
822/* 0067_list_volumes_count_0 */
823"SELECT COUNT(DISTINCT Media.MediaId) as count FROM Media; "
824,
825
826/* 0068_list_volumes_by_name_count_1 */
827"SELECT COUNT(DISTINCT Media.MediaId) as count "
828"FROM Media "
829"WHERE VolumeName='%s' "
830,
831
832/* 0069_list_volumes_by_poolid_count_1 */
833"SELECT COUNT(DISTINCT Media.MediaId) as count "
834"FROM Media "
835"WHERE Media.PoolId=%s "
836,
837
838/* 0070_list_joblog_2 */
839"SELECT Time, LogText FROM Log "
840"WHERE Log.JobId=%s "
841"ORDER BY Log.LogId "
842"%s "
843,
844
845/* 0071_list_joblog_count_1 */
846"SELECT COUNT(*) AS count "
847"FROM Log "
848"WHERE Log.JobId=%s "
849,
850
851/* 0072_get_orphaned_paths_0 */
852"SELECT DISTINCT Path.PathId, File.PathId, Path.Path "
853"FROM Path "
854"LEFT JOIN File USING (PathId) "
855"LEFT JOIN PathHierarchy ON (Path.Pathid = PathHierarchy.PPathId) "
856"WHERE File.PathId IS NULL "
857  "AND PathHierarchy.PPathId IS NULL "
858"LIMIT 300000 "
859,
860
861/* 0073_get_bad_paths_0 */
862"SELECT PathId, Path FROM Path "
863"WHERE  Path != '' "
864  "AND  Path NOT LIKE '%%/' "
865,
866
867/* 0074_bvfs_ls_special_dirs_3 */
868"SELECT 'D', SpecialDir.PathId, SpecialDir.Path, JobId, LStat, FileId "
869"FROM ( "
870    "SELECT %s AS PathId, '.' AS Path "
871    " "
872    "UNION "
873    " "
874    "SELECT PPathId AS PathId, '..' AS Path "
875    "FROM   PathHierarchy "
876    "WHERE  PathId = %s "
877") AS SpecialDir "
878"LEFT JOIN ( "
879    "SELECT PathId, JobId, LStat, FileId "
880    "FROM File "
881    "WHERE File.Name = '' "
882      "AND File.JobId IN (%s) "
883") AS DirAttribute "
884"ON (SpecialDir.PathId = DirAttribute.PathId) "
885,
886
887/* 0075_bvfs_ls_sub_dirs_5 */
888"SELECT 'D', PathId, Path, JobId, LStat, FileId "
889"FROM ( "
890   "SELECT "
891      "Path1.PathId AS PathId, "
892      "Path1.Path AS Path, "
893      "lower(Path1.Path) AS lpath, "
894      "listfile1.JobId AS JobId, "
895      "listfile1.LStat AS LStat, "
896      "listfile1.FileId AS FileId "
897   "FROM ( "
898      "SELECT listpath1.PathId AS PathId "
899      "FROM ( "
900         "SELECT DISTINCT PathHierarchy1.PathId AS PathId "
901         "FROM PathHierarchy AS PathHierarchy1 "
902         "INNER JOIN Path AS Path2 "
903         "ON (PathHierarchy1.PathId = Path2.PathId) "
904         "INNER JOIN PathVisibility AS PathVisibility1 "
905         "ON (PathHierarchy1.PathId = PathVisibility1.PathId) "
906         "WHERE PathHierarchy1.PPathId = %s "
907         "AND PathVisibility1.JobId IN (%s) "
908         "%s "
909         ") AS listpath1 "
910      "LEFT JOIN ( "
911          "SELECT PVD1.PathId AS PathId "
912          "FROM ( "
913             "SELECT PV1.PathId AS PathId, MAX(JobId) AS MaxJobId "
914             "FROM PathVisibility AS PV1 WHERE JobId IN (%s) GROUP BY PathId "
915             ") AS PVD1 "
916             "INNER JOIN File AS F2 "
917             "ON (F2.PathId = PVD1.PathId AND F2.JobId = PVD1.MaxJobId AND F2.FileIndex = 0 AND F2.Name = '') "
918      ") AS listpath2 "
919      "ON (listpath1.PathId = listpath2.PathId) "
920      "WHERE listpath2.PathId IS NULL "
921   ") AS listpath3 "
922   "INNER JOIN Path AS Path1 ON (listpath3.PathId = Path1.PathId) "
923   "LEFT JOIN ( "
924      "SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId "
925      "FROM File AS File1 "
926      "WHERE File1.Name = '' "
927        "AND File1.JobId IN (%s) "
928   ") AS listfile1 "
929   "ON (listpath3.PathId = listfile1.PathId) "
930") AS A "
931,
932
933/* 0076_list_volumes_select_0 */
934"SELECT    MediaId,VolumeName,VolStatus,Enabled, "
935          "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger, "
936          "MediaType,LastWritten,Name AS Storage "
937"FROM      Media "
938"LEFT JOIN Storage USING(StorageId) "
939,
940
941/* 0077_list_volumes_select_long_0 */
942"SELECT    MediaId,VolumeName,Slot,PoolId, "
943          "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs, "
944          "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites, "
945          "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention, "
946          "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger, "
947          "EndFile,EndBlock,LabelType,StorageId,DeviceId, "
948          "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
949          "Comment,Name AS Storage "
950"FROM      Media "
951"LEFT JOIN Storage USING(StorageId) "
952,
953
954NULL
955};
956