public override void Initialise() { var dbFile = ExtractFilename(SmartConnectionString.For(myConfig.ConnectionString)); try { if (!File.Exists(dbFile)) { Logger.Debug("\tCreating Wolfpack SQLite datafile at {0}...", dbFile); SQLiteConnection.CreateFile(dbFile); } var schema = GetSchema("AgentData"); if (schema.Columns.Count == 0) { Logger.Debug("\tCreating AgentData table..."); using (var cmd = SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString) .WithSql(SQLiteStatement.Create("CREATE TABLE AgentData (") .Append("[TypeId] UNIQUEIDENTIFIER NOT NULL,") .Append("[EventType] TEXT NOT NULL,") .Append("[SiteId] TEXT NOT NULL,") .Append("[AgentId] TEXT NOT NULL,") .Append("[CheckId] TEXT NULL,") .Append("[Result] BOOL NULL,") .Append("[GeneratedOnUtc] DATETIME NOT NULL,") .Append("[ReceivedOnUtc] DATETIME NOT NULL,") .Append("[Data] TEXT NOT NULL,") .Append("[Tags] TEXT NULL,") .Append("[Version] UNIQUEIDENTIFIER NOT NULL,") .Append("[ResultCount] REAL NULL,") .Append("[MinuteBucket] INTEGER NULL,") .Append("[HourBucket] INTEGER NULL,") .Append("[DayBucket] INTEGER NULL)"))) { cmd.ExecuteNonQuery(); } } AddColumnIfMissing("MinuteBucket", "INTEGER", true); AddColumnIfMissing("HourBucket", "INTEGER", true); AddColumnIfMissing("DayBucket", "INTEGER", true); // Geo - point AddColumnIfMissing("Latitude", "TEXT", true); AddColumnIfMissing("Longitude", "TEXT", true); Logger.Debug("\tSuccess, AgentData table established"); } catch (Exception) { Logger.Debug("\tError during SQLite datafile/database creation..."); throw; } }
/// <summary> /// Try to put a data value into the cache. /// Does nothing if the cache is deactivated. /// </summary> /// <param name="ds">The ds.</param> /// <param name="connectionString">The connection string.</param> /// <param name="sql">The SQL.</param> /// <param name="cacheItemInfo">The cache item info.</param> public void PutDataValueToCache( object ds, SmartConnectionString connectionString, string sql, AdoNetCacheItemInformation cacheItemInfo ) { if ( CheckPutToCache( cacheItemInfo ) ) { string computedKey = ComputeKey( @"dv", cacheItemInfo, connectionString, sql ); lock ( thisLock ) { // Clean before, if applicable. RemoveAll( sql, cacheItemInfo ); // Put to cache. DoPutDataValueToCache( computedKey, ds, cacheItemInfo ); } } }
/// <summary> /// Try to put a data value into the cache. /// Does nothing if the cache is deactivated. /// </summary> /// <param name="ds">The ds.</param> /// <param name="connectionString">The connection string.</param> /// <param name="spName">Name of the sp.</param> /// <param name="spParameters">The sp parameters.</param> /// <param name="cacheItemInfo">The cache item info.</param> public void PutDataValueToCache( object ds, SmartConnectionString connectionString, string spName, ICollection spParameters, AdoNetCacheItemInformation cacheItemInfo ) { if ( CheckPutToCache( cacheItemInfo ) ) { string pseudoSql = AdoNetBaseHelper< OleDbCommand, OleDbCommandBuilder, OleDbConnection, OleDbDataAdapter, OleDbParameter, AdoNetOleDBParamCollection>. QuickCreatePseudoSqlFromSP( spName, spParameters ); PutDataValueToCache( ds, connectionString, pseudoSql, cacheItemInfo ); } }
/// <summary> /// Try to read a data value from the cache. /// </summary> /// <param name="connectionString">The connection string.</param> /// <param name="sql">The SQL.</param> /// <param name="cacheItemInfo">The cache item info.</param> /// <returns> /// Returns NULL if not found or cache is deactivated. /// </returns> public object GetDataValueFromCache( SmartConnectionString connectionString, string sql, AdoNetCacheItemInformation cacheItemInfo ) { object result; if ( CheckCanGetFromCache( cacheItemInfo ) ) { string computedKey = ComputeKey( @"dv", cacheItemInfo, connectionString, sql ); result = DoGetDataValueFromCache( computedKey, cacheItemInfo ); } else { result = null; } // -- if ( result == null ) { totalMissCount++; LogCentral.Current.LogDebug( string.Format( @"AdoNetCache data value CACHE MISS ({0} hits, {1} misses totally).", totalHitCount, totalMissCount ) ); } else { totalHitCount++; LogCentral.Current.LogDebug( string.Format( @"AdoNetCache data value CACHE HIT ({0} hits, {1} misses totally).", totalHitCount, totalMissCount ) ); } // -- return result; }
// ------------------------------------------------------------------ #endregion #region Caching Data values. // ------------------------------------------------------------------ /// <summary> /// Try to read a data value from the cache. /// </summary> /// <param name="connectionString">The connection string.</param> /// <param name="spName">Name of the sp.</param> /// <param name="spParameters">The sp parameters.</param> /// <param name="cacheItemInfo">The cache item info.</param> /// <returns> /// Returns NULL if not found or cache is deactivated. /// </returns> public object GetDataValueFromCache( SmartConnectionString connectionString, string spName, ICollection spParameters, AdoNetCacheItemInformation cacheItemInfo ) { if ( CheckCanGetFromCache( cacheItemInfo ) ) { string pseudoSql = AdoNetBaseHelper< OleDbCommand, OleDbCommandBuilder, OleDbConnection, OleDbDataAdapter, OleDbParameter, AdoNetOleDBParamCollection>. QuickCreatePseudoSqlFromSP( spName, spParameters ); return GetDataValueFromCache( connectionString, pseudoSql, cacheItemInfo ); } else { return null; } }
/// <summary> /// Try to put a DataTable into the cache. /// Does nothing if the cache is deactivated. /// </summary> /// <param name="ds">The ds.</param> /// <param name="connectionString">The connection string.</param> /// <param name="sql">The SQL.</param> /// <param name="startIndex">The start index.</param> /// <param name="maxCount">The max count.</param> /// <param name="cacheItemInfo">The cache item info.</param> public void PutDataTableToCache( DataTable ds, SmartConnectionString connectionString, string sql, int startIndex, int maxCount, AdoNetCacheItemInformation cacheItemInfo ) { if ( CheckPutToCache( cacheItemInfo ) ) { string computedKey = ComputeKey( @"dt", cacheItemInfo, connectionString, sql, startIndex, maxCount ); lock ( thisLock ) { // Clean before, if applicable. RemoveAll( sql, cacheItemInfo ); // Put to cache. DoPutDataTableToCache( computedKey, ds, cacheItemInfo ); } } }
/// <summary> /// Try to read a DataTable from the cache. /// </summary> /// <param name="connectionString">The connection string.</param> /// <param name="sql">The SQL.</param> /// <param name="startIndex">The start index.</param> /// <param name="maxCount">The max count.</param> /// <param name="cacheItemInfo">The cache item info.</param> /// <returns> /// Returns NULL if not found or cache is deactivated. /// </returns> public DataTable GetDataTableFromCache( SmartConnectionString connectionString, string sql, int startIndex, int maxCount, AdoNetCacheItemInformation cacheItemInfo ) { DataTable result; if ( CheckCanGetFromCache( cacheItemInfo ) ) { string computedKey = ComputeKey( @"dt", cacheItemInfo, connectionString, sql, startIndex, maxCount ); result = DoGetDataTableFromCache( computedKey, cacheItemInfo ); } else { result = null; } // -- if ( result == null ) { totalMissCount++; LogCentral.Current.LogDebug( string.Format( @"AdoNetCache DataTable CACHE MISS ({0} hits, {1} misses totally).", totalHitCount, totalMissCount ) ); } else { totalHitCount++; LogCentral.Current.LogDebug( string.Format( @"AdoNetCache DataTable CACHE HIT ({0} hits, {1} misses totally).", totalHitCount, totalMissCount ) ); } // -- return result; }
/// <summary> /// Makes a unique key from the given spParameters. /// </summary> /// <param name="prefix">The prefix.</param> /// <param name="cacheItemInfo">The cache item info.</param> /// <param name="connectionString">The connection string.</param> /// <param name="sql">The SQL.</param> /// <returns></returns> private static string ComputeKey( string prefix, AdoNetCacheItemInformation cacheItemInfo, SmartConnectionString connectionString, string sql ) { StringBuilder keyComputer = new StringBuilder(); keyComputer.Length = 0; keyComputer.Append( prefix ); keyComputer.Append( connectionString ); keyComputer.Append( sql ); return StringHelper.GenerateHash( keyComputer.ToString() ); }