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);
}
/*
* 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 "
"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 "
"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 "
"FROM Job, FileSet "
"WHERE Job.FileSetId = FileSet.FileSetId "
"AND Job.JobId='%s'%s", edit_int64(jr->JobId, esc), range);
} else { /* all records */
Mmsg(mdb->cmd, "SELECT DISTINCT FileSet.FileSetId AS FileSetId, FileSet, MD5, CreateTime "
"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);
}
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);
}
/*
* Submit general SQL query
*/
bool db_list_sql_query(JCR *jcr, B_DB *mdb, const char *query, OUTPUT_FORMATTER *sendit,
bool verbose, e_list_type type)
{
bool retval = false;
db_lock(mdb);
if (!sql_query(mdb, query, QF_STORE_RESULT)) {
Mmsg(mdb->errmsg, _("Query failed: %s\n"), sql_strerror(mdb));
if (verbose) {
sendit->decoration(mdb->errmsg);
}
goto bail_out;
}
sendit->object_start("query");
list_result(jcr, mdb, sendit, type);
sendit->object_end("query");
sql_free_result(mdb);
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);
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);
}
/*
* 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 if (mdbr->PoolId > 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.PoolId=%s ORDER BY MediaId",
edit_int64(mdbr->PoolId, ed1));
} 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 ORDER BY MediaId");
}
} 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 if (mdbr->PoolId > 0) {
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));
} else {
Mmsg(mdb->cmd, "SELECT MediaId,VolumeName,VolStatus,Enabled,"
"VolBytes,VolFiles,VolRetention,Recycle,Slot,InChanger,MediaType,LastWritten "
"FROM Media ORDER BY MediaId");
}
}
if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
goto bail_out;
}
list_result(jcr, mdb, sendit, type);
sql_free_result(mdb);
bail_out:
db_unlock(mdb);
}
/*
* 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);
}
/*
* 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);
}
