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