public string getLastId()
{
string connection = "URI=file:" + Application.dataPath + "/StreamingAssets/User.db";
Debug.Log(connection);
IDbConnection dbcon = new Mono.Data.SqliteClient.SqliteConnection(connection);
dbcon.Open();
IDbCommand cmnd_read = dbcon.CreateCommand();
//IDataRecord record;
string Name = "'" + newName.text.ToString() + "'";
string query = "Select * from my_user order by id DESC Limit 1";
cmnd_read.CommandText = query;
IDataReader reader;
reader = cmnd_read.ExecuteReader();
string id = "null";
while (reader.Read())
{
id = reader[0].ToString();
}
dbcon.Close();
dbcon = null;
return(id);
}
public static bool VacuumDb(string file)
{
bool ret = true;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", file);
#endif
cn.Open();
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "vacuum;";
cmd.ExecuteNonQuery();
}
cn.Close();
}
}
}
catch (Exception ex)
{
Debug.WriteLine("VacuumDb: " + ex.ToString());
ret = false;
}
return(ret);
}
public void playAfterAddUSER()
{
Debug.Log(SceneName);
Debug.Log(newName.text);
string connection = "URI=file:" + Application.dataPath + "/StreamingAssets/User.db";
Debug.Log(connection);
IDbConnection dbcon = new Mono.Data.SqliteClient.SqliteConnection(connection);
dbcon.Open();
IDbCommand cmnd_read = dbcon.CreateCommand();
//IDataRecord record;
string Name = "'" + newName.text.ToString() + "'";
string query = "INSERT INTO my_user(Nama, MaxSC, MaxCF) VALUES(" + Name + ", '0', '0')";
cmnd_read.CommandText = query;
Debug.Log(cmnd_read.CommandText);
cmnd_read.ExecuteNonQuery();
dbcon.Close();
dbcon = null;
PlayerPrefs.SetString("ID", getLastId());
PlayerPrefs.SetString("scene", SceneName);
Debug.Log(getLastId());
SceneManager.LoadScene(SceneName);
}
int PureImageCache.DeleteOlderThan(DateTime date, int?type)
{
int affectedRows = 0;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format("DELETE FROM Tiles WHERE CacheTime is not NULL and CacheTime < datetime('{0}')", date.ToString("s"));
if (type.HasValue)
{
com.CommandText += " and Type = " + type;
}
affectedRows = com.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("DeleteOlderThan: " + ex);
#endif
Debug.WriteLine("DeleteOlderThan: " + ex);
}
return(affectedRows);
}
public int DeleteOlderThan(DateTime date, MapType type)
{
int affectedRows = 0;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format("DELETE FROM Tiles WHERE CacheTime is not NULL and CacheTime < datetime('{0}') AND Type={1}", date.ToString("s"), (int)type);
affectedRows = com.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("DeleteOlderThan: " + ex.ToString());
#endif
Debug.WriteLine("DeleteOlderThan: " + ex.ToString());
}
return(affectedRows);
}
string getMaxScoreCF(string id)
{
string query = "SELECT max(score) FROM logCF WHERE idUser = "******" ";
string connection = "URI=file:" + Application.dataPath + "/StreamingAssets/User.db";
Debug.Log(connection);
IDbConnection dbcon = new Mono.Data.SqliteClient.SqliteConnection(connection);
dbcon.Open();
IDbCommand cmnd_read = dbcon.CreateCommand();
IDataReader reader;
cmnd_read.CommandText = query;
Debug.Log(cmnd_read.CommandText);
reader = cmnd_read.ExecuteReader();
string maxScore = "";
while (reader.Read())
{
maxScore = reader[0].ToString();
}
dbcon.Close();
dbcon = null;
return(maxScore);
}
public static bool CreateEmptyDB(string file)
{
bool ret = true;
try
{
string dir = Path.GetDirectoryName(file);
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
#if !PocketPC
cmd.CommandText = Properties.Resources.CreateTileDb;
#else
cmd.CommandText = GMap.NET.WindowsMobile.Properties.Resources.CreateTileDb;
#endif
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch
{
#if MONO
Console.WriteLine("CreateEmptyDB: " + exx.ToString());
#endif
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch
{
#if MONO
Console.WriteLine("CreateEmptyDB: " + ex.ToString());
#endif
ret = false;
}
return(ret);
}
public static bool CreateEmptyDB(string file)
{
bool ret = true;
try
{
string dir = Path.GetDirectoryName(file);
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
//cmd.CommandText = Properties.Resources.IpCacheCreateDb;
cmd.CommandText = "CREATE TABLE [Cache] ([Ip] varchar(15) PRIMARY KEY NOT NULL,[CountryName] text NOT NULL,[RegionName] text NOT NULL, [City] text NOT NULL, [Latitude] float NOT NULL, [Longitude] float NOT NULL, [Time] datetime NOT NULL );";
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception exx)
{
Console.WriteLine("CreateEmptyDB: " + exx.ToString());
Debug.WriteLine("CreateEmptyDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("CreateEmptyDB: " + ex.ToString());
#endif
Debug.WriteLine("CreateEmptyDB: " + ex.ToString());
ret = false;
}
return(ret);
}
public static bool CreateEmptyDB(string file)
{
bool ret = true;
try
{
string dir = Path.GetDirectoryName(file);
if(!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False", file);
#endif
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = Properties.Resources.IpCacheCreateDb;
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception exx)
{
Console.WriteLine("CreateEmptyDB: " + exx.ToString());
Debug.WriteLine("CreateEmptyDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("CreateEmptyDB: " + ex.ToString());
#endif
Debug.WriteLine("CreateEmptyDB: " + ex.ToString());
ret = false;
}
return ret;
}
PureImage PureImageCache.GetImageFromCache(MapType type, Point pos, int zoom)
{
PureImage ret = null;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format(sqlSelect, pos.X, pos.Y, zoom, (int)type);
using (DbDataReader rd = com.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (rd.Read())
{
long length = rd.GetBytes(0, 0, null, 0, 0);
byte[] tile = new byte[length];
rd.GetBytes(0, 0, tile, 0, tile.Length);
{
if (GMaps.Instance.ImageProxy != null)
{
MemoryStream stm = new MemoryStream(tile, 0, tile.Length, false, true);
ret = GMaps.Instance.ImageProxy.FromStream(stm);
if (ret != null)
{
ret.Data = stm;
}
}
}
tile = null;
}
rd.Close();
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("GetImageFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetImageFromCache: " + ex.ToString());
ret = null;
}
return(ret);
}
public static bool PreAllocateDB(string file, int addSizeInMBytes)
{
bool ret = true;
try
{
Debug.WriteLine("PreAllocateDB: " + file + ", +" + addSizeInMBytes + "MB");
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = string.Format("create table large (a); insert into large values (zeroblob({0})); drop table large;", addSizeInMBytes * 1024 * 1024);
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception exx)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + exx.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + ex.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + ex.ToString());
ret = false;
}
return(ret);
}
public IpInfo GetDataFromCache(string ip)
{
IpInfo ret = null;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},Default Timeout=33", db);
#endif
cn.Open();
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = "SELECT * FROM Cache WHERE Ip = '" + ip + "'";
using (DbDataReader rd = com.ExecuteReader())
{
if (rd.Read())
{
IpInfo val = new IpInfo();
{
val.Ip = ip;
val.CountryName = rd["CountryName"] as string;
val.RegionName = rd["RegionName"] as string;
val.City = rd["City"] as string;
val.Latitude = (double)rd["Latitude"];
val.Longitude = (double)rd["Longitude"];
val.CacheTime = (DateTime)rd["Time"];
}
ret = val;
}
rd.Close();
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("GetDataFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetDataFromCache: " + ex.ToString());
ret = null;
}
return(ret);
}
public PureImage GetImageFromCache(int type, GPoint pos, int zoom)
{
if (string.IsNullOrEmpty(ConnectionString))
{
return(null);
}
PureImage ret = null;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format(finnalSqlSelect, pos.X, pos.Y, zoom);
using (DbDataReader rd = com.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (rd.Read())
{
long length = rd.GetBytes(0, 0, null, 0, 0);
byte[] tile = new byte[length];
rd.GetBytes(0, 0, tile, 0, tile.Length);
{
if (GMapProvider.TileImageProxy != null)
{
ret = GMapProvider.TileImageProxy.FromArray(tile);
}
}
tile = null;
}
rd.Close();
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("GetImageFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetImageFromCache: " + ex.ToString());
ret = null;
}
return(ret);
}
void updateHighScoreCF(string id, string maxScore)
{
string query = "update my_user set MaxCF = " + maxScore + " where Id = " + id + " ";
string connection = "URI=file:" + Application.dataPath + "/StreamingAssets/User.db";
Debug.Log(connection);
IDbConnection dbcon = new Mono.Data.SqliteClient.SqliteConnection(connection);
dbcon.Open();
IDbCommand cmnd_read = dbcon.CreateCommand();
cmnd_read.CommandText = query;
Debug.Log(cmnd_read.CommandText);
cmnd_read.ExecuteNonQuery();
dbcon.Close();
dbcon = null;
}
public void deleteUSERdata(string id)
{
string connection = "URI=file:" + Application.dataPath + "/StreamingAssets/User.db";
Debug.Log(connection);
IDbConnection dbcon = new Mono.Data.SqliteClient.SqliteConnection(connection);
dbcon.Open();
IDbCommand cmnd_read = dbcon.CreateCommand();
//IDataRecord record;
string query = "Delete from my_user where id = '" + id + "'";
cmnd_read.CommandText = query;
Debug.Log(cmnd_read.CommandText);
cmnd_read.ExecuteNonQuery();
dbcon.Close();
dbcon = null;
}
public void UpdateDataUser(string ID, string sceneName, int score, int jumlah_tangkapan)
{
string query;
if (sceneName == "Colorfull")
{
query = "insert into logCF(idUser, Score, Tanggal, BerhasilPindah) values(" + ID.ToString() + ", " + score.ToString() + ", DATETIME('NOW')," + jumlah_tangkapan.ToString() + ")";
}
else
{
query = "insert into logSC(idUser, Score, Tanggal, BerhasilPindah) values(" + ID.ToString() + ", " + score.ToString() + ", DATETIME('NOW')," + jumlah_tangkapan.ToString() + ")";
}
string connection = "URI=file:" + Application.dataPath + "/StreamingAssets/User.db";
Debug.Log(connection);
IDbConnection dbcon = new Mono.Data.SqliteClient.SqliteConnection(connection);
dbcon.Open();
IDbCommand cmnd_read = dbcon.CreateCommand();
cmnd_read.CommandText = query;
Debug.Log(cmnd_read.CommandText);
cmnd_read.ExecuteNonQuery();
dbcon.Close();
dbcon = null;
if (sceneName == "Colorfull")
{
string score_max = getMaxScoreCF(ID);
updateHighScoreCF(ID, score_max);
}
else
{
string score_max = getMaxScoreSC(ID);
updateHighScoreSC(ID, score_max);
}
}
bool PureImageCache.PutImageToCache(byte[] tile, int type, GPoint pos, int zoom)
{
bool ret = true;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsert;
cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", type));
cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now));
cmd.ExecuteNonQuery();
}
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsertLast;
cmd.Parameters.Add(new SQLiteParameter("@p1", tile));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
ret = false;
}
return(ret);
}
public bool PutDataToCache(string ip, IpInfo data)
{
bool ret = true;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Default Timeout=33", db);
#endif
cn.Open();
{
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO Cache(Ip, CountryName, RegionName, City, Latitude, Longitude, Time) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7)";
cmd.Parameters.Add(new SQLiteParameter("@p1", ip));
cmd.Parameters.Add(new SQLiteParameter("@p2", data.CountryName));
cmd.Parameters.Add(new SQLiteParameter("@p3", data.RegionName));
cmd.Parameters.Add(new SQLiteParameter("@p4", data.City));
cmd.Parameters.Add(new SQLiteParameter("@p5", data.Latitude));
cmd.Parameters.Add(new SQLiteParameter("@p6", data.Longitude));
cmd.Parameters.Add(new SQLiteParameter("@p7", data.CacheTime));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception ex)
{
Console.WriteLine("PutDataToCache: " + ex.ToString());
Debug.WriteLine("PutDataToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
}
cn.Close();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutDataToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutDataToCache: " + ex.ToString());
ret = false;
}
return ret;
}
private static bool AlterDBAddTimeColumn(string file)
{
bool ret = true;
try
{
if(File.Exists(file))
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768;Pooling=True", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768,Pooling=True", file);
#endif
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
bool? NoCacheTimeColumn = null;
try
{
using(DbCommand cmd = new SQLiteCommand("SELECT CacheTime FROM Tiles", cn))
{
cmd.Transaction = tr;
using(DbDataReader rd = cmd.ExecuteReader())
{
rd.Close();
}
NoCacheTimeColumn = false;
}
}
catch(Exception ex)
{
if(ex.Message.Contains("no such column: CacheTime"))
{
NoCacheTimeColumn = true;
}
else
{
throw ex;
}
}
try
{
if(NoCacheTimeColumn.HasValue && NoCacheTimeColumn.Value)
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "ALTER TABLE Tiles ADD CacheTime DATETIME";
cmd.ExecuteNonQuery();
}
tr.Commit();
NoCacheTimeColumn = false;
}
}
catch(Exception exx)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
else
{
ret = false;
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
ret = false;
}
return ret;
}
PureImage PureImageCache.GetImageFromCache(MapType type, GPoint pos, int zoom)
{
PureImage ret = null;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
if(!string.IsNullOrEmpty(attachSqlQuery))
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = attachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Attach: " + x);
}
}
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format(finnalSqlSelect, pos.X, pos.Y, zoom, (int)type);
using(DbDataReader rd = com.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if(rd.Read())
{
long length = rd.GetBytes(0, 0, null, 0, 0);
byte[] tile = new byte[length];
rd.GetBytes(0, 0, tile, 0, tile.Length);
{
if(GMaps.Instance.ImageProxy != null)
{
MemoryStream stm = new MemoryStream(tile, 0, tile.Length, false, true);
ret = GMaps.Instance.ImageProxy.FromStream(stm);
if(ret != null)
{
ret.Data = stm;
}
}
}
tile = null;
}
rd.Close();
}
}
if(!string.IsNullOrEmpty(detachSqlQuery))
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = detachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Detach: " + x);
}
}
}
cn.Close();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("GetImageFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetImageFromCache: " + ex.ToString());
ret = null;
}
return ret;
}
public static bool VacuumDb(string file)
{
bool ret = true;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;Page Size=32768;Pooling=True", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768,Pooling=True", file);
#endif
cn.Open();
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "vacuum;";
cmd.ExecuteNonQuery();
}
cn.Close();
}
}
}
catch(Exception ex)
{
Debug.WriteLine("VacuumDb: " + ex.ToString());
ret = false;
}
return ret;
}
bool PureImageCache.PutImageToCache(byte[] tile, int type, GPoint pos, int zoom)
{
bool ret = true;
if(Created)
{
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsert;
cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", type));
cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now));
cmd.ExecuteNonQuery();
}
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsertLast;
cmd.Parameters.Add(new SQLiteParameter("@p1", tile));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
cn.Close();
}
if(Interlocked.Increment(ref preAllocationPing) % 22 == 0)
{
CheckPreAllocation();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
ret = false;
}
}
return ret;
}
private static bool AlterDBAddTimeColumn(string file)
{
bool ret = true;
try
{
if (File.Exists(file))
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768;Pooling=True", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768,Pooling=True", file);
#endif
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
bool?NoCacheTimeColumn = null;
try
{
using (DbCommand cmd = new SQLiteCommand("SELECT CacheTime FROM Tiles", cn))
{
cmd.Transaction = tr;
using (DbDataReader rd = cmd.ExecuteReader())
{
rd.Close();
}
NoCacheTimeColumn = false;
}
}
catch (Exception ex)
{
if (ex.Message.Contains("no such column: CacheTime"))
{
NoCacheTimeColumn = true;
}
else
{
throw ex;
}
}
try
{
if (NoCacheTimeColumn.HasValue && NoCacheTimeColumn.Value)
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "ALTER TABLE Tiles ADD CacheTime DATETIME";
cmd.ExecuteNonQuery();
}
tr.Commit();
NoCacheTimeColumn = false;
}
}
catch (Exception exx)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
else
{
ret = false;
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
#endif
Debug.WriteLine("AlterDBAddTimeColumn: " + ex.ToString());
ret = false;
}
return(ret);
}
bool PureImageCache.PutImageToCache(MemoryStream tile, MapType type, Point pos, int zoom)
{
bool ret = true;
if (Created)
{
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) VALUES(@p1, @p2, @p3, @p4, @p5)";
cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", (int)type));
cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now));
cmd.ExecuteNonQuery();
}
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO TilesData(id, Tile) VALUES((SELECT last_insert_rowid()), @p1)";
cmd.Parameters.Add(new SQLiteParameter("@p1", tile.GetBuffer()));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
ret = false;
}
}
return(ret);
}
PureImage PureImageCache.GetImageFromCache(int type, GPoint pos, int zoom)
{
PureImage ret = null;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
if (!string.IsNullOrEmpty(attachSqlQuery))
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = attachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Attach: " + x);
}
}
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format(finnalSqlSelect, pos.X, pos.Y, zoom, type);
using (DbDataReader rd = com.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if (rd.Read())
{
long length = rd.GetBytes(0, 0, null, 0, 0);
byte[] tile = new byte[length];
rd.GetBytes(0, 0, tile, 0, tile.Length);
{
if (GMapProvider.TileImageProxy != null)
{
MemoryStream stm = new MemoryStream(tile, 0, tile.Length, false, true);
ret = GMapProvider.TileImageProxy.FromStream(stm);
if (ret != null)
{
ret.Data = stm;
}
}
}
tile = null;
}
rd.Close();
}
}
if (!string.IsNullOrEmpty(detachSqlQuery))
{
using (DbCommand com = cn.CreateCommand())
{
com.CommandText = detachSqlQuery;
int x = com.ExecuteNonQuery();
//Debug.WriteLine("Detach: " + x);
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("GetImageFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetImageFromCache: " + ex.ToString());
ret = null;
}
return(ret);
}
bool PureImageCache.PutImageToCache(MemoryStream tile, MapType type, Point pos, int zoom)
{
bool ret = true;
if(Created)
{
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) VALUES(@p1, @p2, @p3, @p4, @p5)";
cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", (int) type));
cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now));
cmd.ExecuteNonQuery();
}
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO TilesData(id, Tile) VALUES((SELECT last_insert_rowid()), @p1)";
cmd.Parameters.Add(new SQLiteParameter("@p1", tile.GetBuffer()));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
cn.Close();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
ret = false;
}
}
return ret;
}
/// <summary>
/// gets routes from gpsd log file
/// </summary>
/// <param name="gpsdLogFile"></param>
/// <param name="start">start time(UTC) of route, null to read from very start</param>
/// <param name="end">end time(UTC) of route, null to read to the very end</param>
/// <param name="maxPositionDilutionOfPrecision">max value of PositionDilutionOfPrecision, null to get all</param>
/// <returns></returns>
public static IEnumerable<List<GpsLog>> GetRoutesFromMobileLog(string gpsdLogFile, DateTime? start, DateTime? end, double? maxPositionDilutionOfPrecision)
{
#if SQLite
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;", gpsdLogFile);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True", gpsdLogFile);
#endif
cn.Open();
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM GPS ";
int initLenght = cmd.CommandText.Length;
if(start.HasValue)
{
cmd.CommandText += "WHERE TimeUTC >= @t1 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t1", start);
cmd.Parameters.Add(lookupValue);
}
if(end.HasValue)
{
if(cmd.CommandText.Length <= initLenght)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "TimeUTC <= @t2 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t2", end);
cmd.Parameters.Add(lookupValue);
}
if(maxPositionDilutionOfPrecision.HasValue)
{
if(cmd.CommandText.Length <= initLenght)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "PositionDilutionOfPrecision <= @p3 ";
SQLiteParameter lookupValue = new SQLiteParameter("@p3", maxPositionDilutionOfPrecision);
cmd.Parameters.Add(lookupValue);
}
using(DbDataReader rd = cmd.ExecuteReader())
{
List<GpsLog> points = new List<GpsLog>();
while(rd.Read())
{
GpsLog log = new GpsLog();
{
log.TimeUTC = (DateTime)rd["TimeUTC"];
log.SessionCounter = (long)rd["SessionCounter"];
log.Delta = rd["Delta"] as double?;
log.Speed = rd["Speed"] as double?;
log.SeaLevelAltitude = rd["SeaLevelAltitude"] as double?;
log.EllipsoidAltitude = rd["EllipsoidAltitude"] as double?;
log.SatellitesInView = rd["SatellitesInView"] as System.Byte?;
log.SatelliteCount = rd["SatelliteCount"] as System.Byte?;
log.Position = new PointLatLng((double)rd["Lat"], (double)rd["Lng"]);
log.PositionDilutionOfPrecision = rd["PositionDilutionOfPrecision"] as double?;
log.HorizontalDilutionOfPrecision = rd["HorizontalDilutionOfPrecision"] as double?;
log.VerticalDilutionOfPrecision = rd["VerticalDilutionOfPrecision"] as double?;
log.FixQuality = (FixQuality)((byte)rd["FixQuality"]);
log.FixType = (FixType)((byte)rd["FixType"]);
log.FixSelection = (FixSelection)((byte)rd["FixSelection"]);
}
if(log.SessionCounter == 0 && points.Count > 0)
{
List<GpsLog> ret = new List<GpsLog>(points);
points.Clear();
{
yield return ret;
}
}
points.Add(log);
}
if(points.Count > 0)
{
List<GpsLog> ret = new List<GpsLog>(points);
points.Clear();
{
yield return ret;
}
}
points.Clear();
points = null;
rd.Close();
}
}
}
cn.Close();
}
#else
return null;
#endif
}
public static bool PreAllocateDB(string file, int addSizeInMBytes)
{
bool ret = true;
try
{
Debug.WriteLine("PreAllocateDB: " + file + ", +" + addSizeInMBytes + "MB");
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768", file);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768", file);
#endif
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = string.Format("create table large (a); insert into large values (zeroblob({0})); drop table large;", addSizeInMBytes * 1024 * 1024);
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception exx)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + exx.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
cn.Close();
}
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PreAllocateDB: " + ex.ToString());
#endif
Debug.WriteLine("PreAllocateDB: " + ex.ToString());
ret = false;
}
return ret;
}
PureImage PureImageCache.GetImageFromCache(MapType type, Point pos, int zoom)
{
PureImage ret = null;
try
{
string db = gtileCache + GMaps.Instance.LanguageStr + Path.DirectorySeparatorChar + "Data.gmdb";
// get
{
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0}", db);
#endif
cn.Open();
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format("SELECT Tile FROM TilesData WHERE id = (SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3})", pos.X, pos.Y, zoom, (int) type);
using(DbDataReader rd = com.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
{
if(rd.Read())
{
long length = rd.GetBytes(0, 0, null, 0, 0);
byte[] tile = new byte[length];
rd.GetBytes(0, 0, tile, 0, tile.Length);
{
if(GMaps.Instance.ImageProxy != null)
{
MemoryStream stm = new MemoryStream(tile, 0, tile.Length, false, true);
ret = GMaps.Instance.ImageProxy.FromStream(stm);
if(ret!= null)
{
ret.Data = stm;
}
}
}
tile = null;
}
rd.Close();
}
}
}
cn.Close();
}
}
}
}
catch(Exception ex)
{
Debug.WriteLine("GetImageFromCache: " + ex.ToString());
ret = null;
}
return ret;
}
public static bool ExportMapDataToDB(string sourceFile, string destFile)
{
bool ret = true;
try
{
if(!File.Exists(destFile))
{
ret = CreateEmptyDB(destFile);
}
if(ret)
{
using(SQLiteConnection cn1 = new SQLiteConnection())
{
#if !MONO
cn1.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768;Pooling=True", sourceFile);
#else
cn1.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768,Pooling=True", sourceFile);
#endif
cn1.Open();
if(cn1.State == System.Data.ConnectionState.Open)
{
using(SQLiteConnection cn2 = new SQLiteConnection())
{
#if !MONO
cn2.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768;Pooling=True", destFile);
#else
cn2.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768,Pooling=True", destFile);
#endif
cn2.Open();
if(cn2.State == System.Data.ConnectionState.Open)
{
using(SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE \"{0}\" AS Source", sourceFile), cn2))
{
cmd.ExecuteNonQuery();
}
#if !MONO
using(SQLiteTransaction tr = cn2.BeginTransaction())
#else
using(DbTransaction tr = cn2.BeginTransaction())
#endif
{
try
{
List<long> add = new List<long>();
using(SQLiteCommand cmd = new SQLiteCommand("SELECT id, X, Y, Zoom, Type FROM Tiles;", cn1))
{
using(SQLiteDataReader rd = cmd.ExecuteReader())
{
while(rd.Read())
{
long id = rd.GetInt64(0);
using(SQLiteCommand cmd2 = new SQLiteCommand(string.Format("SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3};", rd.GetInt32(1), rd.GetInt32(2), rd.GetInt32(3), rd.GetInt32(4)), cn2))
{
using(SQLiteDataReader rd2 = cmd2.ExecuteReader())
{
if(!rd2.Read())
{
add.Add(id);
}
}
}
}
}
}
foreach(long id in add)
{
using(SQLiteCommand cmd = new SQLiteCommand(string.Format("INSERT INTO Tiles(X, Y, Zoom, Type) SELECT X, Y, Zoom, Type FROM Source.Tiles WHERE id={0}; INSERT INTO TilesData(id, Tile) Values((SELECT last_insert_rowid()), (SELECT Tile FROM Source.TilesData WHERE id={0}));", id), cn2))
{
cmd.Transaction = tr;
cmd.ExecuteNonQuery();
}
}
add.Clear();
tr.Commit();
}
catch
{
tr.Rollback();
ret = false;
}
}
}
}
}
}
}
}
catch(Exception ex)
{
Debug.WriteLine("ExportMapDataToDB: " + ex.ToString());
ret = false;
}
return ret;
}
public static bool ExportMapDataToDB(string sourceFile, string destFile)
{
bool ret = true;
try
{
if (!File.Exists(destFile))
{
ret = CreateEmptyDB(destFile);
}
if (ret)
{
using (SQLiteConnection cn1 = new SQLiteConnection())
{
#if !MONO
cn1.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", sourceFile);
#else
cn1.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", sourceFile);
#endif
cn1.Open();
if (cn1.State == System.Data.ConnectionState.Open)
{
using (SQLiteConnection cn2 = new SQLiteConnection())
{
#if !MONO
cn2.ConnectionString = string.Format("Data Source=\"{0}\";Page Size=32768", destFile);
#else
cn2.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", destFile);
#endif
cn2.Open();
if (cn2.State == System.Data.ConnectionState.Open)
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("ATTACH DATABASE \"{0}\" AS Source", sourceFile), cn2))
{
cmd.ExecuteNonQuery();
}
#if !MONO
using (SQLiteTransaction tr = cn2.BeginTransaction())
#else
using (DbTransaction tr = cn2.BeginTransaction())
#endif
{
try
{
List <long> add = new List <long>();
using (SQLiteCommand cmd = new SQLiteCommand("SELECT id, X, Y, Zoom, Type FROM Tiles;", cn1))
{
using (SQLiteDataReader rd = cmd.ExecuteReader())
{
while (rd.Read())
{
long id = rd.GetInt64(0);
using (SQLiteCommand cmd2 = new SQLiteCommand(string.Format("SELECT id FROM Tiles WHERE X={0} AND Y={1} AND Zoom={2} AND Type={3};", rd.GetInt32(1), rd.GetInt32(2), rd.GetInt32(3), rd.GetInt32(4)), cn2))
{
using (SQLiteDataReader rd2 = cmd2.ExecuteReader())
{
if (!rd2.Read())
{
add.Add(id);
}
}
}
}
}
}
foreach (long id in add)
{
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("INSERT INTO Tiles(X, Y, Zoom, Type, CacheTime) SELECT X, Y, Zoom, Type, CacheTime FROM Source.Tiles WHERE id={0}; INSERT INTO TilesData(id, Tile) Values((SELECT last_insert_rowid()), (SELECT Tile FROM Source.TilesData WHERE id={0}));", id), cn2))
{
cmd.Transaction = tr;
cmd.ExecuteNonQuery();
}
}
add.Clear();
tr.Commit();
}
catch (Exception exx)
{
Debug.WriteLine("ExportMapDataToDB: " + exx.ToString());
tr.Rollback();
ret = false;
}
}
using (SQLiteCommand cmd = new SQLiteCommand("DETACH DATABASE Source;", cn2))
{
cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine("ExportMapDataToDB: " + ex.ToString());
ret = false;
}
return(ret);
}
int PureImageCache.DeleteOlderThan(DateTime date)
{
int affectedRows = 0;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = string.Format("DELETE FROM Tiles WHERE CacheTime is not NULL and CacheTime < datetime('{0}')", date.ToString("s"));
affectedRows = com.ExecuteNonQuery();
}
}
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("DeleteOlderThan: " + ex.ToString());
#endif
Debug.WriteLine("DeleteOlderThan: " + ex.ToString());
}
return affectedRows;
}
bool PureImageCache.PutImageToCache(MemoryStream tile, MapType type, Point pos, int zoom)
{
bool ret = true;
try
{
string dir = gtileCache + GMaps.Instance.LanguageStr + Path.DirectorySeparatorChar;
// precrete dir
if(!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
// save
{
string db = dir + "Data.gmdb";
if(!File.Exists(db))
{
ret = CreateEmptyDB(db);
}
if(ret)
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0}", db);
#endif
cn.Open();
{
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO Tiles(X, Y, Zoom, Type) VALUES(@p1, @p2, @p3, @p4)";
cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", (int) type));
cmd.ExecuteNonQuery();
}
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO TilesData(id, Tile) VALUES((SELECT last_insert_rowid()), @p1)";
cmd.Parameters.Add(new SQLiteParameter("@p1", tile.GetBuffer()));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception ex)
{
Debug.WriteLine("PutImageToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
}
cn.Close();
}
}
}
}
catch(Exception ex)
{
Debug.WriteLine("PutImageToCache: " + ex.ToString());
ret = false;
}
return ret;
}
bool PureImageCache.PutImageToCache(MemoryStream tile, MapType type, GPoint pos, int zoom)
{
bool ret = true;
if(Created)
{
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
cn.ConnectionString = ConnectionString;
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsert;
cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X));
cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y));
cmd.Parameters.Add(new SQLiteParameter("@p3", zoom));
cmd.Parameters.Add(new SQLiteParameter("@p4", (int)type));
cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now));
cmd.ExecuteNonQuery();
}
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = singleSqlInsertLast;
cmd.Parameters.Add(new SQLiteParameter("@p1", tile.GetBuffer()));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
cn.Close();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("PutImageToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutImageToCache: " + ex.ToString());
ret = false;
}
}
return ret;
}
public bool PutDataToCache(string ip, IpInfo data)
{
bool ret = true;
try
{
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Default Timeout=33", db);
#endif
cn.Open();
{
{
using (DbTransaction tr = cn.BeginTransaction())
{
try
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = "INSERT INTO Cache(Ip, CountryName, RegionName, City, Latitude, Longitude, Time) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7)";
cmd.Parameters.Add(new SQLiteParameter("@p1", ip));
cmd.Parameters.Add(new SQLiteParameter("@p2", data.CountryName));
cmd.Parameters.Add(new SQLiteParameter("@p3", data.RegionName));
cmd.Parameters.Add(new SQLiteParameter("@p4", data.City));
cmd.Parameters.Add(new SQLiteParameter("@p5", data.Latitude));
cmd.Parameters.Add(new SQLiteParameter("@p6", data.Longitude));
cmd.Parameters.Add(new SQLiteParameter("@p7", data.CacheTime));
cmd.ExecuteNonQuery();
}
tr.Commit();
}
catch (Exception ex)
{
Console.WriteLine("PutDataToCache: " + ex.ToString());
Debug.WriteLine("PutDataToCache: " + ex.ToString());
tr.Rollback();
ret = false;
}
}
}
}
cn.Close();
}
}
catch (Exception ex)
{
#if MONO
Console.WriteLine("PutDataToCache: " + ex.ToString());
#endif
Debug.WriteLine("PutDataToCache: " + ex.ToString());
ret = false;
}
return(ret);
}
/// <summary>
/// gets routes from gpsd log file
/// </summary>
/// <param name="gpsdLogFile"></param>
/// <param name="start">start time(UTC) of route, null to read from very start</param>
/// <param name="end">end time(UTC) of route, null to read to the very end</param>
/// <param name="maxPositionDilutionOfPrecision">max value of PositionDilutionOfPrecision, null to get all</param>
/// <returns></returns>
public static IEnumerable <List <GpsLog> > GetRoutesFromMobileLog(string gpsdLogFile, DateTime?start, DateTime?end, double?maxPositionDilutionOfPrecision)
{
#if SQLite
using (SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;", gpsdLogFile);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True", gpsdLogFile);
#endif
cn.Open();
{
using (DbCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM GPS ";
int initLenght = cmd.CommandText.Length;
if (start.HasValue)
{
cmd.CommandText += "WHERE TimeUTC >= @t1 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t1", start);
cmd.Parameters.Add(lookupValue);
}
if (end.HasValue)
{
if (cmd.CommandText.Length <= initLenght)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "TimeUTC <= @t2 ";
SQLiteParameter lookupValue = new SQLiteParameter("@t2", end);
cmd.Parameters.Add(lookupValue);
}
if (maxPositionDilutionOfPrecision.HasValue)
{
if (cmd.CommandText.Length <= initLenght)
{
cmd.CommandText += "WHERE ";
}
else
{
cmd.CommandText += "AND ";
}
cmd.CommandText += "PositionDilutionOfPrecision <= @p3 ";
SQLiteParameter lookupValue = new SQLiteParameter("@p3", maxPositionDilutionOfPrecision);
cmd.Parameters.Add(lookupValue);
}
using (DbDataReader rd = cmd.ExecuteReader())
{
List <GpsLog> points = new List <GpsLog>();
while (rd.Read())
{
GpsLog log = new GpsLog();
{
log.TimeUTC = (DateTime)rd["TimeUTC"];
log.SessionCounter = (long)rd["SessionCounter"];
log.Delta = rd["Delta"] as double?;
log.Speed = rd["Speed"] as double?;
log.SeaLevelAltitude = rd["SeaLevelAltitude"] as double?;
log.EllipsoidAltitude = rd["EllipsoidAltitude"] as double?;
log.SatellitesInView = rd["SatellitesInView"] as System.Byte?;
log.SatelliteCount = rd["SatelliteCount"] as System.Byte?;
log.Position = new PointLatLng((double)rd["Lat"], (double)rd["Lng"]);
log.PositionDilutionOfPrecision = rd["PositionDilutionOfPrecision"] as double?;
log.HorizontalDilutionOfPrecision = rd["HorizontalDilutionOfPrecision"] as double?;
log.VerticalDilutionOfPrecision = rd["VerticalDilutionOfPrecision"] as double?;
log.FixQuality = (FixQuality)((byte)rd["FixQuality"]);
log.FixType = (FixType)((byte)rd["FixType"]);
log.FixSelection = (FixSelection)((byte)rd["FixSelection"]);
}
if (log.SessionCounter == 0 && points.Count > 0)
{
List <GpsLog> ret = new List <GpsLog>(points);
points.Clear();
{
yield return(ret);
}
}
points.Add(log);
}
if (points.Count > 0)
{
List <GpsLog> ret = new List <GpsLog>(points);
points.Clear();
{
yield return(ret);
}
}
points.Clear();
points = null;
rd.Close();
}
}
}
cn.Close();
}
#else
return(null);
#endif
}
public IpInfo GetDataFromCache(string ip)
{
IpInfo ret = null;
try
{
using(SQLiteConnection cn = new SQLiteConnection())
{
#if !MONO
cn.ConnectionString = string.Format("Data Source=\"{0}\";", db);
#else
cn.ConnectionString = string.Format("Version=3,URI=file://{0},Default Timeout=33", db);
#endif
cn.Open();
{
using(DbCommand com = cn.CreateCommand())
{
com.CommandText = "SELECT * FROM Cache WHERE Ip = '" + ip + "'";
using(DbDataReader rd = com.ExecuteReader())
{
if(rd.Read())
{
IpInfo val = new IpInfo();
{
val.Ip = ip;
val.CountryName = rd["CountryName"] as string;
val.RegionName = rd["RegionName"] as string;
val.City = rd["City"] as string;
val.Latitude = (double) rd["Latitude"];
val.Longitude = (double) rd["Longitude"];
val.CacheTime = (DateTime) rd["Time"];
}
ret = val;
}
rd.Close();
}
}
}
cn.Close();
}
}
catch(Exception ex)
{
#if MONO
Console.WriteLine("GetDataFromCache: " + ex.ToString());
#endif
Debug.WriteLine("GetDataFromCache: " + ex.ToString());
ret = null;
}
return ret;
}