2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2016 Kern Sibbald
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.
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.
14 This notice must be preserved when any source code is
15 conveyed and/or propagated.
17 Bacula(R) is a registered trademark of Kern Sibbald.
20 * Bacula Catalog Database List records interface routines
22 * Written by Kern Sibbald, March 2000
28 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL
32 /* -----------------------------------------------------------------------
34 * Generic Routines (or almost generic)
36 * -----------------------------------------------------------------------
39 #define append_filter(buf, sql) \
42 pm_strcat(buf, " AND ");\
44 pm_strcpy(buf, " WHERE ");\
46 pm_strcat(buf, sql); \
50 * Submit general SQL query
52 int BDB::bdb_list_sql_query(JCR *jcr, const char *query, DB_LIST_HANDLER *sendit,
53 void *ctx, int verbose, e_list_type type)
56 if (!sql_query(query, QF_STORE_RESULT)) {
57 Mmsg(errmsg, _("Query failed: %s\n"), sql_strerror());
65 list_result(jcr,this, sendit, ctx, type);
71 void BDB::bdb_list_pool_records(JCR *jcr, POOL_DBR *pdbr,
72 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
74 char esc[MAX_ESCAPE_NAME_LENGTH];
77 bdb_escape_string(jcr, esc, pdbr->Name, strlen(pdbr->Name));
79 if (type == VERT_LIST) {
80 if (pdbr->Name[0] != 0) {
81 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
82 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
83 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
84 "RecyclePoolId,LabelType "
85 " FROM Pool WHERE Name='%s'", esc);
87 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
88 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
89 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
90 "RecyclePoolId,LabelType "
91 " FROM Pool ORDER BY PoolId");
94 if (pdbr->Name[0] != 0) {
95 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,MaxVolBytes,VolRetention,Enabled,PoolType,LabelFormat "
96 "FROM Pool WHERE Name='%s'", esc);
98 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,MaxVolBytes,VolRetention,Enabled,PoolType,LabelFormat "
99 "FROM Pool ORDER BY PoolId");
103 if (!QueryDB(jcr, cmd)) {
108 list_result(jcr, this, sendit, ctx, type);
114 void BDB::bdb_list_client_records(JCR *jcr, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
117 if (type == VERT_LIST) {
118 Mmsg(cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
120 "FROM Client ORDER BY ClientId");
122 Mmsg(cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
123 "FROM Client ORDER BY ClientId");
126 if (!QueryDB(jcr, cmd)) {
131 list_result(jcr, this, sendit, ctx, type);
138 * List restore objects
140 * JobId | JobIds: List RestoreObjects for specific Job(s)
141 * It is possible to specify the ObjectType using FileType field.
143 void BDB::bdb_list_restore_objects(JCR *jcr, ROBJECT_DBR *rr, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
149 if (rr->JobIds && is_a_number_list(rr->JobIds)) {
152 } else if (rr->JobId) {
153 jobid = edit_int64(rr->JobId, ed1);
159 if (rr->FileType > 0) {
160 Mmsg(filter, "AND ObjectType = %d ", rr->FileType);
164 if (type == VERT_LIST) {
165 Mmsg(cmd, "SELECT JobId, RestoreObjectId, ObjectName, "
166 "PluginName, ObjectType "
167 "FROM RestoreObject JOIN Job USING (JobId) WHERE JobId IN (%s) %s "
168 "ORDER BY JobTDate ASC, RestoreObjectId",
169 jobid, filter.c_str());
171 Mmsg(cmd, "SELECT JobId, RestoreObjectId, ObjectName, "
172 "PluginName, ObjectType, ObjectLength "
173 "FROM RestoreObject JOIN Job USING (JobId) WHERE JobId IN (%s) %s "
174 "ORDER BY JobTDate ASC, RestoreObjectId",
175 jobid, filter.c_str());
178 if (!QueryDB(jcr, cmd)) {
183 list_result(jcr, this, sendit, ctx, type);
190 * If VolumeName is non-zero, list the record for that Volume
191 * otherwise, list the Volumes in the Pool specified by PoolId
193 void BDB::bdb_list_media_records(JCR *jcr, MEDIA_DBR *mdbr,
194 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
197 char esc[MAX_ESCAPE_NAME_LENGTH];
198 const char *expiresin = expires_in[bdb_get_type_index()];
201 bdb_escape_string(jcr, esc, mdbr->VolumeName, strlen(mdbr->VolumeName));
203 if (type == VERT_LIST) {
204 if (mdbr->VolumeName[0] != 0) {
205 Mmsg(cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
206 "MediaType,MediaTypeId,FirstWritten,LastWritten,LabelDate,VolJobs,"
207 "VolFiles,VolBlocks,VolMounts,VolBytes,VolABytes,VolAPadding,"
208 "VolHoleBytes,VolHoles,VolErrors,VolWrites,"
209 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
210 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
211 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
212 "MediaAddressing,VolReadTime,VolWriteTime,"
213 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
214 "ActionOnPurge,%s AS ExpiresIn, Comment"
215 " FROM Media WHERE Media.VolumeName='%s'", expiresin, esc);
217 Mmsg(cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
218 "MediaType,MediaTypeId,FirstWritten,LastWritten,LabelDate,VolJobs,"
219 "VolFiles,VolBlocks,VolMounts,VolBytes,VolABytes,VolAPadding,"
220 "VolHoleBytes,VolHoles,VolErrors,VolWrites,"
221 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
222 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
223 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
224 "MediaAddressing,VolReadTime,VolWriteTime,"
225 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
226 "ActionOnPurge,%s AS ExpiresIn, Comment"
227 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
228 expiresin, edit_int64(mdbr->PoolId, ed1));
231 if (mdbr->VolumeName[0] != 0) {
232 Mmsg(cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
233 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten,%s AS ExpiresIn "
234 "FROM Media WHERE Media.VolumeName='%s'", expiresin, esc);
236 Mmsg(cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
237 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten,%s AS ExpiresIn "
238 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
239 expiresin, edit_int64(mdbr->PoolId, ed1));
243 if (!QueryDB(jcr, cmd)) {
248 list_result(jcr, this, sendit, ctx, type);
254 void BDB::bdb_list_jobmedia_records(JCR *jcr, uint32_t JobId,
255 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
260 if (type == VERT_LIST) {
261 if (JobId > 0) { /* do by JobId */
262 Mmsg(cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
263 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
265 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
266 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
268 Mmsg(cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
269 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
271 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
275 if (JobId > 0) { /* do by JobId */
276 Mmsg(cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
277 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
278 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
280 Mmsg(cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
281 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
284 if (!QueryDB(jcr, cmd)) {
289 list_result(jcr, this, sendit, ctx, type);
296 void BDB::bdb_list_copies_records(JCR *jcr, uint32_t limit, char *JobIds,
297 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
299 POOL_MEM str_limit(PM_MESSAGE);
300 POOL_MEM str_jobids(PM_MESSAGE);
303 Mmsg(str_limit, " LIMIT %d", limit);
306 if (JobIds && JobIds[0]) {
307 Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ",
313 "SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, "
314 "Job.JobId AS CopyJobId, Media.MediaType "
316 "JOIN JobMedia USING (JobId) "
317 "JOIN Media USING (MediaId) "
318 "WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s",
319 (char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str());
321 if (!QueryDB(jcr, cmd)) {
325 if (sql_num_rows()) {
326 if (JobIds && JobIds[0]) {
327 sendit(ctx, _("These JobIds have copies as follows:\n"));
329 sendit(ctx, _("The catalog contains copies as follows:\n"));
332 list_result(jcr, this, sendit, ctx, type);
341 void BDB::bdb_list_joblog_records(JCR *jcr, uint32_t JobId,
342 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
350 if (type == VERT_LIST) {
351 Mmsg(cmd, "SELECT Time,LogText FROM Log "
352 "WHERE Log.JobId=%s ORDER BY LogId ASC", edit_int64(JobId, ed1));
354 Mmsg(cmd, "SELECT LogText FROM Log "
355 "WHERE Log.JobId=%s ORDER BY LogId ASC", edit_int64(JobId, ed1));
357 if (!QueryDB(jcr, cmd)) {
361 list_result(jcr, this, sendit, ctx, type);
371 * List Job record(s) that match JOB_DBR
373 * Currently, we return all jobs or if jr->JobId is set,
374 * only the job with the specified id.
376 alist *BDB::bdb_list_job_records(JCR *jcr, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
377 void *ctx, e_list_type type)
381 char esc[MAX_ESCAPE_NAME_LENGTH];
383 POOLMEM *where = get_pool_memory(PM_MESSAGE);
384 POOLMEM *tmp = get_pool_memory(PM_MESSAGE);
385 const char *order = "ASC";
389 if (jr->order == 1) {
393 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
398 bdb_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
399 Mmsg(tmp, " Job.Name='%s' ", esc);
400 append_filter(where, tmp);
402 } else if (jr->JobId != 0) {
403 Mmsg(tmp, " Job.JobId=%s ", edit_int64(jr->JobId, ed1));
404 append_filter(where, tmp);
406 } else if (jr->Job[0] != 0) {
407 bdb_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
408 Mmsg(tmp, " Job.Job='%s' ", esc);
409 append_filter(where, tmp);
412 if (type == INCOMPLETE_JOBS && jr->JobStatus == JS_FatalError) {
413 Mmsg(tmp, " Job.JobStatus IN ('E', 'f') ");
414 append_filter(where, tmp);
416 } else if (jr->JobStatus) {
417 Mmsg(tmp, " Job.JobStatus='%c' ", jr->JobStatus);
418 append_filter(where, tmp);
422 Mmsg(tmp, " Job.Type='%c' ", jr->JobType);
423 append_filter(where, tmp);
426 if (jr->JobErrors > 0) {
427 Mmsg(tmp, " Job.JobErrors > 0 ");
428 append_filter(where, tmp);
431 if (jr->ClientId > 0) {
432 Mmsg(tmp, " Job.ClientId=%s ", edit_int64(jr->ClientId, ed1));
433 append_filter(where, tmp);
439 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
440 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
441 "StartTime,EndTime,RealEndTime,JobTDate,"
442 "VolSessionId,VolSessionTime,JobFiles,JobBytes,ReadBytes,JobErrors,"
443 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
444 "Job.FileSetId,FileSet.FileSet,Job.HasBase,Job.HasCache,Job.Comment "
445 "FROM Job JOIN Client USING (ClientId) LEFT JOIN Pool USING (PoolId) "
446 "LEFT JOIN FileSet USING (FileSetId) %s "
447 "ORDER BY StartTime %s %s", where, order, limit);
451 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
452 "FROM Job %s ORDER BY StartTime %s,JobId %s %s", where, order, order, limit);
454 case INCOMPLETE_JOBS:
456 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
457 "FROM Job %s ORDER BY StartTime %s,JobId %s %s",
458 where, order, order, limit);
463 Dmsg1(100, "SQL: %s\n", cmd);
465 free_pool_memory(tmp);
466 free_pool_memory(where);
468 Dmsg1(000, "cmd: %s\n", cmd);
469 if (!QueryDB(jcr, cmd)) {
473 if (type == INCOMPLETE_JOBS) {
475 list = New(alist(10));
477 for (int i=0; (row=sql_fetch_row()) != NULL; i++) {
478 list->append(bstrdup(row[0]));
482 list_result(jcr, this, sendit, ctx, type);
492 void BDB::bdb_list_job_totals(JCR *jcr, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
497 Mmsg(cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
498 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
500 if (!QueryDB(jcr, cmd)) {
505 list_result(jcr, this, sendit, ctx, HORZ_LIST);
510 Mmsg(cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
511 "AS Files,sum(JobBytes) As Bytes FROM Job");
513 if (!QueryDB(jcr, cmd)) {
518 list_result(jcr, this, sendit, ctx, HORZ_LIST);
524 void BDB::bdb_list_files_for_job(JCR *jcr, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
527 LIST_CTX lctx(jcr, this, sendit, ctx, HORZ_LIST);
532 * Stupid MySQL is NON-STANDARD !
534 if (bdb_get_type_index() == SQL_TYPE_MYSQL) {
535 Mmsg(cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
536 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
538 "SELECT PathId, FilenameId "
539 "FROM BaseFiles JOIN File "
540 "ON (BaseFiles.FileId = File.FileId) "
541 "WHERE BaseFiles.JobId = %s"
542 ") AS F, Filename,Path "
543 "WHERE Filename.FilenameId=F.FilenameId "
544 "AND Path.PathId=F.PathId",
545 edit_int64(jobid, ed1), ed1);
547 Mmsg(cmd, "SELECT Path.Path||Filename.Name AS Filename "
548 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
550 "SELECT PathId, FilenameId "
551 "FROM BaseFiles JOIN File "
552 "ON (BaseFiles.FileId = File.FileId) "
553 "WHERE BaseFiles.JobId = %s"
554 ") AS F, Filename,Path "
555 "WHERE Filename.FilenameId=F.FilenameId "
556 "AND Path.PathId=F.PathId",
557 edit_int64(jobid, ed1), ed1);
560 if (!bdb_big_sql_query(cmd, list_result, &lctx)) {
571 void BDB::bdb_list_base_files_for_job(JCR *jcr, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
574 LIST_CTX lctx(jcr, this, sendit, ctx, HORZ_LIST);
579 * Stupid MySQL is NON-STANDARD !
581 if (bdb_get_type_index() == SQL_TYPE_MYSQL) {
582 Mmsg(cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
583 "FROM BaseFiles, File, Filename, Path "
584 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
585 "AND BaseFiles.FileId = File.FileId "
586 "AND Filename.FilenameId=File.FilenameId "
587 "AND Path.PathId=File.PathId",
588 edit_int64(jobid, ed1));
590 Mmsg(cmd, "SELECT Path.Path||Filename.Name AS Filename "
591 "FROM BaseFiles, File, Filename, Path "
592 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
593 "AND BaseFiles.FileId = File.FileId "
594 "AND Filename.FilenameId=File.FilenameId "
595 "AND Path.PathId=File.PathId",
596 edit_int64(jobid, ed1));
599 if (!bdb_big_sql_query(cmd, list_result, &lctx)) {
610 void BDB::bdb_list_snapshot_records(JCR *jcr, SNAPSHOT_DBR *sdbr,
611 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
613 POOLMEM *filter = get_pool_memory(PM_MESSAGE);
614 POOLMEM *tmp = get_pool_memory(PM_MESSAGE);
615 POOLMEM *esc = get_pool_memory(PM_MESSAGE);
622 bdb_escape_string(jcr, esc, sdbr->Name, strlen(sdbr->Name));
623 Mmsg(tmp, "Name='%s'", esc);
624 append_filter(filter, tmp);
626 if (sdbr->SnapshotId > 0) {
627 Mmsg(tmp, "Snapshot.SnapshotId=%d", sdbr->SnapshotId);
628 append_filter(filter, tmp);
630 if (sdbr->ClientId > 0) {
631 Mmsg(tmp, "Snapshot.ClientId=%d", sdbr->ClientId);
632 append_filter(filter, tmp);
634 if (sdbr->JobId > 0) {
635 Mmsg(tmp, "Snapshot.JobId=%d", sdbr->JobId);
636 append_filter(filter, tmp);
639 bdb_escape_string(jcr, esc, sdbr->Client, strlen(sdbr->Client));
640 Mmsg(tmp, "Client.Name='%s'", esc);
641 append_filter(filter, tmp);
643 if (sdbr->Device && *(sdbr->Device)) {
644 esc = check_pool_memory_size(esc, strlen(sdbr->Device) * 2 + 1);
645 bdb_escape_string(jcr, esc, sdbr->Device, strlen(sdbr->Device));
646 Mmsg(tmp, "Device='%s'", esc);
647 append_filter(filter, tmp);
650 bdb_escape_string(jcr, esc, sdbr->Type, strlen(sdbr->Type));
651 Mmsg(tmp, "Type='%s'", esc);
652 append_filter(filter, tmp);
654 if (*sdbr->created_before) {
655 bdb_escape_string(jcr, esc, sdbr->created_before, strlen(sdbr->created_before));
656 Mmsg(tmp, "CreateDate <= '%s'", esc);
657 append_filter(filter, tmp);
659 if (*sdbr->created_after) {
660 bdb_escape_string(jcr, esc, sdbr->created_after, strlen(sdbr->created_after));
661 Mmsg(tmp, "CreateDate >= '%s'", esc);
662 append_filter(filter, tmp);
665 Mmsg(tmp, "CreateTDate < (%s - Retention)", edit_int64(time(NULL), ed1));
666 append_filter(filter, tmp);
668 if (*sdbr->CreateDate) {
669 bdb_escape_string(jcr, esc, sdbr->CreateDate, strlen(sdbr->CreateDate));
670 Mmsg(tmp, "CreateDate = '%s'", esc);
671 append_filter(filter, tmp);
674 if (sdbr->sorted_client) {
675 pm_strcat(filter, " ORDER BY Client.Name, SnapshotId DESC");
678 pm_strcat(filter, " ORDER BY SnapshotId DESC");
681 if (type == VERT_LIST || type == ARG_LIST) {
682 Mmsg(cmd, "SELECT SnapshotId, Snapshot.Name, CreateDate, Client.Name AS Client, "
683 "FileSet.FileSet AS FileSet, JobId, Volume, Device, Type, Retention, Comment "
684 "FROM Snapshot JOIN Client USING (ClientId) LEFT JOIN FileSet USING (FileSetId) %s", filter);
686 } else if (type == HORZ_LIST) {
687 Mmsg(cmd, "SELECT SnapshotId, Snapshot.Name, CreateDate, Client.Name AS Client, "
689 "FROM Snapshot JOIN Client USING (ClientId) %s", filter);
692 if (!QueryDB(jcr, cmd)) {
696 list_result(jcr, this, sendit, ctx, type);
702 free_pool_memory(filter);
703 free_pool_memory(esc);
704 free_pool_memory(tmp);
707 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */