2 * Bacula Catalog Database Update record interface routines
4 * Kern Sibbald, March 2000
9 Copyright (C) 2000-2006 Kern Sibbald
11 This program is free software; you can redistribute it and/or
12 modify it under the terms of the GNU General Public License
13 version 2 as amended with additional clauses defined in the
14 file LICENSE in the main source directory.
16 This program is distributed in the hope that it will be useful,
17 but WITHOUT ANY WARRANTY; without even the implied warranty of
18 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 the file LICENSE for additional details.
23 /* The following is necessary so that we do not include
24 * the dummy external definition of DB.
26 #define __SQL_C /* indicate that this is sql.c */
31 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
33 /* -----------------------------------------------------------------------
35 * Generic Routines (or almost generic)
37 * -----------------------------------------------------------------------
40 /* Imported subroutines */
41 extern void print_result(B_DB *mdb);
42 extern int UpdateDB(const char *file, int line, JCR *jcr, B_DB *db, char *update_cmd);
44 /* -----------------------------------------------------------------------
46 * Generic Routines (or almost generic)
48 * -----------------------------------------------------------------------
50 /* Update the attributes record by adding the file digest */
52 db_add_digest_to_file_record(JCR *jcr, B_DB *mdb, FileId_t FileId, char *digest,
59 Mmsg(mdb->cmd, "UPDATE File SET MD5='%s' WHERE FileId=%s", digest,
60 edit_int64(FileId, ed1));
61 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
66 /* Mark the file record as being visited during database
67 * verify compare. Stuff JobId into the MarkId field
69 int db_mark_file_record(JCR *jcr, B_DB *mdb, FileId_t FileId, JobId_t JobId)
72 char ed1[50], ed2[50];
75 Mmsg(mdb->cmd, "UPDATE File SET MarkId=%s WHERE FileId=%s",
76 edit_int64(JobId, ed1), edit_int64(FileId, ed2));
77 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
83 * Update the Job record at start of Job
85 * Returns: false on failure
89 db_update_job_start_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
91 char dt[MAX_TIME_LENGTH];
96 char ed1[50], ed2[50], ed3[50], ed4[50];
98 stime = jr->StartTime;
99 (void)localtime_r(&stime, &tm);
100 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
101 JobTDate = (btime_t)stime;
104 Mmsg(mdb->cmd, "UPDATE Job SET JobStatus='%c',Level='%c',StartTime='%s',"
105 "ClientId=%s,JobTDate=%s,PoolId=%s WHERE JobId=%s",
106 (char)(jcr->JobStatus),
107 (char)(jr->JobLevel), dt,
108 edit_int64(jr->ClientId, ed1),
109 edit_uint64(JobTDate, ed2),
110 edit_int64(jr->PoolId, ed3),
111 edit_int64(jr->JobId, ed4));
113 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
120 * Given an incoming integer, set the string buffer to either NULL or the value
123 static void edit_num_or_null(char *s, size_t n, uint64_t id) {
125 bsnprintf(s, n, id ? "%s" : "NULL", edit_int64(id, ed1));
130 * Update the Job record at end of Job
132 * Returns: 0 on failure
136 db_update_job_end_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
138 char dt[MAX_TIME_LENGTH];
139 char rdt[MAX_TIME_LENGTH];
143 char ed1[30], ed2[30], ed3[50];
145 char PoolId[50], FileSetId[50], ClientId[50], PriorJobId[50];
148 /* some values are set to zero, which translates to NULL in SQL */
149 edit_num_or_null(PoolId, sizeof(PoolId), jr->PoolId);
150 edit_num_or_null(FileSetId, sizeof(FileSetId), jr->FileSetId);
151 edit_num_or_null(ClientId, sizeof(ClientId), jr->ClientId);
153 if (jr->PriorJobId) {
154 bstrncpy(PriorJobId, edit_int64(jr->PriorJobId, ed1), sizeof(PriorJobId));
156 bstrncpy(PriorJobId, "0", sizeof(PriorJobId));
160 (void)localtime_r(&ttime, &tm);
161 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
163 if (jr->RealEndTime == 0) {
164 jr->RealEndTime = jr->EndTime;
166 ttime = jr->RealEndTime;
167 (void)localtime_r(&ttime, &tm);
168 strftime(rdt, sizeof(rdt), "%Y-%m-%d %H:%M:%S", &tm);
174 "UPDATE Job SET JobStatus='%c',EndTime='%s',"
175 "ClientId=%s,JobBytes=%s,JobFiles=%u,JobErrors=%u,VolSessionId=%u,"
176 "VolSessionTime=%u,PoolId=%s,FileSetId=%s,JobTDate=%s,"
177 "RealEndTime='%s',PriorJobId=%s WHERE JobId=%s",
178 (char)(jr->JobStatus), dt, ClientId, edit_uint64(jr->JobBytes, ed1),
179 jr->JobFiles, jr->JobErrors, jr->VolSessionId, jr->VolSessionTime,
180 PoolId, FileSetId, edit_uint64(JobTDate, ed2),
183 edit_int64(jr->JobId, ed3));
185 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
192 * Update Client record
193 * Returns: 0 on failure
197 db_update_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
200 char ed1[50], ed2[50];
204 memcpy(&tcr, cr, sizeof(tcr));
205 if (!db_create_client_record(jcr, mdb, &tcr)) {
211 "UPDATE Client SET AutoPrune=%d,FileRetention=%s,JobRetention=%s,"
212 "Uname='%s' WHERE Name='%s'",
214 edit_uint64(cr->FileRetention, ed1),
215 edit_uint64(cr->JobRetention, ed2),
216 cr->Uname, cr->Name);
218 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
225 * Update Counters record
226 * Returns: 0 on failure
229 int db_update_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
234 "UPDATE Counters SET MinValue=%d,MaxValue=%d,CurrentValue=%d,"
235 "WrapCounter='%s' WHERE Counter='%s'",
236 cr->MinValue, cr->MaxValue, cr->CurrentValue,
237 cr->WrapCounter, cr->Counter);
239 int stat = UPDATE_DB(jcr, mdb, mdb->cmd);
246 db_update_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
249 char ed1[50], ed2[50], ed3[50], ed4[50];
252 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
253 edit_int64(pr->PoolId, ed4));
254 pr->NumVols = get_sql_record_max(jcr, mdb);
255 Dmsg1(400, "NumVols=%d\n", pr->NumVols);
258 "UPDATE Pool SET NumVols=%u,MaxVols=%u,UseOnce=%d,UseCatalog=%d,"
259 "AcceptAnyVolume=%d,VolRetention='%s',VolUseDuration='%s',"
260 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,Recycle=%d,"
261 "AutoPrune=%d,LabelType=%d,LabelFormat='%s' WHERE PoolId=%s",
262 pr->NumVols, pr->MaxVols, pr->UseOnce, pr->UseCatalog,
263 pr->AcceptAnyVolume, edit_uint64(pr->VolRetention, ed1),
264 edit_uint64(pr->VolUseDuration, ed2),
265 pr->MaxVolJobs, pr->MaxVolFiles,
266 edit_uint64(pr->MaxVolBytes, ed3),
267 pr->Recycle, pr->AutoPrune, pr->LabelType,
271 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
277 db_update_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
283 Mmsg(mdb->cmd, "UPDATE Storage SET AutoChanger=%d WHERE StorageId=%s",
284 sr->AutoChanger, edit_int64(sr->StorageId, ed1));
286 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
293 * Update the Media Record at end of Session
295 * Returns: 0 on failure
299 db_update_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
301 char dt[MAX_TIME_LENGTH];
305 char ed1[50], ed2[50], ed3[50], ed4[50];
306 char ed5[50], ed6[50], ed7[50], ed8[50];
307 char ed9[50], ed10[50], ed11[50];
310 Dmsg1(100, "update_media: FirstWritten=%d\n", mr->FirstWritten);
312 if (mr->set_first_written) {
313 Dmsg1(400, "Set FirstWritten Vol=%s\n", mr->VolumeName);
314 ttime = mr->FirstWritten;
315 (void)localtime_r(&ttime, &tm);
316 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
317 Mmsg(mdb->cmd, "UPDATE Media SET FirstWritten='%s'"
318 " WHERE VolumeName='%s'", dt, mr->VolumeName);
319 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
320 Dmsg1(400, "Firstwritten=%d\n", mr->FirstWritten);
323 /* Label just done? */
324 if (mr->set_label_date) {
325 ttime = mr->LabelDate;
329 (void)localtime_r(&ttime, &tm);
330 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
331 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
332 "WHERE VolumeName='%s'", dt, mr->VolumeName);
333 UPDATE_DB(jcr, mdb, mdb->cmd);
336 if (mr->LastWritten != 0) {
337 ttime = mr->LastWritten;
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 LastWritten='%s' "
341 "WHERE VolumeName='%s'", dt, mr->VolumeName);
342 UPDATE_DB(jcr, mdb, mdb->cmd);
345 Mmsg(mdb->cmd, "UPDATE Media SET VolJobs=%u,"
346 "VolFiles=%u,VolBlocks=%u,VolBytes=%s,VolMounts=%u,VolErrors=%u,"
347 "VolWrites=%u,MaxVolBytes=%s,VolStatus='%s',"
348 "Slot=%d,InChanger=%d,VolReadTime=%s,VolWriteTime=%s,VolParts=%d,"
349 "LabelType=%d,StorageId=%s,PoolId=%s,VolRetention=%s,VolUseDuration=%s,"
350 "MaxVolJobs=%d,MaxVolFiles=%d,Enabled=%d,LocationId=%s,"
351 "ScratchPoolId=%s,RecyclePoolId=%s,RecycleCount=%d"
352 " WHERE VolumeName='%s'",
353 mr->VolJobs, mr->VolFiles, mr->VolBlocks, edit_uint64(mr->VolBytes, ed1),
354 mr->VolMounts, mr->VolErrors, mr->VolWrites,
355 edit_uint64(mr->MaxVolBytes, ed2),
356 mr->VolStatus, mr->Slot, mr->InChanger,
357 edit_uint64(mr->VolReadTime, ed3),
358 edit_uint64(mr->VolWriteTime, ed4),
361 edit_int64(mr->StorageId, ed5),
362 edit_int64(mr->PoolId, ed6),
363 edit_uint64(mr->VolRetention, ed7),
364 edit_uint64(mr->VolUseDuration, ed8),
365 mr->MaxVolJobs, mr->MaxVolFiles,
366 mr->Enabled, edit_uint64(mr->LocationId, ed9),
367 edit_uint64(mr->ScratchPoolId, ed10),
368 edit_uint64(mr->RecyclePoolId, ed11),
372 Dmsg1(400, "%s\n", mdb->cmd);
374 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
376 /* Make sure InChanger is 0 for any record having the same Slot */
377 db_make_inchanger_unique(jcr, mdb, mr);
384 * Update the Media Record Default values from Pool
386 * Returns: 0 on failure
390 db_update_media_defaults(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
393 char ed1[50], ed2[50], ed3[50], ed4[50];
397 if (mr->VolumeName[0]) {
398 Mmsg(mdb->cmd, "UPDATE Media SET "
399 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
400 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s"
401 " WHERE VolumeName='%s'",
402 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
403 edit_uint64(mr->VolUseDuration, ed2),
404 mr->MaxVolJobs, mr->MaxVolFiles,
405 edit_uint64(mr->MaxVolBytes, ed3),
408 Mmsg(mdb->cmd, "UPDATE Media SET "
409 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
410 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s"
412 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
413 edit_uint64(mr->VolUseDuration, ed2),
414 mr->MaxVolJobs, mr->MaxVolFiles,
415 edit_uint64(mr->MaxVolBytes, ed3),
416 edit_int64(mr->PoolId, ed4));
419 Dmsg1(400, "%s\n", mdb->cmd);
421 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
429 * If we have a non-zero InChanger, ensure that no other Media
430 * record has InChanger set on the same Slot.
432 * This routine assumes the database is already locked.
435 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
437 char ed1[50], ed2[50];
438 if (mr->InChanger != 0 && mr->Slot != 0) {
439 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0 WHERE "
440 "Slot=%d AND StorageId=%s AND MediaId!=%s",
442 edit_int64(mr->StorageId, ed1), edit_int64(mr->MediaId, ed2));
443 Dmsg1(400, "%s\n", mdb->cmd);
444 UPDATE_DB(jcr, mdb, mdb->cmd);
451 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
453 /* DUMMY func for Bacula_DB */
457 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/