2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2014 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from many
7 others, a complete list can be found in the file AUTHORS.
9 You may use this file and others of this release according to the
10 license defined in the LICENSE file, which includes the Affero General
11 Public License, v3.0 ("AGPLv3") and some additional permissions and
12 terms pursuant to its AGPLv3 Section 7.
14 Bacula® is a registered trademark of Kern Sibbald.
17 * Bacula Catalog Database Update record interface routines
19 * Written by Kern Sibbald, March 2000
25 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL
31 /* -----------------------------------------------------------------------
33 * Generic Routines (or almost generic)
35 * -----------------------------------------------------------------------
38 /* -----------------------------------------------------------------------
40 * Generic Routines (or almost generic)
42 * -----------------------------------------------------------------------
44 /* Update the attributes record by adding the file digest */
46 db_add_digest_to_file_record(JCR *jcr, B_DB *mdb, FileId_t FileId, char *digest,
51 int len = strlen(digest);
54 mdb->esc_name = check_pool_memory_size(mdb->esc_name, len*2+1);
55 mdb->db_escape_string(jcr, mdb->esc_name, digest, len);
56 Mmsg(mdb->cmd, "UPDATE File SET MD5='%s' WHERE FileId=%s", mdb->esc_name,
57 edit_int64(FileId, ed1));
58 ret = UPDATE_DB(jcr, mdb, mdb->cmd);
63 /* Mark the file record as being visited during database
64 * verify compare. Stuff JobId into the MarkId field
66 int db_mark_file_record(JCR *jcr, B_DB *mdb, FileId_t FileId, JobId_t JobId)
69 char ed1[50], ed2[50];
72 Mmsg(mdb->cmd, "UPDATE File SET MarkId=%s WHERE FileId=%s",
73 edit_int64(JobId, ed1), edit_int64(FileId, ed2));
74 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
80 * Update the Job record at start of Job
82 * Returns: false on failure
86 db_update_job_start_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
88 char dt[MAX_TIME_LENGTH];
93 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50];
95 stime = jr->StartTime;
96 (void)localtime_r(&stime, &tm);
97 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
98 JobTDate = (btime_t)stime;
101 Mmsg(mdb->cmd, "UPDATE Job SET JobStatus='%c',Level='%c',StartTime='%s',"
102 "ClientId=%s,JobTDate=%s,PoolId=%s,FileSetId=%s WHERE JobId=%s",
103 (char)(jcr->JobStatus),
104 (char)(jr->JobLevel), dt,
105 edit_int64(jr->ClientId, ed1),
106 edit_uint64(JobTDate, ed2),
107 edit_int64(jr->PoolId, ed3),
108 edit_int64(jr->FileSetId, ed4),
109 edit_int64(jr->JobId, ed5));
111 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
118 * Update Long term statistics with all jobs that were run before
122 db_update_stats(JCR *jcr, B_DB *mdb, utime_t age)
126 utime_t now = (utime_t)time(NULL);
127 edit_uint64(now - age, ed1);
131 Mmsg(mdb->cmd, fill_jobhisto, ed1);
132 QUERY_DB(jcr, mdb, mdb->cmd); /* TODO: get a message ? */
133 rows = sql_affected_rows(mdb);
141 * Update the Job record at end of Job
143 * Returns: 0 on failure
147 db_update_job_end_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
149 char dt[MAX_TIME_LENGTH];
150 char rdt[MAX_TIME_LENGTH];
154 char ed1[30], ed2[30], ed3[50], ed4[50];
158 if (jr->PriorJobId) {
159 bstrncpy(PriorJobId, edit_int64(jr->PriorJobId, ed1), sizeof(PriorJobId));
161 bstrncpy(PriorJobId, "0", sizeof(PriorJobId));
165 (void)localtime_r(&ttime, &tm);
166 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
168 if (jr->RealEndTime == 0) {
169 jr->RealEndTime = jr->EndTime;
171 ttime = jr->RealEndTime;
172 (void)localtime_r(&ttime, &tm);
173 strftime(rdt, sizeof(rdt), "%Y-%m-%d %H:%M:%S", &tm);
179 "UPDATE Job SET JobStatus='%c',EndTime='%s',"
180 "ClientId=%u,JobBytes=%s,ReadBytes=%s,JobFiles=%u,JobErrors=%u,VolSessionId=%u,"
181 "VolSessionTime=%u,PoolId=%u,FileSetId=%u,JobTDate=%s,"
182 "RealEndTime='%s',PriorJobId=%s,HasBase=%u,PurgedFiles=%u WHERE JobId=%s",
183 (char)(jr->JobStatus), dt, jr->ClientId, edit_uint64(jr->JobBytes, ed1),
184 edit_uint64(jr->ReadBytes, ed4),
185 jr->JobFiles, jr->JobErrors, jr->VolSessionId, jr->VolSessionTime,
186 jr->PoolId, jr->FileSetId, edit_uint64(JobTDate, ed2),
187 rdt, PriorJobId, jr->HasBase, jr->PurgedFiles,
188 edit_int64(jr->JobId, ed3));
190 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
197 * Update Client record
198 * Returns: 0 on failure
202 db_update_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
205 char ed1[50], ed2[50];
206 char esc_name[MAX_ESCAPE_NAME_LENGTH];
207 char esc_uname[MAX_ESCAPE_NAME_LENGTH];
211 memcpy(&tcr, cr, sizeof(tcr));
212 if (!db_create_client_record(jcr, mdb, &tcr)) {
217 mdb->db_escape_string(jcr, esc_name, cr->Name, strlen(cr->Name));
218 mdb->db_escape_string(jcr, esc_uname, cr->Uname, strlen(cr->Uname));
220 "UPDATE Client SET AutoPrune=%d,FileRetention=%s,JobRetention=%s,"
221 "Uname='%s' WHERE Name='%s'",
223 edit_uint64(cr->FileRetention, ed1),
224 edit_uint64(cr->JobRetention, ed2),
225 esc_uname, esc_name);
227 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
234 * Update Counters record
235 * Returns: 0 on failure
238 int db_update_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
240 char esc[MAX_ESCAPE_NAME_LENGTH];
242 mdb->db_escape_string(jcr, esc, cr->Counter, strlen(cr->Counter));
244 update_counter_values[mdb->db_get_type_index()],
245 cr->MinValue, cr->MaxValue, cr->CurrentValue,
246 cr->WrapCounter, esc);
248 int stat = UPDATE_DB(jcr, mdb, mdb->cmd);
254 int db_update_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
257 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50];
258 char esc[MAX_ESCAPE_NAME_LENGTH];
261 mdb->db_escape_string(jcr, esc, pr->LabelFormat, strlen(pr->LabelFormat));
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 esc, 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], ed4[50];
318 char ed5[50], ed6[50], ed7[50], ed8[50];
319 char ed9[50], ed10[50], ed11[50], ed12[50];
321 char esc_name[MAX_ESCAPE_NAME_LENGTH];
322 char esc_status[MAX_ESCAPE_NAME_LENGTH];
324 Dmsg1(100, "update_media: FirstWritten=%d\n", mr->FirstWritten);
326 mdb->db_escape_string(jcr, esc_name, mr->VolumeName, strlen(mr->VolumeName));
327 mdb->db_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
329 if (mr->set_first_written) {
330 Dmsg1(400, "Set FirstWritten Vol=%s\n", mr->VolumeName);
331 ttime = mr->FirstWritten;
332 (void)localtime_r(&ttime, &tm);
333 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
334 Mmsg(mdb->cmd, "UPDATE Media SET FirstWritten='%s'"
335 " WHERE VolumeName='%s'", dt, esc_name);
336 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
337 Dmsg1(400, "Firstwritten=%d\n", mr->FirstWritten);
340 /* Label just done? */
341 if (mr->set_label_date) {
342 ttime = mr->LabelDate;
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 LabelDate='%s' "
349 "WHERE VolumeName='%s'", dt, esc_name);
350 UPDATE_DB(jcr, mdb, mdb->cmd);
353 if (mr->LastWritten != 0) {
354 ttime = mr->LastWritten;
355 (void)localtime_r(&ttime, &tm);
356 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
357 Mmsg(mdb->cmd, "UPDATE Media Set LastWritten='%s' "
358 "WHERE VolumeName='%s'", dt, esc_name);
359 UPDATE_DB(jcr, mdb, mdb->cmd);
362 /* sanity checks for #1066 */
363 if (mr->VolReadTime < 0) {
366 if (mr->VolWriteTime < 0) {
367 mr->VolWriteTime = 0;
370 Mmsg(mdb->cmd, "UPDATE Media SET VolJobs=%u,"
371 "VolFiles=%u,VolBlocks=%u,VolBytes=%s,"
372 "VolMounts=%u,VolErrors=%u,"
373 "VolWrites=%u,MaxVolBytes=%s,VolStatus='%s',"
374 "Slot=%d,InChanger=%d,VolReadTime=%s,VolWriteTime=%s,VolParts=%d,"
375 "LabelType=%d,StorageId=%s,PoolId=%s,VolRetention=%s,VolUseDuration=%s,"
376 "MaxVolJobs=%d,MaxVolFiles=%d,Enabled=%d,LocationId=%s,"
377 "ScratchPoolId=%s,RecyclePoolId=%s,RecycleCount=%d,Recycle=%d,ActionOnPurge=%d"
378 " WHERE VolumeName='%s'",
379 mr->VolJobs, mr->VolFiles, mr->VolBlocks,
380 edit_uint64(mr->VolBytes, ed1),
381 mr->VolMounts, mr->VolErrors, mr->VolWrites,
382 edit_uint64(mr->MaxVolBytes, ed4),
383 esc_status, mr->Slot, mr->InChanger,
384 edit_int64(mr->VolReadTime, ed5),
385 edit_int64(mr->VolWriteTime, ed6),
388 edit_int64(mr->StorageId, ed7),
389 edit_int64(mr->PoolId, ed8),
390 edit_uint64(mr->VolRetention, ed9),
391 edit_uint64(mr->VolUseDuration, ed10),
392 mr->MaxVolJobs, mr->MaxVolFiles,
393 mr->Enabled, edit_uint64(mr->LocationId, ed11),
394 edit_uint64(mr->ScratchPoolId, ed12),
395 edit_uint64(mr->RecyclePoolId, ed13),
396 mr->RecycleCount,mr->Recycle, mr->ActionOnPurge,
399 Dmsg1(400, "%s\n", mdb->cmd);
401 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
403 /* Make sure InChanger is 0 for any record having the same Slot */
404 db_make_inchanger_unique(jcr, mdb, mr);
411 * Update the Media Record Default values from Pool
413 * Returns: 0 on failure
417 db_update_media_defaults(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
420 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50];
421 char esc[MAX_ESCAPE_NAME_LENGTH];
424 if (mr->VolumeName[0]) {
425 mdb->db_escape_string(jcr, esc, mr->VolumeName, strlen(mr->VolumeName));
426 Mmsg(mdb->cmd, "UPDATE Media SET "
427 "ActionOnPurge=%d, Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
428 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,RecyclePoolId=%s"
429 " WHERE VolumeName='%s'",
430 mr->ActionOnPurge, mr->Recycle,edit_uint64(mr->VolRetention, ed1),
431 edit_uint64(mr->VolUseDuration, ed2),
432 mr->MaxVolJobs, mr->MaxVolFiles,
433 edit_uint64(mr->MaxVolBytes, ed3),
434 edit_uint64(mr->RecyclePoolId, ed4),
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"
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_int64(mr->RecyclePoolId, ed4),
446 edit_int64(mr->PoolId, ed5));
449 Dmsg1(400, "%s\n", mdb->cmd);
451 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
459 * If we have a non-zero InChanger, ensure that no other Media
460 * record has InChanger set on the same Slot.
462 * This routine assumes the database is already locked.
465 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
467 char ed1[50], ed2[50];
468 char esc[MAX_ESCAPE_NAME_LENGTH];
469 if (mr->InChanger != 0 && mr->Slot != 0 && mr->StorageId != 0) {
471 if (mr->MediaId != 0) {
472 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
473 "Slot=%d AND StorageId=%s AND MediaId!=%s",
475 edit_int64(mr->StorageId, ed1), edit_int64(mr->MediaId, ed2));
477 } else if (*mr->VolumeName) {
478 mdb->db_escape_string(jcr, esc,mr->VolumeName,strlen(mr->VolumeName));
479 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
480 "Slot=%d AND StorageId=%s AND VolumeName!='%s'",
482 edit_int64(mr->StorageId, ed1), esc);
484 } else { /* used by ua_label to reset all volume with this slot */
485 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0, Slot=0 WHERE "
486 "Slot=%d AND StorageId=%s",
488 edit_int64(mr->StorageId, ed1), mr->VolumeName);
490 Dmsg1(100, "%s\n", mdb->cmd);
491 UPDATE_DB(jcr, mdb, mdb->cmd);
495 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */