public Validation(Database database)
{
this.database = database;
this.mapping = database.Mapping;
this.schema = new Schema(database);
this.MissingTableNames = new HashSet<string>();
this.InvalidTables = new HashSet<SchemaTable>();
this.MissingTableTypeNames = new HashSet<string>();
this.InvalidTableTypes = new HashSet<SchemaTableType>();
this.MissingProcedureNames = new HashSet<string>();
this.InvalidProcedures = new HashSet<SchemaProcedure>();
this.Validate();
this.isValid =
this.MissingTableNames.Count == 0 &
this.InvalidTables.Count == 0 &
this.MissingTableTypeNames.Count == 0 &
this.InvalidTableTypes.Count == 0 &
this.MissingProcedureNames.Count == 0 &
this.InvalidProcedures.Count == 0;
}
internal Load(Database database, ObjectNotLoadedEventHandler objectNotLoaded, RelationNotLoadedEventHandler relationNotLoaded, XmlReader reader)
{
this.database = database;
this.objectNotLoaded = objectNotLoaded;
this.relationNotLoaded = relationNotLoaded;
this.reader = reader;
this.objectTypeByObjectId = new Dictionary<ObjectId, IObjectType>();
this.objectVersionByObjectId = new Dictionary<ObjectId, ObjectVersion>();
}
public Schema(Database database)
{
this.tableByName = new Dictionary<string, SchemaTable>();
this.tableTypeByName = new Dictionary<string, SchemaTableType>();
this.procedureByName = new Dictionary<string, SchemaProcedure>();
this.indexByIndexNameByTableName = new Dictionary<string, Dictionary<string, SchemaIndex>>();
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();
tableName = database.Mapping.NormalizeName(tableName);
var fullyQualifiedTableName = database.SchemaName + "." + tableName;
columnName = database.Mapping.NormalizeName(columnName);
SchemaTable table;
if (!this.tableByName.TryGetValue(fullyQualifiedTableName, out 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;
}
}
}
}
// Table Types
cmdText = @"
select tt.name as table_name, c.name as column_name, t.name AS data_type, c.max_length, c.precision, c.scale
from sys.table_types tt
inner join sys.columns c on c.object_id = tt.type_table_object_id
INNER JOIN sys.types AS t ON t.user_type_id = c.user_type_id
where tt.schema_id = SCHEMA_ID(@domainSchema)";
using (var command = new SqlCommand(cmdText, connection))
{
command.Parameters.Add("@domainSchema", 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 maximumLengthOrdinal = reader.GetOrdinal("max_length");
var precisionOrdinal = reader.GetOrdinal("precision");
var scaleOrdinal = reader.GetOrdinal("scale");
while (reader.Read())
{
var tableName = reader.GetString(tableNameOrdinal);
var columnName = reader.GetString(columnNameOrdinal);
var dataType = reader.GetString(dataTypeOrdinal).Trim().ToLowerInvariant();
var maximumLength = reader.IsDBNull(maximumLengthOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(maximumLengthOrdinal));
var precision = reader.IsDBNull(precisionOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(precisionOrdinal));
var scale = reader.IsDBNull(scaleOrdinal) ? (int?)null : Convert.ToInt32(reader.GetValue(scaleOrdinal));
tableName = tableName.Trim().ToLowerInvariant();
var fullyQualifiedTableName = database.SchemaName + "." + tableName;
SchemaTableType tableType;
if (!this.tableTypeByName.TryGetValue(fullyQualifiedTableName, out tableType))
{
tableType = new SchemaTableType(this, fullyQualifiedTableName);
this.tableTypeByName[fullyQualifiedTableName] = tableType;
}
if (!reader.IsDBNull(columnNameOrdinal))
{
var column = new SchemaTableTypeColumn(tableType, columnName, dataType, maximumLength, precision, scale);
tableType.ColumnByLowercaseColumnName[column.Name] = column;
}
}
}
}
// 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();
var fullyQualifiedName = database.SchemaName + "." + lowercaseRoutineName;
this.procedureByName[fullyQualifiedName] = new SchemaProcedure(this, 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 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, 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();
}
}
}
public Mapping(Database database, bool isObjectIdInteger)
{
this.database = database;
this.IsObjectIdInteger = isObjectIdInteger;
if (this.IsObjectIdInteger)
{
this.SqlTypeForObject = "int";
this.SqlDbTypeForObject = SqlDbType.Int;
}
else
{
this.SqlTypeForObject = "bigint";
this.SqlDbTypeForObject = SqlDbType.BigInt;
}
// TableTypes
// ----------
this.TableTypeNameForObject = database.SchemaName + "." + "_t_o";
this.TableTypeNameForVersionedObject = database.SchemaName + "." + "_t_vo";
this.TableTypeNameForCompositeRelation = database.SchemaName + "." + "_t_c";
this.TableTypeNameForStringRelation = database.SchemaName + "." + "_t_s";
this.TableTypeNameForIntegerRelation = database.SchemaName + "." + "_t_i";
this.TableTypeNameForFloatRelation = database.SchemaName + "." + "_t_f";
this.TableTypeNameForBooleanRelation = database.SchemaName + "." + "_t_bo";
this.TableTypeNameForDateTimeRelation = database.SchemaName + "." + "_t_da";
this.TableTypeNameForUniqueRelation = database.SchemaName + "." + "_t_u";
this.TableTypeNameForBinaryRelation = database.SchemaName + "." + "_t_bi";
this.TableTypeNamePrefixForDecimalRelation = database.SchemaName + "." + "_t_de";
this.TableTypeColumnNameForObject = "_o";
this.TableTypeColumnNameForCache = "_c";
this.TableTypeColumnNameForAssociation = "_a";
this.TableTypeColumnNameForRole = "_r";
this.TableTypeNameForDecimalRelationByScaleByPrecision = new Dictionary<int, Dictionary<int, string>>();
foreach (var relationType in database.MetaPopulation.RelationTypes)
{
var roleType = relationType.RoleType;
if (roleType.ObjectType.IsUnit && ((IUnit)roleType.ObjectType).IsDecimal)
{
var precision = roleType.Precision;
var scale = roleType.Scale;
var tableName = this.TableTypeNamePrefixForDecimalRelation + precision + "_" + scale;
Dictionary<int, string> decimalRelationTableByScale;
if (!this.TableTypeNameForDecimalRelationByScaleByPrecision.TryGetValue(precision.Value, out decimalRelationTableByScale))
{
decimalRelationTableByScale = new Dictionary<int, string>();
this.TableTypeNameForDecimalRelationByScaleByPrecision[precision.Value] = decimalRelationTableByScale;
}
if (!decimalRelationTableByScale.ContainsKey(scale.Value))
{
decimalRelationTableByScale[scale.Value] = tableName;
}
}
}
this.tableTypeDefinitionByName = new Dictionary<string, string>();
{
var sql = new StringBuilder();
sql.Append("CREATE TYPE " + this.TableTypeNameForObject + " AS TABLE\n");
sql.Append("(" + this.TableTypeColumnNameForObject + " " + this.SqlTypeForObject + ")\n");
this.tableTypeDefinitionByName.Add(this.TableTypeNameForObject, sql.ToString());
}
{
var sql = new StringBuilder();
sql.Append("CREATE TYPE " + this.TableTypeNameForVersionedObject + " AS TABLE\n");
sql.Append("(" + this.TableTypeColumnNameForObject + " " + this.SqlTypeForObject + ",\n");
sql.Append(this.TableTypeColumnNameForCache + " " + SqlTypeForCache + ")\n");
this.tableTypeDefinitionByName.Add(this.TableTypeNameForVersionedObject, sql.ToString());
}
{
var sql = new StringBuilder();
sql.Append("CREATE TYPE " + this.TableTypeNameForCompositeRelation + " AS TABLE\n");
sql.Append("(" + this.TableTypeColumnNameForAssociation + " " + this.SqlTypeForObject + ",\n");
sql.Append(this.TableTypeColumnNameForRole + " " + this.SqlTypeForObject + ")\n");
this.tableTypeDefinitionByName.Add(this.TableTypeNameForCompositeRelation, sql.ToString());
}
{
var sql = new StringBuilder();
sql.Append("CREATE TYPE " + this.TableTypeNameForStringRelation + " AS TABLE\n");
sql.Append("(" + this.TableTypeColumnNameForAssociation + " " + this.SqlTypeForObject + ",\n");
sql.Append(this.TableTypeColumnNameForRole + " nvarchar(max))\n");
this.tableTypeDefinitionByName.Add(this.TableTypeNameForStringRelation, sql.ToString());
}
{
var sql = new StringBuilder();
sql.Append("CREATE TYPE " + this.TableTypeNameForIntegerRelation + " AS TABLE\n");
sql.Append("(" + this.TableTypeColumnNameForAssociation + " " + this.SqlTypeForObject + ",\n");
sql.Append(this.TableTypeColumnNameForRole + " int)\n");
this.tableTypeDefinitionByName.Add(this.TableTypeNameForIntegerRelation, sql.ToString());
}
{
var sql = new StringBuilder();
sql.Append("CREATE TYPE " + this.TableTypeNameForFloatRelation + " AS TABLE\n");
sql.Append("(" + this.TableTypeColumnNameForAssociation + " " + this.SqlTypeForObject + ",\n");
sql.Append(this.TableTypeColumnNameForRole + " float)\n");
this.tableTypeDefinitionByName.Add(this.TableTypeNameForFloatRelation, sql.ToString());
}
{
var sql = new StringBuilder();
sql.Append("CREATE TYPE " + this.TableTypeNameForDateTimeRelation + " AS TABLE\n");
sql.Append("(" + this.TableTypeColumnNameForAssociation + " " + this.SqlTypeForObject + ",\n");
sql.Append(this.TableTypeColumnNameForRole + " datetime2)\n");
this.tableTypeDefinitionByName.Add(this.TableTypeNameForDateTimeRelation, sql.ToString());
}
{
var sql = new StringBuilder();
sql.Append("CREATE TYPE " + this.TableTypeNameForBooleanRelation + " AS TABLE\n");
sql.Append("(" + this.TableTypeColumnNameForAssociation + " " + this.SqlTypeForObject + ",\n");
sql.Append(this.TableTypeColumnNameForRole + " bit)\n");
this.tableTypeDefinitionByName.Add(this.TableTypeNameForBooleanRelation, sql.ToString());
}
{
var sql = new StringBuilder();
sql.Append("CREATE TYPE " + this.TableTypeNameForUniqueRelation + " AS TABLE\n");
sql.Append("(" + this.TableTypeColumnNameForAssociation + " " + this.SqlTypeForObject + ",\n");
sql.Append(this.TableTypeColumnNameForRole + " uniqueidentifier)\n");
this.tableTypeDefinitionByName.Add(this.TableTypeNameForUniqueRelation, sql.ToString());
}
{
var sql = new StringBuilder();
sql.Append("CREATE TYPE " + this.TableTypeNameForBinaryRelation + " AS TABLE\n");
sql.Append("(" + this.TableTypeColumnNameForAssociation + " " + this.SqlTypeForObject + ",\n");
sql.Append(this.TableTypeColumnNameForRole + " varbinary(max))\n");
this.tableTypeDefinitionByName.Add(this.TableTypeNameForBinaryRelation, sql.ToString());
}
foreach (var precisionEntry in this.TableTypeNameForDecimalRelationByScaleByPrecision)
{
var precision = precisionEntry.Key;
foreach (var scaleEntry in precisionEntry.Value)
{
var scale = scaleEntry.Key;
var decimalRelationTable = scaleEntry.Value;
var sql = new StringBuilder();
sql.Append("CREATE TYPE " + decimalRelationTable + " AS TABLE\n");
sql.Append("(" + this.TableTypeColumnNameForAssociation + " " + this.SqlTypeForObject + ",\n");
sql.Append(this.TableTypeColumnNameForRole + " DECIMAL(" + precision + "," + scale + ") )\n");
this.tableTypeDefinitionByName.Add(decimalRelationTable, sql.ToString());
}
}
// Tables
// ------
this.TableNameForObjects = database.SchemaName + "." + "_o";
this.TableNameForObjectByClass = new Dictionary<IClass, string>();
this.ColumnNameByRelationType = new Dictionary<IRelationType, string>();
this.ParamNameByRoleType = new Dictionary<IRoleType, string>();
foreach (var @class in this.Database.MetaPopulation.Classes)
{
this.TableNameForObjectByClass.Add(@class, this.database.SchemaName + "." + this.NormalizeName(@class.SingularName));
foreach (var associationType in @class.AssociationTypes)
{
var relationType = associationType.RelationType;
var roleType = relationType.RoleType;
if (!(associationType.IsMany && roleType.IsMany) && relationType.ExistExclusiveClasses && roleType.IsMany)
{
this.ColumnNameByRelationType[relationType] = this.NormalizeName(associationType.SingularPropertyName);
}
}
foreach (var roleType in @class.RoleTypes)
{
var relationType = roleType.RelationType;
var associationType3 = relationType.AssociationType;
if (roleType.ObjectType.IsUnit)
{
this.ColumnNameByRelationType[relationType] = this.NormalizeName(roleType.SingularPropertyName);
this.ParamNameByRoleType[roleType] = string.Format(ParamFormat, roleType.SingularFullName);
}
else
{
if (!(associationType3.IsMany && roleType.IsMany) && relationType.ExistExclusiveClasses && !roleType.IsMany)
{
this.ColumnNameByRelationType[relationType] = this.NormalizeName(roleType.SingularPropertyName);
}
}
}
}
this.TableNameForRelationByRelationType = new Dictionary<IRelationType, string>();
foreach (var relationType in this.Database.MetaPopulation.RelationTypes)
{
var associationType = relationType.AssociationType;
var roleType = relationType.RoleType;
if (!roleType.ObjectType.IsUnit && ((associationType.IsMany && roleType.IsMany) || !relationType.ExistExclusiveClasses))
{
this.TableNameForRelationByRelationType.Add(relationType, this.database.SchemaName + "." + this.NormalizeName(relationType.RoleType.SingularFullName));
}
}
// Procedures
// ----------
this.procedureDefinitionByName = new Dictionary<string, string>();
this.ProcedureNameForLoadObjectByClass = new Dictionary<IClass, string>();
this.ProcedureNameForCreateObjectByClass = new Dictionary<IClass, string>();
this.ProcedureNameForCreateObjectsByClass = new Dictionary<IClass, string>();
this.ProcedureNameForGetUnitRolesByClass = new Dictionary<IClass, string>();
this.ProcedureNameForPrefetchUnitRolesByClass = new Dictionary<IClass, string>();
this.ProcedureNameForSetUnitRoleByRelationTypeByClass = new Dictionary<IClass, Dictionary<IRelationType, string>>();
this.ProcedureNameForGetRoleByRelationType = new Dictionary<IRelationType, string>();
this.ProcedureNameForPrefetchRoleByRelationType = new Dictionary<IRelationType, string>();
this.ProcedureNameForSetRoleByRelationType = new Dictionary<IRelationType, string>();
this.ProcedureNameForAddRoleByRelationType = new Dictionary<IRelationType, string>();
this.ProcedureNameForRemoveRoleByRelationType = new Dictionary<IRelationType, string>();
this.ProcedureNameForClearRoleByRelationType = new Dictionary<IRelationType, string>();
this.ProcedureNameForGetAssociationByRelationType = new Dictionary<IRelationType, string>();
this.ProcedureNameForPrefetchAssociationByRelationType = new Dictionary<IRelationType, string>();
// Get Cache Ids
this.ProcedureNameForGetCache = this.Database.SchemaName + "." + ProcedurePrefixForGetCache;
var definition =
@"CREATE PROCEDURE " + this.ProcedureNameForGetCache + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
SELECT " + ColumnNameForObject + ", " + ColumnNameForCache + @"
FROM " + this.TableNameForObjects + @"
WHERE " + ColumnNameForObject + " IN (SELECT " + this.TableTypeColumnNameForObject + @" FROM " + ParamNameForTableType + ")";
this.procedureDefinitionByName.Add(this.ProcedureNameForGetCache, definition);
// Set Cache Ids
this.ProcedureNameForSetCache = this.Database.SchemaName + "." + ProcedurePrefixForSetCache;
definition =
@"CREATE PROCEDURE " + this.ProcedureNameForSetCache + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForVersionedObject + @" READONLY
AS
UPDATE " + this.TableNameForObjects + @"
SET " + ColumnNameForCache + " = r." + this.TableTypeColumnNameForCache + @"
FROM " + this.TableNameForObjects + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + ColumnNameForObject + " = r." + this.TableTypeColumnNameForObject + @"
";
this.procedureDefinitionByName.Add(this.ProcedureNameForSetCache, definition);
// Update Cache Ids
this.ProcedureNameForUpdateCache = this.Database.SchemaName + "." + ProcedurePrefixForUpdateCache;
definition =
@"CREATE PROCEDURE " + this.ProcedureNameForUpdateCache + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
UPDATE " + this.TableNameForObjects + @"
SET " + ColumnNameForCache + " = " + ColumnNameForCache + @" + 1
FROM " + this.TableNameForObjects + @"
WHERE " + ColumnNameForObject + " IN ( SELECT " + this.TableTypeColumnNameForObject + " FROM " + ParamNameForTableType + ");\n\n";
this.procedureDefinitionByName.Add(this.ProcedureNameForUpdateCache, definition);
foreach (var @class in this.Database.MetaPopulation.Classes)
{
var className = @class.Name.ToLowerInvariant();
var table = this.TableNameForObjectByClass[@class];
// Load Objects
this.ProcedureNameForLoadObjectByClass.Add(@class, this.Database.SchemaName + "." + ProcedurePrefixForLoad + className);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForLoadObjectByClass[@class] + @"
" + ParamNameForType + @" " + SqlTypeForType + @",
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
INSERT INTO " + table + " (" + ColumnNameForType + ", " + ColumnNameForObject + @")
SELECT " + ParamNameForType + @", " + this.TableTypeColumnNameForObject + @"
FROM " + ParamNameForTableType + "\n";
this.procedureDefinitionByName.Add(this.ProcedureNameForLoadObjectByClass[@class], definition);
// CreateObject
this.ProcedureNameForCreateObjectByClass.Add(@class, this.Database.SchemaName + "." + ProcedurePrefixForCreateObject + className);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForCreateObjectByClass[@class] + @"
" + ParamNameForType + @" " + SqlTypeForType + @"
AS
DECLARE " + ParamNameForObject + " AS " + this.SqlTypeForObject + @"
INSERT INTO " + this.TableNameForObjects + " (" + ColumnNameForType + ", " + ColumnNameForCache + @")
VALUES (" + ParamNameForType + ", " + Reference.InitialCacheId + @");
SELECT " + ParamNameForObject + @" = SCOPE_IDENTITY();
INSERT INTO " + table + " (" + ColumnNameForObject + "," + ColumnNameForType + @")
VALUES (" + ParamNameForObject + "," + ParamNameForType + @");
SELECT " + ParamNameForObject + @";";
this.procedureDefinitionByName.Add(this.ProcedureNameForCreateObjectByClass[@class], definition);
// CreateObjects
this.ProcedureNameForCreateObjectsByClass.Add(@class, this.Database.SchemaName + "." + ProcedurePrefixForCreateObjects + className);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForCreateObjectsByClass[@class] + @"
" + ParamNameForType + @" " + SqlTypeForType + @",
" + ParamNameForCount + @" " + SqlTypeForCount + @"
AS
BEGIN
DECLARE @IDS TABLE (id INT);
DECLARE @O INT, @COUNTER INT
SET @COUNTER = 0
WHILE @COUNTER < " + ParamNameForCount + @"
BEGIN
INSERT INTO " + this.TableNameForObjects + " (" + ColumnNameForType + ", " + ColumnNameForCache + @")
VALUES (" + ParamNameForType + ", " + Reference.InitialCacheId + @" );
INSERT INTO @IDS(id)
VALUES (SCOPE_IDENTITY());
SET @COUNTER = @COUNTER+1;
END
INSERT INTO " + this.TableNameForObjectByClass[@class.ExclusiveClass] + " (" + ColumnNameForObject + "," + ColumnNameForType + @")
SELECT ID," + ParamNameForType + @" FROM @IDS;
SELECT id FROM @IDS;
END";
this.procedureDefinitionByName.Add(this.ProcedureNameForCreateObjectsByClass[@class], definition);
var sortedUnitRoleTypes = this.Database.GetSortedUnitRolesByObjectType(@class);
if (sortedUnitRoleTypes.Length > 0)
{
{
// Get Unit Roles
this.ProcedureNameForGetUnitRolesByClass.Add(@class, this.Database.SchemaName + "." + ProcedurePrefixForGetUnits + className);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForGetUnitRolesByClass[@class] + @"
" + ParamNameForObject + " AS " + this.SqlTypeForObject + @"
AS
SELECT ";
var first = true;
foreach (var role in sortedUnitRoleTypes)
{
if (first)
{
first = false;
}
else
{
definition += ", ";
}
definition += this.ColumnNameByRelationType[role.RelationType];
}
definition += @"
FROM " + this.TableNameForObjectByClass[@class.ExclusiveClass] + @"
WHERE " + ColumnNameForObject + "=" + ParamNameForObject;
this.procedureDefinitionByName.Add(this.ProcedureNameForGetUnitRolesByClass[@class], definition);
}
{
// Prefetch Unit Roles
this.ProcedureNameForPrefetchUnitRolesByClass.Add(@class, this.Database.SchemaName + "." + ProcedurePrefixForPrefetchUnits + className);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForPrefetchUnitRolesByClass[@class] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
SELECT " + ColumnNameForObject + ", ";
var first = true;
foreach (var role in sortedUnitRoleTypes)
{
if (first)
{
first = false;
}
else
{
definition += ", ";
}
definition += this.ColumnNameByRelationType[role.RelationType];
}
definition += @"
FROM " + this.TableNameForObjectByClass[@class.ExclusiveClass] + @"
WHERE " + ColumnNameForObject + " IN (SELECT " + this.TableTypeColumnNameForObject + @" FROM " + ParamNameForTableType + ")";
this.procedureDefinitionByName.Add(this.ProcedureNameForPrefetchUnitRolesByClass[@class], definition);
}
}
foreach (var associationType in @class.AssociationTypes)
{
var relationType = associationType.RelationType;
var roleType = relationType.RoleType;
var relationTypeName = roleType.SingularFullName.ToLowerInvariant();
if (!(associationType.IsMany && roleType.IsMany) && relationType.ExistExclusiveClasses && roleType.IsMany)
{
// Get Composites Role (1-*) [object table]
this.ProcedureNameForGetRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForGetRole + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForGetRoleByRelationType[relationType] + @"
" + ParamNameForAssociation + @" " + this.SqlTypeForObject + @"
AS
SELECT " + ColumnNameForObject + @"
FROM " + table + @"
WHERE " + this.ColumnNameByRelationType[relationType] + "=" + ParamNameForAssociation;
this.procedureDefinitionByName.Add(this.ProcedureNameForGetRoleByRelationType[relationType], definition);
// Prefetch Composites Role (1-*) [object table]
this.ProcedureNameForPrefetchRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForPrefetchRole + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForPrefetchRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
SELECT " + this.ColumnNameByRelationType[relationType] + ", " + ColumnNameForObject + @"
FROM " + table + @"
WHERE " + this.ColumnNameByRelationType[relationType] + " IN (SELECT " + this.TableTypeColumnNameForObject + @" FROM " + ParamNameForTableType + ")";
this.procedureDefinitionByName.Add(this.ProcedureNameForPrefetchRoleByRelationType[relationType], definition);
if (associationType.IsOne)
{
// Get Composite Association (1-*) [object table]
this.ProcedureNameForGetAssociationByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForGetAssociation + className + "_" + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForGetAssociationByRelationType[relationType] + @"
" + ParamNameForCompositeRole + @" " + this.SqlTypeForObject + @"
AS
SELECT " + this.ColumnNameByRelationType[relationType] + @"
FROM " + table + @"
WHERE " + ColumnNameForObject + "=" + ParamNameForCompositeRole;
this.procedureDefinitionByName.Add(this.ProcedureNameForGetAssociationByRelationType[relationType], definition);
// Prefetch Composite Association (1-*) [object table]
this.ProcedureNameForPrefetchAssociationByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForPrefetchAssociation + className + "_" + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForPrefetchAssociationByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
SELECT " + this.ColumnNameByRelationType[relationType] + ", " + ColumnNameForObject + @"
FROM " + table + @"
WHERE " + ColumnNameForObject + " IN (SELECT " + this.TableTypeColumnNameForObject + @" FROM " + ParamNameForTableType + ")";
this.procedureDefinitionByName.Add(this.ProcedureNameForPrefetchAssociationByRelationType[relationType], definition);
}
// Add Composite Role (1-*) [object table]
this.ProcedureNameForAddRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForAddRole + className + "_" + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForAddRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForCompositeRelation + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + " = r." + this.TableTypeColumnNameForAssociation + @"
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + ColumnNameForObject + " = r." + this.TableTypeColumnNameForRole;
this.procedureDefinitionByName.Add(this.ProcedureNameForAddRoleByRelationType[relationType], definition);
// Remove Composite Role (1-*) [object table]
this.ProcedureNameForRemoveRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForRemoveRole + className + "_" + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForRemoveRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForCompositeRelation + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + @" = null
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + this.ColumnNameByRelationType[relationType] + " = r." + this.TableTypeColumnNameForAssociation + @" AND
" + ColumnNameForObject + " = r." + this.TableTypeColumnNameForRole;
this.procedureDefinitionByName.Add(this.ProcedureNameForRemoveRoleByRelationType[relationType], definition);
// Clear Composites Role (1-*) [object table]
this.ProcedureNameForClearRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForClearRole + className + "_" + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForClearRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + @" = null
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS a
ON " + this.ColumnNameByRelationType[relationType] + " = a." + this.TableTypeColumnNameForObject;
this.procedureDefinitionByName.Add(this.ProcedureNameForClearRoleByRelationType[relationType], definition);
}
}
var procedureNameForSetUnitRoleByRelationType = new Dictionary<IRelationType, string>();
this.ProcedureNameForSetUnitRoleByRelationTypeByClass.Add(@class, procedureNameForSetUnitRoleByRelationType);
foreach (var roleType in @class.RoleTypes)
{
var relationType = roleType.RelationType;
var associationType = relationType.AssociationType;
var relationTypeName = roleType.SingularFullName.ToLowerInvariant();
if (roleType.ObjectType.IsUnit)
{
procedureNameForSetUnitRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForSetRole + className + "_" + relationTypeName);
var unitTypeTag = ((IUnit)relationType.RoleType.ObjectType).UnitTag;
switch (unitTypeTag)
{
case UnitTags.AllorsString:
// Set String Role
definition = "CREATE PROCEDURE " + procedureNameForSetUnitRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForStringRelation + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + " = r." + this.TableTypeColumnNameForRole + @"
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + ColumnNameForObject + " = r." + this.TableTypeColumnNameForAssociation + @"
";
break;
case UnitTags.AllorsInteger:
// Set Integer Role
definition = "CREATE PROCEDURE " + procedureNameForSetUnitRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForIntegerRelation + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + " = r." + this.TableTypeColumnNameForRole + @"
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + ColumnNameForObject + " = r." + this.TableTypeColumnNameForAssociation + @"
";
break;
case UnitTags.AllorsFloat:
// Set Double Role
definition = "CREATE PROCEDURE " + procedureNameForSetUnitRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForFloatRelation + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + " = r." + this.TableTypeColumnNameForRole + @"
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + ColumnNameForObject + " = r." + this.TableTypeColumnNameForAssociation + @"
";
break;
case UnitTags.AllorsDecimal:
// Set Decimal Role
var decimalRelationTable = this.TableTypeNameForDecimalRelationByScaleByPrecision[roleType.Precision.Value][roleType.Scale.Value];
definition = "CREATE PROCEDURE " + procedureNameForSetUnitRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + decimalRelationTable + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + " = r." + this.TableTypeColumnNameForRole + @"
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + ColumnNameForObject + " = r." + this.TableTypeColumnNameForAssociation + @"
";
break;
case UnitTags.AllorsBoolean:
// Set Boolean Role
definition = "CREATE PROCEDURE " + procedureNameForSetUnitRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForBooleanRelation + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + " = r." + this.TableTypeColumnNameForRole + @"
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + ColumnNameForObject + " = r." + this.TableTypeColumnNameForAssociation + @"
";
break;
case UnitTags.AllorsDateTime:
// Set DateTime Role
definition = "CREATE PROCEDURE " + procedureNameForSetUnitRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForDateTimeRelation + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + " = r." + this.TableTypeColumnNameForRole + @"
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + ColumnNameForObject + " = r." + this.TableTypeColumnNameForAssociation + @"
";
break;
case UnitTags.AllorsUnique:
// Set Unique Role
definition = "CREATE PROCEDURE " + procedureNameForSetUnitRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForUniqueRelation + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + " = r." + this.TableTypeColumnNameForRole + @"
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + ColumnNameForObject + " = r." + this.TableTypeColumnNameForAssociation + @"
";
break;
case UnitTags.AllorsBinary:
// Set Binary Role
definition = "CREATE PROCEDURE " + procedureNameForSetUnitRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForBinaryRelation + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + " = r." + this.TableTypeColumnNameForRole + @"
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + ColumnNameForObject + " = r." + this.TableTypeColumnNameForAssociation + @"
";
break;
default:
throw new ArgumentException("Unknown Unit ObjectType: " + roleType.ObjectType.SingularName);
}
this.procedureDefinitionByName.Add(procedureNameForSetUnitRoleByRelationType[relationType], definition);
}
else
{
if (!(associationType.IsMany && roleType.IsMany) && relationType.ExistExclusiveClasses && roleType.IsOne)
{
// Get Composite Role (1-1 and *-1) [object table]
this.ProcedureNameForGetRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForGetRole + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForGetRoleByRelationType[relationType] + @"
" + ParamNameForAssociation + @" " + this.SqlTypeForObject + @"
AS
SELECT " + this.ColumnNameByRelationType[relationType] + @"
FROM " + table + @"
WHERE " + ColumnNameForObject + "=" + ParamNameForAssociation;
this.procedureDefinitionByName.Add(this.ProcedureNameForGetRoleByRelationType[relationType], definition);
// Prefetch Composite Role (1-1 and *-1) [object table]
this.ProcedureNameForPrefetchRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForPrefetchRole + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForPrefetchRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
SELECT " + ColumnNameForObject + ", " + this.ColumnNameByRelationType[relationType] + @"
FROM " + table + @"
WHERE " + ColumnNameForObject + " IN (SELECT " + this.TableTypeColumnNameForObject + @" FROM " + ParamNameForTableType + ")";
this.procedureDefinitionByName.Add(this.ProcedureNameForPrefetchRoleByRelationType[relationType], definition);
if (associationType.IsOne)
{
// Get Composite Association (1-1) [object table]
this.ProcedureNameForGetAssociationByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForGetAssociation + className + "_" + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForGetAssociationByRelationType[relationType] + @"
" + ParamNameForCompositeRole + @" " + this.SqlTypeForObject + @"
AS
SELECT " + ColumnNameForObject + @"
FROM " + table + @"
WHERE " + this.ColumnNameByRelationType[relationType] + "=" + ParamNameForCompositeRole;
this.procedureDefinitionByName.Add(this.ProcedureNameForGetAssociationByRelationType[relationType], definition);
// Prefetch Composite Association (1-1) [object table]
this.ProcedureNameForPrefetchAssociationByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForPrefetchAssociation + className + "_" + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForPrefetchAssociationByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
SELECT " + ColumnNameForObject + ", " + this.ColumnNameByRelationType[relationType] + @"
FROM " + table + @"
WHERE " + this.ColumnNameByRelationType[relationType] + " IN (SELECT " + this.TableTypeColumnNameForObject + @" FROM " + ParamNameForTableType + ")";
this.procedureDefinitionByName.Add(this.ProcedureNameForPrefetchAssociationByRelationType[relationType], definition);
}
else
{
// Get Composite Association (*-1) [object table]
this.ProcedureNameForGetAssociationByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForGetAssociation + className + "_" + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForGetAssociationByRelationType[relationType] + @"
" + ParamNameForCompositeRole + @" " + this.SqlTypeForObject + @"
AS
SELECT " + ColumnNameForObject + @"
FROM " + table + @"
WHERE " + this.ColumnNameByRelationType[relationType] + "=" + ParamNameForCompositeRole;
this.procedureDefinitionByName.Add(this.ProcedureNameForGetAssociationByRelationType[relationType], definition);
// Prefetch Composite Association (*-1) [object table]
this.ProcedureNameForPrefetchAssociationByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForPrefetchAssociation + className + "_" + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForPrefetchAssociationByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
SELECT " + ColumnNameForObject + ", " + this.ColumnNameByRelationType[relationType] + @"
FROM " + table + @"
WHERE " + this.ColumnNameByRelationType[relationType] + " IN (SELECT " + this.TableTypeColumnNameForObject + @" FROM " + ParamNameForTableType + ")";
this.procedureDefinitionByName.Add(this.ProcedureNameForPrefetchAssociationByRelationType[relationType], definition);
}
// Set Composite Role (1-1 and *-1) [object table]
this.ProcedureNameForSetRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForSetRole + className + "_" + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForSetRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForCompositeRelation + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + " = r." + this.TableTypeColumnNameForRole + @"
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS r
ON " + ColumnNameForObject + " = r." + this.TableTypeColumnNameForAssociation + @"
";
this.procedureDefinitionByName.Add(this.ProcedureNameForSetRoleByRelationType[relationType], definition);
// Clear Composite Role (1-1 and *-1) [object table]
this.ProcedureNameForClearRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForClearRole + className + "_" + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForClearRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
UPDATE " + table + @"
SET " + this.ColumnNameByRelationType[relationType] + @" = null
FROM " + table + @"
INNER JOIN " + ParamNameForTableType + @" AS a
ON " + ColumnNameForObject + " = a." + this.TableTypeColumnNameForObject;
this.procedureDefinitionByName.Add(this.ProcedureNameForClearRoleByRelationType[relationType], definition);
}
}
}
}
foreach (var relationType in this.Database.MetaPopulation.RelationTypes)
{
var associationType = relationType.AssociationType;
var roleType = relationType.RoleType;
var relationTypeName = roleType.SingularFullName.ToLowerInvariant();
if (!roleType.ObjectType.IsUnit && ((associationType.IsMany && roleType.IsMany) || !relationType.ExistExclusiveClasses))
{
var table = this.TableNameForRelationByRelationType[relationType];
this.ProcedureNameForPrefetchAssociationByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForPrefetchAssociation + relationTypeName);
this.ProcedureNameForClearRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForClearRole + relationTypeName);
if (roleType.IsMany)
{
// Get Composites Role (1-* and *-*) [relation table]
this.ProcedureNameForGetRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForGetRole + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForGetRoleByRelationType[relationType] + @"
" + ParamNameForAssociation + @" " + this.SqlTypeForObject + @"
AS
SELECT " + ColumnNameForRole + @"
FROM " + table + @"
WHERE " + ColumnNameForAssociation + "=" + ParamNameForAssociation;
this.procedureDefinitionByName.Add(this.ProcedureNameForGetRoleByRelationType[relationType], definition);
// Prefetch Composites Role (1-* and *-*) [relation table]
this.ProcedureNameForPrefetchRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForPrefetchRole + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForPrefetchRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
SELECT " + ColumnNameForAssociation + ", " + ColumnNameForRole + @"
FROM " + table + @"
WHERE " + ColumnNameForAssociation + " IN (SELECT " + this.TableTypeColumnNameForObject + @" FROM " + ParamNameForTableType + ")";
this.procedureDefinitionByName.Add(this.ProcedureNameForPrefetchRoleByRelationType[relationType], definition);
// Add Composite Role (1-* and *-*) [relation table]
this.ProcedureNameForAddRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForAddRole + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForAddRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForCompositeRelation + @" READONLY
AS
INSERT INTO " + table + " (" + ColumnNameForAssociation + "," + ColumnNameForRole + @")
SELECT " + this.TableTypeColumnNameForAssociation + @", " + this.TableTypeColumnNameForRole + @"
FROM " + ParamNameForTableType + "\n";
this.procedureDefinitionByName.Add(this.ProcedureNameForAddRoleByRelationType[relationType], definition);
// Remove Composite Role (1-* and *-*) [relation table]
this.ProcedureNameForRemoveRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForRemoveRole + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForRemoveRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForCompositeRelation + @" READONLY
AS
DELETE T
FROM " + table + @" T
INNER JOIN " + ParamNameForTableType + @" R
ON T." + ColumnNameForAssociation + " = R." + this.TableTypeColumnNameForAssociation + @"
AND T." + ColumnNameForRole + " = R." + this.TableTypeColumnNameForRole + @";";
this.procedureDefinitionByName.Add(this.ProcedureNameForRemoveRoleByRelationType[relationType], definition);
}
else
{
// Get Composite Role (1-1 and *-1) [relation table]
this.ProcedureNameForGetRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForGetRole + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForGetRoleByRelationType[relationType] + @"
" + ParamNameForAssociation + @" " + this.SqlTypeForObject + @"
AS
SELECT " + ColumnNameForRole + @"
FROM " + table + @"
WHERE " + ColumnNameForAssociation + "=" + ParamNameForAssociation;
this.procedureDefinitionByName.Add(this.ProcedureNameForGetRoleByRelationType[relationType], definition);
// Prefetch Composite Role (1-1 and *-1) [relation table]
this.ProcedureNameForPrefetchRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForPrefetchRole + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForPrefetchRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
SELECT " + ColumnNameForAssociation + ", " + ColumnNameForRole + @"
FROM " + table + @"
WHERE " + ColumnNameForAssociation + " IN (SELECT " + this.TableTypeColumnNameForObject + @" FROM " + ParamNameForTableType + ")";
this.procedureDefinitionByName.Add(this.ProcedureNameForPrefetchRoleByRelationType[relationType], definition);
// Set Composite Role (1-1 and *-1) [relation table]
this.ProcedureNameForSetRoleByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForSetRole + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForSetRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForCompositeRelation + @" READONLY
AS
MERGE " + table + @" T
USING " + ParamNameForTableType + @" AS r
ON T." + ColumnNameForAssociation + @" = r." + this.TableTypeColumnNameForAssociation + @"
WHEN MATCHED THEN
UPDATE SET " + ColumnNameForRole + @"= r." + this.TableTypeColumnNameForRole + @"
WHEN NOT MATCHED THEN
INSERT (" + ColumnNameForAssociation + "," + ColumnNameForRole + @")
VALUES (r." + this.TableTypeColumnNameForAssociation + ", r." + this.TableTypeColumnNameForRole + @");";
this.procedureDefinitionByName.Add(this.ProcedureNameForSetRoleByRelationType[relationType], definition);
}
if (associationType.IsOne)
{
// Get Composite Association (1-1) [relation table]
this.ProcedureNameForGetAssociationByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForGetAssociation + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForGetAssociationByRelationType[relationType] + @"
" + ParamNameForCompositeRole + @" " + this.SqlTypeForObject + @"
AS
SELECT " + ColumnNameForAssociation + @"
FROM " + table + @"
WHERE " + ColumnNameForRole + "=" + ParamNameForCompositeRole;
this.procedureDefinitionByName.Add(this.ProcedureNameForGetAssociationByRelationType[relationType], definition);
// Prefetch Composite Association (1-1) [relation table]
definition = @"CREATE PROCEDURE " + this.ProcedureNameForPrefetchAssociationByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
SELECT " + ColumnNameForAssociation + "," + ColumnNameForRole + @"
FROM " + table + @"
WHERE " + ColumnNameForRole + " IN (SELECT " + this.TableTypeColumnNameForObject + @" FROM " + ParamNameForTableType + ")";
this.procedureDefinitionByName.Add(this.ProcedureNameForPrefetchAssociationByRelationType[relationType], definition);
}
else
{
// Get Composite Association (*-1) [relation table]
this.ProcedureNameForGetAssociationByRelationType.Add(relationType, this.Database.SchemaName + "." + ProcedurePrefixForGetAssociation + relationTypeName);
definition = @"CREATE PROCEDURE " + this.ProcedureNameForGetAssociationByRelationType[relationType] + @"
" + ParamNameForCompositeRole + @" " + this.SqlTypeForObject + @"
AS
SELECT " + ColumnNameForAssociation + @"
FROM " + table + @"
WHERE " + ColumnNameForRole + "=" + ParamNameForCompositeRole;
this.procedureDefinitionByName.Add(this.ProcedureNameForGetAssociationByRelationType[relationType], definition);
// Prefetch Composite Association (*-1) [relation table]
definition = @"CREATE PROCEDURE " + this.ProcedureNameForPrefetchAssociationByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
SELECT " + ColumnNameForAssociation + "," + ColumnNameForRole + @"
FROM " + table + @"
WHERE " + ColumnNameForRole + " IN (SELECT " + this.TableTypeColumnNameForObject + @" FROM " + ParamNameForTableType + ")";
this.procedureDefinitionByName.Add(this.ProcedureNameForPrefetchAssociationByRelationType[relationType], definition);
}
// Clear Composite Role (1-1 and *-1) [relation table]
definition = @"CREATE PROCEDURE " + this.ProcedureNameForClearRoleByRelationType[relationType] + @"
" + ParamNameForTableType + @" " + this.TableTypeNameForObject + @" READONLY
AS
DELETE T
FROM " + table + @" T
INNER JOIN " + ParamNameForTableType + @" A
ON T." + ColumnNameForAssociation + " = A." + this.TableTypeColumnNameForObject;
this.procedureDefinitionByName.Add(this.ProcedureNameForClearRoleByRelationType[relationType], definition);
}
}
}
internal Save(Database database, XmlWriter writer)
{
this.database = database;
this.writer = writer;
}
internal Initialization(Database database)
{
this.database = database;
this.mapping = database.Mapping;
}
internal ManagementSession(Database database)
{
this.database = database;
}
