2 * Bacula Catalog Database List records interface routines
4 * Kern Sibbald, March 2000
10 Copyright (C) 2000-2005 Kern Sibbald
12 This program is free software; you can redistribute it and/or
13 modify it under the terms of the GNU General Public License as
14 published by the Free Software Foundation; either version 2 of
15 the License, or (at your option) any later version.
17 This program is distributed in the hope that it will be useful,
18 but WITHOUT ANY WARRANTY; without even the implied warranty of
19 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
20 General Public License for more details.
22 You should have received a copy of the GNU General Public
23 License along with this program; if not, write to the Free
24 Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
29 /* The following is necessary so that we do not include
30 * the dummy external definition of DB.
32 #define __SQL_C /* indicate that this is sql.c */
37 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
39 /* -----------------------------------------------------------------------
41 * Generic Routines (or almost generic)
43 * -----------------------------------------------------------------------
46 /* Imported subroutines */
47 extern void list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type);
48 extern int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
52 * Submit general SQL query
54 int db_list_sql_query(JCR *jcr, B_DB *mdb, char *query, DB_LIST_HANDLER *sendit,
55 void *ctx, int verbose, e_list_type type)
58 if (sql_query(mdb, query) != 0) {
59 Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb));
61 sendit(ctx, mdb->errmsg);
67 mdb->result = sql_store_result(mdb);
70 list_result(jcr, mdb, sendit, ctx, type);
78 db_list_pool_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
81 if (type == VERT_LIST) {
82 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
83 "AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
84 "AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
85 "RecyclePoolId,LabelType "
86 " FROM Pool ORDER BY PoolId");
88 Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
89 "FROM Pool ORDER BY PoolId");
92 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
97 list_result(jcr, mdb, sendit, ctx, type);
104 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
107 if (type == VERT_LIST) {
108 Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
110 "FROM Client ORDER BY ClientId");
112 Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
113 "FROM Client ORDER BY ClientId");
116 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
121 list_result(jcr, mdb, sendit, ctx, type);
123 sql_free_result(mdb);
129 * If VolumeName is non-zero, list the record for that Volume
130 * otherwise, list the Volumes in the Pool specified by PoolId
133 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
134 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
138 if (type == VERT_LIST) {
139 if (mdbr->VolumeName[0] != 0) {
140 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
141 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
142 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
143 "VolCapacityBytes,VolStatus,Recycle,VolRetention,"
144 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
145 "EndFile,EndBlock,VolParts,LabelType,StorageId"
146 " FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
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.PoolId=%s ORDER BY MediaId",
155 edit_int64(mdbr->PoolId, ed1));
158 if (mdbr->VolumeName[0] != 0) {
159 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,"
160 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
161 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
163 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,"
164 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
165 "FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
166 edit_int64(mdbr->PoolId, ed1));
170 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
175 list_result(jcr, mdb, sendit, ctx, type);
177 sql_free_result(mdb);
181 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
182 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
186 if (type == VERT_LIST) {
187 if (JobId > 0) { /* do by JobId */
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 "
192 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
194 Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
195 "FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
196 "JobMedia.EndBlock,Copy,Stripe "
197 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
201 if (JobId > 0) { /* do by JobId */
202 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
203 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
204 "AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
206 Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
207 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
210 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
215 list_result(jcr, mdb, sendit, ctx, type);
217 sql_free_result(mdb);
224 * List Job record(s) that match JOB_DBR
226 * Currently, we return all jobs or if jr->JobId is set,
227 * only the job with the specified id.
230 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
231 void *ctx, e_list_type type)
237 snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
241 if (type == VERT_LIST) {
242 if (jr->JobId == 0 && jr->Job[0] == 0) {
244 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
245 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
246 "StartTime,EndTime,JobTDate,"
247 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
248 "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet "
249 "FROM Job,Client,Pool,FileSet WHERE "
250 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
251 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
252 } else { /* single record */
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 Job.JobId=%s AND "
260 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
261 "AND FileSet.FileSetId=Job.FileSetId",
262 edit_int64(jr->JobId, ed1));
265 if (jr->JobId == 0 && jr->Job[0] == 0) {
267 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
268 "FROM Job ORDER BY StartTime%s", limit);
269 } else { /* single record */
270 Mmsg(mdb->cmd, "SELECT JobId,Name,StartTime,Type,Level,"
271 "JobFiles,JobBytes,JobStatus FROM Job WHERE JobId=%s",
272 edit_int64(jr->JobId, ed1));
275 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
279 list_result(jcr, mdb, sendit, ctx, type);
281 sql_free_result(mdb);
290 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
295 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
296 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
298 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
303 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
305 sql_free_result(mdb);
308 Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
309 "AS Files,sum(JobBytes) As Bytes FROM Job");
311 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
316 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
318 sql_free_result(mdb);
323 * Stupid MySQL is NON-STANDARD !
326 #define FN "CONCAT(Path.Path,Filename.Name)"
328 #define FN "Path.Path||Filename.Name"
332 db_list_files_for_job(JCR *jcr, B_DB *mdb, JobId_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
337 Mmsg(mdb->cmd, "SELECT " FN " AS Filename FROM File,"
338 "Filename,Path WHERE File.JobId=%s AND Filename.FilenameId=File.FilenameId "
339 "AND Path.PathId=File.PathId",
340 edit_int64(jobid, ed1));
342 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
347 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
349 sql_free_result(mdb);
354 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/