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 $
38 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI
44 /* -----------------------------------------------------------------------
46 * Generic Routines (or almost generic)
48 * -----------------------------------------------------------------------
52 * Submit general SQL query
54 int db_list_sql_query(JCR *jcr, B_DB *mdb, const char *query, DB_LIST_HANDLER *sendit,
55 void *ctx, int verbose, e_list_type type)
58 if (!sql_query(mdb, query, QF_STORE_RESULT)) {
59 Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb));
61 sendit(ctx, mdb->errmsg);
67 list_result(jcr, mdb, sendit, ctx, type);
74 db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr,
75 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
77 char esc[MAX_ESCAPE_NAME_LENGTH];
80 mdb->db_escape_string(jcr, esc, pdbr->Name, strlen(pdbr->Name));
82 if (type == VERT_LIST) {
83 if (pdbr->Name[0] != 0) {
84 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
85 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
86 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
87 "RecyclePoolId,LabelType "
88 " FROM Pool WHERE Name='%s'", esc);
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 ORDER BY PoolId");
97 if (pdbr->Name[0] != 0) {
98 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
99 "FROM Pool WHERE Name='%s'", esc);
101 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
102 "FROM Pool ORDER BY PoolId");
106 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
111 list_result(jcr, mdb, sendit, ctx, type);
113 sql_free_result(mdb);
118 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
121 if (type == VERT_LIST) {
122 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
124 "FROM Client ORDER BY ClientId");
126 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
127 "FROM Client ORDER BY ClientId");
130 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
135 list_result(jcr, mdb, sendit, ctx, type);
137 sql_free_result(mdb);
143 * If VolumeName is non-zero, list the record for that Volume
144 * otherwise, list the Volumes in the Pool specified by PoolId
147 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
148 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
151 char esc[MAX_ESCAPE_NAME_LENGTH];
154 mdb->db_escape_string(jcr, esc, mdbr->VolumeName, strlen(mdbr->VolumeName));
156 if (type == VERT_LIST) {
157 if (mdbr->VolumeName[0] != 0) {
158 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
159 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
160 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
161 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
162 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
163 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
164 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
166 " FROM Media WHERE Media.VolumeName='%s'", esc);
168 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
169 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
170 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
171 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
172 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
173 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
174 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
176 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
177 edit_int64(mdbr->PoolId, ed1));
180 if (mdbr->VolumeName[0] != 0) {
181 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
182 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
183 "FROM Media WHERE Media.VolumeName='%s'", esc);
185 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
186 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
187 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
188 edit_int64(mdbr->PoolId, ed1));
192 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
197 list_result(jcr, mdb, sendit, ctx, type);
199 sql_free_result(mdb);
203 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
204 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
208 if (type == VERT_LIST) {
209 if (JobId > 0) { /* do by JobId */
210 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
211 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
213 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
214 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
216 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
217 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
219 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
223 if (JobId > 0) { /* do by JobId */
224 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
225 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
226 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
228 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
229 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
232 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
237 list_result(jcr, mdb, sendit, ctx, type);
239 sql_free_result(mdb);
244 void db_list_copies_records(JCR *jcr, B_DB *mdb, uint32_t limit, char *JobIds,
245 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
247 POOL_MEM str_limit(PM_MESSAGE);
248 POOL_MEM str_jobids(PM_MESSAGE);
251 Mmsg(str_limit, " LIMIT %d", limit);
254 if (JobIds && JobIds[0]) {
255 Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ",
261 "SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, "
262 "Job.JobId AS CopyJobId, Media.MediaType "
264 "JOIN JobMedia USING (JobId) "
265 "JOIN Media USING (MediaId) "
266 "WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s",
267 (char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str());
269 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
273 if (sql_num_rows(mdb)) {
274 if (JobIds && JobIds[0]) {
275 sendit(ctx, _("These JobIds have copies as follows:\n"));
277 sendit(ctx, _("The catalog contains copies as follows:\n"));
280 list_result(jcr, mdb, sendit, ctx, type);
283 sql_free_result(mdb);
289 void db_list_joblog_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
290 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
298 if (type == VERT_LIST) {
299 Mmsg(mdb->cmd, "SELECT Time,LogText FROM Log "
300 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
302 Mmsg(mdb->cmd, "SELECT LogText FROM Log "
303 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
305 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
309 list_result(jcr, mdb, sendit, ctx, type);
311 sql_free_result(mdb);
319 * List Job record(s) that match JOB_DBR
321 * Currently, we return all jobs or if jr->JobId is set,
322 * only the job with the specified id.
325 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
326 void *ctx, e_list_type type)
330 char esc[MAX_ESCAPE_NAME_LENGTH];
334 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
338 if (type == VERT_LIST) {
339 if (jr->JobId == 0 && jr->Job[0] == 0) {
341 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
342 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
343 "StartTime,EndTime,RealEndTime,JobTDate,"
344 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
345 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
346 "Job.FileSetId,FileSet.FileSet "
347 "FROM Job,Client,Pool,FileSet WHERE "
348 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
349 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
350 } else { /* single record */
352 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
353 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
354 "StartTime,EndTime,RealEndTime,JobTDate,"
355 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
356 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
357 "Job.FileSetId,FileSet.FileSet "
358 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
359 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
360 "AND FileSet.FileSetId=Job.FileSetId",
361 edit_int64(jr->JobId, ed1));
364 if (jr->Name[0] != 0) {
365 mdb->db_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
367 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
368 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", esc);
369 } else if (jr->Job[0] != 0) {
370 mdb->db_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
372 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
373 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", esc);
374 } else if (jr->JobId != 0) {
376 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
377 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
378 } else { /* all records */
380 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
381 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
384 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
388 list_result(jcr, mdb, sendit, ctx, type);
390 sql_free_result(mdb);
399 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
404 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
405 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
407 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
412 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
414 sql_free_result(mdb);
417 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
418 "AS Files,sum(JobBytes) As Bytes FROM Job");
420 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
425 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
427 sql_free_result(mdb);
432 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
435 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
440 * Stupid MySQL is NON-STANDARD !
442 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
443 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
444 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
446 "SELECT PathId, FilenameId "
447 "FROM BaseFiles JOIN File "
448 "ON (BaseFiles.FileId = File.FileId) "
449 "WHERE BaseFiles.JobId = %s"
450 ") AS F, Filename,Path "
451 "WHERE Filename.FilenameId=F.FilenameId "
452 "AND Path.PathId=F.PathId",
453 edit_int64(jobid, ed1), ed1);
455 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
456 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
458 "SELECT PathId, FilenameId "
459 "FROM BaseFiles JOIN File "
460 "ON (BaseFiles.FileId = File.FileId) "
461 "WHERE BaseFiles.JobId = %s"
462 ") AS F, Filename,Path "
463 "WHERE Filename.FilenameId=F.FilenameId "
464 "AND Path.PathId=F.PathId",
465 edit_int64(jobid, ed1), ed1);
468 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
475 sql_free_result(mdb);
480 db_list_base_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
483 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
488 * Stupid MySQL is NON-STANDARD !
490 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
491 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
492 "FROM BaseFiles, File, Filename, Path "
493 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
494 "AND BaseFiles.FileId = File.FileId "
495 "AND Filename.FilenameId=File.FilenameId "
496 "AND Path.PathId=File.PathId",
497 edit_int64(jobid, ed1));
499 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
500 "FROM BaseFiles, File, Filename, Path "
501 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
502 "AND BaseFiles.FileId = File.FileId "
503 "AND Filename.FilenameId=File.FilenameId "
504 "AND Path.PathId=File.PathId",
505 edit_int64(jobid, ed1));
508 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
515 sql_free_result(mdb);
519 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */