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,
64 int len = strlen(digest);
67 mdb->esc_name = check_pool_memory_size(mdb->esc_name, len*2+1);
68 mdb->db_escape_string(jcr, mdb->esc_name, digest, len);
69 Mmsg(mdb->cmd, "UPDATE File SET MD5='%s' WHERE FileId=%s", mdb->esc_name,
70 edit_int64(FileId, ed1));
71 ret = UPDATE_DB(jcr, mdb, mdb->cmd);
76 /* Mark the file record as being visited during database
77 * verify compare. Stuff JobId into the MarkId field
79 int db_mark_file_record(JCR *jcr, B_DB *mdb, FileId_t FileId, JobId_t JobId)
82 char ed1[50], ed2[50];
85 Mmsg(mdb->cmd, "UPDATE File SET MarkId=%s WHERE FileId=%s",
86 edit_int64(JobId, ed1), edit_int64(FileId, ed2));
87 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
93 * Update the Job record at start of Job
95 * Returns: false on failure
99 db_update_job_start_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
101 char dt[MAX_TIME_LENGTH];
106 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50];
108 stime = jr->StartTime;
109 (void)localtime_r(&stime, &tm);
110 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
111 JobTDate = (btime_t)stime;
114 Mmsg(mdb->cmd, "UPDATE Job SET JobStatus='%c',Level='%c',StartTime='%s',"
115 "ClientId=%s,JobTDate=%s,PoolId=%s,FileSetId=%s WHERE JobId=%s",
116 (char)(jcr->JobStatus),
117 (char)(jr->JobLevel), dt,
118 edit_int64(jr->ClientId, ed1),
119 edit_uint64(JobTDate, ed2),
120 edit_int64(jr->PoolId, ed3),
121 edit_int64(jr->FileSetId, ed4),
122 edit_int64(jr->JobId, ed5));
124 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
131 * Update Long term statistics with all jobs that were run before
135 db_update_stats(JCR *jcr, B_DB *mdb, utime_t age)
139 utime_t now = (utime_t)time(NULL);
140 edit_uint64(now - age, ed1);
144 Mmsg(mdb->cmd, fill_jobhisto, ed1);
145 QUERY_DB(jcr, mdb, mdb->cmd); /* TODO: get a message ? */
146 rows = sql_affected_rows(mdb);
154 * Update the Job record at end of Job
156 * Returns: 0 on failure
160 db_update_job_end_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
162 char dt[MAX_TIME_LENGTH];
163 char rdt[MAX_TIME_LENGTH];
167 char ed1[30], ed2[30], ed3[50], ed4[50];
171 if (jr->PriorJobId) {
172 bstrncpy(PriorJobId, edit_int64(jr->PriorJobId, ed1), sizeof(PriorJobId));
174 bstrncpy(PriorJobId, "0", sizeof(PriorJobId));
178 (void)localtime_r(&ttime, &tm);
179 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
181 if (jr->RealEndTime == 0) {
182 jr->RealEndTime = jr->EndTime;
184 ttime = jr->RealEndTime;
185 (void)localtime_r(&ttime, &tm);
186 strftime(rdt, sizeof(rdt), "%Y-%m-%d %H:%M:%S", &tm);
192 "UPDATE Job SET JobStatus='%c',EndTime='%s',"
193 "ClientId=%u,JobBytes=%s,ReadBytes=%s,JobFiles=%u,JobErrors=%u,VolSessionId=%u,"
194 "VolSessionTime=%u,PoolId=%u,FileSetId=%u,JobTDate=%s,"
195 "RealEndTime='%s',PriorJobId=%s,HasBase=%u,PurgedFiles=%u WHERE JobId=%s",
196 (char)(jr->JobStatus), dt, jr->ClientId, edit_uint64(jr->JobBytes, ed1),
197 edit_uint64(jr->ReadBytes, ed4),
198 jr->JobFiles, jr->JobErrors, jr->VolSessionId, jr->VolSessionTime,
199 jr->PoolId, jr->FileSetId, edit_uint64(JobTDate, ed2),
200 rdt, PriorJobId, jr->HasBase, jr->PurgedFiles,
201 edit_int64(jr->JobId, ed3));
203 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
210 * Update Client record
211 * Returns: 0 on failure
215 db_update_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
218 char ed1[50], ed2[50];
219 char esc_name[MAX_ESCAPE_NAME_LENGTH];
220 char esc_uname[MAX_ESCAPE_NAME_LENGTH];
224 memcpy(&tcr, cr, sizeof(tcr));
225 if (!db_create_client_record(jcr, mdb, &tcr)) {
230 mdb->db_escape_string(jcr, esc_name, cr->Name, strlen(cr->Name));
231 mdb->db_escape_string(jcr, esc_uname, cr->Uname, strlen(cr->Uname));
233 "UPDATE Client SET AutoPrune=%d,FileRetention=%s,JobRetention=%s,"
234 "Uname='%s' WHERE Name='%s'",
236 edit_uint64(cr->FileRetention, ed1),
237 edit_uint64(cr->JobRetention, ed2),
238 esc_uname, esc_name);
240 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
247 * Update Counters record
248 * Returns: 0 on failure
251 int db_update_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
253 char esc[MAX_ESCAPE_NAME_LENGTH];
255 mdb->db_escape_string(jcr, esc, cr->Counter, strlen(cr->Counter));
257 "UPDATE Counters SET \"MinValue\"=%d,\"MaxValue\"=%d,CurrentValue=%d,"
258 "WrapCounter='%s' WHERE Counter='%s'",
259 cr->MinValue, cr->MaxValue, cr->CurrentValue,
260 cr->WrapCounter, esc);
262 int stat = UPDATE_DB(jcr, mdb, mdb->cmd);
268 int db_update_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
271 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50];
272 char esc[MAX_ESCAPE_NAME_LENGTH];
275 mdb->db_escape_string(jcr, esc, pr->LabelFormat, strlen(pr->LabelFormat));
277 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
278 edit_int64(pr->PoolId, ed4));
279 pr->NumVols = get_sql_record_max(jcr, mdb);
280 Dmsg1(400, "NumVols=%d\n", pr->NumVols);
283 "UPDATE Pool SET NumVols=%u,MaxVols=%u,UseOnce=%d,UseCatalog=%d,"
284 "AcceptAnyVolume=%d,VolRetention='%s',VolUseDuration='%s',"
285 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,Recycle=%d,"
286 "AutoPrune=%d,LabelType=%d,LabelFormat='%s',RecyclePoolId=%s,"
287 "ScratchPoolId=%s,ActionOnPurge=%d WHERE PoolId=%s",
288 pr->NumVols, pr->MaxVols, pr->UseOnce, pr->UseCatalog,
289 pr->AcceptAnyVolume, edit_uint64(pr->VolRetention, ed1),
290 edit_uint64(pr->VolUseDuration, ed2),
291 pr->MaxVolJobs, pr->MaxVolFiles,
292 edit_uint64(pr->MaxVolBytes, ed3),
293 pr->Recycle, pr->AutoPrune, pr->LabelType,
294 esc, edit_int64(pr->RecyclePoolId,ed5),
295 edit_int64(pr->ScratchPoolId,ed6),
298 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
304 db_update_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
309 Mmsg(mdb->cmd, "UPDATE Storage SET AutoChanger=%d WHERE StorageId=%s",
310 sr->AutoChanger, edit_int64(sr->StorageId, ed1));
312 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
319 * Update the Media Record at end of Session
321 * Returns: 0 on failure
325 db_update_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
327 char dt[MAX_TIME_LENGTH];
331 char ed1[50], ed2[50], ed3[50], ed4[50];
332 char ed5[50], ed6[50], ed7[50], ed8[50];
333 char ed9[50], ed10[50], ed11[50];
334 char esc_name[MAX_ESCAPE_NAME_LENGTH];
335 char esc_status[MAX_ESCAPE_NAME_LENGTH];
337 Dmsg1(100, "update_media: FirstWritten=%d\n", mr->FirstWritten);
339 mdb->db_escape_string(jcr, esc_name, mr->VolumeName, strlen(mr->VolumeName));
340 mdb->db_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
342 if (mr->set_first_written) {
343 Dmsg1(400, "Set FirstWritten Vol=%s\n", mr->VolumeName);
344 ttime = mr->FirstWritten;
345 (void)localtime_r(&ttime, &tm);
346 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
347 Mmsg(mdb->cmd, "UPDATE Media SET FirstWritten='%s'"
348 " WHERE VolumeName='%s'", dt, esc_name);
349 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
350 Dmsg1(400, "Firstwritten=%d\n", mr->FirstWritten);
353 /* Label just done? */
354 if (mr->set_label_date) {
355 ttime = mr->LabelDate;
359 (void)localtime_r(&ttime, &tm);
360 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
361 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
362 "WHERE VolumeName='%s'", dt, esc_name);
363 UPDATE_DB(jcr, mdb, mdb->cmd);
366 if (mr->LastWritten != 0) {
367 ttime = mr->LastWritten;
368 (void)localtime_r(&ttime, &tm);
369 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
370 Mmsg(mdb->cmd, "UPDATE Media Set LastWritten='%s' "
371 "WHERE VolumeName='%s'", dt, esc_name);
372 UPDATE_DB(jcr, mdb, mdb->cmd);
375 /* sanity checks for #1066 */
376 if (mr->VolReadTime < 0) {
379 if (mr->VolWriteTime < 0) {
380 mr->VolWriteTime = 0;
383 Mmsg(mdb->cmd, "UPDATE Media SET VolJobs=%u,"
384 "VolFiles=%u,VolBlocks=%u,VolBytes=%s,VolMounts=%u,VolErrors=%u,"
385 "VolWrites=%u,MaxVolBytes=%s,VolStatus='%s',"
386 "Slot=%d,InChanger=%d,VolReadTime=%s,VolWriteTime=%s,VolParts=%d,"
387 "LabelType=%d,StorageId=%s,PoolId=%s,VolRetention=%s,VolUseDuration=%s,"
388 "MaxVolJobs=%d,MaxVolFiles=%d,Enabled=%d,LocationId=%s,"
389 "ScratchPoolId=%s,RecyclePoolId=%s,RecycleCount=%d,Recycle=%d,ActionOnPurge=%d"
390 " WHERE VolumeName='%s'",
391 mr->VolJobs, mr->VolFiles, mr->VolBlocks, edit_uint64(mr->VolBytes, ed1),
392 mr->VolMounts, mr->VolErrors, mr->VolWrites,
393 edit_uint64(mr->MaxVolBytes, ed2),
394 esc_status, mr->Slot, mr->InChanger,
395 edit_int64(mr->VolReadTime, ed3),
396 edit_int64(mr->VolWriteTime, ed4),
399 edit_int64(mr->StorageId, ed5),
400 edit_int64(mr->PoolId, ed6),
401 edit_uint64(mr->VolRetention, ed7),
402 edit_uint64(mr->VolUseDuration, ed8),
403 mr->MaxVolJobs, mr->MaxVolFiles,
404 mr->Enabled, edit_uint64(mr->LocationId, ed9),
405 edit_uint64(mr->ScratchPoolId, ed10),
406 edit_uint64(mr->RecyclePoolId, ed11),
407 mr->RecycleCount,mr->Recycle, mr->ActionOnPurge,
410 Dmsg1(400, "%s\n", mdb->cmd);
412 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
414 /* Make sure InChanger is 0 for any record having the same Slot */
415 db_make_inchanger_unique(jcr, mdb, mr);
422 * Update the Media Record Default values from Pool
424 * Returns: 0 on failure
428 db_update_media_defaults(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
431 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50];
432 char esc[MAX_ESCAPE_NAME_LENGTH];
435 if (mr->VolumeName[0]) {
436 mdb->db_escape_string(jcr, esc, mr->VolumeName, strlen(mr->VolumeName));
437 Mmsg(mdb->cmd, "UPDATE Media SET "
438 "ActionOnPurge=%d, Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
439 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
440 " WHERE VolumeName='%s'",
441 mr->ActionOnPurge, mr->Recycle,edit_uint64(mr->VolRetention, ed1),
442 edit_uint64(mr->VolUseDuration, ed2),
443 mr->MaxVolJobs, mr->MaxVolFiles,
444 edit_uint64(mr->MaxVolBytes, ed3),
445 edit_uint64(mr->RecyclePoolId, ed4),
448 Mmsg(mdb->cmd, "UPDATE Media SET "
449 "ActionOnPurge=%d, Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
450 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
452 mr->ActionOnPurge, mr->Recycle,edit_uint64(mr->VolRetention, ed1),
453 edit_uint64(mr->VolUseDuration, ed2),
454 mr->MaxVolJobs, mr->MaxVolFiles,
455 edit_uint64(mr->MaxVolBytes, ed3),
456 edit_int64(mr->RecyclePoolId, ed4),
457 edit_int64(mr->PoolId, ed5));
460 Dmsg1(400, "%s\n", mdb->cmd);
462 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
470 * If we have a non-zero InChanger, ensure that no other Media
471 * record has InChanger set on the same Slot.
473 * This routine assumes the database is already locked.
476 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
478 char ed1[50], ed2[50];
479 char esc[MAX_ESCAPE_NAME_LENGTH];
480 if (mr->InChanger != 0 && mr->Slot != 0 && mr->StorageId != 0) {
482 if (mr->MediaId != 0) {
483 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
484 "Slot=%d AND StorageId=%s AND MediaId!=%s",
486 edit_int64(mr->StorageId, ed1), edit_int64(mr->MediaId, ed2));
488 } else if (*mr->VolumeName) {
489 mdb->db_escape_string(jcr, esc,mr->VolumeName,strlen(mr->VolumeName));
490 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
491 "Slot=%d AND StorageId=%s AND VolumeName!='%s'",
493 edit_int64(mr->StorageId, ed1), esc);
495 } else { /* used by ua_label to reset all volume with this slot */
496 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
497 "Slot=%d AND StorageId=%s",
499 edit_int64(mr->StorageId, ed1), mr->VolumeName);
501 Dmsg1(100, "%s\n", mdb->cmd);
502 UPDATE_DB(jcr, mdb, mdb->cmd);
506 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */