2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2015 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,PoolType,LabelFormat "
96 "FROM Pool WHERE Name='%s'", esc);
98 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,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, " Name='%s' ", esc);
400 append_filter(where, tmp);
402 } else if (jr->JobId != 0) {
403 Mmsg(tmp, " 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='%s' ", esc);
409 append_filter(where, tmp);
412 if (type == INCOMPLETE_JOBS && jr->JobStatus == JS_FatalError) {
413 Mmsg(tmp, " JobStatus IN ('E', 'f') ");
414 append_filter(where, tmp);
416 } else if (jr->JobStatus) {
417 Mmsg(tmp, " JobStatus='%c' ", jr->JobStatus);
418 append_filter(where, tmp);
422 Mmsg(tmp, " Type='%c' ", jr->JobType);
423 append_filter(where, tmp);
426 if (jr->JobErrors > 0) {
427 Mmsg(tmp, " JobErrors > 0 ");
428 append_filter(where, tmp);
431 if (jr->ClientId > 0) {
432 Mmsg(tmp, " 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,File.Filename) AS Filename "
583 "FROM BaseFiles, File, Path "
584 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
585 "AND BaseFiles.FileId = File.FileId "
586 "AND Path.PathId=File.PathId",
587 edit_int64(jobid, ed1));
589 Mmsg(cmd, "SELECT Path.Path||File.Filename AS Filename "
590 "FROM BaseFiles, File, Path "
591 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
592 "AND BaseFiles.FileId = File.FileId "
593 "AND Path.PathId=File.PathId",
594 edit_int64(jobid, ed1));
597 if (!bdb_big_sql_query(cmd, list_result, &lctx)) {
608 void BDB::bdb_list_snapshot_records(JCR *jcr, SNAPSHOT_DBR *sdbr,
609 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
611 POOLMEM *filter = get_pool_memory(PM_MESSAGE);
612 POOLMEM *tmp = get_pool_memory(PM_MESSAGE);
613 POOLMEM *esc = get_pool_memory(PM_MESSAGE);
620 bdb_escape_string(jcr, esc, sdbr->Name, strlen(sdbr->Name));
621 Mmsg(tmp, "Name='%s'", esc);
622 append_filter(filter, tmp);
624 if (sdbr->SnapshotId > 0) {
625 Mmsg(tmp, "Snapshot.SnapshotId=%d", sdbr->SnapshotId);
626 append_filter(filter, tmp);
628 if (sdbr->ClientId > 0) {
629 Mmsg(tmp, "Snapshot.ClientId=%d", sdbr->ClientId);
630 append_filter(filter, tmp);
632 if (sdbr->JobId > 0) {
633 Mmsg(tmp, "Snapshot.JobId=%d", sdbr->JobId);
634 append_filter(filter, tmp);
637 bdb_escape_string(jcr, esc, sdbr->Client, strlen(sdbr->Client));
638 Mmsg(tmp, "Client.Name='%s'", esc);
639 append_filter(filter, tmp);
641 if (sdbr->Device && *(sdbr->Device)) {
642 esc = check_pool_memory_size(esc, strlen(sdbr->Device) * 2 + 1);
643 bdb_escape_string(jcr, esc, sdbr->Device, strlen(sdbr->Device));
644 Mmsg(tmp, "Device='%s'", esc);
645 append_filter(filter, tmp);
648 bdb_escape_string(jcr, esc, sdbr->Type, strlen(sdbr->Type));
649 Mmsg(tmp, "Type='%s'", esc);
650 append_filter(filter, tmp);
652 if (*sdbr->created_before) {
653 bdb_escape_string(jcr, esc, sdbr->created_before, strlen(sdbr->created_before));
654 Mmsg(tmp, "CreateDate <= '%s'", esc);
655 append_filter(filter, tmp);
657 if (*sdbr->created_after) {
658 bdb_escape_string(jcr, esc, sdbr->created_after, strlen(sdbr->created_after));
659 Mmsg(tmp, "CreateDate >= '%s'", esc);
660 append_filter(filter, tmp);
663 Mmsg(tmp, "CreateTDate < (%s - Retention)", edit_int64(time(NULL), ed1));
664 append_filter(filter, tmp);
666 if (*sdbr->CreateDate) {
667 bdb_escape_string(jcr, esc, sdbr->CreateDate, strlen(sdbr->CreateDate));
668 Mmsg(tmp, "CreateDate = '%s'", esc);
669 append_filter(filter, tmp);
672 if (sdbr->sorted_client) {
673 pm_strcat(filter, " ORDER BY Client.Name, SnapshotId DESC");
676 pm_strcat(filter, " ORDER BY SnapshotId DESC");
679 if (type == VERT_LIST || type == ARG_LIST) {
680 Mmsg(cmd, "SELECT SnapshotId, Snapshot.Name, CreateDate, Client.Name AS Client, "
681 "FileSet.FileSet AS FileSet, JobId, Volume, Device, Type, Retention, Comment "
682 "FROM Snapshot JOIN Client USING (ClientId) LEFT JOIN FileSet USING (FileSetId) %s", filter);
684 } else if (type == HORZ_LIST) {
685 Mmsg(cmd, "SELECT SnapshotId, Snapshot.Name, CreateDate, Client.Name AS Client, "
687 "FROM Snapshot JOIN Client USING (ClientId) %s", filter);
690 if (!QueryDB(jcr, cmd)) {
694 list_result(jcr, this, sendit, ctx, type);
700 free_pool_memory(filter);
701 free_pool_memory(esc);
702 free_pool_memory(tmp);
705 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */