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 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
297 mdb->num_rows = sql_num_rows(mdb);
298 /* If more than one, report error, but return first row */
299 if (mdb->num_rows > 1) {
300 Mmsg1(&mdb->errmsg, _("More than one Storage record!: %d\n"), (int)(mdb->num_rows));
301 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
303 if (mdb->num_rows >= 1) {
304 if ((row = sql_fetch_row(mdb)) == NULL) {
305 Mmsg1(&mdb->errmsg, _("error fetching Storage row: %s\n"), sql_strerror(mdb));
306 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
307 sql_free_result(mdb);
311 sr->StorageId = str_to_int64(row[0]);
312 sr->AutoChanger = atoi(row[1]); /* bool */
313 sql_free_result(mdb);
317 sql_free_result(mdb);
321 Mmsg(mdb->cmd, "INSERT INTO Storage (Name,AutoChanger)"
322 " VALUES ('%s',%d)", sr->Name, sr->AutoChanger);
324 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
325 Mmsg2(&mdb->errmsg, _("Create DB Storage record %s failed. ERR=%s\n"),
326 mdb->cmd, sql_strerror(mdb));
327 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
330 sr->StorageId = sql_insert_id(mdb, NT_("Storage"));
340 * Create Unique MediaType record
341 * Returns: false on failure
345 db_create_mediatype_record(JCR *jcr, B_DB *mdb, MEDIATYPE_DBR *mr)
349 Dmsg0(200, "In create mediatype\n");
351 Mmsg(mdb->cmd, "SELECT MediaTypeId,MediaType FROM MediaType WHERE MediaType='%s'", mr->MediaType);
352 Dmsg1(200, "selectmediatype: %s\n", mdb->cmd);
354 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
355 mdb->num_rows = sql_num_rows(mdb);
356 if (mdb->num_rows > 0) {
357 Mmsg1(&mdb->errmsg, _("mediatype record %s already exists\n"), mr->MediaType);
358 sql_free_result(mdb);
362 sql_free_result(mdb);
367 "INSERT INTO MediaType (MediaType,ReadOnly) "
371 Dmsg1(200, "Create mediatype: %s\n", mdb->cmd);
372 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
373 Mmsg2(&mdb->errmsg, _("Create db mediatype record %s failed: ERR=%s\n"),
374 mdb->cmd, sql_strerror(mdb));
378 mr->MediaTypeId = sql_insert_id(mdb, NT_("MediaType"));
387 * Create Media record. VolumeName and non-zero Slot must be unique
389 * Returns: 0 on failure
393 db_create_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
396 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50], ed7[50], ed8[50];
397 char ed9[50], ed10[50], ed11[50], ed12[50];
401 Mmsg(mdb->cmd, "SELECT MediaId FROM Media WHERE VolumeName='%s'",
403 Dmsg1(500, "selectpool: %s\n", mdb->cmd);
405 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
406 mdb->num_rows = sql_num_rows(mdb);
407 if (mdb->num_rows > 0) {
408 Mmsg1(&mdb->errmsg, _("Volume \"%s\" already exists.\n"), mr->VolumeName);
409 sql_free_result(mdb);
413 sql_free_result(mdb);
418 "INSERT INTO Media (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,"
419 "VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
420 "VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolParts,"
421 "EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,"
422 "ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge)"
423 "VALUES ('%s','%s',0,%u,%s,%s,%d,%s,%s,%u,%u,'%s',%d,%s,%d,%s,%s,%d,0,0,%d,%s,"
424 "%s,%s,%s,%s,%d,%d)",
426 mr->MediaType, mr->PoolId,
427 edit_uint64(mr->MaxVolBytes,ed1),
428 edit_uint64(mr->VolCapacityBytes, ed2),
430 edit_uint64(mr->VolRetention, ed3),
431 edit_uint64(mr->VolUseDuration, ed4),
436 edit_uint64(mr->VolBytes, ed5),
438 edit_int64(mr->VolReadTime, ed6),
439 edit_int64(mr->VolWriteTime, ed7),
442 edit_int64(mr->StorageId, ed8),
443 edit_int64(mr->DeviceId, ed9),
444 edit_int64(mr->LocationId, ed10),
445 edit_int64(mr->ScratchPoolId, ed11),
446 edit_int64(mr->RecyclePoolId, ed12),
447 mr->Enabled, mr->ActionOnPurge
451 Dmsg1(500, "Create Volume: %s\n", mdb->cmd);
452 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
453 Mmsg2(&mdb->errmsg, _("Create DB Media record %s failed. ERR=%s\n"),
454 mdb->cmd, sql_strerror(mdb));
457 mr->MediaId = sql_insert_id(mdb, NT_("Media"));
459 if (mr->set_label_date) {
460 char dt[MAX_TIME_LENGTH];
461 if (mr->LabelDate == 0) {
462 mr->LabelDate = time(NULL);
464 (void)localtime_r(&mr->LabelDate, &tm);
465 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
466 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
467 "WHERE MediaId=%d", dt, mr->MediaId);
468 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
471 * Make sure that if InChanger is non-zero any other identical slot
472 * has InChanger zero.
474 db_make_inchanger_unique(jcr, mdb, mr);
482 * Create a Unique record for the client -- no duplicates
483 * Returns: 0 on failure
484 * 1 on success with id in cr->ClientId
486 int db_create_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
490 char ed1[50], ed2[50];
493 Mmsg(mdb->cmd, "SELECT ClientId,Uname FROM Client WHERE Name='%s'", cr->Name);
496 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
497 mdb->num_rows = sql_num_rows(mdb);
498 /* If more than one, report error, but return first row */
499 if (mdb->num_rows > 1) {
500 Mmsg1(&mdb->errmsg, _("More than one Client!: %d\n"), (int)(mdb->num_rows));
501 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
503 if (mdb->num_rows >= 1) {
504 if ((row = sql_fetch_row(mdb)) == NULL) {
505 Mmsg1(&mdb->errmsg, _("error fetching Client row: %s\n"), sql_strerror(mdb));
506 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
507 sql_free_result(mdb);
511 cr->ClientId = str_to_int64(row[0]);
513 bstrncpy(cr->Uname, row[1], sizeof(cr->Uname));
515 cr->Uname[0] = 0; /* no name */
517 sql_free_result(mdb);
521 sql_free_result(mdb);
525 Mmsg(mdb->cmd, "INSERT INTO Client (Name,Uname,AutoPrune,"
526 "FileRetention,JobRetention) VALUES "
527 "('%s','%s',%d,%s,%s)", cr->Name, cr->Uname, cr->AutoPrune,
528 edit_uint64(cr->FileRetention, ed1),
529 edit_uint64(cr->JobRetention, ed2));
531 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
532 Mmsg2(&mdb->errmsg, _("Create DB Client record %s failed. ERR=%s\n"),
533 mdb->cmd, sql_strerror(mdb));
534 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
538 cr->ClientId = sql_insert_id(mdb, NT_("Client"));
546 /* Create a Unique record for the Path -- no duplicates */
547 int db_create_path_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
552 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
553 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
555 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
556 strcmp(mdb->cached_path, mdb->path) == 0) {
557 ar->PathId = mdb->cached_path_id;
561 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
563 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
564 mdb->num_rows = sql_num_rows(mdb);
565 if (mdb->num_rows > 1) {
567 Mmsg2(&mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
568 edit_uint64(mdb->num_rows, ed1), mdb->path);
569 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
571 /* Even if there are multiple paths, take the first one */
572 if (mdb->num_rows >= 1) {
573 if ((row = sql_fetch_row(mdb)) == NULL) {
574 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
575 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
576 sql_free_result(mdb);
581 ar->PathId = str_to_int64(row[0]);
582 sql_free_result(mdb);
584 if (ar->PathId != mdb->cached_path_id) {
585 mdb->cached_path_id = ar->PathId;
586 mdb->cached_path_len = mdb->pnl;
587 pm_strcpy(mdb->cached_path, mdb->path);
592 sql_free_result(mdb);
595 Mmsg(mdb->cmd, "INSERT INTO Path (Path) VALUES ('%s')", mdb->esc_name);
597 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
598 Mmsg2(&mdb->errmsg, _("Create db Path record %s failed. ERR=%s\n"),
599 mdb->cmd, sql_strerror(mdb));
600 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
604 ar->PathId = sql_insert_id(mdb, NT_("Path"));
609 if (stat && ar->PathId != mdb->cached_path_id) {
610 mdb->cached_path_id = ar->PathId;
611 mdb->cached_path_len = mdb->pnl;
612 pm_strcpy(mdb->cached_path, mdb->path);
618 * Create a Unique record for the counter -- no duplicates
619 * Returns: 0 on failure
620 * 1 on success with counter filled in
622 int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
628 memset(&mcr, 0, sizeof(mcr));
629 bstrncpy(mcr.Counter, cr->Counter, sizeof(mcr.Counter));
630 if (db_get_counter_record(jcr, mdb, &mcr)) {
631 memcpy(cr, &mcr, sizeof(COUNTER_DBR));
637 Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,MinValue,MaxValue,CurrentValue,"
638 "WrapCounter) VALUES ('%s','%d','%d','%d','%s')",
639 cr->Counter, cr->MinValue, cr->MaxValue, cr->CurrentValue,
642 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
643 Mmsg2(&mdb->errmsg, _("Create DB Counters record %s failed. ERR=%s\n"),
644 mdb->cmd, sql_strerror(mdb));
645 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
656 * Create a FileSet record. This record is unique in the
657 * name and the MD5 signature of the include/exclude sets.
658 * Returns: 0 on failure
659 * 1 on success with FileSetId in record
661 bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
668 fsr->created = false;
669 Mmsg(mdb->cmd, "SELECT FileSetId,CreateTime FROM FileSet WHERE "
670 "FileSet='%s' AND MD5='%s'", fsr->FileSet, fsr->MD5);
673 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
674 mdb->num_rows = sql_num_rows(mdb);
675 if (mdb->num_rows > 1) {
676 Mmsg1(&mdb->errmsg, _("More than one FileSet!: %d\n"), (int)(mdb->num_rows));
677 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
679 if (mdb->num_rows >= 1) {
680 if ((row = sql_fetch_row(mdb)) == NULL) {
681 Mmsg1(&mdb->errmsg, _("error fetching FileSet row: ERR=%s\n"), sql_strerror(mdb));
682 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
683 sql_free_result(mdb);
687 fsr->FileSetId = str_to_int64(row[0]);
688 if (row[1] == NULL) {
689 fsr->cCreateTime[0] = 0;
691 bstrncpy(fsr->cCreateTime, row[1], sizeof(fsr->cCreateTime));
693 sql_free_result(mdb);
697 sql_free_result(mdb);
700 if (fsr->CreateTime == 0 && fsr->cCreateTime[0] == 0) {
701 fsr->CreateTime = time(NULL);
703 (void)localtime_r(&fsr->CreateTime, &tm);
704 strftime(fsr->cCreateTime, sizeof(fsr->cCreateTime), "%Y-%m-%d %H:%M:%S", &tm);
707 Mmsg(mdb->cmd, "INSERT INTO FileSet (FileSet,MD5,CreateTime) "
708 "VALUES ('%s','%s','%s')", fsr->FileSet, fsr->MD5, fsr->cCreateTime);
710 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
711 Mmsg2(&mdb->errmsg, _("Create DB FileSet record %s failed. ERR=%s\n"),
712 mdb->cmd, sql_strerror(mdb));
713 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
717 fsr->FileSetId = sql_insert_id(mdb, NT_("FileSet"));
730 * dev_t st_dev; * device *
731 * ino_t st_ino; * inode *
732 * mode_t st_mode; * protection *
733 * nlink_t st_nlink; * number of hard links *
734 * uid_t st_uid; * user ID of owner *
735 * gid_t st_gid; * group ID of owner *
736 * dev_t st_rdev; * device type (if inode device) *
737 * off_t st_size; * total size, in bytes *
738 * unsigned long st_blksize; * blocksize for filesystem I/O *
739 * unsigned long st_blocks; * number of blocks allocated *
740 * time_t st_atime; * time of last access *
741 * time_t st_mtime; * time of last modification *
742 * time_t st_ctime; * time of last inode change *
746 #ifdef HAVE_BATCH_FILE_INSERT
748 /* All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
749 * tables. This code can be activated by adding "#define HAVE_BATCH_FILE_INSERT 1"
753 * - bulk load a temp table
754 * - insert missing filenames into filename with a single query (lock filenames
755 * - table before that to avoid possible duplicate inserts with concurrent update)
756 * - insert missing paths into path with another single query
757 * - then insert the join between the temp, filename and path tables into file.
764 bool my_batch_start(JCR *jcr, B_DB *mdb)
769 ok = db_sql_query(mdb,
770 "CREATE TEMPORARY TABLE batch ("
776 "MD5 tinyblob)",NULL, NULL);
785 bool my_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
791 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
792 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
794 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
795 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
797 if (ar->Digest == NULL || ar->Digest[0] == 0) {
803 len = Mmsg(mdb->cmd, "INSERT INTO batch VALUES (%u,%s,'%s','%s','%s','%s')",
804 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
805 mdb->esc_name, ar->attr, digest);
807 return INSERT_DB(jcr, mdb, mdb->cmd);
810 /* set error to something to abort operation */
815 bool my_batch_end(JCR *jcr, B_DB *mdb, const char *error)
818 Dmsg0(50, "sql_batch_end started\n");
822 mdb->status = (dbi_error_flag)0;
834 bool db_write_batch_file_records(JCR *jcr)
836 int JobStatus = jcr->JobStatus;
838 if (!jcr->batch_started) { /* no files to backup ? */
839 Dmsg0(50,"db_create_file_record : no files\n");
842 if (job_canceled(jcr)) {
846 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
848 jcr->JobStatus = JS_AttrInserting;
849 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
850 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
853 if (job_canceled(jcr)) {
858 /* we have to lock tables */
859 if (!db_sql_query(jcr->db_batch, sql_batch_lock_path_query, NULL, NULL)) {
860 Jmsg1(jcr, M_FATAL, 0, "Lock Path table %s\n", jcr->db_batch->errmsg);
864 if (!db_sql_query(jcr->db_batch, sql_batch_fill_path_query, NULL, NULL)) {
865 Jmsg1(jcr, M_FATAL, 0, "Fill Path table %s\n",jcr->db_batch->errmsg);
866 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
870 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
871 Jmsg1(jcr, M_FATAL, 0, "Unlock Path table %s\n", jcr->db_batch->errmsg);
875 /* we have to lock tables */
876 if (!db_sql_query(jcr->db_batch,sql_batch_lock_filename_query,NULL, NULL)) {
877 Jmsg1(jcr, M_FATAL, 0, "Lock Filename table %s\n", jcr->db_batch->errmsg);
881 if (!db_sql_query(jcr->db_batch,sql_batch_fill_filename_query, NULL,NULL)) {
882 Jmsg1(jcr,M_FATAL,0,"Fill Filename table %s\n",jcr->db_batch->errmsg);
883 db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
887 if (!db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query,NULL,NULL)) {
888 Jmsg1(jcr, M_FATAL, 0, "Unlock Filename table %s\n", jcr->db_batch->errmsg);
892 if (!db_sql_query(jcr->db_batch,
893 "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)"
894 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
895 "Filename.FilenameId,batch.LStat, batch.MD5 "
897 "JOIN Path ON (batch.Path = Path.Path) "
898 "JOIN Filename ON (batch.Name = Filename.Name)",
901 Jmsg1(jcr, M_FATAL, 0, "Fill File table %s\n", jcr->db_batch->errmsg);
905 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
907 jcr->JobStatus = JobStatus; /* reset entry status */
912 * Create File record in B_DB
914 * In order to reduce database size, we store the File attributes,
915 * the FileName, and the Path separately. In principle, there
916 * is a single FileName record and a single Path record, no matter
917 * how many times it occurs. This is this subroutine, we separate
918 * the file and the path and fill temporary tables with this three records.
920 * Note: all routines that call this expect to be able to call
921 * db_strerror(mdb) to get the error message, so the error message
922 * MUST be edited into mdb->errmsg before returning an error status.
924 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
926 ASSERT(ar->FileType != FT_BASE);
928 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
929 Dmsg0(dbglevel, "put_file_into_catalog\n");
931 /* Open the dedicated connexion */
932 if (!jcr->batch_started) {
933 if (!db_open_batch_connexion(jcr, mdb)) {
934 return false; /* error already printed */
936 if (!sql_batch_start(jcr, jcr->db_batch)) {
938 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
939 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
942 jcr->batch_started = true;
944 B_DB *bdb = jcr->db_batch;
946 split_path_and_file(jcr, bdb, ar->fname);
950 * if (bdb->changes > 100000) {
951 * db_write_batch_file_records(jcr);
953 * sql_batch_start(jcr, bdb);
957 return sql_batch_insert(jcr, bdb, ar);
960 #else /* ! HAVE_BATCH_FILE_INSERT */
963 * Create File record in B_DB
965 * In order to reduce database size, we store the File attributes,
966 * the FileName, and the Path separately. In principle, there
967 * is a single FileName record and a single Path record, no matter
968 * how many times it occurs. This is this subroutine, we separate
969 * the file and the path and create three database records.
971 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
974 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
975 Dmsg0(dbglevel, "put_file_into_catalog\n");
977 split_path_and_file(jcr, mdb, ar->fname);
979 if (!db_create_filename_record(jcr, mdb, ar)) {
982 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
985 if (!db_create_path_record(jcr, mdb, ar)) {
988 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
990 /* Now create master File record */
991 if (!db_create_file_record(jcr, mdb, ar)) {
994 Dmsg0(dbglevel, "db_create_file_record OK\n");
996 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
1007 * This is the master File entry containing the attributes.
1008 * The filename and path records have already been created.
1010 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1013 static const char *no_digest = "0";
1018 ASSERT(ar->FilenameId);
1020 if (ar->Digest == NULL || ar->Digest[0] == 0) {
1023 digest = ar->Digest;
1026 /* Must create it */
1028 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
1029 "LStat,MD5) VALUES (%u,%u,%u,%u,'%s','%s')",
1030 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
1033 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
1034 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
1035 mdb->cmd, sql_strerror(mdb));
1036 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1040 ar->FileId = sql_insert_id(mdb, NT_("File"));
1046 /* Create a Unique record for the filename -- no duplicates */
1047 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1051 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1052 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1054 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1056 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1057 mdb->num_rows = sql_num_rows(mdb);
1058 if (mdb->num_rows > 1) {
1060 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1061 edit_uint64(mdb->num_rows, ed1), mdb->fname);
1062 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1064 if (mdb->num_rows >= 1) {
1065 if ((row = sql_fetch_row(mdb)) == NULL) {
1066 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1067 mdb->fname, sql_strerror(mdb));
1068 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1071 ar->FilenameId = str_to_int64(row[0]);
1073 sql_free_result(mdb);
1074 return ar->FilenameId > 0;
1076 sql_free_result(mdb);
1079 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1081 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
1082 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1083 mdb->cmd, sql_strerror(mdb));
1084 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1087 ar->FilenameId = sql_insert_id(mdb, NT_("Filename"));
1089 return ar->FilenameId > 0;
1092 bool db_write_batch_file_records(JCR *jcr)
1097 #endif /* ! HAVE_BATCH_FILE_INSERT */
1100 /* List of SQL commands to create temp table and indicies */
1101 const char *create_temp_basefile[5] = {
1103 "CREATE TEMPORARY TABLE basefile%lld ("
1104 // "CREATE TABLE basefile%lld ("
1105 "Path BLOB NOT NULL,"
1106 "Name BLOB NOT NULL)",
1109 "CREATE TEMPORARY TABLE basefile%lld ("
1110 // "CREATE TABLE basefile%lld ("
1115 "CREATE TEMPORARY TABLE basefile%lld ("
1120 "CREATE TEMPORARY TABLE basefile%lld ("
1125 "DECLARE GLOBAL TEMPORARY TABLE basefile%lld ("
1126 "Path TEXT NOT NULL,"
1127 "Name TEXT NOT NULL)"
1131 * Create file attributes record, or base file attributes record
1133 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1138 * Make sure we have an acceptable attributes record.
1140 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1141 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1142 Jmsg(jcr, M_FATAL, 0, _("Attempt to put non-attributes into catalog. Stream=%d\n"));
1146 if (ar->FileType != FT_BASE) {
1147 ret = db_create_file_attributes_record(jcr, mdb, ar);
1149 } else if (jcr->HasBase) {
1150 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1153 Jmsg0(jcr, M_FATAL, 0, _("Can't Copy/Migrate job using BaseJob"));
1154 ret = true; /* in copy/migration what do we do ? */
1161 * Create Base File record in B_DB
1164 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1167 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1168 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1171 split_path_and_file(jcr, mdb, ar->fname);
1173 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1174 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1176 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1177 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1179 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1180 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1182 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1189 * Cleanup the base file temporary tables
1191 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1193 POOL_MEM buf(PM_MESSAGE);
1194 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1195 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1197 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1198 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1202 * Put all base file seen in the backup to the BaseFile table
1203 * and cleanup temporary tables
1205 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1213 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1214 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1215 "B.FileId, B.FileIndex "
1216 "FROM basefile%s AS A, new_basefile%s AS B "
1217 "WHERE A.Path = B.Path "
1218 "AND A.Name = B.Name "
1219 "ORDER BY B.FileId",
1220 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1221 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1222 jcr->nb_base_files_used = sql_affected_rows(mdb);
1223 db_cleanup_base_file(jcr, mdb);
1230 * Find the last "accurate" backup state with Base jobs
1231 * 1) Get all files with jobid in list (F subquery)
1232 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1233 * 3) Put the result in a temporary table for the end of job
1236 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1244 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1248 Mmsg(mdb->cmd, create_temp_basefile[db_type], (uint64_t) jcr->JobId);
1249 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1252 Mmsg(buf, select_recent_version[db_type], jobids, jobids);
1254 "CREATE TEMPORARY TABLE new_basefile%lld AS "
1255 //"CREATE TABLE new_basefile%lld AS "
1256 "SELECT Path.Path AS Path, Filename.Name AS Name, Temp.FileIndex AS FileIndex,"
1257 "Temp.JobId AS JobId, Temp.LStat AS LStat, Temp.FileId AS FileId, "
1259 "FROM ( %s ) AS Temp "
1260 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1261 "JOIN Path ON (Path.PathId = Temp.PathId) "
1262 "WHERE Temp.FileIndex > 0",
1263 (uint64_t)jcr->JobId, buf.c_str());
1265 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1271 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */