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];
71 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
72 /* If no Id given, we must find corresponding job */
74 /* Differential is since last Full backup */
76 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
77 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
78 "ORDER BY StartTime DESC LIMIT 1",
79 jr->JobType, L_FULL, jr->Name,
80 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
82 if (jr->JobLevel == L_DIFFERENTIAL) {
83 /* SQL cmd for Differential backup already edited above */
85 /* Incremental is since last Full, Incremental, or Differential */
86 } else if (jr->JobLevel == L_INCREMENTAL) {
88 * For an Incremental job, we must first ensure
89 * that a Full backup was done (cmd edited above)
90 * then we do a second look to find the most recent
93 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
94 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
95 sql_strerror(mdb), mdb->cmd);
98 if ((row = sql_fetch_row(mdb)) == NULL) {
100 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
103 sql_free_result(mdb);
104 /* Now edit SQL command for Incremental Job */
106 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
107 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
108 "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
109 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, jr->Name,
110 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
112 Mmsg1(mdb->errmsg, _("Unknown level=%d\n"), jr->JobLevel);
116 Dmsg1(100, "Submitting: %s\n", mdb->cmd);
117 Mmsg(mdb->cmd, "SELECT StartTime FROM Job WHERE Job.JobId=%s",
118 edit_int64(jr->JobId, ed1));
121 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
122 pm_strcpy(stime, ""); /* set EOS */
123 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
124 sql_strerror(mdb), mdb->cmd);
128 if ((row = sql_fetch_row(mdb)) == NULL) {
129 Mmsg2(&mdb->errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
130 sql_strerror(mdb), mdb->cmd);
131 sql_free_result(mdb);
134 Dmsg1(100, "Got start time: %s\n", row[0]);
135 pm_strcpy(stime, row[0]);
137 sql_free_result(mdb);
149 * Find the last job start time for the specified JobLevel
151 * StartTime is returned in stime
153 * Returns: false on failure
154 * true on success, jr is unchanged, but stime is set
157 db_find_last_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime, int JobLevel)
160 char ed1[50], ed2[50];
164 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
167 "SELECT StartTime FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
168 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
169 "ORDER BY StartTime DESC LIMIT 1",
170 jr->JobType, JobLevel, jr->Name,
171 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
172 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
173 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
174 sql_strerror(mdb), mdb->cmd);
177 if ((row = sql_fetch_row(mdb)) == NULL) {
178 sql_free_result(mdb);
179 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
182 Dmsg1(100, "Got start time: %s\n", row[0]);
183 pm_strcpy(stime, row[0]);
184 sql_free_result(mdb);
194 * Find last failed job since given start-time
195 * it must be either Full or Diff.
197 * Returns: false on failure
198 * true on success, jr is unchanged and stime unchanged
199 * level returned in JobLevel
202 db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
205 char ed1[50], ed2[50];
208 /* Differential is since last Full backup */
210 "SELECT Level FROM Job WHERE JobStatus NOT IN ('T','W') AND "
211 "Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
212 "AND FileSetId=%s AND StartTime>'%s' "
213 "ORDER BY StartTime DESC LIMIT 1",
214 jr->JobType, L_FULL, L_DIFFERENTIAL, jr->Name,
215 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
217 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
222 if ((row = sql_fetch_row(mdb)) == NULL) {
223 sql_free_result(mdb);
227 JobLevel = (int)*row[0];
228 sql_free_result(mdb);
236 * Find JobId of last job that ran. E.g. for
237 * VERIFY_CATALOG we want the JobId of the last INIT.
238 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
240 * Returns: true on success
244 db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
251 Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
252 if (jr->JobLevel == L_VERIFY_CATALOG) {
254 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
255 " JobStatus IN ('T','W') AND Name='%s' AND "
256 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
257 L_VERIFY_INIT, jr->Name,
258 edit_int64(jr->ClientId, ed1));
259 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
260 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
261 jr->JobType == JT_BACKUP) {
264 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
265 "Name='%s' ORDER BY StartTime DESC LIMIT 1", Name);
268 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
269 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
270 edit_int64(jr->ClientId, ed1));
273 Mmsg1(&mdb->errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
277 Dmsg1(100, "Query: %s\n", mdb->cmd);
278 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
282 if ((row = sql_fetch_row(mdb)) == NULL) {
283 Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
284 sql_free_result(mdb);
289 jr->JobId = str_to_int64(row[0]);
290 sql_free_result(mdb);
292 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
293 if (jr->JobId <= 0) {
294 Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
304 * Find Available Media (Volume) for Pool
306 * Find a Volume for a given PoolId, MediaType, and Status.
308 * Returns: 0 on failure
312 db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr)
321 if (item == -1) { /* find oldest volume */
322 /* Find oldest volume */
323 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
324 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
325 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
326 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
327 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
328 "Enabled,LocationId,RecycleCount,InitialWrite,"
329 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
330 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
331 "'Recycle','Purged','Used','Append') AND Enabled=1 "
332 "ORDER BY LastWritten LIMIT 1",
333 edit_int64(mr->PoolId, ed1), mr->MediaType);
336 POOL_MEM changer(PM_FNAME);
337 /* Find next available volume */
339 Mmsg(changer, "AND InChanger=1 AND StorageId=%s",
340 edit_int64(mr->StorageId, ed1));
342 if (strcmp(mr->VolStatus, "Recycle") == 0 ||
343 strcmp(mr->VolStatus, "Purged") == 0) {
344 order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
346 order = sql_media_order_most_recently_written[db_get_type_index(mdb)]; /* take most recently written */
348 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
349 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
350 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
351 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
352 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
353 "Enabled,LocationId,RecycleCount,InitialWrite,"
354 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
355 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
356 "AND VolStatus='%s' "
359 edit_int64(mr->PoolId, ed1), mr->MediaType,
360 mr->VolStatus, changer.c_str(), order, item);
362 Dmsg1(050, "fnextvol=%s\n", mdb->cmd);
363 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
368 numrows = sql_num_rows(mdb);
369 if (item > numrows || item < 1) {
370 Dmsg2(050, "item=%d got=%d\n", item, numrows);
371 Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
377 /* Note, we previously seeked to the row using:
378 * sql_data_seek(mdb, item-1);
379 * but this failed on PostgreSQL, so now we loop
380 * over all the records. This should not be too horrible since
381 * the maximum Volumes we look at in any case is 20.
384 if ((row = sql_fetch_row(mdb)) == NULL) {
385 Dmsg1(050, "Fail fetch item=%d\n", item+1);
386 Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
387 sql_free_result(mdb);
393 /* Return fields in Media Record */
394 mr->MediaId = str_to_int64(row[0]);
395 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
396 mr->VolJobs = str_to_int64(row[2]);
397 mr->VolFiles = str_to_int64(row[3]);
398 mr->VolBlocks = str_to_int64(row[4]);
399 mr->VolBytes = str_to_uint64(row[5]);
400 mr->VolMounts = str_to_int64(row[6]);
401 mr->VolErrors = str_to_int64(row[7]);
402 mr->VolWrites = str_to_int64(row[8]);
403 mr->MaxVolBytes = str_to_uint64(row[9]);
404 mr->VolCapacityBytes = str_to_uint64(row[10]);
405 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
406 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
407 mr->PoolId = str_to_int64(row[13]);
408 mr->VolRetention = str_to_uint64(row[14]);
409 mr->VolUseDuration = str_to_uint64(row[15]);
410 mr->MaxVolJobs = str_to_int64(row[16]);
411 mr->MaxVolFiles = str_to_int64(row[17]);
412 mr->Recycle = str_to_int64(row[18]);
413 mr->Slot = str_to_int64(row[19]);
414 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
415 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
416 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
417 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
418 mr->InChanger = str_to_uint64(row[22]);
419 mr->EndFile = str_to_uint64(row[23]);
420 mr->EndBlock = str_to_uint64(row[24]);
421 mr->VolParts = str_to_int64(row[25]);
422 mr->LabelType = str_to_int64(row[26]);
423 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
424 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
425 mr->StorageId = str_to_int64(row[28]);
426 mr->Enabled = str_to_int64(row[29]);
427 mr->LocationId = str_to_int64(row[30]);
428 mr->RecycleCount = str_to_int64(row[31]);
429 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
430 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
431 mr->ScratchPoolId = str_to_int64(row[33]);
432 mr->RecyclePoolId = str_to_int64(row[34]);
433 mr->VolReadTime = str_to_int64(row[35]);
434 mr->VolWriteTime = str_to_int64(row[36]);
435 mr->ActionOnPurge = str_to_int64(row[37]);
437 sql_free_result(mdb);
440 Dmsg1(050, "Rtn numrows=%d\n", numrows);
444 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */