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 JobStat "
143 "WHERE JobStatus IN ('T', 'f', 'A', 'E') "
144 "AND JobId NOT IN (SELECT JobId FROM JobStat) "
145 "AND JobTDate < %s ", ed1);
146 QUERY_DB(jcr, mdb, mdb->cmd); /* TODO: get a message ? */
147 return sql_affected_rows(mdb);
151 * Given an incoming integer, set the string buffer to either NULL or the value
154 static void edit_num_or_null(char *s, size_t n, uint64_t id) {
156 bsnprintf(s, n, id ? "%s" : "NULL", edit_int64(id, ed1));
160 * Update the Job record at end of Job
162 * Returns: 0 on failure
166 db_update_job_end_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
168 char dt[MAX_TIME_LENGTH];
169 char rdt[MAX_TIME_LENGTH];
173 char ed1[30], ed2[30], ed3[50];
175 char PoolId[50], FileSetId[50], ClientId[50], PriorJobId[50];
178 /* some values are set to zero, which translates to NULL in SQL */
179 edit_num_or_null(PoolId, sizeof(PoolId), jr->PoolId);
180 edit_num_or_null(FileSetId, sizeof(FileSetId), jr->FileSetId);
181 edit_num_or_null(ClientId, sizeof(ClientId), jr->ClientId);
183 if (jr->PriorJobId) {
184 bstrncpy(PriorJobId, edit_int64(jr->PriorJobId, ed1), sizeof(PriorJobId));
186 bstrncpy(PriorJobId, "0", sizeof(PriorJobId));
190 (void)localtime_r(&ttime, &tm);
191 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
193 if (jr->RealEndTime == 0) {
194 jr->RealEndTime = jr->EndTime;
196 ttime = jr->RealEndTime;
197 (void)localtime_r(&ttime, &tm);
198 strftime(rdt, sizeof(rdt), "%Y-%m-%d %H:%M:%S", &tm);
204 "UPDATE Job SET JobStatus='%c',EndTime='%s',"
205 "ClientId=%s,JobBytes=%s,JobFiles=%u,JobErrors=%u,VolSessionId=%u,"
206 "VolSessionTime=%u,PoolId=%s,FileSetId=%s,JobTDate=%s,"
207 "RealEndTime='%s',PriorJobId=%s WHERE JobId=%s",
208 (char)(jr->JobStatus), dt, ClientId, edit_uint64(jr->JobBytes, ed1),
209 jr->JobFiles, jr->JobErrors, jr->VolSessionId, jr->VolSessionTime,
210 PoolId, FileSetId, edit_uint64(JobTDate, ed2),
213 edit_int64(jr->JobId, ed3));
215 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
222 * Update Client record
223 * Returns: 0 on failure
227 db_update_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
230 char ed1[50], ed2[50];
234 memcpy(&tcr, cr, sizeof(tcr));
235 if (!db_create_client_record(jcr, mdb, &tcr)) {
241 "UPDATE Client SET AutoPrune=%d,FileRetention=%s,JobRetention=%s,"
242 "Uname='%s' WHERE Name='%s'",
244 edit_uint64(cr->FileRetention, ed1),
245 edit_uint64(cr->JobRetention, ed2),
246 cr->Uname, cr->Name);
248 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
255 * Update Counters record
256 * Returns: 0 on failure
259 int db_update_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
264 "UPDATE Counters SET MinValue=%d,MaxValue=%d,CurrentValue=%d,"
265 "WrapCounter='%s' WHERE Counter='%s'",
266 cr->MinValue, cr->MaxValue, cr->CurrentValue,
267 cr->WrapCounter, cr->Counter);
269 int stat = UPDATE_DB(jcr, mdb, mdb->cmd);
275 int db_update_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
278 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50];
281 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
282 edit_int64(pr->PoolId, ed4));
283 pr->NumVols = get_sql_record_max(jcr, mdb);
284 Dmsg1(400, "NumVols=%d\n", pr->NumVols);
287 "UPDATE Pool SET NumVols=%u,MaxVols=%u,UseOnce=%d,UseCatalog=%d,"
288 "AcceptAnyVolume=%d,VolRetention='%s',VolUseDuration='%s',"
289 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,Recycle=%d,"
290 "AutoPrune=%d,LabelType=%d,LabelFormat='%s',RecyclePoolId=%s WHERE PoolId=%s",
291 pr->NumVols, pr->MaxVols, pr->UseOnce, pr->UseCatalog,
292 pr->AcceptAnyVolume, edit_uint64(pr->VolRetention, ed1),
293 edit_uint64(pr->VolUseDuration, ed2),
294 pr->MaxVolJobs, pr->MaxVolFiles,
295 edit_uint64(pr->MaxVolBytes, ed3),
296 pr->Recycle, pr->AutoPrune, pr->LabelType,
297 pr->LabelFormat, edit_int64(pr->RecyclePoolId,ed5),
300 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
306 db_update_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
311 Mmsg(mdb->cmd, "UPDATE Storage SET AutoChanger=%d WHERE StorageId=%s",
312 sr->AutoChanger, edit_int64(sr->StorageId, ed1));
314 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
321 * Update the Media Record at end of Session
323 * Returns: 0 on failure
327 db_update_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
329 char dt[MAX_TIME_LENGTH];
333 char ed1[50], ed2[50], ed3[50], ed4[50];
334 char ed5[50], ed6[50], ed7[50], ed8[50];
335 char ed9[50], ed10[50], ed11[50];
338 Dmsg1(100, "update_media: FirstWritten=%d\n", mr->FirstWritten);
340 if (mr->set_first_written) {
341 Dmsg1(400, "Set FirstWritten Vol=%s\n", mr->VolumeName);
342 ttime = mr->FirstWritten;
343 (void)localtime_r(&ttime, &tm);
344 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
345 Mmsg(mdb->cmd, "UPDATE Media SET FirstWritten='%s'"
346 " WHERE VolumeName='%s'", dt, mr->VolumeName);
347 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
348 Dmsg1(400, "Firstwritten=%d\n", mr->FirstWritten);
351 /* Label just done? */
352 if (mr->set_label_date) {
353 ttime = mr->LabelDate;
357 (void)localtime_r(&ttime, &tm);
358 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
359 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
360 "WHERE VolumeName='%s'", dt, mr->VolumeName);
361 UPDATE_DB(jcr, mdb, mdb->cmd);
364 if (mr->LastWritten != 0) {
365 ttime = mr->LastWritten;
366 (void)localtime_r(&ttime, &tm);
367 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
368 Mmsg(mdb->cmd, "UPDATE Media Set LastWritten='%s' "
369 "WHERE VolumeName='%s'", dt, mr->VolumeName);
370 UPDATE_DB(jcr, mdb, mdb->cmd);
373 /* sanity checks for #1066 */
374 if (mr->VolReadTime < 0) {
377 if (mr->VolWriteTime < 0) {
378 mr->VolWriteTime = 0;
381 Mmsg(mdb->cmd, "UPDATE Media SET VolJobs=%u,"
382 "VolFiles=%u,VolBlocks=%u,VolBytes=%s,VolMounts=%u,VolErrors=%u,"
383 "VolWrites=%u,MaxVolBytes=%s,VolStatus='%s',"
384 "Slot=%d,InChanger=%d,VolReadTime=%s,VolWriteTime=%s,VolParts=%d,"
385 "LabelType=%d,StorageId=%s,PoolId=%s,VolRetention=%s,VolUseDuration=%s,"
386 "MaxVolJobs=%d,MaxVolFiles=%d,Enabled=%d,LocationId=%s,"
387 "ScratchPoolId=%s,RecyclePoolId=%s,RecycleCount=%d"
388 " WHERE VolumeName='%s'",
389 mr->VolJobs, mr->VolFiles, mr->VolBlocks, edit_uint64(mr->VolBytes, ed1),
390 mr->VolMounts, mr->VolErrors, mr->VolWrites,
391 edit_uint64(mr->MaxVolBytes, ed2),
392 mr->VolStatus, mr->Slot, mr->InChanger,
393 edit_int64(mr->VolReadTime, ed3),
394 edit_int64(mr->VolWriteTime, ed4),
397 edit_int64(mr->StorageId, ed5),
398 edit_int64(mr->PoolId, ed6),
399 edit_uint64(mr->VolRetention, ed7),
400 edit_uint64(mr->VolUseDuration, ed8),
401 mr->MaxVolJobs, mr->MaxVolFiles,
402 mr->Enabled, edit_uint64(mr->LocationId, ed9),
403 edit_uint64(mr->ScratchPoolId, ed10),
404 edit_uint64(mr->RecyclePoolId, ed11),
408 Dmsg1(400, "%s\n", mdb->cmd);
410 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
412 /* Make sure InChanger is 0 for any record having the same Slot */
413 db_make_inchanger_unique(jcr, mdb, mr);
420 * Update the Media Record Default values from Pool
422 * Returns: 0 on failure
426 db_update_media_defaults(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
429 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50];
433 if (mr->VolumeName[0]) {
434 Mmsg(mdb->cmd, "UPDATE Media SET "
435 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
436 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
437 " WHERE VolumeName='%s'",
438 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
439 edit_uint64(mr->VolUseDuration, ed2),
440 mr->MaxVolJobs, mr->MaxVolFiles,
441 edit_uint64(mr->MaxVolBytes, ed3),
442 edit_uint64(mr->RecyclePoolId, ed4),
445 Mmsg(mdb->cmd, "UPDATE Media SET "
446 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
447 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
449 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
450 edit_uint64(mr->VolUseDuration, ed2),
451 mr->MaxVolJobs, mr->MaxVolFiles,
452 edit_uint64(mr->MaxVolBytes, ed3),
453 edit_int64(mr->RecyclePoolId, ed4),
454 edit_int64(mr->PoolId, ed5));
457 Dmsg1(400, "%s\n", mdb->cmd);
459 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
467 * If we have a non-zero InChanger, ensure that no other Media
468 * record has InChanger set on the same Slot.
470 * This routine assumes the database is already locked.
473 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
475 char ed1[50], ed2[50];
476 if (mr->InChanger != 0 && mr->Slot != 0 && mr->StorageId != 0 &&
478 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0 WHERE "
479 "Slot=%d AND StorageId=%s AND MediaId!=%s",
481 edit_int64(mr->StorageId, ed1), edit_int64(mr->MediaId, ed2));
482 Dmsg1(400, "%s\n", mdb->cmd);
483 UPDATE_DB(jcr, mdb, mdb->cmd);
487 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/