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 three of the GNU Affero 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 Affero 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
33 * Version $Id: sql_update.c 8478 2009-02-18 20:11:55Z kerns $
38 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI
44 /* -----------------------------------------------------------------------
46 * Generic Routines (or almost generic)
48 * -----------------------------------------------------------------------
51 /* -----------------------------------------------------------------------
53 * Generic Routines (or almost generic)
55 * -----------------------------------------------------------------------
57 /* Update the attributes record by adding the file digest */
59 db_add_digest_to_file_record(JCR *jcr, B_DB *mdb, FileId_t FileId, char *digest,
66 Mmsg(mdb->cmd, "UPDATE File SET MD5='%s' WHERE FileId=%s", digest,
67 edit_int64(FileId, ed1));
68 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
73 /* Mark the file record as being visited during database
74 * verify compare. Stuff JobId into the MarkId field
76 int db_mark_file_record(JCR *jcr, B_DB *mdb, FileId_t FileId, JobId_t JobId)
79 char ed1[50], ed2[50];
82 Mmsg(mdb->cmd, "UPDATE File SET MarkId=%s WHERE FileId=%s",
83 edit_int64(JobId, ed1), edit_int64(FileId, ed2));
84 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
90 * Update the Job record at start of Job
92 * Returns: false on failure
96 db_update_job_start_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
98 char dt[MAX_TIME_LENGTH];
103 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50];
105 stime = jr->StartTime;
106 (void)localtime_r(&stime, &tm);
107 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
108 JobTDate = (btime_t)stime;
111 Mmsg(mdb->cmd, "UPDATE Job SET JobStatus='%c',Level='%c',StartTime='%s',"
112 "ClientId=%s,JobTDate=%s,PoolId=%s,FileSetId=%s WHERE JobId=%s",
113 (char)(jcr->JobStatus),
114 (char)(jr->JobLevel), dt,
115 edit_int64(jr->ClientId, ed1),
116 edit_uint64(JobTDate, ed2),
117 edit_int64(jr->PoolId, ed3),
118 edit_int64(jr->FileSetId, ed4),
119 edit_int64(jr->JobId, ed5));
121 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
128 * Update Long term statistics with all jobs that were run before
132 db_update_stats(JCR *jcr, B_DB *mdb, utime_t age)
135 utime_t now = (utime_t)time(NULL);
136 edit_uint64(now - age, ed1);
138 Mmsg(mdb->cmd, fill_jobhisto, ed1);
139 QUERY_DB(jcr, mdb, mdb->cmd); /* TODO: get a message ? */
140 return sql_affected_rows(mdb);
144 * Update the Job record at end of Job
146 * Returns: 0 on failure
150 db_update_job_end_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
152 char dt[MAX_TIME_LENGTH];
153 char rdt[MAX_TIME_LENGTH];
157 char ed1[30], ed2[30], ed3[50], ed4[50];
161 if (jr->PriorJobId) {
162 bstrncpy(PriorJobId, edit_int64(jr->PriorJobId, ed1), sizeof(PriorJobId));
164 bstrncpy(PriorJobId, "0", sizeof(PriorJobId));
168 (void)localtime_r(&ttime, &tm);
169 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
171 if (jr->RealEndTime == 0) {
172 jr->RealEndTime = jr->EndTime;
174 ttime = jr->RealEndTime;
175 (void)localtime_r(&ttime, &tm);
176 strftime(rdt, sizeof(rdt), "%Y-%m-%d %H:%M:%S", &tm);
182 "UPDATE Job SET JobStatus='%c',EndTime='%s',"
183 "ClientId=%u,JobBytes=%s,ReadBytes=%s,JobFiles=%u,JobErrors=%u,VolSessionId=%u,"
184 "VolSessionTime=%u,PoolId=%u,FileSetId=%u,JobTDate=%s,"
185 "RealEndTime='%s',PriorJobId=%s,HasBase=%u,PurgedFiles=%u WHERE JobId=%s",
186 (char)(jr->JobStatus), dt, jr->ClientId, edit_uint64(jr->JobBytes, ed1),
187 edit_uint64(jr->ReadBytes, ed4),
188 jr->JobFiles, jr->JobErrors, jr->VolSessionId, jr->VolSessionTime,
189 jr->PoolId, jr->FileSetId, edit_uint64(JobTDate, ed2),
190 rdt, PriorJobId, jr->HasBase, jr->PurgedFiles,
191 edit_int64(jr->JobId, ed3));
193 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
200 * Update Client record
201 * Returns: 0 on failure
205 db_update_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
208 char ed1[50], ed2[50];
212 memcpy(&tcr, cr, sizeof(tcr));
213 if (!db_create_client_record(jcr, mdb, &tcr)) {
219 "UPDATE Client SET AutoPrune=%d,FileRetention=%s,JobRetention=%s,"
220 "Uname='%s' WHERE Name='%s'",
222 edit_uint64(cr->FileRetention, ed1),
223 edit_uint64(cr->JobRetention, ed2),
224 cr->Uname, cr->Name);
226 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
233 * Update Counters record
234 * Returns: 0 on failure
237 int db_update_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
242 "UPDATE Counters SET \"MinValue\"=%d,\"MaxValue\"=%d,CurrentValue=%d,"
243 "WrapCounter='%s' WHERE Counter='%s'",
244 cr->MinValue, cr->MaxValue, cr->CurrentValue,
245 cr->WrapCounter, cr->Counter);
247 int stat = UPDATE_DB(jcr, mdb, mdb->cmd);
253 int db_update_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
256 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50];
259 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
260 edit_int64(pr->PoolId, ed4));
261 pr->NumVols = get_sql_record_max(jcr, mdb);
262 Dmsg1(400, "NumVols=%d\n", pr->NumVols);
265 "UPDATE Pool SET NumVols=%u,MaxVols=%u,UseOnce=%d,UseCatalog=%d,"
266 "AcceptAnyVolume=%d,VolRetention='%s',VolUseDuration='%s',"
267 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,Recycle=%d,"
268 "AutoPrune=%d,LabelType=%d,LabelFormat='%s',RecyclePoolId=%s,"
269 "ScratchPoolId=%s,ActionOnPurge=%d WHERE PoolId=%s",
270 pr->NumVols, pr->MaxVols, pr->UseOnce, pr->UseCatalog,
271 pr->AcceptAnyVolume, edit_uint64(pr->VolRetention, ed1),
272 edit_uint64(pr->VolUseDuration, ed2),
273 pr->MaxVolJobs, pr->MaxVolFiles,
274 edit_uint64(pr->MaxVolBytes, ed3),
275 pr->Recycle, pr->AutoPrune, pr->LabelType,
276 pr->LabelFormat, edit_int64(pr->RecyclePoolId,ed5),
277 edit_int64(pr->ScratchPoolId,ed6),
280 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
286 db_update_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
291 Mmsg(mdb->cmd, "UPDATE Storage SET AutoChanger=%d WHERE StorageId=%s",
292 sr->AutoChanger, edit_int64(sr->StorageId, ed1));
294 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
301 * Update the Media Record at end of Session
303 * Returns: 0 on failure
307 db_update_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
309 char dt[MAX_TIME_LENGTH];
313 char ed1[50], ed2[50], ed3[50], ed4[50];
314 char ed5[50], ed6[50], ed7[50], ed8[50];
315 char ed9[50], ed10[50], ed11[50];
318 Dmsg1(100, "update_media: FirstWritten=%d\n", mr->FirstWritten);
320 if (mr->set_first_written) {
321 Dmsg1(400, "Set FirstWritten Vol=%s\n", mr->VolumeName);
322 ttime = mr->FirstWritten;
323 (void)localtime_r(&ttime, &tm);
324 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
325 Mmsg(mdb->cmd, "UPDATE Media SET FirstWritten='%s'"
326 " WHERE VolumeName='%s'", dt, mr->VolumeName);
327 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
328 Dmsg1(400, "Firstwritten=%d\n", mr->FirstWritten);
331 /* Label just done? */
332 if (mr->set_label_date) {
333 ttime = mr->LabelDate;
337 (void)localtime_r(&ttime, &tm);
338 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
339 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
340 "WHERE VolumeName='%s'", dt, mr->VolumeName);
341 UPDATE_DB(jcr, mdb, mdb->cmd);
344 if (mr->LastWritten != 0) {
345 ttime = mr->LastWritten;
346 (void)localtime_r(&ttime, &tm);
347 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
348 Mmsg(mdb->cmd, "UPDATE Media Set LastWritten='%s' "
349 "WHERE VolumeName='%s'", dt, mr->VolumeName);
350 UPDATE_DB(jcr, mdb, mdb->cmd);
353 /* sanity checks for #1066 */
354 if (mr->VolReadTime < 0) {
357 if (mr->VolWriteTime < 0) {
358 mr->VolWriteTime = 0;
361 Mmsg(mdb->cmd, "UPDATE Media SET VolJobs=%u,"
362 "VolFiles=%u,VolBlocks=%u,VolBytes=%s,VolMounts=%u,VolErrors=%u,"
363 "VolWrites=%u,MaxVolBytes=%s,VolStatus='%s',"
364 "Slot=%d,InChanger=%d,VolReadTime=%s,VolWriteTime=%s,VolParts=%d,"
365 "LabelType=%d,StorageId=%s,PoolId=%s,VolRetention=%s,VolUseDuration=%s,"
366 "MaxVolJobs=%d,MaxVolFiles=%d,Enabled=%d,LocationId=%s,"
367 "ScratchPoolId=%s,RecyclePoolId=%s,RecycleCount=%d,Recycle=%d,ActionOnPurge=%d"
368 " WHERE VolumeName='%s'",
369 mr->VolJobs, mr->VolFiles, mr->VolBlocks, edit_uint64(mr->VolBytes, ed1),
370 mr->VolMounts, mr->VolErrors, mr->VolWrites,
371 edit_uint64(mr->MaxVolBytes, ed2),
372 mr->VolStatus, mr->Slot, mr->InChanger,
373 edit_int64(mr->VolReadTime, ed3),
374 edit_int64(mr->VolWriteTime, ed4),
377 edit_int64(mr->StorageId, ed5),
378 edit_int64(mr->PoolId, ed6),
379 edit_uint64(mr->VolRetention, ed7),
380 edit_uint64(mr->VolUseDuration, ed8),
381 mr->MaxVolJobs, mr->MaxVolFiles,
382 mr->Enabled, edit_uint64(mr->LocationId, ed9),
383 edit_uint64(mr->ScratchPoolId, ed10),
384 edit_uint64(mr->RecyclePoolId, ed11),
385 mr->RecycleCount,mr->Recycle, mr->ActionOnPurge,
388 Dmsg1(400, "%s\n", mdb->cmd);
390 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
392 /* Make sure InChanger is 0 for any record having the same Slot */
393 db_make_inchanger_unique(jcr, mdb, mr);
400 * Update the Media Record Default values from Pool
402 * Returns: 0 on failure
406 db_update_media_defaults(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
409 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50];
413 if (mr->VolumeName[0]) {
414 Mmsg(mdb->cmd, "UPDATE Media SET "
415 "ActionOnPurge=%d, Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
416 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
417 " WHERE VolumeName='%s'",
418 mr->ActionOnPurge, mr->Recycle,edit_uint64(mr->VolRetention, ed1),
419 edit_uint64(mr->VolUseDuration, ed2),
420 mr->MaxVolJobs, mr->MaxVolFiles,
421 edit_uint64(mr->MaxVolBytes, ed3),
422 edit_uint64(mr->RecyclePoolId, ed4),
425 Mmsg(mdb->cmd, "UPDATE Media SET "
426 "ActionOnPurge=%d, Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
427 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
429 mr->ActionOnPurge, mr->Recycle,edit_uint64(mr->VolRetention, ed1),
430 edit_uint64(mr->VolUseDuration, ed2),
431 mr->MaxVolJobs, mr->MaxVolFiles,
432 edit_uint64(mr->MaxVolBytes, ed3),
433 edit_int64(mr->RecyclePoolId, ed4),
434 edit_int64(mr->PoolId, ed5));
437 Dmsg1(400, "%s\n", mdb->cmd);
439 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
447 * If we have a non-zero InChanger, ensure that no other Media
448 * record has InChanger set on the same Slot.
450 * This routine assumes the database is already locked.
453 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
455 char ed1[50], ed2[50];
456 if (mr->InChanger != 0 && mr->Slot != 0 && mr->StorageId != 0) {
458 if (mr->MediaId != 0) {
459 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
460 "Slot=%d AND StorageId=%s AND MediaId!=%s",
462 edit_int64(mr->StorageId, ed1), edit_int64(mr->MediaId, ed2));
464 } else if (*mr->VolumeName) {
465 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
466 "Slot=%d AND StorageId=%s AND VolumeName!='%s'",
468 edit_int64(mr->StorageId, ed1), mr->VolumeName);
470 } else { /* used by ua_label to reset all volume with this slot */
471 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
472 "Slot=%d AND StorageId=%s",
474 edit_int64(mr->StorageId, ed1), mr->VolumeName);
476 Dmsg1(100, "%s\n", mdb->cmd);
477 UPDATE_DB(jcr, mdb, mdb->cmd);
481 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */