2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2016 Kern Sibbald
6 The original author of Bacula is Kern Sibbald, with contributions
7 from many others, a complete list can be found in the file AUTHORS.
9 You may use this file and others of this release according to the
10 license defined in the LICENSE file, which includes the Affero General
11 Public License, v3.0 ("AGPLv3") and some additional permissions and
12 terms pursuant to its AGPLv3 Section 7.
14 This notice must be preserved when any source code is
15 conveyed and/or propagated.
17 Bacula(R) is a registered trademark of Kern Sibbald.
20 * Bacula Catalog Database Find record interface routines
22 * Note, generally, these routines are more complicated
23 * that a simple search by name or id. Such simple
24 * request are in get.c
26 * Written by Kern Sibbald, December 2000
32 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL
36 /* -----------------------------------------------------------------------
38 * Generic Routines (or almost generic)
40 * -----------------------------------------------------------------------
44 * Find the most recent successful real end time for a job given.
46 * RealEndTime is returned in etime
47 * Job name is returned in job (MAX_NAME_LENGTH)
49 * Returns: false on failure
50 * true on success, jr is unchanged, but etime and job are set
52 bool BDB::bdb_find_last_job_end_time(JCR *jcr, JOB_DBR *jr, POOLMEM **etime,
56 char ed1[50], ed2[50];
57 char esc_name[MAX_ESCAPE_NAME_LENGTH];
60 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
61 pm_strcpy(etime, "0000-00-00 00:00:00"); /* default */
65 "SELECT RealEndTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
66 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s AND FileSetId=%s "
67 "ORDER BY RealEndTime DESC LIMIT 1", jr->JobType,
68 L_FULL, L_DIFFERENTIAL, L_INCREMENTAL, esc_name,
69 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
71 if (!QueryDB(jcr, cmd)) {
72 Mmsg2(&errmsg, _("Query error for end time request: ERR=%s\nCMD=%s\n"),
76 if ((row = sql_fetch_row()) == NULL) {
78 Mmsg(errmsg, _("No prior backup Job record found.\n"));
81 Dmsg1(100, "Got end time: %s\n", row[0]);
82 pm_strcpy(etime, row[0]);
83 bstrncpy(job, row[1], MAX_NAME_LENGTH);
96 * Find job start time if JobId specified, otherwise
97 * find last Job start time Incremental and Differential saves.
99 * StartTime is returned in stime
100 * Job name is returned in job (MAX_NAME_LENGTH)
102 * Returns: false on failure
103 * true on success, jr is unchanged, but stime and job are set
105 bool BDB::bdb_find_job_start_time(JCR *jcr, JOB_DBR *jr, POOLMEM **stime, char *job)
108 char ed1[50], ed2[50];
109 char esc_name[MAX_ESCAPE_NAME_LENGTH];
112 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
113 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
116 /* If no Id given, we must find corresponding job */
117 if (jr->JobId == 0) {
118 /* Differential is since last Full backup */
120 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
121 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
122 "ORDER BY StartTime DESC LIMIT 1",
123 jr->JobType, L_FULL, esc_name,
124 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
126 if (jr->JobLevel == L_DIFFERENTIAL) {
127 /* SQL cmd for Differential backup already edited above */
129 /* Incremental is since last Full, Incremental, or Differential */
130 } else if (jr->JobLevel == L_INCREMENTAL) {
132 * For an Incremental job, we must first ensure
133 * that a Full backup was done (cmd edited above)
134 * then we do a second look to find the most recent
137 if (!QueryDB(jcr, cmd)) {
138 Mmsg2(&errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
139 sql_strerror(), cmd);
142 if ((row = sql_fetch_row()) == NULL) {
144 Mmsg(errmsg, _("No prior Full backup Job record found.\n"));
148 /* Now edit SQL command for Incremental Job */
150 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
151 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
152 "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
153 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, esc_name,
154 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
156 Mmsg1(errmsg, _("Unknown level=%d\n"), jr->JobLevel);
160 Dmsg1(100, "Submitting: %s\n", cmd);
161 Mmsg(cmd, "SELECT StartTime, Job FROM Job WHERE Job.JobId=%s",
162 edit_int64(jr->JobId, ed1));
165 if (!QueryDB(jcr, cmd)) {
166 pm_strcpy(stime, ""); /* set EOS */
167 Mmsg2(&errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
168 sql_strerror(), cmd);
172 if ((row = sql_fetch_row()) == NULL) {
173 Mmsg2(&errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
174 sql_strerror(), cmd);
178 Dmsg2(100, "Got start time: %s, job: %s\n", row[0], row[1]);
179 pm_strcpy(stime, row[0]);
180 bstrncpy(job, row[1], MAX_NAME_LENGTH);
194 * Find the last job start time for the specified JobLevel
196 * StartTime is returned in stime
197 * Job name is returned in job (MAX_NAME_LENGTH)
199 * Returns: false on failure
200 * true on success, jr is unchanged, but stime and job are set
202 bool BDB::bdb_find_last_job_start_time(JCR *jcr, JOB_DBR *jr,
203 POOLMEM **stime, char *job, int JobLevel)
206 char ed1[50], ed2[50];
207 char esc_name[MAX_ESCAPE_NAME_LENGTH];
210 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
211 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
215 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
216 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
217 "ORDER BY StartTime DESC LIMIT 1",
218 jr->JobType, JobLevel, esc_name,
219 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
220 if (!QueryDB(jcr, cmd)) {
221 Mmsg2(&errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
222 sql_strerror(), cmd);
225 if ((row = sql_fetch_row()) == NULL) {
227 Mmsg(errmsg, _("No prior Full backup Job record found.\n"));
230 Dmsg1(100, "Got start time: %s\n", row[0]);
231 pm_strcpy(stime, row[0]);
232 bstrncpy(job, row[1], MAX_NAME_LENGTH);
244 * Find last failed job since given start-time
245 * it must be either Full or Diff.
247 * Returns: false on failure
248 * true on success, jr is unchanged and stime unchanged
249 * level returned in JobLevel
251 bool BDB::bdb_find_failed_job_since(JCR *jcr, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
254 char ed1[50], ed2[50];
255 char esc_name[MAX_ESCAPE_NAME_LENGTH];
258 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
260 /* Differential is since last Full backup */
262 "SELECT Level FROM Job WHERE JobStatus IN ('%c','%c', '%c', '%c') AND "
263 "Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
264 "AND FileSetId=%s AND StartTime>'%s' "
265 "ORDER BY StartTime DESC LIMIT 1",
266 JS_Canceled, JS_ErrorTerminated, JS_Error, JS_FatalError,
267 jr->JobType, L_FULL, L_DIFFERENTIAL, esc_name,
268 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
270 if (!QueryDB(jcr, cmd)) {
275 if ((row = sql_fetch_row()) == NULL) {
280 JobLevel = (int)*row[0];
289 * Find JobId of last job that ran. E.g. for
290 * VERIFY_CATALOG we want the JobId of the last INIT.
291 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
293 * Returns: true on success
296 bool BDB::bdb_find_last_jobid(JCR *jcr, const char *Name, JOB_DBR *jr)
300 char esc_name[MAX_ESCAPE_NAME_LENGTH];
304 Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
305 if (jr->JobLevel == L_VERIFY_CATALOG) {
306 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
308 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
309 " JobStatus IN ('T','W') AND Name='%s' AND "
310 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
311 L_VERIFY_INIT, esc_name,
312 edit_int64(jr->ClientId, ed1));
313 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
314 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
315 jr->JobLevel == L_VERIFY_DATA ||
316 jr->JobType == JT_BACKUP) {
318 bdb_escape_string(jcr, esc_name, (char*)Name,
319 MIN(strlen(Name), sizeof(esc_name)));
321 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
322 "Name='%s' ORDER BY StartTime DESC LIMIT 1", esc_name);
325 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
326 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
327 edit_int64(jr->ClientId, ed1));
330 Mmsg1(&errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
334 Dmsg1(100, "Query: %s\n", cmd);
335 if (!QueryDB(jcr, cmd)) {
339 if ((row = sql_fetch_row()) == NULL) {
340 Mmsg1(&errmsg, _("No Job found for: %s.\n"), cmd);
346 jr->JobId = str_to_int64(row[0]);
349 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
350 if (jr->JobId <= 0) {
351 Mmsg1(&errmsg, _("No Job found for: %s\n"), cmd);
361 * Find Available Media (Volume) for Pool
363 * Find a Volume for a given PoolId, MediaType, and Status.
365 * Returns: 0 on failure
368 int BDB::bdb_find_next_volume(JCR *jcr, int item, bool InChanger, MEDIA_DBR *mr)
373 char esc_type[MAX_ESCAPE_NAME_LENGTH];
374 char esc_status[MAX_ESCAPE_NAME_LENGTH];
378 bdb_escape_string(jcr, esc_type, mr->MediaType, strlen(mr->MediaType));
379 bdb_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
381 if (item == -1) { /* find oldest volume */
382 /* Find oldest volume */
383 Mmsg(cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
384 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
385 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
386 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
387 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
388 "Enabled,LocationId,RecycleCount,InitialWrite,"
389 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
390 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
391 "'Recycle','Purged','Used','Append') AND Enabled=1 "
392 "ORDER BY LastWritten LIMIT 1",
393 edit_int64(mr->PoolId, ed1), esc_type);
396 POOL_MEM changer(PM_FNAME);
397 POOL_MEM voltype(PM_FNAME);
398 POOL_MEM exclude(PM_FNAME);
399 /* Find next available volume */
401 Mmsg(changer, " AND InChanger=1 AND StorageId=%s ",
402 edit_int64(mr->StorageId, ed1));
404 /* Volumes will be automatically excluded from the query, we just take the
405 * first one of the list
407 if (mr->exclude_list && *mr->exclude_list) {
409 Mmsg(exclude, " AND MediaId NOT IN (%s) ", mr->exclude_list);
411 if (strcmp(mr->VolStatus, "Recycle") == 0 ||
412 strcmp(mr->VolStatus, "Purged") == 0) {
413 order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
415 order = sql_media_order_most_recently_written[bdb_get_type_index()]; /* take most recently written */
417 Mmsg(cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
418 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
419 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
420 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
421 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
422 "Enabled,LocationId,RecycleCount,InitialWrite,"
423 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
424 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
425 "AND VolStatus='%s' "
430 edit_int64(mr->PoolId, ed1), esc_type,
433 changer.c_str(), exclude.c_str(), order, item);
435 Dmsg1(100, "fnextvol=%s\n", cmd);
436 if (!QueryDB(jcr, cmd)) {
441 numrows = sql_num_rows();
442 if (item > numrows || item < 1) {
443 Dmsg2(050, "item=%d got=%d\n", item, numrows);
444 Mmsg2(&errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
450 /* Note, we previously seeked to the row using:
451 * sql_data_seek(item-1);
452 * but this failed on PostgreSQL, so now we loop
453 * over all the records. This should not be too horrible since
454 * the maximum Volumes we look at in any case is 20.
457 if ((row = sql_fetch_row()) == NULL) {
458 Dmsg1(050, "Fail fetch item=%d\n", item+1);
459 Mmsg1(&errmsg, _("No Volume record found for item %d.\n"), item);
466 /* Return fields in Media Record */
467 mr->MediaId = str_to_int64(row[0]);
468 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
469 mr->VolJobs = str_to_int64(row[2]);
470 mr->VolFiles = str_to_int64(row[3]);
471 mr->VolBlocks = str_to_int64(row[4]);
472 mr->VolBytes = str_to_uint64(row[5]);
473 mr->VolMounts = str_to_int64(row[6]);
474 mr->VolErrors = str_to_int64(row[7]);
475 mr->VolWrites = str_to_int64(row[8]);
476 mr->MaxVolBytes = str_to_uint64(row[9]);
477 mr->VolCapacityBytes = str_to_uint64(row[10]);
478 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
479 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
480 mr->PoolId = str_to_int64(row[13]);
481 mr->VolRetention = str_to_uint64(row[14]);
482 mr->VolUseDuration = str_to_uint64(row[15]);
483 mr->MaxVolJobs = str_to_int64(row[16]);
484 mr->MaxVolFiles = str_to_int64(row[17]);
485 mr->Recycle = str_to_int64(row[18]);
486 mr->Slot = str_to_int64(row[19]);
487 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
488 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
489 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
490 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
491 mr->InChanger = str_to_uint64(row[22]);
492 mr->EndFile = str_to_uint64(row[23]);
493 mr->EndBlock = str_to_uint64(row[24]);
494 mr->VolType = str_to_int64(row[25]); /* formerly VolParts */
495 mr->LabelType = str_to_int64(row[26]);
496 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
497 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
498 mr->StorageId = str_to_int64(row[28]);
499 mr->Enabled = str_to_int64(row[29]);
500 mr->LocationId = str_to_int64(row[30]);
501 mr->RecycleCount = str_to_int64(row[31]);
502 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
503 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
504 mr->ScratchPoolId = str_to_int64(row[33]);
505 mr->RecyclePoolId = str_to_int64(row[34]);
506 mr->VolReadTime = str_to_int64(row[35]);
507 mr->VolWriteTime = str_to_int64(row[36]);
508 mr->ActionOnPurge = str_to_int64(row[37]);
513 Dmsg1(050, "Rtn numrows=%d\n", numrows);
517 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */