2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2007 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 John Walker.
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 */
45 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
47 /* -----------------------------------------------------------------------
49 * Generic Routines (or almost generic)
51 * -----------------------------------------------------------------------
55 * Submit general SQL query
57 int db_list_sql_query(JCR *jcr, B_DB *mdb, const char *query, DB_LIST_HANDLER *sendit,
58 void *ctx, int verbose, e_list_type type)
61 if (sql_query(mdb, query) != 0) {
62 Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb));
64 sendit(ctx, mdb->errmsg);
70 mdb->result = sql_store_result(mdb);
73 list_result(jcr, mdb, sendit, ctx, type);
81 db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr,
82 DB_LIST_HANDLER *sendit, void *ctx, e_list_type 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 (!QUERY_DB(jcr, mdb, mdb->cmd)) {
114 list_result(jcr, mdb, sendit, ctx, type);
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)
124 if (type == VERT_LIST) {
125 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
127 "FROM Client ORDER BY ClientId");
129 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
130 "FROM Client ORDER BY ClientId");
133 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
138 list_result(jcr, mdb, sendit, ctx, type);
140 sql_free_result(mdb);
146 * If VolumeName is non-zero, list the record for that Volume
147 * otherwise, list the Volumes in the Pool specified by PoolId
150 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
151 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
155 if (type == VERT_LIST) {
156 if (mdbr->VolumeName[0] != 0) {
157 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
158 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
159 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
160 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
161 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
162 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
163 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
165 " FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
167 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
168 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
169 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
170 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
171 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
172 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
173 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
175 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
176 edit_int64(mdbr->PoolId, ed1));
179 if (mdbr->VolumeName[0] != 0) {
180 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
181 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
182 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
184 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
185 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
186 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
187 edit_int64(mdbr->PoolId, ed1));
191 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
196 list_result(jcr, mdb, sendit, ctx, type);
198 sql_free_result(mdb);
202 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
203 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
207 if (type == VERT_LIST) {
208 if (JobId > 0) { /* do by JobId */
209 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
210 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
211 "JobMedia.EndBlock,Copy "
212 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
213 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
215 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
216 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
217 "JobMedia.EndBlock,Copy "
218 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
222 if (JobId > 0) { /* do by JobId */
223 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
224 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
225 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
227 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
228 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
231 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
236 list_result(jcr, mdb, sendit, ctx, type);
238 sql_free_result(mdb);
245 * List Job record(s) that match JOB_DBR
247 * Currently, we return all jobs or if jr->JobId is set,
248 * only the job with the specified id.
251 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
252 void *ctx, e_list_type type)
258 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
262 if (type == VERT_LIST) {
263 if (jr->JobId == 0 && jr->Job[0] == 0) {
265 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
266 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
267 "StartTime,EndTime,RealEndTime,JobTDate,"
268 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
269 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
270 "Job.FileSetId,FileSet.FileSet "
271 "FROM Job,Client,Pool,FileSet WHERE "
272 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
273 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
274 } else { /* single record */
276 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
277 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
278 "StartTime,EndTime,RealEndTime,JobTDate,"
279 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
280 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
281 "Job.FileSetId,FileSet.FileSet "
282 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
283 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
284 "AND FileSet.FileSetId=Job.FileSetId",
285 edit_int64(jr->JobId, ed1));
288 if (jr->Name[0] != 0) {
290 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
291 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", jr->Name);
292 } else if (jr->Job[0] != 0) {
294 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
295 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", jr->Job);
296 } else if (jr->JobId != 0) {
298 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
299 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
300 } else { /* all records */
302 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
303 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
306 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
310 list_result(jcr, mdb, sendit, ctx, type);
312 sql_free_result(mdb);
321 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
326 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
327 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
329 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
334 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
336 sql_free_result(mdb);
339 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
340 "AS Files,sum(JobBytes) As Bytes FROM Job");
342 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
347 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
349 sql_free_result(mdb);
354 * Stupid MySQL is NON-STANDARD !
357 #define FN "CONCAT(Path.Path,Filename.Name)"
359 #define FN "Path.Path||Filename.Name"
363 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
368 Mmsg(mdb->cmd, "SELECT " FN " AS Filename FROM File,"
369 "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId "
370 "AND Path.PathId=File.PathId",
371 edit_int64(jobid, ed1));
373 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
378 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
380 sql_free_result(mdb);
385 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/