1 /* 2 Bacula(R) - The Network Backup Solution 3 4 Copyright (C) 2000-2020 Kern Sibbald 5 6 The original author of Bacula is Kern Sibbald, with contributions 7 from many others, a complete list can be found in the file AUTHORS. 8 9 You may use this file and others of this release according to the 10 license defined in the LICENSE file, which includes the Affero General 11 Public License, v3.0 ("AGPLv3") and some additional permissions and 12 terms pursuant to its AGPLv3 Section 7. 13 14 This notice must be preserved when any source code is 15 conveyed and/or propagated. 16 17 Bacula(R) is a registered trademark of Kern Sibbald. 18 */ 19 /* 20 * This file contains all the SQL commands that are either issued by 21 * the Director or which are database backend specific. 22 * 23 * Written by Kern Sibbald, July MMII 24 */ 25 /* 26 * Note, PostgreSQL imposes some constraints on using DISTINCT and GROUP BY 27 * for example, the following is illegal in PostgreSQL: 28 * SELECT DISTINCT JobId FROM temp ORDER BY StartTime ASC; 29 * because all the ORDER BY expressions must appear in the SELECT list! 30 */ 31 32 #include "bacula.h" 33 34 const char *get_restore_objects = 35 "SELECT JobId,ObjectLength,ObjectFullLength,ObjectIndex," 36 "ObjectType,ObjectCompression,FileIndex,ObjectName," 37 "RestoreObject,PluginName " 38 "FROM RestoreObject " 39 "WHERE JobId IN (%s) " 40 "AND ObjectType = %d " 41 "ORDER BY ObjectIndex ASC"; 42 const char *get_created_running_job = 43 "SELECT JobId, Job, JobStatus FROM Job WHERE JobStatus IN ('C', 'R')"; 44 const char *cleanup_created_job = 45 "UPDATE Job SET JobStatus='f', StartTime=SchedTime, EndTime=SchedTime " 46 "WHERE JobStatus = 'C'"; 47 const char *cleanup_running_job = 48 "UPDATE Job SET JobStatus='f', EndTime=StartTime WHERE JobStatus = 'R'"; 49 50 /* For sql_update.c db_update_stats */ 51 const char *fill_jobhisto = 52 "INSERT INTO JobHisto (JobId, Job, Name, Type, Level," 53 "ClientId, JobStatus," 54 "SchedTime, StartTime, EndTime, RealEndTime, JobTDate," 55 "VolSessionId, VolSessionTime, JobFiles, JobBytes, ReadBytes," 56 "JobErrors, JobMissingFiles, PoolId, FileSetId, PriorJobId, PriorJob, " 57 "PurgedFiles, HasBase, Reviewed, Comment)" 58 "SELECT JobId, Job, Name, Type, Level, ClientId, JobStatus," 59 "SchedTime, StartTime, EndTime, RealEndTime, JobTDate," 60 "VolSessionId, VolSessionTime, JobFiles, JobBytes, ReadBytes," 61 "JobErrors, JobMissingFiles, PoolId, FileSetId, PriorJobId, PriorJob, " 62 "PurgedFiles, HasBase, Reviewed, Comment " 63 "FROM Job " 64 "WHERE JobStatus IN ('T','W','f','A','E')" 65 "AND NOT EXISTS " 66 "(SELECT JobHisto.JobId " 67 "FROM JobHisto WHERE JobHisto.Jobid=Job.JobId)" 68 "AND JobTDate < %s "; 69 70 /* For ua_update.c */ 71 const char *list_pool = "SELECT * FROM Pool WHERE PoolId=%s"; 72 73 /* For ua_dotcmds.c */ 74 const char *client_backups = 75 "SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime," 76 "JobFiles,JobBytes,VolumeName,MediaType,FileSet,Media.Enabled as Enabled" 77 " FROM Client,Job,JobMedia,Media,FileSet" 78 " WHERE Client.Name='%s'" 79 " AND FileSet='%s'" 80 " AND Client.ClientId=Job.ClientId " 81 " AND JobStatus IN ('T','W') AND Type='B' " 82 " AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId " 83 " AND Job.FileSetId=FileSet.FileSetId" 84 " ORDER BY Job.StartTime"; 85 86 /* ====== ua_prune.c */ 87 88 const char *sel_JobMedia = 89 "SELECT DISTINCT JobMedia.JobId FROM JobMedia,Job" 90 " WHERE MediaId=%s AND Job.JobId=JobMedia.JobId " 91 " AND Job.JobTDate<%s AND Job.JobStatus NOT IN ('R', 'C') "; 92 93 /* Delete temp tables and indexes */ 94 const char *drop_deltabs[] = { 95 "DROP TABLE IF EXISTS DelCandidates", 96 NULL}; 97 98 const char *create_delindex = "CREATE INDEX DelInx1 ON DelCandidates (JobId)"; 99 100 /* ======= ua_restore.c */ 101 const char *uar_count_files = 102 "SELECT JobFiles FROM Job WHERE JobId=%s"; 103 104 /* List last 20 Jobs */ 105 const char *uar_list_jobs = 106 "SELECT JobId,Client.Name as Client,Job.Name as Name,StartTime,Level as " 107 "JobLevel,JobFiles,JobBytes " 108 "FROM Client,Job WHERE Client.ClientId=Job.ClientId AND JobStatus IN ('T','W') " 109 "AND Type='B' ORDER BY StartTime DESC LIMIT 20"; 110 111 const char *uar_print_jobs = 112 "SELECT DISTINCT JobId,Level,JobFiles,JobBytes,StartTime,VolumeName" 113 " FROM Job JOIN JobMedia USING (JobId) JOIN Media USING (MediaId) " 114 " WHERE JobId IN (%s) " 115 " ORDER BY StartTime ASC"; 116 117 /* 118 * Find all files for a particular JobId and insert them into 119 * the tree during a restore. 120 */ 121 const char *uar_sel_files = 122 "SELECT Path.Path,File.Filename,FileIndex,JobId,LStat " 123 "FROM File JOIN Path USING (PathId) " 124 "WHERE File.JobId IN (%s)"; 125 126 const char *uar_del_temp = "DROP TABLE IF EXISTS temp"; 127 const char *uar_del_temp1 = "DROP TABLE IF EXISTS temp1"; 128 129 const char *uar_last_full = 130 "INSERT INTO temp1(JobId,JobTDate) SELECT Job.JobId,JobTdate " 131 "FROM Client,Job,JobMedia,Media,FileSet WHERE Client.ClientId=%s " 132 "AND Job.ClientId=%s " 133 "AND Job.StartTime < '%s' " 134 "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' " 135 "AND JobMedia.JobId=Job.JobId " 136 "AND Media.Enabled=1 " 137 "AND JobMedia.MediaId=Media.MediaId " 138 "AND Job.FileSetId=FileSet.FileSetId " 139 "AND FileSet.FileSet='%s' " 140 "%s" 141 "ORDER BY Job.JobTDate DESC LIMIT 1"; 142 143 const char *uar_full = 144 "INSERT INTO temp (JobId,JobTDate,ClientId,Level,JobFiles,JobBytes,StartTime,VolumeName,StartFile,VolSessionId,VolSessionTime) " 145 146 "SELECT Job.JobId,Job.JobTDate," 147 "Job.ClientId,Job.Level,Job.JobFiles,Job.JobBytes," 148 "StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime " 149 "FROM temp1,Job,JobMedia,Media WHERE temp1.JobId=Job.JobId " 150 "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' " 151 "AND Media.Enabled=1 " 152 "AND JobMedia.JobId=Job.JobId " 153 "AND JobMedia.MediaId=Media.MediaId"; 154 155 const char *uar_dif = 156 "INSERT INTO temp (JobId,JobTDate,ClientId,Level,JobFiles,JobBytes,StartTime,VolumeName,StartFile,VolSessionId,VolSessionTime) " 157 "SELECT Job.JobId,Job.JobTDate,Job.ClientId," 158 "Job.Level,Job.JobFiles,Job.JobBytes," 159 "Job.StartTime,Media.VolumeName,JobMedia.StartFile," 160 "Job.VolSessionId,Job.VolSessionTime " 161 "FROM Job,JobMedia,Media,FileSet " 162 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' " 163 "AND Job.ClientId=%s " 164 "AND JobMedia.JobId=Job.JobId " 165 "AND Media.Enabled=1 " 166 "AND JobMedia.MediaId=Media.MediaId " 167 "AND Job.Level='D' AND JobStatus IN ('T','W') AND Type='B' " 168 "AND Job.FileSetId=FileSet.FileSetId " 169 "AND FileSet.FileSet='%s' " 170 "%s" 171 "ORDER BY Job.JobTDate DESC LIMIT 1"; 172 173 const char *uar_inc = 174 "INSERT INTO temp (JobId,JobTDate,ClientId,Level,JobFiles,JobBytes,StartTime,VolumeName,StartFile,VolSessionId,VolSessionTime) " 175 "SELECT Job.JobId,Job.JobTDate,Job.ClientId," 176 "Job.Level,Job.JobFiles,Job.JobBytes," 177 "Job.StartTime,Media.VolumeName,JobMedia.StartFile," 178 "Job.VolSessionId,Job.VolSessionTime " 179 "FROM Job,JobMedia,Media,FileSet " 180 "WHERE Job.JobTDate>%s AND Job.StartTime<'%s' " 181 "AND Job.ClientId=%s " 182 "AND Media.Enabled=1 " 183 "AND JobMedia.JobId=Job.JobId " 184 "AND JobMedia.MediaId=Media.MediaId " 185 "AND Job.Level='I' AND JobStatus IN ('T','W') AND Type='B' " 186 "AND Job.FileSetId=FileSet.FileSetId " 187 "AND FileSet.FileSet='%s' " 188 "%s"; 189 190 const char *uar_list_temp = 191 "SELECT DISTINCT JobId,Level,JobFiles,JobBytes,StartTime,VolumeName" 192 " FROM temp" 193 " ORDER BY StartTime ASC"; 194 195 196 const char *uar_sel_jobid_temp = 197 "SELECT DISTINCT JobId,StartTime FROM temp ORDER BY StartTime ASC"; 198 199 const char *uar_sel_all_temp1 = "SELECT * FROM temp1"; 200 201 const char *uar_sel_all_temp = "SELECT * FROM temp"; 202 203 204 205 /* Select FileSet names for this Client */ 206 const char *uar_sel_fileset = 207 "SELECT DISTINCT FileSet.FileSet FROM Job," 208 "Client,FileSet WHERE Job.FileSetId=FileSet.FileSetId " 209 "AND Job.ClientId=%s AND Client.ClientId=%s " 210 "ORDER BY FileSet.FileSet"; 211 212 /* Select all different FileSet for this client 213 * This query doesn't guarantee that the Id is the latest 214 * version of the FileSet. Can be used with other queries that 215 * use Ids to select the FileSet name. (like in accurate) 216 */ 217 const char *uar_sel_filesetid = 218 "SELECT MAX(FileSet.FileSetId) " 219 "FROM FileSet JOIN Job USING (FileSetId) " 220 "WHERE Job.ClientId=%s " 221 "GROUP BY FileSet"; 222 223 /* 224 * Find JobId, FileIndex for a given path/file and date 225 * for use when inserting individual files into the tree. 226 */ 227 const char *uar_jobid_fileindex = 228 "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Client " 229 "WHERE Job.JobId=File.JobId " 230 "AND Job.StartTime<='%s' " 231 "AND Path.Path='%s' " 232 "AND File.Filename='%s' " 233 "AND Client.Name='%s' " 234 "AND Job.ClientId=Client.ClientId " 235 "AND Path.PathId=File.PathId " 236 "AND JobStatus IN ('T','W') AND Type='B' " 237 "ORDER BY Job.StartTime DESC LIMIT 1"; 238 239 const char *uar_jobids_fileindex = 240 "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Client " 241 "WHERE Job.JobId IN (%s) " 242 "AND Job.JobId=File.JobId " 243 "AND Job.StartTime<='%s' " 244 "AND Path.Path='%s' " 245 "AND File.Filename='%s' " 246 "AND Client.Name='%s' " 247 "AND Job.ClientId=Client.ClientId " 248 "AND Path.PathId=File.PathId " 249 "ORDER BY Job.StartTime DESC LIMIT 1"; 250 251 /* Query to get list of files from table -- presuably built by an external program */ 252 const char *uar_jobid_fileindex_from_table = 253 "SELECT JobId, FileIndex FROM %s ORDER BY JobId, FileIndex ASC"; 254 255 /* Get the list of the last recent version per Delta with a given 256 * jobid list. This is a tricky part because with SQL the result of: 257 * 258 * SELECT MAX(A), B, C, D FROM... GROUP BY (B,C) 259 * 260 * doesn't give the good result (for D). 261 * 262 * With PostgreSQL, we can use DISTINCT ON(), but with Mysql or Sqlite, 263 * we need an extra join using JobTDate. 264 */ 265 static const char *select_recent_version_with_basejob_default = 266 "SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId," 267 "File.Filename AS Filename, LStat, MD5, DeltaSeq," 268 "Job.JobTDate AS JobTDate " 269 "FROM Job, File, ( " 270 "SELECT MAX(JobTDate) AS JobTDate, PathId, Filename " 271 "FROM ( " 272 "SELECT JobTDate, PathId, Filename " /* Get all normal files */ 273 "FROM File JOIN Job USING (JobId) " /* from selected backup */ 274 "WHERE File.JobId IN (%s) " 275 "UNION ALL " 276 "SELECT JobTDate, PathId, Filename " /* Get all files from */ 277 "FROM BaseFiles " /* BaseJob */ 278 "JOIN File USING (FileId) " 279 "JOIN Job ON (BaseJobId = Job.JobId) " 280 "WHERE BaseFiles.JobId IN (%s) " /* Use Max(JobTDate) to find */ 281 ") AS tmp " 282 "GROUP BY PathId, Filename " /* the latest file version */ 283 ") AS T1 " 284 "WHERE (Job.JobId IN ( " /* Security, we force JobId to be valid */ 285 "SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) " 286 "OR Job.JobId IN (%s)) " 287 "AND T1.JobTDate = Job.JobTDate " /* Join on JobTDate to get the orginal */ 288 "AND Job.JobId = File.JobId " /* Job/File record */ 289 "AND T1.PathId = File.PathId " 290 "AND T1.Filename = File.Filename"; 291 292 const char *select_recent_version_with_basejob[] = 293 { 294 /* MySQL */ 295 select_recent_version_with_basejob_default, 296 297 /* PostgreSQL */ /* The DISTINCT ON () permits to avoid extra join */ 298 "SELECT DISTINCT ON (Filename, PathId) JobTDate, JobId, FileId, " 299 "FileIndex, PathId, Filename, LStat, MD5, DeltaSeq " 300 "FROM " 301 "(SELECT FileId, JobId, PathId, Filename, FileIndex, LStat, MD5, DeltaSeq " 302 "FROM File WHERE JobId IN (%s) " 303 "UNION ALL " 304 "SELECT File.FileId, File.JobId, PathId, Filename, " 305 "File.FileIndex, LStat, MD5, DeltaSeq " 306 "FROM BaseFiles JOIN File USING (FileId) " 307 "WHERE BaseFiles.JobId IN (%s) " 308 ") AS T JOIN Job USING (JobId) " 309 "ORDER BY Filename, PathId, JobTDate DESC ", 310 311 /* SQLite */ 312 select_recent_version_with_basejob_default 313 }; 314 315 /* We do the same thing than the previous query, but we include 316 * all delta parts. If the file has been deleted, we can have irrelevant 317 * parts. 318 * 319 * The code that uses results should control the delta sequence with 320 * the following rules: 321 * First Delta = 0 322 * Delta = Previous Delta + 1 323 * 324 * If we detect a gap, we can discard further pieces 325 * If a file starts at 1 instead of 0, the file has been deleted, and further 326 * pieces are useless. 327 * This control should be reset for each new file 328 */ 329 static const char *select_recent_version_with_basejob_and_delta_default = 330 "SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId," 331 "File.Filename AS Filename, LStat, MD5, File.DeltaSeq AS DeltaSeq," 332 "Job.JobTDate AS JobTDate" 333 " FROM Job, File, (" 334 "SELECT MAX(JobTDate) AS JobTDate, PathId, Filename, DeltaSeq " 335 "FROM (" 336 "SELECT JobTDate, PathId, Filename, DeltaSeq " /*Get all normal files*/ 337 "FROM File JOIN Job USING (JobId) " /* from selected backup */ 338 "WHERE File.JobId IN (%s) " 339 "UNION ALL " 340 "SELECT JobTDate, PathId, Filename, DeltaSeq " /*Get all files from */ 341 "FROM BaseFiles " /* BaseJob */ 342 "JOIN File USING (FileId) " 343 "JOIN Job ON (BaseJobId = Job.JobId) " 344 "WHERE BaseFiles.JobId IN (%s) " /* Use Max(JobTDate) to find */ 345 ") AS tmp " 346 "GROUP BY PathId, Filename, DeltaSeq " /* the latest file version */ 347 ") AS T1" 348 " WHERE (Job.JobId IN ( " /* Security, we force JobId to be valid */ 349 "SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s))" 350 " OR Job.JobId IN (%s))" 351 " AND T1.JobTDate = Job.JobTDate" /* Join on JobTDate to get the orginal */ 352 " AND Job.JobId = File.JobId" /* Job/File record */ 353 " AND T1.PathId = File.PathId" 354 " AND T1.Filename = File.Filename"; 355 356 const char *select_recent_version_with_basejob_and_delta[] = 357 { 358 /* MySQL */ 359 select_recent_version_with_basejob_and_delta_default, 360 361 /* Postgresql -- The DISTINCT ON () permits to avoid extra join */ 362 "SELECT DISTINCT ON (Filename, PathId, DeltaSeq) JobTDate, JobId, FileId, " 363 "FileIndex, PathId, Filename, LStat, MD5, DeltaSeq " 364 "FROM " 365 "(SELECT FileId, JobId, PathId, Filename, FileIndex, LStat, MD5,DeltaSeq " 366 "FROM File WHERE JobId IN (%s) " 367 "UNION ALL " 368 "SELECT File.FileId, File.JobId, PathId, Filename, " 369 "File.FileIndex, LStat, MD5, DeltaSeq " 370 "FROM BaseFiles JOIN File USING (FileId) " 371 "WHERE BaseFiles.JobId IN (%s) " 372 ") AS T JOIN Job USING (JobId) " 373 "ORDER BY Filename, PathId, DeltaSeq, JobTDate DESC ", 374 375 /* SQLite */ 376 select_recent_version_with_basejob_and_delta_default 377 }; 378 379 /* Get the list of the last recent version with a given BaseJob jobid list 380 * We don't handle Delta with BaseJobs, they have only Full files 381 */ 382 static const char *select_recent_version_default = 383 "SELECT j1.JobId AS JobId, f1.FileId AS FileId, f1.FileIndex AS FileIndex, " 384 "f1.PathId AS PathId, f1.Filename AS Filename, " 385 "f1.LStat AS LStat, f1.MD5 AS MD5, j1.JobTDate " 386 "FROM ( " /* Choose the last version for each Path/Filename */ 387 "SELECT max(JobTDate) AS JobTDate, PathId, Filename " 388 "FROM File JOIN Job USING (JobId) " 389 "WHERE File.JobId IN (%s) " 390 "GROUP BY PathId, Filename " 391 ") AS t1, Job AS j1, File AS f1 " 392 "WHERE t1.JobTDate = j1.JobTDate " 393 "AND j1.JobId IN (%s) " 394 "AND t1.Filename = f1.Filename " 395 "AND t1.PathId = f1.PathId " 396 "AND j1.JobId = f1.JobId"; 397 398 const char *select_recent_version[] = 399 { 400 /* MySQL */ 401 select_recent_version_default, 402 403 /* PostgreSQL */ 404 "SELECT DISTINCT ON (Filename, PathId) JobTDate, JobId, FileId, " 405 "FileIndex, PathId, Filename, LStat, MD5 " 406 "FROM File JOIN Job USING (JobId) " 407 "WHERE JobId IN (%s) " 408 "ORDER BY Filename, PathId, JobTDate DESC ", 409 410 /* SQLite */ 411 select_recent_version_default 412 }; 413 414 /* We don't create this table as TEMPORARY because MySQL 415 MyISAM 5.0 and 5.1 are unable to run further queries in this mode 416 */ 417 static const char *create_temp_accurate_jobids_default = 418 "CREATE TABLE btemp3%s AS " 419 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles, FileSetId " 420 "FROM Job JOIN FileSet USING (FileSetId) " 421 "WHERE ClientId = %s " 422 "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' " 423 "AND StartTime<'%s' " 424 "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) " 425 " %s " /* Any filter */ 426 "ORDER BY Job.JobTDate DESC LIMIT 1"; 427 428 static const char *create_temp_accurate_jobids_mysql = 429 "CREATE TABLE btemp3%s /*PKEY (DummyPkey INTEGER AUTO_INCREMENT PRIMARY KEY)*/ AS (" 430 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles, FileSetId " 431 "FROM Job JOIN FileSet USING (FileSetId) " 432 "WHERE ClientId = %s " 433 "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' " 434 "AND StartTime<'%s' " 435 "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) " 436 " %s " /* Any filter */ 437 "ORDER BY Job.JobTDate DESC LIMIT 1)"; 438 439 const char *create_temp_accurate_jobids[] = 440 { 441 /* MySQL */ 442 create_temp_accurate_jobids_mysql, 443 /* PostgreSQL */ 444 create_temp_accurate_jobids_default, 445 /* SQLite */ 446 create_temp_accurate_jobids_default 447 }; 448 449 const char *create_temp_basefile[] = 450 { 451 /* MySQL */ 452 "CREATE TEMPORARY TABLE basefile%lld" 453 "(Path BLOB NOT NULL, Name BLOB NOT NULL, " 454 "INDEX (Path(255), Name(255))" 455 "/*PKEY, DummyPkey INTEGER AUTO_INCREMENT PRIMARY KEY*/)", 456 /* PostgreSQL */ 457 "CREATE TEMPORARY TABLE basefile%lld" 458 "(Path TEXT, Name TEXT)", 459 /* SQLite */ 460 "CREATE TEMPORARY TABLE basefile%lld" 461 "(Path TEXT, Name TEXT)" 462 }; 463 464 const char *create_temp_new_basefile[] = 465 { 466 /* MySQL */ 467 "CREATE TEMPORARY TABLE new_basefile%lld /*PKEY(DummyPkey INTEGER AUTO_INCREMENT PRIMARY KEY)*/ AS " 468 "SELECT Path.Path AS Path, Temp.Filename AS Name, Temp.FileIndex AS FileIndex," 469 " Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId," 470 " Temp.MD5 AS MD5" 471 " FROM (%s) AS Temp" 472 " JOIN Path ON (Path.PathId = Temp.PathId)" 473 " WHERE Temp.FileIndex > 0", 474 475 /* PostgreSQL */ 476 "CREATE TEMPORARY TABLE new_basefile%lld AS " 477 "SELECT Path.Path AS Path, Temp.Filename AS Name, Temp.FileIndex AS FileIndex," 478 " Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId," 479 " Temp.MD5 AS MD5" 480 " FROM ( %s ) AS Temp" 481 " JOIN Path ON (Path.PathId = Temp.PathId)" 482 " WHERE Temp.FileIndex > 0", 483 484 /* SQLite */ 485 "CREATE TEMPORARY TABLE new_basefile%lld AS " 486 "SELECT Path.Path AS Path, Temp.Filename AS Name, Temp.FileIndex AS FileIndex," 487 " Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId," 488 " Temp.MD5 AS MD5" 489 " FROM ( %s ) AS Temp" 490 " JOIN Path ON (Path.PathId = Temp.PathId)" 491 " WHERE Temp.FileIndex > 0" 492 }; 493 494 /* ====== ua_prune.c ====== */ 495 496 /* List of SQL commands to create temp table and indicies */ 497 const char *create_deltabs[] = 498 { 499 /* MySQL */ 500 "CREATE TEMPORARY TABLE DelCandidates (" 501 "JobId INTEGER UNSIGNED NOT NULL, " 502 "PurgedFiles TINYINT, " 503 "FileSetId INTEGER UNSIGNED, " 504 "JobFiles INTEGER UNSIGNED, " 505 "JobStatus BINARY(1)" 506 "/*PKEY, DummyPkey INTEGER AUTO_INCREMENT PRIMARY KEY*/)", 507 508 /* PostgreSQL */ 509 "CREATE TEMPORARY TABLE DelCandidates ( " 510 "JobId INTEGER NOT NULL, " 511 "PurgedFiles SMALLINT, " 512 "FileSetId INTEGER, " 513 "JobFiles INTEGER, " 514 "JobStatus char(1))", 515 516 /* SQLite */ 517 "CREATE TEMPORARY TABLE DelCandidates (" 518 "JobId INTEGER UNSIGNED NOT NULL, " 519 "PurgedFiles TINYINT, " 520 "FileSetId INTEGER UNSIGNED, " 521 "JobFiles INTEGER UNSIGNED, " 522 "JobStatus CHAR)" 523 }; 524 525 /* ======= ua_purge.c ====== */ 526 /* Select the first available Copy Job that must be upgraded 527 * to a Backup job when the original backup job is expired. 528 */ 529 static const char *uap_upgrade_copies_oldest_job_default = 530 "CREATE TEMPORARY TABLE cpy_tmp /*PKEY (DummyPkey INTEGER PRIMARY KEY DEFAULT 1)*/ AS " 531 "SELECT MIN(JobId) AS JobId FROM Job " /* Choose the oldest job */ 532 "WHERE Type='%c' " /* JT_JOB_COPY */ 533 "AND ( PriorJobId IN (%s) " /* JobId selection */ 534 "OR " 535 " PriorJobId IN ( " 536 "SELECT PriorJobId " 537 "FROM Job " 538 "WHERE JobId IN (%s) " /* JobId selection */ 539 " AND Type='B' AND JobStatus IN ('T', 'W') " 540 ") " 541 ") " 542 "GROUP BY PriorJobId "; /* one result per copy */ 543 544 const char *uap_upgrade_copies_oldest_job[] = 545 { 546 /* MySQL */ 547 uap_upgrade_copies_oldest_job_default, 548 /* PostgreSQL */ 549 uap_upgrade_copies_oldest_job_default, 550 /* SQLite */ 551 uap_upgrade_copies_oldest_job_default 552 }; 553 554 /* ======= ua_restore.c ====== */ 555 556 /* List Jobs where a particular file is saved */ 557 const char *uar_file[] = 558 { 559 /* MySQL */ 560 "SELECT Job.JobId as JobId," 561 "CONCAT(Path.Path,File.Filename) as Name, " 562 "StartTime,Type as JobType,JobStatus,JobFiles,JobBytes " 563 "FROM Client,Job,File,Path WHERE Client.Name='%s' " 564 "AND Client.ClientId=Job.ClientId " 565 "AND Job.JobId=File.JobId AND File.FileIndex > 0 " 566 "AND Path.PathId=File.PathId " 567 "AND File.Filename='%s' ORDER BY StartTime DESC LIMIT 20", 568 569 /* PostgreSQL */ 570 "SELECT Job.JobId as JobId," 571 "Path.Path||File.Filename as Name, " 572 "StartTime,Type as JobType,JobStatus,JobFiles,JobBytes " 573 "FROM Client,Job,File,Path WHERE Client.Name='%s' " 574 "AND Client.ClientId=Job.ClientId " 575 "AND Job.JobId=File.JobId AND File.FileIndex > 0 " 576 "AND Path.PathId=File.PathId " 577 "AND File.Filename='%s' ORDER BY StartTime DESC LIMIT 20", 578 579 /* SQLite */ 580 "SELECT Job.JobId as JobId," 581 "Path.Path||File.Filename as Name, " 582 "StartTime,Type as JobType,JobStatus,JobFiles,JobBytes " 583 "FROM Client,Job,File,Path WHERE Client.Name='%s' " 584 "AND Client.ClientId=Job.ClientId " 585 "AND Job.JobId=File.JobId AND File.FileIndex > 0 " 586 "AND Path.PathId=File.PathId " 587 "AND File.Filename='%s' ORDER BY StartTime DESC LIMIT 20" 588 }; 589 590 const char *uar_create_temp[] = 591 { 592 /* MySQL */ 593 "CREATE TEMPORARY TABLE temp (" 594 "JobId INTEGER UNSIGNED NOT NULL," 595 "JobTDate BIGINT UNSIGNED," 596 "ClientId INTEGER UNSIGNED," 597 "Level CHAR," 598 "JobFiles INTEGER UNSIGNED," 599 "JobBytes BIGINT UNSIGNED," 600 "StartTime TEXT," 601 "VolumeName TEXT," 602 "StartFile INTEGER UNSIGNED," 603 "VolSessionId INTEGER UNSIGNED," 604 "VolSessionTime INTEGER UNSIGNED" 605 "/*PKEY, DummyPKey INTEGER AUTO_INCREMENT PRIMARY KEY*/)", 606 607 /* PostgreSQL */ 608 "CREATE TEMPORARY TABLE temp (" 609 "JobId INTEGER NOT NULL," 610 "JobTDate BIGINT," 611 "ClientId INTEGER," 612 "Level CHAR," 613 "JobFiles INTEGER," 614 "JobBytes BIGINT," 615 "StartTime TEXT," 616 "VolumeName TEXT," 617 "StartFile INTEGER," 618 "VolSessionId INTEGER," 619 "VolSessionTime INTEGER)", 620 621 /* SQLite */ 622 "CREATE TEMPORARY TABLE temp (" 623 "JobId INTEGER UNSIGNED NOT NULL," 624 "JobTDate BIGINT UNSIGNED," 625 "ClientId INTEGER UNSIGNED," 626 "Level CHAR," 627 "JobFiles INTEGER UNSIGNED," 628 "JobBytes BIGINT UNSIGNED," 629 "StartTime TEXT," 630 "VolumeName TEXT," 631 "StartFile INTEGER UNSIGNED," 632 "VolSessionId INTEGER UNSIGNED," 633 "VolSessionTime INTEGER UNSIGNED)" 634 }; 635 636 const char *uar_create_temp1[] = 637 { 638 /* MySQL */ 639 "CREATE TEMPORARY TABLE temp1 (" 640 "JobId INTEGER UNSIGNED NOT NULL," 641 "JobTDate BIGINT UNSIGNED" 642 "/*PKEY, DummyPKey INTEGER AUTO_INCREMENT PRIMARY KEY*/)", 643 /* PostgreSQL */ 644 "CREATE TEMPORARY TABLE temp1 (" 645 "JobId INTEGER NOT NULL," 646 "JobTDate BIGINT)", 647 /* SQLite */ 648 "CREATE TEMPORARY TABLE temp1 (" 649 "JobId INTEGER UNSIGNED NOT NULL," 650 "JobTDate BIGINT UNSIGNED)" 651 }; 652 653 /* Query to get all files in a directory no recursing 654 * Note, for PostgreSQL since it respects the "Single Value 655 * rule", the results of the SELECT will be unoptimized. 656 * I.e. the same file will be restored multiple times, once 657 * for each time it was backed up. 658 */ 659 660 const char *uar_jobid_fileindex_from_dir[] = 661 { 662 /* MySQL */ 663 "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Client " 664 "WHERE Job.JobId IN (%s) " 665 "AND Job.JobId=File.JobId " 666 "AND Path.Path='%s' " 667 "AND Client.Name='%s' " 668 "AND Job.ClientId=Client.ClientId " 669 "AND Path.PathId=File.Pathid " 670 "GROUP BY File.FileIndex ORDER BY Job.StartTime", 671 672 /* PostgreSQL */ 673 "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Client " 674 "WHERE Job.JobId IN (%s) " 675 "AND Job.JobId=File.JobId " 676 "AND Path.Path='%s' " 677 "AND Client.Name='%s' " 678 "AND Job.ClientId=Client.ClientId " 679 "AND Path.PathId=File.Pathid ORDER BY Job.StartTime ", 680 681 /* SQLite */ 682 "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Client " 683 "WHERE Job.JobId IN (%s) " 684 "AND Job.JobId=File.JobId " 685 "AND Path.Path='%s' " 686 "AND Client.Name='%s' " 687 "AND Job.ClientId=Client.ClientId " 688 "AND Path.PathId=File.Pathid " 689 "GROUP BY File.FileIndex ORDER BY Job.StartTime " 690 }; 691 692 const char *sql_media_order_most_recently_written[] = 693 { 694 /* MySQL */ 695 "ORDER BY LastWritten IS NULL,LastWritten DESC, MediaId", 696 /* PostgreSQL */ 697 "ORDER BY LastWritten IS NULL,LastWritten DESC, MediaId", 698 /* SQLite */ 699 "ORDER BY LastWritten IS NULL,LastWritten DESC, MediaId" 700 }; 701 702 const char *sql_get_max_connections[] = 703 { 704 /* MySQL */ 705 "SHOW VARIABLES LIKE 'max_connections'", 706 /* PostgreSQL */ 707 "SHOW max_connections", 708 /* SQLite */ 709 "SELECT 0" 710 }; 711 712 /* 713 * The Group By can return strange numbers when having multiple 714 * version of a file in the same dataset. 715 */ 716 const char *default_sql_bvfs_select = 717 "CREATE TABLE %s AS" 718 "SELECT File.JobId, File.FileIndex, File.FileId " 719 "FROM Job, File, ( " 720 "SELECT MAX(JobTDate) AS JobTDate, PathId, Filename " 721 "FROM btemp%s GROUP BY PathId, Filename " 722 ") AS T1 " 723 "WHERE T1.JobTDate = Job.JobTDate " 724 "AND Job.JobId = File.JobId " 725 "AND T1.PathId = File.PathId " 726 "AND T1.Filename = File.Filename " 727 "AND File.FileIndex > 0 " 728 "AND Job.JobId IN (SELECT DISTINCT JobId FROM btemp%s) "; 729 730 const char *default_sql_bvfs_select_mysql = 731 "CREATE TABLE %s /*PKEY (DummyPkey INTEGER AUTO_INCREMENT PRIMARY KEY)*/ AS " 732 "SELECT File.JobId, File.FileIndex, File.FileId " 733 "FROM Job, File, ( " 734 "SELECT MAX(JobTDate) AS JobTDate, PathId, Filename " 735 "FROM btemp%s GROUP BY PathId, Filename " 736 ") AS T1 " 737 "WHERE T1.JobTDate = Job.JobTDate " 738 "AND Job.JobId = File.JobId " 739 "AND T1.PathId = File.PathId " 740 "AND T1.Filename = File.Filename " 741 "AND File.FileIndex > 0 " 742 "AND Job.JobId IN (SELECT DISTINCT JobId FROM btemp%s) "; 743 744 const char *sql_bvfs_select[] = 745 { 746 /* MySQL */ 747 default_sql_bvfs_select_mysql, 748 /* PostgreSQL */ 749 "CREATE TABLE %s AS ( " 750 "SELECT JobId, FileIndex, FileId " 751 "FROM ( " 752 "SELECT DISTINCT ON (PathId, Filename) " 753 "JobId, FileIndex, FileId " 754 "FROM btemp%s " 755 "ORDER BY PathId, Filename, JobTDate DESC " 756 ") AS T " 757 "WHERE FileIndex > 0)", 758 /* SQLite */ 759 default_sql_bvfs_select 760 }; 761 762 static const char *sql_bvfs_list_files_default = 763 "SELECT 'F', T.PathId, T.Filename, " 764 "File.JobId, File.LStat, File.FileId " 765 "FROM Job, File, ( " 766 "SELECT MAX(JobTDate) AS JobTDate, PathId, Filename " 767 "FROM ( " 768 "SELECT JobTDate, PathId, Filename " 769 "FROM File JOIN Job USING (JobId) " 770 "WHERE File.JobId IN (%s) AND PathId = %s " 771 "UNION ALL " 772 "SELECT JobTDate, PathId, Filename " 773 "FROM BaseFiles " 774 "JOIN File USING (FileId) " 775 "JOIN Job ON (BaseJobId = Job.JobId) " 776 "WHERE BaseFiles.JobId IN (%s) AND PathId = %s " 777 ") AS tmp GROUP BY PathId, Filename " 778 "LIMIT %lld OFFSET %lld" 779 ") AS T " 780 "WHERE T.JobTDate = Job.JobTDate " 781 "AND Job.JobId = File.JobId " 782 "AND T.PathId = File.PathId " 783 "AND T.Filename = File.Filename " 784 "AND File.Filename != '' " 785 "AND File.FileIndex > 0 " 786 " %s " /* AND Name LIKE '' */ 787 "AND (Job.JobId IN ( " 788 "SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) " 789 "OR Job.JobId IN (%s)) "; 790 791 const char *sql_bvfs_list_files[] = { 792 /* MySQL */ 793 sql_bvfs_list_files_default, 794 795 /* PostgreSQL */ 796 "SELECT Type, PathId, Filename, JobId, LStat, A.FileId " 797 "FROM (" 798 "SELECT DISTINCT ON (Filename) 'F' as Type, PathId, FileId, " 799 "T.Filename, JobId, LStat, FileIndex " 800 "FROM " 801 "(SELECT FileId, JobId, PathId, Filename, FileIndex, LStat, MD5 " 802 "FROM File WHERE JobId IN (%s) AND PathId = %s " 803 "UNION ALL " 804 "SELECT File.FileId, File.JobId, PathId, Filename, " 805 "File.FileIndex, LStat, MD5 " 806 "FROM BaseFiles JOIN File USING (FileId) " 807 "WHERE BaseFiles.JobId IN (%s) AND File.PathId = %s " 808 ") AS T JOIN Job USING (JobId) " 809 " WHERE T.Filename != '' " 810 " %s " /* AND Filename LIKE '' */ 811 "ORDER BY Filename, StartTime DESC " 812 ") AS A WHERE A.FileIndex > 0 " 813 "LIMIT %lld OFFSET %lld ", 814 815 /* SQLite */ 816 sql_bvfs_list_files_default, 817 818 /* SQLite */ 819 sql_bvfs_list_files_default 820 }; 821 822 /* Basically the same thing than select_recent_version_with_basejob_and_delta_default, 823 * but we specify a single file with Filename/PathId 824 * 825 * Input: 826 * 1 JobId to look at 827 * 2 Filename 828 * 3 PathId 829 * 4 JobId to look at 830 * 5 Filename 831 * 6 PathId 832 * 7 Jobid 833 * 8 JobId 834 */ 835 const char *bvfs_select_delta_version_with_basejob_and_delta_default = 836 "SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId, " 837 "File.Filename AS Filename, LStat, MD5, File.DeltaSeq AS DeltaSeq, " 838 "Job.JobTDate AS JobTDate " 839 "FROM Job, File, ( " 840 "SELECT MAX(JobTDate) AS JobTDate, PathId, Filename, DeltaSeq " 841 "FROM ( " 842 "SELECT JobTDate, PathId, Filename, DeltaSeq " /*Get all normal files*/ 843 "FROM File JOIN Job USING (JobId) " /* from selected backup */ 844 "WHERE File.JobId IN (%s) AND Filename = '%s' AND PathId = %s " 845 "UNION ALL " 846 "SELECT JobTDate, PathId, Filename, DeltaSeq " /*Get all files from */ 847 "FROM BaseFiles " /* BaseJob */ 848 "JOIN File USING (FileId) " 849 "JOIN Job ON (BaseJobId = Job.JobId) " 850 "WHERE BaseFiles.JobId IN (%s) " /* Use Max(JobTDate) to find */ 851 " AND Filename = '%s' AND PathId = %s " 852 ") AS tmp " 853 "GROUP BY PathId, Filename, DeltaSeq " /* the latest file version */ 854 ") AS T1 " 855 "WHERE (Job.JobId IN ( " /* Security, we force JobId to be valid */ 856 "SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) " 857 "OR Job.JobId IN (%s)) " 858 "AND T1.JobTDate = Job.JobTDate " /* Join on JobTDate to get the orginal */ 859 "AND Job.JobId = File.JobId " /* Job/File record */ 860 "AND T1.PathId = File.PathId " 861 "AND T1.Filename = File.Filename"; 862 863 864 const char *bvfs_select_delta_version_with_basejob_and_delta[] = 865 { 866 /* MySQL */ 867 bvfs_select_delta_version_with_basejob_and_delta_default, 868 869 /* PostgreSQL */ /* The DISTINCT ON () permits to avoid extra join */ 870 "SELECT DISTINCT ON (Filename, PathId, DeltaSeq) JobTDate, JobId, FileId, " 871 "FileIndex, PathId, Filename, LStat, MD5, DeltaSeq " 872 "FROM " 873 "(SELECT FileId, JobId, PathId, Filename, FileIndex, LStat, MD5,DeltaSeq " 874 "FROM File WHERE JobId IN (%s) AND Filename = '%s' AND PathId = %s " 875 "UNION ALL " 876 "SELECT File.FileId, File.JobId, PathId, Filename, " 877 "File.FileIndex, LStat, MD5, DeltaSeq " 878 "FROM BaseFiles JOIN File USING (FileId) " 879 "WHERE BaseFiles.JobId IN (%s) AND Filename = '%s' AND PathId = %s " 880 ") AS T JOIN Job USING (JobId) " 881 "ORDER BY Filename, PathId, DeltaSeq, JobTDate DESC ", 882 883 /* SQLite */ 884 bvfs_select_delta_version_with_basejob_and_delta_default 885 }; 886 887 888 const char *batch_lock_path_query[] = 889 { 890 /* MySQL */ 891 "LOCK TABLES Path write,batch write,Path as p write", 892 /* PostgreSQL */ 893 "BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE ", 894 /* SQLite */ 895 "BEGIN " 896 }; 897 898 const char *batch_unlock_tables_query[] = 899 { 900 /* MySQL */ 901 "UNLOCK TABLES", 902 /* PostgreSQL */ 903 "COMMIT", 904 /* SQLite */ 905 "COMMIT" 906 }; 907 908 const char *batch_fill_path_query[] = 909 { 910 /* MySQL */ 911 "INSERT INTO Path (Path)" 912 "SELECT a.Path FROM " 913 "(SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS " 914 "(SELECT Path FROM Path AS p WHERE p.Path = a.Path)", 915 916 /* PostgreSQL */ 917 "INSERT INTO Path (Path)" 918 "SELECT a.Path FROM " 919 "(SELECT DISTINCT Path FROM batch) AS a " 920 "WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) ", 921 922 /* SQLite */ 923 "INSERT INTO Path (Path)" 924 "SELECT DISTINCT Path FROM batch " 925 "EXCEPT SELECT Path FROM Path" 926 }; 927 928 const char *match_query[] = 929 { 930 /* MySQL */ 931 "REGEXP", 932 /* PostgreSQL */ 933 "~", 934 /* SQLite */ 935 "LIKE" /* MATCH doesn't seems to work anymore... */ 936 }; 937 938 static const char *insert_counter_values_default = 939 "INSERT INTO Counters (Counter, MinValue, " 940 "MaxValue, CurrentValue, WrapCounter) " 941 "VALUES ('%s','%d','%d','%d','%s')"; 942 943 const char *insert_counter_values[] = { 944 /* MySQL */ 945 "INSERT INTO Counters (Counter, Counters.MinValue, " 946 "Counters.MaxValue, CurrentValue, WrapCounter) " 947 "VALUES ('%s','%d','%d','%d','%s')", 948 949 /* PostgreSQL */ 950 insert_counter_values_default, 951 952 /* SQLite */ 953 insert_counter_values_default 954 }; 955 956 static const char *select_counter_values_default = 957 "SELECT MinValue, MaxValue, CurrentValue, WrapCounter" 958 " FROM Counters WHERE Counter='%s'"; 959 960 const char *select_counter_values[] = 961 { 962 /* MySQL */ 963 "SELECT Counters.MinValue, Counters.MaxValue, CurrentValue, WrapCounter" 964 " FROM Counters WHERE Counter='%s'", 965 966 /* PostgreSQL */ 967 select_counter_values_default, 968 969 /* SQLite */ 970 select_counter_values_default 971 }; 972 973 static const char *update_counter_values_default = 974 "UPDATE Counters SET MinValue=%d, MaxValue=%d, CurrentValue=%d," 975 "WrapCounter='%s' WHERE Counter='%s'"; 976 977 const char *update_counter_values[] = 978 { 979 /* MySQL */ 980 "UPDATE Counters SET Counters.MinValue=%d, Counters.MaxValue=%d," 981 "CurrentValue=%d, WrapCounter='%s' WHERE Counter='%s'", 982 /* PostgreSQL */ 983 update_counter_values_default, 984 /* SQLite */ 985 update_counter_values_default 986 }; 987 988 const char *prune_cache[] = { 989 /* MySQL */ 990 " (Media.LastWritten + Media.CacheRetention) < NOW() ", 991 /* PostgreSQL */ 992 " (Media.LastWritten + (interval '1 second' * Media.CacheRetention)) < NOW() ", 993 /* SQLite */ 994 " ( (strftime('%s', Media.LastWritten) + Media.CacheRetention - strftime('%s', datetime('now', 'localtime'))) < 0) " 995 }; 996 997 const char *expired_volumes[] = { 998 /* MySQL */ 999 "SELECT Media.VolumeName AS volumename," 1000 "Media.LastWritten AS lastwritten" 1001 " FROM Media" 1002 " WHERE VolStatus IN ('Full', 'Used')" 1003 " AND Media.VolRetention > 0 " 1004 " AND ( Media.LastWritten + Media.VolRetention ) < NOW()" 1005 " %s ", 1006 /* PostgreSQL */ 1007 "SELECT Media.VolumeName, Media.LastWritten " 1008 " FROM Media " 1009 " WHERE VolStatus IN ('Full', 'Used') " 1010 " AND Media.VolRetention > 0 " 1011 " AND ( Media.LastWritten + (interval '1 second' * Media.VolRetention ) < NOW()) " 1012 " %s ", 1013 /* SQLite */ 1014 "SELECT Media.VolumeName AS volumename," 1015 "Media.LastWritten AS lastwritten" 1016 " FROM Media" 1017 " WHERE VolStatus IN ('Full', 'Used')" 1018 " AND Media.VolRetention > 0 " 1019 " AND ((strftime('%%s', LastWritten) + Media.VolRetention - strftime('%%s', datetime('now', 'localtime'))) < 0) " 1020 " %s " 1021 }; 1022 1023 const char *expires_in[] = { 1024 /* MySQL */ 1025 "(GREATEST(0, CAST(UNIX_TIMESTAMP(LastWritten) + Media.VolRetention AS SIGNED) - UNIX_TIMESTAMP(NOW())))", 1026 /* PostgreSQL */ 1027 "GREATEST(0, (extract('epoch' from LastWritten + Media.VolRetention * interval '1second' - NOW())::bigint))", 1028 /* SQLite */ 1029 "MAX(0, (strftime('%s', LastWritten) + Media.VolRetention - strftime('%s', datetime('now', 'localtime'))))" 1030 }; 1031 1032 const char *strip_restore[] = { 1033 /* MySQL */ 1034 "DELETE FROM %s WHERE FileId IN (SELECT * FROM (SELECT FileId FROM %s as B JOIN File USING (FileId) WHERE PathId IN (%s)) AS C)", 1035 /* PostgreSQL */ 1036 "DELETE FROM %s WHERE FileId IN (SELECT FileId FROM %s JOIN File USING (FileId) WHERE PathId IN (%s))", 1037 /* SQLite */ 1038 "DELETE FROM %s WHERE FileId IN (SELECT FileId FROM %s JOIN File USING (FileId) WHERE PathId IN (%s))" 1039 }; 1040 1041 const char *poolbytes[] = { 1042 /* MySQL */ 1043 "COALESCE((SELECT SUM(VolBytes+VolABytes) FROM Media WHERE Media.PoolId=Pool.PoolId), 0)", 1044 /* PostgreSQL */ 1045 "COALESCE((SELECT SUM(VolBytes+VolABytes) FROM Media WHERE Media.PoolId=Pool.PoolId), 0)::bigint", 1046 /* SQLite */ 1047 "COALESCE((SELECT SUM(VolBytes+VolABytes) FROM Media WHERE Media.PoolId=Pool.PoolId), 0)" 1048 }; 1049 1050 const char *count_all_jobs = "SELECT COUNT(1) FROM Job"; 1051 const char *count_success_jobs = "SELECT COUNT(1) FROM Job WHERE JobStatus IN ('T', 'I') AND JobErrors=0"; 1052 const char *count_success_jobids = "SELECT COUNT(1) FROM Job WHERE JobStatus IN ('T', 'I') AND JobErrors=0 and JobId in (%s)"; 1053 const char *count_error_jobs = "SELECT COUNT(1) FROM Job WHERE JobStatus IN ('E','f','A')"; 1054 const char *count_error_jobids = "SELECT COUNT(1) FROM Job WHERE JobStatus IN ('E','f','A') and JobId in (%s)"; 1055 const char *count_warning_jobs = "SELECT COUNT(1) FROM Job WHERE JobStatus IN ('T','I') AND NOT JobErrors=0"; 1056 const char *count_warning_jobids = "SELECT COUNT(1) FROM Job WHERE JobStatus IN ('T','I') AND NOT JobErrors=0 and JobId in (%s)"; 1057 const char *sum_jobs_bytes = "SELECT SUM(JobBytes) FROM Job"; 1058 const char *sum_jobids_bytes = "SELECT SUM(JobBytes) FROM Job WHERE JobId in (%s)"; 1059 const char *sum_jobs_files = "SELECT SUM(JobFiles) FROM Job"; 1060 const char *sum_jobids_files = "SELECT SUM(JobFiles) FROM Job WHERE JobId in (%s)"; 1061 const char *count_all_volumes = "SELECT COUNT(1) FROM Media"; 1062 const char *count_avl_volumes = "SELECT COUNT(1) FROM Media WHERE VolStatus='Append'"; 1063 const char *count_error_volumes = "SELECT COUNT(1) FROM Media WHERE VolStatus='Error'"; 1064 const char *count_full_volumes = "SELECT COUNT(1) FROM Media WHERE VolStatus='Full'"; 1065 const char *count_used_volumes = "SELECT COUNT(1) FROM Media WHERE VolStatus='Used'"; 1066 const char *sum_volumes_bytes = "SELECT SUM(VolBytes) FROM Media"; 1067 const char *get_volume_size = "SELECT VolBytes FROM Media WHERE VolumeName='%s'"; 1068 1069 static const char *escape_char_value_default = "\\"; 1070 1071 const char *escape_char_value[] = { 1072 /* MySQL */ 1073 "\\\\", 1074 /* PostgreSQL */ 1075 escape_char_value_default, 1076 /* SQLite */ 1077 escape_char_value_default 1078 }; 1079 1080 static const char *regexp_value_default = "REGEXP"; 1081 1082 const char *regexp_value[] = { 1083 /* MySQL */ 1084 regexp_value_default, 1085 /* PostgreSQL */ 1086 "~", 1087 /* SQLite */ 1088 regexp_value_default 1089 }; 1090