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)
78 char esc[MAX_ESCAPE_NAME_LENGTH];
81 mdb->db_escape_string(jcr, esc, pdbr->Name, strlen(pdbr->Name));
83 if (type == VERT_LIST) {
84 if (pdbr->Name[0] != 0) {
85 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
86 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
87 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
88 "RecyclePoolId,LabelType "
89 " FROM Pool WHERE Name='%s'", esc);
91 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
92 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
93 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
94 "RecyclePoolId,LabelType "
95 " FROM Pool ORDER BY PoolId");
98 if (pdbr->Name[0] != 0) {
99 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
100 "FROM Pool WHERE Name='%s'", esc);
102 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
103 "FROM Pool ORDER BY PoolId");
107 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
112 list_result(jcr, mdb, sendit, ctx, type);
114 sql_free_result(mdb);
119 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
122 if (type == VERT_LIST) {
123 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
125 "FROM Client ORDER BY ClientId");
127 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
128 "FROM Client ORDER BY ClientId");
131 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
136 list_result(jcr, mdb, sendit, ctx, type);
138 sql_free_result(mdb);
144 * If VolumeName is non-zero, list the record for that Volume
145 * otherwise, list the Volumes in the Pool specified by PoolId
148 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
149 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
152 char esc[MAX_ESCAPE_NAME_LENGTH];
155 mdb->db_escape_string(jcr, esc, mdbr->VolumeName, strlen(mdbr->VolumeName));
157 if (type == VERT_LIST) {
158 if (mdbr->VolumeName[0] != 0) {
159 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
160 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
161 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
162 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
163 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
164 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
165 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
167 " FROM Media WHERE Media.VolumeName='%s'", esc);
169 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
170 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
171 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
172 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
173 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
174 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
175 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
177 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
178 edit_int64(mdbr->PoolId, ed1));
181 if (mdbr->VolumeName[0] != 0) {
182 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
183 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
184 "FROM Media WHERE Media.VolumeName='%s'", esc);
186 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
187 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
188 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
189 edit_int64(mdbr->PoolId, ed1));
193 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
198 list_result(jcr, mdb, sendit, ctx, type);
200 sql_free_result(mdb);
204 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
205 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
209 if (type == VERT_LIST) {
210 if (JobId > 0) { /* do by JobId */
211 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
212 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
214 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
215 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
217 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
218 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
220 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
224 if (JobId > 0) { /* do by JobId */
225 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
226 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
227 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
229 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
230 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
233 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
238 list_result(jcr, mdb, sendit, ctx, type);
240 sql_free_result(mdb);
245 void db_list_copies_records(JCR *jcr, B_DB *mdb, uint32_t limit, char *JobIds,
246 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
248 POOL_MEM str_limit(PM_MESSAGE);
249 POOL_MEM str_jobids(PM_MESSAGE);
252 Mmsg(str_limit, " LIMIT %d", limit);
255 if (JobIds && JobIds[0]) {
256 Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ",
262 "SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, "
263 "Job.JobId AS CopyJobId, Media.MediaType "
265 "JOIN JobMedia USING (JobId) "
266 "JOIN Media USING (MediaId) "
267 "WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s",
268 (char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str());
270 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
274 if (sql_num_rows(mdb)) {
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 list_result(jcr, mdb, sendit, ctx, type);
284 sql_free_result(mdb);
290 void db_list_joblog_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
291 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
299 if (type == VERT_LIST) {
300 Mmsg(mdb->cmd, "SELECT Time,LogText FROM Log "
301 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
303 Mmsg(mdb->cmd, "SELECT LogText FROM Log "
304 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
306 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
310 list_result(jcr, mdb, sendit, ctx, type);
312 sql_free_result(mdb);
320 * List Job record(s) that match JOB_DBR
322 * Currently, we return all jobs or if jr->JobId is set,
323 * only the job with the specified id.
326 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
327 void *ctx, e_list_type type)
331 char esc[MAX_ESCAPE_NAME_LENGTH];
335 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
339 if (type == VERT_LIST) {
340 if (jr->JobId == 0 && jr->Job[0] == 0) {
342 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
343 "Job.ClientId,Client.Name as ClientName,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 "
348 "FROM Job,Client,Pool,FileSet WHERE "
349 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
350 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
351 } else { /* single record */
353 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
354 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
355 "StartTime,EndTime,RealEndTime,JobTDate,"
356 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
357 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
358 "Job.FileSetId,FileSet.FileSet "
359 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
360 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
361 "AND FileSet.FileSetId=Job.FileSetId",
362 edit_int64(jr->JobId, ed1));
365 if (jr->Name[0] != 0) {
366 mdb->db_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
368 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
369 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", esc);
370 } else if (jr->Job[0] != 0) {
371 mdb->db_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
373 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
374 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", esc);
375 } else if (jr->JobId != 0) {
377 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
378 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
379 } else { /* all records */
381 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
382 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
385 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
389 list_result(jcr, mdb, sendit, ctx, type);
391 sql_free_result(mdb);
400 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
405 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
406 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
408 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
413 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
415 sql_free_result(mdb);
418 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
419 "AS Files,sum(JobBytes) As Bytes FROM Job");
421 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
426 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
428 sql_free_result(mdb);
433 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
436 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
441 * Stupid MySQL is NON-STANDARD !
443 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
444 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
445 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
447 "SELECT PathId, FilenameId "
448 "FROM BaseFiles JOIN File "
449 "ON (BaseFiles.FileId = File.FileId) "
450 "WHERE BaseFiles.JobId = %s"
451 ") AS F, Filename,Path "
452 "WHERE Filename.FilenameId=F.FilenameId "
453 "AND Path.PathId=F.PathId",
454 edit_int64(jobid, ed1), ed1);
456 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
457 "FROM (SELECT PathId, FilenameId FROM File WHERE JobId=%s "
459 "SELECT PathId, FilenameId "
460 "FROM BaseFiles JOIN File "
461 "ON (BaseFiles.FileId = File.FileId) "
462 "WHERE BaseFiles.JobId = %s"
463 ") AS F, Filename,Path "
464 "WHERE Filename.FilenameId=F.FilenameId "
465 "AND Path.PathId=F.PathId",
466 edit_int64(jobid, ed1), ed1);
469 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
476 sql_free_result(mdb);
481 db_list_base_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
484 LIST_CTX lctx(jcr, mdb, sendit, ctx, HORZ_LIST);
489 * Stupid MySQL is NON-STANDARD !
491 if (db_get_type_index(mdb) == SQL_TYPE_MYSQL) {
492 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename "
493 "FROM BaseFiles, File, Filename, Path "
494 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
495 "AND BaseFiles.FileId = File.FileId "
496 "AND Filename.FilenameId=File.FilenameId "
497 "AND Path.PathId=File.PathId",
498 edit_int64(jobid, ed1));
500 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename "
501 "FROM BaseFiles, File, Filename, Path "
502 "WHERE BaseFiles.JobId=%s AND BaseFiles.BaseJobId = File.JobId "
503 "AND BaseFiles.FileId = File.FileId "
504 "AND Filename.FilenameId=File.FilenameId "
505 "AND Path.PathId=File.PathId",
506 edit_int64(jobid, ed1));
509 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
514 if (!db_big_sql_query(mdb, mdb->cmd, list_result, &lctx)) {
521 sql_free_result(mdb);
525 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */