wxSQLite3Database* objsearch_pi::initDB(void)
{
bool have_to_create = false;
wxString sDBName = *GetpPrivateApplicationDataLocation() + wxFileName::GetPathSeparator()+ wxT("objsearch_pi.db");
wxLogMessage( _T("OBJSEARCH_PI: Database file to be used: %s"), sDBName.c_str() );
if ( !wxFileExists(sDBName) )
{
have_to_create = true;
}
wxSQLite3Database* db = new wxSQLite3Database();
try
{
db->Open( sDBName );
}
catch (wxSQLite3Exception& e)
{
wxLogMessage( _T("OBJSEARCH_PI: DB Exception: %i : %s"), e.GetErrorCode(), e.GetMessage().c_str() );
m_bDBUsable = false;
}
catch (...)
{
wxLogMessage( _T("OBJSEARCH_PI: Unknown exception") );
m_bDBUsable = false;
}
if ( have_to_create && m_bDBUsable )
{
QueryDB( db, wxT("CREATE TABLE chart (id INTEGER PRIMARY KEY AUTOINCREMENT, chartname TEXT, scale REAL, nativescale INTEGER)") );
QueryDB( db, wxT("CREATE TABLE feature (id INTEGER PRIMARY KEY AUTOINCREMENT, featurename TEXT)") );
QueryDB( db, wxT("CREATE TABLE object (chart_id INTEGER, feature_id INTEGER, objname TEXT, lat REAL, lon REAL)") );
}
if ( m_bDBUsable )
{
db->CreateFunction(_T("distanceMercator"), 4, distMercFunc, true);
//sqlite3_create_function(db, "distanceMercator", 4, SQLITE_UTF8, NULL, &distanceMercatorFunc, NULL, NULL));
QueryDB( db, _T("PRAGMA synchronous=OFF") );
QueryDB( db, _T("PRAGMA count_changes=OFF") );
QueryDB( db, _T("PRAGMA journal_mode=MEMORY") );
QueryDB( db, _T("PRAGMA temp_store=MEMORY") );
//Fix the broken objects created by v 0.1 and 0.2
QueryDB( db, _T("UPDATE object SET lon = lon - 360 WHERE lon > 180") );
QueryDB( db, _T("UPDATE object SET lon = lon + 360 WHERE lon < - 180") );
QueryDB( db, _T("DELETE FROM object WHERE lon < - 180 OR lon > 180 OR lat < -90 OR lat > 90") );
}
return db;
}
wxLongLong objsearch_pi::StoreNewFeature(wxString feature)
{
if ( !m_bDBUsable )
return -1;
QueryDB( m_db, wxString::Format(_T("INSERT INTO feature(featurename) VALUES ('%s')"), feature.c_str()) );
return m_db->GetLastRowId();
}
/*
* 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 BDB::bdb_find_failed_job_since(JCR *jcr, JOB_DBR *jr, POOLMEM *stime, int &JobLevel)
{
SQL_ROW row;
char ed1[50], ed2[50];
char esc_name[MAX_ESCAPE_NAME_LENGTH];
bdb_lock();
bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
/* Differential is since last Full backup */
Mmsg(cmd,
"SELECT Level FROM Job WHERE JobStatus IN ('%c','%c', '%c', '%c') 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",
JS_Canceled, JS_ErrorTerminated, JS_Error, JS_FatalError,
jr->JobType, L_FULL, L_DIFFERENTIAL, esc_name,
edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2),
stime);
if (!QueryDB(jcr, cmd)) {
bdb_unlock();
return false;
}
if ((row = sql_fetch_row()) == NULL) {
sql_free_result();
bdb_unlock();
return false;
}
JobLevel = (int)*row[0];
sql_free_result();
bdb_unlock();
return true;
}
void CDetailManager::cache_Task (int gx, int gz, Slot* D)
{
int sx = cg2w_X (gx);
int sz = cg2w_Z (gz);
DetailSlot& DS = QueryDB (sx,sz);
D->empty = (DS.id0==DetailSlot::ID_Empty)&&
(DS.id1==DetailSlot::ID_Empty)&&
(DS.id2==DetailSlot::ID_Empty)&&
(DS.id3==DetailSlot::ID_Empty);
// Unpacking
u32 old_type = D->type;
D->type = stPending;
D->sx = sx;
D->sz = sz;
D->vis.box.min.set (sx*dm_slot_size, DS.r_ybase(), sz*dm_slot_size);
D->vis.box.max.set (D->vis.box.min.x+dm_slot_size, DS.r_ybase()+DS.r_yheight(), D->vis.box.min.z+dm_slot_size);
D->vis.box.grow (EPS_L);
for (u32 i=0; i<dm_obj_in_slot; i++) {
D->G[i].id = DS.r_id (i);
for (u32 clr=0; clr<D->G[i].items.size(); clr++)
poolSI.destroy(D->G[i].items[clr]);
D->G[i].items.clear ();
}
if (old_type != stPending)
{
VERIFY (stPending == D->type);
cache_task.push_back(D);
}
}
Chart objsearch_pi::StoreNewChart(wxString chart, double scale, int nativescale)
{
Chart ch;
if ( !m_bDBUsable )
return ch;
wxFileName chartname(chart);
ch.name = chartname.GetName();
ch.scale = scale;
ch.nativescale = nativescale;
m_chartLoading = chart;
QueryDB( m_db, wxString::Format(_T("INSERT INTO chart(chartname, scale, nativescale) VALUES ('%s', %f, %i)"), ch.name.c_str(), ch.scale, ch.nativescale) );
ch.id = m_db->GetLastRowId();
return ch;
}
/*
* Find the most recent successful real end time for a job given.
*
* RealEndTime is returned in etime
* Job name is returned in job (MAX_NAME_LENGTH)
*
* Returns: false on failure
* true on success, jr is unchanged, but etime and job are set
*/
bool BDB::bdb_find_last_job_end_time(JCR *jcr, JOB_DBR *jr, POOLMEM **etime,
char *job)
{
SQL_ROW row;
char ed1[50], ed2[50];
char esc_name[MAX_ESCAPE_NAME_LENGTH];
bdb_lock();
bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
pm_strcpy(etime, "0000-00-00 00:00:00"); /* default */
job[0] = 0;
Mmsg(cmd,
"SELECT RealEndTime, 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 RealEndTime DESC LIMIT 1", jr->JobType,
L_FULL, L_DIFFERENTIAL, L_INCREMENTAL, esc_name,
edit_int64(jr->ClientId, ed1), edit_int64(jr->FileSetId, ed2));
if (!QueryDB(jcr, cmd)) {
Mmsg2(&errmsg, _("Query error for end time request: ERR=%s\nCMD=%s\n"),
sql_strerror(), cmd);
goto bail_out;
}
if ((row = sql_fetch_row()) == NULL) {
sql_free_result();
Mmsg(errmsg, _("No prior backup Job record found.\n"));
goto bail_out;
}
Dmsg1(100, "Got end time: %s\n", row[0]);
pm_strcpy(etime, row[0]);
bstrncpy(job, row[1], MAX_NAME_LENGTH);
sql_free_result();
bdb_unlock();
return true;
bail_out:
bdb_unlock();
return false;
}
/*
* Find Available Media (Volume) for Pool
*
* Find a Volume for a given PoolId, MediaType, and Status.
*
* Returns: 0 on failure
* numrows on success
*/
int BDB::bdb_find_next_volume(JCR *jcr, int item, bool InChanger, MEDIA_DBR *mr)
{
SQL_ROW row = NULL;
int numrows;
const char *order;
char esc_type[MAX_ESCAPE_NAME_LENGTH];
char esc_status[MAX_ESCAPE_NAME_LENGTH];
char ed1[50];
bdb_lock();
bdb_escape_string(jcr, esc_type, mr->MediaType, strlen(mr->MediaType));
bdb_escape_string(jcr, esc_status, mr->VolStatus, strlen(mr->VolStatus));
if (item == -1) { /* find oldest volume */
/* Find oldest volume */
Mmsg(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,VolParts,LabelType,LabelDate,StorageId,"
"Enabled,LocationId,RecycleCount,InitialWrite,"
"ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
"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);
POOL_MEM voltype(PM_FNAME);
POOL_MEM exclude(PM_FNAME);
/* Find next available volume */
if (InChanger) {
Mmsg(changer, " AND InChanger=1 AND StorageId=%s ",
edit_int64(mr->StorageId, ed1));
}
/* Volumes will be automatically excluded from the query, we just take the
* first one of the list
*/
if (mr->exclude_list && *mr->exclude_list) {
item = 1;
Mmsg(exclude, " AND MediaId NOT IN (%s) ", mr->exclude_list);
}
if (strcmp(mr->VolStatus, "Recycle") == 0 ||
strcmp(mr->VolStatus, "Purged") == 0) {
order = "AND Recycle=1 ORDER BY LastWritten ASC,MediaId"; /* take oldest that can be recycled */
} else {
order = sql_media_order_most_recently_written[bdb_get_type_index()]; /* take most recently written */
}
Mmsg(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,VolParts,LabelType,LabelDate,StorageId,"
"Enabled,LocationId,RecycleCount,InitialWrite,"
"ScratchPoolId,RecyclePoolId,VolReadTime,VolWriteTime,ActionOnPurge "
"FROM Media WHERE PoolId=%s AND MediaType='%s' AND Enabled=1 "
"AND VolStatus='%s' "
"%s "
"%s "
"%s "
"%s LIMIT %d",
edit_int64(mr->PoolId, ed1), esc_type,
esc_status,
voltype.c_str(),
changer.c_str(), exclude.c_str(), order, item);
}
Dmsg1(100, "fnextvol=%s\n", cmd);
if (!QueryDB(jcr, cmd)) {
bdb_unlock();
return 0;
}
numrows = sql_num_rows();
if (item > numrows || item < 1) {
Dmsg2(050, "item=%d got=%d\n", item, numrows);
Mmsg2(&errmsg, _("Request for Volume item %d greater than max %d or less than 1\n"),
item, numrows);
bdb_unlock();
return 0;
}
/* Note, we previously seeked to the row using:
* sql_data_seek(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()) == NULL) {
Dmsg1(050, "Fail fetch item=%d\n", item+1);
Mmsg1(&errmsg, _("No Volume record found for item %d.\n"), item);
sql_free_result();
bdb_unlock();
return 0;
}
}
/* 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->VolType = str_to_int64(row[25]); /* formerly VolParts */
mr->LabelType = str_to_int64(row[26]);
bstrncpy(mr->cLabelDate, row[27]!=NULL?row[27]:"", sizeof(mr->cLabelDate));
mr->LabelDate = (time_t)str_to_utime(mr->cLabelDate);
mr->StorageId = str_to_int64(row[28]);
mr->Enabled = str_to_int64(row[29]);
mr->LocationId = str_to_int64(row[30]);
mr->RecycleCount = str_to_int64(row[31]);
bstrncpy(mr->cInitialWrite, row[32]!=NULL?row[32]:"", sizeof(mr->cInitialWrite));
mr->InitialWrite = (time_t)str_to_utime(mr->cInitialWrite);
mr->ScratchPoolId = str_to_int64(row[33]);
mr->RecyclePoolId = str_to_int64(row[34]);
mr->VolReadTime = str_to_int64(row[35]);
mr->VolWriteTime = str_to_int64(row[36]);
mr->ActionOnPurge = str_to_int64(row[37]);
sql_free_result();
bdb_unlock();
Dmsg1(050, "Rtn numrows=%d\n", numrows);
return numrows;
}
/*
* 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 BDB::bdb_find_last_jobid(JCR *jcr, const char *Name, JOB_DBR *jr)
{
SQL_ROW row;
char ed1[50];
char esc_name[MAX_ESCAPE_NAME_LENGTH];
bdb_lock();
/* Find last full */
Dmsg2(100, "JobLevel=%d JobType=%d\n", jr->JobLevel, jr->JobType);
if (jr->JobLevel == L_VERIFY_CATALOG) {
bdb_escape_string(jcr, esc_name, jr->Name, strlen(jr->Name));
Mmsg(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->JobLevel == L_VERIFY_DATA ||
jr->JobType == JT_BACKUP) {
if (Name) {
bdb_escape_string(jcr, esc_name, (char*)Name,
MIN(strlen(Name), sizeof(esc_name)));
Mmsg(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(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(&errmsg, _("Unknown Job level=%d\n"), jr->JobLevel);
bdb_unlock();
return false;
}
Dmsg1(100, "Query: %s\n", cmd);
if (!QueryDB(jcr, cmd)) {
bdb_unlock();
return false;
}
if ((row = sql_fetch_row()) == NULL) {
Mmsg1(&errmsg, _("No Job found for: %s.\n"), cmd);
sql_free_result();
bdb_unlock();
return false;
}
jr->JobId = str_to_int64(row[0]);
sql_free_result();
Dmsg1(100, "db_get_last_jobid: got JobId=%d\n", jr->JobId);
if (jr->JobId <= 0) {
Mmsg1(&errmsg, _("No Job found for: %s\n"), cmd);
bdb_unlock();
return false;
}
bdb_unlock();
return true;
}
/*
* 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: false on failure
* true on success, jr is unchanged, but stime and job are set
*/
bool BDB::bdb_find_job_start_time(JCR *jcr, JOB_DBR *jr, POOLMEM **stime, char *job)
{
SQL_ROW row;
char ed1[50], ed2[50];
char esc_name[MAX_ESCAPE_NAME_LENGTH];
bdb_lock();
bdb_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(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 (!QueryDB(jcr, cmd)) {
Mmsg2(&errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
sql_strerror(), cmd);
goto bail_out;
}
if ((row = sql_fetch_row()) == NULL) {
sql_free_result();
Mmsg(errmsg, _("No prior Full backup Job record found.\n"));
goto bail_out;
}
sql_free_result();
/* Now edit SQL command for Incremental Job */
Mmsg(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(errmsg, _("Unknown level=%d\n"), jr->JobLevel);
goto bail_out;
}
} else {
Dmsg1(100, "Submitting: %s\n", cmd);
Mmsg(cmd, "SELECT StartTime, Job FROM Job WHERE Job.JobId=%s",
edit_int64(jr->JobId, ed1));
}
if (!QueryDB(jcr, cmd)) {
pm_strcpy(stime, ""); /* set EOS */
Mmsg2(&errmsg, _("Query error for start time request: ERR=%s\nCMD=%s\n"),
sql_strerror(), cmd);
goto bail_out;
}
if ((row = sql_fetch_row()) == NULL) {
Mmsg2(&errmsg, _("No Job record found: ERR=%s\nCMD=%s\n"),
sql_strerror(), cmd);
sql_free_result();
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();
bdb_unlock();
return true;
bail_out:
bdb_unlock();
return false;
}
