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
13 Copyright (C) 2000-2006 Kern Sibbald
15 This program is free software; you can redistribute it and/or
16 modify it under the terms of the GNU General Public License
17 version 2 as amended with additional clauses defined in the
18 file LICENSE in the main source directory.
20 This program is distributed in the hope that it will be useful,
21 but WITHOUT ANY WARRANTY; without even the implied warranty of
22 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 the file LICENSE for additional details.
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 * -----------------------------------------------------------------------
47 * Find job start time if JobId specified, otherwise
48 * find last full save for Incremental and Differential saves.
50 * StartTime is returned in stime
52 * Returns: 0 on failure
53 * 1 on success, jr is unchanged, but stime is set
56 db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime)
59 char ed1[50], ed2[50];
63 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
64 /* If no Id given, we must find corresponding job */
66 /* Differential is since last Full backup */
68 "SELECT StartTime FROM Job WHERE JobStatus='T' AND Type='%c' AND "
69 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
70 "ORDER BY StartTime DESC LIMIT 1",
71 jr->JobType, L_FULL, jr->Name,
72 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
74 if (jr->JobLevel == L_DIFFERENTIAL) {
75 /* SQL cmd for Differential backup already edited above */
77 /* Incremental is since last Full, Incremental, or Differential */
78 } else if (jr->JobLevel == L_INCREMENTAL) {
80 * For an Incremental job, we must first ensure
81 * that a Full backup was done (cmd edited above)
82 * then we do a second look to find the most recent
85 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
86 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
87 sql_strerror(mdb), mdb->cmd);
90 if ((row = sql_fetch_row(mdb)) == NULL) {
92 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
96 /* Now edit SQL command for Incremental Job */
98 "SELECT StartTime FROM Job WHERE JobStatus='T' AND Type='%c' AND "
99 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
100 "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
101 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, jr->Name,
102 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
104 Mmsg1(mdb->errmsg, _("Unknown level=%d\n"), jr->JobLevel);
108 Dmsg1(100, "Submitting: %s\n", mdb->cmd);
109 Mmsg(mdb->cmd, "SELECT StartTime FROM Job WHERE Job.JobId=%s",
110 edit_int64(jr->JobId, ed1));
113 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
114 pm_strcpy(stime, ""); /* set EOS */
115 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
116 sql_strerror(mdb), mdb->cmd);
120 if ((row = sql_fetch_row(mdb)) == NULL) {
121 Mmsg2(&mdb->errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
122 sql_strerror(mdb), mdb->cmd);
123 sql_free_result(mdb);
126 Dmsg1(100, "Got start time: %s\n", row[0]);
127 pm_strcpy(stime, row[0]);
129 sql_free_result(mdb);
140 * Find last failed job since given start-time
141 * it must be either Full or Diff.
143 * Returns: false on failure
144 * true on success, jr is unchanged and stime unchanged
145 * level returned in JobLevel
148 db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
151 char ed1[50], ed2[50];
154 /* Differential is since last Full backup */
156 "SELECT Level FROM Job WHERE JobStatus!='T' AND Type='%c' AND "
157 "Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
158 "AND FileSetId=%s AND StartTime>'%s' "
159 "ORDER BY StartTime DESC LIMIT 1",
160 jr->JobType, L_FULL, L_DIFFERENTIAL, jr->Name,
161 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
164 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
169 if ((row = sql_fetch_row(mdb)) == NULL) {
170 sql_free_result(mdb);
174 JobLevel = (int)*row[0];
175 sql_free_result(mdb);
183 * Find JobId of last job that ran. E.g. for
184 * VERIFY_CATALOG we want the JobId of the last INIT.
185 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
187 * Returns: true on success
191 db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
198 Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
199 if (jr->JobLevel == L_VERIFY_CATALOG) {
201 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
202 " JobStatus='T' AND Name='%s' AND "
203 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
204 L_VERIFY_INIT, jr->Name,
205 edit_int64(jr->ClientId, ed1));
206 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
207 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
208 jr->JobType == JT_BACKUP) {
211 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus='T' AND "
212 "Name='%s' ORDER BY StartTime DESC LIMIT 1", Name);
215 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus='T' AND "
216 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
217 edit_int64(jr->ClientId, ed1));
220 Mmsg1(&mdb->errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
224 Dmsg1(100, "Query: %s\n", mdb->cmd);
225 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
229 if ((row = sql_fetch_row(mdb)) == NULL) {
230 Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
231 sql_free_result(mdb);
236 jr->JobId = str_to_int64(row[0]);
237 sql_free_result(mdb);
239 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
240 if (jr->JobId <= 0) {
241 Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
251 * Find Available Media (Volume) for Pool
253 * Find a Volume for a given PoolId, MediaType, and Status.
255 * Returns: 0 on failure
259 db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr)
268 if (item == -1) { /* find oldest volume */
269 /* Find oldest volume */
270 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
271 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
272 "VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,"
273 "FirstWritten,LastWritten,VolStatus,InChanger,VolParts,"
275 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
276 "'Recycle','Purged','Used','Append') AND Enabled=1 "
277 "ORDER BY LastWritten LIMIT 1",
278 edit_int64(mr->PoolId, ed1), mr->MediaType);
282 /* Find next available volume */
284 bsnprintf(changer, sizeof(changer), "AND InChanger=1 AND StorageId=%s",
285 edit_int64(mr->StorageId, ed1));
289 if (strcmp(mr->VolStatus, "Recycled") == 0 ||
290 strcmp(mr->VolStatus, "Purged") == 0) {
291 order = "ORDER BY LastWritten ASC,MediaId"; /* take oldest */
293 order = "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId"; /* take most recently written */
295 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
296 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
297 "VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,"
298 "FirstWritten,LastWritten,VolStatus,InChanger,VolParts,"
300 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
301 "AND VolStatus='%s' "
304 edit_int64(mr->PoolId, ed1), mr->MediaType,
305 mr->VolStatus, changer, order, item);
307 Dmsg1(100, "fnextvol=%s\n", mdb->cmd);
308 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
313 numrows = sql_num_rows(mdb);
314 if (item > numrows || item < 1) {
315 Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
321 /* Note, we previously seeked to the row using:
322 * sql_data_seek(mdb, item-1);
323 * but this failed on PostgreSQL, so now we loop
324 * over all the records. This should not be too horrible since
325 * the maximum Volumes we look at in any case is 20.
328 if ((row = sql_fetch_row(mdb)) == NULL) {
329 Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
330 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 mr->Enabled = 1; /* ensured via query */
363 sql_free_result(mdb);
370 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/