2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2008 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version two of the GNU General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
29 * Bacula Catalog Database 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 /* The following is necessary so that we do not include
41 * the dummy external definition of DB.
43 #define __SQL_C /* indicate that this is sql.c */
48 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI
50 /* -----------------------------------------------------------------------
52 * Generic Routines (or almost generic)
54 * -----------------------------------------------------------------------
57 /* Forward referenced functions */
58 static int db_get_file_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr, FILE_DBR *fdbr);
59 static int db_get_filename_record(JCR *jcr, B_DB *mdb);
60 static int db_get_path_record(JCR *jcr, B_DB *mdb);
64 * Given a full filename (with path), look up the File record
65 * (with attributes) in the database.
67 * Returns: 0 on failure
68 * 1 on success with the File record in FILE_DBR
70 int db_get_file_attributes_record(JCR *jcr, B_DB *mdb, char *fname, JOB_DBR *jr, FILE_DBR *fdbr)
73 Dmsg1(100, "db_get_file_att_record fname=%s \n", fname);
76 split_path_and_file(jcr, mdb, fname);
78 fdbr->FilenameId = db_get_filename_record(jcr, mdb);
80 fdbr->PathId = db_get_path_record(jcr, mdb);
82 stat = db_get_file_record(jcr, mdb, jr, fdbr);
92 * Returns: 0 on failure
95 * DO NOT use Jmsg in this routine.
97 * Note in this routine, we do not use Jmsg because it may be
98 * called to get attributes of a non-existent file, which is
99 * "normal" if a new file is found during Verify.
101 * The following is a bit of a kludge: because we always backup a
102 * directory entry, we can end up with two copies of the directory
103 * in the backup. One is when we encounter the directory and find
104 * we cannot recurse into it, and the other is when we find an
105 * explicit mention of the directory. This can also happen if the
106 * use includes the directory twice. In this case, Verify
107 * VolumeToCatalog fails because we have two copies in the catalog,
108 * and only the first one is marked (twice). So, when calling from Verify,
109 * jr is not NULL and we know jr->FileIndex is the fileindex
110 * of the version of the directory/file we actually want and do
111 * a more explicit SQL search.
114 int db_get_file_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr, FILE_DBR *fdbr)
118 char ed1[50], ed2[50], ed3[50];
120 if (jcr->get_JobLevel() == L_VERIFY_DISK_TO_CATALOG) {
122 "SELECT FileId, LStat, MD5 FROM File,Job WHERE "
123 "File.JobId=Job.JobId AND File.PathId=%s AND "
124 "File.FilenameId=%s AND Job.Type='B' AND Job.JobStatus IN ('T','W') AND "
125 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
126 edit_int64(fdbr->PathId, ed1),
127 edit_int64(fdbr->FilenameId, ed2),
128 edit_int64(jr->ClientId,ed3));
130 } else if (jr != NULL) {
131 /* Called from Verify so jr->FileIndex is valid */
133 "SELECT FileId, LStat, MD5 FROM File WHERE File.JobId=%s AND File.PathId=%s AND "
134 "File.FilenameId=%s AND FileIndex=%u",
135 edit_int64(fdbr->JobId, ed1),
136 edit_int64(fdbr->PathId, ed2),
137 edit_int64(fdbr->FilenameId,ed3),
141 "SELECT FileId, LStat, MD5 FROM File WHERE File.JobId=%s AND File.PathId=%s AND "
142 "File.FilenameId=%s",
143 edit_int64(fdbr->JobId, ed1),
144 edit_int64(fdbr->PathId, ed2),
145 edit_int64(fdbr->FilenameId,ed3));
147 Dmsg3(450, "Get_file_record JobId=%u FilenameId=%u PathId=%u\n",
148 fdbr->JobId, fdbr->FilenameId, fdbr->PathId);
150 Dmsg1(100, "Query=%s\n", mdb->cmd);
152 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
153 mdb->num_rows = sql_num_rows(mdb);
154 Dmsg1(050, "get_file_record num_rows=%d\n", (int)mdb->num_rows);
155 if (mdb->num_rows > 1) {
156 Mmsg1(mdb->errmsg, _("get_file_record want 1 got rows=%d\n"),
158 Dmsg1(000, "=== Problem! %s", mdb->errmsg);
160 if (mdb->num_rows >= 1) {
161 if ((row = sql_fetch_row(mdb)) == NULL) {
162 Mmsg1(mdb->errmsg, _("Error fetching row: %s\n"), sql_strerror(mdb));
164 fdbr->FileId = (FileId_t)str_to_int64(row[0]);
165 bstrncpy(fdbr->LStat, row[1], sizeof(fdbr->LStat));
166 bstrncpy(fdbr->Digest, row[2], sizeof(fdbr->Digest));
170 Mmsg2(mdb->errmsg, _("File record for PathId=%s FilenameId=%s not found.\n"),
171 edit_int64(fdbr->PathId, ed1),
172 edit_int64(fdbr->FilenameId, ed2));
174 sql_free_result(mdb);
176 Mmsg(mdb->errmsg, _("File record not found in Catalog.\n"));
182 /* Get Filename record
183 * Returns: 0 on failure
184 * FilenameId on success
186 * DO NOT use Jmsg in this routine (see notes for get_file_record)
188 static int db_get_filename_record(JCR *jcr, B_DB *mdb)
193 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
194 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
196 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
197 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
199 mdb->num_rows = sql_num_rows(mdb);
200 if (mdb->num_rows > 1) {
201 Mmsg2(mdb->errmsg, _("More than one Filename!: %s for file: %s\n"),
202 edit_uint64(mdb->num_rows, ed1), mdb->fname);
203 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
205 if (mdb->num_rows >= 1) {
206 if ((row = sql_fetch_row(mdb)) == NULL) {
207 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
209 FilenameId = str_to_int64(row[0]);
210 if (FilenameId <= 0) {
211 Mmsg2(mdb->errmsg, _("Get DB Filename record %s found bad record: %d\n"),
212 mdb->cmd, FilenameId);
217 Mmsg1(mdb->errmsg, _("Filename record: %s not found.\n"), mdb->fname);
219 sql_free_result(mdb);
221 Mmsg(mdb->errmsg, _("Filename record: %s not found in Catalog.\n"), mdb->fname);
227 * Returns: 0 on failure
230 * DO NOT use Jmsg in this routine (see notes for get_file_record)
232 static 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 mdb->num_rows = sql_num_rows(mdb);
250 if (mdb->num_rows > 1) {
251 Mmsg2(mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
252 edit_uint64(mdb->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 */
256 if (mdb->num_rows >= 1) {
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 "
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 "
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 sql_free_result(mdb);
356 * Find VolumeNames for a given JobId
357 * Returns: 0 on error or no Volumes found
358 * number of volumes on success
359 * Volumes are concatenated in VolumeNames
360 * separated by a vertical bar (|) in the order
361 * that they were written.
363 * Returns: number of volumes on success
365 int db_get_job_volume_names(JCR *jcr, B_DB *mdb, JobId_t JobId, POOLMEM **VolumeNames)
373 /* Get one entry per VolumeName, but "sort" by VolIndex */
375 "SELECT VolumeName,MAX(VolIndex) FROM JobMedia,Media WHERE "
376 "JobMedia.JobId=%s AND JobMedia.MediaId=Media.MediaId "
377 "GROUP BY VolumeName "
378 "ORDER BY 2 ASC", edit_int64(JobId,ed1));
380 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
382 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
383 mdb->num_rows = sql_num_rows(mdb);
384 Dmsg1(130, "Num rows=%d\n", mdb->num_rows);
385 if (mdb->num_rows <= 0) {
386 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
389 stat = mdb->num_rows;
390 for (i=0; i < stat; i++) {
391 if ((row = sql_fetch_row(mdb)) == NULL) {
392 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
393 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
397 if (*VolumeNames[0] != 0) {
398 pm_strcat(VolumeNames, "|");
400 pm_strcat(VolumeNames, row[0]);
404 sql_free_result(mdb);
406 Mmsg(mdb->errmsg, _("No Volume for JobId %d found in Catalog.\n"), JobId);
413 * Find Volume parameters for a give JobId
414 * Returns: 0 on error or no Volumes found
415 * number of volumes on success
416 * List of Volumes and start/end file/blocks (malloced structure!)
418 * Returns: number of volumes on success
420 int db_get_job_volume_parameters(JCR *jcr, B_DB *mdb, JobId_t JobId, VOL_PARAMS **VolParams)
426 VOL_PARAMS *Vols = NULL;
430 "SELECT VolumeName,MediaType,FirstIndex,LastIndex,StartFile,"
431 "JobMedia.EndFile,StartBlock,JobMedia.EndBlock,Copy,"
433 " FROM JobMedia,Media WHERE JobMedia.JobId=%s"
434 " AND JobMedia.MediaId=Media.MediaId ORDER BY VolIndex,JobMediaId",
435 edit_int64(JobId, ed1));
437 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
438 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
439 mdb->num_rows = sql_num_rows(mdb);
440 Dmsg1(200, "Num rows=%d\n", mdb->num_rows);
441 if (mdb->num_rows <= 0) {
442 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
445 stat = mdb->num_rows;
448 *VolParams = Vols = (VOL_PARAMS *)malloc(stat * sizeof(VOL_PARAMS));
449 SId = (DBId_t *)malloc(stat * sizeof(DBId_t));
451 for (i=0; i < stat; i++) {
452 if ((row = sql_fetch_row(mdb)) == NULL) {
453 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
454 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
459 uint32_t StartBlock, EndBlock, StartFile, EndFile;
460 bstrncpy(Vols[i].VolumeName, row[0], MAX_NAME_LENGTH);
461 bstrncpy(Vols[i].MediaType, row[1], MAX_NAME_LENGTH);
462 Vols[i].FirstIndex = str_to_uint64(row[2]);
463 Vols[i].LastIndex = str_to_uint64(row[3]);
464 StartFile = str_to_uint64(row[4]);
465 EndFile = str_to_uint64(row[5]);
466 StartBlock = str_to_uint64(row[6]);
467 EndBlock = str_to_uint64(row[7]);
468 Vols[i].StartAddr = (((uint64_t)StartFile)<<32) | StartBlock;
469 Vols[i].EndAddr = (((uint64_t)EndFile)<<32) | EndBlock;
470 // Vols[i].Copy = str_to_uint64(row[8]);
471 Vols[i].Slot = str_to_uint64(row[9]);
472 StorageId = str_to_uint64(row[10]);
473 Vols[i].Storage[0] = 0;
477 for (i=0; i < stat; i++) {
479 Mmsg(mdb->cmd, "SELECT Name from Storage WHERE StorageId=%s",
480 edit_int64(SId[i], ed1));
481 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
482 if ((row = sql_fetch_row(mdb)) && row[0]) {
483 bstrncpy(Vols[i].Storage, row[0], MAX_NAME_LENGTH);
492 sql_free_result(mdb);
501 * Get the number of pool records
503 * Returns: -1 on failure
506 int db_get_num_pool_records(JCR *jcr, B_DB *mdb)
511 Mmsg(mdb->cmd, "SELECT count(*) from Pool");
512 stat = get_sql_record_max(jcr, mdb);
518 * This function returns a list of all the Pool record ids.
519 * The caller must free ids if non-NULL.
521 * Returns 0: on failure
524 int db_get_pool_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
533 Mmsg(mdb->cmd, "SELECT PoolId FROM Pool");
534 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
535 *num_ids = sql_num_rows(mdb);
537 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
538 while ((row = sql_fetch_row(mdb)) != NULL) {
539 id[i++] = str_to_uint64(row[0]);
543 sql_free_result(mdb);
546 Mmsg(mdb->errmsg, _("Pool id select failed: ERR=%s\n"), sql_strerror(mdb));
547 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
555 * This function returns a list of all the Client record ids.
556 * The caller must free ids if non-NULL.
558 * Returns 0: on failure
561 int db_get_client_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
570 Mmsg(mdb->cmd, "SELECT ClientId FROM Client");
571 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
572 *num_ids = sql_num_rows(mdb);
574 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
575 while ((row = sql_fetch_row(mdb)) != NULL) {
576 id[i++] = str_to_uint64(row[0]);
580 sql_free_result(mdb);
583 Mmsg(mdb->errmsg, _("Client id select failed: ERR=%s\n"), sql_strerror(mdb));
584 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
594 * If the PoolId is non-zero, we get its record,
595 * otherwise, we search on the PoolName
597 * Returns: false on failure
600 bool db_get_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr)
607 if (pdbr->PoolId != 0) { /* find by id */
609 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
610 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
611 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId,ScratchPoolId FROM Pool WHERE Pool.PoolId=%s",
612 edit_int64(pdbr->PoolId, ed1));
613 } else { /* find by name */
615 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
616 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
617 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId,ScratchPoolId FROM Pool WHERE Pool.Name='%s'",
620 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
621 mdb->num_rows = sql_num_rows(mdb);
622 if (mdb->num_rows > 1) {
624 Mmsg1(mdb->errmsg, _("More than one Pool!: %s\n"),
625 edit_uint64(mdb->num_rows, ed1));
626 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
627 } else if (mdb->num_rows == 1) {
628 if ((row = sql_fetch_row(mdb)) == NULL) {
629 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
630 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
632 pdbr->PoolId = str_to_int64(row[0]);
633 bstrncpy(pdbr->Name, row[1]!=NULL?row[1]:"", sizeof(pdbr->Name));
634 pdbr->NumVols = str_to_int64(row[2]);
635 pdbr->MaxVols = str_to_int64(row[3]);
636 pdbr->UseOnce = str_to_int64(row[4]);
637 pdbr->UseCatalog = str_to_int64(row[5]);
638 pdbr->AcceptAnyVolume = str_to_int64(row[6]);
639 pdbr->AutoPrune = str_to_int64(row[7]);
640 pdbr->Recycle = str_to_int64(row[8]);
641 pdbr->VolRetention = str_to_int64(row[9]);
642 pdbr->VolUseDuration = str_to_int64(row[10]);
643 pdbr->MaxVolJobs = str_to_int64(row[11]);
644 pdbr->MaxVolFiles = str_to_int64(row[12]);
645 pdbr->MaxVolBytes = str_to_uint64(row[13]);
646 bstrncpy(pdbr->PoolType, row[14]!=NULL?row[14]:"", sizeof(pdbr->PoolType));
647 pdbr->LabelType = str_to_int64(row[15]);
648 bstrncpy(pdbr->LabelFormat, row[16]!=NULL?row[16]:"", sizeof(pdbr->LabelFormat));
649 pdbr->RecyclePoolId = str_to_int64(row[17]);
650 pdbr->ScratchPoolId = str_to_int64(row[18]);
654 sql_free_result(mdb);
658 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
659 edit_int64(pdbr->PoolId, ed1));
660 NumVols = get_sql_record_max(jcr, mdb);
661 Dmsg2(400, "Actual NumVols=%d Pool NumVols=%d\n", NumVols, pdbr->NumVols);
662 if (NumVols != pdbr->NumVols) {
663 pdbr->NumVols = NumVols;
664 db_update_pool_record(jcr, mdb, pdbr);
667 Mmsg(mdb->errmsg, _("Pool record not found in Catalog.\n"));
674 * If the ClientId is non-zero, we get its record,
675 * otherwise, we search on the Client Name
677 * Returns: 0 on failure
680 int db_get_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cdbr)
687 if (cdbr->ClientId != 0) { /* find by id */
689 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
690 "FROM Client WHERE Client.ClientId=%s",
691 edit_int64(cdbr->ClientId, ed1));
692 } else { /* find by name */
694 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
695 "FROM Client WHERE Client.Name='%s'", cdbr->Name);
698 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
699 mdb->num_rows = sql_num_rows(mdb);
700 if (mdb->num_rows > 1) {
701 Mmsg1(mdb->errmsg, _("More than one Client!: %s\n"),
702 edit_uint64(mdb->num_rows, ed1));
703 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
704 } else if (mdb->num_rows == 1) {
705 if ((row = sql_fetch_row(mdb)) == NULL) {
706 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
707 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
709 cdbr->ClientId = str_to_int64(row[0]);
710 bstrncpy(cdbr->Name, row[1]!=NULL?row[1]:"", sizeof(cdbr->Name));
711 bstrncpy(cdbr->Uname, row[2]!=NULL?row[2]:"", sizeof(cdbr->Uname));
712 cdbr->AutoPrune = str_to_int64(row[3]);
713 cdbr->FileRetention = str_to_int64(row[4]);
714 cdbr->JobRetention = str_to_int64(row[5]);
718 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
720 sql_free_result(mdb);
722 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
731 * Returns: 0 on failure
734 int db_get_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
739 Mmsg(mdb->cmd, "SELECT MinValue,MaxValue,CurrentValue,WrapCounter "
740 "FROM Counters WHERE Counter='%s'", cr->Counter);
742 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
743 mdb->num_rows = sql_num_rows(mdb);
745 /* If more than one, report error, but return first row */
746 if (mdb->num_rows > 1) {
747 Mmsg1(mdb->errmsg, _("More than one Counter!: %d\n"), (int)(mdb->num_rows));
748 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
750 if (mdb->num_rows >= 1) {
751 if ((row = sql_fetch_row(mdb)) == NULL) {
752 Mmsg1(mdb->errmsg, _("error fetching Counter row: %s\n"), sql_strerror(mdb));
753 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
754 sql_free_result(mdb);
758 cr->MinValue = str_to_int64(row[0]);
759 cr->MaxValue = str_to_int64(row[1]);
760 cr->CurrentValue = str_to_int64(row[2]);
762 bstrncpy(cr->WrapCounter, row[3], sizeof(cr->WrapCounter));
764 cr->WrapCounter[0] = 0;
766 sql_free_result(mdb);
770 sql_free_result(mdb);
772 Mmsg(mdb->errmsg, _("Counter record: %s not found in Catalog.\n"), cr->Counter);
779 /* Get FileSet Record
780 * If the FileSetId is non-zero, we get its record,
781 * otherwise, we search on the name
783 * Returns: 0 on failure
786 int db_get_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
793 if (fsr->FileSetId != 0) { /* find by id */
795 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
796 "WHERE FileSetId=%s",
797 edit_int64(fsr->FileSetId, ed1));
798 } else { /* find by name */
800 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
801 "WHERE FileSet='%s' ORDER BY CreateTime DESC LIMIT 1", fsr->FileSet);
804 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
805 mdb->num_rows = sql_num_rows(mdb);
806 if (mdb->num_rows > 1) {
808 Mmsg1(mdb->errmsg, _("Error got %s FileSets but expected only one!\n"),
809 edit_uint64(mdb->num_rows, ed1));
810 sql_data_seek(mdb, mdb->num_rows-1);
812 if ((row = sql_fetch_row(mdb)) == NULL) {
813 Mmsg1(mdb->errmsg, _("FileSet record \"%s\" not found.\n"), fsr->FileSet);
815 fsr->FileSetId = str_to_int64(row[0]);
816 bstrncpy(fsr->FileSet, row[1]!=NULL?row[1]:"", sizeof(fsr->FileSet));
817 bstrncpy(fsr->MD5, row[2]!=NULL?row[2]:"", sizeof(fsr->MD5));
818 bstrncpy(fsr->cCreateTime, row[3]!=NULL?row[3]:"", sizeof(fsr->cCreateTime));
819 stat = fsr->FileSetId;
821 sql_free_result(mdb);
823 Mmsg(mdb->errmsg, _("FileSet record not found in Catalog.\n"));
831 * Get the number of Media records
833 * Returns: -1 on failure
836 int db_get_num_media_records(JCR *jcr, B_DB *mdb)
841 Mmsg(mdb->cmd, "SELECT count(*) from Media");
842 stat = get_sql_record_max(jcr, mdb);
849 * This function returns a list of all the Media record ids for
850 * the current Pool with the correct Media Type.
851 * The caller must free ids if non-NULL.
853 * Returns false: on failure
856 bool db_get_media_ids(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr, int *num_ids, uint32_t *ids[])
866 Mmsg(mdb->cmd, "SELECT DISTINCT MediaId FROM Media WHERE PoolId=%s "
867 " AND MediaType='%s'",
868 edit_int64(mr->PoolId, ed1), mr->MediaType);
869 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
870 *num_ids = sql_num_rows(mdb);
872 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
873 while ((row = sql_fetch_row(mdb)) != NULL) {
874 id[i++] = str_to_uint64(row[0]);
878 sql_free_result(mdb);
881 Mmsg(mdb->errmsg, _("Media id select failed: ERR=%s\n"), sql_strerror(mdb));
882 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
891 * This function returns a list of all the DBIds that are returned
894 * Returns false: on failure
897 bool db_get_query_dbids(JCR *jcr, B_DB *mdb, POOL_MEM &query, dbid_list &ids)
905 if (QUERY_DB(jcr, mdb, query.c_str())) {
906 ids.num_ids = sql_num_rows(mdb);
907 if (ids.num_ids > 0) {
908 if (ids.max_ids < ids.num_ids) {
910 ids.DBId = (DBId_t *)malloc(ids.num_ids * sizeof(DBId_t));
912 while ((row = sql_fetch_row(mdb)) != NULL) {
913 ids.DBId[i++] = str_to_uint64(row[0]);
916 sql_free_result(mdb);
919 Mmsg(mdb->errmsg, _("query dbids failed: ERR=%s\n"), sql_strerror(mdb));
920 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
929 * Returns: false: on failure
932 bool db_get_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
939 if (mr->MediaId == 0 && mr->VolumeName[0] == 0) {
940 Mmsg(mdb->cmd, "SELECT count(*) from Media");
941 mr->MediaId = get_sql_record_max(jcr, mdb);
945 if (mr->MediaId != 0) { /* find by id */
946 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
947 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
948 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
949 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
950 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
951 "Enabled,LocationId,RecycleCount,InitialWrite,"
952 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime "
953 "FROM Media WHERE MediaId=%s",
954 edit_int64(mr->MediaId, ed1));
955 } else { /* find by name */
956 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
957 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
958 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
959 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
960 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
961 "Enabled,LocationId,RecycleCount,InitialWrite,"
962 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime "
963 "FROM Media WHERE VolumeName='%s'", mr->VolumeName);
966 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
968 mdb->num_rows = sql_num_rows(mdb);
969 if (mdb->num_rows > 1) {
970 Mmsg1(mdb->errmsg, _("More than one Volume!: %s\n"),
971 edit_uint64(mdb->num_rows, ed1));
972 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
973 } else if (mdb->num_rows == 1) {
974 if ((row = sql_fetch_row(mdb)) == NULL) {
975 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
976 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
979 mr->MediaId = str_to_int64(row[0]);
980 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
981 mr->VolJobs = str_to_int64(row[2]);
982 mr->VolFiles = str_to_int64(row[3]);
983 mr->VolBlocks = str_to_int64(row[4]);
984 mr->VolBytes = str_to_uint64(row[5]);
985 mr->VolMounts = str_to_int64(row[6]);
986 mr->VolErrors = str_to_int64(row[7]);
987 mr->VolWrites = str_to_int64(row[8]);
988 mr->MaxVolBytes = str_to_uint64(row[9]);
989 mr->VolCapacityBytes = str_to_uint64(row[10]);
990 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
991 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
992 mr->PoolId = str_to_int64(row[13]);
993 mr->VolRetention = str_to_uint64(row[14]);
994 mr->VolUseDuration = str_to_uint64(row[15]);
995 mr->MaxVolJobs = str_to_int64(row[16]);
996 mr->MaxVolFiles = str_to_int64(row[17]);
997 mr->Recycle = str_to_int64(row[18]);
998 mr->Slot = str_to_int64(row[19]);
999 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
1000 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
1001 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
1002 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
1003 mr->InChanger = str_to_uint64(row[22]);
1004 mr->EndFile = str_to_uint64(row[23]);
1005 mr->EndBlock = str_to_uint64(row[24]);
1006 mr->VolParts = str_to_int64(row[25]);
1007 mr->LabelType = str_to_int64(row[26]);
1008 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
1009 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
1010 mr->StorageId = str_to_int64(row[28]);
1011 mr->Enabled = str_to_int64(row[29]);
1012 mr->LocationId = str_to_int64(row[30]);
1013 mr->RecycleCount = str_to_int64(row[31]);
1014 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
1015 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
1016 mr->ScratchPoolId = str_to_int64(row[33]);
1017 mr->RecyclePoolId = str_to_int64(row[34]);
1018 mr->VolReadTime = str_to_int64(row[35]);
1019 mr->VolWriteTime = str_to_int64(row[36]);
1024 if (mr->MediaId != 0) {
1025 Mmsg1(mdb->errmsg, _("Media record MediaId=%s not found.\n"),
1026 edit_int64(mr->MediaId, ed1));
1028 Mmsg1(mdb->errmsg, _("Media record for Volume \"%s\" not found.\n"),
1032 sql_free_result(mdb);
1034 if (mr->MediaId != 0) {
1035 Mmsg(mdb->errmsg, _("Media record for MediaId=%u not found in Catalog.\n"),
1038 Mmsg(mdb->errmsg, _("Media record for Vol=%s not found in Catalog.\n"),
1046 * Find the last "accurate" backup state (that can take deleted files in account)
1047 * 1) Get all files with jobid in list (F subquery)
1048 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1049 * 3) Join the result to file table to get fileindex, jobid and lstat information
1051 * TODO: See if we can do the SORT only if needed (as an argument)
1053 bool db_get_file_list(JCR *jcr, B_DB *mdb, char *jobids,
1054 DB_RESULT_HANDLER *result_handler, void *ctx)
1058 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1062 POOL_MEM buf(PM_MESSAGE);
1064 #define new_db_get_file_list
1065 #ifdef new_db_get_file_list
1066 /* This is broken, at least if called from ua_restore.c */
1068 "SELECT Path.Path, Filename.Name, File.FileIndex, File.JobId, File.LStat "
1070 "SELECT max(FileId) as FileId, PathId, FilenameId "
1071 "FROM (SELECT FileId, PathId, FilenameId FROM File WHERE JobId IN (%s)) AS F "
1072 "GROUP BY PathId, FilenameId "
1074 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1075 "JOIN Path ON (Path.PathId = Temp.PathId) "
1076 "JOIN File ON (File.FileId = Temp.FileId) "
1077 "WHERE File.FileIndex > 0 ORDER BY JobId, FileIndex ASC", /* Return sorted by JobId, */
1078 /* FileIndex for restore code */
1082 * I am not sure that this works the same as the code in ua_restore.c
1083 * but it is very similar. The accurate-test fails in a restore. Bad file count.
1085 Mmsg(buf, uar_sel_files, jobids);
1088 return db_sql_query(mdb, buf.c_str(), result_handler, ctx);
1091 /* The decision do change an incr/diff was done before
1093 * Differential : get the last full id
1094 * Incremental : get the last full + last diff + last incr(s) ids
1096 * TODO: look and merge from ua_restore.c
1098 bool db_accurate_get_jobids(JCR *jcr, B_DB *mdb,
1099 JOB_DBR *jr, POOLMEM *jobids)
1102 char clientid[50], jobid[50], filesetid[50];
1103 char date[MAX_TIME_LENGTH];
1104 POOL_MEM query(PM_FNAME);
1105 bstrutime(date, sizeof(date), time(NULL) + 1);
1108 /* First, find the last good Full backup for this job/client/fileset */
1110 "CREATE TABLE btemp3%s AS "
1111 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1112 "FROM Job JOIN FileSet USING (FileSetId) "
1113 "WHERE ClientId = %s "
1114 "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' "
1115 "AND StartTime<'%s' "
1116 "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1117 "ORDER BY Job.JobTDate DESC LIMIT 1",
1118 edit_uint64(jcr->JobId, jobid),
1119 edit_uint64(jr->ClientId, clientid),
1121 edit_uint64(jr->FileSetId, filesetid));
1123 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1127 if (jr->JobLevel == L_INCREMENTAL || jr->JobLevel == L_VIRTUAL_FULL) {
1128 /* Now, find the last differential backup after the last full */
1130 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1131 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1132 "FROM Job JOIN FileSet USING (FileSetId) "
1133 "WHERE ClientId = %s "
1134 "AND Level='D' AND JobStatus IN ('T','W') AND Type='B' "
1135 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1136 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1137 "ORDER BY Job.JobTDate DESC LIMIT 1 ",
1143 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1147 /* We just have to take all incremental after the last Full/Diff */
1149 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1150 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1151 "FROM Job JOIN FileSet USING (FileSetId) "
1152 "WHERE ClientId = %s "
1153 "AND Level='I' AND JobStatus IN ('T','W') AND Type='B' "
1154 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1155 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1156 "ORDER BY Job.JobTDate DESC ",
1161 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1166 /* build a jobid list ie: 1,2,3,4 */
1167 Mmsg(query, "SELECT JobId FROM btemp3%s ORDER by JobTDate", jobid);
1168 db_sql_query(mdb, query.c_str(), db_get_int_handler, jobids);
1169 Dmsg1(1, "db_accurate_get_jobids=%s\n", jobids);
1173 Mmsg(query, "DROP TABLE btemp3%s", jobid);
1174 db_sql_query(mdb, query.c_str(), NULL, NULL);
1180 * Use to build a string of int list from a query. "10,20,30"
1182 int db_get_int_handler(void *ctx, int num_fields, char **row)
1184 POOLMEM *ret = (POOLMEM *)ctx;
1185 if (num_fields == 1) {
1187 pm_strcat(ret, ",");
1189 pm_strcat(ret, row[0]);
1194 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI */