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];
200 bdb_escape_string(jcr, esc, mdbr->VolumeName, strlen(mdbr->VolumeName));
202 if (type == VERT_LIST) {
203 if (mdbr->VolumeName[0] != 0) {
204 Mmsg(cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
205 "MediaType,MediaTypeId,FirstWritten,LastWritten,LabelDate,VolJobs,"
206 "VolFiles,VolBlocks,VolMounts,VolBytes,VolABytes,VolAPadding,"
207 "VolHoleBytes,VolHoles,VolErrors,VolWrites,"
208 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
209 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
210 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
211 "MediaAddressing,VolReadTime,VolWriteTime,"
212 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
213 "ActionOnPurge,Comment"
214 " FROM Media WHERE Media.VolumeName='%s'", esc);
216 Mmsg(cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
217 "MediaType,MediaTypeId,FirstWritten,LastWritten,LabelDate,VolJobs,"
218 "VolFiles,VolBlocks,VolMounts,VolBytes,VolABytes,VolAPadding,"
219 "VolHoleBytes,VolHoles,VolErrors,VolWrites,"
220 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
221 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
222 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
223 "MediaAddressing,VolReadTime,VolWriteTime,"
224 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
225 "ActionOnPurge,Comment"
226 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
227 edit_int64(mdbr->PoolId, ed1));
230 if (mdbr->VolumeName[0] != 0) {
231 Mmsg(cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
232 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,VolParts,LastWritten "
233 "FROM Media WHERE Media.VolumeName='%s'", esc);
235 Mmsg(cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
236 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,VolParts,LastWritten "
237 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
238 edit_int64(mdbr->PoolId, ed1));
242 if (!QueryDB(jcr, cmd)) {
247 list_result(jcr, this, sendit, ctx, type);
253 void BDB::bdb_list_jobmedia_records(JCR *jcr, uint32_t JobId,
254 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
259 if (type == VERT_LIST) {
260 if (JobId > 0) { /* do by JobId */
261 Mmsg(cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
262 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
264 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
265 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
267 Mmsg(cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
268 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
270 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
274 if (JobId > 0) { /* do by JobId */
275 Mmsg(cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
276 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
277 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
279 Mmsg(cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
280 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
283 if (!QueryDB(jcr, cmd)) {
288 list_result(jcr, this, sendit, ctx, type);
295 void BDB::bdb_list_copies_records(JCR *jcr, uint32_t limit, char *JobIds,
296 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
298 POOL_MEM str_limit(PM_MESSAGE);
299 POOL_MEM str_jobids(PM_MESSAGE);
302 Mmsg(str_limit, " LIMIT %d", limit);
305 if (JobIds && JobIds[0]) {
306 Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ",
312 "SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, "
313 "Job.JobId AS CopyJobId, Media.MediaType "
315 "JOIN JobMedia USING (JobId) "
316 "JOIN Media USING (MediaId) "
317 "WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s",
318 (char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str());
320 if (!QueryDB(jcr, cmd)) {
324 if (sql_num_rows()) {
325 if (JobIds && JobIds[0]) {
326 sendit(ctx, _("These JobIds have copies as follows:\n"));
328 sendit(ctx, _("The catalog contains copies as follows:\n"));
331 list_result(jcr, this, sendit, ctx, type);
340 void BDB::bdb_list_joblog_records(JCR *jcr, uint32_t JobId,
341 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
349 if (type == VERT_LIST) {
350 Mmsg(cmd, "SELECT Time,LogText FROM Log "
351 "WHERE Log.JobId=%s ORDER BY LogId ASC", edit_int64(JobId, ed1));
353 Mmsg(cmd, "SELECT LogText FROM Log "
354 "WHERE Log.JobId=%s ORDER BY LogId ASC", edit_int64(JobId, ed1));
356 if (!QueryDB(jcr, cmd)) {
360 list_result(jcr, this, sendit, ctx, type);
370 * List Job record(s) that match JOB_DBR
372 * Currently, we return all jobs or if jr->JobId is set,
373 * only the job with the specified id.
375 alist *BDB::bdb_list_job_records(JCR *jcr, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
376 void *ctx, e_list_type type)
380 char esc[MAX_ESCAPE_NAME_LENGTH];
382 POOLMEM *where = get_pool_memory(PM_MESSAGE);
383 POOLMEM *tmp = get_pool_memory(PM_MESSAGE);
384 const char *order = "ASC";
388 if (jr->order == 1) {
392 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
397 bdb_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
398 Mmsg(tmp, " Name='%s' ", esc);
399 append_filter(where, tmp);
401 } else if (jr->JobId != 0) {
402 Mmsg(tmp, " JobId=%s ", edit_int64(jr->JobId, ed1));
403 append_filter(where, tmp);
405 } else if (jr->Job[0] != 0) {
406 bdb_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
407 Mmsg(tmp, " Job='%s' ", esc);
408 append_filter(where, tmp);
411 if (type == INCOMPLETE_JOBS && jr->JobStatus == JS_FatalError) {
412 Mmsg(tmp, " JobStatus IN ('E', 'f') ");
413 append_filter(where, tmp);
415 } else if (jr->JobStatus) {
416 Mmsg(tmp, " JobStatus='%c' ", jr->JobStatus);
417 append_filter(where, tmp);
421 Mmsg(tmp, " Type='%c' ", jr->JobType);
422 append_filter(where, tmp);
425 if (jr->JobErrors > 0) {
426 Mmsg(tmp, " JobErrors > 0 ");
427 append_filter(where, tmp);
430 if (jr->ClientId > 0) {
431 Mmsg(tmp, " ClientId=%s ", edit_int64(jr->ClientId, ed1));
432 append_filter(where, tmp);
438 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
439 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
440 "StartTime,EndTime,RealEndTime,JobTDate,"
441 "VolSessionId,VolSessionTime,JobFiles,JobBytes,ReadBytes,JobErrors,"
442 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
443 "Job.FileSetId,FileSet.FileSet,Job.HasBase,Job.HasCache,Job.Comment "
444 "FROM Job JOIN Client USING (ClientId) LEFT JOIN Pool USING (PoolId) "
445 "LEFT JOIN FileSet USING (FileSetId) %s "
446 "ORDER BY StartTime %s %s", where, order, limit);
450 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
451 "FROM Job %s ORDER BY StartTime %s,JobId %s %s", where, order, order, limit);
453 case INCOMPLETE_JOBS:
455 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
456 "FROM Job %s ORDER BY StartTime %s,JobId %s %s",
457 where, order, order, limit);
462 Dmsg1(100, "SQL: %s\n", cmd);
464 free_pool_memory(tmp);
465 free_pool_memory(where);
467 Dmsg1(000, "cmd: %s\n", cmd);
468 if (!QueryDB(jcr, cmd)) {
472 if (type == INCOMPLETE_JOBS) {
474 list = New(alist(10));
476 for (int i=0; (row=sql_fetch_row()) != NULL; i++) {
477 list->append(bstrdup(row[0]));
481 list_result(jcr, this, sendit, ctx, type);
491 void BDB::bdb_list_job_totals(JCR *jcr, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
496 Mmsg(cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
497 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
499 if (!QueryDB(jcr, cmd)) {
504 list_result(jcr, this, sendit, ctx, HORZ_LIST);
509 Mmsg(cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
510 "AS Files,sum(JobBytes) As Bytes FROM Job");
512 if (!QueryDB(jcr, cmd)) {
517 list_result(jcr, this, sendit, ctx, HORZ_LIST);
523 void BDB::bdb_list_files_for_job(JCR *jcr, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
526 LIST_CTX lctx(jcr, this, sendit, ctx, HORZ_LIST);
531 * Stupid MySQL is NON-STANDARD !
533 if (bdb_get_type_index() == SQL_TYPE_MYSQL) {
534 Mmsg(cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
535 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
537 "SELECT PathId, FilenameId "
538 "FROM BaseFiles JOIN File "
539 "ON (BaseFiles.FileId = File.FileId) "
540 "WHERE BaseFiles.JobId = %s"
541 ") AS F, Filename,Path "
542 "WHERE Filename.FilenameId=F.FilenameId "
543 "AND Path.PathId=F.PathId",
544 edit_int64(jobid, ed1), ed1);
546 Mmsg(cmd, "SELECT Path.Path||Filename.Name AS Filename "
547 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
549 "SELECT PathId, FilenameId "
550 "FROM BaseFiles JOIN File "
551 "ON (BaseFiles.FileId = File.FileId) "
552 "WHERE BaseFiles.JobId = %s"
553 ") AS F, Filename,Path "
554 "WHERE Filename.FilenameId=F.FilenameId "
555 "AND Path.PathId=F.PathId",
556 edit_int64(jobid, ed1), ed1);
559 if (!bdb_big_sql_query(cmd, list_result, &lctx)) {
570 void BDB::bdb_list_base_files_for_job(JCR *jcr, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
573 LIST_CTX lctx(jcr, this, sendit, ctx, HORZ_LIST);
578 * Stupid MySQL is NON-STANDARD !
580 if (bdb_get_type_index() == SQL_TYPE_MYSQL) {
581 Mmsg(cmd, "SELECT CONCAT(Path.Path,File.Filename) AS Filename "
582 "FROM BaseFiles, File, Path "
583 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
584 "AND BaseFiles.FileId = File.FileId "
585 "AND Path.PathId=File.PathId",
586 edit_int64(jobid, ed1));
588 Mmsg(cmd, "SELECT Path.Path||File.Filename AS Filename "
589 "FROM BaseFiles, File, Path "
590 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
591 "AND BaseFiles.FileId = File.FileId "
592 "AND Path.PathId=File.PathId",
593 edit_int64(jobid, ed1));
596 if (!bdb_big_sql_query(cmd, list_result, &lctx)) {
607 void BDB::bdb_list_snapshot_records(JCR *jcr, SNAPSHOT_DBR *sdbr,
608 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
610 POOLMEM *filter = get_pool_memory(PM_MESSAGE);
611 POOLMEM *tmp = get_pool_memory(PM_MESSAGE);
612 POOLMEM *esc = get_pool_memory(PM_MESSAGE);
619 bdb_escape_string(jcr, esc, sdbr->Name, strlen(sdbr->Name));
620 Mmsg(tmp, "Name='%s'", esc);
621 append_filter(filter, tmp);
623 if (sdbr->SnapshotId > 0) {
624 Mmsg(tmp, "Snapshot.SnapshotId=%d", sdbr->SnapshotId);
625 append_filter(filter, tmp);
627 if (sdbr->ClientId > 0) {
628 Mmsg(tmp, "Snapshot.ClientId=%d", sdbr->ClientId);
629 append_filter(filter, tmp);
631 if (sdbr->JobId > 0) {
632 Mmsg(tmp, "Snapshot.JobId=%d", sdbr->JobId);
633 append_filter(filter, tmp);
636 bdb_escape_string(jcr, esc, sdbr->Client, strlen(sdbr->Client));
637 Mmsg(tmp, "Client.Name='%s'", esc);
638 append_filter(filter, tmp);
640 if (sdbr->Device && *(sdbr->Device)) {
641 esc = check_pool_memory_size(esc, strlen(sdbr->Device) * 2 + 1);
642 bdb_escape_string(jcr, esc, sdbr->Device, strlen(sdbr->Device));
643 Mmsg(tmp, "Device='%s'", esc);
644 append_filter(filter, tmp);
647 bdb_escape_string(jcr, esc, sdbr->Type, strlen(sdbr->Type));
648 Mmsg(tmp, "Type='%s'", esc);
649 append_filter(filter, tmp);
651 if (*sdbr->created_before) {
652 bdb_escape_string(jcr, esc, sdbr->created_before, strlen(sdbr->created_before));
653 Mmsg(tmp, "CreateDate <= '%s'", esc);
654 append_filter(filter, tmp);
656 if (*sdbr->created_after) {
657 bdb_escape_string(jcr, esc, sdbr->created_after, strlen(sdbr->created_after));
658 Mmsg(tmp, "CreateDate >= '%s'", esc);
659 append_filter(filter, tmp);
662 Mmsg(tmp, "CreateTDate < (%s - Retention)", edit_int64(time(NULL), ed1));
663 append_filter(filter, tmp);
665 if (*sdbr->CreateDate) {
666 bdb_escape_string(jcr, esc, sdbr->CreateDate, strlen(sdbr->CreateDate));
667 Mmsg(tmp, "CreateDate = '%s'", esc);
668 append_filter(filter, tmp);
671 if (sdbr->sorted_client) {
672 pm_strcat(filter, " ORDER BY Client.Name, SnapshotId DESC");
675 pm_strcat(filter, " ORDER BY SnapshotId DESC");
678 if (type == VERT_LIST || type == ARG_LIST) {
679 Mmsg(cmd, "SELECT SnapshotId, Snapshot.Name, CreateDate, Client.Name AS Client, "
680 "FileSet.FileSet AS FileSet, JobId, Volume, Device, Type, Retention, Comment "
681 "FROM Snapshot JOIN Client USING (ClientId) LEFT JOIN FileSet USING (FileSetId) %s", filter);
683 } else if (type == HORZ_LIST) {
684 Mmsg(cmd, "SELECT SnapshotId, Snapshot.Name, CreateDate, Client.Name AS Client, "
686 "FROM Snapshot JOIN Client USING (ClientId) %s", filter);
689 if (!QueryDB(jcr, cmd)) {
693 list_result(jcr, this, sendit, ctx, type);
699 free_pool_memory(filter);
700 free_pool_memory(esc);
701 free_pool_memory(tmp);
704 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */