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 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()); }