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); }
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; }
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); }
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); }
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(); } using (SQLiteTransaction tr = cn2.BeginTransaction()) { 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); }
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(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; }
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(); } using(SQLiteTransaction tr = cn2.BeginTransaction()) { 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; }
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; }
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 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); }
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; }
public bool BatchUpDate() { bool ret = true; if (Created) { try { using (SQLiteConnection cn = new SQLiteConnection()) { cn.ConnectionString = ConnectionString; cn.Open(); { using (DbTransaction tr = cn.BeginTransaction()) { try { foreach (var item in BatchPutImageData) { using (DbCommand cmd = cn.CreateCommand()) { cmd.Transaction = tr; cmd.CommandText = singleSqlInsert; cmd.Parameters.Add(new SQLiteParameter("@p1", item.pos.X)); cmd.Parameters.Add(new SQLiteParameter("@p2", item.pos.Y)); cmd.Parameters.Add(new SQLiteParameter("@p3", item.zoom)); cmd.Parameters.Add(new SQLiteParameter("@p4", item.type)); cmd.Parameters.Add(new SQLiteParameter("@p5", BatchPutImage.curTime)); cmd.ExecuteNonQuery(); } using (DbCommand cmd = cn.CreateCommand()) { cmd.Transaction = tr; cmd.CommandText = singleSqlInsertLast; cmd.Parameters.Add(new SQLiteParameter("@p1", item.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(); } reSetBatchQueue(); } catch (Exception ex) { #if MONO Console.WriteLine("PutImageToCache: " + ex.ToString()); #endif Debug.WriteLine("PutImageToCache: " + ex.ToString()); ret = false; } } return(ret); }