2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2009 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version three of the GNU Affero General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU Affero General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
29 * Bacula Catalog Database List records interface routines
31 * Kern Sibbald, March 2000
33 * Version $Id: sql_list.c 8508 2009-03-07 20:59:46Z kerns $
39 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI
45 /* -----------------------------------------------------------------------
47 * Generic Routines (or almost generic)
49 * -----------------------------------------------------------------------
53 * Submit general SQL query
55 int db_list_sql_query(JCR *jcr, B_DB *mdb, const char *query, DB_LIST_HANDLER *sendit,
56 void *ctx, int verbose, e_list_type type)
59 if (!sql_query(mdb, query, QF_STORE_RESULT)) {
60 Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb));
62 sendit(ctx, mdb->errmsg);
68 list_result(jcr, mdb, sendit, ctx, type);
75 db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr,
76 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
79 if (type == VERT_LIST) {
80 if (pdbr->Name[0] != 0) {
81 Mmsg(mdb->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'", pdbr->Name);
87 Mmsg(mdb->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(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
96 "FROM Pool WHERE Name='%s'", pdbr->Name);
98 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
99 "FROM Pool ORDER BY PoolId");
103 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
108 list_result(jcr, mdb, sendit, ctx, type);
110 sql_free_result(mdb);
115 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
118 if (type == VERT_LIST) {
119 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
121 "FROM Client ORDER BY ClientId");
123 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
124 "FROM Client ORDER BY ClientId");
127 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
132 list_result(jcr, mdb, sendit, ctx, type);
134 sql_free_result(mdb);
140 * If VolumeName is non-zero, list the record for that Volume
141 * otherwise, list the Volumes in the Pool specified by PoolId
144 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
145 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
149 if (type == VERT_LIST) {
150 if (mdbr->VolumeName[0] != 0) {
151 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
152 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
153 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
154 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
155 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
156 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
157 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
159 " FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
161 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
162 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
163 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
164 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
165 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
166 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
167 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
169 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
170 edit_int64(mdbr->PoolId, ed1));
173 if (mdbr->VolumeName[0] != 0) {
174 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
175 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
176 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
178 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
179 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
180 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
181 edit_int64(mdbr->PoolId, ed1));
185 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
190 list_result(jcr, mdb, sendit, ctx, type);
192 sql_free_result(mdb);
196 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
197 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
201 if (type == VERT_LIST) {
202 if (JobId > 0) { /* do by JobId */
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 "
207 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
209 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
210 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
212 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
216 if (JobId > 0) { /* do by JobId */
217 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
218 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
219 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
221 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
222 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
225 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
230 list_result(jcr, mdb, sendit, ctx, type);
232 sql_free_result(mdb);
237 void db_list_copies_records(JCR *jcr, B_DB *mdb, uint32_t limit, char *JobIds,
238 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
240 POOL_MEM str_limit(PM_MESSAGE);
241 POOL_MEM str_jobids(PM_MESSAGE);
244 Mmsg(str_limit, " LIMIT %d", limit);
247 if (JobIds && JobIds[0]) {
248 Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ",
254 "SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, "
255 "Job.JobId AS CopyJobId, Media.MediaType "
257 "JOIN JobMedia USING (JobId) "
258 "JOIN Media USING (MediaId) "
259 "WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s",
260 (char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str());
262 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
266 if (sql_num_rows(mdb)) {
267 if (JobIds && JobIds[0]) {
268 sendit(ctx, _("These JobIds have copies as follows:\n"));
270 sendit(ctx, _("The catalog contains copies as follows:\n"));
273 list_result(jcr, mdb, sendit, ctx, type);
276 sql_free_result(mdb);
282 void db_list_joblog_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
283 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
291 if (type == VERT_LIST) {
292 Mmsg(mdb->cmd, "SELECT Time,LogText FROM Log "
293 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
295 Mmsg(mdb->cmd, "SELECT LogText FROM Log "
296 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
298 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
302 list_result(jcr, mdb, sendit, ctx, type);
304 sql_free_result(mdb);
312 * List Job record(s) that match JOB_DBR
314 * Currently, we return all jobs or if jr->JobId is set,
315 * only the job with the specified id.
318 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
319 void *ctx, e_list_type type)
325 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
329 if (type == VERT_LIST) {
330 if (jr->JobId == 0 && jr->Job[0] == 0) {
332 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
333 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
334 "StartTime,EndTime,RealEndTime,JobTDate,"
335 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
336 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
337 "Job.FileSetId,FileSet.FileSet "
338 "FROM Job,Client,Pool,FileSet WHERE "
339 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
340 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
341 } else { /* single record */
343 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
344 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
345 "StartTime,EndTime,RealEndTime,JobTDate,"
346 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
347 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
348 "Job.FileSetId,FileSet.FileSet "
349 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
350 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
351 "AND FileSet.FileSetId=Job.FileSetId",
352 edit_int64(jr->JobId, ed1));
355 if (jr->Name[0] != 0) {
357 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
358 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", jr->Name);
359 } else if (jr->Job[0] != 0) {
361 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
362 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", jr->Job);
363 } else if (jr->JobId != 0) {
365 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
366 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
367 } else { /* all records */
369 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
370 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
373 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
377 list_result(jcr, mdb, sendit, ctx, type);
379 sql_free_result(mdb);
388 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
393 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
394 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
396 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
401 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
403 sql_free_result(mdb);
406 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
407 "AS Files,sum(JobBytes) As Bytes FROM Job");
409 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
414 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
416 sql_free_result(mdb);
421 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
427 * Stupid MySQL is NON-STANDARD !
429 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
430 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
431 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
433 "SELECT PathId, FilenameId "
434 "FROM BaseFiles JOIN File "
435 "ON (BaseFiles.FileId = File.FileId) "
436 "WHERE BaseFiles.JobId = %s"
437 ") AS F, Filename,Path "
438 "WHERE Filename.FilenameId=F.FilenameId "
439 "AND Path.PathId=F.PathId",
440 edit_int64(jobid, ed1), ed1);
442 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
443 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
445 "SELECT PathId, FilenameId "
446 "FROM BaseFiles JOIN File "
447 "ON (BaseFiles.FileId = File.FileId) "
448 "WHERE BaseFiles.JobId = %s"
449 ") AS F, Filename,Path "
450 "WHERE Filename.FilenameId=F.FilenameId "
451 "AND Path.PathId=F.PathId",
452 edit_int64(jobid, ed1), ed1);
455 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
460 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
462 sql_free_result(mdb);
467 db_list_base_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
473 * Stupid MySQL is NON-STANDARD !
475 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
476 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
477 "FROM BaseFiles, File, Filename, Path "
478 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
479 "AND BaseFiles.FileId = File.FileId "
480 "AND Filename.FilenameId=File.FilenameId "
481 "AND Path.PathId=File.PathId",
482 edit_int64(jobid, ed1));
484 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
485 "FROM BaseFiles, File, Filename, Path "
486 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
487 "AND BaseFiles.FileId = File.FileId "
488 "AND Filename.FilenameId=File.FilenameId "
489 "AND Path.PathId=File.PathId",
490 edit_int64(jobid, ed1));
493 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
498 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
500 sql_free_result(mdb);
504 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */