/// <summary>
/// Initializes a new instance of the <see cref="DataSetStoreDB"/> class
/// </summary>
/// <param name="dbInfo">
/// The database information which will used to connect
/// </param>
/// <param name="cacheTableName">
/// The cache database table name
/// </param>
/// <param name="keyFamily">
/// The SDMX keyfamily bean
/// </param>
public DataSetStoreDB(DBInfo dbInfo, string cacheTableName, IDataStructureObject keyFamily, bool CreateTable, bool useAttr)
{
if (dbInfo == null)
{
throw new ArgumentNullException("dbInfo");
}
if (string.IsNullOrEmpty(cacheTableName))
{
throw new ArgumentNullException("cacheTableName");
}
if (keyFamily == null)
{
throw new ArgumentNullException("keyFamily");
}
this._dbInfo = dbInfo;
this._cacheTableName = cacheTableName;
if (dbInfo.Connection.GetType().Name.Contains("Oracle"))
{
this._varChar = "varchar2";
}
kf = keyFamily;
if (CreateTable)
{
this.CreateSqlTable(dbInfo, keyFamily);
}
else
{
this.PrepareSqlColumns(keyFamily);
}
parseSdmx = useAttr;
}
/// <summary>
/// Create the <see cref="_cacheTableName"/> table in the database specified in the given <see cref="DBInfo"/> with columns from the specified KeyFamily Bean
/// It also populates the <see cref="_columns"/>. The components that are used are the Dimensions, TimeDimensions and Primary Measure. Depending on <see cref="ParseSdmxAttributes"/> the SDMX Attributes are either ignored or used
/// </summary>
/// <param name="dbInfo">
/// The <see cref="DBInfo"/>
/// </param>
/// <param name="keyFamily">
/// The key family bean
/// </param>
protected void CreateSqlTable(DBInfo dbInfo, IDataStructureObject keyFamily)
{
var createTable = new StringBuilder("CREATE TABLE " + this._cacheTableName + " (");
var primaryMeasureKey = new StringBuilder();
// dimensions add them all and to primary key
foreach (IDimension comp in keyFamily.DimensionList.Dimensions)
{
if (!comp.TimeDimension)
{
createTable.AppendFormat("{0} {1}({2}) NOT NULL,", comp.Id, this._varChar, VarCharSize);
createTable.AppendLine();
this._columns.Add(comp.Id, null);
if (comp.MeasureDimension)
{
this._measureColumn = comp.Id;
}
primaryMeasureKey.Append(comp.Id);
primaryMeasureKey.Append(",");
}
}
primaryMeasureKey.Length--;
if (keyFamily.TimeDimension != null)
{
createTable.AppendFormat(
"{0} {1}({2}) NOT NULL,", keyFamily.TimeDimension.Id, this._varChar, VarCharSize);
primaryMeasureKey.Append(",");
primaryMeasureKey.Append(keyFamily.TimeDimension.Id);
/*
* lastComma = createTable.Length -1 ;
*/
createTable.AppendLine();
this._columns.Add(keyFamily.TimeDimension.Id, null);
}
createTable.AppendFormat("{0} {1}({2}),", keyFamily.PrimaryMeasure.Id, this._varChar, VarCharSize);
int lastComma = createTable.Length - 1;
createTable.AppendLine();
this._columns.Add(keyFamily.PrimaryMeasure.Id, null);
if (this.ParseSdmxAttributes)
{
foreach (IAttributeObject comp in keyFamily.Attributes)
{
createTable.AppendFormat("{0} {1}({2}),", comp.Id, this._varChar, VarCharSize);
this._columns.Add(comp.Id, null);
lastComma = createTable.Length - 1;
createTable.AppendLine();
}
}
if (primaryMeasureKey.Length > 0)
{
createTable.Append("PRIMARY KEY(");
createTable.Append(primaryMeasureKey);
createTable.Append(")");
createTable.AppendLine();
}
else
{
createTable.Length = lastComma;
}
createTable.Append(")");
using (DbCommand scmd = dbInfo.TransactionCommand)
{
scmd.CommandText = createTable.ToString();
scmd.ExecuteNonQuery();
}
dbInfo.Commit();
}
internal IDataSetStore GetDataset(IDataflowObject df, IDataStructureObject kf, List<DataCriteria> Criterias, ref Dictionary<string, List<DataChacheObject>> DataCache,bool useAttr)
{
// if it is not time series then assume it is cross
SDMXWSFunction op = SDMXWSFunction.GetCompactData;
bool cross = (DataObjConfiguration._TypeEndpoint == EndpointType.V21 || DataObjConfiguration._TypeEndpoint == EndpointType.REST)
? NsiClientHelper.DataflowDsdIsCrossSectional(kf) : !Utils.IsTimeSeries(kf);
if (cross)
op = SDMXWSFunction.GetCrossSectionalData;
var ser = new JavaScriptSerializer();
ser.MaxJsonLength = int.MaxValue;
try
{
//commentato vecchio codice
//IGetSDMX GetSDMXObject = WebServiceSelector.GetSdmxImplementation(DataObjConfiguration);
//GetSDMXObject.ExecuteQuery(CreateQueryBean(df, kf, Criterias), op, FileTmpData);
/*
#region Connessione e Creazione DB SQLLite
string table = Path.Combine(Utils.GetAppPath(), string.Format(CultureInfo.InvariantCulture, "{0}-{1}.sqlite", Utils.MakeKey(df).Replace("+", "_").Replace(".", ""), Guid.NewGuid()));
string ConnectionString = string.Format(CultureInfo.InvariantCulture, Constants.FileDBSettingsFormat, table);
var info = new DBInfo(ConnectionString);
string tempTable = "table_" + Utils.MakeKey(df).Replace("+", "_").Replace(".", "");
IDataSetStore store = new DataSetStoreDB(info, tempTable, kf, true, useAttr);
#endregion
fine vecchio codice*/
//Salvo in Session
/*
if (DataCache == null)
DataCache = new Dictionary<string, List<DataChacheObject>>();
if (!DataCache.ContainsKey(Utils.MakeKey(df)))
DataCache[Utils.MakeKey(df)] = new List<DataChacheObject>();
*/
//string table=null;
//FABIO NEW
//IDataSetStore store = FindDataCacheChart(df, kf, Criterias, ref DataCache, useAttr,out table);
//if (store == null) store = GetDataset(df, kf, Criterias, ref DataCache, useAttr);
#region Connessione e Creazione DB SQLLite FABIO se nullo lo istanzio
// if (store == null)
// {
string table = null;
IGetSDMX GetSDMXObject = WebServiceSelector.GetSdmxImplementation(DataObjConfiguration);
GetSDMXObject.ExecuteQuery(CreateQueryBean(df, kf, Criterias), op, FileTmpData);
#region Connessione e Creazione DB SQLLite
table = Path.Combine(Utils.GetAppPath(), string.Format(CultureInfo.InvariantCulture, "{0}-{1}.sqlite", Utils.MakeKey(df).Replace("+", "_").Replace(".", ""), Guid.NewGuid()));
string ConnectionString = string.Format(CultureInfo.InvariantCulture, Constants.FileDBSettingsFormat, table);
var info = new DBInfo(ConnectionString);
string tempTable = "table_" + Utils.MakeKey(df).Replace("+", "_").Replace(".", "");
IDataSetStore store = new DataSetStoreDB(info, tempTable, kf, true, useAttr);
#endregion
using (var dataLocation = new FileReadableDataLocation(FileTmpData))
{
switch (op)
{
case SDMXWSFunction.GetCompactData:
var compact = new CompactDataReaderEngine(dataLocation, df, kf);
var readerCompact = new SdmxDataReader(kf, store);
readerCompact.ReadData(compact);
break;
case SDMXWSFunction.GetCrossSectionalData:
var dsdCrossSectional = (ICrossSectionalDataStructureObject)kf;
var crossSectional = new CrossSectionalDataReaderEngine(dataLocation, dsdCrossSectional, df);
var reader = new SdmxDataReader(kf, store);
reader.ReadData(crossSectional);
break;
default:
throw new ArgumentException(Resources.ExceptionUnsupported_operation + op.ToString(), "operation");
}
}
// }
#endregion FABIO
//using (var dataLocation = new FileReadableDataLocation(FileTmpData))
//{
// switch (op)
// {
// case SDMXWSFunction.GetCompactData:
// var compact = new CompactDataReaderEngine(dataLocation, df, kf);
// var readerCompact = new SdmxDataReader(kf, store);
// readerCompact.ReadData(compact);
// break;
// case SDMXWSFunction.GetCrossSectionalData:
// var dsdCrossSectional = (ICrossSectionalDataStructureObject)kf;
// var crossSectional = new CrossSectionalDataReaderEngine(dataLocation, dsdCrossSectional, df);
// var reader = new SdmxDataReader(kf, store);
// reader.ReadData(crossSectional);
// break;
// default:
// throw new ArgumentException(Resources.ExceptionUnsupported_operation + op.ToString(), "operation");
// }
//}
/*
Dictionary<string, List<string>> Criteri = new Dictionary<string, List<string>>();
Criterias.ForEach(c => Criteri.Add(c.component, c.values));
DataChacheObject dco = new DataChacheObject()
{
Criterias = Criteri,
DBFileName = table,
};
//aggiunta da fabio
DataCache[Utils.MakeKey(df)].Clear();
//fine aggiunta fabio
DataCache[Utils.MakeKey(df)].Add(dco);
*/
return store;
}
catch (Exception ex)
{
Logger.Warn(ex.Message, ex);
throw ex;
}
finally
{
//delete the temporary file
if (File.Exists(FileTmpData))
File.Delete(FileTmpData);
}
}
private static IDataSetStore BuildDBDataSetStore(SessionQuery query, ConnectionStringSettings settings)
{
var info = new DBInfo(settings);
string tempTable = "table_" + Path.GetRandomFileName().Replace(".", "_");
query.AddToDataDisposeList(info);
return new DataSetStoreDB(info, tempTable, query.KeyFamily,true,false);
}
internal IDataSetStore FindDataCacheChart(IDataflowObject df, IDataStructureObject kf, List<DataCriteria> Criterias, ref Dictionary<string, List<DataChacheObject>> DataCache, bool useAttr, out string DBFileName)
{
DataChacheObject findCache = null;
Dictionary<string, List<string>> Criteri = new Dictionary<string, List<string>>();
Criterias.ForEach(c => Criteri.Add(c.component, c.values));
DBFileName = null;
if (DataCache != null)
{
string DfID = Utils.MakeKey(df);
if (DataCache.ContainsKey(DfID))
{
List<DataChacheObject> singleDataCache = DataCache[DfID];
findCache = singleDataCache.Find(dc => DictionaryEqual(dc.Criterias, Criteri));
}
}
if (findCache != null)
{
if (!string.IsNullOrEmpty(findCache.DBFileName) && File.Exists(findCache.DBFileName))
{
#region Connessione e Creazione DB SQLLite
string ConnectionString = string.Format(CultureInfo.InvariantCulture, Constants.FileDBSettingsFormat, findCache.DBFileName);
var info = new DBInfo(ConnectionString);
string tempTable = "table_" + Utils.MakeKey(df).Replace("+", "_").Replace(".", "");
IDataSetStore store = new DataSetStoreDB(info, tempTable, kf, false, useAttr);
DBFileName = findCache.DBFileName;
return store;
#endregion
}
return null;
}
return null;
}
