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(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,"
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,"
109 "FileRetention,JobRetention "
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)
137 if (type == VERT_LIST) {
138 if (mdbr->VolumeName[0] != 0) {
139 Mmsg(&mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
140 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
141 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
142 "VolCapacityBytes,VolStatus,Recycle,VolRetention,"
143 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger "
144 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
146 Mmsg(&mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
147 "MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
148 "VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
149 "VolCapacityBytes,VolStatus,Recycle,VolRetention,"
150 "VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger "
151 "FROM Media WHERE Media.PoolId=%u ORDER BY MediaId", mdbr->PoolId);
154 if (mdbr->VolumeName[0] != 0) {
155 Mmsg(&mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,"
156 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
157 "FROM Media WHERE Media.VolumeName='%s'", mdbr->VolumeName);
159 Mmsg(&mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,"
160 "VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
161 "FROM Media WHERE Media.PoolId=%u ORDER BY MediaId", mdbr->PoolId);
165 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
170 list_result(jcr, mdb, sendit, ctx, type);
172 sql_free_result(mdb);
176 void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
177 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,MediaId,Media.VolumeName,"
183 "FirstIndex,LastIndex,StartFile,EndFile,StartBlock,EndBlock "
184 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
185 "AND JobMedia.JobId=%u", JobId);
187 Mmsg(&mdb->cmd, "SELECT JobMediaId,JobId,MediaId,Media.VolumeName,"
188 "FirstIndex,LastIndex,StartFile,EndFile,StartBlock,EndBlock "
189 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
193 if (JobId > 0) { /* do by JobId */
194 Mmsg(&mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
195 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
196 "AND JobMedia.JobId=%u", JobId);
198 Mmsg(&mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
199 "FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
202 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
207 list_result(jcr, mdb, sendit, ctx, type);
209 sql_free_result(mdb);
216 * List Job record(s) that match JOB_DBR
218 * Currently, we return all jobs or if jr->JobId is set,
219 * only the job with the specified id.
222 db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit,
223 void *ctx, e_list_type type)
226 if (type == VERT_LIST) {
227 if (jr->JobId == 0 && jr->Job[0] == 0) {
229 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
230 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
231 "StartTime,EndTime,JobTDate,"
232 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
233 "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet "
234 "FROM Job,Client,Pool,FileSet WHERE "
235 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
236 "AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime");
237 } else { /* single record */
239 "SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
240 "Job.ClientId,Client.Name,JobStatus,SchedTime,"
241 "StartTime,EndTime,JobTDate,"
242 "VolSessionId,VolSessionTime,JobFiles,JobErrors,"
243 "JobMissingFiles,Job.PoolId,Pool.Name,Job.FileSetId,FileSet.FileSet "
244 "FROM Job,Client,Pool,FileSet WHERE Job.JobId=%u AND "
245 "Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
246 "AND FileSet.FileSetId=Job.FileSetId", jr->JobId);
249 if (jr->JobId == 0 && jr->Job[0] == 0) {
251 "SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
252 "FROM Job ORDER BY StartTime");
253 } else { /* single record */
254 Mmsg(&mdb->cmd, "SELECT JobId,Name,StartTime,Type,Level,"
255 "JobFiles,JobBytes,JobStatus FROM Job WHERE JobId=%u", jr->JobId);
258 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
262 list_result(jcr, mdb, sendit, ctx, type);
264 sql_free_result(mdb);
273 db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, DB_LIST_HANDLER *sendit, void *ctx)
278 Mmsg(&mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
279 "AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
281 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
286 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
288 sql_free_result(mdb);
291 Mmsg(&mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) \
292 AS Files,sum(JobBytes) As Bytes FROM Job");
294 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
299 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
301 sql_free_result(mdb);
306 * Stupid MySQL is NON-STANDARD !
309 #define FN "CONCAT(Path.Path,Filename.Name)"
311 #define FN "Path.Path||Filename.Name"
315 db_list_files_for_job(JCR *jcr, B_DB *mdb, uint32_t jobid, DB_LIST_HANDLER *sendit, void *ctx)
319 Mmsg(&mdb->cmd, "SELECT " FN " AS Filename FROM File,"
320 "Filename,Path WHERE File.JobId=%u AND Filename.FilenameId=File.FilenameId "
321 "AND Path.PathId=File.PathId",
324 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
329 list_result(jcr, mdb, sendit, ctx, HORZ_LIST);
331 sql_free_result(mdb);
336 #endif /* HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL */