/// <summary>
/// Check whether if a column matches a selection criteria or not.
/// </summary>
public static bool IsSelected(IDbTableBinding binding, IDbColumnBinding column, DbColumnsSelection selection)
{
switch (selection)
{
case DbColumnsSelection.None:
return(false);
case DbColumnsSelection.AutoInc:
return(column.IsAutoIncrement);
case DbColumnsSelection.NonAutoInc:
return(!column.IsAutoIncrement);
case DbColumnsSelection.PrimaryKey:
return(IsInPrimaryKey(binding, column));
case DbColumnsSelection.NonPrimaryKey:
return(!IsInPrimaryKey(binding, column));
case DbColumnsSelection.UniqueKey:
return(IsInUniqueKey(binding, column));
case DbColumnsSelection.All:
default:
return(true);
}
}
/// <summary>
/// Constructor.
/// </summary>
protected DbCreateStoredProcedureTemplate(IDbSetup setup, Type tableTemplate, StoredProcedureFunction function) : base(setup)
{
TableTemplate = tableTemplate;
Binding = setup.GetTableBinding(tableTemplate);
SqlTableName = Binding.GetSqlTableName();
SqlSpName = Binding.GetSqlTableSpName(function);
}
/// <summary>
/// Get an aggregated text from a columns selection.
/// </summary>
public static string AggregateText(this IDbTableBinding binding, DbColumnsSelection selection, string separator, Func <IDbColumnBinding, string> action)
{
if (selection != DbColumnsSelection.None)
{
return(binding.Columns.Where(c => IsSelected(binding, c, selection)).AggregateText(separator, action));
}
return(string.Empty);
}
/// <summary>
/// Get a column binding.
/// </summary>
public static IDbColumnBinding GetColumn(this IDbTableBinding binding, DbColumnsSelection selection)
{
var col = binding.FindColumn(selection);
if (col == null)
{
throw new ArgumentException($"Invalid column of kind {selection} in table [{binding.TableName}]");
}
return(col);
}
/// <summary>
/// Get a column binding.
/// </summary>
public static IDbColumnBinding GetColumn(this IDbTableBinding binding, string propertyName)
{
var col = binding.FindColumn(propertyName);
if (col == null)
{
throw new ArgumentException($"Invalid column {propertyName} in table [{binding.TableName}]");
}
return(col);
}
/// <summary>
/// Load results returned from stored procedure.
/// </summary>
public static bool LoadResults(this IDbTableBinding binding, IDbDataAccess db, DbDataReader reader, object instance)
{
DbHelper.SetDbRef(instance, db);
reader.Read();
if (reader.HasRows)
{
binding.Columns?.ForEach(c => c.GetOutputField(reader, instance));
return(true);
}
return(false);
}
/// <summary>
/// Check foreign key validity.
/// </summary>
private static void CheckForeignKey(this IDbTableBinding binding, IDbTableForeignKeyAttribute fk)
{
var binding2 = binding.Setup.GetTableBinding(fk.PrimaryTableTemplate);
if (binding2.PrimaryKey == null)
{
throw new ArgumentException($"Invalid foreign key definition in table [{binding.TableName}], remote table has no primary key");
}
if (binding2.PrimaryKey.PropertyNames.Length != fk.PropertyNames.Length)
{
throw new ArgumentException($"Invalid foreign key definition in table [{binding.TableName}], local fields count [{fk.PropertyNames.Length}] do not match with foreign fields count [{binding2.PrimaryKey.PropertyNames.Length}]");
}
}
/// <summary>
/// Creates an instance of a column binding.
/// </summary>
public static IDbColumnBinding CreateInstance(IDbSetup setup, PropertyInfo propInfo, DbColumnAttribute attr, IDbTableBinding table)
{
return(TypeHelper.CreateInstance(typeof(DbColumnBinding <,>), new[] { propInfo.DeclaringType, propInfo.PropertyType }, setup, propInfo, attr, table) as IDbColumnBinding);
}
/// <summary>
/// Get the list of columns defined in a key.
/// </summary>
public static string AggregateText(this IDbTableBinding binding, IDbTableKeyAttribute key, string separator, Func <IDbColumnBinding, string> action)
{
return(binding.AggregateText(key.PropertyNames, separator, action));
}
private string GetTableConstraints(IDbTableBinding table)
{
var str = new StringBuilder();
if (table.PrimaryKey != null)
{
var columnsList = table.AggregateText(table.PrimaryKey, ", ", c => c.GetSqlColumnName());
// [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...) [index_option] ...
str.Append(",\n");
str.Append($"{ConstraintKey} {PrimaryKeyKey} ( {columnsList} )");
}
if (table.ForeignKeys.Count > 0)
{
table.CheckConstraints();
foreach (var fk in table.ForeignKeys)
{
var columnsName = table.AggregateText(fk, "_", c => c.ColumnName);
var columnsList = table.AggregateText(fk, ", ", c => c.GetSqlColumnName());
var pkBinding = Setup.GetTableBinding(fk.PrimaryTableTemplate);
var primaryList = pkBinding.AggregateText(DbColumnsSelection.PrimaryKey, ", ", c => c.GetSqlColumnName());
// [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name,...)
// REFERENCES tbl_name (key_part,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option]
var name = fk.DbName ?? $"fk_{TableName}_{columnsName}";
str.Append(",\n");
str.Append($"{ConstraintKey} {name} {ForeignKeyKey} ( {columnsList} ) ");
str.Append($"{ReferecencesKey} {pkBinding.GetSqlTableName()} ( {primaryList} ) ON DELETE NO ACTION ON UPDATE NO ACTION");
// {INDEX|KEY} [index_name] [index_type] (key_part,...) [index_option] ...
str.Append(",\n");
str.Append($"{KeyKey} fk_{TableName}_{columnsName}_idx ( {columnsList} )");
}
}
if (table.UniqueKeys.Count > 0)
{
foreach (var uk in table.UniqueKeys)
{
var columnsName = table.AggregateText(uk, "_", c => c.ColumnName);
var columnsList = table.AggregateText(uk, ", ", c => c.GetSqlColumnName());
// [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (key_part,...) [index_option] ...
var name = uk.DbName ?? $"uk_{TableName}_{columnsName}_idx";
str.Append(",\n");
str.Append($"{ConstraintKey} {UniqueKeyKey} {name} ( {columnsList} )");
}
}
if (table.Indexes.Count > 0)
{
foreach (var idx in table.Indexes)
{
var columnsName = table.AggregateText(idx, "_", c => c.ColumnName);
var columnsList = table.AggregateText(idx, ", ", c => c.GetSqlColumnName());
// {INDEX|KEY} [index_name] [index_type] (key_part,...) [index_option] ...
var name = idx.DbName ?? $"{TableName}_{columnsName}_idx";
str.Append(",\n");
str.Append($"{IndexKey} {name} ( {columnsList} )");
}
}
return(str.ToString());
}
/// <summary>
/// Finds a column binding.
/// </summary>
public static IDbColumnBinding FindColumn(this IDbTableBinding binding, DbColumnsSelection selection)
{
return(binding.Columns?.FirstOrDefault(c => IsSelected(binding, c, selection)));
}
/// <summary>
/// Get an array of column names.
/// </summary>
public static string AggregateText(this IDbTableBinding binding, string[] propertyNames, string separator, Func <IDbColumnBinding, string> action)
{
return(propertyNames.Select(n => binding.GetColumn(n)).AggregateText(separator, action));
}
/// <summary>
/// Check if a table has columns of a certain type.
/// </summary>
public static bool HasColumns(this IDbTableBinding binding, DbColumnsSelection selection)
{
return(binding.Columns.Any(c => IsSelected(binding, c, selection)));
}
/// <summary>
/// Get table stored procedure name as needed to be included in a SQL sentence.
/// </summary>
public static string GetSqlTableSpName(this IDbTableBinding binding, StoredProcedureFunction spFunc)
{
var spName = binding.GetTableSpName(spFunc);
return(binding.Setup.Naming.GetSqlSpName(spName));
}
/// <summary>
/// Check constraints consistency.
/// </summary>
public static void CheckConstraints(this IDbTableBinding binding)
{
binding.ForeignKeys?.ForEach(fk => binding.CheckForeignKey(fk));
}
/// <summary>
/// Finds a column binding.
/// </summary>
public static IDbColumnBinding FindColumn(this IDbTableBinding binding, string propertyName)
{
return(binding.Columns?.FirstOrDefault(i => i.PropertyName == propertyName));
}
/// <summary>
/// Get table stored procedure name.
/// </summary>
public static string GetTableSpName(this IDbTableBinding binding, StoredProcedureFunction spFunc)
{
return(binding.Setup.Naming.GetStoredProcedureName(binding.TableName, spFunc));
}
/// <summary>
/// Set table stored procedure parameters.
/// </summary>
public static void SetTableSpInputParameters <T>(this IDbTableBinding binding, DbCommand cmd, T instance, StoredProcedureFunction spFunc)
{
var columns = DbArchitectureHelper.GetTableSpParameters(spFunc);
binding.MapColumns(columns, c => c.SetInputParameters(cmd, instance));
}
private static bool IsInUniqueKey(IDbTableBinding binding, IDbColumnBinding column)
{
return(column.KeyConstraint == DbKeyConstraint.UniqueKey ||
binding.UniqueKeys.Count == 1 && binding.UniqueKeys.Any(uk => uk.PropertyNames.Contains(column.PropertyName)));
}
private static bool IsInPrimaryKey(IDbTableBinding binding, IDbColumnBinding column)
{
return(column.KeyConstraint == DbKeyConstraint.PrimaryKey ||
column.KeyConstraint == DbKeyConstraint.PrimaryForeignKey ||
binding.PrimaryKey.PropertyNames.Contains(column.PropertyName));
}
/// <summary>
/// Gets a table name.
/// </summary>
public static string GetSqlTableName(this IDbTableBinding table)
{
return(table.Setup.Naming.GetSqlTableName(table.TableName));
}
/// <summary>
/// Maps an action to every column.
/// </summary>
public static void MapColumns(this IDbTableBinding binding, DbColumnsSelection selection, Action <IDbColumnBinding> action)
{
binding.Columns.Where(c => IsSelected(binding, c, selection)).ForEach(c => action(c));
}
