2 * Bacula Catalog Database List records interface routines
4 * Kern Sibbald, March 2000
9 Copyright (C) 2000-2006 Kern Sibbald
11 This program is free software; you can redistribute it and/or
12 modify it under the terms of the GNU General Public License
13 version 2 as amended with additional clauses defined in the
14 file LICENSE in the main source directory.
16 This program is distributed in the hope that it will be useful,
17 but WITHOUT ANY WARRANTY; without even the implied warranty of
18 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 the file LICENSE for additional details.
23 /* The following is necessary so that we do not include
24 * the dummy external definition of DB.
26 #define __SQL_C /* indicate that this is sql.c */
31 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
33 /* -----------------------------------------------------------------------
35 * Generic Routines (or almost generic)
37 * -----------------------------------------------------------------------
40 /* Imported subroutines */
41 extern void list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type);
42 extern int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
46 * Submit general SQL query
48 int db_list_sql_query(JCR *jcr, B_DB *mdb, char *query, DB_LIST_HANDLER *sendit,
49 void *ctx, int verbose, e_list_type type)
52 if (sql_query(mdb, query) != 0) {
53 Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb));
55 sendit(ctx, mdb->errmsg);
61 mdb->result = sql_store_result(mdb);
64 list_result(jcr, mdb, sendit, ctx, type);
72 db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr,
73 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
76 if (type == VERT_LIST) {
77 if (pdbr->Name[0] != 0) {
78 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
79 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
80 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
81 "RecyclePoolId,LabelType "
82 " FROM Pool WHERE Name='%s'", pdbr->Name);
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 ORDER BY PoolId");
91 if (pdbr->Name[0] != 0) {
92 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
93 "FROM Pool WHERE Name='%s'", pdbr->Name);
95 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
96 "FROM Pool ORDER BY PoolId");
100 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
105 list_result(jcr, mdb, sendit, ctx, type);
107 sql_free_result(mdb);
112 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
115 if (type == VERT_LIST) {
116 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
118 "FROM Client ORDER BY ClientId");
120 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
121 "FROM Client ORDER BY ClientId");
124 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
129 list_result(jcr, mdb, sendit, ctx, type);
131 sql_free_result(mdb);
137 * If VolumeName is non-zero, list the record for that Volume
138 * otherwise, list the Volumes in the Pool specified by PoolId
141 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
142 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
146 if (type == VERT_LIST) {
147 if (mdbr->VolumeName[0] != 0) {
148 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
149 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
150 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
151 "VolCapacityBytes,VolStatus,Recycle,VolRetention,"
152 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
153 "EndFile,EndBlock,VolParts,LabelType,StorageId"
154 " FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
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,Recycle,VolRetention,"
160 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
161 "EndFile,EndBlock,VolParts,LabelType,StorageId"
162 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
163 edit_int64(mdbr->PoolId, ed1));
166 if (mdbr->VolumeName[0] != 0) {
167 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,"
168 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
169 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
171 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,"
172 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
173 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
174 edit_int64(mdbr->PoolId, ed1));
178 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
183 list_result(jcr, mdb, sendit, ctx, type);
185 sql_free_result(mdb);
189 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
190 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
194 if (type == VERT_LIST) {
195 if (JobId > 0) { /* do by JobId */
196 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
197 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
198 "JobMedia.EndBlock,Copy,Stripe "
199 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
200 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
202 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
203 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
204 "JobMedia.EndBlock,Copy,Stripe "
205 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
209 if (JobId > 0) { /* do by JobId */
210 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
211 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
212 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
214 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
215 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
218 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
223 list_result(jcr, mdb, sendit, ctx, type);
225 sql_free_result(mdb);
232 * List Job record(s) that match JOB_DBR
234 * Currently, we return all jobs or if jr->JobId is set,
235 * only the job with the specified id.
238 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
239 void *ctx, e_list_type type)
245 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
249 if (type == VERT_LIST) {
250 if (jr->JobId == 0 && jr->Job[0] == 0) {
252 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
253 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
254 "StartTime,EndTime,JobTDate,"
255 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
256 "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet "
257 "FROM Job,Client,Pool,FileSet WHERE "
258 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
259 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
260 } else { /* single record */
262 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
263 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
264 "StartTime,EndTime,JobTDate,"
265 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
266 "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet "
267 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
268 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
269 "AND FileSet.FileSetId=Job.FileSetId",
270 edit_int64(jr->JobId, ed1));
273 if (jr->Name[0] != 0) {
275 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
276 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", jr->Name);
277 } else if (jr->Job[0] != 0) {
279 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
280 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", jr->Job);
281 } else if (jr->JobId != 0) {
283 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
284 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
285 } else { /* all records */
287 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
288 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
291 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
295 list_result(jcr, mdb, sendit, ctx, type);
297 sql_free_result(mdb);
306 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
311 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
312 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
314 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
319 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
321 sql_free_result(mdb);
324 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
325 "AS Files,sum(JobBytes) As Bytes FROM Job");
327 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
332 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
334 sql_free_result(mdb);
339 * Stupid MySQL is NON-STANDARD !
342 #define FN "CONCAT(Path.Path,Filename.Name)"
344 #define FN "Path.Path||Filename.Name"
348 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
353 Mmsg(mdb->cmd, "SELECT " FN " AS Filename FROM File,"
354 "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId "
355 "AND Path.PathId=File.PathId",
356 edit_int64(jobid, ed1));
358 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
363 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
365 sql_free_result(mdb);
370 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/