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)
138 utime_t now = (utime_t)time(NULL);
139 edit_uint64(now - age, ed1);
141 Mmsg(mdb->cmd, fill_jobhisto, ed1);
142 QUERY_DB(jcr, mdb, mdb->cmd); /* TODO: get a message ? */
143 return sql_affected_rows(mdb);
147 * Update the Job record at end of Job
149 * Returns: 0 on failure
153 db_update_job_end_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
155 char dt[MAX_TIME_LENGTH];
156 char rdt[MAX_TIME_LENGTH];
160 char ed1[30], ed2[30], ed3[50], ed4[50];
164 if (jr->PriorJobId) {
165 bstrncpy(PriorJobId, edit_int64(jr->PriorJobId, ed1), sizeof(PriorJobId));
167 bstrncpy(PriorJobId, "0", sizeof(PriorJobId));
171 (void)localtime_r(&ttime, &tm);
172 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
174 if (jr->RealEndTime == 0) {
175 jr->RealEndTime = jr->EndTime;
177 ttime = jr->RealEndTime;
178 (void)localtime_r(&ttime, &tm);
179 strftime(rdt, sizeof(rdt), "%Y-%m-%d %H:%M:%S", &tm);
185 "UPDATE Job SET JobStatus='%c',EndTime='%s',"
186 "ClientId=%u,JobBytes=%s,ReadBytes=%s,JobFiles=%u,JobErrors=%u,VolSessionId=%u,"
187 "VolSessionTime=%u,PoolId=%u,FileSetId=%u,JobTDate=%s,"
188 "RealEndTime='%s',PriorJobId=%s,HasBase=%u,PurgedFiles=%u WHERE JobId=%s",
189 (char)(jr->JobStatus), dt, jr->ClientId, edit_uint64(jr->JobBytes, ed1),
190 edit_uint64(jr->ReadBytes, ed4),
191 jr->JobFiles, jr->JobErrors, jr->VolSessionId, jr->VolSessionTime,
192 jr->PoolId, jr->FileSetId, edit_uint64(JobTDate, ed2),
193 rdt, PriorJobId, jr->HasBase, jr->PurgedFiles,
194 edit_int64(jr->JobId, ed3));
196 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
203 * Update Client record
204 * Returns: 0 on failure
208 db_update_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
211 char ed1[50], ed2[50];
212 char esc_name[MAX_ESCAPE_NAME_LENGTH];
213 char esc_uname[MAX_ESCAPE_NAME_LENGTH];
217 memcpy(&tcr, cr, sizeof(tcr));
218 if (!db_create_client_record(jcr, mdb, &tcr)) {
223 mdb->db_escape_string(jcr, esc_name, cr->Name, strlen(cr->Name));
224 mdb->db_escape_string(jcr, esc_uname, cr->Uname, strlen(cr->Uname));
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 esc_uname, esc_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)
246 char esc[MAX_ESCAPE_NAME_LENGTH];
248 mdb->db_escape_string(jcr, esc, cr->Counter, strlen(cr->Counter));
250 "UPDATE Counters SET \"MinValue\"=%d,\"MaxValue\"=%d,CurrentValue=%d,"
251 "WrapCounter='%s' WHERE Counter='%s'",
252 cr->MinValue, cr->MaxValue, cr->CurrentValue,
253 cr->WrapCounter, esc);
255 int stat = UPDATE_DB(jcr, mdb, mdb->cmd);
261 int db_update_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
264 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50];
265 char esc[MAX_ESCAPE_NAME_LENGTH];
268 mdb->db_escape_string(jcr, esc, pr->LabelFormat, strlen(pr->LabelFormat));
270 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
271 edit_int64(pr->PoolId, ed4));
272 pr->NumVols = get_sql_record_max(jcr, mdb);
273 Dmsg1(400, "NumVols=%d\n", pr->NumVols);
276 "UPDATE Pool SET NumVols=%u,MaxVols=%u,UseOnce=%d,UseCatalog=%d,"
277 "AcceptAnyVolume=%d,VolRetention='%s',VolUseDuration='%s',"
278 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,Recycle=%d,"
279 "AutoPrune=%d,LabelType=%d,LabelFormat='%s',RecyclePoolId=%s,"
280 "ScratchPoolId=%s,ActionOnPurge=%d WHERE PoolId=%s",
281 pr->NumVols, pr->MaxVols, pr->UseOnce, pr->UseCatalog,
282 pr->AcceptAnyVolume, edit_uint64(pr->VolRetention, ed1),
283 edit_uint64(pr->VolUseDuration, ed2),
284 pr->MaxVolJobs, pr->MaxVolFiles,
285 edit_uint64(pr->MaxVolBytes, ed3),
286 pr->Recycle, pr->AutoPrune, pr->LabelType,
287 esc, edit_int64(pr->RecyclePoolId,ed5),
288 edit_int64(pr->ScratchPoolId,ed6),
291 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
297 db_update_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
302 Mmsg(mdb->cmd, "UPDATE Storage SET AutoChanger=%d WHERE StorageId=%s",
303 sr->AutoChanger, edit_int64(sr->StorageId, ed1));
305 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
312 * Update the Media Record at end of Session
314 * Returns: 0 on failure
318 db_update_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
320 char dt[MAX_TIME_LENGTH];
324 char ed1[50], ed2[50], ed3[50], ed4[50];
325 char ed5[50], ed6[50], ed7[50], ed8[50];
326 char ed9[50], ed10[50], ed11[50];
327 char esc_name[MAX_ESCAPE_NAME_LENGTH];
328 char esc_status[MAX_ESCAPE_NAME_LENGTH];
330 Dmsg1(100, "update_media: FirstWritten=%d\n", mr->FirstWritten);
332 mdb->db_escape_string(jcr, esc_name, mr->VolumeName, strlen(mr->VolumeName));
333 mdb->db_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
335 if (mr->set_first_written) {
336 Dmsg1(400, "Set FirstWritten Vol=%s\n", mr->VolumeName);
337 ttime = mr->FirstWritten;
338 (void)localtime_r(&ttime, &tm);
339 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
340 Mmsg(mdb->cmd, "UPDATE Media SET FirstWritten='%s'"
341 " WHERE VolumeName='%s'", dt, esc_name);
342 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
343 Dmsg1(400, "Firstwritten=%d\n", mr->FirstWritten);
346 /* Label just done? */
347 if (mr->set_label_date) {
348 ttime = mr->LabelDate;
352 (void)localtime_r(&ttime, &tm);
353 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
354 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
355 "WHERE VolumeName='%s'", dt, esc_name);
356 UPDATE_DB(jcr, mdb, mdb->cmd);
359 if (mr->LastWritten != 0) {
360 ttime = mr->LastWritten;
361 (void)localtime_r(&ttime, &tm);
362 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
363 Mmsg(mdb->cmd, "UPDATE Media Set LastWritten='%s' "
364 "WHERE VolumeName='%s'", dt, esc_name);
365 UPDATE_DB(jcr, mdb, mdb->cmd);
368 /* sanity checks for #1066 */
369 if (mr->VolReadTime < 0) {
372 if (mr->VolWriteTime < 0) {
373 mr->VolWriteTime = 0;
376 Mmsg(mdb->cmd, "UPDATE Media SET VolJobs=%u,"
377 "VolFiles=%u,VolBlocks=%u,VolBytes=%s,VolMounts=%u,VolErrors=%u,"
378 "VolWrites=%u,MaxVolBytes=%s,VolStatus='%s',"
379 "Slot=%d,InChanger=%d,VolReadTime=%s,VolWriteTime=%s,VolParts=%d,"
380 "LabelType=%d,StorageId=%s,PoolId=%s,VolRetention=%s,VolUseDuration=%s,"
381 "MaxVolJobs=%d,MaxVolFiles=%d,Enabled=%d,LocationId=%s,"
382 "ScratchPoolId=%s,RecyclePoolId=%s,RecycleCount=%d,Recycle=%d,ActionOnPurge=%d"
383 " WHERE VolumeName='%s'",
384 mr->VolJobs, mr->VolFiles, mr->VolBlocks, edit_uint64(mr->VolBytes, ed1),
385 mr->VolMounts, mr->VolErrors, mr->VolWrites,
386 edit_uint64(mr->MaxVolBytes, ed2),
387 esc_status, mr->Slot, mr->InChanger,
388 edit_int64(mr->VolReadTime, ed3),
389 edit_int64(mr->VolWriteTime, ed4),
392 edit_int64(mr->StorageId, ed5),
393 edit_int64(mr->PoolId, ed6),
394 edit_uint64(mr->VolRetention, ed7),
395 edit_uint64(mr->VolUseDuration, ed8),
396 mr->MaxVolJobs, mr->MaxVolFiles,
397 mr->Enabled, edit_uint64(mr->LocationId, ed9),
398 edit_uint64(mr->ScratchPoolId, ed10),
399 edit_uint64(mr->RecyclePoolId, ed11),
400 mr->RecycleCount,mr->Recycle, mr->ActionOnPurge,
403 Dmsg1(400, "%s\n", mdb->cmd);
405 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
407 /* Make sure InChanger is 0 for any record having the same Slot */
408 db_make_inchanger_unique(jcr, mdb, mr);
415 * Update the Media Record Default values from Pool
417 * Returns: 0 on failure
421 db_update_media_defaults(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
424 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50];
425 char esc[MAX_ESCAPE_NAME_LENGTH];
428 if (mr->VolumeName[0]) {
429 mdb->db_escape_string(jcr, esc, mr->VolumeName, strlen(mr->VolumeName));
430 Mmsg(mdb->cmd, "UPDATE Media SET "
431 "ActionOnPurge=%d, Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
432 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
433 " WHERE VolumeName='%s'",
434 mr->ActionOnPurge, 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_uint64(mr->RecyclePoolId, ed4),
441 Mmsg(mdb->cmd, "UPDATE Media SET "
442 "ActionOnPurge=%d, Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
443 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
445 mr->ActionOnPurge, mr->Recycle,edit_uint64(mr->VolRetention, ed1),
446 edit_uint64(mr->VolUseDuration, ed2),
447 mr->MaxVolJobs, mr->MaxVolFiles,
448 edit_uint64(mr->MaxVolBytes, ed3),
449 edit_int64(mr->RecyclePoolId, ed4),
450 edit_int64(mr->PoolId, ed5));
453 Dmsg1(400, "%s\n", mdb->cmd);
455 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
463 * If we have a non-zero InChanger, ensure that no other Media
464 * record has InChanger set on the same Slot.
466 * This routine assumes the database is already locked.
469 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
471 char ed1[50], ed2[50];
472 char esc[MAX_ESCAPE_NAME_LENGTH];
473 if (mr->InChanger != 0 && mr->Slot != 0 && mr->StorageId != 0) {
475 if (mr->MediaId != 0) {
476 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
477 "Slot=%d AND StorageId=%s AND MediaId!=%s",
479 edit_int64(mr->StorageId, ed1), edit_int64(mr->MediaId, ed2));
481 } else if (*mr->VolumeName) {
482 mdb->db_escape_string(jcr, esc,mr->VolumeName,strlen(mr->VolumeName));
483 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
484 "Slot=%d AND StorageId=%s AND VolumeName!='%s'",
486 edit_int64(mr->StorageId, ed1), esc);
488 } else { /* used by ua_label to reset all volume with this slot */
489 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
490 "Slot=%d AND StorageId=%s",
492 edit_int64(mr->StorageId, ed1), mr->VolumeName);
494 Dmsg1(100, "%s\n", mdb->cmd);
495 UPDATE_DB(jcr, mdb, mdb->cmd);
499 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */