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, const 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,Enabled,Recycle,VolRetention,"
152 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
153 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
154 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
156 " FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
158 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
159 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
160 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
161 "VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
162 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
163 "EndFile,EndBlock,VolParts,LabelType,StorageId,DeviceId,"
164 "LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
166 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
167 edit_int64(mdbr->PoolId, ed1));
170 if (mdbr->VolumeName[0] != 0) {
171 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
172 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
173 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
175 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
176 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
177 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
178 edit_int64(mdbr->PoolId, ed1));
182 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
187 list_result(jcr, mdb, sendit, ctx, type);
189 sql_free_result(mdb);
193 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
194 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
198 if (type == VERT_LIST) {
199 if (JobId > 0) { /* do by JobId */
200 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
201 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
202 "JobMedia.EndBlock,Copy "
203 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
204 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
206 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
207 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
208 "JobMedia.EndBlock,Copy "
209 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
213 if (JobId > 0) { /* do by JobId */
214 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
215 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
216 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
218 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
219 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
222 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
227 list_result(jcr, mdb, sendit, ctx, type);
229 sql_free_result(mdb);
236 * List Job record(s) that match JOB_DBR
238 * Currently, we return all jobs or if jr->JobId is set,
239 * only the job with the specified id.
242 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
243 void *ctx, e_list_type type)
249 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
253 if (type == VERT_LIST) {
254 if (jr->JobId == 0 && jr->Job[0] == 0) {
256 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
257 "Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
258 "StartTime,EndTime,RealEndTime,JobTDate,"
259 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
260 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
261 "Job.FileSetId,FileSet.FileSet "
262 "FROM Job,Client,Pool,FileSet WHERE "
263 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
264 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
265 } else { /* single record */
267 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
268 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
269 "StartTime,EndTime,RealEndTime,JobTDate,"
270 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
271 "JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
272 "Job.FileSetId,FileSet.FileSet "
273 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
274 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
275 "AND FileSet.FileSetId=Job.FileSetId",
276 edit_int64(jr->JobId, ed1));
279 if (jr->Name[0] != 0) {
281 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
282 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", jr->Name);
283 } else if (jr->Job[0] != 0) {
285 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
286 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", jr->Job);
287 } else if (jr->JobId != 0) {
289 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
290 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
291 } else { /* all records */
293 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
294 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
297 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
301 list_result(jcr, mdb, sendit, ctx, type);
303 sql_free_result(mdb);
312 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
317 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
318 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
320 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
325 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
327 sql_free_result(mdb);
330 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
331 "AS Files,sum(JobBytes) As Bytes FROM Job");
333 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
338 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
340 sql_free_result(mdb);
345 * Stupid MySQL is NON-STANDARD !
348 #define FN "CONCAT(Path.Path,Filename.Name)"
350 #define FN "Path.Path||Filename.Name"
354 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
359 Mmsg(mdb->cmd, "SELECT " FN " AS Filename FROM File,"
360 "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId "
361 "AND Path.PathId=File.PathId",
362 edit_int64(jobid, ed1));
364 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
369 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
371 sql_free_result(mdb);
376 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/