2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2010 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version two of the GNU General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
29 * Bacula Catalog Database Create record interface routines
31 * Kern Sibbald, March 2000
35 /* The following is necessary so that we do not include
36 * the dummy external definition of DB.
38 #define __SQL_C /* indicate that this is sql.c */
43 static const int dbglevel = 100;
45 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI
47 /* -----------------------------------------------------------------------
49 * Generic Routines (or almost generic)
51 * -----------------------------------------------------------------------
54 /* Forward referenced subroutines */
55 #ifndef HAVE_BATCH_FILE_INSERT
56 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar);
57 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar);
58 #endif /* HAVE_BATCH_FILE_INSERT */
61 /* Create a new record for the Job
62 * Returns: false on failure
66 db_create_job_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
69 char dt[MAX_TIME_LENGTH];
79 stime = jr->SchedTime;
82 (void)localtime_r(&stime, &tm);
83 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
84 JobTDate = (utime_t)stime;
86 len = strlen(jcr->comment);
87 buf.check_size(len*2+1);
88 db_escape_string(jcr, mdb, buf.c_str(), jcr->comment, len);
92 "INSERT INTO Job (Job,Name,Type,Level,JobStatus,SchedTime,JobTDate,"
94 "VALUES ('%s','%s','%c','%c','%c','%s',%s,%s,'%s')",
95 jr->Job, jr->Name, (char)(jr->JobType), (char)(jr->JobLevel),
96 (char)(jr->JobStatus), dt, edit_uint64(JobTDate, ed1),
97 edit_int64(jr->ClientId, ed2), buf.c_str());
99 jr->JobId = sql_insert_id(mdb, mdb->cmd, NT_("Job"));
100 if (jr->JobId == 0) {
101 Mmsg2(&mdb->errmsg, _("Create DB Job record %s failed. ERR=%s\n"),
102 mdb->cmd, sql_strerror(mdb));
112 /* Create a JobMedia record for medium used this job
113 * Returns: false on failure
117 db_create_jobmedia_record(JCR *jcr, B_DB *mdb, JOBMEDIA_DBR *jm)
121 char ed1[50], ed2[50];
125 /* Now get count for VolIndex */
126 Mmsg(mdb->cmd, "SELECT count(*) from JobMedia WHERE JobId=%s",
127 edit_int64(jm->JobId, ed1));
128 count = get_sql_record_max(jcr, mdb);
134 /* Note, jm->Strip is not used and is not likely to be used
135 * in the near future, so I have removed it from the insert
136 * to save space in the DB. KES June 2006.
139 "INSERT INTO JobMedia (JobId,MediaId,FirstIndex,LastIndex,"
140 "StartFile,EndFile,StartBlock,EndBlock,VolIndex) "
141 "VALUES (%s,%s,%u,%u,%u,%u,%u,%u,%u)",
142 edit_int64(jm->JobId, ed1),
143 edit_int64(jm->MediaId, ed2),
144 jm->FirstIndex, jm->LastIndex,
145 jm->StartFile, jm->EndFile, jm->StartBlock, jm->EndBlock,count);
147 Dmsg0(300, mdb->cmd);
148 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
149 Mmsg2(&mdb->errmsg, _("Create JobMedia record %s failed: ERR=%s\n"), mdb->cmd,
153 /* Worked, now update the Media record with the EndFile and EndBlock */
155 "UPDATE Media SET EndFile=%u, EndBlock=%u WHERE MediaId=%u",
156 jm->EndFile, jm->EndBlock, jm->MediaId);
157 if (!UPDATE_DB(jcr, mdb, mdb->cmd)) {
158 Mmsg2(&mdb->errmsg, _("Update Media record %s failed: ERR=%s\n"), mdb->cmd,
164 Dmsg0(300, "Return from JobMedia\n");
168 /* Create Unique Pool record
169 * Returns: false on failure
173 db_create_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
176 char ed1[30], ed2[30], ed3[50], ed4[50], ed5[50];
178 Dmsg0(200, "In create pool\n");
180 Mmsg(mdb->cmd, "SELECT PoolId,Name FROM Pool WHERE Name='%s'", pr->Name);
181 Dmsg1(200, "selectpool: %s\n", mdb->cmd);
183 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
184 mdb->num_rows = sql_num_rows(mdb);
185 if (mdb->num_rows > 0) {
186 Mmsg1(&mdb->errmsg, _("pool record %s already exists\n"), pr->Name);
187 sql_free_result(mdb);
191 sql_free_result(mdb);
196 "INSERT INTO Pool (Name,NumVols,MaxVols,UseOnce,UseCatalog,"
197 "AcceptAnyVolume,AutoPrune,Recycle,VolRetention,VolUseDuration,"
198 "MaxVolJobs,MaxVolFiles,MaxVolBytes,PoolType,LabelType,LabelFormat,"
199 "RecyclePoolId,ScratchPoolId,ActionOnPurge) "
200 "VALUES ('%s',%u,%u,%d,%d,%d,%d,%d,%s,%s,%u,%u,%s,'%s',%d,'%s',%s,%s,%d)",
202 pr->NumVols, pr->MaxVols,
203 pr->UseOnce, pr->UseCatalog,
205 pr->AutoPrune, pr->Recycle,
206 edit_uint64(pr->VolRetention, ed1),
207 edit_uint64(pr->VolUseDuration, ed2),
208 pr->MaxVolJobs, pr->MaxVolFiles,
209 edit_uint64(pr->MaxVolBytes, ed3),
210 pr->PoolType, pr->LabelType, pr->LabelFormat,
211 edit_int64(pr->RecyclePoolId,ed4),
212 edit_int64(pr->ScratchPoolId,ed5),
215 Dmsg1(200, "Create Pool: %s\n", mdb->cmd);
216 pr->PoolId = sql_insert_id(mdb, mdb->cmd, NT_("Pool"));
217 if (pr->PoolId == 0) {
218 Mmsg2(&mdb->errmsg, _("Create db Pool record %s failed: ERR=%s\n"),
219 mdb->cmd, sql_strerror(mdb));
225 Dmsg0(500, "Create Pool: done\n");
230 * Create Unique Device record
231 * Returns: false on failure
235 db_create_device_record(JCR *jcr, B_DB *mdb, DEVICE_DBR *dr)
238 char ed1[30], ed2[30];
240 Dmsg0(200, "In create Device\n");
242 Mmsg(mdb->cmd, "SELECT DeviceId,Name FROM Device WHERE Name='%s'", dr->Name);
243 Dmsg1(200, "selectdevice: %s\n", mdb->cmd);
245 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
246 mdb->num_rows = sql_num_rows(mdb);
247 if (mdb->num_rows > 0) {
248 Mmsg1(&mdb->errmsg, _("Device record %s already exists\n"), dr->Name);
249 sql_free_result(mdb);
253 sql_free_result(mdb);
258 "INSERT INTO Device (Name,MediaTypeId,StorageId) VALUES ('%s',%s,%s)",
260 edit_uint64(dr->MediaTypeId, ed1),
261 edit_int64(dr->StorageId, ed2));
262 Dmsg1(200, "Create Device: %s\n", mdb->cmd);
263 dr->DeviceId = sql_insert_id(mdb, mdb->cmd, NT_("Device"));
264 if (dr->DeviceId == 0) {
265 Mmsg2(&mdb->errmsg, _("Create db Device record %s failed: ERR=%s\n"),
266 mdb->cmd, sql_strerror(mdb));
278 * Create a Unique record for Storage -- no duplicates
279 * Returns: false on failure
280 * true on success with id in sr->StorageId
282 bool db_create_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
288 Mmsg(mdb->cmd, "SELECT StorageId,AutoChanger FROM Storage WHERE Name='%s'", sr->Name);
292 /* Check if it already exists */
293 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
294 mdb->num_rows = sql_num_rows(mdb);
295 /* If more than one, report error, but return first row */
296 if (mdb->num_rows > 1) {
297 Mmsg1(&mdb->errmsg, _("More than one Storage record!: %d\n"), (int)(mdb->num_rows));
298 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
300 if (mdb->num_rows >= 1) {
301 if ((row = sql_fetch_row(mdb)) == NULL) {
302 Mmsg1(&mdb->errmsg, _("error fetching Storage row: %s\n"), sql_strerror(mdb));
303 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
304 sql_free_result(mdb);
308 sr->StorageId = str_to_int64(row[0]);
309 sr->AutoChanger = atoi(row[1]); /* bool */
310 sql_free_result(mdb);
314 sql_free_result(mdb);
318 Mmsg(mdb->cmd, "INSERT INTO Storage (Name,AutoChanger)"
319 " VALUES ('%s',%d)", sr->Name, sr->AutoChanger);
321 sr->StorageId = sql_insert_id(mdb, mdb->cmd, NT_("Storage"));
322 if (sr->StorageId == 0) {
323 Mmsg2(&mdb->errmsg, _("Create DB Storage record %s failed. ERR=%s\n"),
324 mdb->cmd, sql_strerror(mdb));
325 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
337 * Create Unique MediaType record
338 * Returns: false on failure
342 db_create_mediatype_record(JCR *jcr, B_DB *mdb, MEDIATYPE_DBR *mr)
346 Dmsg0(200, "In create mediatype\n");
348 Mmsg(mdb->cmd, "SELECT MediaTypeId,MediaType FROM MediaType WHERE MediaType='%s'", mr->MediaType);
349 Dmsg1(200, "selectmediatype: %s\n", mdb->cmd);
351 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
352 mdb->num_rows = sql_num_rows(mdb);
353 if (mdb->num_rows > 0) {
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_id(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];
397 Mmsg(mdb->cmd, "SELECT MediaId FROM Media WHERE VolumeName='%s'",
399 Dmsg1(500, "selectpool: %s\n", mdb->cmd);
401 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
402 mdb->num_rows = sql_num_rows(mdb);
403 if (mdb->num_rows > 0) {
404 Mmsg1(&mdb->errmsg, _("Volume \"%s\" already exists.\n"), mr->VolumeName);
405 sql_free_result(mdb);
409 sql_free_result(mdb);
414 "INSERT INTO Media (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,"
415 "VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
416 "VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolParts,"
417 "EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,"
418 "ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge)"
419 "VALUES ('%s','%s',0,%u,%s,%s,%d,%s,%s,%u,%u,'%s',%d,%s,%d,%s,%s,%d,0,0,%d,%s,"
420 "%s,%s,%s,%s,%d,%d)",
422 mr->MediaType, mr->PoolId,
423 edit_uint64(mr->MaxVolBytes,ed1),
424 edit_uint64(mr->VolCapacityBytes, ed2),
426 edit_uint64(mr->VolRetention, ed3),
427 edit_uint64(mr->VolUseDuration, ed4),
432 edit_uint64(mr->VolBytes, ed5),
434 edit_int64(mr->VolReadTime, ed6),
435 edit_int64(mr->VolWriteTime, ed7),
438 edit_int64(mr->StorageId, ed8),
439 edit_int64(mr->DeviceId, ed9),
440 edit_int64(mr->LocationId, ed10),
441 edit_int64(mr->ScratchPoolId, ed11),
442 edit_int64(mr->RecyclePoolId, ed12),
443 mr->Enabled, mr->ActionOnPurge
447 Dmsg1(500, "Create Volume: %s\n", mdb->cmd);
448 mr->MediaId = sql_insert_id(mdb, mdb->cmd, NT_("Media"));
449 if (mr->MediaId == 0) {
450 Mmsg2(&mdb->errmsg, _("Create DB Media record %s failed. ERR=%s\n"),
451 mdb->cmd, sql_strerror(mdb));
455 if (mr->set_label_date) {
456 char dt[MAX_TIME_LENGTH];
457 if (mr->LabelDate == 0) {
458 mr->LabelDate = time(NULL);
460 (void)localtime_r(&mr->LabelDate, &tm);
461 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
462 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
463 "WHERE MediaId=%d", dt, mr->MediaId);
464 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
467 * Make sure that if InChanger is non-zero any other identical slot
468 * has InChanger zero.
470 db_make_inchanger_unique(jcr, mdb, mr);
478 * Create a Unique record for the client -- no duplicates
479 * Returns: 0 on failure
480 * 1 on success with id in cr->ClientId
482 int db_create_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
486 char ed1[50], ed2[50];
489 Mmsg(mdb->cmd, "SELECT ClientId,Uname FROM Client WHERE Name='%s'", cr->Name);
492 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
493 mdb->num_rows = sql_num_rows(mdb);
494 /* If more than one, report error, but return first row */
495 if (mdb->num_rows > 1) {
496 Mmsg1(&mdb->errmsg, _("More than one Client!: %d\n"), (int)(mdb->num_rows));
497 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
499 if (mdb->num_rows >= 1) {
500 if ((row = sql_fetch_row(mdb)) == NULL) {
501 Mmsg1(&mdb->errmsg, _("error fetching Client row: %s\n"), sql_strerror(mdb));
502 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
503 sql_free_result(mdb);
507 cr->ClientId = str_to_int64(row[0]);
509 bstrncpy(cr->Uname, row[1], sizeof(cr->Uname));
511 cr->Uname[0] = 0; /* no name */
513 sql_free_result(mdb);
517 sql_free_result(mdb);
521 Mmsg(mdb->cmd, "INSERT INTO Client (Name,Uname,AutoPrune,"
522 "FileRetention,JobRetention) VALUES "
523 "('%s','%s',%d,%s,%s)", cr->Name, cr->Uname, cr->AutoPrune,
524 edit_uint64(cr->FileRetention, ed1),
525 edit_uint64(cr->JobRetention, ed2));
527 cr->ClientId = sql_insert_id(mdb, mdb->cmd, NT_("Client"));
528 if (cr->ClientId == 0) {
529 Mmsg2(&mdb->errmsg, _("Create DB Client record %s failed. ERR=%s\n"),
530 mdb->cmd, sql_strerror(mdb));
531 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
541 /* Create a Unique record for the Path -- no duplicates */
542 int db_create_path_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
547 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
548 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
550 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
551 strcmp(mdb->cached_path, mdb->path) == 0) {
552 ar->PathId = mdb->cached_path_id;
556 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
558 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
559 mdb->num_rows = sql_num_rows(mdb);
560 if (mdb->num_rows > 1) {
562 Mmsg2(&mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
563 edit_uint64(mdb->num_rows, ed1), mdb->path);
564 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
566 /* Even if there are multiple paths, take the first one */
567 if (mdb->num_rows >= 1) {
568 if ((row = sql_fetch_row(mdb)) == NULL) {
569 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
570 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
571 sql_free_result(mdb);
576 ar->PathId = str_to_int64(row[0]);
577 sql_free_result(mdb);
579 if (ar->PathId != mdb->cached_path_id) {
580 mdb->cached_path_id = ar->PathId;
581 mdb->cached_path_len = mdb->pnl;
582 pm_strcpy(mdb->cached_path, mdb->path);
587 sql_free_result(mdb);
590 Mmsg(mdb->cmd, "INSERT INTO Path (Path) VALUES ('%s')", mdb->esc_name);
592 ar->PathId = sql_insert_id(mdb, mdb->cmd, NT_("Path"));
593 if (ar->PathId == 0) {
594 Mmsg2(&mdb->errmsg, _("Create db Path record %s failed. ERR=%s\n"),
595 mdb->cmd, sql_strerror(mdb));
596 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
604 if (stat && ar->PathId != mdb->cached_path_id) {
605 mdb->cached_path_id = ar->PathId;
606 mdb->cached_path_len = mdb->pnl;
607 pm_strcpy(mdb->cached_path, mdb->path);
613 * Create a Unique record for the counter -- no duplicates
614 * Returns: 0 on failure
615 * 1 on success with counter filled in
617 int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
623 memset(&mcr, 0, sizeof(mcr));
624 bstrncpy(mcr.Counter, cr->Counter, sizeof(mcr.Counter));
625 if (db_get_counter_record(jcr, mdb, &mcr)) {
626 memcpy(cr, &mcr, sizeof(COUNTER_DBR));
632 Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,MinValue,MaxValue,CurrentValue,"
633 "WrapCounter) VALUES ('%s','%d','%d','%d','%s')",
634 cr->Counter, cr->MinValue, cr->MaxValue, cr->CurrentValue,
637 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
638 Mmsg2(&mdb->errmsg, _("Create DB Counters record %s failed. ERR=%s\n"),
639 mdb->cmd, sql_strerror(mdb));
640 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
651 * Create a FileSet record. This record is unique in the
652 * name and the MD5 signature of the include/exclude sets.
653 * Returns: 0 on failure
654 * 1 on success with FileSetId in record
656 bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
663 fsr->created = false;
664 Mmsg(mdb->cmd, "SELECT FileSetId,CreateTime FROM FileSet WHERE "
665 "FileSet='%s' AND MD5='%s'", fsr->FileSet, fsr->MD5);
668 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
669 mdb->num_rows = sql_num_rows(mdb);
670 if (mdb->num_rows > 1) {
671 Mmsg1(&mdb->errmsg, _("More than one FileSet!: %d\n"), (int)(mdb->num_rows));
672 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
674 if (mdb->num_rows >= 1) {
675 if ((row = sql_fetch_row(mdb)) == NULL) {
676 Mmsg1(&mdb->errmsg, _("error fetching FileSet row: ERR=%s\n"), sql_strerror(mdb));
677 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
678 sql_free_result(mdb);
682 fsr->FileSetId = str_to_int64(row[0]);
683 if (row[1] == NULL) {
684 fsr->cCreateTime[0] = 0;
686 bstrncpy(fsr->cCreateTime, row[1], sizeof(fsr->cCreateTime));
688 sql_free_result(mdb);
692 sql_free_result(mdb);
695 if (fsr->CreateTime == 0 && fsr->cCreateTime[0] == 0) {
696 fsr->CreateTime = time(NULL);
698 (void)localtime_r(&fsr->CreateTime, &tm);
699 strftime(fsr->cCreateTime, sizeof(fsr->cCreateTime), "%Y-%m-%d %H:%M:%S", &tm);
702 Mmsg(mdb->cmd, "INSERT INTO FileSet (FileSet,MD5,CreateTime) "
703 "VALUES ('%s','%s','%s')", fsr->FileSet, fsr->MD5, fsr->cCreateTime);
705 fsr->FileSetId = sql_insert_id(mdb, mdb->cmd, NT_("FileSet"));
706 if (fsr->FileSetId == 0) {
707 Mmsg2(&mdb->errmsg, _("Create DB FileSet record %s failed. ERR=%s\n"),
708 mdb->cmd, sql_strerror(mdb));
709 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
724 * dev_t st_dev; * device *
725 * ino_t st_ino; * inode *
726 * mode_t st_mode; * protection *
727 * nlink_t st_nlink; * number of hard links *
728 * uid_t st_uid; * user ID of owner *
729 * gid_t st_gid; * group ID of owner *
730 * dev_t st_rdev; * device type (if inode device) *
731 * off_t st_size; * total size, in bytes *
732 * unsigned long st_blksize; * blocksize for filesystem I/O *
733 * unsigned long st_blocks; * number of blocks allocated *
734 * time_t st_atime; * time of last access *
735 * time_t st_mtime; * time of last modification *
736 * time_t st_ctime; * time of last inode change *
740 #ifdef HAVE_BATCH_FILE_INSERT
742 /* All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
743 * tables. This code can be activated by adding "#define HAVE_BATCH_FILE_INSERT 1"
747 * - bulk load a temp table
748 * - insert missing filenames into filename with a single query (lock filenames
749 * - table before that to avoid possible duplicate inserts with concurrent update)
750 * - insert missing paths into path with another single query
751 * - then insert the join between the temp, filename and path tables into file.
758 bool my_batch_start(JCR *jcr, B_DB *mdb)
763 ok = db_sql_query(mdb,
764 "CREATE TEMPORARY TABLE batch ("
770 "MD5 tinyblob)",NULL, NULL);
779 bool my_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
785 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
786 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
788 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
789 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
791 if (ar->Digest == NULL || ar->Digest[0] == 0) {
797 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
798 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
799 mdb->esc_name, ar->attr, digest);
801 return INSERT_DB(jcr, mdb, mdb->cmd);
804 /* set error to something to abort operation */
809 bool my_batch_end(JCR *jcr, B_DB *mdb, const char *error)
812 Dmsg0(50, "sql_batch_end started\n");
816 mdb->status = (dbi_error_flag)0;
828 bool db_write_batch_file_records(JCR *jcr)
830 int JobStatus = jcr->JobStatus;
832 if (!jcr->batch_started) { /* no files to backup ? */
833 Dmsg0(50,"db_create_file_record : no files\n");
836 if (job_canceled(jcr)) {
840 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
842 jcr->JobStatus = JS_AttrInserting;
843 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
844 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
847 if (job_canceled(jcr)) {
852 /* we have to lock tables */
853 if (!db_sql_query(jcr->db_batch, sql_batch_lock_path_query, NULL, NULL)) {
854 Jmsg1(jcr, M_FATAL, 0, "Lock Path table %s\n", jcr->db_batch->errmsg);
858 if (!db_sql_query(jcr->db_batch, sql_batch_fill_path_query, NULL, NULL)) {
859 Jmsg1(jcr, M_FATAL, 0, "Fill Path table %s\n",jcr->db_batch->errmsg);
860 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
864 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
865 Jmsg1(jcr, M_FATAL, 0, "Unlock Path table %s\n", jcr->db_batch->errmsg);
869 /* we have to lock tables */
870 if (!db_sql_query(jcr->db_batch,sql_batch_lock_filename_query,NULL, NULL)) {
871 Jmsg1(jcr, M_FATAL, 0, "Lock Filename table %s\n", jcr->db_batch->errmsg);
875 if (!db_sql_query(jcr->db_batch,sql_batch_fill_filename_query, NULL,NULL)) {
876 Jmsg1(jcr,M_FATAL,0,"Fill Filename table %s\n",jcr->db_batch->errmsg);
877 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
881 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
882 Jmsg1(jcr, M_FATAL, 0, "Unlock Filename table %s\n", jcr->db_batch->errmsg);
886 if (!db_sql_query(jcr->db_batch,
887 "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)"
888 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
889 "Filename.FilenameId,batch.LStat, batch.MD5 "
891 "JOIN Path ON (batch.Path = Path.Path) "
892 "JOIN Filename ON (batch.Name = Filename.Name)",
895 Jmsg1(jcr, M_FATAL, 0, "Fill File table %s\n", jcr->db_batch->errmsg);
899 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
901 jcr->JobStatus = JobStatus; /* reset entry status */
906 * Create File record in B_DB
908 * In order to reduce database size, we store the File attributes,
909 * the FileName, and the Path separately. In principle, there
910 * is a single FileName record and a single Path record, no matter
911 * how many times it occurs. This is this subroutine, we separate
912 * the file and the path and fill temporary tables with this three records.
914 * Note: all routines that call this expect to be able to call
915 * db_strerror(mdb) to get the error message, so the error message
916 * MUST be edited into mdb->errmsg before returning an error status.
918 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
920 ASSERT(ar->FileType != FT_BASE);
922 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
923 Dmsg0(dbglevel, "put_file_into_catalog\n");
925 /* Open the dedicated connexion */
926 if (!jcr->batch_started) {
927 if (!db_open_batch_connexion(jcr, mdb)) {
928 return false; /* error already printed */
930 if (!sql_batch_start(jcr, jcr->db_batch)) {
932 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
933 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
936 jcr->batch_started = true;
938 B_DB *bdb = jcr->db_batch;
940 split_path_and_file(jcr, bdb, ar->fname);
944 * if (bdb->changes > 100000) {
945 * db_write_batch_file_records(jcr);
947 * sql_batch_start(jcr, bdb);
951 return sql_batch_insert(jcr, bdb, ar);
954 #else /* ! HAVE_BATCH_FILE_INSERT */
957 * Create File record in B_DB
959 * In order to reduce database size, we store the File attributes,
960 * the FileName, and the Path separately. In principle, there
961 * is a single FileName record and a single Path record, no matter
962 * how many times it occurs. This is this subroutine, we separate
963 * the file and the path and create three database records.
965 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
968 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
969 Dmsg0(dbglevel, "put_file_into_catalog\n");
971 split_path_and_file(jcr, mdb, ar->fname);
973 if (!db_create_filename_record(jcr, mdb, ar)) {
976 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
979 if (!db_create_path_record(jcr, mdb, ar)) {
982 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
984 /* Now create master File record */
985 if (!db_create_file_record(jcr, mdb, ar)) {
988 Dmsg0(dbglevel, "db_create_file_record OK\n");
990 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
1001 * This is the master File entry containing the attributes.
1002 * The filename and path records have already been created.
1004 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1007 static const char *no_digest = "0";
1012 ASSERT(ar->FilenameId);
1014 if (ar->Digest == NULL || ar->Digest[0] == 0) {
1017 digest = ar->Digest;
1020 /* Must create it */
1022 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
1023 "LStat,MD5) VALUES (%u,%u,%u,%u,'%s','%s')",
1024 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
1027 ar->FileId = sql_insert_id(mdb, mdb->cmd, NT_("File"));
1028 if (ar->FileId == 0) {
1029 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
1030 mdb->cmd, sql_strerror(mdb));
1031 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1039 /* Create a Unique record for the filename -- no duplicates */
1040 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1044 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1045 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1047 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1049 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1050 mdb->num_rows = sql_num_rows(mdb);
1051 if (mdb->num_rows > 1) {
1053 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1054 edit_uint64(mdb->num_rows, ed1), mdb->fname);
1055 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1057 if (mdb->num_rows >= 1) {
1058 if ((row = sql_fetch_row(mdb)) == NULL) {
1059 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1060 mdb->fname, sql_strerror(mdb));
1061 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1064 ar->FilenameId = str_to_int64(row[0]);
1066 sql_free_result(mdb);
1067 return ar->FilenameId > 0;
1069 sql_free_result(mdb);
1072 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1074 ar->FilenameId = sql_insert_id(mdb, mdb->cmd, NT_("Filename"));
1075 if (ar->FilenameId == 0) {
1076 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1077 mdb->cmd, sql_strerror(mdb));
1078 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1080 return ar->FilenameId > 0;
1083 bool db_write_batch_file_records(JCR *jcr)
1088 #endif /* ! HAVE_BATCH_FILE_INSERT */
1091 /* List of SQL commands to create temp table and indicies */
1092 const char *create_temp_basefile[5] = {
1094 "CREATE TEMPORARY TABLE basefile%lld ("
1095 // "CREATE TABLE basefile%lld ("
1096 "Path BLOB NOT NULL,"
1097 "Name BLOB NOT NULL)",
1100 "CREATE TEMPORARY TABLE basefile%lld ("
1101 // "CREATE TABLE basefile%lld ("
1106 "CREATE TEMPORARY TABLE basefile%lld ("
1111 "CREATE TEMPORARY TABLE basefile%lld ("
1116 "DECLARE GLOBAL TEMPORARY TABLE basefile%lld ("
1117 "Path TEXT NOT NULL,"
1118 "Name TEXT NOT NULL)"
1119 "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
1123 * Create file attributes record, or base file attributes record
1125 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1130 * Make sure we have an acceptable attributes record.
1132 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1133 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1134 Jmsg(jcr, M_FATAL, 0, _("Attempt to put non-attributes into catalog. Stream=%d\n"));
1138 if (ar->FileType != FT_BASE) {
1139 ret = db_create_file_attributes_record(jcr, mdb, ar);
1141 } else if (jcr->HasBase) {
1142 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1145 Jmsg0(jcr, M_FATAL, 0, _("Can't Copy/Migrate job using BaseJob"));
1146 ret = true; /* in copy/migration what do we do ? */
1153 * Create Base File record in B_DB
1156 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1159 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1160 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1163 split_path_and_file(jcr, mdb, ar->fname);
1165 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1166 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1168 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1169 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1171 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1172 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1174 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1181 * Cleanup the base file temporary tables
1183 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1185 POOL_MEM buf(PM_MESSAGE);
1186 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1187 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1189 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1190 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1194 * Put all base file seen in the backup to the BaseFile table
1195 * and cleanup temporary tables
1197 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1205 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1206 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1207 "B.FileId, B.FileIndex "
1208 "FROM basefile%s AS A, new_basefile%s AS B "
1209 "WHERE A.Path = B.Path "
1210 "AND A.Name = B.Name "
1211 "ORDER BY B.FileId",
1212 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1213 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1214 jcr->nb_base_files_used = sql_affected_rows(mdb);
1215 db_cleanup_base_file(jcr, mdb);
1222 * Find the last "accurate" backup state with Base jobs
1223 * 1) Get all files with jobid in list (F subquery)
1224 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1225 * 3) Put the result in a temporary table for the end of job
1228 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1236 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1240 Mmsg(mdb->cmd, create_temp_basefile[db_type], (uint64_t) jcr->JobId);
1241 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1244 Mmsg(buf, select_recent_version[db_type], jobids, jobids);
1246 "CREATE TEMPORARY TABLE new_basefile%lld AS "
1247 //"CREATE TABLE new_basefile%lld AS "
1248 "SELECT Path.Path AS Path, Filename.Name AS Name, Temp.FileIndex AS FileIndex,"
1249 "Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId, "
1251 "FROM ( %s ) AS Temp "
1252 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1253 "JOIN Path ON (Path.PathId = Temp.PathId) "
1254 "WHERE Temp.FileIndex > 0",
1255 (uint64_t)jcr->JobId, buf.c_str());
1257 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1265 * Create Restore Object record in B_DB
1268 bool db_create_restore_object_record(JCR *jcr, B_DB *mdb, ROBJECT_DBR *ro)
1271 POOLMEM *esc_obj = get_pool_memory(PM_MESSAGE);
1274 Dmsg1(dbglevel, "Fname=%s\n", ro->fname);
1275 Dmsg0(dbglevel, "put_object_into_catalog\n");
1277 split_path_and_file(jcr, mdb, ro->fname);
1279 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1280 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1282 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1283 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1285 esc_obj = check_pool_memory_size(esc_obj, ro->object_len*2+1);
1286 db_escape_string(jcr, mdb, esc_obj, ro->object, ro->object_len);
1289 "INSERT INTO RestoreObject (Fname,Path,PluginName,RestoreObject"
1290 "ObjectIndex,ObjectType,FileIndex,JobId) VALUES"
1291 "('%s','%s','%s','%s',%d,%d,%d,%u)",
1292 ro->fname, ro->path, ro->plugin_name, esc_obj, ro->object_len,
1293 ro->ObjectIndex, FT_RESTORE_FIRST, ro->FileIndex, ro->JobId);
1295 ro->RestoreObjectId = sql_insert_id(mdb, mdb->cmd, NT_("RestoreObject"));
1296 if (ro->RestoreObjectId == 0) {
1297 Mmsg2(&mdb->errmsg, _("Create db Object record %s failed. ERR=%s"),
1298 mdb->cmd, sql_strerror(mdb));
1299 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1305 free_pool_memory(esc_obj);
1310 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */