2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2008 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 two of the GNU 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 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 Update record interface routines
31 * Kern Sibbald, March 2000
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 || HAVE_DBI
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 * Update Long term statistics with all jobs that were run before
133 db_update_stats(JCR *jcr, B_DB *mdb, utime_t age)
136 utime_t now = (utime_t)time(NULL);
137 edit_uint64(now - age, ed1);
140 "INSERT INTO JobHistory "
143 "WHERE JobStatus IN ('T', 'f', 'A', 'E') "
144 "AND JobId NOT IN (SELECT JobId FROM JobHistory) "
145 "AND JobTDate < %s ", ed1);
146 QUERY_DB(jcr, mdb, mdb->cmd); /* TODO: get a message ? */
147 return sql_affected_rows(mdb);
151 * Update the Job record at end of Job
153 * Returns: 0 on failure
157 db_update_job_end_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
159 char dt[MAX_TIME_LENGTH];
160 char rdt[MAX_TIME_LENGTH];
164 char ed1[30], ed2[30], ed3[50];
168 if (jr->PriorJobId) {
169 bstrncpy(PriorJobId, edit_int64(jr->PriorJobId, ed1), sizeof(PriorJobId));
171 bstrncpy(PriorJobId, "0", sizeof(PriorJobId));
175 (void)localtime_r(&ttime, &tm);
176 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
178 if (jr->RealEndTime == 0) {
179 jr->RealEndTime = jr->EndTime;
181 ttime = jr->RealEndTime;
182 (void)localtime_r(&ttime, &tm);
183 strftime(rdt, sizeof(rdt), "%Y-%m-%d %H:%M:%S", &tm);
189 "UPDATE Job SET JobStatus='%c',EndTime='%s',"
190 "ClientId=%u,JobBytes=%s,JobFiles=%u,JobErrors=%u,VolSessionId=%u,"
191 "VolSessionTime=%u,PoolId=%u,FileSetId=%u,JobTDate=%s,"
192 "RealEndTime='%s',PriorJobId=%s WHERE JobId=%s",
193 (char)(jr->JobStatus), dt, jr->ClientId, edit_uint64(jr->JobBytes, ed1),
194 jr->JobFiles, jr->JobErrors, jr->VolSessionId, jr->VolSessionTime,
195 jr->PoolId, jr->FileSetId, edit_uint64(JobTDate, ed2),
198 edit_int64(jr->JobId, ed3));
200 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
207 * Update Client record
208 * Returns: 0 on failure
212 db_update_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
215 char ed1[50], ed2[50];
219 memcpy(&tcr, cr, sizeof(tcr));
220 if (!db_create_client_record(jcr, mdb, &tcr)) {
226 "UPDATE Client SET AutoPrune=%d,FileRetention=%s,JobRetention=%s,"
227 "Uname='%s' WHERE Name='%s'",
229 edit_uint64(cr->FileRetention, ed1),
230 edit_uint64(cr->JobRetention, ed2),
231 cr->Uname, cr->Name);
233 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
240 * Update Counters record
241 * Returns: 0 on failure
244 int db_update_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
249 "UPDATE Counters SET MinValue=%d,MaxValue=%d,CurrentValue=%d,"
250 "WrapCounter='%s' WHERE Counter='%s'",
251 cr->MinValue, cr->MaxValue, cr->CurrentValue,
252 cr->WrapCounter, cr->Counter);
254 int stat = UPDATE_DB(jcr, mdb, mdb->cmd);
260 int db_update_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
263 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50];
266 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
267 edit_int64(pr->PoolId, ed4));
268 pr->NumVols = get_sql_record_max(jcr, mdb);
269 Dmsg1(400, "NumVols=%d\n", pr->NumVols);
272 "UPDATE Pool SET NumVols=%u,MaxVols=%u,UseOnce=%d,UseCatalog=%d,"
273 "AcceptAnyVolume=%d,VolRetention='%s',VolUseDuration='%s',"
274 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,Recycle=%d,"
275 "AutoPrune=%d,LabelType=%d,LabelFormat='%s',RecyclePoolId=%s,"
276 "ScratchPoolId=%s WHERE PoolId=%s",
277 pr->NumVols, pr->MaxVols, pr->UseOnce, pr->UseCatalog,
278 pr->AcceptAnyVolume, edit_uint64(pr->VolRetention, ed1),
279 edit_uint64(pr->VolUseDuration, ed2),
280 pr->MaxVolJobs, pr->MaxVolFiles,
281 edit_uint64(pr->MaxVolBytes, ed3),
282 pr->Recycle, pr->AutoPrune, pr->LabelType,
283 pr->LabelFormat, edit_int64(pr->RecyclePoolId,ed5),
284 edit_int64(pr->ScratchPoolId,ed6),ed4);
285 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
291 db_update_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
296 Mmsg(mdb->cmd, "UPDATE Storage SET AutoChanger=%d WHERE StorageId=%s",
297 sr->AutoChanger, edit_int64(sr->StorageId, ed1));
299 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
306 * Update the Media Record at end of Session
308 * Returns: 0 on failure
312 db_update_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
314 char dt[MAX_TIME_LENGTH];
318 char ed1[50], ed2[50], ed3[50], ed4[50];
319 char ed5[50], ed6[50], ed7[50], ed8[50];
320 char ed9[50], ed10[50], ed11[50];
323 Dmsg1(100, "update_media: FirstWritten=%d\n", mr->FirstWritten);
325 if (mr->set_first_written) {
326 Dmsg1(400, "Set FirstWritten Vol=%s\n", mr->VolumeName);
327 ttime = mr->FirstWritten;
328 (void)localtime_r(&ttime, &tm);
329 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
330 Mmsg(mdb->cmd, "UPDATE Media SET FirstWritten='%s'"
331 " WHERE VolumeName='%s'", dt, mr->VolumeName);
332 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
333 Dmsg1(400, "Firstwritten=%d\n", mr->FirstWritten);
336 /* Label just done? */
337 if (mr->set_label_date) {
338 ttime = mr->LabelDate;
342 (void)localtime_r(&ttime, &tm);
343 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
344 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
345 "WHERE VolumeName='%s'", dt, mr->VolumeName);
346 UPDATE_DB(jcr, mdb, mdb->cmd);
349 if (mr->LastWritten != 0) {
350 ttime = mr->LastWritten;
351 (void)localtime_r(&ttime, &tm);
352 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
353 Mmsg(mdb->cmd, "UPDATE Media Set LastWritten='%s' "
354 "WHERE VolumeName='%s'", dt, mr->VolumeName);
355 UPDATE_DB(jcr, mdb, mdb->cmd);
358 /* sanity checks for #1066 */
359 if (mr->VolReadTime < 0) {
362 if (mr->VolWriteTime < 0) {
363 mr->VolWriteTime = 0;
366 Mmsg(mdb->cmd, "UPDATE Media SET VolJobs=%u,"
367 "VolFiles=%u,VolBlocks=%u,VolBytes=%s,VolMounts=%u,VolErrors=%u,"
368 "VolWrites=%u,MaxVolBytes=%s,VolStatus='%s',"
369 "Slot=%d,InChanger=%d,VolReadTime=%s,VolWriteTime=%s,VolParts=%d,"
370 "LabelType=%d,StorageId=%s,PoolId=%s,VolRetention=%s,VolUseDuration=%s,"
371 "MaxVolJobs=%d,MaxVolFiles=%d,Enabled=%d,LocationId=%s,"
372 "ScratchPoolId=%s,RecyclePoolId=%s,RecycleCount=%d,Recycle=%d"
373 " WHERE VolumeName='%s'",
374 mr->VolJobs, mr->VolFiles, mr->VolBlocks, edit_uint64(mr->VolBytes, ed1),
375 mr->VolMounts, mr->VolErrors, mr->VolWrites,
376 edit_uint64(mr->MaxVolBytes, ed2),
377 mr->VolStatus, mr->Slot, mr->InChanger,
378 edit_int64(mr->VolReadTime, ed3),
379 edit_int64(mr->VolWriteTime, ed4),
382 edit_int64(mr->StorageId, ed5),
383 edit_int64(mr->PoolId, ed6),
384 edit_uint64(mr->VolRetention, ed7),
385 edit_uint64(mr->VolUseDuration, ed8),
386 mr->MaxVolJobs, mr->MaxVolFiles,
387 mr->Enabled, edit_uint64(mr->LocationId, ed9),
388 edit_uint64(mr->ScratchPoolId, ed10),
389 edit_uint64(mr->RecyclePoolId, ed11),
390 mr->RecycleCount,mr->Recycle,
393 Dmsg1(400, "%s\n", mdb->cmd);
395 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
397 /* Make sure InChanger is 0 for any record having the same Slot */
398 db_make_inchanger_unique(jcr, mdb, mr);
405 * Update the Media Record Default values from Pool
407 * Returns: 0 on failure
411 db_update_media_defaults(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
414 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50];
418 if (mr->VolumeName[0]) {
419 Mmsg(mdb->cmd, "UPDATE Media SET "
420 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
421 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
422 " WHERE VolumeName='%s'",
423 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
424 edit_uint64(mr->VolUseDuration, ed2),
425 mr->MaxVolJobs, mr->MaxVolFiles,
426 edit_uint64(mr->MaxVolBytes, ed3),
427 edit_uint64(mr->RecyclePoolId, ed4),
430 Mmsg(mdb->cmd, "UPDATE Media SET "
431 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
432 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
434 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
435 edit_uint64(mr->VolUseDuration, ed2),
436 mr->MaxVolJobs, mr->MaxVolFiles,
437 edit_uint64(mr->MaxVolBytes, ed3),
438 edit_int64(mr->RecyclePoolId, ed4),
439 edit_int64(mr->PoolId, ed5));
442 Dmsg1(400, "%s\n", mdb->cmd);
444 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
452 * If we have a non-zero InChanger, ensure that no other Media
453 * record has InChanger set on the same Slot.
455 * This routine assumes the database is already locked.
458 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
460 char ed1[50], ed2[50];
461 if (mr->InChanger != 0 && mr->Slot != 0 && mr->StorageId != 0) {
463 if (mr->MediaId != 0) {
464 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
465 "Slot=%d AND StorageId=%s AND MediaId!=%s",
467 edit_int64(mr->StorageId, ed1), edit_int64(mr->MediaId, ed2));
469 } else if (*mr->VolumeName) {
470 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
471 "Slot=%d AND StorageId=%s AND VolumeName!='%s'",
473 edit_int64(mr->StorageId, ed1), mr->VolumeName);
475 } else { /* used by ua_label to reset all volume with this slot */
476 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
477 "Slot=%d AND StorageId=%s",
479 edit_int64(mr->StorageId, ed1), mr->VolumeName);
481 Dmsg1(100, "%s\n", mdb->cmd);
482 UPDATE_DB(jcr, mdb, mdb->cmd);
486 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/