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