2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2012 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 three of the GNU Affero 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 Affero 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
37 static const int dbglevel = 100;
39 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI
45 /* -----------------------------------------------------------------------
47 * Generic Routines (or almost generic)
49 * -----------------------------------------------------------------------
52 /* Forward referenced subroutines */
53 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar);
54 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar);
56 /** Create a new record for the Job
57 * Returns: false on failure
61 db_create_job_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
64 char dt[MAX_TIME_LENGTH];
71 char esc_job[MAX_ESCAPE_NAME_LENGTH];
72 char esc_name[MAX_ESCAPE_NAME_LENGTH];
76 stime = jr->SchedTime;
79 (void)localtime_r(&stime, &tm);
80 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
81 JobTDate = (utime_t)stime;
83 len = strlen(jcr->comment); /* TODO: use jr instead of jcr to get comment */
84 buf.check_size(len*2+1);
85 mdb->db_escape_string(jcr, buf.c_str(), jcr->comment, len);
87 mdb->db_escape_string(jcr, esc_job, jr->Job, strlen(jr->Job));
88 mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
92 "INSERT INTO Job (Job,Name,Type,Level,JobStatus,SchedTime,JobTDate,"
94 "VALUES ('%s','%s','%c','%c','%c','%s',%s,%s,'%s')",
95 esc_job, esc_name, (char)(jr->JobType), (char)(jr->JobLevel),
96 (char)(jr->JobStatus), dt, edit_uint64(JobTDate, ed1),
97 edit_int64(jr->ClientId, ed2), buf.c_str());
99 jr->JobId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Job"));
100 if (jr->JobId == 0) {
101 Mmsg2(&mdb->errmsg, _("Create DB Job record %s failed. ERR=%s\n"),
102 mdb->cmd, sql_strerror(mdb));
112 /** Create a JobMedia record for medium used this job
113 * Returns: false on failure
117 db_create_jobmedia_record(JCR *jcr, B_DB *mdb, JOBMEDIA_DBR *jm)
121 char ed1[50], ed2[50];
125 /* Now get count for VolIndex */
126 Mmsg(mdb->cmd, "SELECT count(*) from JobMedia WHERE JobId=%s",
127 edit_int64(jm->JobId, ed1));
128 count = get_sql_record_max(jcr, mdb);
135 "INSERT INTO JobMedia (JobId,MediaId,FirstIndex,LastIndex,"
136 "StartFile,EndFile,StartBlock,EndBlock,VolIndex) "
137 "VALUES (%s,%s,%u,%u,%u,%u,%u,%u,%u)",
138 edit_int64(jm->JobId, ed1),
139 edit_int64(jm->MediaId, ed2),
140 jm->FirstIndex, jm->LastIndex,
141 jm->StartFile, jm->EndFile, jm->StartBlock, jm->EndBlock,count);
143 Dmsg0(300, mdb->cmd);
144 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
145 Mmsg2(&mdb->errmsg, _("Create JobMedia record %s failed: ERR=%s\n"), mdb->cmd,
149 /* Worked, now update the Media record with the EndFile and EndBlock */
151 "UPDATE Media SET EndFile=%u, EndBlock=%u WHERE MediaId=%u",
152 jm->EndFile, jm->EndBlock, jm->MediaId);
153 if (!UPDATE_DB(jcr, mdb, mdb->cmd)) {
154 Mmsg2(&mdb->errmsg, _("Update Media record %s failed: ERR=%s\n"), mdb->cmd,
160 Dmsg0(300, "Return from JobMedia\n");
164 /** Create Unique Pool record
165 * Returns: false on failure
169 db_create_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
172 char ed1[30], ed2[30], ed3[50], ed4[50], ed5[50];
173 char esc_name[MAX_ESCAPE_NAME_LENGTH];
174 char esc_lf[MAX_ESCAPE_NAME_LENGTH];
179 Dmsg0(200, "In create pool\n");
181 mdb->db_escape_string(jcr, esc_name, pr->Name, strlen(pr->Name));
182 mdb->db_escape_string(jcr, esc_lf, pr->LabelFormat, strlen(pr->LabelFormat));
183 Mmsg(mdb->cmd, "SELECT PoolId,Name FROM Pool WHERE Name='%s'", esc_name);
184 Dmsg1(200, "selectpool: %s\n", mdb->cmd);
186 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
187 num_rows = sql_num_rows(mdb);
189 Mmsg1(&mdb->errmsg, _("pool record %s already exists\n"), pr->Name);
190 sql_free_result(mdb);
194 sql_free_result(mdb);
199 "INSERT INTO Pool (Name,NumVols,MaxVols,UseOnce,UseCatalog,"
200 "AcceptAnyVolume,AutoPrune,Recycle,VolRetention,VolUseDuration,"
201 "MaxVolJobs,MaxVolFiles,MaxVolBytes,PoolType,LabelType,LabelFormat,"
202 "RecyclePoolId,ScratchPoolId,ActionOnPurge) "
203 "VALUES ('%s',%u,%u,%d,%d,%d,%d,%d,%s,%s,%u,%u,%s,'%s',%d,'%s',%s,%s,%d)",
205 pr->NumVols, pr->MaxVols,
206 pr->UseOnce, pr->UseCatalog,
208 pr->AutoPrune, pr->Recycle,
209 edit_uint64(pr->VolRetention, ed1),
210 edit_uint64(pr->VolUseDuration, ed2),
211 pr->MaxVolJobs, pr->MaxVolFiles,
212 edit_uint64(pr->MaxVolBytes, ed3),
213 pr->PoolType, pr->LabelType, esc_lf,
214 edit_int64(pr->RecyclePoolId,ed4),
215 edit_int64(pr->ScratchPoolId,ed5),
218 Dmsg1(200, "Create Pool: %s\n", mdb->cmd);
219 pr->PoolId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Pool"));
220 if (pr->PoolId == 0) {
221 Mmsg2(&mdb->errmsg, _("Create db Pool record %s failed: ERR=%s\n"),
222 mdb->cmd, sql_strerror(mdb));
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];
242 char esc[MAX_ESCAPE_NAME_LENGTH];
245 Dmsg0(200, "In create Device\n");
247 mdb->db_escape_string(jcr, esc, dr->Name, strlen(dr->Name));
248 Mmsg(mdb->cmd, "SELECT DeviceId,Name FROM Device WHERE Name='%s'", esc);
249 Dmsg1(200, "selectdevice: %s\n", mdb->cmd);
251 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
252 num_rows = sql_num_rows(mdb);
254 Mmsg1(&mdb->errmsg, _("Device record %s already exists\n"), dr->Name);
255 sql_free_result(mdb);
259 sql_free_result(mdb);
264 "INSERT INTO Device (Name,MediaTypeId,StorageId) VALUES ('%s',%s,%s)",
266 edit_uint64(dr->MediaTypeId, ed1),
267 edit_int64(dr->StorageId, ed2));
268 Dmsg1(200, "Create Device: %s\n", mdb->cmd);
269 dr->DeviceId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Device"));
270 if (dr->DeviceId == 0) {
271 Mmsg2(&mdb->errmsg, _("Create db Device record %s failed: ERR=%s\n"),
272 mdb->cmd, sql_strerror(mdb));
284 * Create a Unique record for Storage -- no duplicates
285 * Returns: false on failure
286 * true on success with id in sr->StorageId
288 bool db_create_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
293 char esc[MAX_ESCAPE_NAME_LENGTH];
296 mdb->db_escape_string(jcr, esc, sr->Name, strlen(sr->Name));
297 Mmsg(mdb->cmd, "SELECT StorageId,AutoChanger FROM Storage WHERE Name='%s'",esc);
301 /* Check if it already exists */
302 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
303 num_rows = sql_num_rows(mdb);
304 /* If more than one, report error, but return first row */
306 Mmsg1(&mdb->errmsg, _("More than one Storage record!: %d\n"), num_rows);
307 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
310 if ((row = sql_fetch_row(mdb)) == NULL) {
311 Mmsg1(&mdb->errmsg, _("error fetching Storage row: %s\n"), sql_strerror(mdb));
312 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
313 sql_free_result(mdb);
317 sr->StorageId = str_to_int64(row[0]);
318 sr->AutoChanger = atoi(row[1]); /* bool */
319 sql_free_result(mdb);
323 sql_free_result(mdb);
327 Mmsg(mdb->cmd, "INSERT INTO Storage (Name,AutoChanger)"
328 " VALUES ('%s',%d)", esc, sr->AutoChanger);
330 sr->StorageId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Storage"));
331 if (sr->StorageId == 0) {
332 Mmsg2(&mdb->errmsg, _("Create DB Storage record %s failed. ERR=%s\n"),
333 mdb->cmd, sql_strerror(mdb));
334 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
346 * Create Unique MediaType record
347 * Returns: false on failure
351 db_create_mediatype_record(JCR *jcr, B_DB *mdb, MEDIATYPE_DBR *mr)
355 char esc[MAX_ESCAPE_NAME_LENGTH];
357 Dmsg0(200, "In create mediatype\n");
359 mdb->db_escape_string(jcr, esc, mr->MediaType, strlen(mr->MediaType));
360 Mmsg(mdb->cmd, "SELECT MediaTypeId,MediaType FROM MediaType WHERE MediaType='%s'", esc);
361 Dmsg1(200, "selectmediatype: %s\n", mdb->cmd);
363 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
364 num_rows = sql_num_rows(mdb);
366 Mmsg1(&mdb->errmsg, _("mediatype record %s already exists\n"), mr->MediaType);
367 sql_free_result(mdb);
371 sql_free_result(mdb);
376 "INSERT INTO MediaType (MediaType,ReadOnly) "
380 Dmsg1(200, "Create mediatype: %s\n", mdb->cmd);
381 mr->MediaTypeId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("MediaType"));
382 if (mr->MediaTypeId == 0) {
383 Mmsg2(&mdb->errmsg, _("Create db mediatype record %s failed: ERR=%s\n"),
384 mdb->cmd, sql_strerror(mdb));
395 * Create Media record. VolumeName and non-zero Slot must be unique
397 * Returns: 0 on failure
401 db_create_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
404 char ed1[50], ed2[50], ed3[50], ed4[50], ed5[50], ed6[50], ed7[50], ed8[50];
405 char ed9[50], ed10[50], ed11[50], ed12[50];
408 char esc_name[MAX_ESCAPE_NAME_LENGTH];
409 char esc_mtype[MAX_ESCAPE_NAME_LENGTH];
410 char esc_status[MAX_ESCAPE_NAME_LENGTH];
414 mdb->db_escape_string(jcr, esc_name, mr->VolumeName, strlen(mr->VolumeName));
415 mdb->db_escape_string(jcr, esc_mtype, mr->MediaType, strlen(mr->MediaType));
416 mdb->db_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
418 Mmsg(mdb->cmd, "SELECT MediaId FROM Media WHERE VolumeName='%s'", esc_name);
419 Dmsg1(500, "selectpool: %s\n", mdb->cmd);
421 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
422 num_rows = sql_num_rows(mdb);
424 Mmsg1(&mdb->errmsg, _("Volume \"%s\" already exists.\n"), mr->VolumeName);
425 sql_free_result(mdb);
429 sql_free_result(mdb);
434 "INSERT INTO Media (VolumeName,MediaType,MediaTypeId,PoolId,MaxVolBytes,"
435 "VolCapacityBytes,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
436 "VolStatus,Slot,VolBytes,InChanger,VolReadTime,VolWriteTime,VolParts,"
437 "EndFile,EndBlock,LabelType,StorageId,DeviceId,LocationId,"
438 "ScratchPoolId,RecyclePoolId,Enabled,ActionOnPurge)"
439 "VALUES ('%s','%s',0,%u,%s,%s,%d,%s,%s,%u,%u,'%s',%d,%s,%d,%s,%s,%d,0,0,%d,%s,"
440 "%s,%s,%s,%s,%d,%d)",
442 esc_mtype, mr->PoolId,
443 edit_uint64(mr->MaxVolBytes,ed1),
444 edit_uint64(mr->VolCapacityBytes, ed2),
446 edit_uint64(mr->VolRetention, ed3),
447 edit_uint64(mr->VolUseDuration, ed4),
452 edit_uint64(mr->VolBytes, ed5),
454 edit_int64(mr->VolReadTime, ed6),
455 edit_int64(mr->VolWriteTime, ed7),
458 edit_int64(mr->StorageId, ed8),
459 edit_int64(mr->DeviceId, ed9),
460 edit_int64(mr->LocationId, ed10),
461 edit_int64(mr->ScratchPoolId, ed11),
462 edit_int64(mr->RecyclePoolId, ed12),
463 mr->Enabled, mr->ActionOnPurge
467 Dmsg1(500, "Create Volume: %s\n", mdb->cmd);
468 mr->MediaId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Media"));
469 if (mr->MediaId == 0) {
470 Mmsg2(&mdb->errmsg, _("Create DB Media record %s failed. ERR=%s\n"),
471 mdb->cmd, sql_strerror(mdb));
475 if (mr->set_label_date) {
476 char dt[MAX_TIME_LENGTH];
477 if (mr->LabelDate == 0) {
478 mr->LabelDate = time(NULL);
480 (void)localtime_r(&mr->LabelDate, &tm);
481 strftime(dt, sizeof(dt), "%Y-%m-%d %H:%M:%S", &tm);
482 Mmsg(mdb->cmd, "UPDATE Media SET LabelDate='%s' "
483 "WHERE MediaId=%d", dt, mr->MediaId);
484 stat = UPDATE_DB(jcr, mdb, mdb->cmd);
487 * Make sure that if InChanger is non-zero any other identical slot
488 * has InChanger zero.
490 db_make_inchanger_unique(jcr, mdb, mr);
498 * Create a Unique record for the client -- no duplicates
499 * Returns: 0 on failure
500 * 1 on success with id in cr->ClientId
502 int db_create_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cr)
506 char ed1[50], ed2[50];
508 char esc_name[MAX_ESCAPE_NAME_LENGTH];
509 char esc_uname[MAX_ESCAPE_NAME_LENGTH];
512 mdb->db_escape_string(jcr, esc_name, cr->Name, strlen(cr->Name));
513 mdb->db_escape_string(jcr, esc_uname, cr->Uname, strlen(cr->Uname));
514 Mmsg(mdb->cmd, "SELECT ClientId,Uname FROM Client WHERE Name='%s'",esc_name);
517 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
518 num_rows = sql_num_rows(mdb);
519 /* If more than one, report error, but return first row */
521 Mmsg1(&mdb->errmsg, _("More than one Client!: %d\n"), num_rows);
522 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
525 if ((row = sql_fetch_row(mdb)) == NULL) {
526 Mmsg1(&mdb->errmsg, _("error fetching Client row: %s\n"), sql_strerror(mdb));
527 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
528 sql_free_result(mdb);
532 cr->ClientId = str_to_int64(row[0]);
534 bstrncpy(cr->Uname, row[1], sizeof(cr->Uname));
536 cr->Uname[0] = 0; /* no name */
538 sql_free_result(mdb);
542 sql_free_result(mdb);
546 Mmsg(mdb->cmd, "INSERT INTO Client (Name,Uname,AutoPrune,"
547 "FileRetention,JobRetention) VALUES "
548 "('%s','%s',%d,%s,%s)", esc_name, esc_uname, cr->AutoPrune,
549 edit_uint64(cr->FileRetention, ed1),
550 edit_uint64(cr->JobRetention, ed2));
552 cr->ClientId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Client"));
553 if (cr->ClientId == 0) {
554 Mmsg2(&mdb->errmsg, _("Create DB Client record %s failed. ERR=%s\n"),
555 mdb->cmd, sql_strerror(mdb));
556 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
566 /** Create a Unique record for the Path -- no duplicates */
567 int db_create_path_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
573 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
574 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
576 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
577 strcmp(mdb->cached_path, mdb->path) == 0) {
578 ar->PathId = mdb->cached_path_id;
582 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
584 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
585 num_rows = sql_num_rows(mdb);
588 Mmsg2(&mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
589 edit_uint64(num_rows, ed1), mdb->path);
590 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
592 /* Even if there are multiple paths, take the first one */
594 if ((row = sql_fetch_row(mdb)) == NULL) {
595 Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
596 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
597 sql_free_result(mdb);
602 ar->PathId = str_to_int64(row[0]);
603 sql_free_result(mdb);
605 if (ar->PathId != mdb->cached_path_id) {
606 mdb->cached_path_id = ar->PathId;
607 mdb->cached_path_len = mdb->pnl;
608 pm_strcpy(mdb->cached_path, mdb->path);
613 sql_free_result(mdb);
616 Mmsg(mdb->cmd, "INSERT INTO Path (Path) VALUES ('%s')", mdb->esc_name);
618 ar->PathId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Path"));
619 if (ar->PathId == 0) {
620 Mmsg2(&mdb->errmsg, _("Create db Path record %s failed. ERR=%s\n"),
621 mdb->cmd, sql_strerror(mdb));
622 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
630 if (stat && ar->PathId != mdb->cached_path_id) {
631 mdb->cached_path_id = ar->PathId;
632 mdb->cached_path_len = mdb->pnl;
633 pm_strcpy(mdb->cached_path, mdb->path);
639 * Create a Unique record for the counter -- no duplicates
640 * Returns: 0 on failure
641 * 1 on success with counter filled in
643 int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
645 char esc[MAX_ESCAPE_NAME_LENGTH];
650 memset(&mcr, 0, sizeof(mcr));
651 bstrncpy(mcr.Counter, cr->Counter, sizeof(mcr.Counter));
652 if (db_get_counter_record(jcr, mdb, &mcr)) {
653 memcpy(cr, &mcr, sizeof(COUNTER_DBR));
657 mdb->db_escape_string(jcr, esc, cr->Counter, strlen(cr->Counter));
659 Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,Counters.MinValue,Counters.MaxValue,CurrentValue,"
660 "WrapCounter) VALUES ('%s','%d','%d','%d','%s')",
661 esc, cr->MinValue, cr->MaxValue, cr->CurrentValue,
664 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
665 Mmsg2(&mdb->errmsg, _("Create DB Counters record %s failed. ERR=%s\n"),
666 mdb->cmd, sql_strerror(mdb));
667 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
678 * Create a FileSet record. This record is unique in the
679 * name and the MD5 signature of the include/exclude sets.
680 * Returns: 0 on failure
681 * 1 on success with FileSetId in record
683 bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
689 char esc_fs[MAX_ESCAPE_NAME_LENGTH];
690 char esc_md5[MAX_ESCAPE_NAME_LENGTH];
692 /* TODO: Escape FileSet and MD5 */
694 fsr->created = false;
695 mdb->db_escape_string(jcr, esc_fs, fsr->FileSet, strlen(fsr->FileSet));
696 mdb->db_escape_string(jcr, esc_md5, fsr->MD5, strlen(fsr->MD5));
697 Mmsg(mdb->cmd, "SELECT FileSetId,CreateTime FROM FileSet WHERE "
698 "FileSet='%s' AND MD5='%s'", esc_fs, esc_md5);
701 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
702 num_rows = sql_num_rows(mdb);
704 Mmsg1(&mdb->errmsg, _("More than one FileSet!: %d\n"), num_rows);
705 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
708 if ((row = sql_fetch_row(mdb)) == NULL) {
709 Mmsg1(&mdb->errmsg, _("error fetching FileSet row: ERR=%s\n"), sql_strerror(mdb));
710 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
711 sql_free_result(mdb);
715 fsr->FileSetId = str_to_int64(row[0]);
716 if (row[1] == NULL) {
717 fsr->cCreateTime[0] = 0;
719 bstrncpy(fsr->cCreateTime, row[1], sizeof(fsr->cCreateTime));
721 sql_free_result(mdb);
725 sql_free_result(mdb);
728 if (fsr->CreateTime == 0 && fsr->cCreateTime[0] == 0) {
729 fsr->CreateTime = time(NULL);
731 (void)localtime_r(&fsr->CreateTime, &tm);
732 strftime(fsr->cCreateTime, sizeof(fsr->cCreateTime), "%Y-%m-%d %H:%M:%S", &tm);
735 Mmsg(mdb->cmd, "INSERT INTO FileSet (FileSet,MD5,CreateTime) "
736 "VALUES ('%s','%s','%s')", esc_fs, esc_md5, fsr->cCreateTime);
738 fsr->FileSetId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("FileSet"));
739 if (fsr->FileSetId == 0) {
740 Mmsg2(&mdb->errmsg, _("Create DB FileSet record %s failed. ERR=%s\n"),
741 mdb->cmd, sql_strerror(mdb));
742 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
757 * dev_t st_dev; * device *
758 * ino_t st_ino; * inode *
759 * mode_t st_mode; * protection *
760 * nlink_t st_nlink; * number of hard links *
761 * uid_t st_uid; * user ID of owner *
762 * gid_t st_gid; * group ID of owner *
763 * dev_t st_rdev; * device type (if inode device) *
764 * off_t st_size; * total size, in bytes *
765 * unsigned long st_blksize; * blocksize for filesystem I/O *
766 * unsigned long st_blocks; * number of blocks allocated *
767 * time_t st_atime; * time of last access *
768 * time_t st_mtime; * time of last modification *
769 * time_t st_ctime; * time of last inode change *
774 * All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
778 * - bulk load a temp table
779 * - insert missing filenames into filename with a single query (lock filenames
780 * - table before that to avoid possible duplicate inserts with concurrent update)
781 * - insert missing paths into path with another single query
782 * - then insert the join between the temp, filename and path tables into file.
789 bool db_write_batch_file_records(JCR *jcr)
792 int JobStatus = jcr->JobStatus;
794 if (!jcr->batch_started) { /* no files to backup ? */
795 Dmsg0(50,"db_create_file_record : no files\n");
798 if (job_canceled(jcr)) {
802 Dmsg1(50,"db_create_file_record changes=%u\n",jcr->db_batch->changes);
804 jcr->JobStatus = JS_AttrInserting;
805 if (!sql_batch_end(jcr, jcr->db_batch, NULL)) {
806 Jmsg1(jcr, M_FATAL, 0, "Batch end %s\n", jcr->db_batch->errmsg);
809 if (job_canceled(jcr)) {
814 * We have to lock tables
816 if (!db_sql_query(jcr->db_batch, batch_lock_path_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
817 Jmsg1(jcr, M_FATAL, 0, "Lock Path table %s\n", jcr->db_batch->errmsg);
821 if (!db_sql_query(jcr->db_batch, batch_fill_path_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
822 Jmsg1(jcr, M_FATAL, 0, "Fill Path table %s\n",jcr->db_batch->errmsg);
823 db_sql_query(jcr->db_batch, batch_unlock_tables_query[db_get_type_index(jcr->db_batch)], NULL, NULL);
827 if (!db_sql_query(jcr->db_batch, batch_unlock_tables_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
828 Jmsg1(jcr, M_FATAL, 0, "Unlock Path table %s\n", jcr->db_batch->errmsg);
833 * We have to lock tables
835 if (!db_sql_query(jcr->db_batch, batch_lock_filename_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
836 Jmsg1(jcr, M_FATAL, 0, "Lock Filename table %s\n", jcr->db_batch->errmsg);
840 if (!db_sql_query(jcr->db_batch, batch_fill_filename_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
841 Jmsg1(jcr,M_FATAL,0,"Fill Filename table %s\n",jcr->db_batch->errmsg);
842 db_sql_query(jcr->db_batch, batch_unlock_tables_query[db_get_type_index(jcr->db_batch)], NULL, NULL);
846 if (!db_sql_query(jcr->db_batch, batch_unlock_tables_query[db_get_type_index(jcr->db_batch)], NULL, NULL)) {
847 Jmsg1(jcr, M_FATAL, 0, "Unlock Filename table %s\n", jcr->db_batch->errmsg);
851 if (!db_sql_query(jcr->db_batch,
852 "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) "
853 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
854 "Filename.FilenameId,batch.LStat, batch.MD5, batch.DeltaSeq "
856 "JOIN Path ON (batch.Path = Path.Path) "
857 "JOIN Filename ON (batch.Name = Filename.Name)",
860 Jmsg1(jcr, M_FATAL, 0, "Fill File table %s\n", jcr->db_batch->errmsg);
864 jcr->JobStatus = JobStatus; /* reset entry status */
868 db_sql_query(jcr->db_batch, "DROP TABLE batch", NULL,NULL);
869 jcr->batch_started = false;
875 * Create File record in B_DB
877 * In order to reduce database size, we store the File attributes,
878 * the FileName, and the Path separately. In principle, there
879 * is a single FileName record and a single Path record, no matter
880 * how many times it occurs. This is this subroutine, we separate
881 * the file and the path and fill temporary tables with this three records.
883 * Note: all routines that call this expect to be able to call
884 * db_strerror(mdb) to get the error message, so the error message
885 * MUST be edited into mdb->errmsg before returning an error status.
887 bool db_create_batch_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
889 ASSERT(ar->FileType != FT_BASE);
891 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
892 Dmsg0(dbglevel, "put_file_into_catalog\n");
894 /* Open the dedicated connexion */
895 if (!jcr->batch_started) {
896 if (!db_open_batch_connexion(jcr, mdb)) {
897 return false; /* error already printed */
899 if (!sql_batch_start(jcr, jcr->db_batch)) {
901 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
902 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
905 jcr->batch_started = true;
907 B_DB *bdb = jcr->db_batch;
909 split_path_and_file(jcr, bdb, ar->fname);
913 * if (bdb->changes > 100000) {
914 * db_write_batch_file_records(jcr);
916 * sql_batch_start(jcr, bdb);
920 return sql_batch_insert(jcr, bdb, ar);
924 * Create File record in B_DB
926 * In order to reduce database size, we store the File attributes,
927 * the FileName, and the Path separately. In principle, there
928 * is a single FileName record and a single Path record, no matter
929 * how many times it occurs. This is this subroutine, we separate
930 * the file and the path and create three database records.
932 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
935 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
936 Dmsg0(dbglevel, "put_file_into_catalog\n");
938 split_path_and_file(jcr, mdb, ar->fname);
940 if (!db_create_filename_record(jcr, mdb, ar)) {
943 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
946 if (!db_create_path_record(jcr, mdb, ar)) {
949 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
951 /* Now create master File record */
952 if (!db_create_file_record(jcr, mdb, ar)) {
955 Dmsg0(dbglevel, "db_create_file_record OK\n");
957 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
966 * This is the master File entry containing the attributes.
967 * The filename and path records have already been created.
969 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
972 static const char *no_digest = "0";
977 ASSERT(ar->FilenameId);
979 if (ar->Digest == NULL || ar->Digest[0] == 0) {
987 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
988 "LStat,MD5,DeltaSeq) VALUES (%u,%u,%u,%u,'%s','%s',%u)",
989 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
990 ar->attr, digest, ar->DeltaSeq);
992 ar->FileId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("File"));
993 if (ar->FileId == 0) {
994 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
995 mdb->cmd, sql_strerror(mdb));
996 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1004 /** Create a Unique record for the filename -- no duplicates */
1005 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1010 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1011 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1013 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1015 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1016 num_rows = sql_num_rows(mdb);
1019 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1020 edit_uint64(num_rows, ed1), mdb->fname);
1021 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1023 if (num_rows >= 1) {
1024 if ((row = sql_fetch_row(mdb)) == NULL) {
1025 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1026 mdb->fname, sql_strerror(mdb));
1027 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1030 ar->FilenameId = str_to_int64(row[0]);
1032 sql_free_result(mdb);
1033 return ar->FilenameId > 0;
1035 sql_free_result(mdb);
1038 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1040 ar->FilenameId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Filename"));
1041 if (ar->FilenameId == 0) {
1042 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1043 mdb->cmd, sql_strerror(mdb));
1044 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1046 return ar->FilenameId > 0;
1050 * Create file attributes record, or base file attributes record
1052 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1057 * Make sure we have an acceptable attributes record.
1059 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1060 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1061 Jmsg(jcr, M_FATAL, 0, _("Attempt to put non-attributes into catalog. Stream=%d\n"));
1065 if (ar->FileType != FT_BASE) {
1066 if (mdb->batch_insert_available()) {
1067 ret = db_create_batch_file_attributes_record(jcr, mdb, ar);
1069 ret = db_create_file_attributes_record(jcr, mdb, ar);
1071 } else if (jcr->HasBase) {
1072 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1074 Jmsg0(jcr, M_FATAL, 0, _("Cannot Copy/Migrate job using BaseJob"));
1075 ret = true; /* in copy/migration what do we do ? */
1082 * Create Base File record in B_DB
1085 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1088 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1089 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1092 split_path_and_file(jcr, mdb, ar->fname);
1094 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1095 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1097 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1098 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1100 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1101 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1103 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1110 * Cleanup the base file temporary tables
1112 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1114 POOL_MEM buf(PM_MESSAGE);
1115 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1116 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1118 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1119 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1123 * Put all base file seen in the backup to the BaseFile table
1124 * and cleanup temporary tables
1126 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1134 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1135 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1136 "B.FileId, B.FileIndex "
1137 "FROM basefile%s AS A, new_basefile%s AS B "
1138 "WHERE A.Path = B.Path "
1139 "AND A.Name = B.Name "
1140 "ORDER BY B.FileId",
1141 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1142 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1143 jcr->nb_base_files_used = sql_affected_rows(mdb);
1144 db_cleanup_base_file(jcr, mdb);
1151 * Find the last "accurate" backup state with Base jobs
1152 * 1) Get all files with jobid in list (F subquery)
1153 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1154 * 3) Put the result in a temporary table for the end of job
1157 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1165 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1169 Mmsg(mdb->cmd, create_temp_basefile[db_get_type_index(mdb)], (uint64_t) jcr->JobId);
1170 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1173 Mmsg(buf, select_recent_version[db_get_type_index(mdb)], jobids, jobids);
1174 Mmsg(mdb->cmd, create_temp_new_basefile[db_get_type_index(mdb)], (uint64_t)jcr->JobId, buf.c_str());
1176 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1183 * Create Restore Object record in B_DB
1186 bool db_create_restore_object_record(JCR *jcr, B_DB *mdb, ROBJECT_DBR *ro)
1190 POOLMEM *esc_plug_name = get_pool_memory(PM_MESSAGE);
1194 Dmsg1(dbglevel, "Oname=%s\n", ro->object_name);
1195 Dmsg0(dbglevel, "put_object_into_catalog\n");
1197 mdb->fnl = strlen(ro->object_name);
1198 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1199 db_escape_string(jcr, mdb, mdb->esc_name, ro->object_name, mdb->fnl);
1201 db_escape_object(jcr, mdb, ro->object, ro->object_len);
1203 plug_name_len = strlen(ro->plugin_name);
1204 esc_plug_name = check_pool_memory_size(esc_plug_name, plug_name_len*2+1);
1205 db_escape_string(jcr, mdb, esc_plug_name, ro->plugin_name, plug_name_len);
1208 "INSERT INTO RestoreObject (ObjectName,PluginName,RestoreObject,"
1209 "ObjectLength,ObjectFullLength,ObjectIndex,ObjectType,"
1210 "ObjectCompression,FileIndex,JobId) "
1211 "VALUES ('%s','%s','%s',%d,%d,%d,%d,%d,%d,%u)",
1212 mdb->esc_name, esc_plug_name, mdb->esc_obj,
1213 ro->object_len, ro->object_full_len, ro->object_index,
1214 ro->FileType, ro->object_compression, ro->FileIndex, ro->JobId);
1216 ro->RestoreObjectId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("RestoreObject"));
1217 if (ro->RestoreObjectId == 0) {
1218 Mmsg2(&mdb->errmsg, _("Create db Object record %s failed. ERR=%s"),
1219 mdb->cmd, sql_strerror(mdb));
1220 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1226 free_pool_memory(esc_plug_name);
1230 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */