2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2010 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version two of the GNU General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
29 * Bacula Catalog Database Find record interface routines
31 * Note, generally, these routines are more complicated
32 * that a simple search by name or id. Such simple
33 * request are in get.c
35 * Kern Sibbald, December 2000
40 /* The following is necessary so that we do not include
41 * the dummy external definition of DB.
43 #define __SQL_C /* indicate that this is sql.c */
48 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI
50 /* -----------------------------------------------------------------------
52 * Generic Routines (or almost generic)
54 * -----------------------------------------------------------------------
58 * Find job start time if JobId specified, otherwise
59 * find last Job start time Incremental and Differential saves.
61 * StartTime is returned in stime
63 * Returns: 0 on failure
64 * 1 on success, jr is unchanged, but stime is set
67 db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime)
70 char ed1[50], ed2[50];
74 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
75 /* If no Id given, we must find corresponding job */
77 /* Differential is since last Full backup */
80 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
81 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
82 "ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY",
83 jr->JobType, L_FULL, jr->Name,
84 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
87 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
88 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
89 "ORDER BY StartTime DESC LIMIT 1",
90 jr->JobType, L_FULL, jr->Name,
91 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
94 if (jr->JobLevel == L_DIFFERENTIAL) {
95 /* SQL cmd for Differential backup already edited above */
97 /* Incremental is since last Full, Incremental, or Differential */
98 } else if (jr->JobLevel == L_INCREMENTAL) {
100 * For an Incremental job, we must first ensure
101 * that a Full backup was done (cmd edited above)
102 * then we do a second look to find the most recent
105 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
106 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
107 sql_strerror(mdb), mdb->cmd);
110 if ((row = sql_fetch_row(mdb)) == NULL) {
111 sql_free_result(mdb);
112 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
115 sql_free_result(mdb);
116 /* Now edit SQL command for Incremental Job */
119 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
120 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
121 "AND FileSetId=%s ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY",
122 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, jr->Name,
123 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
126 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
127 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
128 "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
129 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, jr->Name,
130 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
133 Mmsg1(mdb->errmsg, _("Unknown level=%d\n"), jr->JobLevel);
137 Dmsg1(100, "Submitting: %s\n", mdb->cmd);
138 Mmsg(mdb->cmd, "SELECT StartTime FROM Job WHERE Job.JobId=%s",
139 edit_int64(jr->JobId, ed1));
142 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
143 pm_strcpy(stime, ""); /* set EOS */
144 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
145 sql_strerror(mdb), mdb->cmd);
149 if ((row = sql_fetch_row(mdb)) == NULL) {
150 Mmsg2(&mdb->errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
151 sql_strerror(mdb), mdb->cmd);
152 sql_free_result(mdb);
155 Dmsg1(100, "Got start time: %s\n", row[0]);
156 pm_strcpy(stime, row[0]);
158 sql_free_result(mdb);
170 * Find the last job start time for the specified JobLevel
172 * StartTime is returned in stime
174 * Returns: false on failure
175 * true on success, jr is unchanged, but stime is set
178 db_find_last_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime, int JobLevel)
181 char ed1[50], ed2[50];
185 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
189 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
190 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
191 "ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY",
192 jr->JobType, JobLevel, jr->Name,
193 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
196 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
197 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
198 "ORDER BY StartTime DESC LIMIT 1",
199 jr->JobType, JobLevel, jr->Name,
200 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
202 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
203 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
204 sql_strerror(mdb), mdb->cmd);
207 if ((row = sql_fetch_row(mdb)) == NULL) {
208 sql_free_result(mdb);
209 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
212 Dmsg1(100, "Got start time: %s\n", row[0]);
213 pm_strcpy(stime, row[0]);
214 sql_free_result(mdb);
224 * Find last failed job since given start-time
225 * it must be either Full or Diff.
227 * Returns: false on failure
228 * true on success, jr is unchanged and stime unchanged
229 * level returned in JobLevel
232 db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
235 char ed1[50], ed2[50];
238 /* Differential is since last Full backup */
241 "SELECT Level FROM Job WHERE JobStatus NOT IN ('T','W') AND "
242 "Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
243 "AND FileSetId=%s AND StartTime>'%s' "
244 "ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY",
245 jr->JobType, L_FULL, L_DIFFERENTIAL, jr->Name,
246 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
250 "SELECT Level FROM Job WHERE JobStatus NOT IN ('T','W') AND "
251 "Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
252 "AND FileSetId=%s AND StartTime>'%s' "
253 "ORDER BY StartTime DESC LIMIT 1",
254 jr->JobType, L_FULL, L_DIFFERENTIAL, jr->Name,
255 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
258 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
263 if ((row = sql_fetch_row(mdb)) == NULL) {
264 sql_free_result(mdb);
268 JobLevel = (int)*row[0];
269 sql_free_result(mdb);
277 * Find JobId of last job that ran. E.g. for
278 * VERIFY_CATALOG we want the JobId of the last INIT.
279 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
281 * Returns: true on success
285 db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
292 Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
293 if (jr->JobLevel == L_VERIFY_CATALOG) {
296 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
297 " JobStatus IN ('T','W') AND Name='%s' AND "
298 "ClientId=%s ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY",
299 L_VERIFY_INIT, jr->Name,
300 edit_int64(jr->ClientId, ed1));
303 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
304 " JobStatus IN ('T','W') AND Name='%s' AND "
305 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
306 L_VERIFY_INIT, jr->Name,
307 edit_int64(jr->ClientId, ed1));
309 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
310 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
311 jr->JobType == JT_BACKUP) {
315 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
316 "Name='%s' ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY", Name);
319 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
320 "Name='%s' ORDER BY StartTime DESC LIMIT 1", Name);
325 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
326 "ClientId=%s ORDER BY StartTime DESC FETCH FIRST 1 ROW ONLY",
327 edit_int64(jr->ClientId, ed1));
330 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
331 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
332 edit_int64(jr->ClientId, ed1));
336 Mmsg1(&mdb->errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
340 Dmsg1(100, "Query: %s\n", mdb->cmd);
341 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
345 if ((row = sql_fetch_row(mdb)) == NULL) {
346 Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
347 sql_free_result(mdb);
352 jr->JobId = str_to_int64(row[0]);
353 sql_free_result(mdb);
355 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
356 if (jr->JobId <= 0) {
357 Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
367 * Find Available Media (Volume) for Pool
369 * Find a Volume for a given PoolId, MediaType, and Status.
371 * Returns: 0 on failure
375 db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr)
384 if (item == -1) { /* find oldest volume */
385 /* Find oldest volume */
387 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
388 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
389 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
390 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
391 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
392 "Enabled,LocationId,RecycleCount,InitialWrite,"
393 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
394 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
395 "'Recycle','Purged','Used','Append') AND Enabled=1 "
396 "ORDER BY LastWritten FETCH FIRST 1 ROW ONLY",
397 edit_int64(mr->PoolId, ed1), mr->MediaType);
399 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
400 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
401 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
402 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
403 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
404 "Enabled,LocationId,RecycleCount,InitialWrite,"
405 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
406 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
407 "'Recycle','Purged','Used','Append') AND Enabled=1 "
408 "ORDER BY LastWritten LIMIT 1",
409 edit_int64(mr->PoolId, ed1), mr->MediaType);
413 POOL_MEM changer(PM_FNAME);
414 /* Find next available volume */
416 Mmsg(changer, "AND InChanger=1 AND StorageId=%s",
417 edit_int64(mr->StorageId, ed1));
419 if (strcmp(mr->VolStatus, "Recycle") == 0 ||
420 strcmp(mr->VolStatus, "Purged") == 0) {
421 order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
423 order = "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId"; /* take most recently written */
426 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
427 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
428 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
429 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
430 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
431 "Enabled,LocationId,RecycleCount,InitialWrite,"
432 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
433 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
434 "AND VolStatus='%s' "
436 "%s FETCH FIRST %d ROWS ONLY",
437 edit_int64(mr->PoolId, ed1), mr->MediaType,
438 mr->VolStatus, changer.c_str(), order, item);
440 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
441 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
442 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
443 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
444 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
445 "Enabled,LocationId,RecycleCount,InitialWrite,"
446 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
447 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
448 "AND VolStatus='%s' "
451 edit_int64(mr->PoolId, ed1), mr->MediaType,
452 mr->VolStatus, changer.c_str(), order, item);
455 Dmsg1(050, "fnextvol=%s\n", mdb->cmd);
456 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
461 numrows = sql_num_rows(mdb);
462 if (item > numrows || item < 1) {
463 Dmsg2(050, "item=%d got=%d\n", item, numrows);
464 Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
470 /* Note, we previously seeked to the row using:
471 * sql_data_seek(mdb, item-1);
472 * but this failed on PostgreSQL, so now we loop
473 * over all the records. This should not be too horrible since
474 * the maximum Volumes we look at in any case is 20.
477 if ((row = sql_fetch_row(mdb)) == NULL) {
478 Dmsg1(050, "Fail fetch item=%d\n", item+1);
479 Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
480 sql_free_result(mdb);
486 /* Return fields in Media Record */
487 mr->MediaId = str_to_int64(row[0]);
488 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
489 mr->VolJobs = str_to_int64(row[2]);
490 mr->VolFiles = str_to_int64(row[3]);
491 mr->VolBlocks = str_to_int64(row[4]);
492 mr->VolBytes = str_to_uint64(row[5]);
493 mr->VolMounts = str_to_int64(row[6]);
494 mr->VolErrors = str_to_int64(row[7]);
495 mr->VolWrites = str_to_int64(row[8]);
496 mr->MaxVolBytes = str_to_uint64(row[9]);
497 mr->VolCapacityBytes = str_to_uint64(row[10]);
498 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
499 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
500 mr->PoolId = str_to_int64(row[13]);
501 mr->VolRetention = str_to_uint64(row[14]);
502 mr->VolUseDuration = str_to_uint64(row[15]);
503 mr->MaxVolJobs = str_to_int64(row[16]);
504 mr->MaxVolFiles = str_to_int64(row[17]);
505 mr->Recycle = str_to_int64(row[18]);
506 mr->Slot = str_to_int64(row[19]);
507 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
508 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
509 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
510 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
511 mr->InChanger = str_to_uint64(row[22]);
512 mr->EndFile = str_to_uint64(row[23]);
513 mr->EndBlock = str_to_uint64(row[24]);
514 mr->VolParts = str_to_int64(row[25]);
515 mr->LabelType = str_to_int64(row[26]);
516 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
517 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
518 mr->StorageId = str_to_int64(row[28]);
519 mr->Enabled = str_to_int64(row[29]);
520 mr->LocationId = str_to_int64(row[30]);
521 mr->RecycleCount = str_to_int64(row[31]);
522 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
523 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
524 mr->ScratchPoolId = str_to_int64(row[33]);
525 mr->RecyclePoolId = str_to_int64(row[34]);
526 mr->VolReadTime = str_to_int64(row[35]);
527 mr->VolWriteTime = str_to_int64(row[36]);
528 mr->ActionOnPurge = str_to_int64(row[37]);
530 sql_free_result(mdb);
533 Dmsg1(050, "Rtn numrows=%d\n", numrows);
538 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/