/// <summary>
/// Initializes a new instance of the <see cref="IdentifiableAnnotationRetrieverEngine"/> class.
/// </summary>
/// <param name="mappingStoreDb">The mapping store database.</param>
/// <param name="tableInfo">The table information.</param>
/// <exception cref="System.ArgumentNullException"><paramref name="tableInfo"/> is null -or- <paramref name="mappingStoreDb"/> is null.</exception>
public IdentifiableAnnotationRetrieverEngine(Database mappingStoreDb, ItemTableInfo tableInfo)
{
if (mappingStoreDb == null)
{
throw new ArgumentNullException("mappingStoreDb");
}
if (tableInfo == null)
{
throw new ArgumentNullException("tableInfo");
}
this._mappingStoreDb = mappingStoreDb;
ISqlQueryInfoBuilder <ItemTableInfo> annotationQueryBuilder = new AnnotationQueryBuilder();
this._annotationSqlQueryInfo = annotationQueryBuilder.Build(tableInfo);
this._annotationCommandBuilder = new AnnotationCommandBuilder(this._mappingStoreDb);
}
/// <summary>
/// Builds the specified nameable table information.
/// </summary>
/// <param name="nameableTableInfo">The nameable table information.</param>
/// <returns>THe <see cref="SqlQueryInfo"/></returns>
public SqlQueryInfo Build(ItemTableInfo nameableTableInfo)
{
// we have some non-nameable and/or special cases...
string annotationRelationTable;
string annotationRelationTableForeignKey;
switch (nameableTableInfo.StructureType)
{
case SdmxStructureEnumType.Component:
annotationRelationTable = AnnotationConstants.ComponentAnnotationTable;
annotationRelationTableForeignKey = "COMP_ID";
break;
case SdmxStructureEnumType.Group:
annotationRelationTable = AnnotationConstants.DsdGroupAnnotationTable;
annotationRelationTableForeignKey = "GR_ID";
break;
case SdmxStructureEnumType.Level:
case SdmxStructureEnumType.Hierarchy:
case SdmxStructureEnumType.HierarchicalCode:
annotationRelationTable = AnnotationConstants.ArtefactAnnotationTable;
annotationRelationTableForeignKey = "ART_ID";
break;
default:
annotationRelationTable = AnnotationConstants.ItemAnnotationTable;
annotationRelationTableForeignKey = "ITEM_ID";
break;
}
var format = string.Format(CultureInfo.InvariantCulture, AnnotationConstants.AnnotationQuery, nameableTableInfo.PrimaryKey, annotationRelationTable, nameableTableInfo.Table, annotationRelationTableForeignKey);
return(new SqlQueryInfo()
{
QueryFormat = string.Format(CultureInfo.InvariantCulture, "{0} WHERE T.{1} = {{0}}", format, nameableTableInfo.ForeignKey), WhereStatus = WhereState.And
});
}
/// <summary>
/// Deletes the child items.
/// </summary>
/// <param name="state">The state.</param>
/// <param name="primaryKey">The primary key.</param>
protected override void DeleteChildStructures(DbTransactionState state, long primaryKey)
{
// There is no "ON DELETE CASCADE" between ARTEFACT and any of HIERARCHY, HLEVEL and HCL tables.
// So we need first to retrieve the records HIERARCHY, HLEVEL and HCL tables.
var itemTableInfo = new ItemTableInfo(SdmxStructureEnumType.Hierarchy)
{
ForeignKey = "HCL_ID", PrimaryKey = "H_ID", Table = "HIERARCHY"
};
var hierarchySubQuery = string.Format(CultureInfo.InvariantCulture, "SELECT {0} FROM {1} WHERE {2} = {{0}}", itemTableInfo.PrimaryKey, itemTableInfo.Table, itemTableInfo.ForeignKey);
// the following two array/list must match...
string[] statements =
{
hierarchySubQuery,
hierarchySubQuery,
"{0}"
};
var tablePrimaryKey = new List <ItemTableInfo> {
new ItemTableInfo(SdmxStructureEnumType.HierarchicalCode)
{
Table = "HCL_CODE", PrimaryKey = "HCODE_ID", ForeignKey = "H_ID"
}, new ItemTableInfo(SdmxStructureEnumType.Level)
{
Table = "HLEVEL", PrimaryKey = "LEVEL_ID", ForeignKey = "H_ID"
}, itemTableInfo
};
var primaryKeys = new Stack <long>();
for (int i = 0; i < statements.Length; i++)
{
var statement = statements[i];
var tupleTableKey = tablePrimaryKey[i];
var tableName = tupleTableKey.Table;
var foreignKey = tupleTableKey.ForeignKey;
var subQuery = string.Format(CultureInfo.InvariantCulture, "SELECT {0} FROM {1} WHERE {2} IN ({3})", tupleTableKey.PrimaryKey, tableName, foreignKey, statement);
// First get the list of primary keys. We need those because we need to delete the HIERARCHY, HCL_CODE and HLEVEL records first and then the corresponding ARTEFACT records.
state.ExecuteReaderFormat(
subQuery,
reader =>
{
while (reader.Read())
{
primaryKeys.Push(reader.GetInt64(0));
}
},
state.Database.CreateInParameter("p_fk", DbType.Int64, primaryKey));
// Delete the annotations
var annotationDeleteStatement = string.Format("DELETE FROM ANNOTATION WHERE ANN_ID IN (SELECT DISTINCT ANN_ID FROM ARTEFACT_ANNOTATION WHERE ART_ID IN ({0}))", subQuery);
state.ExecuteNonQueryFormat(annotationDeleteStatement, state.Database.CreateInParameter("p_fk", DbType.Int64, primaryKey));
// Delete the HIERARCHY, HCL_CODE and HLEVEL records
var deleteStatement = string.Format("DELETE FROM {0} WHERE {1} IN ({2})", tableName, foreignKey, statement);
var executeNonQueryFormat = state.ExecuteNonQueryFormat(deleteStatement, state.Database.CreateInParameter("p_fk", DbType.Int64, primaryKey));
}
// last delete the artefact records. Must be last else you get foreign key constraint violations. No "on delete cascade" thanks to SQL Server.
DbHelper.BulkDelete(state.Database, "ARTEFACT", "ART_ID", primaryKeys);
}
