2 * Bacula Catalog Database Get record interface routines
3 * Note, these routines generally get a record by id or
4 * by name. If more logic is involved, the routine
7 * Kern Sibbald, March 2000
13 Copyright (C) 2000-2006 Kern Sibbald
15 This program is free software; you can redistribute it and/or
16 modify it under the terms of the GNU General Public License
17 version 2 as amended with additional clauses defined in the
18 file LICENSE in the main source directory.
20 This program is distributed in the hope that it will be useful,
21 but WITHOUT ANY WARRANTY; without even the implied warranty of
22 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 the file LICENSE for additional details.
28 /* The following is necessary so that we do not include
29 * the dummy external definition of DB.
31 #define __SQL_C /* indicate that this is sql.c */
36 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL
38 /* -----------------------------------------------------------------------
40 * Generic Routines (or almost generic)
42 * -----------------------------------------------------------------------
45 /* Forward referenced functions */
46 static int db_get_file_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr, FILE_DBR *fdbr);
47 static int db_get_filename_record(JCR *jcr, B_DB *mdb);
48 static int db_get_path_record(JCR *jcr, B_DB *mdb);
51 /* Imported subroutines */
52 extern void print_result(B_DB *mdb);
53 extern int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
54 extern void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname);
59 * Given a full filename (with path), look up the File record
60 * (with attributes) in the database.
62 * Returns: 0 on failure
63 * 1 on success with the File record in FILE_DBR
65 int db_get_file_attributes_record(JCR *jcr, B_DB *mdb, char *fname, JOB_DBR *jr, FILE_DBR *fdbr)
68 Dmsg1(100, "db_get_file_att_record fname=%s \n", fname);
71 split_path_and_file(jcr, mdb, fname);
73 fdbr->FilenameId = db_get_filename_record(jcr, mdb);
75 fdbr->PathId = db_get_path_record(jcr, mdb);
77 stat = db_get_file_record(jcr, mdb, jr, fdbr);
87 * Returns: 0 on failure
90 * DO NOT use Jmsg in this routine.
92 * Note in this routine, we do not use Jmsg because it may be
93 * called to get attributes of a non-existent file, which is
94 * "normal" if a new file is found during Verify.
97 int db_get_file_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr, FILE_DBR *fdbr)
101 char ed1[50], ed2[50], ed3[50];
103 if (jcr->JobLevel == L_VERIFY_DISK_TO_CATALOG) {
105 "SELECT FileId, LStat, MD5 FROM File,Job WHERE "
106 "File.JobId=Job.JobId AND File.PathId=%s AND "
107 "File.FilenameId=%s AND Job.Type='B' AND Job.JobSTATUS='T' AND "
108 "ClientId=%s ORDER BY StartTime DESC LIMIT 1",
109 edit_int64(fdbr->PathId, ed1),
110 edit_int64(fdbr->FilenameId, ed2),
111 edit_int64(jr->ClientId,ed3));
115 "SELECT FileId, LStat, MD5 FROM File WHERE File.JobId=%s AND File.PathId=%s AND "
116 "File.FilenameId=%s",
117 edit_int64(fdbr->JobId, ed1),
118 edit_int64(fdbr->PathId, ed2),
119 edit_int64(fdbr->FilenameId,ed3));
121 Dmsg3(050, "Get_file_record JobId=%u FilenameId=%u PathId=%u\n",
122 fdbr->JobId, fdbr->FilenameId, fdbr->PathId);
124 Dmsg1(100, "Query=%s\n", mdb->cmd);
126 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
127 mdb->num_rows = sql_num_rows(mdb);
128 Dmsg1(050, "get_file_record num_rows=%d\n", (int)mdb->num_rows);
129 if (mdb->num_rows > 1) {
130 Mmsg1(mdb->errmsg, _("get_file_record want 1 got rows=%d\n"),
133 if (mdb->num_rows >= 1) {
134 if ((row = sql_fetch_row(mdb)) == NULL) {
135 Mmsg1(mdb->errmsg, _("Error fetching row: %s\n"), sql_strerror(mdb));
137 fdbr->FileId = (FileId_t)str_to_int64(row[0]);
138 bstrncpy(fdbr->LStat, row[1], sizeof(fdbr->LStat));
139 bstrncpy(fdbr->Digest, row[2], sizeof(fdbr->Digest));
143 Mmsg2(mdb->errmsg, _("File record for PathId=%s FilenameId=%s not found.\n"),
144 edit_int64(fdbr->PathId, ed1),
145 edit_int64(fdbr->FilenameId, ed2));
147 sql_free_result(mdb);
149 Mmsg(mdb->errmsg, _("File record not found in Catalog.\n"));
155 /* Get Filename record
156 * Returns: 0 on failure
157 * FilenameId on success
159 * DO NOT use Jmsg in this routine (see notes for get_file_record)
161 static int db_get_filename_record(JCR *jcr, B_DB *mdb)
166 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->fnl+2);
167 db_escape_string(mdb->esc_name, mdb->fname, mdb->fnl);
169 Mmsg(mdb->cmd, "SELECT FilenameId FROM Filename WHERE Name='%s'", mdb->esc_name);
170 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
172 mdb->num_rows = sql_num_rows(mdb);
173 if (mdb->num_rows > 1) {
174 Mmsg2(mdb->errmsg, _("More than one Filename!: %s for file: %s\n"),
175 edit_uint64(mdb->num_rows, ed1), mdb->fname);
176 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
178 if (mdb->num_rows >= 1) {
179 if ((row = sql_fetch_row(mdb)) == NULL) {
180 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
182 FilenameId = str_to_int64(row[0]);
183 if (FilenameId <= 0) {
184 Mmsg2(mdb->errmsg, _("Get DB Filename record %s found bad record: %d\n"),
185 mdb->cmd, FilenameId);
190 Mmsg1(mdb->errmsg, _("Filename record: %s not found.\n"), mdb->fname);
192 sql_free_result(mdb);
194 Mmsg(mdb->errmsg, _("Filename record: %s not found in Catalog.\n"), mdb->fname);
200 * Returns: 0 on failure
203 * DO NOT use Jmsg in this routine (see notes for get_file_record)
205 static int db_get_path_record(JCR *jcr, B_DB *mdb)
210 mdb->esc_name = check_pool_memory_size(mdb->esc_name, 2*mdb->pnl+2);
211 db_escape_string(mdb->esc_name, mdb->path, mdb->pnl);
213 if (mdb->cached_path_id != 0 && mdb->cached_path_len == mdb->pnl &&
214 strcmp(mdb->cached_path, mdb->path) == 0) {
215 return mdb->cached_path_id;
218 Mmsg(mdb->cmd, "SELECT PathId FROM Path WHERE Path='%s'", mdb->esc_name);
220 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
222 mdb->num_rows = sql_num_rows(mdb);
223 if (mdb->num_rows > 1) {
224 Mmsg2(mdb->errmsg, _("More than one Path!: %s for path: %s\n"),
225 edit_uint64(mdb->num_rows, ed1), mdb->path);
226 Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
228 /* Even if there are multiple paths, take the first one */
229 if (mdb->num_rows >= 1) {
230 if ((row = sql_fetch_row(mdb)) == NULL) {
231 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
233 PathId = str_to_int64(row[0]);
235 Mmsg2(mdb->errmsg, _("Get DB path record %s found bad record: %s\n"),
236 mdb->cmd, edit_int64(PathId, ed1));
240 if (PathId != mdb->cached_path_id) {
241 mdb->cached_path_id = PathId;
242 mdb->cached_path_len = mdb->pnl;
243 pm_strcpy(mdb->cached_path, mdb->path);
248 Mmsg1(mdb->errmsg, _("Path record: %s not found.\n"), mdb->path);
250 sql_free_result(mdb);
252 Mmsg(mdb->errmsg, _("Path record: %s not found in Catalog.\n"), mdb->path);
259 * Get Job record for given JobId or Job name
260 * Returns: false on failure
263 bool db_get_job_record(JCR *jcr, B_DB *mdb, JOB_DBR *jr)
269 if (jr->JobId == 0) {
270 Mmsg(mdb->cmd, "SELECT VolSessionId,VolSessionTime,"
271 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
272 "Type,Level,ClientId,Name "
273 "FROM Job WHERE Job='%s'", jr->Job);
275 Mmsg(mdb->cmd, "SELECT VolSessionId,VolSessionTime,"
276 "PoolId,StartTime,EndTime,JobFiles,JobBytes,JobTDate,Job,JobStatus,"
277 "Type,Level,ClientId,Name "
278 "FROM Job WHERE JobId=%s",
279 edit_int64(jr->JobId, ed1));
282 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
284 return false; /* failed */
286 if ((row = sql_fetch_row(mdb)) == NULL) {
287 Mmsg1(mdb->errmsg, _("No Job found for JobId %s\n"), edit_int64(jr->JobId, ed1));
288 sql_free_result(mdb);
290 return false; /* failed */
293 jr->VolSessionId = str_to_uint64(row[0]);
294 jr->VolSessionTime = str_to_uint64(row[1]);
295 jr->PoolId = str_to_int64(row[2]);
296 bstrncpy(jr->cStartTime, row[3]!=NULL?row[3]:"", sizeof(jr->cStartTime));
297 bstrncpy(jr->cEndTime, row[4]!=NULL?row[4]:"", sizeof(jr->cEndTime));
298 jr->JobFiles = str_to_int64(row[5]);
299 jr->JobBytes = str_to_int64(row[6]);
300 jr->JobTDate = str_to_int64(row[7]);
301 bstrncpy(jr->Job, row[8]!=NULL?row[8]:"", sizeof(jr->Job));
302 jr->JobStatus = (int)*row[9];
303 jr->JobType = (int)*row[10];
304 jr->JobLevel = (int)*row[11];
305 jr->ClientId = str_to_uint64(row[12]!=NULL?row[12]:(char *)"");
306 bstrncpy(jr->Name, row[13]!=NULL?row[13]:"", sizeof(jr->Name));
307 sql_free_result(mdb);
314 * Get MAC record for given JobId
315 * Returns: false on failure
318 bool db_get_mac_record(JCR *jcr, B_DB *mdb, MAC_DBR *mr)
324 Mmsg(mdb->cmd, "SELECT OriginalJobId,JobType,JobLevel,"
325 "SchedTime,StartTime,EndTime,JobTDate"
326 "FROM MAC WHERE JobId=%s",
327 edit_int64(mr->JobId, ed1));
329 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
331 return 0; /* failed */
333 if ((row = sql_fetch_row(mdb)) == NULL) {
334 Mmsg1(mdb->errmsg, _("No MAC record found for JobId %s\n"), ed1);
335 sql_free_result(mdb);
337 return false; /* failed */
340 mr->OriginalJobId = str_to_int64(row[0]);
341 mr->JobType = (int)*row[1];
342 mr->JobLevel = (int)*row[2];
343 bstrncpy(mr->cSchedTime, row[3]!=NULL?row[3]:"", sizeof(mr->cSchedTime));
344 bstrncpy(mr->cStartTime, row[4]!=NULL?row[4]:"", sizeof(mr->cStartTime));
345 bstrncpy(mr->cEndTime, row[5]!=NULL?row[5]:"", sizeof(mr->cEndTime));
346 mr->JobTDate = str_to_int64(row[6]);
347 sql_free_result(mdb);
355 * Find VolumeNames for a given JobId
356 * Returns: 0 on error or no Volumes found
357 * number of volumes on success
358 * Volumes are concatenated in VolumeNames
359 * separated by a vertical bar (|) in the order
360 * that they were written.
362 * Returns: number of volumes on success
364 int db_get_job_volume_names(JCR *jcr, B_DB *mdb, JobId_t JobId, POOLMEM **VolumeNames)
372 /* Get one entry per VolumeName, but "sort" by VolIndex */
374 "SELECT VolumeName,MAX(VolIndex) FROM JobMedia,Media WHERE "
375 "JobMedia.JobId=%s AND JobMedia.MediaId=Media.MediaId "
376 "GROUP BY VolumeName "
377 "ORDER BY 2 ASC", edit_int64(JobId,ed1));
379 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
381 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
382 mdb->num_rows = sql_num_rows(mdb);
383 Dmsg1(130, "Num rows=%d\n", mdb->num_rows);
384 if (mdb->num_rows <= 0) {
385 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
388 stat = mdb->num_rows;
389 for (i=0; i < stat; i++) {
390 if ((row = sql_fetch_row(mdb)) == NULL) {
391 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
392 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
396 if (*VolumeNames[0] != 0) {
397 pm_strcat(VolumeNames, "|");
399 pm_strcat(VolumeNames, row[0]);
403 sql_free_result(mdb);
405 Mmsg(mdb->errmsg, _("No Volume for JobId %d found in Catalog.\n"), JobId);
412 * Find Volume parameters for a give JobId
413 * Returns: 0 on error or no Volumes found
414 * number of volumes on success
415 * List of Volumes and start/end file/blocks (malloced structure!)
417 * Returns: number of volumes on success
419 int db_get_job_volume_parameters(JCR *jcr, B_DB *mdb, JobId_t JobId, VOL_PARAMS **VolParams)
425 VOL_PARAMS *Vols = NULL;
429 "SELECT VolumeName,MediaType,FirstIndex,LastIndex,StartFile,"
430 "JobMedia.EndFile,StartBlock,JobMedia.EndBlock,Copy,Stripe,"
432 " FROM JobMedia,Media WHERE JobMedia.JobId=%s"
433 " AND JobMedia.MediaId=Media.MediaId ORDER BY VolIndex,JobMediaId",
434 edit_int64(JobId, ed1));
436 Dmsg1(130, "VolNam=%s\n", mdb->cmd);
437 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
438 mdb->num_rows = sql_num_rows(mdb);
439 Dmsg1(200, "Num rows=%d\n", mdb->num_rows);
440 if (mdb->num_rows <= 0) {
441 Mmsg1(mdb->errmsg, _("No volumes found for JobId=%d\n"), JobId);
444 stat = mdb->num_rows;
447 *VolParams = Vols = (VOL_PARAMS *)malloc(stat * sizeof(VOL_PARAMS));
448 SId = (DBId_t *)malloc(stat * sizeof(DBId_t));
450 for (i=0; i < stat; i++) {
451 if ((row = sql_fetch_row(mdb)) == NULL) {
452 Mmsg2(mdb->errmsg, _("Error fetching row %d: ERR=%s\n"), i, sql_strerror(mdb));
453 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
458 bstrncpy(Vols[i].VolumeName, row[0], MAX_NAME_LENGTH);
459 bstrncpy(Vols[i].MediaType, row[1], MAX_NAME_LENGTH);
460 Vols[i].FirstIndex = str_to_uint64(row[2]);
461 Vols[i].LastIndex = str_to_uint64(row[3]);
462 Vols[i].StartFile = str_to_uint64(row[4]);
463 Vols[i].EndFile = str_to_uint64(row[5]);
464 Vols[i].StartBlock = str_to_uint64(row[6]);
465 Vols[i].EndBlock = str_to_uint64(row[7]);
466 // Vols[i].Copy = str_to_uint64(row[8]);
467 // Vols[i].Stripe = str_to_uint64(row[9]);
468 Vols[i].Slot = str_to_uint64(row[10]);
469 StorageId = str_to_uint64(row[11]);
470 Vols[i].Storage[0] = 0;
474 for (i=0; i < stat; i++) {
476 Mmsg(mdb->cmd, "SELECT Name from Storage WHERE StorageId=%s",
477 edit_int64(SId[i], ed1));
478 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
479 if ((row = sql_fetch_row(mdb)) != NULL) {
480 bstrncpy(Vols[i].Storage, row[0], MAX_NAME_LENGTH);
486 sql_free_result(mdb);
495 * Get the number of pool records
497 * Returns: -1 on failure
500 int db_get_num_pool_records(JCR *jcr, B_DB *mdb)
505 Mmsg(mdb->cmd, "SELECT count(*) from Pool");
506 stat = get_sql_record_max(jcr, mdb);
512 * This function returns a list of all the Pool record ids.
513 * The caller must free ids if non-NULL.
515 * Returns 0: on failure
518 int db_get_pool_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
527 Mmsg(mdb->cmd, "SELECT PoolId FROM Pool");
528 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
529 *num_ids = sql_num_rows(mdb);
531 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
532 while ((row = sql_fetch_row(mdb)) != NULL) {
533 id[i++] = str_to_uint64(row[0]);
537 sql_free_result(mdb);
540 Mmsg(mdb->errmsg, _("Pool id select failed: ERR=%s\n"), sql_strerror(mdb));
541 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
549 * This function returns a list of all the Client record ids.
550 * The caller must free ids if non-NULL.
552 * Returns 0: on failure
555 int db_get_client_ids(JCR *jcr, B_DB *mdb, int *num_ids, uint32_t *ids[])
564 Mmsg(mdb->cmd, "SELECT ClientId FROM Client");
565 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
566 *num_ids = sql_num_rows(mdb);
568 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
569 while ((row = sql_fetch_row(mdb)) != NULL) {
570 id[i++] = str_to_uint64(row[0]);
574 sql_free_result(mdb);
577 Mmsg(mdb->errmsg, _("Client id select failed: ERR=%s\n"), sql_strerror(mdb));
578 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
588 * If the PoolId is non-zero, we get its record,
589 * otherwise, we search on the PoolName
591 * Returns: false on failure
594 bool db_get_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr)
601 if (pdbr->PoolId != 0) { /* find by id */
603 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
604 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
605 "MaxVolBytes,PoolType,LabelType,LabelFormat FROM Pool WHERE Pool.PoolId=%s",
606 edit_int64(pdbr->PoolId, ed1));
607 } else { /* find by name */
609 "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,AcceptAnyVolume,"
610 "AutoPrune,Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,"
611 "MaxVolBytes,PoolType,LabelType,LabelFormat FROM Pool WHERE Pool.Name='%s'",
615 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
616 mdb->num_rows = sql_num_rows(mdb);
617 if (mdb->num_rows > 1) {
619 Mmsg1(mdb->errmsg, _("More than one Pool!: %s\n"),
620 edit_uint64(mdb->num_rows, ed1));
621 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
622 } else if (mdb->num_rows == 1) {
623 if ((row = sql_fetch_row(mdb)) == NULL) {
624 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
625 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
627 pdbr->PoolId = str_to_int64(row[0]);
628 bstrncpy(pdbr->Name, row[1]!=NULL?row[1]:"", sizeof(pdbr->Name));
629 pdbr->NumVols = str_to_int64(row[2]);
630 pdbr->MaxVols = str_to_int64(row[3]);
631 pdbr->UseOnce = str_to_int64(row[4]);
632 pdbr->UseCatalog = str_to_int64(row[5]);
633 pdbr->AcceptAnyVolume = str_to_int64(row[6]);
634 pdbr->AutoPrune = str_to_int64(row[7]);
635 pdbr->Recycle = str_to_int64(row[8]);
636 pdbr->VolRetention = str_to_int64(row[9]);
637 pdbr->VolUseDuration = str_to_int64(row[10]);
638 pdbr->MaxVolJobs = str_to_int64(row[11]);
639 pdbr->MaxVolFiles = str_to_int64(row[12]);
640 pdbr->MaxVolBytes = str_to_uint64(row[13]);
641 bstrncpy(pdbr->PoolType, row[14]!=NULL?row[14]:"", sizeof(pdbr->PoolType));
642 pdbr->LabelType = str_to_int64(row[15]);
643 bstrncpy(pdbr->LabelFormat, row[16]!=NULL?row[16]:"", sizeof(pdbr->LabelFormat));
647 sql_free_result(mdb);
651 Mmsg(mdb->cmd, "SELECT count(*) from Media WHERE PoolId=%s",
652 edit_int64(pdbr->PoolId, ed1));
653 NumVols = get_sql_record_max(jcr, mdb);
654 Dmsg2(400, "Actual NumVols=%d Pool NumVols=%d\n", NumVols, pdbr->NumVols);
655 if (NumVols != pdbr->NumVols) {
656 pdbr->NumVols = NumVols;
657 db_update_pool_record(jcr, mdb, pdbr);
660 Mmsg(mdb->errmsg, _("Pool record not found in Catalog.\n"));
667 * If the ClientId is non-zero, we get its record,
668 * otherwise, we search on the Client Name
670 * Returns: 0 on failure
673 int db_get_client_record(JCR *jcr, B_DB *mdb, CLIENT_DBR *cdbr)
680 if (cdbr->ClientId != 0) { /* find by id */
682 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
683 "FROM Client WHERE Client.ClientId=%s",
684 edit_int64(cdbr->ClientId, ed1));
685 } else { /* find by name */
687 "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention "
688 "FROM Client WHERE Client.Name='%s'", cdbr->Name);
691 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
692 mdb->num_rows = sql_num_rows(mdb);
693 if (mdb->num_rows > 1) {
694 Mmsg1(mdb->errmsg, _("More than one Client!: %s\n"),
695 edit_uint64(mdb->num_rows, ed1));
696 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
697 } else if (mdb->num_rows == 1) {
698 if ((row = sql_fetch_row(mdb)) == NULL) {
699 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
700 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
702 cdbr->ClientId = str_to_int64(row[0]);
703 bstrncpy(cdbr->Name, row[1]!=NULL?row[1]:"", sizeof(cdbr->Name));
704 bstrncpy(cdbr->Uname, row[2]!=NULL?row[2]:"", sizeof(cdbr->Uname));
705 cdbr->AutoPrune = str_to_int64(row[3]);
706 cdbr->FileRetention = str_to_int64(row[4]);
707 cdbr->JobRetention = str_to_int64(row[5]);
711 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
713 sql_free_result(mdb);
715 Mmsg(mdb->errmsg, _("Client record not found in Catalog.\n"));
724 * Returns: 0 on failure
727 int db_get_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
732 Mmsg(mdb->cmd, "SELECT MinValue,MaxValue,CurrentValue,WrapCounter "
733 "FROM Counters WHERE Counter='%s'", cr->Counter);
735 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
736 mdb->num_rows = sql_num_rows(mdb);
738 /* If more than one, report error, but return first row */
739 if (mdb->num_rows > 1) {
740 Mmsg1(mdb->errmsg, _("More than one Counter!: %d\n"), (int)(mdb->num_rows));
741 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
743 if (mdb->num_rows >= 1) {
744 if ((row = sql_fetch_row(mdb)) == NULL) {
745 Mmsg1(mdb->errmsg, _("error fetching Counter row: %s\n"), sql_strerror(mdb));
746 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
747 sql_free_result(mdb);
751 cr->MinValue = str_to_int64(row[0]);
752 cr->MaxValue = str_to_int64(row[1]);
753 cr->CurrentValue = str_to_int64(row[2]);
755 bstrncpy(cr->WrapCounter, row[3], sizeof(cr->WrapCounter));
757 cr->WrapCounter[0] = 0;
759 sql_free_result(mdb);
763 sql_free_result(mdb);
765 Mmsg(mdb->errmsg, _("Counter record: %s not found in Catalog.\n"), cr->Counter);
772 /* Get FileSet Record
773 * If the FileSetId is non-zero, we get its record,
774 * otherwise, we search on the name
776 * Returns: 0 on failure
779 int db_get_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr)
786 if (fsr->FileSetId != 0) { /* find by id */
788 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
789 "WHERE FileSetId=%s",
790 edit_int64(fsr->FileSetId, ed1));
791 } else { /* find by name */
793 "SELECT FileSetId,FileSet,MD5,CreateTime FROM FileSet "
794 "WHERE FileSet='%s' ORDER BY CreateTime DESC LIMIT 1", fsr->FileSet);
797 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
798 mdb->num_rows = sql_num_rows(mdb);
799 if (mdb->num_rows > 1) {
801 Mmsg1(mdb->errmsg, _("Error got %s FileSets but expected only one!\n"),
802 edit_uint64(mdb->num_rows, ed1));
803 sql_data_seek(mdb, mdb->num_rows-1);
805 if ((row = sql_fetch_row(mdb)) == NULL) {
806 Mmsg1(mdb->errmsg, _("FileSet record \"%s\" not found.\n"), fsr->FileSet);
808 fsr->FileSetId = str_to_int64(row[0]);
809 bstrncpy(fsr->FileSet, row[1]!=NULL?row[1]:"", sizeof(fsr->FileSet));
810 bstrncpy(fsr->MD5, row[2]!=NULL?row[2]:"", sizeof(fsr->MD5));
811 bstrncpy(fsr->cCreateTime, row[3]!=NULL?row[3]:"", sizeof(fsr->cCreateTime));
812 stat = fsr->FileSetId;
814 sql_free_result(mdb);
816 Mmsg(mdb->errmsg, _("FileSet record not found in Catalog.\n"));
824 * Get the number of Media records
826 * Returns: -1 on failure
829 int db_get_num_media_records(JCR *jcr, B_DB *mdb)
834 Mmsg(mdb->cmd, "SELECT count(*) from Media");
835 stat = get_sql_record_max(jcr, mdb);
842 * This function returns a list of all the Media record ids for
844 * The caller must free ids if non-NULL.
846 * Returns false: on failure
849 bool db_get_media_ids(JCR *jcr, B_DB *mdb, uint32_t PoolId, int *num_ids, uint32_t *ids[])
859 Mmsg(mdb->cmd, "SELECT MediaId FROM Media WHERE PoolId=%s",
860 edit_int64(PoolId, ed1));
861 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
862 *num_ids = sql_num_rows(mdb);
864 id = (uint32_t *)malloc(*num_ids * sizeof(uint32_t));
865 while ((row = sql_fetch_row(mdb)) != NULL) {
866 id[i++] = str_to_uint64(row[0]);
870 sql_free_result(mdb);
873 Mmsg(mdb->errmsg, _("Media id select failed: ERR=%s\n"), sql_strerror(mdb));
874 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
884 * Returns: false: on failure
887 bool db_get_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr)
894 if (mr->MediaId == 0 && mr->VolumeName[0] == 0) {
895 Mmsg(mdb->cmd, "SELECT count(*) from Media");
896 mr->MediaId = get_sql_record_max(jcr, mdb);
900 if (mr->MediaId != 0) { /* find by id */
901 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
902 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
903 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
904 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
905 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId "
906 "FROM Media WHERE MediaId=%s",
907 edit_int64(mr->MediaId, ed1));
908 } else { /* find by name */
909 Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
910 "VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
911 "MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
912 "MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
913 "EndFile,EndBlock,VolParts,LabelType,LabelDate,StorageId "
914 "FROM Media WHERE VolumeName='%s'", mr->VolumeName);
917 if (QUERY_DB(jcr, mdb, mdb->cmd)) {
919 mdb->num_rows = sql_num_rows(mdb);
920 if (mdb->num_rows > 1) {
921 Mmsg1(mdb->errmsg, _("More than one Volume!: %s\n"),
922 edit_uint64(mdb->num_rows, ed1));
923 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
924 } else if (mdb->num_rows == 1) {
925 if ((row = sql_fetch_row(mdb)) == NULL) {
926 Mmsg1(mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
927 Jmsg(jcr, M_ERROR, 0, "%s", mdb->errmsg);
930 mr->MediaId = str_to_int64(row[0]);
931 bstrncpy(mr->VolumeName, row[1]!=NULL?row[1]:"", sizeof(mr->VolumeName));
932 mr->VolJobs = str_to_int64(row[2]);
933 mr->VolFiles = str_to_int64(row[3]);
934 mr->VolBlocks = str_to_int64(row[4]);
935 mr->VolBytes = str_to_uint64(row[5]);
936 mr->VolMounts = str_to_int64(row[6]);
937 mr->VolErrors = str_to_int64(row[7]);
938 mr->VolWrites = str_to_int64(row[8]);
939 mr->MaxVolBytes = str_to_uint64(row[9]);
940 mr->VolCapacityBytes = str_to_uint64(row[10]);
941 bstrncpy(mr->MediaType, row[11]!=NULL?row[11]:"", sizeof(mr->MediaType));
942 bstrncpy(mr->VolStatus, row[12]!=NULL?row[12]:"", sizeof(mr->VolStatus));
943 mr->PoolId = str_to_int64(row[13]);
944 mr->VolRetention = str_to_uint64(row[14]);
945 mr->VolUseDuration = str_to_uint64(row[15]);
946 mr->MaxVolJobs = str_to_int64(row[16]);
947 mr->MaxVolFiles = str_to_int64(row[17]);
948 mr->Recycle = str_to_int64(row[18]);
949 mr->Slot = str_to_int64(row[19]);
950 bstrncpy(mr->cFirstWritten, row[20]!=NULL?row[20]:"", sizeof(mr->cFirstWritten));
951 mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
952 bstrncpy(mr->cLastWritten, row[21]!=NULL?row[21]:"", sizeof(mr->cLastWritten));
953 mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
954 mr->InChanger = str_to_uint64(row[22]);
955 mr->EndFile = str_to_uint64(row[23]);
956 mr->EndBlock = str_to_uint64(row[24]);
957 mr->VolParts = str_to_int64(row[25]);
958 mr->LabelType = str_to_int64(row[26]);
959 bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
960 mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
961 mr->StorageId = str_to_int64(row[28]);
965 if (mr->MediaId != 0) {
966 Mmsg1(mdb->errmsg, _("Media record MediaId=%s not found.\n"),
967 edit_int64(mr->MediaId, ed1));
969 Mmsg1(mdb->errmsg, _("Media record for Volume \"%s\" not found.\n"),
973 sql_free_result(mdb);
975 if (mr->MediaId != 0) {
976 Mmsg(mdb->errmsg, _("Media record for MediaId=%u not found in Catalog.\n"),
979 Mmsg(mdb->errmsg, _("Media record for Vol=%s not found in Catalog.\n"),
987 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL*/