/// <summary>
/// Returns the final status (id and is final value) of the specified <paramref name="reference"/>; otherwise it returns null
/// </summary>
/// <param name="state">
/// The state.
/// </param>
/// <param name="reference">
/// The structure reference.
/// </param>
/// <exception cref="ArgumentException">
/// <paramref name="reference"/> unsupported structure
/// </exception>
/// <returns>
/// The <see cref="ArtefactFinalStatus"/> of the specified <paramref name="reference"/>; otherwise it returns null.
/// </returns>
public static ArtefactFinalStatus GetFinalStatus(DbTransactionState state, IStructureReference reference)
{
if (state == null)
{
throw new ArgumentNullException("state");
}
if (reference == null)
{
throw new ArgumentNullException("reference");
}
var tableInfo = _tableInfoBuilder.Build(reference.MaintainableStructureEnumType.EnumType);
if (tableInfo == null)
{
_log.WarnFormat("Unsupported structure type {0}", reference.MaintainableStructureEnumType.EnumType);
return(ArtefactFinalStatus.Empty);
////throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Resources.ExceptionUnsupportedStructureReferenceFormat1, reference), "reference");
}
var finalQueryBuilder = new IsFinalQueryBuilder(state.Database);
var query = finalQueryBuilder.Build(tableInfo);
var maintainableRefObject = reference.MaintainableReference;
var version = maintainableRefObject.SplitVersion(3);
ArtefactFinalStatus artefactFinalStatus = ArtefactFinalStatus.Empty;
state.ExecuteReaderFormat(
query,
reader =>
{
if (reader.Read())
{
var primaryKey = DataReaderHelper.GetInt64(reader, "primaryKey");
var finalStatus = DataReaderHelper.GetBoolean(reader, "isFinal");
artefactFinalStatus = new ArtefactFinalStatus(primaryKey, finalStatus);
}
},
state.Database.CreateInParameter("id", DbType.AnsiString, maintainableRefObject.MaintainableId),
state.Database.CreateInParameter("agency", DbType.AnsiString, maintainableRefObject.AgencyId),
state.Database.CreateInParameter("version1", DbType.AnsiString, version[0].ToDbValue(0)),
state.Database.CreateInParameter("version2", DbType.AnsiString, version[1].ToDbValue(0)),
state.Database.CreateInParameter("version3", DbType.AnsiString, version[2].ToDbValue()));
return(artefactFinalStatus);
}
/// <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);
}
