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
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 */
79 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
80 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
81 "ORDER BY StartTime DESC LIMIT 1",
82 jr->JobType, L_FULL, jr->Name,
83 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
85 if (jr->JobLevel == L_DIFFERENTIAL) {
86 /* SQL cmd for Differential backup already edited above */
88 /* Incremental is since last Full, Incremental, or Differential */
89 } else if (jr->JobLevel == L_INCREMENTAL) {
91 * For an Incremental job, we must first ensure
92 * that a Full backup was done (cmd edited above)
93 * then we do a second look to find the most recent
96 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
97 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
98 sql_strerror(mdb), mdb->cmd);
101 if ((row = sql_fetch_row(mdb)) == NULL) {
102 sql_free_result(mdb);
103 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
106 sql_free_result(mdb);
107 /* Now edit SQL command for Incremental Job */
109 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
110 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
111 "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
112 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, jr->Name,
113 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
115 Mmsg1(mdb->errmsg, _("Unknown level=%d\n"), jr->JobLevel);
119 Dmsg1(100, "Submitting: %s\n", mdb->cmd);
120 Mmsg(mdb->cmd, "SELECT StartTime FROM Job WHERE Job.JobId=%s",
121 edit_int64(jr->JobId, ed1));
124 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
125 pm_strcpy(stime, ""); /* set EOS */
126 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
127 sql_strerror(mdb), mdb->cmd);
131 if ((row = sql_fetch_row(mdb)) == NULL) {
132 Mmsg2(&mdb->errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
133 sql_strerror(mdb), mdb->cmd);
134 sql_free_result(mdb);
137 Dmsg1(100, "Got start time: %s\n", row[0]);
138 pm_strcpy(stime, row[0]);
140 sql_free_result(mdb);
152 * Find the last job start time for the specified JobLevel
154 * StartTime is returned in stime
156 * Returns: false on failure
157 * true on success, jr is unchanged, but stime is set
160 db_find_last_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime, int JobLevel)
163 char ed1[50], ed2[50];
167 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
170 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
171 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
172 "ORDER BY StartTime DESC LIMIT 1",
173 jr->JobType, JobLevel, jr->Name,
174 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
175 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
176 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
177 sql_strerror(mdb), mdb->cmd);
180 if ((row = sql_fetch_row(mdb)) == NULL) {
181 sql_free_result(mdb);
182 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
185 Dmsg1(100, "Got start time: %s\n", row[0]);
186 pm_strcpy(stime, row[0]);
187 sql_free_result(mdb);
197 * Find last failed job since given start-time
198 * it must be either Full or Diff.
200 * Returns: false on failure
201 * true on success, jr is unchanged and stime unchanged
202 * level returned in JobLevel
205 db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
208 char ed1[50], ed2[50];
211 /* Differential is since last Full backup */
213 "SELECT Level FROM Job WHERE JobStatus NOT IN ('T','W') AND "
214 "Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
215 "AND FileSetId=%s AND StartTime>'%s' "
216 "ORDER BY StartTime DESC LIMIT 1",
217 jr->JobType, L_FULL, L_DIFFERENTIAL, jr->Name,
218 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
220 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
225 if ((row = sql_fetch_row(mdb)) == NULL) {
226 sql_free_result(mdb);
230 JobLevel = (int)*row[0];
231 sql_free_result(mdb);
239 * Find JobId of last job that ran. E.g. for
240 * VERIFY_CATALOG we want the JobId of the last INIT.
241 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
243 * Returns: true on success
247 db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
254 Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
255 if (jr->JobLevel == L_VERIFY_CATALOG) {
257 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
258 " JobStatus IN ('T','W') AND Name='%s' AND "
259 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
260 L_VERIFY_INIT, jr->Name,
261 edit_int64(jr->ClientId, ed1));
262 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
263 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
264 jr->JobType == JT_BACKUP) {
267 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
268 "Name='%s' ORDER BY StartTime DESC LIMIT 1", Name);
271 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
272 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
273 edit_int64(jr->ClientId, ed1));
276 Mmsg1(&mdb->errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
280 Dmsg1(100, "Query: %s\n", mdb->cmd);
281 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
285 if ((row = sql_fetch_row(mdb)) == NULL) {
286 Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
287 sql_free_result(mdb);
292 jr->JobId = str_to_int64(row[0]);
293 sql_free_result(mdb);
295 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
296 if (jr->JobId <= 0) {
297 Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
307 * Find Available Media (Volume) for Pool
309 * Find a Volume for a given PoolId, MediaType, and Status.
311 * Returns: 0 on failure
315 db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr)
324 if (item == -1) { /* find oldest volume */
325 /* Find oldest volume */
326 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
327 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
328 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
329 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
330 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
331 "Enabled,LocationId,RecycleCount,InitialWrite,"
332 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
333 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
334 "'Recycle','Purged','Used','Append') AND Enabled=1 "
335 "ORDER BY LastWritten LIMIT 1",
336 edit_int64(mr->PoolId, ed1), mr->MediaType);
339 POOL_MEM changer(PM_FNAME);
340 /* Find next available volume */
342 Mmsg(changer, "AND InChanger=1 AND StorageId=%s",
343 edit_int64(mr->StorageId, ed1));
345 if (strcmp(mr->VolStatus, "Recycle") == 0 ||
346 strcmp(mr->VolStatus, "Purged") == 0) {
347 order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
349 order = sql_media_order_most_recently_written[db_type]; /* take most recently written */
351 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
352 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
353 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
354 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
355 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
356 "Enabled,LocationId,RecycleCount,InitialWrite,"
357 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
358 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
359 "AND VolStatus='%s' "
362 edit_int64(mr->PoolId, ed1), mr->MediaType,
363 mr->VolStatus, changer.c_str(), order, item);
365 Dmsg1(050, "fnextvol=%s\n", mdb->cmd);
366 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
371 numrows = sql_num_rows(mdb);
372 if (item > numrows || item < 1) {
373 Dmsg2(050, "item=%d got=%d\n", item, numrows);
374 Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
380 /* Note, we previously seeked to the row using:
381 * sql_data_seek(mdb, item-1);
382 * but this failed on PostgreSQL, so now we loop
383 * over all the records. This should not be too horrible since
384 * the maximum Volumes we look at in any case is 20.
387 if ((row = sql_fetch_row(mdb)) == NULL) {
388 Dmsg1(050, "Fail fetch item=%d\n", item+1);
389 Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
390 sql_free_result(mdb);
396 /* Return fields in Media Record */
397 mr->MediaId = str_to_int64(row[0]);
398 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
399 mr->VolJobs = str_to_int64(row[2]);
400 mr->VolFiles = str_to_int64(row[3]);
401 mr->VolBlocks = str_to_int64(row[4]);
402 mr->VolBytes = str_to_uint64(row[5]);
403 mr->VolMounts = str_to_int64(row[6]);
404 mr->VolErrors = str_to_int64(row[7]);
405 mr->VolWrites = str_to_int64(row[8]);
406 mr->MaxVolBytes = str_to_uint64(row[9]);
407 mr->VolCapacityBytes = str_to_uint64(row[10]);
408 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
409 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
410 mr->PoolId = str_to_int64(row[13]);
411 mr->VolRetention = str_to_uint64(row[14]);
412 mr->VolUseDuration = str_to_uint64(row[15]);
413 mr->MaxVolJobs = str_to_int64(row[16]);
414 mr->MaxVolFiles = str_to_int64(row[17]);
415 mr->Recycle = str_to_int64(row[18]);
416 mr->Slot = str_to_int64(row[19]);
417 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
418 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
419 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
420 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
421 mr->InChanger = str_to_uint64(row[22]);
422 mr->EndFile = str_to_uint64(row[23]);
423 mr->EndBlock = str_to_uint64(row[24]);
424 mr->VolParts = str_to_int64(row[25]);
425 mr->LabelType = str_to_int64(row[26]);
426 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
427 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
428 mr->StorageId = str_to_int64(row[28]);
429 mr->Enabled = str_to_int64(row[29]);
430 mr->LocationId = str_to_int64(row[30]);
431 mr->RecycleCount = str_to_int64(row[31]);
432 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
433 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
434 mr->ScratchPoolId = str_to_int64(row[33]);
435 mr->RecyclePoolId = str_to_int64(row[34]);
436 mr->VolReadTime = str_to_int64(row[35]);
437 mr->VolWriteTime = str_to_int64(row[36]);
438 mr->ActionOnPurge = str_to_int64(row[37]);
440 sql_free_result(mdb);
443 Dmsg1(050, "Rtn numrows=%d\n", numrows);
448 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/