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 * -----------------------------------------------------------------------
40 * Submit general SQL query
42 int BDB::bdb_list_sql_query(JCR *jcr, const char *query, DB_LIST_HANDLER *sendit,
43 void *ctx, int verbose, e_list_type type)
46 if (!sql_query(query, QF_STORE_RESULT)) {
47 Mmsg(errmsg, _("Query failed: %s\n"), sql_strerror());
55 list_result(jcr,this, sendit, ctx, type);
61 void BDB::bdb_list_pool_records(JCR *jcr, POOL_DBR *pdbr,
62 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
64 char esc[MAX_ESCAPE_NAME_LENGTH];
67 bdb_escape_string(jcr, esc, pdbr->Name, strlen(pdbr->Name));
69 if (type == VERT_LIST) {
70 if (pdbr->Name[0] != 0) {
71 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
72 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
73 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
74 "RecyclePoolId,LabelType "
75 " FROM Pool WHERE Name='%s'", esc);
77 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
78 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
79 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
80 "RecyclePoolId,LabelType "
81 " FROM Pool ORDER BY PoolId");
84 if (pdbr->Name[0] != 0) {
85 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
86 "FROM Pool WHERE Name='%s'", esc);
88 Mmsg(cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
89 "FROM Pool ORDER BY PoolId");
93 if (!QueryDB(jcr, cmd)) {
98 list_result(jcr, this, sendit, ctx, type);
104 void BDB::bdb_list_client_records(JCR *jcr, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
107 if (type == VERT_LIST) {
108 Mmsg(cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
110 "FROM Client ORDER BY ClientId");
112 Mmsg(cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
113 "FROM Client ORDER BY ClientId");
116 if (!QueryDB(jcr, cmd)) {
121 list_result(jcr, this, sendit, ctx, type);
128 * List restore objects
130 * JobId | JobIds: List RestoreObjects for specific Job(s)
131 * It is possible to specify the ObjectType using FileType field.
133 void BDB::bdb_list_restore_objects(JCR *jcr, ROBJECT_DBR *rr, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
139 if (rr->JobIds && is_a_number_list(rr->JobIds)) {
142 } else if (rr->JobId) {
143 jobid = edit_int64(rr->JobId, ed1);
149 if (rr->FileType > 0) {
150 Mmsg(filter, "AND ObjectType = %d ", rr->FileType);
154 if (type == VERT_LIST) {
155 Mmsg(cmd, "SELECT JobId, RestoreObjectId, ObjectName, "
156 "PluginName, ObjectType "
157 "FROM RestoreObject JOIN Job USING (JobId) WHERE JobId IN (%s) %s "
158 "ORDER BY JobTDate ASC, RestoreObjectId",
159 jobid, filter.c_str());
161 Mmsg(cmd, "SELECT JobId, RestoreObjectId, ObjectName, "
162 "PluginName, ObjectType, ObjectLength "
163 "FROM RestoreObject JOIN Job USING (JobId) WHERE JobId IN (%s) %s "
164 "ORDER BY JobTDate ASC, RestoreObjectId",
165 jobid, filter.c_str());
168 if (!QueryDB(jcr, cmd)) {
173 list_result(jcr, this, sendit, ctx, type);
180 * If VolumeName is non-zero, list the record for that Volume
181 * otherwise, list the Volumes in the Pool specified by PoolId
183 void BDB::bdb_list_media_records(JCR *jcr, MEDIA_DBR *mdbr,
184 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
187 char esc[MAX_ESCAPE_NAME_LENGTH];
190 bdb_escape_string(jcr, esc, mdbr->VolumeName, strlen(mdbr->VolumeName));
192 if (type == VERT_LIST) {
193 if (mdbr->VolumeName[0] != 0) {
194 Mmsg(cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
195 "MediaType,MediaTypeId,FirstWritten,LastWritten,LabelDate,VolJobs,"
196 "VolFiles,VolBlocks,VolMounts,VolBytes,VolABytes,VolAPadding,"
197 "VolHoleBytes,VolHoles,VolErrors,VolWrites,"
198 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
199 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
200 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
201 "MediaAddressing,VolReadTime,VolWriteTime,"
202 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
203 "ActionOnPurge,Comment"
204 " FROM Media WHERE Media.VolumeName='%s'", esc);
206 Mmsg(cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
207 "MediaType,MediaTypeId,FirstWritten,LastWritten,LabelDate,VolJobs,"
208 "VolFiles,VolBlocks,VolMounts,VolBytes,VolABytes,VolAPadding,"
209 "VolHoleBytes,VolHoles,VolErrors,VolWrites,"
210 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
211 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
212 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
213 "MediaAddressing,VolReadTime,VolWriteTime,"
214 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
215 "ActionOnPurge,Comment"
216 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
217 edit_int64(mdbr->PoolId, ed1));
220 if (mdbr->VolumeName[0] != 0) {
221 Mmsg(cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
222 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,VolParts,LastWritten "
223 "FROM Media WHERE Media.VolumeName='%s'", esc);
225 Mmsg(cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
226 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,VolParts,LastWritten "
227 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
228 edit_int64(mdbr->PoolId, ed1));
232 if (!QueryDB(jcr, cmd)) {
237 list_result(jcr, this, sendit, ctx, type);
243 void BDB::bdb_list_jobmedia_records(JCR *jcr, uint32_t JobId,
244 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
249 if (type == VERT_LIST) {
250 if (JobId > 0) { /* do by JobId */
251 Mmsg(cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
252 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
254 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
255 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
257 Mmsg(cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
258 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
260 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
264 if (JobId > 0) { /* do by JobId */
265 Mmsg(cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
266 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
267 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
269 Mmsg(cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
270 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
273 if (!QueryDB(jcr, cmd)) {
278 list_result(jcr, this, sendit, ctx, type);
285 void BDB::bdb_list_copies_records(JCR *jcr, uint32_t limit, char *JobIds,
286 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
288 POOL_MEM str_limit(PM_MESSAGE);
289 POOL_MEM str_jobids(PM_MESSAGE);
292 Mmsg(str_limit, " LIMIT %d", limit);
295 if (JobIds && JobIds[0]) {
296 Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ",
302 "SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, "
303 "Job.JobId AS CopyJobId, Media.MediaType "
305 "JOIN JobMedia USING (JobId) "
306 "JOIN Media USING (MediaId) "
307 "WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s",
308 (char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str());
310 if (!QueryDB(jcr, cmd)) {
314 if (sql_num_rows()) {
315 if (JobIds && JobIds[0]) {
316 sendit(ctx, _("These JobIds have copies as follows:\n"));
318 sendit(ctx, _("The catalog contains copies as follows:\n"));
321 list_result(jcr, this, sendit, ctx, type);
330 void BDB::bdb_list_joblog_records(JCR *jcr, uint32_t JobId,
331 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
339 if (type == VERT_LIST) {
340 Mmsg(cmd, "SELECT Time,LogText FROM Log "
341 "WHERE Log.JobId=%s ORDER BY LogId ASC", edit_int64(JobId, ed1));
343 Mmsg(cmd, "SELECT LogText FROM Log "
344 "WHERE Log.JobId=%s ORDER BY LogId ASC", edit_int64(JobId, ed1));
346 if (!QueryDB(jcr, cmd)) {
350 list_result(jcr, this, sendit, ctx, type);
360 * List Job record(s) that match JOB_DBR
362 * Currently, we return all jobs or if jr->JobId is set,
363 * only the job with the specified id.
365 alist *BDB::bdb_list_job_records(JCR *jcr, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
366 void *ctx, e_list_type type)
371 char esc[MAX_ESCAPE_NAME_LENGTH];
376 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
382 if (jr->JobId == 0 && jr->Job[0] == 0) {
384 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
385 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
386 "StartTime,EndTime,RealEndTime,JobTDate,"
387 "VolSessionId,VolSessionTime,JobFiles,JobBytes,ReadBytes,JobErrors,"
388 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
389 "Job.FileSetId,FileSet.FileSet,Job.HasBase,Job.HasCache,Job.Comment "
390 "FROM Job JOIN Client USING (ClientId) LEFT JOIN Pool USING (PoolId) "
391 "LEFT JOIN FileSet USING (FileSetId) ",
392 "ORDER BY StartTime%s", limit);
393 } else { /* single record */
395 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
396 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
397 "StartTime,EndTime,RealEndTime,JobTDate,"
398 "VolSessionId,VolSessionTime,JobFiles,JobBytes,ReadBytes,JobErrors,"
399 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
400 "Job.FileSetId,FileSet.FileSet,Job.HasBase,Job.HasCache,Job.Comment "
401 "FROM Job JOIN Client USING (ClientId) LEFT JOIN Pool USING (PoolId) "
402 "LEFT JOIN FileSet USING (FileSetId) WHERE Job.JobId=%s ",
403 edit_int64(jr->JobId, ed1));
407 if (jr->Name[0] != 0) {
408 bdb_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
410 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
411 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", esc);
412 } else if (jr->Job[0] != 0) {
413 bdb_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
415 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
416 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", esc);
417 } else if (jr->JobId != 0) {
419 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
420 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
421 } else { /* all records */
423 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
424 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
427 case INCOMPLETE_JOBS:
428 if (jr->Name[0] != 0) {
429 bdb_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
430 if (jr->JobStatus == JS_FatalError) {
431 snprintf(status, sizeof(status), "JobStatus IN ('E','f') AND");
432 } else if (jr->JobStatus != 0) {
433 snprintf(status, sizeof(status), "JobStatus='%c' AND", jr->JobStatus);
438 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
439 "FROM Job WHERE %s Name='%s' ORDER BY StartTime,JobId ASC%s",
441 Dmsg1(100, "SQL: %s\n", cmd);
442 } else { /* all records */
443 if (jr->JobStatus != 0) {
444 snprintf(status, sizeof(status), "WHERE JobStatus='%c'", jr->JobStatus);
449 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
450 "FROM Job %s ORDER BY StartTime,JobId ASC%s", status, limit);
451 Dmsg1(100, "SQL: %s\n", cmd);
457 if (!QueryDB(jcr, cmd)) {
461 if (type == INCOMPLETE_JOBS) {
463 list = New(alist(10));
465 for (int i=0; (row=sql_fetch_row()) != NULL; i++) {
466 list->append(bstrdup(row[0]));
470 list_result(jcr, this, sendit, ctx, type);
480 void BDB::bdb_list_job_totals(JCR *jcr, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
485 Mmsg(cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
486 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
488 if (!QueryDB(jcr, cmd)) {
493 list_result(jcr, this, sendit, ctx, HORZ_LIST);
498 Mmsg(cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
499 "AS Files,sum(JobBytes) As Bytes FROM Job");
501 if (!QueryDB(jcr, cmd)) {
506 list_result(jcr, this, sendit, ctx, HORZ_LIST);
512 void BDB::bdb_list_files_for_job(JCR *jcr, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
515 LIST_CTX lctx(jcr, this, sendit, ctx, HORZ_LIST);
520 * Stupid MySQL is NON-STANDARD !
522 if (bdb_get_type_index() == SQL_TYPE_MYSQL) {
523 Mmsg(cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
524 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
526 "SELECT PathId, FilenameId "
527 "FROM BaseFiles JOIN File "
528 "ON (BaseFiles.FileId = File.FileId) "
529 "WHERE BaseFiles.JobId = %s"
530 ") AS F, Filename,Path "
531 "WHERE Filename.FilenameId=F.FilenameId "
532 "AND Path.PathId=F.PathId",
533 edit_int64(jobid, ed1), ed1);
535 Mmsg(cmd, "SELECT 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);
548 if (!bdb_big_sql_query(cmd, list_result, &lctx)) {
559 void BDB::bdb_list_base_files_for_job(JCR *jcr, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
562 LIST_CTX lctx(jcr, this, sendit, ctx, HORZ_LIST);
567 * Stupid MySQL is NON-STANDARD !
569 if (bdb_get_type_index() == SQL_TYPE_MYSQL) {
570 Mmsg(cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
571 "FROM BaseFiles, File, Filename, Path "
572 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
573 "AND BaseFiles.FileId = File.FileId "
574 "AND Filename.FilenameId=File.FilenameId "
575 "AND Path.PathId=File.PathId",
576 edit_int64(jobid, ed1));
578 Mmsg(cmd, "SELECT Path.Path||Filename.Name AS Filename "
579 "FROM BaseFiles, File, Filename, Path "
580 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
581 "AND BaseFiles.FileId = File.FileId "
582 "AND Filename.FilenameId=File.FilenameId "
583 "AND Path.PathId=File.PathId",
584 edit_int64(jobid, ed1));
587 if (!bdb_big_sql_query(cmd, list_result, &lctx)) {
598 #define append_filter(buf, sql) \
601 pm_strcat(buf, " AND ");\
603 pm_strcpy(buf, " WHERE ");\
605 pm_strcat(buf, sql); \
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 */