private void ValidateColumn(SchemaTable table, string columnName, string sqlType)
{
var normalizedColumnName = this.mapping.NormalizeName(columnName);
var objectColumn = table.GetColumn(normalizedColumnName);
if (objectColumn == null || !objectColumn.SqlType.Equals(sqlType))
{
this.InvalidTables.Add(table);
}
}
public Schema(Database database)
{
this.Database = database;
this.TableByName = new Dictionary <string, SchemaTable>();
this.ProcedureByName = new Dictionary <string, SchemaProcedure>();
this.IndexByIndexNameByTableName = new Dictionary <string, Dictionary <string, SchemaIndex> >();
using (var connection = new NpgsqlConnection(database.ConnectionString))
{
connection.Open();
try
{
// Schema
var cmdText = @"
SELECT count(schema_name)
FROM information_schema.schemata
WHERE schema_name = @schemaName";
using (var command = new NpgsqlCommand(cmdText, connection))
{
command.Parameters.Add("@schemaName", NpgsqlDbType.Varchar).Value = database.SchemaName;
var schemaCount = (long)command.ExecuteScalar();
this.Exists = schemaCount != 0;
}
// Tables
cmdText = @"
SELECT T.table_name,
C.column_name,
C.data_type,
C.character_maximum_length,
C.numeric_precision,
C.numeric_scale
FROM information_schema.tables AS T
FULL OUTER JOIN information_schema.columns AS C
ON T.table_name = C.table_name
WHERE T.table_type = 'BASE TABLE'
AND T.table_schema = @tableSchema
AND C.table_schema = @tableSchema";
using (var command = new NpgsqlCommand(cmdText, connection))
{
command.Parameters.Add("@tableSchema", NpgsqlDbType.Varchar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
var tableNameOrdinal = reader.GetOrdinal("table_name");
var columnNameOrdinal = reader.GetOrdinal("column_name");
var dataTypeOrdinal = reader.GetOrdinal("data_type");
var characterMaximumLengthOrdinal = reader.GetOrdinal("character_maximum_length");
var numericPrecisionOrdinal = reader.GetOrdinal("numeric_precision");
var numericScaleOrdinal = reader.GetOrdinal("numeric_scale");
while (reader.Read())
{
var tableName = reader.GetString(tableNameOrdinal);
var columnName = reader.GetString(columnNameOrdinal);
var dataType = reader.GetString(dataTypeOrdinal).Trim().ToLowerInvariant();
var characterMaximumLength = reader.IsDBNull(characterMaximumLengthOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(characterMaximumLengthOrdinal));
var numericPrecision = reader.IsDBNull(numericPrecisionOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(numericPrecisionOrdinal));
var numericScale = reader.IsDBNull(numericScaleOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(numericScaleOrdinal));
tableName = tableName.Trim().ToLowerInvariant();
tableName = database.Mapping.NormalizeName(tableName);
var fullyQualifiedTableName = database.SchemaName + "." + tableName;
columnName = database.Mapping.NormalizeName(columnName);
if (!this.TableByName.TryGetValue(fullyQualifiedTableName, out var table))
{
table = new SchemaTable(this, fullyQualifiedTableName);
this.TableByName[fullyQualifiedTableName] = table;
}
if (!reader.IsDBNull(columnNameOrdinal))
{
var column = new SchemaTableColumn(table, columnName, dataType, characterMaximumLength, numericPrecision, numericScale);
table.ColumnByLowercaseColumnName[column.LowercaseName] = column;
}
}
}
}
// Procedures
cmdText = @"
SELECT routine_name, routine_definition
FROM information_schema.routines
WHERE routine_schema = @routineSchema";
using (var command = new NpgsqlCommand(cmdText, connection))
{
command.Parameters.Add("@routineSchema", NpgsqlDbType.Varchar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var routineNameValue = reader["routine_name"];
var routineDefinitionValue = reader["routine_definition"];
var routineName = (string)routineNameValue;
var routineDefinition = routineDefinitionValue != DBNull.Value ? (string)routineDefinitionValue : string.Empty;
var lowercaseRoutineName = ((string)routineName).Trim().ToLowerInvariant();
var fullyQualifiedName = database.SchemaName + "." + lowercaseRoutineName;
this.ProcedureByName[fullyQualifiedName] = new SchemaProcedure(routineName, routineDefinition);
}
}
}
// // Indeces
// cmdText = @"
// SELECT o.name AS table_name,
// i.name AS index_name
// FROM
// sys.indexes i
// INNER JOIN sys.objects o ON i.object_id = o.object_id
// INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
// WHERE
// i.name IS NOT NULL
// AND o.type = 'U'
// AND i.type = 2
// AND s.name = @tableSchema";
// using (var command = new NpgsqlCommand(cmdText, connection))
// {
// command.Parameters.Add("@tableSchema", NpgsqlDbType.Varchar).Value = database.SchemaName;
// using (var reader = command.ExecuteReader())
// {
// var tableNameOrdinal = reader.GetOrdinal("table_name");
// var indexNameOrdinal = reader.GetOrdinal("index_name");
// while (reader.Read())
// {
// var tableName = reader.GetString(tableNameOrdinal);
// var indexName = reader.GetString(indexNameOrdinal);
// tableName = tableName.Trim().ToLowerInvariant();
// indexName = indexName.Trim().ToLowerInvariant();
// Dictionary<string, SchemaIndex> indexByLowercaseIndexName;
// if (!this.indexByIndexNameByTableName.TryGetValue(tableName, out indexByLowercaseIndexName))
// {
// indexByLowercaseIndexName = new Dictionary<string, SchemaIndex>();
// this.indexByIndexNameByTableName[tableName] = indexByLowercaseIndexName;
// }
// SchemaIndex index;
// if (!indexByLowercaseIndexName.TryGetValue(indexName, out index))
// {
// index = new SchemaIndex(this, indexName);
// indexByLowercaseIndexName[indexName] = index;
// }
// }
// }
// }
}
finally
{
connection.Close();
}
}
}
