Beispiel #1
0
        private void GetColumns(DbConnection connection, DatabaseTable table)
        {
            using var command   = connection.CreateCommand();
            command.CommandText = new StringBuilder()
                                  .AppendLine("SELECT \"name\", \"type\", \"notnull\", \"dflt_value\"")
                                  .AppendLine("FROM pragma_table_info(@table)")
                                  .AppendLine("ORDER BY \"cid\";")
                                  .ToString();

            var parameter = command.CreateParameter();

            parameter.ParameterName = "@table";
            parameter.Value         = table.Name;
            command.Parameters.Add(parameter);

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                var columnName   = reader.GetString(0);
                var dataType     = reader.GetString(1);
                var notNull      = reader.GetBoolean(2);
                var defaultValue = !reader.IsDBNull(3)
                    ? FilterClrDefaults(dataType, notNull, reader.GetString(3))
                    : null;

                _logger.ColumnFound(table.Name, columnName, dataType, notNull, defaultValue);

                table.Columns.Add(new DatabaseColumn(table, columnName, dataType)
                {
                    IsNullable      = !notNull,
                    DefaultValueSql = defaultValue
                });
            }
        }
        private void GetColumns(DbConnection connection, DatabaseTable table)
        {
            using var command   = connection.CreateCommand();
            command.CommandText = new StringBuilder()
                                  .AppendLine("SELECT \"name\", \"type\", \"notnull\", \"dflt_value\"")
                                  .AppendLine("FROM pragma_table_info(@table)")
                                  .AppendLine("ORDER BY \"cid\";")
                                  .ToString();

            var parameter = command.CreateParameter();

            parameter.ParameterName = "@table";
            parameter.Value         = table.Name;
            command.Parameters.Add(parameter);

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                var columnName   = reader.GetString(0);
                var dataType     = reader.GetString(1);
                var notNull      = reader.GetBoolean(2);
                var defaultValue = !reader.IsDBNull(3)
                    ? FilterClrDefaults(dataType, notNull, reader.GetString(3))
                    : null;

                _logger.ColumnFound(table.Name, columnName, dataType, notNull, defaultValue);

                var autoIncrement = 0;
                if (connection is SqliteConnection sqliteConnection &&
                    !(table is DatabaseView))
                {
                    var db = sqliteConnection.Handle;
                    var rc = sqlite3_table_column_metadata(
                        db,
                        connection.Database,
                        table.Name,
                        columnName,
                        out var _,
                        out var _,
                        out var _,
                        out var _,
                        out autoIncrement);
                    SqliteException.ThrowExceptionForRC(rc, db);
                }

                table.Columns.Add(new DatabaseColumn
                {
                    Table           = table,
                    Name            = columnName,
                    StoreType       = dataType,
                    IsNullable      = !notNull,
                    DefaultValueSql = defaultValue,
                    ValueGenerated  = autoIncrement != 0
                        ? ValueGenerated.OnAdd
                        : default(ValueGenerated?)
                });
            }
        }
Beispiel #3
0
        private IEnumerable <DatabaseColumn> GetColumns(DbConnection connection, string table)
        {
            var command = connection.CreateCommand();

            command.CommandText = new StringBuilder()
                                  .AppendLine("SELECT \"name\", \"type\", \"notnull\", \"dflt_value\"")
                                  .AppendLine("FROM pragma_table_info(@table)")
                                  .AppendLine("ORDER BY \"cid\";")
                                  .ToString();

            var parameter = command.CreateParameter();

            parameter.ParameterName = "@table";
            parameter.Value         = table;
            command.Parameters.Add(parameter);

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var columnName   = reader.GetString(0);
                    var dataType     = reader.GetString(1);
                    var notNull      = reader.GetBoolean(2);
                    var defaultValue = !reader.IsDBNull(3)
                        ? reader.GetString(3)
                        : null;

                    _logger.ColumnFound(table, columnName, dataType, notNull, defaultValue);

                    yield return(new DatabaseColumn
                    {
                        Name = columnName,
                        StoreType = dataType,
                        IsNullable = !notNull,
                        DefaultValueSql = defaultValue
                    });
                }
            }
        }
Beispiel #4
0
        void GetColumns(
            NpgsqlConnection connection,
            IReadOnlyList <DatabaseTable> tables,
            string tableFilter)
        {
            using (var command = connection.CreateCommand())
            {
                var commandText = $@"
SELECT
    nspname, relname, typ.typname, basetyp.typname AS basetypname, attname, description, attisdropped,
    {(connection.PostgreSqlVersion >= new Version(10, 0) ? "attidentity" : "''::\"char\" as attidentity")},
    format_type(typ.oid, atttypmod) AS formatted_typname,
    format_type(basetyp.oid, typ.typtypmod) AS formatted_basetypname,
    CASE WHEN pg_proc.proname='array_recv' THEN 'a' ELSE typ.typtype END AS typtype,
    CASE
        WHEN pg_proc.proname='array_recv' THEN elemtyp.typname
        ELSE NULL
    END AS elemtypname,
    (NOT attnotnull) AS nullable,
    CASE WHEN atthasdef THEN (SELECT pg_get_expr(adbin, cls.oid) FROM pg_attrdef WHERE adrelid = cls.oid AND adnum = attr.attnum) ELSE NULL END AS default
FROM pg_class AS cls
JOIN pg_namespace AS ns ON ns.oid = cls.relnamespace
LEFT OUTER JOIN pg_attribute AS attr ON attrelid = cls.oid
LEFT OUTER JOIN pg_type AS typ ON attr.atttypid = typ.oid
LEFT OUTER JOIN pg_proc ON pg_proc.oid = typ.typreceive
LEFT OUTER JOIN pg_type AS elemtyp ON (elemtyp.oid = typ.typelem)
LEFT OUTER JOIN pg_type AS basetyp ON (basetyp.oid = typ.typbasetype)
LEFT OUTER JOIN pg_description AS des ON des.objoid = cls.oid AND des.objsubid = attnum
WHERE
    relkind = 'r' AND
    nspname NOT IN ('pg_catalog', 'information_schema') AND
    attnum > 0 {tableFilter} ORDER BY attnum";

                command.CommandText = commandText;

                using (var reader = command.ExecuteReader())
                {
                    var tableGroups = reader.Cast <DbDataRecord>()
                                      .GroupBy(
                        ddr => (tableSchema: ddr.GetValueOrDefault <string>("nspname"),
                                tableName: ddr.GetValueOrDefault <string>("relname")));

                    foreach (var tableGroup in tableGroups)
                    {
                        var tableSchema = tableGroup.Key.tableSchema;
                        var tableName   = tableGroup.Key.tableName;

                        var table = tables.Single(t => t.Schema == tableSchema && t.Name == tableName);

                        foreach (var record in tableGroup)
                        {
                            var column = new DatabaseColumn
                            {
                                Table             = table,
                                Name              = record.GetValueOrDefault <string>("attname"),
                                IsNullable        = record.GetValueOrDefault <bool>("nullable"),
                                DefaultValueSql   = record.GetValueOrDefault <string>("default"),
                                ComputedColumnSql = null
                            };

                            // We need to know about dropped columns because constraints take them into
                            // account when referencing columns. We'll get rid of them before returning the model.
                            var isDropped = record.GetValueOrDefault <bool>("attisdropped");
                            if (isDropped)
                            {
                                table.Columns.Add(null);
                                continue;
                            }

                            string systemTypeName;
                            var    formattedTypeName     = AdjustFormattedTypeName(record.GetValueOrDefault <string>("formatted_typname"));
                            var    formattedBaseTypeName = record.GetValueOrDefault <string>("formatted_basetypname");
                            if (formattedBaseTypeName == null)
                            {
                                column.StoreType = formattedTypeName;
                                systemTypeName   = record.GetValueOrDefault <string>("typname");
                            }
                            else
                            {
                                // This is a domain type
                                column.StoreType = formattedTypeName;
                                column.SetUnderlyingStoreType(AdjustFormattedTypeName(formattedBaseTypeName));
                                systemTypeName = record.GetValueOrDefault <string>("basetypname");
                            }

                            // Enum types cannot be scaffolded for now (nor can domains of enum types),
                            // skip with an informative message
                            if (_enums.Contains(formattedTypeName) || _enums.Contains(formattedBaseTypeName))
                            {
                                _logger.EnumColumnSkippedWarning(DisplayName(tableSchema, tableName) + '.' + column.Name);
                                // We need to know about skipped columns because constraints take them into
                                // account when referencing columns. We'll get rid of them before returning the model.
                                table.Columns.Add(null);
                                continue;
                            }

                            _logger.ColumnFound(
                                DisplayName(tableSchema, tableName),
                                column.Name,
                                formattedTypeName,
                                column.IsNullable,
                                column.DefaultValueSql);

                            // Identify IDENTITY columns, as well as SERIAL ones.
                            var identityChar = record.GetValueOrDefault <char>("attidentity");
                            if (identityChar == 'a')
                            {
                                column[NpgsqlAnnotationNames.ValueGenerationStrategy] = NpgsqlValueGenerationStrategy.IdentityAlwaysColumn;
                            }
                            else if (identityChar == 'd')
                            {
                                column[NpgsqlAnnotationNames.ValueGenerationStrategy] = NpgsqlValueGenerationStrategy.IdentityByDefaultColumn;
                            }
                            else if (SerialTypes.Contains(systemTypeName) &&
                                     column.DefaultValueSql == $"nextval('{column.Table.Name}_{column.Name}_seq'::regclass)" ||
                                     column.DefaultValueSql == $"nextval('\"{column.Table.Name}_{column.Name}_seq\"'::regclass)")
                            {
                                // Hacky but necessary...
                                // We identify serial columns by examining their default expression,
                                // and reverse-engineer these as ValueGenerated.OnAdd
                                // TODO: Think about composite keys? Do serial magic only for non-composite.
                                column.DefaultValueSql = null;
                                // Serial is the default value generation strategy, so NpgsqlAnnotationCodeGenerator
                                // makes sure it isn't actually rendered
                                column[NpgsqlAnnotationNames.ValueGenerationStrategy] = NpgsqlValueGenerationStrategy.SerialColumn;
                            }

                            if (column[NpgsqlAnnotationNames.ValueGenerationStrategy] != null)
                            {
                                column.ValueGenerated = ValueGenerated.OnAdd;
                            }

                            AdjustDefaults(column, systemTypeName);

                            var comment = record.GetValueOrDefault <string>("description");
                            if (comment != null)
                            {
                                column[NpgsqlAnnotationNames.Comment] = comment;
                            }

                            table.Columns.Add(column);
                        }
                    }
                }
            }
        }
        private void GetColumns(
            DbConnection connection,
            string tableFilter,
            DatabaseModel databaseModel)
        {
            using (var command = (OracleCommand)connection.CreateCommand())
            {
                command.InitialLONGFetchSize = -1;

                command.CommandText = new StringBuilder()
                                      .AppendLine("SELECT")
                                      .AppendLine("   t.tablespace_name,")
                                      .AppendLine("   c.table_name,")
                                      .AppendLine("   c.column_name,")
                                      .AppendLine("   c.column_id,")
                                      .AppendLine("   c.data_type,")
                                      .AppendLine("   c.data_length,")
                                      .AppendLine("   c.data_precision,")
                                      .AppendLine("   c.data_scale,")
                                      .AppendLine("   c.nullable,")
                                      .AppendLine("   c.identity_column,")
                                      .AppendLine("   c.data_default,")
                                      .AppendLine("   c.virtual_column")
                                      .AppendLine("FROM user_tab_cols c")
                                      .AppendLine("INNER JOIN user_tables t ")
                                      .AppendLine("ON UPPER(t.table_name)=UPPER(c.table_name)")
                                      .AppendLine(tableFilter)
                                      .AppendLine("ORDER BY c.column_id")
                                      .ToString();

                using (var reader = command.ExecuteReader())
                {
                    var tableColumnGroups = reader.Cast <DbDataRecord>()
                                            .GroupBy(
                        ddr => (tableSchema: ddr.GetValueOrDefault <string>("tablespace_name"),
                                tableName: ddr.GetValueOrDefault <string>("table_name")));

                    foreach (var tableColumnGroup in tableColumnGroups)
                    {
                        var tableSchema = tableColumnGroup.Key.tableSchema;
                        var tableName   = tableColumnGroup.Key.tableName;
                        var table       = databaseModel.Tables.Single(t => t.Schema == tableSchema && t.Name == tableName);

                        foreach (var dataRecord in tableColumnGroup)
                        {
                            var columnName   = dataRecord.GetValueOrDefault <string>("column_name");
                            var ordinal      = dataRecord.GetValueOrDefault <int>("column_id");
                            var dataTypeName = dataRecord.GetValueOrDefault <string>("data_type");
                            var maxLength    = dataRecord.GetValueOrDefault <int>("data_length");
                            var precision    = dataRecord.GetValueOrDefault <int>("data_precision");
                            var scale        = dataRecord.GetValueOrDefault <int>("data_scale");
                            var isNullable   = dataRecord.GetValueOrDefault <string>("nullable").Equals("Y");
                            var isIdentity   = dataRecord.GetValueOrDefault <string>("identity_column").Equals("YES");
                            var defaultValue = !isIdentity?dataRecord.GetValueOrDefault <string>("data_default") : null;

                            var computedValue = dataRecord.GetValueOrDefault <string>("virtual_column").Equals("YES") ? defaultValue : null;

                            var storeType = GetOracleClrType(dataTypeName, maxLength, precision, scale);
                            if (string.IsNullOrWhiteSpace(defaultValue) ||
                                !string.IsNullOrWhiteSpace(computedValue))
                            {
                                defaultValue = null;
                            }

                            _logger.ColumnFound(
                                DisplayName(tableSchema, tableName),
                                columnName,
                                ordinal,
                                dataTypeName,
                                maxLength,
                                precision,
                                scale,
                                isNullable,
                                isIdentity,
                                defaultValue,
                                computedValue);

                            var column = new DatabaseColumn
                            {
                                Table             = table,
                                Name              = columnName,
                                StoreType         = storeType,
                                IsNullable        = isNullable,
                                DefaultValueSql   = defaultValue,
                                ComputedColumnSql = computedValue,
                                ValueGenerated    = isIdentity
                                    ? ValueGenerated.OnAdd
                                    : default(ValueGenerated?)
                            };

                            table.Columns.Add(column);
                        }
                    }
                }
            }
        }
Beispiel #6
0
        private void GetColumns(DbConnection connection, IReadOnlyList <DatabaseTable> tables)
        {
            using var command   = connection.CreateCommand();
            command.CommandText = $@"SELECT * FROM `INFORMATION_SCHEMA.COLUMNS` ORDER BY TABLE_NAME, ORDINAL_POSITION";

            using var reader = command.ExecuteReader();
            while (reader.Read())
            {
                var tableName = reader.GetValueOrDefault <string>("TABLE_NAME");
                var table     = tables.FirstOrDefault(t => string.Equals(t.Name, tableName)) ??
                                tables.FirstOrDefault(t => string.Equals(t.Name, tableName, StringComparison.OrdinalIgnoreCase));
                if (table != null)
                {
                    var columnName        = reader.GetValueOrDefault <string>("COLUMN_NAME");
                    var dataTypeName      = reader.GetValueOrDefault <string>("DATA_TYPE");
                    var ordinal           = reader.GetValueOrDefault <int>("ORDINAL_POSITION");
                    var nullable          = reader.GetValueOrDefault <bool>("IS_NULLABLE");
                    var maxLength         = reader.GetValueOrDefault <int>("CHARACTER_MAXIMUM_LENGTH");
                    var precision         = reader.GetValueOrDefault <int>("NUMERIC_PRECISION");
                    var scale             = reader.GetValueOrDefault <int>("NUMERIC_SCALE");
                    var defaultValue      = reader.GetValueOrDefault <string>("COLUMN_DEFAULT");
                    var validationRule    = reader.GetValueOrDefault <string>("VALIDATION_RULE");
                    var validationText    = reader.GetValueOrDefault <string>("VALIDATION_TEXT");
                    var identitySeed      = reader.GetValueOrDefault <int?>("IDENTITY_SEED");
                    var identityIncrement = reader.GetValueOrDefault <int?>("IDENTITY_INCREMENT");
                    var computedValue     = (string)null; // TODO: Implement support for expressions
                                                          // (DAO Field2 (though not mentioned)).
                                                          // Might have no equivalent in ADOX.

                    _logger.ColumnFound(
                        tableName,
                        columnName,
                        ordinal,
                        dataTypeName,
                        maxLength,
                        precision,
                        scale,
                        nullable,
                        identitySeed.HasValue,
                        defaultValue,
                        computedValue);

                    var storeType = GetStoreType(dataTypeName, precision, scale, maxLength);
                    defaultValue = FilterClrDefaults(dataTypeName, nullable, defaultValue);

                    var column = new DatabaseColumn
                    {
                        Table             = table,
                        Name              = columnName,
                        StoreType         = storeType,
                        IsNullable        = nullable,
                        DefaultValueSql   = defaultValue,
                        ComputedColumnSql = null,
                        ValueGenerated    = identitySeed.HasValue
                            ? ValueGenerated.OnAdd
                            : storeType == "timestamp"
                                ? ValueGenerated.OnAddOrUpdate
                                : default(ValueGenerated?)
                    };

                    if (storeType == "timestamp")
                    {
                        // Note: annotation name must match `ScaffoldingAnnotationNames.ConcurrencyToken`
                        column["ConcurrencyToken"] = true;
                    }

                    table.Columns.Add(column);
                }
            }
        }