/// <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(); }