public SqlString Render(IList args, ISessionFactoryImplementor factory)
{
//ANSI-SQL92 definition
//<general set function> ::=
//<set function type> <leftparen> [ <setquantifier> ] <value expression> <right paren>
//<set function type> : := AVG | MAX | MIN | SUM | COUNT
//<setquantifier> ::= DISTINCT | ALL
if (args.Count < 1 || args.Count > 2)
{
throw new QueryException(string.Format("Aggregate {0}(): Not enough parameters (attended from 1 to 2).", name));
}
else if ("*".Equals(args[args.Count - 1]) && !acceptAsterisk)
{
throw new QueryException(string.Format("Aggregate {0}(): invalid argument '*'.", name));
}
SqlStringBuilder cmd = new SqlStringBuilder();
cmd.Add(name)
.Add("(");
if (args.Count > 1)
{
object firstArg = args[0];
if (!StringHelper.EqualsCaseInsensitive("distinct", firstArg.ToString()) &&
!StringHelper.EqualsCaseInsensitive("all", firstArg.ToString()))
{
throw new QueryException(string.Format("Aggregate {0}(): token unknow {1}.", name, firstArg));
}
cmd.AddObject(firstArg).Add(" ");
}
cmd.AddObject(args[args.Count - 1])
.Add(")");
return cmd.ToSqlString();
}
public SqlString Render(IList args, ISessionFactoryImplementor factory)
{
if (args.Count < 2)
{
throw new QueryException("coalesce() requires at least two arguments");
}
var sb = new SqlStringBuilder();
sb.Add("Switch");
sb.Add("(");
for (int i = 0; i < args.Count; i++)
{
if (i>0)
{
sb.Add(",");
}
var arg = args[i];
sb.Add("not IsNull(");
sb.AddObject(arg);
sb.Add(")");
sb.Add(",");
sb.AddObject(arg);
}
sb.Add(",");
sb.Add("1=1");
sb.Add(",");
sb.AddObject(args[args.Count - 1]);
sb.Add(")");
return sb.ToSqlString();
}
public SqlString Render(IList args, ISessionFactoryImplementor factory)
{
// TODO: QueryException if args.Count<2 (not present in H3.2)
bool threeArgs = args.Count > 2;
object pattern = args[0];
object orgString = args[1];
object start = threeArgs ? args[2] : null;
SqlStringBuilder buf = new SqlStringBuilder();
buf.Add("charindex(")
.AddObject(pattern)
.Add(", ");
if (threeArgs)
{
buf.Add("right(");
}
buf.AddObject(orgString);
if (threeArgs)
{
buf.Add(", char_length(")
.AddObject(orgString)
.Add(")-(")
.AddObject(start)
.Add("-1))");
}
buf.Add(")");
return buf.ToSqlString();
}
public override SqlString Render(IList args, ISessionFactoryImplementor factory)
{
bool hasArgs = args.Count > 0;
SqlStringBuilder buf = new SqlStringBuilder();
buf.Add(name);
if (hasArgs)
{
buf.Add("(");
for (int i = 0; i < args.Count; i++)
{
object arg = args[i];
if (arg is Parameter || arg is SqlString)
{
buf.AddObject(arg);
}
else
{
buf.Add(arg.ToString());
}
if (i < (args.Count - 1)) buf.Add(", ");
}
buf.Add(")");
}
return buf.ToSqlString();
}
/// <summary>
/// Render the function call as SQL.
/// </summary>
/// <param name="args">List of arguments</param>
/// <param name="factory"></param>
/// <returns>SQL fragment for the function.</returns>
public override SqlString Render(IList args, ISessionFactoryImplementor factory)
{
SqlStringBuilder builder = new SqlStringBuilder();
builder.AddObject(args[0]);
builder.Add(".");
builder.Add(name);
return builder.ToSqlString();
}
/// <summary>
/// Render the function call as SQL.
/// </summary>
/// <param name="args">List of arguments</param>
/// <param name="factory"></param>
/// <returns>SQL fragment for the function.</returns>
public override SqlString Render(IList args, ISessionFactoryImplementor factory)
{
SqlStringBuilder builder = new SqlStringBuilder();
builder.AddObject(args[0]);
builder.Add(".");
builder.Add(name);
builder.Add("(");
for (int i = 1; i < args.Count; i++)
{
builder.AddObject(args[i]);
if (i < (args.Count - 1))
{
builder.Add(", ");
}
}
builder.Add(")");
return builder.ToSqlString();
}
/// <summary>
///
/// </summary>
/// <param name="builder"></param>
/// <param name="iter"></param>
private void AppendTokens(SqlStringBuilder builder, ICollection iter)
{
bool lastSpaceable = true;
bool lastQuoted = false;
foreach (object token in iter)
{
string tokenString = token as string;
SqlString tokenSqlString = token as SqlString;
bool spaceable = !dontSpace.Contains(token);
bool quoted;
//TODO: seems HACKish to cast between String and SqlString
if (tokenString != null)
{
quoted = tokenString.StartsWith("'");
}
else
{
quoted = tokenSqlString.StartsWithCaseInsensitive("'");
}
if (spaceable && lastSpaceable)
{
if (!quoted || !lastQuoted)
{
builder.Add(" ");
}
}
lastSpaceable = spaceable;
if (token.Equals(StringHelper.SqlParameter))
{
Parameter param = Parameter.Placeholder;
builder.Add(param);
}
else
{
// not sure if we have a string or a SqlString here and token is a
// reference to an object - so let the builder figure out what the
// actual object is
builder.AddObject(token);
}
if (tokenString != null)
{
lastQuoted = tokenString.EndsWith("'");
}
else
{
lastQuoted = tokenSqlString.EndsWith("'");
}
}
}
public SqlString Render(IList args, ISessionFactoryImplementor factory)
{
SqlStringBuilder buf = new SqlStringBuilder().Add(begin);
for (int i = 0; i < args.Count; i++)
{
buf.AddObject(args[i]);
if (i < args.Count - 1) buf.Add(sep);
}
return buf.Add(end).ToSqlString();
}
public SqlString Render(IList args, ISessionFactoryImplementor factory)
{
if (args.Count != 2)
{
throw new QueryException("NullIf() requires two arguments");
}
var sb = new SqlStringBuilder();
sb.Add("iif");
sb.Add("(");
sb.AddObject(args[0]);
sb.Add("=");
sb.AddObject(args[1]);
sb.Add(",");
sb.Add("NULL");
sb.Add(",");
sb.AddObject(args[0]);
sb.Add(")");
return sb.ToSqlString();
}
internal static SqlString JoinParts(object separator, IList <object> parts)
{
if (parts.Count == 0)
{
return(SqlString.Empty);
}
if (parts.Count == 1)
{
return(parts[0] is SqlString sqlstring
? sqlstring
: new SqlString(parts));
}
var buf = new SqlStringBuilder();
buf.AddObject(parts[0]);
for (var index = 1; index < parts.Count; index++)
{
buf.AddObject(separator).AddObject(parts[index]);
}
return(buf.ToSqlString());
}
/// <summary>
///
/// </summary>
/// <param name="args"></param>
/// <param name="factory"></param>
/// <returns></returns>
public SqlString Render(IList args, ISessionFactoryImplementor factory)
{
SqlStringBuilder buffer = new SqlStringBuilder();
buffer.Add(this.nameFunction);
buffer.Add(begin);
for (int i = 0; i < args.Count; i++)
{
buffer.AddObject(args[i]);
if (i < args.Count - 1)
buffer.Add(sep);
}
buffer.Add(end);
return buffer.ToSqlString();
}
public static SqlString Join(SqlString separator, IEnumerable objects)
{
SqlStringBuilder buf = new SqlStringBuilder();
bool first = true;
foreach (object obj in objects)
{
if (!first)
{
buf.Add(separator);
}
first = false;
buf.AddObject(obj);
}
return(buf.ToSqlString());
}
/// <summary></summary>
public SqlString ToSqlStringFragment()
{
SqlStringBuilder buf = new SqlStringBuilder(lhs.Length * 4);
string prefix = tableAlias + StringHelper.Dot;
for (int i = 0; i < lhs.Length; i++)
{
buf.Add(prefix)
.Add(lhs[i] + op);
buf.AddObject(rhs[i]);
if (i < lhs.Length - 1)
{
buf.Add(" and ");
}
}
return buf.ToSqlString();
}
public static SqlString Join(SqlString separator, IEnumerable objects)
{
SqlStringBuilder buf = new SqlStringBuilder();
bool first = true;
foreach (object obj in objects)
{
if (!first)
{
buf.Add(separator);
}
first = false;
buf.AddObject(obj);
}
return buf.ToSqlString();
}
/// <summary></summary>
public SqlString ToSqlStringFragment()
{
SqlStringBuilder buf = new SqlStringBuilder(lhs.Length * 4);
string prefix = tableAlias + StringHelper.Dot;
for (int i = 0; i < lhs.Length; i++)
{
buf.Add(prefix)
.Add(lhs[i] + op);
buf.AddObject(rhs[i]);
if (i < lhs.Length - 1)
{
buf.Add(" and ");
}
}
return(buf.ToSqlString());
}
public SqlString Render(IList args, ISessionFactoryImplementor factory)
{
// DONE: QueryException if args.Count<2 (not present in H3.2)
if (args.Count < 2)
{
throw new QueryException("position(): Not enough parameters (attended from 2 to 3).");
}
bool threeArgs = args.Count > 2;
object pattern = args[0];
object orgString = args[1];
object start = threeArgs ? args[2] : null;
SqlStringBuilder buf = new SqlStringBuilder();
if (threeArgs)
{
buf.Add("(");
}
buf.Add("position(")
.AddObject(pattern)
.Add(" in ");
if (threeArgs)
{
buf.Add("substring(");
}
buf.AddObject(orgString);
if (threeArgs)
{
buf.Add(", ")
.AddObject(start)
.Add(")");
}
buf.Add(")");
if (threeArgs)
{
buf.Add("+")
.AddObject(start)
.Add("-1)");
}
return buf.ToSqlString();
}
public SqlString Insert(int index, string text)
{
if (index < 0)
{
throw new ArgumentException("index should be greater than or equal to 0", "index");
}
SqlStringBuilder result = new SqlStringBuilder();
int offset = 0;
bool inserted = false;
foreach (object part in sqlParts)
{
if (inserted)
{
result.AddObject(part);
continue;
}
int nextOffset = offset + LengthOfPart(part);
if (nextOffset < index)
{
result.AddObject(part);
offset = nextOffset;
}
else if (nextOffset == index)
{
result.AddObject(part);
result.Add(text);
inserted = true;
}
else if (offset == index)
{
result.Add(text);
result.AddObject(part);
inserted = true;
}
else if (index > offset && index < nextOffset)
{
string partString = (string) part;
result.Add(partString.Insert(index - offset, text));
inserted = true;
}
else
{
throw new ArgumentException("index too large", "index");
}
}
return result.ToSqlString();
}
private void AddPart(object part)
{
builder.AddObject(part);
CheckLastPartForOrderByClause();
}
/// <summary>
/// WARNING: side-effecty
/// </summary>
private SqlString RenderScalarSelect()
{
bool isSubselect = superQuery != null;
SqlStringBuilder buf = new SqlStringBuilder();
if (scalarTypes.Count == 0)
{
//ie. no select clause
int size = returnedTypes.Count;
for (int k = 0; k < size; k++)
{
scalarTypes.Add(NHibernateUtil.Entity(persisters[k].EntityName));
string[] _names = persisters[k].IdentifierColumnNames;
for (int i = 0; i < _names.Length; i++)
{
buf.Add(returnedTypes[k].ToString()).Add(StringHelper.Dot.ToString()).Add(_names[i]);
if (!isSubselect)
{
buf.Add(" as ").Add(ScalarName(k, i));
}
if (i != _names.Length - 1 || k != size - 1)
{
buf.Add(StringHelper.CommaSpace);
}
}
}
}
else
{
//there _was_ a select clause
int c = 0;
bool nolast = false; //real hacky...
int parenCount = 0; // used to count the nesting of parentheses
for (int tokenIdx = 0; tokenIdx < scalarSelectTokens.Count; tokenIdx++)
{
SqlString next = scalarSelectTokens[tokenIdx];
if (next.Count == 1)
{
string token = next.ToString();
string lc = token.ToLowerInvariant();
ISQLFunction func = Factory.SQLFunctionRegistry.FindSQLFunction(lc);
if (func != null)
{
// Render the HQL function
SqlString renderedFunction = RenderFunctionClause(func, scalarSelectTokens, ref tokenIdx);
buf.Add(renderedFunction);
}
else
{
if (StringHelper.OpenParen.Equals(token))
{
parenCount++;
}
else if (StringHelper.ClosedParen.Equals(token))
{
parenCount--;
}
else if (lc.Equals(StringHelper.CommaSpace))
{
if (nolast)
{
nolast = false;
}
else
{
if (!isSubselect && parenCount == 0)
{
buf.Add(" as ").Add(ScalarName(c++, 0));
}
}
}
buf.Add(token);
if (lc.Equals("distinct") || lc.Equals("all"))
{
buf.Add(" ");
}
}
}
else
{
nolast = true;
int i = 0;
foreach (object token in next.Parts)
{
buf.AddObject(token);
if (!isSubselect)
{
buf.Add(" as ").Add(ScalarName(c, i));
}
if (i != next.Count - 1)
{
buf.Add(StringHelper.CommaSpace);
}
i++;
}
c++;
}
}
if (!isSubselect && !nolast)
{
buf.Add(" as ").Add(ScalarName(c++, 0));
}
}
return buf.ToSqlString();
}
private SqlString GetLimitString(SqlString querySqlString, object offset, object limit)
{
SqlStringBuilder pagingBuilder = new SqlStringBuilder();
pagingBuilder.Add(querySqlString);
if (limit != null)
{
pagingBuilder.Add(" limit ");
pagingBuilder.AddObject(limit);
}
if (offset != null)
{
pagingBuilder.Add(" offset ");
pagingBuilder.AddObject(offset);
}
return pagingBuilder.ToSqlString();
}
public SqlString GetSpatialRelationString(object geometry, SpatialRelation relation, object anotherGeometry, bool criterion)
{
switch (relation)
{
case SpatialRelation.Covers:
string[] patterns = new string[] {
"T*****FF*",
"*T****FF*",
"***T**FF*",
"****T*FF*",
};
SqlStringBuilder builder = new SqlStringBuilder();
builder.Add("(CASE WHEN ");
for (int i = 0; i < patterns.Length; i++)
{
if (i > 0)
builder.Add(" OR ");
builder
.AddObject(geometry)
.Add(".STRelate(")
.AddObject(anotherGeometry)
.Add(", '")
.Add(patterns[i])
.Add("') = 1")
.ToSqlString();
}
builder.Add(" THEN 1 ELSE 0 END)");
builder.Add(criterion ? " = 1" : "");
return builder.ToSqlString();
case SpatialRelation.CoveredBy:
return GetSpatialRelationString(anotherGeometry, SpatialRelation.Covers, geometry, criterion);
default:
return new SqlStringBuilder(8)
.AddObject(geometry)
.Add(".ST")
.Add(relation.ToString())
.Add("(")
.AddObject(anotherGeometry)
.Add(")")
.Add(criterion ? " = 1" : "")
.ToSqlString();
}
}
/// <summary>
/// Applies the template to passed in arguments.
/// </summary>
/// <param name="args">args function arguments</param>
/// <param name="factory">generated SQL function call</param>
/// <returns></returns>
public SqlString Render(IList args, ISessionFactoryImplementor factory)
{
SqlStringBuilder buf = new SqlStringBuilder();
foreach (TemplateChunk tc in chunks)
{
if (tc.ArgumentIndex != InvalidArgumentIndex)
{
int adjustedIndex = tc.ArgumentIndex - 1; // Arg indices are one-based
object arg = adjustedIndex < args.Count ? args[adjustedIndex] : null;
// TODO: if (arg == null) QueryException is better ?
if (arg != null)
{
if (arg is Parameter || arg is SqlString)
{
buf.AddObject(arg);
}
else
{
buf.Add(arg.ToString());
}
}
}
else
{
buf.Add(tc.Text);
}
}
return buf.ToSqlString();
}
/// <summary>
/// Add a <c>LIMIT (TOP)</c> clause to the given SQL <c>SELECT</c>
/// </summary>
/// <param name="querySqlString">A Query in the form of a SqlString.</param>
/// <param name="limit">Maximum number of rows to be returned by the query</param>
/// <param name="offset">Offset of the first row to process in the result set</param>
/// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns>
public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
{
if (offset > 0)
{
throw new NotSupportedException("SQL Server does not support an offset" );
}
/*
* "SELECT TOP limit rest-of-sql-statement"
*/
querySqlString = querySqlString.Compact();
SqlStringBuilder pagingBuilder = new SqlStringBuilder();
bool topAdded = false;
foreach( object sqlPart in querySqlString.SqlParts )
{
if (!topAdded)
{
string sqlPartString = sqlPart as string;
if( sqlPartString != null )
{
string sqlFragment = sqlPartString.TrimStart();
int insertIndex = GetAfterSelectInsertPoint(sqlFragment);
if( insertIndex > 0 )
{
string newFragment = sqlFragment.Insert(insertIndex, " top " + limit.ToString());
pagingBuilder.Add(newFragment);
topAdded = true;
continue;
}
}
}
pagingBuilder.AddObject(sqlPart);
}
return pagingBuilder.ToSqlString();
}
private SqlString GetJetConvertionFunction(Dialect.Dialect dialect, string sqlType, object arg)
{
sqlType = sqlType.ToUpper();
var sb = new SqlStringBuilder();
if (JetConversionFunctions.ContainsKey(sqlType))
{
var functionname = JetConversionFunctions[sqlType];
var sqlArg = arg.ToString();
if (sqlArg.IndexOf("?") >= 0)
{
sb.Add((string)functionname);
sb.Add("(");
sb.AddObject(arg);
sb.Add(")");
}
else
{
sb.Add("iif");
sb.Add("(");
sb.Add("ISNULL");
sb.Add("(");
sb.AddObject(arg);
sb.Add(")");
sb.Add(",");
sb.Add("NULL");
sb.Add(",");
sb.Add((string)functionname);
sb.Add("(");
sb.AddObject(arg);
sb.Add(")");
sb.Add(")");
}
//sb.Add((string)_JetConversionFunctions[sqlType]);
//sb.Add("(");
//sb.AddObject(arg);
//sb.Add(")");
}
else if (sqlType.Contains("TEXT") || sqlType.Contains("CHAR"))
{
sb.Add("CStr");
sb.Add("(");
sb.AddObject(arg);
sb.Add(")");
}
else if (sqlType == "BIT")
{
sb.Add("iif");
sb.Add("(");
sb.AddObject(arg);
sb.Add("<>");
sb.Add("0");
sb.Add(",");
sb.Add(dialect.ToBooleanValueString(true));
sb.Add(",");
sb.Add(dialect.ToBooleanValueString(false));
sb.Add(")");
}
else
{
sb.Add("(");
sb.AddObject(arg);
sb.Add(")");
}
return sb.ToSqlString();
}
/// <summary>
///
/// </summary>
/// <param name="builder"></param>
/// <param name="iter"></param>
private void AppendTokens( SqlStringBuilder builder, ICollection iter )
{
bool lastSpaceable = true;
bool lastQuoted = false;
int debugIndex = 0;
foreach( object token in iter )
{
string tokenString = token as string;
SqlString tokenSqlString = token as SqlString;
bool spaceable = !dontSpace.Contains( token );
bool quoted = false;
//TODO: seems HACKish to cast between String and SqlString
if( tokenString != null )
{
quoted = tokenString.StartsWith( "'" );
}
else
{
quoted = tokenSqlString.StartsWith( "'" );
}
if( spaceable && lastSpaceable )
{
if( !quoted || !lastQuoted )
{
builder.Add( " " );
}
}
lastSpaceable = spaceable;
if( token.Equals( StringHelper.SqlParameter ) )
{
Parameter param = new Parameter( "placeholder" );
builder.Add( param );
}
else
{
// not sure if we have a string or a SqlString here and token is a
// reference to an object - so let the builder figure out what the
// actual object is
builder.AddObject( token );
}
debugIndex++;
if( tokenString != null )
{
lastQuoted = tokenString.EndsWith( "'" );
}
else
{
lastQuoted = tokenSqlString.EndsWith( "'" );
}
}
}
/// <summary>
/// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
/// </summary>
/// <param name="querySqlString">A Query in the form of a SqlString.</param>
/// <param name="hasOffset">Offset of the first row is not zero</param>
/// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns>
public override SqlString GetLimitString( SqlString querySqlString, bool hasOffset )
{
/*
* "select * from (select row_number() over(orderby_clause) as rownum, "
* querySqlString_without select
* " ) as tempresult where rownum between ? and ?"
*/
SqlStringBuilder pagingBuilder = new SqlStringBuilder();
bool isInOrderBy = false;
StringBuilder orderByStringBuilder = new StringBuilder();
// build a new query and extract the order by part
foreach( object sqlPart in querySqlString.SqlParts )
{
string sqlPartString = sqlPart as string;
if( sqlPartString != null )
{
if( sqlPartString.ToLower( System.Globalization.CultureInfo.InvariantCulture ).TrimStart().StartsWith( "order by" ) )
{
isInOrderBy = true;
}
}
if( isInOrderBy && sqlPart is string )
{
orderByStringBuilder.Append( ( string ) sqlPart );
}
else
{
pagingBuilder.AddObject( sqlPart );
}
}
string rownumClause = "rownumber() over(" + orderByStringBuilder.ToString() + ") as rownum, ";
// Add the rownum clause first, right after the original select
pagingBuilder.Insert( 1, rownumClause );
// Add the rest
pagingBuilder.Insert( 0, "select * from (" );
pagingBuilder.Add( ") as tempresult " );
// Add the where clause
pagingBuilder.Add( "where rownum " );
if( hasOffset )
{
pagingBuilder.Add( "between " );
pagingBuilder.Add( new Parameter( "p1", new Int32SqlType() ) ).Add("+1");
pagingBuilder.Add( " and " );
pagingBuilder.Add( new Parameter( "p2", new Int32SqlType() ) );
}
else
{
pagingBuilder.Add( "<= " );
pagingBuilder.Add( new Parameter( "p1", new Int32SqlType() ) );
}
return pagingBuilder.ToSqlString();
}
/// <summary>
/// Render the function call as SQL.
/// </summary>
/// <param name="args">List of arguments</param>
/// <param name="factory"></param>
/// <returns>SQL fragment for the function.</returns>
public virtual SqlString Render(IList args, ISessionFactoryImplementor factory)
{
SqlStringBuilder builder = new SqlStringBuilder();
var sqlName = name;
switch (name)
{
default:
sqlName = SpatialDialect.IsoPrefix + name;
break;
case "GeometryType":
sqlName = name;
break;
}
builder.Add(sqlName);
builder.Add("(");
for (int i = 0; i < args.Count; i++)
{
builder.AddObject(args[i]);
if (i < (args.Count - 1))
{
builder.Add(", ");
}
}
builder.Add(")");
return builder.ToSqlString();
}
private SqlString RestoreParameters(List<Parameter> parametersOriginal, string sqlfixed)
{
Regex regexReplaceParamNamed = new Regex(
"@p\\d+",
RegexOptions.IgnoreCase
| RegexOptions.Singleline
| RegexOptions.CultureInvariant
);
var matches = regexReplaceParamNamed.Matches(sqlfixed);
var parts = regexReplaceParamNamed.Split(sqlfixed);
var sqlBuilder = new SqlStringBuilder();
for (int i = 0; i < parts.Length; i++)
{
if (i > 0)
{
var paramPos = matches[i - 1].Value.Replace("@p", "");
var param = parametersOriginal.FirstOrDefault(p => p.ParameterPosition == Convert.ToInt32(paramPos));
if (param == null)
{
logger.WarnFormat("No parameter found to match position {0}. Using @p{0}", paramPos);
sqlBuilder.Add(string.Format("@p{0}", paramPos));
}
else
{
sqlBuilder.AddObject(param);
}
}
sqlBuilder.Add(parts[i]);
}
return sqlBuilder.ToSqlString();
}
private void RestoreMissingParameters(SqlString originalSQL, ref SqlString transformedSQL)
{
if (originalSQL.Equals(transformedSQL))
{
return;
}
var parametersOriginal = new ArrayList();
var parametersTransformed = new ArrayList();
foreach (var part in originalSQL)
{
if (part is Parameter)
{
parametersOriginal.Add(part);
}
}
foreach (var part in transformedSQL)
{
if (part is Parameter)
{
parametersTransformed.Add(part);
}
}
//same number of parameters , return
if (parametersOriginal.Count == parametersTransformed.Count)
{
return;
}
//fix missing parameters spliting around '?'
var sqlText = transformedSQL.ToString();
Regex regex = new Regex("@x\\d+",
RegexOptions.IgnoreCase
| RegexOptions.CultureInvariant
);
var parametersParts = regex.Split(sqlText);
//parametersParts = sqlText.Split('?');
if ((parametersParts.Length - 1) != parametersOriginal.Count)
{
//can't restore
throw new QueryException("FinalizeJoins JetDriver removed SQL parameteres and can not be restored");
}
var sqlBuilder = new SqlStringBuilder();
for (int i = 0; i < parametersParts.Length; i++)
{
if (i > 0)
{
sqlBuilder.AddObject(parametersOriginal[i - 1]);
}
sqlBuilder.Add(parametersParts[i]);
}
transformedSQL = sqlBuilder.ToSqlString();
}
/// <summary>
///
/// </summary>
/// <param name="builder"></param>
/// <param name="iter"></param>
private static void AppendTokens(SqlStringBuilder builder, IEnumerable iter)
{
bool lastSpaceable = true;
bool lastQuoted = false;
foreach (object token in iter)
{
var tokenString = token as string;
var tokenSqlString = token as SqlString;
bool spaceable = !dontSpace.Contains(token);
bool quoted;
//TODO: seems HACKish to cast between String and SqlString
if (tokenString != null)
{
quoted = tokenString.StartsWith("'");
}
else
{
quoted = tokenSqlString.StartsWithCaseInsensitive("'");
}
if (spaceable && lastSpaceable)
{
if (!quoted || !lastQuoted)
{
builder.Add(" ");
}
}
lastSpaceable = spaceable;
if (token.Equals(StringHelper.SqlParameter))
{
Parameter param = Parameter.Placeholder;
builder.Add(param);
}
else
{
// not sure if we have a string or a SqlString here and token is a
// reference to an object - so let the builder figure out what the
// actual object is
builder.AddObject(token);
}
if (tokenString != null)
{
lastQuoted = tokenString.EndsWith("'");
}
else
{
lastQuoted = tokenSqlString.EndsWith("'");
}
}
}
/// <summary>
/// Gets the spatial relate string.
/// </summary>
/// <param name="geometry">The geometry.</param>
/// <param name="anotherGeometry">Another geometry.</param>
/// <param name="pattern">The pattern.</param>
/// <param name="isStringPattern">if set to <c>true</c> [is string pattern].</param>
/// <param name="criterion">if set to <c>true</c> [criterion].</param>
/// <returns></returns>
public SqlString GetSpatialRelateString(object geometry, object anotherGeometry, object pattern, bool isStringPattern, bool criterion)
{
if (pattern == null)
{
throw new ArgumentNullException("pattern",
"Ms SQL 2008 does not supports the syntax returning a DE-9IM for the two geometries. For STRelate the 'intersection_pattern_matrix' is mandatory and the result is a boolean.");
}
var builder = new SqlStringBuilder();
builder
.AddObject(geometry)
.Add(".STRelate(")
.AddObject(anotherGeometry);
builder.Add(", ");
if (isStringPattern)
{
builder
.Add("'")
.Add((string) pattern)
.Add("'");
}
else
{
builder.AddObject(pattern);
}
return builder
.Add(")")
.Add(criterion ? " = 1" : "")
.ToSqlString();
}
public SqlString GetSpatialRelateString(object geometry, object anotherGeometry, object pattern, bool isStringPattern, bool criterion)
{
SqlStringBuilder builder = new SqlStringBuilder();
builder
.Add(DialectPrefix)
.Add("Relate");
if (pattern == null)
{
builder.Add("Pattern");
}
builder
.Add("(")
.AddObject(geometry)
.Add(", ")
.AddObject(anotherGeometry);
if (pattern != null)
{
builder.Add(", ");
if (isStringPattern)
{
builder
.Add("'")
.Add((string)pattern)
.Add("'");
}
else
{
builder.AddObject(pattern);
}
}
return builder
.Add(")")
.Add(criterion ? " = 1" : "")
.ToSqlString();
}
/// <summary>
/// Gets the spatial analysis string.
/// </summary>
/// <param name="geometry">The geometry.</param>
/// <param name="analysis">The analysis.</param>
/// <param name="extraArgument">The extra argument.</param>
/// <returns></returns>
public SqlString GetSpatialAnalysisString(object geometry, SpatialAnalysis analysis, object extraArgument)
{
bool[] geomArgs;
SqlStringBuilder sql = new SqlStringBuilder();
sql.Add("MDSYS.");
bool isGeomReturn = true;
switch (analysis)
{
case SpatialAnalysis.Buffer:
sql.Add("OGC_BUFFER");
geomArgs = new bool[] { true, false };
break;
case SpatialAnalysis.ConvexHull:
sql.Add("OGC_CONVEXHULL");
geomArgs = new bool[] { true};
break;
case SpatialAnalysis.Difference:
sql.Add("OGC_DIFFERENCE");
geomArgs = new bool[] { true, true };
break;
case SpatialAnalysis.Distance:
sql.Add("OGC_DISTANCE");
geomArgs = new bool[] { true, true };
isGeomReturn = false;
break;
case SpatialAnalysis.Intersection:
sql.Add("OGC_INTERSECTION");
geomArgs = new bool[] { true, true };
break;
case SpatialAnalysis.SymDifference:
sql.Add("OGC_SYMMETRICDIFFERENCE");
geomArgs = new bool[] { true, true };
break;
case SpatialAnalysis.Union:
sql.Add("OGC_UNION");
geomArgs = new bool[] { true, true };
break;
default:
throw new ArgumentException(
"Unknown SpatialAnalysisFunction ("
+ Enum.GetName(typeof(SpatialAnalysis), analysis) + ").");
}
sql.Add("(");
if (geomArgs.Length > 0)
{
WrapInSTGeometry(geometry, sql);
}
if (geomArgs.Length > 1)
{
sql.Add(",");
if (geomArgs[1])
WrapInSTGeometry(extraArgument, sql);
else
sql.AddObject(extraArgument);
}
sql.Add(")");
if (isGeomReturn)
sql.Add(".geom");
return sql.ToSqlString();
}