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