2 * Bacula Catalog Database List records interface routines
4 * Kern Sibbald, March 2000
9 Copyright (C) 2000-2005 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, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
75 if (type == VERT_LIST) {
76 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
77 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
78 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
79 "RecyclePoolId,LabelType "
80 " FROM Pool ORDER BY PoolId");
82 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
83 "FROM Pool ORDER BY PoolId");
86 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
91 list_result(jcr, mdb, sendit, ctx, type);
98 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
101 if (type == VERT_LIST) {
102 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
104 "FROM Client ORDER BY ClientId");
106 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
107 "FROM Client ORDER BY ClientId");
110 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
115 list_result(jcr, mdb, sendit, ctx, type);
117 sql_free_result(mdb);
123 * If VolumeName is non-zero, list the record for that Volume
124 * otherwise, list the Volumes in the Pool specified by PoolId
127 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
128 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
132 if (type == VERT_LIST) {
133 if (mdbr->VolumeName[0] != 0) {
134 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
135 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
136 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
137 "VolCapacityBytes,VolStatus,Recycle,VolRetention,"
138 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
139 "EndFile,EndBlock,VolParts,LabelType,StorageId"
140 " FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
142 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
143 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
144 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
145 "VolCapacityBytes,VolStatus,Recycle,VolRetention,"
146 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
147 "EndFile,EndBlock,VolParts,LabelType,StorageId"
148 " FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
149 edit_int64(mdbr->PoolId, ed1));
152 if (mdbr->VolumeName[0] != 0) {
153 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,"
154 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
155 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
157 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,"
158 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
159 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
160 edit_int64(mdbr->PoolId, ed1));
164 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
169 list_result(jcr, mdb, sendit, ctx, type);
171 sql_free_result(mdb);
175 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
176 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
180 if (type == VERT_LIST) {
181 if (JobId > 0) { /* do by JobId */
182 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
183 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
184 "JobMedia.EndBlock,Copy,Stripe "
185 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
186 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
188 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
189 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
190 "JobMedia.EndBlock,Copy,Stripe "
191 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
195 if (JobId > 0) { /* do by JobId */
196 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
197 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
198 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
200 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
201 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
204 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
209 list_result(jcr, mdb, sendit, ctx, type);
211 sql_free_result(mdb);
218 * List Job record(s) that match JOB_DBR
220 * Currently, we return all jobs or if jr->JobId is set,
221 * only the job with the specified id.
224 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
225 void *ctx, e_list_type type)
231 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
235 if (type == VERT_LIST) {
236 if (jr->JobId == 0 && jr->Job[0] == 0) {
238 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
239 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
240 "StartTime,EndTime,JobTDate,"
241 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
242 "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet "
243 "FROM Job,Client,Pool,FileSet WHERE "
244 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
245 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
246 } else { /* single record */
248 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
249 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
250 "StartTime,EndTime,JobTDate,"
251 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
252 "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet "
253 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
254 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
255 "AND FileSet.FileSetId=Job.FileSetId",
256 edit_int64(jr->JobId, ed1));
259 if (jr->JobId == 0 && jr->Job[0] == 0) {
261 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
262 "FROM Job ORDER BY StartTime%s", limit);
263 } else { /* single record */
264 Mmsg(mdb->cmd, "SELECT JobId,Name,StartTime,Type,Level,"
265 "JobFiles,JobBytes,JobStatus FROM Job WHERE JobId=%s",
266 edit_int64(jr->JobId, ed1));
269 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
273 list_result(jcr, mdb, sendit, ctx, type);
275 sql_free_result(mdb);
284 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
289 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
290 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
292 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
297 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
299 sql_free_result(mdb);
302 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
303 "AS Files,sum(JobBytes) As Bytes FROM Job");
305 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
310 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
312 sql_free_result(mdb);
317 * Stupid MySQL is NON-STANDARD !
320 #define FN "CONCAT(Path.Path,Filename.Name)"
322 #define FN "Path.Path||Filename.Name"
326 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
331 Mmsg(mdb->cmd, "SELECT " FN " AS Filename FROM File,"
332 "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId "
333 "AND Path.PathId=File.PathId",
334 edit_int64(jobid, ed1));
336 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
341 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
343 sql_free_result(mdb);
348 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/