2 * Bacula Catalog Database List records interface routines
4 * Kern Sibbald, March 2000
10 Copyright (C) 2000, 2001, 2002 Kern Sibbald and John Walker
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_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
39 /* -----------------------------------------------------------------------
41 * Generic Routines (or almost generic)
43 * -----------------------------------------------------------------------
46 /* Imported subroutines */
47 extern void list_result(B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type);
48 extern int QueryDB(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(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 "
85 "FROM Pool ORDER BY PoolId");
87 Mmsg(&mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
88 "FROM Pool ORDER BY PoolId");
91 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
96 list_result(mdb, sendit, ctx, type);
103 db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
106 if (type == VERT_LIST) {
107 Mmsg(&mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
108 "FileRetention,JobRetention "
109 "FROM Client ORDER BY ClientId");
111 Mmsg(&mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
112 "FROM Client ORDER BY ClientId");
115 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
120 list_result(mdb, sendit, ctx, type);
122 sql_free_result(mdb);
128 * If VolumeName is non-zero, list the record for that Volume
129 * otherwise, list the Volumes in the Pool specified by PoolId
132 db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
133 DB_LIST_HANDLER *sendit, void *ctx, e_list_type type)
136 if (type == VERT_LIST) {
137 if (mdbr->VolumeName[0] != 0) {
138 Mmsg(&mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
139 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
140 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
141 "VolCapacityBytes,VolStatus,Recycle,VolRetention,"
142 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes "
143 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
145 Mmsg(&mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
146 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
147 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
148 "VolCapacityBytes,VolStatus,Recycle,VolRetention,"
149 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes "
150 "FROM Media WHERE Media.PoolId=%u ORDER BY MediaId", mdbr->PoolId);
153 if (mdbr->VolumeName[0] != 0) {
154 Mmsg(&mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,"
155 "VolBytes,VolFiles,VolRetention,Recycle,Slot,MediaType,LastWritten "
156 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
158 Mmsg(&mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,"
159 "VolBytes,VolFiles,VolRetention,Recycle,Slot,MediaType,LastWritten "
160 "FROM Media WHERE Media.PoolId=%u ORDER BY MediaId", mdbr->PoolId);
164 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
169 list_result(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)
179 if (type == VERT_LIST) {
180 if (JobId > 0) { /* do by JobId */
181 Mmsg(&mdb->cmd, "SELECT JobMediaId,JobId,MediaId,Media.VolumeName,"
182 "FirstIndex,LastIndex,StartFile,EndFile,StartBlock,EndBlock "
183 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
184 "AND JobMedia.JobId=%u", JobId);
186 Mmsg(&mdb->cmd, "SELECT JobMediaId,JobId,MediaId,Media.VolumeName,"
187 "FirstIndex,LastIndex,StartFile,EndFile,StartBlock,EndBlock "
188 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
192 if (JobId > 0) { /* do by JobId */
193 Mmsg(&mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
194 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
195 "AND JobMedia.JobId=%u", JobId);
197 Mmsg(&mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
198 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
201 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
206 list_result(mdb, sendit, ctx, type);
208 sql_free_result(mdb);
215 * List Job record(s) that match JOB_DBR
217 * Currently, we return all jobs or if jr->JobId is set,
218 * only the job with the specified id.
221 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
222 void *ctx, e_list_type type)
225 if (type == VERT_LIST) {
226 if (jr->JobId == 0 && jr->Job[0] == 0) {
228 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
229 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
230 "StartTime,EndTime,JobTDate,"
231 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
232 "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet "
233 "FROM Job,Client,Pool,FileSet WHERE "
234 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
235 "AND FileSet.FileSetId=Job.FileSetId ORDER BY JobId");
236 } else { /* single record */
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 Job.JobId=%u AND "
244 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
245 "AND FileSet.FileSetId=Job.FileSetId", jr->JobId);
248 if (jr->JobId == 0 && jr->Job[0] == 0) {
250 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
251 "FROM Job ORDER BY JobId");
252 } else { /* single record */
253 Mmsg(&mdb->cmd, "SELECT JobId,Name,StartTime,Type,Level,"
254 "JobFiles,JobBytes,JobStatus FROM Job WHERE JobId=%u", jr->JobId);
257 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
261 list_result(mdb, sendit, ctx, type);
263 sql_free_result(mdb);
272 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
277 Mmsg(&mdb->cmd, "SELECT count(*) AS Jobs, sum(JobFiles) \
278 AS Files, sum(JobBytes) AS Bytes, Name AS Job FROM Job GROUP BY Name");
280 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
285 list_result(mdb, sendit, ctx, HORZ_LIST);
287 sql_free_result(mdb);
290 Mmsg(&mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) \
291 AS Files,sum(JobBytes) As Bytes FROM Job");
293 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
298 list_result(mdb, sendit, ctx, HORZ_LIST);
300 sql_free_result(mdb);
306 db_list_files_for_job(JCR *jcr, B_DB *mdb, uint32_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
310 Mmsg(&mdb->cmd, "SELECT Path.Path,Filename.Name FROM File,\
311 Filename,Path WHERE File.JobId=%u AND Filename.FilenameId=File.FilenameId \
312 AND Path.PathId=File.PathId",
315 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
320 list_result(mdb, sendit, ctx, HORZ_LIST);
322 sql_free_result(mdb);
327 #endif /* HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL */