2 * Bacula Catalog Database Find record interface routines
4 * Note, generally, these routines are more complicated
5 * that a simple search by name or id. Such simple
8 * Kern Sibbald, December 2000
14 Copyright (C) 2000-2005 Kern Sibbald
16 This program is free software; you can redistribute it and/or
17 modify it under the terms of the GNU General Public License as
18 published by the Free Software Foundation; either version 2 of
19 the License, or (at your option) any later version.
21 This program is distributed in the hope that it will be useful,
22 but WITHOUT ANY WARRANTY; without even the implied warranty of
23 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
24 General Public License for more details.
26 You should have received a copy of the GNU General Public
27 License along with this program; if not, write to the Free
28 Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
34 /* The following is necessary so that we do not include
35 * the dummy external definition of DB.
37 #define __SQL_C /* indicate that this is sql.c */
42 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
44 /* -----------------------------------------------------------------------
46 * Generic Routines (or almost generic)
48 * -----------------------------------------------------------------------
51 /* Imported subroutines */
52 extern void print_result(B_DB *mdb);
53 extern int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
56 * Find job start time if JobId specified, otherwise
57 * find last full save for Incremental and Differential saves.
59 * StartTime is returned in stime
61 * Returns: 0 on failure
62 * 1 on success, jr is unchanged, but stime is set
65 db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime)
68 char ed1[50], ed2[50];
72 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
73 /* If no Id given, we must find corresponding job */
75 /* Differential is since last Full backup */
77 "SELECT StartTime FROM Job WHERE JobStatus='T' AND Type='%c' AND "
78 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
79 "ORDER BY StartTime DESC LIMIT 1",
80 jr->JobType, L_FULL, jr->Name,
81 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
83 if (jr->JobLevel == L_DIFFERENTIAL) {
84 /* SQL cmd for Differential backup already edited above */
86 /* Incremental is since last Full, Incremental, or Differential */
87 } else if (jr->JobLevel == L_INCREMENTAL) {
89 * For an Incremental job, we must first ensure
90 * that a Full backup was done (cmd edited above)
91 * then we do a second look to find the most recent
94 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
95 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
96 sql_strerror(mdb), mdb->cmd);
100 if ((row = sql_fetch_row(mdb)) == NULL) {
101 sql_free_result(mdb);
102 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
106 sql_free_result(mdb);
107 /* Now edit SQL command for Incremental Job */
109 "SELECT StartTime FROM Job WHERE JobStatus='T' AND Type='%c' AND "
110 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
111 "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
112 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, jr->Name,
113 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
115 Mmsg1(&mdb->errmsg, _("Unknown level=%d\n"), jr->JobLevel);
120 Dmsg1(100, "Submitting: %s\n", mdb->cmd);
121 Mmsg(mdb->cmd, "SELECT StartTime FROM Job WHERE Job.JobId=%s",
122 edit_int64(jr->JobId, ed1));
125 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
126 pm_strcpy(stime, ""); /* set EOS */
127 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
128 sql_strerror(mdb), mdb->cmd);
133 if ((row = sql_fetch_row(mdb)) == NULL) {
134 Mmsg2(&mdb->errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
135 sql_strerror(mdb), mdb->cmd);
136 sql_free_result(mdb);
140 Dmsg1(100, "Got start time: %s\n", row[0]);
141 pm_strcpy(stime, row[0]);
143 sql_free_result(mdb);
150 * Find last failed job since given start-time
151 * it must be either Full or Diff.
153 * Returns: false on failure
154 * true on success, jr is unchanged and stime unchanged
155 * level returned in JobLevel
158 db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
161 char ed1[50], ed2[50];
164 /* Differential is since last Full backup */
166 "SELECT Level FROM Job WHERE JobStatus!='T' AND Type='%c' AND "
167 "Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
168 "AND FileSetId=%s AND StartTime>'%s' "
169 "ORDER BY StartTime DESC LIMIT 1",
170 jr->JobType, L_FULL, L_DIFFERENTIAL, jr->Name,
171 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
174 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
179 if ((row = sql_fetch_row(mdb)) == NULL) {
180 sql_free_result(mdb);
184 JobLevel = (int)*row[0];
185 sql_free_result(mdb);
193 * Find JobId of last job that ran. E.g. for
194 * VERIFY_CATALOG we want the JobId of the last INIT.
195 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
197 * Returns: 1 on success
201 db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
208 if (jr->JobLevel == L_VERIFY_CATALOG) {
210 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
211 " JobStatus='T' AND Name='%s' AND "
212 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
213 L_VERIFY_INIT, jr->Name,
214 edit_int64(jr->ClientId, ed1));
215 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
216 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG) {
219 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus='T' AND "
220 "Name='%s' ORDER BY StartTime DESC LIMIT 1", Name);
223 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus='T' AND "
224 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
225 edit_int64(jr->ClientId, ed1));
228 Mmsg1(&mdb->errmsg, _("Unknown Job level=%c\n"), jr->JobLevel);
232 Dmsg1(100, "Query: %s\n", mdb->cmd);
233 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
237 if ((row = sql_fetch_row(mdb)) == NULL) {
238 Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
239 sql_free_result(mdb);
244 jr->JobId = atoi(row[0]);
245 sql_free_result(mdb);
247 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
248 if (jr->JobId <= 0) {
249 Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
259 * Find Available Media (Volume) for Pool
261 * Find a Volume for a given PoolId, MediaType, and Status.
263 * Returns: 0 on failure
267 db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr)
271 const char *changer, *order;
275 if (item == -1) { /* find oldest volume */
276 /* Find oldest volume */
277 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
278 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
279 "VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,"
280 "FirstWritten,LastWritten,VolStatus,InChanger,VolParts,"
282 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
283 "'Recycle','Purged','Used','Append') "
284 "ORDER BY LastWritten LIMIT 1",
285 edit_int64(mr->PoolId, ed1), mr->MediaType);
288 /* Find next available volume */
290 changer = "AND InChanger=1";
294 if (strcmp(mr->VolStatus, "Recycled") == 0 ||
295 strcmp(mr->VolStatus, "Purged") == 0) {
296 order = "ORDER BY LastWritten ASC,MediaId"; /* take oldest */
298 order = "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId"; /* take most recently written */
300 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
301 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
302 "VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,"
303 "FirstWritten,LastWritten,VolStatus,InChanger,VolParts,"
305 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus='%s' "
308 edit_int64(mr->PoolId, ed1), mr->MediaType,
309 mr->VolStatus, changer, order, item);
311 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
316 numrows = sql_num_rows(mdb);
317 if (item > numrows) {
318 Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d\n"),
324 /* Seek to desired item
325 * Note, we use base 1; SQL uses base 0
327 sql_data_seek(mdb, item-1);
329 if ((row = sql_fetch_row(mdb)) == NULL) {
330 Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
331 sql_free_result(mdb);
336 /* Return fields in Media Record */
337 mr->MediaId = str_to_int64(row[0]);
338 bstrncpy(mr->VolumeName, row[1], sizeof(mr->VolumeName));
339 mr->VolJobs = str_to_int64(row[2]);
340 mr->VolFiles = str_to_int64(row[3]);
341 mr->VolBlocks = str_to_int64(row[4]);
342 mr->VolBytes = str_to_uint64(row[5]);
343 mr->VolMounts = str_to_int64(row[6]);
344 mr->VolErrors = str_to_int64(row[7]);
345 mr->VolWrites = str_to_int64(row[8]);
346 mr->MaxVolBytes = str_to_uint64(row[9]);
347 mr->VolCapacityBytes = str_to_uint64(row[10]);
348 mr->VolRetention = str_to_uint64(row[11]);
349 mr->VolUseDuration = str_to_uint64(row[12]);
350 mr->MaxVolJobs = str_to_int64(row[13]);
351 mr->MaxVolFiles = str_to_int64(row[14]);
352 mr->Recycle = str_to_int64(row[15]);
353 mr->Slot = str_to_int64(row[16]);
354 bstrncpy(mr->cFirstWritten, row[17]!=NULL?row[17]:"", sizeof(mr->cFirstWritten));
355 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
356 bstrncpy(mr->cLastWritten, row[18]!=NULL?row[18]:"", sizeof(mr->cLastWritten));
357 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
358 bstrncpy(mr->VolStatus, row[19], sizeof(mr->VolStatus));
359 mr->InChanger = str_to_int64(row[20]);
360 mr->VolParts = str_to_int64(row[21]);
361 mr->LabelType = str_to_int64(row[22]);
362 sql_free_result(mdb);
369 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/