2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2015 Kern Sibbald
5 Copyright (C) 2000-2014 Free Software Foundation Europe e.V.
7 The original author of Bacula is Kern Sibbald, with contributions
8 from many others, a complete list can be found in the file AUTHORS.
10 You may use this file and others of this release according to the
11 license defined in the LICENSE file, which includes the Affero General
12 Public License, v3.0 ("AGPLv3") and some additional permissions and
13 terms pursuant to its AGPLv3 Section 7.
15 This notice must be preserved when any source code is
16 conveyed and/or propagated.
18 Bacula(R) is a registered trademark of Kern Sibbald.
21 * Bacula Catalog Database Find record interface routines
23 * Note, generally, these routines are more complicated
24 * that a simple search by name or id. Such simple
25 * request are in get.c
27 * Written by Kern Sibbald, December 2000
33 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL
37 /* -----------------------------------------------------------------------
39 * Generic Routines (or almost generic)
41 * -----------------------------------------------------------------------
45 * Find the most recent successful real end time for a job given.
47 * RealEndTime is returned in etime
48 * Job name is returned in job (MAX_NAME_LENGTH)
50 * Returns: false on failure
51 * true on success, jr is unchanged, but etime and job are set
53 bool BDB::bdb_find_last_job_end_time(JCR *jcr, JOB_DBR *jr, POOLMEM **etime,
57 char ed1[50], ed2[50];
58 char esc_name[MAX_ESCAPE_NAME_LENGTH];
61 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
62 pm_strcpy(etime, "0000-00-00 00:00:00"); /* default */
66 "SELECT RealEndTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
67 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s AND FileSetId=%s "
68 "ORDER BY RealEndTime DESC LIMIT 1", jr->JobType,
69 L_FULL, L_DIFFERENTIAL, L_INCREMENTAL, esc_name,
70 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
72 if (!QueryDB(jcr, cmd)) {
73 Mmsg2(&errmsg, _("Query error for end time request: ERR=%s\nCMD=%s\n"),
77 if ((row = sql_fetch_row()) == NULL) {
79 Mmsg(errmsg, _("No prior backup Job record found.\n"));
82 Dmsg1(100, "Got end time: %s\n", row[0]);
83 pm_strcpy(etime, row[0]);
84 bstrncpy(job, row[1], MAX_NAME_LENGTH);
97 * Find job start time if JobId specified, otherwise
98 * find last Job start time Incremental and Differential saves.
100 * StartTime is returned in stime
101 * Job name is returned in job (MAX_NAME_LENGTH)
103 * Returns: false on failure
104 * true on success, jr is unchanged, but stime and job are set
106 bool BDB::bdb_find_job_start_time(JCR *jcr, JOB_DBR *jr, POOLMEM **stime, char *job)
109 char ed1[50], ed2[50];
110 char esc_name[MAX_ESCAPE_NAME_LENGTH];
113 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
114 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
117 /* If no Id given, we must find corresponding job */
118 if (jr->JobId == 0) {
119 /* Differential is since last Full backup */
121 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
122 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
123 "ORDER BY StartTime DESC LIMIT 1",
124 jr->JobType, L_FULL, esc_name,
125 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
127 if (jr->JobLevel == L_DIFFERENTIAL) {
128 /* SQL cmd for Differential backup already edited above */
130 /* Incremental is since last Full, Incremental, or Differential */
131 } else if (jr->JobLevel == L_INCREMENTAL) {
133 * For an Incremental job, we must first ensure
134 * that a Full backup was done (cmd edited above)
135 * then we do a second look to find the most recent
138 if (!QueryDB(jcr, cmd)) {
139 Mmsg2(&errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
140 sql_strerror(), cmd);
143 if ((row = sql_fetch_row()) == NULL) {
145 Mmsg(errmsg, _("No prior Full backup Job record found.\n"));
149 /* Now edit SQL command for Incremental Job */
151 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
152 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
153 "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
154 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, esc_name,
155 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
157 Mmsg1(errmsg, _("Unknown level=%d\n"), jr->JobLevel);
161 Dmsg1(100, "Submitting: %s\n", cmd);
162 Mmsg(cmd, "SELECT StartTime, Job FROM Job WHERE Job.JobId=%s",
163 edit_int64(jr->JobId, ed1));
166 if (!QueryDB(jcr, cmd)) {
167 pm_strcpy(stime, ""); /* set EOS */
168 Mmsg2(&errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
169 sql_strerror(), cmd);
173 if ((row = sql_fetch_row()) == NULL) {
174 Mmsg2(&errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
175 sql_strerror(), cmd);
179 Dmsg2(100, "Got start time: %s, job: %s\n", row[0], row[1]);
180 pm_strcpy(stime, row[0]);
181 bstrncpy(job, row[1], MAX_NAME_LENGTH);
195 * Find the last job start time for the specified JobLevel
197 * StartTime is returned in stime
198 * Job name is returned in job (MAX_NAME_LENGTH)
200 * Returns: false on failure
201 * true on success, jr is unchanged, but stime and job are set
203 bool BDB::bdb_find_last_job_start_time(JCR *jcr, JOB_DBR *jr,
204 POOLMEM **stime, char *job, int JobLevel)
207 char ed1[50], ed2[50];
208 char esc_name[MAX_ESCAPE_NAME_LENGTH];
211 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
212 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
216 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
217 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
218 "ORDER BY StartTime DESC LIMIT 1",
219 jr->JobType, JobLevel, esc_name,
220 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
221 if (!QueryDB(jcr, cmd)) {
222 Mmsg2(&errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
223 sql_strerror(), cmd);
226 if ((row = sql_fetch_row()) == NULL) {
228 Mmsg(errmsg, _("No prior Full backup Job record found.\n"));
231 Dmsg1(100, "Got start time: %s\n", row[0]);
232 pm_strcpy(stime, row[0]);
233 bstrncpy(job, row[1], MAX_NAME_LENGTH);
245 * Find last failed job since given start-time
246 * it must be either Full or Diff.
248 * Returns: false on failure
249 * true on success, jr is unchanged and stime unchanged
250 * level returned in JobLevel
252 bool BDB::bdb_find_failed_job_since(JCR *jcr, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
255 char ed1[50], ed2[50];
256 char esc_name[MAX_ESCAPE_NAME_LENGTH];
259 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
261 /* Differential is since last Full backup */
263 "SELECT Level FROM Job WHERE JobStatus IN ('%c','%c', '%c', '%c') AND "
264 "Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
265 "AND FileSetId=%s AND StartTime>'%s' "
266 "ORDER BY StartTime DESC LIMIT 1",
267 JS_Canceled, JS_ErrorTerminated, JS_Error, JS_FatalError,
268 jr->JobType, L_FULL, L_DIFFERENTIAL, esc_name,
269 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
271 if (!QueryDB(jcr, cmd)) {
276 if ((row = sql_fetch_row()) == NULL) {
281 JobLevel = (int)*row[0];
290 * Find JobId of last job that ran. E.g. for
291 * VERIFY_CATALOG we want the JobId of the last INIT.
292 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
294 * Returns: true on success
297 bool BDB::bdb_find_last_jobid(JCR *jcr, const char *Name, JOB_DBR *jr)
301 char esc_name[MAX_ESCAPE_NAME_LENGTH];
305 Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
306 if (jr->JobLevel == L_VERIFY_CATALOG) {
307 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
309 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
310 " JobStatus IN ('T','W') AND Name='%s' AND "
311 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
312 L_VERIFY_INIT, esc_name,
313 edit_int64(jr->ClientId, ed1));
314 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
315 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
316 jr->JobLevel == L_VERIFY_DATA ||
317 jr->JobType == JT_BACKUP) {
319 bdb_escape_string(jcr, esc_name, (char*)Name,
320 MIN(strlen(Name), sizeof(esc_name)));
322 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
323 "Name='%s' ORDER BY StartTime DESC LIMIT 1", esc_name);
326 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
327 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
328 edit_int64(jr->ClientId, ed1));
331 Mmsg1(&errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
335 Dmsg1(100, "Query: %s\n", cmd);
336 if (!QueryDB(jcr, cmd)) {
340 if ((row = sql_fetch_row()) == NULL) {
341 Mmsg1(&errmsg, _("No Job found for: %s.\n"), cmd);
347 jr->JobId = str_to_int64(row[0]);
350 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
351 if (jr->JobId <= 0) {
352 Mmsg1(&errmsg, _("No Job found for: %s\n"), cmd);
362 * Find Available Media (Volume) for Pool
364 * Find a Volume for a given PoolId, MediaType, and Status.
366 * Returns: 0 on failure
369 int BDB::bdb_find_next_volume(JCR *jcr, int item, bool InChanger, MEDIA_DBR *mr)
374 char esc_type[MAX_ESCAPE_NAME_LENGTH];
375 char esc_status[MAX_ESCAPE_NAME_LENGTH];
379 bdb_escape_string(jcr, esc_type, mr->MediaType, strlen(mr->MediaType));
380 bdb_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
382 if (item == -1) { /* find oldest volume */
383 /* Find oldest volume */
384 Mmsg(cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
385 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
386 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
387 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
388 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
389 "Enabled,LocationId,RecycleCount,InitialWrite,"
390 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
391 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
392 "'Recycle','Purged','Used','Append') AND Enabled=1 "
393 "ORDER BY LastWritten LIMIT 1",
394 edit_int64(mr->PoolId, ed1), esc_type);
397 POOL_MEM changer(PM_FNAME);
398 POOL_MEM voltype(PM_FNAME);
399 POOL_MEM exclude(PM_FNAME);
400 /* Find next available volume */
402 Mmsg(changer, " AND InChanger=1 AND StorageId=%s ",
403 edit_int64(mr->StorageId, ed1));
405 /* Volumes will be automatically excluded from the query, we just take the
406 * first one of the list
408 if (mr->exclude_list && *mr->exclude_list) {
410 Mmsg(exclude, " AND MediaId NOT IN (%s) ", mr->exclude_list);
412 if (strcmp(mr->VolStatus, "Recycle") == 0 ||
413 strcmp(mr->VolStatus, "Purged") == 0) {
414 order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
416 order = sql_media_order_most_recently_written[bdb_get_type_index()]; /* take most recently written */
418 Mmsg(cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
419 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
420 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
421 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
422 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
423 "Enabled,LocationId,RecycleCount,InitialWrite,"
424 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
425 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
426 "AND VolStatus='%s' "
431 edit_int64(mr->PoolId, ed1), esc_type,
434 changer.c_str(), exclude.c_str(), order, item);
436 Dmsg1(100, "fnextvol=%s\n", cmd);
437 if (!QueryDB(jcr, cmd)) {
442 numrows = sql_num_rows();
443 if (item > numrows || item < 1) {
444 Dmsg2(050, "item=%d got=%d\n", item, numrows);
445 Mmsg2(&errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
451 /* Note, we previously seeked to the row using:
452 * sql_data_seek(item-1);
453 * but this failed on PostgreSQL, so now we loop
454 * over all the records. This should not be too horrible since
455 * the maximum Volumes we look at in any case is 20.
458 if ((row = sql_fetch_row()) == NULL) {
459 Dmsg1(050, "Fail fetch item=%d\n", item+1);
460 Mmsg1(&errmsg, _("No Volume record found for item %d.\n"), item);
467 /* Return fields in Media Record */
468 mr->MediaId = str_to_int64(row[0]);
469 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
470 mr->VolJobs = str_to_int64(row[2]);
471 mr->VolFiles = str_to_int64(row[3]);
472 mr->VolBlocks = str_to_int64(row[4]);
473 mr->VolBytes = str_to_uint64(row[5]);
474 mr->VolMounts = str_to_int64(row[6]);
475 mr->VolErrors = str_to_int64(row[7]);
476 mr->VolWrites = str_to_int64(row[8]);
477 mr->MaxVolBytes = str_to_uint64(row[9]);
478 mr->VolCapacityBytes = str_to_uint64(row[10]);
479 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
480 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
481 mr->PoolId = str_to_int64(row[13]);
482 mr->VolRetention = str_to_uint64(row[14]);
483 mr->VolUseDuration = str_to_uint64(row[15]);
484 mr->MaxVolJobs = str_to_int64(row[16]);
485 mr->MaxVolFiles = str_to_int64(row[17]);
486 mr->Recycle = str_to_int64(row[18]);
487 mr->Slot = str_to_int64(row[19]);
488 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
489 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
490 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
491 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
492 mr->InChanger = str_to_uint64(row[22]);
493 mr->EndFile = str_to_uint64(row[23]);
494 mr->EndBlock = str_to_uint64(row[24]);
495 mr->VolType = str_to_int64(row[25]); /* formerly VolParts */
496 mr->LabelType = str_to_int64(row[26]);
497 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
498 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
499 mr->StorageId = str_to_int64(row[28]);
500 mr->Enabled = str_to_int64(row[29]);
501 mr->LocationId = str_to_int64(row[30]);
502 mr->RecycleCount = str_to_int64(row[31]);
503 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
504 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
505 mr->ScratchPoolId = str_to_int64(row[33]);
506 mr->RecyclePoolId = str_to_int64(row[34]);
507 mr->VolReadTime = str_to_int64(row[35]);
508 mr->VolWriteTime = str_to_int64(row[36]);
509 mr->ActionOnPurge = str_to_int64(row[37]);
514 Dmsg1(050, "Rtn numrows=%d\n", numrows);
518 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */