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], ed5[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,FileSetId=%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->FileSetId, ed4),
121 edit_int64(jr->JobId, ed5));
123 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
130 * Update Long term statistics with all jobs that were run before
134 db_update_stats(JCR *jcr, B_DB *mdb, utime_t age)
137 utime_t now = (utime_t)time(NULL);
138 edit_uint64(now - age, ed1);
140 Mmsg(mdb->cmd, fill_jobhisto, ed1);
141 QUERY_DB(jcr, mdb, mdb->cmd); /* TODO: get a message ? */
142 return sql_affected_rows(mdb);
146 * Update the Job record at end of Job
148 * Returns: 0 on failure
152 db_update_job_end_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
154 char dt[MAX_TIME_LENGTH];
155 char rdt[MAX_TIME_LENGTH];
159 char ed1[30], ed2[30], ed3[50], ed4[50];
163 if (jr->PriorJobId) {
164 bstrncpy(PriorJobId, edit_int64(jr->PriorJobId, ed1), sizeof(PriorJobId));
166 bstrncpy(PriorJobId, "0", sizeof(PriorJobId));
170 (void)localtime_r(&ttime, &tm);
171 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
173 if (jr->RealEndTime == 0) {
174 jr->RealEndTime = jr->EndTime;
176 ttime = jr->RealEndTime;
177 (void)localtime_r(&ttime, &tm);
178 strftime(rdt, sizeof(rdt), "%Y-%m-%d %H:%M:%S", &tm);
184 "UPDATE Job SET JobStatus='%c',EndTime='%s',"
185 "ClientId=%u,JobBytes=%s,ReadBytes=%s,JobFiles=%u,JobErrors=%u,VolSessionId=%u,"
186 "VolSessionTime=%u,PoolId=%u,FileSetId=%u,JobTDate=%s,"
187 "RealEndTime='%s',PriorJobId=%s,HasBase=%u WHERE JobId=%s",
188 (char)(jr->JobStatus), dt, jr->ClientId, edit_uint64(jr->JobBytes, ed1),
189 edit_uint64(jr->ReadBytes, ed4),
190 jr->JobFiles, jr->JobErrors, jr->VolSessionId, jr->VolSessionTime,
191 jr->PoolId, jr->FileSetId, edit_uint64(JobTDate, ed2),
195 edit_int64(jr->JobId, ed3));
197 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
204 * Update Client record
205 * Returns: 0 on failure
209 db_update_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
212 char ed1[50], ed2[50];
216 memcpy(&tcr, cr, sizeof(tcr));
217 if (!db_create_client_record(jcr, mdb, &tcr)) {
223 "UPDATE Client SET AutoPrune=%d,FileRetention=%s,JobRetention=%s,"
224 "Uname='%s' WHERE Name='%s'",
226 edit_uint64(cr->FileRetention, ed1),
227 edit_uint64(cr->JobRetention, ed2),
228 cr->Uname, cr->Name);
230 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
237 * Update Counters record
238 * Returns: 0 on failure
241 int db_update_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
246 "UPDATE Counters SET MinValue=%d,MaxValue=%d,CurrentValue=%d,"
247 "WrapCounter='%s' WHERE Counter='%s'",
248 cr->MinValue, cr->MaxValue, cr->CurrentValue,
249 cr->WrapCounter, cr->Counter);
251 int stat = UPDATE_DB(jcr, mdb, mdb->cmd);
257 int db_update_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
260 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50];
263 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
264 edit_int64(pr->PoolId, ed4));
265 pr->NumVols = get_sql_record_max(jcr, mdb);
266 Dmsg1(400, "NumVols=%d\n", pr->NumVols);
269 "UPDATE Pool SET NumVols=%u,MaxVols=%u,UseOnce=%d,UseCatalog=%d,"
270 "AcceptAnyVolume=%d,VolRetention='%s',VolUseDuration='%s',"
271 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,Recycle=%d,"
272 "AutoPrune=%d,LabelType=%d,LabelFormat='%s',RecyclePoolId=%s,"
273 "ScratchPoolId=%s,ActionOnPurge=%d WHERE PoolId=%s",
274 pr->NumVols, pr->MaxVols, pr->UseOnce, pr->UseCatalog,
275 pr->AcceptAnyVolume, edit_uint64(pr->VolRetention, ed1),
276 edit_uint64(pr->VolUseDuration, ed2),
277 pr->MaxVolJobs, pr->MaxVolFiles,
278 edit_uint64(pr->MaxVolBytes, ed3),
279 pr->Recycle, pr->AutoPrune, pr->LabelType,
280 pr->LabelFormat, edit_int64(pr->RecyclePoolId,ed5),
281 edit_int64(pr->ScratchPoolId,ed6),
284 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
290 db_update_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
295 Mmsg(mdb->cmd, "UPDATE Storage SET AutoChanger=%d WHERE StorageId=%s",
296 sr->AutoChanger, edit_int64(sr->StorageId, ed1));
298 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
305 * Update the Media Record at end of Session
307 * Returns: 0 on failure
311 db_update_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
313 char dt[MAX_TIME_LENGTH];
317 char ed1[50], ed2[50], ed3[50], ed4[50];
318 char ed5[50], ed6[50], ed7[50], ed8[50];
319 char ed9[50], ed10[50], ed11[50];
322 Dmsg1(100, "update_media: FirstWritten=%d\n", mr->FirstWritten);
324 if (mr->set_first_written) {
325 Dmsg1(400, "Set FirstWritten Vol=%s\n", mr->VolumeName);
326 ttime = mr->FirstWritten;
327 (void)localtime_r(&ttime, &tm);
328 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
329 Mmsg(mdb->cmd, "UPDATE Media SET FirstWritten='%s'"
330 " WHERE VolumeName='%s'", dt, mr->VolumeName);
331 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
332 Dmsg1(400, "Firstwritten=%d\n", mr->FirstWritten);
335 /* Label just done? */
336 if (mr->set_label_date) {
337 ttime = mr->LabelDate;
341 (void)localtime_r(&ttime, &tm);
342 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
343 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
344 "WHERE VolumeName='%s'", dt, mr->VolumeName);
345 UPDATE_DB(jcr, mdb, mdb->cmd);
348 if (mr->LastWritten != 0) {
349 ttime = mr->LastWritten;
350 (void)localtime_r(&ttime, &tm);
351 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
352 Mmsg(mdb->cmd, "UPDATE Media Set LastWritten='%s' "
353 "WHERE VolumeName='%s'", dt, mr->VolumeName);
354 UPDATE_DB(jcr, mdb, mdb->cmd);
357 /* sanity checks for #1066 */
358 if (mr->VolReadTime < 0) {
361 if (mr->VolWriteTime < 0) {
362 mr->VolWriteTime = 0;
365 Mmsg(mdb->cmd, "UPDATE Media SET VolJobs=%u,"
366 "VolFiles=%u,VolBlocks=%u,VolBytes=%s,VolMounts=%u,VolErrors=%u,"
367 "VolWrites=%u,MaxVolBytes=%s,VolStatus='%s',"
368 "Slot=%d,InChanger=%d,VolReadTime=%s,VolWriteTime=%s,VolParts=%d,"
369 "LabelType=%d,StorageId=%s,PoolId=%s,VolRetention=%s,VolUseDuration=%s,"
370 "MaxVolJobs=%d,MaxVolFiles=%d,Enabled=%d,LocationId=%s,"
371 "ScratchPoolId=%s,RecyclePoolId=%s,RecycleCount=%d,Recycle=%d,ActionOnPurge=%d"
372 " WHERE VolumeName='%s'",
373 mr->VolJobs, mr->VolFiles, mr->VolBlocks, edit_uint64(mr->VolBytes, ed1),
374 mr->VolMounts, mr->VolErrors, mr->VolWrites,
375 edit_uint64(mr->MaxVolBytes, ed2),
376 mr->VolStatus, mr->Slot, mr->InChanger,
377 edit_int64(mr->VolReadTime, ed3),
378 edit_int64(mr->VolWriteTime, ed4),
381 edit_int64(mr->StorageId, ed5),
382 edit_int64(mr->PoolId, ed6),
383 edit_uint64(mr->VolRetention, ed7),
384 edit_uint64(mr->VolUseDuration, ed8),
385 mr->MaxVolJobs, mr->MaxVolFiles,
386 mr->Enabled, edit_uint64(mr->LocationId, ed9),
387 edit_uint64(mr->ScratchPoolId, ed10),
388 edit_uint64(mr->RecyclePoolId, ed11),
389 mr->RecycleCount,mr->Recycle, mr->ActionOnPurge,
392 Dmsg1(400, "%s\n", mdb->cmd);
394 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
396 /* Make sure InChanger is 0 for any record having the same Slot */
397 db_make_inchanger_unique(jcr, mdb, mr);
404 * Update the Media Record Default values from Pool
406 * Returns: 0 on failure
410 db_update_media_defaults(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
413 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50];
417 if (mr->VolumeName[0]) {
418 Mmsg(mdb->cmd, "UPDATE Media SET "
419 "ActionOnPurge=%d, Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
420 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
421 " WHERE VolumeName='%s'",
422 mr->ActionOnPurge, mr->Recycle,edit_uint64(mr->VolRetention, ed1),
423 edit_uint64(mr->VolUseDuration, ed2),
424 mr->MaxVolJobs, mr->MaxVolFiles,
425 edit_uint64(mr->MaxVolBytes, ed3),
426 edit_uint64(mr->RecyclePoolId, ed4),
429 Mmsg(mdb->cmd, "UPDATE Media SET "
430 "ActionOnPurge=%d, Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
431 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
433 mr->ActionOnPurge, mr->Recycle,edit_uint64(mr->VolRetention, ed1),
434 edit_uint64(mr->VolUseDuration, ed2),
435 mr->MaxVolJobs, mr->MaxVolFiles,
436 edit_uint64(mr->MaxVolBytes, ed3),
437 edit_int64(mr->RecyclePoolId, ed4),
438 edit_int64(mr->PoolId, ed5));
441 Dmsg1(400, "%s\n", mdb->cmd);
443 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
451 * If we have a non-zero InChanger, ensure that no other Media
452 * record has InChanger set on the same Slot.
454 * This routine assumes the database is already locked.
457 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
459 char ed1[50], ed2[50];
460 if (mr->InChanger != 0 && mr->Slot != 0 && mr->StorageId != 0) {
462 if (mr->MediaId != 0) {
463 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
464 "Slot=%d AND StorageId=%s AND MediaId!=%s",
466 edit_int64(mr->StorageId, ed1), edit_int64(mr->MediaId, ed2));
468 } else if (*mr->VolumeName) {
469 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
470 "Slot=%d AND StorageId=%s AND VolumeName!='%s'",
472 edit_int64(mr->StorageId, ed1), mr->VolumeName);
474 } else { /* used by ua_label to reset all volume with this slot */
475 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
476 "Slot=%d AND StorageId=%s",
478 edit_int64(mr->StorageId, ed1), mr->VolumeName);
480 Dmsg1(100, "%s\n", mdb->cmd);
481 UPDATE_DB(jcr, mdb, mdb->cmd);
485 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/