public string BuildPhrase(ConnectorBase conn)
{
string ret = @"REPLACE(";
if (SourceType == ValueObjectType.ColumnName)
{
if (SourceTableName != null && SourceTableName.Length > 0)
{
ret += conn.EncloseFieldName(SourceTableName);
ret += ".";
}
ret += conn.EncloseFieldName(Source);
}
else if (SourceType == ValueObjectType.Value)
{
ret += conn.PrepareValue(Source);
}
else ret += Source;
ret += ",";
if (SearchType == ValueObjectType.ColumnName)
{
if (SearchTableName != null && SearchTableName.Length > 0)
{
ret += conn.EncloseFieldName(SearchTableName);
ret += ".";
}
ret += conn.EncloseFieldName(Search);
}
else if (SearchType == ValueObjectType.Value)
{
ret += conn.PrepareValue(Search);
}
else ret += Search;
ret += ",";
if (ReplaceWithType == ValueObjectType.ColumnName)
{
if (ReplaceWithTableName != null && ReplaceWithTableName.Length > 0)
{
ret += conn.EncloseFieldName(ReplaceWithTableName);
ret += ".";
}
ret += conn.EncloseFieldName(ReplaceWith);
}
else if (ReplaceWithType == ValueObjectType.Value)
{
ret += conn.PrepareValue(ReplaceWith);
}
else ret += ReplaceWith;
ret += ")";
return ret;
}
public string BuildPhrase(ConnectorBase conn)
{
string ret;
if (conn.TYPE == ConnectorBase.SqlServiceType.MSSQL)
ret = @"RAND(CAST(NEWID() AS VARBINARY)) * ";
else if (conn.TYPE == ConnectorBase.SqlServiceType.MYSQL)
ret = @"RAND() * ";
else // if (conn.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
ret = @"RANDOM() * ";
if (ObjectType == ValueObjectType.ColumnName)
{
if (TableName != null && TableName.Length > 0)
{
ret += conn.EncloseFieldName(TableName);
ret += ".";
}
ret += conn.EncloseFieldName(Object);
}
else if (ObjectType == ValueObjectType.Value)
{
ret += conn.PrepareValue(Object);
}
else ret += Object;
return ret;
}
public string BuildPhrase(ConnectorBase conn)
{
string ret;
ret = @"MAX(";
if (ObjectType == ValueObjectType.ColumnName)
{
if (TableName != null && TableName.Length > 0)
{
ret += conn.EncloseFieldName(TableName);
ret += ".";
}
ret += conn.EncloseFieldName(Object.ToString());
}
else if (ObjectType == ValueObjectType.Value)
{
ret += conn.PrepareValue(Object);
}
else ret += Object;
ret += ")";
return ret;
}
public string BuildPhrase(ConnectorBase conn, Query relatedQuery = null)
{
if (Values.Count == 0)
{
return conn.PrepareValue("");
}
else
{
bool first = true;
if (conn.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL && !IgnoreNulls)
{
// PostgreSQL does not ignore NULL values in || operator, like CONCAT in other sql languages
string ret = "";
foreach (var value in Values)
{
if (first) first = false;
else ret += " || ";
ret += value.Build(conn, relatedQuery);
}
return ret;
}
else
{
// PostgreSQL ignores NULL values in CONCAT
bool coalesce = IgnoreNulls && conn.TYPE != ConnectorBase.SqlServiceType.POSTGRESQL;
string ret = "CONCAT(";
foreach (var value in Values)
{
if (first) first = false;
else ret += ",";
if (coalesce)
{
ret += "COALESCE(";
ret += value.Build(conn, relatedQuery);
ret += ",'')";
}
else
{
ret += value.Build(conn, relatedQuery);
}
}
ret += ")";
return ret;
}
}
}
public string BuildPhrase(ConnectorBase conn)
{
string ret = @"";
if (ObjectType1 == ValueObjectType.ColumnName)
{
if (TableName1 != null && TableName1.Length > 0)
{
ret += conn.EncloseFieldName(TableName1);
ret += ".";
}
ret += conn.EncloseFieldName(Object1.ToString());
}
else if (ObjectType1 == ValueObjectType.Value)
{
ret += @"(" + conn.PrepareValue(Object1) + @")";
}
else ret += Object1;
ret += @"+";
if (ObjectType2 == ValueObjectType.ColumnName)
{
if (TableName2 != null && TableName2.Length > 0)
{
ret += conn.EncloseFieldName(TableName2);
ret += ".";
}
ret += conn.EncloseFieldName(Object2.ToString());
}
else if (ObjectType2 == ValueObjectType.Value)
{
ret += @"(" + conn.PrepareValue(Object2) + @")";
}
else ret += Object2;
return ret;
}
public string BuildPhrase(ConnectorBase conn)
{
string ret;
if (conn.TYPE == ConnectorBase.SqlServiceType.MYSQL || conn.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
ret = @"IFNULL(";
else ret = @"ISNULL(";
if (FirstObjectType == ValueObjectType.ColumnName)
{
if (FirstTableName != null && FirstTableName.Length > 0)
{
ret += conn.EncloseFieldName(FirstTableName);
ret += ".";
}
ret += conn.EncloseFieldName((string)FirstObject);
}
else if (FirstObjectType == ValueObjectType.Value)
{
ret += conn.PrepareValue(FirstObject);
}
else ret += FirstObject;
ret += ", ";
if (SecondObjectType == ValueObjectType.ColumnName)
{
if (SecondTableName != null && SecondTableName.Length > 0)
{
ret += conn.EncloseFieldName(SecondTableName);
ret += ".";
}
ret += conn.EncloseFieldName((string)SecondObject);
}
else if (SecondObjectType == ValueObjectType.Value)
{
ret += conn.PrepareValue(SecondObject);
}
else ret += SecondObject;
ret += ")";
return ret;
}
public string BuildPhrase(ConnectorBase conn, Query relatedQuery = null)
{
StringBuilder sb = new StringBuilder();
if (conn.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append("string_agg(");
if (Distinct)
{
sb.Append("DISTINCT ");
}
sb.Append(Value.Build(conn, relatedQuery));
if (Separator != null)
{
sb.Append("," + conn.PrepareValue(Separator));
}
else
{
sb.Append(",','");
}
if (OrderBy != null && OrderBy.Count > 0)
{
OrderBy.BuildCommand(sb, conn, relatedQuery, false);
}
sb.Append(")");
}
else if (conn.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
sb.Append("GROUP_CONCAT(");
if (Distinct)
{
sb.Append("DISTINCT ");
}
sb.Append(Value.Build(conn, relatedQuery));
if (OrderBy != null && OrderBy.Count > 0)
{
OrderBy.BuildCommand(sb, conn, relatedQuery, false);
}
if (Separator != null)
{
sb.Append(" SEPARATOR " + conn.PrepareValue(Separator));
}
sb.Append(")");
}
else
{
throw new NotSupportedException("SHA1 is not supported by " + conn.TYPE.ToString());
}
return sb.ToString();
}
private static void BuildSingleValue(
StringBuilder outputBuilder, ConnectorBase conn,
string firstTableName, object value, ValueObjectType valueType,
string otherTableName, object otherValue, ValueObjectType otherType,
Query relatedQuery,
TableSchema rightTableSchema, string rightTableName)
{
if (valueType == ValueObjectType.Value)
{
if (value is Query)
{
outputBuilder.Append('(');
outputBuilder.Append(((Query)value).BuildCommand(conn));
outputBuilder.Append(')');
}
else if (value is WhereList)
{
outputBuilder.Append('(');
((WhereList)value).BuildCommand(outputBuilder, conn, relatedQuery, rightTableSchema, rightTableName);
outputBuilder.Append(')');
}
else if (value is ICollection)
{
ICollection collIn = value as ICollection;
StringBuilder sbIn = new StringBuilder();
sbIn.Append('(');
bool first = true;
TableSchema schema = null;
if (object.ReferenceEquals(otherTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
schema = rightTableSchema;
}
else
{
if (relatedQuery != null)
{
if (otherTableName == null || !relatedQuery.TableAliasMap.TryGetValue(otherTableName, out schema))
{
schema = relatedQuery.Schema;
}
}
}
foreach (object objIn in collIn)
{
if (first) first = false;
else sbIn.Append(',');
if (schema != null)
{
sbIn.Append(Query.PrepareColumnValue(schema.Columns.Find((string)otherValue), objIn, conn, relatedQuery));
}
else
{
sbIn.Append(conn.PrepareValue(objIn, relatedQuery));
}
}
if (first)
{
sbIn.Append("NULL"); // Avoid exceptions, create a NULL list, where the condition will always return FALSE
}
sbIn.Append(')');
outputBuilder.Append(sbIn.ToString());
}
else if (otherType == ValueObjectType.ColumnName)
{
TableSchema schema = null;
if (object.ReferenceEquals(otherTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
schema = rightTableSchema;
}
else
{
if (relatedQuery != null)
{
if (otherTableName == null || !relatedQuery.TableAliasMap.TryGetValue(otherTableName, out schema))
{
schema = relatedQuery.Schema;
}
}
}
if (schema != null)
{
// Try to match value type to the other value type
outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)otherValue), value, conn, relatedQuery));
}
else
{
// Format it according to generic rules
outputBuilder.Append(conn.PrepareValue(value, relatedQuery));
}
}
else
{
outputBuilder.Append(conn.PrepareValue(value, relatedQuery));
}
}
else if (valueType == ValueObjectType.ColumnName)
{
if (firstTableName != null)
{
if (object.ReferenceEquals(firstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(conn.WrapFieldName(rightTableName));
}
else
{
outputBuilder.Append(conn.WrapFieldName(firstTableName));
}
outputBuilder.Append('.');
}
outputBuilder.Append(conn.WrapFieldName((string)value));
}
else
{
outputBuilder.Append(value == null ? @"NULL" : value);
}
}
private void BuildSelectList(StringBuilder sb, ConnectorBase connection)
{
if (_ListSelect == null || _ListSelect.Count == 0)
{
sb.Append("*");
}
else
{
bool bFirst = true;
foreach (SelectColumn sel in _ListSelect)
{
if (bFirst) bFirst = false;
else sb.Append(',');
if (sel.ObjectType == ValueObjectType.Value)
{
if (sel.Value is Query)
{
sb.Append('(');
sb.Append(((Query)sel.Value).BuildCommand(connection));
sb.Append(')');
}
else
{
sb.Append(connection.PrepareValue(sel.Value, this));
}
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.WrapFieldName(sel.Alias));
}
}
else if (sel.ObjectType == ValueObjectType.Literal)
{
if (string.IsNullOrEmpty(sel.Alias))
{
sb.Append(sel.Value.ToString());
}
else
{
sb.Append(sel.Value.ToString());
sb.Append(@" AS ");
sb.Append(connection.WrapFieldName(sel.Alias));
}
}
else
{
if (_ListJoin != null && _ListJoin.Count > 0 && string.IsNullOrEmpty(sel.TableName))
{
if (Schema != null)
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.WrapFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.WrapFieldName(_SchemaName));
}
else sb.Append(connection.WrapFieldName(_FromExpressionTableAlias));
sb.Append('.');
sb.Append(sel.Value == null ? "*" : connection.WrapFieldName(sel.Value.ToString()));
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.WrapFieldName(sel.Alias));
}
}
else
{
if (!string.IsNullOrEmpty(sel.TableName))
{
sb.Append(connection.WrapFieldName(sel.TableName));
sb.Append('.');
}
sb.Append(sel.Value == null ? "*" : connection.WrapFieldName(sel.Value.ToString()));
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.WrapFieldName(sel.Alias));
}
}
}
}
}
}
private void BuildSelectForMsAccessLimitOffset(StringBuilder sb, ConnectorBase connection)
{
// MSACCESS - LIMIT,OFFSET:
// SELECT * FROM
// (
// SELECT TOP [limit] * FROM
// (
// SELECT TOP [offset+limit]
// [selects] FROM [tables, joins]
// WHERE [wheres]
// ORDER BY [orders]
// ) pp ORDER BY [inverted orders]
// ) p ORDER BY [orders]
sb.AppendFormat(@"SELECT * FROM ( SELECT TOP {0} * FROM ( SELECT TOP {1} ", Limit, Offset + Limit);
if (IsDistinct) sb.Append(@"DISTINCT ");
bool bFirst = true;
foreach (SelectColumn sel in _ListSelect)
{
if (bFirst) bFirst = false;
else sb.Append(',');
if (sel.ObjectType == ValueObjectType.Value)
{
if (sel.Value is Query)
{
sb.Append('(');
sb.Append(((Query)sel.Value).BuildCommand(connection));
sb.Append(')');
}
else
{
sb.Append(connection.PrepareValue(sel.Value));
}
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
else if (sel.ObjectType == ValueObjectType.Literal)
{
if (string.IsNullOrEmpty(sel.Alias))
{
sb.Append(sel.ColumnName);
}
else
{
sb.Append(sel.ColumnName);
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
else
{
if (_ListJoin != null && _ListJoin.Count > 0 && string.IsNullOrEmpty(sel.TableName))
{
if (Schema != null)
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
}
else sb.Append(connection.EncloseFieldName(_FromExpressionTableAlias));
sb.Append('.');
sb.Append(connection.EncloseFieldName(sel.ColumnName));
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
else
{
if (!string.IsNullOrEmpty(sel.TableName))
{
sb.Append(connection.EncloseFieldName(sel.TableName));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(sel.ColumnName));
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
}
}
sb.Append(@" FROM ");
if (Schema != null)
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
}
else
{
sb.Append(@"(");
if (_FromExpression is dg.Sql.IPhrase)
{
sb.Append(((dg.Sql.IPhrase)_FromExpression).BuildPhrase(connection));
}
else sb.Append(_FromExpression);
sb.Append(@") ");
sb.Append(connection.EncloseFieldName(_FromExpressionTableAlias));
}
BuildJoin(sb, connection);
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
StringBuilder sbOrderBy = new StringBuilder();
StringBuilder sbGroupBy = new StringBuilder();
BuildGroupBy(sbGroupBy, connection, false);
BuildOrderBy(sbOrderBy, connection, false);
sb.Append(sbOrderBy.ToString());
sb.Append(sbGroupBy.ToString());
sb.Append(@") pp ");
BuildGroupBy(sb, connection, true);
BuildOrderBy(sb, connection, true);
sb.Append(@") p ");
sb.Append(sbOrderBy.ToString());
sb.Append(sbGroupBy.ToString());
}
public string BuildCommand(ConnectorBase connection)
{
if (this.QueryMode == QueryMode.ExecuteStoredProcedure || this.QueryMode == QueryMode.None) return string.Empty;
StringBuilder sb = new StringBuilder();
bool ownsConnection = false;
if (connection == null)
{
ownsConnection = true;
connection = ConnectorBase.NewInstance();
}
try
{
bool bFirst;
if (this.QueryMode != QueryMode.None)
{
switch (this.QueryMode)
{
case QueryMode.Select:
{
if (_ListSelect.Count == 0) _ListSelect.Add(new SelectColumn(@"*", true));
if (Offset > 0 &&
connection.TYPE == ConnectorBase.SqlServiceType.MSSQL &&
!connection.SupportsSelectPaging())
{
// Special case for Sql Server where in versions prior to 2012 there was no paging support
BuildSelectForMsSqlPaging(sb, connection);
}
else if (Offset > 0 && Limit > 0 &&
connection.TYPE == ConnectorBase.SqlServiceType.MSACCESS)
{
// Special case for Ms Access where paging is not supported so we do a complex emulation of LIMIT+OFFSET
BuildSelectForMsAccessLimitOffset(sb, connection);
}
else if (Offset > 0 &&
connection.TYPE == ConnectorBase.SqlServiceType.MSACCESS)
{
// Special case for Ms Access where paging is not supported so we do a complex emulation of OFFSET
BuildSelectForMsAccessOffset(sb, connection);
}
else
{
sb.Append(@" SELECT ");
if (IsDistinct) sb.Append(@"DISTINCT ");
if (Limit > 0 &&
(connection.TYPE == ConnectorBase.SqlServiceType.MSACCESS ||
(connection.TYPE == ConnectorBase.SqlServiceType.MSSQL && !(connection.SupportsSelectPaging() && Offset > 0))
))
{
sb.Append(@"TOP " + Limit);
sb.Append(' ');
}
bFirst = true;
foreach (SelectColumn sel in _ListSelect)
{
if (bFirst) bFirst = false;
else sb.Append(',');
if (sel.ObjectType == ValueObjectType.Value)
{
if (sel.Value is Query)
{
sb.Append('(');
sb.Append(((Query)sel.Value).BuildCommand(connection));
sb.Append(')');
}
else
{
sb.Append(connection.PrepareValue(sel.Value));
}
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
else if (sel.ObjectType == ValueObjectType.Literal)
{
if (string.IsNullOrEmpty(sel.Alias))
{
sb.Append(sel.ColumnName);
}
else
{
sb.Append(sel.ColumnName);
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
else
{
if (_ListJoin != null && _ListJoin.Count > 0 && string.IsNullOrEmpty(sel.TableName))
{
if (Schema != null)
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
}
else sb.Append(connection.EncloseFieldName(_FromExpressionTableAlias));
sb.Append('.');
sb.Append(connection.EncloseFieldName(sel.ColumnName));
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
else
{
if (!string.IsNullOrEmpty(sel.TableName))
{
sb.Append(connection.EncloseFieldName(sel.TableName));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(sel.ColumnName));
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
}
}
sb.Append(@" FROM ");
if (Schema != null)
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
}
else
{
sb.Append(@"(");
if (_FromExpression is dg.Sql.IPhrase)
{
sb.Append(((dg.Sql.IPhrase)_FromExpression).BuildPhrase(connection));
}
else sb.Append(_FromExpression);
sb.Append(@") ");
sb.Append(connection.EncloseFieldName(_FromExpressionTableAlias));
}
BuildJoin(sb, connection);
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
BuildGroupBy(sb, connection, false);
BuildOrderBy(sb, connection, false);
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
if (Limit > 0)
{
sb.Append(@" LIMIT ");
sb.Append(Limit);
// OFFSET is not supported without LIMIT
if (Offset > 0)
{
sb.Append(@" OFFSET ");
sb.Append(Offset);
}
}
}
else if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
if (Limit > 0)
{
sb.Append(@" LIMIT ");
sb.Append(Limit);
}
if (Offset > 0)
{
sb.Append(@" OFFSET ");
sb.Append(Offset);
}
}
else if (connection.TYPE == ConnectorBase.SqlServiceType.MSSQL)
{
if (connection.SupportsSelectPaging() && Offset > 0)
{
// If we are in MsSql OFFSET/FETCH mode...
sb.Append(@" OFFSET ");
sb.Append(Offset);
sb.Append(@" ROWS");
if (Limit > 0)
{
sb.Append(@" FETCH NEXT ");
sb.Append(Limit);
sb.Append(@" ROWS ONLY");
}
}
}
// Done with select query
}
// Write out supported hints
switch (_QueryHint)
{
case QueryHint.ForUpdate:
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL ||
connection.TYPE == ConnectorBase.SqlServiceType.MSSQL ||
connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@" FOR UPDATE");
}
break;
case QueryHint.LockInSharedMode:
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
sb.Append(@" LOCK IN SHARED MODE");
}
else if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@" FOR SHARE");
}
break;
}
}
break;
case QueryMode.Insert:
{
sb.Append(@"INSERT INTO ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append(@" (");
bFirst = true;
foreach (AssignmentColumn ins in _ListInsertUpdate)
{
if (bFirst) bFirst = false;
else sb.Append(',');
sb.Append(connection.EncloseFieldName(ins.ColumnName));
}
if (InsertExpression != null)
{
sb.Append(@") ");
sb.Append(InsertExpression);
}
else
{
sb.Append(@") VALUES (");
bFirst = true;
foreach (AssignmentColumn ins in _ListInsertUpdate)
{
if (bFirst) bFirst = false;
else sb.Append(',');
if (ins.SecondType == ValueObjectType.Literal)
{
sb.Append(ins.Second);
}
else if (ins.SecondType == ValueObjectType.Value)
{
if (ins.Second is Query)
{
sb.Append('(');
sb.Append(((Query)ins.Second).BuildCommand(connection));
sb.Append(')');
}
else PrepareColumnValue(Schema.Columns.Find(ins.ColumnName), ins.Second, sb, connection);
}
else if (ins.SecondType == ValueObjectType.ColumnName)
{
if (ins.SecondTableName != null)
{
sb.Append(connection.EncloseFieldName(ins.SecondTableName));
sb.Append(@".");
}
sb.Append(connection.EncloseFieldName(ins.Second.ToString()));
}
}
sb.Append(@")");
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
}
}
break;
case QueryMode.Update:
{
bool hasJoins = _ListJoin != null && _ListJoin.Count > 0;
sb.Append(@"UPDATE ");
if (hasJoins &&
(connection.TYPE == ConnectorBase.SqlServiceType.MSSQL ||
connection.TYPE == ConnectorBase.SqlServiceType.MSACCESS) &&
_FromExpressionTableAlias != null && _FromExpressionTableAlias.Length > 0)
{
sb.Append(connection.EncloseFieldName(_FromExpressionTableAlias));
}
else
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
if (_FromExpressionTableAlias != null && _FromExpressionTableAlias.Length > 0)
{
sb.Append(' ');
sb.Append(connection.EncloseFieldName(_FromExpressionTableAlias));
}
}
if (hasJoins)
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
BuildJoin(sb, connection);
}
}
bFirst = true;
foreach (AssignmentColumn upd in _ListInsertUpdate)
{
if (bFirst)
{
sb.Append(@" SET ");
bFirst = false;
}
else sb.Append(',');
sb.Append(connection.EncloseFieldName(upd.ColumnName));
sb.Append('=');
if (upd.SecondType == ValueObjectType.Literal)
{
sb.Append(upd.Second);
}
else if (upd.SecondType == ValueObjectType.Value)
{
PrepareColumnValue(Schema.Columns.Find(upd.ColumnName), upd.Second, sb, connection);
}
else if (upd.SecondType == ValueObjectType.ColumnName)
{
if (upd.SecondTableName != null)
{
sb.Append(connection.EncloseFieldName(upd.SecondTableName));
sb.Append(@".");
}
sb.Append(connection.EncloseFieldName(upd.Second.ToString()));
}
}
if (hasJoins)
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MSSQL ||
connection.TYPE == ConnectorBase.SqlServiceType.MSACCESS)
{
sb.Append(@" FROM ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
if (_FromExpressionTableAlias != null && _FromExpressionTableAlias.Length > 0)
{
sb.Append(' ');
sb.Append(connection.EncloseFieldName(_FromExpressionTableAlias));
}
BuildJoin(sb, connection);
}
}
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
BuildOrderBy(sb, connection, false);
}
break;
case QueryMode.InsertOrUpdate:
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
sb.Append(@"REPLACE INTO ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append(@" (");
bFirst = true;
foreach (AssignmentColumn ins in _ListInsertUpdate)
{
if (bFirst) bFirst = false;
else sb.Append(',');
sb.Append(connection.EncloseFieldName(ins.ColumnName));
}
if (InsertExpression != null)
{
sb.Append(@") ");
sb.Append(InsertExpression);
}
else
{
sb.Append(@") VALUES (");
bFirst = true;
foreach (AssignmentColumn ins in _ListInsertUpdate)
{
if (bFirst) bFirst = false;
else sb.Append(',');
if (ins.SecondType == ValueObjectType.Literal)
{
sb.Append(ins.Second);
}
else if (ins.SecondType == ValueObjectType.Value)
{
if (ins.Second is Query)
{
sb.Append('(');
sb.Append(((Query)ins.Second).BuildCommand(connection));
sb.Append(')');
}
else PrepareColumnValue(Schema.Columns.Find(ins.ColumnName), ins.Second, sb, connection);
}
else if (ins.SecondType == ValueObjectType.ColumnName)
{
if (ins.SecondTableName != null)
{
sb.Append(connection.EncloseFieldName(ins.SecondTableName));
sb.Append(@".");
}
sb.Append(connection.EncloseFieldName(ins.Second.ToString()));
}
}
sb.Append(@")");
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
}
}
else
{
_NeedTransaction = true;
if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
throw new NotImplementedException(@"This operation is not implemented for PostgreSQL due to database limitations.");
}
// MSSQL
QueryMode qm = this.QueryMode;
this.QueryMode = QueryMode.Update;
sb.Append(BuildCommand(connection));
sb.Append(@"; IF @@rowcount = 0 BEGIN ");
this.QueryMode = QueryMode.Insert;
sb.Append(BuildCommand(connection));
sb.Append(@"; END");
this.QueryMode = qm;
}
}
break;
case QueryMode.Delete:
{
sb.Append(@"DELETE");
if (_ListJoin != null && _ListJoin.Count > 0)
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
}
sb.Append(@" FROM ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
BuildJoin(sb, connection);
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
BuildOrderBy(sb, connection, false);
}
break;
case QueryMode.CreateTable:
{
sb.Append(@"CREATE TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append('(');
int iPrimaryKeys = 0;
bool bSep = false;
foreach (TableSchema.Column col in Schema.Columns)
{
if (col.IsPrimaryKey) iPrimaryKeys++;
if (bSep) sb.Append(@", "); else bSep = true;
BuildColumnProperties(sb, connection, col, false);
}
if (iPrimaryKeys > 0)
{
if (bSep) sb.Append(@", ");
sb.AppendFormat(@"CONSTRAINT {0} PRIMARY KEY(", connection.EncloseFieldName(@"PK_" + _SchemaName));
bSep = false;
foreach (TableSchema.Column col in Schema.Columns)
{
if (!col.IsPrimaryKey) continue;
if (bSep) sb.Append(@", "); else bSep = true;
if (col.IsPrimaryKey) sb.Append(connection.EncloseFieldName(col.Name));
}
bSep = true;
sb.Append(')');
}
sb.Append(')');
if (Schema.TableOptions != null && Schema.TableOptions.Count > 0)
{
foreach (KeyValuePair<string, string> option in Schema.TableOptions)
{
sb.Append(' ');
sb.Append(option.Key);
sb.Append('=');
sb.Append(option.Value);
}
}
}
break;
case QueryMode.CreateIndex:
BuildCreateIndex(sb, connection, _CreateIndexObject);
break;
case QueryMode.CreateIndexes:
{
if ((Schema.Indexes.Count + Schema.ForeignKeys.Count) > 1)
{
_NeedTransaction = true;
}
foreach (TableSchema.Index index in Schema.Indexes)
{
BuildCreateIndex(sb, connection, index);
sb.Append(@";");
}
foreach (TableSchema.ForeignKey foreignKey in Schema.ForeignKeys)
{
BuildCreateIndex(sb, connection, foreignKey);
sb.Append(@";");
}
}
break;
case QueryMode.AddColumn:
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append(@" ADD ");
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL || connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@"COLUMN ");
}
BuildColumnProperties(sb, connection, _AlterColumn, false);
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
int idx = Schema.Columns.IndexOf(_AlterColumn);
if (idx == 0) sb.Append(@"FIRST ");
else sb.AppendFormat(@"AFTER {0} ", connection.EncloseFieldName(Schema.Columns[idx - 1].Name));
}
}
break;
case QueryMode.ChangeColumn:
{
if (_AlterColumnOldName != null && _AlterColumnOldName.Length == 0) _AlterColumnOldName = null;
if (_AlterColumnOldName != null)
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MSSQL)
{
sb.Append(@"EXEC sp_rename ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append('.');
sb.Append(connection.EncloseFieldName(_AlterColumnOldName));
sb.Append(',');
sb.Append(connection.EncloseFieldName(_AlterColumn.Name));
sb.Append(@",'COLUMN';");
}
else if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append(@" RENAME COLUMN ");
sb.Append(connection.EncloseFieldName(_AlterColumnOldName));
sb.Append(@" TO ");
sb.Append(connection.EncloseFieldName(_AlterColumn.Name));
sb.Append(';');
}
}
if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
// Very limited syntax, will have to do this with several statements
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
string alterColumnStatement = @" ALTER COLUMN ";
alterColumnStatement += connection.EncloseFieldName(_AlterColumn.Name);
sb.Append(alterColumnStatement);
sb.Append(@" TYPE ");
bool isTextField; // UNUSED HERE
BuildColumnPropertiesDataType(sb, connection, _AlterColumn, out isTextField);
sb.Append(',');
sb.Append(alterColumnStatement);
sb.Append(_AlterColumn.Nullable ? @" DROP NOT NULL;" : @" SET NOT NULL;");
sb.Append(alterColumnStatement);
sb.Append(@" SET DEFAULT ");
PrepareColumnValue(_AlterColumn, _AlterColumn.Default, sb, connection);
sb.Append(';');
}
else
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append(' ');
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
sb.AppendFormat(@"CHANGE {0} ", connection.EncloseFieldName(_AlterColumnOldName != null ? _AlterColumnOldName : _AlterColumn.Name));
}
else
{
sb.Append(@"ALTER COLUMN ");
}
BuildColumnProperties(sb, connection, _AlterColumn, connection.TYPE == ConnectorBase.SqlServiceType.MSSQL);
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
int idx = Schema.Columns.IndexOf(_AlterColumn);
if (idx == 0) sb.Append(@"FIRST ");
else sb.AppendFormat(@"AFTER {0} ", connection.EncloseFieldName(Schema.Columns[idx - 1].Name));
}
}
}
break;
case QueryMode.DropColumn:
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append(@" DROP COLUMN ");
sb.Append(connection.EncloseFieldName(_DropColumnName));
}
break;
case QueryMode.DropForeignKey:
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append(@" DROP FOREIGN KEY ");
sb.Append(connection.EncloseFieldName(_DropColumnName));
}
else if (connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append(@" DROP CONSTRAINT ");
sb.Append(connection.EncloseFieldName(_DropColumnName));
}
else
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append(@" DROP CONSTRAINT ");
sb.Append(connection.EncloseFieldName(_DropColumnName));
}
}
break;
case QueryMode.DropIndex:
{
if (connection.TYPE == ConnectorBase.SqlServiceType.MYSQL || connection.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append(@" DROP INDEX ");
sb.Append(connection.EncloseFieldName(_DropColumnName));
}
else
{
sb.Append(@"ALTER TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
sb.Append(@" DROP CONSTRAINT ");
sb.Append(connection.EncloseFieldName(_DropColumnName));
}
}
break;
case QueryMode.DropTable:
{
sb.Append(@"DROP TABLE ");
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
}
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (ownsConnection && connection != null)
{
connection.Close();
connection.Dispose();
connection = null;
}
}
return sb.ToString();
}
public string BuildPhrase(ConnectorBase conn)
{
StringBuilder sb = new StringBuilder();
if (conn.TYPE == ConnectorBase.SqlServiceType.MYSQL)
{
sb.Append(@"TIMESTAMPADD(");
switch (Unit)
{
case DateTimeUnit.Microsecond:
sb.Append(@"MICROSECOND");
break;
case DateTimeUnit.Millisecond:
sb.Append(@"MICROSECOND"); // Will multiply by 1000
break;
default:
case DateTimeUnit.Second:
sb.Append(@"SECOND");
break;
case DateTimeUnit.Minute:
sb.Append(@"MINUTE");
break;
case DateTimeUnit.Hour:
sb.Append(@"HOUR");
break;
case DateTimeUnit.Day:
sb.Append(@"DAY");
break;
case DateTimeUnit.Week:
sb.Append(@"WEEK");
break;
case DateTimeUnit.Month:
sb.Append(@"MONTH");
break;
case DateTimeUnit.QuarterYear:
sb.Append(@"QUARTER");
break;
case DateTimeUnit.Year:
sb.Append(@"YEAR");
break;
}
sb.Append(',');
sb.Append(Unit == DateTimeUnit.Millisecond ? Interval * 1000L : Interval);
sb.Append(',');
if (ObjectType == ValueObjectType.ColumnName)
{
if (TableName != null && TableName.Length > 0)
{
sb.Append(conn.EncloseFieldName(TableName));
sb.Append(".");
}
sb.Append(conn.EncloseFieldName(Object.ToString()));
}
else if (ObjectType == ValueObjectType.Value)
{
sb.Append(conn.PrepareValue(Object));
}
else sb.Append(Object);
sb.Append(')');
}
else if (conn.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
if (ObjectType == ValueObjectType.ColumnName)
{
if (TableName != null && TableName.Length > 0)
{
sb.Append(conn.EncloseFieldName(TableName));
sb.Append(".");
}
sb.Append(conn.EncloseFieldName(Object.ToString()));
}
else if (ObjectType == ValueObjectType.Value)
{
sb.Append(conn.PrepareValue(Object));
}
else sb.Append(Object);
sb.Append(" + INTERVAL '");
switch (Unit)
{
case DateTimeUnit.Microsecond:
sb.Append(Interval / (double)1000);
sb.Append(@" milliseconds");
break;
case DateTimeUnit.Millisecond:
sb.Append(Interval);
sb.Append(@" milliseconds");
break;
default:
case DateTimeUnit.Second:
sb.Append(Interval);
sb.Append(@" seconds");
break;
case DateTimeUnit.Minute:
sb.Append(Interval);
sb.Append(@" minutes");
break;
case DateTimeUnit.Hour:
sb.Append(Interval);
sb.Append(@" hours");
break;
case DateTimeUnit.Day:
sb.Append(Interval);
sb.Append(@" days");
break;
case DateTimeUnit.Week:
sb.Append(Interval);
sb.Append(@" weeks");
break;
case DateTimeUnit.Month:
sb.Append(Interval);
sb.Append(@" months");
break;
case DateTimeUnit.QuarterYear:
sb.Append(Interval * 3);
sb.Append(@" months");
break;
case DateTimeUnit.Year:
sb.Append(Interval * 3);
sb.Append(@" years");
break;
}
sb.Append('\'');
}
else // if (conn.TYPE == ConnectorBase.SqlServiceType.MSSQL || conn.TYPE == ConnectorBase.SqlServiceType.MSACCESS)
{
sb.Append(@"DATEADD(");
switch (Unit)
{
case DateTimeUnit.Microsecond:
sb.Append(@"microsecond");
break;
case DateTimeUnit.Millisecond:
sb.Append(@"millisecond");
break;
default:
case DateTimeUnit.Second:
sb.Append(@"second");
break;
case DateTimeUnit.Minute:
sb.Append(@"minute");
break;
case DateTimeUnit.Hour:
sb.Append(@"hour");
break;
case DateTimeUnit.Day:
sb.Append(@"day");
break;
case DateTimeUnit.Week:
sb.Append(@"week");
break;
case DateTimeUnit.Month:
sb.Append(@"month");
break;
case DateTimeUnit.QuarterYear:
sb.Append(@"quarter");
break;
case DateTimeUnit.Year:
sb.Append(@"year");
break;
}
sb.Append(',');
sb.Append(Interval);
sb.Append(',');
if (ObjectType == ValueObjectType.ColumnName)
{
if (TableName != null && TableName.Length > 0)
{
sb.Append(conn.EncloseFieldName(TableName));
sb.Append(".");
}
sb.Append(conn.EncloseFieldName(Object.ToString()));
}
else if (ObjectType == ValueObjectType.Value)
{
sb.Append(conn.PrepareValue(Object));
}
else sb.Append(Object);
sb.Append(')');
}
return sb.ToString();
}
private void BuildSelectForMsSqlPaging(StringBuilder sb, ConnectorBase connection)
{
// MSSQL:
// WITH [table] AS
// (
// SELECT [selects],ROW_NUMBER() OVER([orders]) AS __ROWID__
// FROM [tables, joins] WHERE [wheres]
// )
// SELECT * FROM [table]
// WHERE __ROWID__ BETWEEN [offset+1] AND [offset+1+limit]
// --WHERE __ROWID__ > offset
sb.Append(@"WITH [Ordered Table] AS ( SELECT ");
if (IsDistinct) sb.Append(@"DISTINCT ");
bool bFirst = true;
foreach (SelectColumn sel in _ListSelect)
{
if (bFirst) bFirst = false;
else sb.Append(',');
if (sel.ObjectType == ValueObjectType.Value)
{
if (sel.Value is Query)
{
sb.Append('(');
sb.Append(((Query)sel.Value).BuildCommand(connection));
sb.Append(')');
}
else
{
sb.Append(connection.PrepareValue(sel.Value));
}
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
else if (sel.ObjectType == ValueObjectType.Literal)
{
if (string.IsNullOrEmpty(sel.Alias))
{
sb.Append(sel.ColumnName);
}
else
{
sb.Append(sel.ColumnName);
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
else
{
if (_ListJoin != null && _ListJoin.Count > 0 && string.IsNullOrEmpty(sel.TableName))
{
if (Schema != null)
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
}
else sb.Append(connection.EncloseFieldName(_FromExpressionTableAlias));
sb.Append('.');
sb.Append(connection.EncloseFieldName(sel.ColumnName));
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
else
{
if (!string.IsNullOrEmpty(sel.TableName))
{
sb.Append(connection.EncloseFieldName(sel.TableName));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(sel.ColumnName));
if (!string.IsNullOrEmpty(sel.Alias))
{
sb.Append(@" AS ");
sb.Append(connection.EncloseFieldName(sel.Alias));
}
}
}
}
sb.Append(@",ROW_NUMBER() OVER(");
BuildOrderBy(sb, connection, false);
sb.Append(@") AS __ROWID__");
sb.Append(@" FROM ");
if (Schema != null)
{
if (Schema.DatabaseOwner.Length > 0)
{
sb.Append(connection.EncloseFieldName(Schema.DatabaseOwner));
sb.Append('.');
}
sb.Append(connection.EncloseFieldName(_SchemaName));
}
else
{
sb.Append(@"(");
if (_FromExpression is dg.Sql.IPhrase)
{
sb.Append(((dg.Sql.IPhrase)_FromExpression).BuildPhrase(connection));
}
else sb.Append(_FromExpression);
sb.Append(@") ");
sb.Append(connection.EncloseFieldName(_FromExpressionTableAlias));
}
BuildJoin(sb, connection);
if (_ListWhere != null && _ListWhere.Count > 0)
{
sb.Append(@" WHERE ");
_ListWhere.BuildCommand(sb, connection, this, null, null);
}
sb.Append(@") SELECT * FROM [Ordered Table]");
if (Limit > 0) sb.AppendFormat(@" WHERE __ROWID__ BETWEEN {0} AND {1}", Offset + 1, Offset + 1 + Limit);
else sb.AppendFormat(@" WHERE __ROWID__ > {0}", Offset);
}
public string BuildPhrase(ConnectorBase conn, Query relatedQuery = null)
{
StringBuilder sb = new StringBuilder();
if (conn.TYPE == ConnectorBase.SqlServiceType.MSSQL)
{
}
else
{
if (conn.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@"ST_Contains(");
}
else // MYSQL
{
sb.Append(@"MBRContains(");
}
}
if (OuterValueType == ValueObjectType.ColumnName)
{
if (OuterTableName != null && OuterTableName.Length > 0)
{
sb.Append(conn.WrapFieldName(OuterTableName));
sb.Append(".");
}
sb.Append(conn.WrapFieldName(OuterValue.ToString()));
}
else if (OuterValueType == ValueObjectType.Value)
{
if (OuterValue is Geometry)
{
((Geometry)OuterValue).BuildValue(sb, conn);
}
else
{
sb.Append(conn.PrepareValue(OuterValue, relatedQuery));
}
}
else sb.Append(OuterValue);
if (conn.TYPE == ConnectorBase.SqlServiceType.MSSQL)
{
sb.Append(@".STContains(");
}
else // MYSQL, PostgreSQL
{
sb.Append(@",");
}
if (InnerValueType == ValueObjectType.ColumnName)
{
if (InnerTableName != null && InnerTableName.Length > 0)
{
sb.Append(conn.WrapFieldName(InnerTableName));
sb.Append(".");
}
sb.Append(conn.WrapFieldName(InnerValue.ToString()));
}
else if (InnerValueType == ValueObjectType.Value)
{
if (InnerValue is Geometry)
{
((Geometry)InnerValue).BuildValue(sb, conn);
}
else
{
sb.Append(conn.PrepareValue(InnerValue, relatedQuery));
}
}
else sb.Append(InnerValue);
sb.Append(@")");
return sb.ToString();
}
public string Build(ConnectorBase conn, Query relatedQuery = null)
{
string ret = "";
if (Type == ValueObjectType.ColumnName)
{
if (TableName != null && TableName.Length > 0)
{
ret += conn.WrapFieldName(TableName);
ret += ".";
}
ret += conn.WrapFieldName(Value.ToString());
}
else if (Type == ValueObjectType.Value)
{
ret += @"(" + conn.PrepareValue(Value, relatedQuery) + @")";
}
else
{
ret += Value;
}
return ret;
}
public string BuildPhrase(ConnectorBase conn)
{
StringBuilder sb = new StringBuilder();
if (conn.TYPE == ConnectorBase.SqlServiceType.MSSQL)
{
}
else
{
if (conn.TYPE == ConnectorBase.SqlServiceType.POSTGRESQL)
{
sb.Append(@"ST_Contains(");
}
else // MYSQL
{
sb.Append(@"MBRContains(");
}
}
if (ContainingObjectType == ValueObjectType.ColumnName)
{
if (ContainingTableName != null && ContainingTableName.Length > 0)
{
sb.Append(conn.EncloseFieldName(ContainingTableName));
sb.Append(".");
}
sb.Append(conn.EncloseFieldName(ContainingObject.ToString()));
}
else if (ContainingObjectType == ValueObjectType.Value)
{
if (ContainingObject is Geometry)
{
((Geometry)ContainingObject).BuildValue(sb, conn);
}
else
{
sb.Append(conn.PrepareValue(ContainingObject));
}
}
else sb.Append(ContainingObject);
if (conn.TYPE == ConnectorBase.SqlServiceType.MSSQL)
{
sb.Append(@".STContains(");
}
else // MYSQL, PostgreSQL
{
sb.Append(@",");
}
if (ContainedObjectType == ValueObjectType.ColumnName)
{
if (ContainedTableName != null && ContainedTableName.Length > 0)
{
sb.Append(conn.EncloseFieldName(ContainedTableName));
sb.Append(".");
}
sb.Append(conn.EncloseFieldName(ContainedObject.ToString()));
}
else if (ContainedObjectType == ValueObjectType.Value)
{
if (ContainedObject is Geometry)
{
((Geometry)ContainedObject).BuildValue(sb, conn);
}
else
{
sb.Append(conn.PrepareValue(ContainedObject));
}
}
else sb.Append(ContainedObject);
sb.Append(@")");
return sb.ToString();
}
public string BuildPhrase(ConnectorBase conn, Query relatedQuery = null)
{
string ret = "";
switch (conn.TYPE)
{
case ConnectorBase.SqlServiceType.MYSQL:
{
ret += "JSON_UNQUOTE(JSON_EXTRACT(";
ret += Value.Build(conn, relatedQuery);
ret += ", ";
ret += conn.PrepareValue(Path);
ret += "))";
}
break;
case ConnectorBase.SqlServiceType.MSSQL:
{
ret += "JSON_VALUE(";
ret += Value.Build(conn, relatedQuery);
ret += ", ";
ret += conn.PrepareValue(Path);
ret += ")";
}
break;
case ConnectorBase.SqlServiceType.POSTGRESQL:
{ // No support for returning "self". Postgres works with actual json Objects.
var parts = PathParts(Path);
if (parts.Count > 0 && parts[0] == "$")
{
parts.RemoveAt(0);
}
ret += "json_extract_path_text(";
ret += Value.Build(conn, relatedQuery);
foreach (var part in parts)
{
ret += ", " + conn.PrepareValue(part);
}
ret += ")";
}
break;
default:
throw new NotSupportedException("JsonExtract is not supported by current DB type");
}
return ret;
}
public void BuildCommand(StringBuilder outputBuilder, bool isFirst, ConnectorBase conn, Query relatedQuery, TableSchema rightTableSchema, string rightTableName)
{
if (!isFirst)
{
switch (Condition)
{
case WhereCondition.AND:
outputBuilder.Append(@" AND ");
break;
case WhereCondition.OR:
outputBuilder.Append(@" OR ");
break;
}
}
if (Comparision == WhereComparision.None && // Its not a comparison
// And there's no list or the list is empty
(!(First is WhereList) || ((WhereList)First).Count == 0) &&
// And it's not a literal expression
FirstType != ValueObjectType.Literal &&
FirstType != ValueObjectType.Value
)
{
outputBuilder.Append(@"1"); // dump a dummy TRUE condition to fill the blank
return;
}
if (First is WhereList)
{
outputBuilder.Append('(');
((WhereList)First).BuildCommand(outputBuilder, conn, relatedQuery, rightTableSchema, rightTableName);
outputBuilder.Append(')');
}
else
{
if (FirstType == ValueObjectType.Value)
{
if (SecondType == ValueObjectType.ColumnName)
{
if (object.ReferenceEquals(SecondTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(Query.PrepareColumnValue(rightTableSchema.Columns.Find((string)Second), First, conn));
}
else
{
TableSchema schema;
if (SecondTableName == null || !relatedQuery.TableAliasMap.TryGetValue(SecondTableName, out schema))
{
schema = relatedQuery.Schema;
}
outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)Second), First, conn));
}
}
else
{
outputBuilder.Append(conn.PrepareValue(First));
}
}
else if (FirstType == ValueObjectType.ColumnName)
{
if (FirstTableName != null)
{
if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(conn.EncloseFieldName(rightTableName));
}
else
{
outputBuilder.Append(conn.EncloseFieldName(FirstTableName));
}
outputBuilder.Append('.');
}
outputBuilder.Append(conn.EncloseFieldName((string)First));
}
else
{
outputBuilder.Append(First == null ? @"NULL" : First);
}
if (Comparision != WhereComparision.None)
{
switch (Comparision)
{
case WhereComparision.EqualsTo:
if (First == null || Second == null) outputBuilder.Append(@" IS ");
else outputBuilder.Append(@" = ");
break;
case WhereComparision.NotEqualsTo:
if (First == null || Second == null) outputBuilder.Append(@" IS NOT ");
else outputBuilder.Append(@" <> ");
break;
case WhereComparision.GreaterThan:
outputBuilder.Append(@" > ");
break;
case WhereComparision.GreaterThanOrEqual:
outputBuilder.Append(@" >= ");
break;
case WhereComparision.LessThan:
outputBuilder.Append(@" < ");
break;
case WhereComparision.LessThanOrEqual:
outputBuilder.Append(@" <= ");
break;
case WhereComparision.Is:
outputBuilder.Append(@" IS ");
break;
case WhereComparision.IsNot:
outputBuilder.Append(@" IS NOT ");
break;
case WhereComparision.Like:
outputBuilder.Append(@" LIKE ");
break;
case WhereComparision.Between:
outputBuilder.Append(@" BETWEEN ");
break;
case WhereComparision.In:
outputBuilder.Append(@" IN ");
break;
case WhereComparision.NotIn:
outputBuilder.Append(@" NOT IN ");
break;
}
if (Comparision != WhereComparision.In && Comparision != WhereComparision.NotIn)
{
if (SecondType == ValueObjectType.Value)
{
if (Second is Query)
{
outputBuilder.Append('(');
outputBuilder.Append(((Query)Second).BuildCommand(conn));
outputBuilder.Append(')');
}
else
{
if (FirstType == ValueObjectType.ColumnName)
{
// Match SECOND value to FIRST's column type
if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(Query.PrepareColumnValue(rightTableSchema.Columns.Find((string)First), Second, conn));
}
else
{
TableSchema schema;
if (FirstTableName == null || !relatedQuery.TableAliasMap.TryGetValue(FirstTableName, out schema))
{
schema = relatedQuery.Schema;
}
outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)First), Second, conn));
}
}
else
{
outputBuilder.Append(conn.PrepareValue(Second));
}
}
}
else if (SecondType == ValueObjectType.ColumnName)
{
if (SecondTableName != null)
{
if (object.ReferenceEquals(SecondTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(conn.EncloseFieldName(rightTableName));
}
else
{
outputBuilder.Append(conn.EncloseFieldName(SecondTableName));
}
outputBuilder.Append('.');
}
outputBuilder.Append(conn.EncloseFieldName((string)Second));
}
else
{
if (Second == null) outputBuilder.Append(@"NULL");
else outputBuilder.Append(Second);
}
}
else
{
if (Second is Query) outputBuilder.AppendFormat(@"({0})", Second.ToString());
else
{
ICollection collIn = Second as ICollection;
if (collIn != null)
{
StringBuilder sbIn = new StringBuilder();
sbIn.Append('(');
bool first = true;
TableSchema schema = null;
if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
schema = rightTableSchema;
}
else
{
if (FirstTableName == null || !relatedQuery.TableAliasMap.TryGetValue(FirstTableName, out schema))
{
schema = relatedQuery.Schema;
}
}
foreach (object objIn in collIn)
{
if (first) first = false; else sbIn.Append(',');
if (schema != null) sbIn.Append(Query.PrepareColumnValue(schema.Columns.Find((string)First), objIn, conn));
else sbIn.Append(conn.PrepareValue(objIn));
}
sbIn.Append(')');
outputBuilder.Append(sbIn.ToString());
}
else outputBuilder.Append(Second);
}
}
if (Comparision == WhereComparision.Between)
{
outputBuilder.Append(@" AND ");
if (ThirdType == ValueObjectType.Value)
{
if (FirstType == ValueObjectType.ColumnName)
{
TableSchema schema = null;
if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
schema = rightTableSchema;
}
else
{
if (FirstTableName == null || !relatedQuery.TableAliasMap.TryGetValue(FirstTableName, out schema))
{
schema = relatedQuery.Schema;
}
}
outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)First), Third, conn));
}
else outputBuilder.Append(conn.PrepareValue(Third));
}
else if (ThirdType == ValueObjectType.ColumnName)
{
if (ThirdTableName != null)
{
if (object.ReferenceEquals(ThirdTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(conn.EncloseFieldName(rightTableName));
}
else
{
outputBuilder.Append(conn.EncloseFieldName(ThirdTableName));
}
outputBuilder.Append('.');
}
outputBuilder.Append(conn.EncloseFieldName((string)Third));
}
else outputBuilder.Append(Third == null ? @"NULL" : Third);
}
if (Comparision == WhereComparision.Like)
{
outputBuilder.Append(' ');
outputBuilder.Append(conn.LikeEscapingStatement);
outputBuilder.Append(' ');
}
}
}
}
