/// <summary> /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query. /// </summary> /// <param name="info"> /// The current state of the data retrieval which containts the current query and mapping set /// </param> public void GenerateSql(DataRetrievalInfo info) { var seriesInfo = info as DataRetrievalInfoSeries; if (seriesInfo == null) { throw new ArgumentException("seriesInfo is not of DataRetrievalInfoSeries type"); } GenerateDataSetSql(seriesInfo); }
/// <summary> /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query. /// </summary> /// <param name="info"> /// The current state of the data retrieval which containts the current query and mapping set /// </param> public void GenerateSql(DataRetrievalInfo info) { var seriesInfo = info as DataRetrievalInfoSeries; if (seriesInfo == null) { throw new ArgumentException("seriesInfo is not of DataRetrievalInfoSeries type"); } // generate sql queries for groups foreach (var groupEntity in seriesInfo.Groups) { var information = groupEntity.Value; information.SQL = GenerateGroupSql(information, seriesInfo); } }
/// <summary> /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query. /// </summary> /// <param name="info"> /// The current state of the data retrieval which containts the current query and mapping set /// </param> public void GenerateSql(DataRetrievalInfo info) { Logger.Info(Resources.InfoBeginGenerateSql); SqlQuery sqlQuery = new SqlQuery(); string sql = string.Empty; try { sql = GenerateSelect(false, info.ComponentMapping.Values); sqlQuery.appendSql(sql); sqlQuery.appendSql(GenerateFrom(info.MappingSet)); if (info.ComplexQuery != null) { sqlQuery.appendSql(GenerateComplexWhere(info)); } else { sqlQuery.appendSql(GenerateWhere(info)); } var orderComponents = _orderedComponentBuilder.Build(info); sqlQuery.appendSql(GenerateOrderBy(info, orderComponents)); } catch (DataRetrieverException dex) { throw new DataRetrieverException(dex, dex.SdmxErrorCode, dex.Message); } catch (Exception ex) { Logger.Error(ex.ToString()); throw new DataRetrieverException(ex, SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError), Resources.ErrorUnableToGenerateSQL); } // log for easy debug Logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedSQLFormat1, sql)); Logger.Info(Resources.InfoEndGenerateSql); info.SqlString = sqlQuery.getSql(); }
/// <summary> /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query. /// </summary> /// <param name="info"> /// The current state of the data retrieval which containts the current query and mapping set /// </param> public void GenerateSql(DataRetrievalInfo info) { Logger.Info(Resources.InfoBeginGenerateSql); var seriesInfo = info as DataRetrievalInfoSeries; if (seriesInfo == null) { throw new ArgumentException("seriesInfo is not of DataRetrievalInfoSeries type"); } SqlQuery sqlQuery = new SqlQuery(); string sql = string.Empty; try { sql = GenerateSelect(false, seriesInfo.ComponentMapping.Values); sqlQuery.appendSql(sql); sqlQuery.appendSql(GenerateFrom(seriesInfo.MappingSet)); AppendCachedWhere(seriesInfo, sqlQuery); var orderComponents = _orderedComponentBuilder.Build(seriesInfo); sqlQuery.appendSql(GenerateOrderBy(seriesInfo, orderComponents)); } catch (DataRetrieverException) { throw; } catch (Exception ex) { Logger.Error(ex.ToString()); throw new DataRetrieverException(ex, SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError), Resources.ErrorUnableToGenerateSQL); } // log for easy debug Logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedSQLFormat1, sql)); Logger.Info(Resources.InfoEndGenerateSql); info.SqlString = sqlQuery.getSql(); }
/// <summary> /// Builds an ordered list of Components from the specified <paramref name="info"/> /// </summary> /// <param name="info"> /// The DataRetriever state information. /// </param> /// <returns> /// The list of components. /// </returns> public IList<ComponentEntity> Build(DataRetrievalInfo info) { DsdEntity dsd = info.MappingSet.Dataflow.Dsd; IBaseDataQuery baseDataQuery = (IBaseDataQuery)info.ComplexQuery ?? info.Query; // build a list of the components that that must affect the order, // in the correct order (dimensions, then time) var orderComponents = new List<ComponentEntity>(); var dimensionAtObservation = baseDataQuery.DimensionAtObservation; var allDimensions = DimensionAtObservation.GetFromEnum(DimensionAtObservationEnumType.All).Value; if (dimensionAtObservation.Equals(allDimensions)) { HandleFlat(orderComponents, dsd); } else { HandleOrdered(dsd, dimensionAtObservation, orderComponents); } return orderComponents; }
/// <summary> /// This method generates the ORDER BY part of the query /// </summary> /// <param name="info"> /// The current data retrieval state /// </param> /// <returns> /// The string containing the ORDER BY part of the query /// </returns> private static string GenerateOrderByLocalColumns(DataRetrievalInfo info) { var orderComponents = _orderedComponentBuilder.Build(info); var orderBy = GenerateOrderBy(info, orderComponents); return orderBy; }
/// <summary> /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query. /// </summary> /// <param name="info"> /// The current state of the data retrieval which containts the current query and mapping set /// </param> public void GenerateSql(DataRetrievalInfo info) { Logger.Info(Resources.InfoBeginGenerateSql); var seriesInfo = info as DataRetrievalInfoSeries; if (seriesInfo == null) { throw new ArgumentException("seriesInfo is not of DataRetrievalInfoSeries type"); } MappingSetEntity mappingSet = info.MappingSet; SqlQuery sqlQuery = new SqlQuery(); string sql = string.Empty; try { // Generate Query subparts var mappingEntities = ConvertToMapping(seriesInfo.AllComponentMappings); mappingEntities.Add(seriesInfo.TimeMapping); sql = GenerateSelect(false, mappingEntities); sqlQuery.appendSql(sql); sqlQuery.appendSql(GenerateFrom(mappingSet)); AppendCachedWhere(seriesInfo, sqlQuery); sqlQuery.appendSql(GenerateOrderByLocalColumns(seriesInfo)); } catch (DataRetrieverException) { throw; } catch (Exception ex) { Logger.Error(ex.ToString()); throw new DataRetrieverException(ex, SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError), Resources.ErrorUnableToGenerateSQL); } // log for easy debug Logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedSQLFormat1, sql)); Logger.Info(Resources.InfoEndGenerateSql); info.SqlString = sqlQuery.getSql(); }
/// <summary> /// Generates SQL where clauses from <paramref name="time"/> /// </summary> /// <param name="time"> /// The <see cref="IComplexDataQuerySelectionGroup"/> /// </param> /// <param name="info"> /// The current data retrieval state /// </param> /// <param name="freqValue"> /// The frequency value /// </param> /// <returns> /// The string containing the time part of the WHERE in an SQL query. /// </returns> private static string GenerateWhereClause(IComplexDataQuerySelectionGroup time, DataRetrievalInfo info, string freqValue) { return info.TimeTranscoder != null ? info.TimeTranscoder.GenerateWhere(time.DateFrom, time.DateTo, freqValue) : string.Empty; }
/// <summary> /// Maps a component to one or more local columns and it's value to one or more local codes /// </summary> /// <param name="id"> /// The DSD Component identifier e.g. FREQ /// </param> /// <param name="conditionValue"> /// The DSD Component condition value (from the SDMX Query) /// </param> /// <param name="info"> /// The current Data Retrieval status /// </param> /// <returns> /// An string containing the SQL query where condition /// </returns> private static string GenerateComponentWhere(string id, IComplexComponentValue conditionValue, DataRetrievalInfo info) { var ret = new StringBuilder(); // MappingEntity mapping; // check if there is a mapping for this component if (id != null && conditionValue != null) { string operatorValue = "="; if (conditionValue.OrderedOperator != null) { operatorValue = GetSqlOrderedOperator(conditionValue.OrderedOperator); } else if (conditionValue.TextSearchOperator != null) { operatorValue = GetSqlTextSearchOperator(conditionValue.TextSearchOperator); } IComponentMapping componentMappingType; if (info.ComponentIdMap.TryGetValue(id, out componentMappingType)) { ret.Append(componentMappingType.GenerateComponentWhere(conditionValue.Value, operatorValue)); } else if (info.MeasureComponent == null || !id.Equals(info.MeasureComponent.Id)) { // component is not in the mapping ret.Append( string.Format( CultureInfo.InvariantCulture, " ('{0} is not mapped' " + operatorValue + " '{1}') ", id.Replace("'", "''"), conditionValue.Value.Replace("'", "''"))); } } return ret.ToString(); }
/// <summary> /// This method generates the WHERE part of the complex query /// </summary> /// <param name="info"> /// The current data retrieval state /// </param> /// <returns> /// The string containing the required SQL part. For example, "where (FREQ='M')" /// </returns> protected static string GenerateComplexWhere(DataRetrievalInfo info) { IComplexDataQuery query = info.ComplexQuery; _logger.Info(Resources.InfoBeginGenerateWhere); var whereBuilder = new StringBuilder(); if (query != null) { if (query.HasSelections()) { string dimId = string.Empty; if (query.DataStructure.TimeDimension != null) { IList<IDimension> dimLst = query.DataStructure.DimensionList.Dimensions; foreach (IDimension dim in dimLst) { if (dim.FrequencyDimension) { dimId = dim.Id; break; // only one dimension has FrequencyDimension = true so no need to look further } } } IList<IComplexDataQuerySelectionGroup> selGrps = query.SelectionGroups; foreach (IComplexDataQuerySelectionGroup sg in selGrps) { // AND has precedence over OR if (whereBuilder.Length > 0) { whereBuilder.Append(" OR "); } IList<string> freqs = new List<string>(); if (sg.HasSelectionForConcept(dimId)) { IComplexDataQuerySelection selConcept = sg.GetSelectionsForConcept(dimId); if (selConcept.HasMultipleValues()) { foreach (IComplexComponentValue val in selConcept.Values) { freqs.Add(val.Value); } } else { freqs.Add(selConcept.Value.Value); } } else { // HACK FIX ME TODO freqs.Add(null); } string sqlWhere = string.Empty; foreach (string freqVal in freqs) { if (sqlWhere != string.Empty) { whereBuilder.Append(" AND "); } sqlWhere = GenerateWhereClause(sg, info, freqVal); whereBuilder.Append(sqlWhere); } if (sg.PrimaryMeasureValue != null && sg.PrimaryMeasureValue.Count > 0) { if (sqlWhere != string.Empty) { whereBuilder.Append(" AND "); } foreach (IComplexComponentValue complexValue in sg.PrimaryMeasureValue) { sqlWhere = GenerateComponentWhere(PrimaryMeasure.FixedId, complexValue, info); whereBuilder.Append(sqlWhere); } } foreach (IComplexDataQuerySelection sel in sg.Selections) { if (sqlWhere != string.Empty) { whereBuilder.Append(" AND "); } if (sel.HasMultipleValues()) { int contor = 0; whereBuilder.Append("("); var componentValuesOperator = GetComponentValuesOperator(sel); foreach (IComplexComponentValue val in sel.Values) { if (contor > 0) { whereBuilder.Append(componentValuesOperator); } sqlWhere = GenerateComponentWhere(sel.ComponentId, val, info); whereBuilder.Append(sqlWhere); contor++; } whereBuilder.Append(")"); } else { sqlWhere = GenerateComponentWhere(sel.ComponentId, sel.Value, info); whereBuilder.Append(sqlWhere); } } } } // MAT-274 if (whereBuilder.Length > 0) { whereBuilder.Insert(0, "where "); } } // log for easy debug _logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedWhereFormat1, whereBuilder)); _logger.Info(Resources.InfoEndGenerateWhere); return whereBuilder.ToString(); }
/// <summary> /// This method generates the ORDER BY part of the query for the specified <paramref name="orderComponents"/> /// </summary> /// <param name="info"> /// The current Data retrieval state /// </param> /// <param name="orderComponents"> /// The order components. /// </param> /// <returns> /// the ORDER BY part of the query for the specified <paramref name="orderComponents"/> /// </returns> protected static string GenerateOrderBy(DataRetrievalInfo info, IEnumerable<ComponentEntity> orderComponents) { _logger.Info(Resources.InfoGenerateOrderBy); var orderBy = new StringBuilder("order by "); var orderColumns = new List<string>(); var effectiveDimensionAtObs = info.EffectiveDimensionAtObservation; IEnumerable<ComponentEntity> componentEntities = orderComponents as ComponentEntity[] ?? orderComponents.ToArray(); // TODO there might be an issue with dim at obs sharing a column with another obs. // add each components contribution to the order by of the query foreach (ComponentEntity component in componentEntities) { MappingEntity mapping; if (info.ComponentMapping.TryGetValue(component, out mapping)) { foreach (DataSetColumnEntity column in mapping.Columns) { if (!orderColumns.Contains(column.Name)) { orderColumns.Add(column.Name); } } } } bool hasLastObs = !info.HasFirstAndLastNObservations && info.HasLastNObservations; if (hasLastObs) { var dimAtObsComponent = componentEntities.FirstOrDefault(entity => entity.Id.Equals(effectiveDimensionAtObs)); // Groups might not have the dimension at observation // DataSets will not have dimension at observation. if (dimAtObsComponent != null) { var mappingEntity = info.ComponentMapping[dimAtObsComponent]; for (int i = 0; i < orderColumns.Count; i++) { var column = orderColumns[i]; if (mappingEntity.Columns.Any(entity => entity.Name.Equals(column))) { orderColumns[i] += " DESC"; } } } } orderBy.Append(string.Join(CommaSeparator, orderColumns)); // log for easy debug _logger.Info( string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedOrderByFormat1, orderBy)); _logger.Info(Resources.InfoEndGenerateOrderBy); return orderBy.ToString(); }
/// <summary> /// Initialize the internal order of the components based on the specified <paramref name="componentMappings"/> /// </summary> /// <param name="componentMappings"> /// The component mappings /// </param> /// /// <param name="info"> /// The data retrieval info /// </param> private void SetComponentOrder(IEnumerable<IComponentMapping> componentMappings, DataRetrievalInfo info) { foreach (IComponentMapping componentMapping in componentMappings) { var componentValue = new ComponentValue(componentMapping.Component); this.ComponentValues.Add(componentValue); switch (componentMapping.Component.ComponentType) { case SdmxComponentType.Dimension: this._dimensionValues.Add(componentValue); if (!componentValue.Key.Id.Equals(info.DimensionAtObservation)) this._keyValues.Add(componentValue); break; case SdmxComponentType.Attribute: switch (componentMapping.Component.AttributeAttachmentLevel) { case AttachmentLevel.DataSet: this._attributeDataSetValues.Add(componentValue); break; case AttachmentLevel.Group: // NOTE we expect only the attributes of a specific group to be in _attributeGroupValues this._attributeGroupValues.Add(componentValue); break; case AttachmentLevel.Series: if (IsDimensionObsReference(componentValue, info)) this._attributeObservationValues.Add(componentValue); else this._attributeSeriesValues.Add(componentValue); break; case AttachmentLevel.Observation: this._attributeObservationValues.Add(componentValue); break; } break; case SdmxComponentType.PrimaryMeasure: this.PrimaryMeasureValue = componentValue; break; case SdmxComponentType.CrossSectionalMeasure: this._xsMeasures.Add(componentValue); break; } } }
/// <summary> /// Initializes a new instance of the <see cref="MappedValues"/> class. /// </summary> /// <param name="info"> /// The current data retrieval state /// </param> /// <param name="componentMappings"> /// The component Mappings. /// </param> public MappedValues(DataRetrievalInfo info, IEnumerable<IComponentMapping> componentMappings) { this.SetComponentOrder(componentMappings, info); this.SetTimeDimensionComponent(info.TimeTranscoder); if (info.TimeTranscoder != null && !info.IsTimePeriodAtObservation && info.TimeTranscoder.Component.ComponentType == SdmxComponentType.TimeDimension) this._keyValues.Add(this.TimeDimensionValue); this._previousKeyValues = new string[this._keyValues.Count]; }
/// <summary> /// Initializes a new instance of the <see cref="MappedValuesFlat"/> class. /// </summary> /// <param name="info"> /// The current data retrieval state /// </param> public MappedValuesFlat(DataRetrievalInfo info) { this.SetComponentOrder(info.AllComponentMappings); this.SetTimeDimensionComponent(info.TimeTranscoder); }
/// <summary> /// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query. /// </summary> /// <param name="info"> /// The current state of the data retrieval which containts the current query and mapping set /// </param> public void GenerateSql(DataRetrievalInfo info) { MappingSetEntity mappingSet = info.MappingSet; Logger.Info(Resources.InfoBeginGenerateSql); SqlQuery sqlQuery = new SqlQuery(); string sql = string.Empty; try { // Generate Query subparts sql = GenerateSelect(false, info.ComponentMapping.Values); sqlQuery.appendSql(sql); sqlQuery.appendSql(GenerateFrom(mappingSet)); //the WHERE part sqlQuery.appendSql(GenerateWhere(info)); sqlQuery.appendSql(GenerateXSOrderByLocalColumns(info)); } catch (Exception ex) { Logger.Error(ex.ToString()); throw new DataRetrieverException(ex, SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError), Resources.ErrorUnableToGenerateSQL); //ErrorTypes.QUERY_PARSING_ERROR, Resources.ErrorUnableToGenerateSQL, ex); } // log for easy debug Logger.Info(string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedSQLFormat1, sql)); Logger.Info(Resources.InfoEndGenerateSql); info.SqlString = sqlQuery.getSql(); }
/// <summary> /// This method generates the ORDER BY part of the query /// </summary> /// <param name="info"> /// The current data retrieval state /// </param> /// <returns> /// The string containing the ORDER BY part of the query /// </returns> private static string GenerateXSOrderByLocalColumns(DataRetrievalInfo info) { DsdEntity dsd = info.MappingSet.Dataflow.Dsd; var orderComponents = _orderedComponentBuilder.Build(info); var crossOrderedComponents = new List<ComponentEntity>(orderComponents); crossOrderedComponents.Sort(OnCrossSectionalComparison); var orderBy = GenerateOrderBy(info, crossOrderedComponents); return orderBy; }
/// <summary> /// Checks if the dimension at observation is in the dimension references of the component <paramref name="componentMappings"/> /// </summary> /// <param name="componentValue"> /// The component value /// </param> /// /// <param name="info"> /// The data retrieval info /// </param> private bool IsDimensionObsReference(ComponentValue componentValue, DataRetrievalInfo info) { IBaseDataQuery baseDataQuery = (IBaseDataQuery)info.ComplexQuery ?? info.Query; foreach (IAttributeObject attr in baseDataQuery.DataStructure.AttributeList.Attributes) { if (attr.Id.Equals(componentValue.Key.Id)) { if (attr.DimensionReferences.Contains(info.DimensionAtObservation)) return true; return false; } } return false; }
/// <summary> /// Initializes a new instance of the <see cref="MappedXsValues"/> class. /// </summary> /// <param name="info"> /// The info. /// </param> public MappedXsValues(DataRetrievalInfo info) { var values = (CrossSectionalLevels[])Enum.GetValues(typeof(CrossSectionalLevels)); for (int i = 0; i < values.Length; i++) { this._dimensionValues.Add(new List<ComponentValue>()); this._attributeValues.Add(new List<ComponentValue>()); this._keyValues.Add(new List<ComponentValue>()); } this._dataSetIndex = GetIndex(CrossSectionalLevels.DataSet); this._groupIndex = GetIndex(CrossSectionalLevels.Group); this._sectionIndex = GetIndex(CrossSectionalLevels.Section); this._observationIndex = GetIndex(CrossSectionalLevels.Observation); this.SetComponentOrder(info.AllComponentMappings); if (info.TimeTranscoder != null) { var timeDimensionComponent = this.SetTimeDimensionComponent(info.TimeTranscoder); this.HandleTimeDimension(timeDimensionComponent); } for (int i = 0; i < this._keyValues.Count; i++) { List<ComponentValue> keyValue = this._keyValues[i]; this._previousKeyValues[i] = new string[keyValue.Count]; } }
/// <summary> /// Initializes a new instance of the <see cref="MappedValuesFlat"/> class. /// </summary> /// <param name="record"> /// The current data record reference. used to retrieve the local columns /// </param> /// <param name="info"> /// The current data retrieval state /// </param> public MappedValuesFlat(IDataRecord record, DataRetrievalInfo info) : this(info) { this._record = record; }
/// <summary> /// Initializes a new instance of the <see cref="MappedValues"/> class. /// </summary> /// <param name="info"> /// The current data retrieval state /// </param> public MappedValues(DataRetrievalInfo info) : this(info, info.AllComponentMappings) { }