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 plus additions
11 that are listed in the file LICENSE.
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
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(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(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 "
278 "FROM Job WHERE Job='%s'", jr->Job);
280 Mmsg(mdb->cmd, "SELECT VolSessionId,VolSessionTime,"
281 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
282 "Type,Level,ClientId,Name,PriorJobId,RealEndTime,JobId "
283 "FROM Job WHERE JobId=%s",
284 edit_int64(jr->JobId, ed1));
287 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
289 return false; /* failed */
291 if ((row = sql_fetch_row(mdb)) == NULL) {
292 Mmsg1(mdb->errmsg, _("No Job found for JobId %s\n"), edit_int64(jr->JobId, ed1));
293 sql_free_result(mdb);
295 return false; /* failed */
298 jr->VolSessionId = str_to_uint64(row[0]);
299 jr->VolSessionTime = str_to_uint64(row[1]);
300 jr->PoolId = str_to_int64(row[2]);
301 bstrncpy(jr->cStartTime, row[3]!=NULL?row[3]:"", sizeof(jr->cStartTime));
302 bstrncpy(jr->cEndTime, row[4]!=NULL?row[4]:"", sizeof(jr->cEndTime));
303 jr->JobFiles = str_to_int64(row[5]);
304 jr->JobBytes = str_to_int64(row[6]);
305 jr->JobTDate = str_to_int64(row[7]);
306 bstrncpy(jr->Job, row[8]!=NULL?row[8]:"", sizeof(jr->Job));
307 jr->JobStatus = (int)*row[9];
308 jr->JobType = (int)*row[10];
309 jr->JobLevel = (int)*row[11];
310 jr->ClientId = str_to_uint64(row[12]!=NULL?row[12]:(char *)"");
311 bstrncpy(jr->Name, row[13]!=NULL?row[13]:"", sizeof(jr->Name));
312 jr->PriorJobId = str_to_uint64(row[14]!=NULL?row[14]:(char *)"");
313 bstrncpy(jr->cRealEndTime, row[15]!=NULL?row[15]:"", sizeof(jr->cRealEndTime));
314 if (jr->JobId == 0) {
315 jr->JobId = str_to_int64(row[16]);
317 sql_free_result(mdb);
324 * Find VolumeNames for a given JobId
325 * Returns: 0 on error or no Volumes found
326 * number of volumes on success
327 * Volumes are concatenated in VolumeNames
328 * separated by a vertical bar (|) in the order
329 * that they were written.
331 * Returns: number of volumes on success
333 int db_get_job_volume_names(JCR *jcr, B_DB *mdb, JobId_t JobId, POOLMEM **VolumeNames)
341 /* Get one entry per VolumeName, but "sort" by VolIndex */
343 "SELECT VolumeName,MAX(VolIndex) FROM JobMedia,Media WHERE "
344 "JobMedia.JobId=%s AND JobMedia.MediaId=Media.MediaId "
345 "GROUP BY VolumeName "
346 "ORDER BY 2 ASC", edit_int64(JobId,ed1));
348 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
350 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
351 mdb->num_rows = sql_num_rows(mdb);
352 Dmsg1(130, "Num rows=%d\n", mdb->num_rows);
353 if (mdb->num_rows <= 0) {
354 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
357 stat = mdb->num_rows;
358 for (i=0; i < stat; i++) {
359 if ((row = sql_fetch_row(mdb)) == NULL) {
360 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
361 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
365 if (*VolumeNames[0] != 0) {
366 pm_strcat(VolumeNames, "|");
368 pm_strcat(VolumeNames, row[0]);
372 sql_free_result(mdb);
374 Mmsg(mdb->errmsg, _("No Volume for JobId %d found in Catalog.\n"), JobId);
381 * Find Volume parameters for a give JobId
382 * Returns: 0 on error or no Volumes found
383 * number of volumes on success
384 * List of Volumes and start/end file/blocks (malloced structure!)
386 * Returns: number of volumes on success
388 int db_get_job_volume_parameters(JCR *jcr, B_DB *mdb, JobId_t JobId, VOL_PARAMS **VolParams)
394 VOL_PARAMS *Vols = NULL;
398 "SELECT VolumeName,MediaType,FirstIndex,LastIndex,StartFile,"
399 "JobMedia.EndFile,StartBlock,JobMedia.EndBlock,Copy,"
401 " FROM JobMedia,Media WHERE JobMedia.JobId=%s"
402 " AND JobMedia.MediaId=Media.MediaId ORDER BY VolIndex,JobMediaId",
403 edit_int64(JobId, ed1));
405 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
406 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
407 mdb->num_rows = sql_num_rows(mdb);
408 Dmsg1(200, "Num rows=%d\n", mdb->num_rows);
409 if (mdb->num_rows <= 0) {
410 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
413 stat = mdb->num_rows;
416 *VolParams = Vols = (VOL_PARAMS *)malloc(stat * sizeof(VOL_PARAMS));
417 SId = (DBId_t *)malloc(stat * sizeof(DBId_t));
419 for (i=0; i < stat; i++) {
420 if ((row = sql_fetch_row(mdb)) == NULL) {
421 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
422 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
427 bstrncpy(Vols[i].VolumeName, row[0], MAX_NAME_LENGTH);
428 bstrncpy(Vols[i].MediaType, row[1], MAX_NAME_LENGTH);
429 Vols[i].FirstIndex = str_to_uint64(row[2]);
430 Vols[i].LastIndex = str_to_uint64(row[3]);
431 Vols[i].StartFile = str_to_uint64(row[4]);
432 Vols[i].EndFile = str_to_uint64(row[5]);
433 Vols[i].StartBlock = str_to_uint64(row[6]);
434 Vols[i].EndBlock = str_to_uint64(row[7]);
435 // Vols[i].Copy = str_to_uint64(row[8]);
436 Vols[i].Slot = str_to_uint64(row[9]);
437 StorageId = str_to_uint64(row[10]);
438 Vols[i].Storage[0] = 0;
442 for (i=0; i < stat; i++) {
444 Mmsg(mdb->cmd, "SELECT Name from Storage WHERE StorageId=%s",
445 edit_int64(SId[i], ed1));
446 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
447 if ((row = sql_fetch_row(mdb)) != NULL) {
448 bstrncpy(Vols[i].Storage, row[0], MAX_NAME_LENGTH);
457 sql_free_result(mdb);
466 * Get the number of pool records
468 * Returns: -1 on failure
471 int db_get_num_pool_records(JCR *jcr, B_DB *mdb)
476 Mmsg(mdb->cmd, "SELECT count(*) from Pool");
477 stat = get_sql_record_max(jcr, mdb);
483 * This function returns a list of all the Pool record ids.
484 * The caller must free ids if non-NULL.
486 * Returns 0: on failure
489 int db_get_pool_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
498 Mmsg(mdb->cmd, "SELECT PoolId FROM Pool");
499 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
500 *num_ids = sql_num_rows(mdb);
502 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
503 while ((row = sql_fetch_row(mdb)) != NULL) {
504 id[i++] = str_to_uint64(row[0]);
508 sql_free_result(mdb);
511 Mmsg(mdb->errmsg, _("Pool id select failed: ERR=%s\n"), sql_strerror(mdb));
512 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
520 * This function returns a list of all the Client record ids.
521 * The caller must free ids if non-NULL.
523 * Returns 0: on failure
526 int db_get_client_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
535 Mmsg(mdb->cmd, "SELECT ClientId FROM Client");
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, _("Client id select failed: ERR=%s\n"), sql_strerror(mdb));
549 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
559 * If the PoolId is non-zero, we get its record,
560 * otherwise, we search on the PoolName
562 * Returns: false on failure
565 bool db_get_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr)
572 if (pdbr->PoolId != 0) { /* find by id */
574 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
575 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
576 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId FROM Pool WHERE Pool.PoolId=%s",
577 edit_int64(pdbr->PoolId, ed1));
578 } else { /* find by name */
580 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
581 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
582 "MaxVolBytes,PoolType,LabelType,LabelFormat,RecyclePoolId FROM Pool WHERE Pool.Name='%s'",
586 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
587 mdb->num_rows = sql_num_rows(mdb);
588 if (mdb->num_rows > 1) {
590 Mmsg1(mdb->errmsg, _("More than one Pool!: %s\n"),
591 edit_uint64(mdb->num_rows, ed1));
592 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
593 } else if (mdb->num_rows == 1) {
594 if ((row = sql_fetch_row(mdb)) == NULL) {
595 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
596 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
598 pdbr->PoolId = str_to_int64(row[0]);
599 bstrncpy(pdbr->Name, row[1]!=NULL?row[1]:"", sizeof(pdbr->Name));
600 pdbr->NumVols = str_to_int64(row[2]);
601 pdbr->MaxVols = str_to_int64(row[3]);
602 pdbr->UseOnce = str_to_int64(row[4]);
603 pdbr->UseCatalog = str_to_int64(row[5]);
604 pdbr->AcceptAnyVolume = str_to_int64(row[6]);
605 pdbr->AutoPrune = str_to_int64(row[7]);
606 pdbr->Recycle = str_to_int64(row[8]);
607 pdbr->VolRetention = str_to_int64(row[9]);
608 pdbr->VolUseDuration = str_to_int64(row[10]);
609 pdbr->MaxVolJobs = str_to_int64(row[11]);
610 pdbr->MaxVolFiles = str_to_int64(row[12]);
611 pdbr->MaxVolBytes = str_to_uint64(row[13]);
612 bstrncpy(pdbr->PoolType, row[14]!=NULL?row[14]:"", sizeof(pdbr->PoolType));
613 pdbr->LabelType = str_to_int64(row[15]);
614 bstrncpy(pdbr->LabelFormat, row[16]!=NULL?row[16]:"", sizeof(pdbr->LabelFormat));
615 pdbr->RecyclePoolId = str_to_int64(row[17]);
619 sql_free_result(mdb);
623 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
624 edit_int64(pdbr->PoolId, ed1));
625 NumVols = get_sql_record_max(jcr, mdb);
626 Dmsg2(400, "Actual NumVols=%d Pool NumVols=%d\n", NumVols, pdbr->NumVols);
627 if (NumVols != pdbr->NumVols) {
628 pdbr->NumVols = NumVols;
629 db_update_pool_record(jcr, mdb, pdbr);
632 Mmsg(mdb->errmsg, _("Pool record not found in Catalog.\n"));
639 * If the ClientId is non-zero, we get its record,
640 * otherwise, we search on the Client Name
642 * Returns: 0 on failure
645 int db_get_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cdbr)
652 if (cdbr->ClientId != 0) { /* find by id */
654 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
655 "FROM Client WHERE Client.ClientId=%s",
656 edit_int64(cdbr->ClientId, ed1));
657 } else { /* find by name */
659 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
660 "FROM Client WHERE Client.Name='%s'", cdbr->Name);
663 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
664 mdb->num_rows = sql_num_rows(mdb);
665 if (mdb->num_rows > 1) {
666 Mmsg1(mdb->errmsg, _("More than one Client!: %s\n"),
667 edit_uint64(mdb->num_rows, ed1));
668 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
669 } else if (mdb->num_rows == 1) {
670 if ((row = sql_fetch_row(mdb)) == NULL) {
671 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
672 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
674 cdbr->ClientId = str_to_int64(row[0]);
675 bstrncpy(cdbr->Name, row[1]!=NULL?row[1]:"", sizeof(cdbr->Name));
676 bstrncpy(cdbr->Uname, row[2]!=NULL?row[2]:"", sizeof(cdbr->Uname));
677 cdbr->AutoPrune = str_to_int64(row[3]);
678 cdbr->FileRetention = str_to_int64(row[4]);
679 cdbr->JobRetention = str_to_int64(row[5]);
683 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
685 sql_free_result(mdb);
687 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
696 * Returns: 0 on failure
699 int db_get_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
704 Mmsg(mdb->cmd, "SELECT MinValue,MaxValue,CurrentValue,WrapCounter "
705 "FROM Counters WHERE Counter='%s'", cr->Counter);
707 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
708 mdb->num_rows = sql_num_rows(mdb);
710 /* If more than one, report error, but return first row */
711 if (mdb->num_rows > 1) {
712 Mmsg1(mdb->errmsg, _("More than one Counter!: %d\n"), (int)(mdb->num_rows));
713 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
715 if (mdb->num_rows >= 1) {
716 if ((row = sql_fetch_row(mdb)) == NULL) {
717 Mmsg1(mdb->errmsg, _("error fetching Counter row: %s\n"), sql_strerror(mdb));
718 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
719 sql_free_result(mdb);
723 cr->MinValue = str_to_int64(row[0]);
724 cr->MaxValue = str_to_int64(row[1]);
725 cr->CurrentValue = str_to_int64(row[2]);
727 bstrncpy(cr->WrapCounter, row[3], sizeof(cr->WrapCounter));
729 cr->WrapCounter[0] = 0;
731 sql_free_result(mdb);
735 sql_free_result(mdb);
737 Mmsg(mdb->errmsg, _("Counter record: %s not found in Catalog.\n"), cr->Counter);
744 /* Get FileSet Record
745 * If the FileSetId is non-zero, we get its record,
746 * otherwise, we search on the name
748 * Returns: 0 on failure
751 int db_get_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
758 if (fsr->FileSetId != 0) { /* find by id */
760 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
761 "WHERE FileSetId=%s",
762 edit_int64(fsr->FileSetId, ed1));
763 } else { /* find by name */
765 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
766 "WHERE FileSet='%s' ORDER BY CreateTime DESC LIMIT 1", fsr->FileSet);
769 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
770 mdb->num_rows = sql_num_rows(mdb);
771 if (mdb->num_rows > 1) {
773 Mmsg1(mdb->errmsg, _("Error got %s FileSets but expected only one!\n"),
774 edit_uint64(mdb->num_rows, ed1));
775 sql_data_seek(mdb, mdb->num_rows-1);
777 if ((row = sql_fetch_row(mdb)) == NULL) {
778 Mmsg1(mdb->errmsg, _("FileSet record \"%s\" not found.\n"), fsr->FileSet);
780 fsr->FileSetId = str_to_int64(row[0]);
781 bstrncpy(fsr->FileSet, row[1]!=NULL?row[1]:"", sizeof(fsr->FileSet));
782 bstrncpy(fsr->MD5, row[2]!=NULL?row[2]:"", sizeof(fsr->MD5));
783 bstrncpy(fsr->cCreateTime, row[3]!=NULL?row[3]:"", sizeof(fsr->cCreateTime));
784 stat = fsr->FileSetId;
786 sql_free_result(mdb);
788 Mmsg(mdb->errmsg, _("FileSet record not found in Catalog.\n"));
796 * Get the number of Media records
798 * Returns: -1 on failure
801 int db_get_num_media_records(JCR *jcr, B_DB *mdb)
806 Mmsg(mdb->cmd, "SELECT count(*) from Media");
807 stat = get_sql_record_max(jcr, mdb);
814 * This function returns a list of all the Media record ids for
815 * the current Pool with the correct Media Type.
816 * The caller must free ids if non-NULL.
818 * Returns false: on failure
821 bool db_get_media_ids(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr, int *num_ids, uint32_t *ids[])
831 Mmsg(mdb->cmd, "SELECT DISTINCT MediaId FROM Media WHERE PoolId=%s "
832 " AND MediaType='%s'",
833 edit_int64(mr->PoolId, ed1), mr->MediaType);
834 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
835 *num_ids = sql_num_rows(mdb);
837 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
838 while ((row = sql_fetch_row(mdb)) != NULL) {
839 id[i++] = str_to_uint64(row[0]);
843 sql_free_result(mdb);
846 Mmsg(mdb->errmsg, _("Media id select failed: ERR=%s\n"), sql_strerror(mdb));
847 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
857 * Returns: false: on failure
860 bool db_get_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
867 if (mr->MediaId == 0 && mr->VolumeName[0] == 0) {
868 Mmsg(mdb->cmd, "SELECT count(*) from Media");
869 mr->MediaId = get_sql_record_max(jcr, mdb);
873 if (mr->MediaId != 0) { /* find by id */
874 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
875 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
876 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
877 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
878 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
879 "Enabled,LocationId,RecycleCount,InitialWrite,"
880 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime "
881 "FROM Media WHERE MediaId=%s",
882 edit_int64(mr->MediaId, ed1));
883 } else { /* find by name */
884 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
885 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
886 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
887 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
888 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId,"
889 "Enabled,LocationId,RecycleCount,InitialWrite,"
890 "ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime "
891 "FROM Media WHERE VolumeName='%s'", mr->VolumeName);
894 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
896 mdb->num_rows = sql_num_rows(mdb);
897 if (mdb->num_rows > 1) {
898 Mmsg1(mdb->errmsg, _("More than one Volume!: %s\n"),
899 edit_uint64(mdb->num_rows, ed1));
900 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
901 } else if (mdb->num_rows == 1) {
902 if ((row = sql_fetch_row(mdb)) == NULL) {
903 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
904 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
907 mr->MediaId = str_to_int64(row[0]);
908 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
909 mr->VolJobs = str_to_int64(row[2]);
910 mr->VolFiles = str_to_int64(row[3]);
911 mr->VolBlocks = str_to_int64(row[4]);
912 mr->VolBytes = str_to_uint64(row[5]);
913 mr->VolMounts = str_to_int64(row[6]);
914 mr->VolErrors = str_to_int64(row[7]);
915 mr->VolWrites = str_to_int64(row[8]);
916 mr->MaxVolBytes = str_to_uint64(row[9]);
917 mr->VolCapacityBytes = str_to_uint64(row[10]);
918 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
919 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
920 mr->PoolId = str_to_int64(row[13]);
921 mr->VolRetention = str_to_uint64(row[14]);
922 mr->VolUseDuration = str_to_uint64(row[15]);
923 mr->MaxVolJobs = str_to_int64(row[16]);
924 mr->MaxVolFiles = str_to_int64(row[17]);
925 mr->Recycle = str_to_int64(row[18]);
926 mr->Slot = str_to_int64(row[19]);
927 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
928 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
929 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
930 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
931 mr->InChanger = str_to_uint64(row[22]);
932 mr->EndFile = str_to_uint64(row[23]);
933 mr->EndBlock = str_to_uint64(row[24]);
934 mr->VolParts = str_to_int64(row[25]);
935 mr->LabelType = str_to_int64(row[26]);
936 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
937 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
938 mr->StorageId = str_to_int64(row[28]);
939 mr->Enabled = str_to_int64(row[29]);
940 mr->LocationId = str_to_int64(row[30]);
941 mr->RecycleCount = str_to_int64(row[31]);
942 bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
943 mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
944 mr->ScratchPoolId = str_to_int64(row[33]);
945 mr->RecyclePoolId = str_to_int64(row[34]);
946 mr->VolReadTime = str_to_int64(row[35]);
947 mr->VolWriteTime = str_to_int64(row[36]);
952 if (mr->MediaId != 0) {
953 Mmsg1(mdb->errmsg, _("Media record MediaId=%s not found.\n"),
954 edit_int64(mr->MediaId, ed1));
956 Mmsg1(mdb->errmsg, _("Media record for Volume \"%s\" not found.\n"),
960 sql_free_result(mdb);
962 if (mr->MediaId != 0) {
963 Mmsg(mdb->errmsg, _("Media record for MediaId=%u not found in Catalog.\n"),
966 Mmsg(mdb->errmsg, _("Media record for Vol=%s not found in Catalog.\n"),
974 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/