public Validation(Database database)
{
this.database = database;
this.schema = new Schema(database);
this.MissingTableNames = new HashSet<string>();
this.MissingColumnNamesByTable = new Dictionary<Table, HashSet<string>>();
this.InvalidTables = new HashSet<Table>();
this.InvalidColumns = new HashSet<TableColumn>();
this.Validate();
this.success = this.MissingTableNames.Count == 0 &
this.MissingColumnNamesByTable.Count == 0 &
this.InvalidTables.Count == 0 &
this.InvalidColumns.Count == 0;
}
public Schema(Database database)
{
this.database = database;
this.tableByLowercaseTableName = new Dictionary<string, Table>();
this.tableTypeByLowercaseTableTypeName = new Dictionary<string, TableType>();
this.procedureByLowercaseProcedureName = new Dictionary<string, Procedure>();
this.viewByLowercaseViewName = new Dictionary<string, View>();
this.indexByLowercaseIndexNameByLowercaseTableName = new Dictionary<string, Dictionary<string, Index>>();
using (var connection = new SqlConnection(database.ConnectionString))
{
connection.Open();
try
{
// Schema
var cmdText = @"
SELECT count(schema_name)
FROM information_schema.schemata
WHERE schema_name = @schemaName";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@schemaName", SqlDbType.NVarChar).Value = database.SchemaName;
var schemaCount = (int)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 SqlCommand(cmdText, connection))
{
command.Parameters.Add("@tableSchema", SqlDbType.NVarChar).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();
Table table;
if (!this.TableByLowercaseTableName.TryGetValue(tableName, out table))
{
table = new Table(this, tableName);
this.TableByLowercaseTableName[tableName] = table;
}
if (!reader.IsDBNull(columnNameOrdinal))
{
var column = new TableColumn(table, columnName, dataType, characterMaximumLength, numericPrecision, numericScale);
table.ColumnByLowercaseColumnName[column.LowercaseName] = column;
}
}
}
}
// Table Types
cmdText = @"
SELECT domain_name
FROM information_schema.domains
WHERE data_type='table type' AND
domain_schema = @domainSchema";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@domainSchema", SqlDbType.NVarChar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var tableTypeName = (string)reader["domain_name"];
var lowercaseTableTypeName = tableTypeName.Trim().ToLowerInvariant();
this.TableTypeByLowercaseTableTypeName[lowercaseTableTypeName] = new TableType(this, tableTypeName);
}
}
}
// Procedures
cmdText = @"
SELECT routine_name, routine_definition
FROM information_schema.routines
WHERE routine_schema = @routineSchema";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@routineSchema", SqlDbType.NVarChar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var routineName = (string)reader["routine_name"];
var routineDefinition = (string)reader["routine_definition"];
var lowercaseRoutineName = routineName.Trim().ToLowerInvariant();
this.ProcedureByLowercaseProcedureName[lowercaseRoutineName] = new Procedure(this, routineName, routineDefinition);
}
}
}
// Views
cmdText = @"
SELECT _v.table_name AS view_name,
_v.view_definition,
_u.table_name
FROM information_schema.views AS _v
LEFT JOIN information_schema.view_table_usage AS _u
ON _v.table_name = _u.view_name
AND _v.table_schema = _u.view_schema
AND _v.table_schema = _u.table_schema
WHERE _v.table_schema = @tableSchema";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@tableSchema", SqlDbType.NVarChar).Value = database.SchemaName;
using (var reader = command.ExecuteReader())
{
var viewNameOrdinal = reader.GetOrdinal("view_name");
var viewDefinitionOrdinal = reader.GetOrdinal("view_definition");
var tableNameOrdinal = reader.GetOrdinal("table_name");
while (reader.Read())
{
var viewName = reader.GetString(viewNameOrdinal);
var viewDefinition = reader.GetString(viewDefinitionOrdinal);
viewName = viewName.Trim().ToLowerInvariant();
View view;
if (!this.ViewByLowercaseViewName.TryGetValue(viewName, out view))
{
view = new View(this, viewName, viewDefinition);
this.ViewByLowercaseViewName[viewName] = view;
}
if (!reader.IsDBNull(tableNameOrdinal))
{
var tableName = reader.GetString(tableNameOrdinal);
Table table;
if (this.TableByLowercaseTableName.TryGetValue(tableName.ToLowerInvariant(), out table))
{
view.Tables.Add(table);
}
}
}
}
}
// 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 SqlCommand(cmdText, connection))
{
command.Parameters.Add("@tableSchema", SqlDbType.NVarChar).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, Index> indexByLowercaseIndexName;
if (!this.indexByLowercaseIndexNameByLowercaseTableName.TryGetValue(tableName, out indexByLowercaseIndexName))
{
indexByLowercaseIndexName = new Dictionary<string, Index>();
this.indexByLowercaseIndexNameByLowercaseTableName[tableName] = indexByLowercaseIndexName;
}
Index index;
if (!indexByLowercaseIndexName.TryGetValue(indexName, out index))
{
index = new Index(this, indexName);
indexByLowercaseIndexName[indexName] = index;
}
}
}
}
}
finally
{
connection.Close();
}
}
}
protected Mapping(Database database, string sqlTypeForObject, SqlDbType sqlDbTypeForObject)
{
this.database = database;
this.sqlTypeForObject = sqlTypeForObject;
this.sqlDbTypeForObject = sqlDbTypeForObject;
if (!this.database.MetaPopulation.IsValid)
{
throw new Exception("MetaPopulation is invalid.");
}
this.tableNameByRelationType = new Dictionary<IRelationType, string>();
this.sqlTypeByRoleType = new Dictionary<IRoleType, string>();
this.sqlDbTypeByRoleType = new Dictionary<IRoleType, SqlDbType>();
this.tableTypeNameByRelationType = new Dictionary<IRelationType, string>();
this.tableTypeSqlTypeByRelationType = new Dictionary<IRelationType, string>();
this.sqlMetaDataByRoleType = new Dictionary<IRoleType, SqlMetaData[]>();
this.sqlMetaDataForObject = new SqlMetaData(TableTypeColumnNameForObject, this.SqlDbTypeForObject);
this.procedureNameForGetRoleByRelationType = new Dictionary<IRelationType, string>();
this.procedureNameForGetAssociationByRelationType = new Dictionary<IRelationType, string>();
this.procedureNameForSetRoleByRelationType = new Dictionary<IRelationType, string>();
this.procedureNameForAddRoleByRelationType = new Dictionary<IRelationType, string>();
this.procedureNameForRemoveRoleByRelationType = new Dictionary<IRelationType, string>();
this.procedureNameForDeleteRoleByRelationType = new Dictionary<IRelationType, string>();
var sqlMetaDataBySqlType = new Dictionary<string, SqlMetaData[]>();
var sqlMetaDataForCompositeRelation = new[]
{
new SqlMetaData(TableTypeColumnNameForAssociation, this.SqlDbTypeForObject),
new SqlMetaData(TableTypeColumnNameForRole, this.SqlDbTypeForObject)
};
foreach (var relationType in this.Database.MetaPopulation.RelationTypes)
{
var tableName = "_" + relationType.Id.ToString("N");
SqlDbType sqlDbType;
string sqlType;
var sqlMetaData = sqlMetaDataForCompositeRelation;
this.procedureNameForGetRoleByRelationType.Add(relationType, "_gr_" + relationType.Id.ToString("N").ToLowerInvariant());
this.procedureNameForGetAssociationByRelationType.Add(relationType, "_ga_" + relationType.Id.ToString("N").ToLowerInvariant());
this.procedureNameForSetRoleByRelationType.Add(relationType, "_sr_" + relationType.Id.ToString("N").ToLowerInvariant());
this.procedureNameForAddRoleByRelationType.Add(relationType, "_ar_" + relationType.Id.ToString("N").ToLowerInvariant());
this.procedureNameForRemoveRoleByRelationType.Add(relationType, "_rr_" + relationType.Id.ToString("N").ToLowerInvariant());
this.procedureNameForDeleteRoleByRelationType.Add(relationType, "_dr_" + relationType.Id.ToString("N").ToLowerInvariant());
var tableTypeName = TableTypeNameForCompositeRelations;
var tableTypeSqlType = this.sqlTypeForObject;
var roleType = relationType.RoleType;
var unit = roleType.ObjectType as IUnit;
if (unit != null)
{
switch (unit.UnitTag)
{
case UnitTags.AllorsBinary:
sqlDbType = SqlDbType.VarBinary;
if (roleType.Size != -1 && roleType.Size <= 8000)
{
tableName = tableName + "_binary_" + roleType.Size;
sqlType = "varbinary(" + roleType.Size + ")";
}
else
{
tableName = tableName + "_binary";
sqlType = "varbinary(MAX)";
}
tableTypeName = "_t_bi";
tableTypeSqlType = "varbinary(MAX)";
if (!sqlMetaDataBySqlType.ContainsKey(tableTypeSqlType))
{
sqlMetaDataBySqlType[tableTypeSqlType] = new[]
{
new SqlMetaData(TableTypeColumnNameForAssociation, this.SqlDbTypeForObject),
new SqlMetaData(TableTypeColumnNameForRole, sqlDbType, -1)
};
}
sqlMetaData = sqlMetaDataBySqlType[tableTypeSqlType];
break;
case UnitTags.AllorsBoolean:
tableName = tableName + "_boolean";
sqlDbType = SqlDbType.Bit;
sqlType = "bit";
tableTypeName = "_t_bo";
tableTypeSqlType = sqlType;
if (!sqlMetaDataBySqlType.ContainsKey(tableTypeSqlType))
{
sqlMetaDataBySqlType[tableTypeSqlType] = new[]
{
new SqlMetaData(TableTypeColumnNameForAssociation, this.SqlDbTypeForObject),
new SqlMetaData(TableTypeColumnNameForRole, sqlDbType)
};
}
sqlMetaData = sqlMetaDataBySqlType[tableTypeSqlType];
break;
case UnitTags.AllorsDateTime:
tableName = tableName + "_datetime";
sqlDbType = SqlDbType.DateTime2;
sqlType = "datetime2";
tableTypeName = "_t_da";
tableTypeSqlType = sqlType;
if (!sqlMetaDataBySqlType.ContainsKey(tableTypeSqlType))
{
sqlMetaDataBySqlType[tableTypeSqlType] = new[]
{
new SqlMetaData(TableTypeColumnNameForAssociation, this.SqlDbTypeForObject),
new SqlMetaData(TableTypeColumnNameForRole, sqlDbType)
};
}
sqlMetaData = sqlMetaDataBySqlType[tableTypeSqlType];
break;
case UnitTags.AllorsDecimal:
tableName = tableName + "_decimal_" + roleType.Precision + "_" + roleType.Scale;
sqlDbType = SqlDbType.Decimal;
sqlType = "decimal(" + roleType.Precision + "," + roleType.Scale + ")";
tableTypeName = "_t_de_" + roleType.Precision + "_" + roleType.Scale;
tableTypeSqlType = sqlType;
if (!sqlMetaDataBySqlType.ContainsKey(tableTypeSqlType))
{
sqlMetaDataBySqlType[tableTypeSqlType] = new[]
{
new SqlMetaData(TableTypeColumnNameForAssociation, this.SqlDbTypeForObject),
new SqlMetaData(TableTypeColumnNameForRole, sqlDbType, (byte)roleType.Precision.Value, (byte)roleType.Scale.Value)
};
}
sqlMetaData = sqlMetaDataBySqlType[tableTypeSqlType];
break;
case UnitTags.AllorsFloat:
tableName = tableName + "_float";
sqlDbType = SqlDbType.Float;
sqlType = "float";
tableTypeName = "_t_f";
tableTypeSqlType = sqlType;
if (!sqlMetaDataBySqlType.ContainsKey(tableTypeSqlType))
{
sqlMetaDataBySqlType[tableTypeSqlType] = new[]
{
new SqlMetaData(TableTypeColumnNameForAssociation, this.SqlDbTypeForObject),
new SqlMetaData(TableTypeColumnNameForRole, sqlDbType)
};
}
sqlMetaData = sqlMetaDataBySqlType[tableTypeSqlType];
break;
case UnitTags.AllorsInteger:
tableName = tableName + "_integer";
sqlDbType = SqlDbType.Int;
sqlType = "int";
tableTypeName = "_t_i";
tableTypeSqlType = sqlType;
if (!sqlMetaDataBySqlType.ContainsKey(tableTypeSqlType))
{
sqlMetaDataBySqlType[tableTypeSqlType] = new[]
{
new SqlMetaData(TableTypeColumnNameForAssociation, this.SqlDbTypeForObject),
new SqlMetaData(TableTypeColumnNameForRole, sqlDbType)
};
}
sqlMetaData = sqlMetaDataBySqlType[tableTypeSqlType];
break;
case UnitTags.AllorsString:
sqlDbType = SqlDbType.NVarChar;
if (roleType.Size != -1 && roleType.Size <= 4000)
{
tableName = tableName + "_string_" + roleType.Size;
sqlType = "nvarchar(" + roleType.Size + ")";
}
else
{
tableName = tableName + "_string";
sqlType = "nvarchar(MAX)";
}
tableTypeName = "_t_s";
tableTypeSqlType = "nvarchar(MAX)";
if (!sqlMetaDataBySqlType.ContainsKey(tableTypeSqlType))
{
sqlMetaDataBySqlType[tableTypeSqlType] = new[]
{
new SqlMetaData(TableTypeColumnNameForAssociation, this.SqlDbTypeForObject),
new SqlMetaData(TableTypeColumnNameForRole, sqlDbType, -1)
};
}
sqlMetaData = sqlMetaDataBySqlType[tableTypeSqlType];
break;
case UnitTags.AllorsUnique:
tableName = tableName + "_unique";
sqlDbType = SqlDbType.UniqueIdentifier;
sqlType = "uniqueidentifier";
tableTypeName = "_t_u";
tableTypeSqlType = sqlType;
if (!sqlMetaDataBySqlType.ContainsKey(tableTypeSqlType))
{
sqlMetaDataBySqlType[tableTypeSqlType] = new[]
{
new SqlMetaData(TableTypeColumnNameForAssociation, this.SqlDbTypeForObject),
new SqlMetaData(TableTypeColumnNameForRole, sqlDbType)
};
}
sqlMetaData = sqlMetaDataBySqlType[tableTypeSqlType];
break;
default:
throw new NotSupportedException("Unit " + unit + "is not supported.");
}
}
else
{
if (relationType.AssociationType.IsOne)
{
if (roleType.IsOne)
{
tableName = tableName + "_11";
}
else
{
tableName = tableName + "_1m";
}
}
else
{
if (roleType.IsOne)
{
tableName = tableName + "_n1";
}
else
{
tableName = tableName + "_nm";
}
}
sqlDbType = this.SqlDbTypeForObject;
sqlType = this.SqlTypeForObject;
}
this.tableNameByRelationType[relationType] = tableName.ToLowerInvariant();
this.sqlDbTypeByRoleType[roleType] = sqlDbType;
this.sqlTypeByRoleType[roleType] = sqlType;
this.tableTypeNameByRelationType[relationType] = tableTypeName.ToLowerInvariant();
this.tableTypeSqlTypeByRelationType[relationType] = tableTypeSqlType.ToLowerInvariant();
this.sqlMetaDataByRoleType[roleType] = sqlMetaData;
}
}
public MappingInteger(Database database)
: base(database, "int", SqlDbType.Int)
{
}
public MappingLong(Database database)
: base(database, "bigint", SqlDbType.BigInt)
{
}
