static QueryTemplate SqlQueryNonTimed(this SqlExpr sqlExpr, bool arrayResults, string connName, out string[] inputs, out string[] outputs)
{
var allColumns = sqlExpr.resNdx.Keys;
string[] colsNames;
IEnumerable <AliasExpr> colsExprs;
Expr[] orderBy;
int i;
if (sqlExpr.resNdx.TryGetValue(nameof(INS_OUTS_SEPARATOR), out i))
{
inputs = allColumns.Take(i).ToArray();
outputs = allColumns.Skip(i + 1).ToArray();
colsNames = inputs.Union(outputs).ToArray();
var rs = sqlExpr.results;
colsExprs = rs.Take(i).Union(rs.Skip(i + 1));
orderBy = rs.Take(i).Select(e => e.right).ToArray();
}
else
{
inputs = new string[0];
outputs = allColumns.ToArray();
colsNames = allColumns.ToArray();
colsExprs = sqlExpr.results;
orderBy = null;
}
var expr = sqlExpr.CreateQueryExpr(new ReferenceExpr("1=1{0}"), null, orderBy, null, colsNames);
var lstColsExprs = colsExprs
//.Where(a => a.alias != nameof(START_TIME) && a.alias != nameof(END_TIME))
.Select(ae => ae.expr.ToString()).ToArray();
return(QueryTemplate.Get(colsNames, lstColsExprs, null, expr.ToString(), sqlExpr, arrayResults, connName));
}
public void ErrorTest1()
{
Assert.ThrowsException <ArgumentException>(() =>
{
Select <T>(t => t.OtherCol == SqlExpr.Eval <int?>("errortest", 1, "引数5でエラーになることを確認", false, 3.5));
});
}
public void Place2()
{
SqlExpr e;
//
// Expr演算子の結合の優先順位が適用前後で変わらないことを確認する
//
e = new SqlExpr("@PH");
e.Place("PH", "~a");
Assert.That(e.ToString(), Is.EqualTo("~a"));
e = new SqlExpr("@PH");
e.Place("PH", "a+b");
Assert.That(e.ToString(), Is.EqualTo("a+b"));
e = new SqlExpr("~@PH");
e.Place("PH", "~a");
Assert.That(e.ToString(), Is.EqualTo("~~a"));
e = new SqlExpr("~@PH");
e.Place("PH", "a+b");
Assert.That(e.ToString(), Is.EqualTo("~(a+b)"));
e = new SqlExpr("@PH || 'abc'");
e.Place("PH", "~a");
Assert.That(e.ToString(), Is.EqualTo("~a||'abc'"));
e = new SqlExpr("@PH || 'abc'");
e.Place("PH", "a+b");
Assert.That(e.ToString(), Is.EqualTo("(a+b)||'abc'"));
}
public void Tutorial()
{
int?val1 = 100; // (bound to @IntCol)
Select <T>(t => t.IntCol == val1);
Select <T>(t => t.IntCol != val1);
Select <T>(t => t.IntCol < val1);
Select <T>(t => t.IntCol > val1);
Select <T>(t => t.IntCol <= val1);
Select <T>(t => t.IntCol >= val1);
int?val2 = null;
Select <T>(t => t.IntCol == val2);
Select <T>(t => t.IntCol != val2);
Select <T>(t => t.IntCol == t.OtherCol);
string[] inValues = { "111", "222", "333" }; // (bound to @TextCol)
Select <T>(t => t.TextCol == SqlExpr.In(inValues));
Select <T>(t => t.TextCol != SqlExpr.In(inValues));
Select <T>(t => t.IntCol != SqlExpr.In(new[] { 1, 2, 3 }));
string likeValue = "%test%"; // (bound to @TextCol)
Select <T>(t => t.TextCol == SqlExpr.Like(likeValue));
Select <T>(t => t.TextCol != SqlExpr.Like(likeValue));
int b1 = 1; // (bound to @IntCol)
int b2 = 99; // (bound to @P01)
Select <T>(t => t.IntCol == SqlExpr.Between(b1, b2));
Select <T>(t => t.TextCol == "111" && t.IntCol < 200);
Select <T>(t => t.TextCol == "111" || t.IntCol < 200);
Select <T>(t => !(t.TextCol == "111" || t.IntCol < 200));
string text1 = "111";
string text2 = null;
Select <T>(t => text1 == null || t.TextCol == text1);
Select <T>(t => text1 != null && t.TextCol == text1);
Select <T>(t => text2 == null || t.TextCol == text2);
Select <T>(t => text2 != null && t.TextCol == text2);
Select <T>(t => t.TextCol == SqlExpr.In <string>("select text from otherTable where..."));
Select <T>(t => t.IntCol == SqlExpr.In <int?>("select text from otherTable where..."));
Select <T>(t => SqlExpr.Eval("exists(select * from otherTable where...)"));
var idText = "userIdText";
var pwText = "passswordText";
var salt = "hashsalt";
Select <T>(t => SqlExpr.Eval("id=", idText, " AND pw=CRYPT(", pwText, ", pw)"));
Select <T>(t => t.TextCol == SqlExpr.Eval <string>("CRYPT(", pwText, ", pw)"));
Select <T>(t => t.TextCol == SqlExpr.Eval <string>("CRYPT(", pwText, ",", salt, ")"));
}
public void RangeTest()
{
var range = SqlExpr.Range <DateTime>();
var comp = range.Compile();
Assert.IsTrue(comp(new DateTime(2019, 01, 26), new DateTime(2019, 01, 30), new DateTime(2019, 01, 28)));
Assert.IsFalse(comp(new DateTime(2019, 01, 26), new DateTime(2019, 01, 30), new DateTime(2019, 01, 31)));
Assert.IsTrue(comp(new DateTime(2019, 01, 26), null, new DateTime(2019, 01, 31)));
}
protected QueryTemplate(string[] colsNames, string[] colsExprs, string[] varsNames, string queryTemplateText, SqlExpr SrcSqlExpr, bool arrayResults, string connName)
{
this.colsNames = colsNames;
this.colsExprs = colsExprs;
this.varsNames = varsNames;
this.queryTemplateText = queryTemplateText;
this.SrcSqlExpr = SrcSqlExpr;
this.arrayResults = arrayResults;
this.connName = connName;
}
public void IsDefault()
{
SqlExpr e;
e = new SqlExpr("Default");
Assert.That(e.IsDefault, Is.True);
e = new SqlExpr("CAST(a As NEWTYPE)");
Assert.That(e.IsDefault, Is.False);
}
public void IsPlaceHolderOnly()
{
SqlExpr e;
e = new SqlExpr("@PH");
Assert.That(e.IsPlaceHolderOnly, Is.True);
e = new SqlExpr("Cast(a as NEWTYPE)");
Assert.That(e.IsPlaceHolderOnly, Is.False);
}
public new void ToString()
{
SqlExpr e;
e = new SqlExpr("x / 8 ");
e.ToString();
Assert.That(e.ToString(), Is.EqualTo("x/8"));
e = new SqlExpr("Default");
e.ToString();
Assert.That(e.ToString(), Is.EqualTo("DEFAULT"));
}
public void Clone()
{
SqlExpr e;
SqlExpr e1;
e = new SqlExpr("@PH");
e1 = e.Clone();
Assert.That(e.ToString(), Is.EqualTo("@PH"));
Assert.That(e1.ToString(), Is.EqualTo("@PH"));
e1.Place("PH", "x");
Assert.That(e.ToString(), Is.EqualTo("@PH"));
Assert.That(e1.ToString(), Is.EqualTo("x"));
}
public void Empty()
{
SqlExpr e = new SqlExpr();
Assert.That(e.Clone().ToString(), Is.EqualTo(""));
Assert.That(e.GetAllPlaceHolders(), Is.EqualTo(new string[] { }));
Assert.That(e.HasUnplacedHolder("T"), Is.False);
Assert.That(e.HasUnplacedHolders(), Is.False);
Assert.That(e.IsEmpty, Is.True);
Assert.That(e.IsPlaceHolderOnly, Is.False);
e.Place("PH", "a");
Assert.That(e.ToString(), Is.EqualTo(""));
}
static SqlExpr SqlFromTmpl(SqlExpr tmpl, SqlSectionExpr newSelect)
{
if (tmpl[SqlSectionExpr.Kind.Select] == newSelect)
{
return(tmpl);
}
return(SqlFromSections(
newSelect,
tmpl[SqlSectionExpr.Kind.From],
tmpl[SqlSectionExpr.Kind.Where],
tmpl[SqlSectionExpr.Kind.OrderBy],
tmpl[SqlSectionExpr.Kind.GroupBy]
));
}
public void IsLiteral()
{
SqlExpr e;
e = new SqlExpr("default");
Assert.That(e.IsLiteral, Is.False);
e = new SqlExpr("Cast(1 as NEWTYPE)");
Assert.That(e.IsLiteral, Is.False);
e = new SqlExpr("'abc'");
Assert.That(e.IsLiteral, Is.True);
e = new SqlExpr("1192");
Assert.That(e.IsLiteral, Is.True);
}
public void Place()
{
SqlExpr e;
e = new SqlExpr("DEFAULT");
e.Place("PH", "a");
Assert.That(e.ToString(), Is.EqualTo("DEFAULT"));
e = new SqlExpr("@PH");
Assert.Throws <CannotBuildASTException>(() => { e.Place("PH", "a=1"); });
e = new SqlExpr("@PH");
e.Place("PH", "a");
Assert.That(e.ToString(), Is.EqualTo("a"));
e = new SqlExpr("cast(@PH as VARCHAR2)");
e.Place("PH", "x");
Assert.That(e.ToString(), Is.EqualTo("CAST(x AS VARCHAR2)"));
}
public void SimpleSelectFuncExpr()
{
var min = new DateTime(2019, 01, 26);
var max = new DateTime(2019, 01, 30);
var r = Sql
.From(new SqlTable <Cliente>())
.Select(x => new
{
fecha = SqlExpr.Range <DateTime>().Invoke(min, max, x.Fecha)
});
var actual = r.ToSql(SqlText.ParamMode.EntityFramework).Sql;
var expected = @"
SELECT
((""x"".""Fecha"" >= @min) AND (""x"".""Fecha"" <= @max)) AS ""fecha""
FROM ""Cliente"" ""x""
";
AssertSql.AreEqual(expected, actual);
}
public void HasUnplacedHolders()
{
SqlExpr e;
e = new SqlExpr("@PH");
Assert.That(e.HasUnplacedHolders(), Is.True);
e.Place("PH", "'abc'");
Assert.That(e.HasUnplacedHolders(), Is.False);
e = new SqlExpr("@PH1 + @PH2");
e.Place("PH1", "+100");
Assert.That(e.HasUnplacedHolders(), Is.True);
e.Place("PH2", "-9");
Assert.That(e.HasUnplacedHolders(), Is.False);
e = new SqlExpr("@PH || @PH");
e.Place("PH", "'abc'");
Assert.That(e.HasUnplacedHolders(), Is.False);
e.Place("PH", "'abc'");
Assert.That(e.HasUnplacedHolders(), Is.False);
}
public void SqlExprTest()
{
QueryBuilder.DefaultInstance = new QueryBuilder.SQLite();
using (IDbConnection connection = GetSqliteDbConnection())
{
var createTableSql = DDLAttribute.GenerateCreateSQL <Member>();
connection.Execute(createTableSql);
var list1 = connection.Select <Member>(r =>
(r.Name == ToSql.In(new[] { "A", "B" }) ||
r.Name != SqlExpr.Like("%TEST%") ||
r.Name == SqlExpr.Between("1", "5") ||
DateTime.Now < r.CreatedAt));
var list2 = connection.Select <Member>(r =>
(r.Id == SqlExpr.In <int>("SELECT MAX(id) FROM Members") ||
SqlExpr.Eval("EXISTS(SELECT * FROM Members m2 WHERE id=sqlite_version())") ||
r.Id == SqlExpr.Eval <int>("MAX(", 1, ",", 2, ",", 3, ")")
));
}
}
public void GetAllPlaceHolders()
{
SqlExpr e;
e = new SqlExpr("@PH");
Assert.That(e.GetAllPlaceHolders()
, Is.EqualTo(new Dictionary <string, string> {
{ "PH", "" }
}));
e.Place("PH", "'abc'");
Assert.That(e.GetAllPlaceHolders()
, Is.EqualTo(new Dictionary <string, string> {
{ "PH", "'abc'" }
}));
e = new SqlExpr("@PH1 + @PH2");
e.Place("PH1", "+100");
Assert.That(e.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string>
{
{ "PH1", "+100" }, { "PH2", "" }
}));
e.Place("PH2", "-9");
Assert.That(e.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string>
{
{ "PH1", "+100" }, { "PH2", "-9" }
}));
e = new SqlExpr("@PH || @PH");
e.Place("PH", "'abc'");
Assert.That(e.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string>
{
{ "PH", "'abc'" }
}));
e.Place("PH", "'abc'");
Assert.That(e.GetAllPlaceHolders(), Is.EqualTo(new Dictionary <string, string>
{
{ "PH", "'abc'" }
}));
}
public static QueryTemplate Get(string[] colsNames, string[] colsExprs, string[] varsNames, string queryTemplateText, SqlExpr SrcSqlExpr, bool arrayResults, string connName)
{
QueryTemplate res;
lock (registry)
{
if (!registry.TryGetValue(queryTemplateText, out res))
{
res = new QueryTemplate(colsNames, colsExprs, varsNames, queryTemplateText, SrcSqlExpr, arrayResults, connName);
registry.Add(queryTemplateText, res);
}
}
return(res);
}
protected IQueryable <DbClassifier> BuildQuery(DbContext db, ClassifierType type, ClassifierSearchRequest request)
{
IQueryable <DbClassifier> query = null;
if (type.HierarchyType == HierarchyType.Groups)
{
if (request.GroupUid != null)
{
if (request.Depth == null || request.Depth == "0") // todo: use constant
{
query = from trees in db.GetTable <DbClassifierTree>()
join childrenGroups in db.GetTable <DbClassifierGroup>() on trees.Uid equals childrenGroups.TreeUid
join links in db.GetTable <DbClassifierLink>() on childrenGroups.Uid equals links.GroupUid
join c in db.GetTable <DbClassifier>() on links.ItemUid equals c.Uid
where trees.TypeUid == type.Uid &&
trees.Uid == request.TreeUid &&
childrenGroups.Uid == request.GroupUid
select c;
}
else
{
query = from trees in db.GetTable <DbClassifierTree>()
join parentGroups in db.GetTable <DbClassifierGroup>() on trees.Uid equals parentGroups.TreeUid
join closures in db.GetTable <DbClassifierClosure>() on parentGroups.Uid equals closures.ParentUid
join childrenGroups in db.GetTable <DbClassifierGroup>() on closures.ChildUid equals childrenGroups
.Uid
join links in db.GetTable <DbClassifierLink>() on childrenGroups.Uid equals links.GroupUid
join c in db.GetTable <DbClassifier>() on links.ItemUid equals c.Uid
where trees.TypeUid == type.Uid &&
trees.Uid == request.TreeUid &&
parentGroups.Uid == request.GroupUid
select c;
}
}
}
else if (type.HierarchyType == HierarchyType.Items)
{
if (request.GroupUid != null)
{
if (request.Depth == null || request.Depth == "0") // todo: use enum or constant
{
query = from parent in db.GetTable <DbClassifier>()
join @class in db.GetTable <DbClassifier>() on parent.Uid equals @class.ParentUid
where parent.TypeUid == type.Uid && parent.Uid == request.GroupUid
select @class;
}
else
{
query = from parent in db.GetTable <DbClassifier>()
join closures in db.GetTable <DbClassifierClosure>() on parent.Uid equals closures.ParentUid
join @class in db.GetTable <DbClassifier>() on closures.ChildUid equals @class.Uid
where parent.TypeUid == type.Uid && parent.Uid == request.GroupUid && closures.Level > 0
select @class;
}
}
}
if (query == null)
{
query = from c in db.GetTable <DbClassifier>()
where c.TypeUid == type.Uid
select c;
}
if (request.Uid != null)
{
query = query.Where(x => x.Uid == request.Uid);
}
if (request.Uids != null)
{
query = query.Where(x => request.Uids.Contains(x.Uid));
}
if (request.Code != null)
{
query = query.Where(x => x.Code == request.Code);
}
if (request.SearchTerm != null)
{
query = query.Where(x => SqlExpr.ILike(x.Name, "%" + request.SearchTerm + "%"));
// query = query.Where(x => Sql.Like(x.Name, "%" + request.SearchTerm + "%"));
// query = query.Where(x => x.Name.Contains(request.SearchTerm));
}
return(query);
}
public static void ShouldBe(this SqlExpr sqlExpr, string expected)
{
sqlExpr.Should().NotBeNull();
sqlExpr.ToSqlCode().Should().Be(expected);
}
public static SqlExpr AND(this SqlExpr exp1, SqlExpr exp2)
{
return SqlExpr.OPR(exp1, "AND", exp2);
}
public static SqlExpr AddParameter(this string columnName, SqlExpr value)
{
return SqlExpr.AddParameter(columnName, columnName);
}
public static SqlExpr SUBSTRING(this SqlExpr expr, SqlExpr start, SqlExpr length)
{
return SqlExpr.Func("SUBSTRING", expr, start, length);
}
public static SqlExpr OR(this SqlExpr exp1, SqlExpr exp2)
{
return SqlExpr.OPR(exp1, "OR", exp2);
}
public void Tutorial()
{
QueryBuilder.DefaultInstance = new QueryBuilder.SQLite();
using (IDbConnection connection = GetSqliteDbConnection())
{
// optional : create table -----------
var createTableSql = DDLAttribute.GenerateCreateSQL <Member>();
connection.Execute(createTableSql);
// -> create table Members
// (
// "Id" INTEGER, -- identity
// "Name",
// Phone_No,
// "CreatedAt",
// "UpdatedAt",
// primary key( "Id")
// )
var tableInfoTsv = DDLAttribute.GenerateTableDefTSV <Member>();
Trace.WriteLine(tableInfoTsv);
// select 1 record -------------------
Member select1 = connection.Select(
() => new Member {
Id = 5
});
Member select2 = connection.Select(
() => new Member {
Id = 6
},
r => new { r.Id, r.Name });
// (for update etc.)
Member selectForUpdate = connection.Select(
() => new Member {
Id = 7
},
otherClauses: "--FOR UPDATE"); // SQLite doesn't support "FOR UPDATE", so commented out
var targetMember = new Member {
Id = 8, Name = "LockTest"
};
var lockedMember = connection.Select(
() => targetMember, // where [Key] or [ConcurrencyCheck] is set
otherClauses: "--FOR UPDATE"); // SQLite doesn't support "FOR UPDATE", so commented out
// (with SqlExpr)
Member select3 = connection.Select(
() => new Member {
Id = SqlExpr.Eval <int>("(SELECT MAX(id) FROM Members)")
}
);
// select records --------------------
IReadOnlyList <Member> list1 = connection.Select <Member>();
IReadOnlyList <Member> list2 = connection.Select <Member>(
r => r.Name == "TEST");
IReadOnlyList <Member> list3 = connection.Select <Member>(
r => r.Name != "TEST",
r => new { r.Id, r.Name });
IReadOnlyList <Member> list4 = connection.Select <Member>(
r => r.Tel != null,
$"ORDER BY {nameof(Member.Name)} LIMIT 5 OFFSET 10");
IReadOnlyList <Member> list5 = connection.Select <Member>(
r => r.Tel != null,
r => new { r.Id, r.Name },
$"ORDER BY {nameof(Member.Name)} LIMIT 5 OFFSET 10");
// count -----------------------------
ulong count1 = connection.Count <Member>();
ulong count2 = connection.Count <Member>(
r => (r.Id >= 3 && r.Id <= 9));
// insert ----------------------------
var rec1 = new Member {
Name = "InsertTest", Tel = "177"
};
int insert1 = connection.Insert(rec1);
var rec2 = new Member {
Name = "ParticularColumnOnly1", CreatedAt = null
};
int insert2 = connection.Insert(rec2,
r => new { r.Name, r.CreatedAt });
var rec3 = new Member {
Name = "IdentityTest", Tel = "7777"
};
int insert3 = connection.InsertAndRetrieveId(rec3);
Trace.WriteLine("insertedID=" + rec3.Id); // -> 3
int insertX = connection.Insert(
() => new Member {
Id = 888, Name = "ParticularColumnOnly2"
});
// (with SqlExpr)
string nameExample = "SqlExpr.Eval Usage Example";
int insertX2 = connection.Insert(
() => new Member {
Name = SqlExpr.Eval <string>("Upper(", nameExample, ")")
});
// insert records -------------------
int insertMulti = connection.InsertRows(new[] {
new Member {
Name = "MultiInsert1", Tel = null
},
new Member {
Name = "MultiInsert2", Tel = "999-999-9999"
},
new Member {
Name = "MultiInsert3", Tel = "88-8888-8888"
},
});
// update record ---------------------
rec1 = connection.Select(() => new Member {
Id = 1
});
rec1.Name = "Updatetest";
int update1 = connection.Update(rec1);
rec2.Id = 2;
rec2.Tel = "6666-66-6666";
int update2 = connection.Update(rec2, r => new { r.Tel });
int update3 = connection.Update(
() => new Member {
Name = "updateName"
},
r => r.Tel == "55555-5-5555");
// (with SqlExpr)
int update4 = connection.Update(
() => new Member {
Name = SqlExpr.Eval <String>("SUBSTR(name,", 1, ",", 4, ")")
},
r => r.Tel == "55555-5-5555");
// delete record
var delRec = new Member {
Id = 999, Name = "XXXX"
};
int delete1 = connection.Delete(delRec);
int delete2 = connection.Delete <Member>(
r => r.Name == null);
// truncate
connection.Truncate <Member>();
}
}
static QueryTemplate SqlQueryTimed(SqlExpr sqlExpr, DbFuncType queryKind, bool arrayResults, string connName)
{
var rn = sqlExpr.resNdx;
var rf = sqlExpr.resFields;
var rs = sqlExpr.results;
var idValueExpr = rf[0]; // first column of query must be an ID of subject
var idAlias = rs[0];
int iStartTimeField = rn[refStartTime.name];
var startTime = QueryTimeInfo.Get(rf[iStartTimeField]);
System.Diagnostics.Trace.Assert(startTime.timeExpr != null, "START_TIME column is not found");
QueryTimeInfo endTime;
{
int i;
endTime = QueryTimeInfo.Get(rn.TryGetValue(nameof(END_TIME), out i) ? rf[i] : null);
}
var orderBy = new Expr[] { idAlias.right, refStartTime };
Expr expr;
if (endTime.timeExpr != null && queryKind != DbFuncType.GetSchemaOnly)
{ // value has two timestamps - START_TIME and END_TIME
Expr cond;
switch (queryKind)
{
case DbFuncType.TimeInterval:
cond = Cond_TimeInterval(startTime, endTime, sMinTime); break;
case DbFuncType.TimeRawInterval:
cond = Cond_TimeInterval(startTime, endTime, sATime); break;
case DbFuncType.TimeSlice:
cond = Cond_TimeSlice(startTime, endTime, sMinTime); break;
default:
throw new NotSupportedException($"Unsupported DbFuncType value: {queryKind.ToString()}");
}
cond = new SequenceExpr(cond, new ReferenceExpr("{0}"));
expr = sqlExpr.CreateQueryExpr(cond, null, orderBy);
}
else
{ // value has only one timestamp - START_TIME
Expr cond_aggr = null, cond_simp = null;
switch (queryKind)
{
case DbFuncType.GetSchemaOnly:
break;
case DbFuncType.TimeRawInterval:
cond_simp = Cond_TimeIntervalHalfOpen(startTime, sATime, sBTime); break;
case DbFuncType.TimeInterval:
cond_aggr = Cond_TimeSlice(startTime, sMinTime, sATime);
cond_simp = Cond_TimeIntervalHalfOpen(startTime, sATime, sBTime); break;
case DbFuncType.TimeSlice:
cond_aggr = Cond_TimeSlice(startTime, sMinTime, sAtTime); break;
default:
throw new NotSupportedException($"Unsupported DbFuncType value: {queryKind.ToString()}");
}
if (cond_aggr != null)
{
var exprKeep = new CallExpr("KEEP", new SequenceExpr(new ReferenceExpr("DENSE_RANK LAST ORDER BY"), startTime.valueExpr));
cond_aggr = new SequenceExpr(cond_aggr, new ReferenceExpr("{0}"));
expr = sqlExpr.CreateQueryExpr(cond_aggr, idAlias, orderBy,
src =>
{
Expr res;
if (src.expr.Traverse(e => e)
.OfType <CallExpr>()
.Select(ce => ce.funcName.ToUpperInvariant())
.Where(fn => fn == "MIN" || fn == "MAX" || fn == "SUM" || fn == "AVG" || fn == "COUNT").Any())
{
res = src.expr;
}
else
{
res = new CallExpr("MAX", src.expr);
}
if (src.alias != nameof(START_TIME))
{
res = new SequenceExpr(res, exprKeep);
}
return(new AliasExpr(res, src.right));
});
}
else
{
expr = null;
}
if (cond_simp != null)
{
cond_simp = new SequenceExpr(cond_simp, new ReferenceExpr("{0}"));
var expr2 = sqlExpr.CreateQueryExpr(cond_simp, null, orderBy);
if (expr == null)
{
expr = expr2;
}
else
{
//expr = new SequenceExpr(expr, new ReferenceExpr("UNION ALL"), expr2);
var sqlA = ((MultiExpr)expr).args.Cast <SqlSectionExpr>();
//var order = sqlA.FirstOrDefault(s => s.kind == SqlSectionExpr.Kind.OrderBy);
var bodyA = sqlA.Where(s => s.kind != SqlSectionExpr.Kind.OrderBy);
//var sqlB = ((MultiExpr)expr2).args.Cast<SqlSectionExpr>();
//var orderB = sqlB.FirstOrDefault(s => s.kind == SqlSectionExpr.Kind.OrderBy);
//var bodyB = sqlB.Where(s => s.kind != SqlSectionExpr.Kind.OrderBy);
var lst = new List <Expr>();
lst.AddRange(bodyA);
lst.Add(new ReferenceExpr("UNION ALL"));
lst.Add(expr2);
expr = new SequenceExpr(lst);
}
}
else if (expr == null)
{
expr = sqlExpr.CreateQueryExpr();
}
}
string[] qryVars = null;
switch (queryKind)
{
case DbFuncType.TimeRawInterval:
qryVars = new string[] { sMinTime, sATime, sBTime }; // sMinTime here is dummy (not used really and specified only for unification)
break;
case DbFuncType.TimeInterval:
qryVars = new string[] { sMinTime, sATime, sBTime };
break;
case DbFuncType.TimeSlice:
qryVars = new string[] { sMinTime, sAtTime };
break;
case DbFuncType.GetSchemaOnly:
qryVars = new string[0];
break;
}
return(QueryTemplate.Get(
rs.Select(x => x.alias).ToArray(),
rs.Select(x => x.expr.ToString()).ToArray(),
qryVars, expr.ToString(),
sqlExpr, arrayResults, connName));
}
static QueryTemplate SqlCommandInsert(this SqlExpr sql, string connName, string defaultLocation, out string[] outputs)
{
var secFrom = sql[SqlSectionExpr.Kind.From];
outputs = null;
if (secFrom == null || secFrom.args.Count > 1 || !(secFrom.args[0] is ReferenceExpr reTable))
{
// don't generate INSERT for multiple tables or for none
return(null);
}
if (
sql[SqlSectionExpr.Kind.GroupBy] != null ||
sql[SqlSectionExpr.Kind.OrderBy] != null ||
sql[SqlSectionExpr.Kind.Where] != null
)
{
// don't generate INSERT from complex query definition
return(null);
}
var colsNames = new List <string>();
var colsExprs = new List <string>();
var sb = new StringBuilder($"INSERT INTO {secFrom.args[0]} (");
{
bool firstCol = true;
foreach (var colExpr in sql[SqlSectionExpr.Kind.Select].args)
{
if (!(colExpr is AliasExpr ae))
{
return(null);
}
if (firstCol)
{
firstCol = false;
}
else
{
sb.Append(',');
}
if (ae.expr is ReferenceExpr re)
{
if (string.Compare(ae.alias, nameof(INS_OUTS_SEPARATOR)) == 0)
{
continue;
}
colsNames.Add(ae.alias);
colsExprs.Add(re.name);
sb.Append(re.name);
}
else
{
return(null);
}
}
sb.Append(") VALUES ");
}
outputs = new string[] { $"{reTable.name}_OBJ_{defaultLocation}_RowsInserted" };
var qt = QueryTemplate.Get(colsNames.ToArray(), colsExprs.ToArray(), null, sb.ToString(), sql, false, connName);
return(qt);
}
public Locator(SqlExpr expression)
{
this.where = expression.ToString();
}
internal SqlExpr PostProc(SqlFuncPreprocessingCtx c, SqlExpr sql)
{
c.tblAttrs.TryGetValue(Attr.Tbl.AbstractTable, out var objAbstractTable);
c.tblAttrs.TryGetValue(Attr.Tbl.LookupTableTemplate, out var objLookupTableTemplate);
var modFunc = c.tblAttrs.TryGetValue(Attr.Tbl.Substance, out var objSubstance)
? ModifyFieldExpr(c, objSubstance.ToString())
: (objAbstractTable == null && objLookupTableTemplate == null)
? ModifyFieldExpr(c, null)
: (expr, attrs) => expr;
SqlSectionExpr select = sql[SqlSectionExpr.Kind.Select];
#region Postprocess SELECT expression: insert inherited fields if needed
if (c.tblAttrs.TryGetValue(Attr.Tbl._columns_attrs, out var objInnerAttrs))
{
var innerAttrs = (IList <Dictionary <Attr.Col, object> >)objInnerAttrs;
var args = select.args;
//bool changed = false;
int n = innerAttrs.Count;
var newInner = new List <Dictionary <Attr.Col, object> >(n);
var fields = new List <Expr>(n);
for (int i = 0; i < n; i++)
{
var attrs = innerAttrs[i];
#region 'Inherits' attribute processing
if (attrs.TryGet(Attr.Col.Inherits, out var objInherits))
{ // inherit lot of fields from abstract tables
var lst = objInherits as IList;
if (lst == null)
{
lst = new object[] { objInherits }
}
;
foreach (var aT in lst)
{
if (!abstracts.TryGetValue(aT.ToString(), out var abstr))
{
throw new Generator.Exception($"No one AbstractTable='{aT}' found");
}
var inheritedFields = abstr.sql[SqlSectionExpr.Kind.Select].args;
// inherit fields
//changed = true;
if (abstr.attrs.TryGetValue(Attr.Tbl._columns_attrs, out var objInners))
{
var inners = (Dictionary <Attr.Col, object>[])objInners;
int k = inheritedFields.Count;
for (int j = 0; j < k; j++)
{
fields.Add(modFunc(inheritedFields[j], inners[j]));
}
// inherit fields attributes
newInner.AddRange(inners);
}
else
{
fields.AddRange(inheritedFields.Select(s => modFunc(s, null)));
// no attributes to inherit
for (int j = inheritedFields.Count - 1; j >= 0; j--)
{
newInner.Add(null);
}
}
}
}
#endregion
if (i < args.Count)
{
// add field
fields.Add(modFunc(args[i], attrs));
}
newInner.Add(attrs);
}
//if (changed)
{
// inherited fields added, create updated SELECT expression
select = new SqlSectionExpr(SqlSectionExpr.Kind.Select, fields);
c.tblAttrs[Attr.Tbl._columns_attrs] = newInner.ToArray();
}
}
else if (objSubstance != null && objAbstractTable == null && objLookupTableTemplate == null)
{
select = new SqlSectionExpr(SqlSectionExpr.Kind.Select, select.args.Select(s => modFunc(s, null)).ToList());
}
#endregion
var newSql = SqlFromTmpl(sql, select);
if (objAbstractTable != null)
{ // It is "abstract table", add to abstracts dictionary
var abstractTable = objAbstractTable.ToString();
abstracts.Add(abstractTable, new SqlInfo()
{
sql = newSql, attrs = c.tblAttrs
});
return(null);
}
if (objLookupTableTemplate != null)
{
var ltt = objLookupTableTemplate.ToString();
templates.Add(ltt, new SqlInfo()
{
sql = newSql, attrs = c.tblAttrs
});
return(null);
}
return(newSql);
}
public SqlExpr PostProc(SqlExpr sql) => ldr.PostProc(this, sql);
public static void ShouldEqual(this string expected, SqlExpr sqlExpr)
{
var sqlExprCode = $"{sqlExpr}";
expected.MergeToCode().TrimCode().ToExpectedObject().ShouldEqual(sqlExprCode.MergeToCode().TrimCode());
}
