public EntityStore(string databaseFullPath, DestructorType destructorType = DestructorType.None) { _databaseFullPath = databaseFullPath; _destructorType = destructorType; _connectionString = String.Format("Data Source={0}; Version=3; Read Only=False; Pooling=True; Max Pool Size=10", _databaseFullPath); if (!File.Exists(_databaseFullPath)) { SQLiteConnection.CreateFile(_databaseFullPath); Log.Info("Successfully created the EntityStore database file at {0}", databaseFullPath); } else { Log.Info("Successfully confirmed that the EntityStore database exists at {0}", databaseFullPath); } using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { Log.Debug("About to create or check for the Entities table in the EntityStore database."); cmd.CommandText = "CREATE TABLE IF NOT EXISTS Entities (entityType TEXT, entityKey TEXT, entityBlob TEXT, entityTag TEXT, lastModified DATETIME, PRIMARY KEY (entityType, entityKey))"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); Log.Info("Successfully created or checked that the Entities table exists in the EntityStore database."); } } }
/// <summary> /// Initializes a new instance of the <see cref="InMemorySQLiteDatabase"/> class. /// </summary> public InMemorySQLiteDatabase() { var connectionStringBuilder = new SQLiteConnectionStringBuilder { DataSource = ":memory:", Version = 3, DefaultTimeout = 5, #if MONO JournalMode = SQLiteJournalModeEnum.Off, #else JournalMode = SQLiteJournalModeEnum.Memory, #endif UseUTF16Encoding = true }; ConnectionString = connectionStringBuilder.ToString(); connectionManager = new SQLiteConnectionManager(connectionStringBuilder.ConnectionString); sharedConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString); sharedConnection.Open(); sqlRunner = new AdHocSqlRunner(() => sharedConnection.CreateCommand(), null, () => true); }
/// <summary> /// Initializes a new instance of the <see cref="TemporarySQLiteDatabase"/> class. /// </summary> /// <param name="name">The name.</param> public TemporarySQLiteDatabase(string name) { dataSourcePath = Path.Combine(Environment.CurrentDirectory, name); var connectionStringBuilder = new SQLiteConnectionStringBuilder { DataSource = name, Version = 3, DefaultTimeout = 5, #if MONO JournalMode = SQLiteJournalModeEnum.Off, #else JournalMode = SQLiteJournalModeEnum.Memory, #endif UseUTF16Encoding = true }; sqLiteConnection = new SQLiteConnection(connectionStringBuilder.ConnectionString); sqLiteConnection.Open(); sharedConnection = new SharedConnection(sqLiteConnection); sqlRunner = new AdHocSqlRunner(() => sqLiteConnection.CreateCommand(), null, () => true); }
public byte[] GetEntity(string entityType, string entityKey, out string entityTag, out DateTime? lastModified) { using (Log.Scope(LogLevel.Debug, "GetEntity")) { using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT entityTag, lastModified, entityBlob FROM Entities WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); var reader = cmd.ExecuteReader(CommandBehavior.SingleRow); if (!reader.Read()) { Log.Debug("Could not find Entity with EntityType '{0}' and EntityKey '{1}'", entityType, entityKey); entityTag = null; lastModified = null; return null; } else { entityTag = null; if (!reader.IsDBNull(0)) entityTag = reader.GetString(0); lastModified = null; if (!reader.IsDBNull(1)) lastModified = reader.GetDateTime(1); Log.Debug("Found Entity with EntityType '{0}' and EntityKey '{1}'. It had EntityTag '{2}' and was LastModified '{3}'", entityType, entityKey, entityTag, lastModified); if (!reader.IsDBNull(2)) return ReadBytes(reader, 2); else return null; } } } } }
public bool BatchUpDate() { bool ret = true; if (Created) { try { using (SQLiteConnection cn = new SQLiteConnection()) { cn.ConnectionString = ConnectionString; cn.Open(); { using (DbTransaction tr = cn.BeginTransaction()) { try { foreach (var item in BatchPutImageData) { using (DbCommand cmd = cn.CreateCommand()) { cmd.Transaction = tr; cmd.CommandText = singleSqlInsert; cmd.Parameters.Add(new SQLiteParameter("@p1", item.pos.X)); cmd.Parameters.Add(new SQLiteParameter("@p2", item.pos.Y)); cmd.Parameters.Add(new SQLiteParameter("@p3", item.zoom)); cmd.Parameters.Add(new SQLiteParameter("@p4", item.type)); cmd.Parameters.Add(new SQLiteParameter("@p5", BatchPutImage.curTime)); cmd.ExecuteNonQuery(); } using (DbCommand cmd = cn.CreateCommand()) { cmd.Transaction = tr; cmd.CommandText = singleSqlInsertLast; cmd.Parameters.Add(new SQLiteParameter("@p1", item.tile)); cmd.ExecuteNonQuery(); } } tr.Commit(); } catch (Exception ex) { #if MONO Console.WriteLine("PutImageToCache: " + ex.ToString()); #endif Debug.WriteLine("PutImageToCache: " + ex.ToString()); tr.Rollback(); ret = false; } } } cn.Close(); } if (Interlocked.Increment(ref preAllocationPing) % 22 == 0) { CheckPreAllocation(); } reSetBatchQueue(); } catch (Exception ex) { #if MONO Console.WriteLine("PutImageToCache: " + ex.ToString()); #endif Debug.WriteLine("PutImageToCache: " + ex.ToString()); ret = false; } } return(ret); }
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) { ret = GMapProvider.TileImageProxy.FromArray(tile); } } tile = null; } rd.Close(); } } if(!string.IsNullOrEmpty(detachSqlQuery)) { using(DbCommand com = cn.CreateCommand()) { com.CommandText = detachSqlQuery; int x = com.ExecuteNonQuery(); //Debug.WriteLine("Detach: " + x); } } } cn.Close(); } } catch(Exception ex) { #if MONO Console.WriteLine("GetImageFromCache: " + ex.ToString()); #endif Debug.WriteLine("GetImageFromCache: " + ex.ToString()); ret = null; } return ret; }
public static bool VacuumDb(string file) { bool ret = true; try { using(SQLiteConnection cn = new SQLiteConnection()) { #if !MONO cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=True;Page Size=32768", file); #else cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Page Size=32768", file); #endif cn.Open(); { using(DbCommand cmd = cn.CreateCommand()) { cmd.CommandText = "vacuum;"; cmd.ExecuteNonQuery(); } cn.Close(); } } } catch(Exception ex) { Debug.WriteLine("VacuumDb: " + ex.ToString()); ret = false; } return ret; }
public static bool PreAllocateDB(string file, int addSizeInMBytes) { bool ret = true; try { Debug.WriteLine("PreAllocateDB: " + file + ", +" + addSizeInMBytes + "MB"); using(SQLiteConnection cn = new SQLiteConnection()) { #if !MONO cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768", file); #else cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768", file); #endif cn.Open(); { using(DbTransaction tr = cn.BeginTransaction()) { try { using(DbCommand cmd = cn.CreateCommand()) { cmd.Transaction = tr; cmd.CommandText = string.Format("create table large (a); insert into large values (zeroblob({0})); drop table large;", addSizeInMBytes * 1024 * 1024); cmd.ExecuteNonQuery(); } tr.Commit(); } catch(Exception exx) { #if MONO Console.WriteLine("PreAllocateDB: " + exx.ToString()); #endif Debug.WriteLine("PreAllocateDB: " + exx.ToString()); tr.Rollback(); ret = false; } } cn.Close(); } } } catch(Exception ex) { #if MONO Console.WriteLine("PreAllocateDB: " + ex.ToString()); #endif Debug.WriteLine("PreAllocateDB: " + ex.ToString()); ret = false; } return ret; }
public bool PutDataToCache(string ip, IpInfo data) { bool ret = true; try { using(SQLiteConnection cn = new SQLiteConnection()) { #if !MONO cn.ConnectionString = string.Format("Data Source=\"{0}\";", db); #else cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=True,Default Timeout=33", db); #endif cn.Open(); { { using(DbTransaction tr = cn.BeginTransaction()) { try { using(DbCommand cmd = cn.CreateCommand()) { cmd.Transaction = tr; cmd.CommandText = "INSERT INTO Cache(Ip, CountryName, RegionName, City, Latitude, Longitude, Time) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7)"; cmd.Parameters.Add(new SQLiteParameter("@p1", ip)); cmd.Parameters.Add(new SQLiteParameter("@p2", data.CountryName)); cmd.Parameters.Add(new SQLiteParameter("@p3", data.RegionName)); cmd.Parameters.Add(new SQLiteParameter("@p4", data.City)); cmd.Parameters.Add(new SQLiteParameter("@p5", data.Latitude)); cmd.Parameters.Add(new SQLiteParameter("@p6", data.Longitude)); cmd.Parameters.Add(new SQLiteParameter("@p7", data.CacheTime)); cmd.ExecuteNonQuery(); } tr.Commit(); } catch(Exception ex) { Console.WriteLine("PutDataToCache: " + ex.ToString()); Debug.WriteLine("PutDataToCache: " + ex.ToString()); tr.Rollback(); ret = false; } } } } cn.Close(); } } catch(Exception ex) { #if MONO Console.WriteLine("PutDataToCache: " + ex.ToString()); #endif Debug.WriteLine("PutDataToCache: " + ex.ToString()); ret = false; } return ret; }
/// <summary> /// Backup the database /// </summary> public string Backup(string passkey) { // Take the main connection lock lock (s_lockObject) { try { var backupDir = Path.Combine(Path.GetTempPath(), "db-copy"); if (!Directory.Exists(backupDir)) { Directory.CreateDirectory(backupDir); } ISQLitePlatform platform = ApplicationContext.Current.GetService <ISQLitePlatform>(); var connectionStrings = (ApplicationContext.Current.GetService <IConfigurationManager>()).GetSection <DcDataConfigurationSection>().ConnectionString; for (var i = 0; i < connectionStrings.Count; i++) { var pool = this.GetConnectionPool(connectionStrings[i].Name); //pool.CloseAll(); using (pool.Lock()) // Lock the pool { this.m_tracer.TraceInfo("Will backup {0} with passkey {1}", connectionStrings[i].GetComponent("dbfile"), !String.IsNullOrEmpty(passkey)); if (!File.Exists(connectionStrings[i].GetComponent("dbfile"))) { continue; } var dataFile = connectionStrings[i].GetComponent("dbfile"); ApplicationContext.Current.SetProgress(Strings.locale_backup, (float)i / connectionStrings.Count); var backupFile = Path.Combine(backupDir, Path.GetFileName(dataFile)); if (File.Exists(backupFile)) { File.Delete(backupFile); } // Create empty database this.m_tracer.TraceVerbose("Creating temporary copy of database at {0}...", backupFile); // Create new encrypted database Mono.Data.Sqlite.SqliteConnection.CreateFile(backupFile); using (var prodConn = new Mono.Data.Sqlite.SqliteConnection($"Data Source={backupFile}")) { prodConn.Open(); using (var cmd = prodConn.CreateCommand()) { cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = $"PRAGMA key = '{passkey}'"; cmd.ExecuteNonQuery(); } } // Commands to be run string[] sqlScript = { $"ATTACH DATABASE '{backupFile}' AS bak_db KEY '{passkey}'", "SELECT sqlcipher_export('bak_db')", "DETACH DATABASE bak_db" }; // Attempt to use the existing security key using (var bakConn = new Mono.Data.Sqlite.SqliteConnection($"Data Source={dataFile}")) { if (connectionStrings[i].GetComponent("encrypt") == "true") { bakConn.SetPassword(ApplicationContext.Current.GetCurrentContextSecurityKey()); } bakConn.Open(); foreach (var sql in sqlScript) { using (var cmd = bakConn.CreateCommand()) { cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } } } } return(backupDir); } catch (Exception e) { throw new DataPersistenceException("Error backing up database", e); } } }
/// <summary> /// Rekey databases /// </summary> public void RekeyDatabases() { if (ApplicationContext.Current.GetCurrentContextSecurityKey() == null) { return; // no need to rekey } lock (s_lockObject) // prevent others from obtaining connections try { ISQLitePlatform platform = ApplicationContext.Current.GetService <ISQLitePlatform>(); var connectionStrings = (ApplicationContext.Current.GetService <IConfigurationManager>()).GetSection <DcDataConfigurationSection>().ConnectionString; for (var i = 0; i < connectionStrings.Count; i++) { ApplicationContext.Current.SetProgress(Strings.locale_backup_restore, (float)i / connectionStrings.Count); var cstr = connectionStrings[i]; // Obtain and lock the connection pool this.m_tracer.TraceVerbose("Rekey {0} - Closing existing connections", cstr.Name); var pool = this.GetConnectionPool(cstr.Name); pool.CloseAll(); using (pool.Lock()) // Prevent new pool connections from being created { var dbFile = cstr.GetComponent("dbfile"); try { if (!File.Exists(dbFile)) { continue; // could not find file } File.Move(dbFile, Path.ChangeExtension(dbFile, "old")); // Create new encrypted database Mono.Data.Sqlite.SqliteConnection.CreateFile(dbFile); using (var prodConn = new Mono.Data.Sqlite.SqliteConnection($"Data Source={dbFile}")) { prodConn.Open(); using (var cmd = prodConn.CreateCommand()) { cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = $"PRAGMA key = '{Encoding.UTF8.GetString(ApplicationContext.Current.GetCurrentContextSecurityKey())}'"; cmd.ExecuteNonQuery(); } } // Commands to be run string[] sqlScript = { $"ATTACH DATABASE '{dbFile}' AS prod_db KEY '{Encoding.UTF8.GetString(ApplicationContext.Current.GetCurrentContextSecurityKey())}'", "SELECT sqlcipher_export('prod_db')", "DETACH DATABASE prod_db" }; // Attempt to use the existing security key using (var bakConn = new Mono.Data.Sqlite.SqliteConnection($"Data Source={Path.ChangeExtension(dbFile, "old")}")) { bakConn.Open(); foreach (var sql in sqlScript) { using (var cmd = bakConn.CreateCommand()) { cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } } } finally { File.Delete(Path.ChangeExtension(dbFile, "old")); } } } } catch (Exception e) { this.m_tracer.TraceError("Error rekeying database: {0}", e); throw new Exception("Error re-keying databases", e); } }
/// <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 "; if(start.HasValue) { cmd.CommandText += "WHERE TimeUTC >= @t1 "; SQLiteParameter lookupValue = new SQLiteParameter("@t1", start); cmd.Parameters.Add(lookupValue); } if(end.HasValue) { if(!start.HasValue) { 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(!start.HasValue && !end.HasValue) { 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>(); long lastSessionCounter = -1; 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 - lastSessionCounter != 1 && points.Count > 0) { List<GpsLog> ret = new List<GpsLog>(points); points.Clear(); { yield return ret; } } points.Add(log); lastSessionCounter = log.SessionCounter; } 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 }
/// <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 "; if (start.HasValue) { cmd.CommandText += "WHERE TimeUTC >= @t1 "; SQLiteParameter lookupValue = new SQLiteParameter("@t1", start); cmd.Parameters.Add(lookupValue); } if (end.HasValue) { if (!start.HasValue) { 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 (!start.HasValue && !end.HasValue) { 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>(); long lastSessionCounter = -1; 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 - lastSessionCounter != 1 && points.Count > 0) { List <GpsLog> ret = new List <GpsLog>(points); points.Clear(); { yield return(ret); } } points.Add(log); lastSessionCounter = log.SessionCounter; } if (points.Count > 0) { List <GpsLog> ret = new List <GpsLog>(points); points.Clear(); { yield return(ret); } } points.Clear(); points = null; rd.Close(); } } } cn.Close(); } #else return(null); #endif }
public Statement Prepare(string query) => new Statement(Database.CreateCommand(), query);
public IDictionary<string, byte[]> GetAllEntities(string entityType) { using (Log.Scope(LogLevel.Debug, "GetAllEntities")) { using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT entityKey, entityBlob FROM Entities WHERE entityType=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); var reader = cmd.ExecuteReader(CommandBehavior.SingleResult); var dict = new Dictionary<string, byte[]>(8); while(reader.Read()) { string entityKey = null; if (!reader.IsDBNull(0)) entityKey = reader.GetString(0); Log.Debug("Found Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); if (reader.IsDBNull(1)) continue; var bytes = ReadBytes(reader, 1); dict.Add(entityKey, bytes); } return dict; } } } }
public bool PutEntity(string entityType, string entityKey, byte[] entityBlob, string entityTag = null, DateTime? lastModified = null, bool alwaysOverwrite = true) { using (Log.Scope(LogLevel.Debug, "PutEntity")) { bool worked = false; SQLiteConnection connection; using (connection = new SQLiteConnection(_connectionString)) { connection.Open(); SQLiteCommand cmd; using (cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT COUNT(entityType) FROM Entities WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType}); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); var someType = cmd.ExecuteScalar(); var foundCount = (long)someType; if (foundCount > 0) { if (alwaysOverwrite) { using (cmd = connection.CreateCommand()) { cmd.CommandText = "UPDATE Entities SET entityBlob=?, entityTag=?, lastModified=? WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.Object, ParameterName = "entityBlob", Value = entityBlob }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); if (cmd.ExecuteNonQuery() > 0) { worked = true; Log.Debug("Put (update) Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); } } } else { using (cmd = connection.CreateCommand()) { cmd.CommandText = "SELECT COUNT(entityType) FROM Entities WHERE entityType=? AND entityKey=? AND entityTag=? AND lastModified=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified }); if ((int)cmd.ExecuteScalar() > 0) { using (cmd = connection.CreateCommand()) { cmd.CommandText = "UPDATE Entities SET entityBlob=?, entityTag=?, lastModified=? WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.Object, ParameterName = "entityBlob", Value = entityBlob }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); if (cmd.ExecuteNonQuery() > 0) { worked = true; Log.Debug("Put (update) Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); } } } } } } else { using (cmd = connection.CreateCommand()) { cmd.CommandText = "INSERT INTO Entities (entityType, entityKey, entityBlob, entityTag, lastModified) VALUES (?, ?, ?, ?, ?)"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.Object, ParameterName = "entityBlob", Value = entityBlob }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityTag", Value = entityTag }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.DateTime, ParameterName = "lastModified", Value = lastModified }); if (cmd.ExecuteNonQuery() > 0) { worked = true; Log.Debug("Put (insert) Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); } } } } } return worked; } }
public IpInfo GetDataFromCache(string ip) { IpInfo ret = null; try { using(SQLiteConnection cn = new SQLiteConnection()) { #if !MONO cn.ConnectionString = string.Format("Data Source=\"{0}\";", db); #else cn.ConnectionString = string.Format("Version=3,URI=file://{0},Default Timeout=33", db); #endif cn.Open(); { using(DbCommand com = cn.CreateCommand()) { com.CommandText = "SELECT * FROM Cache WHERE Ip = '" + ip + "'"; using(DbDataReader rd = com.ExecuteReader()) { if(rd.Read()) { IpInfo val = new IpInfo(); { val.Ip = ip; val.CountryName = rd["CountryName"] as string; val.RegionName = rd["RegionName"] as string; val.City = rd["City"] as string; val.Latitude = (double)rd["Latitude"]; val.Longitude = (double)rd["Longitude"]; val.CacheTime = (DateTime)rd["Time"]; } ret = val; } rd.Close(); } } } cn.Close(); } } catch(Exception ex) { #if MONO Console.WriteLine("GetDataFromCache: " + ex.ToString()); #endif Debug.WriteLine("GetDataFromCache: " + ex.ToString()); ret = null; } return ret; }
public bool DeleteEntity(string entityType, string entityKey) { using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { cmd.CommandText = "DELETE FROM Entities WHERE entityType=? AND entityKey=?"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityType", Value = entityType }); cmd.Parameters.Add(new SQLiteParameter() { DbType = DbType.String, ParameterName = "entityKey", Value = entityKey }); var affectedCount = cmd.ExecuteNonQuery(); Log.Debug("Deleted Entity with EntityType '{0}' and EntityKey '{1}'.", entityType, entityKey); return affectedCount == 1; } } }
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) { ret = GMapProvider.TileImageProxy.FromArray(tile); } } tile = null; } rd.Close(); } } if (!string.IsNullOrEmpty(detachSqlQuery)) { using (DbCommand com = cn.CreateCommand()) { com.CommandText = detachSqlQuery; int x = com.ExecuteNonQuery(); //Debug.WriteLine("Detach: " + x); } } } cn.Close(); } } catch (Exception ex) { #if MONO Console.WriteLine("GetImageFromCache: " + ex.ToString()); #endif Debug.WriteLine("GetImageFromCache: " + ex.ToString()); ret = null; } return(ret); }
public static bool 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(Exception exx) { #if MONO Console.WriteLine("CreateEmptyDB: " + exx.ToString()); #endif Debug.WriteLine("CreateEmptyDB: " + exx.ToString()); tr.Rollback(); ret = false; } } cn.Close(); } } } catch(Exception ex) { #if MONO Console.WriteLine("CreateEmptyDB: " + ex.ToString()); #endif Debug.WriteLine("CreateEmptyDB: " + ex.ToString()); ret = false; } return ret; }
public static bool CreateEmptyDB(string file) { bool ret = true; try { string dir = Path.GetDirectoryName(file); if (!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } using (SQLiteConnection cn = new SQLiteConnection()) { #if !MONO cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;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 (Exception exx) { #if MONO Console.WriteLine("CreateEmptyDB: " + exx.ToString()); #endif 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); }
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; }
private static bool AlterDBAddTimeColumn(string file) { bool ret = true; try { if (File.Exists(file)) { using (SQLiteConnection cn = new SQLiteConnection()) { #if !MONO cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;Page Size=32768;Pooling=True", file); #else cn.ConnectionString = string.Format("Version=3,URI=file://{0},FailIfMissing=False,Page Size=32768,Pooling=True", file); #endif cn.Open(); { using (DbTransaction tr = cn.BeginTransaction()) { bool?NoCacheTimeColumn = null; try { using (DbCommand cmd = new SQLiteCommand("SELECT CacheTime FROM Tiles", cn)) { cmd.Transaction = tr; using (DbDataReader rd = cmd.ExecuteReader()) { rd.Close(); } NoCacheTimeColumn = false; } } catch (Exception ex) { if (ex.Message.Contains("no such column: CacheTime")) { NoCacheTimeColumn = true; } else { throw ex; } } try { if (NoCacheTimeColumn.HasValue && NoCacheTimeColumn.Value) { using (DbCommand cmd = cn.CreateCommand()) { cmd.Transaction = tr; cmd.CommandText = "ALTER TABLE Tiles ADD CacheTime DATETIME"; cmd.ExecuteNonQuery(); } tr.Commit(); NoCacheTimeColumn = false; } } catch (Exception exx) { #if MONO Console.WriteLine("AlterDBAddTimeColumn: " + exx.ToString()); #endif Debug.WriteLine("AlterDBAddTimeColumn: " + exx.ToString()); tr.Rollback(); ret = false; } } cn.Close(); } } } else { ret = false; } } catch (Exception ex) { #if MONO Console.WriteLine("AlterDBAddTimeColumn: " + ex.ToString()); #endif Debug.WriteLine("AlterDBAddTimeColumn: " + ex.ToString()); ret = false; } return(ret); }
bool PureImageCache.PutImageToCache(byte[] tile, int type, GPoint pos, int zoom) { bool ret = true; if(Created) { try { using(SQLiteConnection cn = new SQLiteConnection()) { cn.ConnectionString = ConnectionString; cn.Open(); { using(DbTransaction tr = cn.BeginTransaction()) { try { using(DbCommand cmd = cn.CreateCommand()) { cmd.Transaction = tr; cmd.CommandText = singleSqlInsert; cmd.Parameters.Add(new SQLiteParameter("@p1", pos.X)); cmd.Parameters.Add(new SQLiteParameter("@p2", pos.Y)); cmd.Parameters.Add(new SQLiteParameter("@p3", zoom)); cmd.Parameters.Add(new SQLiteParameter("@p4", type)); cmd.Parameters.Add(new SQLiteParameter("@p5", DateTime.Now)); cmd.ExecuteNonQuery(); } using(DbCommand cmd = cn.CreateCommand()) { cmd.Transaction = tr; cmd.CommandText = singleSqlInsertLast; cmd.Parameters.Add(new SQLiteParameter("@p1", tile)); cmd.ExecuteNonQuery(); } tr.Commit(); } catch(Exception ex) { #if MONO Console.WriteLine("PutImageToCache: " + ex.ToString()); #endif Debug.WriteLine("PutImageToCache: " + ex.ToString()); tr.Rollback(); ret = false; } } } cn.Close(); } if(Interlocked.Increment(ref preAllocationPing) % 22 == 0) { CheckPreAllocation(); } } catch(Exception ex) { #if MONO Console.WriteLine("PutImageToCache: " + ex.ToString()); #endif Debug.WriteLine("PutImageToCache: " + ex.ToString()); ret = false; } } return ret; }
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); }
int PureImageCache.DeleteOlderThan(DateTime date, int? type) { int affectedRows = 0; try { using(SQLiteConnection cn = new SQLiteConnection()) { cn.ConnectionString = ConnectionString; cn.Open(); { using(DbCommand com = cn.CreateCommand()) { com.CommandText = string.Format("DELETE FROM Tiles WHERE CacheTime is not NULL and CacheTime < datetime('{0}')", date.ToString("s")); if(type.HasValue) { com.CommandText += " and Type = " + type; } affectedRows = com.ExecuteNonQuery(); } } } } catch(Exception ex) { #if MONO Console.WriteLine("DeleteOlderThan: " + ex); #endif Debug.WriteLine("DeleteOlderThan: " + ex); } return affectedRows; }
public static void Main(string[] args) { var options = new Options(); CommandLine.ICommandLineParser cmdParser = new CommandLine.CommandLineParser(new CommandLine.CommandLineParserSettings(System.Console.Error)); if (cmdParser.ParseArguments(args, options)) { string connectionString = string.Format("URI=file:{0}", options.Database); #if (NET) var connection = new System.Data.SQLite.SQLiteConnection(connectionString); #else var connection = new Mono.Data.Sqlite.SqliteConnection(connectionString); #endif connection.Open(); var command = connection.CreateCommand(); command.CommandText = "CREATE TABLE IF NOT EXISTS at (id INTEGER PRIMARY KEY NOT NULL,name VARCHAR,surname VARCHAR,year INTEGER,gender CHAR,time VARCHAR)"; command.ExecuteNonQuery(); var repo = new AthleteRepository(command); switch (options.Action) { case Action.Module: { // 10mm d=> 28pt // 15mm => 42pt //float marginLeft, float marginRight, float marginTop, float marginBottom var document = new iTextSharp.text.Document(iTextSharp.text.PageSize.A4, 10, 10, 36, 36); iTextSharp.text.pdf.PdfWriter.GetInstance(document, new System.IO.FileStream("./module.pdf", System.IO.FileMode.Create)); document.Open(); var builder = new ModuleBuilder(document, options.YearEdition, 10); for (int page = 0; page < 10; page++) { builder.AddPage(); } document.Close(); break; } case Action.Insert: { System.Console.WriteLine("Drop all results?[y/N]?"); string yes = System.Console.ReadLine(); if (yes == "y") { FileHelpers.FileHelperEngine<Athlete> engine = new FileHelpers.FileHelperEngine<Athlete>(); Athlete[] athletes = engine.ReadFile(options.Input); repo.DeleteAll(); foreach (var a in athletes) { System.Console.WriteLine(a.Name); repo.Insert(a); } } break; } case Action.CreateList: case Action.CreateResult: { string catFileName = GetCatFileName(options); string reportFileName = GetReportFileName(options); var document = new iTextSharp.text.Document(iTextSharp.text.PageSize.A4, 10, 10, 36, 36); iTextSharp.text.pdf.PdfWriter.GetInstance(document, new System.IO.FileStream(reportFileName, System.IO.FileMode.Create)); document.Open(); IBuilder builder = null; if (options.Action == Action.CreateList) { builder = new ListBuilder(document); } else { builder = new PdfBuilder(document); } Category[] cats = GetCategories(catFileName); foreach (Category cat in cats) { if (log.IsDebugEnabled) log.Debug("parse" + cat.Id); builder.BeginReport(cat.Title, options.YearEdition); var athletes = repo.Query(string.Format (cat.Sql, options.YearEdition)); foreach (Athlete athlete in athletes) { builder.Add(athlete); } builder.EndReport(); } document.Close(); break; } case Action.Interactive: { Category[] cats = GetCategories(GetCatFileName(options)); var parser = new TimeParser(); foreach (Category cat in cats) { System.Console.WriteLine("========{0}=========", cat.Id); var athletes = repo.Query(string.Format (cat.Sql, options.YearEdition)); foreach (Athlete athlete in athletes) { System.Console.Write("{0:00} {1}\t{2}({3}):", athlete.Id, athlete.Surname, athlete.Name, athlete.Gender); string time = string.Empty; string fmt = string.Empty; do { time = System.Console.ReadLine(); fmt = parser.Parse(time); if (!string.IsNullOrEmpty(fmt) ) { System.Console.WriteLine(fmt); repo.UpdateTime(athlete.Id, fmt); } else { if (time != "s") { System.Console.WriteLine("invalid.."); } } } while (string.IsNullOrEmpty(fmt) && time != "s"); } } break; } } connection.Close(); } }