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 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
101 Mmsg2(&mdb->errmsg, _("Create DB Job record %s failed. ERR=%s\n"),
102 mdb->cmd, sql_strerror(mdb));
106 jr->JobId = sql_insert_id(mdb, NT_("Job"));
114 /* Create a JobMedia record for medium used this job
115 * Returns: false on failure
119 db_create_jobmedia_record(JCR *jcr, B_DB *mdb, JOBMEDIA_DBR *jm)
123 char ed1[50], ed2[50];
127 /* Now get count for VolIndex */
128 Mmsg(mdb->cmd, "SELECT count(*) from JobMedia WHERE JobId=%s",
129 edit_int64(jm->JobId, ed1));
130 count = get_sql_record_max(jcr, mdb);
136 /* Note, jm->Strip is not used and is not likely to be used
137 * in the near future, so I have removed it from the insert
138 * to save space in the DB. KES June 2006.
141 "INSERT INTO JobMedia (JobId,MediaId,FirstIndex,LastIndex,"
142 "StartFile,EndFile,StartBlock,EndBlock,VolIndex) "
143 "VALUES (%s,%s,%u,%u,%u,%u,%u,%u,%u)",
144 edit_int64(jm->JobId, ed1),
145 edit_int64(jm->MediaId, ed2),
146 jm->FirstIndex, jm->LastIndex,
147 jm->StartFile, jm->EndFile, jm->StartBlock, jm->EndBlock,count);
149 Dmsg0(300, mdb->cmd);
150 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
151 Mmsg2(&mdb->errmsg, _("Create JobMedia record %s failed: ERR=%s\n"), mdb->cmd,
155 /* Worked, now update the Media record with the EndFile and EndBlock */
157 "UPDATE Media SET EndFile=%u, EndBlock=%u WHERE MediaId=%u",
158 jm->EndFile, jm->EndBlock, jm->MediaId);
159 if (!UPDATE_DB(jcr, mdb, mdb->cmd)) {
160 Mmsg2(&mdb->errmsg, _("Update Media record %s failed: ERR=%s\n"), mdb->cmd,
166 Dmsg0(300, "Return from JobMedia\n");
170 /* Create Unique Pool record
171 * Returns: false on failure
175 db_create_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
178 char ed1[30], ed2[30], ed3[50], ed4[50], ed5[50];
180 Dmsg0(200, "In create pool\n");
182 Mmsg(mdb->cmd, "SELECT PoolId,Name FROM Pool WHERE Name='%s'", pr->Name);
183 Dmsg1(200, "selectpool: %s\n", mdb->cmd);
185 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
186 mdb->num_rows = sql_num_rows(mdb);
187 if (mdb->num_rows > 0) {
188 Mmsg1(&mdb->errmsg, _("pool record %s already exists\n"), pr->Name);
189 sql_free_result(mdb);
193 sql_free_result(mdb);
198 "INSERT INTO Pool (Name,NumVols,MaxVols,UseOnce,UseCatalog,"
199 "AcceptAnyVolume,AutoPrune,Recycle,VolRetention,VolUseDuration,"
200 "MaxVolJobs,MaxVolFiles,MaxVolBytes,PoolType,LabelType,LabelFormat,"
201 "RecyclePoolId,ScratchPoolId,ActionOnPurge) "
202 "VALUES ('%s',%u,%u,%d,%d,%d,%d,%d,%s,%s,%u,%u,%s,'%s',%d,'%s',%s,%s,%d)",
204 pr->NumVols, pr->MaxVols,
205 pr->UseOnce, pr->UseCatalog,
207 pr->AutoPrune, pr->Recycle,
208 edit_uint64(pr->VolRetention, ed1),
209 edit_uint64(pr->VolUseDuration, ed2),
210 pr->MaxVolJobs, pr->MaxVolFiles,
211 edit_uint64(pr->MaxVolBytes, ed3),
212 pr->PoolType, pr->LabelType, pr->LabelFormat,
213 edit_int64(pr->RecyclePoolId,ed4),
214 edit_int64(pr->ScratchPoolId,ed5),
217 Dmsg1(200, "Create Pool: %s\n", mdb->cmd);
218 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
219 Mmsg2(&mdb->errmsg, _("Create db Pool record %s failed: ERR=%s\n"),
220 mdb->cmd, sql_strerror(mdb));
224 pr->PoolId = sql_insert_id(mdb, NT_("Pool"));
232 * Create Unique Device record
233 * Returns: false on failure
237 db_create_device_record(JCR *jcr, B_DB *mdb, DEVICE_DBR *dr)
240 char ed1[30], ed2[30];
242 Dmsg0(200, "In create Device\n");
244 Mmsg(mdb->cmd, "SELECT DeviceId,Name FROM Device WHERE Name='%s'", dr->Name);
245 Dmsg1(200, "selectdevice: %s\n", mdb->cmd);
247 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
248 mdb->num_rows = sql_num_rows(mdb);
249 if (mdb->num_rows > 0) {
250 Mmsg1(&mdb->errmsg, _("Device record %s already exists\n"), dr->Name);
251 sql_free_result(mdb);
255 sql_free_result(mdb);
260 "INSERT INTO Device (Name,MediaTypeId,StorageId) VALUES ('%s',%s,%s)",
262 edit_uint64(dr->MediaTypeId, ed1),
263 edit_int64(dr->StorageId, ed2));
264 Dmsg1(200, "Create Device: %s\n", mdb->cmd);
265 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
266 Mmsg2(&mdb->errmsg, _("Create db Device record %s failed: ERR=%s\n"),
267 mdb->cmd, sql_strerror(mdb));
271 dr->DeviceId = sql_insert_id(mdb, NT_("Device"));
281 * Create a Unique record for Storage -- no duplicates
282 * Returns: false on failure
283 * true on success with id in sr->StorageId
285 bool db_create_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
291 Mmsg(mdb->cmd, "SELECT StorageId,AutoChanger FROM Storage WHERE Name='%s'", sr->Name);
295 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
296 mdb->num_rows = sql_num_rows(mdb);
297 /* If more than one, report error, but return first row */
298 if (mdb->num_rows > 1) {
299 Mmsg1(&mdb->errmsg, _("More than one Storage record!: %d\n"), (int)(mdb->num_rows));
300 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
302 if (mdb->num_rows >= 1) {
303 if ((row = sql_fetch_row(mdb)) == NULL) {
304 Mmsg1(&mdb->errmsg, _("error fetching Storage row: %s\n"), sql_strerror(mdb));
305 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
306 sql_free_result(mdb);
310 sr->StorageId = str_to_int64(row[0]);
311 sr->AutoChanger = atoi(row[1]); /* bool */
312 sql_free_result(mdb);
316 sql_free_result(mdb);
320 Mmsg(mdb->cmd, "INSERT INTO Storage (Name,AutoChanger)"
321 " VALUES ('%s',%d)", sr->Name, sr->AutoChanger);
323 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
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);
329 sr->StorageId = sql_insert_id(mdb, NT_("Storage"));
339 * Create Unique MediaType record
340 * Returns: false on failure
344 db_create_mediatype_record(JCR *jcr, B_DB *mdb, MEDIATYPE_DBR *mr)
348 Dmsg0(200, "In create mediatype\n");
350 Mmsg(mdb->cmd, "SELECT MediaTypeId,MediaType FROM MediaType WHERE MediaType='%s'", mr->MediaType);
351 Dmsg1(200, "selectmediatype: %s\n", mdb->cmd);
353 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
354 mdb->num_rows = sql_num_rows(mdb);
355 if (mdb->num_rows > 0) {
356 Mmsg1(&mdb->errmsg, _("mediatype record %s already exists\n"), mr->MediaType);
357 sql_free_result(mdb);
361 sql_free_result(mdb);
366 "INSERT INTO MediaType (MediaType,ReadOnly) "
370 Dmsg1(200, "Create mediatype: %s\n", mdb->cmd);
371 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
372 Mmsg2(&mdb->errmsg, _("Create db mediatype record %s failed: ERR=%s\n"),
373 mdb->cmd, sql_strerror(mdb));
377 mr->MediaTypeId = sql_insert_id(mdb, NT_("MediaType"));
386 * Create Media record. VolumeName and non-zero Slot must be unique
388 * Returns: 0 on failure
392 db_create_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
395 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50], ed7[50], ed8[50];
396 char ed9[50], ed10[50], ed11[50], ed12[50];
400 Mmsg(mdb->cmd, "SELECT MediaId FROM Media WHERE VolumeName='%s'",
402 Dmsg1(500, "selectpool: %s\n", mdb->cmd);
404 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
405 mdb->num_rows = sql_num_rows(mdb);
406 if (mdb->num_rows > 0) {
407 Mmsg1(&mdb->errmsg, _("Volume \"%s\" already exists.\n"), mr->VolumeName);
408 sql_free_result(mdb);
412 sql_free_result(mdb);
417 "INSERT INTO Media (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,"
418 "VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
419 "VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolParts,"
420 "EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,"
421 "ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge)"
422 "VALUES ('%s','%s',0,%u,%s,%s,%d,%s,%s,%u,%u,'%s',%d,%s,%d,%s,%s,%d,0,0,%d,%s,"
423 "%s,%s,%s,%s,%d,%d)",
425 mr->MediaType, mr->PoolId,
426 edit_uint64(mr->MaxVolBytes,ed1),
427 edit_uint64(mr->VolCapacityBytes, ed2),
429 edit_uint64(mr->VolRetention, ed3),
430 edit_uint64(mr->VolUseDuration, ed4),
435 edit_uint64(mr->VolBytes, ed5),
437 edit_int64(mr->VolReadTime, ed6),
438 edit_int64(mr->VolWriteTime, ed7),
441 edit_int64(mr->StorageId, ed8),
442 edit_int64(mr->DeviceId, ed9),
443 edit_int64(mr->LocationId, ed10),
444 edit_int64(mr->ScratchPoolId, ed11),
445 edit_int64(mr->RecyclePoolId, ed12),
446 mr->Enabled, mr->ActionOnPurge
450 Dmsg1(500, "Create Volume: %s\n", mdb->cmd);
451 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
452 Mmsg2(&mdb->errmsg, _("Create DB Media record %s failed. ERR=%s\n"),
453 mdb->cmd, sql_strerror(mdb));
456 mr->MediaId = sql_insert_id(mdb, NT_("Media"));
458 if (mr->set_label_date) {
459 char dt[MAX_TIME_LENGTH];
460 if (mr->LabelDate == 0) {
461 mr->LabelDate = time(NULL);
463 (void)localtime_r(&mr->LabelDate, &tm);
464 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
465 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
466 "WHERE MediaId=%d", dt, mr->MediaId);
467 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
470 * Make sure that if InChanger is non-zero any other identical slot
471 * has InChanger zero.
473 db_make_inchanger_unique(jcr, mdb, mr);
481 * Create a Unique record for the client -- no duplicates
482 * Returns: 0 on failure
483 * 1 on success with id in cr->ClientId
485 int db_create_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
489 char ed1[50], ed2[50];
492 Mmsg(mdb->cmd, "SELECT ClientId,Uname FROM Client WHERE Name='%s'", cr->Name);
495 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
496 mdb->num_rows = sql_num_rows(mdb);
497 /* If more than one, report error, but return first row */
498 if (mdb->num_rows > 1) {
499 Mmsg1(&mdb->errmsg, _("More than one Client!: %d\n"), (int)(mdb->num_rows));
500 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
502 if (mdb->num_rows >= 1) {
503 if ((row = sql_fetch_row(mdb)) == NULL) {
504 Mmsg1(&mdb->errmsg, _("error fetching Client row: %s\n"), sql_strerror(mdb));
505 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
506 sql_free_result(mdb);
510 cr->ClientId = str_to_int64(row[0]);
512 bstrncpy(cr->Uname, row[1], sizeof(cr->Uname));
514 cr->Uname[0] = 0; /* no name */
516 sql_free_result(mdb);
520 sql_free_result(mdb);
524 Mmsg(mdb->cmd, "INSERT INTO Client (Name,Uname,AutoPrune,"
525 "FileRetention,JobRetention) VALUES "
526 "('%s','%s',%d,%s,%s)", cr->Name, cr->Uname, cr->AutoPrune,
527 edit_uint64(cr->FileRetention, ed1),
528 edit_uint64(cr->JobRetention, ed2));
530 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
531 Mmsg2(&mdb->errmsg, _("Create DB Client record %s failed. ERR=%s\n"),
532 mdb->cmd, sql_strerror(mdb));
533 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
537 cr->ClientId = sql_insert_id(mdb, NT_("Client"));
545 /* Create a Unique record for the Path -- no duplicates */
546 int db_create_path_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
551 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
552 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
554 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
555 strcmp(mdb->cached_path, mdb->path) == 0) {
556 ar->PathId = mdb->cached_path_id;
560 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
562 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
563 mdb->num_rows = sql_num_rows(mdb);
564 if (mdb->num_rows > 1) {
566 Mmsg2(&mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
567 edit_uint64(mdb->num_rows, ed1), mdb->path);
568 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
570 /* Even if there are multiple paths, take the first one */
571 if (mdb->num_rows >= 1) {
572 if ((row = sql_fetch_row(mdb)) == NULL) {
573 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
574 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
575 sql_free_result(mdb);
580 ar->PathId = str_to_int64(row[0]);
581 sql_free_result(mdb);
583 if (ar->PathId != mdb->cached_path_id) {
584 mdb->cached_path_id = ar->PathId;
585 mdb->cached_path_len = mdb->pnl;
586 pm_strcpy(mdb->cached_path, mdb->path);
591 sql_free_result(mdb);
594 Mmsg(mdb->cmd, "INSERT INTO Path (Path) VALUES ('%s')", mdb->esc_name);
596 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
597 Mmsg2(&mdb->errmsg, _("Create db Path record %s failed. ERR=%s\n"),
598 mdb->cmd, sql_strerror(mdb));
599 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
603 ar->PathId = sql_insert_id(mdb, NT_("Path"));
608 if (stat && ar->PathId != mdb->cached_path_id) {
609 mdb->cached_path_id = ar->PathId;
610 mdb->cached_path_len = mdb->pnl;
611 pm_strcpy(mdb->cached_path, mdb->path);
617 * Create a Unique record for the counter -- no duplicates
618 * Returns: 0 on failure
619 * 1 on success with counter filled in
621 int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
627 memset(&mcr, 0, sizeof(mcr));
628 bstrncpy(mcr.Counter, cr->Counter, sizeof(mcr.Counter));
629 if (db_get_counter_record(jcr, mdb, &mcr)) {
630 memcpy(cr, &mcr, sizeof(COUNTER_DBR));
636 Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,MinValue,MaxValue,CurrentValue,"
637 "WrapCounter) VALUES ('%s','%d','%d','%d','%s')",
638 cr->Counter, cr->MinValue, cr->MaxValue, cr->CurrentValue,
641 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
642 Mmsg2(&mdb->errmsg, _("Create DB Counters record %s failed. ERR=%s\n"),
643 mdb->cmd, sql_strerror(mdb));
644 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
655 * Create a FileSet record. This record is unique in the
656 * name and the MD5 signature of the include/exclude sets.
657 * Returns: 0 on failure
658 * 1 on success with FileSetId in record
660 bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
667 fsr->created = false;
668 Mmsg(mdb->cmd, "SELECT FileSetId,CreateTime FROM FileSet WHERE "
669 "FileSet='%s' AND MD5='%s'", fsr->FileSet, fsr->MD5);
672 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
673 mdb->num_rows = sql_num_rows(mdb);
674 if (mdb->num_rows > 1) {
675 Mmsg1(&mdb->errmsg, _("More than one FileSet!: %d\n"), (int)(mdb->num_rows));
676 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
678 if (mdb->num_rows >= 1) {
679 if ((row = sql_fetch_row(mdb)) == NULL) {
680 Mmsg1(&mdb->errmsg, _("error fetching FileSet row: ERR=%s\n"), sql_strerror(mdb));
681 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
682 sql_free_result(mdb);
686 fsr->FileSetId = str_to_int64(row[0]);
687 if (row[1] == NULL) {
688 fsr->cCreateTime[0] = 0;
690 bstrncpy(fsr->cCreateTime, row[1], sizeof(fsr->cCreateTime));
692 sql_free_result(mdb);
696 sql_free_result(mdb);
699 if (fsr->CreateTime == 0 && fsr->cCreateTime[0] == 0) {
700 fsr->CreateTime = time(NULL);
702 (void)localtime_r(&fsr->CreateTime, &tm);
703 strftime(fsr->cCreateTime, sizeof(fsr->cCreateTime), "%Y-%m-%d %H:%M:%S", &tm);
706 Mmsg(mdb->cmd, "INSERT INTO FileSet (FileSet,MD5,CreateTime) "
707 "VALUES ('%s','%s','%s')", fsr->FileSet, fsr->MD5, fsr->cCreateTime);
709 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
710 Mmsg2(&mdb->errmsg, _("Create DB FileSet record %s failed. ERR=%s\n"),
711 mdb->cmd, sql_strerror(mdb));
712 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
716 fsr->FileSetId = sql_insert_id(mdb, NT_("FileSet"));
729 * dev_t st_dev; * device *
730 * ino_t st_ino; * inode *
731 * mode_t st_mode; * protection *
732 * nlink_t st_nlink; * number of hard links *
733 * uid_t st_uid; * user ID of owner *
734 * gid_t st_gid; * group ID of owner *
735 * dev_t st_rdev; * device type (if inode device) *
736 * off_t st_size; * total size, in bytes *
737 * unsigned long st_blksize; * blocksize for filesystem I/O *
738 * unsigned long st_blocks; * number of blocks allocated *
739 * time_t st_atime; * time of last access *
740 * time_t st_mtime; * time of last modification *
741 * time_t st_ctime; * time of last inode change *
745 #ifdef HAVE_BATCH_FILE_INSERT
747 /* All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
748 * tables. This code can be activated by adding "#define HAVE_BATCH_FILE_INSERT 1"
752 * - bulk load a temp table
753 * - insert missing filenames into filename with a single query (lock filenames
754 * - table before that to avoid possible duplicate inserts with concurrent update)
755 * - insert missing paths into path with another single query
756 * - then insert the join between the temp, filename and path tables into file.
763 bool my_batch_start(JCR *jcr, B_DB *mdb)
768 ok = db_sql_query(mdb,
769 "CREATE TEMPORARY TABLE batch ("
775 "MD5 tinyblob)",NULL, NULL);
784 bool my_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
790 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
791 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
793 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
794 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
796 if (ar->Digest == NULL || ar->Digest[0] == 0) {
802 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
803 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
804 mdb->esc_name, ar->attr, digest);
806 return INSERT_DB(jcr, mdb, mdb->cmd);
809 /* set error to something to abort operation */
814 bool my_batch_end(JCR *jcr, B_DB *mdb, const char *error)
817 Dmsg0(50, "sql_batch_end started\n");
821 mdb->status = (dbi_error_flag)0;
833 bool db_write_batch_file_records(JCR *jcr)
835 int JobStatus = jcr->JobStatus;
837 if (!jcr->batch_started) { /* no files to backup ? */
838 Dmsg0(50,"db_create_file_record : no files\n");
841 if (job_canceled(jcr)) {
845 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
847 jcr->JobStatus = JS_AttrInserting;
848 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
849 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
852 if (job_canceled(jcr)) {
857 /* we have to lock tables */
858 if (!db_sql_query(jcr->db_batch, sql_batch_lock_path_query, NULL, NULL)) {
859 Jmsg1(jcr, M_FATAL, 0, "Lock Path table %s\n", jcr->db_batch->errmsg);
863 if (!db_sql_query(jcr->db_batch, sql_batch_fill_path_query, NULL, NULL)) {
864 Jmsg1(jcr, M_FATAL, 0, "Fill Path table %s\n",jcr->db_batch->errmsg);
865 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
869 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
870 Jmsg1(jcr, M_FATAL, 0, "Unlock Path table %s\n", jcr->db_batch->errmsg);
874 /* we have to lock tables */
875 if (!db_sql_query(jcr->db_batch,sql_batch_lock_filename_query,NULL, NULL)) {
876 Jmsg1(jcr, M_FATAL, 0, "Lock Filename table %s\n", jcr->db_batch->errmsg);
880 if (!db_sql_query(jcr->db_batch,sql_batch_fill_filename_query, NULL,NULL)) {
881 Jmsg1(jcr,M_FATAL,0,"Fill Filename table %s\n",jcr->db_batch->errmsg);
882 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
886 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
887 Jmsg1(jcr, M_FATAL, 0, "Unlock Filename table %s\n", jcr->db_batch->errmsg);
891 if (!db_sql_query(jcr->db_batch,
892 "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)"
893 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
894 "Filename.FilenameId,batch.LStat, batch.MD5 "
896 "JOIN Path ON (batch.Path = Path.Path) "
897 "JOIN Filename ON (batch.Name = Filename.Name)",
900 Jmsg1(jcr, M_FATAL, 0, "Fill File table %s\n", jcr->db_batch->errmsg);
904 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
906 jcr->JobStatus = JobStatus; /* reset entry status */
911 * Create File record in B_DB
913 * In order to reduce database size, we store the File attributes,
914 * the FileName, and the Path separately. In principle, there
915 * is a single FileName record and a single Path record, no matter
916 * how many times it occurs. This is this subroutine, we separate
917 * the file and the path and fill temporary tables with this three records.
919 * Note: all routines that call this expect to be able to call
920 * db_strerror(mdb) to get the error message, so the error message
921 * MUST be edited into mdb->errmsg before returning an error status.
923 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
925 ASSERT(ar->FileType != FT_BASE);
927 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
928 Dmsg0(dbglevel, "put_file_into_catalog\n");
930 /* Open the dedicated connexion */
931 if (!jcr->batch_started) {
932 if (!db_open_batch_connexion(jcr, mdb)) {
933 return false; /* error already printed */
935 if (!sql_batch_start(jcr, jcr->db_batch)) {
937 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
938 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
941 jcr->batch_started = true;
943 B_DB *bdb = jcr->db_batch;
945 split_path_and_file(jcr, bdb, ar->fname);
949 * if (bdb->changes > 100000) {
950 * db_write_batch_file_records(jcr);
952 * sql_batch_start(jcr, bdb);
956 return sql_batch_insert(jcr, bdb, ar);
959 #else /* ! HAVE_BATCH_FILE_INSERT */
962 * Create File record in B_DB
964 * In order to reduce database size, we store the File attributes,
965 * the FileName, and the Path separately. In principle, there
966 * is a single FileName record and a single Path record, no matter
967 * how many times it occurs. This is this subroutine, we separate
968 * the file and the path and create three database records.
970 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
973 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
974 Dmsg0(dbglevel, "put_file_into_catalog\n");
976 split_path_and_file(jcr, mdb, ar->fname);
978 if (!db_create_filename_record(jcr, mdb, ar)) {
981 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
984 if (!db_create_path_record(jcr, mdb, ar)) {
987 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
989 /* Now create master File record */
990 if (!db_create_file_record(jcr, mdb, ar)) {
993 Dmsg0(dbglevel, "db_create_file_record OK\n");
995 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
1006 * This is the master File entry containing the attributes.
1007 * The filename and path records have already been created.
1009 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1012 static const char *no_digest = "0";
1017 ASSERT(ar->FilenameId);
1019 if (ar->Digest == NULL || ar->Digest[0] == 0) {
1022 digest = ar->Digest;
1025 /* Must create it */
1027 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
1028 "LStat,MD5) VALUES (%u,%u,%u,%u,'%s','%s')",
1029 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
1032 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
1033 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
1034 mdb->cmd, sql_strerror(mdb));
1035 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1039 ar->FileId = sql_insert_id(mdb, NT_("File"));
1045 /* Create a Unique record for the filename -- no duplicates */
1046 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1050 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1051 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1053 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1055 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1056 mdb->num_rows = sql_num_rows(mdb);
1057 if (mdb->num_rows > 1) {
1059 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1060 edit_uint64(mdb->num_rows, ed1), mdb->fname);
1061 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1063 if (mdb->num_rows >= 1) {
1064 if ((row = sql_fetch_row(mdb)) == NULL) {
1065 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1066 mdb->fname, sql_strerror(mdb));
1067 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1070 ar->FilenameId = str_to_int64(row[0]);
1072 sql_free_result(mdb);
1073 return ar->FilenameId > 0;
1075 sql_free_result(mdb);
1078 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1080 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
1081 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1082 mdb->cmd, sql_strerror(mdb));
1083 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1086 ar->FilenameId = sql_insert_id(mdb, NT_("Filename"));
1088 return ar->FilenameId > 0;
1091 bool db_write_batch_file_records(JCR *jcr)
1096 #endif /* ! HAVE_BATCH_FILE_INSERT */
1099 /* List of SQL commands to create temp table and indicies */
1100 const char *create_temp_basefile[4] = {
1102 "CREATE TEMPORARY TABLE basefile%lld ("
1103 // "CREATE TABLE basefile%lld ("
1104 "Path BLOB NOT NULL,"
1105 "Name BLOB NOT NULL)",
1108 "CREATE TEMPORARY TABLE basefile%lld ("
1109 // "CREATE TABLE basefile%lld ("
1114 "CREATE TEMPORARY TABLE basefile%lld ("
1119 "CREATE TEMPORARY TABLE basefile%lld ("
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 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 */