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)
{
}
