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 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); }
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; }
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); }