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_autokey_record(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);
135 "INSERT INTO JobMedia (JobId,MediaId,FirstIndex,LastIndex,"
136 "StartFile,EndFile,StartBlock,EndBlock,VolIndex) "
137 "VALUES (%s,%s,%u,%u,%u,%u,%u,%u,%u)",
138 edit_int64(jm->JobId, ed1),
139 edit_int64(jm->MediaId, ed2),
140 jm->FirstIndex, jm->LastIndex,
141 jm->StartFile, jm->EndFile, jm->StartBlock, jm->EndBlock,count);
143 Dmsg0(300, mdb->cmd);
144 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
145 Mmsg2(&mdb->errmsg, _("Create JobMedia record %s failed: ERR=%s\n"), mdb->cmd,
149 /* Worked, now update the Media record with the EndFile and EndBlock */
151 "UPDATE Media SET EndFile=%u, EndBlock=%u WHERE MediaId=%u",
152 jm->EndFile, jm->EndBlock, jm->MediaId);
153 if (!UPDATE_DB(jcr, mdb, mdb->cmd)) {
154 Mmsg2(&mdb->errmsg, _("Update Media record %s failed: ERR=%s\n"), mdb->cmd,
160 Dmsg0(300, "Return from JobMedia\n");
164 /** Create Unique Pool record
165 * Returns: false on failure
169 db_create_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
172 char ed1[30], ed2[30], ed3[50], ed4[50], ed5[50];
174 Dmsg0(200, "In create pool\n");
176 Mmsg(mdb->cmd, "SELECT PoolId,Name FROM Pool WHERE Name='%s'", pr->Name);
177 Dmsg1(200, "selectpool: %s\n", mdb->cmd);
179 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
180 mdb->num_rows = sql_num_rows(mdb);
181 if (mdb->num_rows > 0) {
182 Mmsg1(&mdb->errmsg, _("pool record %s already exists\n"), pr->Name);
183 sql_free_result(mdb);
187 sql_free_result(mdb);
192 "INSERT INTO Pool (Name,NumVols,MaxVols,UseOnce,UseCatalog,"
193 "AcceptAnyVolume,AutoPrune,Recycle,VolRetention,VolUseDuration,"
194 "MaxVolJobs,MaxVolFiles,MaxVolBytes,PoolType,LabelType,LabelFormat,"
195 "RecyclePoolId,ScratchPoolId,ActionOnPurge) "
196 "VALUES ('%s',%u,%u,%d,%d,%d,%d,%d,%s,%s,%u,%u,%s,'%s',%d,'%s',%s,%s,%d)",
198 pr->NumVols, pr->MaxVols,
199 pr->UseOnce, pr->UseCatalog,
201 pr->AutoPrune, pr->Recycle,
202 edit_uint64(pr->VolRetention, ed1),
203 edit_uint64(pr->VolUseDuration, ed2),
204 pr->MaxVolJobs, pr->MaxVolFiles,
205 edit_uint64(pr->MaxVolBytes, ed3),
206 pr->PoolType, pr->LabelType, pr->LabelFormat,
207 edit_int64(pr->RecyclePoolId,ed4),
208 edit_int64(pr->ScratchPoolId,ed5),
211 Dmsg1(200, "Create Pool: %s\n", mdb->cmd);
212 pr->PoolId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Pool"));
213 if (pr->PoolId == 0) {
214 Mmsg2(&mdb->errmsg, _("Create db Pool record %s failed: ERR=%s\n"),
215 mdb->cmd, sql_strerror(mdb));
221 Dmsg0(500, "Create Pool: done\n");
226 * Create Unique Device record
227 * Returns: false on failure
231 db_create_device_record(JCR *jcr, B_DB *mdb, DEVICE_DBR *dr)
234 char ed1[30], ed2[30];
236 Dmsg0(200, "In create Device\n");
238 Mmsg(mdb->cmd, "SELECT DeviceId,Name FROM Device WHERE Name='%s'", dr->Name);
239 Dmsg1(200, "selectdevice: %s\n", mdb->cmd);
241 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
242 mdb->num_rows = sql_num_rows(mdb);
243 if (mdb->num_rows > 0) {
244 Mmsg1(&mdb->errmsg, _("Device record %s already exists\n"), dr->Name);
245 sql_free_result(mdb);
249 sql_free_result(mdb);
254 "INSERT INTO Device (Name,MediaTypeId,StorageId) VALUES ('%s',%s,%s)",
256 edit_uint64(dr->MediaTypeId, ed1),
257 edit_int64(dr->StorageId, ed2));
258 Dmsg1(200, "Create Device: %s\n", mdb->cmd);
259 dr->DeviceId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Device"));
260 if (dr->DeviceId == 0) {
261 Mmsg2(&mdb->errmsg, _("Create db Device record %s failed: ERR=%s\n"),
262 mdb->cmd, sql_strerror(mdb));
274 * Create a Unique record for Storage -- no duplicates
275 * Returns: false on failure
276 * true on success with id in sr->StorageId
278 bool db_create_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
284 Mmsg(mdb->cmd, "SELECT StorageId,AutoChanger FROM Storage WHERE Name='%s'", sr->Name);
288 /* Check if it already exists */
289 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
290 mdb->num_rows = sql_num_rows(mdb);
291 /* If more than one, report error, but return first row */
292 if (mdb->num_rows > 1) {
293 Mmsg1(&mdb->errmsg, _("More than one Storage record!: %d\n"), (int)(mdb->num_rows));
294 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
296 if (mdb->num_rows >= 1) {
297 if ((row = sql_fetch_row(mdb)) == NULL) {
298 Mmsg1(&mdb->errmsg, _("error fetching Storage row: %s\n"), sql_strerror(mdb));
299 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
300 sql_free_result(mdb);
304 sr->StorageId = str_to_int64(row[0]);
305 sr->AutoChanger = atoi(row[1]); /* bool */
306 sql_free_result(mdb);
310 sql_free_result(mdb);
314 Mmsg(mdb->cmd, "INSERT INTO Storage (Name,AutoChanger)"
315 " VALUES ('%s',%d)", sr->Name, sr->AutoChanger);
317 sr->StorageId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Storage"));
318 if (sr->StorageId == 0) {
319 Mmsg2(&mdb->errmsg, _("Create DB Storage record %s failed. ERR=%s\n"),
320 mdb->cmd, sql_strerror(mdb));
321 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
333 * Create Unique MediaType record
334 * Returns: false on failure
338 db_create_mediatype_record(JCR *jcr, B_DB *mdb, MEDIATYPE_DBR *mr)
342 Dmsg0(200, "In create mediatype\n");
344 Mmsg(mdb->cmd, "SELECT MediaTypeId,MediaType FROM MediaType WHERE MediaType='%s'", mr->MediaType);
345 Dmsg1(200, "selectmediatype: %s\n", mdb->cmd);
347 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
348 mdb->num_rows = sql_num_rows(mdb);
349 if (mdb->num_rows > 0) {
350 Mmsg1(&mdb->errmsg, _("mediatype record %s already exists\n"), mr->MediaType);
351 sql_free_result(mdb);
355 sql_free_result(mdb);
360 "INSERT INTO MediaType (MediaType,ReadOnly) "
364 Dmsg1(200, "Create mediatype: %s\n", mdb->cmd);
365 mr->MediaTypeId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("MediaType"));
366 if (mr->MediaTypeId == 0) {
367 Mmsg2(&mdb->errmsg, _("Create db mediatype record %s failed: ERR=%s\n"),
368 mdb->cmd, sql_strerror(mdb));
379 * Create Media record. VolumeName and non-zero Slot must be unique
381 * Returns: 0 on failure
385 db_create_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
388 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50], ed7[50], ed8[50];
389 char ed9[50], ed10[50], ed11[50], ed12[50];
393 Mmsg(mdb->cmd, "SELECT MediaId FROM Media WHERE VolumeName='%s'",
395 Dmsg1(500, "selectpool: %s\n", mdb->cmd);
397 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
398 mdb->num_rows = sql_num_rows(mdb);
399 if (mdb->num_rows > 0) {
400 Mmsg1(&mdb->errmsg, _("Volume \"%s\" already exists.\n"), mr->VolumeName);
401 sql_free_result(mdb);
405 sql_free_result(mdb);
410 "INSERT INTO Media (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,"
411 "VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
412 "VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolParts,"
413 "EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,"
414 "ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge)"
415 "VALUES ('%s','%s',0,%u,%s,%s,%d,%s,%s,%u,%u,'%s',%d,%s,%d,%s,%s,%d,0,0,%d,%s,"
416 "%s,%s,%s,%s,%d,%d)",
418 mr->MediaType, mr->PoolId,
419 edit_uint64(mr->MaxVolBytes,ed1),
420 edit_uint64(mr->VolCapacityBytes, ed2),
422 edit_uint64(mr->VolRetention, ed3),
423 edit_uint64(mr->VolUseDuration, ed4),
428 edit_uint64(mr->VolBytes, ed5),
430 edit_int64(mr->VolReadTime, ed6),
431 edit_int64(mr->VolWriteTime, ed7),
434 edit_int64(mr->StorageId, ed8),
435 edit_int64(mr->DeviceId, ed9),
436 edit_int64(mr->LocationId, ed10),
437 edit_int64(mr->ScratchPoolId, ed11),
438 edit_int64(mr->RecyclePoolId, ed12),
439 mr->Enabled, mr->ActionOnPurge
443 Dmsg1(500, "Create Volume: %s\n", mdb->cmd);
444 mr->MediaId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Media"));
445 if (mr->MediaId == 0) {
446 Mmsg2(&mdb->errmsg, _("Create DB Media record %s failed. ERR=%s\n"),
447 mdb->cmd, sql_strerror(mdb));
451 if (mr->set_label_date) {
452 char dt[MAX_TIME_LENGTH];
453 if (mr->LabelDate == 0) {
454 mr->LabelDate = time(NULL);
456 (void)localtime_r(&mr->LabelDate, &tm);
457 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
458 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
459 "WHERE MediaId=%d", dt, mr->MediaId);
460 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
463 * Make sure that if InChanger is non-zero any other identical slot
464 * has InChanger zero.
466 db_make_inchanger_unique(jcr, mdb, mr);
474 * Create a Unique record for the client -- no duplicates
475 * Returns: 0 on failure
476 * 1 on success with id in cr->ClientId
478 int db_create_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
482 char ed1[50], ed2[50];
485 Mmsg(mdb->cmd, "SELECT ClientId,Uname FROM Client WHERE Name='%s'", cr->Name);
488 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
489 mdb->num_rows = sql_num_rows(mdb);
490 /* If more than one, report error, but return first row */
491 if (mdb->num_rows > 1) {
492 Mmsg1(&mdb->errmsg, _("More than one Client!: %d\n"), (int)(mdb->num_rows));
493 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
495 if (mdb->num_rows >= 1) {
496 if ((row = sql_fetch_row(mdb)) == NULL) {
497 Mmsg1(&mdb->errmsg, _("error fetching Client row: %s\n"), sql_strerror(mdb));
498 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
499 sql_free_result(mdb);
503 cr->ClientId = str_to_int64(row[0]);
505 bstrncpy(cr->Uname, row[1], sizeof(cr->Uname));
507 cr->Uname[0] = 0; /* no name */
509 sql_free_result(mdb);
513 sql_free_result(mdb);
517 Mmsg(mdb->cmd, "INSERT INTO Client (Name,Uname,AutoPrune,"
518 "FileRetention,JobRetention) VALUES "
519 "('%s','%s',%d,%s,%s)", cr->Name, cr->Uname, cr->AutoPrune,
520 edit_uint64(cr->FileRetention, ed1),
521 edit_uint64(cr->JobRetention, ed2));
523 cr->ClientId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Client"));
524 if (cr->ClientId == 0) {
525 Mmsg2(&mdb->errmsg, _("Create DB Client record %s failed. ERR=%s\n"),
526 mdb->cmd, sql_strerror(mdb));
527 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
537 /** Create a Unique record for the Path -- no duplicates */
538 int db_create_path_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
543 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
544 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
546 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
547 strcmp(mdb->cached_path, mdb->path) == 0) {
548 ar->PathId = mdb->cached_path_id;
552 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
554 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
555 mdb->num_rows = sql_num_rows(mdb);
556 if (mdb->num_rows > 1) {
558 Mmsg2(&mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
559 edit_uint64(mdb->num_rows, ed1), mdb->path);
560 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
562 /* Even if there are multiple paths, take the first one */
563 if (mdb->num_rows >= 1) {
564 if ((row = sql_fetch_row(mdb)) == NULL) {
565 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
566 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
567 sql_free_result(mdb);
572 ar->PathId = str_to_int64(row[0]);
573 sql_free_result(mdb);
575 if (ar->PathId != mdb->cached_path_id) {
576 mdb->cached_path_id = ar->PathId;
577 mdb->cached_path_len = mdb->pnl;
578 pm_strcpy(mdb->cached_path, mdb->path);
583 sql_free_result(mdb);
586 Mmsg(mdb->cmd, "INSERT INTO Path (Path) VALUES ('%s')", mdb->esc_name);
588 ar->PathId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Path"));
589 if (ar->PathId == 0) {
590 Mmsg2(&mdb->errmsg, _("Create db Path record %s failed. ERR=%s\n"),
591 mdb->cmd, sql_strerror(mdb));
592 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
600 if (stat && ar->PathId != mdb->cached_path_id) {
601 mdb->cached_path_id = ar->PathId;
602 mdb->cached_path_len = mdb->pnl;
603 pm_strcpy(mdb->cached_path, mdb->path);
609 * Create a Unique record for the counter -- no duplicates
610 * Returns: 0 on failure
611 * 1 on success with counter filled in
613 int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
619 memset(&mcr, 0, sizeof(mcr));
620 bstrncpy(mcr.Counter, cr->Counter, sizeof(mcr.Counter));
621 if (db_get_counter_record(jcr, mdb, &mcr)) {
622 memcpy(cr, &mcr, sizeof(COUNTER_DBR));
628 Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,MinValue,MaxValue,CurrentValue,"
629 "WrapCounter) VALUES ('%s','%d','%d','%d','%s')",
630 cr->Counter, cr->MinValue, cr->MaxValue, cr->CurrentValue,
633 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
634 Mmsg2(&mdb->errmsg, _("Create DB Counters record %s failed. ERR=%s\n"),
635 mdb->cmd, sql_strerror(mdb));
636 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
647 * Create a FileSet record. This record is unique in the
648 * name and the MD5 signature of the include/exclude sets.
649 * Returns: 0 on failure
650 * 1 on success with FileSetId in record
652 bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
659 fsr->created = false;
660 Mmsg(mdb->cmd, "SELECT FileSetId,CreateTime FROM FileSet WHERE "
661 "FileSet='%s' AND MD5='%s'", fsr->FileSet, fsr->MD5);
664 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
665 mdb->num_rows = sql_num_rows(mdb);
666 if (mdb->num_rows > 1) {
667 Mmsg1(&mdb->errmsg, _("More than one FileSet!: %d\n"), (int)(mdb->num_rows));
668 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
670 if (mdb->num_rows >= 1) {
671 if ((row = sql_fetch_row(mdb)) == NULL) {
672 Mmsg1(&mdb->errmsg, _("error fetching FileSet row: ERR=%s\n"), sql_strerror(mdb));
673 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
674 sql_free_result(mdb);
678 fsr->FileSetId = str_to_int64(row[0]);
679 if (row[1] == NULL) {
680 fsr->cCreateTime[0] = 0;
682 bstrncpy(fsr->cCreateTime, row[1], sizeof(fsr->cCreateTime));
684 sql_free_result(mdb);
688 sql_free_result(mdb);
691 if (fsr->CreateTime == 0 && fsr->cCreateTime[0] == 0) {
692 fsr->CreateTime = time(NULL);
694 (void)localtime_r(&fsr->CreateTime, &tm);
695 strftime(fsr->cCreateTime, sizeof(fsr->cCreateTime), "%Y-%m-%d %H:%M:%S", &tm);
698 Mmsg(mdb->cmd, "INSERT INTO FileSet (FileSet,MD5,CreateTime) "
699 "VALUES ('%s','%s','%s')", fsr->FileSet, fsr->MD5, fsr->cCreateTime);
701 fsr->FileSetId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("FileSet"));
702 if (fsr->FileSetId == 0) {
703 Mmsg2(&mdb->errmsg, _("Create DB FileSet record %s failed. ERR=%s\n"),
704 mdb->cmd, sql_strerror(mdb));
705 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
720 * dev_t st_dev; * device *
721 * ino_t st_ino; * inode *
722 * mode_t st_mode; * protection *
723 * nlink_t st_nlink; * number of hard links *
724 * uid_t st_uid; * user ID of owner *
725 * gid_t st_gid; * group ID of owner *
726 * dev_t st_rdev; * device type (if inode device) *
727 * off_t st_size; * total size, in bytes *
728 * unsigned long st_blksize; * blocksize for filesystem I/O *
729 * unsigned long st_blocks; * number of blocks allocated *
730 * time_t st_atime; * time of last access *
731 * time_t st_mtime; * time of last modification *
732 * time_t st_ctime; * time of last inode change *
736 #ifdef HAVE_BATCH_FILE_INSERT
738 /** All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
739 * tables. This code can be activated by adding "#define HAVE_BATCH_FILE_INSERT 1"
743 * - bulk load a temp table
744 * - insert missing filenames into filename with a single query (lock filenames
745 * - table before that to avoid possible duplicate inserts with concurrent update)
746 * - insert missing paths into path with another single query
747 * - then insert the join between the temp, filename and path tables into file.
754 bool my_batch_start(JCR *jcr, B_DB *mdb)
759 ok = db_sql_query(mdb,
760 "CREATE TEMPORARY TABLE batch ("
766 "MD5 tinyblob)",NULL, NULL);
775 bool my_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
781 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
782 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
784 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
785 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
787 if (ar->Digest == NULL || ar->Digest[0] == 0) {
793 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
794 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
795 mdb->esc_name, ar->attr, digest);
797 return INSERT_DB(jcr, mdb, mdb->cmd);
800 /* set error to something to abort operation */
805 bool my_batch_end(JCR *jcr, B_DB *mdb, const char *error)
808 Dmsg0(50, "sql_batch_end started\n");
812 mdb->status = (dbi_error_flag)0;
824 bool db_write_batch_file_records(JCR *jcr)
826 int JobStatus = jcr->JobStatus;
828 if (!jcr->batch_started) { /* no files to backup ? */
829 Dmsg0(50,"db_create_file_record : no files\n");
832 if (job_canceled(jcr)) {
836 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
838 jcr->JobStatus = JS_AttrInserting;
839 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
840 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
843 if (job_canceled(jcr)) {
848 /* we have to lock tables */
849 if (!db_sql_query(jcr->db_batch, sql_batch_lock_path_query, NULL, NULL)) {
850 Jmsg1(jcr, M_FATAL, 0, "Lock Path table %s\n", jcr->db_batch->errmsg);
854 if (!db_sql_query(jcr->db_batch, sql_batch_fill_path_query, NULL, NULL)) {
855 Jmsg1(jcr, M_FATAL, 0, "Fill Path table %s\n",jcr->db_batch->errmsg);
856 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
860 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
861 Jmsg1(jcr, M_FATAL, 0, "Unlock Path table %s\n", jcr->db_batch->errmsg);
865 /* we have to lock tables */
866 if (!db_sql_query(jcr->db_batch,sql_batch_lock_filename_query,NULL, NULL)) {
867 Jmsg1(jcr, M_FATAL, 0, "Lock Filename table %s\n", jcr->db_batch->errmsg);
871 if (!db_sql_query(jcr->db_batch,sql_batch_fill_filename_query, NULL,NULL)) {
872 Jmsg1(jcr,M_FATAL,0,"Fill Filename table %s\n",jcr->db_batch->errmsg);
873 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
877 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
878 Jmsg1(jcr, M_FATAL, 0, "Unlock Filename table %s\n", jcr->db_batch->errmsg);
882 if (!db_sql_query(jcr->db_batch,
883 "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)"
884 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
885 "Filename.FilenameId,batch.LStat, batch.MD5 "
887 "JOIN Path ON (batch.Path = Path.Path) "
888 "JOIN Filename ON (batch.Name = Filename.Name)",
891 Jmsg1(jcr, M_FATAL, 0, "Fill File table %s\n", jcr->db_batch->errmsg);
895 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
897 jcr->JobStatus = JobStatus; /* reset entry status */
902 * Create File record in B_DB
904 * In order to reduce database size, we store the File attributes,
905 * the FileName, and the Path separately. In principle, there
906 * is a single FileName record and a single Path record, no matter
907 * how many times it occurs. This is this subroutine, we separate
908 * the file and the path and fill temporary tables with this three records.
910 * Note: all routines that call this expect to be able to call
911 * db_strerror(mdb) to get the error message, so the error message
912 * MUST be edited into mdb->errmsg before returning an error status.
914 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
916 ASSERT(ar->FileType != FT_BASE);
918 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
919 Dmsg0(dbglevel, "put_file_into_catalog\n");
921 /* Open the dedicated connexion */
922 if (!jcr->batch_started) {
923 if (!db_open_batch_connexion(jcr, mdb)) {
924 return false; /* error already printed */
926 if (!sql_batch_start(jcr, jcr->db_batch)) {
928 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
929 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
932 jcr->batch_started = true;
934 B_DB *bdb = jcr->db_batch;
936 split_path_and_file(jcr, bdb, ar->fname);
940 * if (bdb->changes > 100000) {
941 * db_write_batch_file_records(jcr);
943 * sql_batch_start(jcr, bdb);
947 return sql_batch_insert(jcr, bdb, ar);
950 #else /* ! HAVE_BATCH_FILE_INSERT */
953 * Create File record in B_DB
955 * In order to reduce database size, we store the File attributes,
956 * the FileName, and the Path separately. In principle, there
957 * is a single FileName record and a single Path record, no matter
958 * how many times it occurs. This is this subroutine, we separate
959 * the file and the path and create three database records.
961 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
964 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
965 Dmsg0(dbglevel, "put_file_into_catalog\n");
967 split_path_and_file(jcr, mdb, ar->fname);
969 if (!db_create_filename_record(jcr, mdb, ar)) {
972 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
975 if (!db_create_path_record(jcr, mdb, ar)) {
978 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
980 /* Now create master File record */
981 if (!db_create_file_record(jcr, mdb, ar)) {
984 Dmsg0(dbglevel, "db_create_file_record OK\n");
986 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
997 * This is the master File entry containing the attributes.
998 * The filename and path records have already been created.
1000 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1003 static const char *no_digest = "0";
1008 ASSERT(ar->FilenameId);
1010 if (ar->Digest == NULL || ar->Digest[0] == 0) {
1013 digest = ar->Digest;
1016 /* Must create it */
1018 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
1019 "LStat,MD5) VALUES (%u,%u,%u,%u,'%s','%s')",
1020 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
1023 ar->FileId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("File"));
1024 if (ar->FileId == 0) {
1025 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
1026 mdb->cmd, sql_strerror(mdb));
1027 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1035 /** Create a Unique record for the filename -- no duplicates */
1036 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1040 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1041 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1043 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1045 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1046 mdb->num_rows = sql_num_rows(mdb);
1047 if (mdb->num_rows > 1) {
1049 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1050 edit_uint64(mdb->num_rows, ed1), mdb->fname);
1051 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1053 if (mdb->num_rows >= 1) {
1054 if ((row = sql_fetch_row(mdb)) == NULL) {
1055 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1056 mdb->fname, sql_strerror(mdb));
1057 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1060 ar->FilenameId = str_to_int64(row[0]);
1062 sql_free_result(mdb);
1063 return ar->FilenameId > 0;
1065 sql_free_result(mdb);
1068 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1070 ar->FilenameId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Filename"));
1071 if (ar->FilenameId == 0) {
1072 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1073 mdb->cmd, sql_strerror(mdb));
1074 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1076 return ar->FilenameId > 0;
1079 bool db_write_batch_file_records(JCR *jcr)
1084 #endif /* ! HAVE_BATCH_FILE_INSERT */
1087 /* List of SQL commands to create temp table and indicies */
1088 const char *create_temp_basefile[5] = {
1090 "CREATE TEMPORARY TABLE basefile%lld ("
1091 // "CREATE TABLE basefile%lld ("
1092 "Path BLOB NOT NULL,"
1093 "Name BLOB NOT NULL)",
1096 "CREATE TEMPORARY TABLE basefile%lld ("
1097 // "CREATE TABLE basefile%lld ("
1102 "CREATE TEMPORARY TABLE basefile%lld ("
1107 "CREATE TEMPORARY TABLE basefile%lld ("
1112 "DECLARE GLOBAL TEMPORARY TABLE basefile%lld ("
1113 "Path TEXT NOT NULL,"
1114 "Name TEXT NOT NULL)"
1115 "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
1119 * Create file attributes record, or base file attributes record
1121 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1126 * Make sure we have an acceptable attributes record.
1128 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1129 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1130 Jmsg(jcr, M_FATAL, 0, _("Attempt to put non-attributes into catalog. Stream=%d\n"));
1134 if (ar->FileType != FT_BASE) {
1135 ret = db_create_file_attributes_record(jcr, mdb, ar);
1137 } else if (jcr->HasBase) {
1138 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1141 Jmsg0(jcr, M_FATAL, 0, _("Can't Copy/Migrate job using BaseJob"));
1142 ret = true; /* in copy/migration what do we do ? */
1149 * Create Base File record in B_DB
1152 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1155 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1156 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1159 split_path_and_file(jcr, mdb, ar->fname);
1161 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1162 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1164 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1165 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1167 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1168 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1170 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1177 * Cleanup the base file temporary tables
1179 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1181 POOL_MEM buf(PM_MESSAGE);
1182 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1183 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1185 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1186 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1190 * Put all base file seen in the backup to the BaseFile table
1191 * and cleanup temporary tables
1193 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1201 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1202 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1203 "B.FileId, B.FileIndex "
1204 "FROM basefile%s AS A, new_basefile%s AS B "
1205 "WHERE A.Path = B.Path "
1206 "AND A.Name = B.Name "
1207 "ORDER BY B.FileId",
1208 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1209 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1210 jcr->nb_base_files_used = sql_affected_rows(mdb);
1211 db_cleanup_base_file(jcr, mdb);
1218 * Find the last "accurate" backup state with Base jobs
1219 * 1) Get all files with jobid in list (F subquery)
1220 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1221 * 3) Put the result in a temporary table for the end of job
1224 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1232 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1236 Mmsg(mdb->cmd, create_temp_basefile[db_type], (uint64_t) jcr->JobId);
1237 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1240 Mmsg(buf, select_recent_version[db_type], jobids, jobids);
1242 "CREATE TEMPORARY TABLE new_basefile%lld AS "
1243 //"CREATE TABLE new_basefile%lld AS "
1244 "SELECT Path.Path AS Path, Filename.Name AS Name, Temp.FileIndex AS FileIndex,"
1245 "Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId, "
1247 "FROM ( %s ) AS Temp "
1248 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1249 "JOIN Path ON (Path.PathId = Temp.PathId) "
1250 "WHERE Temp.FileIndex > 0",
1251 (uint64_t)jcr->JobId, buf.c_str());
1253 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1261 * Create Restore Object record in B_DB
1264 bool db_create_restore_object_record(JCR *jcr, B_DB *mdb, ROBJECT_DBR *ro)
1267 POOLMEM *esc_obj = get_pool_memory(PM_MESSAGE);
1270 Dmsg1(dbglevel, "Fname=%s\n", ro->fname);
1271 Dmsg0(dbglevel, "put_object_into_catalog\n");
1273 split_path_and_file(jcr, mdb, ro->full_fname);
1275 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1276 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1278 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1279 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1281 esc_obj = check_pool_memory_size(esc_obj, ro->object_len*2+1);
1282 db_escape_string(jcr, mdb, esc_obj, ro->object, ro->object_len);
1285 "INSERT INTO RestoreObject (Fname,Path,PluginName,RestoreObject,"
1286 "ObjectLength,ObjectIndex,ObjectType,FileIndex,JobId) VALUES"
1287 "('%s','%s','%s','%s',%d,%d,%d,%d,%u)",
1288 mdb->esc_name, mdb->esc_path, mdb->esc_path, esc_obj, ro->object_len,
1289 ro->ObjectIndex, FT_RESTORE_FIRST, ro->FileIndex, ro->JobId);
1291 ro->RestoreObjectId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("RestoreObject"));
1292 if (ro->RestoreObjectId == 0) {
1293 Mmsg2(&mdb->errmsg, _("Create db Object record %s failed. ERR=%s"),
1294 mdb->cmd, sql_strerror(mdb));
1295 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1301 free_pool_memory(esc_obj);
1306 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */