/// <summary>
/// This method executes an SQL query for a group on the dissemination database and writes it to <see cref="DataRetrievalInfoSeries.SeriesWriter"/> The SQL query is located inside <paramref name="groupInformation"/> at <see cref="GroupInformation.SQL"/> . The group information is located in <paramref name="groupInformation"/>
/// </summary>
/// <param name="groupInformation">
/// The current Time Series Group information
/// </param>
/// <param name="info">
/// The current DataRetrieval state
/// </param>
/// <param name="connection">
/// The connection to the dissemination database
/// </param>
private void ExecuteGroupSqlQuery(
GroupInformation groupInformation, DataRetrievalInfoSeries info, DbConnection connection)
{
if (string.IsNullOrEmpty(groupInformation.SQL))
{
return;
}
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = groupInformation.SQL;
command.CommandTimeout = 0;
var stopwatch = new Stopwatch();
stopwatch.Start();
using (IDataReader reader = command.ExecuteReader())
{
stopwatch.Stop();
Logger.Info(
string.Format(CultureInfo.InvariantCulture, "Execute Group SQL Reader: {0}", stopwatch.Elapsed));
while (reader.Read())
{
var componentValues = new MappedValues(info, groupInformation.ComponentMappings);
if (HandleComponentMapping(reader, componentValues, groupInformation.ComponentMappings, info)
&& !ProcessedKeySet(groupInformation, info, componentValues))
{
this.StoreTimeSeriesGroupResults(info, groupInformation, componentValues);
}
}
}
}
}
/// <summary>
/// Check if the specified <paramref name="targetGroup"/> contains the <paramref name="componentValues"/>
/// </summary>
/// <param name="targetGroup">
/// The target group
/// </param>
/// <param name="info">
/// The current data retrieval state
/// </param>
/// <param name="componentValues">
/// The component values
/// </param>
/// <returns>
/// A value indicating whether the key is already processed or not.
/// </returns>
private static bool ProcessedKeySet(
GroupInformation targetGroup, DataRetrievalInfoSeries info, MappedValues componentValues)
{
var current = new ReadOnlyKey(componentValues, info.GroupNameTable);
if (!targetGroup.KeySet.ContainsKey(current))
{
targetGroup.KeySet.Add(current, null);
return false;
}
return true;
}
/// <summary>
/// Store the SDMX compliant data for each component entity in the store
/// </summary>
/// <param name="info">
/// The current Data Retrieval state
/// </param>
/// <param name="groupInformation">
/// The current group related information
/// </param>
/// <param name="row">
/// The map between components and their values
/// </param>
protected virtual void StoreTimeSeriesGroupResults(
DataRetrievalInfoSeries info, GroupInformation groupInformation, MappedValues row)
{
info.SeriesWriter.StartGroup(groupInformation.ThisGroup.Id);
// write group dimensions
foreach (var dimensionValue in row.DimensionValues)
{
info.SeriesWriter.WriteGroupKeyValue(dimensionValue.Key.Id, dimensionValue.Value);
}
// write group attributes
WriteAttributes(row.AttributeGroupValues, info);
}
/// <summary>
/// This method generates the SQL SELECT statement for the dissemination database that will return the data for the incoming Query.
/// </summary>
/// <param name="groupBean">
/// The group Bean.
/// </param>
/// <param name="info">
/// The current data retrieval state
/// </param>
/// <returns>
/// The string containing the SQL query that needs to be executed on the dissemination database, in order to return the data required by the input query
/// </returns>
private static string GenerateGroupSql(GroupInformation groupBean, DataRetrievalInfoSeries info)
{
MappingSetEntity mappingSet = info.MappingSet;
Logger.Info(Resources.InfoBeginGenerateSql);
SqlQuery sqlQuery = new SqlQuery();
string sql = string.Empty;
try
{
// Generate Query subparts
sql = GenerateSelect(true, ConvertToMapping(groupBean.ComponentMappings));
sqlQuery.appendSql(sql);
sqlQuery.appendSql(GenerateFrom(mappingSet));
if (string.IsNullOrEmpty(info.SqlWhereCache))
{
info.SqlWhereCache = GenerateWhere(info);
}
sqlQuery.appendSql(info.SqlWhereCache);
bool bFlat = false;
var allDimensions = DimensionAtObservation.GetFromEnum(DimensionAtObservationEnumType.All).Value;
IBaseDataQuery baseDataQuery = (IBaseDataQuery)info.ComplexQuery ?? info.Query;
//the Flat option will be read only when we have flat aka AllDimensions
if (baseDataQuery.DimensionAtObservation.Equals(allDimensions))
Boolean.TryParse(ConfigurationManager.AppSettings["QueryFlatFormat"], out bFlat);
if (!bFlat)
sqlQuery.appendSql(GenerateOrderBy(info, groupBean.ThisGroup.Dimensions));
}
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);
return sqlQuery.getSql();
}
/// <summary>
/// Add <paramref name="componentMapping"/> to <see cref="_groups"/>
/// </summary>
/// <param name="componentMapping">
/// The group level attribute <see cref="IComponentMapping"/>
/// </param>
private void AddTimeSeriesGroups(IComponentMapping componentMapping)
{
foreach (var groupEntity in componentMapping.Component.AttAssignmentGroups)
{
if (!this._groups.ContainsKey(groupEntity))
{
var information = new GroupInformation(groupEntity);
this._groups.Add(groupEntity, information);
information.ComponentMappings.Add(componentMapping);
}
}
}
/// <summary>
/// Store the SDMX compliant data for each component entity in the store
/// </summary>
/// <param name="info">
/// The current Data Retrieval state
/// </param>
/// <param name="groupInformation">
/// The current group related information
/// </param>
/// <param name="row">
/// The map between components and their values
/// </param>
protected override void StoreTimeSeriesGroupResults(
DataRetrievalInfoSeries info, GroupInformation groupInformation, MappedValues row)
{
if (groupInformation.MeasureComponent == null)
{
base.StoreTimeSeriesGroupResults(info, groupInformation, row);
return;
}
foreach (var measureDimensionQueryValue in info.CrossSectionalMeasureMappings)
{
info.SeriesWriter.StartGroup(groupInformation.ThisGroup.Id);
// write group dimensions
foreach (var dimensionValue in row.DimensionValues)
{
info.SeriesWriter.WriteGroupKeyValue(dimensionValue.Key.Id, dimensionValue.Value);
}
var xsComponent = measureDimensionQueryValue.Components[0];
info.SeriesWriter.WriteGroupKeyValue(
groupInformation.MeasureComponent.Id, xsComponent.CrossSectionalMeasureCode);
// write group attributes
WriteAttributes(row.AttributeGroupValues, info);
}
}
