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)
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 /* 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 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
318 Mmsg2(&mdb->errmsg, _("Create DB Storage record %s failed. ERR=%s\n"),
319 mdb->cmd, sql_strerror(mdb));
320 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
323 sr->StorageId = sql_insert_id(mdb, NT_("Storage"));
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 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
366 Mmsg2(&mdb->errmsg, _("Create db mediatype record %s failed: ERR=%s\n"),
367 mdb->cmd, sql_strerror(mdb));
371 mr->MediaTypeId = sql_insert_id(mdb, NT_("MediaType"));
380 * Create Media record. VolumeName and non-zero Slot must be unique
382 * Returns: 0 on failure
386 db_create_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
389 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50], ed7[50], ed8[50];
390 char ed9[50], ed10[50], ed11[50], ed12[50];
394 Mmsg(mdb->cmd, "SELECT MediaId FROM Media WHERE VolumeName='%s'",
396 Dmsg1(500, "selectpool: %s\n", mdb->cmd);
398 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
399 mdb->num_rows = sql_num_rows(mdb);
400 if (mdb->num_rows > 0) {
401 Mmsg1(&mdb->errmsg, _("Volume \"%s\" already exists.\n"), mr->VolumeName);
402 sql_free_result(mdb);
406 sql_free_result(mdb);
411 "INSERT INTO Media (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,"
412 "VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
413 "VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolParts,"
414 "EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,"
415 "ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge)"
416 "VALUES ('%s','%s',0,%u,%s,%s,%d,%s,%s,%u,%u,'%s',%d,%s,%d,%s,%s,%d,0,0,%d,%s,"
417 "%s,%s,%s,%s,%d,%d)",
419 mr->MediaType, mr->PoolId,
420 edit_uint64(mr->MaxVolBytes,ed1),
421 edit_uint64(mr->VolCapacityBytes, ed2),
423 edit_uint64(mr->VolRetention, ed3),
424 edit_uint64(mr->VolUseDuration, ed4),
429 edit_uint64(mr->VolBytes, ed5),
431 edit_int64(mr->VolReadTime, ed6),
432 edit_int64(mr->VolWriteTime, ed7),
435 edit_int64(mr->StorageId, ed8),
436 edit_int64(mr->DeviceId, ed9),
437 edit_int64(mr->LocationId, ed10),
438 edit_int64(mr->ScratchPoolId, ed11),
439 edit_int64(mr->RecyclePoolId, ed12),
440 mr->Enabled, mr->ActionOnPurge
444 Dmsg1(500, "Create Volume: %s\n", mdb->cmd);
445 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
446 Mmsg2(&mdb->errmsg, _("Create DB Media record %s failed. ERR=%s\n"),
447 mdb->cmd, sql_strerror(mdb));
450 mr->MediaId = sql_insert_id(mdb, NT_("Media"));
452 if (mr->set_label_date) {
453 char dt[MAX_TIME_LENGTH];
454 if (mr->LabelDate == 0) {
455 mr->LabelDate = time(NULL);
457 (void)localtime_r(&mr->LabelDate, &tm);
458 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
459 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
460 "WHERE MediaId=%d", dt, mr->MediaId);
461 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
464 * Make sure that if InChanger is non-zero any other identical slot
465 * has InChanger zero.
467 db_make_inchanger_unique(jcr, mdb, mr);
475 * Create a Unique record for the client -- no duplicates
476 * Returns: 0 on failure
477 * 1 on success with id in cr->ClientId
479 int db_create_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
483 char ed1[50], ed2[50];
486 Mmsg(mdb->cmd, "SELECT ClientId,Uname FROM Client WHERE Name='%s'", cr->Name);
489 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
490 mdb->num_rows = sql_num_rows(mdb);
491 /* If more than one, report error, but return first row */
492 if (mdb->num_rows > 1) {
493 Mmsg1(&mdb->errmsg, _("More than one Client!: %d\n"), (int)(mdb->num_rows));
494 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
496 if (mdb->num_rows >= 1) {
497 if ((row = sql_fetch_row(mdb)) == NULL) {
498 Mmsg1(&mdb->errmsg, _("error fetching Client row: %s\n"), sql_strerror(mdb));
499 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
500 sql_free_result(mdb);
504 cr->ClientId = str_to_int64(row[0]);
506 bstrncpy(cr->Uname, row[1], sizeof(cr->Uname));
508 cr->Uname[0] = 0; /* no name */
510 sql_free_result(mdb);
514 sql_free_result(mdb);
518 Mmsg(mdb->cmd, "INSERT INTO Client (Name,Uname,AutoPrune,"
519 "FileRetention,JobRetention) VALUES "
520 "('%s','%s',%d,%s,%s)", cr->Name, cr->Uname, cr->AutoPrune,
521 edit_uint64(cr->FileRetention, ed1),
522 edit_uint64(cr->JobRetention, ed2));
524 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
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);
531 cr->ClientId = sql_insert_id(mdb, NT_("Client"));
539 /* Create a Unique record for the Path -- no duplicates */
540 int db_create_path_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
545 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
546 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
548 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
549 strcmp(mdb->cached_path, mdb->path) == 0) {
550 ar->PathId = mdb->cached_path_id;
554 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
556 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
557 mdb->num_rows = sql_num_rows(mdb);
558 if (mdb->num_rows > 1) {
560 Mmsg2(&mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
561 edit_uint64(mdb->num_rows, ed1), mdb->path);
562 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
564 /* Even if there are multiple paths, take the first one */
565 if (mdb->num_rows >= 1) {
566 if ((row = sql_fetch_row(mdb)) == NULL) {
567 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
568 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
569 sql_free_result(mdb);
574 ar->PathId = str_to_int64(row[0]);
575 sql_free_result(mdb);
577 if (ar->PathId != mdb->cached_path_id) {
578 mdb->cached_path_id = ar->PathId;
579 mdb->cached_path_len = mdb->pnl;
580 pm_strcpy(mdb->cached_path, mdb->path);
585 sql_free_result(mdb);
588 Mmsg(mdb->cmd, "INSERT INTO Path (Path) VALUES ('%s')", mdb->esc_name);
590 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
591 Mmsg2(&mdb->errmsg, _("Create db Path record %s failed. ERR=%s\n"),
592 mdb->cmd, sql_strerror(mdb));
593 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
597 ar->PathId = sql_insert_id(mdb, NT_("Path"));
602 if (stat && ar->PathId != mdb->cached_path_id) {
603 mdb->cached_path_id = ar->PathId;
604 mdb->cached_path_len = mdb->pnl;
605 pm_strcpy(mdb->cached_path, mdb->path);
611 * Create a Unique record for the counter -- no duplicates
612 * Returns: 0 on failure
613 * 1 on success with counter filled in
615 int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
621 memset(&mcr, 0, sizeof(mcr));
622 bstrncpy(mcr.Counter, cr->Counter, sizeof(mcr.Counter));
623 if (db_get_counter_record(jcr, mdb, &mcr)) {
624 memcpy(cr, &mcr, sizeof(COUNTER_DBR));
630 Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,MinValue,MaxValue,CurrentValue,"
631 "WrapCounter) VALUES ('%s','%d','%d','%d','%s')",
632 cr->Counter, cr->MinValue, cr->MaxValue, cr->CurrentValue,
635 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
636 Mmsg2(&mdb->errmsg, _("Create DB Counters record %s failed. ERR=%s\n"),
637 mdb->cmd, sql_strerror(mdb));
638 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
649 * Create a FileSet record. This record is unique in the
650 * name and the MD5 signature of the include/exclude sets.
651 * Returns: 0 on failure
652 * 1 on success with FileSetId in record
654 bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
661 fsr->created = false;
662 Mmsg(mdb->cmd, "SELECT FileSetId,CreateTime FROM FileSet WHERE "
663 "FileSet='%s' AND MD5='%s'", fsr->FileSet, fsr->MD5);
666 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
667 mdb->num_rows = sql_num_rows(mdb);
668 if (mdb->num_rows > 1) {
669 Mmsg1(&mdb->errmsg, _("More than one FileSet!: %d\n"), (int)(mdb->num_rows));
670 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
672 if (mdb->num_rows >= 1) {
673 if ((row = sql_fetch_row(mdb)) == NULL) {
674 Mmsg1(&mdb->errmsg, _("error fetching FileSet row: ERR=%s\n"), sql_strerror(mdb));
675 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
676 sql_free_result(mdb);
680 fsr->FileSetId = str_to_int64(row[0]);
681 if (row[1] == NULL) {
682 fsr->cCreateTime[0] = 0;
684 bstrncpy(fsr->cCreateTime, row[1], sizeof(fsr->cCreateTime));
686 sql_free_result(mdb);
690 sql_free_result(mdb);
693 if (fsr->CreateTime == 0 && fsr->cCreateTime[0] == 0) {
694 fsr->CreateTime = time(NULL);
696 (void)localtime_r(&fsr->CreateTime, &tm);
697 strftime(fsr->cCreateTime, sizeof(fsr->cCreateTime), "%Y-%m-%d %H:%M:%S", &tm);
700 Mmsg(mdb->cmd, "INSERT INTO FileSet (FileSet,MD5,CreateTime) "
701 "VALUES ('%s','%s','%s')", fsr->FileSet, fsr->MD5, fsr->cCreateTime);
703 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
704 Mmsg2(&mdb->errmsg, _("Create DB FileSet record %s failed. ERR=%s\n"),
705 mdb->cmd, sql_strerror(mdb));
706 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
710 fsr->FileSetId = sql_insert_id(mdb, NT_("FileSet"));
723 * dev_t st_dev; * device *
724 * ino_t st_ino; * inode *
725 * mode_t st_mode; * protection *
726 * nlink_t st_nlink; * number of hard links *
727 * uid_t st_uid; * user ID of owner *
728 * gid_t st_gid; * group ID of owner *
729 * dev_t st_rdev; * device type (if inode device) *
730 * off_t st_size; * total size, in bytes *
731 * unsigned long st_blksize; * blocksize for filesystem I/O *
732 * unsigned long st_blocks; * number of blocks allocated *
733 * time_t st_atime; * time of last access *
734 * time_t st_mtime; * time of last modification *
735 * time_t st_ctime; * time of last inode change *
739 #ifdef HAVE_BATCH_FILE_INSERT
741 /* All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
742 * tables. This code can be activated by adding "#define HAVE_BATCH_FILE_INSERT 1"
746 * - bulk load a temp table
747 * - insert missing filenames into filename with a single query (lock filenames
748 * - table before that to avoid possible duplicate inserts with concurrent update)
749 * - insert missing paths into path with another single query
750 * - then insert the join between the temp, filename and path tables into file.
757 bool my_batch_start(JCR *jcr, B_DB *mdb)
762 ok = db_sql_query(mdb,
763 "CREATE TEMPORARY TABLE batch ("
769 "MD5 tinyblob)",NULL, NULL);
778 bool my_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
784 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
785 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
787 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
788 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
790 if (ar->Digest == NULL || ar->Digest[0] == 0) {
796 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
797 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
798 mdb->esc_name, ar->attr, digest);
800 return INSERT_DB(jcr, mdb, mdb->cmd);
803 /* set error to something to abort operation */
808 bool my_batch_end(JCR *jcr, B_DB *mdb, const char *error)
811 Dmsg0(50, "sql_batch_end started\n");
815 mdb->status = (dbi_error_flag)0;
827 bool db_write_batch_file_records(JCR *jcr)
829 int JobStatus = jcr->JobStatus;
831 if (!jcr->batch_started) { /* no files to backup ? */
832 Dmsg0(50,"db_create_file_record : no files\n");
835 if (job_canceled(jcr)) {
839 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
841 jcr->JobStatus = JS_AttrInserting;
842 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
843 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
846 if (job_canceled(jcr)) {
851 /* we have to lock tables */
852 if (!db_sql_query(jcr->db_batch, sql_batch_lock_path_query, NULL, NULL)) {
853 Jmsg1(jcr, M_FATAL, 0, "Lock Path table %s\n", jcr->db_batch->errmsg);
857 if (!db_sql_query(jcr->db_batch, sql_batch_fill_path_query, NULL, NULL)) {
858 Jmsg1(jcr, M_FATAL, 0, "Fill Path table %s\n",jcr->db_batch->errmsg);
859 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
863 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
864 Jmsg1(jcr, M_FATAL, 0, "Unlock Path table %s\n", jcr->db_batch->errmsg);
868 /* we have to lock tables */
869 if (!db_sql_query(jcr->db_batch,sql_batch_lock_filename_query,NULL, NULL)) {
870 Jmsg1(jcr, M_FATAL, 0, "Lock Filename table %s\n", jcr->db_batch->errmsg);
874 if (!db_sql_query(jcr->db_batch,sql_batch_fill_filename_query, NULL,NULL)) {
875 Jmsg1(jcr,M_FATAL,0,"Fill Filename table %s\n",jcr->db_batch->errmsg);
876 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
880 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
881 Jmsg1(jcr, M_FATAL, 0, "Unlock Filename table %s\n", jcr->db_batch->errmsg);
885 if (!db_sql_query(jcr->db_batch,
886 "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)"
887 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
888 "Filename.FilenameId,batch.LStat, batch.MD5 "
890 "JOIN Path ON (batch.Path = Path.Path) "
891 "JOIN Filename ON (batch.Name = Filename.Name)",
894 Jmsg1(jcr, M_FATAL, 0, "Fill File table %s\n", jcr->db_batch->errmsg);
898 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
900 jcr->JobStatus = JobStatus; /* reset entry status */
905 * Create File record in B_DB
907 * In order to reduce database size, we store the File attributes,
908 * the FileName, and the Path separately. In principle, there
909 * is a single FileName record and a single Path record, no matter
910 * how many times it occurs. This is this subroutine, we separate
911 * the file and the path and fill temporary tables with this three records.
913 * Note: all routines that call this expect to be able to call
914 * db_strerror(mdb) to get the error message, so the error message
915 * MUST be edited into mdb->errmsg before returning an error status.
917 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
919 ASSERT(ar->FileType != FT_BASE);
921 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
922 Dmsg0(dbglevel, "put_file_into_catalog\n");
924 /* Open the dedicated connexion */
925 if (!jcr->batch_started) {
926 if (!db_open_batch_connexion(jcr, mdb)) {
927 return false; /* error already printed */
929 if (!sql_batch_start(jcr, jcr->db_batch)) {
931 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
932 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
935 jcr->batch_started = true;
937 B_DB *bdb = jcr->db_batch;
939 split_path_and_file(jcr, bdb, ar->fname);
943 * if (bdb->changes > 100000) {
944 * db_write_batch_file_records(jcr);
946 * sql_batch_start(jcr, bdb);
950 return sql_batch_insert(jcr, bdb, ar);
953 #else /* ! HAVE_BATCH_FILE_INSERT */
956 * Create File record in B_DB
958 * In order to reduce database size, we store the File attributes,
959 * the FileName, and the Path separately. In principle, there
960 * is a single FileName record and a single Path record, no matter
961 * how many times it occurs. This is this subroutine, we separate
962 * the file and the path and create three database records.
964 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
967 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
968 Dmsg0(dbglevel, "put_file_into_catalog\n");
970 split_path_and_file(jcr, mdb, ar->fname);
972 if (!db_create_filename_record(jcr, mdb, ar)) {
975 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
978 if (!db_create_path_record(jcr, mdb, ar)) {
981 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
983 /* Now create master File record */
984 if (!db_create_file_record(jcr, mdb, ar)) {
987 Dmsg0(dbglevel, "db_create_file_record OK\n");
989 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
1000 * This is the master File entry containing the attributes.
1001 * The filename and path records have already been created.
1003 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1006 static const char *no_digest = "0";
1011 ASSERT(ar->FilenameId);
1013 if (ar->Digest == NULL || ar->Digest[0] == 0) {
1016 digest = ar->Digest;
1019 /* Must create it */
1021 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
1022 "LStat,MD5) VALUES (%u,%u,%u,%u,'%s','%s')",
1023 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
1026 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
1027 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
1028 mdb->cmd, sql_strerror(mdb));
1029 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1033 ar->FileId = sql_insert_id(mdb, NT_("File"));
1039 /* Create a Unique record for the filename -- no duplicates */
1040 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1044 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1045 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1047 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1049 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1050 mdb->num_rows = sql_num_rows(mdb);
1051 if (mdb->num_rows > 1) {
1053 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1054 edit_uint64(mdb->num_rows, ed1), mdb->fname);
1055 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1057 if (mdb->num_rows >= 1) {
1058 if ((row = sql_fetch_row(mdb)) == NULL) {
1059 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1060 mdb->fname, sql_strerror(mdb));
1061 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1064 ar->FilenameId = str_to_int64(row[0]);
1066 sql_free_result(mdb);
1067 return ar->FilenameId > 0;
1069 sql_free_result(mdb);
1072 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1074 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
1075 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1076 mdb->cmd, sql_strerror(mdb));
1077 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1080 ar->FilenameId = sql_insert_id(mdb, NT_("Filename"));
1082 return ar->FilenameId > 0;
1085 bool db_write_batch_file_records(JCR *jcr)
1090 #endif /* ! HAVE_BATCH_FILE_INSERT */
1093 /* List of SQL commands to create temp table and indicies */
1094 const char *create_temp_basefile[5] = {
1096 "CREATE TEMPORARY TABLE basefile%lld ("
1097 // "CREATE TABLE basefile%lld ("
1098 "Path BLOB NOT NULL,"
1099 "Name BLOB NOT NULL)",
1102 "CREATE TEMPORARY TABLE basefile%lld ("
1103 // "CREATE TABLE basefile%lld ("
1108 "CREATE TEMPORARY TABLE basefile%lld ("
1113 "CREATE TEMPORARY TABLE basefile%lld ("
1118 "DECLARE GLOBAL TEMPORARY TABLE basefile%lld ("
1119 "Path TEXT NOT NULL,"
1120 "Name TEXT NOT NULL)"
1121 "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
1125 * Create file attributes record, or base file attributes record
1127 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1132 * Make sure we have an acceptable attributes record.
1134 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1135 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1136 Jmsg(jcr, M_FATAL, 0, _("Attempt to put non-attributes into catalog. Stream=%d\n"));
1140 if (ar->FileType != FT_BASE) {
1141 ret = db_create_file_attributes_record(jcr, mdb, ar);
1143 } else if (jcr->HasBase) {
1144 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1147 Jmsg0(jcr, M_FATAL, 0, _("Can't Copy/Migrate job using BaseJob"));
1148 ret = true; /* in copy/migration what do we do ? */
1155 * Create Base File record in B_DB
1158 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1161 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1162 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1165 split_path_and_file(jcr, mdb, ar->fname);
1167 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1168 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1170 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1171 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1173 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1174 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1176 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1183 * Cleanup the base file temporary tables
1185 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1187 POOL_MEM buf(PM_MESSAGE);
1188 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1189 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1191 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1192 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1196 * Put all base file seen in the backup to the BaseFile table
1197 * and cleanup temporary tables
1199 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1207 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1208 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1209 "B.FileId, B.FileIndex "
1210 "FROM basefile%s AS A, new_basefile%s AS B "
1211 "WHERE A.Path = B.Path "
1212 "AND A.Name = B.Name "
1213 "ORDER BY B.FileId",
1214 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1215 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1216 jcr->nb_base_files_used = sql_affected_rows(mdb);
1217 db_cleanup_base_file(jcr, mdb);
1224 * Find the last "accurate" backup state with Base jobs
1225 * 1) Get all files with jobid in list (F subquery)
1226 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1227 * 3) Put the result in a temporary table for the end of job
1230 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1238 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1242 Mmsg(mdb->cmd, create_temp_basefile[db_type], (uint64_t) jcr->JobId);
1243 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1246 /* Quick and dirty fix for MySQL and Bacula 5.0.1 */
1249 "CREATE INDEX basefile%lld_idx ON basefile%lld (Path(255), Name(255))",
1250 (uint64_t) jcr->JobId, (uint64_t) jcr->JobId);
1251 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1255 Mmsg(buf, select_recent_version[db_type], jobids, jobids);
1257 "CREATE TEMPORARY TABLE new_basefile%lld AS "
1258 //"CREATE TABLE new_basefile%lld AS "
1259 "SELECT Path.Path AS Path, Filename.Name AS Name, Temp.FileIndex AS FileIndex,"
1260 "Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId, "
1262 "FROM ( %s ) AS Temp "
1263 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1264 "JOIN Path ON (Path.PathId = Temp.PathId) "
1265 "WHERE Temp.FileIndex > 0",
1266 (uint64_t)jcr->JobId, buf.c_str());
1268 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1274 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */