Example #1
0
        internal static void SetForeignKeyMemberName(GetSchemaOptions schemaOptions, TableSchema table, ForeignKeySchema key)
        {
            string name = null;

            if (schemaOptions.GetAssociationMemberName != null)
            {
                name = schemaOptions.GetAssociationMemberName(key);

                if (name != null)
                {
                    key.MemberName = ToValidName(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 (table.ForeignKeys.Select(_ => _.MemberName).Concat(
                            table.Columns.Select(_ => _.MemberName)).Concat(
                            new[] { table.TypeName }).Any(_ => _ == 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 && _ != table.TableName &&
                                              (table.SchemaName == null || table.IsDefaultSchema || _ != table.SchemaName))
                                       .ToArray());

                    var digitEnd = 0;
                    for (var i = name.Length - 1; i >= 0; i--)
                    {
                        if (char.IsDigit(name[i]))
                        {
                            digitEnd++;
                        }
                        else
                        {
                            break;
                        }
                    }

                    if (digitEnd > 0)
                    {
                        name = name.Substring(0, name.Length - digitEnd);
                    }
                }

                if (string.IsNullOrEmpty(name))
                {
                    name = key.OtherTable != key.ThisTable ? key.OtherTable.TableName : key.KeyName;
                }

                if (table.ForeignKeys.Select(_ => _.MemberName).Concat(
                        table.Columns.Select(_ => _.MemberName)).Concat(
                        new[] { table.TypeName }).All(_ => _ != name))
                {
                    key.MemberName = ToValidName(name);
                }
            }
        }
Example #2
0
        public virtual DatabaseSchema GetSchema(DataConnection dataConnection, GetSchemaOptions options = null)
        {
            if (options == null)
            {
                options = new GetSchemaOptions();
            }

            IncludedSchemas       = GetHashSet(options.IncludedSchemas, options.StringComparer);
            ExcludedSchemas       = GetHashSet(options.ExcludedSchemas, options.StringComparer);
            IncludedCatalogs      = GetHashSet(options.IncludedCatalogs, options.StringComparer);
            ExcludedCatalogs      = GetHashSet(options.ExcludedCatalogs, options.StringComparer);
            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.Count == 0 || IncludedSchemas.Contains(t.SchemaName)) &&
                        (ExcludedSchemas.Count == 0 || !ExcludedSchemas.Contains(t.SchemaName)) &&
                        (IncludedCatalogs.Count == 0 || IncludedCatalogs.Contains(t.CatalogName)) &&
                        (ExcludedCatalogs.Count == 0 || !ExcludedCatalogs.Contains(t.CatalogName))
                        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>(),
                    IsProviderSpecific = t.IsProviderSpecific
                }
                    ).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;
                    var columnType = column.c.ColumnType ?? GetDbType(dataType, column.dt, column.c.Length, column.c.Precision, column.c.Scale);

                    column.t.Columns.Add(new ColumnSchema
                    {
                        Table                = column.t,
                        ColumnName           = column.c.Name,
                        ColumnType           = columnType,
                        IsNullable           = isNullable,
                        MemberName           = ToValidName(column.c.Name),
                        MemberType           = ToTypeName(systemType, isNullable),
                        SystemType           = systemType ?? typeof(object),
                        DataType             = GetDataType(dataType, column.c.ColumnType, column.c.Length, column.c.Precision, column.c.Scale),
                        ProviderSpecificType = GetProviderSpecificType(dataType),
                        SkipOnInsert         = column.c.SkipOnInsert || column.c.IsIdentity,
                        SkipOnUpdate         = column.c.SkipOnUpdate || column.c.IsIdentity,
                        IsPrimaryKey         = column.pk != null,
                        PrimaryKeyOrder      = 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.Count == 0 || IncludedSchemas.Contains(sp.SchemaName)) &&
                            (ExcludedSchemas.Count == 0 || !ExcludedSchemas.Contains(sp.SchemaName)) &&
                            (IncludedCatalogs.Count == 0 || IncludedCatalogs.Contains(sp.CatalogName)) &&
                            (ExcludedCatalogs.Count == 0 || !ExcludedCatalogs.Contains(sp.CatalogName))
                            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, pr.Length, pr.Precision, pr.Scale),
                            ProviderSpecificType = GetProviderSpecificType(pr.DataType),
                        }
                            ).ToList()
                    } into ps
                            where ps.Parameters.All(p => p.SchemaType != "table type")
                            select ps
                        ).ToList();

                    var current = 1;

                    var isActiveTransaction = dataConnection.Transaction != null;

                    if (GetProcedureSchemaExecutesProcedure && isActiveTransaction)
                    {
                        throw new LinqToDBException("Cannot read schema with GetSchemaOptions.GetProcedures = true from transaction. Remove transaction or set GetSchemaOptions.GetProcedures to false");
                    }

                    if (!isActiveTransaction)
                    {
                        dataConnection.BeginTransaction();
                    }

                    try
                    {
                        foreach (var procedure in procedures)
                        {
                            if ((!procedure.IsFunction || procedure.IsTableFunction) && options.LoadProcedure(procedure))
                            {
                                var commandText = sqlProvider.ConvertTableName(new StringBuilder(),
                                                                               procedure.CatalogName,
                                                                               procedure.SchemaName,
                                                                               procedure.ProcedureName).ToString();

                                LoadProcedureTableSchema(dataConnection, procedure, commandText, tables);
                            }

                            options.ProcedureLoadingProgress(procedures.Count, current++);
                        }
                    }
                    finally
                    {
                        if (!isActiveTransaction)
                        {
                            dataConnection.RollbackTransaction();
                        }
                    }
                }
                else
                {
                    procedures = new List <ProcedureSchema>();
                }

                var psp = GetProviderSpecificProcedures(dataConnection);

                if (psp != null)
                {
                    procedures.AddRange(psp);
                }

                #endregion
            }
            else
            {
                procedures = new List <ProcedureSchema>();
            }

            return(ProcessSchema(new DatabaseSchema
            {
                DataSource = GetDataSourceName(dbConnection),
                Database = GetDatabaseName(dbConnection),
                ServerVersion = dbConnection.ServerVersion,
                Tables = tables,
                Procedures = procedures,
                ProviderSpecificTypeNamespace = GetProviderSpecificTypeNamespace(),
                DataTypesSchema = DataTypesSchema,
            }, options));
        }
Example #3
0
        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,
                    });
                }

                #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
            }
            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 ?? 0, pr.Precision, pr.Scale)

                                                 orderby pr.Ordinal
                                                 select new ParameterSchema
                        {
                            SchemaName = pr.ParameterName,
                            SchemaType = GetDbType(pr.DataType, dt, pr.Length ?? 0, 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();

                            CommandType     commandType;
                            DataParameter[] parameters;

                            if (procedure.IsTableFunction)
                            {
                                commandText = "SELECT * FROM " + commandText + "(";

                                for (var i = 0; i < procedure.Parameters.Count; i++)
                                {
                                    if (i != 0)
                                    {
                                        commandText += ",";
                                    }
                                    commandText += "NULL";
                                }

                                commandText += ")";
                                commandType  = CommandType.Text;
                                parameters   = new DataParameter[0];
                            }
                            else
                            {
                                commandType = CommandType.StoredProcedure;
                                parameters  = procedure.Parameters.Select(p =>
                                                                          new DataParameter
                                {
                                    Name  = p.ParameterName,
                                    Value =
                                        p.SystemType == typeof(string)   ? "" :
                                        p.SystemType == typeof(DateTime) ? DateTime.Now :
                                        DefaultValue.GetValue(p.SystemType),
                                    DataType  = p.DataType,
                                    Size      = p.Size,
                                    Direction =
                                        p.IsIn ?
                                        p.IsOut ?
                                        ParameterDirection.InputOutput :
                                        ParameterDirection.Input :
                                        ParameterDirection.Output
                                }).ToArray();
                            }

                            {
                                try
                                {
                                    var st = GetProcedureSchema(dataConnection, commandText, commandType, parameters);

                                    if (st != null)
                                    {
                                        procedure.ResultTable = new TableSchema
                                        {
                                            IsProcedureResult = true,
                                            TypeName          = ToValidName(procedure.ProcedureName + "Result"),
                                            ForeignKeys       = new List <ForeignKeySchema>(),
                                            Columns           = GetProcedureResultColumns(st)
                                        };

                                        foreach (var column in procedure.ResultTable.Columns)
                                        {
                                            column.Table = procedure.ResultTable;
                                        }

                                        procedure.SimilarTables =
                                            (
                                                from t in tables
                                                where t.Columns.Count == procedure.ResultTable.Columns.Count
                                                let zip = t.Columns.Zip(procedure.ResultTable.Columns, (c1, c2) => new { c1, c2 })
                                                          where zip.All(z => z.c1.ColumnName == z.c2.ColumnName && z.c1.SystemType == z.c2.SystemType)
                                                          select t
                                            ).ToList();
                                    }
                                }
                                catch (Exception ex)
                                {
                                    procedure.ResultException = ex;
                                }
                            }
                        }

                        options.ProcedureLoadingProgress(procedures.Count, current++);
                    }
                }
                else
                {
                    procedures = new List <ProcedureSchema>();
                }

                #endregion
            }
            else
            {
                procedures = new List <ProcedureSchema>();
            }

            return(ProcessSchema(new DatabaseSchema
            {
                DataSource = GetDataSourceName(dbConnection),
                Database = GetDatabaseName(dbConnection),
                ServerVersion = dbConnection.ServerVersion,
                Tables = tables,
                Procedures = procedures,
            }));
        }
Example #4
0
        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;

            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,
                    });
                }

                #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
            }
            else
                tables = new List<TableSchema>();

            if (options.GetProcedures)
            {
                #region Procedures

                var sqlProvider = dataConnection.DataProvider.CreateSqlBuilder();
                var procs       = GetProcedures(dataConnection);
                var procPparams = GetProcedureParameters(dataConnection);

                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 ?? 0, pr.Precision, pr.Scale)

                                orderby pr.Ordinal
                                select new ParameterSchema
                                {
                                    SchemaName    = pr.ParameterName,
                                    SchemaType    = GetDbType(pr.DataType, dt, pr.Length ?? 0, pr.Precision, pr.Scale),
                                    IsIn          = pr.IsIn,
                                    IsOut         = pr.IsOut,
                                    IsResult      = pr.IsResult,
                                    Size          = pr.Length,
                                    ParameterName = ToValidName(pr.ParameterName),
                                    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();

                            CommandType     commandType;
                            DataParameter[] parameters;

                            if (procedure.IsTableFunction)
                            {
                                commandText = "SELECT * FROM " + commandText + "(";

                                for (var i = 0; i < procedure.Parameters.Count; i++)
                                {
                                    if (i != 0)
                                        commandText += ",";
                                    commandText += "NULL";
                                }

                                commandText += ")";
                                commandType = CommandType.Text;
                                parameters  = new DataParameter[0];
                            }
                            else
                            {
                                commandType = CommandType.StoredProcedure;
                                parameters  = procedure.Parameters.Select(p =>
                                    new DataParameter
                                    {
                                        Name      = p.ParameterName,
                                        Value     =
                                            p.SystemType == typeof(string)   ? "" :
                                            p.SystemType == typeof(DateTime) ? DateTime.Now :
                                                DefaultValue.GetValue(p.SystemType),
                                        DataType  = p.DataType,
                                        Size      = p.Size,
                                        Direction =
                                            p.IsIn ?
                                                p.IsOut ?
                                                    ParameterDirection.InputOutput :
                                                    ParameterDirection.Input :
                                                ParameterDirection.Output
                                    }).ToArray();
                            }

                            {
                                try
                                {
                                    var st = GetProcedureSchema(dataConnection, commandText, commandType, parameters);

                                    if (st != null)
                                    {
                                        procedure.ResultTable = new TableSchema
                                        {
                                            IsProcedureResult = true,
                                            TypeName          = ToValidName(procedure.ProcedureName + "Result"),
                                            ForeignKeys       = new List<ForeignKeySchema>(),
                                            Columns           = GetProcedureResultColumns(st)
                                        };

                                        foreach (var column in procedure.ResultTable.Columns)
                                            column.Table = procedure.ResultTable;

                                        procedure.SimilarTables =
                                        (
                                            from  t in tables
                                            where t.Columns.Count == procedure.ResultTable.Columns.Count
                                            let zip = t.Columns.Zip(procedure.ResultTable.Columns, (c1, c2) => new { c1, c2 })
                                            where zip.All(z => z.c1.ColumnName == z.c2.ColumnName && z.c1.SystemType == z.c2.SystemType)
                                            select t
                                        ).ToList();
                                    }
                                }
                                catch (Exception ex)
                                {
                                    procedure.ResultException = ex;
                                }
                            }
                        }

                        options.ProcedureLoadingProgress(procedures.Count, current++);
                    }
                }
                else
                    procedures = new List<ProcedureSchema>();

                #endregion
            }
            else
                procedures = new List<ProcedureSchema>();

            return ProcessSchema(new DatabaseSchema
            {
                DataSource    = GetDataSourceName(dbConnection),
                Database      = GetDatabaseName  (dbConnection),
                ServerVersion = dbConnection.ServerVersion,
                Tables        = tables,
                Procedures    = procedures,
            });
        }
Example #5
0
		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;
					var columnType = column.c.ColumnType ?? GetDbType(dataType, column.dt, column.c.Length, column.c.Precision, column.c.Scale);

					column.t.Columns.Add(new ColumnSchema
					{
						Table                = column.t,
						ColumnName           = column.c.Name,
						ColumnType           = columnType,
						IsNullable           = isNullable,
						MemberName           = ToValidName(column.c.Name),
						MemberType           = ToTypeName(systemType, isNullable),
						SystemType           = systemType ?? typeof(object),
						DataType             = GetDataType(dataType, column.c.ColumnType, column.c.Length, column.c.Precision, column.c.Scale),
						ProviderSpecificType = GetProviderSpecificType(dataType),
						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, pr.Length, pr.Precision, pr.Scale),
									ProviderSpecificType = GetProviderSpecificType(pr.DataType),
								}
							).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 commandText = sqlProvider.ConvertTableName(new StringBuilder(),
								 procedure.CatalogName,
								 procedure.SchemaName,
								 procedure.ProcedureName).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,
				ProviderSpecificTypeNamespace = GetProviderSpecificTypeNamespace(),
				DataTypesSchema               = DataTypesSchema,

			}, options);
		}