private List <SqlObjectName> LoadDependencies(DatabaseConfig config, SqlObjectName viewName)
{
List <SqlObjectName> result = new List <SqlObjectName>();
config.DatabaseConnectionString.WithReader(
$@"SELECT DISTINCT referenced_schema_name, referenced_entity_name, obj.type
FROM
sys.dm_sql_referenced_entities('{viewName}', 'OBJECT') e
LEFT JOIN
sys.objects obj
ON
obj.object_id = referenced_id",
reader =>
{
while (reader.Read())
{
var objectName = new SqlObjectName(reader[1] as string, reader[0] as string);
if (((string)reader[2]).Trim() == "V")
{
result.AddRange(LoadDependencies(config, objectName));
}
else
{
result.Add(objectName);
}
}
});
return(result);
}
public TableObserver(Database db, SqlObjectName sqlObjectName, string[] primaryKeyColumns, bool memoryTrigger)
{
_db = db;
SqlObjectName = sqlObjectName;
Schema = new RowSchema(db.Config.DatabaseConnectionString, sqlObjectName, primaryKeyColumns);
_insertTriggerName = string.Format(db.Config.TriggerNameTemplate, sqlObjectName.Schema, "insert", sqlObjectName.Name);
_deleteTriggerName = string.Format(db.Config.TriggerNameTemplate, sqlObjectName.Schema, "delete", sqlObjectName.Name);
_updateTriggerName = string.Format(db.Config.TriggerNameTemplate, sqlObjectName.Schema, "update", sqlObjectName.Name);
SetupDatabaseSchema(memoryTrigger);
}
public SqlMergedViewObserver(Database db, SqlObjectName viewName, string primaryKeyColumn, params string[] primaryKeyColumns)
{
primaryKeyColumns = primaryKeyColumns ?? new string[0];
primaryKeyColumns = new[] { primaryKeyColumn }.Union(primaryKeyColumns).ToArray();
_db = db;
_keyColumns = primaryKeyColumns;
ViewName = viewName;
CacheTableName = string.Format(_db.Config.ViewCacheTableNameTemplate, viewName.Schema, viewName.Name);
var dependencies = LoadDependencies(db.Config, viewName).Select(db.GetOrAddTable).ToList();
db.Config.DatabaseConnectionString.CreateViewCache(viewName, CacheTableName, primaryKeyColumns);
var mergeThread = SetupDependencyEvent(dependencies);
CacheTable = SetupCacheTable(db, CacheTableName, primaryKeyColumns);
_disposeHelper.Attach(() =>
{
mergeThread.Abort();
mergeThread.Join();
});
_disposeHelper.Attach(CacheTable);
_disposeHelper.Attach(() =>
{
_db.Config.DatabaseConnectionString.WithConnection(con =>
{
using (var command = con.CreateCommand())
{
command.CommandText = $"DROP TABLE {CacheTableName}";
command.ExecuteNonQuery();
}
});
});
mergeThread.Start();
}
/// <summary>
/// Initialize a new instance of <see cref="TableObserver"/>
/// </summary>
/// <param name="db">The database that contains table</param>
/// <param name="sqlObjectName">The name of the table</param>
public TableObserver(Database db, SqlObjectName sqlObjectName)
{
_db = db;
SqlObjectName = sqlObjectName;
Schema = new RowSchema(db.Config.DatabaseConnectionString, sqlObjectName);
_insertTriggerName = string.Format(db.Config.TriggerNameTemplate, sqlObjectName.Schema, "insert", sqlObjectName.Name);
_deleteTriggerName = string.Format(db.Config.TriggerNameTemplate, sqlObjectName.Schema, "delete", sqlObjectName.Name);
_updateTriggerName = string.Format(db.Config.TriggerNameTemplate, sqlObjectName.Schema, "update", sqlObjectName.Name);
bool isMemoryTable = false;
db.Config.DatabaseConnectionString.WithConnection(con =>
{
using (var command = con.CreateCommand())
{
command.CommandText = "SELECT is_memory_optimized FROM sys.tables WHERE name = @name And schema_id = schema_id(@schema)";
command.Parameters.AddWithValue("name", sqlObjectName.Name);
command.Parameters.AddWithValue("schema", sqlObjectName.Schema);
isMemoryTable = (bool)command.ExecuteScalar();
}
});
SetupDatabaseSchema(isMemoryTable);
}
internal IReference <TableObserver> GetOrAddTable(SqlObjectName sqlObjectName, string[] primaryKeyColumns, bool isMemoryTable)
{
return(_disposeHelper.DoOrThrow(() => _tables.GetOrCreate(sqlObjectName, t => new TableObserver(this, sqlObjectName, primaryKeyColumns, isMemoryTable))));
}
public IReference <TableObserver> GetOrAddTable(SqlObjectName sqlObjectName)
{
return(_disposeHelper.DoOrThrow(() => _tables.GetOrCreate(sqlObjectName, t => new TableObserver(this, sqlObjectName))));
}
