// TODO Duplicate from MariaDB
private List <ForeignKeyInfo> GetForeignKeyInfo(List <List <string> > fkData, string tableName)
{
var list = new List <ForeignKeyInfo>();
foreach (var row in fkData)
{
try
{
ForeignKeyInfo info = new ForeignKeyInfo()
{
FromTable = tableName, //reader.GetString(2),
FromTableFieldName = row.ElementAt(3),
ToTable = row.ElementAt(5),
ToTableFieldName = row.ElementAt(6),
};
//0 0 DiscordServers DiscordServerId DiscordServerId NO ACTION CASCADE NONE
list.Add(info);
}
catch (Exception ex)
{
//_logger.LogError(ex, ex.Message);
}
}
return(list);
}
private string getEntityPropertyByReferencesForeignKey(ForeignKeyInfo foreignKey, All all)
{
//string baseColumnName = foreignKey.BaseColumnName;
//if (foreignKey.BaseColumnName.EndsWith("id", StringComparison.OrdinalIgnoreCase))
// baseColumnName = foreignKey.BaseColumnName.Substring(0, foreignKey.BaseColumnName.IndexOf("id", StringComparison.OrdinalIgnoreCase));
return($"\t\tpublic virtual ICollection<{foreignKey.BaseTableName}> {foreignKey.ReferencesPropertyName(all)} {{ get; set; }}");
}
protected virtual void CreateForeignKeyCore(ForeignKeyInfo fk)
{
if (fk.ConstraintName != null)
{
Put("^constraint %i ", fk.ConstraintName);
}
Put("^foreign ^key (");
ColumnRefs(fk.Columns);
Put(") ^references %f", fk.RefTableFullName);
if (fk.RefColumns != null)
{
WriteRaw("(");
ColumnRefs(fk.RefColumns);
WriteRaw(")");
}
string ondelete = fk.OnDeleteAction.SqlName();
string onupdate = fk.OnUpdateAction.SqlName();
if (ondelete != null)
{
Put(" ^on ^delete %k", ondelete);
}
if (onupdate != null)
{
Put(" ^on ^update %k", onupdate);
}
}
/// <inheritdoc/>
protected override IPathNode VisitForeignKey(ForeignKey key)
{
var referencingTable = targetModel.Tables[resolver.GetNodeName(key.Owner)];
var referencingColumns = new List <StorageColumnInfo>();
foreach (var refColumn in key.Columns)
{
referencingColumns.Add(referencingTable.Columns[refColumn.Name]);
}
var foreignKeyInfo = new ForeignKeyInfo(referencingTable, key.Name)
{
OnUpdateAction = ConvertReferentialAction(key.OnUpdate),
OnRemoveAction = ConvertReferentialAction(key.OnDelete)
};
var referencedTable = targetModel.Tables[resolver.GetNodeName(key.ReferencedTable)];
foreignKeyInfo.PrimaryKey = referencedTable.PrimaryIndex;
foreach (var column in referencingColumns)
{
new ForeignKeyColumnRef(foreignKeyInfo, column);
}
return(foreignKeyInfo);
}
private static ForeignKeyInfo GetForeignKey(SqlConnection con, string senderTable, string sentColumn)
{
var cmdForeignKeyReferences = new SqlCommand
{
CommandText = MyQueries.GetForeignKeyQuery(senderTable, sentColumn),
CommandType = CommandType.Text,
Connection = con
};
var drForeignKeyReferences = cmdForeignKeyReferences.ExecuteReader();
ForeignKeyInfo foreignkey = null;
if (drForeignKeyReferences.Read())
{
foreignkey = new ForeignKeyInfo
{
FkName = drForeignKeyReferences[0].ToString(),
TableName = drForeignKeyReferences[1].ToString(),
ColumnName = drForeignKeyReferences[2].ToString(),
ReferencedTable = drForeignKeyReferences[3].ToString(),
ReferencedColumn = drForeignKeyReferences[4].ToString()
};
}
drForeignKeyReferences.Close();
return(foreignkey);
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="column"></param>
/// <param name="schema"></param>
/// <returns></returns>
public EfDbCodeTableColumnBuilder ForeignKey(string table, string column, string schema = null)
{
var foreignKey = new ForeignKeyInfo();
foreignKey.Column = _column;
foreignKey.Table = TableBuilder.TableInfo;
var databaseTablesManager = TableBuilder.ServicesContainer.GetService <IDatabaseTablesManager>();
var referenceTable = new TableInfo();
referenceTable.Name = table;
referenceTable.Schema = schema ?? databaseTablesManager.DefaultSchema;
var referenceColumn = new ColumnInfo();
referenceColumn.Name = column;
referenceColumn.Table = referenceTable;
referenceTable.Columns.Add(referenceColumn);
referenceTable.PrimaryKey = new PrimaryKeyInfo();
referenceTable.PrimaryKey.PrimaryColumn = referenceColumn;
referenceTable.PrimaryKey.Table = referenceTable;
foreignKey.ReferenceTable = referenceTable;
foreignKey.ReferenceColumn = referenceColumn;
TableBuilder.TableInfo.ForeignKeys.Add(foreignKey);
return(this);
}
public static ForeignKeyInfoCollection GetForeignKeyData(this OleDbConnection connection, string tableName)
{
var foreignKeyRestrictions = new object[] { null, null, null, null, null, tableName };
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
DataTable foreignKeySchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, foreignKeyRestrictions);
if (!alreadyOpen)
{
connection.Close();
}
var foreignKeyData = new ForeignKeyInfoCollection();
foreach (DataRow row in foreignKeySchema.Rows)
{
var fkInfo = new ForeignKeyInfo
{
ForeignKeyColumn = row.Field <string>("FK_COLUMN_NAME"),
ForeignKeyName = row.Field <string>("FK_NAME"),
ForeignKeyTable = row.Field <string>("FK_TABLE_NAME"),
PrimaryKeyColumn = row.Field <string>("PK_COLUMN_NAME"),
PrimaryKeyName = row.Field <string>("PK_NAME"),
PrimaryKeyTable = row.Field <string>("PK_TABLE_NAME")
};
foreignKeyData.Add(fkInfo);
}
return(foreignKeyData);
}
// help
// list
// into <table>
// query <type> (select, insert, update)
// draw image?
/* [Command("help")]
* public async Task SqlTableHelp()
* {
*
* }*/
private List <ForeignKeyInfo> GetForeignKeyInfo(DbCommand command, string tableName)
{
var list = new List <ForeignKeyInfo>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
try
{
ForeignKeyInfo info = new ForeignKeyInfo()
{
FromTable = tableName, //reader.GetString(2),
FromTableFieldName = reader.GetString(1),
ToTable = reader.GetString(2),
ToTableFieldName = reader.GetString(3),
};
//0 0 DiscordServers DiscordServerId DiscordServerId NO ACTION CASCADE NONE
list.Add(info);
}
catch (Exception ex)
{
_logger.LogError(ex, ex.Message);
}
}
}
return(list);
}
public async Task <IEnumerable <ForeignKeyInfo> > GetForeignKeysAsync()
{
var foreignKeys = new List <ForeignKeyInfo>();
var cmd = CommandBuilder.BuildGetForeignKeysCommand(Connection);
await Connection.OpenAsync();
using var reader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
while (await reader.ReadAsync())
{
var item = new ForeignKeyInfo
{
Name = reader.GetString(0),
PrimaryTableId = reader.GetInt32(1),
PrimaryColumnId = reader.GetInt32(2),
ForeignTableId = reader.GetInt32(3),
ForeignSchemaName = reader.GetString(4),
ForeignTableName = reader.GetString(5),
ForeignColumnId = reader.GetInt32(6),
ForeignColumnName = reader.GetString(7)
};
foreignKeys.Add(item);
}
return(foreignKeys);
}
/// <summary>
///
/// </summary>
/// <param name="databaseForeignKey"></param>
/// <param name="codeForeignKeys"></param>
protected virtual void MergeDatabaseTableForeignKey(ForeignKeyInfo databaseForeignKey, List <ForeignKeyInfo> codeForeignKeys)
{
var codeForeignKey = codeForeignKeys.FirstOrDefault(f => f.IsEqual(databaseForeignKey));
if (codeForeignKey == null)
{
DropForeignKey(databaseForeignKey);
}
}
/// <summary>
///
/// </summary>
/// <param name="codeForeignKey"></param>
/// <param name="databaseForeignKeys"></param>
protected virtual void MergeCodeTableForeignKey(ForeignKeyInfo codeForeignKey, List <ForeignKeyInfo> databaseForeignKeys)
{
var databaseForeignKey = databaseForeignKeys.FirstOrDefault(f => f.IsEqual(codeForeignKey));
if (databaseForeignKey == null)
{
CreateForeignKey(codeForeignKey);
}
}
internal override string CreateSaveList(TableInfo baseClass, ForeignKeyInfo fkInfo, EntityBase baseInstance, out TenorParameter[] parameters, out System.Data.DataTable data)
{
IList values = (IList)fkInfo.PropertyValue(baseInstance);
data = new System.Data.DataTable();
for (int i = 0; i < fkInfo.LocalManyToManyFields.Length; i++)
{
data.Columns.Add(fkInfo.LocalManyToManyFields[i], fkInfo.LocalFields[i].RelatedProperty.PropertyType);
}
for (int i = 0; i < fkInfo.ForeignManyToManyFields.Length; i++)
{
data.Columns.Add(fkInfo.ForeignManyToManyFields[i], fkInfo.ForeignFields[i].RelatedProperty.PropertyType);
}
for (int i = 0; i < values.Count; i++)
{
System.Data.DataRow row = data.NewRow();
for (int j = 0; j < fkInfo.LocalManyToManyFields.Length; j++)
{
row[fkInfo.LocalManyToManyFields[j]] = fkInfo.LocalFields[j].PropertyValue(baseInstance);
}
for (int j = 0; j < fkInfo.ForeignFields.Length; j++)
{
row[fkInfo.ForeignManyToManyFields[j]] = fkInfo.ForeignFields[j].PropertyValue(values[i]);
}
data.Rows.Add(row);
}
const string localParamPrefix = "local{0}";
List <TenorParameter> parame = new List <TenorParameter>();
string tableExpression = GetPrefixAndTable(fkInfo.ManyToManyTablePrefix, fkInfo.ManyToManyTable);
// generating delete statement
StringBuilder sql = new StringBuilder();
sql.Append(string.Format("DELETE FROM {0} WHERE ", tableExpression));
for (int i = 0; i < fkInfo.LocalManyToManyFields.Length; i++)
{
if (i > 0)
{
sql.Append(this.GetOperator(LogicalOperator.And));
}
sql.Append(this.CommandBuilder.QuoteIdentifier(fkInfo.LocalManyToManyFields[i]));
sql.Append(" = ");
sql.Append(this.ParameterIdentifier + string.Format(localParamPrefix, i));
TenorParameter p = new TenorParameter(string.Format(localParamPrefix, i), fkInfo.LocalFields[i].PropertyValue(baseInstance));
parame.Add(p);
}
parameters = parame.ToArray();
sql.AppendLine();
return(sql.ToString());
}
/// <summary>
///
/// </summary>
/// <param name="foreignKey"></param>
/// <param name="foreignKeyMetadata"></param>
/// <returns></returns>
private ColumnInfo GetReferenceColumn(ForeignKeyInfo foreignKey, ForeignKeyMetadata foreignKeyMetadata)
{
var referenceColumn = foreignKeyMetadata.ReferencedColumn;
if (string.IsNullOrEmpty(referenceColumn))
{
return(foreignKey?.ReferenceTable?.PrimaryKey?.PrimaryColumn);
}
return(foreignKey.ReferenceTable.GetColumn(referenceColumn));
}
public virtual void DropForeignKey(ForeignKeyInfo fk)
{
if (_dialectCaps.ExplicitDropConstraint)
{
PutCmd("^alter ^table %f ^drop ^foreign ^key %i", fk.OwnerTable, fk.ConstraintName);
}
else
{
DropConstraint(fk);
}
}
private static void CreateHierarchyForeignKey(TableInfo referencingTable, TableInfo referencedTable, StorageIndexInfo referencingIndex, string foreignKeyName)
{
var foreignKey = new ForeignKeyInfo(referencingTable, foreignKeyName)
{
PrimaryKey = referencedTable.PrimaryIndex,
OnRemoveAction = ReferentialAction.None,
OnUpdateAction = ReferentialAction.None
};
foreignKey.ForeignKeyColumns.Set(referencingIndex);
}
public virtual string ForeignKeyConstraintSyntax(ForeignKeyInfo foreignKeyInfo)
{
string cascadeDelete = (foreignKeyInfo.CascadeDelete) ? " ON DELETE CASCADE" : string.Empty;
return
($"CONSTRAINT {ApplyDelimiter(foreignKeyInfo.ConstraintName)} FOREIGN KEY(\r\n" +
$"\t{ApplyDelimiter(foreignKeyInfo.Child.ColumnName)}\r\n" +
$") REFERENCES {ApplyDelimiter(foreignKeyInfo.Parent.Schema)}.{ApplyDelimiter(foreignKeyInfo.Parent.TableName)} (\r\n" +
$"\t{ApplyDelimiter(foreignKeyInfo.Parent.ColumnName)}\r\n" +
$")" + cascadeDelete);
}
private IEnumerable <ForeignKeyInfo> GetForeignKeys(DataTable dataTable)
{
foreach (DataRow row in dataTable.Rows)
{
var foreignKey = new ForeignKeyInfo();
foreignKey.ReferencesTableName = getStringOrNull(row[0]);
foreignKey.ReferencesColumnName = getStringOrNull(row[1]);
foreignKey.BaseTableName = getStringOrNull(row[2]);
foreignKey.BaseColumnName = getStringOrNull(row[3]);
yield return(foreignKey);
}
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="sqlForeignKey"></param>
/// <returns></returns>
private ForeignKeyInfo ConvertToForeignKeyInfo(TableInfo table, SqlForeignKey sqlForeignKey)
{
var foreignKey = new ForeignKeyInfo();
foreignKey.Name = sqlForeignKey.ConstraintName;
foreignKey.Table = table;
foreignKey.Column = table.GetColumn(sqlForeignKey.ColumnName);
foreignKey.ReferenceTable = _tableInfos.FirstOrDefault(sqlForeignKey.IsReferenceTable);
foreignKey.ReferenceColumn = foreignKey.ReferenceTable?.Columns.FirstOrDefault(sqlForeignKey.IsReferenceColumn);
return(foreignKey);
}
public virtual Statement[] Render(AddForeignKeyChange change)
{
ForeignKeyInfo fk = change.ForeignKey;
string[] cols = fk.Columns.ToArray();
string[] refcols = fk.ReferencedColumns.ToArray();
string sql = string.Format("alter table {0} {1}",
GetQualifiedName(change.Table),
_dialect.GetAddForeignKeyConstraintString(fk.Name, cols, GetQualifiedName(change.Table.Schema, fk.ReferencedTable), refcols, true));
return(new Statement[] { new Statement(sql) });
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="foreignKey"></param>
protected virtual T ForeignKeyCommand <T>(ForeignKeyInfo foreignKey) where T : class, IForeignKeyCommand
{
var service = CommandServices.GetCommand <T>();
if (service == null)
{
return(null);
}
service.SetForeignKey(foreignKey);
Commands.Add(service);
return(service);
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="foreignKeyMetadata"></param>
/// <returns></returns>
private ForeignKeyInfo GetForeignKey(MetadataTableInfo table, ForeignKeyMetadata foreignKeyMetadata)
{
var foreignKey = new ForeignKeyInfo();
foreignKey.Name = foreignKeyMetadata.Name;
foreignKey.Column = table.GetColumn(foreignKeyMetadata.Column.Name);
foreignKey.Table = table;
foreignKey.ReferenceTable = GetReferenceTable(foreignKeyMetadata);
foreignKey.ReferenceColumn = GetReferenceColumn(foreignKey, foreignKeyMetadata);
MigrateManager.CorrectName(foreignKey);
return(foreignKey);
}
/// <summary>
///
/// </summary>
/// <param name="foreignKey"></param>
/// <param name="table"></param>
/// <returns></returns>
private ForeignKeyInfo GetForeignKey(IForeignKey foreignKey, EFTableInfo table)
{
var result = new ForeignKeyInfo();
result.Table = table;
result.Name = foreignKey.Relational().Name;
result.Column = table.GetColumn(foreignKey.GetColumnName());
result.ReferenceTable = GetReferenceTable(foreignKey);
result.ReferenceColumn = result.ReferenceTable.GetColumn(foreignKey.GetReferenceColumnName());
_migrateManager.CorrectName(result);
return(result);
}
public void ForeignKeyValidationTest()
{
storage.Validate();
var foreignKey = new ForeignKeyInfo(referencingTable, "ForeignKey");
AssertEx.Throws <AggregateException>(foreignKey.Validate);
foreignKey.PrimaryKey = foreignPrimary;
AssertEx.Throws <AggregateException>(foreignKey.Validate);
foreignKey.ForeignKeyColumns.Set(invalideReferencingIndex);
AssertEx.Throws <AggregateException>(foreignKey.Validate);
foreignKey.ForeignKeyColumns.Set(referencingIndex);
foreignKey.Validate();
}
private static void CreateReferenceForeignKey(TableInfo referencingTable, TableInfo referencedTable, FieldInfo referencingField, string foreignKeyName)
{
var foreignColumns = referencingField.Columns.Select(column => referencingTable.Columns[column.Name]).ToList();
var foreignKey = new ForeignKeyInfo(referencingTable, foreignKeyName)
{
PrimaryKey = referencedTable.PrimaryIndex,
OnRemoveAction = ReferentialAction.None,
OnUpdateAction = ReferentialAction.None
};
foreach (var foreignColumn in foreignColumns)
{
new ForeignKeyColumnRef(foreignKey, foreignColumn);
}
}
private static string CheckManyToOne(string receivedTable, string senderTable, string connectionString, out ForeignKeyInfo foreignKey)
{
using (var con = new SqlConnection(connectionString))
{
con.Open();
var cmdForeignKeyReferences = new SqlCommand
{
CommandText = MyQueries.GetForeignKeyQueryBetweenTablesQuery(senderTable, receivedTable),
CommandType = CommandType.Text,
Connection = con
};
var drForeignKeyReferences = cmdForeignKeyReferences.ExecuteReader();
ForeignKeyInfo foreignkey = null;
if (drForeignKeyReferences.Read())
{
foreignkey = new ForeignKeyInfo
{
FkName = drForeignKeyReferences[0].ToString(),
TableName = drForeignKeyReferences[1].ToString(),
ColumnName = drForeignKeyReferences[2].ToString(),
ReferencedTable = drForeignKeyReferences[3].ToString(),
ReferencedColumn = drForeignKeyReferences[4].ToString()
};
}
drForeignKeyReferences.Close();
var primaryKey = GetPrimaryKey(con, senderTable);
string result;
if (foreignkey != null)
{
result = "manytoone";
if (foreignkey.ColumnName == primaryKey.ColumnName)
{
result = (foreignkey.ColumnName == primaryKey.ColumnName) ? "onetoone" : "manytoone";
}
}
else
{
result = null;
}
foreignKey = foreignkey;
return(result);
}
}
/// <summary>
/// Gets the foreign key property for the specified source type and including type
/// using the configured <see cref="IForeignKeyPropertyResolver"/>.
/// </summary>
/// <param name="sourceType">The source type which should contain the foreign key property.</param>
/// <param name="includingType">The type of the foreign key relation.</param>
/// <param name="foreignKeyRelation">The foreign key relationship type.</param>
/// <returns>The foreign key property for <paramref name="sourceType"/> and <paramref name="includingType"/>.</returns>
public static PropertyInfo ForeignKeyProperty(Type sourceType, Type includingType, out ForeignKeyRelation foreignKeyRelation)
{
var key = $"{sourceType};{includingType}";
if (!_typeForeignKeyPropertyCache.TryGetValue(key, out var foreignKeyInfo))
{
// Resolve the property and relation.
var foreignKeyProperty = _foreignKeyPropertyResolver.ResolveForeignKeyProperty(sourceType, includingType, out foreignKeyRelation);
// Cache the info.
foreignKeyInfo = new ForeignKeyInfo(foreignKeyProperty, foreignKeyRelation);
_typeForeignKeyPropertyCache.TryAdd(key, foreignKeyInfo);
}
foreignKeyRelation = foreignKeyInfo.Relation;
LogReceived?.Invoke($"Resolved property '{foreignKeyInfo.PropertyInfo}' ({foreignKeyInfo.Relation}) as foreign key between '{sourceType}' and '{includingType}'");
return(foreignKeyInfo.PropertyInfo);
}
private static IRelation CreateForeignKeyMapping(Type entityType,
PropertyInfo propertyInfo,
ForeignKeyInfo foreignKeyInfo)
{
var objectMappings = CreateForeignKeyColumnMappings(foreignKeyInfo);
IRelation relation = new DefaultRelation(propertyInfo.Name,
foreignKeyInfo.Name,
entityType,
foreignKeyInfo.RelatedOjectType,
objectMappings,
foreignKeyInfo.UpdateRule,
foreignKeyInfo.DeleteRule,
foreignKeyInfo.ReverseRelation,
foreignKeyInfo.NonIdentifyingRelation,
foreignKeyInfo.FetchStrategy,
foreignKeyInfo.Nullable);
return(relation);
}
private void CreateForeignKey(ForeignKeyInfo foreignKeyInfo)
{
var referencingTable = FindTable(foreignKeyInfo.Parent);
var foreignKey = referencingTable.CreateForeignKey(foreignKeyInfo.Name);
foreignKey.OnUpdate = ConvertReferentialAction(foreignKeyInfo.OnUpdateAction);
foreignKey.OnDelete = ConvertReferentialAction(foreignKeyInfo.OnRemoveAction);
foreignKey.IsDeferrable = provider.Supports(ProviderFeatures.DeferrableConstraints);
foreignKey.IsInitiallyDeferred = foreignKey.IsDeferrable;
var referencingColumns = foreignKeyInfo.ForeignKeyColumns
.Select(cr => FindColumn(referencingTable, cr.Value.Name));
foreignKey.Columns.AddRange(referencingColumns);
var referencedTable = FindTable(foreignKeyInfo.PrimaryKey.Parent);
var referencedColumns = foreignKeyInfo.PrimaryKey.KeyColumns
.Select(cr => FindColumn(referencedTable, cr.Value.Name));
foreignKey.ReferencedTable = referencedTable;
foreignKey.ReferencedColumns.AddRange(referencedColumns);
}
private static string CheckOneToOne(string receivedTable, string senderTable, string connectionString, out ForeignKeyInfo oneToOneforeignkey)
{
using (var con = new SqlConnection(connectionString))
{
con.Open();
var cmdOneToOneRelationship = new SqlCommand
{
CommandText = MyQueries.GetForeignKeyQueryBetweenTablesQuery(receivedTable, senderTable),
CommandType = CommandType.Text,
Connection = con
};
var primaryKey = GetPrimaryKey(con, receivedTable);
var drOneToOneRelationship = cmdOneToOneRelationship.ExecuteReader();
var oneToOneForeignKey = new ForeignKeyInfo();
if (drOneToOneRelationship.Read())
{
oneToOneForeignKey.FkName = drOneToOneRelationship[0].ToString();
oneToOneForeignKey.TableName = drOneToOneRelationship[1].ToString();
oneToOneForeignKey.ColumnName = drOneToOneRelationship[2].ToString();
oneToOneForeignKey.ReferencedTable = drOneToOneRelationship[3].ToString();
oneToOneForeignKey.ReferencedColumn = drOneToOneRelationship[4].ToString();
}
drOneToOneRelationship.Close();
string result;
if (oneToOneForeignKey.ColumnName != null)
{
result = (primaryKey.ColumnName == oneToOneForeignKey.ColumnName) ? "onetoone" : "onetomany";
}
else
{
result = "";
}
oneToOneforeignkey = oneToOneForeignKey;
return(result);
}
}
private static RelationColumnMapping[] CreateForeignKeyColumnMappings(ForeignKeyInfo foreignKeyInfo)
{
var objectMappings = new RelationColumnMapping[foreignKeyInfo.FromFieldMappings.Length];
string[] fromFieldMappings = foreignKeyInfo.FromFieldMappings;
string[] toFieldMappings = foreignKeyInfo.ToFieldMappings;
if (fromFieldMappings.Length != toFieldMappings.Length)
{
LogManager.GetLogger(typeof(EntityInfoCache)).Fatal(
"incorrect relation definition, no of from columns should ne equal to no of to columns");
throw new IncorrectFieldDefinitionException(
"incorrect relation definition, no of from columns should ne equal to no of to columns");
}
for (int i = 0; i < fromFieldMappings.Length; i++)
{
string fromMapping = fromFieldMappings[i];
string toMapping = toFieldMappings[i];
objectMappings[i] = new RelationColumnMapping(fromMapping, toMapping);
}
return(objectMappings);
}
public static List<ForeignKeyInfo> GetTableSingleFieldForeignKeys(IDbConnection connection, string schema, string tableName)
{
var inf = InformationSchema(connection);
List<ForeignKeyInfo> foreignKeyInfos = new List<ForeignKeyInfo>();
if (Dialect.HasFlag(SqlDialect.Sqlite))
{
try
{
using (var reader =
SqlHelper.ExecuteReader(connection,
(String.Format("PRAGMA foreign_key_list({0});", tableName))))
{
while (reader.Read())
{
ForeignKeyInfo foreignKeyInfo = new ForeignKeyInfo();
foreignKeyInfo.FKTable = tableName;
foreignKeyInfo.FKColumn = reader.GetString(3);
foreignKeyInfo.PKTable = reader.GetString(2);
foreignKeyInfo.PKColumn = reader.GetString(4);
foreignKeyInfos.Add(foreignKeyInfo);
}
}
}
catch (Exception)
{
}
return foreignKeyInfos;
}
try
{
var c = new Alias(inf + "REFERENTIAL_CONSTRAINTS", "c");
var fk = new Alias(inf + "CONSTRAINT_COLUMN_USAGE", "fk");
var pk = new Alias(inf + "KEY_COLUMN_USAGE", "pk");
var list = connection.Query<ForeignKeyInfo>(new SqlQuery()
.From(c)
.From(fk)
.From(pk)
.Where(
fk._("TABLE_SCHEMA") == schema &
fk._("TABLE_NAME") == tableName &
fk._("CONSTRAINT_SCHEMA") == c._("CONSTRAINT_SCHEMA") &
fk._("CONSTRAINT_NAME") == c._("CONSTRAINT_NAME") &
pk._("CONSTRAINT_SCHEMA") == c._("UNIQUE_CONSTRAINT_SCHEMA") &
pk._("CONSTRAINT_NAME") == c._("UNIQUE_CONSTRAINT_NAME"))
.Select(fk["CONSTRAINT_NAME"], "FKName")
.Select(fk["TABLE_SCHEMA"], "FKSchema")
.Select(fk["TABLE_NAME"], "FKTable")
.Select(fk["COLUMN_NAME"], "FKColumn")
.Select(pk["TABLE_SCHEMA"], "PKSchema")
.Select(pk["TABLE_NAME"], "PKTable")
.Select(pk["COLUMN_NAME"], "PKColumn")
.OrderBy(fk["CONSTRAINT_NAME"]));
foreach (var foreignKeyInfo in list)
{
string priorName = "";
bool priorDeleted = false;
// eğer bir önceki ile aynıysa bunu listeye ekleme ve öncekini de sil
if (priorName == foreignKeyInfo.FKName)
{
if (!priorDeleted)
{
foreignKeyInfos.RemoveAt(foreignKeyInfos.Count - 1);
priorDeleted = true;
}
continue;
}
foreignKeyInfos.Add(foreignKeyInfo);
priorDeleted = false;
priorName = foreignKeyInfo.FKName;
}
}
catch (Exception)
{
}
return foreignKeyInfos;
}
private Dictionary<string, Dictionary<string, ForeignKeyInfo>> GetForeignKeys(SqlConnection conn, List<string> tableNames)
{
string tables = "";
foreach (string t in tableNames)
{
tables += "'" + t + "',";
}
tables = tables.EndsWith(",")?tables.Substring(0,tables.Length-1):tables;
string sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PKPK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
CConstraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME ";
if ( !tables.Equals(""))
{
sql += "WHERE FK.TABLE_NAME IN (" + tables + ")";
}
Dictionary<string, Dictionary<string,ForeignKeyInfo>> fks = new Dictionary<string, Dictionary<string,ForeignKeyInfo>>();
return fks;
DataSet ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
foreach (DataRow row in ds.Tables[0].Rows)
{
ForeignKeyInfo fk = new ForeignKeyInfo()
{
Constraint_Name = row["CConstraint_Name"].ToString(),
FK_Column = row["FK_Column"].ToString(),
FK_Table = row["FK_Table"].ToString(),
PK_Column = row["PK_Column"].ToString(),
PK_Table = row["PKPK_Table"].ToString()
};
Dictionary<string,ForeignKeyInfo> fks_table = null;
if ( fks.ContainsKey(fk.FK_Table))
{
fks_table = fks[fk.FK_Table];
}
else{
fks_table = new Dictionary<string,ForeignKeyInfo>();
fks.Add(fk.FK_Table,fks_table);
}
fks_table.Add(fk.FK_Column,fk);
}
return fks;
}
public DropForeignKeyChange(TableInfo table, ForeignKeyInfo fk)
: base(table)
{
_fk = fk;
}
public AddForeignKey(ForeignKeyInfo foreignKey, string note)
{
ForeignKey = foreignKey;
Note = note;
}
public override IList<Data.Database.ForeignKeyInfo> GetForeignKeys(string tbl_name)
{
var ret = new List<ForeignKeyInfo>();
var sql = "SELECT [c].[name] AS [column], [c2].[name] AS [foreign_column], [o].[name] AS [foreign_table] " +
"FROM [sys].[columns] AS c " +
" LEFT JOIN [sys].[foreign_key_columns] fkc " +
" ON c.[object_id] = fkc.[parent_object_id] AND c.[column_id] = fkc.[parent_column_id] " +
" LEFT JOIN [sys].[columns] c2 " +
" ON fkc.[referenced_object_id] = c2.[object_id] AND fkc.[referenced_column_id] = c2.[column_id] " +
" LEFT JOIN [sys].[objects] o " +
" ON c2.[object_id] = o.[object_id] " +
"WHERE c.[object_id] = OBJECT_ID('" + tbl_name + "') AND fkc.parent_object_id IS NOT NULL";
var rows = this.ExecuteQuery(sql);
foreach (var row in rows)
{
var ci = new ForeignKeyInfo()
{
Column = (string)row["column"],
ForeignTable = (string)row["foreign_table"],
ForeignColumn = (string)row["foreign_column"],
};
ret.Add(ci);
}
return ret;
}
private void LogFailedForeignKey(ForeignKeyInfo foreignKey)
=> Logger.LogWarning(Strings.ForeignKeyScaffoldError(foreignKey.Table, string.Join(",", foreignKey.From)));
private void LoadForeignKeys(SqliteConnection connection, ModelBuilder modelBuilder, ICollection<string> tables)
{
foreach (var tableName in tables)
{
var fkList = connection.CreateCommand();
fkList.CommandText = $"PRAGMA foreign_key_list(\"{tableName.Replace("\"", "\"\"")}\");";
var foreignKeys = new Dictionary<int, ForeignKeyInfo>();
using (var reader = fkList.ExecuteReader())
{
while (reader.Read())
{
var id = reader.GetInt32((int)ForeignKeyList.Id);
var refTable = reader.GetString((int)ForeignKeyList.Table);
ForeignKeyInfo foreignKey;
if (!foreignKeys.TryGetValue(id, out foreignKey))
{
foreignKeys.Add(id, (foreignKey = new ForeignKeyInfo { Table = tableName, ReferencedTable = refTable }));
}
foreignKey.From.Add(reader.GetString((int)ForeignKeyList.From));
foreignKey.To.Add(reader.GetString((int)ForeignKeyList.To));
}
}
var dependentEntityType = modelBuilder.Entity(tableName).Metadata;
foreach (var fkInfo in foreignKeys.Values)
{
try
{
var principalEntityType = modelBuilder.Model.EntityTypes.First(e => e.Name.Equals(fkInfo.ReferencedTable, StringComparison.OrdinalIgnoreCase));
var principalProps = fkInfo.To
.Select(to => principalEntityType
.Properties
.First(p => p.Sqlite().ColumnName.Equals(to, StringComparison.OrdinalIgnoreCase))
)
.ToList()
.AsReadOnly();
var principalKey = principalEntityType.FindKey(principalProps);
if (principalKey == null)
{
var index = principalEntityType.FindIndex(principalProps);
if (index != null
&& index.IsUnique == true)
{
principalKey = principalEntityType.AddKey(principalProps);
}
else
{
LogFailedForeignKey(fkInfo);
continue;
}
}
var depProps = fkInfo.From
.Select(
@from => dependentEntityType
.Properties.
First(p => p.Sqlite().ColumnName.Equals(@from, StringComparison.OrdinalIgnoreCase))
)
.ToList()
.AsReadOnly();
var foreignKey = dependentEntityType.GetOrAddForeignKey(depProps, principalKey, principalEntityType);
if (dependentEntityType.FindIndex(depProps)?.IsUnique == true
|| dependentEntityType.GetKeys().Any(k => k.Properties.All(p => depProps.Contains(p))))
{
foreignKey.IsUnique = true;
}
}
catch (InvalidOperationException)
{
LogFailedForeignKey(fkInfo);
}
}
}
}
public static List<ForeignKeyInfo> GetTableSingleFieldForeignKeys(IDbConnection connection, string schema, string tableName)
{
var inf = InformationSchema(connection);
List<ForeignKeyInfo> foreignKeyInfos = new List<ForeignKeyInfo>();
if (connection.GetDialect().ServerType.StartsWith("Sqlite", StringComparison.OrdinalIgnoreCase))
{
try
{
using (var reader =
SqlHelper.ExecuteReader(connection,
(String.Format("PRAGMA foreign_key_list({0});", tableName))))
{
while (reader.Read())
{
ForeignKeyInfo foreignKeyInfo = new ForeignKeyInfo();
foreignKeyInfo.FKTable = tableName;
foreignKeyInfo.FKColumn = reader.GetString(3);
foreignKeyInfo.PKTable = reader.GetString(2);
foreignKeyInfo.PKColumn = reader.GetString(4);
foreignKeyInfos.Add(foreignKeyInfo);
}
}
}
catch (Exception)
{
}
return foreignKeyInfos;
}
if (connection.GetDialect().ServerType.StartsWith("Firebird", StringComparison.OrdinalIgnoreCase))
{
var query = @"
select
PK.RDB$RELATION_NAME as PKTABLE_NAME
,ISP.RDB$FIELD_NAME as PKCOLUMN_NAME
,FK.RDB$RELATION_NAME as FKTABLE_NAME
,ISF.RDB$FIELD_NAME as FKCOLUMN_NAME
,(ISP.RDB$FIELD_POSITION + 1) as KEY_SEQ
,RC.RDB$UPDATE_RULE as UPDATE_RULE
,RC.RDB$DELETE_RULE as DELETE_RULE
,PK.RDB$CONSTRAINT_NAME as PK_NAME
,FK.RDB$CONSTRAINT_NAME as FK_NAME
from
RDB$RELATION_CONSTRAINTS PK
,RDB$RELATION_CONSTRAINTS FK
,RDB$REF_CONSTRAINTS RC
,RDB$INDEX_SEGMENTS ISP
,RDB$INDEX_SEGMENTS ISF
WHERE FK.RDB$RELATION_NAME = '{0}' and
FK.RDB$CONSTRAINT_NAME = RC.RDB$CONSTRAINT_NAME
and PK.RDB$CONSTRAINT_NAME = RC.RDB$CONST_NAME_UQ
and ISP.RDB$INDEX_NAME = PK.RDB$INDEX_NAME
and ISF.RDB$INDEX_NAME = FK.RDB$INDEX_NAME
and ISP.RDB$FIELD_POSITION = ISF.RDB$FIELD_POSITION
order by 1, 5";
try
{
using (var reader =
SqlHelper.ExecuteReader(connection,
(String.Format(query, tableName))))
{
while (reader.Read())
{
ForeignKeyInfo foreignKeyInfo = new ForeignKeyInfo();
foreignKeyInfo.FKTable = tableName;
foreignKeyInfo.FKColumn = reader.GetString(3).TrimEnd();
foreignKeyInfo.PKTable = reader.GetString(0).TrimEnd();
foreignKeyInfo.PKColumn = reader.GetString(1).TrimEnd();
foreignKeyInfos.Add(foreignKeyInfo);
}
}
}
catch (Exception)
{
}
return foreignKeyInfos;
}
if (connection.GetDialect().ServerType.StartsWith("Postgres", StringComparison.OrdinalIgnoreCase))
{
try
{
var list = connection.Query(
@"SELECT * FROM (
SELECT
o.conname AS constraint_name,
(SELECT nspname FROM pg_namespace WHERE oid=m.relnamespace) AS source_schema,
m.relname AS source_table,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = m.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS source_column,
(SELECT nspname FROM pg_namespace WHERE oid=f.relnamespace) AS target_schema,
f.relname AS target_table,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = f.oid AND a.attnum = o.confkey[1] AND a.attisdropped = false) AS target_column
FROM
pg_constraint o LEFT JOIN pg_class c ON c.oid = o.conrelid
LEFT JOIN pg_class f ON f.oid = o.confrelid LEFT JOIN pg_class m ON m.oid = o.conrelid
WHERE
o.contype = 'f' AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r')) x
WHERE source_schema = @sh AND source_table = @tb
ORDER BY constraint_name", new { sh = schema, tb = tableName }).ToList();
foreach (var fk in list)
{
string priorName = "";
bool priorDeleted = false;
// eğer bir önceki ile aynıysa bunu listeye ekleme ve öncekini de sil
var fkName = fk.constraint_name as string;
if (priorName == fkName)
{
if (!priorDeleted)
{
foreignKeyInfos.RemoveAt(foreignKeyInfos.Count - 1);
priorDeleted = true;
}
continue;
}
var foreignKeyInfo = new ForeignKeyInfo
{
FKName = fkName,
FKSchema = fk.source_schema,
FKTable = fk.source_table,
FKColumn = fk.source_column,
PKSchema = fk.target_schema,
PKTable = fk.target_table,
PKColumn = fk.target_column
};
foreignKeyInfos.Add(foreignKeyInfo);
priorDeleted = false;
priorName = foreignKeyInfo.FKName;
}
}
catch (Exception ex)
{
ex.Log();
}
return foreignKeyInfos;
}
if (connection.GetDialect().ServerType.StartsWith("MySql", StringComparison.OrdinalIgnoreCase))
{
using (var reader =
SqlHelper.ExecuteReader(connection,
(String.Format(@"
SELECT k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME
FROM information_schema.TABLE_CONSTRAINTS i
LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND i.TABLE_SCHEMA = '{0}'
AND i.TABLE_NAME = '{1}'"
, schema, tableName))))
{
while (reader.Read())
{
ForeignKeyInfo foreignKeyInfo = new ForeignKeyInfo();
foreignKeyInfo.FKTable = tableName;
foreignKeyInfo.FKColumn = reader.GetString(0);
foreignKeyInfo.PKTable = reader.GetString(1);
foreignKeyInfo.PKColumn = reader.GetString(2);
foreignKeyInfos.Add(foreignKeyInfo);
}
}
return foreignKeyInfos;
}
if (connection.GetDialect().ServerType.StartsWith("SqlServer", StringComparison.OrdinalIgnoreCase))
{
var c = new Alias(inf + "REFERENTIAL_CONSTRAINTS", "c");
var fk = new Alias(inf + "CONSTRAINT_COLUMN_USAGE", "fk");
var pk = new Alias(inf + "KEY_COLUMN_USAGE", "pk");
var list = connection.Query<ForeignKeyInfo>(new SqlQuery()
.From(c)
.From(fk)
.From(pk)
.Where(
fk._("TABLE_SCHEMA") == schema &
fk._("TABLE_NAME") == tableName &
fk._("CONSTRAINT_SCHEMA") == c._("CONSTRAINT_SCHEMA") &
fk._("CONSTRAINT_NAME") == c._("CONSTRAINT_NAME") &
pk._("CONSTRAINT_SCHEMA") == c._("UNIQUE_CONSTRAINT_SCHEMA") &
pk._("CONSTRAINT_NAME") == c._("UNIQUE_CONSTRAINT_NAME"))
.Select(fk["CONSTRAINT_NAME"], "FKName")
.Select(fk["TABLE_SCHEMA"], "FKSchema")
.Select(fk["TABLE_NAME"], "FKTable")
.Select(fk["COLUMN_NAME"], "FKColumn")
.Select(pk["TABLE_SCHEMA"], "PKSchema")
.Select(pk["TABLE_NAME"], "PKTable")
.Select(pk["COLUMN_NAME"], "PKColumn")
.OrderBy(fk["CONSTRAINT_NAME"]));
foreach (var foreignKeyInfo in list)
{
string priorName = "";
bool priorDeleted = false;
// eğer bir önceki ile aynıysa bunu listeye ekleme ve öncekini de sil
if (priorName == foreignKeyInfo.FKName)
{
if (!priorDeleted)
{
foreignKeyInfos.RemoveAt(foreignKeyInfos.Count - 1);
priorDeleted = true;
}
continue;
}
foreignKeyInfos.Add(foreignKeyInfo);
priorDeleted = false;
priorName = foreignKeyInfo.FKName;
}
}
return foreignKeyInfos;
}
