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 Copyright (C) 2000-2005 Kern Sibbald
15 This program is free software; you can redistribute it and/or
16 modify it under the terms of the GNU General Public License
17 version 2 as amended with additional clauses defined in the
18 file LICENSE in the main source directory.
20 This program is distributed in the hope that it will be useful,
21 but WITHOUT ANY WARRANTY; without even the implied warranty of
22 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 the file LICENSE for additional details.
29 /* The following is necessary so that we do not include
30 * the dummy external definition of DB.
32 #define __SQL_C /* indicate that this is sql.c */
37 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
39 /* -----------------------------------------------------------------------
41 * Generic Routines (or almost generic)
43 * -----------------------------------------------------------------------
46 /* Imported subroutines */
47 extern void print_result(B_DB *mdb);
48 extern int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
51 * Find job start time if JobId specified, otherwise
52 * find last full save for Incremental and Differential saves.
54 * StartTime is returned in stime
56 * Returns: 0 on failure
57 * 1 on success, jr is unchanged, but stime is set
60 db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime)
63 char ed1[50], ed2[50];
67 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
68 /* If no Id given, we must find corresponding job */
70 /* Differential is since last Full backup */
72 "SELECT StartTime FROM Job WHERE JobStatus='T' AND Type='%c' AND "
73 "Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
74 "ORDER BY StartTime DESC LIMIT 1",
75 jr->JobType, L_FULL, jr->Name,
76 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
78 if (jr->JobLevel == L_DIFFERENTIAL) {
79 /* SQL cmd for Differential backup already edited above */
81 /* Incremental is since last Full, Incremental, or Differential */
82 } else if (jr->JobLevel == L_INCREMENTAL) {
84 * For an Incremental job, we must first ensure
85 * that a Full backup was done (cmd edited above)
86 * then we do a second look to find the most recent
89 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
90 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
91 sql_strerror(mdb), mdb->cmd);
94 if ((row = sql_fetch_row(mdb)) == NULL) {
96 Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
100 /* Now edit SQL command for Incremental Job */
102 "SELECT StartTime FROM Job WHERE JobStatus='T' AND Type='%c' AND "
103 "Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
104 "AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
105 jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, jr->Name,
106 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
108 Mmsg1(&mdb->errmsg, _("Unknown level=%d\n"), jr->JobLevel);
112 Dmsg1(100, "Submitting: %s\n", mdb->cmd);
113 Mmsg(mdb->cmd, "SELECT StartTime FROM Job WHERE Job.JobId=%s",
114 edit_int64(jr->JobId, ed1));
117 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
118 pm_strcpy(stime, ""); /* set EOS */
119 Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
120 sql_strerror(mdb), mdb->cmd);
124 if ((row = sql_fetch_row(mdb)) == NULL) {
125 Mmsg2(&mdb->errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
126 sql_strerror(mdb), mdb->cmd);
127 sql_free_result(mdb);
130 Dmsg1(100, "Got start time: %s\n", row[0]);
131 pm_strcpy(stime, row[0]);
133 sql_free_result(mdb);
144 * Find last failed job since given start-time
145 * it must be either Full or Diff.
147 * Returns: false on failure
148 * true on success, jr is unchanged and stime unchanged
149 * level returned in JobLevel
152 db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
155 char ed1[50], ed2[50];
158 /* Differential is since last Full backup */
160 "SELECT Level FROM Job WHERE JobStatus!='T' AND Type='%c' AND "
161 "Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
162 "AND FileSetId=%s AND StartTime>'%s' "
163 "ORDER BY StartTime DESC LIMIT 1",
164 jr->JobType, L_FULL, L_DIFFERENTIAL, jr->Name,
165 edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
168 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
173 if ((row = sql_fetch_row(mdb)) == NULL) {
174 sql_free_result(mdb);
178 JobLevel = (int)*row[0];
179 sql_free_result(mdb);
187 * Find JobId of last job that ran. E.g. for
188 * VERIFY_CATALOG we want the JobId of the last INIT.
189 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
191 * Returns: true on success
195 db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
202 if (jr->JobLevel == L_VERIFY_CATALOG) {
204 "SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
205 " JobStatus='T' AND Name='%s' AND "
206 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
207 L_VERIFY_INIT, jr->Name,
208 edit_int64(jr->ClientId, ed1));
209 } else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
210 jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
211 jr->JobType == JT_MIGRATE) {
214 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus='T' AND "
215 "Name='%s' ORDER BY StartTime DESC LIMIT 1", Name);
218 "SELECT JobId FROM Job WHERE Type='B' AND JobStatus='T' AND "
219 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
220 edit_int64(jr->ClientId, ed1));
223 Mmsg1(&mdb->errmsg, _("Unknown Job level=%c\n"), jr->JobLevel);
227 Dmsg1(100, "Query: %s\n", mdb->cmd);
228 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
232 if ((row = sql_fetch_row(mdb)) == NULL) {
233 Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
234 sql_free_result(mdb);
239 jr->JobId = str_to_int64(row[0]);
240 sql_free_result(mdb);
242 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
243 if (jr->JobId <= 0) {
244 Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
254 * Find Available Media (Volume) for Pool
256 * Find a Volume for a given PoolId, MediaType, and Status.
258 * Returns: 0 on failure
262 db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr)
266 const char *changer, *order;
270 if (item == -1) { /* find oldest volume */
271 /* Find oldest volume */
272 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
273 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
274 "VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,"
275 "FirstWritten,LastWritten,VolStatus,InChanger,VolParts,"
277 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
278 "'Recycle','Purged','Used','Append') "
279 "ORDER BY LastWritten LIMIT 1",
280 edit_int64(mr->PoolId, ed1), mr->MediaType);
283 /* Find next available volume */
285 changer = "AND InChanger=1";
289 if (strcmp(mr->VolStatus, "Recycled") == 0 ||
290 strcmp(mr->VolStatus, "Purged") == 0) {
291 order = "ORDER BY LastWritten ASC,MediaId"; /* take oldest */
293 order = "ORDER BY LastWritten IS NULL,LastWritten DESC,MediaId"; /* take most recently written */
295 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
296 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
297 "VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,"
298 "FirstWritten,LastWritten,VolStatus,InChanger,VolParts,"
300 "FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus='%s' "
303 edit_int64(mr->PoolId, ed1), mr->MediaType,
304 mr->VolStatus, changer, order, item);
306 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
311 numrows = sql_num_rows(mdb);
312 if (item > numrows) {
313 Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d\n"),
319 /* Seek to desired item
320 * Note, we use base 1; SQL uses base 0
322 sql_data_seek(mdb, item-1);
324 if ((row = sql_fetch_row(mdb)) == NULL) {
325 Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
326 sql_free_result(mdb);
331 /* Return fields in Media Record */
332 mr->MediaId = str_to_int64(row[0]);
333 bstrncpy(mr->VolumeName, row[1], sizeof(mr->VolumeName));
334 mr->VolJobs = str_to_int64(row[2]);
335 mr->VolFiles = str_to_int64(row[3]);
336 mr->VolBlocks = str_to_int64(row[4]);
337 mr->VolBytes = str_to_uint64(row[5]);
338 mr->VolMounts = str_to_int64(row[6]);
339 mr->VolErrors = str_to_int64(row[7]);
340 mr->VolWrites = str_to_int64(row[8]);
341 mr->MaxVolBytes = str_to_uint64(row[9]);
342 mr->VolCapacityBytes = str_to_uint64(row[10]);
343 mr->VolRetention = str_to_uint64(row[11]);
344 mr->VolUseDuration = str_to_uint64(row[12]);
345 mr->MaxVolJobs = str_to_int64(row[13]);
346 mr->MaxVolFiles = str_to_int64(row[14]);
347 mr->Recycle = str_to_int64(row[15]);
348 mr->Slot = str_to_int64(row[16]);
349 bstrncpy(mr->cFirstWritten, row[17]!=NULL?row[17]:"", sizeof(mr->cFirstWritten));
350 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
351 bstrncpy(mr->cLastWritten, row[18]!=NULL?row[18]:"", sizeof(mr->cLastWritten));
352 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
353 bstrncpy(mr->VolStatus, row[19], sizeof(mr->VolStatus));
354 mr->InChanger = str_to_int64(row[20]);
355 mr->VolParts = str_to_int64(row[21]);
356 mr->LabelType = str_to_int64(row[22]);
357 sql_free_result(mdb);
364 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/