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