2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2014 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from many
7 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 Bacula® is a registered trademark of Kern Sibbald.
17 * Bacula Catalog Database Find record interface routines
19 * Note, generally, these routines are more complicated
20 * that a simple search by name or id. Such simple
21 * request are in get.c
23 * Written by Kern Sibbald, December 2000
29 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL
35 /* -----------------------------------------------------------------------
37 * Generic Routines (or almost generic)
39 * -----------------------------------------------------------------------
43 * Find job start time if JobId specified, otherwise
44 * find last Job start time Incremental and Differential saves.
46 * StartTime is returned in stime
47 * Job name is returned in job (MAX_NAME_LENGTH)
49 * Returns: 0 on failure
50 * 1 on success, jr is unchanged, but stime and job are set
53 db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime, char *job)
56 char ed1[50], ed2[50];
57 char esc_name[MAX_ESCAPE_NAME_LENGTH];
60 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
61 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, Job FROM Job WHERE JobStatus IN ('T','W') 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, esc_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, Job FROM Job WHERE JobStatus IN ('T','W') 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, esc_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, Job 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 Dmsg2(100, "Got start time: %s, job: %s\n", row[0], row[1]);
127 pm_strcpy(stime, row[0]);
128 bstrncpy(job, row[1], MAX_NAME_LENGTH);
130 sql_free_result(mdb);
142 * Find the last job start time for the specified JobLevel
144 * StartTime is returned in stime
145 * Job name is returned in job (MAX_NAME_LENGTH)
147 * Returns: false on failure
148 * true on success, jr is unchanged, but stime and job are set
151 db_find_last_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr,
152 POOLMEM **stime, char *job, int JobLevel)
155 char ed1[50], ed2[50];
156 char esc_name[MAX_ESCAPE_NAME_LENGTH];
159 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
160 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
164 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
165 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
166 "ORDER BY StartTime DESC LIMIT 1",
167 jr->JobType, JobLevel, esc_name,
168 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
169 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
170 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
171 sql_strerror(mdb), mdb->cmd);
174 if ((row = sql_fetch_row(mdb)) == NULL) {
175 sql_free_result(mdb);
176 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
179 Dmsg1(100, "Got start time: %s\n", row[0]);
180 pm_strcpy(stime, row[0]);
181 bstrncpy(job, row[1], MAX_NAME_LENGTH);
183 sql_free_result(mdb);
193 * Find last failed job since given start-time
194 * it must be either Full or Diff.
196 * Returns: false on failure
197 * true on success, jr is unchanged and stime unchanged
198 * level returned in JobLevel
201 db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
204 char ed1[50], ed2[50];
205 char esc_name[MAX_ESCAPE_NAME_LENGTH];
208 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
210 /* Differential is since last Full backup */
212 "SELECT Level FROM Job WHERE JobStatus NOT IN ('T','W') AND "
213 "Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
214 "AND FileSetId=%s AND StartTime>'%s' "
215 "ORDER BY StartTime DESC LIMIT 1",
216 jr->JobType, L_FULL, L_DIFFERENTIAL, esc_name,
217 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
219 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
224 if ((row = sql_fetch_row(mdb)) == NULL) {
225 sql_free_result(mdb);
229 JobLevel = (int)*row[0];
230 sql_free_result(mdb);
238 * Find JobId of last job that ran. E.g. for
239 * VERIFY_CATALOG we want the JobId of the last INIT.
240 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
242 * Returns: true on success
246 db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
250 char esc_name[MAX_ESCAPE_NAME_LENGTH];
254 Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
255 if (jr->JobLevel == L_VERIFY_CATALOG) {
256 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
258 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
259 " JobStatus IN ('T','W') AND Name='%s' AND "
260 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
261 L_VERIFY_INIT, esc_name,
262 edit_int64(jr->ClientId, ed1));
263 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
264 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
265 jr->JobType == JT_BACKUP) {
267 mdb->db_escape_string(jcr, esc_name, (char*)Name,
268 MIN(strlen(Name), sizeof(esc_name)));
270 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
271 "Name='%s' ORDER BY StartTime DESC LIMIT 1", esc_name);
274 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
275 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
276 edit_int64(jr->ClientId, ed1));
279 Mmsg1(&mdb->errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
283 Dmsg1(100, "Query: %s\n", mdb->cmd);
284 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
288 if ((row = sql_fetch_row(mdb)) == NULL) {
289 Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
290 sql_free_result(mdb);
295 jr->JobId = str_to_int64(row[0]);
296 sql_free_result(mdb);
298 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
299 if (jr->JobId <= 0) {
300 Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
310 * Find Available Media (Volume) for Pool
312 * Find a Volume for a given PoolId, MediaType, and Status.
314 * Returns: 0 on failure
318 db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr)
323 char esc_type[MAX_ESCAPE_NAME_LENGTH];
324 char esc_status[MAX_ESCAPE_NAME_LENGTH];
328 mdb->db_escape_string(jcr, esc_type, mr->MediaType, strlen(mr->MediaType));
329 mdb->db_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
331 if (item == -1) { /* find oldest volume */
332 /* Find oldest volume */
333 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
334 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
335 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
336 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
337 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
338 "Enabled,LocationId,RecycleCount,InitialWrite,"
339 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
340 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
341 "'Recycle','Purged','Used','Append') AND Enabled=1 "
342 "ORDER BY LastWritten LIMIT 1",
343 edit_int64(mr->PoolId, ed1), esc_type);
346 POOL_MEM changer(PM_FNAME);
347 /* Find next available volume */
349 * replace switch with
350 * if (StorageId == 0)
354 Mmsg(changer, " AND InChanger=1 AND StorageId=%s ",
355 edit_int64(mr->StorageId, ed1));
357 if (strcmp(mr->VolStatus, "Recycle") == 0 ||
358 strcmp(mr->VolStatus, "Purged") == 0) {
359 order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
361 order = sql_media_order_most_recently_written[db_get_type_index(mdb)]; /* take most recently written */
363 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
364 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
365 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
366 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
367 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
368 "Enabled,LocationId,RecycleCount,InitialWrite,"
369 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
370 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
371 "AND VolStatus='%s' "
374 edit_int64(mr->PoolId, ed1), esc_type,
375 esc_status, changer.c_str(), order, item);
377 Dmsg1(100, "fnextvol=%s\n", mdb->cmd);
378 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
383 numrows = sql_num_rows(mdb);
384 if (item > numrows || item < 1) {
385 Dmsg2(050, "item=%d got=%d\n", item, numrows);
386 Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
392 /* Note, we previously seeked to the row using:
393 * sql_data_seek(mdb, item-1);
394 * but this failed on PostgreSQL, so now we loop
395 * over all the records. This should not be too horrible since
396 * the maximum Volumes we look at in any case is 20.
399 if ((row = sql_fetch_row(mdb)) == NULL) {
400 Dmsg1(050, "Fail fetch item=%d\n", item+1);
401 Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
402 sql_free_result(mdb);
408 /* Return fields in Media Record */
409 mr->MediaId = str_to_int64(row[0]);
410 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
411 mr->VolJobs = str_to_int64(row[2]);
412 mr->VolFiles = str_to_int64(row[3]);
413 mr->VolBlocks = str_to_int64(row[4]);
414 mr->VolBytes = str_to_uint64(row[5]);
415 mr->VolMounts = str_to_int64(row[6]);
416 mr->VolErrors = str_to_int64(row[7]);
417 mr->VolWrites = str_to_int64(row[8]);
418 mr->MaxVolBytes = str_to_uint64(row[9]);
419 mr->VolCapacityBytes = str_to_uint64(row[10]);
420 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
421 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
422 mr->PoolId = str_to_int64(row[13]);
423 mr->VolRetention = str_to_uint64(row[14]);
424 mr->VolUseDuration = str_to_uint64(row[15]);
425 mr->MaxVolJobs = str_to_int64(row[16]);
426 mr->MaxVolFiles = str_to_int64(row[17]);
427 mr->Recycle = str_to_int64(row[18]);
428 mr->Slot = str_to_int64(row[19]);
429 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
430 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
431 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
432 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
433 mr->InChanger = str_to_uint64(row[22]);
434 mr->EndFile = str_to_uint64(row[23]);
435 mr->EndBlock = str_to_uint64(row[24]);
436 mr->VolParts = str_to_int64(row[25]);
437 mr->LabelType = str_to_int64(row[26]);
438 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
439 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
440 mr->StorageId = str_to_int64(row[28]);
441 mr->Enabled = str_to_int64(row[29]);
442 mr->LocationId = str_to_int64(row[30]);
443 mr->RecycleCount = str_to_int64(row[31]);
444 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
445 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
446 mr->ScratchPoolId = str_to_int64(row[33]);
447 mr->RecyclePoolId = str_to_int64(row[34]);
448 mr->VolReadTime = str_to_int64(row[35]);
449 mr->VolWriteTime = str_to_int64(row[36]);
450 mr->ActionOnPurge = str_to_int64(row[37]);
452 sql_free_result(mdb);
455 Dmsg1(050, "Rtn numrows=%d\n", numrows);
459 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */