protected override IMappedValues CreateMappedValues(DataRetrievalInfo info, IDataReader reader)
{
var seriesInfo = info as DataRetrievalInfoSeries;
if (seriesInfo == null)
{
throw new ArgumentException(Resources.ErrorTypeNotDataRetrievalInfoSeries, "info");
}
return new MappedValues(info) { StartedDataSet = seriesInfo.DataSetAttributes.Count > 0 };
}
protected override int StoreResults(IMappedValues componentValues, DataRetrievalInfo info)
{
var row = componentValues as MappedValues;
if (row == null)
{
throw new ArgumentException("mappedValues not of MappedValues type");
}
var seriesInfo = info as DataRetrievalInfoSeries;
RDFWriteSeries(seriesInfo, row);
return RDFWriteObservation(seriesInfo, row, row.PrimaryMeasureValue.Value);
}
/// <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>
/// 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 executes an SQL query on the dissemination database
/// </summary>
/// <param name="info">
/// The current Data Retrieval state
/// </param>
/// <param name="queryEngine">
/// The query engine.
/// </param>
private void ExecuteSql(DataRetrievalInfo info, IRDFDataQueryEngine queryEngine)
{
Logger.Info(Resources.InfoStartExecutingSql);
queryEngine.ExecuteSqlQuery(info);
Logger.Info(Resources.InfoEndExecutingSql);
if (info.RecordsRead == 0 && (info.ComplexQuery != null))
{
throw new DataRetrieverException(Resources.NoRecordsFound,
SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.NoResultsFound));
}
}
/// <summary>
/// Check if the <see cref="DataRetrievalInfo.MappingSet"/> from <paramref name="info"/> is complete
/// </summary>
/// <param name="info">
/// The current data retrieval state
/// </param>
/// <exception cref="DataRetrieverException">
/// Incomplete mapping set. Please check if all dimensions, measure(s) and mandatory attributes are mapped
/// </exception>
private static void ValidateMappingSet(DataRetrievalInfo info)
{
// check if mapping is complete first
if (!MappingUtils.IsMappingSetComplete(info.MappingSet.Dataflow.Dsd, info.ComponentMapping))
{
throw new IncompleteMappingSetException(Resources.ErrorIncompleteMappingSet);
}
}
/// <summary>
/// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query.
/// </summary>
/// <exception cref="System.ArgumentNullException">
/// <paramref name="query"/>
/// is null
/// </exception>
/// <exception cref="DataRetrieverException">
/// <see cref="ErrorTypes"/>
/// </exception>
/// <param name="query">
/// The <see cref="Estat.Sdmx.Model.Query.QueryBean"/> modeling an SDMX-ML Query
/// </param>
/// <returns>
/// The generated sql query.
/// </returns>
/// <example>
/// An example using this method in C#
/// <code source="ReUsingExamples\DataRetriever\ReUsingDataRetrieverManySteps.cs" lang="cs" />
/// </example>
public string GenerateSqlQuery(IDataQuery query)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
try
{
// Get mapping set
MappingSetEntity mappingSet = this.Initialize(query);
// build the data retrieval state
var info = new DataRetrievalInfo(mappingSet, query, this._connectionStringSettings);
// check if mapping set is complete. TODO Remove this when SRA-166 is implemented
ValidateMappingSet(info);
// return the sql query
return info.SqlString;
}
catch (DataRetrieverException)
{
throw;
}
catch (SdmxException)
{
throw;
}
catch (Exception ex)
{
throw new DataRetrieverException(ex,
SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.SemanticError),
Resources.DataRetriever_GenerateSqlQuery_Could_not_generate_sql_query);
}
}
/// <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>
/// 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>
public MappedValues(DataRetrievalInfo info)
: this(info, info.AllComponentMappings)
{
}
public void ExecuteSqlQuery(DataRetrievalInfo info)
{
this.ExecuteDbCommand(info);
}
/// <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 = "";
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)
{
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 = "";
foreach (string freqVal in freqs)
{
if (sqlWhere != "") whereBuilder.Append(" AND ");
sqlWhere = GenerateWhereClause(sg, info, freqVal);
whereBuilder.Append(sqlWhere);
}
if (sg.PrimaryMeasureValue != null && sg.PrimaryMeasureValue.Count > 0)
{
if (sqlWhere != "") 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 != "") whereBuilder.Append(" AND ");
if (sel.HasMultipleValues())
{
int contor = 0;
whereBuilder.Append("(");
foreach (IComplexComponentValue val in sel.Values)
{
if (contor > 0) whereBuilder.Append(" OR ");
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 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>
/// 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;
}
protected override int StoreResults(IMappedValues componentValues, int limit, DataRetrievalInfo info)
{
return this.StoreResults(componentValues, info);
}
/// <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 sOperator = "=";
if (conditionValue.OrderedOperator != null)
sOperator = GetSqlOrderedOperator(conditionValue.OrderedOperator);
else if (conditionValue.TextSearchOperator != null)
sOperator = GetSqlTextSearchOperator(conditionValue.TextSearchOperator);
IComponentMapping componentMappingType;
if (info.ComponentIdMap.TryGetValue(id, out componentMappingType))
{
ret.Append(componentMappingType.GenerateComponentWhere(conditionValue.Value, sOperator));
}
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' " + sOperator + " '{1}') ",
id.Replace("'", "''"),
conditionValue.Value.Replace("'", "''")));
}
}
return ret.ToString();
}
/// <summary>
/// Generate the SQL string using <paramref name="sqlBuilder"/> with <paramref name="info"/>
/// </summary>
/// <param name="info">
/// The current dataretrieval state.
/// </param>
/// <param name="sqlBuilder">
/// The sql builder.
/// </param>
private void GenerateSql(DataRetrievalInfo info, ISqlBuilder sqlBuilder)
{
// Generate sql query
Logger.Info(Resources.InfoStartGeneratingSQLDDB);
sqlBuilder.GenerateSql(info);
Logger.Info(Resources.InfoEndGeneratingSQLDDB);
}
/// <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>
/// 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>();
// add each components comtribution to the order by of the query
foreach (ComponentEntity component in orderComponents)
{
MappingEntity mapping;
if (info.ComponentMapping.TryGetValue(component, out mapping))
{
foreach (DataSetColumnEntity column in mapping.Columns)
{
if (!orderColumns.Contains(column.Name))
{
orderColumns.Add(column.Name);
}
}
}
}
orderBy.Append(string.Join(CommaSeparator, orderColumns.ToArray()));
IBaseDataQuery baseDataQuery = (IBaseDataQuery)info.ComplexQuery ?? info.Query;
bool hasLastObs = baseDataQuery.LastNObservations.HasValue && baseDataQuery.LastNObservations.Value > 0 && (!baseDataQuery.FirstNObservations.HasValue || baseDataQuery.FirstNObservations.Value == 0);
if (hasLastObs)
{
// we assume that the last dimension is the dimension at observarion.
orderBy.Append(" DESC");
}
// log for easy debug
Logger.Info(
string.Format(CultureInfo.InvariantCulture, Resources.InfoGeneratedOrderByFormat1, orderBy));
Logger.Info(Resources.InfoEndGenerateOrderBy);
return orderBy.ToString();
}
/// <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>
/// <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;
}