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)
58 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
62 if (!db_big_sql_query(mdb, query, list_result, &lctx)) {
63 Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb));
65 sendit(ctx, mdb->errmsg);
79 db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr,
80 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
82 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
85 if (type == VERT_LIST) {
86 if (pdbr->Name[0] != 0) {
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 WHERE Name='%s'", pdbr->Name);
93 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
94 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
95 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
96 "RecyclePoolId,LabelType "
97 " FROM Pool ORDER BY PoolId");
100 if (pdbr->Name[0] != 0) {
101 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
102 "FROM Pool WHERE Name='%s'", pdbr->Name);
104 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
105 "FROM Pool ORDER BY PoolId");
109 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
116 sql_free_result(mdb);
121 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
123 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
126 if (type == VERT_LIST) {
127 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
129 "FROM Client ORDER BY ClientId");
131 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
132 "FROM Client ORDER BY ClientId");
135 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
141 sql_free_result(mdb);
147 * If VolumeName is non-zero, list the record for that Volume
148 * otherwise, list the Volumes in the Pool specified by PoolId
151 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
152 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
155 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
158 if (type == VERT_LIST) {
159 if (mdbr->VolumeName[0] != 0) {
160 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
161 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
162 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
163 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
164 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
165 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
166 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
168 " FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
170 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
171 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
172 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
173 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
174 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
175 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
176 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
178 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
179 edit_int64(mdbr->PoolId, ed1));
182 if (mdbr->VolumeName[0] != 0) {
183 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
184 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
185 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
187 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
188 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
189 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
190 edit_int64(mdbr->PoolId, ed1));
194 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
201 sql_free_result(mdb);
205 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
206 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
209 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
212 if (type == VERT_LIST) {
213 if (JobId > 0) { /* do by JobId */
214 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
215 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
217 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
218 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
220 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
221 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
223 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
227 if (JobId > 0) { /* do by JobId */
228 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
229 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
230 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
232 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
233 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
237 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
244 sql_free_result(mdb);
249 void db_list_copies_records(JCR *jcr, B_DB *mdb, uint32_t limit, char *JobIds,
250 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
252 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
253 POOL_MEM str_limit(PM_MESSAGE);
254 POOL_MEM str_jobids(PM_MESSAGE);
257 Mmsg(str_limit, " LIMIT %d", limit);
260 if (JobIds && JobIds[0]) {
261 Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ",
267 "SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, "
268 "Job.JobId AS CopyJobId, Media.MediaType "
270 "JOIN JobMedia USING (JobId) "
271 "JOIN Media USING (MediaId) "
272 "WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s",
273 (char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str());
275 if (JobIds && JobIds[0]) {
276 sendit(ctx, _("These JobIds have copies as follows:\n"));
278 sendit(ctx, _("The catalog contains copies as follows:\n"));
281 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
287 sql_free_result(mdb);
293 void db_list_joblog_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
294 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
297 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
303 if (type == VERT_LIST) {
304 Mmsg(mdb->cmd, "SELECT Time,LogText FROM Log "
305 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
307 Mmsg(mdb->cmd, "SELECT LogText FROM Log "
308 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
311 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
317 sql_free_result(mdb);
325 * List Job record(s) that match JOB_DBR
327 * Currently, we return all jobs or if jr->JobId is set,
328 * only the job with the specified id.
331 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
332 void *ctx, e_list_type type)
336 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
340 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
344 if (type == VERT_LIST) {
345 if (jr->JobId == 0 && jr->Job[0] == 0) {
347 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
348 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
349 "StartTime,EndTime,RealEndTime,JobTDate,"
350 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
351 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
352 "Job.FileSetId,FileSet.FileSet "
353 "FROM Job,Client,Pool,FileSet WHERE "
354 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
355 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
356 } else { /* single record */
358 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
359 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
360 "StartTime,EndTime,RealEndTime,JobTDate,"
361 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
362 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
363 "Job.FileSetId,FileSet.FileSet "
364 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
365 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
366 "AND FileSet.FileSetId=Job.FileSetId",
367 edit_int64(jr->JobId, ed1));
370 if (jr->Name[0] != 0) {
372 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
373 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", jr->Name);
374 } else if (jr->Job[0] != 0) {
376 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
377 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", jr->Job);
378 } else if (jr->JobId != 0) {
380 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
381 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
382 } else { /* all records */
384 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
385 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
389 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
395 sql_free_result(mdb);
404 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
406 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
411 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
412 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
414 if (!db_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
420 sql_free_result(mdb);
423 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
424 "AS Files,sum(JobBytes) As Bytes FROM Job");
427 if (!db_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
434 sql_free_result(mdb);
439 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
441 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
447 * Stupid MySQL is NON-STANDARD !
449 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
450 Mmsg(mdb->cmd, "SELECT CONCAT(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);
462 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
463 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
465 "SELECT PathId, FilenameId "
466 "FROM BaseFiles JOIN File "
467 "ON (BaseFiles.FileId = File.FileId) "
468 "WHERE BaseFiles.JobId = %s"
469 ") AS F, Filename,Path "
470 "WHERE Filename.FilenameId=F.FilenameId "
471 "AND Path.PathId=F.PathId",
472 edit_int64(jobid, ed1), ed1);
475 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
482 sql_free_result(mdb);
487 db_list_base_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
489 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
495 * Stupid MySQL is NON-STANDARD !
497 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
498 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
499 "FROM BaseFiles, File, Filename, Path "
500 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
501 "AND BaseFiles.FileId = File.FileId "
502 "AND Filename.FilenameId=File.FilenameId "
503 "AND Path.PathId=File.PathId",
504 edit_int64(jobid, ed1));
506 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
507 "FROM BaseFiles, File, Filename, Path "
508 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
509 "AND BaseFiles.FileId = File.FileId "
510 "AND Filename.FilenameId=File.FilenameId "
511 "AND Path.PathId=File.PathId",
512 edit_int64(jobid, ed1));
515 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
522 sql_free_result(mdb);
526 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */