2 * Bacula Catalog Database Find record interface routines
4 * Note, generally, these routines are more complicated
5 * that a simple search by name or id. Such simple
8 * Kern Sibbald, December 2000
13 Bacula® - The Network Backup Solution
15 Copyright (C) 2000-2006 Free Software Foundation Europe e.V.
17 The main author of Bacula is Kern Sibbald, with contributions from
18 many others, a complete list can be found in the file AUTHORS.
19 This program is Free Software; you can redistribute it and/or
20 modify it under the terms of version two of the GNU General Public
21 License as published by the Free Software Foundation and included
24 This program is distributed in the hope that it will be useful, but
25 WITHOUT ANY WARRANTY; without even the implied warranty of
26 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
27 General Public License for more details.
29 You should have received a copy of the GNU General Public License
30 along with this program; if not, write to the Free Software
31 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
34 Bacula® is a registered trademark of John Walker.
35 The licensor of Bacula is the Free Software Foundation Europe
36 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
37 Switzerland, email:ftf@fsfeurope.org.
41 /* The following is necessary so that we do not include
42 * the dummy external definition of DB.
44 #define __SQL_C /* indicate that this is sql.c */
49 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
51 /* -----------------------------------------------------------------------
53 * Generic Routines (or almost generic)
55 * -----------------------------------------------------------------------
59 * Find job start time if JobId specified, otherwise
60 * find last full save for Incremental and Differential saves.
62 * StartTime is returned in stime
64 * Returns: 0 on failure
65 * 1 on success, jr is unchanged, but stime is set
68 db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime)
71 char ed1[50], ed2[50];
75 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 FROM Job WHERE JobStatus='T' 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, jr->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 FROM Job WHERE JobStatus='T' 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, jr->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 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 Dmsg1(100, "Got start time: %s\n", row[0]);
139 pm_strcpy(stime, row[0]);
141 sql_free_result(mdb);
152 * Find last failed job since given start-time
153 * it must be either Full or Diff.
155 * Returns: false on failure
156 * true on success, jr is unchanged and stime unchanged
157 * level returned in JobLevel
160 db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
163 char ed1[50], ed2[50];
166 /* Differential is since last Full backup */
168 "SELECT Level FROM Job WHERE JobStatus!='T' AND Type='%c' AND "
169 "Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
170 "AND FileSetId=%s AND StartTime>'%s' "
171 "ORDER BY StartTime DESC LIMIT 1",
172 jr->JobType, L_FULL, L_DIFFERENTIAL, jr->Name,
173 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
176 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
181 if ((row = sql_fetch_row(mdb)) == NULL) {
182 sql_free_result(mdb);
186 JobLevel = (int)*row[0];
187 sql_free_result(mdb);
195 * Find JobId of last job that ran. E.g. for
196 * VERIFY_CATALOG we want the JobId of the last INIT.
197 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
199 * Returns: true on success
203 db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
210 Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
211 if (jr->JobLevel == L_VERIFY_CATALOG) {
213 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
214 " JobStatus='T' AND Name='%s' AND "
215 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
216 L_VERIFY_INIT, jr->Name,
217 edit_int64(jr->ClientId, ed1));
218 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
219 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
220 jr->JobType == JT_BACKUP) {
223 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus='T' AND "
224 "Name='%s' ORDER BY StartTime DESC LIMIT 1", Name);
227 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus='T' AND "
228 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
229 edit_int64(jr->ClientId, ed1));
232 Mmsg1(&mdb->errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
236 Dmsg1(100, "Query: %s\n", mdb->cmd);
237 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
241 if ((row = sql_fetch_row(mdb)) == NULL) {
242 Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
243 sql_free_result(mdb);
248 jr->JobId = str_to_int64(row[0]);
249 sql_free_result(mdb);
251 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
252 if (jr->JobId <= 0) {
253 Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
263 * Find Available Media (Volume) for Pool
265 * Find a Volume for a given PoolId, MediaType, and Status.
267 * Returns: 0 on failure
271 db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr)
280 if (item == -1) { /* find oldest volume */
281 /* Find oldest volume */
282 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
283 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
284 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
285 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
286 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
287 "Enabled,LocationId,RecycleCount,InitialWrite,"
288 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime "
289 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
290 "'Recycle','Purged','Used','Append') AND Enabled=1 "
291 "ORDER BY LastWritten LIMIT 1",
292 edit_int64(mr->PoolId, ed1), mr->MediaType);
296 /* Find next available volume */
298 bsnprintf(changer, sizeof(changer), "AND InChanger=1 AND StorageId=%s",
299 edit_int64(mr->StorageId, ed1));
303 if (strcmp(mr->VolStatus, "Recycle") == 0 ||
304 strcmp(mr->VolStatus, "Purged") == 0) {
305 order = "ORDER BY LastWritten ASC,MediaId"; /* take oldest */
307 order = "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId"; /* take most recently written */
309 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
310 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
311 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
312 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
313 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
314 "Enabled,LocationId,RecycleCount,InitialWrite,"
315 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime "
316 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
317 "AND VolStatus='%s' "
320 edit_int64(mr->PoolId, ed1), mr->MediaType,
321 mr->VolStatus, changer, order, item);
323 Dmsg1(050, "fnextvol=%s\n", mdb->cmd);
324 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
329 numrows = sql_num_rows(mdb);
330 if (item > numrows || item < 1) {
331 Dmsg2(050, "item=%d got=%d\n", item, numrows);
332 Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
338 /* Note, we previously seeked to the row using:
339 * sql_data_seek(mdb, item-1);
340 * but this failed on PostgreSQL, so now we loop
341 * over all the records. This should not be too horrible since
342 * the maximum Volumes we look at in any case is 20.
345 if ((row = sql_fetch_row(mdb)) == NULL) {
346 Dmsg1(050, "Fail fetch item=%d\n", item+1);
347 Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
348 sql_free_result(mdb);
354 /* Return fields in Media Record */
355 mr->MediaId = str_to_int64(row[0]);
356 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
357 mr->VolJobs = str_to_int64(row[2]);
358 mr->VolFiles = str_to_int64(row[3]);
359 mr->VolBlocks = str_to_int64(row[4]);
360 mr->VolBytes = str_to_uint64(row[5]);
361 mr->VolMounts = str_to_int64(row[6]);
362 mr->VolErrors = str_to_int64(row[7]);
363 mr->VolWrites = str_to_int64(row[8]);
364 mr->MaxVolBytes = str_to_uint64(row[9]);
365 mr->VolCapacityBytes = str_to_uint64(row[10]);
366 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
367 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
368 mr->PoolId = str_to_int64(row[13]);
369 mr->VolRetention = str_to_uint64(row[14]);
370 mr->VolUseDuration = str_to_uint64(row[15]);
371 mr->MaxVolJobs = str_to_int64(row[16]);
372 mr->MaxVolFiles = str_to_int64(row[17]);
373 mr->Recycle = str_to_int64(row[18]);
374 mr->Slot = str_to_int64(row[19]);
375 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
376 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
377 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
378 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
379 mr->InChanger = str_to_uint64(row[22]);
380 mr->EndFile = str_to_uint64(row[23]);
381 mr->EndBlock = str_to_uint64(row[24]);
382 mr->VolParts = str_to_int64(row[25]);
383 mr->LabelType = str_to_int64(row[26]);
384 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
385 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
386 mr->StorageId = str_to_int64(row[28]);
387 mr->Enabled = str_to_int64(row[29]);
388 mr->LocationId = str_to_int64(row[30]);
389 mr->RecycleCount = str_to_int64(row[31]);
390 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
391 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
392 mr->ScratchPoolId = str_to_int64(row[33]);
393 mr->RecyclePoolId = str_to_int64(row[34]);
394 mr->VolReadTime = str_to_int64(row[35]);
395 mr->VolWriteTime = str_to_int64(row[36]);
397 sql_free_result(mdb);
400 Dmsg1(050, "Rtn numrows=%d\n", numrows);
405 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/