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