public static void GetRanking(SqlString strQuery, SqlString strSelectExtremas, SqlInt32 numberOfRecords, SqlString strRankingWeights, SqlString strRankingExpressions, SqlBoolean showInternalAttr, SqlString strColumnNames)
{
SPRanking ranking = new SPRanking();
ranking.Provider = Helper.ProviderClr;
ranking.ConnectionString = Helper.CnnStringSqlclr;
ranking.RecordAmountLimit = numberOfRecords.Value;
ranking.ShowInternalAttributes = showInternalAttr.Value;
ranking.GetRankingTable(strQuery.ToString(), false, strSelectExtremas.ToString(), strRankingWeights.ToString(), strRankingExpressions.ToString(), strColumnNames.ToString());
}
public static void GetRanking(SqlString strQuery, SqlString strSelectExtremas, SqlInt32 numberOfRecords, SqlString strRankingWeights, SqlString strRankingExpressions, SqlBoolean showInternalAttr, SqlString strColumnNames)
{
SPRanking ranking = new SPRanking();
ranking.Provider = Helper.ProviderClr;
ranking.ConnectionString = Helper.CnnStringSqlclr;
ranking.RecordAmountLimit = numberOfRecords.Value;
ranking.ShowInternalAttributes = showInternalAttr.Value;
ranking.GetRankingTable(strQuery.ToString(), false, strSelectExtremas.ToString(), strRankingWeights.ToString(), strRankingExpressions.ToString(), strColumnNames.ToString());
}
/// <summary>
/// Returns a datatable with the tuples from the SQL statement
/// The sql will be resolved into pieces, in order to call the Skyline algorithms without MSSQL CLR
/// </summary>
/// <param name="strPrefSQL"></param>
/// <param name="strategy"></param>
/// <param name="model"></param>
/// <returns></returns>
public DataTable GetResults(String strPrefSQL, SkylineStrategy strategy, PrefSQLModel model, bool ShowInternalAttributes)
{
DataTable dt = new DataTable();
//Default Parameter
string strQuery = "";
string strOperators = "";
int numberOfRecords = 0;
string[] parameter = null;
//Native SQL algorithm is already a valid SQL statement
if (strPrefSQL.StartsWith("SELECT", true, null))
{
if (model == null || !model.HasSkylineSample)
{
//If query doesn't need skyline calculation (i.e. query without preference clause) --> set algorithm to nativeSQL
strategy = new SkylineSQL();
}
else
{
throw new Exception("native SQL not yet supported."); // TODO: consider native SQL support
}
}
else
{
//Determine parameter only with skyline of clause and not with weihtedsum clause
DetermineParameters(strPrefSQL, out parameter, out strQuery, out strOperators, out numberOfRecords);
}
try
{
if (model != null && model.Ranking.Count > 0)
{
SPRanking ranking = new SPRanking();
ranking.Provider = DriverString;
ranking.ConnectionString = ConnectionString;
string strSelectExtremas = "";
string strRankingWeights = "";
string strRankingExpressions = "";
string strColumnNames = "";
// Set the decimal seperator, because prefSQL double values are always with decimal separator "."
NumberFormatInfo format = new NumberFormatInfo();
format.NumberDecimalSeparator = ".";
foreach (RankingModel rankingModel in model.Ranking)
{
strSelectExtremas += rankingModel.SelectExtrema + ";";
strRankingWeights += rankingModel.Weight.ToString(format) + ";";
strRankingExpressions += rankingModel.Expression + ";";
strColumnNames += rankingModel.FullColumnName.Replace(".", "_") + ";";
}
strSelectExtremas = strSelectExtremas.TrimEnd(';');
strRankingWeights = strRankingWeights.TrimEnd(';');
strRankingExpressions = strRankingExpressions.TrimEnd(';');
dt = ranking.GetRankingTable(strQuery, strSelectExtremas, strRankingWeights, strRankingExpressions, ShowInternalAttributes, strColumnNames);
}
else if (strategy.IsNative())
{
if (model == null || !model.HasSkylineSample)
{
//Native SQL
//Generic database provider
//Create the provider factory from the namespace provider, you could create any other provider factory.. for Oracle, MySql, etc...
DbProviderFactory factory = DbProviderFactories.GetFactory(DriverString);
// use the factory object to create Data access objects.
DbConnection connection = factory.CreateConnection(); // will return the connection object, in this case, SqlConnection ...
if (connection != null)
{
connection.ConnectionString = ConnectionString;
connection.Open();
DbCommand command = connection.CreateCommand();
command.CommandText = strPrefSQL;
DbDataAdapter db = factory.CreateDataAdapter();
if (db != null)
{
db.SelectCommand = command;
db.Fill(dt);
}
}
}
else
{
throw new Exception("native SQL not yet supported."); // TODO: consider native SQL support
}
}
else
{
if (strategy.SupportImplicitPreference() == false && model.ContainsOpenPreference)
{
throw new Exception(strategy.GetType() + " does not support implicit preferences!");
}
if (strategy.SupportIncomparable() == false && model.WithIncomparable)
{
throw new Exception(strategy.GetType() + " does not support incomparale tuples");
}
//Set the database provider
strategy.Provider = DriverString;
strategy.ConnectionString = ConnectionString;
strategy.Cardinality = Cardinality;
strategy.WindowHandling = WindowHandling;
strategy.RecordAmountLimit = numberOfRecords;
strategy.HasIncomparablePreferences = model.WithIncomparable;
strategy.AdditionParameters = parameter;
strategy.SortType = (int)model.Ordering;
if (!model.HasSkylineSample)
{
dt = strategy.GetSkylineTable(strQuery, strOperators);
TimeInMilliseconds = strategy.TimeMilliseconds;
NumberOfComparisons = strategy.NumberOfComparisons;
NumberOfMoves = strategy.NumberOfMoves;
}
else
{
var skylineSample = new SkylineSampling
{
SubsetCount = model.SkylineSampleCount,
SubsetDimension = model.SkylineSampleDimension,
SelectedStrategy = strategy
};
dt = skylineSample.GetSkylineTable(strQuery, strOperators);
TimeInMilliseconds = skylineSample.TimeMilliseconds;
//NumberOfOperations = skylineSample.NumberOfOperations;
}
}
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
throw;
}
return dt;
}
/// <summary>
/// Parses a PREFERENE SQL Statement in an ANSI SQL Statement
/// </summary>
/// <param name="strInput"></param>
/// <param name="prefSQLParam"></param>
/// <returns>Return the ANSI SQL Statement</returns>
/// <exception cref="Exception">This is exception is thrown because the String is not a valid PrefSQL Query</exception>
internal string ParsePreferenceSQL(string strInput, PrefSQLModel prefSQLParam)
{
SQLSort sqlSort = new SQLSort();
SQLCriterion sqlCriterion = new SQLCriterion();
string strSQLReturn = ""; //The SQL-Query that is built on the basis of the prefSQL
PrefSQLModel prefSQL = prefSQLParam ?? GetPrefSqlModelFromPreferenceSql(strInput);
try
{
//Check if parse was successful and query contains PrefSQL syntax
if (prefSQL != null) // && strInput.IndexOf(SkylineOf) > 0
{
if (prefSQL.Skyline.Count > 0)
{
//Mark as incomparable if needed (to choose the correct algorithm)
//withIncomparable = prefSQL.WithIncomparable;
//Add all Syntax before the Skyline-Clause
strSQLReturn = strInput.Substring(0, strInput.IndexOf(SkylineOf, StringComparison.Ordinal) - 1).TrimStart(' ');
//Add Skyline Attributes to select list. This option is i.e. useful to create a dominance graph.
//With help of the skyline values it is easier to create this graph
if (ShowInternalAttributes)
{
//Add the attributes to the existing SELECT clause
string strSQLSelectClause = GetSelectClauseForSkylineAttributes(prefSQL);
string strSQLBeforeFrom = strSQLReturn.Substring(0, strSQLReturn.IndexOf("FROM", StringComparison.Ordinal));
string strSQLAfterFromShow = strSQLReturn.Substring(strSQLReturn.IndexOf("FROM", StringComparison.Ordinal));
strSQLReturn = strSQLBeforeFrom + strSQLSelectClause + " " + strSQLAfterFromShow;
}
//Add ORDER BY Clause
string strOrderBy = "";
if (strInput.IndexOf("ORDER BY", StringComparison.Ordinal) > 0)
{
if (prefSQL.Ordering == Ordering.AsIs)
{
string strTmpInput = strInput;
//Replace category clauses
//Start with latest order by (otherwise substring start, stop position are changed)
for (int iIndex = prefSQL.OrderBy.Count - 1; iIndex >= 0; iIndex--)
{
OrderByModel model = prefSQL.OrderBy[iIndex];
strTmpInput = strTmpInput.Substring(0, model.Start) + model.Text + strTmpInput.Substring(model.Stop);
}
strOrderBy = strTmpInput.Substring(strInput.IndexOf("ORDER BY", StringComparison.Ordinal));
}
else
{
strOrderBy = sqlSort.GetSortClause(prefSQL, prefSQL.Ordering); // sqlSort.getSortClause(prefSQL, _OrderType);
}
//Add space charachter in front of ORDER BY if not already present
if (!strOrderBy.Substring(0, 1).Equals(" "))
{
strOrderBy = " " + strOrderBy;
}
}
////////////////////////////////////////////
//attributes used for native sql algorithm
string strWhere = sqlCriterion.GetCriterionClause(prefSQL, strSQLReturn);
////////////////////////////////////////////
//attributes used for other algorithms
string strOperators;
string strAttributesSkyline = BuildPreferencesBNL(prefSQL, out strOperators);
//Without SELECT
//Remove TOP keyword, expect for the native SQL algorithm
if (prefSQL.NumberOfRecords != 0 && SkylineType.IsNative() == false)
{
//Remove Top Keyword in inner clause
int iPosTop = strSQLReturn.IndexOf("TOP", StringComparison.Ordinal);
int iPosTopEnd = strSQLReturn.Substring(iPosTop + 3).TrimStart().IndexOf(" ", StringComparison.Ordinal);
string strSQLAfterTop = strSQLReturn.Substring(iPosTop + 3).TrimStart();
strSQLReturn = strSQLReturn.Substring(0, iPosTop) + strSQLAfterTop.Substring(iPosTopEnd + 1);
}
string strAttributesOutput = ", " + strSQLReturn.Substring(7, strSQLReturn.IndexOf("FROM", StringComparison.Ordinal) - 7);
string strSQLAfterFrom = strSQLReturn.Substring(strSQLReturn.IndexOf("FROM", StringComparison.Ordinal));
string strFirstSQL = "SELECT " + strAttributesSkyline + " " + strAttributesOutput + strSQLAfterFrom;
if (SkylineType.IsNative())
{
strFirstSQL = strSQLReturn;
}
string strOrderByAttributes = sqlSort.GetSortClause(prefSQL, WindowSort);
////////////////////////////////////////////
//attributes used for hexagon
string[] additionalParameters = new string[6];
string strOperatorsHexagon;
string strAttributesSkylineHexagon = BuildSelectHexagon(prefSQL, out strOperatorsHexagon);
//Without SELECT
//Quote quotes because it is a parameter of the stored procedure
string strFirstSQLHexagon = "SELECT " + strAttributesSkylineHexagon + " " + strAttributesOutput + strSQLAfterFrom;
strFirstSQLHexagon = strFirstSQLHexagon.Replace("'", "''");
//Quote quotes because it is a parameter of the stored procedure
//string strSelectDistinctIncomparable = "";
string weightHexagonIncomparable = "";
string strSelectDistinctIncomparable = BuildIncomparableHexagon(prefSQL, ref weightHexagonIncomparable);
strSelectDistinctIncomparable = strSelectDistinctIncomparable.Replace("'", "''");
additionalParameters[0] = strFirstSQLHexagon;
additionalParameters[1] = strOperatorsHexagon;
additionalParameters[2] = strSelectDistinctIncomparable;
additionalParameters[3] = weightHexagonIncomparable;
_skylineType.SortType = (int)prefSQL.Ordering;
_skylineType.RecordAmountLimit = prefSQL.NumberOfRecords;
_skylineType.MultipleSkylineUpToLevel = _skylineUpToLevel;
_skylineType.AdditionParameters = additionalParameters;
_skylineType.HasIncomparablePreferences = prefSQL.WithIncomparable;
//Now create the query depending on the Skyline algorithm
if (!prefSQL.HasSkylineSample)
{
strSQLReturn = _skylineType.GetStoredProcedureCommand(strWhere, strOrderBy, strFirstSQL,
strOperators, strOrderByAttributes);
}
else
{
var skylineSample = new SkylineSampling
{
SubsetCount = prefSQL.SkylineSampleCount,
SubsetDimension = prefSQL.SkylineSampleDimension,
SelectedStrategy = _skylineType
};
strSQLReturn = skylineSample.GetStoredProcedureCommand(strWhere, strOrderBy, strFirstSQL,
strOperators, strOrderByAttributes);
}
}
if(prefSQL.Ranking.Count > 0)
{
if(prefSQL.ContainsOpenPreference)
{
throw new Exception("WeightedSum cannot handle implicit INCOMPARABLE values. Please add the explicit OTHERS EQUAL to the preference");
}
string strSelectExtremas = "";
string strRankingWeights = "";
string strRankingExpressions = "";
string strColumnNames = "";
// Set the decimal seperator, because prefSQL double values are always with decimal separator "."
NumberFormatInfo format = new NumberFormatInfo();
format.NumberDecimalSeparator = ".";
foreach (RankingModel rankingModel in prefSQL.Ranking)
{
strSelectExtremas += rankingModel.SelectExtrema + ";";
strRankingWeights += rankingModel.Weight.ToString(format) + ";";
strRankingExpressions += rankingModel.Expression + ";";
strColumnNames += rankingModel.FullColumnName.Replace(".", "_") + ";";
}
strSelectExtremas = strSelectExtremas.TrimEnd(';');
strRankingWeights = strRankingWeights.TrimEnd(';');
strRankingExpressions = strRankingExpressions.TrimEnd(';');
strColumnNames = strColumnNames.TrimEnd(';');
SPRanking spRanking = new SPRanking();
spRanking.ShowInternalAttributes = ShowInternalAttributes;
//Quoting is done in GetStoredProc Command
//string strExecSQL = strInput.Replace("'", "''");
strSQLReturn = spRanking.GetStoredProcedureCommand(strInput, strSelectExtremas, strRankingWeights, strRankingExpressions, strColumnNames);
}
}
else
{
//Query does not contain a preference --> return original query
strSQLReturn = strInput;
}
}
catch (Exception e)
{
//Parse syntaxerror
throw new Exception(e.Message);
}
return strSQLReturn;
}