public IScriptBlock CreateBlock(Options options)
{
SqlBlock block = new SqlBlock(BlockType.Create, this.TableName, this.Type);
// Header
block.AppendLine($"IF OBJECT_ID(N'{this.TableName}', N'U') IS NULL");
block.AppendLine($" CREATE TABLE [{this.TableName}] (");
// Add each column in the table
foreach (ColumnObject column in this.Columns)
{
block.Append(" ");
block.Append(column.GetColumnDefinition());
block.AppendLine(",");
}
// Add the PK constraint
ColumnObject[] pkColumns = this.Columns.Where(col => col.IsPrimaryKey).ToArray();
if (pkColumns.HasValues())
{
block.Append(" Constraint [" + pkColumns.First().IndexName + "] PRIMARY KEY NONCLUSTERED (");
block.AppendLine(String.Join(", ", pkColumns.Select(col => col.ColumnName)) + ")");
}
// TODO: Add constraints
block.Append(" );");
return(block);
}
public IScriptBlock DropBlock(Options options)
{
SqlBlock block = new SqlBlock(BlockType.Drop, this.FunctionName, this.Type);
block.AppendLine("IF OBJECT_ID(N'" + this.FunctionName + "', N'" + this.TypeString + " ') IS NOT NULL");
block.Append("DROP FUNCTION " + this.FullyQualifiedName);
return(block);
}
public IScriptBlock DropBlock(Options options)
{
SqlBlock block = new SqlBlock(BlockType.Drop, this.ProcedureName, this.Type);
block.AppendLine("IF OBJECT_ID(N'" + this.ProcedureName + "', N'P') IS NOT NULL");
block.Append("DROP PROCEDURE " + this.FullyQualifiedName);
return(block);
}
public IScriptBlock DropBlock(Options options)
{
SqlBlock block = new SqlBlock(BlockType.Drop, this.ViewName, this.Type);
block.AppendLine("IF OBJECT_ID(N'" + this.ViewName + "', N'V') IS NOT NULL");
block.Append("DROP VIEW " + this.FullyQualifiedName);
return(block);
}
internal override SqlBlock VisitBlock(SqlBlock block)
{
SqlBlock nb = new SqlBlock(block.SourceExpression);
foreach (SqlStatement stmt in block.Statements)
{
nb.Statements.Add((SqlStatement)this.Visit(stmt));
}
return(nb);
}
private IScriptBlock CreateFunction()
{
SqlBlock block = new SqlBlock(BlockType.Drop, this.FunctionName, this.Type);
// If it exists, drop it
block.AppendLine("IF OBJECT_ID(N'" + this.FunctionName + "', N'" + this.TypeString + "') IS NOT NULL");
block.AppendLine(" DROP FUNCTION " + this.FullyQualifiedName);
block.AppendLine("GO");
block.Append(this.FunctionDefinition);
return(block);
}
internal string[] FormatBlock(SqlBlock block, bool isDebug) {
List<string> results = new List<string>(block.Statements.Count);
for (int i = 0, n = block.Statements.Count; i < n; i++) {
SqlStatement stmt = block.Statements[i];
SqlSelect select = stmt as SqlSelect;
if (select != null && select.DoNotOutput) {
continue;
} else {
results.Add(this.Format(stmt, isDebug));
}
}
return results.ToArray();
}
private IScriptBlock CreateViewBlock()
{
SqlBlock block = new SqlBlock(BlockType.Drop, this.ViewName, this.Type);
// If it exists, drop it
block.AppendLine("IF OBJECT_ID(N'" + this.ViewName + "', N'V') IS NOT NULL");
block.AppendLine(" DROP VIEW " + this.FullyQualifiedName);
block.AppendLine("GO");
block.Append(this.ViewDefinition);
return(block);
}
private IScriptBlock CreateProcedure()
{
SqlBlock block = new SqlBlock(BlockType.Drop, this.ProcedureName, this.Type);
// If it exists, drop it
block.AppendLine("IF OBJECT_ID(N'" + this.ProcedureName + "', N'P') IS NOT NULL");
block.AppendLine(" DROP PROCEDURE " + this.FullyQualifiedName);
block.AppendLine("GO");
block.Append(this.ProcedureDefinition);
return(block);
}
internal string[] FormatBlock(SqlBlock block, bool isDebug)
{
List <string> results = new List <string>(block.Statements.Count);
for (int i = 0, n = block.Statements.Count; i < n; i++)
{
SqlStatement stmt = block.Statements[i];
SqlSelect select = stmt as SqlSelect;
if (select != null && select.DoNotOutput)
{
continue;
}
results.Add(this.Format(stmt, isDebug));
}
return(results.ToArray());
}
internal ReadOnlyCollection<ReadOnlyCollection<SqlParameterInfo>> ParameterizeBlock(SqlBlock block) {
SqlParameterInfo rowStatus =
new SqlParameterInfo(
new SqlParameter(typeof(int), typeProvider.From(typeof(int)), "@ROWCOUNT", block.SourceExpression)
);
List<ReadOnlyCollection<SqlParameterInfo>> list = new List<ReadOnlyCollection<SqlParameterInfo>>();
for (int i = 0, n = block.Statements.Count; i < n; i++) {
SqlNode statement = block.Statements[i];
List<SqlParameterInfo> parameters = this.ParameterizeInternal(statement);
if (i > 0) {
parameters.Add(rowStatus);
}
list.Add(parameters.AsReadOnly());
}
return list.AsReadOnly();
}
//每写完一句，用分号分隔。
internal override SqlBlock VisitBlock(SqlBlock block)
{
int num = 0;
int count = block.Statements.Count;
while (num < count)
{
this.Visit(block.Statements[num]);
if (num < (count - 1))
{
var select = block.Statements[num + 1] as SqlSelect;
if ((select == null) || !select.DoNotOutput)
{
sb.Append(";");
this.NewLine();
this.NewLine();
}
}
num++;
}
return(block);
}
internal override SqlBlock VisitBlock(SqlBlock block) {
for (int i = 0, n = block.Statements.Count; i < n; i++) {
this.Visit(block.Statements[i]);
if (i < n - 1) {
SqlSelect select = block.Statements[i+1] as SqlSelect;
if (select == null || !select.DoNotOutput) {
this.NewLine();
this.NewLine();
}
}
}
return block;
}
internal virtual SqlBlock VisitBlock(SqlBlock b) {
for (int i = 0, n = b.Statements.Count; i < n; i++) {
b.Statements[i] = (SqlStatement)this.Visit(b.Statements[i]);
}
return b;
}
ReadOnlyCollection <ReadOnlyCollection <SqlParameterInfo> > ISqlParameterizer.ParameterizeBlock(SqlBlock block)
{
//var item = new SqlParameterInfo(new SqlParameter(typeof(int), typeProvider.From(typeof(int)),
// "@ROWCOUNT", block.SourceExpression));
var list = new List <ReadOnlyCollection <SqlParameterInfo> >();
int num = 0;
int count = block.Statements.Count;
while (num < count)
{
SqlNode node = block.Statements[num];
List <SqlParameterInfo> list2 = this.ParameterizeInternal(node);
if (num > 0)
{
//list2.Add(item);
}
list.Add(list2.AsReadOnly());
num++;
}
return(list.AsReadOnly());
}
internal override SqlBlock VisitBlock(SqlBlock block) {
SqlBlock nb = new SqlBlock(block.SourceExpression);
foreach (SqlStatement stmt in block.Statements) {
nb.Statements.Add((SqlStatement)this.Visit(stmt));
}
return nb;
}
private SqlStatement VisitUpdate(Expression item, LambdaExpression check, LambdaExpression resultSelector) {
if (item == null) {
throw Error.ArgumentNull("item");
}
MetaTable metaTable = this.services.Model.GetTable(item.Type);
Expression source = this.services.Context.GetTable(metaTable.RowType.Type).Expression;
Type rowType = metaTable.RowType.Type;
bool saveAllowDeferred = this.allowDeferred;
this.allowDeferred = false;
try {
Expression seq = source;
// construct identity predicate based on supplied item
ParameterExpression p = Expression.Parameter(rowType, "p");
LambdaExpression idPredicate = Expression.Lambda(Expression.Equal(p, item), p);
// combine predicate and check expression into single find predicate
LambdaExpression findPredicate = idPredicate;
if (check != null) {
findPredicate = Expression.Lambda(Expression.And(Expression.Invoke(findPredicate, p), Expression.Invoke(check, p)), p);
}
seq = Expression.Call(typeof(Enumerable), "Where", new Type[] { rowType }, seq, findPredicate);
// source 'query' is based on table + find predicate
SqlSelect ss = new RetypeCheckClause().VisitSelect(this.VisitSequence(seq));
// construct update assignments from 'item' info
List<SqlAssign> assignments = new List<SqlAssign>();
ConstantExpression conItem = item as ConstantExpression;
if (conItem == null) {
throw Error.UpdateItemMustBeConstant();
}
if (conItem.Value == null) {
throw Error.ArgumentNull("item");
}
// get changes from data services to construct update command
Type entityType = conItem.Value.GetType();
MetaType metaType = this.services.Model.GetMetaType(entityType);
ITable table = this.services.Context.GetTable(metaType.InheritanceRoot.Type);
foreach (ModifiedMemberInfo mmi in table.GetModifiedMembers(conItem.Value)) {
MetaDataMember mdm = metaType.GetDataMember(mmi.Member);
assignments.Add(
new SqlAssign(
sql.Member(ss.Selection, mmi.Member),
new SqlValue(mdm.Type, this.typeProvider.From(mdm.Type), mmi.CurrentValue, true, source),
source
));
}
SqlUpdate upd = new SqlUpdate(ss, assignments, source);
if (resultSelector == null) {
return upd;
}
SqlSelect select = null;
// build select to return result
seq = source;
seq = Expression.Call(typeof(Enumerable), "Where", new Type[] { rowType }, seq, idPredicate);
seq = Expression.Call(typeof(Enumerable), "Select", new Type[] { rowType, resultSelector.Body.Type }, seq, resultSelector);
select = this.VisitSequence(seq);
select.Where = sql.AndAccumulate(
sql.Binary(SqlNodeType.GT, this.GetRowCountExpression(), sql.ValueFromObject(0, false, this.dominatingExpression)),
select.Where
);
// combine update & select into statement block
SqlBlock block = new SqlBlock(source);
block.Statements.Add(upd);
block.Statements.Add(select);
return block;
}
finally {
this.allowDeferred = saveAllowDeferred;
}
}
private SqlStatement VisitInsert(Expression item, LambdaExpression resultSelector) {
if (item == null) {
throw Error.ArgumentNull("item");
}
this.dominatingExpression = item;
MetaTable metaTable = this.services.Model.GetTable(item.Type);
Expression source = this.services.Context.GetTable(metaTable.RowType.Type).Expression;
MetaType itemMetaType = null;
SqlNew sqlItem = null;
// construct insert assignments from 'item' info
ConstantExpression conItem = item as ConstantExpression;
if (conItem == null) {
throw Error.InsertItemMustBeConstant();
}
if (conItem.Value == null) {
throw Error.ArgumentNull("item");
}
// construct insert based on constant value
List<SqlMemberAssign> bindings = new List<SqlMemberAssign>();
itemMetaType = metaTable.RowType.GetInheritanceType(conItem.Value.GetType());
SqlExpression sqlExprItem = sql.ValueFromObject(conItem.Value, true, source);
foreach (MetaDataMember mm in itemMetaType.PersistentDataMembers) {
if (!mm.IsAssociation && !mm.IsDbGenerated && !mm.IsVersion) {
bindings.Add(new SqlMemberAssign(mm.Member, sql.Member(sqlExprItem, mm.Member)));
}
}
ConstructorInfo cons = itemMetaType.Type.GetConstructor(Type.EmptyTypes);
System.Diagnostics.Debug.Assert(cons != null);
sqlItem = sql.New(itemMetaType, cons, null, null, bindings, item);
SqlTable tab = sql.Table(metaTable, metaTable.RowType, this.dominatingExpression);
SqlInsert sin = new SqlInsert(tab, sqlItem, item);
if (resultSelector == null) {
return sin;
}
else {
MetaDataMember id = itemMetaType.DBGeneratedIdentityMember;
bool isDbGenOnly = false;
if (id != null) {
isDbGenOnly = this.IsDbGeneratedKeyProjectionOnly(resultSelector.Body, id);
if (id.Type == typeof(Guid) && (this.converterStrategy & ConverterStrategy.CanOutputFromInsert) != 0) {
sin.OutputKey = new SqlColumn(id.Type, sql.Default(id), id.Name, id, null, this.dominatingExpression);
if (!isDbGenOnly) {
sin.OutputToLocal = true;
}
}
}
SqlSelect result = null;
SqlSelect preResult = null;
SqlAlias tableAlias = new SqlAlias(tab);
SqlAliasRef tableAliasRef = new SqlAliasRef(tableAlias);
System.Diagnostics.Debug.Assert(resultSelector.Parameters.Count == 1);
this.map.Add(resultSelector.Parameters[0], tableAliasRef);
SqlExpression projection = this.VisitExpression(resultSelector.Body);
// build select to return result
SqlExpression pred = null;
if (id != null) {
pred = sql.Binary(
SqlNodeType.EQ,
sql.Member(tableAliasRef, id.Member),
this.GetIdentityExpression(id, sin.OutputKey != null)
);
}
else {
SqlExpression itemExpression = this.VisitExpression(item);
pred = sql.Binary(SqlNodeType.EQ2V, tableAliasRef, itemExpression);
}
result = new SqlSelect(projection, tableAlias, resultSelector);
result.Where = pred;
// Since we're only projecting back a single generated key, we can
// optimize the query to a simple selection (e.g. SELECT @@IDENTITY)
// rather than selecting back from the table.
if (id != null && isDbGenOnly) {
if (sin.OutputKey == null) {
SqlExpression exp = this.GetIdentityExpression(id, false);
if (exp.ClrType != id.Type) {
ProviderType sqlType = sql.Default(id);
exp = sql.ConvertTo(id.Type, sqlType, exp);
}
// The result selector passed in was bound to the table -
// we need to rebind to the single result as an array projection
ParameterExpression p = Expression.Parameter(id.Type, "p");
Expression[] init = new Expression[1] { Expression.Convert(p, typeof(object)) };
NewArrayExpression arrExp = Expression.NewArrayInit(typeof(object), init);
LambdaExpression rs = Expression.Lambda(arrExp, p);
this.map.Add(p, exp);
SqlExpression proj = this.VisitExpression(rs.Body);
preResult = new SqlSelect(proj, null, rs);
}
else {
// case handled in formatter automatically
}
result.DoNotOutput = true;
}
// combine insert & result into block
SqlBlock block = new SqlBlock(this.dominatingExpression);
block.Statements.Add(sin);
if (preResult != null) {
block.Statements.Add(preResult);
}
block.Statements.Add(result);
return block;
}
}
// TODO Indexes, Constraints, etc
public IEnumerable <IScriptBlock> AlterTo(IDatabaseObject obj, Options options)
{
if (obj is TableObject == false)
{
throw new InvalidOperationException("Cannot alter table to an object that is not a table");
}
var blocks = new List <IScriptBlock>();
var otherTable = obj as TableObject;
List <ColumnObject> modifiedColumns = new List <ColumnObject>();
List <ColumnObject> additionalColumns = new List <ColumnObject>();
// Start off assuming all columns will be removed, and we take items out of the list as we compare
List <ColumnObject> removedColumns = new List <ColumnObject>(this.Columns);
// Find all the modified and additional columns
foreach (ColumnObject columnA in otherTable.Columns)
{
ColumnObject equivalentColumn = null;
int loopLength = removedColumns.Count;
for (int i = 0; i < loopLength; i++)
{
ColumnObject columnB = removedColumns[i];
// If a column with the same name exists, then the column is not being removed anymore
// and we need to do a deeper check on the columns to see if they are equal
if (columnA.FullyQualifiedName == columnB.FullyQualifiedName)
{
equivalentColumn = columnB;
removedColumns[i] = removedColumns.Last();
removedColumns.RemoveAt(loopLength - 1);
break;
}
}
// If there was no equivalent object, it is a new column so end here
if (equivalentColumn == null)
{
additionalColumns.Add(columnA);
continue;
}
// If the columns have the same name but are not complete equal, it has been modified
if (!columnA.Equals(equivalentColumn, options))
{
modifiedColumns.Add(columnA);
}
}
// Drop columns that should no longer be on the table
if (removedColumns.HasItems())
{
var block = new SqlBlock(BlockType.Alter, this.TableName, ObjectType.Column);
foreach (var column in removedColumns)
{
block.Append("ALTER TABLE ");
block.Append(this.FullyQualifiedName);
block.Append(" DROP COLUMN ");
block.AppendLine(column.ColumnName);
block.AppendLine("GO");
}
blocks.Add(block);
}
// Add new columns
if (additionalColumns.HasItems())
{
var block = new SqlBlock(BlockType.Alter, this.TableName, ObjectType.Column);
foreach (var column in additionalColumns)
{
block.Append("ALTER TABLE ");
block.Append(this.FullyQualifiedName);
block.Append(" ADD ");
block.AppendLine(column.GetColumnDefinition());
block.AppendLine("GO");
}
blocks.Add(block);
}
// Modify existing columns
if (modifiedColumns.HasItems())
{
var block = new SqlBlock(BlockType.Alter, this.TableName, ObjectType.Column);
foreach (var column in modifiedColumns)
{
block.Append("ALTER TABLE ");
block.Append(this.FullyQualifiedName);
block.Append(" ALTER COLUMN ");
block.AppendLine(column.GetColumnDefinition());
block.AppendLine("GO");
}
blocks.Add(block);
}
return(blocks);
}
internal virtual SqlBlock VisitBlock(SqlBlock b) {
for (int i = 0, n = b.Statements.Count; i < n; i++) {
b.Statements[i] = (SqlStatement)this.Visit(b.Statements[i]);
}
return b;
}
internal ReadOnlyCollection <ReadOnlyCollection <SqlParameterInfo> > ParameterizeBlock(SqlBlock block)
{
SqlParameterInfo rowStatus =
new SqlParameterInfo(
new SqlParameter(typeof(int), _typeProvider.From(typeof(int)), "@ROWCOUNT", block.SourceExpression)
);
List <ReadOnlyCollection <SqlParameterInfo> > list = new List <ReadOnlyCollection <SqlParameterInfo> >();
for (int i = 0, n = block.Statements.Count; i < n; i++)
{
SqlNode statement = block.Statements[i];
List <SqlParameterInfo> parameters = this.ParameterizeInternal(statement);
if (i > 0)
{
parameters.Add(rowStatus);
}
list.Add(parameters.AsReadOnly());
}
return(list.AsReadOnly());
}
