/// <summary>
/// Deletes rows from a table using a variable list of and-connected where
/// conditions. Fields identified using the 'parameters' collection need not
/// be part of the primary key.
/// </summary>
public static commandprimitive delete_wildcard(Iddl db, string targettable, deletecommandparameters parameters)
{
expressionlist expressions = new expressionlist();
/* build the where clause */
bool bneedand = false;
foreach (deletecommandparameter parm in parameters)
{
if (bneedand)
{
expressions.Add(where_and);
}
if (parm.size.HasValue)
{
expressions.Add(where_fld_eq_p(parm.fieldname, parm.paramname, parm.paramtype, parm.size.Value));
}
else
{
expressions.Add(where_fld_eq_p(parm.fieldname, parm.paramname, parm.paramtype));
}
bneedand = true;
}
if (expressions.Count <= 0)
{
throw new ArgumentOutOfRangeException("parameters", "Cannot execute a delete_wildcard statement without specifying any restrictions.");
}
/* compose the final statement */
deletestatement del = new deletestatement(
targettable,
new whereclause(expressions)
);
return(new commandadhoc(db, del));
}
private static void adhoc_poormans_detail(expressionlist clause, string fieldidentifier, string qstoken)
{
string tablealias, fieldname;
/* identify the field - possible with and without table alias prefixed */
string[] field = fieldidentifier.Split(new char[] { '.' });
if (field.Length > 1)
{
tablealias = field[0];
fieldname = field[1];
}
else
{
tablealias = null;
fieldname = field[0];
}
/* poor man's full text search */
clause.Add(where_fld_like_v_wildcard(tablealias, fieldname, qstoken));
}
public expressionlist PKQueryExpression(Iddl db)
{
expressionlist ex = new expressionlist();
foreach (Field f in this)
{
if (f.PrimaryKeyConstituent)
{
if (ex.Count > 0)
{
ex.Add(shortcuts.where_and);
}
ex.Add(shortcuts.where_fld_eq_p(f.Name, db.MakeParamName(f.Name), f.Type, f.Size));
}
}
if (ex.Count <= 0)
{
Trace.WriteLine("Attention: attempt to execute a primary-key based update statement where no primary key definition is present, resulting in an update affecting all rows in the table.");
}
/* deliver */
return(ex);
}
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(expressionlist clause, string qstoken, bool bneedand, params string[] fieldlist)
{
bool _bneedand = bneedand;
adhoc_poormans_fulltext(clause, qstoken, ref _bneedand, (expressionlist)null, fieldlist);
}
/// <summary>
/// [dlatikay 20101116] while solving PSI-1010-03446, we support now a pipe-separated multipart syntax for the fieldlist.
/// This will enable building dynamic search conditions for composite names, thusly:
/// "T.OriginatorLastname|T.OriginatorMiddlename|T.OriginatorFirstname", searching frequent permutations of a tripartite human name
/// </summary>
public static void adhoc_poormans_fulltext(expressionlist clause, string qstoken, ref bool bneedand, expressionlist custom_or_clause, params string[] fieldlist)
{
if (String.IsNullOrWhiteSpace(qstoken))
{
/* optimisation added [dlatikay 20101116] */
return;
}
bool first = true;
if (fieldlist == null || fieldlist.Length <= 0)
{
return;
}
if (bneedand)
{
clause.Add(where_and);
}
/* expression list */
clause.Add(where_bracketopen);
foreach (string compositefield in fieldlist)
{
if (String.IsNullOrEmpty(compositefield) == false)
{
if (first)
{
first = false;
}
else
{
clause.Add(shortcuts.where_or);
}
/* added functionality [dlatikay 20101116] for multipart comparisons */
string[] parts = compositefield.Split(new char[] { '|' });
if (parts.Length == 3) /* last-middle-first! remember we're talking about field identifiers here */
{
/* assumption: first part is lastname in index [0], then middlename in [1], then firstname in [2] */
clause.Add(shortcuts.where_bracketopen);
/* now, think the other way round: how many parts has our search token?
* Bush Kate, Oldfield Michael Gordon; van Veen Hermann;
* permutations: Kate Bush, Mike Gordon Oldfield, Hermann van Veen;
* so, expressed in index sequences, that boils down to:
* AUSTRIAN 2: 0-2, 2-0
* DUTCH 3: 1-0-2, 2-1-0
* BRITISH 3: 0-2-1, 2-1-0
* giving us the final set of permutations to support for two part tokens:
* 0-2
* 2-0
* and for three-part tokens:
* 1-0-2, 2-1-0, 0-2-1 */
string[] tokens = (qstoken ?? "").Split(new char[] { ' ' });
if (tokens.Length == 2)
{
clause.Add(shortcuts.where_bracketopen);
/* 0-2 */
adhoc_poormans_detail(clause, parts[0], tokens[0]);
clause.Add(shortcuts.where_and);
adhoc_poormans_detail(clause, parts[2], tokens[1]);
clause.Add(shortcuts.where_bracketclose);
clause.Add(shortcuts.where_or);
clause.Add(shortcuts.where_bracketopen);
/* 2-0 */
adhoc_poormans_detail(clause, parts[2], tokens[0]);
clause.Add(shortcuts.where_and);
adhoc_poormans_detail(clause, parts[0], tokens[1]);
clause.Add(shortcuts.where_bracketclose);
}
else if (tokens.Length == 3)
{
clause.Add(shortcuts.where_bracketopen);
/* 1-0-2 */
adhoc_poormans_detail(clause, parts[1], tokens[0]);
clause.Add(shortcuts.where_and);
adhoc_poormans_detail(clause, parts[0], tokens[1]);
clause.Add(shortcuts.where_and);
adhoc_poormans_detail(clause, parts[2], tokens[2]);
clause.Add(shortcuts.where_bracketclose);
clause.Add(shortcuts.where_or);
/* 2-1-0 */
clause.Add(shortcuts.where_bracketopen);
adhoc_poormans_detail(clause, parts[2], tokens[0]);
clause.Add(shortcuts.where_and);
adhoc_poormans_detail(clause, parts[1], tokens[1]);
clause.Add(shortcuts.where_and);
adhoc_poormans_detail(clause, parts[0], tokens[2]);
clause.Add(shortcuts.where_bracketclose);
clause.Add(shortcuts.where_or);
/* 0-2-1 */
clause.Add(shortcuts.where_bracketopen);
adhoc_poormans_detail(clause, parts[0], tokens[0]);
clause.Add(shortcuts.where_and);
adhoc_poormans_detail(clause, parts[2], tokens[1]);
clause.Add(shortcuts.where_and);
adhoc_poormans_detail(clause, parts[1], tokens[2]);
clause.Add(shortcuts.where_bracketclose);
}
else if (tokens.Length > 0)
{
/* default. all other combinations recognize the verbatim search string in the first field */
adhoc_poormans_detail(clause, parts[0], qstoken);
}
/* ready for next */
clause.Add(shortcuts.where_bracketclose);
}
else if (parts.Length == 1)
{
adhoc_poormans_detail(clause, parts[0], qstoken);
}
}
}
/* [pstraka>dlatikay 20100120] additional custom FT search ORed to the automatically built query */
if (custom_or_clause != null)
{
if (custom_or_clause.Count > 0)
{
if (!first)
{
clause.Add(shortcuts.where_or);
}
clause.AddRange(custom_or_clause);
}
}
/* conclude */
clause.Add(shortcuts.where_bracketclose);
bneedand = true;
}
public mssqlfulltextjoin(string alias, expressionlist expr)
: base("FREETEXTTABLE(doc02FT,Chunk,@token)", alias, expr)
{
/* DL9284293042JJHJJ9899 for how @token is registered with the DAL compiler */
}
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 innerjoin(Type table, string alias, expressionlist expr) : base(joinflavor.inner, table.Name, alias, expr)
{
}
public innerjoin(selectstatement subqry, string alias, expressionlist expr) : base(joinflavor.inner, subqry, alias, expr)
{
}
public joinstatement(joinflavor type, selectstatement subqry, expressionlist expr) : this(type, null, null, subqry, expr)
{
}
public innerjoin(string table, expressionlist expr) : base(joinflavor.inner, table, expr)
{
}
public leftjoin(string table, expressionlist expr) : base(joinflavor.left, table, expr)
{
}
public whereclause()
{
expressions = new expressionlist();
}
public joinstatement()
{
expressions = new expressionlist();
}
public outerjoin(selectstatement subqry, expressionlist expr) : base(joinflavor.outer, subqry, expr)
{
}
public outerjoin(string table, string alias, expressionlist expr) : base(joinflavor.outer, table, alias, expr)
{
}
public leftjoin(selectstatement subqry, string alias, expressionlist expr) : base(joinflavor.left, subqry, alias, expr)
{
}
public leftjoin(Type table, string alias, expressionlist expr) : base(joinflavor.left, table.Name, alias, expr)
{
}
public joinstatement(joinflavor type, string table, string alias, expressionlist expr) : this(type, table, alias, null, expr)
{
}
public whereclause(expressionlist conditions)
{
expressions = conditions;
}
