/*
* List Job totals
*
*/
void db_list_job_totals(JCR *jcr, B_DB *mdb, JOB_DBR *jr, OUTPUT_FORMATTER *sendit)
{
db_lock(mdb);
/* List by Job */
Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
"AS Files,sum(JobBytes) AS Bytes,Name AS Job FROM Job GROUP BY Name");
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
list_result(jcr, mdb, sendit, HORZ_LIST);
sql_free_result(mdb);
/* Do Grand Total */
Mmsg(mdb->cmd, "SELECT count(*) AS Jobs,sum(JobFiles) "
"AS Files,sum(JobBytes) As Bytes FROM Job");
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
list_result(jcr, mdb, sendit, HORZ_LIST);
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
void db_list_client_records(JCR *jcr, B_DB *mdb, char *clientname, OUTPUT_FORMATTER *sendit, e_list_type type)
{
db_lock(mdb);
POOL_MEM clientfilter(PM_MESSAGE);
if (clientname) {
clientfilter.bsprintf("WHERE Name = '%s'", clientname);
}
if (type == VERT_LIST) {
Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
"JobRetention "
"FROM Client %s ORDER BY ClientId ", clientfilter.c_str());
} else {
Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
"FROM Client %s ORDER BY ClientId", clientfilter.c_str());
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->array_start("clients");
list_result(jcr, mdb, sendit, type);
sendit->array_end("clients");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
void db_list_client_records(JCR *jcr, B_DB *mdb, OUTPUT_FORMATTER *sendit, e_list_type type)
{
db_lock(mdb);
if (type == VERT_LIST) {
Mmsg(mdb->cmd, "SELECT ClientId,Name,Uname,AutoPrune,FileRetention,"
"JobRetention "
"FROM Client ORDER BY ClientId");
} else {
Mmsg(mdb->cmd, "SELECT ClientId,Name,FileRetention,JobRetention "
"FROM Client ORDER BY ClientId");
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->object_start("clients");
list_result(jcr, mdb, sendit, type);
sendit->object_end("clients");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
/*
* Find last failed job since given start-time
* it must be either Full or Diff.
*
* Returns: false on failure
* true on success, jr is unchanged and stime unchanged
* level returned in JobLevel
*/
bool
db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
{
SQL_ROW row;
char ed1[50], ed2[50];
db_lock(mdb);
/* Differential is since last Full backup */
Mmsg(mdb->cmd,
"SELECT Level FROM Job WHERE JobStatus!='T' AND Type='%c' AND "
"Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
"AND FileSetId=%s AND StartTime>'%s' "
"ORDER BY StartTime DESC LIMIT 1",
jr->JobType, L_FULL, L_DIFFERENTIAL, jr->Name,
edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
stime);
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
db_unlock(mdb);
return false;
}
if ((row = sql_fetch_row(mdb)) == NULL) {
sql_free_result(mdb);
db_unlock(mdb);
return false;
}
JobLevel = (int)*row[0];
sql_free_result(mdb);
db_unlock(mdb);
return true;
}
/*
* Find last failed job since given start-time
* it must be either Full or Diff.
*
* Returns: false on failure
* true on success, jr is unchanged and stime unchanged
* level returned in JobLevel
*/
bool db_find_failed_job_since(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
{
bool retval = false;
SQL_ROW row;
char ed1[50], ed2[50];
char esc_name[MAX_ESCAPE_NAME_LENGTH];
db_lock(mdb);
mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
/* Differential is since last Full backup */
Mmsg(mdb->cmd,
"SELECT Level FROM Job WHERE JobStatus NOT IN ('T','W') AND "
"Type='%c' AND Level IN ('%c','%c') AND Name='%s' AND ClientId=%s "
"AND FileSetId=%s AND StartTime>'%s' "
"ORDER BY StartTime DESC LIMIT 1",
jr->JobType, L_FULL, L_DIFFERENTIAL, esc_name,
edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
stime);
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
if ((row = sql_fetch_row(mdb)) == NULL) {
sql_free_result(mdb);
goto bail_out;
}
JobLevel = (int)*row[0];
sql_free_result(mdb);
retval = true;
bail_out:
db_unlock(mdb);
return retval;
}
/*
* Find JobId of last job that ran. E.g. for
* VERIFY_CATALOG we want the JobId of the last INIT.
* For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
*
* Returns: true on success
* false on failure
*/
bool
db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
{
SQL_ROW row;
char ed1[50];
/* Find last full */
db_lock(mdb);
Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
if (jr->JobLevel == L_VERIFY_CATALOG) {
Mmsg(mdb->cmd,
"SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
" JobStatus IN ('T','W') AND Name='%s' AND "
"ClientId=%s ORDER BY StartTime DESC LIMIT 1",
L_VERIFY_INIT, jr->Name,
edit_int64(jr->ClientId, ed1));
} else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
jr->JobType == JT_BACKUP) {
if (Name) {
Mmsg(mdb->cmd,
"SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
"Name='%s' ORDER BY StartTime DESC LIMIT 1", Name);
} else {
Mmsg(mdb->cmd,
"SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
"ClientId=%s ORDER BY StartTime DESC LIMIT 1",
edit_int64(jr->ClientId, ed1));
}
} else {
Mmsg1(&mdb->errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
db_unlock(mdb);
return false;
}
Dmsg1(100, "Query: %s\n", mdb->cmd);
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
db_unlock(mdb);
return false;
}
if ((row = sql_fetch_row(mdb)) == NULL) {
Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
sql_free_result(mdb);
db_unlock(mdb);
return false;
}
jr->JobId = str_to_int64(row[0]);
sql_free_result(mdb);
Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
if (jr->JobId <= 0) {
Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
db_unlock(mdb);
return false;
}
db_unlock(mdb);
return true;
}
/*
* If VolumeName is non-zero, list the record for that Volume
* otherwise, list the Volumes in the Pool specified by PoolId
*/
void db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr,
OUTPUT_FORMATTER *sendit, e_list_type type)
{
char ed1[50];
char esc[MAX_ESCAPE_NAME_LENGTH];
db_lock(mdb);
mdb->db_escape_string(jcr, esc, mdbr->VolumeName, strlen(mdbr->VolumeName));
if (type == VERT_LIST) {
if (mdbr->VolumeName[0] != 0) {
Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
"MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
"VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
"VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
"VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
"EndFile,EndBlock,LabelType,StorageId,DeviceId,"
"LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
"Comment"
" FROM Media WHERE Media.VolumeName='%s'", esc);
} else {
Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,Slot,PoolId,"
"MediaType,FirstWritten,LastWritten,LabelDate,VolJobs,"
"VolFiles,VolBlocks,VolMounts,VolBytes,VolErrors,VolWrites,"
"VolCapacityBytes,VolStatus,Enabled,Recycle,VolRetention,"
"VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes,InChanger,"
"EndFile,EndBlock,LabelType,StorageId,DeviceId,"
"LocationId,RecycleCount,InitialWrite,ScratchPoolId,RecyclePoolId, "
"Comment"
" FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
edit_int64(mdbr->PoolId, ed1));
}
} else {
if (mdbr->VolumeName[0] != 0) {
Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
"VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
"FROM Media WHERE Media.VolumeName='%s'", esc);
} else {
Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
"VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
"FROM Media WHERE Media.PoolId=%s ORDER BY MediaId",
edit_int64(mdbr->PoolId, ed1));
}
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->object_start("media");
list_result(jcr, mdb, sendit, type);
sendit->object_end("media");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
/*
* Delete Pool record, must also delete all associated
* Media records.
*
* Returns: false on error
* true on success
* PoolId = number of Pools deleted (should be 1)
* NumVols = number of Media records deleted
*/
bool db_delete_pool_record(JCR *jcr, B_DB *mdb, POOL_DBR *pr)
{
bool retval = false;
SQL_ROW row;
int num_rows;
char esc[MAX_ESCAPE_NAME_LENGTH];
db_lock(mdb);
mdb->db_escape_string(jcr, esc, pr->Name, strlen(pr->Name));
Mmsg(mdb->cmd, "SELECT PoolId FROM Pool WHERE Name='%s'", esc);
Dmsg1(10, "selectpool: %s\n", mdb->cmd);
pr->PoolId = pr->NumVols = 0;
if (QUERY_DB(jcr, mdb, mdb->cmd)) {
num_rows = sql_num_rows(mdb);
if (num_rows == 0) {
Mmsg(mdb->errmsg, _("No pool record %s exists\n"), pr->Name);
sql_free_result(mdb);
goto bail_out;
} else if (num_rows != 1) {
Mmsg(mdb->errmsg, _("Expecting one pool record, got %d\n"), num_rows);
sql_free_result(mdb);
goto bail_out;
}
if ((row = sql_fetch_row(mdb)) == NULL) {
Mmsg1(mdb->errmsg, _("Error fetching row %s\n"), sql_strerror(mdb));
goto bail_out;
}
pr->PoolId = str_to_int64(row[0]);
sql_free_result(mdb);
}
/* Delete Media owned by this pool */
Mmsg(mdb->cmd,
"DELETE FROM Media WHERE Media.PoolId = %d", pr->PoolId);
pr->NumVols = DELETE_DB(jcr, mdb, mdb->cmd);
Dmsg1(200, "Deleted %d Media records\n", pr->NumVols);
/* Delete Pool */
Mmsg(mdb->cmd,
"DELETE FROM Pool WHERE Pool.PoolId = %d", pr->PoolId);
pr->PoolId = DELETE_DB(jcr, mdb, mdb->cmd);
Dmsg1(200, "Deleted %d Pool records\n", pr->PoolId);
retval = true;
bail_out:
db_unlock(mdb);
return retval;
}
/*
* Update Long term statistics with all jobs that were run before
* age seconds
*/
int db_update_stats(JCR *jcr, B_DB *mdb, utime_t age)
{
char ed1[30];
int rows;
utime_t now = (utime_t)time(NULL);
edit_uint64(now - age, ed1);
db_lock(mdb);
Mmsg(mdb->cmd, fill_jobhisto, ed1);
QUERY_DB(jcr, mdb, mdb->cmd); /* TODO: get a message ? */
rows = sql_affected_rows(mdb);
db_unlock(mdb);
return rows;
}
void db_list_copies_records(JCR *jcr, B_DB *mdb, uint32_t limit, char *JobIds,
OUTPUT_FORMATTER *send, e_list_type type)
{
POOL_MEM str_limit(PM_MESSAGE);
POOL_MEM str_jobids(PM_MESSAGE);
if (limit > 0) {
Mmsg(str_limit, " LIMIT %d", limit);
}
if (JobIds && JobIds[0]) {
Mmsg(str_jobids, " AND (Job.PriorJobId IN (%s) OR Job.JobId IN (%s)) ",
JobIds, JobIds);
}
db_lock(mdb);
Mmsg(mdb->cmd,
"SELECT DISTINCT Job.PriorJobId AS JobId, Job.Job, "
"Job.JobId AS CopyJobId, Media.MediaType "
"FROM Job "
"JOIN JobMedia USING (JobId) "
"JOIN Media USING (MediaId) "
"WHERE Job.Type = '%c' %s ORDER BY Job.PriorJobId DESC %s",
(char) JT_JOB_COPY, str_jobids.c_str(), str_limit.c_str());
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
if (sql_num_rows(mdb)) {
if (JobIds && JobIds[0]) {
send->decoration(_("These JobIds have copies as follows:\n"));
} else {
send->decoration(_("The catalog contains copies as follows:\n"));
}
send->object_start("copies");
list_result(jcr, mdb, send, type);
send->object_end("copies");
}
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
void db_list_pool_records(JCR *jcr, B_DB *mdb, POOL_DBR *pdbr,
OUTPUT_FORMATTER *sendit, e_list_type type)
{
char esc[MAX_ESCAPE_NAME_LENGTH];
db_lock(mdb);
mdb->db_escape_string(jcr, esc, pdbr->Name, strlen(pdbr->Name));
if (type == VERT_LIST) {
if (pdbr->Name[0] != 0) {
Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
"AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
"AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
"RecyclePoolId,LabelType "
" FROM Pool WHERE Name='%s'", esc);
} else {
Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,UseOnce,UseCatalog,"
"AcceptAnyVolume,VolRetention,VolUseDuration,MaxVolJobs,MaxVolBytes,"
"AutoPrune,Recycle,PoolType,LabelFormat,Enabled,ScratchPoolId,"
"RecyclePoolId,LabelType "
" FROM Pool ORDER BY PoolId");
}
} else {
if (pdbr->Name[0] != 0) {
Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
"FROM Pool WHERE Name='%s'", esc);
} else {
Mmsg(mdb->cmd, "SELECT PoolId,Name,NumVols,MaxVols,PoolType,LabelFormat "
"FROM Pool ORDER BY PoolId");
}
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->array_start("pools");
list_result(jcr, mdb, sendit, type);
sendit->array_end("pools");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
/*
* List fileset
*/
void db_list_filesets(JCR *jcr, B_DB *mdb, JOB_DBR *jr, OUTPUT_FORMATTER *sendit,
e_list_type type)
{
POOL_MEM str_limit(PM_MESSAGE);
char esc[MAX_ESCAPE_NAME_LENGTH];
db_lock(mdb);
if (jr->limit > 0) {
Mmsg(str_limit, " LIMIT %d", jr->limit);
}
if (jr->Name[0] != 0) {
mdb->db_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
Mmsg(mdb->cmd, "SELECT DISTINCT FileSetId, FileSet, MD5, CreateTime "
"FROM Job, FileSet "
"WHERE Job.FileSetId = FileSet.FileSetId "
"AND Job.Name='%s'%s", esc, str_limit.c_str());
} else if (jr->Job[0] != 0) {
mdb->db_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
Mmsg(mdb->cmd, "SELECT DISTINCT FileSetId, FileSet, MD5, CreateTime "
"FROM Job, FileSet "
"WHERE Job.FileSetId = FileSet.FileSetId "
"AND Job.Name='%s'%s", esc, str_limit.c_str());
} else if (jr->JobId != 0) {
Mmsg(mdb->cmd, "SELECT DISTINCT FileSetId, FileSet, MD5, CreateTime "
"FROM Job, FileSet "
"WHERE Job.FileSetId = FileSet.FileSetId "
"AND Job.JobId='%s'%s", edit_int64(jr->JobId, esc), str_limit.c_str());
} else { /* all records */
Mmsg(mdb->cmd, "SELECT DISTINCT FileSetId, FileSet, MD5, CreateTime "
"FROM FileSet ORDER BY FileSetId ASC%s", str_limit.c_str());
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->object_start("filesets");
list_result(jcr, mdb, sendit, type);
sendit->object_end("filesets");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
/*
* List fileset
*/
void db_list_filesets(JCR *jcr, B_DB *mdb, JOB_DBR *jr, const char *range,
OUTPUT_FORMATTER *sendit, e_list_type type)
{
char esc[MAX_ESCAPE_NAME_LENGTH];
db_lock(mdb);
if (jr->Name[0] != 0) {
mdb->db_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
Mmsg(mdb->cmd, "SELECT DISTINCT FileSet.FileSetId AS FileSetId, FileSet, MD5, CreateTime, FileSetText "
"FROM Job, FileSet "
"WHERE Job.FileSetId = FileSet.FileSetId "
"AND Job.Name='%s'%s", esc, range);
} else if (jr->Job[0] != 0) {
mdb->db_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
Mmsg(mdb->cmd, "SELECT DISTINCT FileSet.FileSetId AS FileSetId, FileSet, MD5, CreateTime, FileSetText "
"FROM Job, FileSet "
"WHERE Job.FileSetId = FileSet.FileSetId "
"AND Job.Name='%s'%s", esc, range);
} else if (jr->JobId != 0) {
Mmsg(mdb->cmd, "SELECT DISTINCT FileSet.FileSetId AS FileSetId, FileSet, MD5, CreateTime, FileSetText "
"FROM Job, FileSet "
"WHERE Job.FileSetId = FileSet.FileSetId "
"AND Job.JobId='%s'%s", edit_int64(jr->JobId, esc), range);
} else if (jr->FileSetId != 0) {
Mmsg(mdb->cmd, "SELECT FileSetId, FileSet, MD5, CreateTime, FileSetText "
"FROM FileSet "
"WHERE FileSetId=%s", edit_int64(jr->FileSetId, esc));
} else { /* all records */
Mmsg(mdb->cmd, "SELECT DISTINCT FileSet.FileSetId AS FileSetId, FileSet, MD5, CreateTime, FileSetText "
"FROM FileSet ORDER BY FileSetId ASC%s", range);
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->array_start("filesets");
list_result(jcr, mdb, sendit, type);
sendit->array_end("filesets");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
/*
* Get record max. Query is already in mdb->cmd
* No locking done
*
* Returns: -1 on failure
* count on success
*/
int get_sql_record_max(JCR *jcr, B_DB *mdb)
{
SQL_ROW row;
int retval = 0;
if (QUERY_DB(jcr, mdb, mdb->cmd)) {
if ((row = sql_fetch_row(mdb)) == NULL) {
Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
retval = -1;
} else {
retval = str_to_int64(row[0]);
}
sql_free_result(mdb);
} else {
Mmsg1(&mdb->errmsg, _("error fetching row: %s\n"), sql_strerror(mdb));
retval = -1;
}
return retval;
}
void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
OUTPUT_FORMATTER *sendit, e_list_type type)
{
char ed1[50];
db_lock(mdb);
if (type == VERT_LIST) {
if (JobId > 0) { /* do by JobId */
Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
"FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
"JobMedia.EndBlock "
"FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
"AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
} else {
Mmsg(mdb->cmd, "SELECT JobMediaId,JobId,Media.MediaId,Media.VolumeName,"
"FirstIndex,LastIndex,StartFile,JobMedia.EndFile,StartBlock,"
"JobMedia.EndBlock "
"FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
}
} else {
if (JobId > 0) { /* do by JobId */
Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
"FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId "
"AND JobMedia.JobId=%s", edit_int64(JobId, ed1));
} else {
Mmsg(mdb->cmd, "SELECT JobId,Media.VolumeName,FirstIndex,LastIndex "
"FROM JobMedia,Media WHERE Media.MediaId=JobMedia.MediaId");
}
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->array_start("jobmedia");
list_result(jcr, mdb, sendit, type);
sendit->array_end("jobmedia");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
/*
* Find the last job start time for the specified JobLevel
*
* StartTime is returned in stime
* Job name is returned in job (MAX_NAME_LENGTH)
*
* Returns: false on failure
* true on success, jr is unchanged, but stime and job are set
*/
bool db_find_last_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr,
POOLMEM **stime, char *job, int JobLevel)
{
bool retval = false;
SQL_ROW row;
char ed1[50], ed2[50];
char esc_name[MAX_ESCAPE_NAME_LENGTH];
db_lock(mdb);
mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
job[0] = 0;
Mmsg(mdb->cmd,
"SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
"Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
"ORDER BY StartTime DESC LIMIT 1",
jr->JobType, JobLevel, esc_name,
edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
sql_strerror(mdb), mdb->cmd);
goto bail_out;
}
if ((row = sql_fetch_row(mdb)) == NULL) {
sql_free_result(mdb);
Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
goto bail_out;
}
Dmsg1(100, "Got start time: %s\n", row[0]);
pm_strcpy(stime, row[0]);
bstrncpy(job, row[1], MAX_NAME_LENGTH);
sql_free_result(mdb);
retval = true;
bail_out:
db_unlock(mdb);
return retval;
}
void db_list_log_records(JCR *jcr, B_DB *mdb, const char* range, bool reverse,
OUTPUT_FORMATTER *sendit, e_list_type type)
{
const char *query;
const char *reverse_query = "SELECT LogId, Time, LogText FROM Log ORDER BY Log.LogId DESC %s";
const char *forward_query = "SELECT LogId, Time, LogText FROM ("
"SELECT LogId, Time, LogText FROM Log ORDER BY Log.LogId DESC %s"
") AS sub ORDER BY LogId ASC";
if (reverse) {
query = reverse_query;
} else {
query = forward_query;
}
db_lock(mdb);
if (type == VERT_LIST) {
Mmsg(mdb->cmd, query, range);
} else {
Mmsg(mdb->cmd, query, range);
/*
* When something else then a vertical list is requested set the list type
* to RAW_LIST e.g. non formated raw data as that makes the only sense for
* the logtext output. The logtext already has things like \n etc in it
* so we should just dump the raw content out for the best visible output.
*/
type = RAW_LIST;
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->array_start("log");
list_result(jcr, mdb, sendit, type);
sendit->array_end("log");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
void db_list_joblog_records(JCR *jcr, B_DB *mdb, uint32_t JobId,
OUTPUT_FORMATTER *sendit, e_list_type type)
{
char ed1[50];
if (JobId <= 0) {
return;
}
db_lock(mdb);
if (type == VERT_LIST) {
Mmsg(mdb->cmd, "SELECT Time, LogText FROM Log "
"WHERE Log.JobId=%s ORDER BY Log.LogId", edit_int64(JobId, ed1));
} else {
Mmsg(mdb->cmd, "SELECT Time, LogText FROM Log "
"WHERE Log.JobId=%s ORDER BY Log.LogId", edit_int64(JobId, ed1));
/*
* When something else then a vertical list is requested set the list type
* to RAW_LIST e.g. non formated raw data as that makes the only sense for
* the logtext output. The logtext already has things like \n etc in it
* so we should just dump the raw content out for the best visible output.
*/
type = RAW_LIST;
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->array_start("joblog");
list_result(jcr, mdb, sendit, type);
sendit->array_end("joblog");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
/*
* Find job start time if JobId specified, otherwise
* find last Job start time Incremental and Differential saves.
*
* StartTime is returned in stime
* Job name is returned in job (MAX_NAME_LENGTH)
*
* Returns: 0 on failure
* 1 on success, jr is unchanged, but stime and job are set
*/
bool db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime, char *job)
{
bool retval = false;
SQL_ROW row;
char ed1[50], ed2[50];
char esc_name[MAX_ESCAPE_NAME_LENGTH];
db_lock(mdb);
mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
pm_strcpy(stime, "0000-00-00 00:00:00"); /* default */
job[0] = 0;
/* If no Id given, we must find corresponding job */
if (jr->JobId == 0) {
/* Differential is since last Full backup */
Mmsg(mdb->cmd,
"SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
"Level='%c' AND Name='%s' AND ClientId=%s AND FileSetId=%s "
"ORDER BY StartTime DESC LIMIT 1",
jr->JobType, L_FULL, esc_name,
edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
if (jr->JobLevel == L_DIFFERENTIAL) {
/* SQL cmd for Differential backup already edited above */
/* Incremental is since last Full, Incremental, or Differential */
} else if (jr->JobLevel == L_INCREMENTAL) {
/*
* For an Incremental job, we must first ensure
* that a Full backup was done (cmd edited above)
* then we do a second look to find the most recent
* backup
*/
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
sql_strerror(mdb), mdb->cmd);
goto bail_out;
}
if ((row = sql_fetch_row(mdb)) == NULL) {
sql_free_result(mdb);
Mmsg(mdb->errmsg, _("No prior Full backup Job record found.\n"));
goto bail_out;
}
sql_free_result(mdb);
/* Now edit SQL command for Incremental Job */
Mmsg(mdb->cmd,
"SELECT StartTime, Job FROM Job WHERE JobStatus IN ('T','W') AND Type='%c' AND "
"Level IN ('%c','%c','%c') AND Name='%s' AND ClientId=%s "
"AND FileSetId=%s ORDER BY StartTime DESC LIMIT 1",
jr->JobType, L_INCREMENTAL, L_DIFFERENTIAL, L_FULL, esc_name,
edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
} else {
Mmsg1(mdb->errmsg, _("Unknown level=%d\n"), jr->JobLevel);
goto bail_out;
}
} else {
Dmsg1(100, "Submitting: %s\n", mdb->cmd);
Mmsg(mdb->cmd, "SELECT StartTime, Job FROM Job WHERE Job.JobId=%s",
edit_int64(jr->JobId, ed1));
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
pm_strcpy(stime, ""); /* set EOS */
Mmsg2(&mdb->errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
sql_strerror(mdb), mdb->cmd);
goto bail_out;
}
if ((row = sql_fetch_row(mdb)) == NULL) {
Mmsg2(&mdb->errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
sql_strerror(mdb), mdb->cmd);
sql_free_result(mdb);
goto bail_out;
}
Dmsg2(100, "Got start time: %s, job: %s\n", row[0], row[1]);
pm_strcpy(stime, row[0]);
bstrncpy(job, row[1], MAX_NAME_LENGTH);
sql_free_result(mdb);
retval = true;
bail_out:
db_unlock(mdb);
return retval;
}
/*
* Find Available Media (Volume) for Pool
*
* Find a Volume for a given PoolId, MediaType, and Status.
* The unwanted_volumes variable lists the VolumeNames which we should skip if any.
*
* Returns: 0 on failure
* numrows on success
*/
int db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr, const char *unwanted_volumes)
{
char ed1[50];
int num_rows = 0;
SQL_ROW row = NULL;
bool find_oldest = false;
bool found_candidate = false;
char esc_type[MAX_ESCAPE_NAME_LENGTH];
char esc_status[MAX_ESCAPE_NAME_LENGTH];
db_lock(mdb);
mdb->db_escape_string(jcr, esc_type, mr->MediaType, strlen(mr->MediaType));
mdb->db_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
if (item == -1) {
find_oldest = true;
item = 1;
}
retry_fetch:
if (find_oldest) {
/*
* Find oldest volume(s)
*/
Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
"VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
"MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
"MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
"EndFile,EndBlock,LabelType,LabelDate,StorageId,"
"Enabled,LocationId,RecycleCount,InitialWrite,"
"ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,"
"ActionOnPurge,EncryptionKey,MinBlocksize,MaxBlocksize "
"FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
"'Recycle','Purged','Used','Append') AND Enabled=1 "
"ORDER BY LastWritten LIMIT %d",
edit_int64(mr->PoolId, ed1), esc_type, item);
} else {
POOL_MEM changer(PM_FNAME);
const char *order;
/*
* Find next available volume
*/
if (InChanger) {
Mmsg(changer, "AND InChanger=1 AND StorageId=%s", edit_int64(mr->StorageId, ed1));
}
if (bstrcmp(mr->VolStatus, "Recycle") ||
bstrcmp(mr->VolStatus, "Purged")) {
order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
} else {
order = sql_media_order_most_recently_written[db_get_type_index(mdb)]; /* take most recently written */
}
Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
"VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
"MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
"MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
"EndFile,EndBlock,LabelType,LabelDate,StorageId,"
"Enabled,LocationId,RecycleCount,InitialWrite,"
"ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,"
"ActionOnPurge,EncryptionKey,MinBlocksize,MaxBlocksize "
"FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
"AND VolStatus='%s' "
"%s "
"%s LIMIT %d",
edit_int64(mr->PoolId, ed1), esc_type,
esc_status, changer.c_str(), order, item);
}
Dmsg1(100, "fnextvol=%s\n", mdb->cmd);
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
num_rows = sql_num_rows(mdb);
if (item > num_rows || item < 1) {
Dmsg2(050, "item=%d got=%d\n", item, num_rows);
Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"), item, num_rows);
num_rows = 0;
goto bail_out;
}
for (int i = 0 ; i < item; i++) {
if ((row = sql_fetch_row(mdb)) == NULL) {
Dmsg1(050, "Fail fetch item=%d\n", i);
Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), i);
sql_free_result(mdb);
num_rows = 0;
goto bail_out;
}
/*
* See if this is not on the unwanted volumes list.
*/
if (unwanted_volumes && is_on_unwanted_volumes_list(row[1], unwanted_volumes)) {
continue;
}
/*
* Return fields in Media Record
*/
mr->MediaId = str_to_int64(row[0]);
bstrncpy(mr->VolumeName, (row[1] != NULL) ? row[1] : "", sizeof(mr->VolumeName));
mr->VolJobs = str_to_int64(row[2]);
mr->VolFiles = str_to_int64(row[3]);
mr->VolBlocks = str_to_int64(row[4]);
mr->VolBytes = str_to_uint64(row[5]);
mr->VolMounts = str_to_int64(row[6]);
mr->VolErrors = str_to_int64(row[7]);
mr->VolWrites = str_to_int64(row[8]);
mr->MaxVolBytes = str_to_uint64(row[9]);
mr->VolCapacityBytes = str_to_uint64(row[10]);
bstrncpy(mr->MediaType, (row[11] != NULL) ? row[11] : "", sizeof(mr->MediaType));
bstrncpy(mr->VolStatus, (row[12] != NULL) ? row[12] : "", sizeof(mr->VolStatus));
mr->PoolId = str_to_int64(row[13]);
mr->VolRetention = str_to_uint64(row[14]);
mr->VolUseDuration = str_to_uint64(row[15]);
mr->MaxVolJobs = str_to_int64(row[16]);
mr->MaxVolFiles = str_to_int64(row[17]);
mr->Recycle = str_to_int64(row[18]);
mr->Slot = str_to_int64(row[19]);
bstrncpy(mr->cFirstWritten, (row[20] != NULL) ? row[20] : "", sizeof(mr->cFirstWritten));
mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
bstrncpy(mr->cLastWritten, (row[21] != NULL) ? row[21] : "", sizeof(mr->cLastWritten));
mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
mr->InChanger = str_to_uint64(row[22]);
mr->EndFile = str_to_uint64(row[23]);
mr->EndBlock = str_to_uint64(row[24]);
mr->LabelType = str_to_int64(row[25]);
bstrncpy(mr->cLabelDate, (row[26] != NULL) ? row[26] : "", sizeof(mr->cLabelDate));
mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
mr->StorageId = str_to_int64(row[27]);
mr->Enabled = str_to_int64(row[28]);
mr->LocationId = str_to_int64(row[29]);
mr->RecycleCount = str_to_int64(row[30]);
bstrncpy(mr->cInitialWrite, (row[31] != NULL) ? row[31] : "", sizeof(mr->cInitialWrite));
mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
mr->ScratchPoolId = str_to_int64(row[32]);
mr->RecyclePoolId = str_to_int64(row[33]);
mr->VolReadTime = str_to_int64(row[34]);
mr->VolWriteTime = str_to_int64(row[35]);
mr->ActionOnPurge = str_to_int64(row[36]);
bstrncpy(mr->EncrKey, (row[37] != NULL) ? row[37] : "", sizeof(mr->EncrKey));
mr->MinBlocksize = str_to_int32(row[38]);
mr->MaxBlocksize = str_to_int32(row[39]);
sql_free_result(mdb);
found_candidate = true;
break;
}
if (!found_candidate && find_oldest) {
item++;
goto retry_fetch;
}
bail_out:
db_unlock(mdb);
Dmsg1(050, "Rtn numrows=%d\n", num_rows);
return num_rows;
}
/*
* Find JobId of last job that ran. E.g. for
* VERIFY_CATALOG we want the JobId of the last INIT.
* For VERIFY_VOLUME_TO_CATALOG, we want the JobId of the last Job.
*
* Returns: true on success
* false on failure
*/
bool db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr)
{
bool retval = false;
SQL_ROW row;
char ed1[50];
char esc_name[MAX_ESCAPE_NAME_LENGTH];
db_lock(mdb);
/* Find last full */
Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
if (jr->JobLevel == L_VERIFY_CATALOG) {
mdb->db_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
Mmsg(mdb->cmd,
"SELECT JobId FROM Job WHERE Type='V' AND Level='%c' AND "
" JobStatus IN ('T','W') AND Name='%s' AND "
"ClientId=%s ORDER BY StartTime DESC LIMIT 1",
L_VERIFY_INIT, esc_name,
edit_int64(jr->ClientId, ed1));
} else if (jr->JobLevel == L_VERIFY_VOLUME_TO_CATALOG ||
jr->JobLevel == L_VERIFY_DISK_TO_CATALOG ||
jr->JobType == JT_BACKUP) {
if (Name) {
mdb->db_escape_string(jcr, esc_name, (char*)Name,
MIN(strlen(Name), sizeof(esc_name)));
Mmsg(mdb->cmd,
"SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
"Name='%s' ORDER BY StartTime DESC LIMIT 1", esc_name);
} else {
Mmsg(mdb->cmd,
"SELECT JobId FROM Job WHERE Type='B' AND JobStatus IN ('T','W') AND "
"ClientId=%s ORDER BY StartTime DESC LIMIT 1",
edit_int64(jr->ClientId, ed1));
}
} else {
Mmsg1(&mdb->errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
goto bail_out;
}
Dmsg1(100, "Query: %s\n", mdb->cmd);
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
if ((row = sql_fetch_row(mdb)) == NULL) {
Mmsg1(&mdb->errmsg, _("No Job found for: %s.\n"), mdb->cmd);
sql_free_result(mdb);
goto bail_out;
}
jr->JobId = str_to_int64(row[0]);
sql_free_result(mdb);
Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
if (jr->JobId <= 0) {
Mmsg1(&mdb->errmsg, _("No Job found for: %s\n"), mdb->cmd);
goto bail_out;
}
retval = true;
bail_out:
db_unlock(mdb);
return retval;
}
/*
* List Job record(s) that match JOB_DBR
*/
void db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, const char *range,
const char* clientname, int jobstatus, const char* volumename,
utime_t since_time, int last, int count,
OUTPUT_FORMATTER *sendit, e_list_type type)
{
char ed1[50];
char esc[MAX_ESCAPE_NAME_LENGTH];
POOL_MEM temp(PM_MESSAGE),
selection(PM_MESSAGE),
criteria(PM_MESSAGE);
char dt[MAX_TIME_LENGTH];
if (jr->JobId > 0) {
temp.bsprintf("AND Job.JobId=%s", edit_int64(jr->JobId, ed1));
pm_strcat(selection, temp.c_str());
}
if (jr->Name[0] != 0) {
mdb->db_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
temp.bsprintf( "AND Job.Name = '%s' ", esc);
pm_strcat(selection, temp.c_str());
}
if (clientname) {
temp.bsprintf("AND Client.Name = '%s' ", clientname);
pm_strcat(selection, temp.c_str());
}
if (jobstatus) {
temp.bsprintf("AND Job.JobStatus = '%c' ", jobstatus);
pm_strcat(selection, temp.c_str());
}
if (volumename) {
temp.bsprintf("AND Media.Volumename = '%s' ", volumename);
pm_strcat(selection, temp.c_str());
}
if (since_time) {
bstrutime(dt, sizeof(dt), since_time);
temp.bsprintf("AND Job.SchedTime > '%s' ", dt);
pm_strcat(selection, temp.c_str());
}
db_lock(mdb);
if (count > 0) {
Mmsg(mdb->cmd, list_jobs_count, selection.c_str(), range);
} else if (last > 0) {
if (type == VERT_LIST) {
Mmsg(mdb->cmd, list_jobs_long_last, selection.c_str(), range);
} else {
Mmsg(mdb->cmd, list_jobs_last, selection.c_str(), range);
}
} else {
if (type == VERT_LIST) {
Mmsg(mdb->cmd, list_jobs_long, selection.c_str(), range);
} else {
Mmsg(mdb->cmd, list_jobs, selection.c_str(), range);
}
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->array_start("jobs");
list_result(jcr, mdb, sendit, type);
sendit->array_end("jobs");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
/*
* List Job record(s) that match JOB_DBR
*/
void db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, const char *range,
const char* clientname, int jobstatus, const char* volumename,
utime_t since_time, int last, int count,
OUTPUT_FORMATTER *sendit, e_list_type type)
{
char ed1[50];
char esc[MAX_ESCAPE_NAME_LENGTH];
POOL_MEM temp(PM_MESSAGE),
selection(PM_MESSAGE),
criteria(PM_MESSAGE);
POOL_MEM selection_last(PM_MESSAGE);
char dt[MAX_TIME_LENGTH];
if (jr->JobId > 0) {
temp.bsprintf("AND Job.JobId=%s", edit_int64(jr->JobId, ed1));
pm_strcat(selection, temp.c_str());
}
if (jr->Name[0] != 0) {
mdb->db_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
temp.bsprintf( "AND Job.Name = '%s' ", esc);
pm_strcat(selection, temp.c_str());
}
if (clientname) {
temp.bsprintf("AND Client.Name = '%s' ", clientname);
pm_strcat(selection, temp.c_str());
}
if (jobstatus) {
temp.bsprintf("AND Job.JobStatus = '%c' ", jobstatus);
pm_strcat(selection, temp.c_str());
}
if (volumename) {
temp.bsprintf("AND Media.Volumename = '%s' ", volumename);
pm_strcat(selection, temp.c_str());
}
if (since_time) {
bstrutime(dt, sizeof(dt), since_time);
temp.bsprintf("AND Job.SchedTime > '%s' ", dt);
pm_strcat(selection, temp.c_str());
}
if (last > 0) {
/*
* Show only the last run of a job (Job.Name).
* Do a subquery to get a list of matching JobIds
* to be used in the main query later.
*
* range: while it might be more efficient,
* to apply the range to the subquery,
* at least mariadb 10 does not support this.
* Therefore range is handled in the main query.
*/
temp.bsprintf("AND Job.JobId IN (%s) ", list_jobs_last);
selection_last.bsprintf(temp.c_str(), selection.c_str(), "");
/*
* As the existing selection is handled in the subquery,
* overwrite the main query selection
* by the newly created selection_last.
*/
pm_strcpy(selection, selection_last.c_str());
}
db_lock(mdb);
if (count > 0) {
Mmsg(mdb->cmd, list_jobs_count, selection.c_str(), range);
} else {
if (type == VERT_LIST) {
Mmsg(mdb->cmd, list_jobs_long, selection.c_str(), range);
} else {
Mmsg(mdb->cmd, list_jobs, selection.c_str(), range);
}
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->array_start("jobs");
list_result(jcr, mdb, sendit, type);
sendit->array_end("jobs");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
/*
* Find Available Media (Volume) for Pool
*
* Find a Volume for a given PoolId, MediaType, and Status.
*
* Returns: 0 on failure
* numrows on success
*/
int db_find_next_volume(JCR *jcr, B_DB *mdb, int item, bool InChanger, MEDIA_DBR *mr)
{
SQL_ROW row = NULL;
int num_rows = 0;
const char *order;
char esc_type[MAX_ESCAPE_NAME_LENGTH];
char esc_status[MAX_ESCAPE_NAME_LENGTH];
char ed1[50];
db_lock(mdb);
mdb->db_escape_string(jcr, esc_type, mr->MediaType, strlen(mr->MediaType));
mdb->db_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
if (item == -1) { /* find oldest volume */
/* Find oldest volume */
Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
"VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
"MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
"MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
"EndFile,EndBlock,LabelType,LabelDate,StorageId,"
"Enabled,LocationId,RecycleCount,InitialWrite,"
"ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,"
"ActionOnPurge,EncryptionKey,MinBlocksize,MaxBlocksize "
"FROM Media WHERE PoolId=%s AND MediaType='%s' AND VolStatus IN ('Full',"
"'Recycle','Purged','Used','Append') AND Enabled=1 "
"ORDER BY LastWritten LIMIT 1",
edit_int64(mr->PoolId, ed1), esc_type);
item = 1;
} else {
POOL_MEM changer(PM_FNAME);
/* Find next available volume */
if (InChanger) {
Mmsg(changer, "AND InChanger=1 AND StorageId=%s",
edit_int64(mr->StorageId, ed1));
}
if (bstrcmp(mr->VolStatus, "Recycle") ||
bstrcmp(mr->VolStatus, "Purged")) {
order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
} else {
order = sql_media_order_most_recently_written[db_get_type_index(mdb)]; /* take most recently written */
}
Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolJobs,VolFiles,VolBlocks,"
"VolBytes,VolMounts,VolErrors,VolWrites,MaxVolBytes,VolCapacityBytes,"
"MediaType,VolStatus,PoolId,VolRetention,VolUseDuration,MaxVolJobs,"
"MaxVolFiles,Recycle,Slot,FirstWritten,LastWritten,InChanger,"
"EndFile,EndBlock,LabelType,LabelDate,StorageId,"
"Enabled,LocationId,RecycleCount,InitialWrite,"
"ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,"
"ActionOnPurge,EncryptionKey,MinBlocksize,MaxBlocksize "
"FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
"AND VolStatus='%s' "
"%s "
"%s LIMIT %d",
edit_int64(mr->PoolId, ed1), esc_type,
esc_status, changer.c_str(), order, item);
}
Dmsg1(100, "fnextvol=%s\n", mdb->cmd);
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
num_rows = sql_num_rows(mdb);
if (item > num_rows || item < 1) {
Dmsg2(050, "item=%d got=%d\n", item, num_rows);
Mmsg2(&mdb->errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
item, num_rows);
num_rows = 0;
goto bail_out;
}
/* Note, we previously seeked to the row using: sql_data_seek(mdb, item-1);
* but this failed on PostgreSQL, so now we loop over all the records.
* This should not be too horrible since the maximum Volumes we look at
* in any case is 20.
*/
while (item-- > 0) {
if ((row = sql_fetch_row(mdb)) == NULL) {
Dmsg1(050, "Fail fetch item=%d\n", item+1);
Mmsg1(&mdb->errmsg, _("No Volume record found for item %d.\n"), item);
sql_free_result(mdb);
num_rows = 0;
goto bail_out;
}
}
/* Return fields in Media Record */
mr->MediaId = str_to_int64(row[0]);
bstrncpy(mr->VolumeName, (row[1] != NULL) ? row[1] : "", sizeof(mr->VolumeName));
mr->VolJobs = str_to_int64(row[2]);
mr->VolFiles = str_to_int64(row[3]);
mr->VolBlocks = str_to_int64(row[4]);
mr->VolBytes = str_to_uint64(row[5]);
mr->VolMounts = str_to_int64(row[6]);
mr->VolErrors = str_to_int64(row[7]);
mr->VolWrites = str_to_int64(row[8]);
mr->MaxVolBytes = str_to_uint64(row[9]);
mr->VolCapacityBytes = str_to_uint64(row[10]);
bstrncpy(mr->MediaType, (row[11] != NULL) ? row[11] : "", sizeof(mr->MediaType));
bstrncpy(mr->VolStatus, (row[12] != NULL) ? row[12] : "", sizeof(mr->VolStatus));
mr->PoolId = str_to_int64(row[13]);
mr->VolRetention = str_to_uint64(row[14]);
mr->VolUseDuration = str_to_uint64(row[15]);
mr->MaxVolJobs = str_to_int64(row[16]);
mr->MaxVolFiles = str_to_int64(row[17]);
mr->Recycle = str_to_int64(row[18]);
mr->Slot = str_to_int64(row[19]);
bstrncpy(mr->cFirstWritten, (row[20] != NULL) ? row[20] : "", sizeof(mr->cFirstWritten));
mr->FirstWritten = (time_t)str_to_utime(mr->cFirstWritten);
bstrncpy(mr->cLastWritten, (row[21] != NULL) ? row[21] : "", sizeof(mr->cLastWritten));
mr->LastWritten = (time_t)str_to_utime(mr->cLastWritten);
mr->InChanger = str_to_uint64(row[22]);
mr->EndFile = str_to_uint64(row[23]);
mr->EndBlock = str_to_uint64(row[24]);
mr->LabelType = str_to_int64(row[25]);
bstrncpy(mr->cLabelDate, (row[26] != NULL) ? row[26] : "", sizeof(mr->cLabelDate));
mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
mr->StorageId = str_to_int64(row[27]);
mr->Enabled = str_to_int64(row[28]);
mr->LocationId = str_to_int64(row[29]);
mr->RecycleCount = str_to_int64(row[30]);
bstrncpy(mr->cInitialWrite, (row[31] != NULL) ? row[31] : "", sizeof(mr->cInitialWrite));
mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
mr->ScratchPoolId = str_to_int64(row[32]);
mr->RecyclePoolId = str_to_int64(row[33]);
mr->VolReadTime = str_to_int64(row[34]);
mr->VolWriteTime = str_to_int64(row[35]);
mr->ActionOnPurge = str_to_int64(row[36]);
bstrncpy(mr->EncrKey, (row[37] != NULL) ? row[37] : "", sizeof(mr->EncrKey));
mr->MinBlocksize = str_to_int32(row[38]);
mr->MaxBlocksize = str_to_int32(row[39]);
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
Dmsg1(050, "Rtn numrows=%d\n", num_rows);
return num_rows;
}
/*
* List Job record(s) that match JOB_DBR
*
* Currently, we return all jobs or if jr->JobId is set,
* only the job with the specified id.
*/
void db_list_job_records(JCR *jcr, B_DB *mdb, JOB_DBR *jr, OUTPUT_FORMATTER *sendit,
e_list_type type)
{
char ed1[50];
char limit[100];
char esc[MAX_ESCAPE_NAME_LENGTH];
db_lock(mdb);
if (jr->limit > 0) {
snprintf(limit, sizeof(limit), " LIMIT %d", jr->limit);
} else {
limit[0] = 0;
}
if (type == VERT_LIST) {
if (jr->JobId == 0 && jr->Job[0] == 0) {
Mmsg(mdb->cmd,
"SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
"Job.ClientId,Client.Name as ClientName,JobStatus,SchedTime,"
"StartTime,EndTime,RealEndTime,JobTDate,"
"VolSessionId,VolSessionTime,JobFiles,JobErrors,"
"JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
"Job.FileSetId,FileSet.FileSet "
"FROM Job,Client,Pool,FileSet WHERE "
"Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
"AND FileSet.FileSetId=Job.FileSetId ORDER BY StartTime%s", limit);
} else { /* single record */
Mmsg(mdb->cmd,
"SELECT JobId,Job,Job.Name,PurgedFiles,Type,Level,"
"Job.ClientId,Client.Name,JobStatus,SchedTime,"
"StartTime,EndTime,RealEndTime,JobTDate,"
"VolSessionId,VolSessionTime,JobFiles,JobErrors,"
"JobMissingFiles,Job.PoolId,Pool.Name as PooLname,PriorJobId,"
"Job.FileSetId,FileSet.FileSet "
"FROM Job,Client,Pool,FileSet WHERE Job.JobId=%s AND "
"Client.ClientId=Job.ClientId AND Pool.PoolId=Job.PoolId "
"AND FileSet.FileSetId=Job.FileSetId",
edit_int64(jr->JobId, ed1));
}
} else {
if (jr->Name[0] != 0) {
mdb->db_escape_string(jcr, esc, jr->Name, strlen(jr->Name));
Mmsg(mdb->cmd,
"SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
"FROM Job WHERE Name='%s' ORDER BY JobId ASC", esc);
} else if (jr->Job[0] != 0) {
mdb->db_escape_string(jcr, esc, jr->Job, strlen(jr->Job));
Mmsg(mdb->cmd,
"SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
"FROM Job WHERE Job='%s' ORDER BY JobId ASC", esc);
} else if (jr->JobId != 0) {
Mmsg(mdb->cmd,
"SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
"FROM Job WHERE JobId=%s", edit_int64(jr->JobId, ed1));
} else { /* all records */
Mmsg(mdb->cmd,
"SELECT JobId,Name,StartTime,Type,Level,JobFiles,JobBytes,JobStatus "
"FROM Job ORDER BY JobId ASC%s", limit);
}
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
sendit->object_start("jobs");
list_result(jcr, mdb, sendit, type);
sendit->object_end("jobs");
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}