2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2010 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,\"MinValue\",\"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, MarkId) "
853 "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
854 "Filename.FilenameId,batch.LStat, batch.MD5, batch.MarkId "
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);
874 * Create File record in B_DB
876 * In order to reduce database size, we store the File attributes,
877 * the FileName, and the Path separately. In principle, there
878 * is a single FileName record and a single Path record, no matter
879 * how many times it occurs. This is this subroutine, we separate
880 * the file and the path and fill temporary tables with this three records.
882 * Note: all routines that call this expect to be able to call
883 * db_strerror(mdb) to get the error message, so the error message
884 * MUST be edited into mdb->errmsg before returning an error status.
886 bool db_create_batch_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
888 ASSERT(ar->FileType != FT_BASE);
890 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
891 Dmsg0(dbglevel, "put_file_into_catalog\n");
893 /* Open the dedicated connexion */
894 if (!jcr->batch_started) {
895 if (!db_open_batch_connexion(jcr, mdb)) {
896 return false; /* error already printed */
898 if (!sql_batch_start(jcr, jcr->db_batch)) {
900 "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
901 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
904 jcr->batch_started = true;
906 B_DB *bdb = jcr->db_batch;
908 split_path_and_file(jcr, bdb, ar->fname);
912 * if (bdb->changes > 100000) {
913 * db_write_batch_file_records(jcr);
915 * sql_batch_start(jcr, bdb);
919 return sql_batch_insert(jcr, bdb, ar);
923 * Create File record in B_DB
925 * In order to reduce database size, we store the File attributes,
926 * the FileName, and the Path separately. In principle, there
927 * is a single FileName record and a single Path record, no matter
928 * how many times it occurs. This is this subroutine, we separate
929 * the file and the path and create three database records.
931 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
934 Dmsg1(dbglevel, "Fname=%s\n", ar->fname);
935 Dmsg0(dbglevel, "put_file_into_catalog\n");
937 split_path_and_file(jcr, mdb, ar->fname);
939 if (!db_create_filename_record(jcr, mdb, ar)) {
942 Dmsg1(dbglevel, "db_create_filename_record: %s\n", mdb->esc_name);
945 if (!db_create_path_record(jcr, mdb, ar)) {
948 Dmsg1(dbglevel, "db_create_path_record: %s\n", mdb->esc_name);
950 /* Now create master File record */
951 if (!db_create_file_record(jcr, mdb, ar)) {
954 Dmsg0(dbglevel, "db_create_file_record OK\n");
956 Dmsg3(dbglevel, "CreateAttributes Path=%s File=%s FilenameId=%d\n", mdb->path, mdb->fname, ar->FilenameId);
965 * This is the master File entry containing the attributes.
966 * The filename and path records have already been created.
968 static int db_create_file_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
971 static const char *no_digest = "0";
976 ASSERT(ar->FilenameId);
978 if (ar->Digest == NULL || ar->Digest[0] == 0) {
986 "INSERT INTO File (FileIndex,JobId,PathId,FilenameId,"
987 "LStat,MD5,MarkId) VALUES (%u,%u,%u,%u,'%s','%s',%u)",
988 ar->FileIndex, ar->JobId, ar->PathId, ar->FilenameId,
989 ar->attr, digest, ar->DeltaSeq);
991 ar->FileId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("File"));
992 if (ar->FileId == 0) {
993 Mmsg2(&mdb->errmsg, _("Create db File record %s failed. ERR=%s"),
994 mdb->cmd, sql_strerror(mdb));
995 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1003 /** Create a Unique record for the filename -- no duplicates */
1004 static int db_create_filename_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1009 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
1010 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1012 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
1014 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1015 num_rows = sql_num_rows(mdb);
1018 Mmsg2(&mdb->errmsg, _("More than one Filename! %s for file: %s\n"),
1019 edit_uint64(num_rows, ed1), mdb->fname);
1020 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
1022 if (num_rows >= 1) {
1023 if ((row = sql_fetch_row(mdb)) == NULL) {
1024 Mmsg2(&mdb->errmsg, _("Error fetching row for file=%s: ERR=%s\n"),
1025 mdb->fname, sql_strerror(mdb));
1026 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1029 ar->FilenameId = str_to_int64(row[0]);
1031 sql_free_result(mdb);
1032 return ar->FilenameId > 0;
1034 sql_free_result(mdb);
1037 Mmsg(mdb->cmd, "INSERT INTO Filename (Name) VALUES ('%s')", mdb->esc_name);
1039 ar->FilenameId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("Filename"));
1040 if (ar->FilenameId == 0) {
1041 Mmsg2(&mdb->errmsg, _("Create db Filename record %s failed. ERR=%s\n"),
1042 mdb->cmd, sql_strerror(mdb));
1043 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1045 return ar->FilenameId > 0;
1049 * Create file attributes record, or base file attributes record
1051 bool db_create_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1056 * Make sure we have an acceptable attributes record.
1058 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
1059 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
1060 Jmsg(jcr, M_FATAL, 0, _("Attempt to put non-attributes into catalog. Stream=%d\n"));
1064 if (ar->FileType != FT_BASE) {
1065 if (mdb->batch_insert_available()) {
1066 ret = db_create_batch_file_attributes_record(jcr, mdb, ar);
1068 ret = db_create_file_attributes_record(jcr, mdb, ar);
1070 } else if (jcr->HasBase) {
1071 ret = db_create_base_file_attributes_record(jcr, mdb, ar);
1073 Jmsg0(jcr, M_FATAL, 0, _("Can't Copy/Migrate job using BaseJob"));
1074 ret = true; /* in copy/migration what do we do ? */
1081 * Create Base File record in B_DB
1084 bool db_create_base_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
1087 Dmsg1(dbglevel, "create_base_file Fname=%s\n", ar->fname);
1088 Dmsg0(dbglevel, "put_base_file_into_catalog\n");
1091 split_path_and_file(jcr, mdb, ar->fname);
1093 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1094 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
1096 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
1097 db_escape_string(jcr, mdb, mdb->esc_path, mdb->path, mdb->pnl);
1099 Mmsg(mdb->cmd, "INSERT INTO basefile%lld (Path, Name) VALUES ('%s','%s')",
1100 (uint64_t)jcr->JobId, mdb->esc_path, mdb->esc_name);
1102 ret = INSERT_DB(jcr, mdb, mdb->cmd);
1109 * Cleanup the base file temporary tables
1111 static void db_cleanup_base_file(JCR *jcr, B_DB *mdb)
1113 POOL_MEM buf(PM_MESSAGE);
1114 Mmsg(buf, "DROP TABLE new_basefile%lld", (uint64_t) jcr->JobId);
1115 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1117 Mmsg(buf, "DROP TABLE basefile%lld", (uint64_t) jcr->JobId);
1118 db_sql_query(mdb, buf.c_str(), NULL, NULL);
1122 * Put all base file seen in the backup to the BaseFile table
1123 * and cleanup temporary tables
1125 bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
1133 "INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) "
1134 "SELECT B.JobId AS BaseJobId, %s AS JobId, "
1135 "B.FileId, B.FileIndex "
1136 "FROM basefile%s AS A, new_basefile%s AS B "
1137 "WHERE A.Path = B.Path "
1138 "AND A.Name = B.Name "
1139 "ORDER BY B.FileId",
1140 edit_uint64(jcr->JobId, ed1), ed1, ed1);
1141 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1142 jcr->nb_base_files_used = sql_affected_rows(mdb);
1143 db_cleanup_base_file(jcr, mdb);
1150 * Find the last "accurate" backup state with Base jobs
1151 * 1) Get all files with jobid in list (F subquery)
1152 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1153 * 3) Put the result in a temporary table for the end of job
1156 bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
1164 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1168 Mmsg(mdb->cmd, create_temp_basefile[db_get_type_index(mdb)], (uint64_t) jcr->JobId);
1169 if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
1172 Mmsg(buf, select_recent_version[db_get_type_index(mdb)], jobids, jobids);
1173 Mmsg(mdb->cmd, create_temp_new_basefile[db_get_type_index(mdb)], (uint64_t)jcr->JobId, buf.c_str());
1175 ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
1182 * Create Restore Object record in B_DB
1185 bool db_create_restore_object_record(JCR *jcr, B_DB *mdb, ROBJECT_DBR *ro)
1189 POOLMEM *esc_plug_name = get_pool_memory(PM_MESSAGE);
1193 Dmsg1(dbglevel, "Oname=%s\n", ro->object_name);
1194 Dmsg0(dbglevel, "put_object_into_catalog\n");
1196 mdb->fnl = strlen(ro->object_name);
1197 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
1198 db_escape_string(jcr, mdb, mdb->esc_name, ro->object_name, mdb->fnl);
1200 db_escape_object(jcr, mdb, ro->object, ro->object_len);
1202 plug_name_len = strlen(ro->plugin_name);
1203 esc_plug_name = check_pool_memory_size(esc_plug_name, plug_name_len*2+1);
1204 db_escape_string(jcr, mdb, esc_plug_name, ro->plugin_name, plug_name_len);
1207 "INSERT INTO RestoreObject (ObjectName,PluginName,RestoreObject,"
1208 "ObjectLength,ObjectFullLength,ObjectIndex,ObjectType,"
1209 "ObjectCompression,FileIndex,JobId) "
1210 "VALUES ('%s','%s','%s',%d,%d,%d,%d,%d,%d,%u)",
1211 mdb->esc_name, esc_plug_name, mdb->esc_obj,
1212 ro->object_len, ro->object_full_len, ro->object_index,
1213 FT_RESTORE_FIRST, ro->object_compression, ro->FileIndex, ro->JobId);
1215 ro->RestoreObjectId = sql_insert_autokey_record(mdb, mdb->cmd, NT_("RestoreObject"));
1216 if (ro->RestoreObjectId == 0) {
1217 Mmsg2(&mdb->errmsg, _("Create db Object record %s failed. ERR=%s"),
1218 mdb->cmd, sql_strerror(mdb));
1219 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
1225 free_pool_memory(esc_plug_name);
1229 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */