2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2008 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 two of the GNU 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 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
37 /* The following is necessary so that we do not include
38 * the dummy external definition of DB.
40 #define __SQL_C /* indicate that this is sql.c */
47 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI
49 /* -----------------------------------------------------------------------
51 * Generic Routines (or almost generic)
53 * -----------------------------------------------------------------------
57 * Submit general SQL query
59 int db_list_sql_query(JCR *jcr, B_DB *mdb, const char *query, DB_LIST_HANDLER *sendit,
60 void *ctx, int verbose, e_list_type type)
63 if (sql_query(mdb, query) != 0) {
64 Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb));
66 sendit(ctx, mdb->errmsg);
72 mdb->result = sql_store_result(mdb);
75 list_result(jcr, mdb, sendit, ctx, type);
83 db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr,
84 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
87 if (type == VERT_LIST) {
88 if (pdbr->Name[0] != 0) {
89 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
90 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
91 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
92 "RecyclePoolId,LabelType "
93 " FROM Pool WHERE Name='%s'", pdbr->Name);
95 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
96 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
97 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
98 "RecyclePoolId,LabelType "
99 " FROM Pool ORDER BY PoolId");
102 if (pdbr->Name[0] != 0) {
103 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
104 "FROM Pool WHERE Name='%s'", pdbr->Name);
106 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
107 "FROM Pool ORDER BY PoolId");
111 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
116 list_result(jcr, mdb, sendit, ctx, type);
118 sql_free_result(mdb);
123 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type 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 (!QUERY_DB(jcr, mdb, mdb->cmd)) {
140 list_result(jcr, mdb, sendit, ctx, type);
142 sql_free_result(mdb);
148 * If VolumeName is non-zero, list the record for that Volume
149 * otherwise, list the Volumes in the Pool specified by PoolId
152 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
153 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
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'", mdbr->VolumeName);
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'", mdbr->VolumeName);
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,"
213 "JobMedia.EndBlock,Copy "
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,"
219 "JobMedia.EndBlock,Copy "
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_joblog_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
246 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
253 if (type == VERT_LIST) {
254 Mmsg(mdb->cmd, "SELECT Time,LogText FROM Log "
255 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
257 Mmsg(mdb->cmd, "SELECT LogText FROM Log "
258 "WHERE Log.JobId=%s", edit_int64(JobId, ed1));
260 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
264 list_result(jcr, mdb, sendit, ctx, type);
266 sql_free_result(mdb);
274 * List Job record(s) that match JOB_DBR
276 * Currently, we return all jobs or if jr->JobId is set,
277 * only the job with the specified id.
280 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
281 void *ctx, e_list_type type)
287 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
291 if (type == VERT_LIST) {
292 if (jr->JobId == 0 && jr->Job[0] == 0) {
294 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
295 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
296 "StartTime,EndTime,RealEndTime,JobTDate,"
297 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
298 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
299 "Job.FileSetId,FileSet.FileSet "
300 "FROM Job,Client,Pool,FileSet WHERE "
301 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
302 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
303 } else { /* single record */
305 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
306 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
307 "StartTime,EndTime,RealEndTime,JobTDate,"
308 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
309 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
310 "Job.FileSetId,FileSet.FileSet "
311 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
312 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
313 "AND FileSet.FileSetId=Job.FileSetId",
314 edit_int64(jr->JobId, ed1));
317 if (jr->Name[0] != 0) {
319 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
320 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", jr->Name);
321 } else if (jr->Job[0] != 0) {
323 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
324 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", jr->Job);
325 } else if (jr->JobId != 0) {
327 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
328 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
329 } else { /* all records */
331 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
332 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
335 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
339 list_result(jcr, mdb, sendit, ctx, type);
341 sql_free_result(mdb);
350 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
355 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
356 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
358 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
363 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
365 sql_free_result(mdb);
368 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
369 "AS Files,sum(JobBytes) As Bytes FROM Job");
371 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
376 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
378 sql_free_result(mdb);
383 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
389 * Stupid MySQL is NON-STANDARD !
391 if (db_type == SQL_TYPE_MYSQL) {
392 Mmsg(mdb->cmd, "SELECT CONCAT(Path.Path,Filename.Name) AS Filename FROM File,"
393 "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId "
394 "AND Path.PathId=File.PathId",
395 edit_int64(jobid, ed1));
397 Mmsg(mdb->cmd, "SELECT Path.Path||Filename.Name AS Filename FROM File,"
398 "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId "
399 "AND Path.PathId=File.PathId",
400 edit_int64(jobid, ed1));
403 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
408 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
410 sql_free_result(mdb);
415 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/