/// <summary>
/// Write the SDMX compliant values for dataset attached attributes to the <see cref="DataRetrievalInfoSeries.SeriesWriter"/> if <see cref="MappedValues.StartedDataSet"/> is true
/// </summary>
/// <param name="componentValues">
/// The map between components and their values
/// </param>
/// <param name="info">
/// The current Data Retrieval state
/// </param>
protected static void TryWriteDataSet(MappedValues componentValues, DataRetrievalInfoSeries info)
{
if (!componentValues.StartedDataSet)
{
WriteDataSetResults(componentValues, info);
componentValues.StartedDataSet = true;
}
}
/// <summary>
/// Writes the attributes.
/// </summary>
/// <param name="attributes">The attributes.</param>
/// <param name="info">The info.</param>
protected static void WriteAttributes(IEnumerable<ComponentValue> attributes, DataRetrievalInfoSeries info)
{
// write attributes
foreach (var keyValuePair in attributes)
{
var componentEntity = keyValuePair.Key;
var value = keyValuePair.Value;
// SODIHD-1272 write optional attribute only if it is not empty.
if (componentEntity.ComponentType != SdmxComponentType.Attribute || componentEntity.AttStatus != AssignmentStatus.Conditional || !string.IsNullOrEmpty(value))
{
info.SeriesWriter.WriteAttributeValue(componentEntity.Id, value);
}
}
}
/// <summary>
/// Retrieve data from a DDB and write it to the specified <paramref name="writer"/> This is the main public method of the DataRetriever class. It is called with a populated QueryBean (containing essentially an SDMX-ML Query) and a database Connection to a "Mapping Store" database. This method is responsible for:
/// <list type="bullet">
/// <item>
/// Retrieving the <see cref="MappingSetEntity"/> (the class containing the performed mappings), according to the provided Dataflow ID, from the "Mapping Store". Mapping Sets are defined on a Dataflow basis. Thus, this method checks the input QueryBean for the Dataflow that data are requested and fetches the appropriate
/// <see cref="MappingSetEntity"/>. If no <see cref="MappingSetEntity"/> exists, an exception (<see cref="DataRetrieverException"/>) is thrown.
/// </item>
/// <item>
/// Calling the method generating the appropriate SQL for the dissemination database.
/// </item>
/// <item>
/// Calling the method that executes the generated SQL and uses the
/// <paramref name="writer"/>
/// to write the output.
/// </item>
/// </list>
/// <note type="note">
/// The "Data Retriever" expects exactly one Dataflow clause under the DataWhere clause, exactly one
/// DataFlowBean within the DataWhereBean (which in turn resides inside the incoming QueryBean).
/// </note>
/// </summary>
/// <exception cref="DataRetrieverException">
/// See the
/// <see cref="ErrorTypes"/>
/// for more details
/// </exception>
/// <exception cref="System.ArgumentNullException">
/// <paramref name="query"/>
/// is null
/// -or-
/// <paramref name="writer"/>
/// is null
/// </exception>
/// <param name="query">
/// The query bean for which data will be requested
/// </param>
/// <param name="writer">
/// The <see cref="ISeriesWriter"/> (e.g. Compact, Generic) writer
/// </param>
/// <example>
/// An example using this method in C# with <see cref="CompactWriter"/>
/// <code source="ReUsingExamples\DataRetriever\ReUsingDataRetriever.cs" lang="cs">
/// </code>
/// An example using this method in C# with <see cref="GenericDataWriter"/>
/// <code source="ReUsingExamples\DataRetriever\ReUsingDataRetrieverGeneric.cs" lang="cs">
/// </code>
/// </example>
public void GetData(IDataQuery dataQuery, IDsplDataWriterEngine dataWriter)
{
if (dataQuery == null)
{
throw new ArgumentNullException("query");
}
if (dataWriter == null)
{
throw new ArgumentNullException("writer");
}
try
{
Logger.Info(Resources.InfoDataRetrieverBBInvoked);
Logger.Info(Resources.InfoOutput + dataWriter.GetType().Name);
// validate input and initialize the mappingset entitiy
MappingSetEntity mappingSet = this.Initialize(dataQuery);
var info = new DataRetrievalInfoSeries(mappingSet, dataQuery, this._connectionStringSettings, dataWriter, this._sdmxSchemaVersion)
{
DefaultHeader = this._defaultHeader
};
ValidateMappingSet(info);
//Pietro 27/01
//this.WriteHeader(dataWriter, info);
//(SRA-345)
//DR the info from I*DataQuery. DimensionAtObservation to IDataWriterEngine.StartDataSet
IDatasetStructureReference dsr = new DatasetStructureReferenceCore("", dataQuery.DataStructure.AsReference, null, null, dataQuery.DimensionAtObservation);
IDatasetHeader header = new DatasetHeaderCore(this._defaultHeader.DatasetId, this._defaultHeader.Action, dsr);
//info.MappingSet.DataSet.Description.ToString()
//info.MappingSet.Dataflow.Dsd.Id.ToString()
dataWriter.SetDsdOrder(dataQuery.DataStructure);
dataWriter.StartDataset(info.MappingSet.DataSet.Description.ToString(), dataQuery.Dataflow, dataQuery.DataStructure, header, null);
// Generate sql query
this.GenerateSql(info, _sqlBuilder);
this.GenerateSql(info, SeriesDataSetSqlBuilder.Instance);
this.GenerateSql(info, SeriesGroupSqlBuilder.Instance);
// execute sql query
this.ExecuteSql(info, SeriesQueryEngineManager.Instance.GetQueryEngine(info));
// close output
dataWriter.Close();
Logger.Info(Resources.InfoEndDataRetrieverBBInvoked);
}
catch (DataRetrieverException)
{
throw;
}
catch (SdmxException)
{
throw;
}
catch (DbException ex)
{
Logger.Error(ex.ToString());
throw new DataRetrieverException(ex,
SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.InternalServerError),
Resources.DataRetriever_ExecuteSqlQuery_Error_executing_generated_SQL_and_populating_SDMX_model);
}
catch (OutOfMemoryException)
{
throw;
}
catch (Exception ex)
{
Logger.Error(ex.ToString());
throw new DataRetrieverException(ex,
SdmxErrorCode.GetFromEnum(SdmxErrorCodeEnumType.InternalServerError),
Resources.DataRetriever_ExecuteSqlQuery_Error_during_writing_responce);
}
}
/// <summary>
/// Appends the cached where to <paramref name="sql"/> from <see cref="DataRetrievalInfoSeries.SqlWhereCache"/> if it is not null or from <see cref="SqlBuilderBase.GenerateWhere"/>
/// </summary>
/// <param name="info">
/// The current DataRetrieval state
/// </param>
/// <param name="sql">
/// The SQL String buffer to
/// </param>
private static void AppendCachedWhere(DataRetrievalInfoSeries info, SqlQuery sqq)
{
if (string.IsNullOrEmpty(info.SqlWhereCache))
{
info.SqlWhereCache = GenerateWhere(info);
}
sqq.appendSql(info.SqlWhereCache);
}
/// <summary>
/// Write observation element. The time, observation and attribute values are in <paramref name="row"/>
/// </summary>
/// <param name="info">
/// The current Data Retrieval state
/// </param>
/// <param name="row">
/// The map between components and their values
/// </param>
/// <param name="value">
/// The observation value
/// </param>
/// <returns>
/// The number of observations stored. Is is always 1
/// </returns>
protected static int WriteObservation(DataRetrievalInfoSeries info, MappedValues row, string value)
{
// write time and obs value
info.SeriesWriter.WriteObservation(row.DimensionAtObservationValue, value);
// write observation attributes
WriteAttributes(row.AttributeObservationValues, info);
return 1;
}
/// <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>
/// Write the SDMX compliant values for dataset attached attributes to the <see cref="DataRetrievalInfoSeries.SeriesWriter"/>
/// </summary>
/// <param name="componentValues">
/// The map between components and their values
/// </param>
/// <param name="info">
/// The current Data Retrieval state
/// </param>
private static void WriteDataSetResults(MappedValues componentValues, DataRetrievalInfoSeries info)
{
WriteAttributes(componentValues.AttributeDataSetValues, info);
}
/// <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 executes an SQL query for retrieving the dataset attributes on the dissemination database and writes it to <see cref="DataRetrievalInfoSeries.SeriesWriter"/>
/// </summary>
/// <param name="info">
/// The current DataRetrieval state
/// </param>
/// <param name="connection">
/// The connection to the dissemination database
/// </param>
private static void ExecuteDataSetAttributeSqlQuery(DataRetrievalInfoSeries info, DbConnection connection)
{
if (string.IsNullOrEmpty(info.DataSetSqlString))
{
return;
}
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = info.DataSetSqlString;
// for pc-axis
command.CommandTimeout = 0;
var stopwatch = new Stopwatch();
stopwatch.Start();
using (IDataReader reader = command.ExecuteReader())
{
stopwatch.Stop();
Logger.Info(
string.Format(
CultureInfo.InvariantCulture, "Execute DataSet Attribute SQL Reader: {0}", stopwatch.Elapsed));
bool found = false;
while (!found && reader.Read())
{
var componentValues = new MappedValues(info, info.DataSetAttributes);
if (HandleComponentMapping(reader, componentValues, info.DataSetAttributes, info))
{
WriteDataSetResults(componentValues, info);
found = true;
}
}
// To avoid populating the IDataReader.RecordsAffected on IDataReader close which can take some time on some cases (typec)
// this requires an updated MySQL driver, 6.3.7 or later. See http://bugs.mysql.com/bug.php?id=60541
command.Cancel();
}
}
}
/// <summary>
/// Write a series element.
/// </summary>
/// <param name="info">
/// The current Data Retrieval state
/// </param>
/// <param name="row">
/// The map between components and their values
/// </param>
protected static void WriteSeries(DataRetrievalInfoSeries info, MappedValues row)
{
// start series
info.SeriesWriter.StartSeries();
// write dimensions
foreach (var dimensionValue in row.DimensionValues)
{
if (!dimensionValue.Key.Id.Equals(info.DimensionAtObservation))
{
info.SeriesWriter.WriteSeriesKeyValue(dimensionValue.Key.Id, dimensionValue.Value);
}
}
// write time period if it is not the dimension at observation
if (!info.IsTimePeriodAtObservation && info.TimeTranscoder != null)
{
info.SeriesWriter.WriteSeriesKeyValue(info.TimeTranscoder.Component.Id, row.TimeValue);
}
// write series attributes
WriteAttributes(row.AttributeSeriesValues, 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>
/// Store the SDMX compliant data for each component entity in the store
/// </summary>
/// <param name="info">
/// The current Data Retrieval state
/// </param>
/// <param name="maxMeasures">
/// The max number of measures to write
/// </param>
/// <param name="row">
/// The map between components and their values
/// </param>
/// <returns>
/// The number of observations stored
/// </returns>
private static int WriteXsMeasures(DataRetrievalInfoSeries info, int maxMeasures, MappedValues row)
{
int count = 0;
if (row.IsNewKey())
{
TryWriteDataSet(row, info);
WriteXsMeasureCache(info, row.XSMeasureCaches);
row.InitXsBuffer();
}
for (int i = 0; i < maxMeasures; i++)
{
var crossSectionalMeasureMapping = info.CrossSectionalMeasureMappings[i];
var xsComponent = crossSectionalMeasureMapping.Components[0];
row.AddToBuffer(xsComponent);
count++;
}
return count;
}
/// <summary>
/// Write the cached <paramref name="xsMeasures"/> .
/// </summary>
/// <param name="info">
/// The current dataretrieval info
/// </param>
/// <param name="xsMeasures">
/// The xs measures.
/// </param>
private static void WriteXsMeasureCache(
DataRetrievalInfoSeries info, IEnumerable<KeyValuePair<ComponentEntity, XsMeasureCache>> xsMeasures)
{
foreach (var xsMeasure in xsMeasures)
{
// start series
info.SeriesWriter.StartSeries();
foreach (var dimensionValue in xsMeasure.Value.CachedSeriesKey)
{
info.SeriesWriter.WriteSeriesKeyValue(dimensionValue.Key.Id, dimensionValue.Value);
}
info.SeriesWriter.WriteSeriesKeyValue(info.MeasureComponent.Id, xsMeasure.Value.XSMeasureCode);
WriteAttributes(xsMeasure.Value.CachedSeriesAttributes, info);
for (int index = 0; index < xsMeasure.Value.XSMeasureCachedObservations.Count; index++)
{
var cachedObservation = xsMeasure.Value.XSMeasureCachedObservations[index];
var attributes = xsMeasure.Value.Attributes[index];
info.SeriesWriter.WriteObservation(cachedObservation.Key, cachedObservation.Value);
WriteAttributes(attributes, info);
}
}
}
/// <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);
}
}