/// <summary>
/// Loads the table and column schema details from the database.
/// </summary>
/// <param name="db">The <see cref="DatabaseBase"/>.</param>
/// <param name="refDataSchema">The reference data schema.</param>
/// <param name="autoSecurity">Indicates whether the UserRole security should be automatically applied.</param>
/// <param name="skipSqlSpecific">Indicates whether to skip the Microsoft SQL Server specific metadata queries.</param>
public static List <Table> LoadTablesAndColumns(DatabaseBase db, string refDataSchema = null, bool autoSecurity = false, bool skipSqlSpecific = false)
{
var tables = new List <Table>();
Table table = null;
db.SqlStatement(ResourceManager.GetResourceContent("SelectTableAndColumns.sql")).SelectQuery((dr) =>
{
var ct = TableMapper.Default.MapFromDb(dr, Mapper.OperationTypes.Get);
if (table == null || table.Schema != ct.Schema || table.Name != ct.Name)
{
tables.Add(table = ct);
}
table.Columns.Add(ColumnMapper.Default.MapFromDb(dr, Mapper.OperationTypes.Get));
if (autoSecurity && table.Schema != refDataSchema)
{
table.UserRole = $"{table.Schema}.{table.Name}";
}
});
// Configure all the single column primary and unique constraints.
foreach (var pks in db.SqlStatement(ResourceManager.GetResourceContent("SelectTablePrimaryKey.sql")).SelectQuery((dr) =>
{
return(new
{
ConstraintName = dr.GetValue <string>("CONSTRAINT_NAME"),
TableSchema = dr.GetValue <string>("TABLE_SCHEMA"),
TableName = dr.GetValue <string>("TABLE_NAME"),
TableColumnName = dr.GetValue <string>("COLUMN_NAME"),
IsPrimaryKey = dr.GetValue <string>("CONSTRAINT_TYPE").StartsWith("PRIMARY", StringComparison.InvariantCultureIgnoreCase),
});
}).GroupBy(x => x.ConstraintName))
{
// Only single column unique columns are supported.
if (pks.Count() > 1 && !pks.First().IsPrimaryKey)
{
continue;
}
// Set the column flags as appropriate.
foreach (var pk in pks)
{
var col = (from t in tables
from c in t.Columns
where t.Schema == pk.TableSchema && t.Name == pk.TableName && c.Name == pk.TableColumnName
select c).Single();
if (pk.IsPrimaryKey)
{
col.IsPrimaryKey = true;
col.IsIdentity = col.DefaultValue != null;
}
else
{
col.IsUnique = true;
}
}
}
if (!skipSqlSpecific)
{
// Configure all the single column foreign keys.
foreach (var fks in db.SqlStatement(ResourceManager.GetResourceContent("SelectTableForeignKeys.sql")).SelectQuery((dr) =>
{
return(new
{
ConstraintName = dr.GetValue <string>("FK_CONSTRAINT_NAME"),
TableSchema = dr.GetValue <string>("FK_SCHEMA_NAME"),
TableName = dr.GetValue <string>("FK_TABLE_NAME"),
TableColumnName = dr.GetValue <string>("FK_COLUMN_NAME"),
ForeignSchema = dr.GetValue <string>("UQ_SCHEMA_NAME"),
ForeignTable = dr.GetValue <string>("UQ_TABLE_NAME"),
ForiegnColumn = dr.GetValue <string>("UQ_COLUMN_NAME")
});
}).GroupBy(x => x.ConstraintName).Where(x => x.Count() == 1))
{
var fk = fks.Single();
var col = (from t in tables
from c in t.Columns
where t.Schema == fk.TableSchema && t.Name == fk.TableName && c.Name == fk.TableColumnName
select c).Single();
col.ForeignSchema = fk.ForeignSchema;
col.ForeignTable = fk.ForeignTable;
col.ForeignColumn = fk.ForiegnColumn;
col.IsForeignRefData = col.ForeignSchema == refDataSchema;
}
db.SqlStatement(ResourceManager.GetResourceContent("SelectTableIdentityColumns.sql")).SelectQuery((dr) =>
{
var t = tables.Single(x => x.Schema == dr.GetValue <string>("TABLE_SCHEMA") && x.Name == dr.GetValue <string>("TABLE_NAME"));
var c = t.Columns.Single(x => x.Name == dr.GetValue <string>("COLUMN_NAME"));
c.IsIdentity = true;
c.IdentitySeed = 1;
c.IdentityIncrement = 1;
});
db.SqlStatement(ResourceManager.GetResourceContent("SelectTableAlwaysGeneratedColumns.sql")).SelectQuery((dr) =>
{
var t = tables.Single(x => x.Schema == dr.GetValue <string>("TABLE_SCHEMA") && x.Name == dr.GetValue <string>("TABLE_NAME"));
var c = t.Columns.Single(x => x.Name == dr.GetValue <string>("COLUMN_NAME"));
t.Columns.Remove(c);
});
db.SqlStatement(ResourceManager.GetResourceContent("SelectTableGeneratedColumns.sql")).SelectQuery((dr) =>
{
var t = tables.Single(x => x.Schema == dr.GetValue <string>("TABLE_SCHEMA") && x.Name == dr.GetValue <string>("TABLE_NAME"));
var c = t.Columns.Single(x => x.Name == dr.GetValue <string>("COLUMN_NAME"));
c.IsComputed = true;
});
}
// Auto-determine reference data relationships even where no foreign key defined.
foreach (var t in tables)
{
foreach (var col in t.Columns.Where(x => !x.IsForeignRefData && x.Name.Length > 2 && x.Name.EndsWith("Id")))
{
var rt = tables.Where(x => x.Name != t.Name && x.Name == col.Name.Substring(0, col.Name.Length - 2) && x.Schema == refDataSchema).SingleOrDefault();
if (rt != null)
{
col.ForeignSchema = rt.Schema;
col.ForeignTable = rt.Name;
col.ForeignColumn = rt.Columns.Where(x => x.IsPrimaryKey).First().Name;
col.IsForeignRefData = col.ForeignSchema == refDataSchema;
}
}
}
return(tables);
}