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 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 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 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];
309 Dmsg1(100, "update_media: FirstWritten=%d\n", mr->FirstWritten);
311 if (mr->set_first_written) {
312 Dmsg1(400, "Set FirstWritten Vol=%s\n", mr->VolumeName);
313 ttime = mr->FirstWritten;
314 localtime_r(&ttime, &tm);
315 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
316 Mmsg(mdb->cmd, "UPDATE Media SET FirstWritten='%s'"
317 " WHERE VolumeName='%s'", dt, mr->VolumeName);
318 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
319 Dmsg1(400, "Firstwritten=%d\n", mr->FirstWritten);
322 /* Label just done? */
323 if (mr->set_label_date) {
324 ttime = mr->LabelDate;
328 localtime_r(&ttime, &tm);
329 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
330 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
331 "WHERE VolumeName='%s'", dt, mr->VolumeName);
332 UPDATE_DB(jcr, mdb, mdb->cmd);
335 if (mr->LastWritten != 0) {
336 ttime = mr->LastWritten;
337 localtime_r(&ttime, &tm);
338 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
339 Mmsg(mdb->cmd, "UPDATE Media Set LastWritten='%s' "
340 "WHERE VolumeName='%s'", dt, mr->VolumeName);
341 UPDATE_DB(jcr, mdb, mdb->cmd);
344 Mmsg(mdb->cmd, "UPDATE Media SET VolJobs=%u,"
345 "VolFiles=%u,VolBlocks=%u,VolBytes=%s,VolMounts=%u,VolErrors=%u,"
346 "VolWrites=%u,MaxVolBytes=%s,VolStatus='%s',"
347 "Slot=%d,InChanger=%d,VolReadTime=%s,VolWriteTime=%s,VolParts=%d,"
348 "LabelType=%d,StorageId=%s,PoolId=%s,VolRetention=%s,VolUseDuration=%s,"
349 "MaxVolJobs=%d,MaxVolFiles=%d"
350 " WHERE VolumeName='%s'",
351 mr->VolJobs, mr->VolFiles, mr->VolBlocks, edit_uint64(mr->VolBytes, ed1),
352 mr->VolMounts, mr->VolErrors, mr->VolWrites,
353 edit_uint64(mr->MaxVolBytes, ed2),
354 mr->VolStatus, mr->Slot, mr->InChanger,
355 edit_uint64(mr->VolReadTime, ed3),
356 edit_uint64(mr->VolWriteTime, ed4),
359 edit_int64(mr->StorageId, ed5),
360 edit_int64(mr->PoolId, ed6),
361 edit_uint64(mr->VolRetention, ed7),
362 edit_uint64(mr->VolUseDuration, ed8),
363 mr->MaxVolJobs, mr->MaxVolFiles,
366 Dmsg1(400, "%s\n", mdb->cmd);
368 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
370 /* Make sure InChanger is 0 for any record having the same Slot */
371 db_make_inchanger_unique(jcr, mdb, mr);
378 * Update the Media Record Default values from Pool
380 * Returns: 0 on failure
384 db_update_media_defaults(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
387 char ed1[50], ed2[50], ed3[50], ed4[50];
391 if (mr->VolumeName[0]) {
392 Mmsg(mdb->cmd, "UPDATE Media SET "
393 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
394 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s"
395 " WHERE VolumeName='%s'",
396 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
397 edit_uint64(mr->VolUseDuration, ed2),
398 mr->MaxVolJobs, mr->MaxVolFiles,
399 edit_uint64(mr->MaxVolBytes, ed3),
402 Mmsg(mdb->cmd, "UPDATE Media SET "
403 "Recycle=%d,VolRetention=%s,VolUseDuration=%s,"
404 "MaxVolJobs=%u,MaxVolFiles=%u,MaxVolBytes=%s"
406 mr->Recycle,edit_uint64(mr->VolRetention, ed1),
407 edit_uint64(mr->VolUseDuration, ed2),
408 mr->MaxVolJobs, mr->MaxVolFiles,
409 edit_uint64(mr->MaxVolBytes, ed3),
410 edit_int64(mr->PoolId, ed4));
413 Dmsg1(400, "%s\n", mdb->cmd);
415 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
423 * If we have a non-zero InChanger, ensure that no other Media
424 * record has InChanger set on the same Slot.
426 * This routine assumes the database is already locked.
429 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
431 char ed1[50], ed2[50];
432 if (mr->InChanger != 0 && mr->Slot != 0) {
433 Mmsg(mdb->cmd, "UPDATE Media SET InChanger=0 WHERE "
434 "Slot=%d AND StorageId=%s AND MediaId!=%s",
436 edit_int64(mr->StorageId, ed1), edit_int64(mr->MediaId, ed2));
437 Dmsg1(400, "%s\n", mdb->cmd);
438 UPDATE_DB(jcr, mdb, mdb->cmd);
445 db_make_inchanger_unique(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
447 /* DUMMY func for Bacula_DB */
451 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/