private void Expression()
{
var users = new TableDefinition
{
Name = "Users",
Columns = new ColumnDefinition[]
{
new ColumnDefinition("Id", FieldType.Int),
new ColumnDefinition("Username", FieldType.String),
new ColumnDefinition("Password", FieldType.String),
}
};
// Select password from useres where id == 2
var select = new SqlSelectQuery();
select.From = new SqlTableSource(users, "u");
select.Fields = new SqlSelectField[]
{
new SqlSelectField(new SqlColumnExpression(select.From.FieldByIndex(2)), null)
};
select.Where = new SqlMathExpression(
FieldType.Bool,
new SqlColumnExpression(select.From.FieldByIndex(0)),
FieldMathOperator.Equal,
new SqlConstantExpression(FieldType.Int, 2)
);
var driver = new SQLLiteDatabaseDriver();
string sql = driver.GenerateSelect(select);
Console.WriteLine(sql);
}
public void TestParsingSelectQueryWithThreeJoin()
{
var secondJoin = string.Format("Select e,f,g from [Data2 as t1 Join (Select * FROM Data3) as t2 on t1.he=t2.hy]");
string sentences = string.Format("Select a,b,c from [Data as t1 Join ({0}) as t2 on t1.ha=t2.ho]", secondJoin);
SqlSelectQuery query = new SqlSelectQuery(sentences);
Assert.IsTrue(query.Attributes.Trim() == "a,b,c");
Assert.IsTrue(query.TableName.Trim() == "Data");
Assert.IsTrue(query.ConditionClause.Trim() == "");
Assert.IsTrue(query.Strategy.Equals(EvaluationStrategy.Default));
Assert.IsTrue(query.JoinOnAttributes.Trim() == "t1.ha=t2.ho");
Assert.IsTrue(query.HasSubquery == true);
var subquery = query.SubQuery;
Assert.IsTrue(subquery.HasSubquery == true);
Assert.IsTrue(subquery.TableName.Trim() == "Data2");
Assert.IsTrue(subquery.Attributes == "e,f,g");
Assert.IsTrue(subquery.JoinOnAttributes.Trim() == "t1.he=t2.hy");
var subsubquery = subquery.SubQuery;
Assert.IsTrue(subsubquery.HasSubquery == false);
Assert.IsTrue(subsubquery.TableName.Trim() == "Data3");
Assert.IsTrue(subsubquery.Attributes == "*");
}
public string submitQuerySQL(string sql, out DataTable answerSet)
{
SqlQuery rawQuery = new SqlQuery(sql);
QueryType qType = rawQuery.ProcessType();
answerSet = null;
switch (qType)
{
case QueryType.INSERT:
var query = new SqlInsertQuery(sql);
query.ProcessAndPopulateEachField();
var iHandler = new InsertQueryHandler(query, underlineDatabase);
answerSet = iHandler.HandleInsertQuery();
break;
case QueryType.SELECT:
var squery = new SqlSelectQuery(sql);
var sHandler = new SelectQueryHandler(squery, underlineDatabase);
answerSet = sHandler.HandleSelectSqlQuery();
break;
case QueryType.CREATE:
var cquery = new SqlCreateTableQuery(sql);
var handler = new CreateTableHandler(cquery, underlineDatabase);
answerSet = handler.HandleCreateTableQuery();
break;
default:
break;
}
return("end of submitSQL function");
}
/// <summary>
/// Default is Extensional method, while monte carlo is sampling using frequency of event occur
/// </summary>
/// <param name="attributes"> must specify what they are, wildcard like * not supported !</param>
/// <returns></returns>
private DataTable ComputeJointResultUsingNaiveStrategy(string attributes, SqlSelectQuery query, bool intermediate)
{
int attributeSize = PreparePossibleStatesTable(query.TableName);
CreatePossibleWorldsTable(query.TableName, attributeSize);
PreparePossibleWorldsTable(query.TableName);
PreparePossibleWorldsAggregatedTable(query.TableName);
return(NaiveStrategy(attributes, query.ConditionClause, query.TableName, intermediate));
}
public IEnumerable <T> Select <T>() where T : class
{
var sql = new SqlSelectQuery <T>();
sql = sql.Select((o) => o);
var strSql = sql.ToString();
return(null);
}
public IEnumerable <T> Select <T>(Expression <Func <T, object> > columns, Expression <Func <T, bool> > where = null) where T : class
{
var sql = new SqlSelectQuery <T>();
sql = sql.Select(columns)
.Where(where);
var strSql = (sql as ISqlQuery).ToString();
return(null);
}
public void TestParsingSelectQueryWithWhereClause()
{
string sentences = "Select a,b,c from Data where (a=25 and b=15)";
SqlSelectQuery query = new SqlSelectQuery(sentences);
Assert.IsTrue(query.Attributes.Trim() == "a,b,c");
Assert.IsTrue(query.TableName.Trim() == "Data");
Assert.IsTrue(query.ConditionClause.Trim() == "a=25 and b=15");
Assert.IsTrue(query.Strategy.Equals(EvaluationStrategy.Default));
}
public void TestParsingSelectQueryWithOnlyStrategy()
{
string sentences = "Select a,b,c from Data Evaluate using (monte Carlo)";
SqlSelectQuery query = new SqlSelectQuery(sentences);
Assert.IsTrue(query.Attributes.Trim() == "a,b,c");
Assert.IsTrue(query.TableName.Trim() == "Data");
Assert.IsTrue(query.ConditionClause.Trim() == "");
Assert.IsTrue(query.Strategy.Equals(EvaluationStrategy.MonteCarlo));
}
private SqlSelectQuery <Member> MembersQuery(bool searchAll)
{
SqlSelectQuery <Member> query = Context.Select <Member>();
if (searchAll)
{
return(query);
}
return(query
.Where(x => x.Activated).EqualTo(true)
.Where(x => x.Disabled).EqualTo(false)
.WhereAny <MemberSubscription, SubscriptionType>(x => x.Type,
new [] { SubscriptionType.Trial, SubscriptionType.Full, SubscriptionType.Partial }));
}
public IEnumerable <T> Select <T>(string columns, string from, string where = null, string groupBy = null, string having = null, string orderBy = null, int pageIndex = 1, int pageSize = 40) where T : class
{
int rowsOffet = (pageIndex - 1) * pageSize;
var sql = new SqlSelectQuery <string>();
sql = sql.Select(columns, from)
.Where(where)
.GroupBy(groupBy)
.Having(having)
.OrderBy(orderBy)
.Skip(rowsOffet)
.Take(pageSize);
var strSql = (sql as ISqlQuery).ToString();
return(null);
}
public IEnumerable <T> Select <T>(Expression <Func <T, object> > columns, Expression <Func <T, bool> > where = null, Expression <Func <T, object> > groupBy = null, Expression <Func <T, object> > having = null, Expression <Func <T, object> > orderBy = null, int pageIndex = 1, int pageSize = 40) where T : class
{
int rowsOffet = (pageIndex - 1) * pageSize;
var sql = new SqlSelectQuery <T>();
sql = sql.Select(columns)
.Where(where)
.GroupBy(groupBy)
.Having(having)
.OrderBy(orderBy)
.Skip(rowsOffet)
.Take(pageSize);
var strSql = (sql as ISqlQuery).ToString();
return(null);
}
public void TestParsingSelectQueryWithJoin()
{
string sentences = "Select a,b,c from [Data as t1 Join (SELECT * FROM Data2) as t2 on t1.ha=t2.ho]";
SqlSelectQuery query = new SqlSelectQuery(sentences);
Assert.IsTrue(query.Attributes.Trim() == "a,b,c");
Assert.IsTrue(query.TableName.Trim() == "Data");
Assert.IsTrue(query.ConditionClause.Trim() == "");
Assert.IsTrue(query.Strategy.Equals(EvaluationStrategy.Default));
var subquery = query.SubQuery;
Assert.IsTrue(query.JoinOnAttributes.Trim() == "t1.ha=t2.ho");
Assert.IsTrue(query.HasSubquery);
Assert.IsTrue(subquery.HasSubquery == false);
Assert.IsTrue(subquery.TableName.Trim() == "Data2");
}
public T Select <T>(Expression <Func <T, object> > columns, object id) where T : class
{
var entityDef = GetEntityDefintion(typeof(T));
var pkAttributeName = entityDef.Attributes.FirstOrDefault(o => o.IsPrimaryKey == true)?.Name;
if (pkAttributeName == null)
{
pkAttributeName = entityDef.Attributes.First().Name;
}
var sql = new SqlSelectQuery <T>();
sql = sql.Select(columns)
.Where($"{pkAttributeName} == '{id.ToString()}'");
var strSql = (sql as ISqlQuery).ToString();
return(null);
}
private void GenereateSelectFields(SqlGenerator sql, SqlSelectQuery query)
{
if (query.Fields == null)
{
// Select all fields from
GenerateCommaSep(sql, query.From.Columns, true, (f, _) => {
GenerateField(sql, new SqlColumnExpression(f.Type, f.Name, query.From));
});
return;
}
GenerateCommaSep(sql, query.Fields, true, (field, _) => {
GenerateField(sql, field.Expression);
if (!String.IsNullOrEmpty(field.Alias))
{
sql.Add($" AS {field.Alias}");
}
});
}
public string GenerateSelect(SqlSelectQuery query)
{
SqlGenerator sql = new SqlGenerator();
// If there is only 1 const selection
if (query.From is SqlConstantSource constSource)
{
GenerateSource(sql, constSource);
return(sql.Generate());
}
sql.Add("SELECT");
sql.NewLineIndent();
GenereateSelectFields(sql, query);
sql.NewLineUnIndent();
sql.Add("FROM");
sql.NewLineIndent();
GenerateSource(sql, query.From);
sql.NewLineUnIndent();
if (query.Where != null)
{
sql.Add("WHERE");
sql.NewLineIndent();
GenerateField(sql, query.Where);
sql.NewLineUnIndent();
}
if (query.GroupBy?.Length > 0)
{
sql.Add("GROUP BY");
GenerateCommaSep(sql, query.GroupBy, false, (f, _) => GenerateField(sql, f));
}
if (query.OrderBy?.Length > 0)
{
sql.Add("ORDER BY");
GenerateCommaSep(sql, query.OrderBy, false, (f, _) => {
GenerateField(sql, f.By);
sql.Add(f.Ascending ? "ASC" : "DESC");
});
}
return(sql.Generate());
}
// s =>
public QuerySourceType(SqlSelectQuery source, Type type, SourceType sourceType)
{
this.Source = source;
this.Type = type;
this.SourceType = sourceType;
}
public SelectQueryHandler(SqlSelectQuery squery, IStandardDatabase underlineDatabase)
{
_query = squery;
this.underlineDatabase = underlineDatabase;
}
private DataTable ExecuteMonteCarloSampling(string samplingResultTable, string samplingTargetTable, int samplingRuns, SqlSelectQuery query)
{
//:Todo not used
Random random = new Random();
var sql = String.Format("select count(*) from {0}", samplingTargetTable);
var result = underlineDatabase.ExecuteSqlWithResult(sql);
if (result.Rows.Count != 1)
{
throw new Exception("");
}
var noOfWorlds = (int)result.Rows[0][0];
DataTable allSample = new DataTable();
for (int i = 1; i <= samplingRuns; i++)
{
var worldNoSelected = random.Next(1, noOfWorlds);
var selectStringWorld = string.Format("SELECT * FROM {0} WHERE worldNo = {1}", samplingTargetTable, worldNoSelected);
var aSample = underlineDatabase.ExecuteSqlWithResult(selectStringWorld);
if (i == 1)
{
allSample = aSample.Clone();
}
allSample = addOneTableToAnother(allSample, aSample, random);
}
DataTable frenquencyResult = computeFrequencyResult(allSample, samplingResultTable, query.Attributes, samplingRuns, query);
return(frenquencyResult);
}
/// <summary>
/// save allSample table back to database in order to be able to use group by keyword.
/// the temporary table called samplingResultTable is created for this purposes
/// </summary>
/// <param name="allSample"></param>
/// <param name="samplingResultTable"></param>
/// <param name="query"></param>
/// <returns></returns>
private DataTable computeFrequencyResult(DataTable allSample, string samplingResultTable, string selectedFields, int noOfSamplingRuns, SqlSelectQuery query)
{
WriteTableIntoDatabase(samplingResultTable, allSample);
var groupingSql = string.Format("SELECT {0},(COUNT(*)/{2}) as p FROM {1} GROUP BY {0}", selectedFields, samplingResultTable, noOfSamplingRuns);
return(underlineDatabase.ExecuteSqlWithResult(groupingSql));
}
public static void GetParameterValues_NoConditions_ReturnsEmptyCollection()
{
SqlContext context = CreateMockContext();
SqlQuery <TestEntity> query = new SqlSelectQuery <TestEntity>(context);
(SqlColumn, string, object)[] parameterValues = query.GetParameterValues(context).ToArray();
