2 * Bacula Catalog Database Update record interface routines
4 * Kern Sibbald, March 2000
9 Bacula® - The Network Backup Solution
11 Copyright (C) 2000-2006 Free Software Foundation Europe e.V.
13 The main author of Bacula is Kern Sibbald, with contributions from
14 many others, a complete list can be found in the file AUTHORS.
15 This program is Free Software; you can redistribute it and/or
16 modify it under the terms of version two of the GNU General Public
17 License as published by the Free Software Foundation plus additions
18 that are listed in the file LICENSE.
20 This program is distributed in the hope that it will be useful, but
21 WITHOUT ANY WARRANTY; without even the implied warranty of
22 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
23 General Public License for more details.
25 You should have received a copy of the GNU General Public License
26 along with this program; if not, write to the Free Software
27 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
30 Bacula® is a registered trademark of John Walker.
31 The licensor of Bacula is the Free Software Foundation Europe
32 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
33 Switzerland, email:ftf@fsfeurope.org.
36 /* The following is necessary so that we do not include
37 * the dummy external definition of DB.
39 #define __SQL_C /* indicate that this is sql.c */
44 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
46 /* -----------------------------------------------------------------------
48 * Generic Routines (or almost generic)
50 * -----------------------------------------------------------------------
53 /* -----------------------------------------------------------------------
55 * Generic Routines (or almost generic)
57 * -----------------------------------------------------------------------
59 /* Update the attributes record by adding the file digest */
61 db_add_digest_to_file_record(JCR *jcr, B_DB *mdb, FileId_t FileId, char *digest,
68 Mmsg(mdb->cmd, "UPDATE File SET MD5='%s' WHERE FileId=%s", digest,
69 edit_int64(FileId, ed1));
70 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
75 /* Mark the file record as being visited during database
76 * verify compare. Stuff JobId into the MarkId field
78 int db_mark_file_record(JCR *jcr, B_DB *mdb, FileId_t FileId, JobId_t JobId)
81 char ed1[50], ed2[50];
84 Mmsg(mdb->cmd, "UPDATE File SET MarkId=%s WHERE FileId=%s",
85 edit_int64(JobId, ed1), edit_int64(FileId, ed2));
86 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
92 * Update the Job record at start of Job
94 * Returns: false on failure
98 db_update_job_start_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
100 char dt[MAX_TIME_LENGTH];
105 char ed1[50], ed2[50], ed3[50], ed4[50];
107 stime = jr->StartTime;
108 (void)localtime_r(&stime, &tm);
109 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
110 JobTDate = (btime_t)stime;
113 Mmsg(mdb->cmd, "UPDATE Job SET JobStatus='%c',Level='%c',StartTime='%s',"
114 "ClientId=%s,JobTDate=%s,PoolId=%s WHERE JobId=%s",
115 (char)(jcr->JobStatus),
116 (char)(jr->JobLevel), dt,
117 edit_int64(jr->ClientId, ed1),
118 edit_uint64(JobTDate, ed2),
119 edit_int64(jr->PoolId, ed3),
120 edit_int64(jr->JobId, ed4));
122 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
129 * Given an incoming integer, set the string buffer to either NULL or the value
132 static void edit_num_or_null(char *s, size_t n, uint64_t id) {
134 bsnprintf(s, n, id ? "%s" : "NULL", edit_int64(id, ed1));
139 * Update the Job record at end of Job
141 * Returns: 0 on failure
145 db_update_job_end_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
147 char dt[MAX_TIME_LENGTH];
148 char rdt[MAX_TIME_LENGTH];
152 char ed1[30], ed2[30], ed3[50];
154 char PoolId[50], FileSetId[50], ClientId[50], PriorJobId[50];
157 /* some values are set to zero, which translates to NULL in SQL */
158 edit_num_or_null(PoolId, sizeof(PoolId), jr->PoolId);
159 edit_num_or_null(FileSetId, sizeof(FileSetId), jr->FileSetId);
160 edit_num_or_null(ClientId, sizeof(ClientId), jr->ClientId);
162 if (jr->PriorJobId) {
163 bstrncpy(PriorJobId, edit_int64(jr->PriorJobId, ed1), sizeof(PriorJobId));
165 bstrncpy(PriorJobId, "0", sizeof(PriorJobId));
169 (void)localtime_r(&ttime, &tm);
170 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
172 if (jr->RealEndTime == 0) {
173 jr->RealEndTime = jr->EndTime;
175 ttime = jr->RealEndTime;
176 (void)localtime_r(&ttime, &tm);
177 strftime(rdt, sizeof(rdt), "%Y-%m-%d %H:%M:%S", &tm);
183 "UPDATE Job SET JobStatus='%c',EndTime='%s',"
184 "ClientId=%s,JobBytes=%s,JobFiles=%u,JobErrors=%u,VolSessionId=%u,"
185 "VolSessionTime=%u,PoolId=%s,FileSetId=%s,JobTDate=%s,"
186 "RealEndTime='%s',PriorJobId=%s WHERE JobId=%s",
187 (char)(jr->JobStatus), dt, ClientId, edit_uint64(jr->JobBytes, ed1),
188 jr->JobFiles, jr->JobErrors, jr->VolSessionId, jr->VolSessionTime,
189 PoolId, FileSetId, edit_uint64(JobTDate, ed2),
192 edit_int64(jr->JobId, ed3));
194 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
201 * Update Client record
202 * Returns: 0 on failure
206 db_update_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
209 char ed1[50], ed2[50];
213 memcpy(&tcr, cr, sizeof(tcr));
214 if (!db_create_client_record(jcr, mdb, &tcr)) {
220 "UPDATE Client SET AutoPrune=%d,FileRetention=%s,JobRetention=%s,"
221 "Uname='%s' WHERE Name='%s'",
223 edit_uint64(cr->FileRetention, ed1),
224 edit_uint64(cr->JobRetention, ed2),
225 cr->Uname, cr->Name);
227 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
234 * Update Counters record
235 * Returns: 0 on failure
238 int db_update_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
243 "UPDATE Counters SET MinValue=%d,MaxValue=%d,CurrentValue=%d,"
244 "WrapCounter='%s' WHERE Counter='%s'",
245 cr->MinValue, cr->MaxValue, cr->CurrentValue,
246 cr->WrapCounter, cr->Counter);
248 int stat = UPDATE_DB(jcr, mdb, mdb->cmd);
255 db_update_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
258 char ed1[50], ed2[50], ed3[50], ed4[50];
261 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
262 edit_int64(pr->PoolId, ed4));
263 pr->NumVols = get_sql_record_max(jcr, mdb);
264 Dmsg1(400, "NumVols=%d\n", pr->NumVols);
267 "UPDATE Pool SET NumVols=%u,MaxVols=%u,UseOnce=%d,UseCatalog=%d,"
268 "AcceptAnyVolume=%d,VolRetention='%s',VolUseDuration='%s',"
269 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,Recycle=%d,"
270 "AutoPrune=%d,LabelType=%d,LabelFormat='%s' WHERE PoolId=%s",
271 pr->NumVols, pr->MaxVols, pr->UseOnce, pr->UseCatalog,
272 pr->AcceptAnyVolume, edit_uint64(pr->VolRetention, ed1),
273 edit_uint64(pr->VolUseDuration, ed2),
274 pr->MaxVolJobs, pr->MaxVolFiles,
275 edit_uint64(pr->MaxVolBytes, ed3),
276 pr->Recycle, pr->AutoPrune, pr->LabelType,
280 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
286 db_update_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
292 Mmsg(mdb->cmd, "UPDATE Storage SET AutoChanger=%d WHERE StorageId=%s",
293 sr->AutoChanger, edit_int64(sr->StorageId, ed1));
295 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
302 * Update the Media Record at end of Session
304 * Returns: 0 on failure
308 db_update_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
310 char dt[MAX_TIME_LENGTH];
314 char ed1[50], ed2[50], ed3[50], ed4[50];
315 char ed5[50], ed6[50], ed7[50], ed8[50];
316 char ed9[50], ed10[50], ed11[50];
319 Dmsg1(100, "update_media: FirstWritten=%d\n", mr->FirstWritten);
321 if (mr->set_first_written) {
322 Dmsg1(400, "Set FirstWritten Vol=%s\n", mr->VolumeName);
323 ttime = mr->FirstWritten;
324 (void)localtime_r(&ttime, &tm);
325 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
326 Mmsg(mdb->cmd, "UPDATE Media SET FirstWritten='%s'"
327 " WHERE VolumeName='%s'", dt, mr->VolumeName);
328 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
329 Dmsg1(400, "Firstwritten=%d\n", mr->FirstWritten);
332 /* Label just done? */
333 if (mr->set_label_date) {
334 ttime = mr->LabelDate;
338 (void)localtime_r(&ttime, &tm);
339 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
340 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
341 "WHERE VolumeName='%s'", dt, mr->VolumeName);
342 UPDATE_DB(jcr, mdb, mdb->cmd);
345 if (mr->LastWritten != 0) {
346 ttime = mr->LastWritten;
347 (void)localtime_r(&ttime, &tm);
348 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
349 Mmsg(mdb->cmd, "UPDATE Media Set LastWritten='%s' "
350 "WHERE VolumeName='%s'", dt, mr->VolumeName);
351 UPDATE_DB(jcr, mdb, mdb->cmd);
354 Mmsg(mdb->cmd, "UPDATE Media SET VolJobs=%u,"
355 "VolFiles=%u,VolBlocks=%u,VolBytes=%s,VolMounts=%u,VolErrors=%u,"
356 "VolWrites=%u,MaxVolBytes=%s,VolStatus='%s',"
357 "Slot=%d,InChanger=%d,VolReadTime=%s,VolWriteTime=%s,VolParts=%d,"
358 "LabelType=%d,StorageId=%s,PoolId=%s,VolRetention=%s,VolUseDuration=%s,"
359 "MaxVolJobs=%d,MaxVolFiles=%d,Enabled=%d,LocationId=%s,"
360 "ScratchPoolId=%s,RecyclePoolId=%s,RecycleCount=%d"
361 " WHERE VolumeName='%s'",
362 mr->VolJobs, mr->VolFiles, mr->VolBlocks, edit_uint64(mr->VolBytes, ed1),
363 mr->VolMounts, mr->VolErrors, mr->VolWrites,
364 edit_uint64(mr->MaxVolBytes, ed2),
365 mr->VolStatus, mr->Slot, mr->InChanger,
366 edit_int64(mr->VolReadTime, ed3),
367 edit_int64(mr->VolWriteTime, ed4),
370 edit_int64(mr->StorageId, ed5),
371 edit_int64(mr->PoolId, ed6),
372 edit_uint64(mr->VolRetention, ed7),
373 edit_uint64(mr->VolUseDuration, ed8),
374 mr->MaxVolJobs, mr->MaxVolFiles,
375 mr->Enabled, edit_uint64(mr->LocationId, ed9),
376 edit_uint64(mr->ScratchPoolId, ed10),
377 edit_uint64(mr->RecyclePoolId, ed11),
381 Dmsg1(400, "%s\n", mdb->cmd);
383 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
385 /* Make sure InChanger is 0 for any record having the same Slot */
386 db_make_inchanger_unique(jcr, mdb, mr);
393 * Update the Media Record Default values from Pool
395 * Returns: 0 on failure
399 db_update_media_defaults(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
402 char ed1[50], ed2[50], ed3[50], ed4[50];
406 if (mr->VolumeName[0]) {
407 Mmsg(mdb->cmd, "UPDATE Media SET "
408 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
409 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s"
410 " WHERE VolumeName='%s'",
411 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
412 edit_uint64(mr->VolUseDuration, ed2),
413 mr->MaxVolJobs, mr->MaxVolFiles,
414 edit_uint64(mr->MaxVolBytes, ed3),
417 Mmsg(mdb->cmd, "UPDATE Media SET "
418 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
419 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s"
421 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
422 edit_uint64(mr->VolUseDuration, ed2),
423 mr->MaxVolJobs, mr->MaxVolFiles,
424 edit_uint64(mr->MaxVolBytes, ed3),
425 edit_int64(mr->PoolId, ed4));
428 Dmsg1(400, "%s\n", mdb->cmd);
430 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
438 * If we have a non-zero InChanger, ensure that no other Media
439 * record has InChanger set on the same Slot.
441 * This routine assumes the database is already locked.
444 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
446 char ed1[50], ed2[50];
447 if (mr->InChanger != 0 && mr->Slot != 0) {
448 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0 WHERE "
449 "Slot=%d AND StorageId=%s AND MediaId!=%s",
451 edit_int64(mr->StorageId, ed1), edit_int64(mr->MediaId, ed2));
452 Dmsg1(400, "%s\n", mdb->cmd);
453 UPDATE_DB(jcr, mdb, mdb->cmd);
457 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/