/// <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); }