2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2009 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
36 * Version $Id: sql_get.c 8918 2009-06-23 11:56:35Z ricozz $
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);
63 * Given a full filename (with path), look up the File record
64 * (with attributes) in the database.
66 * Returns: 0 on failure
67 * 1 on success with the File record in FILE_DBR
69 int db_get_file_attributes_record(JCR *jcr, B_DB *mdb, char *fname, JOB_DBR *jr, FILE_DBR *fdbr)
72 Dmsg1(100, "db_get_file_att_record fname=%s \n", fname);
75 split_path_and_file(jcr, mdb, fname);
77 fdbr->FilenameId = db_get_filename_record(jcr, mdb);
79 fdbr->PathId = db_get_path_record(jcr, mdb);
81 stat = db_get_file_record(jcr, mdb, jr, fdbr);
91 * Returns: 0 on failure
94 * DO NOT use Jmsg in this routine.
96 * Note in this routine, we do not use Jmsg because it may be
97 * called to get attributes of a non-existent file, which is
98 * "normal" if a new file is found during Verify.
100 * The following is a bit of a kludge: because we always backup a
101 * directory entry, we can end up with two copies of the directory
102 * in the backup. One is when we encounter the directory and find
103 * we cannot recurse into it, and the other is when we find an
104 * explicit mention of the directory. This can also happen if the
105 * use includes the directory twice. In this case, Verify
106 * VolumeToCatalog fails because we have two copies in the catalog,
107 * and only the first one is marked (twice). So, when calling from Verify,
108 * jr is not NULL and we know jr->FileIndex is the fileindex
109 * of the version of the directory/file we actually want and do
110 * a more explicit SQL search.
113 int db_get_file_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr, FILE_DBR *fdbr)
117 char ed1[50], ed2[50], ed3[50];
119 if (jcr->get_JobLevel() == L_VERIFY_DISK_TO_CATALOG) {
121 "SELECT FileId, LStat, MD5 FROM File,Job WHERE "
122 "File.JobId=Job.JobId AND File.PathId=%s AND "
123 "File.FilenameId=%s AND Job.Type='B' AND Job.JobStatus IN ('T','W') AND "
124 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
125 edit_int64(fdbr->PathId, ed1),
126 edit_int64(fdbr->FilenameId, ed2),
127 edit_int64(jr->ClientId,ed3));
129 } else if (jr != NULL) {
130 /* Called from Verify so jr->FileIndex is valid */
132 "SELECT FileId, LStat, MD5 FROM File WHERE File.JobId=%s AND File.PathId=%s AND "
133 "File.FilenameId=%s AND FileIndex=%u",
134 edit_int64(fdbr->JobId, ed1),
135 edit_int64(fdbr->PathId, ed2),
136 edit_int64(fdbr->FilenameId,ed3),
140 "SELECT FileId, LStat, MD5 FROM File WHERE File.JobId=%s AND File.PathId=%s AND "
141 "File.FilenameId=%s",
142 edit_int64(fdbr->JobId, ed1),
143 edit_int64(fdbr->PathId, ed2),
144 edit_int64(fdbr->FilenameId,ed3));
146 Dmsg3(450, "Get_file_record JobId=%u FilenameId=%u PathId=%u\n",
147 fdbr->JobId, fdbr->FilenameId, fdbr->PathId);
149 Dmsg1(100, "Query=%s\n", mdb->cmd);
151 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
152 mdb->num_rows = sql_num_rows(mdb);
153 Dmsg1(050, "get_file_record num_rows=%d\n", (int)mdb->num_rows);
154 if (mdb->num_rows > 1) {
155 Mmsg1(mdb->errmsg, _("get_file_record want 1 got rows=%d\n"),
157 Dmsg1(000, "=== Problem! %s", mdb->errmsg);
159 if (mdb->num_rows >= 1) {
160 if ((row = sql_fetch_row(mdb)) == NULL) {
161 Mmsg1(mdb->errmsg, _("Error fetching row: %s\n"), sql_strerror(mdb));
163 fdbr->FileId = (FileId_t)str_to_int64(row[0]);
164 bstrncpy(fdbr->LStat, row[1], sizeof(fdbr->LStat));
165 bstrncpy(fdbr->Digest, row[2], sizeof(fdbr->Digest));
169 Mmsg2(mdb->errmsg, _("File record for PathId=%s FilenameId=%s not found.\n"),
170 edit_int64(fdbr->PathId, ed1),
171 edit_int64(fdbr->FilenameId, ed2));
173 sql_free_result(mdb);
175 Mmsg(mdb->errmsg, _("File record not found in Catalog.\n"));
181 /* Get Filename record
182 * Returns: 0 on failure
183 * FilenameId on success
185 * DO NOT use Jmsg in this routine (see notes for get_file_record)
187 static int db_get_filename_record(JCR *jcr, B_DB *mdb)
192 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
193 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
195 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
196 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
198 mdb->num_rows = sql_num_rows(mdb);
199 if (mdb->num_rows > 1) {
200 Mmsg2(mdb->errmsg, _("More than one Filename!: %s for file: %s\n"),
201 edit_uint64(mdb->num_rows, ed1), mdb->fname);
202 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
204 if (mdb->num_rows >= 1) {
205 if ((row = sql_fetch_row(mdb)) == NULL) {
206 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
208 FilenameId = str_to_int64(row[0]);
209 if (FilenameId <= 0) {
210 Mmsg2(mdb->errmsg, _("Get DB Filename record %s found bad record: %d\n"),
211 mdb->cmd, FilenameId);
216 Mmsg1(mdb->errmsg, _("Filename record: %s not found.\n"), mdb->fname);
218 sql_free_result(mdb);
220 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)
236 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
237 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
239 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
240 strcmp(mdb->cached_path, mdb->path) == 0) {
241 return mdb->cached_path_id;
244 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
246 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
248 mdb->num_rows = sql_num_rows(mdb);
249 if (mdb->num_rows > 1) {
250 Mmsg2(mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
251 edit_uint64(mdb->num_rows, ed1), mdb->path);
252 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
254 /* Even if there are multiple paths, take the first one */
255 if (mdb->num_rows >= 1) {
256 if ((row = sql_fetch_row(mdb)) == NULL) {
257 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
259 PathId = str_to_int64(row[0]);
261 Mmsg2(mdb->errmsg, _("Get DB path record %s found bad record: %s\n"),
262 mdb->cmd, edit_int64(PathId, ed1));
266 if (PathId != mdb->cached_path_id) {
267 mdb->cached_path_id = PathId;
268 mdb->cached_path_len = mdb->pnl;
269 pm_strcpy(mdb->cached_path, mdb->path);
274 Mmsg1(mdb->errmsg, _("Path record: %s not found.\n"), mdb->path);
276 sql_free_result(mdb);
278 Mmsg(mdb->errmsg, _("Path record: %s not found in Catalog.\n"), mdb->path);
285 * Get Job record for given JobId or Job name
286 * Returns: false on failure
289 bool db_get_job_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
295 if (jr->JobId == 0) {
296 Mmsg(mdb->cmd, "SELECT VolSessionId,VolSessionTime,"
297 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
298 "Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,"
299 "SchedTime,RealEndTime,ReadBytes,HasBase "
300 "FROM Job WHERE Job='%s'", jr->Job);
302 Mmsg(mdb->cmd, "SELECT VolSessionId,VolSessionTime,"
303 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
304 "Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,"
305 "SchedTime,RealEndTime,ReadBytes,HasBase "
306 "FROM Job WHERE JobId=%s",
307 edit_int64(jr->JobId, ed1));
310 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
312 return false; /* failed */
314 if ((row = sql_fetch_row(mdb)) == NULL) {
315 Mmsg1(mdb->errmsg, _("No Job found for JobId %s\n"), edit_int64(jr->JobId, ed1));
316 sql_free_result(mdb);
318 return false; /* failed */
321 jr->VolSessionId = str_to_uint64(row[0]);
322 jr->VolSessionTime = str_to_uint64(row[1]);
323 jr->PoolId = str_to_int64(row[2]);
324 bstrncpy(jr->cStartTime, row[3]!=NULL?row[3]:"", sizeof(jr->cStartTime));
325 bstrncpy(jr->cEndTime, row[4]!=NULL?row[4]:"", sizeof(jr->cEndTime));
326 jr->JobFiles = str_to_int64(row[5]);
327 jr->JobBytes = str_to_int64(row[6]);
328 jr->JobTDate = str_to_int64(row[7]);
329 bstrncpy(jr->Job, row[8]!=NULL?row[8]:"", sizeof(jr->Job));
330 jr->JobStatus = row[9]!=NULL?(int)*row[9]:JS_FatalError;
331 jr->JobType = row[10]!=NULL?(int)*row[10]:JT_BACKUP;
332 jr->JobLevel = row[11]!=NULL?(int)*row[11]:L_NONE;
333 jr->ClientId = str_to_uint64(row[12]!=NULL?row[12]:(char *)"");
334 bstrncpy(jr->Name, row[13]!=NULL?row[13]:"", sizeof(jr->Name));
335 jr->PriorJobId = str_to_uint64(row[14]!=NULL?row[14]:(char *)"");
336 bstrncpy(jr->cRealEndTime, row[15]!=NULL?row[15]:"", sizeof(jr->cRealEndTime));
337 if (jr->JobId == 0) {
338 jr->JobId = str_to_int64(row[16]);
340 jr->FileSetId = str_to_int64(row[17]);
341 bstrncpy(jr->cSchedTime, row[3]!=NULL?row[18]:"", sizeof(jr->cSchedTime));
342 bstrncpy(jr->cRealEndTime, row[3]!=NULL?row[19]:"", sizeof(jr->cRealEndTime));
343 jr->ReadBytes = str_to_int64(row[20]);
344 jr->StartTime = str_to_utime(jr->cStartTime);
345 jr->SchedTime = str_to_utime(jr->cSchedTime);
346 jr->EndTime = str_to_utime(jr->cEndTime);
347 jr->RealEndTime = str_to_utime(jr->cRealEndTime);
348 jr->HasBase = str_to_int64(row[21]);
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,"
432 "Slot,StorageId,InChanger"
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].InChanger = str_to_uint64(row[11]);
474 Vols[i].Storage[0] = 0;
478 for (i=0; i < stat; i++) {
480 Mmsg(mdb->cmd, "SELECT Name from Storage WHERE StorageId=%s",
481 edit_int64(SId[i], ed1));
482 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
483 if ((row = sql_fetch_row(mdb)) && row[0]) {
484 bstrncpy(Vols[i].Storage, row[0], MAX_NAME_LENGTH);
493 sql_free_result(mdb);
502 * Get the number of pool records
504 * Returns: -1 on failure
507 int db_get_num_pool_records(JCR *jcr, B_DB *mdb)
512 Mmsg(mdb->cmd, "SELECT count(*) from Pool");
513 stat = get_sql_record_max(jcr, mdb);
519 * This function returns a list of all the Pool record ids.
520 * The caller must free ids if non-NULL.
522 * Returns 0: on failure
525 int db_get_pool_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
534 Mmsg(mdb->cmd, "SELECT PoolId FROM Pool");
535 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
536 *num_ids = sql_num_rows(mdb);
538 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
539 while ((row = sql_fetch_row(mdb)) != NULL) {
540 id[i++] = str_to_uint64(row[0]);
544 sql_free_result(mdb);
547 Mmsg(mdb->errmsg, _("Pool id select failed: ERR=%s\n"), sql_strerror(mdb));
548 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
556 * This function returns a list of all the Client record ids.
557 * The caller must free ids if non-NULL.
559 * Returns 0: on failure
562 int db_get_client_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
571 Mmsg(mdb->cmd, "SELECT ClientId FROM Client");
572 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
573 *num_ids = sql_num_rows(mdb);
575 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
576 while ((row = sql_fetch_row(mdb)) != NULL) {
577 id[i++] = str_to_uint64(row[0]);
581 sql_free_result(mdb);
584 Mmsg(mdb->errmsg, _("Client id select failed: ERR=%s\n"), sql_strerror(mdb));
585 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
595 * If the PoolId is non-zero, we get its record,
596 * otherwise, we search on the PoolName
598 * Returns: false on failure
601 bool db_get_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr)
608 if (pdbr->PoolId != 0) { /* find by id */
610 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
611 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
612 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId,ScratchPoolId FROM Pool WHERE Pool.PoolId=%s",
613 edit_int64(pdbr->PoolId, ed1));
614 } else { /* find by name */
616 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
617 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
618 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId,ScratchPoolId FROM Pool WHERE Pool.Name='%s'",
621 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
622 mdb->num_rows = sql_num_rows(mdb);
623 if (mdb->num_rows > 1) {
625 Mmsg1(mdb->errmsg, _("More than one Pool!: %s\n"),
626 edit_uint64(mdb->num_rows, ed1));
627 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
628 } else if (mdb->num_rows == 1) {
629 if ((row = sql_fetch_row(mdb)) == NULL) {
630 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
631 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
633 pdbr->PoolId = str_to_int64(row[0]);
634 bstrncpy(pdbr->Name, row[1]!=NULL?row[1]:"", sizeof(pdbr->Name));
635 pdbr->NumVols = str_to_int64(row[2]);
636 pdbr->MaxVols = str_to_int64(row[3]);
637 pdbr->UseOnce = str_to_int64(row[4]);
638 pdbr->UseCatalog = str_to_int64(row[5]);
639 pdbr->AcceptAnyVolume = str_to_int64(row[6]);
640 pdbr->AutoPrune = str_to_int64(row[7]);
641 pdbr->Recycle = str_to_int64(row[8]);
642 pdbr->VolRetention = str_to_int64(row[9]);
643 pdbr->VolUseDuration = str_to_int64(row[10]);
644 pdbr->MaxVolJobs = str_to_int64(row[11]);
645 pdbr->MaxVolFiles = str_to_int64(row[12]);
646 pdbr->MaxVolBytes = str_to_uint64(row[13]);
647 bstrncpy(pdbr->PoolType, row[14]!=NULL?row[14]:"", sizeof(pdbr->PoolType));
648 pdbr->LabelType = str_to_int64(row[15]);
649 bstrncpy(pdbr->LabelFormat, row[16]!=NULL?row[16]:"", sizeof(pdbr->LabelFormat));
650 pdbr->RecyclePoolId = str_to_int64(row[17]);
651 pdbr->ScratchPoolId = str_to_int64(row[18]);
655 sql_free_result(mdb);
659 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
660 edit_int64(pdbr->PoolId, ed1));
661 NumVols = get_sql_record_max(jcr, mdb);
662 Dmsg2(400, "Actual NumVols=%d Pool NumVols=%d\n", NumVols, pdbr->NumVols);
663 if (NumVols != pdbr->NumVols) {
664 pdbr->NumVols = NumVols;
665 db_update_pool_record(jcr, mdb, pdbr);
668 Mmsg(mdb->errmsg, _("Pool record not found in Catalog.\n"));
675 * If the ClientId is non-zero, we get its record,
676 * otherwise, we search on the Client Name
678 * Returns: 0 on failure
681 int db_get_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cdbr)
688 if (cdbr->ClientId != 0) { /* find by id */
690 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
691 "FROM Client WHERE Client.ClientId=%s",
692 edit_int64(cdbr->ClientId, ed1));
693 } else { /* find by name */
695 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
696 "FROM Client WHERE Client.Name='%s'", cdbr->Name);
699 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
700 mdb->num_rows = sql_num_rows(mdb);
701 if (mdb->num_rows > 1) {
702 Mmsg1(mdb->errmsg, _("More than one Client!: %s\n"),
703 edit_uint64(mdb->num_rows, ed1));
704 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
705 } else if (mdb->num_rows == 1) {
706 if ((row = sql_fetch_row(mdb)) == NULL) {
707 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
708 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
710 cdbr->ClientId = str_to_int64(row[0]);
711 bstrncpy(cdbr->Name, row[1]!=NULL?row[1]:"", sizeof(cdbr->Name));
712 bstrncpy(cdbr->Uname, row[2]!=NULL?row[2]:"", sizeof(cdbr->Uname));
713 cdbr->AutoPrune = str_to_int64(row[3]);
714 cdbr->FileRetention = str_to_int64(row[4]);
715 cdbr->JobRetention = str_to_int64(row[5]);
719 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
721 sql_free_result(mdb);
723 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
732 * Returns: 0 on failure
735 int db_get_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
740 Mmsg(mdb->cmd, "SELECT MinValue,MaxValue,CurrentValue,WrapCounter "
741 "FROM Counters WHERE Counter='%s'", cr->Counter);
743 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
744 mdb->num_rows = sql_num_rows(mdb);
746 /* If more than one, report error, but return first row */
747 if (mdb->num_rows > 1) {
748 Mmsg1(mdb->errmsg, _("More than one Counter!: %d\n"), (int)(mdb->num_rows));
749 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
751 if (mdb->num_rows >= 1) {
752 if ((row = sql_fetch_row(mdb)) == NULL) {
753 Mmsg1(mdb->errmsg, _("error fetching Counter row: %s\n"), sql_strerror(mdb));
754 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
755 sql_free_result(mdb);
759 cr->MinValue = str_to_int64(row[0]);
760 cr->MaxValue = str_to_int64(row[1]);
761 cr->CurrentValue = str_to_int64(row[2]);
763 bstrncpy(cr->WrapCounter, row[3], sizeof(cr->WrapCounter));
765 cr->WrapCounter[0] = 0;
767 sql_free_result(mdb);
771 sql_free_result(mdb);
773 Mmsg(mdb->errmsg, _("Counter record: %s not found in Catalog.\n"), cr->Counter);
780 /* Get FileSet Record
781 * If the FileSetId is non-zero, we get its record,
782 * otherwise, we search on the name
784 * Returns: 0 on failure
787 int db_get_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
794 if (fsr->FileSetId != 0) { /* find by id */
796 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
797 "WHERE FileSetId=%s",
798 edit_int64(fsr->FileSetId, ed1));
799 } else { /* find by name */
801 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
802 "WHERE FileSet='%s' ORDER BY CreateTime DESC LIMIT 1", fsr->FileSet);
805 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
806 mdb->num_rows = sql_num_rows(mdb);
807 if (mdb->num_rows > 1) {
809 Mmsg1(mdb->errmsg, _("Error got %s FileSets but expected only one!\n"),
810 edit_uint64(mdb->num_rows, ed1));
811 sql_data_seek(mdb, mdb->num_rows-1);
813 if ((row = sql_fetch_row(mdb)) == NULL) {
814 Mmsg1(mdb->errmsg, _("FileSet record \"%s\" not found.\n"), fsr->FileSet);
816 fsr->FileSetId = str_to_int64(row[0]);
817 bstrncpy(fsr->FileSet, row[1]!=NULL?row[1]:"", sizeof(fsr->FileSet));
818 bstrncpy(fsr->MD5, row[2]!=NULL?row[2]:"", sizeof(fsr->MD5));
819 bstrncpy(fsr->cCreateTime, row[3]!=NULL?row[3]:"", sizeof(fsr->cCreateTime));
820 stat = fsr->FileSetId;
822 sql_free_result(mdb);
824 Mmsg(mdb->errmsg, _("FileSet record not found in Catalog.\n"));
832 * Get the number of Media records
834 * Returns: -1 on failure
837 int db_get_num_media_records(JCR *jcr, B_DB *mdb)
842 Mmsg(mdb->cmd, "SELECT count(*) from Media");
843 stat = get_sql_record_max(jcr, mdb);
850 * This function returns a list of all the Media record ids for
851 * the current Pool with the correct Media Type.
852 * The caller must free ids if non-NULL.
854 * Returns false: on failure
857 bool db_get_media_ids(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr, int *num_ids, uint32_t *ids[])
867 Mmsg(mdb->cmd, "SELECT DISTINCT MediaId FROM Media WHERE PoolId=%s "
868 " AND MediaType='%s'",
869 edit_int64(mr->PoolId, ed1), mr->MediaType);
870 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
871 *num_ids = sql_num_rows(mdb);
873 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
874 while ((row = sql_fetch_row(mdb)) != NULL) {
875 id[i++] = str_to_uint64(row[0]);
879 sql_free_result(mdb);
882 Mmsg(mdb->errmsg, _("Media id select failed: ERR=%s\n"), sql_strerror(mdb));
883 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
892 * This function returns a list of all the DBIds that are returned
895 * Returns false: on failure
898 bool db_get_query_dbids(JCR *jcr, B_DB *mdb, POOL_MEM &query, dbid_list &ids)
906 if (QUERY_DB(jcr, mdb, query.c_str())) {
907 ids.num_ids = sql_num_rows(mdb);
908 if (ids.num_ids > 0) {
909 if (ids.max_ids < ids.num_ids) {
911 ids.DBId = (DBId_t *)malloc(ids.num_ids * sizeof(DBId_t));
913 while ((row = sql_fetch_row(mdb)) != NULL) {
914 ids.DBId[i++] = str_to_uint64(row[0]);
917 sql_free_result(mdb);
920 Mmsg(mdb->errmsg, _("query dbids failed: ERR=%s\n"), sql_strerror(mdb));
921 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
930 * Returns: false: on failure
933 bool db_get_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
940 if (mr->MediaId == 0 && mr->VolumeName[0] == 0) {
941 Mmsg(mdb->cmd, "SELECT count(*) from Media");
942 mr->MediaId = get_sql_record_max(jcr, mdb);
946 if (mr->MediaId != 0) { /* find by id */
947 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
948 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
949 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
950 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
951 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
952 "Enabled,LocationId,RecycleCount,InitialWrite,"
953 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime "
954 "FROM Media WHERE MediaId=%s",
955 edit_int64(mr->MediaId, ed1));
956 } else { /* find by name */
957 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
958 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
959 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
960 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
961 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
962 "Enabled,LocationId,RecycleCount,InitialWrite,"
963 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime "
964 "FROM Media WHERE VolumeName='%s'", mr->VolumeName);
967 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
969 mdb->num_rows = sql_num_rows(mdb);
970 if (mdb->num_rows > 1) {
971 Mmsg1(mdb->errmsg, _("More than one Volume!: %s\n"),
972 edit_uint64(mdb->num_rows, ed1));
973 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
974 } else if (mdb->num_rows == 1) {
975 if ((row = sql_fetch_row(mdb)) == NULL) {
976 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
977 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
980 mr->MediaId = str_to_int64(row[0]);
981 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
982 mr->VolJobs = str_to_int64(row[2]);
983 mr->VolFiles = str_to_int64(row[3]);
984 mr->VolBlocks = str_to_int64(row[4]);
985 mr->VolBytes = str_to_uint64(row[5]);
986 mr->VolMounts = str_to_int64(row[6]);
987 mr->VolErrors = str_to_int64(row[7]);
988 mr->VolWrites = str_to_int64(row[8]);
989 mr->MaxVolBytes = str_to_uint64(row[9]);
990 mr->VolCapacityBytes = str_to_uint64(row[10]);
991 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
992 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
993 mr->PoolId = str_to_int64(row[13]);
994 mr->VolRetention = str_to_uint64(row[14]);
995 mr->VolUseDuration = str_to_uint64(row[15]);
996 mr->MaxVolJobs = str_to_int64(row[16]);
997 mr->MaxVolFiles = str_to_int64(row[17]);
998 mr->Recycle = str_to_int64(row[18]);
999 mr->Slot = str_to_int64(row[19]);
1000 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
1001 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
1002 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
1003 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
1004 mr->InChanger = str_to_uint64(row[22]);
1005 mr->EndFile = str_to_uint64(row[23]);
1006 mr->EndBlock = str_to_uint64(row[24]);
1007 mr->VolParts = str_to_int64(row[25]);
1008 mr->LabelType = str_to_int64(row[26]);
1009 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
1010 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
1011 mr->StorageId = str_to_int64(row[28]);
1012 mr->Enabled = str_to_int64(row[29]);
1013 mr->LocationId = str_to_int64(row[30]);
1014 mr->RecycleCount = str_to_int64(row[31]);
1015 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
1016 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
1017 mr->ScratchPoolId = str_to_int64(row[33]);
1018 mr->RecyclePoolId = str_to_int64(row[34]);
1019 mr->VolReadTime = str_to_int64(row[35]);
1020 mr->VolWriteTime = str_to_int64(row[36]);
1025 if (mr->MediaId != 0) {
1026 Mmsg1(mdb->errmsg, _("Media record MediaId=%s not found.\n"),
1027 edit_int64(mr->MediaId, ed1));
1029 Mmsg1(mdb->errmsg, _("Media record for Volume \"%s\" not found.\n"),
1033 sql_free_result(mdb);
1035 if (mr->MediaId != 0) {
1036 Mmsg(mdb->errmsg, _("Media record for MediaId=%u not found in Catalog.\n"),
1039 Mmsg(mdb->errmsg, _("Media record for Vol=%s not found in Catalog.\n"),
1047 * Find the last "accurate" backup state (that can take deleted files in
1049 * 1) Get all files with jobid in list (F subquery)
1050 * Get all files in BaseFiles with jobid in list
1051 * 2) Take only the last version of each file (Temp subquery) => accurate list
1053 * 3) Join the result to file table to get fileindex, jobid and lstat information
1055 * TODO: See if we can do the SORT only if needed (as an argument)
1057 bool db_get_file_list(JCR *jcr, B_DB *mdb, char *jobids,
1058 DB_RESULT_HANDLER *result_handler, void *ctx)
1062 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1066 POOL_MEM buf(PM_MESSAGE);
1068 #define new_db_get_file_list
1069 #ifdef new_db_get_file_list
1070 POOL_MEM buf2(PM_MESSAGE);
1071 Mmsg(buf2, select_recent_version_with_basejob[db_type],
1072 jobids, jobids, jobids, jobids);
1074 "SELECT Path.Path, Filename.Name, Temp.FileIndex, Temp.JobId, LStat, MD5 "
1075 "FROM ( %s ) AS Temp "
1076 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1077 "JOIN Path ON (Path.PathId = Temp.PathId) "
1078 "WHERE FileIndex > 0 "
1079 "ORDER BY Temp.JobId, FileIndex ASC",/* Return sorted by JobId, */
1080 /* FileIndex for restore code */
1084 * I am not sure that this works the same as the code in ua_restore.c but it
1085 * is very similar. The accurate-test fails in a restore. Bad file count.
1087 Mmsg(buf, uar_sel_files, jobids);
1090 return db_sql_query(mdb, buf.c_str(), result_handler, ctx);
1094 * This procedure gets the base jobid list used by jobids,
1096 bool db_get_used_base_jobids(JCR *jcr, B_DB *mdb,
1097 POOLMEM *jobids, db_list_ctx *result)
1101 "SELECT DISTINCT BaseJobId "
1102 " FROM Job JOIN BaseFiles USING (JobId) "
1103 " WHERE Job.HasBase = 1 "
1104 " AND JobId IN (%s) ", jobids);
1105 return db_sql_query(mdb, buf.c_str(), db_list_handler, result);
1108 /* The decision do change an incr/diff was done before
1110 * Differential : get the last full id
1111 * Incremental : get the last full + last diff + last incr(s) ids
1113 * If you specify jr->StartTime, it will be used to limit the search
1114 * in the time. (usually now)
1116 * TODO: look and merge from ua_restore.c
1118 bool db_accurate_get_jobids(JCR *jcr, B_DB *mdb,
1119 JOB_DBR *jr, db_list_ctx *jobids)
1122 char clientid[50], jobid[50], filesetid[50];
1123 char date[MAX_TIME_LENGTH];
1124 POOL_MEM query(PM_FNAME);
1126 /* Take the current time as upper limit if nothing else specified */
1127 utime_t StartTime = (jr->StartTime)?jr->StartTime:time(NULL);
1129 bstrutime(date, sizeof(date), StartTime + 1);
1130 jobids->list[0] = 0;
1133 /* First, find the last good Full backup for this job/client/fileset */
1135 "CREATE TABLE btemp3%s AS "
1136 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1137 "FROM Job JOIN FileSet USING (FileSetId) "
1138 "WHERE ClientId = %s "
1139 "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' "
1140 "AND StartTime<'%s' "
1141 "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1142 "ORDER BY Job.JobTDate DESC LIMIT 1",
1143 edit_uint64(jcr->JobId, jobid),
1144 edit_uint64(jr->ClientId, clientid),
1146 edit_uint64(jr->FileSetId, filesetid));
1148 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1152 if (jr->JobLevel == L_INCREMENTAL || jr->JobLevel == L_VIRTUAL_FULL) {
1153 /* Now, find the last differential backup after the last full */
1155 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1156 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1157 "FROM Job JOIN FileSet USING (FileSetId) "
1158 "WHERE ClientId = %s "
1159 "AND Level='D' AND JobStatus IN ('T','W') AND Type='B' "
1160 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1161 "AND StartTime < '%s' "
1162 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1163 "ORDER BY Job.JobTDate DESC LIMIT 1 ",
1170 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1174 /* We just have to take all incremental after the last Full/Diff */
1176 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1177 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1178 "FROM Job JOIN FileSet USING (FileSetId) "
1179 "WHERE ClientId = %s "
1180 "AND Level='I' AND JobStatus IN ('T','W') AND Type='B' "
1181 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1182 "AND StartTime < '%s' "
1183 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1184 "ORDER BY Job.JobTDate DESC ",
1190 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1195 /* build a jobid list ie: 1,2,3,4 */
1196 Mmsg(query, "SELECT JobId FROM btemp3%s ORDER by JobTDate", jobid);
1197 db_sql_query(mdb, query.c_str(), db_list_handler, jobids);
1198 Dmsg1(1, "db_accurate_get_jobids=%s\n", jobids->list);
1202 Mmsg(query, "DROP TABLE btemp3%s", jobid);
1203 db_sql_query(mdb, query.c_str(), NULL, NULL);
1208 bool db_get_base_file_list(JCR *jcr, B_DB *mdb,
1209 DB_RESULT_HANDLER *result_handler, void *ctx)
1211 POOL_MEM buf(PM_MESSAGE);
1214 "SELECT Path, Name, FileIndex, JobId, LStat, MD5 "
1215 "FROM new_basefile%lld ORDER BY JobId, FileIndex ASC",
1216 (uint64_t) jcr->JobId);
1218 return db_sql_query(mdb, buf.c_str(), result_handler, ctx);
1221 bool db_get_base_jobid(JCR *jcr, B_DB *mdb, JOB_DBR *jr, JobId_t *jobid)
1223 char date[MAX_TIME_LENGTH];
1224 int64_t id = *jobid = 0;
1225 POOL_MEM query(PM_FNAME);
1227 // char clientid[50], filesetid[50];
1229 utime_t StartTime = (jr->StartTime)?jr->StartTime:time(NULL);
1230 bstrutime(date, sizeof(date), StartTime + 1);
1232 /* we can take also client name, fileset, etc... */
1235 "SELECT JobId, Job, StartTime, EndTime, JobTDate, PurgedFiles "
1237 // "JOIN FileSet USING (FileSetId) JOIN Client USING (ClientId) "
1238 "WHERE Job.Name = '%s' "
1239 "AND Level='B' AND JobStatus IN ('T','W') AND Type='B' "
1240 // "AND FileSet.FileSet= '%s' "
1241 // "AND Client.Name = '%s' "
1242 "AND StartTime<'%s' "
1243 "ORDER BY Job.JobTDate DESC LIMIT 1",
1245 // edit_uint64(jr->ClientId, clientid),
1246 // edit_uint64(jr->FileSetId, filesetid));
1249 Dmsg1(10, "db_get_base_jobid q=%s\n", query.c_str());
1250 if (!db_sql_query(mdb, query.c_str(), db_int64_handler, &id)) {
1253 *jobid = (JobId_t) id;
1255 Dmsg1(10, "db_get_base_jobid=%lld\n", id);
1263 * Use to build a string of int list from a query. "10,20,30"
1265 int db_get_int_handler(void *ctx, int num_fields, char **row)
1267 POOLMEM *ret = (POOLMEM *)ctx;
1268 if (num_fields == 1) {
1270 pm_strcat(ret, ",");
1272 pm_strcat(ret, row[0]);
1277 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI */