/// <summary>
/// tmAttribute
/// </summary>
/// <returns>ID of new row inserted to database.</returns>
private int SetColumn(string name, int matrixDBID, ColumnTypeEnum columnSubType, ValueSubTypeEnum valueSubType, string formula, int primaryKeyPosition)
{
string tableName = "tmAttribute";
/* MultiColumns are not implemented so if you want to implement it
* you should implement appropriate box module and somewhere here
* you should implement storing setting for the MultiColumn i.e.
* MCPosition,MCLength,MCDelimeter
* */
//ValueMin,ValueMax,ValueAvg,ValueModus,ValueVariability are not stored its needless
string autoIncrementColumn = GetAutoIncrementColumnName(tableName);
int autoIncrementValue = GetTableAutoIncrementValue(tableName, 1);
string query = "INSERT INTO " + tableName + " (" + autoIncrementColumn
+ ",Name,MatrixID,AttributeSubTypeID,ValueSubTypeID"
+ ",Formula,PrimaryKeyPosition,wSavedCountUsed) VALUES "
+ "(" + autoIncrementValue + ","
+ "'" + name + "',"
+ matrixDBID + ","
+ constants.ColumnSubTypeEnumDictionary[columnSubType] + ","
+ constants.ValueSubTypeEnumDictionary[valueSubType] + ","
+ "'" + formula + "',"
+ primaryKeyPosition + ","
+ constants.wSavedCountUsed
+ ")";
ExecuteInsertQuery(query, tableName);
return autoIncrementValue;
}
/// <summary>
/// Determines whether colum sub type is cardinal.
/// </summary>
/// <param name="columnValueSubType">Value sub type of the column.</param>
/// <returns>
/// <c>true</c> if colum sub type is cardinal; otherwise, <c>false</c>.
/// </returns>
public static bool IsColumSubTypeCardinal(ValueSubTypeEnum columnValueSubType)
{
switch (GetColumnValueTypeByValueSubType(columnValueSubType))
{
case ValueType.Integral:
case ValueType.Floating:
case ValueType.DateTime:
//case ValueType.Time:
return true;
default:
return false;
}
}
/// <summary>
/// Determines whether colum sub type is ordinal.
/// </summary>
/// <param name="columnValueSubType">Value sub type of the column.</param>
/// <returns>
/// <c>true</c> if colum value sub type is ordinal; otherwise, <c>false</c>.
/// </returns>
public static bool IsColumSubTypeOrdinal(ValueSubTypeEnum columnValueSubType)
{
switch (GetColumnValueTypeByValueSubType(columnValueSubType))
{
case ValueType.Unknown:
return false;
default:
return true;
}
}
/// <summary>
/// Gets the <see cref="T:Ferda.Modules.Boxes.DataMiningCommon.Column.StatisticsInfo">statistics</see>
/// i.e. count of distinct values, minimal result, maximal result, average result
/// (if result type is not cardinal average length is returned) and for cardinal values
/// also computes variability and standard deviation.
/// </summary>
/// <param name="odbcConnectionString">The ODBC connection string.</param>
/// <param name="dataMatrixName">Name of the data matrix.</param>
/// <param name="columnSelectExpression">The column select expression.</param>
/// <param name="columnSubType">Type of the column sub.</param>
/// <param name="boxIdentity">The box identity.</param>
/// <returns>
/// <see cref="T:Ferda.Modules.Boxes.DataMiningCommon.Column.StatisticsInfo"/>
/// i.e. count of distinct values, minimal result, maximal result, average result
/// (if result type is not cardinal average length is returned) and for cardinal values
/// also computes variability and standard deviation.
/// </returns>
public static StatisticsInfo GetStatistics(string odbcConnectionString, string dataMatrixName, string columnSelectExpression, ValueSubTypeEnum columnSubType, string boxIdentity)
{
//throws exception if odbcConnectionString is wrong
OdbcConnection conn = Ferda.Modules.Helpers.Data.OdbcConnections.GetConnection(odbcConnectionString, boxIdentity);
StatisticsInfo result = new StatisticsInfo();
OdbcCommand odbcCommand = new OdbcCommand();
odbcCommand.Connection = conn;
bool isCardinal = false;
try
{
OdbcDataAdapter myDataAdapter = new OdbcDataAdapter(
"SELECT DISTINCT " + SqlSecurity.ColumnQuote + columnSelectExpression + SqlSecurity.ColumnQuote + " FROM " + "`" + dataMatrixName + "`",
conn);
System.Data.DataSet myDataSet = new System.Data.DataSet();
myDataAdapter.Fill(myDataSet);
result.ValueDistincts = Convert.ToInt64(myDataSet.Tables[0].Rows.Count);
/* much more effective but unsupported
* UNDONE tohle predelat
odbcCommand.CommandText = "SELECT COUNT(DISTINCT " + SqlSecurity.ColumnQuote + columnSelectExpression + SqlSecurity.ColumnQuote + ") FROM " + "`" + dataMatrixName + "`";
result.ValueDistincts = Convert.ToInt64(odbcCommand.ExecuteScalar());
* */
isCardinal = IsColumSubTypeCardinal(GetColumnSubTypeByDataType(GetDataType(odbcConnectionString, dataMatrixName, columnSelectExpression, boxIdentity)));
}
catch (Exception ex)
{
//throws exception if dataMatrixName or columnName is wrong
TestColumnSelectExpression(odbcConnectionString, dataMatrixName, columnSelectExpression, boxIdentity);
//or other reason for exception
throw Ferda.Modules.Exceptions.BadParamsUnexpectedReasonError(ex, boxIdentity);
}
string selectMaxExpression = "MAX(" + SqlSecurity.ColumnQuote + columnSelectExpression + SqlSecurity.ColumnQuote + ") AS Maximum";
string selectMinExpression = "MIN(" + SqlSecurity.ColumnQuote + columnSelectExpression + SqlSecurity.ColumnQuote + ") AS Minimum";
string selectAvgExpression = (isCardinal)
? "AVG(" + SqlSecurity.ColumnQuote + columnSelectExpression + SqlSecurity.ColumnQuote + ") AS Average"
: "AVG(LEN(" + SqlSecurity.ColumnQuote + columnSelectExpression + SqlSecurity.ColumnQuote + ")) AS Average";
odbcCommand.CommandText = "SELECT "
+ selectMaxExpression + ","
+ selectMinExpression + ","
+ selectAvgExpression
+ " FROM " + "`" + dataMatrixName + "`";
//System.Diagnostics.Debug.WriteLine("aggr_s:" + DateTime.Now.ToString());
OdbcDataReader odbcDataReader = odbcCommand.ExecuteReader();
//System.Diagnostics.Debug.WriteLine("aggr_e:" + DateTime.Now.ToString());
if (odbcDataReader.Read())
{
result.ValueMax = odbcDataReader["Maximum"].ToString();
result.ValueMin = odbcDataReader["Minimum"].ToString();
result.ValueAverage = odbcDataReader["Average"].ToString();
}
odbcDataReader.Close();
//System.Diagnostics.Debug.WriteLine("card_s:" + DateTime.Now.ToString());
if (isCardinal)
{
odbcCommand.CommandText = "SELECT COUNT(1) FROM " + "`" + dataMatrixName + "`";
long dataMatrixRowsCount = Convert.ToInt64(odbcCommand.ExecuteScalar());
//TODO optimize this
odbcCommand.CommandText =
"SELECT SUM( "
+ "(" + SqlSecurity.ColumnQuote + columnSelectExpression + SqlSecurity.ColumnQuote + " - '" + result.ValueAverage + "')"
+ " * (" + SqlSecurity.ColumnQuote + columnSelectExpression + SqlSecurity.ColumnQuote + " - '" + result.ValueAverage + "')"
+ ") FROM " + "`" + dataMatrixName + "`";
result.ValueVariability = Convert.ToDouble(odbcCommand.ExecuteScalar()) / dataMatrixRowsCount;
result.ValueStandardDeviation = Math.Sqrt(result.ValueVariability);
}
//System.Diagnostics.Debug.WriteLine("card_e:" + DateTime.Now.ToString());
return result;
}
/// <summary>
/// Gets the <see cref="T:Ferda.Modules.Helpers.Data.Column.ValueType">value type</see>
/// of the column by <see cref="T:Ferda.Modules.ValueSubTypeEnum">value sub type</see>.
/// </summary>
/// <param name="columnValueSubType">Type of the column value sub.</param>
/// <returns></returns>
public static ValueType GetColumnValueTypeByValueSubType(ValueSubTypeEnum columnValueSubType)
{
switch (columnValueSubType)
{
case ValueSubTypeEnum.ShortIntegerType:
case ValueSubTypeEnum.UnsignedShortIntegerType:
case ValueSubTypeEnum.IntegerType:
case ValueSubTypeEnum.UnsignedIntegerType:
case ValueSubTypeEnum.LongIntegerType:
case ValueSubTypeEnum.UnsignedLongIntegerType:
return ValueType.Integral;
case ValueSubTypeEnum.FloatType:
case ValueSubTypeEnum.DoubleType:
case ValueSubTypeEnum.DecimalType:
return ValueType.Floating;
case ValueSubTypeEnum.DateTimeType:
return ValueType.DateTime;
//case ValueSubTypeEnum.TimeType:
////return ValueType.Time;
//return ValueType.DateTime;
case ValueSubTypeEnum.BooleanType:
return ValueType.Boolean;
case ValueSubTypeEnum.StringType:
return ValueType.String;
case ValueSubTypeEnum.Unknown:
return ValueType.Unknown;
default:
throw Ferda.Modules.Exceptions.SwitchCaseNotImplementedError(columnValueSubType);
}
}
public ValueSubTypeEnum Value(string boxIdentity, DateTimeT lastReloadTime, string connectionString, string dataMatrixName, string columnSelectExpression, bool leaveConnection)
{
lock (this)
{
Dictionary<string, IComparable> cacheSetting = new Dictionary<string, IComparable>();
cacheSetting.Add(Database.DatabaseBoxInfo.typeIdentifier + Database.DatabaseBoxInfo.OdbcConnectionStringPropertyName, connectionString);
cacheSetting.Add(DataMatrix.DataMatrixBoxInfo.typeIdentifier + DataMatrix.DataMatrixBoxInfo.DataMatrixNamePropertyName, dataMatrixName);
cacheSetting.Add(Column.ColumnBoxInfo.typeIdentifier + Column.ColumnBoxInfo.ColumnSelectExpressionPropertyName, columnSelectExpression);
if (IsObsolete(lastReloadTime, cacheSetting))
{
value = ValueSubTypeEnum.Unknown;
value = Ferda.Modules.Helpers.Data.Column.GetColumnSubTypeByDataType(
Ferda.Modules.Helpers.Data.Column.GetDataType(connectionString, dataMatrixName, columnSelectExpression, boxIdentity));
}
return value;
}
}
public StatisticsInfo Value(string boxIdentity, DateTimeT lastReloadTime, string connectionString, string dataMatrixName, long dataMatrixRecordsCount, string columnSelectExpression, ValueSubTypeEnum columnValueSubType)
{
lock (this)
{
Dictionary<string, IComparable> cacheSetting = new Dictionary<string, IComparable>();
cacheSetting.Add(Database.DatabaseBoxInfo.typeIdentifier + Database.DatabaseBoxInfo.OdbcConnectionStringPropertyName, connectionString);
cacheSetting.Add(DataMatrix.DataMatrixBoxInfo.typeIdentifier + DataMatrix.DataMatrixBoxInfo.DataMatrixNamePropertyName, dataMatrixName);
cacheSetting.Add(DataMatrix.DataMatrixBoxInfo.typeIdentifier + DataMatrix.DataMatrixBoxInfo.RecordCountPropertyName, dataMatrixRecordsCount);
cacheSetting.Add(Column.ColumnBoxInfo.typeIdentifier + Column.ColumnBoxInfo.ColumnSelectExpressionPropertyName, columnSelectExpression);
if (IsObsolete(lastReloadTime, cacheSetting))
{
value = new StatisticsInfo();
value = Ferda.Modules.Helpers.Data.Column.GetStatistics(connectionString, dataMatrixName, columnSelectExpression, columnValueSubType, boxIdentity);
}
if (value == null)
value = new StatisticsInfo();
return value;
}
}
/// <summary>
/// Class constructor
/// </summary>
/// <param name="columnInfo">ColumnInfo</param>
/// <param name="categoriesStruct">CategoriesStruct</param>
public DBInteraction(ColumnInfo columnInfo, CategoriesStruct categoriesStruct)
{
this.connectionString = columnInfo.dataMatrix.database.odbcConnectionString;
this.columnSelectExpression = columnInfo.columnSelectExpression;
this.dataMatrixName = columnInfo.dataMatrix.dataMatrixName;
this.rowCount = columnInfo.dataMatrix.recordsCount;
this.categoriesStruct = categoriesStruct;
this.columnType = columnInfo.columnSubType;
this.connection = this.GetConnection();
}
