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->getJobLevel() == 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 if ((row = sql_fetch_row(mdb)) == NULL) {
156 Mmsg1(mdb->errmsg, _("Error fetching row: %s\n"), sql_strerror(mdb));
158 fdbr->FileId = (FileId_t)str_to_int64(row[0]);
159 bstrncpy(fdbr->LStat, row[1], sizeof(fdbr->LStat));
160 bstrncpy(fdbr->Digest, row[2], sizeof(fdbr->Digest));
162 if (mdb->num_rows > 1) {
163 Mmsg3(mdb->errmsg, _("get_file_record want 1 got rows=%d PathId=%s FilenameId=%s\n"),
165 edit_int64(fdbr->PathId, ed1),
166 edit_int64(fdbr->FilenameId, ed2));
167 Dmsg1(000, "=== Problem! %s", mdb->errmsg);
171 Mmsg2(mdb->errmsg, _("File record for PathId=%s FilenameId=%s not found.\n"),
172 edit_int64(fdbr->PathId, ed1),
173 edit_int64(fdbr->FilenameId, ed2));
175 sql_free_result(mdb);
177 Mmsg(mdb->errmsg, _("File record not found in Catalog.\n"));
183 /* Get Filename record
184 * Returns: 0 on failure
185 * FilenameId on success
187 * DO NOT use Jmsg in this routine (see notes for get_file_record)
189 static int db_get_filename_record(JCR *jcr, B_DB *mdb)
194 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
195 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
197 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
198 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
200 mdb->num_rows = sql_num_rows(mdb);
201 if (mdb->num_rows > 1) {
202 Mmsg2(mdb->errmsg, _("More than one Filename!: %s for file: %s\n"),
203 edit_uint64(mdb->num_rows, ed1), mdb->fname);
204 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
206 if (mdb->num_rows >= 1) {
207 if ((row = sql_fetch_row(mdb)) == NULL) {
208 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
210 FilenameId = str_to_int64(row[0]);
211 if (FilenameId <= 0) {
212 Mmsg2(mdb->errmsg, _("Get DB Filename record %s found bad record: %d\n"),
213 mdb->cmd, FilenameId);
218 Mmsg1(mdb->errmsg, _("Filename record: %s not found.\n"), mdb->fname);
220 sql_free_result(mdb);
222 Mmsg(mdb->errmsg, _("Filename record: %s not found in Catalog.\n"), mdb->fname);
228 * Returns: 0 on failure
231 * DO NOT use Jmsg in this routine (see notes for get_file_record)
233 int db_get_path_record(JCR *jcr, B_DB *mdb)
238 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
239 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
241 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
242 strcmp(mdb->cached_path, mdb->path) == 0) {
243 return mdb->cached_path_id;
246 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
248 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
250 mdb->num_rows = sql_num_rows(mdb);
251 if (mdb->num_rows > 1) {
252 Mmsg2(mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
253 edit_uint64(mdb->num_rows, ed1), mdb->path);
254 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
256 /* Even if there are multiple paths, take the first one */
257 if (mdb->num_rows >= 1) {
258 if ((row = sql_fetch_row(mdb)) == NULL) {
259 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
261 PathId = str_to_int64(row[0]);
263 Mmsg2(mdb->errmsg, _("Get DB path record %s found bad record: %s\n"),
264 mdb->cmd, edit_int64(PathId, ed1));
268 if (PathId != mdb->cached_path_id) {
269 mdb->cached_path_id = PathId;
270 mdb->cached_path_len = mdb->pnl;
271 pm_strcpy(mdb->cached_path, mdb->path);
276 Mmsg1(mdb->errmsg, _("Path record: %s not found.\n"), mdb->path);
278 sql_free_result(mdb);
280 Mmsg(mdb->errmsg, _("Path record: %s not found in Catalog.\n"), mdb->path);
287 * Get Job record for given JobId or Job name
288 * Returns: false on failure
291 bool db_get_job_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
297 if (jr->JobId == 0) {
298 Mmsg(mdb->cmd, "SELECT VolSessionId,VolSessionTime,"
299 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
300 "Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,"
301 "SchedTime,RealEndTime,ReadBytes,HasBase "
302 "FROM Job WHERE Job='%s'", jr->Job);
304 Mmsg(mdb->cmd, "SELECT VolSessionId,VolSessionTime,"
305 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
306 "Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,"
307 "SchedTime,RealEndTime,ReadBytes,HasBase "
308 "FROM Job WHERE JobId=%s",
309 edit_int64(jr->JobId, ed1));
312 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
314 return false; /* failed */
316 if ((row = sql_fetch_row(mdb)) == NULL) {
317 Mmsg1(mdb->errmsg, _("No Job found for JobId %s\n"), edit_int64(jr->JobId, ed1));
318 sql_free_result(mdb);
320 return false; /* failed */
323 jr->VolSessionId = str_to_uint64(row[0]);
324 jr->VolSessionTime = str_to_uint64(row[1]);
325 jr->PoolId = str_to_int64(row[2]);
326 bstrncpy(jr->cStartTime, row[3]!=NULL?row[3]:"", sizeof(jr->cStartTime));
327 bstrncpy(jr->cEndTime, row[4]!=NULL?row[4]:"", sizeof(jr->cEndTime));
328 jr->JobFiles = str_to_int64(row[5]);
329 jr->JobBytes = str_to_int64(row[6]);
330 jr->JobTDate = str_to_int64(row[7]);
331 bstrncpy(jr->Job, row[8]!=NULL?row[8]:"", sizeof(jr->Job));
332 jr->JobStatus = row[9]!=NULL?(int)*row[9]:JS_FatalError;
333 jr->JobType = row[10]!=NULL?(int)*row[10]:JT_BACKUP;
334 jr->JobLevel = row[11]!=NULL?(int)*row[11]:L_NONE;
335 jr->ClientId = str_to_uint64(row[12]!=NULL?row[12]:(char *)"");
336 bstrncpy(jr->Name, row[13]!=NULL?row[13]:"", sizeof(jr->Name));
337 jr->PriorJobId = str_to_uint64(row[14]!=NULL?row[14]:(char *)"");
338 bstrncpy(jr->cRealEndTime, row[15]!=NULL?row[15]:"", sizeof(jr->cRealEndTime));
339 if (jr->JobId == 0) {
340 jr->JobId = str_to_int64(row[16]);
342 jr->FileSetId = str_to_int64(row[17]);
343 bstrncpy(jr->cSchedTime, row[3]!=NULL?row[18]:"", sizeof(jr->cSchedTime));
344 bstrncpy(jr->cRealEndTime, row[3]!=NULL?row[19]:"", sizeof(jr->cRealEndTime));
345 jr->ReadBytes = str_to_int64(row[20]);
346 jr->StartTime = str_to_utime(jr->cStartTime);
347 jr->SchedTime = str_to_utime(jr->cSchedTime);
348 jr->EndTime = str_to_utime(jr->cEndTime);
349 jr->RealEndTime = str_to_utime(jr->cRealEndTime);
350 jr->HasBase = str_to_int64(row[21]);
351 sql_free_result(mdb);
358 * Find VolumeNames for a given JobId
359 * Returns: 0 on error or no Volumes found
360 * number of volumes on success
361 * Volumes are concatenated in VolumeNames
362 * separated by a vertical bar (|) in the order
363 * that they were written.
365 * Returns: number of volumes on success
367 int db_get_job_volume_names(JCR *jcr, B_DB *mdb, JobId_t JobId, POOLMEM **VolumeNames)
375 /* Get one entry per VolumeName, but "sort" by VolIndex */
377 "SELECT VolumeName,MAX(VolIndex) FROM JobMedia,Media WHERE "
378 "JobMedia.JobId=%s AND JobMedia.MediaId=Media.MediaId "
379 "GROUP BY VolumeName "
380 "ORDER BY 2 ASC", edit_int64(JobId,ed1));
382 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
384 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
385 mdb->num_rows = sql_num_rows(mdb);
386 Dmsg1(130, "Num rows=%d\n", mdb->num_rows);
387 if (mdb->num_rows <= 0) {
388 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
391 stat = mdb->num_rows;
392 for (i=0; i < stat; i++) {
393 if ((row = sql_fetch_row(mdb)) == NULL) {
394 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
395 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
399 if (*VolumeNames[0] != 0) {
400 pm_strcat(VolumeNames, "|");
402 pm_strcat(VolumeNames, row[0]);
406 sql_free_result(mdb);
408 Mmsg(mdb->errmsg, _("No Volume for JobId %d found in Catalog.\n"), JobId);
415 * Find Volume parameters for a give JobId
416 * Returns: 0 on error or no Volumes found
417 * number of volumes on success
418 * List of Volumes and start/end file/blocks (malloced structure!)
420 * Returns: number of volumes on success
422 int db_get_job_volume_parameters(JCR *jcr, B_DB *mdb, JobId_t JobId, VOL_PARAMS **VolParams)
428 VOL_PARAMS *Vols = NULL;
432 "SELECT VolumeName,MediaType,FirstIndex,LastIndex,StartFile,"
433 "JobMedia.EndFile,StartBlock,JobMedia.EndBlock,"
434 "Slot,StorageId,InChanger"
435 " FROM JobMedia,Media WHERE JobMedia.JobId=%s"
436 " AND JobMedia.MediaId=Media.MediaId ORDER BY VolIndex,JobMediaId",
437 edit_int64(JobId, ed1));
439 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
440 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
441 mdb->num_rows = sql_num_rows(mdb);
442 Dmsg1(200, "Num rows=%d\n", mdb->num_rows);
443 if (mdb->num_rows <= 0) {
444 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
447 stat = mdb->num_rows;
450 *VolParams = Vols = (VOL_PARAMS *)malloc(stat * sizeof(VOL_PARAMS));
451 SId = (DBId_t *)malloc(stat * sizeof(DBId_t));
453 for (i=0; i < stat; i++) {
454 if ((row = sql_fetch_row(mdb)) == NULL) {
455 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
456 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
461 uint32_t StartBlock, EndBlock, StartFile, EndFile;
462 bstrncpy(Vols[i].VolumeName, row[0], MAX_NAME_LENGTH);
463 bstrncpy(Vols[i].MediaType, row[1], MAX_NAME_LENGTH);
464 Vols[i].FirstIndex = str_to_uint64(row[2]);
465 Vols[i].LastIndex = str_to_uint64(row[3]);
466 StartFile = str_to_uint64(row[4]);
467 EndFile = str_to_uint64(row[5]);
468 StartBlock = str_to_uint64(row[6]);
469 EndBlock = str_to_uint64(row[7]);
470 Vols[i].StartAddr = (((uint64_t)StartFile)<<32) | StartBlock;
471 Vols[i].EndAddr = (((uint64_t)EndFile)<<32) | EndBlock;
472 Vols[i].Slot = str_to_uint64(row[8]);
473 StorageId = str_to_uint64(row[9]);
474 Vols[i].InChanger = str_to_uint64(row[10]);
475 Vols[i].Storage[0] = 0;
479 for (i=0; i < stat; i++) {
481 Mmsg(mdb->cmd, "SELECT Name from Storage WHERE StorageId=%s",
482 edit_int64(SId[i], ed1));
483 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
484 if ((row = sql_fetch_row(mdb)) && row[0]) {
485 bstrncpy(Vols[i].Storage, row[0], MAX_NAME_LENGTH);
494 sql_free_result(mdb);
503 * Get the number of pool records
505 * Returns: -1 on failure
508 int db_get_num_pool_records(JCR *jcr, B_DB *mdb)
513 Mmsg(mdb->cmd, "SELECT count(*) from Pool");
514 stat = get_sql_record_max(jcr, mdb);
520 * This function returns a list of all the Pool record ids.
521 * The caller must free ids if non-NULL.
523 * Returns 0: on failure
526 int db_get_pool_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
535 Mmsg(mdb->cmd, "SELECT PoolId FROM Pool");
536 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
537 *num_ids = sql_num_rows(mdb);
539 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
540 while ((row = sql_fetch_row(mdb)) != NULL) {
541 id[i++] = str_to_uint64(row[0]);
545 sql_free_result(mdb);
548 Mmsg(mdb->errmsg, _("Pool id select failed: ERR=%s\n"), sql_strerror(mdb));
549 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
557 * This function returns a list of all the Client record ids.
558 * The caller must free ids if non-NULL.
560 * Returns 0: on failure
563 int db_get_client_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
572 Mmsg(mdb->cmd, "SELECT ClientId FROM Client");
573 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
574 *num_ids = sql_num_rows(mdb);
576 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
577 while ((row = sql_fetch_row(mdb)) != NULL) {
578 id[i++] = str_to_uint64(row[0]);
582 sql_free_result(mdb);
585 Mmsg(mdb->errmsg, _("Client id select failed: ERR=%s\n"), sql_strerror(mdb));
586 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
596 * If the PoolId is non-zero, we get its record,
597 * otherwise, we search on the PoolName
599 * Returns: false on failure
602 bool db_get_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr)
609 if (pdbr->PoolId != 0) { /* find by id */
611 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
612 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
613 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId,ScratchPoolId,"
614 "ActionOnPurge FROM Pool WHERE Pool.PoolId=%s",
615 edit_int64(pdbr->PoolId, ed1));
616 } else { /* find by name */
618 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
619 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
620 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId,ScratchPoolId,"
621 "ActionOnPurge FROM Pool WHERE Pool.Name='%s'",
624 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
625 mdb->num_rows = sql_num_rows(mdb);
626 if (mdb->num_rows > 1) {
628 Mmsg1(mdb->errmsg, _("More than one Pool!: %s\n"),
629 edit_uint64(mdb->num_rows, ed1));
630 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
631 } else if (mdb->num_rows == 1) {
632 if ((row = sql_fetch_row(mdb)) == NULL) {
633 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
634 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
636 pdbr->PoolId = str_to_int64(row[0]);
637 bstrncpy(pdbr->Name, row[1]!=NULL?row[1]:"", sizeof(pdbr->Name));
638 pdbr->NumVols = str_to_int64(row[2]);
639 pdbr->MaxVols = str_to_int64(row[3]);
640 pdbr->UseOnce = str_to_int64(row[4]);
641 pdbr->UseCatalog = str_to_int64(row[5]);
642 pdbr->AcceptAnyVolume = str_to_int64(row[6]);
643 pdbr->AutoPrune = str_to_int64(row[7]);
644 pdbr->Recycle = str_to_int64(row[8]);
645 pdbr->VolRetention = str_to_int64(row[9]);
646 pdbr->VolUseDuration = str_to_int64(row[10]);
647 pdbr->MaxVolJobs = str_to_int64(row[11]);
648 pdbr->MaxVolFiles = str_to_int64(row[12]);
649 pdbr->MaxVolBytes = str_to_uint64(row[13]);
650 bstrncpy(pdbr->PoolType, row[14]!=NULL?row[14]:"", sizeof(pdbr->PoolType));
651 pdbr->LabelType = str_to_int64(row[15]);
652 bstrncpy(pdbr->LabelFormat, row[16]!=NULL?row[16]:"", sizeof(pdbr->LabelFormat));
653 pdbr->RecyclePoolId = str_to_int64(row[17]);
654 pdbr->ScratchPoolId = str_to_int64(row[18]);
655 pdbr->ActionOnPurge = str_to_int32(row[19]);
659 sql_free_result(mdb);
663 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
664 edit_int64(pdbr->PoolId, ed1));
665 NumVols = get_sql_record_max(jcr, mdb);
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, mdb, pdbr);
672 Mmsg(mdb->errmsg, _("Pool record not found in Catalog.\n"));
679 * If the ClientId is non-zero, we get its record,
680 * otherwise, we search on the Client Name
682 * Returns: 0 on failure
685 int db_get_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cdbr)
692 if (cdbr->ClientId != 0) { /* find by id */
694 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
695 "FROM Client WHERE Client.ClientId=%s",
696 edit_int64(cdbr->ClientId, ed1));
697 } else { /* find by name */
699 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
700 "FROM Client WHERE Client.Name='%s'", cdbr->Name);
703 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
704 mdb->num_rows = sql_num_rows(mdb);
705 if (mdb->num_rows > 1) {
706 Mmsg1(mdb->errmsg, _("More than one Client!: %s\n"),
707 edit_uint64(mdb->num_rows, ed1));
708 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
709 } else if (mdb->num_rows == 1) {
710 if ((row = sql_fetch_row(mdb)) == NULL) {
711 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
712 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
714 cdbr->ClientId = str_to_int64(row[0]);
715 bstrncpy(cdbr->Name, row[1]!=NULL?row[1]:"", sizeof(cdbr->Name));
716 bstrncpy(cdbr->Uname, row[2]!=NULL?row[2]:"", sizeof(cdbr->Uname));
717 cdbr->AutoPrune = str_to_int64(row[3]);
718 cdbr->FileRetention = str_to_int64(row[4]);
719 cdbr->JobRetention = str_to_int64(row[5]);
723 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
725 sql_free_result(mdb);
727 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
736 * Returns: 0 on failure
739 int db_get_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
744 Mmsg(mdb->cmd, "SELECT MinValue,MaxValue,CurrentValue,WrapCounter "
745 "FROM Counters WHERE Counter='%s'", cr->Counter);
747 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
748 mdb->num_rows = sql_num_rows(mdb);
750 /* If more than one, report error, but return first row */
751 if (mdb->num_rows > 1) {
752 Mmsg1(mdb->errmsg, _("More than one Counter!: %d\n"), (int)(mdb->num_rows));
753 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
755 if (mdb->num_rows >= 1) {
756 if ((row = sql_fetch_row(mdb)) == NULL) {
757 Mmsg1(mdb->errmsg, _("error fetching Counter row: %s\n"), sql_strerror(mdb));
758 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
759 sql_free_result(mdb);
763 cr->MinValue = str_to_int64(row[0]);
764 cr->MaxValue = str_to_int64(row[1]);
765 cr->CurrentValue = str_to_int64(row[2]);
767 bstrncpy(cr->WrapCounter, row[3], sizeof(cr->WrapCounter));
769 cr->WrapCounter[0] = 0;
771 sql_free_result(mdb);
775 sql_free_result(mdb);
777 Mmsg(mdb->errmsg, _("Counter record: %s not found in Catalog.\n"), cr->Counter);
784 /* Get FileSet Record
785 * If the FileSetId is non-zero, we get its record,
786 * otherwise, we search on the name
788 * Returns: 0 on failure
791 int db_get_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
798 if (fsr->FileSetId != 0) { /* find by id */
800 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
801 "WHERE FileSetId=%s",
802 edit_int64(fsr->FileSetId, ed1));
803 } else { /* find by name */
805 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
806 "WHERE FileSet='%s' ORDER BY CreateTime DESC LIMIT 1", fsr->FileSet);
809 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
810 mdb->num_rows = sql_num_rows(mdb);
811 if (mdb->num_rows > 1) {
813 Mmsg1(mdb->errmsg, _("Error got %s FileSets but expected only one!\n"),
814 edit_uint64(mdb->num_rows, ed1));
815 sql_data_seek(mdb, mdb->num_rows-1);
817 if ((row = sql_fetch_row(mdb)) == NULL) {
818 Mmsg1(mdb->errmsg, _("FileSet record \"%s\" not found.\n"), fsr->FileSet);
820 fsr->FileSetId = str_to_int64(row[0]);
821 bstrncpy(fsr->FileSet, row[1]!=NULL?row[1]:"", sizeof(fsr->FileSet));
822 bstrncpy(fsr->MD5, row[2]!=NULL?row[2]:"", sizeof(fsr->MD5));
823 bstrncpy(fsr->cCreateTime, row[3]!=NULL?row[3]:"", sizeof(fsr->cCreateTime));
824 stat = fsr->FileSetId;
826 sql_free_result(mdb);
828 Mmsg(mdb->errmsg, _("FileSet record not found in Catalog.\n"));
836 * Get the number of Media records
838 * Returns: -1 on failure
841 int db_get_num_media_records(JCR *jcr, B_DB *mdb)
846 Mmsg(mdb->cmd, "SELECT count(*) from Media");
847 stat = get_sql_record_max(jcr, mdb);
854 * This function returns a list of all the Media record ids for
855 * the current Pool with the correct Media Type.
856 * The caller must free ids if non-NULL.
858 * Returns false: on failure
861 bool db_get_media_ids(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr, int *num_ids, uint32_t *ids[])
871 Mmsg(mdb->cmd, "SELECT DISTINCT MediaId FROM Media WHERE PoolId=%s "
872 " AND MediaType='%s'",
873 edit_int64(mr->PoolId, ed1), mr->MediaType);
874 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
875 *num_ids = sql_num_rows(mdb);
877 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
878 while ((row = sql_fetch_row(mdb)) != NULL) {
879 id[i++] = str_to_uint64(row[0]);
883 sql_free_result(mdb);
886 Mmsg(mdb->errmsg, _("Media id select failed: ERR=%s\n"), sql_strerror(mdb));
887 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
896 * This function returns a list of all the DBIds that are returned
899 * Returns false: on failure
902 bool db_get_query_dbids(JCR *jcr, B_DB *mdb, POOL_MEM &query, dbid_list &ids)
910 if (QUERY_DB(jcr, mdb, query.c_str())) {
911 ids.num_ids = sql_num_rows(mdb);
912 if (ids.num_ids > 0) {
913 if (ids.max_ids < ids.num_ids) {
915 ids.DBId = (DBId_t *)malloc(ids.num_ids * sizeof(DBId_t));
917 while ((row = sql_fetch_row(mdb)) != NULL) {
918 ids.DBId[i++] = str_to_uint64(row[0]);
921 sql_free_result(mdb);
924 Mmsg(mdb->errmsg, _("query dbids failed: ERR=%s\n"), sql_strerror(mdb));
925 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
934 * Returns: false: on failure
937 bool db_get_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
944 if (mr->MediaId == 0 && mr->VolumeName[0] == 0) {
945 Mmsg(mdb->cmd, "SELECT count(*) from Media");
946 mr->MediaId = get_sql_record_max(jcr, mdb);
950 if (mr->MediaId != 0) { /* find by id */
951 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
952 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
953 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
954 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
955 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
956 "Enabled,LocationId,RecycleCount,InitialWrite,"
957 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
958 "FROM Media WHERE MediaId=%s",
959 edit_int64(mr->MediaId, ed1));
960 } else { /* find by name */
961 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
962 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
963 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
964 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
965 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
966 "Enabled,LocationId,RecycleCount,InitialWrite,"
967 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
968 "FROM Media WHERE VolumeName='%s'", mr->VolumeName);
971 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
973 mdb->num_rows = sql_num_rows(mdb);
974 if (mdb->num_rows > 1) {
975 Mmsg1(mdb->errmsg, _("More than one Volume!: %s\n"),
976 edit_uint64(mdb->num_rows, ed1));
977 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
978 } else if (mdb->num_rows == 1) {
979 if ((row = sql_fetch_row(mdb)) == NULL) {
980 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
981 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
984 mr->MediaId = str_to_int64(row[0]);
985 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
986 mr->VolJobs = str_to_int64(row[2]);
987 mr->VolFiles = str_to_int64(row[3]);
988 mr->VolBlocks = str_to_int64(row[4]);
989 mr->VolBytes = str_to_uint64(row[5]);
990 mr->VolMounts = str_to_int64(row[6]);
991 mr->VolErrors = str_to_int64(row[7]);
992 mr->VolWrites = str_to_int64(row[8]);
993 mr->MaxVolBytes = str_to_uint64(row[9]);
994 mr->VolCapacityBytes = str_to_uint64(row[10]);
995 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
996 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
997 mr->PoolId = str_to_int64(row[13]);
998 mr->VolRetention = str_to_uint64(row[14]);
999 mr->VolUseDuration = str_to_uint64(row[15]);
1000 mr->MaxVolJobs = str_to_int64(row[16]);
1001 mr->MaxVolFiles = str_to_int64(row[17]);
1002 mr->Recycle = str_to_int64(row[18]);
1003 mr->Slot = str_to_int64(row[19]);
1004 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
1005 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
1006 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
1007 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
1008 mr->InChanger = str_to_uint64(row[22]);
1009 mr->EndFile = str_to_uint64(row[23]);
1010 mr->EndBlock = str_to_uint64(row[24]);
1011 mr->VolParts = str_to_int64(row[25]);
1012 mr->LabelType = str_to_int64(row[26]);
1013 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
1014 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
1015 mr->StorageId = str_to_int64(row[28]);
1016 mr->Enabled = str_to_int64(row[29]);
1017 mr->LocationId = str_to_int64(row[30]);
1018 mr->RecycleCount = str_to_int64(row[31]);
1019 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
1020 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
1021 mr->ScratchPoolId = str_to_int64(row[33]);
1022 mr->RecyclePoolId = str_to_int64(row[34]);
1023 mr->VolReadTime = str_to_int64(row[35]);
1024 mr->VolWriteTime = str_to_int64(row[36]);
1025 mr->ActionOnPurge = str_to_int32(row[37]);
1030 if (mr->MediaId != 0) {
1031 Mmsg1(mdb->errmsg, _("Media record MediaId=%s not found.\n"),
1032 edit_int64(mr->MediaId, ed1));
1034 Mmsg1(mdb->errmsg, _("Media record for Volume \"%s\" not found.\n"),
1038 sql_free_result(mdb);
1040 if (mr->MediaId != 0) {
1041 Mmsg(mdb->errmsg, _("Media record for MediaId=%u not found in Catalog.\n"),
1044 Mmsg(mdb->errmsg, _("Media record for Vol=%s not found in Catalog.\n"),
1052 * Find the last "accurate" backup state (that can take deleted files in
1054 * 1) Get all files with jobid in list (F subquery)
1055 * Get all files in BaseFiles with jobid in list
1056 * 2) Take only the last version of each file (Temp subquery) => accurate list
1058 * 3) Join the result to file table to get fileindex, jobid and lstat information
1060 * TODO: See if we can do the SORT only if needed (as an argument)
1062 bool db_get_file_list(JCR *jcr, B_DB *mdb, char *jobids,
1063 DB_RESULT_HANDLER *result_handler, void *ctx)
1067 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1071 POOL_MEM buf(PM_MESSAGE);
1073 #define new_db_get_file_list
1074 #ifdef new_db_get_file_list
1075 POOL_MEM buf2(PM_MESSAGE);
1076 Mmsg(buf2, select_recent_version_with_basejob[db_type],
1077 jobids, jobids, jobids, jobids);
1079 "SELECT Path.Path, Filename.Name, Temp.FileIndex, Temp.JobId, LStat, MD5 "
1080 "FROM ( %s ) AS Temp "
1081 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1082 "JOIN Path ON (Path.PathId = Temp.PathId) "
1083 "WHERE FileIndex > 0 "
1084 "ORDER BY Temp.JobId, FileIndex ASC",/* Return sorted by JobId, */
1085 /* FileIndex for restore code */
1089 * I am not sure that this works the same as the code in ua_restore.c but it
1090 * is very similar. The accurate-test fails in a restore. Bad file count.
1092 Mmsg(buf, uar_sel_files, jobids);
1095 return db_sql_query(mdb, buf.c_str(), result_handler, ctx);
1099 * This procedure gets the base jobid list used by jobids,
1101 bool db_get_used_base_jobids(JCR *jcr, B_DB *mdb,
1102 POOLMEM *jobids, db_list_ctx *result)
1106 "SELECT DISTINCT BaseJobId "
1107 " FROM Job JOIN BaseFiles USING (JobId) "
1108 " WHERE Job.HasBase = 1 "
1109 " AND Job.JobId IN (%s) ", jobids);
1110 return db_sql_query(mdb, buf.c_str(), db_list_handler, result);
1113 /* The decision do change an incr/diff was done before
1115 * Differential : get the last full id
1116 * Incremental : get the last full + last diff + last incr(s) ids
1118 * If you specify jr->StartTime, it will be used to limit the search
1119 * in the time. (usually now)
1121 * TODO: look and merge from ua_restore.c
1123 bool db_accurate_get_jobids(JCR *jcr, B_DB *mdb,
1124 JOB_DBR *jr, db_list_ctx *jobids)
1127 char clientid[50], jobid[50], filesetid[50];
1128 char date[MAX_TIME_LENGTH];
1129 POOL_MEM query(PM_FNAME);
1131 /* Take the current time as upper limit if nothing else specified */
1132 utime_t StartTime = (jr->StartTime)?jr->StartTime:time(NULL);
1134 bstrutime(date, sizeof(date), StartTime + 1);
1135 jobids->list[0] = 0;
1138 /* First, find the last good Full backup for this job/client/fileset */
1140 "CREATE TABLE btemp3%s AS "
1141 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1142 "FROM Job JOIN FileSet USING (FileSetId) "
1143 "WHERE ClientId = %s "
1144 "AND Level='F' AND JobStatus IN ('T','W') AND Type='B' "
1145 "AND StartTime<'%s' "
1146 "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1147 "ORDER BY Job.JobTDate DESC LIMIT 1",
1148 edit_uint64(jcr->JobId, jobid),
1149 edit_uint64(jr->ClientId, clientid),
1151 edit_uint64(jr->FileSetId, filesetid));
1153 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1157 if (jr->JobLevel == L_INCREMENTAL || jr->JobLevel == L_VIRTUAL_FULL) {
1158 /* Now, find the last differential backup after the last full */
1160 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1161 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1162 "FROM Job JOIN FileSet USING (FileSetId) "
1163 "WHERE ClientId = %s "
1164 "AND Level='D' AND JobStatus IN ('T','W') AND Type='B' "
1165 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1166 "AND StartTime < '%s' "
1167 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1168 "ORDER BY Job.JobTDate DESC LIMIT 1 ",
1175 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1179 /* We just have to take all incremental after the last Full/Diff */
1181 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1182 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1183 "FROM Job JOIN FileSet USING (FileSetId) "
1184 "WHERE ClientId = %s "
1185 "AND Level='I' AND JobStatus IN ('T','W') AND Type='B' "
1186 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1187 "AND StartTime < '%s' "
1188 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1189 "ORDER BY Job.JobTDate DESC ",
1195 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1200 /* build a jobid list ie: 1,2,3,4 */
1201 Mmsg(query, "SELECT JobId FROM btemp3%s ORDER by JobTDate", jobid);
1202 db_sql_query(mdb, query.c_str(), db_list_handler, jobids);
1203 Dmsg1(1, "db_accurate_get_jobids=%s\n", jobids->list);
1207 Mmsg(query, "DROP TABLE btemp3%s", jobid);
1208 db_sql_query(mdb, query.c_str(), NULL, NULL);
1213 bool db_get_base_file_list(JCR *jcr, B_DB *mdb,
1214 DB_RESULT_HANDLER *result_handler, void *ctx)
1216 POOL_MEM buf(PM_MESSAGE);
1219 "SELECT Path, Name, FileIndex, JobId, LStat, MD5 "
1220 "FROM new_basefile%lld ORDER BY JobId, FileIndex ASC",
1221 (uint64_t) jcr->JobId);
1223 return db_sql_query(mdb, buf.c_str(), result_handler, ctx);
1226 bool db_get_base_jobid(JCR *jcr, B_DB *mdb, JOB_DBR *jr, JobId_t *jobid)
1228 POOL_MEM query(PM_FNAME);
1231 char date[MAX_TIME_LENGTH];
1235 // char clientid[50], filesetid[50];
1237 StartTime = (jr->StartTime)?jr->StartTime:time(NULL);
1238 bstrutime(date, sizeof(date), StartTime + 1);
1240 /* we can take also client name, fileset, etc... */
1243 "SELECT JobId, Job, StartTime, EndTime, JobTDate, PurgedFiles "
1245 // "JOIN FileSet USING (FileSetId) JOIN Client USING (ClientId) "
1246 "WHERE Job.Name = '%s' "
1247 "AND Level='B' AND JobStatus IN ('T','W') AND Type='B' "
1248 // "AND FileSet.FileSet= '%s' "
1249 // "AND Client.Name = '%s' "
1250 "AND StartTime<'%s' "
1251 "ORDER BY Job.JobTDate DESC LIMIT 1",
1253 // edit_uint64(jr->ClientId, clientid),
1254 // edit_uint64(jr->FileSetId, filesetid));
1257 Dmsg1(10, "db_get_base_jobid q=%s\n", query.c_str());
1258 if (!db_sql_query(mdb, query.c_str(), db_int64_handler, &lctx)) {
1261 *jobid = (JobId_t) lctx.value;
1263 Dmsg1(10, "db_get_base_jobid=%lld\n", *jobid);
1270 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI */