2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2014 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from many
7 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 Bacula® is a registered trademark of Kern Sibbald.
17 * Bacula Catalog Database List records interface routines
19 * Written by Kern Sibbald, March 2000
25 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL
31 /* -----------------------------------------------------------------------
33 * Generic Routines (or almost generic)
35 * -----------------------------------------------------------------------
39 * Submit general SQL query
41 int db_list_sql_query(JCR *jcr, B_DB *mdb, const char *query, DB_LIST_HANDLER *sendit,
42 void *ctx, int verbose, e_list_type type)
45 if (!sql_query(mdb, query, QF_STORE_RESULT)) {
46 Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb));
48 sendit(ctx, mdb->errmsg);
54 list_result(jcr, mdb, sendit, ctx, type);
61 db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr,
62 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
64 char esc[MAX_ESCAPE_NAME_LENGTH];
67 mdb->db_escape_string(jcr, esc, pdbr->Name, strlen(pdbr->Name));
69 if (type == VERT_LIST) {
70 if (pdbr->Name[0] != 0) {
71 Mmsg(mdb->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(mdb->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(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
86 "FROM Pool WHERE Name='%s'", esc);
88 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
89 "FROM Pool ORDER BY PoolId");
93 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
98 list_result(jcr, mdb, sendit, ctx, type);
100 sql_free_result(mdb);
105 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
108 if (type == VERT_LIST) {
109 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
111 "FROM Client ORDER BY ClientId");
113 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
114 "FROM Client ORDER BY ClientId");
117 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
122 list_result(jcr, mdb, sendit, ctx, type);
124 sql_free_result(mdb);
130 * If VolumeName is non-zero, list the record for that Volume
131 * otherwise, list the Volumes in the Pool specified by PoolId
134 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
135 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
138 char esc[MAX_ESCAPE_NAME_LENGTH];
141 mdb->db_escape_string(jcr, esc, mdbr->VolumeName, strlen(mdbr->VolumeName));
143 if (type == VERT_LIST) {
144 if (mdbr->VolumeName[0] != 0) {
145 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
146 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
147 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
148 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
149 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
150 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
151 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
152 "ActionOnPurge,Comment"
153 " FROM Media WHERE Media.VolumeName='%s'", esc);
155 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
156 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
157 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
158 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
159 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
160 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
161 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
162 "ActionOnPurge,Comment"
163 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
164 edit_int64(mdbr->PoolId, ed1));
167 if (mdbr->VolumeName[0] != 0) {
168 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
169 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
170 "FROM Media WHERE Media.VolumeName='%s'", esc);
172 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
173 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
174 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
175 edit_int64(mdbr->PoolId, ed1));
179 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
184 list_result(jcr, mdb, sendit, ctx, type);
186 sql_free_result(mdb);
190 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
191 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
195 if (type == VERT_LIST) {
196 if (JobId > 0) { /* do by JobId */
197 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
198 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
200 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
201 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
203 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
204 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
206 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
210 if (JobId > 0) { /* do by JobId */
211 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
212 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
213 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
215 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
216 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
219 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
224 list_result(jcr, mdb, sendit, ctx, type);
226 sql_free_result(mdb);
231 void db_list_copies_records(JCR *jcr, B_DB *mdb, uint32_t limit, char *JobIds,
232 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
234 POOL_MEM str_limit(PM_MESSAGE);
235 POOL_MEM str_jobids(PM_MESSAGE);
238 Mmsg(str_limit, " LIMIT %d", limit);
241 if (JobIds && JobIds[0]) {
242 Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ",
248 "SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, "
249 "Job.JobId AS CopyJobId, Media.MediaType "
251 "JOIN JobMedia USING (JobId) "
252 "JOIN Media USING (MediaId) "
253 "WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s",
254 (char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str());
256 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
260 if (sql_num_rows(mdb)) {
261 if (JobIds && JobIds[0]) {
262 sendit(ctx, _("These JobIds have copies as follows:\n"));
264 sendit(ctx, _("The catalog contains copies as follows:\n"));
267 list_result(jcr, mdb, sendit, ctx, type);
270 sql_free_result(mdb);
276 void db_list_joblog_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
277 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
285 if (type == VERT_LIST) {
286 Mmsg(mdb->cmd, "SELECT Time,LogText FROM Log "
287 "WHERE Log.JobId=%s ORDER BY LogId ASC", edit_int64(JobId, ed1));
289 Mmsg(mdb->cmd, "SELECT LogText FROM Log "
290 "WHERE Log.JobId=%s ORDER BY LogId ASC", edit_int64(JobId, ed1));
292 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
296 list_result(jcr, mdb, sendit, ctx, type);
298 sql_free_result(mdb);
306 * List Job record(s) that match JOB_DBR
308 * Currently, we return all jobs or if jr->JobId is set,
309 * only the job with the specified id.
312 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
313 void *ctx, e_list_type type)
318 char esc[MAX_ESCAPE_NAME_LENGTH];
323 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
329 if (jr->JobId == 0 && jr->Job[0] == 0) {
331 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
332 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
333 "StartTime,EndTime,RealEndTime,JobTDate,"
334 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
335 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
336 "Job.FileSetId,FileSet.FileSet,Job.HasCache "
337 "FROM Job,Client,Pool,FileSet WHERE "
338 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
339 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
340 } else { /* single record */
342 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
343 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
344 "StartTime,EndTime,RealEndTime,JobTDate,"
345 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
346 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
347 "Job.FileSetId,FileSet.FileSet,Job.HasCache "
348 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
349 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
350 "AND FileSet.FileSetId=Job.FileSetId",
351 edit_int64(jr->JobId, ed1));
355 if (jr->Name[0] != 0) {
356 mdb->db_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
358 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
359 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", esc);
360 } else if (jr->Job[0] != 0) {
361 mdb->db_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
363 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
364 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", esc);
365 } else if (jr->JobId != 0) {
367 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
368 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
369 } else { /* all records */
371 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
372 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
378 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
382 sql_data_seek(mdb, 0);
383 list_result(jcr, mdb, sendit, ctx, type);
384 sql_free_result(mdb);
394 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
399 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
400 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
402 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
407 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
409 sql_free_result(mdb);
412 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
413 "AS Files,sum(JobBytes) As Bytes FROM Job");
415 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
420 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
422 sql_free_result(mdb);
427 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
430 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
435 * Stupid MySQL is NON-STANDARD !
437 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
438 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
439 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
441 "SELECT PathId, FilenameId "
442 "FROM BaseFiles JOIN File "
443 "ON (BaseFiles.FileId = File.FileId) "
444 "WHERE BaseFiles.JobId = %s"
445 ") AS F, Filename,Path "
446 "WHERE Filename.FilenameId=F.FilenameId "
447 "AND Path.PathId=F.PathId",
448 edit_int64(jobid, ed1), ed1);
450 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
451 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
453 "SELECT PathId, FilenameId "
454 "FROM BaseFiles JOIN File "
455 "ON (BaseFiles.FileId = File.FileId) "
456 "WHERE BaseFiles.JobId = %s"
457 ") AS F, Filename,Path "
458 "WHERE Filename.FilenameId=F.FilenameId "
459 "AND Path.PathId=F.PathId",
460 edit_int64(jobid, ed1), ed1);
463 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
470 sql_free_result(mdb);
475 db_list_base_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
478 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
483 * Stupid MySQL is NON-STANDARD !
485 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
486 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
487 "FROM BaseFiles, File, Filename, Path "
488 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
489 "AND BaseFiles.FileId = File.FileId "
490 "AND Filename.FilenameId=File.FilenameId "
491 "AND Path.PathId=File.PathId",
492 edit_int64(jobid, ed1));
494 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
495 "FROM BaseFiles, File, Filename, Path "
496 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
497 "AND BaseFiles.FileId = File.FileId "
498 "AND Filename.FilenameId=File.FilenameId "
499 "AND Path.PathId=File.PathId",
500 edit_int64(jobid, ed1));
503 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
510 sql_free_result(mdb);
514 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */