2 * Bacula Catalog Database List records interface routines
4 * Kern Sibbald, March 2000
9 Bacula® - The Network Backup Solution
11 Copyright (C) 2000-2006 Free Software Foundation Europe e.V.
13 The main author of Bacula is Kern Sibbald, with contributions from
14 many others, a complete list can be found in the file AUTHORS.
15 This program is Free Software; you can redistribute it and/or
16 modify it under the terms of version two of the GNU General Public
17 License as published by the Free Software Foundation plus additions
18 that are listed in the file LICENSE.
20 This program is distributed in the hope that it will be useful, but
21 WITHOUT ANY WARRANTY; without even the implied warranty of
22 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
23 General Public License for more details.
25 You should have received a copy of the GNU General Public License
26 along with this program; if not, write to the Free Software
27 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
30 Bacula® is a registered trademark of John Walker.
31 The licensor of Bacula is the Free Software Foundation Europe
32 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
33 Switzerland, email:ftf@fsfeurope.org.
36 /* The following is necessary so that we do not include
37 * the dummy external definition of DB.
39 #define __SQL_C /* indicate that this is sql.c */
44 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
46 /* -----------------------------------------------------------------------
48 * Generic Routines (or almost generic)
50 * -----------------------------------------------------------------------
54 * Submit general SQL query
56 int db_list_sql_query(JCR *jcr, B_DB *mdb, const char *query, DB_LIST_HANDLER *sendit,
57 void *ctx, int verbose, e_list_type type)
60 if (sql_query(mdb, query) != 0) {
61 Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb));
63 sendit(ctx, mdb->errmsg);
69 mdb->result = sql_store_result(mdb);
72 list_result(jcr, mdb, sendit, ctx, type);
80 db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr,
81 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
84 if (type == VERT_LIST) {
85 if (pdbr->Name[0] != 0) {
86 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
87 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
88 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
89 "RecyclePoolId,LabelType "
90 " FROM Pool WHERE Name='%s'", pdbr->Name);
92 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
93 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
94 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
95 "RecyclePoolId,LabelType "
96 " FROM Pool ORDER BY PoolId");
99 if (pdbr->Name[0] != 0) {
100 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
101 "FROM Pool WHERE Name='%s'", pdbr->Name);
103 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
104 "FROM Pool ORDER BY PoolId");
108 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
113 list_result(jcr, mdb, sendit, ctx, type);
115 sql_free_result(mdb);
120 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
123 if (type == VERT_LIST) {
124 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
126 "FROM Client ORDER BY ClientId");
128 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
129 "FROM Client ORDER BY ClientId");
132 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
137 list_result(jcr, mdb, sendit, ctx, type);
139 sql_free_result(mdb);
145 * If VolumeName is non-zero, list the record for that Volume
146 * otherwise, list the Volumes in the Pool specified by PoolId
149 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
150 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
154 if (type == VERT_LIST) {
155 if (mdbr->VolumeName[0] != 0) {
156 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
157 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
158 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
159 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
160 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
161 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
162 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
164 " FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
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.PoolId=%s ORDER BY MediaId",
175 edit_int64(mdbr->PoolId, ed1));
178 if (mdbr->VolumeName[0] != 0) {
179 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
180 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
181 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
183 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
184 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
185 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
186 edit_int64(mdbr->PoolId, ed1));
190 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
195 list_result(jcr, mdb, sendit, ctx, type);
197 sql_free_result(mdb);
201 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
202 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
206 if (type == VERT_LIST) {
207 if (JobId > 0) { /* do by JobId */
208 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
209 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
210 "JobMedia.EndBlock,Copy "
211 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
212 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
214 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
215 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
216 "JobMedia.EndBlock,Copy "
217 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
221 if (JobId > 0) { /* do by JobId */
222 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
223 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
224 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
226 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
227 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
230 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
235 list_result(jcr, mdb, sendit, ctx, type);
237 sql_free_result(mdb);
244 * List Job record(s) that match JOB_DBR
246 * Currently, we return all jobs or if jr->JobId is set,
247 * only the job with the specified id.
250 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
251 void *ctx, e_list_type type)
257 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
261 if (type == VERT_LIST) {
262 if (jr->JobId == 0 && jr->Job[0] == 0) {
264 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
265 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
266 "StartTime,EndTime,RealEndTime,JobTDate,"
267 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
268 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
269 "Job.FileSetId,FileSet.FileSet "
270 "FROM Job,Client,Pool,FileSet WHERE "
271 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
272 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
273 } else { /* single record */
275 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
276 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
277 "StartTime,EndTime,RealEndTime,JobTDate,"
278 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
279 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
280 "Job.FileSetId,FileSet.FileSet "
281 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
282 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
283 "AND FileSet.FileSetId=Job.FileSetId",
284 edit_int64(jr->JobId, ed1));
287 if (jr->Name[0] != 0) {
289 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
290 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", jr->Name);
291 } else if (jr->Job[0] != 0) {
293 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
294 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", jr->Job);
295 } else if (jr->JobId != 0) {
297 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
298 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
299 } else { /* all records */
301 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
302 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
305 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
309 list_result(jcr, mdb, sendit, ctx, type);
311 sql_free_result(mdb);
320 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
325 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
326 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
328 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
333 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
335 sql_free_result(mdb);
338 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
339 "AS Files,sum(JobBytes) As Bytes FROM Job");
341 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
346 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
348 sql_free_result(mdb);
353 * Stupid MySQL is NON-STANDARD !
356 #define FN "CONCAT(Path.Path,Filename.Name)"
358 #define FN "Path.Path||Filename.Name"
362 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
367 Mmsg(mdb->cmd, "SELECT " FN " AS Filename FROM File,"
368 "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId "
369 "AND Path.PathId=File.PathId",
370 edit_int64(jobid, ed1));
372 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
377 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
379 sql_free_result(mdb);
384 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/