public Statement Write(Operator @operator)
{
_sql = new SqlWriter();
_parameters = new Dictionary <string, object>();
VisitOperator(@operator, null);
return(new Statement(_sql.ToString(), Statement.StatementType.Text, _parameters));
}
private static string BuildWhereClause(Select select, IEntityMapping mapping, IDictionary <string, object> parameters)
{
if (!select.HasWhere && !select.HasSetOperations)
{
return(null);
}
var writer = new SqlWriter();
if (select.HasWhere)
{
writer.Write(BuildOperators(select.Where, mapping, parameters));
}
if (select.HasSetOperations)
{
foreach (var setOperation in select.SetOperatons)
{
var statement = CreateStatement(setOperation.Select, mapping);
parameters.AddRange(statement.Parameters);
if (!writer.Empty)
{
writer.And.Flush();
}
if (setOperation.Type == SetOperation.OperationType.Compliment)
{
writer.Not.Flush();
}
writer.Exists.OpenBlock.Trim().Write(statement.Text).Trim().CloseBlock.Flush();
}
}
return(writer.ToString());
}
public static Statement CreateStatement(Insert insert, IEntityMapping mapping)
{
var writer = new SqlWriter();
IDictionary <string, object> parameters = null;
writer.InsertInto.QuotedName(insert.Into.Name);
var resultType = Statement.ResultType.None;
switch (insert.Type)
{
case Insert.SetType.Values:
writer.OpenBlock.Trim().FieldList(x => x.Comma.Flush(), insert.Values.Keys).Trim().CloseBlock.Flush();
parameters = new Dictionary <string, object>();
writer.Values.OpenBlock.Trim().ParameterList(x => x.Comma.Flush(), insert.Values.Values.Select(x => parameters.AddWithUniquelyNamedKey(x))).
Trim().CloseBlock.Flush();
if (insert.HasIdentityKey)
{
writer.Trim().QuerySeperator.Select.ScopeIdentity(typeof(int));
resultType = Statement.ResultType.Scalar;
}
break;
case Insert.SetType.Query:
var select = SelectWriter <TEntity> .CreateStatement(insert.Query, mapping);
parameters = select.Parameters;
writer.OpenBlock.Trim().FieldList(x => x.Comma.Flush(), SelectWriter <TEntity> .BuildProjection(insert.Query, mapping, parameters)).Trim().CloseBlock.Flush();
writer.Write(select.Text);
break;
}
return(new Statement(writer.ToString(), Statement.StatementType.Text, resultType, parameters));
}
private Statement Write(Action write)
{
_sql = SqlWriter.CreateWriter();
_parameters = new Dictionary <string, object>();
write();
return(new Statement(_sql.ToString(), Statement.StatementType.Text, _parameters));
}
public static Statement CreateColumnsIntersectionStatement(IEnumerable <string> tables)
{
var sql = new SqlWriter();
foreach (var table in tables.Select((x, i) => new { First = i == 0, Name = x }))
{
if (!table.First)
{
sql.Intersect.Flush();
}
sql.Select.QuotedName(System.Columns.Name).Trim().Comma.
Case.QuotedName(System.Columns.SystemTypeId).When.DataTypeId(DataTypes.VarChar.SqlId).Then.DataTypeId(DataTypes.NVarChar.SqlId).
When.DataTypeId(DataTypes.Char.SqlId).Then.DataTypeId(DataTypes.NChar.SqlId).
When.DataTypeId(DataTypes.Text.SqlId).Then.DataTypeId(DataTypes.NText.SqlId).
Else.QuotedName(System.Columns.SystemTypeId).End.As.QuotedName(System.Columns.SystemTypeId).Trim().Comma.
Case.QuotedName(System.Columns.UserTypeId).When.DataTypeId(DataTypes.VarChar.SqlId).Then.DataTypeId(DataTypes.NVarChar.SqlId).
When.DataTypeId(DataTypes.Char.SqlId).Then.DataTypeId(DataTypes.NChar.SqlId).
When.DataTypeId(DataTypes.Text.SqlId).Then.DataTypeId(DataTypes.NText.SqlId).
Else.QuotedName(System.Columns.UserTypeId).End.As.QuotedName(System.Columns.UserTypeId).
From.Write(System.Columns.TableName).
Where.QuotedName(System.Columns.ObjectId).Equal.ObjectId(table.Name);
}
return(new Statement(sql.ToString(), Statement.StatementType.Text, Statement.ResultType.Multiple));
}
public static Statement CreateAddColumnStatement(string tableName, Column column)
{
var writer = new SqlWriter();
writer.Alter.Table.QuotedName(tableName).Add.Flush();
WriteColumnDefinition(writer, column);
return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None));
}
private static string BuildOperators(Operator @operator, IEntityMapping mapping, IDictionary <string, object> parameters)
{
var writer = new SqlWriter();
var statement = WhereWriter <TEntity> .CreateStatement(@operator, mapping);
parameters.AddRange(statement.Parameters);
writer.Write(statement.Text);
return(writer.ToString());
}
public static Statement CreateStatement(Update update, IEntityMapping mapping)
{
var writer = new SqlWriter();
var parameters = new Dictionary <string, object>();
writer.Update.QuotedName(update.Table.Name).Set.
ParameterAssignmentList(x => x.Comma.Flush(), update.Assignment.ToDictionary(x => x.Key, x => parameters.AddWithUniquelyNamedKey(x.Value)));
var where = WhereWriter <TEntity> .CreateStatement(update.Where, mapping);
writer.Where.Write(where.Text);
parameters.AddRange(where.Parameters);
return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None, parameters));
}
public static Statement CreateStatement(Sync sync, IEntityMapping mapping)
{
var writer = new SqlWriter();
IDictionary <string, object> parameters = new Dictionary <string, object>();
var fields = sync.Target.Projection.Zip(sync.Source.Projection, (t, s) => new { Target = t.Projection, Source = s.Projection })
.Where(x => !sync.ExcludedFields.Any(y => (y.HasKey ? y.Key : y.Name) == (x.Target.Field.HasKey ? x.Target.Field.Key : x.Target.Field.Name))).ToList();
writer.Update.QuotedName(sync.Target.From.Alias).Set
.ExpressionList(x => x.Comma.Flush(), fields, (f, s) => s.
Write(ProjectionWriter <TEntity> .CreateStatement(f.Target, mapping).MergeParameters(parameters).Text).Equal.
Write(ProjectionWriter <TEntity> .CreateStatement(f.Source, mapping).MergeParameters(parameters).Text))
.From.QuotedName(sync.Target.From.Table.Name).QuotedName(sync.Target.From.Alias).Inner.Join
.QuotedName(sync.Source.From.Table.Name).QuotedName(sync.Source.From.Alias)
.On.Write(ProjectionWriter <TEntity> .CreateStatement(sync.TargetKey, mapping).MergeParameters(parameters).Text).Equal
.Write(ProjectionWriter <TEntity> .CreateStatement(sync.SourceKey, mapping).MergeParameters(parameters).Text)
.Do(sync.Source.HasWhere, x => x.And.Write(WhereWriter <TEntity> .CreateStatement(sync.Source.Where, mapping).MergeParameters(parameters).Text))
.Do(sync.Target.HasWhere, x => x.Where.Write(WhereWriter <TEntity> .CreateStatement(sync.Target.Where, mapping).MergeParameters(parameters).Text));
return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None, parameters));
}
public static Statement CreateGetIndexesStatement(string tableName)
{
var writer = new SqlWriter();
writer.Select.
Write(System.Indexes.Aliased.Name).Trim().Comma.
Write(System.Indexes.Aliased.Type).Trim().Comma.
Write(System.Indexes.Aliased.IsUnique).Trim().Comma.
Write(System.Indexes.Aliased.IsPrimaryKey).Trim().Comma.
Write(System.Columns.Aliased.Name).As.QuotedName(SqlWriter.Aliases.ColumnName).Trim().Comma.
Write(System.IndexColumns.Aliased.IsDescendingKey).
From.Write(System.Indexes.TableName).Write(System.Indexes.TableAlias).
Join.Write(System.IndexColumns.TableName).Write(System.IndexColumns.TableAlias).On.
Write(System.Indexes.Aliased.ObjectId).Equal.Write(System.IndexColumns.Aliased.ObjectId).And.
Write(System.Indexes.Aliased.IndexId).Equal.Write(System.IndexColumns.Aliased.IndexId).
Join.Write(System.Columns.TableName).Write(System.Columns.TableAlias).On.
Write(System.IndexColumns.Aliased.ObjectId).Equal.Write(System.Columns.Aliased.ObjectId).And.
Write(System.IndexColumns.Aliased.ColumnId).Equal.Write(System.Columns.Aliased.ColumnId).
Where.Write(System.Indexes.Aliased.ObjectId).Equal.ObjectId(tableName).
OrderBy.Write(System.Indexes.Aliased.Name).Flush();
return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.Multiple));
}
private static string BuildOrderBy(IEnumerable <OrderBy> orderBy, IEntityMapping mapping, IDictionary <string, object> parameters)
{
if (orderBy == null || !orderBy.Any())
{
return("");
}
var writer = new SqlWriter();
orderBy.Select((x, i) => new { Last = i == orderBy.Count() - 1, OrderBy = x }).ToList().ForEach(x =>
{
if (x.OrderBy.Type == OrderBy.SourceType.Operator)
{
writer.Case.When.Write(BuildOperators(x.OrderBy.Operator, mapping, parameters)).Then
.Value(1, SqlDbType.Int).Else.Value(0, SqlDbType.Int).End.Flush();
}
else
{
writer.Write(BuildProjection(x.OrderBy.Projection, mapping, parameters));
}
writer.Do(x.OrderBy.Order == Order.Descending, y => y.Descending.Flush(), y => y.Ascending.Flush()).Do(!x.Last, y => y.Trim().Comma.Flush());
});
return(writer.ToString());
}
public static Statement CreateStatement(Select select, IEntityMapping mapping, IEnumerable <string> projectionOverride = null, bool noLock = false)
{
var sql = new SqlWriter();
var parameters = new Dictionary <string, object>();
var projection = projectionOverride ?? BuildProjection(select, mapping, parameters);
var whereClause = BuildWhereClause(select, mapping, parameters);
var orderByClause = select.HasOrderBy ? BuildOrderBy(select.OrderBy, mapping, parameters) : null;
Action <SqlWriter> writeProjection = x => x.Do(projection != null, y => y.FieldList(z => z.Comma.Flush(), projection), y => y.Wildcard.Flush());
if (select.Any)
{
sql.Select.Cast().Trim().OpenBlock.Trim().Case.When.Exists.OpenBlock.Trim();
}
if (select.From.IsTable || select.HasConditions)
{
sql.Select.Flush();
if (select.First || select.FirstOrDefault)
{
sql.Top(1);
}
else if (select.Single)
{
sql.Top(2);
}
else if (select.HasTop && !select.HasStart)
{
sql.Do(select.TopType == Select.TopValueType.Count, x => x.Top(select.Top), x => x.TopPercent(select.Top));
}
if (select.Count)
{
sql.CountWildcard.Flush();
}
else
{
sql.Do(writeProjection);
}
sql.From.Flush();
if (select.HasStart)
{
sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma.
RowNumber().Over.OpenBlock.Trim().OrderBy.
Do(select.HasOrderBy, x => x.Write(orderByClause), x => x.Do(projection != null, writeProjection, y => y.QuotedName(mapping.Key.GetColumnName()))).Trim().
CloseBlock.As.RowNumberAlias.From.Flush();
}
if (select.HasDuplicates)
{
var duplicateProjection = BuildProjection(select.Duplicates.Distinct, mapping, parameters);
sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma.
RowNumber().Over.OpenBlock.Trim().Partition.By.Write(duplicateProjection).OrderBy.Write(BuildOrderBy(select.Duplicates.OrderBy, mapping, parameters)).
Trim().CloseBlock.As.PartitionAlias.From.Flush();
}
else if (select.HasDistinct)
{
var distinctProjection = BuildProjections(select.Distinct.Select(x => x.Projection), mapping, parameters);
sql.OpenBlock.Trim().Select.Do(writeProjection).Trim().Comma.
RowNumber().Over.OpenBlock.Trim().Partition.By.ExpressionList(z => z.Comma.Flush(), distinctProjection).OrderBy.
Write(BuildOrderBy(select.Distinct.Any(x => x.HasOrder) ? select.Distinct.Where(x => x.HasOrder).Select(x => x.Order) :
select.Distinct.Select(x => new OrderBy {
Type = OrderBy.SourceType.Projection,
Projection = x.Projection,
Order = Order.Ascending
}), mapping, parameters)).Trim().
CloseBlock.As.PartitionAlias.From.Flush();
}
}
switch (select.From.Type)
{
case Data.DataType.Table:
sql.QuotedName(select.From.Table.Name).Write(select.From.Alias);
if (noLock)
{
sql.With(x => x.NoLock.Flush());
}
break;
case Data.DataType.Query:
var first = true;
if (select.HasConditions)
{
sql.OpenBlock.Trim().Flush();
}
foreach (var subQuery in select.From.Queries.Select(x => CreateStatement(x, mapping, projection, noLock)))
{
sql.Do(!first, x => x.Union.Flush()).Write(subQuery.Text).Flush();
parameters.AddRange(subQuery.Parameters);
first = false;
}
if (select.HasConditions)
{
sql.Trim().CloseBlock.As.Write(select.From.Alias).Flush();
}
break;
}
if (select.From.IsTable || select.HasConditions)
{
if (select.HasWhere || select.HasSetOperations)
{
sql.Where.Write(whereClause).Flush();
}
if (select.Randomize)
{
sql.OrderBy.NewId();
}
// The reason why we dont do an order by if there is a start is because the order by is
// already specified in the row number definition. So we dont need to specify it again.
else if (select.HasOrderBy && !select.HasStart && !select.HasDistinct)
{
sql.OrderBy.Write(orderByClause);
}
if (select.HasDuplicates)
{
sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.PartitionAlias.GreaterThan.Value(1, SqlDbType.Int).Flush();
}
else if (select.HasDistinct)
{
sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.PartitionAlias.Equal.Value(1, SqlDbType.Int).Flush();
}
if (select.HasStart)
{
sql.Trim().CloseBlock.As.Write(select.From.Alias).Where.RowNumberAlias.Flush();
if (select.HasTop && select.HasStart)
{
sql.Between(select.Start, select.Start + (select.Top - 1));
}
else
{
sql.GreaterThanOrEqual.Value(select.Start, SqlDbType.Int);
}
}
}
if (select.Any)
{
sql.Trim().CloseBlock.Then.Value(1, SqlDbType.Bit).Else.Value(0, SqlDbType.Bit).End.As.Write(DataTypes.Bit.SqlName).Trim().CloseBlock.Flush();
}
return(new Statement(sql.ToString(), Statement.StatementType.Text, GetResultType(select), parameters));
}