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
14 Copyright (C) 2000, 2001, 2002 Kern Sibbald and John Walker
16 This program is free software; you can redistribute it and/or
17 modify it under the terms of the GNU General Public License as
18 published by the Free Software Foundation; either version 2 of
19 the License, or (at your option) any later version.
21 This program is distributed in the hope that it will be useful,
22 but WITHOUT ANY WARRANTY; without even the implied warranty of
23 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
24 General Public License for more details.
26 You should have received a copy of the GNU General Public
27 License along with this program; if not, write to the Free
28 Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
33 /* *****FIXME**** fix fixed length of select_cmd[] and insert_cmd[] */
35 /* The following is necessary so that we do not include
36 * the dummy external definition of DB.
38 #define __SQL_C /* indicate that this is sql.c */
43 #if HAVE_MYSQL || HAVE_SQLITE
45 /* -----------------------------------------------------------------------
47 * Generic Routines (or almost generic)
49 * -----------------------------------------------------------------------
52 /* Imported subroutines */
53 extern void print_result(B_DB *mdb);
54 extern int QueryDB(char *file, int line, B_DB *db, char *select_cmd);
57 /* Find job start time. Used to find last full save
58 * for Incremental and Differential saves.
60 * Returns: 0 on failure
61 * 1 on success, jr is unchanged, but stime is set
64 db_find_job_start_time(B_DB *mdb, JOB_DBR *jr, POOLMEM **stime)
69 pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
71 /* If no Id given, we must find corresponding job */
73 /* Differential is since last Full backup */
74 if (jr->Level == L_DIFFERENTIAL) {
76 "SELECT JobId from Job WHERE JobStatus='T' and Type='%c' and \
77 Level='%c' and Name='%s' and ClientId=%d and FileSetId=%d \
78 ORDER by StartTime DESC LIMIT 1",
79 jr->Type, L_FULL, jr->Name, jr->ClientId, jr->FileSetId);
80 /* Incremental is since last Full, Incremental, or Differential */
81 } else if (jr->Level == L_INCREMENTAL) {
83 "SELECT JobId from Job WHERE JobStatus='T' and Type='%c' and \
84 (Level='%c' or Level='%c' or Level='%c') and Name='%s' and ClientId=%d \
85 ORDER by StartTime DESC LIMIT 1",
86 jr->Type, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, jr->Name,
89 Mmsg1(&mdb->errmsg, _("Unknown level=%d\n"), jr->Level);
93 Dmsg1(100, "Submitting: %s\n", mdb->cmd);
94 if (!QUERY_DB(mdb, mdb->cmd)) {
95 Mmsg1(&mdb->errmsg, _("Query error for start time request: %s\n"), mdb->cmd);
99 if ((row = sql_fetch_row(mdb)) == NULL) {
100 sql_free_result(mdb);
104 JobId = atoi(row[0]);
105 sql_free_result(mdb);
107 JobId = jr->JobId; /* search for particular id */
110 Dmsg1(100, "Submitting: %s\n", mdb->cmd);
111 Mmsg(&mdb->cmd, "SELECT StartTime from Job WHERE Job.JobId=%d", JobId);
113 if (!QUERY_DB(mdb, mdb->cmd)) {
114 Mmsg1(&mdb->errmsg, _("Query error for start time request: %s\n"), mdb->cmd);
119 if ((row = sql_fetch_row(mdb)) == NULL) {
120 pm_strcpy(stime, ""); /* set EOS */
121 Mmsg2(&mdb->errmsg, _("No Job found for JobId=%d: %s\n"), JobId, sql_strerror(mdb));
122 sql_free_result(mdb);
126 Dmsg1(100, "Got start time: %s\n", row[0]);
127 pm_strcpy(stime, row[0]);
129 sql_free_result(mdb);
136 * Find JobId of last job that ran. E.g. for
137 * VERIFY_CATALOG we want the JobId of the last INIT.
138 * For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
140 * Returns: 1 on success
144 db_find_last_jobid(B_DB *mdb, JOB_DBR *jr)
150 if (jr->Level == L_VERIFY_CATALOG) {
152 "SELECT JobId FROM Job WHERE Type='%c' AND Level='%c' AND Name='%s' AND \
153 ClientId=%d ORDER BY StartTime DESC LIMIT 1",
154 JT_VERIFY, L_VERIFY_INIT, jr->Name, jr->ClientId);
155 } else if (jr->Level == L_VERIFY_VOLUME_TO_CATALOG) {
157 "SELECT JobId FROM Job WHERE Type='%c' AND \
158 ClientId=%d ORDER BY StartTime DESC LIMIT 1",
159 JT_BACKUP, jr->ClientId);
161 Mmsg1(&mdb->errmsg, _("Unknown Job level=%c\n"), jr->Level);
166 if (!QUERY_DB(mdb, mdb->cmd)) {
170 if ((row = sql_fetch_row(mdb)) == NULL) {
171 Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
172 sql_free_result(mdb);
177 jr->JobId = atoi(row[0]);
178 sql_free_result(mdb);
180 Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
181 if (jr->JobId <= 0) {
182 Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
192 * Find Available Media (Volume) for Pool
194 * Find a Volume for a given PoolId, MediaType, and Status.
196 * Returns: 0 on failure
200 db_find_next_volume(B_DB *mdb, int item, MEDIA_DBR *mr)
206 Mmsg(&mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,\
207 VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,\
208 VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,Recycle,Slot,\
209 FirstWritten FROM Media WHERE PoolId=%d AND MediaType='%s' AND VolStatus='%s' \
210 ORDER BY MediaId", mr->PoolId, mr->MediaType, mr->VolStatus);
212 if (!QUERY_DB(mdb, mdb->cmd)) {
217 numrows = sql_num_rows(mdb);
218 if (item > numrows) {
219 Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d\n"),
225 /* Seek to desired item
226 * Note, we use base 1; SQL uses base 0
228 sql_data_seek(mdb, item-1);
230 if ((row = sql_fetch_row(mdb)) == NULL) {
231 Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
232 sql_free_result(mdb);
237 /* Return fields in Media Record */
238 mr->MediaId = str_to_int64(row[0]);
239 bstrncpy(mr->VolumeName, row[1], sizeof(mr->VolumeName));
240 mr->VolJobs = str_to_int64(row[2]);
241 mr->VolFiles = str_to_int64(row[3]);
242 mr->VolBlocks = str_to_int64(row[4]);
243 mr->VolBytes = str_to_uint64(row[5]);
244 mr->VolMounts = str_to_int64(row[6]);
245 mr->VolErrors = str_to_int64(row[7]);
246 mr->VolWrites = str_to_int64(row[8]);
247 mr->MaxVolBytes = str_to_uint64(row[9]);
248 mr->VolCapacityBytes = str_to_uint64(row[10]);
249 mr->VolRetention = str_to_uint64(row[11]);
250 mr->VolUseDuration = str_to_uint64(row[12]);
251 mr->MaxVolJobs = str_to_int64(row[13]);
252 mr->MaxVolFiles = str_to_int64(row[14]);
253 mr->Recycle = str_to_int64(row[15]);
254 mr->Slot = str_to_int64(row[16]);
255 bstrncpy(mr->cFirstWritten, row[17]!=NULL?row[17]:"", sizeof(mr->cFirstWritten));
257 sql_free_result(mdb);
264 #endif /* HAVE_MYSQL || HAVE_SQLITE */