2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2015 Kern Sibbald
5 Copyright (C) 2000-2014 Free Software Foundation Europe e.V.
7 The original author of Bacula is Kern Sibbald, with contributions
8 from many others, a complete list can be found in the file AUTHORS.
10 You may use this file and others of this release according to the
11 license defined in the LICENSE file, which includes the Affero General
12 Public License, v3.0 ("AGPLv3") and some additional permissions and
13 terms pursuant to its AGPLv3 Section 7.
15 This notice must be preserved when any source code is
16 conveyed and/or propagated.
18 Bacula(R) is a registered trademark of Kern Sibbald.
21 * Bacula Catalog Database List records interface routines
23 * Written by Kern Sibbald, March 2000
29 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL
33 /* -----------------------------------------------------------------------
35 * Generic Routines (or almost generic)
37 * -----------------------------------------------------------------------
41 * Submit general SQL query
43 int BDB::bdb_list_sql_query(JCR *jcr, const char *query, DB_LIST_HANDLER *sendit,
44 void *ctx, int verbose, e_list_type type)
47 if (!sql_query(query, QF_STORE_RESULT)) {
48 Mmsg(errmsg, _("Query failed: %s\n"), sql_strerror());
56 list_result(jcr,this, sendit, ctx, type);
62 void BDB::bdb_list_pool_records(JCR *jcr, POOL_DBR *pdbr,
63 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
65 char esc[MAX_ESCAPE_NAME_LENGTH];
68 bdb_escape_string(jcr, esc, pdbr->Name, strlen(pdbr->Name));
70 if (type == VERT_LIST) {
71 if (pdbr->Name[0] != 0) {
72 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
73 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
74 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
75 "RecyclePoolId,LabelType "
76 " FROM Pool WHERE Name='%s'", esc);
78 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
79 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
80 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
81 "RecyclePoolId,LabelType "
82 " FROM Pool ORDER BY PoolId");
85 if (pdbr->Name[0] != 0) {
86 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
87 "FROM Pool WHERE Name='%s'", esc);
89 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
90 "FROM Pool ORDER BY PoolId");
94 if (!QueryDB(jcr, cmd)) {
99 list_result(jcr, this, sendit, ctx, type);
105 void BDB::bdb_list_client_records(JCR *jcr, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
108 if (type == VERT_LIST) {
109 Mmsg(cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
111 "FROM Client ORDER BY ClientId");
113 Mmsg(cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
114 "FROM Client ORDER BY ClientId");
117 if (!QueryDB(jcr, cmd)) {
122 list_result(jcr, this, sendit, ctx, type);
129 * List restore objects
131 * JobId | JobIds: List RestoreObjects for specific Job(s)
132 * It is possible to specify the ObjectType using FileType field.
134 void BDB::bdb_list_restore_objects(JCR *jcr, ROBJECT_DBR *rr, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
140 if (rr->JobIds && is_a_number_list(rr->JobIds)) {
143 } else if (rr->JobId) {
144 jobid = edit_int64(rr->JobId, ed1);
150 if (rr->FileType > 0) {
151 Mmsg(filter, "AND ObjectType = %d ", rr->FileType);
155 if (type == VERT_LIST) {
156 Mmsg(cmd, "SELECT JobId, RestoreObjectId, ObjectName, "
157 "PluginName, ObjectType "
158 "FROM RestoreObject JOIN Job USING (JobId) WHERE JobId IN (%s) %s "
159 "ORDER BY JobTDate ASC, RestoreObjectId",
160 jobid, filter.c_str());
162 Mmsg(cmd, "SELECT JobId, RestoreObjectId, ObjectName, "
163 "PluginName, ObjectType, ObjectLength "
164 "FROM RestoreObject JOIN Job USING (JobId) WHERE JobId IN (%s) %s "
165 "ORDER BY JobTDate ASC, RestoreObjectId",
166 jobid, filter.c_str());
169 if (!QueryDB(jcr, cmd)) {
174 list_result(jcr, this, sendit, ctx, type);
181 * If VolumeName is non-zero, list the record for that Volume
182 * otherwise, list the Volumes in the Pool specified by PoolId
184 void BDB::bdb_list_media_records(JCR *jcr, MEDIA_DBR *mdbr,
185 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
188 char esc[MAX_ESCAPE_NAME_LENGTH];
191 bdb_escape_string(jcr, esc, mdbr->VolumeName, strlen(mdbr->VolumeName));
193 if (type == VERT_LIST) {
194 if (mdbr->VolumeName[0] != 0) {
195 Mmsg(cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
196 "MediaType,MediaTypeId,FirstWritten,LastWritten,LabelDate,VolJobs,"
197 "VolFiles,VolBlocks,VolMounts,VolBytes,VolABytes,VolAPadding,"
198 "VolHoleBytes,VolHoles,VolErrors,VolWrites,"
199 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
200 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
201 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
202 "MediaAddressing,VolReadTime,VolWriteTime,"
203 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
204 "ActionOnPurge,Comment"
205 " FROM Media WHERE Media.VolumeName='%s'", esc);
207 Mmsg(cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
208 "MediaType,MediaTypeId,FirstWritten,LastWritten,LabelDate,VolJobs,"
209 "VolFiles,VolBlocks,VolMounts,VolBytes,VolABytes,VolAPadding,"
210 "VolHoleBytes,VolHoles,VolErrors,VolWrites,"
211 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
212 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
213 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
214 "MediaAddressing,VolReadTime,VolWriteTime,"
215 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
216 "ActionOnPurge,Comment"
217 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
218 edit_int64(mdbr->PoolId, ed1));
221 if (mdbr->VolumeName[0] != 0) {
222 Mmsg(cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
223 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,VolParts,LastWritten "
224 "FROM Media WHERE Media.VolumeName='%s'", esc);
226 Mmsg(cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
227 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,VolParts,LastWritten "
228 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
229 edit_int64(mdbr->PoolId, ed1));
233 if (!QueryDB(jcr, cmd)) {
238 list_result(jcr, this, sendit, ctx, type);
244 void BDB::bdb_list_jobmedia_records(JCR *jcr, uint32_t JobId,
245 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
250 if (type == VERT_LIST) {
251 if (JobId > 0) { /* do by JobId */
252 Mmsg(cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
253 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
255 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
256 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
258 Mmsg(cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
259 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
261 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
265 if (JobId > 0) { /* do by JobId */
266 Mmsg(cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
267 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
268 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
270 Mmsg(cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
271 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
274 if (!QueryDB(jcr, cmd)) {
279 list_result(jcr, this, sendit, ctx, type);
286 void BDB::bdb_list_copies_records(JCR *jcr, uint32_t limit, char *JobIds,
287 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
289 POOL_MEM str_limit(PM_MESSAGE);
290 POOL_MEM str_jobids(PM_MESSAGE);
293 Mmsg(str_limit, " LIMIT %d", limit);
296 if (JobIds && JobIds[0]) {
297 Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ",
303 "SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, "
304 "Job.JobId AS CopyJobId, Media.MediaType "
306 "JOIN JobMedia USING (JobId) "
307 "JOIN Media USING (MediaId) "
308 "WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s",
309 (char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str());
311 if (!QueryDB(jcr, cmd)) {
315 if (sql_num_rows()) {
316 if (JobIds && JobIds[0]) {
317 sendit(ctx, _("These JobIds have copies as follows:\n"));
319 sendit(ctx, _("The catalog contains copies as follows:\n"));
322 list_result(jcr, this, sendit, ctx, type);
331 void BDB::bdb_list_joblog_records(JCR *jcr, uint32_t JobId,
332 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
340 if (type == VERT_LIST) {
341 Mmsg(cmd, "SELECT Time,LogText FROM Log "
342 "WHERE Log.JobId=%s ORDER BY LogId ASC", edit_int64(JobId, ed1));
344 Mmsg(cmd, "SELECT LogText FROM Log "
345 "WHERE Log.JobId=%s ORDER BY LogId ASC", edit_int64(JobId, ed1));
347 if (!QueryDB(jcr, cmd)) {
351 list_result(jcr, this, sendit, ctx, type);
361 * List Job record(s) that match JOB_DBR
363 * Currently, we return all jobs or if jr->JobId is set,
364 * only the job with the specified id.
366 alist *BDB::bdb_list_job_records(JCR *jcr, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
367 void *ctx, e_list_type type)
372 char esc[MAX_ESCAPE_NAME_LENGTH];
377 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
383 if (jr->JobId == 0 && jr->Job[0] == 0) {
385 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
386 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
387 "StartTime,EndTime,RealEndTime,JobTDate,"
388 "VolSessionId,VolSessionTime,JobFiles,JobBytes,ReadBytes,JobErrors,"
389 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
390 "Job.FileSetId,FileSet.FileSet,Job.HasCache "
391 "FROM Job JOIN Client USING (ClientId) LEFT JOIN Pool USING (PoolId) "
392 "LEFT JOIN FileSet USING (FileSetId) ",
393 "ORDER BY StartTime%s", limit);
394 } else { /* single record */
396 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
397 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
398 "StartTime,EndTime,RealEndTime,JobTDate,"
399 "VolSessionId,VolSessionTime,JobFiles,JobBytes,ReadBytes,JobErrors,"
400 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
401 "Job.FileSetId,FileSet.FileSet,Job.HasCache "
402 "FROM Job JOIN Client USING (ClientId) LEFT JOIN Pool USING (PoolId) "
403 "LEFT JOIN FileSet USING (FileSetId) WHERE Job.JobId=%s ",
404 edit_int64(jr->JobId, ed1));
408 if (jr->Name[0] != 0) {
409 bdb_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
411 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
412 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", esc);
413 } else if (jr->Job[0] != 0) {
414 bdb_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
416 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
417 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", esc);
418 } else if (jr->JobId != 0) {
420 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
421 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
422 } else { /* all records */
424 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
425 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
428 case INCOMPLETE_JOBS:
429 if (jr->Name[0] != 0) {
430 bdb_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
431 if (jr->JobStatus == JS_FatalError) {
432 snprintf(status, sizeof(status), "JobStatus IN ('E','f') AND");
433 } else if (jr->JobStatus != 0) {
434 snprintf(status, sizeof(status), "JobStatus='%c' AND", jr->JobStatus);
439 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
440 "FROM Job WHERE %s Name='%s' ORDER BY StartTime,JobId ASC%s",
442 Dmsg1(100, "SQL: %s\n", cmd);
443 } else { /* all records */
444 if (jr->JobStatus != 0) {
445 snprintf(status, sizeof(status), "WHERE JobStatus='%c'", jr->JobStatus);
450 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
451 "FROM Job %s ORDER BY StartTime,JobId ASC%s", status, limit);
452 Dmsg1(100, "SQL: %s\n", cmd);
458 if (!QueryDB(jcr, cmd)) {
462 if (type == INCOMPLETE_JOBS) {
464 list = New(alist(10));
466 for (int i=0; (row=sql_fetch_row()) != NULL; i++) {
467 list->append(bstrdup(row[0]));
471 list_result(jcr, this, sendit, ctx, type);
481 void BDB::bdb_list_job_totals(JCR *jcr, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
486 Mmsg(cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
487 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
489 if (!QueryDB(jcr, cmd)) {
494 list_result(jcr, this, sendit, ctx, HORZ_LIST);
499 Mmsg(cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
500 "AS Files,sum(JobBytes) As Bytes FROM Job");
502 if (!QueryDB(jcr, cmd)) {
507 list_result(jcr, this, sendit, ctx, HORZ_LIST);
513 void BDB::bdb_list_files_for_job(JCR *jcr, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
516 LIST_CTX lctx(jcr, this, sendit, ctx, HORZ_LIST);
521 * Stupid MySQL is NON-STANDARD !
523 if (bdb_get_type_index() == SQL_TYPE_MYSQL) {
524 Mmsg(cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
525 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
527 "SELECT PathId, FilenameId "
528 "FROM BaseFiles JOIN File "
529 "ON (BaseFiles.FileId = File.FileId) "
530 "WHERE BaseFiles.JobId = %s"
531 ") AS F, Filename,Path "
532 "WHERE Filename.FilenameId=F.FilenameId "
533 "AND Path.PathId=F.PathId",
534 edit_int64(jobid, ed1), ed1);
536 Mmsg(cmd, "SELECT Path.Path||Filename.Name AS Filename "
537 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
539 "SELECT PathId, FilenameId "
540 "FROM BaseFiles JOIN File "
541 "ON (BaseFiles.FileId = File.FileId) "
542 "WHERE BaseFiles.JobId = %s"
543 ") AS F, Filename,Path "
544 "WHERE Filename.FilenameId=F.FilenameId "
545 "AND Path.PathId=F.PathId",
546 edit_int64(jobid, ed1), ed1);
549 if (!bdb_big_sql_query(cmd, list_result, &lctx)) {
560 void BDB::bdb_list_base_files_for_job(JCR *jcr, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
563 LIST_CTX lctx(jcr, this, sendit, ctx, HORZ_LIST);
568 * Stupid MySQL is NON-STANDARD !
570 if (bdb_get_type_index() == SQL_TYPE_MYSQL) {
571 Mmsg(cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
572 "FROM BaseFiles, File, Filename, Path "
573 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
574 "AND BaseFiles.FileId = File.FileId "
575 "AND Filename.FilenameId=File.FilenameId "
576 "AND Path.PathId=File.PathId",
577 edit_int64(jobid, ed1));
579 Mmsg(cmd, "SELECT Path.Path||Filename.Name AS Filename "
580 "FROM BaseFiles, File, Filename, Path "
581 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
582 "AND BaseFiles.FileId = File.FileId "
583 "AND Filename.FilenameId=File.FilenameId "
584 "AND Path.PathId=File.PathId",
585 edit_int64(jobid, ed1));
588 if (!bdb_big_sql_query(cmd, list_result, &lctx)) {
599 #define append_filter(buf, sql) \
602 pm_strcat(buf, " AND ");\
604 pm_strcpy(buf, " WHERE ");\
606 pm_strcat(buf, sql); \
609 void BDB::bdb_list_snapshot_records(JCR *jcr, SNAPSHOT_DBR *sdbr,
610 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
612 POOLMEM *filter = get_pool_memory(PM_MESSAGE);
613 POOLMEM *tmp = get_pool_memory(PM_MESSAGE);
614 POOLMEM *esc = get_pool_memory(PM_MESSAGE);
621 bdb_escape_string(jcr, esc, sdbr->Name, strlen(sdbr->Name));
622 Mmsg(tmp, "Name='%s'", esc);
623 append_filter(filter, tmp);
625 if (sdbr->SnapshotId > 0) {
626 Mmsg(tmp, "Snapshot.SnapshotId=%d", sdbr->SnapshotId);
627 append_filter(filter, tmp);
629 if (sdbr->ClientId > 0) {
630 Mmsg(tmp, "Snapshot.ClientId=%d", sdbr->ClientId);
631 append_filter(filter, tmp);
633 if (sdbr->JobId > 0) {
634 Mmsg(tmp, "Snapshot.JobId=%d", sdbr->JobId);
635 append_filter(filter, tmp);
638 bdb_escape_string(jcr, esc, sdbr->Client, strlen(sdbr->Client));
639 Mmsg(tmp, "Client.Name='%s'", esc);
640 append_filter(filter, tmp);
642 if (sdbr->Device && *(sdbr->Device)) {
643 esc = check_pool_memory_size(esc, strlen(sdbr->Device) * 2 + 1);
644 bdb_escape_string(jcr, esc, sdbr->Device, strlen(sdbr->Device));
645 Mmsg(tmp, "Device='%s'", esc);
646 append_filter(filter, tmp);
649 bdb_escape_string(jcr, esc, sdbr->Type, strlen(sdbr->Type));
650 Mmsg(tmp, "Type='%s'", esc);
651 append_filter(filter, tmp);
653 if (*sdbr->created_before) {
654 bdb_escape_string(jcr, esc, sdbr->created_before, strlen(sdbr->created_before));
655 Mmsg(tmp, "CreateDate <= '%s'", esc);
656 append_filter(filter, tmp);
658 if (*sdbr->created_after) {
659 bdb_escape_string(jcr, esc, sdbr->created_after, strlen(sdbr->created_after));
660 Mmsg(tmp, "CreateDate >= '%s'", esc);
661 append_filter(filter, tmp);
664 Mmsg(tmp, "CreateTDate < (%s - Retention)", edit_int64(time(NULL), ed1));
665 append_filter(filter, tmp);
667 if (*sdbr->CreateDate) {
668 bdb_escape_string(jcr, esc, sdbr->CreateDate, strlen(sdbr->CreateDate));
669 Mmsg(tmp, "CreateDate = '%s'", esc);
670 append_filter(filter, tmp);
673 if (sdbr->sorted_client) {
674 pm_strcat(filter, " ORDER BY Client.Name, SnapshotId DESC");
677 pm_strcat(filter, " ORDER BY SnapshotId DESC");
680 if (type == VERT_LIST || type == ARG_LIST) {
681 Mmsg(cmd, "SELECT SnapshotId, Snapshot.Name, CreateDate, Client.Name AS Client, "
682 "FileSet.FileSet AS FileSet, JobId, Volume, Device, Type, Retention, Comment "
683 "FROM Snapshot JOIN Client USING (ClientId) LEFT JOIN FileSet USING (FileSetId) %s", filter);
685 } else if (type == HORZ_LIST) {
686 Mmsg(cmd, "SELECT SnapshotId, Snapshot.Name, CreateDate, Client.Name AS Client, "
688 "FROM Snapshot JOIN Client USING (ClientId) %s", filter);
691 if (!QueryDB(jcr, cmd)) {
695 list_result(jcr, this, sendit, ctx, type);
701 free_pool_memory(filter);
702 free_pool_memory(esc);
703 free_pool_memory(tmp);
706 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */