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 /* -----------------------------------------------------------------------
42 * Generic Routines (or almost generic)
44 * -----------------------------------------------------------------------
46 /* Update the attributes record by adding the file digest */
48 db_add_digest_to_file_record(JCR *jcr, B_DB *mdb, FileId_t FileId, char *digest,
55 Mmsg(mdb->cmd, "UPDATE File SET MD5='%s' WHERE FileId=%s", digest,
56 edit_int64(FileId, ed1));
57 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
62 /* Mark the file record as being visited during database
63 * verify compare. Stuff JobId into the MarkId field
65 int db_mark_file_record(JCR *jcr, B_DB *mdb, FileId_t FileId, JobId_t JobId)
68 char ed1[50], ed2[50];
71 Mmsg(mdb->cmd, "UPDATE File SET MarkId=%s WHERE FileId=%s",
72 edit_int64(JobId, ed1), edit_int64(FileId, ed2));
73 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
79 * Update the Job record at start of Job
81 * Returns: false on failure
85 db_update_job_start_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
87 char dt[MAX_TIME_LENGTH];
92 char ed1[50], ed2[50], ed3[50], ed4[50];
94 stime = jr->StartTime;
95 (void)localtime_r(&stime, &tm);
96 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
97 JobTDate = (btime_t)stime;
100 Mmsg(mdb->cmd, "UPDATE Job SET JobStatus='%c',Level='%c',StartTime='%s',"
101 "ClientId=%s,JobTDate=%s,PoolId=%s WHERE JobId=%s",
102 (char)(jcr->JobStatus),
103 (char)(jr->JobLevel), dt,
104 edit_int64(jr->ClientId, ed1),
105 edit_uint64(JobTDate, ed2),
106 edit_int64(jr->PoolId, ed3),
107 edit_int64(jr->JobId, ed4));
109 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
116 * Given an incoming integer, set the string buffer to either NULL or the value
119 static void edit_num_or_null(char *s, size_t n, uint64_t id) {
121 bsnprintf(s, n, id ? "%s" : "NULL", edit_int64(id, ed1));
126 * Update the Job record at end of Job
128 * Returns: 0 on failure
132 db_update_job_end_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
134 char dt[MAX_TIME_LENGTH];
135 char rdt[MAX_TIME_LENGTH];
139 char ed1[30], ed2[30], ed3[50];
141 char PoolId[50], FileSetId[50], ClientId[50], PriorJobId[50];
144 /* some values are set to zero, which translates to NULL in SQL */
145 edit_num_or_null(PoolId, sizeof(PoolId), jr->PoolId);
146 edit_num_or_null(FileSetId, sizeof(FileSetId), jr->FileSetId);
147 edit_num_or_null(ClientId, sizeof(ClientId), jr->ClientId);
149 if (jr->PriorJobId) {
150 bstrncpy(PriorJobId, edit_int64(jr->PriorJobId, ed1), sizeof(PriorJobId));
152 bstrncpy(PriorJobId, "0", sizeof(PriorJobId));
156 (void)localtime_r(&ttime, &tm);
157 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
159 if (jr->RealEndTime == 0) {
160 jr->RealEndTime = jr->EndTime;
162 ttime = jr->RealEndTime;
163 (void)localtime_r(&ttime, &tm);
164 strftime(rdt, sizeof(rdt), "%Y-%m-%d %H:%M:%S", &tm);
170 "UPDATE Job SET JobStatus='%c',EndTime='%s',"
171 "ClientId=%s,JobBytes=%s,JobFiles=%u,JobErrors=%u,VolSessionId=%u,"
172 "VolSessionTime=%u,PoolId=%s,FileSetId=%s,JobTDate=%s,"
173 "RealEndTime='%s',PriorJobId=%s WHERE JobId=%s",
174 (char)(jr->JobStatus), dt, ClientId, edit_uint64(jr->JobBytes, ed1),
175 jr->JobFiles, jr->JobErrors, jr->VolSessionId, jr->VolSessionTime,
176 PoolId, FileSetId, edit_uint64(JobTDate, ed2),
179 edit_int64(jr->JobId, ed3));
181 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
188 * Update Client record
189 * Returns: 0 on failure
193 db_update_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
196 char ed1[50], ed2[50];
200 memcpy(&tcr, cr, sizeof(tcr));
201 if (!db_create_client_record(jcr, mdb, &tcr)) {
207 "UPDATE Client SET AutoPrune=%d,FileRetention=%s,JobRetention=%s,"
208 "Uname='%s' WHERE Name='%s'",
210 edit_uint64(cr->FileRetention, ed1),
211 edit_uint64(cr->JobRetention, ed2),
212 cr->Uname, cr->Name);
214 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
221 * Update Counters record
222 * Returns: 0 on failure
225 int db_update_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
230 "UPDATE Counters SET MinValue=%d,MaxValue=%d,CurrentValue=%d,"
231 "WrapCounter='%s' WHERE Counter='%s'",
232 cr->MinValue, cr->MaxValue, cr->CurrentValue,
233 cr->WrapCounter, cr->Counter);
235 int stat = UPDATE_DB(jcr, mdb, mdb->cmd);
242 db_update_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
245 char ed1[50], ed2[50], ed3[50], ed4[50];
248 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
249 edit_int64(pr->PoolId, ed4));
250 pr->NumVols = get_sql_record_max(jcr, mdb);
251 Dmsg1(400, "NumVols=%d\n", pr->NumVols);
254 "UPDATE Pool SET NumVols=%u,MaxVols=%u,UseOnce=%d,UseCatalog=%d,"
255 "AcceptAnyVolume=%d,VolRetention='%s',VolUseDuration='%s',"
256 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s,Recycle=%d,"
257 "AutoPrune=%d,LabelType=%d,LabelFormat='%s' WHERE PoolId=%s",
258 pr->NumVols, pr->MaxVols, pr->UseOnce, pr->UseCatalog,
259 pr->AcceptAnyVolume, edit_uint64(pr->VolRetention, ed1),
260 edit_uint64(pr->VolUseDuration, ed2),
261 pr->MaxVolJobs, pr->MaxVolFiles,
262 edit_uint64(pr->MaxVolBytes, ed3),
263 pr->Recycle, pr->AutoPrune, pr->LabelType,
267 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
273 db_update_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
279 Mmsg(mdb->cmd, "UPDATE Storage SET AutoChanger=%d WHERE StorageId=%s",
280 sr->AutoChanger, edit_int64(sr->StorageId, ed1));
282 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
289 * Update the Media Record at end of Session
291 * Returns: 0 on failure
295 db_update_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
297 char dt[MAX_TIME_LENGTH];
301 char ed1[50], ed2[50], ed3[50], ed4[50];
302 char ed5[50], ed6[50], ed7[50], ed8[50];
303 char ed9[50], ed10[50], ed11[50];
306 Dmsg1(100, "update_media: FirstWritten=%d\n", mr->FirstWritten);
308 if (mr->set_first_written) {
309 Dmsg1(400, "Set FirstWritten Vol=%s\n", mr->VolumeName);
310 ttime = mr->FirstWritten;
311 (void)localtime_r(&ttime, &tm);
312 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
313 Mmsg(mdb->cmd, "UPDATE Media SET FirstWritten='%s'"
314 " WHERE VolumeName='%s'", dt, mr->VolumeName);
315 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
316 Dmsg1(400, "Firstwritten=%d\n", mr->FirstWritten);
319 /* Label just done? */
320 if (mr->set_label_date) {
321 ttime = mr->LabelDate;
325 (void)localtime_r(&ttime, &tm);
326 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
327 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
328 "WHERE VolumeName='%s'", dt, mr->VolumeName);
329 UPDATE_DB(jcr, mdb, mdb->cmd);
332 if (mr->LastWritten != 0) {
333 ttime = mr->LastWritten;
334 (void)localtime_r(&ttime, &tm);
335 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
336 Mmsg(mdb->cmd, "UPDATE Media Set LastWritten='%s' "
337 "WHERE VolumeName='%s'", dt, mr->VolumeName);
338 UPDATE_DB(jcr, mdb, mdb->cmd);
341 Mmsg(mdb->cmd, "UPDATE Media SET VolJobs=%u,"
342 "VolFiles=%u,VolBlocks=%u,VolBytes=%s,VolMounts=%u,VolErrors=%u,"
343 "VolWrites=%u,MaxVolBytes=%s,VolStatus='%s',"
344 "Slot=%d,InChanger=%d,VolReadTime=%s,VolWriteTime=%s,VolParts=%d,"
345 "LabelType=%d,StorageId=%s,PoolId=%s,VolRetention=%s,VolUseDuration=%s,"
346 "MaxVolJobs=%d,MaxVolFiles=%d,Enabled=%d,LocationId=%s,"
347 "ScratchPoolId=%s,RecyclePoolId=%s,RecycleCount=%d"
348 " WHERE VolumeName='%s'",
349 mr->VolJobs, mr->VolFiles, mr->VolBlocks, edit_uint64(mr->VolBytes, ed1),
350 mr->VolMounts, mr->VolErrors, mr->VolWrites,
351 edit_uint64(mr->MaxVolBytes, ed2),
352 mr->VolStatus, mr->Slot, mr->InChanger,
353 edit_uint64(mr->VolReadTime, ed3),
354 edit_uint64(mr->VolWriteTime, ed4),
357 edit_int64(mr->StorageId, ed5),
358 edit_int64(mr->PoolId, ed6),
359 edit_uint64(mr->VolRetention, ed7),
360 edit_uint64(mr->VolUseDuration, ed8),
361 mr->MaxVolJobs, mr->MaxVolFiles,
362 mr->Enabled, edit_uint64(mr->LocationId, ed9),
363 edit_uint64(mr->ScratchPoolId, ed10),
364 edit_uint64(mr->RecyclePoolId, ed11),
368 Dmsg1(400, "%s\n", mdb->cmd);
370 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
372 /* Make sure InChanger is 0 for any record having the same Slot */
373 db_make_inchanger_unique(jcr, mdb, mr);
380 * Update the Media Record Default values from Pool
382 * Returns: 0 on failure
386 db_update_media_defaults(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
389 char ed1[50], ed2[50], ed3[50], ed4[50];
393 if (mr->VolumeName[0]) {
394 Mmsg(mdb->cmd, "UPDATE Media SET "
395 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
396 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s"
397 " WHERE VolumeName='%s'",
398 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
399 edit_uint64(mr->VolUseDuration, ed2),
400 mr->MaxVolJobs, mr->MaxVolFiles,
401 edit_uint64(mr->MaxVolBytes, ed3),
404 Mmsg(mdb->cmd, "UPDATE Media SET "
405 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
406 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s"
408 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
409 edit_uint64(mr->VolUseDuration, ed2),
410 mr->MaxVolJobs, mr->MaxVolFiles,
411 edit_uint64(mr->MaxVolBytes, ed3),
412 edit_int64(mr->PoolId, ed4));
415 Dmsg1(400, "%s\n", mdb->cmd);
417 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
425 * If we have a non-zero InChanger, ensure that no other Media
426 * record has InChanger set on the same Slot.
428 * This routine assumes the database is already locked.
431 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
433 char ed1[50], ed2[50];
434 if (mr->InChanger != 0 && mr->Slot != 0) {
435 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0 WHERE "
436 "Slot=%d AND StorageId=%s AND MediaId!=%s",
438 edit_int64(mr->StorageId, ed1), edit_int64(mr->MediaId, ed2));
439 Dmsg1(400, "%s\n", mdb->cmd);
440 UPDATE_DB(jcr, mdb, mdb->cmd);
444 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/