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,"
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)
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,"
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,"
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)
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");
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");
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*/