Ejemplo n.º 1
0
    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);
    }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
    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);
    }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
    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);
    }
Ejemplo n.º 7
0
        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);
        }
Ejemplo n.º 8
0
        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);
        }
Ejemplo n.º 9
0
      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;
      }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 11
0
        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);
        }
Ejemplo n.º 12
0
        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);
        }
Ejemplo n.º 13
0
        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);
        }
Ejemplo n.º 14
0
    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;
    }
Ejemplo n.º 15
0
    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;
    }
Ejemplo n.º 16
0
    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);
        }
    }
Ejemplo n.º 17
0
        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);
        }
Ejemplo n.º 18
0
        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;
        }
Ejemplo n.º 19
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;
        }
Ejemplo n.º 20
0
        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;
        }
Ejemplo n.º 21
0
        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;
        }
Ejemplo n.º 22
0
      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;
      }
Ejemplo n.º 23
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);
        }
Ejemplo n.º 24
0
        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);
        }
Ejemplo n.º 26
0
        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;
        }
Ejemplo n.º 27
0
        /// <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
        }
Ejemplo n.º 28
0
      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;
      }
Ejemplo n.º 29
0
      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;
      }
Ejemplo n.º 30
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;
        }
Ejemplo n.º 31
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);
        }
Ejemplo n.º 32
0
        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;
        }
Ejemplo n.º 33
0
      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;
      }
Ejemplo n.º 34
0
        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;
        }
Ejemplo n.º 35
0
        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);
        }
Ejemplo n.º 36
0
        /// <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
        }
Ejemplo n.º 37
0
        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;
        }