2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2007 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 John Walker.
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.
102 int db_get_file_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr, FILE_DBR *fdbr)
106 char ed1[50], ed2[50], ed3[50];
108 if (jcr->JobLevel == L_VERIFY_DISK_TO_CATALOG) {
110 "SELECT FileId, LStat, MD5 FROM File,Job WHERE "
111 "File.JobId=Job.JobId AND File.PathId=%s AND "
112 "File.FilenameId=%s AND Job.Type='B' AND Job.JobSTATUS='T' AND "
113 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
114 edit_int64(fdbr->PathId, ed1),
115 edit_int64(fdbr->FilenameId, ed2),
116 edit_int64(jr->ClientId,ed3));
120 "SELECT FileId, LStat, MD5 FROM File WHERE File.JobId=%s AND File.PathId=%s AND "
121 "File.FilenameId=%s",
122 edit_int64(fdbr->JobId, ed1),
123 edit_int64(fdbr->PathId, ed2),
124 edit_int64(fdbr->FilenameId,ed3));
126 Dmsg3(050, "Get_file_record JobId=%u FilenameId=%u PathId=%u\n",
127 fdbr->JobId, fdbr->FilenameId, fdbr->PathId);
129 Dmsg1(100, "Query=%s\n", mdb->cmd);
131 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
132 mdb->num_rows = sql_num_rows(mdb);
133 Dmsg1(050, "get_file_record num_rows=%d\n", (int)mdb->num_rows);
134 if (mdb->num_rows > 1) {
135 Mmsg1(mdb->errmsg, _("get_file_record want 1 got rows=%d\n"),
138 if (mdb->num_rows >= 1) {
139 if ((row = sql_fetch_row(mdb)) == NULL) {
140 Mmsg1(mdb->errmsg, _("Error fetching row: %s\n"), sql_strerror(mdb));
142 fdbr->FileId = (FileId_t)str_to_int64(row[0]);
143 bstrncpy(fdbr->LStat, row[1], sizeof(fdbr->LStat));
144 bstrncpy(fdbr->Digest, row[2], sizeof(fdbr->Digest));
148 Mmsg2(mdb->errmsg, _("File record for PathId=%s FilenameId=%s not found.\n"),
149 edit_int64(fdbr->PathId, ed1),
150 edit_int64(fdbr->FilenameId, ed2));
152 sql_free_result(mdb);
154 Mmsg(mdb->errmsg, _("File record not found in Catalog.\n"));
160 /* Get Filename record
161 * Returns: 0 on failure
162 * FilenameId on success
164 * DO NOT use Jmsg in this routine (see notes for get_file_record)
166 static int db_get_filename_record(JCR *jcr, B_DB *mdb)
171 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
172 db_escape_string(jcr, mdb, mdb->esc_name, mdb->fname, mdb->fnl);
174 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
175 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
177 mdb->num_rows = sql_num_rows(mdb);
178 if (mdb->num_rows > 1) {
179 Mmsg2(mdb->errmsg, _("More than one Filename!: %s for file: %s\n"),
180 edit_uint64(mdb->num_rows, ed1), mdb->fname);
181 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
183 if (mdb->num_rows >= 1) {
184 if ((row = sql_fetch_row(mdb)) == NULL) {
185 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
187 FilenameId = str_to_int64(row[0]);
188 if (FilenameId <= 0) {
189 Mmsg2(mdb->errmsg, _("Get DB Filename record %s found bad record: %d\n"),
190 mdb->cmd, FilenameId);
195 Mmsg1(mdb->errmsg, _("Filename record: %s not found.\n"), mdb->fname);
197 sql_free_result(mdb);
199 Mmsg(mdb->errmsg, _("Filename record: %s not found in Catalog.\n"), mdb->fname);
205 * Returns: 0 on failure
208 * DO NOT use Jmsg in this routine (see notes for get_file_record)
210 static int db_get_path_record(JCR *jcr, B_DB *mdb)
215 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
216 db_escape_string(jcr, mdb, mdb->esc_name, mdb->path, mdb->pnl);
218 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
219 strcmp(mdb->cached_path, mdb->path) == 0) {
220 return mdb->cached_path_id;
223 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
225 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
227 mdb->num_rows = sql_num_rows(mdb);
228 if (mdb->num_rows > 1) {
229 Mmsg2(mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
230 edit_uint64(mdb->num_rows, ed1), mdb->path);
231 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
233 /* Even if there are multiple paths, take the first one */
234 if (mdb->num_rows >= 1) {
235 if ((row = sql_fetch_row(mdb)) == NULL) {
236 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
238 PathId = str_to_int64(row[0]);
240 Mmsg2(mdb->errmsg, _("Get DB path record %s found bad record: %s\n"),
241 mdb->cmd, edit_int64(PathId, ed1));
245 if (PathId != mdb->cached_path_id) {
246 mdb->cached_path_id = PathId;
247 mdb->cached_path_len = mdb->pnl;
248 pm_strcpy(mdb->cached_path, mdb->path);
253 Mmsg1(mdb->errmsg, _("Path record: %s not found.\n"), mdb->path);
255 sql_free_result(mdb);
257 Mmsg(mdb->errmsg, _("Path record: %s not found in Catalog.\n"), mdb->path);
264 * Get Job record for given JobId or Job name
265 * Returns: false on failure
268 bool db_get_job_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
274 if (jr->JobId == 0) {
275 Mmsg(mdb->cmd, "SELECT VolSessionId,VolSessionTime,"
276 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
277 "Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,"
278 "SchedTime,RealEndTime "
279 "FROM Job WHERE Job='%s'", jr->Job);
281 Mmsg(mdb->cmd, "SELECT VolSessionId,VolSessionTime,"
282 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
283 "Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId,FileSetId,"
284 "SchedTime,RealEndTime "
285 "FROM Job WHERE JobId=%s",
286 edit_int64(jr->JobId, ed1));
289 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
291 return false; /* failed */
293 if ((row = sql_fetch_row(mdb)) == NULL) {
294 Mmsg1(mdb->errmsg, _("No Job found for JobId %s\n"), edit_int64(jr->JobId, ed1));
295 sql_free_result(mdb);
297 return false; /* failed */
300 jr->VolSessionId = str_to_uint64(row[0]);
301 jr->VolSessionTime = str_to_uint64(row[1]);
302 jr->PoolId = str_to_int64(row[2]);
303 bstrncpy(jr->cStartTime, row[3]!=NULL?row[3]:"", sizeof(jr->cStartTime));
304 bstrncpy(jr->cEndTime, row[4]!=NULL?row[4]:"", sizeof(jr->cEndTime));
305 jr->JobFiles = str_to_int64(row[5]);
306 jr->JobBytes = str_to_int64(row[6]);
307 jr->JobTDate = str_to_int64(row[7]);
308 bstrncpy(jr->Job, row[8]!=NULL?row[8]:"", sizeof(jr->Job));
309 jr->JobStatus = row[9]!=NULL?(int)*row[9]:JS_FatalError;
310 jr->JobType = row[10]!=NULL?(int)*row[10]:JT_BACKUP;
311 jr->JobLevel = row[11]!=NULL?(int)*row[11]:L_NONE;
312 jr->ClientId = str_to_uint64(row[12]!=NULL?row[12]:(char *)"");
313 bstrncpy(jr->Name, row[13]!=NULL?row[13]:"", sizeof(jr->Name));
314 jr->PriorJobId = str_to_uint64(row[14]!=NULL?row[14]:(char *)"");
315 bstrncpy(jr->cRealEndTime, row[15]!=NULL?row[15]:"", sizeof(jr->cRealEndTime));
316 if (jr->JobId == 0) {
317 jr->JobId = str_to_int64(row[16]);
319 jr->FileSetId = str_to_int64(row[17]);
320 bstrncpy(jr->cSchedTime, row[3]!=NULL?row[18]:"", sizeof(jr->cSchedTime));
321 bstrncpy(jr->cRealEndTime, row[3]!=NULL?row[19]:"", sizeof(jr->cRealEndTime));
322 jr->StartTime = str_to_utime(jr->cStartTime);
323 jr->SchedTime = str_to_utime(jr->cSchedTime);
324 jr->EndTime = str_to_utime(jr->cEndTime);
325 jr->RealEndTime = str_to_utime(jr->cRealEndTime);
326 sql_free_result(mdb);
333 * Find VolumeNames for a given JobId
334 * Returns: 0 on error or no Volumes found
335 * number of volumes on success
336 * Volumes are concatenated in VolumeNames
337 * separated by a vertical bar (|) in the order
338 * that they were written.
340 * Returns: number of volumes on success
342 int db_get_job_volume_names(JCR *jcr, B_DB *mdb, JobId_t JobId, POOLMEM **VolumeNames)
350 /* Get one entry per VolumeName, but "sort" by VolIndex */
352 "SELECT VolumeName,MAX(VolIndex) FROM JobMedia,Media WHERE "
353 "JobMedia.JobId=%s AND JobMedia.MediaId=Media.MediaId "
354 "GROUP BY VolumeName "
355 "ORDER BY 2 ASC", edit_int64(JobId,ed1));
357 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
359 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
360 mdb->num_rows = sql_num_rows(mdb);
361 Dmsg1(130, "Num rows=%d\n", mdb->num_rows);
362 if (mdb->num_rows <= 0) {
363 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
366 stat = mdb->num_rows;
367 for (i=0; i < stat; i++) {
368 if ((row = sql_fetch_row(mdb)) == NULL) {
369 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
370 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
374 if (*VolumeNames[0] != 0) {
375 pm_strcat(VolumeNames, "|");
377 pm_strcat(VolumeNames, row[0]);
381 sql_free_result(mdb);
383 Mmsg(mdb->errmsg, _("No Volume for JobId %d found in Catalog.\n"), JobId);
390 * Find Volume parameters for a give JobId
391 * Returns: 0 on error or no Volumes found
392 * number of volumes on success
393 * List of Volumes and start/end file/blocks (malloced structure!)
395 * Returns: number of volumes on success
397 int db_get_job_volume_parameters(JCR *jcr, B_DB *mdb, JobId_t JobId, VOL_PARAMS **VolParams)
403 VOL_PARAMS *Vols = NULL;
407 "SELECT VolumeName,MediaType,FirstIndex,LastIndex,StartFile,"
408 "JobMedia.EndFile,StartBlock,JobMedia.EndBlock,Copy,"
410 " FROM JobMedia,Media WHERE JobMedia.JobId=%s"
411 " AND JobMedia.MediaId=Media.MediaId ORDER BY VolIndex,JobMediaId",
412 edit_int64(JobId, ed1));
414 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
415 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
416 mdb->num_rows = sql_num_rows(mdb);
417 Dmsg1(200, "Num rows=%d\n", mdb->num_rows);
418 if (mdb->num_rows <= 0) {
419 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
422 stat = mdb->num_rows;
425 *VolParams = Vols = (VOL_PARAMS *)malloc(stat * sizeof(VOL_PARAMS));
426 SId = (DBId_t *)malloc(stat * sizeof(DBId_t));
428 for (i=0; i < stat; i++) {
429 if ((row = sql_fetch_row(mdb)) == NULL) {
430 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
431 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
436 bstrncpy(Vols[i].VolumeName, row[0], MAX_NAME_LENGTH);
437 bstrncpy(Vols[i].MediaType, row[1], MAX_NAME_LENGTH);
438 Vols[i].FirstIndex = str_to_uint64(row[2]);
439 Vols[i].LastIndex = str_to_uint64(row[3]);
440 Vols[i].StartFile = str_to_uint64(row[4]);
441 Vols[i].EndFile = str_to_uint64(row[5]);
442 Vols[i].StartBlock = str_to_uint64(row[6]);
443 Vols[i].EndBlock = str_to_uint64(row[7]);
444 // Vols[i].Copy = str_to_uint64(row[8]);
445 Vols[i].Slot = str_to_uint64(row[9]);
446 StorageId = str_to_uint64(row[10]);
447 Vols[i].Storage[0] = 0;
451 for (i=0; i < stat; i++) {
453 Mmsg(mdb->cmd, "SELECT Name from Storage WHERE StorageId=%s",
454 edit_int64(SId[i], ed1));
455 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
456 if ((row = sql_fetch_row(mdb)) && row[0]) {
457 bstrncpy(Vols[i].Storage, row[0], MAX_NAME_LENGTH);
466 sql_free_result(mdb);
475 * Get the number of pool records
477 * Returns: -1 on failure
480 int db_get_num_pool_records(JCR *jcr, B_DB *mdb)
485 Mmsg(mdb->cmd, "SELECT count(*) from Pool");
486 stat = get_sql_record_max(jcr, mdb);
492 * This function returns a list of all the Pool record ids.
493 * The caller must free ids if non-NULL.
495 * Returns 0: on failure
498 int db_get_pool_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
507 Mmsg(mdb->cmd, "SELECT PoolId FROM Pool");
508 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
509 *num_ids = sql_num_rows(mdb);
511 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
512 while ((row = sql_fetch_row(mdb)) != NULL) {
513 id[i++] = str_to_uint64(row[0]);
517 sql_free_result(mdb);
520 Mmsg(mdb->errmsg, _("Pool id select failed: ERR=%s\n"), sql_strerror(mdb));
521 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
529 * This function returns a list of all the Client record ids.
530 * The caller must free ids if non-NULL.
532 * Returns 0: on failure
535 int db_get_client_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
544 Mmsg(mdb->cmd, "SELECT ClientId FROM Client");
545 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
546 *num_ids = sql_num_rows(mdb);
548 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
549 while ((row = sql_fetch_row(mdb)) != NULL) {
550 id[i++] = str_to_uint64(row[0]);
554 sql_free_result(mdb);
557 Mmsg(mdb->errmsg, _("Client id select failed: ERR=%s\n"), sql_strerror(mdb));
558 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
568 * If the PoolId is non-zero, we get its record,
569 * otherwise, we search on the PoolName
571 * Returns: false on failure
574 bool db_get_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr)
581 if (pdbr->PoolId != 0) { /* find by id */
583 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
584 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
585 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId FROM Pool WHERE Pool.PoolId=%s",
586 edit_int64(pdbr->PoolId, ed1));
587 } else { /* find by name */
589 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
590 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
591 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId FROM Pool WHERE Pool.Name='%s'",
595 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
596 mdb->num_rows = sql_num_rows(mdb);
597 if (mdb->num_rows > 1) {
599 Mmsg1(mdb->errmsg, _("More than one Pool!: %s\n"),
600 edit_uint64(mdb->num_rows, ed1));
601 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
602 } else if (mdb->num_rows == 1) {
603 if ((row = sql_fetch_row(mdb)) == NULL) {
604 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
605 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
607 pdbr->PoolId = str_to_int64(row[0]);
608 bstrncpy(pdbr->Name, row[1]!=NULL?row[1]:"", sizeof(pdbr->Name));
609 pdbr->NumVols = str_to_int64(row[2]);
610 pdbr->MaxVols = str_to_int64(row[3]);
611 pdbr->UseOnce = str_to_int64(row[4]);
612 pdbr->UseCatalog = str_to_int64(row[5]);
613 pdbr->AcceptAnyVolume = str_to_int64(row[6]);
614 pdbr->AutoPrune = str_to_int64(row[7]);
615 pdbr->Recycle = str_to_int64(row[8]);
616 pdbr->VolRetention = str_to_int64(row[9]);
617 pdbr->VolUseDuration = str_to_int64(row[10]);
618 pdbr->MaxVolJobs = str_to_int64(row[11]);
619 pdbr->MaxVolFiles = str_to_int64(row[12]);
620 pdbr->MaxVolBytes = str_to_uint64(row[13]);
621 bstrncpy(pdbr->PoolType, row[14]!=NULL?row[14]:"", sizeof(pdbr->PoolType));
622 pdbr->LabelType = str_to_int64(row[15]);
623 bstrncpy(pdbr->LabelFormat, row[16]!=NULL?row[16]:"", sizeof(pdbr->LabelFormat));
624 pdbr->RecyclePoolId = str_to_int64(row[17]);
628 sql_free_result(mdb);
632 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
633 edit_int64(pdbr->PoolId, ed1));
634 NumVols = get_sql_record_max(jcr, mdb);
635 Dmsg2(400, "Actual NumVols=%d Pool NumVols=%d\n", NumVols, pdbr->NumVols);
636 if (NumVols != pdbr->NumVols) {
637 pdbr->NumVols = NumVols;
638 db_update_pool_record(jcr, mdb, pdbr);
641 Mmsg(mdb->errmsg, _("Pool record not found in Catalog.\n"));
648 * If the ClientId is non-zero, we get its record,
649 * otherwise, we search on the Client Name
651 * Returns: 0 on failure
654 int db_get_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cdbr)
661 if (cdbr->ClientId != 0) { /* find by id */
663 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
664 "FROM Client WHERE Client.ClientId=%s",
665 edit_int64(cdbr->ClientId, ed1));
666 } else { /* find by name */
668 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
669 "FROM Client WHERE Client.Name='%s'", cdbr->Name);
672 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
673 mdb->num_rows = sql_num_rows(mdb);
674 if (mdb->num_rows > 1) {
675 Mmsg1(mdb->errmsg, _("More than one Client!: %s\n"),
676 edit_uint64(mdb->num_rows, ed1));
677 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
678 } else if (mdb->num_rows == 1) {
679 if ((row = sql_fetch_row(mdb)) == NULL) {
680 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
681 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
683 cdbr->ClientId = str_to_int64(row[0]);
684 bstrncpy(cdbr->Name, row[1]!=NULL?row[1]:"", sizeof(cdbr->Name));
685 bstrncpy(cdbr->Uname, row[2]!=NULL?row[2]:"", sizeof(cdbr->Uname));
686 cdbr->AutoPrune = str_to_int64(row[3]);
687 cdbr->FileRetention = str_to_int64(row[4]);
688 cdbr->JobRetention = str_to_int64(row[5]);
692 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
694 sql_free_result(mdb);
696 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
705 * Returns: 0 on failure
708 int db_get_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
713 Mmsg(mdb->cmd, "SELECT MinValue,MaxValue,CurrentValue,WrapCounter "
714 "FROM Counters WHERE Counter='%s'", cr->Counter);
716 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
717 mdb->num_rows = sql_num_rows(mdb);
719 /* If more than one, report error, but return first row */
720 if (mdb->num_rows > 1) {
721 Mmsg1(mdb->errmsg, _("More than one Counter!: %d\n"), (int)(mdb->num_rows));
722 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
724 if (mdb->num_rows >= 1) {
725 if ((row = sql_fetch_row(mdb)) == NULL) {
726 Mmsg1(mdb->errmsg, _("error fetching Counter row: %s\n"), sql_strerror(mdb));
727 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
728 sql_free_result(mdb);
732 cr->MinValue = str_to_int64(row[0]);
733 cr->MaxValue = str_to_int64(row[1]);
734 cr->CurrentValue = str_to_int64(row[2]);
736 bstrncpy(cr->WrapCounter, row[3], sizeof(cr->WrapCounter));
738 cr->WrapCounter[0] = 0;
740 sql_free_result(mdb);
744 sql_free_result(mdb);
746 Mmsg(mdb->errmsg, _("Counter record: %s not found in Catalog.\n"), cr->Counter);
753 /* Get FileSet Record
754 * If the FileSetId is non-zero, we get its record,
755 * otherwise, we search on the name
757 * Returns: 0 on failure
760 int db_get_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
767 if (fsr->FileSetId != 0) { /* find by id */
769 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
770 "WHERE FileSetId=%s",
771 edit_int64(fsr->FileSetId, ed1));
772 } else { /* find by name */
774 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
775 "WHERE FileSet='%s' ORDER BY CreateTime DESC LIMIT 1", fsr->FileSet);
778 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
779 mdb->num_rows = sql_num_rows(mdb);
780 if (mdb->num_rows > 1) {
782 Mmsg1(mdb->errmsg, _("Error got %s FileSets but expected only one!\n"),
783 edit_uint64(mdb->num_rows, ed1));
784 sql_data_seek(mdb, mdb->num_rows-1);
786 if ((row = sql_fetch_row(mdb)) == NULL) {
787 Mmsg1(mdb->errmsg, _("FileSet record \"%s\" not found.\n"), fsr->FileSet);
789 fsr->FileSetId = str_to_int64(row[0]);
790 bstrncpy(fsr->FileSet, row[1]!=NULL?row[1]:"", sizeof(fsr->FileSet));
791 bstrncpy(fsr->MD5, row[2]!=NULL?row[2]:"", sizeof(fsr->MD5));
792 bstrncpy(fsr->cCreateTime, row[3]!=NULL?row[3]:"", sizeof(fsr->cCreateTime));
793 stat = fsr->FileSetId;
795 sql_free_result(mdb);
797 Mmsg(mdb->errmsg, _("FileSet record not found in Catalog.\n"));
805 * Get the number of Media records
807 * Returns: -1 on failure
810 int db_get_num_media_records(JCR *jcr, B_DB *mdb)
815 Mmsg(mdb->cmd, "SELECT count(*) from Media");
816 stat = get_sql_record_max(jcr, mdb);
823 * This function returns a list of all the Media record ids for
824 * the current Pool with the correct Media Type.
825 * The caller must free ids if non-NULL.
827 * Returns false: on failure
830 bool db_get_media_ids(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr, int *num_ids, uint32_t *ids[])
840 Mmsg(mdb->cmd, "SELECT DISTINCT MediaId FROM Media WHERE PoolId=%s "
841 " AND MediaType='%s'",
842 edit_int64(mr->PoolId, ed1), mr->MediaType);
843 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
844 *num_ids = sql_num_rows(mdb);
846 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
847 while ((row = sql_fetch_row(mdb)) != NULL) {
848 id[i++] = str_to_uint64(row[0]);
852 sql_free_result(mdb);
855 Mmsg(mdb->errmsg, _("Media id select failed: ERR=%s\n"), sql_strerror(mdb));
856 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
865 * This function returns a list of all the DBIds that are returned
868 * Returns false: on failure
871 bool db_get_query_dbids(JCR *jcr, B_DB *mdb, POOL_MEM &query, dbid_list &ids)
879 if (QUERY_DB(jcr, mdb, query.c_str())) {
880 ids.num_ids = sql_num_rows(mdb);
881 if (ids.num_ids > 0) {
882 if (ids.max_ids < ids.num_ids) {
884 ids.DBId = (DBId_t *)malloc(ids.num_ids * sizeof(DBId_t));
886 while ((row = sql_fetch_row(mdb)) != NULL) {
887 ids.DBId[i++] = str_to_uint64(row[0]);
890 sql_free_result(mdb);
893 Mmsg(mdb->errmsg, _("query dbids failed: ERR=%s\n"), sql_strerror(mdb));
894 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
903 * Returns: false: on failure
906 bool db_get_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
913 if (mr->MediaId == 0 && mr->VolumeName[0] == 0) {
914 Mmsg(mdb->cmd, "SELECT count(*) from Media");
915 mr->MediaId = get_sql_record_max(jcr, mdb);
919 if (mr->MediaId != 0) { /* find by id */
920 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
921 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
922 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
923 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
924 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
925 "Enabled,LocationId,RecycleCount,InitialWrite,"
926 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime "
927 "FROM Media WHERE MediaId=%s",
928 edit_int64(mr->MediaId, ed1));
929 } else { /* find by name */
930 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
931 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
932 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
933 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
934 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
935 "Enabled,LocationId,RecycleCount,InitialWrite,"
936 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime "
937 "FROM Media WHERE VolumeName='%s'", mr->VolumeName);
940 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
942 mdb->num_rows = sql_num_rows(mdb);
943 if (mdb->num_rows > 1) {
944 Mmsg1(mdb->errmsg, _("More than one Volume!: %s\n"),
945 edit_uint64(mdb->num_rows, ed1));
946 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
947 } else if (mdb->num_rows == 1) {
948 if ((row = sql_fetch_row(mdb)) == NULL) {
949 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
950 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
953 mr->MediaId = str_to_int64(row[0]);
954 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
955 mr->VolJobs = str_to_int64(row[2]);
956 mr->VolFiles = str_to_int64(row[3]);
957 mr->VolBlocks = str_to_int64(row[4]);
958 mr->VolBytes = str_to_uint64(row[5]);
959 mr->VolMounts = str_to_int64(row[6]);
960 mr->VolErrors = str_to_int64(row[7]);
961 mr->VolWrites = str_to_int64(row[8]);
962 mr->MaxVolBytes = str_to_uint64(row[9]);
963 mr->VolCapacityBytes = str_to_uint64(row[10]);
964 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
965 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
966 mr->PoolId = str_to_int64(row[13]);
967 mr->VolRetention = str_to_uint64(row[14]);
968 mr->VolUseDuration = str_to_uint64(row[15]);
969 mr->MaxVolJobs = str_to_int64(row[16]);
970 mr->MaxVolFiles = str_to_int64(row[17]);
971 mr->Recycle = str_to_int64(row[18]);
972 mr->Slot = str_to_int64(row[19]);
973 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
974 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
975 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
976 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
977 mr->InChanger = str_to_uint64(row[22]);
978 mr->EndFile = str_to_uint64(row[23]);
979 mr->EndBlock = str_to_uint64(row[24]);
980 mr->VolParts = str_to_int64(row[25]);
981 mr->LabelType = str_to_int64(row[26]);
982 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
983 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
984 mr->StorageId = str_to_int64(row[28]);
985 mr->Enabled = str_to_int64(row[29]);
986 mr->LocationId = str_to_int64(row[30]);
987 mr->RecycleCount = str_to_int64(row[31]);
988 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
989 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
990 mr->ScratchPoolId = str_to_int64(row[33]);
991 mr->RecyclePoolId = str_to_int64(row[34]);
992 mr->VolReadTime = str_to_int64(row[35]);
993 mr->VolWriteTime = str_to_int64(row[36]);
998 if (mr->MediaId != 0) {
999 Mmsg1(mdb->errmsg, _("Media record MediaId=%s not found.\n"),
1000 edit_int64(mr->MediaId, ed1));
1002 Mmsg1(mdb->errmsg, _("Media record for Volume \"%s\" not found.\n"),
1006 sql_free_result(mdb);
1008 if (mr->MediaId != 0) {
1009 Mmsg(mdb->errmsg, _("Media record for MediaId=%u not found in Catalog.\n"),
1012 Mmsg(mdb->errmsg, _("Media record for Vol=%s not found in Catalog.\n"),
1020 * Find the last "accurate" backup state (that can take deleted files in account)
1021 * 1) Get all files with jobid in list (F subquery)
1022 * 2) Take only the last version of each file (Temp subquery) => accurate list is ok
1023 * 3) Join the result to file table to get fileindex, jobid and lstat information
1025 * TODO: On postgresql, this is done with
1026 SELECT DISTINCT ON (PathId, FilenameId) FileIndex, Path, Name, LStat
1027 FROM File JOIN Filename USING (FilenameId) JOIN Path USING (PathId) WHERE JobId IN (40341)
1028 ORDER BY PathId, FilenameId, JobId DESC
1030 bool db_get_file_list(JCR *jcr, B_DB *mdb, char *jobids,
1031 DB_RESULT_HANDLER *result_handler, void *ctx)
1035 Mmsg(mdb->errmsg, _("ERR=JobIds are empty\n"));
1040 POOL_MEM buf (PM_MESSAGE);
1043 "SELECT Path.Path, Filename.Name, File.FileIndex, File.JobId, File.LStat "
1045 "SELECT max(FileId) as FileId, PathId, FilenameId "
1046 "FROM (SELECT FileId, PathId, FilenameId FROM File WHERE JobId IN (%s)) AS F "
1047 "GROUP BY PathId, FilenameId "
1049 "JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) "
1050 "JOIN Path ON (Path.PathId = Temp.PathId) "
1051 "JOIN File ON (File.FileId = Temp.FileId) "
1052 "WHERE File.FileIndex > 0 ",
1055 return db_sql_query(mdb, buf.c_str(), result_handler, ctx);
1059 /* Full : do nothing
1060 * Differential : get the last full id
1061 * Incremental : get the last full + last diff + last incr(s) ids
1063 * TODO: look and merge from ua_restore.c
1065 bool db_accurate_get_jobids(JCR *jcr, B_DB *mdb,
1066 JOB_DBR *jr, POOLMEM *jobids)
1068 char clientid[50], jobid[50], filesetid[50];
1069 char date[MAX_TIME_LENGTH];
1071 POOL_MEM query (PM_FNAME);
1072 bstrutime(date, sizeof(date), time(NULL) + 1);
1075 /* First, find the last good Full backup for this job/client/fileset */
1077 "CREATE TEMPORARY TABLE btemp3%s AS "
1078 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1079 "FROM Job JOIN FileSet USING (FileSetId) "
1080 "WHERE ClientId = %s "
1081 "AND Level='F' AND JobStatus='T' AND Type='B' "
1082 "AND StartTime<'%s' "
1083 "AND FileSet.FileSet=(SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1084 "ORDER BY Job.JobTDate DESC LIMIT 1",
1085 edit_uint64(jcr->JobId, jobid),
1086 edit_uint64(jr->ClientId, clientid),
1088 edit_uint64(jr->FileSetId, filesetid));
1090 if (!db_sql_query(mdb, query.c_str(), NULL, NULL)) {
1094 if (jr->JobLevel == L_INCREMENTAL) {
1096 /* Now, find the last differential backup after the last full */
1098 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1099 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1100 "FROM Job JOIN FileSet USING (FileSetId) "
1101 "WHERE ClientId = %s "
1102 "AND Level='D' AND JobStatus='T' AND Type='B' "
1103 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1104 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1105 "ORDER BY Job.JobTDate DESC LIMIT 1 ",
1111 db_sql_query(mdb, query.c_str(), NULL, NULL);
1113 /* We just have to take all incremental after the last Full/Diff */
1115 "INSERT INTO btemp3%s (JobId, StartTime, EndTime, JobTDate, PurgedFiles) "
1116 "SELECT JobId, StartTime, EndTime, JobTDate, PurgedFiles "
1117 "FROM Job JOIN FileSet USING (FileSetId) "
1118 "WHERE ClientId = %s "
1119 "AND Level='I' AND JobStatus='T' AND Type='B' "
1120 "AND StartTime > (SELECT EndTime FROM btemp3%s ORDER BY EndTime DESC LIMIT 1) "
1121 "AND FileSet.FileSet= (SELECT FileSet FROM FileSet WHERE FileSetId = %s) "
1122 "ORDER BY Job.JobTDate DESC ",
1127 db_sql_query(mdb, query.c_str(), NULL, NULL);
1130 /* build a jobid list ie: 1,2,3,4 */
1131 Mmsg(query, "SELECT JobId FROM btemp3%s", jobid);
1132 db_sql_query(mdb, query.c_str(), db_get_int_handler, jobids);
1133 Dmsg1(1, "db_accurate_get_jobids=%s\n", jobids);
1135 Mmsg(query, "DROP TABLE btemp3%s", jobid);
1136 db_sql_query(mdb, query.c_str(), NULL, NULL);
1142 * Use to build a string of int list from a query. "10,20,30"
1144 int db_get_int_handler(void *ctx, int num_fields, char **row)
1146 POOLMEM *ret = (POOLMEM *)ctx;
1147 if (num_fields == 1) {
1149 pm_strcat(ret, ",");
1151 pm_strcat(ret, row[0]);
1156 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI */