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 jr->JobId = sql_insert_id(mdb, mdb->cmd, NT_("Job"));
95 Mmsg2(&mdb->errmsg, _("Create DB Job record %s failed. ERR=%s\n"),
96 mdb->cmd, sql_strerror(mdb));
106 /* Create a JobMedia record for medium used this job
107 * Returns: false on failure
111 db_create_jobmedia_record(JCR *jcr, B_DB *mdb, JOBMEDIA_DBR *jm)
115 char ed1[50], ed2[50];
119 /* Now get count for VolIndex */
120 Mmsg(mdb->cmd, "SELECT count(*) from JobMedia WHERE JobId=%s",
121 edit_int64(jm->JobId, ed1));
122 count = get_sql_record_max(jcr, mdb);
128 /* Note, jm->Strip is not used and is not likely to be used
129 * in the near future, so I have removed it from the insert
130 * to save space in the DB. KES June 2006.
133 "INSERT INTO JobMedia (JobId,MediaId,FirstIndex,LastIndex,"
134 "StartFile,EndFile,StartBlock,EndBlock,VolIndex) "
135 "VALUES (%s,%s,%u,%u,%u,%u,%u,%u,%u)",
136 edit_int64(jm->JobId, ed1),
137 edit_int64(jm->MediaId, ed2),
138 jm->FirstIndex, jm->LastIndex,
139 jm->StartFile, jm->EndFile, jm->StartBlock, jm->EndBlock,count);
141 Dmsg0(300, mdb->cmd);
142 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
143 Mmsg2(&mdb->errmsg, _("Create JobMedia record %s failed: ERR=%s\n"), mdb->cmd,
147 /* Worked, now update the Media record with the EndFile and EndBlock */
149 "UPDATE Media SET EndFile=%u, EndBlock=%u WHERE MediaId=%u",
150 jm->EndFile, jm->EndBlock, jm->MediaId);
151 if (!UPDATE_DB(jcr, mdb, mdb->cmd)) {
152 Mmsg2(&mdb->errmsg, _("Update Media record %s failed: ERR=%s\n"), mdb->cmd,
158 Dmsg0(300, "Return from JobMedia\n");
162 /* Create Unique Pool record
163 * Returns: false on failure
167 db_create_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
170 char ed1[30], ed2[30], ed3[50], ed4[50], ed5[50];
172 Dmsg0(200, "In create pool\n");
174 Mmsg(mdb->cmd, "SELECT PoolId,Name FROM Pool WHERE Name='%s'", pr->Name);
175 Dmsg1(200, "selectpool: %s\n", mdb->cmd);
177 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
178 mdb->num_rows = sql_num_rows(mdb);
179 if (mdb->num_rows > 0) {
180 Mmsg1(&mdb->errmsg, _("pool record %s already exists\n"), pr->Name);
181 sql_free_result(mdb);
185 sql_free_result(mdb);
190 "INSERT INTO Pool (Name,NumVols,MaxVols,UseOnce,UseCatalog,"
191 "AcceptAnyVolume,AutoPrune,Recycle,VolRetention,VolUseDuration,"
192 "MaxVolJobs,MaxVolFiles,MaxVolBytes,PoolType,LabelType,LabelFormat,"
193 "RecyclePoolId,ScratchPoolId,ActionOnPurge) "
194 "VALUES ('%s',%u,%u,%d,%d,%d,%d,%d,%s,%s,%u,%u,%s,'%s',%d,'%s',%s,%s,%d)",
196 pr->NumVols, pr->MaxVols,
197 pr->UseOnce, pr->UseCatalog,
199 pr->AutoPrune, pr->Recycle,
200 edit_uint64(pr->VolRetention, ed1),
201 edit_uint64(pr->VolUseDuration, ed2),
202 pr->MaxVolJobs, pr->MaxVolFiles,
203 edit_uint64(pr->MaxVolBytes, ed3),
204 pr->PoolType, pr->LabelType, pr->LabelFormat,
205 edit_int64(pr->RecyclePoolId,ed4),
206 edit_int64(pr->ScratchPoolId,ed5),
209 Dmsg1(200, "Create Pool: %s\n", mdb->cmd);
210 pr->PoolId = sql_insert_id(mdb, mdb->cmd, NT_("Pool"));
211 if (pr->PoolId == 0) {
212 Mmsg2(&mdb->errmsg, _("Create db Pool record %s failed: ERR=%s\n"),
213 mdb->cmd, sql_strerror(mdb));
223 * Create Unique Device record
224 * Returns: false on failure
228 db_create_device_record(JCR *jcr, B_DB *mdb, DEVICE_DBR *dr)
231 char ed1[30], ed2[30];
233 Dmsg0(200, "In create Device\n");
235 Mmsg(mdb->cmd, "SELECT DeviceId,Name FROM Device WHERE Name='%s'", dr->Name);
236 Dmsg1(200, "selectdevice: %s\n", mdb->cmd);
238 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
239 mdb->num_rows = sql_num_rows(mdb);
240 if (mdb->num_rows > 0) {
241 Mmsg1(&mdb->errmsg, _("Device record %s already exists\n"), dr->Name);
242 sql_free_result(mdb);
246 sql_free_result(mdb);
251 "INSERT INTO Device (Name,MediaTypeId,StorageId) VALUES ('%s',%s,%s)",
253 edit_uint64(dr->MediaTypeId, ed1),
254 edit_int64(dr->StorageId, ed2));
255 Dmsg1(200, "Create Device: %s\n", mdb->cmd);
256 dr->DeviceId = sql_insert_id(mdb, mdb->cmd, NT_("Device"));
257 if (dr->DeviceId == 0) {
258 Mmsg2(&mdb->errmsg, _("Create db Device record %s failed: ERR=%s\n"),
259 mdb->cmd, sql_strerror(mdb));
271 * Create a Unique record for Storage -- no duplicates
272 * Returns: false on failure
273 * true on success with id in sr->StorageId
275 bool db_create_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
281 Mmsg(mdb->cmd, "SELECT StorageId,AutoChanger FROM Storage WHERE Name='%s'", sr->Name);
285 /* Check if it already exists */
286 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
287 mdb->num_rows = sql_num_rows(mdb);
288 /* If more than one, report error, but return first row */
289 if (mdb->num_rows > 1) {
290 Mmsg1(&mdb->errmsg, _("More than one Storage record!: %d\n"), (int)(mdb->num_rows));
291 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
293 if (mdb->num_rows >= 1) {
294 if ((row = sql_fetch_row(mdb)) == NULL) {
295 Mmsg1(&mdb->errmsg, _("error fetching Storage row: %s\n"), sql_strerror(mdb));
296 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
297 sql_free_result(mdb);
301 sr->StorageId = str_to_int64(row[0]);
302 sr->AutoChanger = atoi(row[1]); /* bool */
303 sql_free_result(mdb);
307 sql_free_result(mdb);
311 Mmsg(mdb->cmd, "INSERT INTO Storage (Name,AutoChanger)"
312 " VALUES ('%s',%d)", sr->Name, sr->AutoChanger);
314 sr->StorageId = sql_insert_id(mdb, mdb->cmd, NT_("Storage"));
315 if (sr->StorageId == 0) {
316 Mmsg2(&mdb->errmsg, _("Create DB Storage record %s failed. ERR=%s\n"),
317 mdb->cmd, sql_strerror(mdb));
318 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
330 * Create Unique MediaType record
331 * Returns: false on failure
335 db_create_mediatype_record(JCR *jcr, B_DB *mdb, MEDIATYPE_DBR *mr)
339 Dmsg0(200, "In create mediatype\n");
341 Mmsg(mdb->cmd, "SELECT MediaTypeId,MediaType FROM MediaType WHERE MediaType='%s'", mr->MediaType);
342 Dmsg1(200, "selectmediatype: %s\n", mdb->cmd);
344 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
345 mdb->num_rows = sql_num_rows(mdb);
346 if (mdb->num_rows > 0) {
347 Mmsg1(&mdb->errmsg, _("mediatype record %s already exists\n"), mr->MediaType);
348 sql_free_result(mdb);
352 sql_free_result(mdb);
357 "INSERT INTO MediaType (MediaType,ReadOnly) "
361 Dmsg1(200, "Create mediatype: %s\n", mdb->cmd);
362 mr->MediaTypeId = sql_insert_id(mdb, mdb->cmd, NT_("MediaType"));
363 if (mr->MediaTypeId == 0) {
364 Mmsg2(&mdb->errmsg, _("Create db mediatype record %s failed: ERR=%s\n"),
365 mdb->cmd, sql_strerror(mdb));
376 * Create Media record. VolumeName and non-zero Slot must be unique
378 * Returns: 0 on failure
382 db_create_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
385 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50], ed7[50], ed8[50];
386 char ed9[50], ed10[50], ed11[50], ed12[50];
390 Mmsg(mdb->cmd, "SELECT MediaId FROM Media WHERE VolumeName='%s'",
392 Dmsg1(500, "selectpool: %s\n", mdb->cmd);
394 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
395 mdb->num_rows = sql_num_rows(mdb);
396 if (mdb->num_rows > 0) {
397 Mmsg1(&mdb->errmsg, _("Volume \"%s\" already exists.\n"), mr->VolumeName);
398 sql_free_result(mdb);
402 sql_free_result(mdb);
407 "INSERT INTO Media (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,"
408 "VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
409 "VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolParts,"
410 "EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,"
411 "ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge)"
412 "VALUES ('%s','%s',0,%u,%s,%s,%d,%s,%s,%u,%u,'%s',%d,%s,%d,%s,%s,%d,0,0,%d,%s,"
413 "%s,%s,%s,%s,%d,%d)",
415 mr->MediaType, mr->PoolId,
416 edit_uint64(mr->MaxVolBytes,ed1),
417 edit_uint64(mr->VolCapacityBytes, ed2),
419 edit_uint64(mr->VolRetention, ed3),
420 edit_uint64(mr->VolUseDuration, ed4),
425 edit_uint64(mr->VolBytes, ed5),
427 edit_int64(mr->VolReadTime, ed6),
428 edit_int64(mr->VolWriteTime, ed7),
431 edit_int64(mr->StorageId, ed8),
432 edit_int64(mr->DeviceId, ed9),
433 edit_int64(mr->LocationId, ed10),
434 edit_int64(mr->ScratchPoolId, ed11),
435 edit_int64(mr->RecyclePoolId, ed12),
436 mr->Enabled, mr->ActionOnPurge
440 Dmsg1(500, "Create Volume: %s\n", mdb->cmd);
441 mr->MediaId = sql_insert_id(mdb, mdb->cmd), NT_("Media"));
442 if (mr->MediaId == 0) {
443 Mmsg2(&mdb->errmsg, _("Create DB Media record %s failed. ERR=%s\n"),
444 mdb->cmd, sql_strerror(mdb));
448 if (mr->set_label_date) {
449 char dt[MAX_TIME_LENGTH];
450 if (mr->LabelDate == 0) {
451 mr->LabelDate = time(NULL);
453 (void)localtime_r(&mr->LabelDate, &tm);
454 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
455 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
456 "WHERE MediaId=%d", dt, mr->MediaId);
457 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
460 * Make sure that if InChanger is non-zero any other identical slot
461 * has InChanger zero.
463 db_make_inchanger_unique(jcr, mdb, mr);
471 * Create a Unique record for the client -- no duplicates
472 * Returns: 0 on failure
473 * 1 on success with id in cr->ClientId
475 int db_create_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
479 char ed1[50], ed2[50];
482 Mmsg(mdb->cmd, "SELECT ClientId,Uname FROM Client WHERE Name='%s'", cr->Name);
485 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
486 mdb->num_rows = sql_num_rows(mdb);
487 /* If more than one, report error, but return first row */
488 if (mdb->num_rows > 1) {
489 Mmsg1(&mdb->errmsg, _("More than one Client!: %d\n"), (int)(mdb->num_rows));
490 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
492 if (mdb->num_rows >= 1) {
493 if ((row = sql_fetch_row(mdb)) == NULL) {
494 Mmsg1(&mdb->errmsg, _("error fetching Client row: %s\n"), sql_strerror(mdb));
495 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
496 sql_free_result(mdb);
500 cr->ClientId = str_to_int64(row[0]);
502 bstrncpy(cr->Uname, row[1], sizeof(cr->Uname));
504 cr->Uname[0] = 0; /* no name */
506 sql_free_result(mdb);
510 sql_free_result(mdb);
514 Mmsg(mdb->cmd, "INSERT INTO Client (Name,Uname,AutoPrune,"
515 "FileRetention,JobRetention) VALUES "
516 "('%s','%s',%d,%s,%s)", cr->Name, cr->Uname, cr->AutoPrune,
517 edit_uint64(cr->FileRetention, ed1),
518 edit_uint64(cr->JobRetention, ed2));
520 cr->ClientId = sql_insert_id(mdb, mdb->cmd, NT_("Client"));
521 if (cr->ClientId == 0) {
522 Mmsg2(&mdb->errmsg, _("Create DB Client record %s failed. ERR=%s\n"),
523 mdb->cmd, sql_strerror(mdb));
524 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
534 /* Create a Unique record for the Path -- no duplicates */
535 int db_create_path_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
540 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
541 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
543 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
544 strcmp(mdb->cached_path, mdb->path) == 0) {
545 ar->PathId = mdb->cached_path_id;
549 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
551 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
552 mdb->num_rows = sql_num_rows(mdb);
553 if (mdb->num_rows > 1) {
555 Mmsg2(&mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
556 edit_uint64(mdb->num_rows, ed1), mdb->path);
557 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
559 /* Even if there are multiple paths, take the first one */
560 if (mdb->num_rows >= 1) {
561 if ((row = sql_fetch_row(mdb)) == NULL) {
562 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
563 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
564 sql_free_result(mdb);
569 ar->PathId = str_to_int64(row[0]);
570 sql_free_result(mdb);
572 if (ar->PathId != mdb->cached_path_id) {
573 mdb->cached_path_id = ar->PathId;
574 mdb->cached_path_len = mdb->pnl;
575 pm_strcpy(mdb->cached_path, mdb->path);
580 sql_free_result(mdb);
583 Mmsg(mdb->cmd, "INSERT INTO Path (Path) VALUES ('%s')", mdb->esc_name);
585 ar->PathId = sql_insert_id(mdb, mdb->cmd, NT_("Path"));
586 if (ar->PathId == 0) {
587 Mmsg2(&mdb->errmsg, _("Create db Path record %s failed. ERR=%s\n"),
588 mdb->cmd, sql_strerror(mdb));
589 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
597 if (stat && ar->PathId != mdb->cached_path_id) {
598 mdb->cached_path_id = ar->PathId;
599 mdb->cached_path_len = mdb->pnl;
600 pm_strcpy(mdb->cached_path, mdb->path);
606 * Create a Unique record for the counter -- no duplicates
607 * Returns: 0 on failure
608 * 1 on success with counter filled in
610 int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
616 memset(&mcr, 0, sizeof(mcr));
617 bstrncpy(mcr.Counter, cr->Counter, sizeof(mcr.Counter));
618 if (db_get_counter_record(jcr, mdb, &mcr)) {
619 memcpy(cr, &mcr, sizeof(COUNTER_DBR));
625 Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,MinValue,MaxValue,CurrentValue,"
626 "WrapCounter) VALUES ('%s','%d','%d','%d','%s')",
627 cr->Counter, cr->MinValue, cr->MaxValue, cr->CurrentValue,
630 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
631 Mmsg2(&mdb->errmsg, _("Create DB Counters record %s failed. ERR=%s\n"),
632 mdb->cmd, sql_strerror(mdb));
633 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
644 * Create a FileSet record. This record is unique in the
645 * name and the MD5 signature of the include/exclude sets.
646 * Returns: 0 on failure
647 * 1 on success with FileSetId in record
649 bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
656 fsr->created = false;
657 Mmsg(mdb->cmd, "SELECT FileSetId,CreateTime FROM FileSet WHERE "
658 "FileSet='%s' AND MD5='%s'", fsr->FileSet, fsr->MD5);
661 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
662 mdb->num_rows = sql_num_rows(mdb);
663 if (mdb->num_rows > 1) {
664 Mmsg1(&mdb->errmsg, _("More than one FileSet!: %d\n"), (int)(mdb->num_rows));
665 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
667 if (mdb->num_rows >= 1) {
668 if ((row = sql_fetch_row(mdb)) == NULL) {
669 Mmsg1(&mdb->errmsg, _("error fetching FileSet row: ERR=%s\n"), sql_strerror(mdb));
670 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
671 sql_free_result(mdb);
675 fsr->FileSetId = str_to_int64(row[0]);
676 if (row[1] == NULL) {
677 fsr->cCreateTime[0] = 0;
679 bstrncpy(fsr->cCreateTime, row[1], sizeof(fsr->cCreateTime));
681 sql_free_result(mdb);
685 sql_free_result(mdb);
688 if (fsr->CreateTime == 0 && fsr->cCreateTime[0] == 0) {
689 fsr->CreateTime = time(NULL);
691 (void)localtime_r(&fsr->CreateTime, &tm);
692 strftime(fsr->cCreateTime, sizeof(fsr->cCreateTime), "%Y-%m-%d %H:%M:%S", &tm);
695 Mmsg(mdb->cmd, "INSERT INTO FileSet (FileSet,MD5,CreateTime) "
696 "VALUES ('%s','%s','%s')", fsr->FileSet, fsr->MD5, fsr->cCreateTime);
698 fsr->FileSetId = sql_insert_id(mdb, mdb->cmd, NT_("FileSet"));
699 if (fsr->FileSetId == 0) {
700 Mmsg2(&mdb->errmsg, _("Create DB FileSet record %s failed. ERR=%s\n"),
701 mdb->cmd, sql_strerror(mdb));
702 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
717 * dev_t st_dev; * device *
718 * ino_t st_ino; * inode *
719 * mode_t st_mode; * protection *
720 * nlink_t st_nlink; * number of hard links *
721 * uid_t st_uid; * user ID of owner *
722 * gid_t st_gid; * group ID of owner *
723 * dev_t st_rdev; * device type (if inode device) *
724 * off_t st_size; * total size, in bytes *
725 * unsigned long st_blksize; * blocksize for filesystem I/O *
726 * unsigned long st_blocks; * number of blocks allocated *
727 * time_t st_atime; * time of last access *
728 * time_t st_mtime; * time of last modification *
729 * time_t st_ctime; * time of last inode change *
733 #ifdef HAVE_BATCH_FILE_INSERT
735 /* All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
736 * tables. This code can be activated by adding "#define HAVE_BATCH_FILE_INSERT 1"
740 * - bulk load a temp table
741 * - insert missing filenames into filename with a single query (lock filenames
742 * - table before that to avoid possible duplicate inserts with concurrent update)
743 * - insert missing paths into path with another single query
744 * - then insert the join between the temp, filename and path tables into file.
751 bool my_batch_start(JCR *jcr, B_DB *mdb)
756 ok = db_sql_query(mdb,
757 "CREATE TEMPORARY TABLE batch ("
763 "MD5 tinyblob)",NULL, NULL);
772 bool my_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
778 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
779 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
781 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
782 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
784 if (ar->Digest == NULL || ar->Digest[0] == 0) {
790 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
791 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
792 mdb->esc_name, ar->attr, digest);
794 return INSERT_DB(jcr, mdb, mdb->cmd);
797 /* set error to something to abort operation */
802 bool my_batch_end(JCR *jcr, B_DB *mdb, const char *error)
805 Dmsg0(50, "sql_batch_end started\n");
809 mdb->status = (dbi_error_flag)0;
821 bool db_write_batch_file_records(JCR *jcr)
823 int JobStatus = jcr->JobStatus;
825 if (!jcr->batch_started) { /* no files to backup ? */
826 Dmsg0(50,"db_create_file_record : no files\n");
829 if (job_canceled(jcr)) {
833 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
835 jcr->JobStatus = JS_AttrInserting;
836 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
837 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
840 if (job_canceled(jcr)) {
845 /* we have to lock tables */
846 if (!db_sql_query(jcr->db_batch, sql_batch_lock_path_query, NULL, NULL)) {
847 Jmsg1(jcr, M_FATAL, 0, "Lock Path table %s\n", jcr->db_batch->errmsg);
851 if (!db_sql_query(jcr->db_batch, sql_batch_fill_path_query, NULL, NULL)) {
852 Jmsg1(jcr, M_FATAL, 0, "Fill Path table %s\n",jcr->db_batch->errmsg);
853 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
857 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
858 Jmsg1(jcr, M_FATAL, 0, "Unlock Path table %s\n", jcr->db_batch->errmsg);
862 /* we have to lock tables */
863 if (!db_sql_query(jcr->db_batch,sql_batch_lock_filename_query,NULL, NULL)) {
864 Jmsg1(jcr, M_FATAL, 0, "Lock Filename table %s\n", jcr->db_batch->errmsg);
868 if (!db_sql_query(jcr->db_batch,sql_batch_fill_filename_query, NULL,NULL)) {
869 Jmsg1(jcr,M_FATAL,0,"Fill Filename table %s\n",jcr->db_batch->errmsg);
870 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
874 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
875 Jmsg1(jcr, M_FATAL, 0, "Unlock Filename table %s\n", jcr->db_batch->errmsg);
879 if (!db_sql_query(jcr->db_batch,
880 "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)"
881 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
882 "Filename.FilenameId,batch.LStat, batch.MD5 "
884 "JOIN Path ON (batch.Path = Path.Path) "
885 "JOIN Filename ON (batch.Name = Filename.Name)",
888 Jmsg1(jcr, M_FATAL, 0, "Fill File table %s\n", jcr->db_batch->errmsg);
892 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
894 jcr->JobStatus = JobStatus; /* reset entry status */
899 * Create File record in B_DB
901 * In order to reduce database size, we store the File attributes,
902 * the FileName, and the Path separately. In principle, there
903 * is a single FileName record and a single Path record, no matter
904 * how many times it occurs. This is this subroutine, we separate
905 * the file and the path and fill temporary tables with this three records.
907 * Note: all routines that call this expect to be able to call
908 * db_strerror(mdb) to get the error message, so the error message
909 * MUST be edited into mdb->errmsg before returning an error status.
911 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
913 ASSERT(ar->FileType != FT_BASE);
915 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
916 Dmsg0(dbglevel, "put_file_into_catalog\n");
918 /* Open the dedicated connexion */
919 if (!jcr->batch_started) {
920 if (!db_open_batch_connexion(jcr, mdb)) {
921 return false; /* error already printed */
923 if (!sql_batch_start(jcr, jcr->db_batch)) {
925 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
926 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
929 jcr->batch_started = true;
931 B_DB *bdb = jcr->db_batch;
933 split_path_and_file(jcr, bdb, ar->fname);
937 * if (bdb->changes > 100000) {
938 * db_write_batch_file_records(jcr);
940 * sql_batch_start(jcr, bdb);
944 return sql_batch_insert(jcr, bdb, ar);
947 #else /* ! HAVE_BATCH_FILE_INSERT */
950 * Create File record in B_DB
952 * In order to reduce database size, we store the File attributes,
953 * the FileName, and the Path separately. In principle, there
954 * is a single FileName record and a single Path record, no matter
955 * how many times it occurs. This is this subroutine, we separate
956 * the file and the path and create three database records.
958 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
961 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
962 Dmsg0(dbglevel, "put_file_into_catalog\n");
964 split_path_and_file(jcr, mdb, ar->fname);
966 if (!db_create_filename_record(jcr, mdb, ar)) {
969 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
972 if (!db_create_path_record(jcr, mdb, ar)) {
975 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
977 /* Now create master File record */
978 if (!db_create_file_record(jcr, mdb, ar)) {
981 Dmsg0(dbglevel, "db_create_file_record OK\n");
983 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
994 * This is the master File entry containing the attributes.
995 * The filename and path records have already been created.
997 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1000 static const char *no_digest = "0";
1005 ASSERT(ar->FilenameId);
1007 if (ar->Digest == NULL || ar->Digest[0] == 0) {
1010 digest = ar->Digest;
1013 /* Must create it */
1015 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
1016 "LStat,MD5) VALUES (%u,%u,%u,%u,'%s','%s')",
1017 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
1020 ar->FileId = sql_insert_id(mdb, mdb->cmd, NT_("File"));
1021 if (ar->FileId == 0) {
1022 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
1023 mdb->cmd, sql_strerror(mdb));
1024 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1032 /* Create a Unique record for the filename -- no duplicates */
1033 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1037 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1038 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1040 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1042 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1043 mdb->num_rows = sql_num_rows(mdb);
1044 if (mdb->num_rows > 1) {
1046 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1047 edit_uint64(mdb->num_rows, ed1), mdb->fname);
1048 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1050 if (mdb->num_rows >= 1) {
1051 if ((row = sql_fetch_row(mdb)) == NULL) {
1052 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1053 mdb->fname, sql_strerror(mdb));
1054 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1057 ar->FilenameId = str_to_int64(row[0]);
1059 sql_free_result(mdb);
1060 return ar->FilenameId > 0;
1062 sql_free_result(mdb);
1065 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1067 ar->FilenameId = sql_insert_id(mdb, mdb->cmd, NT_("Filename"));
1068 if (ar->FilenameId == 0) {
1069 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1070 mdb->cmd, sql_strerror(mdb));
1071 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1073 return ar->FilenameId > 0;
1076 bool db_write_batch_file_records(JCR *jcr)
1081 #endif /* ! HAVE_BATCH_FILE_INSERT */
1084 /* List of SQL commands to create temp table and indicies */
1085 const char *create_temp_basefile[5] = {
1087 "CREATE TEMPORARY TABLE basefile%lld ("
1088 // "CREATE TABLE basefile%lld ("
1089 "Path BLOB NOT NULL,"
1090 "Name BLOB NOT NULL)",
1093 "CREATE TEMPORARY TABLE basefile%lld ("
1094 // "CREATE TABLE basefile%lld ("
1099 "CREATE TEMPORARY TABLE basefile%lld ("
1104 "CREATE TEMPORARY TABLE basefile%lld ("
1109 "DECLARE GLOBAL TEMPORARY TABLE basefile%lld ("
1110 "Path TEXT NOT NULL,"
1111 "Name TEXT NOT NULL)"
1112 "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
1116 * Create file attributes record, or base file attributes record
1118 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1123 * Make sure we have an acceptable attributes record.
1125 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1126 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1127 Jmsg(jcr, M_FATAL, 0, _("Attempt to put non-attributes into catalog. Stream=%d\n"));
1131 if (ar->FileType != FT_BASE) {
1132 ret = db_create_file_attributes_record(jcr, mdb, ar);
1134 } else if (jcr->HasBase) {
1135 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1138 Jmsg0(jcr, M_FATAL, 0, _("Can't Copy/Migrate job using BaseJob"));
1139 ret = true; /* in copy/migration what do we do ? */
1146 * Create Base File record in B_DB
1149 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1152 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1153 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1156 split_path_and_file(jcr, mdb, ar->fname);
1158 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1159 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1161 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1162 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1164 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1165 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1167 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1174 * Cleanup the base file temporary tables
1176 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1178 POOL_MEM buf(PM_MESSAGE);
1179 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1180 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1182 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1183 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1187 * Put all base file seen in the backup to the BaseFile table
1188 * and cleanup temporary tables
1190 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1198 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1199 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1200 "B.FileId, B.FileIndex "
1201 "FROM basefile%s AS A, new_basefile%s AS B "
1202 "WHERE A.Path = B.Path "
1203 "AND A.Name = B.Name "
1204 "ORDER BY B.FileId",
1205 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1206 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1207 jcr->nb_base_files_used = sql_affected_rows(mdb);
1208 db_cleanup_base_file(jcr, mdb);
1215 * Find the last "accurate" backup state with Base jobs
1216 * 1) Get all files with jobid in list (F subquery)
1217 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1218 * 3) Put the result in a temporary table for the end of job
1221 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1229 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1233 Mmsg(mdb->cmd, create_temp_basefile[db_type], (uint64_t) jcr->JobId);
1234 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1237 /* Quick and dirty fix for MySQL and Bacula 5.0.1 */
1240 "CREATE INDEX basefile%lld_idx ON basefile%lld (Path(255), Name(255))",
1241 (uint64_t) jcr->JobId, (uint64_t) jcr->JobId);
1242 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1246 Mmsg(buf, select_recent_version[db_type], jobids, jobids);
1248 "CREATE TEMPORARY TABLE new_basefile%lld AS "
1249 //"CREATE TABLE new_basefile%lld AS "
1250 "SELECT Path.Path AS Path, Filename.Name AS Name, Temp.FileIndex AS FileIndex,"
1251 "Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId, "
1253 "FROM ( %s ) AS Temp "
1254 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1255 "JOIN Path ON (Path.PathId = Temp.PathId) "
1256 "WHERE Temp.FileIndex > 0",
1257 (uint64_t)jcr->JobId, buf.c_str());
1259 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1265 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */