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 char esc[MAX_ESCAPE_NAME_LENGTH];
83 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
86 mdb->db_escape_string(jcr, esc, pdbr->Name, strlen(pdbr->Name));
88 if (type == VERT_LIST) {
89 if (pdbr->Name[0] != 0) {
90 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
91 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
92 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
93 "RecyclePoolId,LabelType "
94 " FROM Pool WHERE Name='%s'", esc);
96 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
97 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
98 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
99 "RecyclePoolId,LabelType "
100 " FROM Pool ORDER BY PoolId");
103 if (pdbr->Name[0] != 0) {
104 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
105 "FROM Pool WHERE Name='%s'", esc);
107 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
108 "FROM Pool ORDER BY PoolId");
112 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
119 sql_free_result(mdb);
124 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
126 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
129 if (type == VERT_LIST) {
130 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
132 "FROM Client ORDER BY ClientId");
134 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
135 "FROM Client ORDER BY ClientId");
138 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
144 sql_free_result(mdb);
150 * If VolumeName is non-zero, list the record for that Volume
151 * otherwise, list the Volumes in the Pool specified by PoolId
154 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
155 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
158 char esc[MAX_ESCAPE_NAME_LENGTH];
159 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
162 mdb->db_escape_string(jcr, esc, mdbr->VolumeName, strlen(mdbr->VolumeName));
164 if (type == VERT_LIST) {
165 if (mdbr->VolumeName[0] != 0) {
166 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
167 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
168 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
169 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
170 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
171 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
172 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
174 " FROM Media WHERE Media.VolumeName='%s'", esc);
176 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
177 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
178 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
179 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
180 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
181 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
182 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
184 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
185 edit_int64(mdbr->PoolId, ed1));
188 if (mdbr->VolumeName[0] != 0) {
189 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
190 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
191 "FROM Media WHERE Media.VolumeName='%s'", esc);
193 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
194 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
195 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
196 edit_int64(mdbr->PoolId, ed1));
200 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
207 sql_free_result(mdb);
211 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
212 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
215 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
218 if (type == VERT_LIST) {
219 if (JobId > 0) { /* do by JobId */
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 "
224 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
226 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
227 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
229 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
233 if (JobId > 0) { /* do by JobId */
234 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
235 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
236 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
238 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
239 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
243 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
250 sql_free_result(mdb);
255 void db_list_copies_records(JCR *jcr, B_DB *mdb, uint32_t limit, char *JobIds,
256 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
258 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
259 POOL_MEM str_limit(PM_MESSAGE);
260 POOL_MEM str_jobids(PM_MESSAGE);
263 Mmsg(str_limit, " LIMIT %d", limit);
266 if (JobIds && JobIds[0]) {
267 Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ",
273 "SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, "
274 "Job.JobId AS CopyJobId, Media.MediaType "
276 "JOIN JobMedia USING (JobId) "
277 "JOIN Media USING (MediaId) "
278 "WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s",
279 (char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str());
281 if (JobIds && JobIds[0]) {
282 sendit(ctx, _("These JobIds have copies as follows:\n"));
284 sendit(ctx, _("The catalog contains copies as follows:\n"));
287 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
293 sql_free_result(mdb);
299 void db_list_joblog_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
300 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
303 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
309 if (type == VERT_LIST) {
310 Mmsg(mdb->cmd, "SELECT Time,LogText FROM Log "
311 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
313 Mmsg(mdb->cmd, "SELECT LogText FROM Log "
314 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
317 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
323 sql_free_result(mdb);
331 * List Job record(s) that match JOB_DBR
333 * Currently, we return all jobs or if jr->JobId is set,
334 * only the job with the specified id.
337 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
338 void *ctx, e_list_type type)
342 char esc[MAX_ESCAPE_NAME_LENGTH];
343 LIST_CTX lctx(jcr, mdb, sendit, ctx, type);
347 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
351 if (type == VERT_LIST) {
352 if (jr->JobId == 0 && jr->Job[0] == 0) {
354 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
355 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
356 "StartTime,EndTime,RealEndTime,JobTDate,"
357 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
358 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
359 "Job.FileSetId,FileSet.FileSet "
360 "FROM Job,Client,Pool,FileSet WHERE "
361 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
362 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
363 } else { /* single record */
365 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
366 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
367 "StartTime,EndTime,RealEndTime,JobTDate,"
368 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
369 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
370 "Job.FileSetId,FileSet.FileSet "
371 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
372 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
373 "AND FileSet.FileSetId=Job.FileSetId",
374 edit_int64(jr->JobId, ed1));
377 if (jr->Name[0] != 0) {
378 mdb->db_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
380 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
381 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", esc);
382 } else if (jr->Job[0] != 0) {
383 mdb->db_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
385 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
386 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", esc);
387 } else if (jr->JobId != 0) {
389 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
390 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
391 } else { /* all records */
393 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
394 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
398 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
404 sql_free_result(mdb);
413 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
415 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
420 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
421 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
423 if (!db_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
429 sql_free_result(mdb);
432 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
433 "AS Files,sum(JobBytes) As Bytes FROM Job");
436 if (!db_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
443 sql_free_result(mdb);
448 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
450 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
456 * Stupid MySQL is NON-STANDARD !
458 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
459 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
460 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
462 "SELECT PathId, FilenameId "
463 "FROM BaseFiles JOIN File "
464 "ON (BaseFiles.FileId = File.FileId) "
465 "WHERE BaseFiles.JobId = %s"
466 ") AS F, Filename,Path "
467 "WHERE Filename.FilenameId=F.FilenameId "
468 "AND Path.PathId=F.PathId",
469 edit_int64(jobid, ed1), ed1);
471 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
472 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
474 "SELECT PathId, FilenameId "
475 "FROM BaseFiles JOIN File "
476 "ON (BaseFiles.FileId = File.FileId) "
477 "WHERE BaseFiles.JobId = %s"
478 ") AS F, Filename,Path "
479 "WHERE Filename.FilenameId=F.FilenameId "
480 "AND Path.PathId=F.PathId",
481 edit_int64(jobid, ed1), ed1);
484 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
491 sql_free_result(mdb);
496 db_list_base_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
498 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
504 * Stupid MySQL is NON-STANDARD !
506 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
507 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
508 "FROM BaseFiles, File, Filename, Path "
509 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
510 "AND BaseFiles.FileId = File.FileId "
511 "AND Filename.FilenameId=File.FilenameId "
512 "AND Path.PathId=File.PathId",
513 edit_int64(jobid, ed1));
515 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
516 "FROM BaseFiles, File, Filename, Path "
517 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
518 "AND BaseFiles.FileId = File.FileId "
519 "AND Filename.FilenameId=File.FilenameId "
520 "AND Path.PathId=File.PathId",
521 edit_int64(jobid, ed1));
524 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
531 sql_free_result(mdb);
535 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */