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 Get record interface routines
30 * Note, these routines generally get a record by id or
31 * by name. If more logic is involved, the routine
34 * Kern Sibbald, March 2000
40 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI
46 /* -----------------------------------------------------------------------
48 * Generic Routines (or almost generic)
50 * -----------------------------------------------------------------------
53 /* Forward referenced functions */
54 static int db_get_file_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr, FILE_DBR *fdbr);
55 static int db_get_filename_record(JCR *jcr, B_DB *mdb);
59 * Given a full filename (with path), look up the File record
60 * (with attributes) in the database.
62 * Returns: 0 on failure
63 * 1 on success with the File record in FILE_DBR
65 int db_get_file_attributes_record(JCR *jcr, B_DB *mdb, char *fname, JOB_DBR *jr, FILE_DBR *fdbr)
68 Dmsg1(100, "db_get_file_att_record fname=%s \n", fname);
71 split_path_and_file(jcr, mdb, fname);
73 fdbr->FilenameId = db_get_filename_record(jcr, mdb);
75 fdbr->PathId = db_get_path_record(jcr, mdb);
77 stat = db_get_file_record(jcr, mdb, jr, fdbr);
87 * Returns: 0 on failure
90 * DO NOT use Jmsg in this routine.
92 * Note in this routine, we do not use Jmsg because it may be
93 * called to get attributes of a non-existent file, which is
94 * "normal" if a new file is found during Verify.
96 * The following is a bit of a kludge: because we always backup a
97 * directory entry, we can end up with two copies of the directory
98 * in the backup. One is when we encounter the directory and find
99 * we cannot recurse into it, and the other is when we find an
100 * explicit mention of the directory. This can also happen if the
101 * use includes the directory twice. In this case, Verify
102 * VolumeToCatalog fails because we have two copies in the catalog,
103 * and only the first one is marked (twice). So, when calling from Verify,
104 * VolumeToCatalog jr is not NULL and we know jr->FileIndex is the fileindex
105 * of the version of the directory/file we actually want and do
106 * a more explicit SQL search.
109 int db_get_file_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr, FILE_DBR *fdbr)
113 char ed1[50], ed2[50], ed3[50];
116 if (jcr->getJobLevel() == L_VERIFY_DISK_TO_CATALOG) {
118 "SELECT FileId, LStat, MD5 FROM File,Job WHERE "
119 "File.JobId=Job.JobId AND File.PathId=%s AND "
120 "File.FilenameId=%s AND Job.Type='B' AND Job.JobStatus IN ('T','W') AND "
121 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
122 edit_int64(fdbr->PathId, ed1),
123 edit_int64(fdbr->FilenameId, ed2),
124 edit_int64(jr->ClientId,ed3));
125 } else if (jcr->getJobLevel() == L_VERIFY_VOLUME_TO_CATALOG) {
127 "SELECT FileId, LStat, MD5 FROM File WHERE File.JobId=%s AND File.PathId=%s AND "
128 "File.FilenameId=%s AND File.FileIndex=%u",
129 edit_int64(fdbr->JobId, ed1),
130 edit_int64(fdbr->PathId, ed2),
131 edit_int64(fdbr->FilenameId,ed3),
135 "SELECT FileId, LStat, MD5 FROM File WHERE File.JobId=%s AND File.PathId=%s AND "
136 "File.FilenameId=%s",
137 edit_int64(fdbr->JobId, ed1),
138 edit_int64(fdbr->PathId, ed2),
139 edit_int64(fdbr->FilenameId,ed3));
141 Dmsg3(450, "Get_file_record JobId=%u FilenameId=%u PathId=%u\n",
142 fdbr->JobId, fdbr->FilenameId, fdbr->PathId);
144 Dmsg1(100, "Query=%s\n", mdb->cmd);
146 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
147 num_rows = sql_num_rows(mdb);
148 Dmsg1(050, "get_file_record num_rows=%d\n", num_rows);
150 if ((row = sql_fetch_row(mdb)) == NULL) {
151 Mmsg1(mdb->errmsg, _("Error fetching row: %s\n"), sql_strerror(mdb));
153 fdbr->FileId = (FileId_t)str_to_int64(row[0]);
154 bstrncpy(fdbr->LStat, row[1], sizeof(fdbr->LStat));
155 bstrncpy(fdbr->Digest, row[2], sizeof(fdbr->Digest));
158 Mmsg3(mdb->errmsg, _("get_file_record want 1 got rows=%d PathId=%s FilenameId=%s\n"),
160 edit_int64(fdbr->PathId, ed1),
161 edit_int64(fdbr->FilenameId, ed2));
162 Dmsg1(000, "=== Problem! %s", mdb->errmsg);
166 Mmsg2(mdb->errmsg, _("File record for PathId=%s FilenameId=%s not found.\n"),
167 edit_int64(fdbr->PathId, ed1),
168 edit_int64(fdbr->FilenameId, ed2));
170 sql_free_result(mdb);
172 Mmsg(mdb->errmsg, _("File record not found in Catalog.\n"));
179 * Get Filename record
180 * Returns: 0 on failure
181 * FilenameId on success
183 * DO NOT use Jmsg in this routine (see notes for get_file_record)
185 static int db_get_filename_record(JCR *jcr, B_DB *mdb)
191 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
192 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
194 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
195 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
197 num_rows = sql_num_rows(mdb);
199 Mmsg2(mdb->errmsg, _("More than one Filename!: %s for file: %s\n"),
200 edit_uint64(num_rows, ed1), mdb->fname);
201 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
204 if ((row = sql_fetch_row(mdb)) == NULL) {
205 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
207 FilenameId = str_to_int64(row[0]);
208 if (FilenameId <= 0) {
209 Mmsg2(mdb->errmsg, _("Get DB Filename record %s found bad record: %d\n"),
210 mdb->cmd, FilenameId);
215 Mmsg1(mdb->errmsg, _("Filename record: %s not found.\n"), mdb->fname);
217 sql_free_result(mdb);
219 Mmsg(mdb->errmsg, _("Filename record: %s not found in Catalog.\n"), mdb->fname);
226 * Returns: 0 on failure
229 * DO NOT use Jmsg in this routine (see notes for get_file_record)
231 int db_get_path_record(JCR *jcr, B_DB *mdb)
237 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
238 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
240 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
241 strcmp(mdb->cached_path, mdb->path) == 0) {
242 return mdb->cached_path_id;
245 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
247 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
249 num_rows = sql_num_rows(mdb);
251 Mmsg2(mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
252 edit_uint64(num_rows, ed1), mdb->path);
253 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
255 /* Even if there are multiple paths, take the first one */
257 if ((row = sql_fetch_row(mdb)) == NULL) {
258 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
260 PathId = str_to_int64(row[0]);
262 Mmsg2(mdb->errmsg, _("Get DB path record %s found bad record: %s\n"),
263 mdb->cmd, edit_int64(PathId, ed1));
267 if (PathId != mdb->cached_path_id) {
268 mdb->cached_path_id = PathId;
269 mdb->cached_path_len = mdb->pnl;
270 pm_strcpy(mdb->cached_path, mdb->path);
275 Mmsg1(mdb->errmsg, _("Path record: %s not found.\n"), mdb->path);
277 sql_free_result(mdb);
279 Mmsg(mdb->errmsg, _("Path record: %s not found in Catalog.\n"), mdb->path);
286 * Get Job record for given JobId or Job name
287 * Returns: false on failure
290 bool db_get_job_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
296 if (jr->JobId == 0) {
297 Mmsg(mdb->cmd, "SELECT VolSessionId,VolSessionTime,"
298 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
299 "Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,"
300 "SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles "
301 "FROM Job WHERE Job='%s'", jr->Job);
303 Mmsg(mdb->cmd, "SELECT VolSessionId,VolSessionTime,"
304 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
305 "Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,"
306 "SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles "
307 "FROM Job WHERE JobId=%s",
308 edit_int64(jr->JobId, ed1));
311 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
313 return false; /* failed */
315 if ((row = sql_fetch_row(mdb)) == NULL) {
316 Mmsg1(mdb->errmsg, _("No Job found for JobId %s\n"), edit_int64(jr->JobId, ed1));
317 sql_free_result(mdb);
319 return false; /* failed */
322 jr->VolSessionId = str_to_uint64(row[0]);
323 jr->VolSessionTime = str_to_uint64(row[1]);
324 jr->PoolId = str_to_int64(row[2]);
325 bstrncpy(jr->cStartTime, row[3]!=NULL?row[3]:"", sizeof(jr->cStartTime));
326 bstrncpy(jr->cEndTime, row[4]!=NULL?row[4]:"", sizeof(jr->cEndTime));
327 jr->JobFiles = str_to_int64(row[5]);
328 jr->JobBytes = str_to_int64(row[6]);
329 jr->JobTDate = str_to_int64(row[7]);
330 bstrncpy(jr->Job, row[8]!=NULL?row[8]:"", sizeof(jr->Job));
331 jr->JobStatus = row[9]!=NULL?(int)*row[9]:JS_FatalError;
332 jr->JobType = row[10]!=NULL?(int)*row[10]:JT_BACKUP;
333 jr->JobLevel = row[11]!=NULL?(int)*row[11]:L_NONE;
334 jr->ClientId = str_to_uint64(row[12]!=NULL?row[12]:(char *)"");
335 bstrncpy(jr->Name, row[13]!=NULL?row[13]:"", sizeof(jr->Name));
336 jr->PriorJobId = str_to_uint64(row[14]!=NULL?row[14]:(char *)"");
337 bstrncpy(jr->cRealEndTime, row[15]!=NULL?row[15]:"", sizeof(jr->cRealEndTime));
338 if (jr->JobId == 0) {
339 jr->JobId = str_to_int64(row[16]);
341 jr->FileSetId = str_to_int64(row[17]);
342 bstrncpy(jr->cSchedTime, row[3]!=NULL?row[18]:"", sizeof(jr->cSchedTime));
343 bstrncpy(jr->cRealEndTime, row[3]!=NULL?row[19]:"", sizeof(jr->cRealEndTime));
344 jr->ReadBytes = str_to_int64(row[20]);
345 jr->StartTime = str_to_utime(jr->cStartTime);
346 jr->SchedTime = str_to_utime(jr->cSchedTime);
347 jr->EndTime = str_to_utime(jr->cEndTime);
348 jr->RealEndTime = str_to_utime(jr->cRealEndTime);
349 jr->HasBase = str_to_int64(row[21]);
350 jr->PurgedFiles = str_to_int64(row[22]);
351 sql_free_result(mdb);
358 * Find VolumeNames for a given JobId
359 * Returns: 0 on error or no Volumes found
360 * number of volumes on success
361 * Volumes are concatenated in VolumeNames
362 * separated by a vertical bar (|) in the order
363 * that they were written.
365 * Returns: number of volumes on success
367 int db_get_job_volume_names(JCR *jcr, B_DB *mdb, JobId_t JobId, POOLMEM **VolumeNames)
376 /* Get one entry per VolumeName, but "sort" by VolIndex */
378 "SELECT VolumeName,MAX(VolIndex) FROM JobMedia,Media WHERE "
379 "JobMedia.JobId=%s AND JobMedia.MediaId=Media.MediaId "
380 "GROUP BY VolumeName "
381 "ORDER BY 2 ASC", edit_int64(JobId,ed1));
383 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
385 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
386 num_rows = sql_num_rows(mdb);
387 Dmsg1(130, "Num rows=%d\n", num_rows);
389 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
393 for (i=0; i < stat; i++) {
394 if ((row = sql_fetch_row(mdb)) == NULL) {
395 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
396 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
400 if (*VolumeNames[0] != 0) {
401 pm_strcat(VolumeNames, "|");
403 pm_strcat(VolumeNames, row[0]);
407 sql_free_result(mdb);
409 Mmsg(mdb->errmsg, _("No Volume for JobId %d found in Catalog.\n"), JobId);
416 * Find Volume parameters for a give JobId
417 * Returns: 0 on error or no Volumes found
418 * number of volumes on success
419 * List of Volumes and start/end file/blocks (malloced structure!)
421 * Returns: number of volumes on success
423 int db_get_job_volume_parameters(JCR *jcr, B_DB *mdb, JobId_t JobId, VOL_PARAMS **VolParams)
429 VOL_PARAMS *Vols = NULL;
434 "SELECT VolumeName,MediaType,FirstIndex,LastIndex,StartFile,"
435 "JobMedia.EndFile,StartBlock,JobMedia.EndBlock,"
436 "Slot,StorageId,InChanger"
437 " FROM JobMedia,Media WHERE JobMedia.JobId=%s"
438 " AND JobMedia.MediaId=Media.MediaId ORDER BY VolIndex,JobMediaId",
439 edit_int64(JobId, ed1));
441 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
442 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
443 num_rows = sql_num_rows(mdb);
444 Dmsg1(200, "Num rows=%d\n", num_rows);
446 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
452 *VolParams = Vols = (VOL_PARAMS *)malloc(stat * sizeof(VOL_PARAMS));
453 SId = (DBId_t *)malloc(stat * sizeof(DBId_t));
455 for (i=0; i < stat; i++) {
456 if ((row = sql_fetch_row(mdb)) == NULL) {
457 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
458 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
463 uint32_t StartBlock, EndBlock, StartFile, EndFile;
464 bstrncpy(Vols[i].VolumeName, row[0], MAX_NAME_LENGTH);
465 bstrncpy(Vols[i].MediaType, row[1], MAX_NAME_LENGTH);
466 Vols[i].FirstIndex = str_to_uint64(row[2]);
467 Vols[i].LastIndex = str_to_uint64(row[3]);
468 StartFile = str_to_uint64(row[4]);
469 EndFile = str_to_uint64(row[5]);
470 StartBlock = str_to_uint64(row[6]);
471 EndBlock = str_to_uint64(row[7]);
472 Vols[i].StartAddr = (((uint64_t)StartFile)<<32) | StartBlock;
473 Vols[i].EndAddr = (((uint64_t)EndFile)<<32) | EndBlock;
474 Vols[i].Slot = str_to_uint64(row[8]);
475 StorageId = str_to_uint64(row[9]);
476 Vols[i].InChanger = str_to_uint64(row[10]);
477 Vols[i].Storage[0] = 0;
481 for (i=0; i < stat; i++) {
483 Mmsg(mdb->cmd, "SELECT Name from Storage WHERE StorageId=%s",
484 edit_int64(SId[i], ed1));
485 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
486 if ((row = sql_fetch_row(mdb)) && row[0]) {
487 bstrncpy(Vols[i].Storage, row[0], MAX_NAME_LENGTH);
496 sql_free_result(mdb);
505 * Get the number of pool records
507 * Returns: -1 on failure
510 int db_get_num_pool_records(JCR *jcr, B_DB *mdb)
515 Mmsg(mdb->cmd, "SELECT count(*) from Pool");
516 stat = get_sql_record_max(jcr, mdb);
522 * This function returns a list of all the Pool record ids.
523 * The caller must free ids if non-NULL.
525 * Returns 0: on failure
528 int db_get_pool_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
537 Mmsg(mdb->cmd, "SELECT PoolId FROM Pool");
538 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
539 *num_ids = sql_num_rows(mdb);
541 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
542 while ((row = sql_fetch_row(mdb)) != NULL) {
543 id[i++] = str_to_uint64(row[0]);
547 sql_free_result(mdb);
550 Mmsg(mdb->errmsg, _("Pool id select failed: ERR=%s\n"), sql_strerror(mdb));
551 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
559 * This function returns a list of all the Client record ids.
560 * The caller must free ids if non-NULL.
562 * Returns 0: on failure
565 int db_get_client_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
574 Mmsg(mdb->cmd, "SELECT ClientId FROM Client ORDER BY Name");
575 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
576 *num_ids = sql_num_rows(mdb);
578 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
579 while ((row = sql_fetch_row(mdb)) != NULL) {
580 id[i++] = str_to_uint64(row[0]);
584 sql_free_result(mdb);
587 Mmsg(mdb->errmsg, _("Client id select failed: ERR=%s\n"), sql_strerror(mdb));
588 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
599 * If the PoolId is non-zero, we get its record,
600 * otherwise, we search on the PoolName
602 * Returns: false on failure
605 bool db_get_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr)
613 if (pdbr->PoolId != 0) { /* find by id */
615 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
616 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
617 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId,ScratchPoolId,"
618 "ActionOnPurge FROM Pool WHERE Pool.PoolId=%s",
619 edit_int64(pdbr->PoolId, ed1));
620 } else { /* find by name */
622 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
623 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
624 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId,ScratchPoolId,"
625 "ActionOnPurge FROM Pool WHERE Pool.Name='%s'",
628 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
629 num_rows = sql_num_rows(mdb);
632 Mmsg1(mdb->errmsg, _("More than one Pool!: %s\n"),
633 edit_uint64(num_rows, ed1));
634 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
635 } else if (num_rows == 1) {
636 if ((row = sql_fetch_row(mdb)) == NULL) {
637 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
638 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
640 pdbr->PoolId = str_to_int64(row[0]);
641 bstrncpy(pdbr->Name, row[1]!=NULL?row[1]:"", sizeof(pdbr->Name));
642 pdbr->NumVols = str_to_int64(row[2]);
643 pdbr->MaxVols = str_to_int64(row[3]);
644 pdbr->UseOnce = str_to_int64(row[4]);
645 pdbr->UseCatalog = str_to_int64(row[5]);
646 pdbr->AcceptAnyVolume = str_to_int64(row[6]);
647 pdbr->AutoPrune = str_to_int64(row[7]);
648 pdbr->Recycle = str_to_int64(row[8]);
649 pdbr->VolRetention = str_to_int64(row[9]);
650 pdbr->VolUseDuration = str_to_int64(row[10]);
651 pdbr->MaxVolJobs = str_to_int64(row[11]);
652 pdbr->MaxVolFiles = str_to_int64(row[12]);
653 pdbr->MaxVolBytes = str_to_uint64(row[13]);
654 bstrncpy(pdbr->PoolType, row[14]!=NULL?row[14]:"", sizeof(pdbr->PoolType));
655 pdbr->LabelType = str_to_int64(row[15]);
656 bstrncpy(pdbr->LabelFormat, row[16]!=NULL?row[16]:"", sizeof(pdbr->LabelFormat));
657 pdbr->RecyclePoolId = str_to_int64(row[17]);
658 pdbr->ScratchPoolId = str_to_int64(row[18]);
659 pdbr->ActionOnPurge = str_to_int32(row[19]);
663 sql_free_result(mdb);
667 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
668 edit_int64(pdbr->PoolId, ed1));
669 NumVols = get_sql_record_max(jcr, mdb);
670 Dmsg2(400, "Actual NumVols=%d Pool NumVols=%d\n", NumVols, pdbr->NumVols);
671 if (NumVols != pdbr->NumVols) {
672 pdbr->NumVols = NumVols;
673 db_update_pool_record(jcr, mdb, pdbr);
676 Mmsg(mdb->errmsg, _("Pool record not found in Catalog.\n"));
684 * If the ClientId is non-zero, we get its record,
685 * otherwise, we search on the Client Name
687 * Returns: 0 on failure
690 int db_get_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cdbr)
698 if (cdbr->ClientId != 0) { /* find by id */
700 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
701 "FROM Client WHERE Client.ClientId=%s",
702 edit_int64(cdbr->ClientId, ed1));
703 } else { /* find by name */
705 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
706 "FROM Client WHERE Client.Name='%s'", cdbr->Name);
709 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
710 num_rows = sql_num_rows(mdb);
712 Mmsg1(mdb->errmsg, _("More than one Client!: %s\n"),
713 edit_uint64(num_rows, ed1));
714 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
715 } else if (num_rows == 1) {
716 if ((row = sql_fetch_row(mdb)) == NULL) {
717 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
718 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
720 cdbr->ClientId = str_to_int64(row[0]);
721 bstrncpy(cdbr->Name, row[1]!=NULL?row[1]:"", sizeof(cdbr->Name));
722 bstrncpy(cdbr->Uname, row[2]!=NULL?row[2]:"", sizeof(cdbr->Uname));
723 cdbr->AutoPrune = str_to_int64(row[3]);
724 cdbr->FileRetention = str_to_int64(row[4]);
725 cdbr->JobRetention = str_to_int64(row[5]);
729 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
731 sql_free_result(mdb);
733 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
742 * Returns: 0 on failure
745 int db_get_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
751 Mmsg(mdb->cmd, "SELECT \"MinValue\",\"MaxValue\",CurrentValue,WrapCounter "
752 "FROM Counters WHERE Counter='%s'", cr->Counter);
754 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
755 num_rows = sql_num_rows(mdb);
757 /* If more than one, report error, but return first row */
759 Mmsg1(mdb->errmsg, _("More than one Counter!: %d\n"), num_rows);
760 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
763 if ((row = sql_fetch_row(mdb)) == NULL) {
764 Mmsg1(mdb->errmsg, _("error fetching Counter row: %s\n"), sql_strerror(mdb));
765 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
766 sql_free_result(mdb);
770 cr->MinValue = str_to_int64(row[0]);
771 cr->MaxValue = str_to_int64(row[1]);
772 cr->CurrentValue = str_to_int64(row[2]);
774 bstrncpy(cr->WrapCounter, row[3], sizeof(cr->WrapCounter));
776 cr->WrapCounter[0] = 0;
778 sql_free_result(mdb);
782 sql_free_result(mdb);
784 Mmsg(mdb->errmsg, _("Counter record: %s not found in Catalog.\n"), cr->Counter);
793 * If the FileSetId is non-zero, we get its record,
794 * otherwise, we search on the name
796 * Returns: 0 on failure
799 int db_get_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
807 if (fsr->FileSetId != 0) { /* find by id */
809 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
810 "WHERE FileSetId=%s",
811 edit_int64(fsr->FileSetId, ed1));
812 } else { /* find by name */
814 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
815 "WHERE FileSet='%s' ORDER BY CreateTime DESC LIMIT 1", fsr->FileSet);
818 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
819 num_rows = sql_num_rows(mdb);
822 Mmsg1(mdb->errmsg, _("Error got %s FileSets but expected only one!\n"),
823 edit_uint64(num_rows, ed1));
824 sql_data_seek(mdb, num_rows-1);
826 if ((row = sql_fetch_row(mdb)) == NULL) {
827 Mmsg1(mdb->errmsg, _("FileSet record \"%s\" not found.\n"), fsr->FileSet);
829 fsr->FileSetId = str_to_int64(row[0]);
830 bstrncpy(fsr->FileSet, row[1]!=NULL?row[1]:"", sizeof(fsr->FileSet));
831 bstrncpy(fsr->MD5, row[2]!=NULL?row[2]:"", sizeof(fsr->MD5));
832 bstrncpy(fsr->cCreateTime, row[3]!=NULL?row[3]:"", sizeof(fsr->cCreateTime));
833 stat = fsr->FileSetId;
835 sql_free_result(mdb);
837 Mmsg(mdb->errmsg, _("FileSet record not found in Catalog.\n"));
845 * Get the number of Media records
847 * Returns: -1 on failure
850 int db_get_num_media_records(JCR *jcr, B_DB *mdb)
855 Mmsg(mdb->cmd, "SELECT count(*) from Media");
856 stat = get_sql_record_max(jcr, mdb);
862 * This function returns a list of all the Media record ids for
863 * the current Pool, the correct Media Type, Recyle, Enabled, StorageId, VolBytes
864 * VolumeName if specified
865 * The caller must free ids if non-NULL.
867 * Returns false: on failure
870 bool db_get_media_ids(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr, int *num_ids, uint32_t *ids[])
877 char buf[MAX_NAME_LENGTH*3]; /* Can contain MAX_NAME_LENGTH*2+1 + AND ....='' */
878 char esc[MAX_NAME_LENGTH*2+1];
883 Mmsg(mdb->cmd, "SELECT DISTINCT MediaId FROM Media WHERE Recycle=%d AND Enabled=%d ",
884 mr->Recycle, mr->Enabled);
886 if (*mr->MediaType) {
887 db_escape_string(jcr, mdb, esc, mr->MediaType, strlen(mr->MediaType));
888 bsnprintf(buf, sizeof(buf), "AND MediaType='%s' ", esc);
889 pm_strcat(mdb->cmd, buf);
893 bsnprintf(buf, sizeof(buf), "AND StorageId=%s ", edit_uint64(mr->StorageId, ed1));
894 pm_strcat(mdb->cmd, buf);
898 bsnprintf(buf, sizeof(buf), "AND PoolId=%s ", edit_uint64(mr->PoolId, ed1));
899 pm_strcat(mdb->cmd, buf);
903 bsnprintf(buf, sizeof(buf), "AND VolBytes > %s ", edit_uint64(mr->VolBytes, ed1));
904 pm_strcat(mdb->cmd, buf);
907 if (*mr->VolumeName) {
908 db_escape_string(jcr, mdb, esc, mr->VolumeName, strlen(mr->VolumeName));
909 bsnprintf(buf, sizeof(buf), "AND VolumeName = '%s' ", esc);
910 pm_strcat(mdb->cmd, buf);
913 if (*mr->VolStatus) {
914 db_escape_string(jcr, mdb, esc, mr->VolStatus, strlen(mr->VolStatus));
915 bsnprintf(buf, sizeof(buf), "AND VolStatus = '%s' ", esc);
916 pm_strcat(mdb->cmd, buf);
919 Dmsg1(100, "q=%s\n", mdb->cmd);
921 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
922 *num_ids = sql_num_rows(mdb);
924 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
925 while ((row = sql_fetch_row(mdb)) != NULL) {
926 id[i++] = str_to_uint64(row[0]);
930 sql_free_result(mdb);
933 Mmsg(mdb->errmsg, _("Media id select failed: ERR=%s\n"), sql_strerror(mdb));
934 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
943 * This function returns a list of all the DBIds that are returned
946 * Returns false: on failure
949 bool db_get_query_dbids(JCR *jcr, B_DB *mdb, POOL_MEM &query, dbid_list &ids)
957 if (QUERY_DB(jcr, mdb, query.c_str())) {
958 ids.num_ids = sql_num_rows(mdb);
959 if (ids.num_ids > 0) {
960 if (ids.max_ids < ids.num_ids) {
962 ids.DBId = (DBId_t *)malloc(ids.num_ids * sizeof(DBId_t));
964 while ((row = sql_fetch_row(mdb)) != NULL) {
965 ids.DBId[i++] = str_to_uint64(row[0]);
968 sql_free_result(mdb);
971 Mmsg(mdb->errmsg, _("query dbids failed: ERR=%s\n"), sql_strerror(mdb));
972 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
982 * Returns: false: on failure
985 bool db_get_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
993 if (mr->MediaId == 0 && mr->VolumeName[0] == 0) {
994 Mmsg(mdb->cmd, "SELECT count(*) from Media");
995 mr->MediaId = get_sql_record_max(jcr, mdb);
999 if (mr->MediaId != 0) { /* find by id */
1000 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
1001 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
1002 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
1003 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
1004 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
1005 "Enabled,LocationId,RecycleCount,InitialWrite,"
1006 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
1007 "FROM Media WHERE MediaId=%s",
1008 edit_int64(mr->MediaId, ed1));
1009 } else { /* find by name */
1010 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
1011 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
1012 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
1013 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
1014 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
1015 "Enabled,LocationId,RecycleCount,InitialWrite,"
1016 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
1017 "FROM Media WHERE VolumeName='%s'", mr->VolumeName);
1020 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
1022 num_rows = sql_num_rows(mdb);
1024 Mmsg1(mdb->errmsg, _("More than one Volume!: %s\n"),
1025 edit_uint64(num_rows, ed1));
1026 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1027 } else if (num_rows == 1) {
1028 if ((row = sql_fetch_row(mdb)) == NULL) {
1029 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
1030 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
1033 mr->MediaId = str_to_int64(row[0]);
1034 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
1035 mr->VolJobs = str_to_int64(row[2]);
1036 mr->VolFiles = str_to_int64(row[3]);
1037 mr->VolBlocks = str_to_int64(row[4]);
1038 mr->VolBytes = str_to_uint64(row[5]);
1039 mr->VolMounts = str_to_int64(row[6]);
1040 mr->VolErrors = str_to_int64(row[7]);
1041 mr->VolWrites = str_to_int64(row[8]);
1042 mr->MaxVolBytes = str_to_uint64(row[9]);
1043 mr->VolCapacityBytes = str_to_uint64(row[10]);
1044 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
1045 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
1046 mr->PoolId = str_to_int64(row[13]);
1047 mr->VolRetention = str_to_uint64(row[14]);
1048 mr->VolUseDuration = str_to_uint64(row[15]);
1049 mr->MaxVolJobs = str_to_int64(row[16]);
1050 mr->MaxVolFiles = str_to_int64(row[17]);
1051 mr->Recycle = str_to_int64(row[18]);
1052 mr->Slot = str_to_int64(row[19]);
1053 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
1054 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
1055 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
1056 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
1057 mr->InChanger = str_to_uint64(row[22]);
1058 mr->EndFile = str_to_uint64(row[23]);
1059 mr->EndBlock = str_to_uint64(row[24]);
1060 mr->VolParts = str_to_int64(row[25]);
1061 mr->LabelType = str_to_int64(row[26]);
1062 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
1063 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
1064 mr->StorageId = str_to_int64(row[28]);
1065 mr->Enabled = str_to_int64(row[29]);
1066 mr->LocationId = str_to_int64(row[30]);
1067 mr->RecycleCount = str_to_int64(row[31]);
1068 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
1069 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
1070 mr->ScratchPoolId = str_to_int64(row[33]);
1071 mr->RecyclePoolId = str_to_int64(row[34]);
1072 mr->VolReadTime = str_to_int64(row[35]);
1073 mr->VolWriteTime = str_to_int64(row[36]);
1074 mr->ActionOnPurge = str_to_int32(row[37]);
1079 if (mr->MediaId != 0) {
1080 Mmsg1(mdb->errmsg, _("Media record MediaId=%s not found.\n"),
1081 edit_int64(mr->MediaId, ed1));
1083 Mmsg1(mdb->errmsg, _("Media record for Volume \"%s\" not found.\n"),
1087 sql_free_result(mdb);
1089 if (mr->MediaId != 0) {
1090 Mmsg(mdb->errmsg, _("Media record for MediaId=%u not found in Catalog.\n"),
1093 Mmsg(mdb->errmsg, _("Media record for Vol=%s not found in Catalog.\n"),
1100 /* Remove all MD5 from a query (can save lot of memory with many files) */
1101 static void strip_md5(char *q)
1104 while ((p = strstr(p, ", MD5"))) {
1105 memset(p, ' ', 5 * sizeof(char));
1110 * Find the last "accurate" backup state (that can take deleted files in
1112 * 1) Get all files with jobid in list (F subquery)
1113 * Get all files in BaseFiles with jobid in list
1114 * 2) Take only the last version of each file (Temp subquery) => accurate list
1116 * 3) Join the result to file table to get fileindex, jobid and lstat information
1118 * TODO: See if we can do the SORT only if needed (as an argument)
1120 bool db_get_file_list(JCR *jcr, B_DB *mdb, char *jobids,
1121 bool use_md5, bool use_delta,
1122 DB_RESULT_HANDLER *result_handler, void *ctx)
1126 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1130 POOL_MEM buf(PM_MESSAGE);
1131 POOL_MEM buf2(PM_MESSAGE);
1133 Mmsg(buf2, select_recent_version_with_basejob_and_delta[db_get_type_index(mdb)],
1134 jobids, jobids, jobids, jobids);
1137 Mmsg(buf2, select_recent_version_with_basejob[db_get_type_index(mdb)],
1138 jobids, jobids, jobids, jobids);
1141 /* bsr code is optimized for JobId sorted, with Delta, we need to get
1142 * them ordered by date. JobTDate and JobId can be mixed if using Copy
1146 "SELECT Path.Path, Filename.Name, T1.FileIndex, T1.JobId, LStat, MarkId, MD5 "
1147 "FROM ( %s ) AS T1 "
1148 "JOIN Filename ON (Filename.FilenameId = T1.FilenameId) "
1149 "JOIN Path ON (Path.PathId = T1.PathId) "
1150 "WHERE FileIndex > 0 "
1151 "ORDER BY T1.JobTDate, FileIndex ASC",/* Return sorted by JobTDate */
1152 /* FileIndex for restore code */
1156 strip_md5(buf.c_str());
1159 Dmsg1(100, "q=%s\n", buf.c_str());
1161 return db_big_sql_query(mdb, buf.c_str(), result_handler, ctx);
1165 * This procedure gets the base jobid list used by jobids,
1167 bool db_get_used_base_jobids(JCR *jcr, B_DB *mdb,
1168 POOLMEM *jobids, db_list_ctx *result)
1172 "SELECT DISTINCT BaseJobId "
1173 " FROM Job JOIN BaseFiles USING (JobId) "
1174 " WHERE Job.HasBase = 1 "
1175 " AND Job.JobId IN (%s) ", jobids);
1176 return db_sql_query(mdb, buf.c_str(), db_list_handler, result);
1180 * The decision do change an incr/diff was done before
1182 * Differential : get the last full id
1183 * Incremental : get the last full + last diff + last incr(s) ids
1185 * If you specify jr->StartTime, it will be used to limit the search
1186 * in the time. (usually now)
1188 * TODO: look and merge from ua_restore.c
1190 bool db_accurate_get_jobids(JCR *jcr, B_DB *mdb,
1191 JOB_DBR *jr, db_list_ctx *jobids)
1194 char clientid[50], jobid[50], filesetid[50];
1195 char date[MAX_TIME_LENGTH];
1196 POOL_MEM query(PM_FNAME);
1198 /* Take the current time as upper limit if nothing else specified */
1199 utime_t StartTime = (jr->StartTime)?jr->StartTime:time(NULL);
1201 bstrutime(date, sizeof(date), StartTime + 1);
1204 /* First, find the last good Full backup for this job/client/fileset */
1205 Mmsg(query, create_temp_accurate_jobids[db_get_type_index(mdb)],
1206 edit_uint64(jcr->JobId, jobid),
1207 edit_uint64(jr->ClientId, clientid),
1209 edit_uint64(jr->FileSetId, filesetid));
1211 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1215 if (jr->JobLevel == L_INCREMENTAL || jr->JobLevel == L_VIRTUAL_FULL) {
1216 /* Now, find the last differential backup after the last full */
1218 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1219 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1220 "FROM Job JOIN FileSet USING (FileSetId) "
1221 "WHERE ClientId = %s "
1222 "AND Level='D' AND JobStatus IN ('T','W') AND Type='B' "
1223 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1224 "AND StartTime < '%s' "
1225 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1226 "ORDER BY Job.JobTDate DESC LIMIT 1 ",
1233 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1237 /* We just have to take all incremental after the last Full/Diff */
1239 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1240 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1241 "FROM Job JOIN FileSet USING (FileSetId) "
1242 "WHERE ClientId = %s "
1243 "AND Level='I' AND JobStatus IN ('T','W') AND Type='B' "
1244 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1245 "AND StartTime < '%s' "
1246 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1247 "ORDER BY Job.JobTDate DESC ",
1253 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1258 /* build a jobid list ie: 1,2,3,4 */
1259 Mmsg(query, "SELECT JobId FROM btemp3%s ORDER by JobTDate", jobid);
1260 db_sql_query(mdb, query.c_str(), db_list_handler, jobids);
1261 Dmsg1(1, "db_accurate_get_jobids=%s\n", jobids->list);
1265 Mmsg(query, "DROP TABLE btemp3%s", jobid);
1266 db_sql_query(mdb, query.c_str(), NULL, NULL);
1271 bool db_get_base_file_list(JCR *jcr, B_DB *mdb, bool use_md5,
1272 DB_RESULT_HANDLER *result_handler, void *ctx)
1274 POOL_MEM buf(PM_MESSAGE);
1277 "SELECT Path, Name, FileIndex, JobId, LStat, 0 As MarkId, MD5 "
1278 "FROM new_basefile%lld ORDER BY JobId, FileIndex ASC",
1279 (uint64_t) jcr->JobId);
1282 strip_md5(buf.c_str());
1284 return db_sql_query(mdb, buf.c_str(), result_handler, ctx);
1287 bool db_get_base_jobid(JCR *jcr, B_DB *mdb, JOB_DBR *jr, JobId_t *jobid)
1289 POOL_MEM query(PM_FNAME);
1292 char date[MAX_TIME_LENGTH];
1294 // char clientid[50], filesetid[50];
1299 StartTime = (jr->StartTime)?jr->StartTime:time(NULL);
1300 bstrutime(date, sizeof(date), StartTime + 1);
1302 /* we can take also client name, fileset, etc... */
1305 "SELECT JobId, Job, StartTime, EndTime, JobTDate, PurgedFiles "
1307 // "JOIN FileSet USING (FileSetId) JOIN Client USING (ClientId) "
1308 "WHERE Job.Name = '%s' "
1309 "AND Level='B' AND JobStatus IN ('T','W') AND Type='B' "
1310 // "AND FileSet.FileSet= '%s' "
1311 // "AND Client.Name = '%s' "
1312 "AND StartTime<'%s' "
1313 "ORDER BY Job.JobTDate DESC LIMIT 1",
1315 // edit_uint64(jr->ClientId, clientid),
1316 // edit_uint64(jr->FileSetId, filesetid));
1319 Dmsg1(10, "db_get_base_jobid q=%s\n", query.c_str());
1320 if (!db_sql_query(mdb, query.c_str(), db_int64_handler, &lctx)) {
1323 *jobid = (JobId_t) lctx.value;
1325 Dmsg1(10, "db_get_base_jobid=%lld\n", *jobid);
1332 /* Get JobIds associated with a volume */
1333 bool db_get_volume_jobids(JCR *jcr, B_DB *mdb,
1334 MEDIA_DBR *mr, db_list_ctx *lst)
1340 Mmsg(mdb->cmd, "SELECT DISTINCT JobId FROM JobMedia WHERE MediaId=%s",
1341 edit_int64(mr->MediaId, ed1));
1342 ret = db_sql_query(mdb, mdb->cmd, db_list_handler, lst);
1347 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */