2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2012 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
59 * Job name is returned in job (MAX_NAME_LENGTH)
61 * Returns: 0 on failure
62 * 1 on success, jr is unchanged, but stime and job are set
65 db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime, char *job)
68 char ed1[50], ed2[50];
69 char esc_name[MAX_ESCAPE_NAME_LENGTH];
72 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
73 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
76 /* If no Id given, we must find corresponding job */
78 /* Differential is since last Full backup */
80 "SELECT StartTime, Job 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 LIMIT 1",
83 jr->JobType, L_FULL, esc_name,
84 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
86 if (jr->JobLevel == L_DIFFERENTIAL) {
87 /* SQL cmd for Differential backup already edited above */
89 /* Incremental is since last Full, Incremental, or Differential */
90 } else if (jr->JobLevel == L_INCREMENTAL) {
92 * For an Incremental job, we must first ensure
93 * that a Full backup was done (cmd edited above)
94 * then we do a second look to find the most recent
97 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
98 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
99 sql_strerror(mdb), mdb->cmd);
102 if ((row = sql_fetch_row(mdb)) == NULL) {
103 sql_free_result(mdb);
104 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
107 sql_free_result(mdb);
108 /* Now edit SQL command for Incremental Job */
110 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
111 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
112 "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
113 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, esc_name,
114 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
116 Mmsg1(mdb->errmsg, _("Unknown level=%d\n"), jr->JobLevel);
120 Dmsg1(100, "Submitting: %s\n", mdb->cmd);
121 Mmsg(mdb->cmd, "SELECT StartTime, Job FROM Job WHERE Job.JobId=%s",
122 edit_int64(jr->JobId, ed1));
125 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
126 pm_strcpy(stime, ""); /* set EOS */
127 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
128 sql_strerror(mdb), mdb->cmd);
132 if ((row = sql_fetch_row(mdb)) == NULL) {
133 Mmsg2(&mdb->errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
134 sql_strerror(mdb), mdb->cmd);
135 sql_free_result(mdb);
138 Dmsg2(100, "Got start time: %s, job: %s\n", row[0], row[1]);
139 pm_strcpy(stime, row[0]);
140 bstrncpy(job, row[1], MAX_NAME_LENGTH);
142 sql_free_result(mdb);
154 * Find the last job start time for the specified JobLevel
156 * StartTime is returned in stime
157 * Job name is returned in job (MAX_NAME_LENGTH)
159 * Returns: false on failure
160 * true on success, jr is unchanged, but stime and job are set
163 db_find_last_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr,
164 POOLMEM **stime, char *job, int JobLevel)
167 char ed1[50], ed2[50];
168 char esc_name[MAX_ESCAPE_NAME_LENGTH];
171 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
172 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
176 "SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
177 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
178 "ORDER BY StartTime DESC LIMIT 1",
179 jr->JobType, JobLevel, esc_name,
180 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
181 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
182 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
183 sql_strerror(mdb), mdb->cmd);
186 if ((row = sql_fetch_row(mdb)) == NULL) {
187 sql_free_result(mdb);
188 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
191 Dmsg1(100, "Got start time: %s\n", row[0]);
192 pm_strcpy(stime, row[0]);
193 bstrncpy(job, row[1], MAX_NAME_LENGTH);
195 sql_free_result(mdb);
205 * Find last failed job since given start-time
206 * it must be either Full or Diff.
208 * Returns: false on failure
209 * true on success, jr is unchanged and stime unchanged
210 * level returned in JobLevel
213 db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
216 char ed1[50], ed2[50];
217 char esc_name[MAX_ESCAPE_NAME_LENGTH];
220 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
222 /* Differential is since last Full backup */
224 "SELECT Level FROM Job WHERE JobStatus NOT IN ('T','W') AND "
225 "Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
226 "AND FileSetId=%s AND StartTime>'%s' "
227 "ORDER BY StartTime DESC LIMIT 1",
228 jr->JobType, L_FULL, L_DIFFERENTIAL, esc_name,
229 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
231 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
236 if ((row = sql_fetch_row(mdb)) == NULL) {
237 sql_free_result(mdb);
241 JobLevel = (int)*row[0];
242 sql_free_result(mdb);
250 * Find JobId of last job that ran. E.g. for
251 * VERIFY_CATALOG we want the JobId of the last INIT.
252 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
254 * Returns: true on success
258 db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
262 char esc_name[MAX_ESCAPE_NAME_LENGTH];
266 Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
267 if (jr->JobLevel == L_VERIFY_CATALOG) {
268 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
270 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
271 " JobStatus IN ('T','W') AND Name='%s' AND "
272 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
273 L_VERIFY_INIT, esc_name,
274 edit_int64(jr->ClientId, ed1));
275 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
276 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
277 jr->JobType == JT_BACKUP) {
279 mdb->db_escape_string(jcr, esc_name, (char*)Name,
280 MIN(strlen(Name), sizeof(esc_name)));
282 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
283 "Name='%s' ORDER BY StartTime DESC LIMIT 1", esc_name);
286 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
287 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
288 edit_int64(jr->ClientId, ed1));
291 Mmsg1(&mdb->errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
295 Dmsg1(100, "Query: %s\n", mdb->cmd);
296 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
300 if ((row = sql_fetch_row(mdb)) == NULL) {
301 Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
302 sql_free_result(mdb);
307 jr->JobId = str_to_int64(row[0]);
308 sql_free_result(mdb);
310 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
311 if (jr->JobId <= 0) {
312 Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
322 * Find Available Media (Volume) for Pool
324 * Find a Volume for a given PoolId, MediaType, and Status.
326 * Returns: 0 on failure
330 db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr)
335 char esc_type[MAX_ESCAPE_NAME_LENGTH];
336 char esc_status[MAX_ESCAPE_NAME_LENGTH];
340 mdb->db_escape_string(jcr, esc_type, mr->MediaType, strlen(mr->MediaType));
341 mdb->db_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
343 if (item == -1) { /* find oldest volume */
344 /* Find oldest volume */
345 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
346 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
347 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
348 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
349 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
350 "Enabled,LocationId,RecycleCount,InitialWrite,"
351 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
352 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
353 "'Recycle','Purged','Used','Append') AND Enabled=1 "
354 "ORDER BY LastWritten LIMIT 1",
355 edit_int64(mr->PoolId, ed1), esc_type);
358 POOL_MEM changer(PM_FNAME);
359 /* Find next available volume */
361 Mmsg(changer, "AND InChanger=1 AND StorageId=%s",
362 edit_int64(mr->StorageId, ed1));
364 if (strcmp(mr->VolStatus, "Recycle") == 0 ||
365 strcmp(mr->VolStatus, "Purged") == 0) {
366 order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
368 order = sql_media_order_most_recently_written[db_get_type_index(mdb)]; /* take most recently written */
370 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
371 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
372 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
373 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
374 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
375 "Enabled,LocationId,RecycleCount,InitialWrite,"
376 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
377 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
378 "AND VolStatus='%s' "
381 edit_int64(mr->PoolId, ed1), esc_type,
382 esc_status, changer.c_str(), order, item);
384 Dmsg1(100, "fnextvol=%s\n", mdb->cmd);
385 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
390 numrows = sql_num_rows(mdb);
391 if (item > numrows || item < 1) {
392 Dmsg2(050, "item=%d got=%d\n", item, numrows);
393 Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
399 /* Note, we previously seeked to the row using:
400 * sql_data_seek(mdb, item-1);
401 * but this failed on PostgreSQL, so now we loop
402 * over all the records. This should not be too horrible since
403 * the maximum Volumes we look at in any case is 20.
406 if ((row = sql_fetch_row(mdb)) == NULL) {
407 Dmsg1(050, "Fail fetch item=%d\n", item+1);
408 Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
409 sql_free_result(mdb);
415 /* Return fields in Media Record */
416 mr->MediaId = str_to_int64(row[0]);
417 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
418 mr->VolJobs = str_to_int64(row[2]);
419 mr->VolFiles = str_to_int64(row[3]);
420 mr->VolBlocks = str_to_int64(row[4]);
421 mr->VolBytes = str_to_uint64(row[5]);
422 mr->VolMounts = str_to_int64(row[6]);
423 mr->VolErrors = str_to_int64(row[7]);
424 mr->VolWrites = str_to_int64(row[8]);
425 mr->MaxVolBytes = str_to_uint64(row[9]);
426 mr->VolCapacityBytes = str_to_uint64(row[10]);
427 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
428 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
429 mr->PoolId = str_to_int64(row[13]);
430 mr->VolRetention = str_to_uint64(row[14]);
431 mr->VolUseDuration = str_to_uint64(row[15]);
432 mr->MaxVolJobs = str_to_int64(row[16]);
433 mr->MaxVolFiles = str_to_int64(row[17]);
434 mr->Recycle = str_to_int64(row[18]);
435 mr->Slot = str_to_int64(row[19]);
436 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
437 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
438 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
439 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
440 mr->InChanger = str_to_uint64(row[22]);
441 mr->EndFile = str_to_uint64(row[23]);
442 mr->EndBlock = str_to_uint64(row[24]);
443 mr->VolParts = str_to_int64(row[25]);
444 mr->LabelType = str_to_int64(row[26]);
445 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
446 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
447 mr->StorageId = str_to_int64(row[28]);
448 mr->Enabled = str_to_int64(row[29]);
449 mr->LocationId = str_to_int64(row[30]);
450 mr->RecycleCount = str_to_int64(row[31]);
451 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
452 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
453 mr->ScratchPoolId = str_to_int64(row[33]);
454 mr->RecyclePoolId = str_to_int64(row[34]);
455 mr->VolReadTime = str_to_int64(row[35]);
456 mr->VolWriteTime = str_to_int64(row[36]);
457 mr->ActionOnPurge = str_to_int64(row[37]);
459 sql_free_result(mdb);
462 Dmsg1(050, "Rtn numrows=%d\n", numrows);
466 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */