2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2008 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
36 /* The following is necessary so that we do not include
37 * the dummy external definition of DB.
39 #define __SQL_C /* indicate that this is sql.c */
44 static const int dbglevel = 100;
46 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI
48 /* -----------------------------------------------------------------------
50 * Generic Routines (or almost generic)
52 * -----------------------------------------------------------------------
55 /* Forward referenced subroutines */
56 #ifndef HAVE_BATCH_FILE_INSERT
57 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar);
58 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar);
59 #endif /* HAVE_BATCH_FILE_INSERT */
62 /* Create a new record for the Job
63 * Returns: false on failure
67 db_create_job_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
69 char dt[MAX_TIME_LENGTH];
78 stime = jr->SchedTime;
81 (void)localtime_r(&stime, &tm);
82 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
83 JobTDate = (utime_t)stime;
87 "INSERT INTO Job (Job,Name,Type,Level,JobStatus,SchedTime,JobTDate,ClientId) "
88 "VALUES ('%s','%s','%c','%c','%c','%s',%s,%s)",
89 jr->Job, jr->Name, (char)(jr->JobType), (char)(jr->JobLevel),
90 (char)(jr->JobStatus), dt, edit_uint64(JobTDate, ed1),
91 edit_int64(jr->ClientId, ed2));
93 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
94 Mmsg2(&mdb->errmsg, _("Create DB Job record %s failed. ERR=%s\n"),
95 mdb->cmd, sql_strerror(mdb));
99 jr->JobId = sql_insert_id(mdb, NT_("Job"));
107 /* Create a JobMedia record for medium used this job
108 * Returns: false on failure
112 db_create_jobmedia_record(JCR *jcr, B_DB *mdb, JOBMEDIA_DBR *jm)
116 char ed1[50], ed2[50];
120 /* Now get count for VolIndex */
121 Mmsg(mdb->cmd, "SELECT count(*) from JobMedia WHERE JobId=%s",
122 edit_int64(jm->JobId, ed1));
123 count = get_sql_record_max(jcr, mdb);
129 /* Note, jm->Strip is not used and is not likely to be used
130 * in the near future, so I have removed it from the insert
131 * to save space in the DB. KES June 2006.
134 "INSERT INTO JobMedia (JobId,MediaId,FirstIndex,LastIndex,"
135 "StartFile,EndFile,StartBlock,EndBlock,VolIndex) "
136 "VALUES (%s,%s,%u,%u,%u,%u,%u,%u,%u)",
137 edit_int64(jm->JobId, ed1),
138 edit_int64(jm->MediaId, ed2),
139 jm->FirstIndex, jm->LastIndex,
140 jm->StartFile, jm->EndFile, jm->StartBlock, jm->EndBlock,count);
142 Dmsg0(300, mdb->cmd);
143 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
144 Mmsg2(&mdb->errmsg, _("Create JobMedia record %s failed: ERR=%s\n"), mdb->cmd,
148 /* Worked, now update the Media record with the EndFile and EndBlock */
150 "UPDATE Media SET EndFile=%u, EndBlock=%u WHERE MediaId=%u",
151 jm->EndFile, jm->EndBlock, jm->MediaId);
152 if (!UPDATE_DB(jcr, mdb, mdb->cmd)) {
153 Mmsg2(&mdb->errmsg, _("Update Media record %s failed: ERR=%s\n"), mdb->cmd,
159 Dmsg0(300, "Return from JobMedia\n");
163 /* Create Unique Pool record
164 * Returns: false on failure
168 db_create_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
171 char ed1[30], ed2[30], ed3[50], ed4[50], ed5[50];
173 Dmsg0(200, "In create pool\n");
175 Mmsg(mdb->cmd, "SELECT PoolId,Name FROM Pool WHERE Name='%s'", pr->Name);
176 Dmsg1(200, "selectpool: %s\n", mdb->cmd);
178 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
179 mdb->num_rows = sql_num_rows(mdb);
180 if (mdb->num_rows > 0) {
181 Mmsg1(&mdb->errmsg, _("pool record %s already exists\n"), pr->Name);
182 sql_free_result(mdb);
186 sql_free_result(mdb);
191 "INSERT INTO Pool (Name,NumVols,MaxVols,UseOnce,UseCatalog,"
192 "AcceptAnyVolume,AutoPrune,Recycle,VolRetention,VolUseDuration,"
193 "MaxVolJobs,MaxVolFiles,MaxVolBytes,PoolType,LabelType,LabelFormat,"
194 "RecyclePoolId,ScratchPoolId,ActionOnPurge) "
195 "VALUES ('%s',%u,%u,%d,%d,%d,%d,%d,%s,%s,%u,%u,%s,'%s',%d,'%s',%s,%s,%d)",
197 pr->NumVols, pr->MaxVols,
198 pr->UseOnce, pr->UseCatalog,
200 pr->AutoPrune, pr->Recycle,
201 edit_uint64(pr->VolRetention, ed1),
202 edit_uint64(pr->VolUseDuration, ed2),
203 pr->MaxVolJobs, pr->MaxVolFiles,
204 edit_uint64(pr->MaxVolBytes, ed3),
205 pr->PoolType, pr->LabelType, pr->LabelFormat,
206 edit_int64(pr->RecyclePoolId,ed4),
207 edit_int64(pr->ScratchPoolId,ed5),
210 Dmsg1(200, "Create Pool: %s\n", mdb->cmd);
211 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
212 Mmsg2(&mdb->errmsg, _("Create db Pool record %s failed: ERR=%s\n"),
213 mdb->cmd, sql_strerror(mdb));
217 pr->PoolId = sql_insert_id(mdb, NT_("Pool"));
225 * Create Unique Device record
226 * Returns: false on failure
230 db_create_device_record(JCR *jcr, B_DB *mdb, DEVICE_DBR *dr)
233 char ed1[30], ed2[30];
235 Dmsg0(200, "In create Device\n");
237 Mmsg(mdb->cmd, "SELECT DeviceId,Name FROM Device WHERE Name='%s'", dr->Name);
238 Dmsg1(200, "selectdevice: %s\n", mdb->cmd);
240 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
241 mdb->num_rows = sql_num_rows(mdb);
242 if (mdb->num_rows > 0) {
243 Mmsg1(&mdb->errmsg, _("Device record %s already exists\n"), dr->Name);
244 sql_free_result(mdb);
248 sql_free_result(mdb);
253 "INSERT INTO Device (Name,MediaTypeId,StorageId) VALUES ('%s',%s,%s)",
255 edit_uint64(dr->MediaTypeId, ed1),
256 edit_int64(dr->StorageId, ed2));
257 Dmsg1(200, "Create Device: %s\n", mdb->cmd);
258 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
259 Mmsg2(&mdb->errmsg, _("Create db Device record %s failed: ERR=%s\n"),
260 mdb->cmd, sql_strerror(mdb));
264 dr->DeviceId = sql_insert_id(mdb, NT_("Device"));
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 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
289 mdb->num_rows = sql_num_rows(mdb);
290 /* If more than one, report error, but return first row */
291 if (mdb->num_rows > 1) {
292 Mmsg1(&mdb->errmsg, _("More than one Storage record!: %d\n"), (int)(mdb->num_rows));
293 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
295 if (mdb->num_rows >= 1) {
296 if ((row = sql_fetch_row(mdb)) == NULL) {
297 Mmsg1(&mdb->errmsg, _("error fetching Storage row: %s\n"), sql_strerror(mdb));
298 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
299 sql_free_result(mdb);
303 sr->StorageId = str_to_int64(row[0]);
304 sr->AutoChanger = atoi(row[1]); /* bool */
305 sql_free_result(mdb);
309 sql_free_result(mdb);
313 Mmsg(mdb->cmd, "INSERT INTO Storage (Name,AutoChanger)"
314 " VALUES ('%s',%d)", sr->Name, sr->AutoChanger);
316 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
317 Mmsg2(&mdb->errmsg, _("Create DB Storage record %s failed. ERR=%s\n"),
318 mdb->cmd, sql_strerror(mdb));
319 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
322 sr->StorageId = sql_insert_id(mdb, NT_("Storage"));
332 * Create Unique MediaType record
333 * Returns: false on failure
337 db_create_mediatype_record(JCR *jcr, B_DB *mdb, MEDIATYPE_DBR *mr)
341 Dmsg0(200, "In create mediatype\n");
343 Mmsg(mdb->cmd, "SELECT MediaTypeId,MediaType FROM MediaType WHERE MediaType='%s'", mr->MediaType);
344 Dmsg1(200, "selectmediatype: %s\n", mdb->cmd);
346 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
347 mdb->num_rows = sql_num_rows(mdb);
348 if (mdb->num_rows > 0) {
349 Mmsg1(&mdb->errmsg, _("mediatype record %s already exists\n"), mr->MediaType);
350 sql_free_result(mdb);
354 sql_free_result(mdb);
359 "INSERT INTO MediaType (MediaType,ReadOnly) "
363 Dmsg1(200, "Create mediatype: %s\n", mdb->cmd);
364 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
365 Mmsg2(&mdb->errmsg, _("Create db mediatype record %s failed: ERR=%s\n"),
366 mdb->cmd, sql_strerror(mdb));
370 mr->MediaTypeId = sql_insert_id(mdb, NT_("MediaType"));
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 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
445 Mmsg2(&mdb->errmsg, _("Create DB Media record %s failed. ERR=%s\n"),
446 mdb->cmd, sql_strerror(mdb));
449 mr->MediaId = sql_insert_id(mdb, NT_("Media"));
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 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
524 Mmsg2(&mdb->errmsg, _("Create DB Client record %s failed. ERR=%s\n"),
525 mdb->cmd, sql_strerror(mdb));
526 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
530 cr->ClientId = sql_insert_id(mdb, NT_("Client"));
538 /* Create a Unique record for the Path -- no duplicates */
539 int db_create_path_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
544 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
545 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
547 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
548 strcmp(mdb->cached_path, mdb->path) == 0) {
549 ar->PathId = mdb->cached_path_id;
553 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
555 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
556 mdb->num_rows = sql_num_rows(mdb);
557 if (mdb->num_rows > 1) {
559 Mmsg2(&mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
560 edit_uint64(mdb->num_rows, ed1), mdb->path);
561 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
563 /* Even if there are multiple paths, take the first one */
564 if (mdb->num_rows >= 1) {
565 if ((row = sql_fetch_row(mdb)) == NULL) {
566 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
567 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
568 sql_free_result(mdb);
573 ar->PathId = str_to_int64(row[0]);
574 sql_free_result(mdb);
576 if (ar->PathId != mdb->cached_path_id) {
577 mdb->cached_path_id = ar->PathId;
578 mdb->cached_path_len = mdb->pnl;
579 pm_strcpy(mdb->cached_path, mdb->path);
584 sql_free_result(mdb);
587 Mmsg(mdb->cmd, "INSERT INTO Path (Path) VALUES ('%s')", mdb->esc_name);
589 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
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);
596 ar->PathId = sql_insert_id(mdb, NT_("Path"));
601 if (stat && ar->PathId != mdb->cached_path_id) {
602 mdb->cached_path_id = ar->PathId;
603 mdb->cached_path_len = mdb->pnl;
604 pm_strcpy(mdb->cached_path, mdb->path);
610 * Create a Unique record for the counter -- no duplicates
611 * Returns: 0 on failure
612 * 1 on success with counter filled in
614 int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
620 memset(&mcr, 0, sizeof(mcr));
621 bstrncpy(mcr.Counter, cr->Counter, sizeof(mcr.Counter));
622 if (db_get_counter_record(jcr, mdb, &mcr)) {
623 memcpy(cr, &mcr, sizeof(COUNTER_DBR));
629 Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,MinValue,MaxValue,CurrentValue,"
630 "WrapCounter) VALUES ('%s','%d','%d','%d','%s')",
631 cr->Counter, cr->MinValue, cr->MaxValue, cr->CurrentValue,
634 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
635 Mmsg2(&mdb->errmsg, _("Create DB Counters record %s failed. ERR=%s\n"),
636 mdb->cmd, sql_strerror(mdb));
637 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
648 * Create a FileSet record. This record is unique in the
649 * name and the MD5 signature of the include/exclude sets.
650 * Returns: 0 on failure
651 * 1 on success with FileSetId in record
653 bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
660 fsr->created = false;
661 Mmsg(mdb->cmd, "SELECT FileSetId,CreateTime FROM FileSet WHERE "
662 "FileSet='%s' AND MD5='%s'", fsr->FileSet, fsr->MD5);
665 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
666 mdb->num_rows = sql_num_rows(mdb);
667 if (mdb->num_rows > 1) {
668 Mmsg1(&mdb->errmsg, _("More than one FileSet!: %d\n"), (int)(mdb->num_rows));
669 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
671 if (mdb->num_rows >= 1) {
672 if ((row = sql_fetch_row(mdb)) == NULL) {
673 Mmsg1(&mdb->errmsg, _("error fetching FileSet row: ERR=%s\n"), sql_strerror(mdb));
674 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
675 sql_free_result(mdb);
679 fsr->FileSetId = str_to_int64(row[0]);
680 if (row[1] == NULL) {
681 fsr->cCreateTime[0] = 0;
683 bstrncpy(fsr->cCreateTime, row[1], sizeof(fsr->cCreateTime));
685 sql_free_result(mdb);
689 sql_free_result(mdb);
692 if (fsr->CreateTime == 0 && fsr->cCreateTime[0] == 0) {
693 fsr->CreateTime = time(NULL);
695 (void)localtime_r(&fsr->CreateTime, &tm);
696 strftime(fsr->cCreateTime, sizeof(fsr->cCreateTime), "%Y-%m-%d %H:%M:%S", &tm);
699 Mmsg(mdb->cmd, "INSERT INTO FileSet (FileSet,MD5,CreateTime) "
700 "VALUES ('%s','%s','%s')", fsr->FileSet, fsr->MD5, fsr->cCreateTime);
702 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
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);
709 fsr->FileSetId = sql_insert_id(mdb, NT_("FileSet"));
722 * dev_t st_dev; * device *
723 * ino_t st_ino; * inode *
724 * mode_t st_mode; * protection *
725 * nlink_t st_nlink; * number of hard links *
726 * uid_t st_uid; * user ID of owner *
727 * gid_t st_gid; * group ID of owner *
728 * dev_t st_rdev; * device type (if inode device) *
729 * off_t st_size; * total size, in bytes *
730 * unsigned long st_blksize; * blocksize for filesystem I/O *
731 * unsigned long st_blocks; * number of blocks allocated *
732 * time_t st_atime; * time of last access *
733 * time_t st_mtime; * time of last modification *
734 * time_t st_ctime; * time of last inode change *
738 #ifdef HAVE_BATCH_FILE_INSERT
740 /* All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
741 * tables. This code can be activated by adding "#define HAVE_BATCH_FILE_INSERT 1"
745 * - bulk load a temp table
746 * - insert missing filenames into filename with a single query (lock filenames
747 * - table before that to avoid possible duplicate inserts with concurrent update)
748 * - insert missing paths into path with another single query
749 * - then insert the join between the temp, filename and path tables into file.
756 bool my_batch_start(JCR *jcr, B_DB *mdb)
761 ok = db_sql_query(mdb,
762 "CREATE TEMPORARY TABLE batch ("
768 "MD5 tinyblob)",NULL, NULL);
777 bool my_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
783 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
784 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
786 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
787 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
789 if (ar->Digest == NULL || ar->Digest[0] == 0) {
795 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
796 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
797 mdb->esc_name, ar->attr, digest);
799 return INSERT_DB(jcr, mdb, mdb->cmd);
802 /* set error to something to abort operation */
807 bool my_batch_end(JCR *jcr, B_DB *mdb, const char *error)
810 Dmsg0(50, "sql_batch_end started\n");
814 mdb->status = (dbi_error_flag)0;
826 bool db_write_batch_file_records(JCR *jcr)
828 int JobStatus = jcr->JobStatus;
830 if (!jcr->batch_started) { /* no files to backup ? */
831 Dmsg0(50,"db_create_file_record : no files\n");
834 if (job_canceled(jcr)) {
838 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
840 jcr->JobStatus = JS_AttrInserting;
841 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
842 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
845 if (job_canceled(jcr)) {
850 /* 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);
867 /* we have to lock tables */
868 if (!db_sql_query(jcr->db_batch,sql_batch_lock_filename_query,NULL, NULL)) {
869 Jmsg1(jcr, M_FATAL, 0, "Lock Filename table %s\n", jcr->db_batch->errmsg);
873 if (!db_sql_query(jcr->db_batch,sql_batch_fill_filename_query, NULL,NULL)) {
874 Jmsg1(jcr,M_FATAL,0,"Fill Filename table %s\n",jcr->db_batch->errmsg);
875 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
879 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
880 Jmsg1(jcr, M_FATAL, 0, "Unlock Filename table %s\n", jcr->db_batch->errmsg);
884 if (!db_sql_query(jcr->db_batch,
885 "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)"
886 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
887 "Filename.FilenameId,batch.LStat, batch.MD5 "
889 "JOIN Path ON (batch.Path = Path.Path) "
890 "JOIN Filename ON (batch.Name = Filename.Name)",
893 Jmsg1(jcr, M_FATAL, 0, "Fill File table %s\n", jcr->db_batch->errmsg);
897 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
899 jcr->JobStatus = JobStatus; /* reset entry status */
904 * Create File record in B_DB
906 * In order to reduce database size, we store the File attributes,
907 * the FileName, and the Path separately. In principle, there
908 * is a single FileName record and a single Path record, no matter
909 * how many times it occurs. This is this subroutine, we separate
910 * the file and the path and fill temporary tables with this three records.
912 * Note: all routines that call this expect to be able to call
913 * db_strerror(mdb) to get the error message, so the error message
914 * MUST be edited into mdb->errmsg before returning an error status.
916 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
918 ASSERT(ar->FileType != FT_BASE);
920 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
921 Dmsg0(dbglevel, "put_file_into_catalog\n");
923 /* Open the dedicated connexion */
924 if (!jcr->batch_started) {
925 if (!db_open_batch_connexion(jcr, mdb)) {
926 return false; /* error already printed */
928 if (!sql_batch_start(jcr, jcr->db_batch)) {
930 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
931 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
934 jcr->batch_started = true;
936 B_DB *bdb = jcr->db_batch;
938 split_path_and_file(jcr, bdb, ar->fname);
942 * if (bdb->changes > 100000) {
943 * db_write_batch_file_records(jcr);
945 * sql_batch_start(jcr, bdb);
949 return sql_batch_insert(jcr, bdb, ar);
952 #else /* ! HAVE_BATCH_FILE_INSERT */
955 * Create File record in B_DB
957 * In order to reduce database size, we store the File attributes,
958 * the FileName, and the Path separately. In principle, there
959 * is a single FileName record and a single Path record, no matter
960 * how many times it occurs. This is this subroutine, we separate
961 * the file and the path and create three database records.
963 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
966 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
967 Dmsg0(dbglevel, "put_file_into_catalog\n");
969 split_path_and_file(jcr, mdb, ar->fname);
971 if (!db_create_filename_record(jcr, mdb, ar)) {
974 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
977 if (!db_create_path_record(jcr, mdb, ar)) {
980 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
982 /* Now create master File record */
983 if (!db_create_file_record(jcr, mdb, ar)) {
986 Dmsg0(dbglevel, "db_create_file_record OK\n");
988 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
999 * This is the master File entry containing the attributes.
1000 * The filename and path records have already been created.
1002 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1005 static const char *no_digest = "0";
1010 ASSERT(ar->FilenameId);
1012 if (ar->Digest == NULL || ar->Digest[0] == 0) {
1015 digest = ar->Digest;
1018 /* Must create it */
1020 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
1021 "LStat,MD5) VALUES (%u,%u,%u,%u,'%s','%s')",
1022 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
1025 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
1026 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
1027 mdb->cmd, sql_strerror(mdb));
1028 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1032 ar->FileId = sql_insert_id(mdb, NT_("File"));
1038 /* Create a Unique record for the filename -- no duplicates */
1039 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1043 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1044 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1046 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1048 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1049 mdb->num_rows = sql_num_rows(mdb);
1050 if (mdb->num_rows > 1) {
1052 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1053 edit_uint64(mdb->num_rows, ed1), mdb->fname);
1054 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1056 if (mdb->num_rows >= 1) {
1057 if ((row = sql_fetch_row(mdb)) == NULL) {
1058 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1059 mdb->fname, sql_strerror(mdb));
1060 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1063 ar->FilenameId = str_to_int64(row[0]);
1065 sql_free_result(mdb);
1066 return ar->FilenameId > 0;
1068 sql_free_result(mdb);
1071 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1073 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
1074 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1075 mdb->cmd, sql_strerror(mdb));
1076 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1079 ar->FilenameId = sql_insert_id(mdb, NT_("Filename"));
1081 return ar->FilenameId > 0;
1084 bool db_write_batch_file_records(JCR *jcr)
1089 #endif /* ! HAVE_BATCH_FILE_INSERT */
1092 /* List of SQL commands to create temp table and indicies */
1093 const char *create_temp_basefile[4] = {
1095 "CREATE TEMPORARY TABLE basefile%lld ("
1096 // "CREATE TABLE basefile%lld ("
1097 "Path BLOB NOT NULL,"
1098 "Name BLOB NOT NULL)",
1101 "CREATE TEMPORARY TABLE basefile%lld ("
1102 // "CREATE TABLE basefile%lld ("
1107 "CREATE TEMPORARY TABLE basefile%lld ("
1112 "CREATE TEMPORARY TABLE basefile%lld ("
1118 * Create file attributes record, or base file attributes record
1120 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1125 * Make sure we have an acceptable attributes record.
1127 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1128 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1129 Jmsg(jcr, M_FATAL, 0, _("Attempt to put non-attributes into catalog. Stream=%d\n"));
1133 if (ar->FileType != FT_BASE) {
1134 ret = db_create_file_attributes_record(jcr, mdb, ar);
1136 } else if (jcr->HasBase) {
1137 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1140 Jmsg0(jcr, M_FATAL, 0, _("Can't Copy/Migrate job using BaseJob"));
1141 ret = true; /* in copy/migration what do we do ? */
1148 * Create Base File record in B_DB
1151 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1154 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1155 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1158 split_path_and_file(jcr, mdb, ar->fname);
1160 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1161 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1163 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1164 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1166 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1167 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1169 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1176 * Cleanup the base file temporary tables
1178 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1180 POOL_MEM buf(PM_MESSAGE);
1181 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1182 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1184 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1185 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1189 * Put all base file seen in the backup to the BaseFile table
1190 * and cleanup temporary tables
1192 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1200 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1201 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1202 "B.FileId, B.FileIndex "
1203 "FROM basefile%s AS A, new_basefile%s AS B "
1204 "WHERE A.Path = B.Path "
1205 "AND A.Name = B.Name "
1206 "ORDER BY B.FileId",
1207 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1208 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1209 jcr->nb_base_files_used = sql_affected_rows(mdb);
1210 db_cleanup_base_file(jcr, mdb);
1217 * Find the last "accurate" backup state with Base jobs
1218 * 1) Get all files with jobid in list (F subquery)
1219 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1220 * 3) Put the result in a temporary table for the end of job
1223 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1231 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1235 Mmsg(mdb->cmd, create_temp_basefile[db_type], (uint64_t) jcr->JobId);
1236 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1239 Mmsg(buf, select_recent_version[db_type], jobids, jobids);
1241 "CREATE TEMPORARY TABLE new_basefile%lld AS "
1242 //"CREATE TABLE new_basefile%lld AS "
1243 "SELECT Path.Path AS Path, Filename.Name AS Name, Temp.FileIndex AS FileIndex,"
1244 "Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId, "
1246 "FROM ( %s ) AS Temp "
1247 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1248 "JOIN Path ON (Path.PathId = Temp.PathId) "
1249 "WHERE Temp.FileIndex > 0",
1250 (uint64_t)jcr->JobId, buf.c_str());
1252 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1258 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI */