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"
155 " FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
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"
164 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
165 edit_int64(mdbr->PoolId, ed1));
168 if (mdbr->VolumeName[0] != 0) {
169 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
170 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
171 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
173 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
174 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
175 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
176 edit_int64(mdbr->PoolId, ed1));
180 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
185 list_result(jcr, mdb, sendit, ctx, type);
187 sql_free_result(mdb);
191 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
192 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
196 if (type == VERT_LIST) {
197 if (JobId > 0) { /* do by JobId */
198 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
199 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
200 "JobMedia.EndBlock,Copy "
201 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
202 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
204 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
205 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
206 "JobMedia.EndBlock,Copy "
207 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
211 if (JobId > 0) { /* do by JobId */
212 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
213 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
214 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
216 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
217 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
220 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
225 list_result(jcr, mdb, sendit, ctx, type);
227 sql_free_result(mdb);
234 * List Job record(s) that match JOB_DBR
236 * Currently, we return all jobs or if jr->JobId is set,
237 * only the job with the specified id.
240 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
241 void *ctx, e_list_type type)
247 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
251 if (type == VERT_LIST) {
252 if (jr->JobId == 0 && jr->Job[0] == 0) {
254 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
255 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
256 "StartTime,EndTime,JobTDate,"
257 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
258 "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet "
259 "FROM Job,Client,Pool,FileSet WHERE "
260 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
261 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
262 } else { /* single record */
264 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
265 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
266 "StartTime,EndTime,JobTDate,"
267 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
268 "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet "
269 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
270 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
271 "AND FileSet.FileSetId=Job.FileSetId",
272 edit_int64(jr->JobId, ed1));
275 if (jr->Name[0] != 0) {
277 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
278 "FROM Job WHERE Name='%s' ORDER BY StartTime,JobId ASC", jr->Name);
279 } else if (jr->Job[0] != 0) {
281 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
282 "FROM Job WHERE Job='%s' ORDER BY StartTime,JobId ASC", jr->Job);
283 } else if (jr->JobId != 0) {
285 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
286 "FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
287 } else { /* all records */
289 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
290 "FROM Job ORDER BY StartTime,JobId ASC%s", limit);
293 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
297 list_result(jcr, mdb, sendit, ctx, type);
299 sql_free_result(mdb);
308 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
313 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
314 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
316 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
321 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
323 sql_free_result(mdb);
326 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
327 "AS Files,sum(JobBytes) As Bytes FROM Job");
329 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
334 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
336 sql_free_result(mdb);
341 * Stupid MySQL is NON-STANDARD !
344 #define FN "CONCAT(Path.Path,Filename.Name)"
346 #define FN "Path.Path||Filename.Name"
350 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
355 Mmsg(mdb->cmd, "SELECT " FN " AS Filename FROM File,"
356 "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId "
357 "AND Path.PathId=File.PathId",
358 edit_int64(jobid, ed1));
360 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
365 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
367 sql_free_result(mdb);
372 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/