2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2017 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
31 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL
35 /* -----------------------------------------------------------------------
37 * Generic Routines (or almost generic)
39 * -----------------------------------------------------------------------
43 * Find the most recent successful real end time for a job given.
45 * RealEndTime is returned in etime
46 * Job name is returned in job (MAX_NAME_LENGTH)
48 * Returns: false on failure
49 * true on success, jr is unchanged, but etime and job are set
51 bool BDB::bdb_find_last_job_end_time(JCR *jcr, JOB_DBR *jr, POOLMEM **etime,
55 char ed1[50], ed2[50];
56 char esc_name[MAX_ESCAPE_NAME_LENGTH];
59 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
60 pm_strcpy(etime, "0000-00-00 00:00:00"); /* default */
64 "SELECT RealEndTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
65 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s AND FileSetId=%s "
66 "ORDER BY RealEndTime DESC LIMIT 1", jr->JobType,
67 L_FULL, L_DIFFERENTIAL, L_INCREMENTAL, esc_name,
68 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
70 if (!QueryDB(jcr, cmd)) {
71 Mmsg2(&errmsg, _("Query error for end time request: ERR=%s\nCMD=%s\n"),
75 if ((row = sql_fetch_row()) == NULL) {
77 Mmsg(errmsg, _("No prior backup Job record found.\n"));
80 Dmsg1(100, "Got end time: %s\n", row[0]);
81 pm_strcpy(etime, row[0]);
82 bstrncpy(job, row[1], MAX_NAME_LENGTH);
95 * Find job start time if JobId specified, otherwise
96 * find last Job start time Incremental and Differential saves.
98 * StartTime is returned in stime
99 * Job name is returned in job (MAX_NAME_LENGTH)
101 * Returns: false on failure
102 * true on success, jr is unchanged, but stime and job are set
104 bool BDB::bdb_find_job_start_time(JCR *jcr, JOB_DBR *jr, POOLMEM **stime, char *job)
107 char ed1[50], ed2[50];
108 char esc_name[MAX_ESCAPE_NAME_LENGTH];
111 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
112 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
115 /* If no Id given, we must find corresponding job */
116 if (jr->JobId == 0) {
117 /* Differential is since last Full backup */
119 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
120 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
121 "ORDER BY StartTime DESC LIMIT 1",
122 jr->JobType, L_FULL, esc_name,
123 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
125 if (jr->JobLevel == L_DIFFERENTIAL) {
126 /* SQL cmd for Differential backup already edited above */
128 /* Incremental is since last Full, Incremental, or Differential */
129 } else if (jr->JobLevel == L_INCREMENTAL) {
131 * For an Incremental job, we must first ensure
132 * that a Full backup was done (cmd edited above)
133 * then we do a second look to find the most recent
136 if (!QueryDB(jcr, cmd)) {
137 Mmsg2(&errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
138 sql_strerror(), cmd);
141 if ((row = sql_fetch_row()) == NULL) {
143 Mmsg(errmsg, _("No prior Full backup Job record found.\n"));
147 /* Now edit SQL command for Incremental Job */
149 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
150 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
151 "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
152 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, esc_name,
153 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
155 Mmsg1(errmsg, _("Unknown level=%d\n"), jr->JobLevel);
159 Dmsg1(100, "Submitting: %s\n", cmd);
160 Mmsg(cmd, "SELECT StartTime, Job FROM Job WHERE Job.JobId=%s",
161 edit_int64(jr->JobId, ed1));
164 if (!QueryDB(jcr, cmd)) {
165 pm_strcpy(stime, ""); /* set EOS */
166 Mmsg2(&errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
167 sql_strerror(), cmd);
171 if ((row = sql_fetch_row()) == NULL) {
172 Mmsg2(&errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
173 sql_strerror(), cmd);
177 Dmsg2(100, "Got start time: %s, job: %s\n", row[0], row[1]);
178 pm_strcpy(stime, row[0]);
179 bstrncpy(job, row[1], MAX_NAME_LENGTH);
193 * Find the last job start time for the specified JobLevel
195 * StartTime is returned in stime
196 * Job name is returned in job (MAX_NAME_LENGTH)
198 * Returns: false on failure
199 * true on success, jr is unchanged, but stime and job are set
201 bool BDB::bdb_find_last_job_start_time(JCR *jcr, JOB_DBR *jr,
202 POOLMEM **stime, char *job, int JobLevel)
205 char ed1[50], ed2[50];
206 char esc_name[MAX_ESCAPE_NAME_LENGTH];
209 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
210 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
214 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
215 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
216 "ORDER BY StartTime DESC LIMIT 1",
217 jr->JobType, JobLevel, esc_name,
218 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
219 if (!QueryDB(jcr, cmd)) {
220 Mmsg2(&errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
221 sql_strerror(), cmd);
224 if ((row = sql_fetch_row()) == NULL) {
226 Mmsg(errmsg, _("No prior Full backup Job record found.\n"));
229 Dmsg1(100, "Got start time: %s\n", row[0]);
230 pm_strcpy(stime, row[0]);
231 bstrncpy(job, row[1], MAX_NAME_LENGTH);
243 * Find last failed job since given start-time
244 * it must be either Full or Diff.
246 * Returns: false on failure
247 * true on success, jr is unchanged and stime unchanged
248 * level returned in JobLevel
250 bool BDB::bdb_find_failed_job_since(JCR *jcr, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
253 char ed1[50], ed2[50];
254 char esc_name[MAX_ESCAPE_NAME_LENGTH];
257 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
259 /* Differential is since last Full backup */
261 "SELECT Level FROM Job WHERE JobStatus IN ('%c','%c', '%c', '%c') AND "
262 "Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
263 "AND FileSetId=%s AND StartTime>'%s' "
264 "ORDER BY StartTime DESC LIMIT 1",
265 JS_Canceled, JS_ErrorTerminated, JS_Error, JS_FatalError,
266 jr->JobType, L_FULL, L_DIFFERENTIAL, esc_name,
267 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
269 if (!QueryDB(jcr, cmd)) {
274 if ((row = sql_fetch_row()) == NULL) {
279 JobLevel = (int)*row[0];
288 * Find JobId of last job that ran. E.g. for
289 * VERIFY_CATALOG we want the JobId of the last INIT.
290 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
292 * Returns: true on success
295 bool BDB::bdb_find_last_jobid(JCR *jcr, const char *Name, JOB_DBR *jr)
299 char esc_name[MAX_ESCAPE_NAME_LENGTH];
303 Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
304 if (jr->JobLevel == L_VERIFY_CATALOG) {
305 bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
307 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
308 " JobStatus IN ('T','W') AND Name='%s' AND "
309 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
310 L_VERIFY_INIT, esc_name,
311 edit_int64(jr->ClientId, ed1));
312 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
313 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
314 jr->JobLevel == L_VERIFY_DATA ||
315 jr->JobType == JT_BACKUP) {
317 bdb_escape_string(jcr, esc_name, (char*)Name,
318 MIN(strlen(Name), sizeof(esc_name)));
320 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
321 "Name='%s' ORDER BY StartTime DESC LIMIT 1", esc_name);
324 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
325 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
326 edit_int64(jr->ClientId, ed1));
329 Mmsg1(&errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
333 Dmsg1(100, "Query: %s\n", cmd);
334 if (!QueryDB(jcr, cmd)) {
338 if ((row = sql_fetch_row()) == NULL) {
339 Mmsg1(&errmsg, _("No Job found for: %s.\n"), cmd);
345 jr->JobId = str_to_int64(row[0]);
348 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
349 if (jr->JobId <= 0) {
350 Mmsg1(&errmsg, _("No Job found for: %s\n"), cmd);
360 * Find Available Media (Volume) for Pool
362 * Find a Volume for a given PoolId, MediaType, and Status.
364 * Returns: 0 on failure
367 int BDB::bdb_find_next_volume(JCR *jcr, int item, bool InChanger, MEDIA_DBR *mr)
372 char esc_type[MAX_ESCAPE_NAME_LENGTH];
373 char esc_status[MAX_ESCAPE_NAME_LENGTH];
377 bdb_escape_string(jcr, esc_type, mr->MediaType, strlen(mr->MediaType));
378 bdb_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
380 if (item == -1) { /* find oldest volume */
381 /* Find oldest volume */
382 Mmsg(cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
383 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
384 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
385 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
386 "EndFile,EndBlock,VolType,VolParts,VolCloudParts,LastPartBytes,"
387 "LabelType,LabelDate,StorageId,"
388 "Enabled,LocationId,RecycleCount,InitialWrite,"
389 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge,CacheRetention "
390 "FROM Media WHERE PoolId=%s AND MediaType='%s' "
391 " AND (VolStatus IN ('Full', 'Append', 'Used') OR (VolStatus IN ('Recycle', 'Purged', 'Used') AND Recycle=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 * replace switch with
403 * if (StorageId == 0)
405 * else use mr->sid_group; but it must be set!!!
408 ASSERT(mr->sid_group);
410 Mmsg(changer, " AND InChanger=1 AND StorageId IN (%s) ",
413 Mmsg(changer, " AND InChanger=1 AND StorageId=%s ",
414 edit_int64(mr->StorageId, ed1));
417 /* Volumes will be automatically excluded from the query, we just take the
418 * first one of the list
420 if (mr->exclude_list && *mr->exclude_list) {
422 Mmsg(exclude, " AND MediaId NOT IN (%s) ", mr->exclude_list);
424 if (strcmp(mr->VolStatus, "Recycle") == 0 ||
425 strcmp(mr->VolStatus, "Purged") == 0) {
426 order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
428 order = sql_media_order_most_recently_written[bdb_get_type_index()]; /* take most recently written */
430 if (mr->VolType == 0) {
433 Mmsg(voltype, "AND VolType IN (0,%d)", mr->VolType);
435 Mmsg(cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
436 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
437 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
438 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
439 "EndFile,EndBlock,VolType,VolParts,VolCloudParts,LastPartBytes,"
440 "LabelType,LabelDate,StorageId,"
441 "Enabled,LocationId,RecycleCount,InitialWrite,"
442 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge,CacheRetention "
443 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
444 "AND VolStatus='%s' "
449 edit_int64(mr->PoolId, ed1), esc_type,
452 changer.c_str(), exclude.c_str(), order, item);
454 Dmsg1(100, "fnextvol=%s\n", cmd);
455 if (!QueryDB(jcr, cmd)) {
460 numrows = sql_num_rows();
461 if (item > numrows || item < 1) {
462 Dmsg2(050, "item=%d got=%d\n", item, numrows);
463 Mmsg2(&errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
469 /* Note, we previously seeked to the row using:
470 * sql_data_seek(item-1);
471 * but this failed on PostgreSQL, so now we loop
472 * over all the records. This should not be too horrible since
473 * the maximum Volumes we look at in any case is 20.
476 if ((row = sql_fetch_row()) == NULL) {
477 Dmsg1(050, "Fail fetch item=%d\n", item+1);
478 Mmsg1(&errmsg, _("No Volume record found for item %d.\n"), item);
485 /* Return fields in Media Record */
486 mr->MediaId = str_to_int64(row[0]);
487 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
488 mr->VolJobs = str_to_int64(row[2]);
489 mr->VolFiles = str_to_int64(row[3]);
490 mr->VolBlocks = str_to_int64(row[4]);
491 mr->VolBytes = str_to_uint64(row[5]);
492 mr->VolMounts = str_to_int64(row[6]);
493 mr->VolErrors = str_to_int64(row[7]);
494 mr->VolWrites = str_to_int64(row[8]);
495 mr->MaxVolBytes = str_to_uint64(row[9]);
496 mr->VolCapacityBytes = str_to_uint64(row[10]);
497 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
498 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
499 mr->PoolId = str_to_int64(row[13]);
500 mr->VolRetention = str_to_uint64(row[14]);
501 mr->VolUseDuration = str_to_uint64(row[15]);
502 mr->MaxVolJobs = str_to_int64(row[16]);
503 mr->MaxVolFiles = str_to_int64(row[17]);
504 mr->Recycle = str_to_int64(row[18]);
505 mr->Slot = str_to_int64(row[19]);
506 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
507 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
508 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
509 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
510 mr->InChanger = str_to_uint64(row[22]);
511 mr->EndFile = str_to_uint64(row[23]);
512 mr->EndBlock = str_to_uint64(row[24]);
513 mr->VolType = str_to_int64(row[25]);
514 mr->VolParts = str_to_int64(row[26]);
515 mr->VolCloudParts = str_to_int64(row[27]);
516 mr->LastPartBytes = str_to_int64(row[28]);
517 mr->LabelType = str_to_int64(row[29]);
518 bstrncpy(mr->cLabelDate, row[30]!=NULL?row[30]:"", sizeof(mr->cLabelDate));
519 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
520 mr->StorageId = str_to_int64(row[31]);
521 mr->Enabled = str_to_int64(row[32]);
522 mr->LocationId = str_to_int64(row[33]);
523 mr->RecycleCount = str_to_int64(row[34]);
524 bstrncpy(mr->cInitialWrite, row[35]!=NULL?row[35]:"", sizeof(mr->cInitialWrite));
525 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
526 mr->ScratchPoolId = str_to_int64(row[36]);
527 mr->RecyclePoolId = str_to_int64(row[37]);
528 mr->VolReadTime = str_to_int64(row[38]);
529 mr->VolWriteTime = str_to_int64(row[39]);
530 mr->ActionOnPurge = str_to_int64(row[40]);
531 mr->CacheRetention = str_to_int64(row[41]);
536 Dmsg1(050, "Rtn numrows=%d\n", numrows);
540 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */