protected virtual DatabaseSchema ProcessSchema(DatabaseSchema databaseSchema, GetSchemaOptions schemaOptions) { foreach (var t in databaseSchema.Tables) { foreach (var key in t.ForeignKeys.ToList()) { if (!key.KeyName.EndsWith("_BackReference")) { key.OtherTable.ForeignKeys.Add( key.BackReference = new ForeignKeySchema { KeyName = key.KeyName + "_BackReference", MemberName = key.MemberName + "_BackReference", AssociationType = AssociationType.Auto, OtherTable = t, ThisColumns = key.OtherColumns, OtherColumns = key.ThisColumns, CanBeNull = true }); } } } foreach (var t in databaseSchema.Tables) { foreach (var key in t.ForeignKeys) { if (key.BackReference != null && key.AssociationType == AssociationType.Auto) { if (key.ThisColumns.All(_ => _.IsPrimaryKey)) { if (t.Columns.Count(_ => _.IsPrimaryKey) == key.ThisColumns.Count) { key.AssociationType = AssociationType.OneToOne; } else { key.AssociationType = AssociationType.ManyToOne; } } else { key.AssociationType = AssociationType.ManyToOne; } key.CanBeNull = key.ThisColumns.All(_ => _.IsNullable); } } foreach (var key in t.ForeignKeys) { string name = null; if (schemaOptions.GetAssociationMemberName != null) { name = schemaOptions.GetAssociationMemberName(key); if (name != null) { key.MemberName = name; } } if (name == null) { name = key.MemberName; if (key.BackReference != null && key.ThisColumns.Count == 1 && key.ThisColumns[0].MemberName.ToLower().EndsWith("id")) { name = key.ThisColumns[0].MemberName; name = name.Substring(0, name.Length - "id".Length); if (t.ForeignKeys.Select(_ => _.MemberName).Concat( t.Columns.Select(_ => _.MemberName)).Concat( new[] { t.TypeName }).All(_ => _ != name)) { name = key.MemberName; } } if (name == key.MemberName) { if (name.StartsWith("FK_")) { name = name.Substring(3); } if (name.EndsWith("_BackReference")) { name = name.Substring(0, name.Length - "_BackReference".Length); } name = string.Join("", name .Split('_') .Where(_ => _.Length > 0 && _ != t.TableName && (t.SchemaName == null || t.IsDefaultSchema || _ != t.SchemaName)) .ToArray()); } if (name.Length != 0 && t.ForeignKeys.Select(_ => _.MemberName).Concat( t.Columns.Select(_ => _.MemberName)).Concat( new[] { t.TypeName }).All(_ => _ != name)) { key.MemberName = name; } } } } return(databaseSchema); }
public virtual DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null) { if (options == null) { options = new GetSchemaOptions(); } IncludedSchemas = options.IncludedSchemas ?? new string[0]; ExcludedSchemas = options.ExcludedSchemas ?? new string[0]; GenerateChar1AsString = options.GenerateChar1AsString; var dbConnection = (DbConnection)dataConnection.Connection; InitProvider(dataConnection); DataTypes = GetDataTypes(dataConnection); DataTypesDic = new Dictionary <string, DataTypeInfo>(DataTypes.Count, StringComparer.OrdinalIgnoreCase); foreach (var dt in DataTypes) { if (!DataTypesDic.ContainsKey(dt.TypeName)) { DataTypesDic.Add(dt.TypeName, dt); } } List <TableSchema> tables; List <ProcedureSchema> procedures; if (options.GetTables) { tables = ( from t in GetTables(dataConnection) where (IncludedSchemas.Length == 0 || IncludedSchemas.Contains(t.SchemaName)) && (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(t.SchemaName)) select new TableSchema { ID = t.TableID, CatalogName = t.CatalogName, SchemaName = t.SchemaName, TableName = t.TableName, Description = t.Description, IsDefaultSchema = t.IsDefaultSchema, IsView = t.IsView, TypeName = ToValidName(t.TableName), Columns = new List <ColumnSchema>(), ForeignKeys = new List <ForeignKeySchema>() } ).ToList(); var pks = GetPrimaryKeys(dataConnection); #region Columns var columns = from c in GetColumns(dataConnection) join pk in pks on c.TableID + "." + c.Name equals pk.TableID + "." + pk.ColumnName into g2 from pk in g2.DefaultIfEmpty() join t in tables on c.TableID equals t.ID orderby c.Ordinal select new { t, c, dt = GetDataType(c.DataType), pk }; foreach (var column in columns) { var dataType = column.c.DataType; var systemType = GetSystemType(dataType, column.c.ColumnType, column.dt, column.c.Length, column.c.Precision, column.c.Scale); var isNullable = column.c.IsNullable; column.t.Columns.Add(new ColumnSchema { Table = column.t, ColumnName = column.c.Name, ColumnType = column.c.ColumnType ?? GetDbType(dataType, column.dt, column.c.Length, column.c.Precision, column.c.Scale), IsNullable = isNullable, MemberName = ToValidName(column.c.Name), MemberType = ToTypeName(systemType, isNullable), SystemType = systemType ?? typeof(object), DataType = GetDataType(dataType, column.c.ColumnType), SkipOnInsert = column.c.SkipOnInsert || column.c.IsIdentity, SkipOnUpdate = column.c.SkipOnUpdate || column.c.IsIdentity, IsPrimaryKey = column.pk != null, PrimaryKeyOrder = column.pk != null ? column.pk.Ordinal : -1, IsIdentity = column.c.IsIdentity, Description = column.c.Description, Length = column.c.Length, Precision = column.c.Precision, Scale = column.c.Scale }); } #endregion #region FK var fks = GetForeignKeys(dataConnection); foreach (var fk in fks.OrderBy(f => f.Ordinal)) { var thisTable = (from t in tables where t.ID == fk.ThisTableID select t).FirstOrDefault(); var otherTable = (from t in tables where t.ID == fk.OtherTableID select t).FirstOrDefault(); if (thisTable == null || otherTable == null) { continue; } var thisColumn = (from c in thisTable.Columns where c.ColumnName == fk.ThisColumn select c).Single(); var otherColumn = (from c in otherTable.Columns where c.ColumnName == fk.OtherColumn select c).Single(); var key = thisTable.ForeignKeys.FirstOrDefault(f => f.KeyName == fk.Name); if (key == null) { key = new ForeignKeySchema { KeyName = fk.Name, MemberName = ToValidName(fk.Name), ThisTable = thisTable, OtherTable = otherTable, ThisColumns = new List <ColumnSchema>(), OtherColumns = new List <ColumnSchema>(), CanBeNull = true }; thisTable.ForeignKeys.Add(key); } key.ThisColumns.Add(thisColumn); key.OtherColumns.Add(otherColumn); } #endregion var pst = GetProviderSpecificTables(dataConnection); if (pst != null) { tables.AddRange(pst); } } else { tables = new List <TableSchema>(); } if (options.GetProcedures) { #region Procedures var sqlProvider = dataConnection.DataProvider.CreateSqlBuilder(); var procs = GetProcedures(dataConnection); var procPparams = GetProcedureParameters(dataConnection); var n = 0; if (procs != null) { procedures = ( from sp in procs where (IncludedSchemas.Length == 0 || IncludedSchemas.Contains(sp.SchemaName)) && (ExcludedSchemas.Length == 0 || !ExcludedSchemas.Contains(sp.SchemaName)) join p in procPparams on sp.ProcedureID equals p.ProcedureID into gr select new ProcedureSchema { CatalogName = sp.CatalogName, SchemaName = sp.SchemaName, ProcedureName = sp.ProcedureName, MemberName = ToValidName(sp.ProcedureName), IsFunction = sp.IsFunction, IsTableFunction = sp.IsTableFunction, IsAggregateFunction = sp.IsAggregateFunction, IsDefaultSchema = sp.IsDefaultSchema, Parameters = ( from pr in gr join dt in DataTypes on pr.DataType equals dt.TypeName into g1 from dt in g1.DefaultIfEmpty() let systemType = GetSystemType(pr.DataType, null, dt, pr.Length, pr.Precision, pr.Scale) orderby pr.Ordinal select new ParameterSchema { SchemaName = pr.ParameterName, SchemaType = GetDbType(pr.DataType, dt, pr.Length, pr.Precision, pr.Scale), IsIn = pr.IsIn, IsOut = pr.IsOut, IsResult = pr.IsResult, Size = pr.Length, ParameterName = ToValidName(pr.ParameterName ?? "par" + ++n), ParameterType = ToTypeName(systemType, true), SystemType = systemType ?? typeof(object), DataType = GetDataType(pr.DataType, null) } ).ToList() } into ps where ps.Parameters.All(p => p.SchemaType != "table type") select ps ).ToList(); var current = 1; foreach (var procedure in procedures) { if ((!procedure.IsFunction || procedure.IsTableFunction) && options.LoadProcedure(procedure)) { var catalog = procedure.CatalogName == null ? null : sqlProvider.Convert(procedure.CatalogName, ConvertType.NameToDatabase).ToString(); var schema = procedure.SchemaName == null ? null : sqlProvider.Convert(procedure.SchemaName, ConvertType.NameToOwner).ToString(); var procName = procedure.ProcedureName == null ? null : sqlProvider.Convert(procedure.ProcedureName, ConvertType.NameToQueryTable).ToString(); var commandText = sqlProvider.BuildTableName(new StringBuilder(), catalog, schema, procName).ToString(); LoadProcedureTableSchema(dataConnection, procedure, commandText, tables); } options.ProcedureLoadingProgress(procedures.Count, current++); } } else { procedures = new List <ProcedureSchema>(); } #endregion var psp = GetProviderSpecificProcedures(dataConnection); if (psp != null) { procedures.AddRange(psp); } } else { procedures = new List <ProcedureSchema>(); } return(ProcessSchema(new DatabaseSchema { DataSource = GetDataSourceName(dbConnection), Database = GetDatabaseName(dbConnection), ServerVersion = dbConnection.ServerVersion, Tables = tables, Procedures = procedures }, options)); }