示例#1
0
        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;
        }
示例#2
0
        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);
        }
示例#3
0
        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;
        }
示例#4
0
        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);
        }