/// <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)
{
state.ExecuteNonQueryFormat(
"DELETE FROM ANNOTATION WHERE ANN_ID IN (SELECT DISTINCT ANN_ID FROM COMPONENT_ANNOTATION WHERE COMP_ID IN (SELECT COMP_ID FROM COMPONENT WHERE DSD_ID = {0}))",
state.Database.CreateInParameter("p_fk", DbType.Int64, primaryKey));
state.ExecuteNonQueryFormat(
"DELETE FROM ANNOTATION WHERE ANN_ID IN (SELECT DISTINCT ANN_ID FROM GROUP_ANNOTATION WHERE GR_ID IN (SELECT GR_ID FROM DSD_GROUP WHERE DSD_ID = {0}))",
state.Database.CreateInParameter("p_fk", DbType.Int64, primaryKey));
}
/// <summary>
/// Delete an ARTEFACT with the specified <paramref name="primaryKey"/> value
/// </summary>
/// <param name="state">
/// The Mapping Store <see cref="DbTransactionState"/>
/// </param>
/// <param name="primaryKey">
/// The primary key value.
/// </param>
/// <returns>
/// The number of records deleted.
/// </returns>
public int Delete(DbTransactionState state, long primaryKey)
{
if (state == null)
{
throw new ArgumentNullException("state");
}
this.DeleteChildStructures(state, primaryKey);
_log.DebugFormat(CultureInfo.InvariantCulture, "Deleting artefact record with primary key (ART_ID) = {0}", primaryKey);
state.ExecuteNonQueryFormat("DELETE FROM ANNOTATION WHERE ANN_ID in (select ANN_ID from ARTEFACT_ANNOTATION WHERE ART_ID = {0})", state.Database.CreateInParameter("artId", DbType.Int64, primaryKey));
return(state.ExecuteNonQueryFormat("DELETE FROM ARTEFACT WHERE ART_ID = {0}", state.Database.CreateInParameter("artId", DbType.Int64, primaryKey)));
}
/// <summary>
/// Inserts the attribute attachment measures. (SDMX V2.0 only)
/// </summary>
/// <param name="state">The state.</param>
/// <param name="dsd">The DSD.</param>
/// <param name="components">The components.</param>
private static void InsertAttributeAttachmentMeasures(DbTransactionState state, IDataStructureObject dsd, ItemStatusCollection components)
{
var cross = dsd as ICrossSectionalDataStructureObject;
if (cross == null)
{
// it is not a SDXM v2.0 cross sectional DSD therefor there are no Measure dimensions or CrossSectional measures.
return;
}
var parameterList = new List <DbParameter[]>();
foreach (var attributeObject in cross.Attributes)
{
ItemStatus attributeStatus;
if (components.TryGetValue(attributeObject.Id, out attributeStatus))
{
foreach (var crossSectionalMeasure in cross.GetAttachmentMeasures(attributeObject))
{
ItemStatus measureStatus;
if (components.TryGetValue(crossSectionalMeasure.Id, out measureStatus))
{
var parameters = new DbParameter[2];
parameters[0] = state.Database.CreateInParameter(IdParameter, DbType.Int64, attributeStatus.SysID);
parameters[1] = state.Database.CreateInParameter("measureId", DbType.Int64, measureStatus.SysID);
parameterList.Add(parameters);
}
}
}
}
state.ExecuteNonQueryFormat("insert into ATT_MEASURE (ATT_COMP_ID, MEASURE_COMP_ID) VALUES ({0}, {1})", parameterList);
}
/// <summary>
/// Insert attribute group.
/// </summary>
/// <param name="state">
/// The state.
/// </param>
/// <param name="dsd">
/// The DSD.
/// </param>
/// <param name="components">
/// The components.
/// </param>
private static void InsertAttributeDimensions(DbTransactionState state, IDataStructureObject dsd, ItemStatusCollection components)
{
var parameterList = new List <DbParameter[]>();
foreach (var attributeObject in dsd.DimensionGroupAttributes)
{
ItemStatus attributeStatus;
if (components.TryGetValue(attributeObject.Id, out attributeStatus))
{
foreach (var dimensionReference in attributeObject.DimensionReferences)
{
ItemStatus dimensionStatus;
if (components.TryGetValue(dimensionReference, out dimensionStatus))
{
var parameters = new DbParameter[2];
parameters[0] = state.Database.CreateInParameter(IdParameter, DbType.Int64, attributeStatus.SysID);
parameters[1] = state.Database.CreateInParameter(GroupIdParameter, DbType.Int64, dimensionStatus.SysID);
parameterList.Add(parameters);
}
}
}
}
state.ExecuteNonQueryFormat("insert into ATTR_DIMS (ATTR_ID, DIM_ID) VALUES ({0}, {1})", parameterList);
}
/// <summary>
/// Insert dimension group.
/// </summary>
/// <param name="state">
/// The state.
/// </param>
/// <param name="dsd">
/// The DSD.
/// </param>
/// <param name="groups">
/// The groups.
/// </param>
/// <param name="components">
/// The components.
/// </param>
private static void InsertDimensionGroup(DbTransactionState state, IDataStructureObject dsd, ItemStatusCollection groups, ItemStatusCollection components)
{
var parameterList = new List <DbParameter[]>();
foreach (var dsdGroup in dsd.Groups)
{
ItemStatus dsdGroupStatus;
if (groups.TryGetValue(dsdGroup.Id, out dsdGroupStatus))
{
foreach (var dimensionRef in dsdGroup.DimensionRefs)
{
ItemStatus dimensionStatus;
if (components.TryGetValue(dimensionRef, out dimensionStatus))
{
var parameters = new DbParameter[2];
parameters[0] = state.Database.CreateInParameter(IdParameter, DbType.Int64, dimensionStatus.SysID);
parameters[1] = state.Database.CreateInParameter(GroupIdParameter, DbType.Int64, dsdGroupStatus.SysID);
parameterList.Add(parameters);
}
}
}
}
state.ExecuteNonQueryFormat("insert into DIM_GROUP (COMP_ID, GR_ID) VALUES ({0}, {1})", parameterList);
}
/// <summary>
/// Converts the concept schemes to code lists.
/// </summary>
/// <param name="state">
/// The state.
/// </param>
/// <param name="conceptSchemesPerMeasureDimension">
/// The concept schemes per measure dimension.
/// </param>
private void ConvertConceptSchemes(DbTransactionState state, IEnumerable <KeyValuePair <long, IMaintainableRefObject> > conceptSchemesPerMeasureDimension)
{
var transactionalDatabase = new Database(this._database, state.Transaction);
var mutableObjectRetrievalManager = this.GetRetrievalManager(transactionalDatabase);
var codelistPrimaryKeyCache = new Dictionary <IMaintainableRefObject, ArtefactFinalStatus>();
foreach (var keyValuePair in conceptSchemesPerMeasureDimension)
{
ArtefactFinalStatus artefactFinalStatus;
if (!codelistPrimaryKeyCache.TryGetValue(keyValuePair.Value, out artefactFinalStatus))
{
artefactFinalStatus = ArtefactBaseEngine.GetFinalStatus(state, new StructureReferenceImpl(keyValuePair.Value, SdmxStructureEnumType.CodeList));
if (artefactFinalStatus.IsEmpty)
{
var conceptScheme = mutableObjectRetrievalManager.GetMutableConceptScheme(keyValuePair.Value, false, false);
var codelist = conceptScheme.ConvertToCodelist();
var importStatus = this._importEngine.Insert(state, codelist.ImmutableInstance);
artefactFinalStatus = new ArtefactFinalStatus(importStatus.PrimaryKeyValue, true);
}
codelistPrimaryKeyCache.Add(keyValuePair.Value, artefactFinalStatus);
}
state.ExecuteNonQueryFormat(
UpdateComponentCodelist,
transactionalDatabase.CreateInParameter("clId", DbType.Int64, artefactFinalStatus.PrimaryKey),
transactionalDatabase.CreateInParameter("compId", DbType.Int64, keyValuePair.Key));
}
}
/// <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)
{
var itemTableInfos = new[] { new ItemTableInfo(SdmxStructureEnumType.StructureMap)
{
ForeignKey = "SS_ID", PrimaryKey = "SM_ID", Table = "STRUCTURE_MAP"
}, new ItemTableInfo(SdmxStructureEnumType.CodeListMap)
{
ForeignKey = "SS_ID", PrimaryKey = "CLM_ID", Table = "CODELIST_MAP"
} };
foreach (var itemTableInfo in itemTableInfos)
{
var annotationQuery = string.Format("DELETE FROM ANNOTATION WHERE ANN_ID IN (SELECT DISTINCT ANN_ID FROM ITEM_ANNOTATION WHERE ITEM_ID IN (SELECT {0} FROM {1} WHERE {2} = {{0}})) ", itemTableInfo.PrimaryKey, itemTableInfo.Table, itemTableInfo.ForeignKey);
state.ExecuteNonQueryFormat(annotationQuery, state.Database.CreateInParameter("p_fk", DbType.Int64, primaryKey));
var query = string.Format("DELETE FROM ITEM WHERE ITEM_ID IN (SELECT DISTINCT {0} FROM {1} WHERE {2} = {{0}}) ", itemTableInfo.PrimaryKey, itemTableInfo.Table, itemTableInfo.ForeignKey);
state.ExecuteNonQueryFormat(query, state.Database.CreateInParameter("p_fk", DbType.Int64, primaryKey));
}
}
/// <summary>
/// The (slow) method that inserts items from <paramref name="items"/> without parent code information with an update
/// SQL statement for parents.
/// </summary>
/// <param name="state">
/// The state.
/// </param>
/// <param name="parentArtefact">
/// The parent artefact.
/// </param>
/// <param name="items">
/// The code collection
/// </param>
/// <param name="parentMap">
/// The parent map.
/// </param>
/// <exception cref="MappingStoreException">
/// Invalid parent code found.
/// </exception>
/// <remarks>
/// This method normally shouldn't run because Common API <c>SDMX Source.NET 0.9.15</c> validation rules check for
/// codes with parent recursive loops. <see cref="ExceptionCode.ParentRecursiveLoop"/>
/// </remarks>
private void InsertItemsWithUpdate(DbTransactionState state, long parentArtefact, ICollection <TItem> items, IDictionary <string, long> parentMap)
{
if (items.Count == 0)
{
return;
}
_log.InfoFormat(CultureInfo.InvariantCulture, "Using slow InsertItemsWithUpdate method for {0} codes. Already inserted : {1}", items.Count, parentMap.Count);
var itemsWithParents = new List <KeyValuePair <long, string> >(items.Count);
foreach (var item in items)
{
var parentItem = this.GetParentItem(item);
if (!string.IsNullOrEmpty(parentItem))
{
long parentItemPrimaryKey;
long itemID;
if (parentMap.TryGetValue(parentItem, out parentItemPrimaryKey))
{
itemID = this.InsertCode(state, parentArtefact, parentItemPrimaryKey, item);
}
else
{
itemID = this.InsertCode(state, parentArtefact, 0, item);
itemsWithParents.Add(new KeyValuePair <long, string>(itemID, parentItem));
}
parentMap.Add(item.Id, itemID);
}
else
{
Debug.Fail("Error in first algorithm. We should have never reached this point.");
}
}
foreach (var itemWithParent in itemsWithParents)
{
long parentCodePrimaryKey;
if (parentMap.TryGetValue(itemWithParent.Value, out parentCodePrimaryKey))
{
state.ExecuteNonQueryFormat(
"update DSD_CODE set PARENT_CODE = {0} where LCD_ID = {1}",
state.Database.CreateInParameter("parent", DbType.Int64, parentCodePrimaryKey),
state.Database.CreateInParameter("code", DbType.Int64, itemWithParent.Key));
}
else
{
var message = "Invalid parent code : " + itemWithParent.Value;
_log.Error(message);
throw new MappingStoreException(message);
}
}
}
/// <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)
{
var itemTableInfo = this.ItemTable;
// delete annotations first
var annotationQuery = string.Format("DELETE FROM ANNOTATION WHERE ANN_ID IN (SELECT DISTINCT ANN_ID FROM ITEM_ANNOTATION WHERE ITEM_ID IN (SELECT {0} FROM {1} WHERE {2} = {{0}})) ", itemTableInfo.PrimaryKey, itemTableInfo.Table, itemTableInfo.ForeignKey);
var annotationsDeleted = state.ExecuteNonQueryFormat(annotationQuery, state.Database.CreateInParameter("p_fk", DbType.Int64, primaryKey));
_log.DebugFormat(CultureInfo.InvariantCulture, "Item Annotations records deleted {0}", annotationsDeleted);
// set parent item to null to avoid issues with MySQL
if (!string.IsNullOrWhiteSpace(itemTableInfo.ParentItem))
{
var noparentStatement = string.Format("UPDATE {0} SET {1} = NULL WHERE {2} = {{0}} ", itemTableInfo.Table, itemTableInfo.ParentItem, itemTableInfo.ForeignKey);
var parentSetToNull = state.ExecuteNonQueryFormat(noparentStatement, state.Database.CreateInParameter("p_fk", DbType.Int64, primaryKey));
_log.DebugFormat(CultureInfo.InvariantCulture, "Parents set to null : {0}", parentSetToNull);
}
var query = string.Format("DELETE FROM ITEM WHERE ITEM_ID IN (SELECT DISTINCT {0} FROM {1} WHERE {2} = {{0}}) ", itemTableInfo.PrimaryKey, itemTableInfo.Table, itemTableInfo.ForeignKey);
var itemsDeleted = state.ExecuteNonQueryFormat(query, state.Database.CreateInParameter("p_fk", DbType.Int64, primaryKey));
_log.DebugFormat(CultureInfo.InvariantCulture, "Item records deleted {0}", itemsDeleted);
}
/// <summary>
/// Insert attribute group.
/// </summary>
/// <param name="state">
/// The state.
/// </param>
/// <param name="dsd">
/// The DSD.
/// </param>
/// <param name="groups">
/// The groups.
/// </param>
/// <param name="components">
/// The components.
/// </param>
private static void InsertAttributeGroup(DbTransactionState state, IDataStructureObject dsd, ItemStatusCollection groups, ItemStatusCollection components)
{
var parameterList = new List <DbParameter[]>();
foreach (var attributeObject in dsd.GroupAttributes)
{
ItemStatus dsdGroupStatus;
if (attributeObject.AttachmentGroup != null && groups.TryGetValue(attributeObject.AttachmentGroup, out dsdGroupStatus))
{
ItemStatus attributeStatus;
if (components.TryGetValue(attributeObject.Id, out attributeStatus))
{
var parameters = new DbParameter[2];
parameters[0] = state.Database.CreateInParameter(IdParameter, DbType.Int64, attributeStatus.SysID);
parameters[1] = state.Database.CreateInParameter(GroupIdParameter, DbType.Int64, dsdGroupStatus.SysID);
parameterList.Add(parameters);
}
}
}
state.ExecuteNonQueryFormat("insert into ATT_GROUP (COMP_ID, GR_ID) VALUES ({0}, {1})", parameterList);
}
/// <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);
}
