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 three of the GNU Affero 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 Affero 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
41 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI
47 /* -----------------------------------------------------------------------
49 * Generic Routines (or almost generic)
51 * -----------------------------------------------------------------------
55 * Find job start time if JobId specified, otherwise
56 * find last Job start time Incremental and Differential saves.
58 * StartTime is returned in stime
60 * Returns: 0 on failure
61 * 1 on success, jr is unchanged, but stime is set
64 db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime)
67 char ed1[50], ed2[50];
68 char esc_name[MAX_ESCAPE_NAME_LENGTH];
71 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
72 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
73 /* If no Id given, we must find corresponding job */
75 /* Differential is since last Full backup */
77 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
78 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
79 "ORDER BY StartTime DESC LIMIT 1",
80 jr->JobType, L_FULL, esc_name,
81 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
83 if (jr->JobLevel == L_DIFFERENTIAL) {
84 /* SQL cmd for Differential backup already edited above */
86 /* Incremental is since last Full, Incremental, or Differential */
87 } else if (jr->JobLevel == L_INCREMENTAL) {
89 * For an Incremental job, we must first ensure
90 * that a Full backup was done (cmd edited above)
91 * then we do a second look to find the most recent
94 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
95 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
96 sql_strerror(mdb), mdb->cmd);
99 if ((row = sql_fetch_row(mdb)) == NULL) {
100 sql_free_result(mdb);
101 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
104 sql_free_result(mdb);
105 /* Now edit SQL command for Incremental Job */
107 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
108 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
109 "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
110 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, esc_name,
111 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
113 Mmsg1(mdb->errmsg, _("Unknown level=%d\n"), jr->JobLevel);
117 Dmsg1(100, "Submitting: %s\n", mdb->cmd);
118 Mmsg(mdb->cmd, "SELECT StartTime FROM Job WHERE Job.JobId=%s",
119 edit_int64(jr->JobId, ed1));
122 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
123 pm_strcpy(stime, ""); /* set EOS */
124 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
125 sql_strerror(mdb), mdb->cmd);
129 if ((row = sql_fetch_row(mdb)) == NULL) {
130 Mmsg2(&mdb->errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
131 sql_strerror(mdb), mdb->cmd);
132 sql_free_result(mdb);
135 Dmsg1(100, "Got start time: %s\n", row[0]);
136 pm_strcpy(stime, row[0]);
138 sql_free_result(mdb);
150 * Find the last job start time for the specified JobLevel
152 * StartTime is returned in stime
154 * Returns: false on failure
155 * true on success, jr is unchanged, but stime is set
158 db_find_last_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime, int JobLevel)
161 char ed1[50], ed2[50];
162 char esc_name[MAX_ESCAPE_NAME_LENGTH];
165 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
166 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
169 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
170 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
171 "ORDER BY StartTime DESC LIMIT 1",
172 jr->JobType, JobLevel, esc_name,
173 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
174 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
175 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
176 sql_strerror(mdb), mdb->cmd);
179 if ((row = sql_fetch_row(mdb)) == NULL) {
180 sql_free_result(mdb);
181 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
184 Dmsg1(100, "Got start time: %s\n", row[0]);
185 pm_strcpy(stime, row[0]);
186 sql_free_result(mdb);
196 * Find last failed job since given start-time
197 * it must be either Full or Diff.
199 * Returns: false on failure
200 * true on success, jr is unchanged and stime unchanged
201 * level returned in JobLevel
204 db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
207 char ed1[50], ed2[50];
208 char esc_name[MAX_ESCAPE_NAME_LENGTH];
211 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
213 /* Differential is since last Full backup */
215 "SELECT Level FROM Job WHERE JobStatus NOT IN ('T','W') AND "
216 "Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
217 "AND FileSetId=%s AND StartTime>'%s' "
218 "ORDER BY StartTime DESC LIMIT 1",
219 jr->JobType, L_FULL, L_DIFFERENTIAL, esc_name,
220 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
222 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
227 if ((row = sql_fetch_row(mdb)) == NULL) {
228 sql_free_result(mdb);
232 JobLevel = (int)*row[0];
233 sql_free_result(mdb);
241 * Find JobId of last job that ran. E.g. for
242 * VERIFY_CATALOG we want the JobId of the last INIT.
243 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
245 * Returns: true on success
249 db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
253 char esc_name[MAX_ESCAPE_NAME_LENGTH];
257 Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
258 if (jr->JobLevel == L_VERIFY_CATALOG) {
259 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
261 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
262 " JobStatus IN ('T','W') AND Name='%s' AND "
263 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
264 L_VERIFY_INIT, esc_name,
265 edit_int64(jr->ClientId, ed1));
266 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
267 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
268 jr->JobType == JT_BACKUP) {
270 mdb->db_escape_string(jcr, esc_name, (char*)Name,
271 MIN(strlen(Name), sizeof(esc_name)));
273 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
274 "Name='%s' ORDER BY StartTime DESC LIMIT 1", esc_name);
277 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
278 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
279 edit_int64(jr->ClientId, ed1));
282 Mmsg1(&mdb->errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
286 Dmsg1(100, "Query: %s\n", mdb->cmd);
287 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
291 if ((row = sql_fetch_row(mdb)) == NULL) {
292 Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
293 sql_free_result(mdb);
298 jr->JobId = str_to_int64(row[0]);
299 sql_free_result(mdb);
301 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
302 if (jr->JobId <= 0) {
303 Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
313 * Find Available Media (Volume) for Pool
315 * Find a Volume for a given PoolId, MediaType, and Status.
317 * Returns: 0 on failure
321 db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr)
326 char esc_type[MAX_ESCAPE_NAME_LENGTH];
327 char esc_status[MAX_ESCAPE_NAME_LENGTH];
331 mdb->db_escape_string(jcr, esc_type, mr->MediaType, strlen(mr->MediaType));
332 mdb->db_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
334 if (item == -1) { /* find oldest volume */
335 /* Find oldest volume */
336 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
337 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
338 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
339 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
340 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
341 "Enabled,LocationId,RecycleCount,InitialWrite,"
342 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
343 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
344 "'Recycle','Purged','Used','Append') AND Enabled=1 "
345 "ORDER BY LastWritten LIMIT 1",
346 edit_int64(mr->PoolId, ed1), esc_type);
349 POOL_MEM changer(PM_FNAME);
350 /* Find next available volume */
352 Mmsg(changer, "AND InChanger=1 AND StorageId=%s",
353 edit_int64(mr->StorageId, ed1));
355 if (strcmp(mr->VolStatus, "Recycle") == 0 ||
356 strcmp(mr->VolStatus, "Purged") == 0) {
357 order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
359 order = sql_media_order_most_recently_written[db_get_type_index(mdb)]; /* take most recently written */
361 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
362 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
363 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
364 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
365 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
366 "Enabled,LocationId,RecycleCount,InitialWrite,"
367 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
368 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
369 "AND VolStatus='%s' "
372 edit_int64(mr->PoolId, ed1), esc_type,
373 esc_status, changer.c_str(), order, item);
375 Dmsg1(050, "fnextvol=%s\n", mdb->cmd);
376 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
381 numrows = sql_num_rows(mdb);
382 if (item > numrows || item < 1) {
383 Dmsg2(050, "item=%d got=%d\n", item, numrows);
384 Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
390 /* Note, we previously seeked to the row using:
391 * sql_data_seek(mdb, item-1);
392 * but this failed on PostgreSQL, so now we loop
393 * over all the records. This should not be too horrible since
394 * the maximum Volumes we look at in any case is 20.
397 if ((row = sql_fetch_row(mdb)) == NULL) {
398 Dmsg1(050, "Fail fetch item=%d\n", item+1);
399 Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
400 sql_free_result(mdb);
406 /* Return fields in Media Record */
407 mr->MediaId = str_to_int64(row[0]);
408 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
409 mr->VolJobs = str_to_int64(row[2]);
410 mr->VolFiles = str_to_int64(row[3]);
411 mr->VolBlocks = str_to_int64(row[4]);
412 mr->VolBytes = str_to_uint64(row[5]);
413 mr->VolMounts = str_to_int64(row[6]);
414 mr->VolErrors = str_to_int64(row[7]);
415 mr->VolWrites = str_to_int64(row[8]);
416 mr->MaxVolBytes = str_to_uint64(row[9]);
417 mr->VolCapacityBytes = str_to_uint64(row[10]);
418 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
419 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
420 mr->PoolId = str_to_int64(row[13]);
421 mr->VolRetention = str_to_uint64(row[14]);
422 mr->VolUseDuration = str_to_uint64(row[15]);
423 mr->MaxVolJobs = str_to_int64(row[16]);
424 mr->MaxVolFiles = str_to_int64(row[17]);
425 mr->Recycle = str_to_int64(row[18]);
426 mr->Slot = str_to_int64(row[19]);
427 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
428 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
429 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
430 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
431 mr->InChanger = str_to_uint64(row[22]);
432 mr->EndFile = str_to_uint64(row[23]);
433 mr->EndBlock = str_to_uint64(row[24]);
434 mr->VolParts = str_to_int64(row[25]);
435 mr->LabelType = str_to_int64(row[26]);
436 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
437 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
438 mr->StorageId = str_to_int64(row[28]);
439 mr->Enabled = str_to_int64(row[29]);
440 mr->LocationId = str_to_int64(row[30]);
441 mr->RecycleCount = str_to_int64(row[31]);
442 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
443 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
444 mr->ScratchPoolId = str_to_int64(row[33]);
445 mr->RecyclePoolId = str_to_int64(row[34]);
446 mr->VolReadTime = str_to_int64(row[35]);
447 mr->VolWriteTime = str_to_int64(row[36]);
448 mr->ActionOnPurge = str_to_int64(row[37]);
450 sql_free_result(mdb);
453 Dmsg1(050, "Rtn numrows=%d\n", numrows);
457 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */