2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2017 Kern Sibbald
6 The original author of Bacula is Kern Sibbald, with contributions
7 from many others, a complete list can be found in the file AUTHORS.
9 You may use this file and others of this release according to the
10 license defined in the LICENSE file, which includes the Affero General
11 Public License, v3.0 ("AGPLv3") and some additional permissions and
12 terms pursuant to its AGPLv3 Section 7.
14 This notice must be preserved when any source code is
15 conveyed and/or propagated.
17 Bacula(R) is a registered trademark of Kern Sibbald.
20 * Bacula Catalog Database Get record interface routines
21 * Note, these routines generally get a record by id or
22 * by name. If more logic is involved, the routine
25 * Written by Kern Sibbald, March 2000
30 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL
34 /* -----------------------------------------------------------------------
36 * Generic Routines (or almost generic)
38 * -----------------------------------------------------------------------
42 * Given a full filename (with path), look up the File record
43 * (with attributes) in the database.
45 * Returns: false on failure
46 * true on success with the File record in FILE_DBR
48 bool BDB::bdb_get_file_attributes_record(JCR *jcr, char *afname, JOB_DBR *jr, FILE_DBR *fdbr)
52 Dmsg1(500, "db_get_file_att_record fname=%s \n", afname);
56 split_path_and_file(jcr, this, afname);
58 fdbr->FilenameId = bdb_get_filename_record(jcr);
60 fdbr->PathId = bdb_get_path_record(jcr);
62 ok = bdb_get_file_record(jcr, jr, fdbr);
73 * DO NOT use Jmsg in this routine.
75 * Note in this routine, we do not use Jmsg because it may be
76 * called to get attributes of a non-existent file, which is
77 * "normal" if a new file is found during Verify.
79 * The following is a bit of a kludge: because we always backup a
80 * directory entry, we can end up with two copies of the directory
81 * in the backup. One is when we encounter the directory and find
82 * we cannot recurse into it, and the other is when we find an
83 * explicit mention of the directory. This can also happen if the
84 * use includes the directory twice. In this case, Verify
85 * VolumeToCatalog fails because we have two copies in the catalog, and
86 * only the first one is marked (twice). So, when calling from Verify,
87 * VolumeToCatalog jr is not NULL, and we know jr->FileIndex is the fileindex
88 * of the version of the directory/file we actually want and do
89 * a more explicit SQL search.
91 * Returns: false on failure
95 bool BDB::bdb_get_file_record(JCR *jcr, JOB_DBR *jr, FILE_DBR *fdbr)
99 char ed1[50], ed2[50], ed3[50];
101 switch (jcr->getJobLevel()) {
102 case L_VERIFY_VOLUME_TO_CATALOG:
104 "SELECT FileId, LStat, MD5 FROM File WHERE File.JobId=%s AND File.PathId=%s AND "
105 "File.FilenameId=%s AND File.FileIndex=%u",
106 edit_int64(fdbr->JobId, ed1),
107 edit_int64(fdbr->PathId, ed2),
108 edit_int64(fdbr->FilenameId,ed3),
111 case L_VERIFY_DISK_TO_CATALOG:
113 "SELECT FileId, LStat, MD5 FROM File,Job WHERE "
114 "File.JobId=Job.JobId AND File.PathId=%s AND "
115 "File.FilenameId=%s AND Job.Type='B' AND Job.JobStatus IN ('T','W') AND "
116 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
117 edit_int64(fdbr->PathId, ed1),
118 edit_int64(fdbr->FilenameId, ed2),
119 edit_int64(jr->ClientId,ed3));
123 "SELECT FileId, LStat, MD5 FROM File WHERE File.JobId=%s AND File.PathId=%s AND "
124 "File.FilenameId=%s",
125 edit_int64(fdbr->JobId, ed1),
126 edit_int64(fdbr->PathId, ed2),
127 edit_int64(fdbr->FilenameId,ed3));
131 Dmsg3(450, "Get_file_record JobId=%u FilenameId=%u PathId=%u\n",
132 fdbr->JobId, fdbr->FilenameId, fdbr->PathId);
134 Dmsg1(100, "Query=%s\n", cmd);
136 if (QueryDB(jcr, cmd)) {
137 Dmsg1(100, "get_file_record sql_num_rows()=%d\n", sql_num_rows());
138 if (sql_num_rows() >= 1) {
139 if ((row = sql_fetch_row()) == NULL) {
140 Mmsg1(errmsg, _("Error fetching row: %s\n"), sql_strerror());
142 fdbr->FileId = (FileId_t)str_to_int64(row[0]);
143 bstrncpy(fdbr->LStat, row[1], sizeof(fdbr->LStat));
144 bstrncpy(fdbr->Digest, row[2], sizeof(fdbr->Digest));
146 if (sql_num_rows() > 1) {
147 Mmsg3(errmsg, _("get_file_record want 1 got rows=%d PathId=%s FilenameId=%s\n"),
149 edit_int64(fdbr->PathId, ed1),
150 edit_int64(fdbr->FilenameId, ed2));
151 Dmsg1(000, "=== Problem! %s", errmsg);
155 Mmsg2(errmsg, _("File record for PathId=%s FilenameId=%s not found.\n"),
156 edit_int64(fdbr->PathId, ed1),
157 edit_int64(fdbr->FilenameId, ed2));
161 Mmsg(errmsg, _("File record not found in Catalog.\n"));
167 * Get Filename record
168 * Returns: 0 on failure
169 * FilenameId on success
171 * DO NOT use Jmsg in this routine (see notes for get_file_record)
173 int BDB::bdb_get_filename_record(JCR *jcr)
178 esc_name = check_pool_memory_size(esc_name, 2*fnl+2);
179 bdb_escape_string(jcr, esc_name, fname, fnl);
181 Mmsg(cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
182 if (QueryDB(jcr, cmd)) {
184 if (sql_num_rows() > 1) {
185 Mmsg2(errmsg, _("More than one Filename!: %s for file: %s\n"),
186 edit_uint64(sql_num_rows(), ed1), fname);
187 Jmsg(jcr, M_WARNING, 0, "%s", errmsg);
189 if (sql_num_rows() >= 1) {
190 if ((row = sql_fetch_row()) == NULL) {
191 Mmsg1(errmsg, _("error fetching row: %s\n"), sql_strerror());
193 FilenameId = str_to_int64(row[0]);
194 if (FilenameId <= 0) {
195 Mmsg2(errmsg, _("Get DB Filename record %s found bad record: %d\n"),
201 Mmsg1(errmsg, _("Filename record: %s not found.\n"), fname);
205 Mmsg(errmsg, _("Filename record: %s not found in Catalog.\n"), fname);
212 * Returns: 0 on failure
215 * DO NOT use Jmsg in this routine (see notes for get_file_record)
217 int BDB::bdb_get_path_record(JCR *jcr)
222 esc_name = check_pool_memory_size(esc_name, 2*pnl+2);
223 bdb_escape_string(jcr, esc_name, path, pnl);
225 if (cached_path_id != 0 && cached_path_len == pnl &&
226 strcmp(cached_path, path) == 0) {
227 return cached_path_id;
230 Mmsg(cmd, "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
232 if (QueryDB(jcr, cmd)) {
234 if (sql_num_rows() > 1) {
235 Mmsg2(errmsg, _("More than one Path!: %s for path: %s\n"),
236 edit_uint64(sql_num_rows(), ed1), path);
237 Jmsg(jcr, M_WARNING, 0, "%s", errmsg);
239 /* Even if there are multiple paths, take the first one */
240 if (sql_num_rows() >= 1) {
241 if ((row = sql_fetch_row()) == NULL) {
242 Mmsg1(errmsg, _("error fetching row: %s\n"), sql_strerror());
244 PathId = str_to_int64(row[0]);
246 Mmsg2(errmsg, _("Get DB path record %s found bad record: %s\n"),
247 cmd, edit_int64(PathId, ed1));
251 if (PathId != cached_path_id) {
252 cached_path_id = PathId;
253 cached_path_len = pnl;
254 pm_strcpy(cached_path, path);
259 Mmsg1(errmsg, _("Path record: %s not found.\n"), path);
263 Mmsg(errmsg, _("Path record: %s not found in Catalog.\n"), path);
270 * Get Job record for given JobId or Job name
271 * Returns: false on failure
274 bool BDB::bdb_get_job_record(JCR *jcr, JOB_DBR *jr)
278 char esc[MAX_ESCAPE_NAME_LENGTH];
281 if (jr->JobId == 0) {
282 bdb_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
283 Mmsg(cmd, "SELECT VolSessionId,VolSessionTime,"
284 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
285 "Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,"
286 "SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles "
287 "FROM Job WHERE Job='%s'", esc);
289 Mmsg(cmd, "SELECT VolSessionId,VolSessionTime,"
290 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
291 "Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,"
292 "SchedTime,RealEndTime,ReadBytes,HasBase,PurgedFiles "
293 "FROM Job WHERE JobId=%s",
294 edit_int64(jr->JobId, ed1));
297 if (!QueryDB(jcr, cmd)) {
299 return false; /* failed */
301 if ((row = sql_fetch_row()) == NULL) {
302 Mmsg1(errmsg, _("No Job found for JobId %s\n"), edit_int64(jr->JobId, ed1));
305 return false; /* failed */
308 jr->VolSessionId = str_to_uint64(row[0]);
309 jr->VolSessionTime = str_to_uint64(row[1]);
310 jr->PoolId = str_to_int64(row[2]);
311 bstrncpy(jr->cStartTime, row[3]!=NULL?row[3]:"", sizeof(jr->cStartTime));
312 bstrncpy(jr->cEndTime, row[4]!=NULL?row[4]:"", sizeof(jr->cEndTime));
313 jr->JobFiles = str_to_int64(row[5]);
314 jr->JobBytes = str_to_int64(row[6]);
315 jr->JobTDate = str_to_int64(row[7]);
316 bstrncpy(jr->Job, row[8]!=NULL?row[8]:"", sizeof(jr->Job));
317 jr->JobStatus = row[9]!=NULL?(int)*row[9]:JS_FatalError;
318 jr->JobType = row[10]!=NULL?(int)*row[10]:JT_BACKUP;
319 jr->JobLevel = row[11]!=NULL?(int)*row[11]:L_NONE;
320 jr->ClientId = str_to_uint64(row[12]!=NULL?row[12]:(char *)"");
321 bstrncpy(jr->Name, row[13]!=NULL?row[13]:"", sizeof(jr->Name));
322 jr->PriorJobId = str_to_uint64(row[14]!=NULL?row[14]:(char *)"");
323 bstrncpy(jr->cRealEndTime, row[15]!=NULL?row[15]:"", sizeof(jr->cRealEndTime));
324 if (jr->JobId == 0) {
325 jr->JobId = str_to_int64(row[16]);
327 jr->FileSetId = str_to_int64(row[17]);
328 bstrncpy(jr->cSchedTime, row[18]!=NULL?row[18]:"", sizeof(jr->cSchedTime));
329 bstrncpy(jr->cRealEndTime, row[19]!=NULL?row[19]:"", sizeof(jr->cRealEndTime));
330 jr->ReadBytes = str_to_int64(row[20]);
331 jr->StartTime = str_to_utime(jr->cStartTime);
332 jr->SchedTime = str_to_utime(jr->cSchedTime);
333 jr->EndTime = str_to_utime(jr->cEndTime);
334 jr->RealEndTime = str_to_utime(jr->cRealEndTime);
335 jr->HasBase = str_to_int64(row[21]);
336 jr->PurgedFiles = str_to_int64(row[22]);
344 * Find VolumeNames for a given JobId
345 * Returns: 0 on error or no Volumes found
346 * number of volumes on success
347 * Volumes are concatenated in VolumeNames
348 * separated by a vertical bar (|) in the order
349 * that they were written.
351 * Returns: number of volumes on success
353 int BDB::bdb_get_job_volume_names(JCR *jcr, JobId_t JobId, POOLMEM **VolumeNames)
361 /* Get one entry per VolumeName, but "sort" by VolIndex */
363 "SELECT VolumeName,MAX(VolIndex) FROM JobMedia,Media WHERE "
364 "JobMedia.JobId=%s AND JobMedia.MediaId=Media.MediaId "
365 "GROUP BY VolumeName "
366 "ORDER BY 2 ASC", edit_int64(JobId,ed1));
368 Dmsg1(130, "VolNam=%s\n", cmd);
370 if (QueryDB(jcr, cmd)) {
371 Dmsg1(130, "Num rows=%d\n", sql_num_rows());
372 if (sql_num_rows() <= 0) {
373 Mmsg1(errmsg, _("No volumes found for JobId=%d\n"), JobId);
376 stat = sql_num_rows();
377 for (i=0; i < stat; i++) {
378 if ((row = sql_fetch_row()) == NULL) {
379 Mmsg2(errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror());
380 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
384 if (*VolumeNames[0] != 0) {
385 pm_strcat(VolumeNames, "|");
387 pm_strcat(VolumeNames, row[0]);
393 Mmsg(errmsg, _("No Volume for JobId %d found in Catalog.\n"), JobId);
400 * Find Volume parameters for a give JobId
401 * Returns: 0 on error or no Volumes found
402 * number of volumes on success
403 * List of Volumes and start/end file/blocks (malloced structure!)
405 * Returns: number of volumes on success
407 int BDB::bdb_get_job_volume_parameters(JCR *jcr, JobId_t JobId, VOL_PARAMS **VolParams)
413 VOL_PARAMS *Vols = NULL;
417 "SELECT VolumeName,MediaType,FirstIndex,LastIndex,StartFile,"
418 "JobMedia.EndFile,StartBlock,JobMedia.EndBlock,"
419 "Slot,StorageId,InChanger"
420 " FROM JobMedia,Media WHERE JobMedia.JobId=%s"
421 " AND JobMedia.MediaId=Media.MediaId ORDER BY VolIndex,JobMediaId",
422 edit_int64(JobId, ed1));
424 Dmsg1(130, "VolNam=%s\n", cmd);
425 if (QueryDB(jcr, cmd)) {
426 Dmsg1(200, "Num rows=%d\n", sql_num_rows());
427 if (sql_num_rows() <= 0) {
428 Mmsg1(errmsg, _("No volumes found for JobId=%d\n"), JobId);
431 stat = sql_num_rows();
434 *VolParams = Vols = (VOL_PARAMS *)malloc(stat * sizeof(VOL_PARAMS));
435 SId = (DBId_t *)malloc(stat * sizeof(DBId_t));
437 for (i=0; i < stat; i++) {
438 if ((row = sql_fetch_row()) == NULL) {
439 Mmsg2(errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror());
440 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
445 uint32_t StartBlock, EndBlock, StartFile, EndFile;
446 bstrncpy(Vols[i].VolumeName, row[0], MAX_NAME_LENGTH);
447 bstrncpy(Vols[i].MediaType, row[1], MAX_NAME_LENGTH);
448 Vols[i].FirstIndex = str_to_uint64(row[2]);
449 Vols[i].LastIndex = str_to_uint64(row[3]);
450 StartFile = str_to_uint64(row[4]);
451 EndFile = str_to_uint64(row[5]);
452 StartBlock = str_to_uint64(row[6]);
453 EndBlock = str_to_uint64(row[7]);
454 Vols[i].StartAddr = (((uint64_t)StartFile)<<32) | StartBlock;
455 Vols[i].EndAddr = (((uint64_t)EndFile)<<32) | EndBlock;
456 Vols[i].Slot = str_to_uint64(row[8]);
457 StorageId = str_to_uint64(row[9]);
458 Vols[i].InChanger = str_to_uint64(row[10]);
459 Vols[i].Storage[0] = 0;
463 for (i=0; i < stat; i++) {
465 Mmsg(cmd, "SELECT Name from Storage WHERE StorageId=%s",
466 edit_int64(SId[i], ed1));
467 if (QueryDB(jcr, cmd)) {
468 if ((row = sql_fetch_row()) && row[0]) {
469 bstrncpy(Vols[i].Storage, row[0], MAX_NAME_LENGTH);
487 * Get the number of pool records
489 * Returns: -1 on failure
492 int BDB::bdb_get_num_pool_records(JCR *jcr)
497 Mmsg(cmd, "SELECT count(*) from Pool");
498 stat = get_sql_record_max(jcr, this);
504 * This function returns a list of all the Pool record ids.
505 * The caller must free ids if non-NULL.
507 * Returns 0: on failure
510 int BDB::bdb_get_pool_ids(JCR *jcr, int *num_ids, uint32_t *ids[])
519 Mmsg(cmd, "SELECT PoolId FROM Pool ORDER By Name");
520 if (QueryDB(jcr, cmd)) {
521 *num_ids = sql_num_rows();
523 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
524 while ((row = sql_fetch_row()) != NULL) {
525 id[i++] = str_to_uint64(row[0]);
532 Mmsg(errmsg, _("Pool id select failed: ERR=%s\n"), sql_strerror());
533 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
541 * This function returns a list of all the Client record ids.
542 * The caller must free ids if non-NULL.
544 * Returns 0: on failure
547 int BDB::bdb_get_client_ids(JCR *jcr, int *num_ids, uint32_t *ids[])
556 Mmsg(cmd, "SELECT ClientId FROM Client ORDER BY Name ASC");
557 if (QueryDB(jcr, cmd)) {
558 *num_ids = sql_num_rows();
560 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
561 while ((row = sql_fetch_row()) != NULL) {
562 id[i++] = str_to_uint64(row[0]);
569 Mmsg(errmsg, _("Client id select failed: ERR=%s\n"), sql_strerror());
570 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
578 * Get Pool Id, Scratch Pool Id, Recycle Pool Id
579 * Returns: false on failure
582 bool BDB::bdb_get_pool_record(JCR *jcr, POOL_DBR *pdbr)
587 char esc[MAX_ESCAPE_NAME_LENGTH];
590 if (pdbr->PoolId != 0) { /* find by id */
592 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
593 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
594 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId,ScratchPoolId,"
595 "ActionOnPurge,CacheRetention FROM Pool WHERE Pool.PoolId=%s",
596 edit_int64(pdbr->PoolId, ed1));
597 } else { /* find by name */
598 bdb_escape_string(jcr, esc, pdbr->Name, strlen(pdbr->Name));
600 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
601 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
602 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId,ScratchPoolId,"
603 "ActionOnPurge,CacheRetention FROM Pool WHERE Pool.Name='%s'", esc);
605 if (QueryDB(jcr, cmd)) {
606 if (sql_num_rows() > 1) {
608 Mmsg1(errmsg, _("More than one Pool! Num=%s\n"),
609 edit_uint64(sql_num_rows(), ed1));
610 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
611 } else if (sql_num_rows() == 1) {
612 if ((row = sql_fetch_row()) == NULL) {
613 Mmsg1(errmsg, _("error fetching row: %s\n"), sql_strerror());
614 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
616 pdbr->PoolId = str_to_int64(row[0]);
617 bstrncpy(pdbr->Name, row[1]!=NULL?row[1]:"", sizeof(pdbr->Name));
618 pdbr->NumVols = str_to_int64(row[2]);
619 pdbr->MaxVols = str_to_int64(row[3]);
620 pdbr->UseOnce = str_to_int64(row[4]);
621 pdbr->UseCatalog = str_to_int64(row[5]);
622 pdbr->AcceptAnyVolume = str_to_int64(row[6]);
623 pdbr->AutoPrune = str_to_int64(row[7]);
624 pdbr->Recycle = str_to_int64(row[8]);
625 pdbr->VolRetention = str_to_int64(row[9]);
626 pdbr->VolUseDuration = str_to_int64(row[10]);
627 pdbr->MaxVolJobs = str_to_int64(row[11]);
628 pdbr->MaxVolFiles = str_to_int64(row[12]);
629 pdbr->MaxVolBytes = str_to_uint64(row[13]);
630 bstrncpy(pdbr->PoolType, row[14]!=NULL?row[14]:"", sizeof(pdbr->PoolType));
631 pdbr->LabelType = str_to_int64(row[15]);
632 bstrncpy(pdbr->LabelFormat, row[16]!=NULL?row[16]:"", sizeof(pdbr->LabelFormat));
633 pdbr->RecyclePoolId = str_to_int64(row[17]);
634 pdbr->ScratchPoolId = str_to_int64(row[18]);
635 pdbr->ActionOnPurge = str_to_int32(row[19]);
636 pdbr->CacheRetention = str_to_int64(row[20]);
647 * If the PoolId is non-zero, we get its record,
648 * otherwise, we search on the PoolName and we compute the number of volumes
650 * Returns: false on failure
653 bool BDB::bdb_get_pool_numvols(JCR *jcr, POOL_DBR *pdbr)
658 ok = db_get_pool_record(jcr, this, pdbr);
663 Mmsg(cmd, "SELECT count(*) from Media WHERE PoolId=%s",
664 edit_int64(pdbr->PoolId, ed1));
665 NumVols = get_sql_record_max(jcr, this);
666 Dmsg2(400, "Actual NumVols=%d Pool NumVols=%d\n", NumVols, pdbr->NumVols);
667 if (NumVols != pdbr->NumVols) {
668 pdbr->NumVols = NumVols;
669 db_update_pool_record(jcr, this, pdbr);
672 Mmsg(errmsg, _("Pool record not found in Catalog.\n"));
679 * Free restoreobject record (some fields are allocated through malloc)
681 void db_free_restoreobject_record(JCR *jcr, ROBJECT_DBR *rr)
686 if (rr->object_name) {
687 free(rr->object_name);
689 if (rr->plugin_name) {
690 free(rr->plugin_name);
692 rr->object = rr->plugin_name = rr->object_name = NULL;
696 * Get RestoreObject Record
697 * If the RestoreObjectId is non-zero, we get its record
699 * You must call db_free_restoreobject_record() after db_get_restoreobject_record()
701 * Returns: false on failure
704 bool BDB::bdb_get_restoreobject_record(JCR *jcr, ROBJECT_DBR *rr)
713 "SELECT ObjectName, PluginName, ObjectType, JobId, ObjectCompression, "
714 "RestoreObject, ObjectLength, ObjectFullLength, FileIndex "
715 "FROM RestoreObject "
716 "WHERE RestoreObjectId=%s",
717 edit_int64(rr->RestoreObjectId, ed1));
719 /* Using the JobId permits to check the Job name against ACLs and
720 * make sure that the current user is authorized to see the Restore object
723 pm_strcat(cmd, " AND JobId=");
724 pm_strcat(cmd, edit_int64(rr->JobId, ed1));
726 } else if (rr->JobIds && is_a_number_list(rr->JobIds)) {
727 pm_strcat(cmd, " AND JobId IN (");
728 pm_strcat(cmd, rr->JobIds);
732 if (QueryDB(jcr, cmd)) {
733 if (sql_num_rows() > 1) {
735 Mmsg1(errmsg, _("Error got %s RestoreObjects but expected only one!\n"),
736 edit_uint64(sql_num_rows(), ed1));
737 sql_data_seek(sql_num_rows()-1);
739 if ((row = sql_fetch_row()) == NULL) {
740 Mmsg1(errmsg, _("RestoreObject record \"%d\" not found.\n"), rr->RestoreObjectId);
742 db_free_restoreobject_record(jcr, rr);
743 rr->object_name = bstrdup(row[0]);
744 rr->plugin_name = bstrdup(row[1]);
745 rr->FileType = str_to_uint64(row[2]);
746 rr->JobId = str_to_uint64(row[3]);
747 rr->object_compression = str_to_int64(row[4]);
748 rr->object_len = str_to_uint64(row[6]);
749 rr->object_full_len = str_to_uint64(row[7]);
750 rr->object_index = str_to_uint64(row[8]);
752 bdb_unescape_object(jcr,
754 rr->object_len, /* Object length */
757 if (rr->object_compression > 0) {
758 int out_len = rr->object_full_len + 100; /* full length */
759 char *obj = (char *)malloc(out_len);
760 Zinflate(cmd, rr->object_len, obj, out_len); /* out_len is updated */
761 if (out_len != (int)rr->object_full_len) {
762 Dmsg3(10, "Decompression failed. Len wanted=%d got=%d. Object=%s\n",
763 rr->object_full_len, out_len, rr->plugin_name);
765 Mmsg(errmsg, _("Decompression failed. Len wanted=%d got=%d. Object=%s\n"),
766 rr->object_full_len, out_len, rr->plugin_name);
770 rr->object_len = out_len;
773 rr->object = (char *)malloc(sizeof(char)*(len+1));
774 memcpy(rr->object, cmd, len);
776 rr->object_len = len;
783 Mmsg(errmsg, _("RestoreObject record not found in Catalog.\n"));
791 * If the ClientId is non-zero, we get its record,
792 * otherwise, we search on the Client Name
794 * Returns: 0 on failure
797 int BDB::bdb_get_client_record(JCR *jcr, CLIENT_DBR *cdbr)
802 char esc[MAX_ESCAPE_NAME_LENGTH];
805 if (cdbr->ClientId != 0) { /* find by id */
807 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
808 "FROM Client WHERE Client.ClientId=%s",
809 edit_int64(cdbr->ClientId, ed1));
810 } else { /* find by name */
811 bdb_escape_string(jcr, esc, cdbr->Name, strlen(cdbr->Name));
813 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
814 "FROM Client WHERE Client.Name='%s'", esc);
817 if (QueryDB(jcr, cmd)) {
818 if (sql_num_rows() > 1) {
819 Mmsg1(errmsg, _("More than one Client!: %s\n"),
820 edit_uint64(sql_num_rows(), ed1));
821 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
822 } else if (sql_num_rows() == 1) {
823 if ((row = sql_fetch_row()) == NULL) {
824 Mmsg1(errmsg, _("error fetching row: %s\n"), sql_strerror());
825 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
827 cdbr->ClientId = str_to_int64(row[0]);
828 bstrncpy(cdbr->Name, row[1]!=NULL?row[1]:"", sizeof(cdbr->Name));
829 bstrncpy(cdbr->Uname, row[2]!=NULL?row[2]:"", sizeof(cdbr->Uname));
830 cdbr->AutoPrune = str_to_int64(row[3]);
831 cdbr->FileRetention = str_to_int64(row[4]);
832 cdbr->JobRetention = str_to_int64(row[5]);
836 Mmsg(errmsg, _("Client record not found in Catalog.\n"));
840 Mmsg(errmsg, _("Client record not found in Catalog.\n"));
849 * Returns: 0 on failure
852 bool BDB::bdb_get_counter_record(JCR *jcr, COUNTER_DBR *cr)
855 char esc[MAX_ESCAPE_NAME_LENGTH];
858 bdb_escape_string(jcr, esc, cr->Counter, strlen(cr->Counter));
860 Mmsg(cmd, select_counter_values[bdb_get_type_index()], esc);
861 if (QueryDB(jcr, cmd)) {
863 /* If more than one, report error, but return first row */
864 if (sql_num_rows() > 1) {
865 Mmsg1(errmsg, _("More than one Counter!: %d\n"), sql_num_rows());
866 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
868 if (sql_num_rows() >= 1) {
869 if ((row = sql_fetch_row()) == NULL) {
870 Mmsg1(errmsg, _("error fetching Counter row: %s\n"), sql_strerror());
871 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
876 cr->MinValue = str_to_int64(row[0]);
877 cr->MaxValue = str_to_int64(row[1]);
878 cr->CurrentValue = str_to_int64(row[2]);
880 bstrncpy(cr->WrapCounter, row[3], sizeof(cr->WrapCounter));
882 cr->WrapCounter[0] = 0;
890 Mmsg(errmsg, _("Counter record: %s not found in Catalog.\n"), cr->Counter);
899 * If the FileSetId is non-zero, we get its record,
900 * otherwise, we search on the name
902 * Returns: 0 on failure
905 int BDB::bdb_get_fileset_record(JCR *jcr, FILESET_DBR *fsr)
910 char esc[MAX_ESCAPE_NAME_LENGTH];
913 if (fsr->FileSetId != 0) { /* find by id */
915 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
916 "WHERE FileSetId=%s",
917 edit_int64(fsr->FileSetId, ed1));
918 } else { /* find by name */
919 bdb_escape_string(jcr, esc, fsr->FileSet, strlen(fsr->FileSet));
921 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
922 "WHERE FileSet='%s' ORDER BY CreateTime DESC LIMIT 1", esc);
925 if (QueryDB(jcr, cmd)) {
926 if (sql_num_rows() > 1) {
928 Mmsg1(errmsg, _("Error got %s FileSets but expected only one!\n"),
929 edit_uint64(sql_num_rows(), ed1));
930 sql_data_seek(sql_num_rows()-1);
932 if ((row = sql_fetch_row()) == NULL) {
933 Mmsg1(errmsg, _("FileSet record \"%s\" not found.\n"), fsr->FileSet);
935 fsr->FileSetId = str_to_int64(row[0]);
936 bstrncpy(fsr->FileSet, row[1]!=NULL?row[1]:"", sizeof(fsr->FileSet));
937 bstrncpy(fsr->MD5, row[2]!=NULL?row[2]:"", sizeof(fsr->MD5));
938 bstrncpy(fsr->cCreateTime, row[3]!=NULL?row[3]:"", sizeof(fsr->cCreateTime));
939 stat = fsr->FileSetId;
943 Mmsg(errmsg, _("FileSet record not found in Catalog.\n"));
951 * Get the number of Media records
953 * Returns: -1 on failure
956 int BDB::bdb_get_num_media_records(JCR *jcr)
961 Mmsg(cmd, "SELECT count(*) from Media");
962 stat = get_sql_record_max(jcr, this);
968 * This function returns a list of all the Media record ids for
969 * the current Pool, the correct Media Type, Recyle, Enabled, StorageId, VolBytes
970 * VolumeName if specified
971 * The caller must free ids if non-NULL.
973 * Returns false: on failure
976 bool BDB::bdb_get_media_ids(JCR *jcr, MEDIA_DBR *mr, int *num_ids, uint32_t *ids[])
983 char buf[MAX_NAME_LENGTH*3]; /* Can contain MAX_NAME_LENGTH*2+1 + AND ....='' */
984 char esc[MAX_NAME_LENGTH*2+1];
989 Mmsg(cmd, "SELECT DISTINCT MediaId FROM Media WHERE Enabled=%d ",
992 if (mr->Recycle >= 0) {
993 bsnprintf(buf, sizeof(buf), "AND Recycle=%d ", mr->Recycle);
997 if (*mr->MediaType) {
998 bdb_escape_string(jcr, esc, mr->MediaType, strlen(mr->MediaType));
999 bsnprintf(buf, sizeof(buf), "AND MediaType='%s' ", esc);
1000 pm_strcat(cmd, buf);
1003 if (mr->sid_group) {
1004 bsnprintf(buf, sizeof(buf), "AND StorageId IN (%s) ", mr->sid_group);
1005 pm_strcat(cmd, buf);
1006 } else if (mr->StorageId) {
1007 bsnprintf(buf, sizeof(buf), "AND StorageId=%s ", edit_uint64(mr->StorageId, ed1));
1008 pm_strcat(cmd, buf);
1012 bsnprintf(buf, sizeof(buf), "AND PoolId=%s ", edit_uint64(mr->PoolId, ed1));
1013 pm_strcat(cmd, buf);
1017 bsnprintf(buf, sizeof(buf), "AND VolBytes > %s ", edit_uint64(mr->VolBytes, ed1));
1018 pm_strcat(cmd, buf);
1021 if (*mr->VolumeName) {
1022 bdb_escape_string(jcr, esc, mr->VolumeName, strlen(mr->VolumeName));
1023 bsnprintf(buf, sizeof(buf), "AND VolumeName = '%s' ", esc);
1024 pm_strcat(cmd, buf);
1027 if (*mr->VolStatus) {
1028 bdb_escape_string(jcr, esc, mr->VolStatus, strlen(mr->VolStatus));
1029 bsnprintf(buf, sizeof(buf), "AND VolStatus = '%s' ", esc);
1030 pm_strcat(cmd, buf);
1033 /* Filter the volumes with the CacheRetention */
1034 if (mr->CacheRetention) {
1035 bsnprintf(buf, sizeof(buf), "AND %s ", prune_cache[bdb_get_type_index()]);
1036 pm_strcat(cmd, buf);
1039 Dmsg1(100, "q=%s\n", cmd);
1041 if (QueryDB(jcr, cmd)) {
1042 *num_ids = sql_num_rows();
1044 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
1045 while ((row = sql_fetch_row()) != NULL) {
1046 id[i++] = str_to_uint64(row[0]);
1053 Mmsg(errmsg, _("Media id select failed: ERR=%s\n"), sql_strerror());
1054 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
1062 * This function returns a list of all the DBIds that are returned
1065 * Returns false: on failure
1068 bool BDB::bdb_get_query_dbids(JCR *jcr, POOL_MEM &query, dbid_list &ids)
1076 if (QueryDB(jcr, query.c_str())) {
1077 ids.num_ids = sql_num_rows();
1078 if (ids.num_ids > 0) {
1079 if (ids.max_ids < ids.num_ids) {
1081 ids.DBId = (DBId_t *)malloc(ids.num_ids * sizeof(DBId_t));
1083 while ((row = sql_fetch_row()) != NULL) {
1084 ids.DBId[i++] = str_to_uint64(row[0]);
1090 Mmsg(errmsg, _("query dbids failed: ERR=%s\n"), sql_strerror());
1091 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
1101 * Returns: false: on failure
1104 bool BDB::bdb_get_media_record(JCR *jcr, MEDIA_DBR *mr)
1109 char esc[MAX_ESCAPE_NAME_LENGTH];
1112 if (mr->MediaId == 0 && mr->VolumeName[0] == 0) {
1113 Mmsg(cmd, "SELECT count(*) from Media");
1114 mr->MediaId = get_sql_record_max(jcr, this);
1118 if (mr->MediaId != 0) { /* find by id */
1119 Mmsg(cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,"
1120 "VolBlocks,VolBytes,VolABytes,VolHoleBytes,VolHoles,VolMounts,"
1121 "VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
1122 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
1123 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
1124 "EndFile,EndBlock,VolType,VolParts,VolCloudParts,LastPartBytes,"
1125 "LabelType,LabelDate,StorageId,"
1126 "Enabled,LocationId,RecycleCount,InitialWrite,"
1127 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge,CacheRetention "
1128 "FROM Media WHERE MediaId=%s",
1129 edit_int64(mr->MediaId, ed1));
1130 } else { /* find by name */
1131 bdb_escape_string(jcr, esc, mr->VolumeName, strlen(mr->VolumeName));
1132 Mmsg(cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,"
1133 "VolBlocks,VolBytes,VolABytes,VolHoleBytes,VolHoles,VolMounts,"
1134 "VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
1135 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
1136 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
1137 "EndFile,EndBlock,VolType,VolParts,VolCloudParts,LastPartBytes,"
1138 "LabelType,LabelDate,StorageId,"
1139 "Enabled,LocationId,RecycleCount,InitialWrite,"
1140 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge,CacheRetention "
1141 "FROM Media WHERE VolumeName='%s'", esc);
1144 if (QueryDB(jcr, cmd)) {
1146 if (sql_num_rows() > 1) {
1147 Mmsg1(errmsg, _("More than one Volume!: %s\n"),
1148 edit_uint64(sql_num_rows(), ed1));
1149 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
1150 } else if (sql_num_rows() == 1) {
1151 if ((row = sql_fetch_row()) == NULL) {
1152 Mmsg1(errmsg, _("error fetching row: %s\n"), sql_strerror());
1153 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
1155 mr->MediaId = str_to_int64(row[0]);
1156 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
1157 mr->VolJobs = str_to_int64(row[2]);
1158 mr->VolFiles = str_to_int64(row[3]);
1159 mr->VolBlocks = str_to_int64(row[4]);
1160 mr->VolBytes = str_to_uint64(row[5]);
1161 mr->VolABytes = str_to_uint64(row[6]);
1162 mr->VolHoleBytes = str_to_uint64(row[7]);
1163 mr->VolHoles = str_to_int64(row[8]);
1164 mr->VolMounts = str_to_int64(row[9]);
1165 mr->VolErrors = str_to_int64(row[10]);
1166 mr->VolWrites = str_to_int64(row[11]);
1167 mr->MaxVolBytes = str_to_uint64(row[12]);
1168 mr->VolCapacityBytes = str_to_uint64(row[13]);
1169 bstrncpy(mr->MediaType, row[14]!=NULL?row[14]:"", sizeof(mr->MediaType));
1170 bstrncpy(mr->VolStatus, row[15]!=NULL?row[15]:"", sizeof(mr->VolStatus));
1171 mr->PoolId = str_to_int64(row[16]);
1172 mr->VolRetention = str_to_uint64(row[17]);
1173 mr->VolUseDuration = str_to_uint64(row[18]);
1174 mr->MaxVolJobs = str_to_int64(row[19]);
1175 mr->MaxVolFiles = str_to_int64(row[20]);
1176 mr->Recycle = str_to_int64(row[21]);
1177 mr->Slot = str_to_int64(row[22]);
1178 bstrncpy(mr->cFirstWritten, row[23]!=NULL?row[23]:"", sizeof(mr->cFirstWritten));
1179 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
1180 bstrncpy(mr->cLastWritten, row[24]!=NULL?row[24]:"", sizeof(mr->cLastWritten));
1181 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
1182 mr->InChanger = str_to_uint64(row[25]);
1183 mr->EndFile = str_to_uint64(row[26]);
1184 mr->EndBlock = str_to_uint64(row[27]);
1185 mr->VolType = str_to_int64(row[28]);
1186 mr->VolParts = str_to_int64(row[29]);
1187 mr->VolCloudParts = str_to_int64(row[30]);
1188 mr->LastPartBytes = str_to_uint64(row[31]);
1189 mr->LabelType = str_to_int64(row[32]);
1190 bstrncpy(mr->cLabelDate, row[33]!=NULL?row[33]:"", sizeof(mr->cLabelDate));
1191 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
1192 mr->StorageId = str_to_int64(row[34]);
1193 mr->Enabled = str_to_int64(row[35]);
1194 mr->LocationId = str_to_int64(row[36]);
1195 mr->RecycleCount = str_to_int64(row[37]);
1196 bstrncpy(mr->cInitialWrite, row[38]!=NULL?row[38]:"", sizeof(mr->cInitialWrite));
1197 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
1198 mr->ScratchPoolId = str_to_int64(row[39]);
1199 mr->RecyclePoolId = str_to_int64(row[40]);
1200 mr->VolReadTime = str_to_int64(row[41]);
1201 mr->VolWriteTime = str_to_int64(row[42]);
1202 mr->ActionOnPurge = str_to_int32(row[43]);
1203 mr->CacheRetention = str_to_int64(row[44]);
1208 if (mr->MediaId != 0) {
1209 Mmsg1(errmsg, _("Media record with MediaId=%s not found.\n"),
1210 edit_int64(mr->MediaId, ed1));
1212 Mmsg1(errmsg, _("Media record for Volume name \"%s\" not found.\n"),
1218 if (mr->MediaId != 0) {
1219 Mmsg(errmsg, _("Media record for MediaId=%u not found in Catalog.\n"),
1222 Mmsg(errmsg, _("Media record for Volume Name \"%s\" not found in Catalog.\n"),
1229 /* Remove all MD5 from a query (can save lot of memory with many files) */
1230 static void strip_md5(char *q)
1233 while ((p = strstr(p, ", MD5"))) {
1234 memset(p, ' ', 5 * sizeof(char));
1239 * Find the last "accurate" backup state (that can take deleted files in
1241 * 1) Get all files with jobid in list (F subquery)
1242 * Get all files in BaseFiles with jobid in list
1243 * 2) Take only the last version of each file (Temp subquery) => accurate list
1245 * 3) Join the result to file table to get fileindex, jobid and lstat information
1247 * TODO: See if we can do the SORT only if needed (as an argument)
1249 bool BDB::bdb_get_file_list(JCR *jcr, char *jobids,
1250 bool use_md5, bool use_delta,
1251 DB_RESULT_HANDLER *result_handler, void *ctx)
1255 Mmsg(errmsg, _("ERR=JobIds are empty\n"));
1259 POOL_MEM buf(PM_MESSAGE);
1260 POOL_MEM buf2(PM_MESSAGE);
1262 Mmsg(buf2, select_recent_version_with_basejob_and_delta[bdb_get_type_index()],
1263 jobids, jobids, jobids, jobids);
1266 Mmsg(buf2, select_recent_version_with_basejob[bdb_get_type_index()],
1267 jobids, jobids, jobids, jobids);
1270 /* bsr code is optimized for JobId sorted, with Delta, we need to get
1271 * them ordered by date. JobTDate and JobId can be mixed if using Copy
1275 "SELECT Path.Path, Filename.Name, T1.FileIndex, T1.JobId, LStat, DeltaSeq, MD5 "
1276 "FROM ( %s ) AS T1 "
1277 "JOIN Filename ON (Filename.FilenameId = T1.FilenameId) "
1278 "JOIN Path ON (Path.PathId = T1.PathId) "
1279 "WHERE FileIndex > 0 "
1280 "ORDER BY T1.JobTDate, FileIndex ASC",/* Return sorted by JobTDate */
1281 /* FileIndex for restore code */
1285 strip_md5(buf.c_str());
1288 Dmsg1(100, "q=%s\n", buf.c_str());
1290 return bdb_big_sql_query(buf.c_str(), result_handler, ctx);
1294 * This procedure gets the base jobid list used by jobids,
1296 bool BDB::bdb_get_used_base_jobids(JCR *jcr,
1297 POOLMEM *jobids, db_list_ctx *result)
1302 "SELECT DISTINCT BaseJobId "
1303 " FROM Job JOIN BaseFiles USING (JobId) "
1304 " WHERE Job.HasBase = 1 "
1305 " AND Job.JobId IN (%s) ", jobids);
1306 return bdb_sql_query(buf.c_str(), db_list_handler, result);
1309 /* Mutex used to have global counter on btemp table */
1310 static pthread_mutex_t btemp_mutex = PTHREAD_MUTEX_INITIALIZER;
1311 static uint32_t btemp_cur = 1;
1314 * The decision do change an incr/diff was done before
1316 * Differential : get the last full id
1317 * Incremental : get the last full + last diff + last incr(s) ids
1319 * If you specify jr->StartTime, it will be used to limit the search
1320 * in the time. (usually now)
1322 * TODO: look and merge from ua_restore.c
1324 bool BDB::bdb_get_accurate_jobids(JCR *jcr,
1325 JOB_DBR *jr, db_list_ctx *jobids)
1328 char clientid[50], jobid[50], filesetid[50];
1329 char date[MAX_TIME_LENGTH];
1330 POOL_MEM query(PM_FNAME);
1332 /* Take the current time as upper limit if nothing else specified */
1333 utime_t StartTime = (jr->StartTime)?jr->StartTime:time(NULL);
1335 bstrutime(date, sizeof(date), StartTime + 1);
1338 /* If we are comming from bconsole, we must ensure that we
1339 * have a unique name.
1341 if (jcr->JobId == 0) {
1343 bsnprintf(jobid, sizeof(jobid), "0%u", btemp_cur++);
1346 edit_uint64(jcr->JobId, jobid);
1349 /* First, find the last good Full backup for this job/client/fileset */
1350 Mmsg(query, create_temp_accurate_jobids[bdb_get_type_index()],
1352 edit_uint64(jr->ClientId, clientid),
1354 edit_uint64(jr->FileSetId, filesetid));
1356 if (!bdb_sql_query(query.c_str(), NULL, NULL)) {
1360 if (jr->JobLevel == L_INCREMENTAL || jr->JobLevel == L_VIRTUAL_FULL) {
1361 /* Now, find the last differential backup after the last full */
1363 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1364 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1365 "FROM Job JOIN FileSet USING (FileSetId) "
1366 "WHERE ClientId = %s "
1367 "AND Level='D' AND JobStatus IN ('T','W') AND Type='B' "
1368 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1369 "AND StartTime < '%s' "
1370 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1371 "ORDER BY Job.JobTDate DESC LIMIT 1 ",
1378 if (!bdb_sql_query(query.c_str(), NULL, NULL)) {
1382 /* We just have to take all incremental after the last Full/Diff */
1384 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1385 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1386 "FROM Job JOIN FileSet USING (FileSetId) "
1387 "WHERE ClientId = %s "
1388 "AND Level='I' AND JobStatus IN ('T','W') AND Type='B' "
1389 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1390 "AND StartTime < '%s' "
1391 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1392 "ORDER BY Job.JobTDate DESC ",
1398 if (!bdb_sql_query(query.c_str(), NULL, NULL)) {
1403 /* build a jobid list ie: 1,2,3,4 */
1404 Mmsg(query, "SELECT JobId FROM btemp3%s ORDER by JobTDate", jobid);
1405 bdb_sql_query(query.c_str(), db_list_handler, jobids);
1406 Dmsg1(1, "db_get_accurate_jobids=%s\n", jobids->list);
1410 Mmsg(query, "DROP TABLE btemp3%s", jobid);
1411 bdb_sql_query(query.c_str(), NULL, NULL);
1416 bool BDB::bdb_get_base_file_list(JCR *jcr, bool use_md5,
1417 DB_RESULT_HANDLER *result_handler, void *ctx)
1419 POOL_MEM buf(PM_MESSAGE);
1422 "SELECT Path, Name, FileIndex, JobId, LStat, 0 As DeltaSeq, MD5 "
1423 "FROM new_basefile%lld ORDER BY JobId, FileIndex ASC",
1424 (uint64_t) jcr->JobId);
1427 strip_md5(buf.c_str());
1429 return bdb_sql_query(buf.c_str(), result_handler, ctx);
1432 bool BDB::bdb_get_base_jobid(JCR *jcr, JOB_DBR *jr, JobId_t *jobid)
1434 POOL_MEM query(PM_FNAME);
1437 char date[MAX_TIME_LENGTH];
1438 char esc[MAX_ESCAPE_NAME_LENGTH];
1445 StartTime = (jr->StartTime)?jr->StartTime:time(NULL);
1446 bstrutime(date, sizeof(date), StartTime + 1);
1447 bdb_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
1449 /* we can take also client name, fileset, etc... */
1452 "SELECT JobId, Job, StartTime, EndTime, JobTDate, PurgedFiles "
1454 // "JOIN FileSet USING (FileSetId) JOIN Client USING (ClientId) "
1455 "WHERE Job.Name = '%s' "
1456 "AND Level='B' AND JobStatus IN ('T','W') AND Type='B' "
1457 // "AND FileSet.FileSet= '%s' "
1458 // "AND Client.Name = '%s' "
1459 "AND StartTime<'%s' "
1460 "ORDER BY Job.JobTDate DESC LIMIT 1",
1462 // edit_uint64(jr->ClientId, clientid),
1463 // edit_uint64(jr->FileSetId, filesetid));
1466 Dmsg1(10, "db_get_base_jobid q=%s\n", query.c_str());
1467 if (!bdb_sql_query(query.c_str(), db_int64_handler, &lctx)) {
1470 *jobid = (JobId_t) lctx.value;
1472 Dmsg1(10, "db_get_base_jobid=%lld\n", *jobid);
1479 /* Get JobIds associated with a volume */
1480 bool BDB::bdb_get_volume_jobids(JCR *jcr,
1481 MEDIA_DBR *mr, db_list_ctx *lst)
1487 Mmsg(cmd, "SELECT DISTINCT JobId FROM JobMedia WHERE MediaId=%s",
1488 edit_int64(mr->MediaId, ed1));
1489 ret = bdb_sql_query(cmd, db_list_handler, lst);
1495 * Get Snapshot Record
1497 * Returns: false: on failure
1500 bool BDB::bdb_get_snapshot_record(JCR *jcr, SNAPSHOT_DBR *sr)
1505 char esc[MAX_ESCAPE_NAME_LENGTH];
1506 POOL_MEM filter1, filter2;
1508 if (sr->SnapshotId == 0 && (sr->Name[0] == 0 || sr->Device[0] == 0)) {
1509 Dmsg0(10, "No SnapshotId or Name/Device provided\n");
1515 if (sr->SnapshotId != 0) { /* find by id */
1516 Mmsg(filter1, "Snapshot.SnapshotId=%d", sr->SnapshotId);
1518 } else if (*sr->Name && *sr->Device) { /* find by name */
1519 bdb_escape_string(jcr, esc, sr->Name, strlen(sr->Name));
1520 Mmsg(filter1, "Snapshot.Name='%s'", esc);
1521 bdb_escape_string(jcr, esc, sr->Device, strlen(sr->Device));
1522 Mmsg(filter2, "AND Snapshot.Device='%s'", esc);
1525 Dmsg0(10, "No SnapshotId or Name and Device\n");
1529 Mmsg(cmd, "SELECT SnapshotId, Snapshot.Name, JobId, Snapshot.FileSetId, "
1530 "FileSet.FileSet, CreateTDate, CreateDate, "
1531 "Client.Name AS Client, Snapshot.ClientId, Volume, Device, Type, Retention, "
1532 "Comment FROM Snapshot JOIN Client USING (ClientId) LEFT JOIN FileSet USING (FileSetId) WHERE %s %s",
1533 filter1.c_str(), filter2.c_str());
1535 if (QueryDB(jcr, cmd)) {
1537 if (sql_num_rows() > 1) {
1538 Mmsg1(errmsg, _("More than one Snapshot!: %s\n"),
1539 edit_uint64(sql_num_rows(), ed1));
1540 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
1541 } else if (sql_num_rows() == 1) {
1542 if ((row = sql_fetch_row()) == NULL) {
1543 Mmsg1(errmsg, _("error fetching row: %s\n"), sql_strerror());
1544 Jmsg(jcr, M_ERROR, 0, "%s", errmsg);
1548 sr->need_to_free = true;
1549 sr->SnapshotId = str_to_int64(row[0]);
1550 bstrncpy(sr->Name, row[1], sizeof(sr->Name));
1551 sr->JobId = str_to_int64(row[2]);
1552 sr->FileSetId = str_to_int64(row[3]);
1553 bstrncpy(sr->FileSet, row[4], sizeof(sr->FileSet));
1554 sr->CreateTDate = str_to_uint64(row[5]);
1555 bstrncpy(sr->CreateDate, row[6], sizeof(sr->CreateDate));
1556 bstrncpy(sr->Client, row[7], sizeof(sr->Client));
1557 sr->ClientId = str_to_int64(row[8]);
1558 sr->Volume = bstrdup(row[9]);
1559 sr->Device = bstrdup(row[10]);
1560 bstrncpy(sr->Type, row[11], sizeof(sr->Type));
1561 sr->Retention = str_to_int64(row[12]);
1562 bstrncpy(sr->Comment, NPRTB(row[13]), sizeof(sr->Comment));
1566 if (sr->SnapshotId != 0) {
1567 Mmsg1(errmsg, _("Snapshot record with SnapshotId=%s not found.\n"),
1568 edit_int64(sr->SnapshotId, ed1));
1570 Mmsg1(errmsg, _("Snapshot record for Snapshot name \"%s\" not found.\n"),
1576 if (sr->SnapshotId != 0) {
1577 Mmsg1(errmsg, _("Snapshot record with SnapshotId=%s not found.\n"),
1578 edit_int64(sr->SnapshotId, ed1));
1580 Mmsg1(errmsg, _("Snapshot record for Snapshot name \"%s\" not found.\n"),
1589 static void build_estimate_query(BDB *db, POOL_MEM &query, const char *mode,
1590 char *job_esc, char level)
1592 POOL_MEM filter, tmp;
1599 /* MySQL doesn't have statistic functions */
1600 if (db->bdb_get_type_index() == SQL_TYPE_POSTGRESQL) {
1601 /* postgresql have functions that permit to handle lineal regression
1603 * REGR_SLOPE(Y,X) = get x
1604 * REGR_INTERCEPT(Y,X) = get b
1605 * and we need y when x=now()
1606 * CORR gives the correlation
1607 * (TODO: display progress bar only if CORR > 0.8)
1609 btime_t now = time(NULL);
1611 "SELECT temp.jobname AS jobname, "
1612 "COALESCE(CORR(value,JobTDate),0) AS corr, "
1613 "(%s*REGR_SLOPE(value,JobTDate) "
1614 " + REGR_INTERCEPT(value,JobTDate)) AS value, "
1615 "AVG(value) AS avg_value, "
1616 " COUNT(1) AS nb ", edit_int64(now, ed1));
1619 "SELECT jobname AS jobname, "
1620 "0.1 AS corr, AVG(value) AS value, AVG(value) AS avg_value, "
1624 /* if it's a differential, we need to compare since the last full
1626 * F D D D F D D D F I I I I D I I I
1628 * | # # # # # # | # #
1629 * | # # # # # # # # | # # # # # # # # #
1630 * +----------------- +-------------------
1632 if (level == L_DIFFERENTIAL) {
1634 " AND Job.StartTime > ( "
1635 " SELECT StartTime "
1637 " WHERE Job.Name = '%s' "
1638 " AND Job.Level = 'F' "
1639 " AND Job.JobStatus IN ('T', 'W') "
1640 " ORDER BY Job.StartTime DESC LIMIT 1) ",
1645 " SELECT Job.Name AS jobname, "
1647 " JobTDate AS jobtdate "
1648 " FROM Job INNER JOIN Client USING (ClientId) "
1649 " WHERE Job.Name = '%s' "
1650 " AND Job.Level = '%c' "
1651 " AND Job.JobStatus IN ('T', 'W') "
1653 "ORDER BY StartTime DESC "
1655 ") AS temp GROUP BY temp.jobname",
1656 mode, job_esc, level, filter.c_str()
1658 pm_strcat(query, tmp.c_str());
1661 bool BDB::bdb_get_job_statistics(JCR *jcr, JOB_DBR *jr)
1664 POOL_MEM queryB, queryF, query;
1665 char job_esc[MAX_ESCAPE_NAME_LENGTH];
1669 bdb_escape_string(jcr, job_esc, jr->Name, strlen(jr->Name));
1670 build_estimate_query(this, queryB, "JobBytes", job_esc, jr->JobLevel);
1671 build_estimate_query(this, queryF, "JobFiles", job_esc, jr->JobLevel);
1673 "SELECT bytes.corr * 100 AS corr_jobbytes, " /* 0 */
1674 "bytes.value AS jobbytes, " /* 1 */
1675 "bytes.avg_value AS avg_jobbytes, " /* 2 */
1676 "bytes.nb AS nb_jobbytes, " /* 3 */
1677 "files.corr * 100 AS corr_jobfiles, " /* 4 */
1678 "files.value AS jobfiles, " /* 5 */
1679 "files.avg_value AS avg_jobfiles, " /* 6 */
1680 "files.nb AS nb_jobfiles " /* 7 */
1681 "FROM (%s) AS bytes LEFT JOIN (%s) AS files USING (jobname)",
1682 queryB.c_str(), queryF.c_str());
1683 Dmsg1(100, "query=%s\n", query.c_str());
1685 if (QueryDB(jcr, query.c_str())) {
1687 if (sql_num_rows() > 1) {
1688 Mmsg1(errmsg, _("More than one Result!: %s\n"),
1689 edit_uint64(sql_num_rows(), ed1));
1694 if ((row = sql_fetch_row()) == NULL) {
1695 Mmsg1(errmsg, _("error fetching row: %s\n"), sql_strerror());
1697 jr->CorrJobBytes = str_to_int64(row[0]);
1698 jr->JobBytes = str_to_int64(row[1]);
1700 /* lineal expression with only one job doesn't return a correct value */
1701 if (str_to_int64(row[3]) == 1) {
1702 jr->JobBytes = str_to_int64(row[2]); /* Take the AVG value */
1704 jr->CorrNbJob = str_to_int64(row[3]); /* Number of jobs used in this sample */
1705 jr->CorrJobFiles = str_to_int64(row[4]);
1706 jr->JobFiles = str_to_int64(row[5]);
1708 if (str_to_int64(row[7]) == 1) {
1709 jr->JobFiles = str_to_int64(row[6]); /* Take the AVG value */
1719 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */