public override string FromKeyword() { if (stmt is selectstatement) { /* access wants its joins wrapped - there is a closing-bracket logic after each same level join */ selectstatement st = stmt as selectstatement; if (st.Joins != null) { return("FROM " + new String('(', st.Joins.Count)); } } return("FROM"); }
public selectfield(selectfunctiondef func, expression expression, string rowsource_consumeralias, Ioperand literalconst, string fieldname, string asname, selectstatement subqry, sortorder order) { /* [rzamponi 20100112] FxCop CA1805:DoNotInitializeUnnecessarily */ //if (func == selectfunctiondef.none) Function = null; else Function = new selectfunction(func); if (func != selectfunctiondef.none) { Function = new selectfunction(func); } Expression = expression; RowsourceConsumerAlias = rowsource_consumeralias; literal = literalconst; Fieldname = fieldname; AsName = asname; subquery = subqry; if (order == sortorder.so_asc) { Ascending = true; } else if (order == sortorder.so_desc) { Descending = true; } }
public joinstatement(joinflavor type, string table, string alias, selectstatement subqry, expressionlist expr) { switch (type) { case joinflavor.inner: Inner = true; break; case joinflavor.left: Left = true; break; case joinflavor.outer: Outer = true; break; default: throw new NotSupportedException(type.ToString()); } Tablename = table; subquery = subqry; expressions = expr; ConsumerAlias = alias; }
public static void adhoc_poormans_fulltext_ref(selectstatement sel, string qstoken, ref bool bneedand, expressionlist custom_or_clause, params string[] fieldlist) { adhoc_poormans_fulltext(sel.Where.expressions, qstoken, ref bneedand, (expressionlist)null, fieldlist); }
public virtual bool Compile(Isqlcompiler _parent) { parent = _parent; /* 1. special case with toplevel select statement: if there is an ordering defined in the field list, * then we append those ordering hints to the order by clause even if there is none */ if (stmt is selectstatement && (!(stmt is unionstatement))) { selectstatement s = stmt as selectstatement; foreach (selectfield f in s.Fields) { if (f.definesOrder) { if (parent != null && parent.TypeOfCommand.Equals(typeof(insertstatement)) == false) { throw new ArgumentException("subquery may not define a sort order"); } if (s.OrderBy == null) { s.OrderBy = new orderbystatement(); } s.OrderBy.fields.Add(f); } } #if DEBUG if (s.Where != null && s.Where.expressions.Count <= 0) { throw new ArgumentException("cannot have empty 'where' clause"); } if (s.Having != null && s.Having.expressions.Count <= 0) { throw new ArgumentException("cannot have empty 'having' clause"); } if (s.GroupBy != null && s.GroupBy.fields.Count <= 0) { throw new ArgumentException("cannot have empty 'group by' clause"); } if (s.OrderBy != null && s.OrderBy.fields.Count <= 0) { throw new ArgumentException("cannot have empty 'order by' clause"); } #endif } /* some tasks are only performed during debug mode */ #if DEBUG /* 1. debug task: propagation of toplevel statements to other manifold members required? */ if (_parent == null) { string sproc; if (type_of_procedure == null) { sproc = stmt.GetType().Name; } else { sproc = type_of_procedure.Name; } Tracing.InfoDAL("{0} Compiled \"{1}\", a {2} command for {3}", DateTime.Now.Ticks.ToString().PadLeft(20, '0'), sproc, stmt.ToString(), db.GetProvider.ToString()); //Tracing.VerboseDAL("at {0}", Environment.StackTrace); PropagateStatementCompilation(stmt); } /* 2. debug task: identify missing or inconsistent fields because some providers don't tell line numbers of wrong field names */ /* -!- */ #endif return(true); }
public static Iexpressionlistitem where_fld_in(string tablealias, string fieldname, selectstatement in_subquery) { return(new expression(new operand(new selectfield(tablealias, fieldname)), comparisonoperator.coIn, new operand(new selectfield(new subquery(in_subquery), null)))); }
public static Iexpressionlistitem where_notexists(selectstatement notexists_subquery) { return(new expression(null as Ioperand, comparisonoperator.coNotExists, new operand(new selectfield(new subquery(notexists_subquery), null)))); }
protected override void TopClauseAsPrefix(selectstatement st, StringBuilder sink) { sink.AppendFormat(" TOP {0} ", st.Top); }
public innerjoin(selectstatement subqry, string alias, expressionlist expr) : base(joinflavor.inner, subqry, alias, expr) { }
public outerjoin(selectstatement subqry, expressionlist expr) : base(joinflavor.outer, subqry, expr) { }
public tableexpression(selectstatement subqry, string consumeralias) { local_Tablename = null; local_consumeralias = consumeralias; subquery = subqry; }
/// <summary> /// Dynamically built DAL sql at its best /// DL20130829KKROE328LLLOJJKAKAK0A /// </summary> public selectstatement GenerateLookup(ITable table, ITable tableL, string fieldname_s, string fieldname_m, string lcid, string limit_to_username) { const string LOOKUP = "LOOKUP"; const string LOOKUP_L = "LOOKUPL"; const string LOOKUP_LBASE = "LOOKUPLB"; /* 1. compose the primary select statement */ var pkname = table.SingleFieldPK.Name; var sel = new selectstatement(); sel.Fields = new selectlist() { new selectfield(shortcuts.op_coalesce(LOOKUP, "lm_when", LOOKUP, "crea_when"), "crea_lm") }; if (string.IsNullOrEmpty(fieldname_m)) { sel.Fields.Add(new selectfield(LOOKUP, fieldname_s, "Caption")); sel.Fields.Add(new selectliteral(new literal(DBNull.Value, DbType.String), "mCaption")); } else { sel.Fields.Add(new selectfield(LOOKUP, fieldname_s, "Caption")); sel.Fields.Add(new selectfield(LOOKUP, fieldname_m, "mCaption")); } sel.Tables = new tableexpressions(table.GetType(), LOOKUP); sel.Where = new whereclause(new expressionlist()); if (String.IsNullOrWhiteSpace(limit_to_username) == false) { sel.Where.expressions.Add(shortcuts.where_bracketopen); sel.Where.expressions.Add(shortcuts.where_fld_eq_v(LOOKUP, "crea_who", limit_to_username)); sel.Where.expressions.Add(shortcuts.where_or); sel.Where.expressions.Add(shortcuts.where_fld_eq_v(LOOKUP, "lm_who", limit_to_username)); sel.Where.expressions.Add(shortcuts.where_bracketclose); sel.Where.expressions.Add(shortcuts.where_and); } sel.Where.expressions.Add(shortcuts.where_fld_notnull(LOOKUP, fieldname_s)); sel.Where.expressions.Add(shortcuts.where_and); sel.Where.expressions.Add(shortcuts.where_fld_null(LOOKUP, "del_when")); /* 2. if we have a translation table, compose as a union */ if (tableL == null) { sel.OrderBy = new orderbystatement(new selectlist() { new selectfield(shortcuts.op_coalesce(LOOKUP, "lm_when", LOOKUP, "crea_when"), sortorder.so_desc) }); return(sel); } else { /* lookup ass a union with translation table */ var selL = new selectstatement(); selL.Fields = new selectlist() { new selectfield(shortcuts.op_coalesce(LOOKUP_LBASE, "lm_when", LOOKUP_LBASE, "crea_when"), "crea_lm") }; if (string.IsNullOrEmpty(fieldname_m)) { selL.Fields.Add(new selectfield(LOOKUP_L, fieldname_s, "Caption")); selL.Fields.Add(new selectliteral(new literal(DBNull.Value, DbType.String), "mCaption")); } else { selL.Fields.Add(new selectfield(LOOKUP_L, fieldname_s, "Caption")); selL.Fields.Add(new selectfield(LOOKUP_L, fieldname_m, "mCaption")); } selL.Tables = new tableexpressions(tableL.GetType(), LOOKUP_L); selL.Joins = new joinstatements() { new innerjoin(table.GetType(), LOOKUP_LBASE, new expressionlist() { shortcuts.where_fld_eq(LOOKUP_LBASE, pkname, LOOKUP_L, pkname) }) }; selL.Where = new whereclause(new expressionlist()); if (String.IsNullOrWhiteSpace(limit_to_username) == false) { sel.Where.expressions.Add(shortcuts.where_bracketopen); sel.Where.expressions.Add(shortcuts.where_fld_eq_v(LOOKUP_LBASE, "crea_who", limit_to_username)); sel.Where.expressions.Add(shortcuts.where_or); sel.Where.expressions.Add(shortcuts.where_fld_eq_v(LOOKUP_LBASE, "lm_who", limit_to_username)); sel.Where.expressions.Add(shortcuts.where_bracketclose); sel.Where.expressions.Add(shortcuts.where_and); } selL.Where.expressions.Add(shortcuts.where_fld_eq_v(LOOKUP_L, "LCID", lcid)); selL.Where.expressions.Add(shortcuts.where_and); selL.Where.expressions.Add(shortcuts.where_fld_notnull(LOOKUP_L, fieldname_s)); selL.Where.expressions.Add(shortcuts.where_and); selL.Where.expressions.Add(shortcuts.where_fld_null(LOOKUP_LBASE, "del_when")); return(new unionstatement(true, new selectstatements() { sel, selL }, new orderbystatement(new selectlist() { new selectfield(shortcuts.op_coalesce(null, "lm_when", null, "crea_when"), sortorder.so_desc) }))); } }
public leftjoin(selectstatement subqry, string alias, expressionlist expr) : base(joinflavor.left, subqry, alias, expr) { }
protected override void TopClauseAsCondition(selectstatement st) { st.WhereEnsure(true); st.Where.expressions.Add(shortcuts.where_fld_le_i(null, "ROWNUM", st.Top)); }
public insertstatement(Type targettable, selectlist targetfields, unionstatement source) { TargetTable = targettable.Name; TargetFields = targetfields; Source = source; }
public insertstatement(string targettable, selectlist targetfields, insertselect source) { TargetTable = targettable; TargetFields = targetfields; Source = source; }
public updatestatement(string targettablename, selectlist targetfields, selectstatement source) { TargetTable = new tableexpression(targettablename); TargetFields = targetfields; Source = source; }
public selectfield(selectfunctiondef func, string rowsource_consumeralias, Ioperand literalconst, string fieldname, string asname, selectstatement subqry, sortorder order) : this(func, null, rowsource_consumeralias, literalconst, fieldname, asname, subqry, order) { }
public updatestatement(tableexpression targettable, selectlist targetfields, selectstatement source) { TargetTable = targettable; TargetFields = targetfields; Source = source; }
public joinstatement(joinflavor type, selectstatement subqry, expressionlist expr) : this(type, null, null, subqry, expr) { }
public tableexpression(selectstatement subqry) : this(subqry, null) { }
public selectfield(selectstatement subqry, string fieldname, sortorder order) : this(selectfunctiondef.subquery, null, null, null, fieldname, null, subqry, order) { }