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 Create record interface routines
19 * Written by Kern Sibbald, March 2000
25 static const int dbglevel = 100;
27 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL
33 /* -----------------------------------------------------------------------
35 * Generic Routines (or almost generic)
37 * -----------------------------------------------------------------------
40 /* Forward referenced subroutines */
41 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar);
42 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar);
44 /** Create a new record for the Job
45 * Returns: false on failure
49 db_create_job_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
52 char dt[MAX_TIME_LENGTH];
59 char esc_job[MAX_ESCAPE_NAME_LENGTH];
60 char esc_name[MAX_ESCAPE_NAME_LENGTH];
64 stime = jr->SchedTime;
67 (void)localtime_r(&stime, &tm);
68 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
69 JobTDate = (utime_t)stime;
71 len = strlen(jcr->comment); /* TODO: use jr instead of jcr to get comment */
72 buf.check_size(len*2+1);
73 mdb->db_escape_string(jcr, buf.c_str(), jcr->comment, len);
75 mdb->db_escape_string(jcr, esc_job, jr->Job, strlen(jr->Job));
76 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
80 "INSERT INTO Job (Job,Name,Type,Level,JobStatus,SchedTime,JobTDate,"
82 "VALUES ('%s','%s','%c','%c','%c','%s',%s,%s,'%s')",
83 esc_job, esc_name, (char)(jr->JobType), (char)(jr->JobLevel),
84 (char)(jr->JobStatus), dt, edit_uint64(JobTDate, ed1),
85 edit_int64(jr->ClientId, ed2), buf.c_str());
87 jr->JobId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Job"));
89 Mmsg2(&mdb->errmsg, _("Create DB Job record %s failed. ERR=%s\n"),
90 mdb->cmd, sql_strerror(mdb));
100 /** Create a JobMedia record for medium used this job
101 * Returns: false on failure
105 db_create_jobmedia_record(JCR *jcr, B_DB *mdb, JOBMEDIA_DBR *jm)
109 char ed1[50], ed2[50];
113 /* Now get count for VolIndex */
114 Mmsg(mdb->cmd, "SELECT count(*) from JobMedia WHERE JobId=%s",
115 edit_int64(jm->JobId, ed1));
116 count = get_sql_record_max(jcr, mdb);
123 "INSERT INTO JobMedia (JobId,MediaId,FirstIndex,LastIndex,"
124 "StartFile,EndFile,StartBlock,EndBlock,VolIndex) "
125 "VALUES (%s,%s,%u,%u,%u,%u,%u,%u,%u)",
126 edit_int64(jm->JobId, ed1),
127 edit_int64(jm->MediaId, ed2),
128 jm->FirstIndex, jm->LastIndex,
129 jm->StartFile, jm->EndFile, jm->StartBlock, jm->EndBlock,count);
131 Dmsg0(300, mdb->cmd);
132 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
133 Mmsg2(&mdb->errmsg, _("Create JobMedia record %s failed: ERR=%s\n"), mdb->cmd,
137 /* Worked, now update the Media record with the EndFile and EndBlock */
139 "UPDATE Media SET EndFile=%u, EndBlock=%u WHERE MediaId=%u",
140 jm->EndFile, jm->EndBlock, jm->MediaId);
141 if (!UPDATE_DB(jcr, mdb, mdb->cmd)) {
142 Mmsg2(&mdb->errmsg, _("Update Media record %s failed: ERR=%s\n"), mdb->cmd,
148 Dmsg0(300, "Return from JobMedia\n");
152 /** Create Unique Pool record
153 * Returns: false on failure
157 db_create_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
160 char ed1[30], ed2[30], ed3[50], ed4[50], ed5[50];
161 char esc_name[MAX_ESCAPE_NAME_LENGTH];
162 char esc_lf[MAX_ESCAPE_NAME_LENGTH];
167 Dmsg0(200, "In create pool\n");
169 mdb->db_escape_string(jcr, esc_name, pr->Name, strlen(pr->Name));
170 mdb->db_escape_string(jcr, esc_lf, pr->LabelFormat, strlen(pr->LabelFormat));
171 Mmsg(mdb->cmd, "SELECT PoolId,Name FROM Pool WHERE Name='%s'", esc_name);
172 Dmsg1(200, "selectpool: %s\n", mdb->cmd);
174 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
175 num_rows = sql_num_rows(mdb);
177 Mmsg1(&mdb->errmsg, _("pool record %s already exists\n"), pr->Name);
178 sql_free_result(mdb);
182 sql_free_result(mdb);
187 "INSERT INTO Pool (Name,NumVols,MaxVols,UseOnce,UseCatalog,"
188 "AcceptAnyVolume,AutoPrune,Recycle,VolRetention,VolUseDuration,"
189 "MaxVolJobs,MaxVolFiles,MaxVolBytes,PoolType,LabelType,LabelFormat,"
190 "RecyclePoolId,ScratchPoolId,ActionOnPurge) "
191 "VALUES ('%s',%u,%u,%d,%d,%d,%d,%d,%s,%s,%u,%u,%s,'%s',%d,'%s',%s,%s,%d)",
193 pr->NumVols, pr->MaxVols,
194 pr->UseOnce, pr->UseCatalog,
196 pr->AutoPrune, pr->Recycle,
197 edit_uint64(pr->VolRetention, ed1),
198 edit_uint64(pr->VolUseDuration, ed2),
199 pr->MaxVolJobs, pr->MaxVolFiles,
200 edit_uint64(pr->MaxVolBytes, ed3),
201 pr->PoolType, pr->LabelType, esc_lf,
202 edit_int64(pr->RecyclePoolId,ed4),
203 edit_int64(pr->ScratchPoolId,ed5),
206 Dmsg1(200, "Create Pool: %s\n", mdb->cmd);
207 pr->PoolId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Pool"));
208 if (pr->PoolId == 0) {
209 Mmsg2(&mdb->errmsg, _("Create db Pool record %s failed: ERR=%s\n"),
210 mdb->cmd, sql_strerror(mdb));
216 Dmsg0(500, "Create Pool: done\n");
221 * Create Unique Device record
222 * Returns: false on failure
226 db_create_device_record(JCR *jcr, B_DB *mdb, DEVICE_DBR *dr)
229 char ed1[30], ed2[30];
230 char esc[MAX_ESCAPE_NAME_LENGTH];
233 Dmsg0(200, "In create Device\n");
235 mdb->db_escape_string(jcr, esc, dr->Name, strlen(dr->Name));
236 Mmsg(mdb->cmd, "SELECT DeviceId,Name FROM Device WHERE Name='%s'", esc);
237 Dmsg1(200, "selectdevice: %s\n", mdb->cmd);
239 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
240 num_rows = sql_num_rows(mdb);
242 Mmsg1(&mdb->errmsg, _("Device record %s already exists\n"), dr->Name);
243 sql_free_result(mdb);
247 sql_free_result(mdb);
252 "INSERT INTO Device (Name,MediaTypeId,StorageId) VALUES ('%s',%s,%s)",
254 edit_uint64(dr->MediaTypeId, ed1),
255 edit_int64(dr->StorageId, ed2));
256 Dmsg1(200, "Create Device: %s\n", mdb->cmd);
257 dr->DeviceId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Device"));
258 if (dr->DeviceId == 0) {
259 Mmsg2(&mdb->errmsg, _("Create db Device record %s failed: ERR=%s\n"),
260 mdb->cmd, sql_strerror(mdb));
272 * Create a Unique record for Storage -- no duplicates
273 * Returns: false on failure
274 * true on success with id in sr->StorageId
276 bool db_create_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
281 char esc[MAX_ESCAPE_NAME_LENGTH];
284 mdb->db_escape_string(jcr, esc, sr->Name, strlen(sr->Name));
285 Mmsg(mdb->cmd, "SELECT StorageId,AutoChanger FROM Storage WHERE Name='%s'",esc);
289 /* Check if it already exists */
290 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
291 num_rows = sql_num_rows(mdb);
292 /* If more than one, report error, but return first row */
294 Mmsg1(&mdb->errmsg, _("More than one Storage record!: %d\n"), num_rows);
295 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
298 if ((row = sql_fetch_row(mdb)) == NULL) {
299 Mmsg1(&mdb->errmsg, _("error fetching Storage row: %s\n"), sql_strerror(mdb));
300 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
301 sql_free_result(mdb);
305 sr->StorageId = str_to_int64(row[0]);
306 sr->AutoChanger = atoi(row[1]); /* bool */
307 sql_free_result(mdb);
311 sql_free_result(mdb);
315 Mmsg(mdb->cmd, "INSERT INTO Storage (Name,AutoChanger)"
316 " VALUES ('%s',%d)", esc, sr->AutoChanger);
318 sr->StorageId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Storage"));
319 if (sr->StorageId == 0) {
320 Mmsg2(&mdb->errmsg, _("Create DB Storage record %s failed. ERR=%s\n"),
321 mdb->cmd, sql_strerror(mdb));
322 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
334 * Create Unique MediaType record
335 * Returns: false on failure
339 db_create_mediatype_record(JCR *jcr, B_DB *mdb, MEDIATYPE_DBR *mr)
343 char esc[MAX_ESCAPE_NAME_LENGTH];
345 Dmsg0(200, "In create mediatype\n");
347 mdb->db_escape_string(jcr, esc, mr->MediaType, strlen(mr->MediaType));
348 Mmsg(mdb->cmd, "SELECT MediaTypeId,MediaType FROM MediaType WHERE MediaType='%s'", esc);
349 Dmsg1(200, "selectmediatype: %s\n", mdb->cmd);
351 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
352 num_rows = sql_num_rows(mdb);
354 Mmsg1(&mdb->errmsg, _("mediatype record %s already exists\n"), mr->MediaType);
355 sql_free_result(mdb);
359 sql_free_result(mdb);
364 "INSERT INTO MediaType (MediaType,ReadOnly) "
368 Dmsg1(200, "Create mediatype: %s\n", mdb->cmd);
369 mr->MediaTypeId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("MediaType"));
370 if (mr->MediaTypeId == 0) {
371 Mmsg2(&mdb->errmsg, _("Create db mediatype record %s failed: ERR=%s\n"),
372 mdb->cmd, sql_strerror(mdb));
383 * Create Media record. VolumeName and non-zero Slot must be unique
385 * Returns: 0 on failure
389 db_create_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
392 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50], ed7[50], ed8[50];
393 char ed9[50], ed10[50], ed11[50], ed12[50];
396 char esc_name[MAX_ESCAPE_NAME_LENGTH];
397 char esc_mtype[MAX_ESCAPE_NAME_LENGTH];
398 char esc_status[MAX_ESCAPE_NAME_LENGTH];
402 mdb->db_escape_string(jcr, esc_name, mr->VolumeName, strlen(mr->VolumeName));
403 mdb->db_escape_string(jcr, esc_mtype, mr->MediaType, strlen(mr->MediaType));
404 mdb->db_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
406 Mmsg(mdb->cmd, "SELECT MediaId FROM Media WHERE VolumeName='%s'", esc_name);
407 Dmsg1(500, "selectpool: %s\n", mdb->cmd);
409 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
410 num_rows = sql_num_rows(mdb);
412 Mmsg1(&mdb->errmsg, _("Volume \"%s\" already exists.\n"), mr->VolumeName);
413 sql_free_result(mdb);
417 sql_free_result(mdb);
422 "INSERT INTO Media (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,"
423 "VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
424 "VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolParts,"
425 "EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,"
426 "ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge)"
427 "VALUES ('%s','%s',0,%u,%s,%s,%d,%s,%s,%u,%u,'%s',%d,%s,%d,%s,%s,%d,0,0,%d,%s,"
428 "%s,%s,%s,%s,%d,%d)",
430 esc_mtype, mr->PoolId,
431 edit_uint64(mr->MaxVolBytes,ed1),
432 edit_uint64(mr->VolCapacityBytes, ed2),
434 edit_uint64(mr->VolRetention, ed3),
435 edit_uint64(mr->VolUseDuration, ed4),
440 edit_uint64(mr->VolBytes, ed5),
442 edit_int64(mr->VolReadTime, ed6),
443 edit_int64(mr->VolWriteTime, ed7),
446 edit_int64(mr->StorageId, ed8),
447 edit_int64(mr->DeviceId, ed9),
448 edit_int64(mr->LocationId, ed10),
449 edit_int64(mr->ScratchPoolId, ed11),
450 edit_int64(mr->RecyclePoolId, ed12),
451 mr->Enabled, mr->ActionOnPurge
455 Dmsg1(500, "Create Volume: %s\n", mdb->cmd);
456 mr->MediaId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Media"));
457 if (mr->MediaId == 0) {
458 Mmsg2(&mdb->errmsg, _("Create DB Media record %s failed. ERR=%s\n"),
459 mdb->cmd, sql_strerror(mdb));
463 if (mr->set_label_date) {
464 char dt[MAX_TIME_LENGTH];
465 if (mr->LabelDate == 0) {
466 mr->LabelDate = time(NULL);
468 (void)localtime_r(&mr->LabelDate, &tm);
469 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
470 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
471 "WHERE MediaId=%d", dt, mr->MediaId);
472 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
475 * Make sure that if InChanger is non-zero any other identical slot
476 * has InChanger zero.
478 db_make_inchanger_unique(jcr, mdb, mr);
486 * Create a Unique record for the client -- no duplicates
487 * Returns: 0 on failure
488 * 1 on success with id in cr->ClientId
490 int db_create_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
494 char ed1[50], ed2[50];
496 char esc_name[MAX_ESCAPE_NAME_LENGTH];
497 char esc_uname[MAX_ESCAPE_NAME_LENGTH];
500 mdb->db_escape_string(jcr, esc_name, cr->Name, strlen(cr->Name));
501 mdb->db_escape_string(jcr, esc_uname, cr->Uname, strlen(cr->Uname));
502 Mmsg(mdb->cmd, "SELECT ClientId,Uname FROM Client WHERE Name='%s'",esc_name);
505 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
506 num_rows = sql_num_rows(mdb);
507 /* If more than one, report error, but return first row */
509 Mmsg1(&mdb->errmsg, _("More than one Client!: %d\n"), num_rows);
510 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
513 if ((row = sql_fetch_row(mdb)) == NULL) {
514 Mmsg1(&mdb->errmsg, _("error fetching Client row: %s\n"), sql_strerror(mdb));
515 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
516 sql_free_result(mdb);
520 cr->ClientId = str_to_int64(row[0]);
522 bstrncpy(cr->Uname, row[1], sizeof(cr->Uname));
524 cr->Uname[0] = 0; /* no name */
526 sql_free_result(mdb);
530 sql_free_result(mdb);
534 Mmsg(mdb->cmd, "INSERT INTO Client (Name,Uname,AutoPrune,"
535 "FileRetention,JobRetention) VALUES "
536 "('%s','%s',%d,%s,%s)", esc_name, esc_uname, cr->AutoPrune,
537 edit_uint64(cr->FileRetention, ed1),
538 edit_uint64(cr->JobRetention, ed2));
540 cr->ClientId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Client"));
541 if (cr->ClientId == 0) {
542 Mmsg2(&mdb->errmsg, _("Create DB Client record %s failed. ERR=%s\n"),
543 mdb->cmd, sql_strerror(mdb));
544 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
554 /** Create a Unique record for the Path -- no duplicates */
555 int db_create_path_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
562 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
563 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
565 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
566 strcmp(mdb->cached_path, mdb->path) == 0) {
567 ar->PathId = mdb->cached_path_id;
571 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
573 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
574 num_rows = sql_num_rows(mdb);
577 Mmsg2(&mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
578 edit_uint64(num_rows, ed1), mdb->path);
579 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
581 /* Even if there are multiple paths, take the first one */
583 if ((row = sql_fetch_row(mdb)) == NULL) {
584 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
585 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
586 sql_free_result(mdb);
589 "Your Path table is broken. "
590 "Please, use dbcheck to correct it.");
593 ar->PathId = str_to_int64(row[0]);
594 sql_free_result(mdb);
596 if (ar->PathId != mdb->cached_path_id) {
597 mdb->cached_path_id = ar->PathId;
598 mdb->cached_path_len = mdb->pnl;
599 pm_strcpy(mdb->cached_path, mdb->path);
604 sql_free_result(mdb);
607 Mmsg(mdb->cmd, "INSERT INTO Path (Path) VALUES ('%s')", mdb->esc_name);
609 ar->PathId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Path"));
610 if (ar->PathId == 0) {
611 Mmsg2(&mdb->errmsg, _("Create db Path record %s failed. ERR=%s\n"),
612 mdb->cmd, sql_strerror(mdb));
613 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
621 if (stat && ar->PathId != mdb->cached_path_id) {
622 mdb->cached_path_id = ar->PathId;
623 mdb->cached_path_len = mdb->pnl;
624 pm_strcpy(mdb->cached_path, mdb->path);
630 * Create a Unique record for the counter -- no duplicates
631 * Returns: 0 on failure
632 * 1 on success with counter filled in
634 int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
636 char esc[MAX_ESCAPE_NAME_LENGTH];
641 memset(&mcr, 0, sizeof(mcr));
642 bstrncpy(mcr.Counter, cr->Counter, sizeof(mcr.Counter));
643 if (db_get_counter_record(jcr, mdb, &mcr)) {
644 memcpy(cr, &mcr, sizeof(COUNTER_DBR));
648 mdb->db_escape_string(jcr, esc, cr->Counter, strlen(cr->Counter));
651 Mmsg(mdb->cmd, insert_counter_values[db_get_type_index(mdb)],
652 esc, cr->MinValue, cr->MaxValue, cr->CurrentValue,
655 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
656 Mmsg2(&mdb->errmsg, _("Create DB Counters record %s failed. ERR=%s\n"),
657 mdb->cmd, sql_strerror(mdb));
658 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
668 * Create a FileSet record. This record is unique in the
669 * name and the MD5 signature of the include/exclude sets.
670 * Returns: 0 on failure
671 * 1 on success with FileSetId in record
673 bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
679 char esc_fs[MAX_ESCAPE_NAME_LENGTH];
680 char esc_md5[MAX_ESCAPE_NAME_LENGTH];
682 /* TODO: Escape FileSet and MD5 */
684 fsr->created = false;
685 mdb->db_escape_string(jcr, esc_fs, fsr->FileSet, strlen(fsr->FileSet));
686 mdb->db_escape_string(jcr, esc_md5, fsr->MD5, strlen(fsr->MD5));
687 Mmsg(mdb->cmd, "SELECT FileSetId,CreateTime FROM FileSet WHERE "
688 "FileSet='%s' AND MD5='%s'", esc_fs, esc_md5);
691 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
692 num_rows = sql_num_rows(mdb);
694 Mmsg1(&mdb->errmsg, _("More than one FileSet!: %d\n"), num_rows);
695 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
698 if ((row = sql_fetch_row(mdb)) == NULL) {
699 Mmsg1(&mdb->errmsg, _("error fetching FileSet row: ERR=%s\n"), sql_strerror(mdb));
700 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
701 sql_free_result(mdb);
705 fsr->FileSetId = str_to_int64(row[0]);
706 if (row[1] == NULL) {
707 fsr->cCreateTime[0] = 0;
709 bstrncpy(fsr->cCreateTime, row[1], sizeof(fsr->cCreateTime));
711 sql_free_result(mdb);
715 sql_free_result(mdb);
718 if (fsr->CreateTime == 0 && fsr->cCreateTime[0] == 0) {
719 fsr->CreateTime = time(NULL);
721 (void)localtime_r(&fsr->CreateTime, &tm);
722 strftime(fsr->cCreateTime, sizeof(fsr->cCreateTime), "%Y-%m-%d %H:%M:%S", &tm);
725 Mmsg(mdb->cmd, "INSERT INTO FileSet (FileSet,MD5,CreateTime) "
726 "VALUES ('%s','%s','%s')", esc_fs, esc_md5, fsr->cCreateTime);
728 fsr->FileSetId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("FileSet"));
729 if (fsr->FileSetId == 0) {
730 Mmsg2(&mdb->errmsg, _("Create DB FileSet record %s failed. ERR=%s\n"),
731 mdb->cmd, sql_strerror(mdb));
732 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
747 * dev_t st_dev; * device *
748 * ino_t st_ino; * inode *
749 * mode_t st_mode; * protection *
750 * nlink_t st_nlink; * number of hard links *
751 * uid_t st_uid; * user ID of owner *
752 * gid_t st_gid; * group ID of owner *
753 * dev_t st_rdev; * device type (if inode device) *
754 * off_t st_size; * total size, in bytes *
755 * unsigned long st_blksize; * blocksize for filesystem I/O *
756 * unsigned long st_blocks; * number of blocks allocated *
757 * time_t st_atime; * time of last access *
758 * time_t st_mtime; * time of last modification *
759 * time_t st_ctime; * time of last inode change *
763 /* For maintenance, we can put batch mode in hold */
764 static bool batch_mode_enabled = true;
766 void db_disable_batch_insert(bool enabled)
768 batch_mode_enabled = enabled;
772 * All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
776 * - bulk load a temp table
777 * - insert missing filenames into filename with a single query (lock filenames
778 * - table before that to avoid possible duplicate inserts with concurrent update)
779 * - insert missing paths into path with another single query
780 * - then insert the join between the temp, filename and path tables into file.
787 bool db_write_batch_file_records(JCR *jcr)
790 int JobStatus = jcr->JobStatus;
792 if (!jcr->batch_started) { /* no files to backup ? */
793 Dmsg0(50,"db_create_file_record : no files\n");
797 if (job_canceled(jcr)) {
801 jcr->JobStatus = JS_AttrInserting;
803 /* Check if batch mode is on hold */
804 while (!batch_mode_enabled) {
805 Dmsg0(50, "batch mode is on hold\n");
808 if (job_canceled(jcr)) {
813 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
815 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
816 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
819 if (job_canceled(jcr)) {
824 * We have to lock tables
826 if (!db_sql_query(jcr->db_batch, batch_lock_path_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
827 Jmsg1(jcr, M_FATAL, 0, "Lock Path table %s\n", jcr->db_batch->errmsg);
831 if (!db_sql_query(jcr->db_batch, batch_fill_path_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
832 Jmsg1(jcr, M_FATAL, 0, "Fill Path table %s\n",jcr->db_batch->errmsg);
833 db_sql_query(jcr->db_batch, batch_unlock_tables_query[db_get_type_index(jcr->db_batch)], NULL, NULL);
837 if (!db_sql_query(jcr->db_batch, batch_unlock_tables_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
838 Jmsg1(jcr, M_FATAL, 0, "Unlock Path table %s\n", jcr->db_batch->errmsg);
843 * We have to lock tables
845 if (!db_sql_query(jcr->db_batch, batch_lock_filename_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
846 Jmsg1(jcr, M_FATAL, 0, "Lock Filename table %s\n", jcr->db_batch->errmsg);
850 if (!db_sql_query(jcr->db_batch, batch_fill_filename_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
851 Jmsg1(jcr,M_FATAL,0,"Fill Filename table %s\n",jcr->db_batch->errmsg);
852 db_sql_query(jcr->db_batch, batch_unlock_tables_query[db_get_type_index(jcr->db_batch)], NULL, NULL);
856 if (!db_sql_query(jcr->db_batch, batch_unlock_tables_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
857 Jmsg1(jcr, M_FATAL, 0, "Unlock Filename table %s\n", jcr->db_batch->errmsg);
861 if (!db_sql_query(jcr->db_batch,
862 "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) "
863 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
864 "Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq "
866 "JOIN Path ON (batch.Path = Path.Path) "
867 "JOIN Filename ON (batch.Name = Filename.Name)",
870 Jmsg1(jcr, M_FATAL, 0, "Fill File table %s\n", jcr->db_batch->errmsg);
874 jcr->JobStatus = JobStatus; /* reset entry status */
878 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
879 jcr->batch_started = false;
885 * Create File record in B_DB
887 * In order to reduce database size, we store the File attributes,
888 * the FileName, and the Path separately. In principle, there
889 * is a single FileName record and a single Path record, no matter
890 * how many times it occurs. This is this subroutine, we separate
891 * the file and the path and fill temporary tables with this three records.
893 * Note: all routines that call this expect to be able to call
894 * db_strerror(mdb) to get the error message, so the error message
895 * MUST be edited into mdb->errmsg before returning an error status.
897 bool db_create_batch_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
899 ASSERT(ar->FileType != FT_BASE);
901 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
902 Dmsg0(dbglevel, "put_file_into_catalog\n");
904 if (jcr->batch_started && jcr->db_batch->changes > 500000) {
905 db_write_batch_file_records(jcr);
906 jcr->db_batch->changes = 0;
909 /* Open the dedicated connexion */
910 if (!jcr->batch_started) {
911 if (!db_open_batch_connexion(jcr, mdb)) {
912 return false; /* error already printed */
914 if (!sql_batch_start(jcr, jcr->db_batch)) {
916 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
917 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
920 jcr->batch_started = true;
923 split_path_and_file(jcr, jcr->db_batch, ar->fname);
925 return sql_batch_insert(jcr, jcr->db_batch, ar);
929 * Create File record in B_DB
931 * In order to reduce database size, we store the File attributes,
932 * the FileName, and the Path separately. In principle, there
933 * is a single FileName record and a single Path record, no matter
934 * how many times it occurs. This is this subroutine, we separate
935 * the file and the path and create three database records.
937 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
940 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
941 Dmsg0(dbglevel, "put_file_into_catalog\n");
943 split_path_and_file(jcr, mdb, ar->fname);
945 if (!db_create_filename_record(jcr, mdb, ar)) {
948 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
951 if (!db_create_path_record(jcr, mdb, ar)) {
954 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
956 /* Now create master File record */
957 if (!db_create_file_record(jcr, mdb, ar)) {
960 Dmsg0(dbglevel, "db_create_file_record OK\n");
962 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
971 * This is the master File entry containing the attributes.
972 * The filename and path records have already been created.
974 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
977 static const char *no_digest = "0";
982 ASSERT(ar->FilenameId);
984 if (ar->Digest == NULL || ar->Digest[0] == 0) {
992 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
993 "LStat,MD5,DeltaSeq) VALUES (%u,%u,%u,%u,'%s','%s',%u)",
994 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
995 ar->attr, digest, ar->DeltaSeq);
997 ar->FileId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("File"));
998 if (ar->FileId == 0) {
999 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
1000 mdb->cmd, sql_strerror(mdb));
1001 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1009 /** Create a Unique record for the filename -- no duplicates */
1010 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1016 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1017 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1019 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1021 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1022 num_rows = sql_num_rows(mdb);
1025 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1026 edit_uint64(num_rows, ed1), mdb->fname);
1027 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1029 if (num_rows >= 1) {
1030 if ((row = sql_fetch_row(mdb)) == NULL) {
1031 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1032 mdb->fname, sql_strerror(mdb));
1033 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1036 ar->FilenameId = str_to_int64(row[0]);
1038 sql_free_result(mdb);
1039 return ar->FilenameId > 0;
1041 sql_free_result(mdb);
1044 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1046 ar->FilenameId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Filename"));
1047 if (ar->FilenameId == 0) {
1048 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1049 mdb->cmd, sql_strerror(mdb));
1050 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1052 return ar->FilenameId > 0;
1056 * Create file attributes record, or base file attributes record
1058 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1064 * Make sure we have an acceptable attributes record.
1066 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1067 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1068 Mmsg1(&mdb->errmsg, _("Attempt to put non-attributes into catalog. Stream=%d\n"),
1070 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1074 if (ar->FileType != FT_BASE) {
1075 if (mdb->batch_insert_available()) {
1076 ret = db_create_batch_file_attributes_record(jcr, mdb, ar);
1077 /* Error message already printed */
1079 ret = db_create_file_attributes_record(jcr, mdb, ar);
1081 } else if (jcr->HasBase) {
1082 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1084 Mmsg0(&mdb->errmsg, _("Cannot Copy/Migrate job using BaseJob.\n"));
1085 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1086 ret = true; /* in copy/migration what do we do ? */
1093 * Create Base File record in B_DB
1096 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1099 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1100 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1103 split_path_and_file(jcr, mdb, ar->fname);
1105 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1106 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1108 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1109 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1111 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1112 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1114 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1121 * Cleanup the base file temporary tables
1123 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1125 POOL_MEM buf(PM_MESSAGE);
1126 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1127 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1129 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1130 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1134 * Put all base file seen in the backup to the BaseFile table
1135 * and cleanup temporary tables
1137 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1145 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1146 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1147 "B.FileId, B.FileIndex "
1148 "FROM basefile%s AS A, new_basefile%s AS B "
1149 "WHERE A.Path = B.Path "
1150 "AND A.Name = B.Name "
1151 "ORDER BY B.FileId",
1152 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1153 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1155 * Display error now, because the subsequent cleanup destroys the
1156 * error message from the above query.
1159 Jmsg1(jcr, M_FATAL, 0, "%s", db_strerror(jcr->db));
1161 jcr->nb_base_files_used = sql_affected_rows(mdb);
1162 db_cleanup_base_file(jcr, mdb);
1169 * Find the last "accurate" backup state with Base jobs
1170 * 1) Get all files with jobid in list (F subquery)
1171 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1172 * 3) Put the result in a temporary table for the end of job
1175 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1183 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1187 Mmsg(mdb->cmd, create_temp_basefile[db_get_type_index(mdb)], (uint64_t) jcr->JobId);
1188 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1191 Mmsg(buf, select_recent_version[db_get_type_index(mdb)], jobids, jobids);
1192 Mmsg(mdb->cmd, create_temp_new_basefile[db_get_type_index(mdb)], (uint64_t)jcr->JobId, buf.c_str());
1194 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1201 * Create Restore Object record in B_DB
1204 bool db_create_restore_object_record(JCR *jcr, B_DB *mdb, ROBJECT_DBR *ro)
1208 POOLMEM *esc_plug_name = get_pool_memory(PM_MESSAGE);
1212 Dmsg1(dbglevel, "Oname=%s\n", ro->object_name);
1213 Dmsg0(dbglevel, "put_object_into_catalog\n");
1215 mdb->fnl = strlen(ro->object_name);
1216 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1217 db_escape_string(jcr, mdb, mdb->esc_name, ro->object_name, mdb->fnl);
1219 db_escape_object(jcr, mdb, ro->object, ro->object_len);
1221 plug_name_len = strlen(ro->plugin_name);
1222 esc_plug_name = check_pool_memory_size(esc_plug_name, plug_name_len*2+1);
1223 db_escape_string(jcr, mdb, esc_plug_name, ro->plugin_name, plug_name_len);
1226 "INSERT INTO RestoreObject (ObjectName,PluginName,RestoreObject,"
1227 "ObjectLength,ObjectFullLength,ObjectIndex,ObjectType,"
1228 "ObjectCompression,FileIndex,JobId) "
1229 "VALUES ('%s','%s','%s',%d,%d,%d,%d,%d,%d,%u)",
1230 mdb->esc_name, esc_plug_name, mdb->esc_obj,
1231 ro->object_len, ro->object_full_len, ro->object_index,
1232 ro->FileType, ro->object_compression, ro->FileIndex, ro->JobId);
1234 ro->RestoreObjectId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("RestoreObject"));
1235 if (ro->RestoreObjectId == 0) {
1236 Mmsg2(&mdb->errmsg, _("Create db Object record %s failed. ERR=%s"),
1237 mdb->cmd, sql_strerror(mdb));
1238 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1244 free_pool_memory(esc_plug_name);
1248 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */