public bool InitDatabase(Utils.DBCon dbcon)
{
/*
* database:
* areainfo -> id, parentid, level, name, minlat, minlon, maxlat, maxlon
* poly -> id, areainfoid, position, lat, lon
*/
bool result = false;
try
{
object o = dbcon.ExecuteScalar("SELECT name FROM sqlite_master WHERE type='table' AND name='areainfo'");
if (o == null || o.GetType() == typeof(DBNull))
{
dbcon.ExecuteNonQuery("create table 'areainfo' (id integer, parentid integer, level integer, name text, minlat real, minlon real, maxlat real, maxlon real)");
}
o = dbcon.ExecuteScalar("SELECT name FROM sqlite_master WHERE type='table' AND name='poly'");
if (o == null || o.GetType() == typeof(DBNull))
{
dbcon.ExecuteNonQuery("create table 'poly' (id integer, areainfoid integer, position integer, lat real, lon real)");
dbcon.ExecuteNonQuery("create index idx_poly on poly (areainfoid)");
}
result = true;
}
catch
{
}
return(result);
}
public void Initialize(Framework.Interfaces.ICore core)
{
_core = core;
_availableWaypoints = new Hashtable();
try
{
_dbcon = new Utils.DBConComSqlite(Path.Combine(core.PluginDataPath,"gcvote.db3"));
object o = _dbcon.ExecuteScalar("SELECT name FROM sqlite_master WHERE type='table' AND name='votes'");
if (o == null || o.GetType() == typeof(DBNull))
{
_dbcon.ExecuteNonQuery("create table 'votes' (Waypoint text, VoteMedian float, VoteAvg float, VoteCnt integer, VoteUser double)");
_dbcon.ExecuteNonQuery("create unique index idx_votess on votes (Waypoint)");
}
DbDataReader dr = _dbcon.ExecuteReader("select Waypoint from votes");
while (dr.Read())
{
_availableWaypoints.Add(dr[0], true);
}
}
catch
{
_dbcon = null;
_availableWaypoints.Clear();
}
}
public void Initialize(Framework.Interfaces.ICore core)
{
_core = core;
_availableWaypoints = new Hashtable();
try
{
_dbcon = new Utils.DBConComSqlite(Path.Combine(core.PluginDataPath, "gcvote.db3"));
object o = _dbcon.ExecuteScalar("SELECT name FROM sqlite_master WHERE type='table' AND name='votes'");
if (o == null || o.GetType() == typeof(DBNull))
{
_dbcon.ExecuteNonQuery("create table 'votes' (Waypoint text, VoteMedian float, VoteAvg float, VoteCnt integer, VoteUser double)");
_dbcon.ExecuteNonQuery("create unique index idx_votess on votes (Waypoint)");
}
DbDataReader dr = _dbcon.ExecuteReader("select Waypoint from votes");
while (dr.Read())
{
_availableWaypoints.Add(dr[0], true);
}
}
catch
{
_dbcon = null;
_availableWaypoints.Clear();
}
}
public void Initialize(Framework.Interfaces.ICore core)
{
if (_bookmarks == null)
{
_bookmarks = new Hashtable();
try
{
_dbcon = new Utils.DBConComSqlite(Path.Combine(core.PluginDataPath, "gccollections.db3"));
object o = _dbcon.ExecuteScalar("SELECT name FROM sqlite_master WHERE type='table' AND name='bookmark'");
if (o == null || o.GetType() == typeof(DBNull))
{
_dbcon.ExecuteNonQuery("create table 'bookmark' (ID integer primary key autoincrement, Name text)");
_dbcon.ExecuteNonQuery("create table 'codes' (BookmarkID integer, Code text)");
}
DbDataReader dr = _dbcon.ExecuteReader("select ID, Name from bookmark");
while (dr.Read())
{
BookmarkInfo bmi = new BookmarkInfo();
bmi.ID = (int)dr["ID"];
bmi.Name = (string)dr["Name"];
_bookmarks.Add(bmi.Name.ToLower(), bmi);
}
dr = _dbcon.ExecuteReader("select BookmarkID, Code from codes");
while (dr.Read())
{
int id = (int)dr["BookmarkID"];
BookmarkInfo bmi = (from BookmarkInfo b in _bookmarks.Values where b.ID==id select b).FirstOrDefault();
if (bmi != null)
{
bmi.GeocacheCodes.Add(dr["Code"], true);
}
}
}
catch
{
}
}
}
private Utils.DBCon initDatabase()
{
if (_dbcon == null)
{
try
{
_dbcon = new Utils.DBConComSqlite(_databaseFile);
object o = _dbcon.ExecuteScalar("SELECT name FROM sqlite_master WHERE type='table' AND name='attachements'");
if (o == null || o.GetType() == typeof(DBNull))
{
_dbcon.ExecuteNonQuery("create table 'attachements' (code text, filepath text, comment text)");
_dbcon.ExecuteNonQuery("create unique index idx_attachements on attachements (code)");
}
}
catch
{
_dbcon = null;
}
}
return _dbcon;
}
public SqliteSettingsStorage()
{
_availableKeys = new Hashtable();
_ignoredGeocacheCodes = new Hashtable();
_ignoredGeocacheNames = new Hashtable();
_ignoredGeocacheOwners = new Hashtable();
try
{
string sf = Properties.Settings.Default.SettingsFolder;
if (string.IsNullOrEmpty(sf))
{
sf = Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData), "GAPPSF");
}
if (!Directory.Exists(sf))
{
Directory.CreateDirectory(sf);
}
Properties.Settings.Default.SettingsFolder = sf;
Properties.Settings.Default.Save();
sf = Path.Combine(sf, "settings.db3");
_dbcon = new Utils.DBConComSqlite(sf);
if (!_dbcon.TableExists("settings"))
{
_dbcon.ExecuteNonQuery("create table 'settings' (item_name text, item_value text)");
_dbcon.ExecuteNonQuery("create index idx_key on settings (item_name)");
}
else
{
DbDataReader dr = _dbcon.ExecuteReader("select item_name, item_value from settings");
while (dr.Read())
{
_availableKeys[dr[0] as string] = dr[1] as string;
}
}
if (!_dbcon.TableExists("ignoregc"))
{
_dbcon.ExecuteNonQuery("create table 'ignoregc' (item_name text, item_value text)");
}
else
{
DbDataReader dr = _dbcon.ExecuteReader("select item_name, item_value from ignoregc");
while (dr.Read())
{
string item = dr[0] as string;
if (item == "code")
{
_ignoredGeocacheCodes[dr[1] as string] = true;
}
else if (item == "name")
{
_ignoredGeocacheNames[dr[1] as string] = true;
}
else if (item == "owner")
{
_ignoredGeocacheOwners[dr[1] as string] = true;
}
}
}
if (!_dbcon.TableExists("gccombm"))
{
_dbcon.ExecuteNonQuery("create table 'gccombm' (bm_id text, bm_name text, bmguid text)");
_dbcon.ExecuteNonQuery("create index idx_bmid on gccombm (bm_id)");
}
if (!_dbcon.TableExists("gccomgc"))
{
_dbcon.ExecuteNonQuery("create table 'gccomgc' (bm_id text, gccode text)");
_dbcon.ExecuteNonQuery("create index idx_bmgcid on gccomgc (bm_id)");
}
if (!_dbcon.TableExists("attachm"))
{
_dbcon.ExecuteNonQuery("create table 'attachm' (gccode text, filename text, comments text)");
_dbcon.ExecuteNonQuery("create index idx_att on attachm (gccode)");
}
if (!_dbcon.TableExists("formulasolv"))
{
_dbcon.ExecuteNonQuery("create table 'formulasolv' (gccode text, formula text)");
_dbcon.ExecuteNonQuery("create index idx_form on formulasolv (gccode)");
}
if (!_dbcon.TableExists("gcnotes"))
{
_dbcon.ExecuteNonQuery("create table 'gcnotes' (gccode text, notes text)");
_dbcon.ExecuteNonQuery("create index idx_note on gcnotes (gccode)");
}
if (!_dbcon.TableExists("gccollection"))
{
_dbcon.ExecuteNonQuery("create table 'gccollection' (col_id integer primary key autoincrement, name text)");
//_dbcon.ExecuteNonQuery("create index idx_col on gccollection (name)");
}
if (!_dbcon.TableExists("gcincol"))
{
_dbcon.ExecuteNonQuery("create table 'gcincol' (col_id integer, gccode text)");
_dbcon.ExecuteNonQuery("create index idx_gccol on gcincol (col_id)");
}
if (!_dbcon.TableExists("gcdist"))
{
_dbcon.ExecuteNonQuery("create table 'gcdist' (gccode text, dist float)");
_dbcon.ExecuteNonQuery("create index idx_dist on gcdist (gccode)");
}
if (!_dbcon.TableExists("gcvotes"))
{
_dbcon.ExecuteNonQuery("create table 'gcvotes' (gccode text, VoteMedian float, VoteAvg float, VoteCnt integer, VoteUser float)");
_dbcon.ExecuteNonQuery("create unique index idx_gcvotes on gcvotes (gccode)");
}
object o = _dbcon.ExecuteScalar("PRAGMA integrity_check");
if (o as string == "ok")
{
//what is expected
}
else
{
//oeps?
_dbcon.Dispose();
_dbcon = null;
}
}
catch//(Exception e)
{
//Core.ApplicationData.Instance.Logger.AddLog(this, e);
_dbcon = null;
}
}
public SqliteSettingsStorage()
{
_availableKeys = new Hashtable();
_ignoredGeocacheCodes = new Hashtable();
_ignoredGeocacheNames = new Hashtable();
_ignoredGeocacheOwners = new Hashtable();
try
{
string sf = Properties.Settings.Default.SettingsFolder;
if (string.IsNullOrEmpty(sf))
{
sf = Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData), "GAPPSF");
}
if (!Directory.Exists(sf))
{
Directory.CreateDirectory(sf);
}
Properties.Settings.Default.SettingsFolder = sf;
Properties.Settings.Default.Save();
sf = Path.Combine(sf, "settings.db3");
//if (!DesignerProperties.GetIsInDesignMode(new DependencyObject()))
{
_dbcon = new Utils.DBConComSqlite(sf);
if (!_dbcon.TableExists("settings"))
{
_dbcon.ExecuteNonQuery("create table 'settings' (item_name text, item_value text)");
_dbcon.ExecuteNonQuery("create index idx_key on settings (item_name)");
}
else
{
DbDataReader dr = _dbcon.ExecuteReader("select item_name, item_value from settings");
while (dr.Read())
{
_availableKeys[dr[0] as string] = dr[1] as string;
}
}
if (!_dbcon.TableExists("ignoregc"))
{
_dbcon.ExecuteNonQuery("create table 'ignoregc' (item_name text, item_value text)");
}
else
{
DbDataReader dr = _dbcon.ExecuteReader("select item_name, item_value from ignoregc");
while (dr.Read())
{
string item = dr[0] as string;
if (item == "code")
{
_ignoredGeocacheCodes[dr[1] as string] = true;
}
else if (item == "name")
{
_ignoredGeocacheNames[dr[1] as string] = true;
}
else if (item == "owner")
{
_ignoredGeocacheOwners[dr[1] as string] = true;
}
}
}
if (!_dbcon.TableExists("gccombm"))
{
_dbcon.ExecuteNonQuery("create table 'gccombm' (bm_id text, bm_name text, bmguid text)");
_dbcon.ExecuteNonQuery("create index idx_bmid on gccombm (bm_id)");
}
if (!_dbcon.TableExists("gccomgc"))
{
_dbcon.ExecuteNonQuery("create table 'gccomgc' (bm_id text, gccode text)");
_dbcon.ExecuteNonQuery("create index idx_bmgcid on gccomgc (bm_id)");
}
if (!_dbcon.TableExists("attachm"))
{
_dbcon.ExecuteNonQuery("create table 'attachm' (gccode text, filename text, comments text)");
_dbcon.ExecuteNonQuery("create index idx_att on attachm (gccode)");
}
if (!_dbcon.TableExists("formulasolv"))
{
_dbcon.ExecuteNonQuery("create table 'formulasolv' (gccode text, formula text)");
_dbcon.ExecuteNonQuery("create index idx_form on formulasolv (gccode)");
}
if (!_dbcon.TableExists("gcnotes"))
{
_dbcon.ExecuteNonQuery("create table 'gcnotes' (gccode text, notes text)");
_dbcon.ExecuteNonQuery("create index idx_note on gcnotes (gccode)");
}
if (!_dbcon.TableExists("gccollection"))
{
_dbcon.ExecuteNonQuery("create table 'gccollection' (col_id integer primary key autoincrement, name text)");
//_dbcon.ExecuteNonQuery("create index idx_col on gccollection (name)");
}
if (!_dbcon.TableExists("gcincol"))
{
_dbcon.ExecuteNonQuery("create table 'gcincol' (col_id integer, gccode text)");
_dbcon.ExecuteNonQuery("create index idx_gccol on gcincol (col_id)");
}
if (!_dbcon.TableExists("gcdist"))
{
_dbcon.ExecuteNonQuery("create table 'gcdist' (gccode text, dist float)");
_dbcon.ExecuteNonQuery("create index idx_dist on gcdist (gccode)");
}
if (!_dbcon.TableExists("gcvotes"))
{
_dbcon.ExecuteNonQuery("create table 'gcvotes' (gccode text, VoteMedian float, VoteAvg float, VoteCnt integer, VoteUser float)");
_dbcon.ExecuteNonQuery("create unique index idx_gcvotes on gcvotes (gccode)");
}
if (!_dbcon.TableExists("trkgroups"))
{
_dbcon.ExecuteNonQuery("create table 'trkgroups' (id integer, name text)");
}
if (!_dbcon.TableExists("trkimages"))
{
_dbcon.ExecuteNonQuery("create table 'trkimages' (url text, imagedata blob)");
}
if (!_dbcon.TableExists("trktrackables"))
{
_dbcon.ExecuteNonQuery("create table 'trktrackables' (groupid integer, AllowedToBeCollected integer, Archived integer, BugTypeID integer, Code text, CurrentGeocacheCode text, CurrentGoal text, DateCreated text, Description text, IconUrl text, Id integer, InCollection integer, Name text, TBTypeName text, Url text, WptTypeID integer, Owner text, HopCount integer, DiscoverCount integer, InCacheCount integer, DistanceKm real, Lat real, Lon real)");
_dbcon.ExecuteNonQuery("create index idx_trackablesgroup on trktrackables (groupid)");
_dbcon.ExecuteNonQuery("create index idx_trackablescode on trktrackables (code)");
}
if (!_dbcon.TableExists("trktravels"))
{
_dbcon.ExecuteNonQuery("create table 'trktravels' (pos integer, TrackableCode text, GeocacheCode text, lat real, lon real, DateLogged text)");
_dbcon.ExecuteNonQuery("create index idx_travels on trktravels (TrackableCode)");
}
if (!_dbcon.TableExists("trklogs"))
{
_dbcon.ExecuteNonQuery("create table 'trklogs' (TrackableCode text, ID integer, LogCode text, GeocacheCode text, IsArchived integer, LoggedBy text, LogGuid text, LogIsEncoded integer, LogText text, WptLogTypeId integer, Url text, UTCCreateDate text, VisitDate text)");
_dbcon.ExecuteNonQuery("create index idx_logstb on trklogs (TrackableCode)");
_dbcon.ExecuteNonQuery("create index idx_logsid on trklogs (ID)");
}
object o = _dbcon.ExecuteScalar("PRAGMA integrity_check");
if (o as string == "ok")
{
//what is expected
}
else
{
//oeps?
_dbcon.Dispose();
_dbcon = null;
}
}
}
catch//(Exception e)
{
//Core.ApplicationData.Instance.Logger.AddLog(this, e);
_dbcon = null;
}
}