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
33 * Version $Id: sql_create.c 8407 2009-01-28 10:47:21Z ricozz $
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_INGRES || 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)
70 char dt[MAX_TIME_LENGTH];
80 stime = jr->SchedTime;
83 (void)localtime_r(&stime, &tm);
84 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
85 JobTDate = (utime_t)stime;
87 len = strlen(jcr->comment);
88 buf.check_size(len*2+1);
89 db_escape_string(jcr, mdb, buf.c_str(), jcr->comment, len);
93 "INSERT INTO Job (Job,Name,Type,Level,JobStatus,SchedTime,JobTDate,"
95 "VALUES ('%s','%s','%c','%c','%c','%s',%s,%s,'%s')",
96 jr->Job, jr->Name, (char)(jr->JobType), (char)(jr->JobLevel),
97 (char)(jr->JobStatus), dt, edit_uint64(JobTDate, ed1),
98 edit_int64(jr->ClientId, ed2), buf.c_str());
100 jr->JobId = sql_insert_id(mdb, mdb->cmd, NT_("Job"));
101 if (jr->JobId == 0) {
102 Mmsg2(&mdb->errmsg, _("Create DB Job record %s failed. ERR=%s\n"),
103 mdb->cmd, sql_strerror(mdb));
113 /* Create a JobMedia record for medium used this job
114 * Returns: false on failure
118 db_create_jobmedia_record(JCR *jcr, B_DB *mdb, JOBMEDIA_DBR *jm)
122 char ed1[50], ed2[50];
126 /* Now get count for VolIndex */
127 Mmsg(mdb->cmd, "SELECT count(*) from JobMedia WHERE JobId=%s",
128 edit_int64(jm->JobId, ed1));
129 count = get_sql_record_max(jcr, mdb);
135 /* Note, jm->Strip is not used and is not likely to be used
136 * in the near future, so I have removed it from the insert
137 * to save space in the DB. KES June 2006.
140 "INSERT INTO JobMedia (JobId,MediaId,FirstIndex,LastIndex,"
141 "StartFile,EndFile,StartBlock,EndBlock,VolIndex) "
142 "VALUES (%s,%s,%u,%u,%u,%u,%u,%u,%u)",
143 edit_int64(jm->JobId, ed1),
144 edit_int64(jm->MediaId, ed2),
145 jm->FirstIndex, jm->LastIndex,
146 jm->StartFile, jm->EndFile, jm->StartBlock, jm->EndBlock,count);
148 Dmsg0(300, mdb->cmd);
149 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
150 Mmsg2(&mdb->errmsg, _("Create JobMedia record %s failed: ERR=%s\n"), mdb->cmd,
154 /* Worked, now update the Media record with the EndFile and EndBlock */
156 "UPDATE Media SET EndFile=%u, EndBlock=%u WHERE MediaId=%u",
157 jm->EndFile, jm->EndBlock, jm->MediaId);
158 if (!UPDATE_DB(jcr, mdb, mdb->cmd)) {
159 Mmsg2(&mdb->errmsg, _("Update Media record %s failed: ERR=%s\n"), mdb->cmd,
165 Dmsg0(300, "Return from JobMedia\n");
169 /* Create Unique Pool record
170 * Returns: false on failure
174 db_create_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
177 char ed1[30], ed2[30], ed3[50], ed4[50], ed5[50];
179 Dmsg0(200, "In create pool\n");
181 Mmsg(mdb->cmd, "SELECT PoolId,Name FROM Pool WHERE Name='%s'", pr->Name);
182 Dmsg1(200, "selectpool: %s\n", mdb->cmd);
184 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
185 mdb->num_rows = sql_num_rows(mdb);
186 if (mdb->num_rows > 0) {
187 Mmsg1(&mdb->errmsg, _("pool record %s already exists\n"), pr->Name);
188 sql_free_result(mdb);
192 sql_free_result(mdb);
197 "INSERT INTO Pool (Name,NumVols,MaxVols,UseOnce,UseCatalog,"
198 "AcceptAnyVolume,AutoPrune,Recycle,VolRetention,VolUseDuration,"
199 "MaxVolJobs,MaxVolFiles,MaxVolBytes,PoolType,LabelType,LabelFormat,"
200 "RecyclePoolId,ScratchPoolId,ActionOnPurge) "
201 "VALUES ('%s',%u,%u,%d,%d,%d,%d,%d,%s,%s,%u,%u,%s,'%s',%d,'%s',%s,%s,%d)",
203 pr->NumVols, pr->MaxVols,
204 pr->UseOnce, pr->UseCatalog,
206 pr->AutoPrune, pr->Recycle,
207 edit_uint64(pr->VolRetention, ed1),
208 edit_uint64(pr->VolUseDuration, ed2),
209 pr->MaxVolJobs, pr->MaxVolFiles,
210 edit_uint64(pr->MaxVolBytes, ed3),
211 pr->PoolType, pr->LabelType, pr->LabelFormat,
212 edit_int64(pr->RecyclePoolId,ed4),
213 edit_int64(pr->ScratchPoolId,ed5),
216 Dmsg1(200, "Create Pool: %s\n", mdb->cmd);
217 pr->PoolId = sql_insert_id(mdb, mdb->cmd, NT_("Pool"));
218 if (pr->PoolId == 0) {
219 Mmsg2(&mdb->errmsg, _("Create db Pool record %s failed: ERR=%s\n"),
220 mdb->cmd, sql_strerror(mdb));
226 Dmsg0(500, "Create Pool: done\n");
231 * Create Unique Device record
232 * Returns: false on failure
236 db_create_device_record(JCR *jcr, B_DB *mdb, DEVICE_DBR *dr)
239 char ed1[30], ed2[30];
241 Dmsg0(200, "In create Device\n");
243 Mmsg(mdb->cmd, "SELECT DeviceId,Name FROM Device WHERE Name='%s'", dr->Name);
244 Dmsg1(200, "selectdevice: %s\n", mdb->cmd);
246 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
247 mdb->num_rows = sql_num_rows(mdb);
248 if (mdb->num_rows > 0) {
249 Mmsg1(&mdb->errmsg, _("Device record %s already exists\n"), dr->Name);
250 sql_free_result(mdb);
254 sql_free_result(mdb);
259 "INSERT INTO Device (Name,MediaTypeId,StorageId) VALUES ('%s',%s,%s)",
261 edit_uint64(dr->MediaTypeId, ed1),
262 edit_int64(dr->StorageId, ed2));
263 Dmsg1(200, "Create Device: %s\n", mdb->cmd);
264 dr->DeviceId = sql_insert_id(mdb, mdb->cmd, NT_("Device"));
265 if (dr->DeviceId == 0) {
266 Mmsg2(&mdb->errmsg, _("Create db Device record %s failed: ERR=%s\n"),
267 mdb->cmd, sql_strerror(mdb));
279 * Create a Unique record for Storage -- no duplicates
280 * Returns: false on failure
281 * true on success with id in sr->StorageId
283 bool db_create_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
289 Mmsg(mdb->cmd, "SELECT StorageId,AutoChanger FROM Storage WHERE Name='%s'", sr->Name);
293 /* Check if it already exists */
294 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
295 mdb->num_rows = sql_num_rows(mdb);
296 /* If more than one, report error, but return first row */
297 if (mdb->num_rows > 1) {
298 Mmsg1(&mdb->errmsg, _("More than one Storage record!: %d\n"), (int)(mdb->num_rows));
299 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
301 if (mdb->num_rows >= 1) {
302 if ((row = sql_fetch_row(mdb)) == NULL) {
303 Mmsg1(&mdb->errmsg, _("error fetching Storage row: %s\n"), sql_strerror(mdb));
304 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
305 sql_free_result(mdb);
309 sr->StorageId = str_to_int64(row[0]);
310 sr->AutoChanger = atoi(row[1]); /* bool */
311 sql_free_result(mdb);
315 sql_free_result(mdb);
319 Mmsg(mdb->cmd, "INSERT INTO Storage (Name,AutoChanger)"
320 " VALUES ('%s',%d)", sr->Name, sr->AutoChanger);
322 sr->StorageId = sql_insert_id(mdb, mdb->cmd, NT_("Storage"));
323 if (sr->StorageId == 0) {
324 Mmsg2(&mdb->errmsg, _("Create DB Storage record %s failed. ERR=%s\n"),
325 mdb->cmd, sql_strerror(mdb));
326 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
338 * Create Unique MediaType record
339 * Returns: false on failure
343 db_create_mediatype_record(JCR *jcr, B_DB *mdb, MEDIATYPE_DBR *mr)
347 Dmsg0(200, "In create mediatype\n");
349 Mmsg(mdb->cmd, "SELECT MediaTypeId,MediaType FROM MediaType WHERE MediaType='%s'", mr->MediaType);
350 Dmsg1(200, "selectmediatype: %s\n", mdb->cmd);
352 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
353 mdb->num_rows = sql_num_rows(mdb);
354 if (mdb->num_rows > 0) {
355 Mmsg1(&mdb->errmsg, _("mediatype record %s already exists\n"), mr->MediaType);
356 sql_free_result(mdb);
360 sql_free_result(mdb);
365 "INSERT INTO MediaType (MediaType,ReadOnly) "
369 Dmsg1(200, "Create mediatype: %s\n", mdb->cmd);
370 mr->MediaTypeId = sql_insert_id(mdb, mdb->cmd, NT_("MediaType"));
371 if (mr->MediaTypeId == 0) {
372 Mmsg2(&mdb->errmsg, _("Create db mediatype record %s failed: ERR=%s\n"),
373 mdb->cmd, sql_strerror(mdb));
384 * Create Media record. VolumeName and non-zero Slot must be unique
386 * Returns: 0 on failure
390 db_create_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
393 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50], ed7[50], ed8[50];
394 char ed9[50], ed10[50], ed11[50], ed12[50];
398 Mmsg(mdb->cmd, "SELECT MediaId FROM Media WHERE VolumeName='%s'",
400 Dmsg1(500, "selectpool: %s\n", mdb->cmd);
402 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
403 mdb->num_rows = sql_num_rows(mdb);
404 if (mdb->num_rows > 0) {
405 Mmsg1(&mdb->errmsg, _("Volume \"%s\" already exists.\n"), mr->VolumeName);
406 sql_free_result(mdb);
410 sql_free_result(mdb);
415 "INSERT INTO Media (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,"
416 "VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
417 "VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolParts,"
418 "EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,"
419 "ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge)"
420 "VALUES ('%s','%s',0,%u,%s,%s,%d,%s,%s,%u,%u,'%s',%d,%s,%d,%s,%s,%d,0,0,%d,%s,"
421 "%s,%s,%s,%s,%d,%d)",
423 mr->MediaType, mr->PoolId,
424 edit_uint64(mr->MaxVolBytes,ed1),
425 edit_uint64(mr->VolCapacityBytes, ed2),
427 edit_uint64(mr->VolRetention, ed3),
428 edit_uint64(mr->VolUseDuration, ed4),
433 edit_uint64(mr->VolBytes, ed5),
435 edit_int64(mr->VolReadTime, ed6),
436 edit_int64(mr->VolWriteTime, ed7),
439 edit_int64(mr->StorageId, ed8),
440 edit_int64(mr->DeviceId, ed9),
441 edit_int64(mr->LocationId, ed10),
442 edit_int64(mr->ScratchPoolId, ed11),
443 edit_int64(mr->RecyclePoolId, ed12),
444 mr->Enabled, mr->ActionOnPurge
448 Dmsg1(500, "Create Volume: %s\n", mdb->cmd);
449 mr->MediaId = sql_insert_id(mdb, mdb->cmd), NT_("Media"));
450 if (mr->MediaId == 0) {
451 Mmsg2(&mdb->errmsg, _("Create DB Media record %s failed. ERR=%s\n"),
452 mdb->cmd, sql_strerror(mdb));
456 if (mr->set_label_date) {
457 char dt[MAX_TIME_LENGTH];
458 if (mr->LabelDate == 0) {
459 mr->LabelDate = time(NULL);
461 (void)localtime_r(&mr->LabelDate, &tm);
462 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
463 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
464 "WHERE MediaId=%d", dt, mr->MediaId);
465 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
468 * Make sure that if InChanger is non-zero any other identical slot
469 * has InChanger zero.
471 db_make_inchanger_unique(jcr, mdb, mr);
479 * Create a Unique record for the client -- no duplicates
480 * Returns: 0 on failure
481 * 1 on success with id in cr->ClientId
483 int db_create_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
487 char ed1[50], ed2[50];
490 Mmsg(mdb->cmd, "SELECT ClientId,Uname FROM Client WHERE Name='%s'", cr->Name);
493 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
494 mdb->num_rows = sql_num_rows(mdb);
495 /* If more than one, report error, but return first row */
496 if (mdb->num_rows > 1) {
497 Mmsg1(&mdb->errmsg, _("More than one Client!: %d\n"), (int)(mdb->num_rows));
498 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
500 if (mdb->num_rows >= 1) {
501 if ((row = sql_fetch_row(mdb)) == NULL) {
502 Mmsg1(&mdb->errmsg, _("error fetching Client row: %s\n"), sql_strerror(mdb));
503 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
504 sql_free_result(mdb);
508 cr->ClientId = str_to_int64(row[0]);
510 bstrncpy(cr->Uname, row[1], sizeof(cr->Uname));
512 cr->Uname[0] = 0; /* no name */
514 sql_free_result(mdb);
518 sql_free_result(mdb);
522 Mmsg(mdb->cmd, "INSERT INTO Client (Name,Uname,AutoPrune,"
523 "FileRetention,JobRetention) VALUES "
524 "('%s','%s',%d,%s,%s)", cr->Name, cr->Uname, cr->AutoPrune,
525 edit_uint64(cr->FileRetention, ed1),
526 edit_uint64(cr->JobRetention, ed2));
528 cr->ClientId = sql_insert_id(mdb, mdb->cmd, NT_("Client"));
529 if (cr->ClientId == 0) {
530 Mmsg2(&mdb->errmsg, _("Create DB Client record %s failed. ERR=%s\n"),
531 mdb->cmd, sql_strerror(mdb));
532 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
542 /* Create a Unique record for the Path -- no duplicates */
543 int db_create_path_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
548 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
549 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
551 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
552 strcmp(mdb->cached_path, mdb->path) == 0) {
553 ar->PathId = mdb->cached_path_id;
557 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
559 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
560 mdb->num_rows = sql_num_rows(mdb);
561 if (mdb->num_rows > 1) {
563 Mmsg2(&mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
564 edit_uint64(mdb->num_rows, ed1), mdb->path);
565 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
567 /* Even if there are multiple paths, take the first one */
568 if (mdb->num_rows >= 1) {
569 if ((row = sql_fetch_row(mdb)) == NULL) {
570 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
571 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
572 sql_free_result(mdb);
577 ar->PathId = str_to_int64(row[0]);
578 sql_free_result(mdb);
580 if (ar->PathId != mdb->cached_path_id) {
581 mdb->cached_path_id = ar->PathId;
582 mdb->cached_path_len = mdb->pnl;
583 pm_strcpy(mdb->cached_path, mdb->path);
588 sql_free_result(mdb);
591 Mmsg(mdb->cmd, "INSERT INTO Path (Path) VALUES ('%s')", mdb->esc_name);
593 ar->PathId = sql_insert_id(mdb, mdb->cmd, NT_("Path"));
594 if (ar->PathId == 0) {
595 Mmsg2(&mdb->errmsg, _("Create db Path record %s failed. ERR=%s\n"),
596 mdb->cmd, sql_strerror(mdb));
597 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
605 if (stat && ar->PathId != mdb->cached_path_id) {
606 mdb->cached_path_id = ar->PathId;
607 mdb->cached_path_len = mdb->pnl;
608 pm_strcpy(mdb->cached_path, mdb->path);
614 * Create a Unique record for the counter -- no duplicates
615 * Returns: 0 on failure
616 * 1 on success with counter filled in
618 int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
624 memset(&mcr, 0, sizeof(mcr));
625 bstrncpy(mcr.Counter, cr->Counter, sizeof(mcr.Counter));
626 if (db_get_counter_record(jcr, mdb, &mcr)) {
627 memcpy(cr, &mcr, sizeof(COUNTER_DBR));
633 Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,MinValue,MaxValue,CurrentValue,"
634 "WrapCounter) VALUES ('%s','%d','%d','%d','%s')",
635 cr->Counter, cr->MinValue, cr->MaxValue, cr->CurrentValue,
638 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
639 Mmsg2(&mdb->errmsg, _("Create DB Counters record %s failed. ERR=%s\n"),
640 mdb->cmd, sql_strerror(mdb));
641 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
652 * Create a FileSet record. This record is unique in the
653 * name and the MD5 signature of the include/exclude sets.
654 * Returns: 0 on failure
655 * 1 on success with FileSetId in record
657 bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
664 fsr->created = false;
665 Mmsg(mdb->cmd, "SELECT FileSetId,CreateTime FROM FileSet WHERE "
666 "FileSet='%s' AND MD5='%s'", fsr->FileSet, fsr->MD5);
669 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
670 mdb->num_rows = sql_num_rows(mdb);
671 if (mdb->num_rows > 1) {
672 Mmsg1(&mdb->errmsg, _("More than one FileSet!: %d\n"), (int)(mdb->num_rows));
673 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
675 if (mdb->num_rows >= 1) {
676 if ((row = sql_fetch_row(mdb)) == NULL) {
677 Mmsg1(&mdb->errmsg, _("error fetching FileSet row: ERR=%s\n"), sql_strerror(mdb));
678 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
679 sql_free_result(mdb);
683 fsr->FileSetId = str_to_int64(row[0]);
684 if (row[1] == NULL) {
685 fsr->cCreateTime[0] = 0;
687 bstrncpy(fsr->cCreateTime, row[1], sizeof(fsr->cCreateTime));
689 sql_free_result(mdb);
693 sql_free_result(mdb);
696 if (fsr->CreateTime == 0 && fsr->cCreateTime[0] == 0) {
697 fsr->CreateTime = time(NULL);
699 (void)localtime_r(&fsr->CreateTime, &tm);
700 strftime(fsr->cCreateTime, sizeof(fsr->cCreateTime), "%Y-%m-%d %H:%M:%S", &tm);
703 Mmsg(mdb->cmd, "INSERT INTO FileSet (FileSet,MD5,CreateTime) "
704 "VALUES ('%s','%s','%s')", fsr->FileSet, fsr->MD5, fsr->cCreateTime);
706 fsr->FileSetId = sql_insert_id(mdb, mdb->cmd, NT_("FileSet"));
707 if (fsr->FileSetId == 0) {
708 Mmsg2(&mdb->errmsg, _("Create DB FileSet record %s failed. ERR=%s\n"),
709 mdb->cmd, sql_strerror(mdb));
710 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
725 * dev_t st_dev; * device *
726 * ino_t st_ino; * inode *
727 * mode_t st_mode; * protection *
728 * nlink_t st_nlink; * number of hard links *
729 * uid_t st_uid; * user ID of owner *
730 * gid_t st_gid; * group ID of owner *
731 * dev_t st_rdev; * device type (if inode device) *
732 * off_t st_size; * total size, in bytes *
733 * unsigned long st_blksize; * blocksize for filesystem I/O *
734 * unsigned long st_blocks; * number of blocks allocated *
735 * time_t st_atime; * time of last access *
736 * time_t st_mtime; * time of last modification *
737 * time_t st_ctime; * time of last inode change *
741 #ifdef HAVE_BATCH_FILE_INSERT
743 /* All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
744 * tables. This code can be activated by adding "#define HAVE_BATCH_FILE_INSERT 1"
748 * - bulk load a temp table
749 * - insert missing filenames into filename with a single query (lock filenames
750 * - table before that to avoid possible duplicate inserts with concurrent update)
751 * - insert missing paths into path with another single query
752 * - then insert the join between the temp, filename and path tables into file.
759 bool my_batch_start(JCR *jcr, B_DB *mdb)
764 ok = db_sql_query(mdb,
765 "CREATE TEMPORARY TABLE batch ("
771 "MD5 tinyblob)",NULL, NULL);
780 bool my_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
786 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
787 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
789 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
790 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
792 if (ar->Digest == NULL || ar->Digest[0] == 0) {
798 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
799 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
800 mdb->esc_name, ar->attr, digest);
802 return INSERT_DB(jcr, mdb, mdb->cmd);
805 /* set error to something to abort operation */
810 bool my_batch_end(JCR *jcr, B_DB *mdb, const char *error)
813 Dmsg0(50, "sql_batch_end started\n");
817 mdb->status = (dbi_error_flag)0;
829 bool db_write_batch_file_records(JCR *jcr)
831 int JobStatus = jcr->JobStatus;
833 if (!jcr->batch_started) { /* no files to backup ? */
834 Dmsg0(50,"db_create_file_record : no files\n");
837 if (job_canceled(jcr)) {
841 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
843 jcr->JobStatus = JS_AttrInserting;
844 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
845 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
848 if (job_canceled(jcr)) {
853 /* we have to lock tables */
854 if (!db_sql_query(jcr->db_batch, sql_batch_lock_path_query, NULL, NULL)) {
855 Jmsg1(jcr, M_FATAL, 0, "Lock Path table %s\n", jcr->db_batch->errmsg);
859 if (!db_sql_query(jcr->db_batch, sql_batch_fill_path_query, NULL, NULL)) {
860 Jmsg1(jcr, M_FATAL, 0, "Fill Path table %s\n",jcr->db_batch->errmsg);
861 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
865 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
866 Jmsg1(jcr, M_FATAL, 0, "Unlock Path table %s\n", jcr->db_batch->errmsg);
870 /* we have to lock tables */
871 if (!db_sql_query(jcr->db_batch,sql_batch_lock_filename_query,NULL, NULL)) {
872 Jmsg1(jcr, M_FATAL, 0, "Lock Filename table %s\n", jcr->db_batch->errmsg);
876 if (!db_sql_query(jcr->db_batch,sql_batch_fill_filename_query, NULL,NULL)) {
877 Jmsg1(jcr,M_FATAL,0,"Fill Filename table %s\n",jcr->db_batch->errmsg);
878 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
882 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
883 Jmsg1(jcr, M_FATAL, 0, "Unlock Filename table %s\n", jcr->db_batch->errmsg);
887 if (!db_sql_query(jcr->db_batch,
888 "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)"
889 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
890 "Filename.FilenameId,batch.LStat, batch.MD5 "
892 "JOIN Path ON (batch.Path = Path.Path) "
893 "JOIN Filename ON (batch.Name = Filename.Name)",
896 Jmsg1(jcr, M_FATAL, 0, "Fill File table %s\n", jcr->db_batch->errmsg);
900 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
902 jcr->JobStatus = JobStatus; /* reset entry status */
907 * Create File record in B_DB
909 * In order to reduce database size, we store the File attributes,
910 * the FileName, and the Path separately. In principle, there
911 * is a single FileName record and a single Path record, no matter
912 * how many times it occurs. This is this subroutine, we separate
913 * the file and the path and fill temporary tables with this three records.
915 * Note: all routines that call this expect to be able to call
916 * db_strerror(mdb) to get the error message, so the error message
917 * MUST be edited into mdb->errmsg before returning an error status.
919 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
921 ASSERT(ar->FileType != FT_BASE);
923 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
924 Dmsg0(dbglevel, "put_file_into_catalog\n");
926 /* Open the dedicated connexion */
927 if (!jcr->batch_started) {
928 if (!db_open_batch_connexion(jcr, mdb)) {
929 return false; /* error already printed */
931 if (!sql_batch_start(jcr, jcr->db_batch)) {
933 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
934 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
937 jcr->batch_started = true;
939 B_DB *bdb = jcr->db_batch;
941 split_path_and_file(jcr, bdb, ar->fname);
945 * if (bdb->changes > 100000) {
946 * db_write_batch_file_records(jcr);
948 * sql_batch_start(jcr, bdb);
952 return sql_batch_insert(jcr, bdb, ar);
955 #else /* ! HAVE_BATCH_FILE_INSERT */
958 * Create File record in B_DB
960 * In order to reduce database size, we store the File attributes,
961 * the FileName, and the Path separately. In principle, there
962 * is a single FileName record and a single Path record, no matter
963 * how many times it occurs. This is this subroutine, we separate
964 * the file and the path and create three database records.
966 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
969 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
970 Dmsg0(dbglevel, "put_file_into_catalog\n");
972 split_path_and_file(jcr, mdb, ar->fname);
974 if (!db_create_filename_record(jcr, mdb, ar)) {
977 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
980 if (!db_create_path_record(jcr, mdb, ar)) {
983 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
985 /* Now create master File record */
986 if (!db_create_file_record(jcr, mdb, ar)) {
989 Dmsg0(dbglevel, "db_create_file_record OK\n");
991 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
1002 * This is the master File entry containing the attributes.
1003 * The filename and path records have already been created.
1005 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1008 static const char *no_digest = "0";
1013 ASSERT(ar->FilenameId);
1015 if (ar->Digest == NULL || ar->Digest[0] == 0) {
1018 digest = ar->Digest;
1021 /* Must create it */
1023 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
1024 "LStat,MD5) VALUES (%u,%u,%u,%u,'%s','%s')",
1025 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
1028 ar->FileId = sql_insert_id(mdb, mdb->cmd, NT_("File"));
1029 if (ar->FileId == 0) {
1030 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
1031 mdb->cmd, sql_strerror(mdb));
1032 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1040 /* Create a Unique record for the filename -- no duplicates */
1041 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1045 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1046 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1048 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1050 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1051 mdb->num_rows = sql_num_rows(mdb);
1052 if (mdb->num_rows > 1) {
1054 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1055 edit_uint64(mdb->num_rows, ed1), mdb->fname);
1056 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1058 if (mdb->num_rows >= 1) {
1059 if ((row = sql_fetch_row(mdb)) == NULL) {
1060 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1061 mdb->fname, sql_strerror(mdb));
1062 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1065 ar->FilenameId = str_to_int64(row[0]);
1067 sql_free_result(mdb);
1068 return ar->FilenameId > 0;
1070 sql_free_result(mdb);
1073 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1075 ar->FilenameId = sql_insert_id(mdb, mdb->cmd, NT_("Filename"));
1076 if (ar->FilenameId == 0) {
1077 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1078 mdb->cmd, sql_strerror(mdb));
1079 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
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[5] = {
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 ("
1117 "DECLARE GLOBAL TEMPORARY TABLE basefile%lld ("
1118 "Path TEXT NOT NULL,"
1119 "Name TEXT NOT NULL)"
1120 "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
1124 * Create file attributes record, or base file attributes record
1126 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1131 * Make sure we have an acceptable attributes record.
1133 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1134 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1135 Jmsg(jcr, M_FATAL, 0, _("Attempt to put non-attributes into catalog. Stream=%d\n"));
1139 if (ar->FileType != FT_BASE) {
1140 ret = db_create_file_attributes_record(jcr, mdb, ar);
1142 } else if (jcr->HasBase) {
1143 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1146 Jmsg0(jcr, M_FATAL, 0, _("Can't Copy/Migrate job using BaseJob"));
1147 ret = true; /* in copy/migration what do we do ? */
1154 * Create Base File record in B_DB
1157 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1160 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1161 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1164 split_path_and_file(jcr, mdb, ar->fname);
1166 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1167 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1169 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1170 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1172 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1173 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1175 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1182 * Cleanup the base file temporary tables
1184 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1186 POOL_MEM buf(PM_MESSAGE);
1187 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1188 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1190 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1191 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1195 * Put all base file seen in the backup to the BaseFile table
1196 * and cleanup temporary tables
1198 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1206 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1207 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1208 "B.FileId, B.FileIndex "
1209 "FROM basefile%s AS A, new_basefile%s AS B "
1210 "WHERE A.Path = B.Path "
1211 "AND A.Name = B.Name "
1212 "ORDER BY B.FileId",
1213 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1214 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1215 jcr->nb_base_files_used = sql_affected_rows(mdb);
1216 db_cleanup_base_file(jcr, mdb);
1223 * Find the last "accurate" backup state with Base jobs
1224 * 1) Get all files with jobid in list (F subquery)
1225 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1226 * 3) Put the result in a temporary table for the end of job
1229 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1237 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1241 Mmsg(mdb->cmd, create_temp_basefile[db_type], (uint64_t) jcr->JobId);
1242 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1245 Mmsg(buf, select_recent_version[db_type], jobids, jobids);
1247 "CREATE TEMPORARY TABLE new_basefile%lld AS "
1248 //"CREATE TABLE new_basefile%lld AS "
1249 "SELECT Path.Path AS Path, Filename.Name AS Name, Temp.FileIndex AS FileIndex,"
1250 "Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId, "
1252 "FROM ( %s ) AS Temp "
1253 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1254 "JOIN Path ON (Path.PathId = Temp.PathId) "
1255 "WHERE Temp.FileIndex > 0",
1256 (uint64_t)jcr->JobId, buf.c_str());
1258 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1264 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */