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)
827 int JobStatus = jcr->JobStatus;
829 if (!jcr->batch_started) { /* no files to backup ? */
830 Dmsg0(50,"db_create_file_record : no files\n");
833 if (job_canceled(jcr)) {
837 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
839 jcr->JobStatus = JS_AttrInserting;
840 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
841 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
844 if (job_canceled(jcr)) {
849 * We have to lock tables
851 if (!db_sql_query(jcr->db_batch, sql_batch_lock_path_query, NULL, NULL)) {
852 Jmsg1(jcr, M_FATAL, 0, "Lock Path table %s\n", jcr->db_batch->errmsg);
856 if (!db_sql_query(jcr->db_batch, sql_batch_fill_path_query, NULL, NULL)) {
857 Jmsg1(jcr, M_FATAL, 0, "Fill Path table %s\n",jcr->db_batch->errmsg);
858 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
862 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
863 Jmsg1(jcr, M_FATAL, 0, "Unlock Path table %s\n", jcr->db_batch->errmsg);
868 * 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 jcr->JobStatus = JobStatus; /* reset entry status */
903 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
909 * Create File record in B_DB
911 * In order to reduce database size, we store the File attributes,
912 * the FileName, and the Path separately. In principle, there
913 * is a single FileName record and a single Path record, no matter
914 * how many times it occurs. This is this subroutine, we separate
915 * the file and the path and fill temporary tables with this three records.
917 * Note: all routines that call this expect to be able to call
918 * db_strerror(mdb) to get the error message, so the error message
919 * MUST be edited into mdb->errmsg before returning an error status.
921 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
923 ASSERT(ar->FileType != FT_BASE);
925 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
926 Dmsg0(dbglevel, "put_file_into_catalog\n");
928 /* Open the dedicated connexion */
929 if (!jcr->batch_started) {
930 if (!db_open_batch_connexion(jcr, mdb)) {
931 return false; /* error already printed */
933 if (!sql_batch_start(jcr, jcr->db_batch)) {
935 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
936 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
939 jcr->batch_started = true;
941 B_DB *bdb = jcr->db_batch;
943 split_path_and_file(jcr, bdb, ar->fname);
947 * if (bdb->changes > 100000) {
948 * db_write_batch_file_records(jcr);
950 * sql_batch_start(jcr, bdb);
954 return sql_batch_insert(jcr, bdb, ar);
957 #else /* ! HAVE_BATCH_FILE_INSERT */
960 * Create File record in B_DB
962 * In order to reduce database size, we store the File attributes,
963 * the FileName, and the Path separately. In principle, there
964 * is a single FileName record and a single Path record, no matter
965 * how many times it occurs. This is this subroutine, we separate
966 * the file and the path and create three database records.
968 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
971 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
972 Dmsg0(dbglevel, "put_file_into_catalog\n");
974 split_path_and_file(jcr, mdb, ar->fname);
976 if (!db_create_filename_record(jcr, mdb, ar)) {
979 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
982 if (!db_create_path_record(jcr, mdb, ar)) {
985 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
987 /* Now create master File record */
988 if (!db_create_file_record(jcr, mdb, ar)) {
991 Dmsg0(dbglevel, "db_create_file_record OK\n");
993 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
1004 * This is the master File entry containing the attributes.
1005 * The filename and path records have already been created.
1007 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1010 static const char *no_digest = "0";
1015 ASSERT(ar->FilenameId);
1017 if (ar->Digest == NULL || ar->Digest[0] == 0) {
1020 digest = ar->Digest;
1023 /* Must create it */
1025 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
1026 "LStat,MD5) VALUES (%u,%u,%u,%u,'%s','%s')",
1027 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
1030 ar->FileId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("File"));
1031 if (ar->FileId == 0) {
1032 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
1033 mdb->cmd, sql_strerror(mdb));
1034 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1042 /** Create a Unique record for the filename -- no duplicates */
1043 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1047 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1048 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1050 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1052 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1053 mdb->num_rows = sql_num_rows(mdb);
1054 if (mdb->num_rows > 1) {
1056 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1057 edit_uint64(mdb->num_rows, ed1), mdb->fname);
1058 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1060 if (mdb->num_rows >= 1) {
1061 if ((row = sql_fetch_row(mdb)) == NULL) {
1062 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1063 mdb->fname, sql_strerror(mdb));
1064 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1067 ar->FilenameId = str_to_int64(row[0]);
1069 sql_free_result(mdb);
1070 return ar->FilenameId > 0;
1072 sql_free_result(mdb);
1075 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1077 ar->FilenameId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Filename"));
1078 if (ar->FilenameId == 0) {
1079 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1080 mdb->cmd, sql_strerror(mdb));
1081 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1083 return ar->FilenameId > 0;
1086 bool db_write_batch_file_records(JCR *jcr)
1091 #endif /* ! HAVE_BATCH_FILE_INSERT */
1094 * Create file attributes record, or base file attributes record
1096 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1101 * Make sure we have an acceptable attributes record.
1103 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1104 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1105 Jmsg(jcr, M_FATAL, 0, _("Attempt to put non-attributes into catalog. Stream=%d\n"));
1109 if (ar->FileType != FT_BASE) {
1110 ret = db_create_file_attributes_record(jcr, mdb, ar);
1112 } else if (jcr->HasBase) {
1113 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1116 Jmsg0(jcr, M_FATAL, 0, _("Can't Copy/Migrate job using BaseJob"));
1117 ret = true; /* in copy/migration what do we do ? */
1124 * Create Base File record in B_DB
1127 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1130 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1131 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1134 split_path_and_file(jcr, mdb, ar->fname);
1136 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1137 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1139 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1140 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1142 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1143 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1145 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1152 * Cleanup the base file temporary tables
1154 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1156 POOL_MEM buf(PM_MESSAGE);
1157 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1158 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1160 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1161 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1165 * Put all base file seen in the backup to the BaseFile table
1166 * and cleanup temporary tables
1168 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1176 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1177 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1178 "B.FileId, B.FileIndex "
1179 "FROM basefile%s AS A, new_basefile%s AS B "
1180 "WHERE A.Path = B.Path "
1181 "AND A.Name = B.Name "
1182 "ORDER BY B.FileId",
1183 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1184 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1185 jcr->nb_base_files_used = sql_affected_rows(mdb);
1186 db_cleanup_base_file(jcr, mdb);
1193 * Find the last "accurate" backup state with Base jobs
1194 * 1) Get all files with jobid in list (F subquery)
1195 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1196 * 3) Put the result in a temporary table for the end of job
1199 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1207 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1211 Mmsg(mdb->cmd, create_temp_basefile[db_type], (uint64_t) jcr->JobId);
1212 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1215 Mmsg(buf, select_recent_version[db_type], jobids, jobids);
1216 Mmsg(mdb->cmd, create_temp_new_basefile[db_type], (uint64_t)jcr->JobId, buf.c_str());
1218 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1225 * Create Restore Object record in B_DB
1228 bool db_create_restore_object_record(JCR *jcr, B_DB *mdb, ROBJECT_DBR *ro)
1232 POOLMEM *esc_plug_name = get_pool_memory(PM_MESSAGE);
1236 Dmsg1(dbglevel, "Oname=%s\n", ro->object_name);
1237 Dmsg0(dbglevel, "put_object_into_catalog\n");
1239 mdb->fnl = strlen(ro->object_name);
1240 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1241 db_escape_string(jcr, mdb, mdb->esc_name, ro->object_name, mdb->fnl);
1243 db_escape_object(jcr, mdb, ro->object, ro->object_len);
1245 plug_name_len = strlen(ro->plugin_name);
1246 esc_plug_name = check_pool_memory_size(esc_plug_name, plug_name_len*2+1);
1247 db_escape_string(jcr, mdb, esc_plug_name, ro->plugin_name, plug_name_len);
1250 "INSERT INTO RestoreObject (ObjectName,PluginName,RestoreObject,"
1251 "ObjectLength,ObjectFullLength,ObjectIndex,ObjectType,"
1252 "ObjectCompression,FileIndex,JobId) "
1253 "VALUES ('%s','%s','%s',%d,%d,%d,%d,%d,%d,%u)",
1254 mdb->esc_name, esc_plug_name, mdb->esc_obj,
1255 ro->object_len, ro->object_full_len, ro->object_index,
1256 FT_RESTORE_FIRST, ro->object_compression, ro->FileIndex, ro->JobId);
1258 ro->RestoreObjectId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("RestoreObject"));
1259 if (ro->RestoreObjectId == 0) {
1260 Mmsg2(&mdb->errmsg, _("Create db Object record %s failed. ERR=%s"),
1261 mdb->cmd, sql_strerror(mdb));
1262 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1268 free_pool_memory(esc_plug_name);
1273 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */