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 * -----------------------------------------------------------------------
41 * Submit general SQL query
43 int db_list_sql_query(JCR *jcr, B_DB *mdb, const char *query, DB_LIST_HANDLER *sendit,
44 void *ctx, int verbose, e_list_type type)
47 if (sql_query(mdb, query) != 0) {
48 Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb));
50 sendit(ctx, mdb->errmsg);
56 mdb->result = sql_store_result(mdb);
59 list_result(jcr, mdb, sendit, ctx, type);
67 db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr,
68 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
71 if (type == VERT_LIST) {
72 if (pdbr->Name[0] != 0) {
73 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
74 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
75 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
76 "RecyclePoolId,LabelType "
77 " FROM Pool WHERE Name='%s'", pdbr->Name);
79 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
80 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
81 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
82 "RecyclePoolId,LabelType "
83 " FROM Pool ORDER BY PoolId");
86 if (pdbr->Name[0] != 0) {
87 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
88 "FROM Pool WHERE Name='%s'", pdbr->Name);
90 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
91 "FROM Pool ORDER BY PoolId");
95 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
100 list_result(jcr, mdb, sendit, ctx, type);
102 sql_free_result(mdb);
107 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
110 if (type == VERT_LIST) {
111 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
113 "FROM Client ORDER BY ClientId");
115 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
116 "FROM Client ORDER BY ClientId");
119 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
124 list_result(jcr, mdb, sendit, ctx, type);
126 sql_free_result(mdb);
132 * If VolumeName is non-zero, list the record for that Volume
133 * otherwise, list the Volumes in the Pool specified by PoolId
136 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
137 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
141 if (type == VERT_LIST) {
142 if (mdbr->VolumeName[0] != 0) {
143 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
144 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
145 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
146 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
147 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
148 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
149 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
151 " FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
153 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
154 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
155 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
156 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
157 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
158 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
159 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
161 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
162 edit_int64(mdbr->PoolId, ed1));
165 if (mdbr->VolumeName[0] != 0) {
166 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
167 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
168 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
170 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
171 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
172 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
173 edit_int64(mdbr->PoolId, ed1));
177 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
182 list_result(jcr, mdb, sendit, ctx, type);
184 sql_free_result(mdb);
188 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
189 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
193 if (type == VERT_LIST) {
194 if (JobId > 0) { /* do by JobId */
195 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
196 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
197 "JobMedia.EndBlock,Copy "
198 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
199 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
201 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
202 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
203 "JobMedia.EndBlock,Copy "
204 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
208 if (JobId > 0) { /* do by JobId */
209 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
210 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
211 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
213 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
214 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
217 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
222 list_result(jcr, mdb, sendit, ctx, type);
224 sql_free_result(mdb);
231 * List Job record(s) that match JOB_DBR
233 * Currently, we return all jobs or if jr->JobId is set,
234 * only the job with the specified id.
237 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
238 void *ctx, e_list_type type)
244 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
248 if (type == VERT_LIST) {
249 if (jr->JobId == 0 && jr->Job[0] == 0) {
251 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
252 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
253 "StartTime,EndTime,RealEndTime,JobTDate,"
254 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
255 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
256 "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,RealEndTime,JobTDate,"
265 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
266 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
267 "Job.FileSetId,FileSet.FileSet "
268 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
269 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
270 "AND FileSet.FileSetId=Job.FileSetId",
271 edit_int64(jr->JobId, ed1));
274 if (jr->Name[0] != 0) {
276 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
277 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", jr->Name);
278 } else if (jr->Job[0] != 0) {
280 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
281 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", jr->Job);
282 } else if (jr->JobId != 0) {
284 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
285 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
286 } else { /* all records */
288 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
289 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
292 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
296 list_result(jcr, mdb, sendit, ctx, type);
298 sql_free_result(mdb);
307 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
312 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
313 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
315 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
320 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
322 sql_free_result(mdb);
325 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
326 "AS Files,sum(JobBytes) As Bytes FROM Job");
328 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
333 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
335 sql_free_result(mdb);
340 * Stupid MySQL is NON-STANDARD !
343 #define FN "CONCAT(Path.Path,Filename.Name)"
345 #define FN "Path.Path||Filename.Name"
349 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
354 Mmsg(mdb->cmd, "SELECT " FN " AS Filename FROM File,"
355 "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId "
356 "AND Path.PathId=File.PathId",
357 edit_int64(jobid, ed1));
359 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
364 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
366 sql_free_result(mdb);
371 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/