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"));
228 Dmsg0(500, "Create Pool: done\n");
233 * Create Unique Device record
234 * Returns: false on failure
238 db_create_device_record(JCR *jcr, B_DB *mdb, DEVICE_DBR *dr)
241 char ed1[30], ed2[30];
243 Dmsg0(200, "In create Device\n");
245 Mmsg(mdb->cmd, "SELECT DeviceId,Name FROM Device WHERE Name='%s'", dr->Name);
246 Dmsg1(200, "selectdevice: %s\n", mdb->cmd);
248 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
249 mdb->num_rows = sql_num_rows(mdb);
250 if (mdb->num_rows > 0) {
251 Mmsg1(&mdb->errmsg, _("Device record %s already exists\n"), dr->Name);
252 sql_free_result(mdb);
256 sql_free_result(mdb);
261 "INSERT INTO Device (Name,MediaTypeId,StorageId) VALUES ('%s',%s,%s)",
263 edit_uint64(dr->MediaTypeId, ed1),
264 edit_int64(dr->StorageId, ed2));
265 Dmsg1(200, "Create Device: %s\n", mdb->cmd);
266 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
267 Mmsg2(&mdb->errmsg, _("Create db Device record %s failed: ERR=%s\n"),
268 mdb->cmd, sql_strerror(mdb));
272 dr->DeviceId = sql_insert_id(mdb, NT_("Device"));
282 * Create a Unique record for Storage -- no duplicates
283 * Returns: false on failure
284 * true on success with id in sr->StorageId
286 bool db_create_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
292 Mmsg(mdb->cmd, "SELECT StorageId,AutoChanger FROM Storage WHERE Name='%s'", sr->Name);
296 /* Check if it already exists */
297 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
298 mdb->num_rows = sql_num_rows(mdb);
299 /* If more than one, report error, but return first row */
300 if (mdb->num_rows > 1) {
301 Mmsg1(&mdb->errmsg, _("More than one Storage record!: %d\n"), (int)(mdb->num_rows));
302 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
304 if (mdb->num_rows >= 1) {
305 if ((row = sql_fetch_row(mdb)) == NULL) {
306 Mmsg1(&mdb->errmsg, _("error fetching Storage row: %s\n"), sql_strerror(mdb));
307 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
308 sql_free_result(mdb);
312 sr->StorageId = str_to_int64(row[0]);
313 sr->AutoChanger = atoi(row[1]); /* bool */
314 sql_free_result(mdb);
318 sql_free_result(mdb);
322 Mmsg(mdb->cmd, "INSERT INTO Storage (Name,AutoChanger)"
323 " VALUES ('%s',%d)", sr->Name, sr->AutoChanger);
325 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
326 Mmsg2(&mdb->errmsg, _("Create DB Storage record %s failed. ERR=%s\n"),
327 mdb->cmd, sql_strerror(mdb));
328 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
331 sr->StorageId = sql_insert_id(mdb, NT_("Storage"));
341 * Create Unique MediaType record
342 * Returns: false on failure
346 db_create_mediatype_record(JCR *jcr, B_DB *mdb, MEDIATYPE_DBR *mr)
350 Dmsg0(200, "In create mediatype\n");
352 Mmsg(mdb->cmd, "SELECT MediaTypeId,MediaType FROM MediaType WHERE MediaType='%s'", mr->MediaType);
353 Dmsg1(200, "selectmediatype: %s\n", mdb->cmd);
355 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
356 mdb->num_rows = sql_num_rows(mdb);
357 if (mdb->num_rows > 0) {
358 Mmsg1(&mdb->errmsg, _("mediatype record %s already exists\n"), mr->MediaType);
359 sql_free_result(mdb);
363 sql_free_result(mdb);
368 "INSERT INTO MediaType (MediaType,ReadOnly) "
372 Dmsg1(200, "Create mediatype: %s\n", mdb->cmd);
373 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
374 Mmsg2(&mdb->errmsg, _("Create db mediatype record %s failed: ERR=%s\n"),
375 mdb->cmd, sql_strerror(mdb));
379 mr->MediaTypeId = sql_insert_id(mdb, NT_("MediaType"));
388 * Create Media record. VolumeName and non-zero Slot must be unique
390 * Returns: 0 on failure
394 db_create_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
397 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50], ed7[50], ed8[50];
398 char ed9[50], ed10[50], ed11[50], ed12[50];
402 Mmsg(mdb->cmd, "SELECT MediaId FROM Media WHERE VolumeName='%s'",
404 Dmsg1(500, "selectpool: %s\n", mdb->cmd);
406 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
407 mdb->num_rows = sql_num_rows(mdb);
408 if (mdb->num_rows > 0) {
409 Mmsg1(&mdb->errmsg, _("Volume \"%s\" already exists.\n"), mr->VolumeName);
410 sql_free_result(mdb);
414 sql_free_result(mdb);
419 "INSERT INTO Media (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,"
420 "VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
421 "VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolParts,"
422 "EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,"
423 "ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge)"
424 "VALUES ('%s','%s',0,%u,%s,%s,%d,%s,%s,%u,%u,'%s',%d,%s,%d,%s,%s,%d,0,0,%d,%s,"
425 "%s,%s,%s,%s,%d,%d)",
427 mr->MediaType, mr->PoolId,
428 edit_uint64(mr->MaxVolBytes,ed1),
429 edit_uint64(mr->VolCapacityBytes, ed2),
431 edit_uint64(mr->VolRetention, ed3),
432 edit_uint64(mr->VolUseDuration, ed4),
437 edit_uint64(mr->VolBytes, ed5),
439 edit_int64(mr->VolReadTime, ed6),
440 edit_int64(mr->VolWriteTime, ed7),
443 edit_int64(mr->StorageId, ed8),
444 edit_int64(mr->DeviceId, ed9),
445 edit_int64(mr->LocationId, ed10),
446 edit_int64(mr->ScratchPoolId, ed11),
447 edit_int64(mr->RecyclePoolId, ed12),
448 mr->Enabled, mr->ActionOnPurge
452 Dmsg1(500, "Create Volume: %s\n", mdb->cmd);
453 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
454 Mmsg2(&mdb->errmsg, _("Create DB Media record %s failed. ERR=%s\n"),
455 mdb->cmd, sql_strerror(mdb));
458 mr->MediaId = sql_insert_id(mdb, NT_("Media"));
460 if (mr->set_label_date) {
461 char dt[MAX_TIME_LENGTH];
462 if (mr->LabelDate == 0) {
463 mr->LabelDate = time(NULL);
465 (void)localtime_r(&mr->LabelDate, &tm);
466 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
467 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
468 "WHERE MediaId=%d", dt, mr->MediaId);
469 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
472 * Make sure that if InChanger is non-zero any other identical slot
473 * has InChanger zero.
475 db_make_inchanger_unique(jcr, mdb, mr);
483 * Create a Unique record for the client -- no duplicates
484 * Returns: 0 on failure
485 * 1 on success with id in cr->ClientId
487 int db_create_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
491 char ed1[50], ed2[50];
494 Mmsg(mdb->cmd, "SELECT ClientId,Uname FROM Client WHERE Name='%s'", cr->Name);
497 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
498 mdb->num_rows = sql_num_rows(mdb);
499 /* If more than one, report error, but return first row */
500 if (mdb->num_rows > 1) {
501 Mmsg1(&mdb->errmsg, _("More than one Client!: %d\n"), (int)(mdb->num_rows));
502 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
504 if (mdb->num_rows >= 1) {
505 if ((row = sql_fetch_row(mdb)) == NULL) {
506 Mmsg1(&mdb->errmsg, _("error fetching Client row: %s\n"), sql_strerror(mdb));
507 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
508 sql_free_result(mdb);
512 cr->ClientId = str_to_int64(row[0]);
514 bstrncpy(cr->Uname, row[1], sizeof(cr->Uname));
516 cr->Uname[0] = 0; /* no name */
518 sql_free_result(mdb);
522 sql_free_result(mdb);
526 Mmsg(mdb->cmd, "INSERT INTO Client (Name,Uname,AutoPrune,"
527 "FileRetention,JobRetention) VALUES "
528 "('%s','%s',%d,%s,%s)", cr->Name, cr->Uname, cr->AutoPrune,
529 edit_uint64(cr->FileRetention, ed1),
530 edit_uint64(cr->JobRetention, ed2));
532 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
533 Mmsg2(&mdb->errmsg, _("Create DB Client record %s failed. ERR=%s\n"),
534 mdb->cmd, sql_strerror(mdb));
535 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
539 cr->ClientId = sql_insert_id(mdb, NT_("Client"));
547 /* Create a Unique record for the Path -- no duplicates */
548 int db_create_path_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
553 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
554 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
556 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
557 strcmp(mdb->cached_path, mdb->path) == 0) {
558 ar->PathId = mdb->cached_path_id;
562 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
564 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
565 mdb->num_rows = sql_num_rows(mdb);
566 if (mdb->num_rows > 1) {
568 Mmsg2(&mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
569 edit_uint64(mdb->num_rows, ed1), mdb->path);
570 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
572 /* Even if there are multiple paths, take the first one */
573 if (mdb->num_rows >= 1) {
574 if ((row = sql_fetch_row(mdb)) == NULL) {
575 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
576 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
577 sql_free_result(mdb);
582 ar->PathId = str_to_int64(row[0]);
583 sql_free_result(mdb);
585 if (ar->PathId != mdb->cached_path_id) {
586 mdb->cached_path_id = ar->PathId;
587 mdb->cached_path_len = mdb->pnl;
588 pm_strcpy(mdb->cached_path, mdb->path);
593 sql_free_result(mdb);
596 Mmsg(mdb->cmd, "INSERT INTO Path (Path) VALUES ('%s')", mdb->esc_name);
598 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
599 Mmsg2(&mdb->errmsg, _("Create db Path record %s failed. ERR=%s\n"),
600 mdb->cmd, sql_strerror(mdb));
601 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
605 ar->PathId = sql_insert_id(mdb, NT_("Path"));
610 if (stat && ar->PathId != mdb->cached_path_id) {
611 mdb->cached_path_id = ar->PathId;
612 mdb->cached_path_len = mdb->pnl;
613 pm_strcpy(mdb->cached_path, mdb->path);
619 * Create a Unique record for the counter -- no duplicates
620 * Returns: 0 on failure
621 * 1 on success with counter filled in
623 int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
629 memset(&mcr, 0, sizeof(mcr));
630 bstrncpy(mcr.Counter, cr->Counter, sizeof(mcr.Counter));
631 if (db_get_counter_record(jcr, mdb, &mcr)) {
632 memcpy(cr, &mcr, sizeof(COUNTER_DBR));
638 Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,MinValue,MaxValue,CurrentValue,"
639 "WrapCounter) VALUES ('%s','%d','%d','%d','%s')",
640 cr->Counter, cr->MinValue, cr->MaxValue, cr->CurrentValue,
643 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
644 Mmsg2(&mdb->errmsg, _("Create DB Counters record %s failed. ERR=%s\n"),
645 mdb->cmd, sql_strerror(mdb));
646 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
657 * Create a FileSet record. This record is unique in the
658 * name and the MD5 signature of the include/exclude sets.
659 * Returns: 0 on failure
660 * 1 on success with FileSetId in record
662 bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
669 fsr->created = false;
670 Mmsg(mdb->cmd, "SELECT FileSetId,CreateTime FROM FileSet WHERE "
671 "FileSet='%s' AND MD5='%s'", fsr->FileSet, fsr->MD5);
674 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
675 mdb->num_rows = sql_num_rows(mdb);
676 if (mdb->num_rows > 1) {
677 Mmsg1(&mdb->errmsg, _("More than one FileSet!: %d\n"), (int)(mdb->num_rows));
678 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
680 if (mdb->num_rows >= 1) {
681 if ((row = sql_fetch_row(mdb)) == NULL) {
682 Mmsg1(&mdb->errmsg, _("error fetching FileSet row: ERR=%s\n"), sql_strerror(mdb));
683 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
684 sql_free_result(mdb);
688 fsr->FileSetId = str_to_int64(row[0]);
689 if (row[1] == NULL) {
690 fsr->cCreateTime[0] = 0;
692 bstrncpy(fsr->cCreateTime, row[1], sizeof(fsr->cCreateTime));
694 sql_free_result(mdb);
698 sql_free_result(mdb);
701 if (fsr->CreateTime == 0 && fsr->cCreateTime[0] == 0) {
702 fsr->CreateTime = time(NULL);
704 (void)localtime_r(&fsr->CreateTime, &tm);
705 strftime(fsr->cCreateTime, sizeof(fsr->cCreateTime), "%Y-%m-%d %H:%M:%S", &tm);
708 Mmsg(mdb->cmd, "INSERT INTO FileSet (FileSet,MD5,CreateTime) "
709 "VALUES ('%s','%s','%s')", fsr->FileSet, fsr->MD5, fsr->cCreateTime);
711 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
712 Mmsg2(&mdb->errmsg, _("Create DB FileSet record %s failed. ERR=%s\n"),
713 mdb->cmd, sql_strerror(mdb));
714 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
718 fsr->FileSetId = sql_insert_id(mdb, NT_("FileSet"));
731 * dev_t st_dev; * device *
732 * ino_t st_ino; * inode *
733 * mode_t st_mode; * protection *
734 * nlink_t st_nlink; * number of hard links *
735 * uid_t st_uid; * user ID of owner *
736 * gid_t st_gid; * group ID of owner *
737 * dev_t st_rdev; * device type (if inode device) *
738 * off_t st_size; * total size, in bytes *
739 * unsigned long st_blksize; * blocksize for filesystem I/O *
740 * unsigned long st_blocks; * number of blocks allocated *
741 * time_t st_atime; * time of last access *
742 * time_t st_mtime; * time of last modification *
743 * time_t st_ctime; * time of last inode change *
747 #ifdef HAVE_BATCH_FILE_INSERT
749 /* All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
750 * tables. This code can be activated by adding "#define HAVE_BATCH_FILE_INSERT 1"
754 * - bulk load a temp table
755 * - insert missing filenames into filename with a single query (lock filenames
756 * - table before that to avoid possible duplicate inserts with concurrent update)
757 * - insert missing paths into path with another single query
758 * - then insert the join between the temp, filename and path tables into file.
765 bool my_batch_start(JCR *jcr, B_DB *mdb)
770 ok = db_sql_query(mdb,
771 "CREATE TEMPORARY TABLE batch ("
777 "MD5 tinyblob)",NULL, NULL);
786 bool my_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
792 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
793 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
795 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
796 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
798 if (ar->Digest == NULL || ar->Digest[0] == 0) {
804 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
805 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
806 mdb->esc_name, ar->attr, digest);
808 return INSERT_DB(jcr, mdb, mdb->cmd);
811 /* set error to something to abort operation */
816 bool my_batch_end(JCR *jcr, B_DB *mdb, const char *error)
819 Dmsg0(50, "sql_batch_end started\n");
823 mdb->status = (dbi_error_flag)0;
835 bool db_write_batch_file_records(JCR *jcr)
837 int JobStatus = jcr->JobStatus;
839 if (!jcr->batch_started) { /* no files to backup ? */
840 Dmsg0(50,"db_create_file_record : no files\n");
843 if (job_canceled(jcr)) {
847 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
849 jcr->JobStatus = JS_AttrInserting;
850 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
851 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
854 if (job_canceled(jcr)) {
859 /* we have to lock tables */
860 if (!db_sql_query(jcr->db_batch, sql_batch_lock_path_query, NULL, NULL)) {
861 Jmsg1(jcr, M_FATAL, 0, "Lock Path table %s\n", jcr->db_batch->errmsg);
865 if (!db_sql_query(jcr->db_batch, sql_batch_fill_path_query, NULL, NULL)) {
866 Jmsg1(jcr, M_FATAL, 0, "Fill Path table %s\n",jcr->db_batch->errmsg);
867 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
871 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
872 Jmsg1(jcr, M_FATAL, 0, "Unlock Path table %s\n", jcr->db_batch->errmsg);
876 /* we have to lock tables */
877 if (!db_sql_query(jcr->db_batch,sql_batch_lock_filename_query,NULL, NULL)) {
878 Jmsg1(jcr, M_FATAL, 0, "Lock Filename table %s\n", jcr->db_batch->errmsg);
882 if (!db_sql_query(jcr->db_batch,sql_batch_fill_filename_query, NULL,NULL)) {
883 Jmsg1(jcr,M_FATAL,0,"Fill Filename table %s\n",jcr->db_batch->errmsg);
884 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
888 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
889 Jmsg1(jcr, M_FATAL, 0, "Unlock Filename table %s\n", jcr->db_batch->errmsg);
893 if (!db_sql_query(jcr->db_batch,
894 "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)"
895 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
896 "Filename.FilenameId,batch.LStat, batch.MD5 "
898 "JOIN Path ON (batch.Path = Path.Path) "
899 "JOIN Filename ON (batch.Name = Filename.Name)",
902 Jmsg1(jcr, M_FATAL, 0, "Fill File table %s\n", jcr->db_batch->errmsg);
906 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
908 jcr->JobStatus = JobStatus; /* reset entry status */
913 * Create File record in B_DB
915 * In order to reduce database size, we store the File attributes,
916 * the FileName, and the Path separately. In principle, there
917 * is a single FileName record and a single Path record, no matter
918 * how many times it occurs. This is this subroutine, we separate
919 * the file and the path and fill temporary tables with this three records.
921 * Note: all routines that call this expect to be able to call
922 * db_strerror(mdb) to get the error message, so the error message
923 * MUST be edited into mdb->errmsg before returning an error status.
925 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
927 ASSERT(ar->FileType != FT_BASE);
929 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
930 Dmsg0(dbglevel, "put_file_into_catalog\n");
932 /* Open the dedicated connexion */
933 if (!jcr->batch_started) {
934 if (!db_open_batch_connexion(jcr, mdb)) {
935 return false; /* error already printed */
937 if (!sql_batch_start(jcr, jcr->db_batch)) {
939 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
940 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
943 jcr->batch_started = true;
945 B_DB *bdb = jcr->db_batch;
947 split_path_and_file(jcr, bdb, ar->fname);
951 * if (bdb->changes > 100000) {
952 * db_write_batch_file_records(jcr);
954 * sql_batch_start(jcr, bdb);
958 return sql_batch_insert(jcr, bdb, ar);
961 #else /* ! HAVE_BATCH_FILE_INSERT */
964 * Create File record in B_DB
966 * In order to reduce database size, we store the File attributes,
967 * the FileName, and the Path separately. In principle, there
968 * is a single FileName record and a single Path record, no matter
969 * how many times it occurs. This is this subroutine, we separate
970 * the file and the path and create three database records.
972 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
975 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
976 Dmsg0(dbglevel, "put_file_into_catalog\n");
978 split_path_and_file(jcr, mdb, ar->fname);
980 if (!db_create_filename_record(jcr, mdb, ar)) {
983 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
986 if (!db_create_path_record(jcr, mdb, ar)) {
989 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
991 /* Now create master File record */
992 if (!db_create_file_record(jcr, mdb, ar)) {
995 Dmsg0(dbglevel, "db_create_file_record OK\n");
997 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
1008 * This is the master File entry containing the attributes.
1009 * The filename and path records have already been created.
1011 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1014 static const char *no_digest = "0";
1019 ASSERT(ar->FilenameId);
1021 if (ar->Digest == NULL || ar->Digest[0] == 0) {
1024 digest = ar->Digest;
1027 /* Must create it */
1029 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
1030 "LStat,MD5) VALUES (%u,%u,%u,%u,'%s','%s')",
1031 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
1034 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
1035 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
1036 mdb->cmd, sql_strerror(mdb));
1037 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1041 ar->FileId = sql_insert_id(mdb, NT_("File"));
1047 /* Create a Unique record for the filename -- no duplicates */
1048 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1052 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1053 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1055 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1057 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1058 mdb->num_rows = sql_num_rows(mdb);
1059 if (mdb->num_rows > 1) {
1061 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1062 edit_uint64(mdb->num_rows, ed1), mdb->fname);
1063 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1065 if (mdb->num_rows >= 1) {
1066 if ((row = sql_fetch_row(mdb)) == NULL) {
1067 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1068 mdb->fname, sql_strerror(mdb));
1069 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1072 ar->FilenameId = str_to_int64(row[0]);
1074 sql_free_result(mdb);
1075 return ar->FilenameId > 0;
1077 sql_free_result(mdb);
1080 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1082 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
1083 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1084 mdb->cmd, sql_strerror(mdb));
1085 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1088 ar->FilenameId = sql_insert_id(mdb, NT_("Filename"));
1090 return ar->FilenameId > 0;
1093 bool db_write_batch_file_records(JCR *jcr)
1098 #endif /* ! HAVE_BATCH_FILE_INSERT */
1101 /* List of SQL commands to create temp table and indicies */
1102 const char *create_temp_basefile[5] = {
1104 "CREATE TEMPORARY TABLE basefile%lld ("
1105 // "CREATE TABLE basefile%lld ("
1106 "Path BLOB NOT NULL,"
1107 "Name BLOB NOT NULL)",
1110 "CREATE TEMPORARY TABLE basefile%lld ("
1111 // "CREATE TABLE basefile%lld ("
1116 "CREATE TEMPORARY TABLE basefile%lld ("
1121 "CREATE TEMPORARY TABLE basefile%lld ("
1126 "DECLARE GLOBAL TEMPORARY TABLE basefile%lld ("
1127 "Path TEXT NOT NULL,"
1128 "Name TEXT NOT NULL)"
1132 * Create file attributes record, or base file attributes record
1134 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1139 * Make sure we have an acceptable attributes record.
1141 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1142 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1143 Jmsg(jcr, M_FATAL, 0, _("Attempt to put non-attributes into catalog. Stream=%d\n"));
1147 if (ar->FileType != FT_BASE) {
1148 ret = db_create_file_attributes_record(jcr, mdb, ar);
1150 } else if (jcr->HasBase) {
1151 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1154 Jmsg0(jcr, M_FATAL, 0, _("Can't Copy/Migrate job using BaseJob"));
1155 ret = true; /* in copy/migration what do we do ? */
1162 * Create Base File record in B_DB
1165 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1168 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1169 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1172 split_path_and_file(jcr, mdb, ar->fname);
1174 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1175 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1177 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1178 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1180 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1181 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1183 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1190 * Cleanup the base file temporary tables
1192 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1194 POOL_MEM buf(PM_MESSAGE);
1195 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1196 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1198 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1199 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1203 * Put all base file seen in the backup to the BaseFile table
1204 * and cleanup temporary tables
1206 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1214 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1215 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1216 "B.FileId, B.FileIndex "
1217 "FROM basefile%s AS A, new_basefile%s AS B "
1218 "WHERE A.Path = B.Path "
1219 "AND A.Name = B.Name "
1220 "ORDER BY B.FileId",
1221 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1222 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1223 jcr->nb_base_files_used = sql_affected_rows(mdb);
1224 db_cleanup_base_file(jcr, mdb);
1231 * Find the last "accurate" backup state with Base jobs
1232 * 1) Get all files with jobid in list (F subquery)
1233 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1234 * 3) Put the result in a temporary table for the end of job
1237 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1245 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1249 Mmsg(mdb->cmd, create_temp_basefile[db_type], (uint64_t) jcr->JobId);
1250 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1253 Mmsg(buf, select_recent_version[db_type], jobids, jobids);
1255 "CREATE TEMPORARY TABLE new_basefile%lld AS "
1256 //"CREATE TABLE new_basefile%lld AS "
1257 "SELECT Path.Path AS Path, Filename.Name AS Name, Temp.FileIndex AS FileIndex,"
1258 "Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId, "
1260 "FROM ( %s ) AS Temp "
1261 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1262 "JOIN Path ON (Path.PathId = Temp.PathId) "
1263 "WHERE Temp.FileIndex > 0",
1264 (uint64_t)jcr->JobId, buf.c_str());
1266 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1272 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */