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 CreateAddNonClusteredIndexStatement(string tableName, params Index.Column[] columns)
{
var indexName = string.Format("IX_{0}_{1}", tableName, string.Join("_", columns.Select(x => x.Name)));
var writer = SqlWriter.CreateWriter().Create.NonClustered.Index.QuotedName(indexName).On.QuotedName(tableName).OpenBlock.Trim();
var first = true;
foreach (var column in columns)
{
if (!first)
{
writer.Trim().Comma.Flush();
}
writer.QuotedName(column.Name).Do(column.Descending, x => x.Descending.Flush(), x => x.Ascending.Flush()).Flush();
first = false;
}
writer.Trim().CloseBlock.Flush();
return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None));
}
public static Statement CreateSharedColumnsStatement(Select select, Table target = null)
{
var sourceTables = select.GetSourceTables().Select(x => x.From.Table.Name).ToList();
if (target != null)
{
sourceTables.Add(target.Name);
}
var targetName = target != null ? target.Name : sourceTables.First();
var statement = CreateColumnsIntersectionStatement(sourceTables);
var writer = WriteSelectColumns(SqlWriter.CreateWriter(), sourceTables).
Join.OpenBlock.Trim().Write(statement.Text).Trim().CloseBlock.SubQueryAlias.On.
SubQueryColumn(System.Columns.Name).Equal.Write(System.Columns.Aliased.Name).And.
Write(System.Columns.Aliased.ObjectId).Equal.ObjectId(targetName);
return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.Multiple));
}
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 CreateTableCreateStatement(string tableName, params Column[] columns)
{
var writer = SqlWriter.CreateWriter().Create.Table.QuotedName(tableName).OpenBlock.Trim();
var first = true;
foreach (var column in columns)
{
if (!first)
{
writer.Trim().Comma.Flush();
}
WriteColumnDefinition(writer, column);
first = false;
}
var primaryKey = columns.FirstOrDefault(x => x.Key != Column.KeyType.None);
if (primaryKey != null)
{
writer.Trim().Comma.PrimaryKeyConstraint(
tableName, primaryKey.Name, primaryKey.Key == Column.KeyType.ClusteredPrimaryKey);
}
writer.Trim().CloseBlock.Flush();
return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None));
}
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));
}
public static SqlWriter WriteSelectColumns(SqlWriter writer, IEnumerable <string> tables = null)
{
return(writer.Select.
Write(System.Columns.Aliased.Name).Trim().Comma.
Write(System.Columns.Aliased.SystemTypeId).Trim().Comma.
Cast(x => x.Case.When.Write(System.Columns.Aliased.SystemTypeId).In.
OpenBlock.Trim().Write(DataTypes.NChar.SqlId).Trim().Comma.Write(DataTypes.NVarChar.SqlId).Trim().Comma.Write(DataTypes.NText.SqlId).Trim().CloseBlock.
Then.Write(System.Columns.Aliased.MaxLength).Divide.Value(2, SqlDbType.Int).Else.Write(System.Columns.Aliased.MaxLength).End.Flush(), typeof(short), null, null, null).
As.QuotedName(System.Columns.MaxLength).Trim().Comma.
Write(System.Columns.Aliased.IsNullable).Trim().Comma.
Write(System.Columns.Aliased.IsIdentity).Trim().Comma.
Cast(x => x.Trim().OpenBlock.Trim().
Case.ObjectDefinition(y => y.Write(System.Columns.Aliased.DefaultObjectId).Flush()).
When.GetDateColumnDefault.Then.True.
When.NewIdColumnDefault.Then.True.
When.NewSequentialIdColumnDefault.Then.True.
Else.False.End.Trim().
CloseBlock.Flush(), typeof(bool), null, null, null).As.QuotedName(SqlWriter.Aliases.IsAutoGenerated).Trim().Comma.
Case.ObjectDefinition(y => y.Write(System.Columns.Aliased.DefaultObjectId).Flush()).
When.GetDateColumnDefault.Then.Null.
When.NewIdColumnDefault.Then.Null.
When.NewSequentialIdColumnDefault.Then.Null.
Else.Replace(x => x.Replace(y => y.ObjectDefinition(z => z.Write(System.Columns.Aliased.DefaultObjectId).Flush()),
y => y.QuotedString("("),
y => y.QuotedString("")),
x => x.QuotedString(")"),
x => x.QuotedString("")).End.
As.QuotedName(SqlWriter.Aliases.DefaultValue).Trim().Comma.
IsNull(x => x.Write(System.Indexes.Aliased.IsPrimaryKey).Flush(), x => x.False.Flush()).As.QuotedName(System.Indexes.IsPrimaryKey).Trim().Comma.
Cast(x => x.Trim().OpenBlock.Trim().
Case.Write(System.Indexes.Aliased.Type).
When.Value(1, SqlDbType.Int).Then.True.
Else.False.End.Trim().CloseBlock.Flush(),
typeof(bool), null, null, null).As.QuotedName(SqlWriter.Aliases.IsPrimaryKeyClustered).Trim().Comma.
Write(System.Columns.Aliased.Precision).Trim().Comma.
Write(System.Columns.Aliased.Scale).Trim().Comma.
Write(System.ComputedColumns.Aliased.Definition).As.QuotedName(SqlWriter.Aliases.Computation).Trim().Comma.
Write(System.ComputedColumns.Aliased.IsPersisted).As.QuotedName(SqlWriter.Aliases.PersistedComputation).
Do(tables != null && tables.Any(), sql => sql.Trim().Comma.Cast(z => z.Case.When.Write(System.Columns.Aliased.SystemTypeId).LessThan.OpenBlock.Trim().
Select.Max(x => x.QuotedName(System.Columns.SystemTypeId)).From.Write(System.Columns.TableName).
Where.QuotedName(System.Columns.Name).Equal.SubQueryColumn(System.Columns.Name).And.
QuotedName(System.Columns.SystemTypeId).In.OpenBlock.Trim().ExpressionList(x => x.Comma.Flush(), x => x.DataTypeId(DataTypes.Char.SqlId),
x => x.DataTypeId(DataTypes.VarChar.SqlId),
x => x.DataTypeId(DataTypes.Text.SqlId),
x => x.DataTypeId(DataTypes.NChar.SqlId),
x => x.DataTypeId(DataTypes.NVarChar.SqlId),
x => x.DataTypeId(DataTypes.NText.SqlId)).Trim().CloseBlock.And.
QuotedName(System.Columns.ObjectId).In.OpenBlock.Trim().ExpressionList(x => x.Comma.Flush(),
tables.Select <string, Action <SqlWriter> >(x => y => y.ObjectId(x))).Trim().CloseBlock.Trim().CloseBlock.
Then.True.Else.False.End.Flush(), typeof(bool), null, null, null).As.QuotedName(SqlWriter.Aliases.IsNarrowing)).
From.OpenBlock.OpenBlock.OpenBlock.Write(System.Columns.TableName).Write(System.Columns.TableAlias).
Left.Join.Write(System.IndexColumns.TableName).Write(System.IndexColumns.TableAlias).On.
Write(System.Columns.Aliased.ColumnId).Equal.Write(System.IndexColumns.Aliased.ColumnId).And.
Write(System.Columns.Aliased.ObjectId).Equal.Write(System.IndexColumns.Aliased.ObjectId).CloseBlock.
Left.Join.Write(System.Indexes.TableName).Write(System.Indexes.TableAlias).On.
Write(System.Indexes.Aliased.IndexId).Equal.Write(System.IndexColumns.Aliased.IndexId).And.
Write(System.Indexes.Aliased.ObjectId).Equal.Write(System.IndexColumns.Aliased.ObjectId).CloseBlock.
Left.Join.Write(System.ComputedColumns.TableName).Write(System.ComputedColumns.TableAlias).On.
Write(System.Columns.Aliased.ColumnId).Equal.Write(System.ComputedColumns.Aliased.ColumnId).And.
Write(System.Columns.Aliased.ObjectId).Equal.Write(System.ComputedColumns.Aliased.ObjectId).CloseBlock);
}
public static Statement CreateRemoveNonClusteredIndexStatement(string tableName, string indexName)
{
var writer = SqlWriter.CreateWriter().IfIndexExists(tableName, indexName).Drop.Index.QuotedName(indexName).On.QuotedName(tableName);
return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None));
}
public static Statement CreateRemoveColumnStatement(string tableName, string columnName)
{
var writer = SqlWriter.CreateWriter().IfColumnExists(tableName, columnName).Alter.Table.QuotedName(tableName).Drop.Column.QuotedName(columnName);
return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None));
}
private static void WriteColumnDefinition(SqlWriter writer, Column column)
{
writer.ColumnDefinition(column.Name, column.Type, column.SqlType, column.Length, column.Precision,
column.Scale, column.Key != Column.KeyType.None, column.IsIdentity, column.IsNullable,
column.IsAutoGenerated, column.Computation, column.ComputationPersisted, column.DefaultValue);
}
public static Statement CreateDeleteTableStatement(string tableName)
{
var writer = SqlWriter.CreateWriter().If.TableExists(tableName).Drop.Table.QuotedName(tableName);
return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.None));
}
public static Statement CreateProcedureExistsStatement(string name)
{
var writer = SqlWriter.CreateWriter().Select.ProcedureExistsValue(name);
return(new Statement(writer.ToString(), Statement.StatementType.Text, Statement.ResultType.Scalar));
}
