private void AddCreateUniqueConstraintBatch(Index index, QuoteType quoteType) { List <ColumnWithSortOrder> columns = new List <ColumnWithSortOrder>(); foreach (var member in index.Members) { Column column = index.Parent.Columns.FirstOrDefault(c => c.Name == member.Column); columns.Add( ScriptFactory.ColumnWithSortOrder( ScriptFactory.ColumnReferenceExpression( ScriptFactory.MultiPartIdentifier( ScriptFactory.Identifier(member.Column, quoteType)), ColumnType.Regular), SortOrder.Ascending)); } UniqueConstraintDefinition uniqueConstraint = ScriptFactory.UniqueConstraintDefinition( ScriptFactory.Identifier(index.Name, quoteType), false, index.IsClustered, null, null, null, columns ); AddBatch(ScriptFactory.AlterTableAddTableElement( ScriptFactory.TableDefinition( null, null, new List <ConstraintDefinition> { uniqueConstraint }, null), Generator.GenerateSchemaObjectName(index.Parent, quoteType))); }
public override void ExplicitVisit(UniqueConstraintDefinition node) { if (node.ConstraintIdentifier != null) { _buffer.Append("constraint "); node.ConstraintIdentifier.Accept(this); } if (node.IsPrimaryKey) { _buffer.Append(" primary key ("); } else { _buffer.Append(" unique ("); } for (int index = 0; index < node.Columns.Count; ++index) { node.Columns[index].Accept(this); if (index < node.Columns.Count - 1) { _buffer.Append(", "); } } _buffer.Append(")"); }
protected override object InternalVisit(UniqueConstraintDefinition node) { Action <Table, Column> applier = (table, column) => { var isPK = node.IsPrimaryKey; /* * INFO(Richo): We can get the columns from two places: the "column" arg or the node's "Columns" property. * The node's property takes precedence, but it could come empty if the constraint was specified inline. * In those cases we rely on the "column" argument. */ Column[] columns; if (node.Columns == null || node.Columns.Count() == 0) { columns = new[] { column }; } else { columns = node.Columns .Select(c => table.GetColumn(Visit <string[]>(c.Column.MultiPartIdentifier).Last())) .ToArray(); } var name = node.ConstraintIdentifier?.Value; if (string.IsNullOrWhiteSpace(name)) { string id = string.Join("", Guid.NewGuid().ToByteArray().Select(b => b.ToString("X2"))); if (isPK) { name = string.Format("PK_{0}_{1}", table.TableName, id); } else { name = string.Format("UC_{0}_{1}_{2}", table.TableName, string.Join("_", columns.Select(c => c.ColumnName)), id); } } if (isPK && table.PrimaryKey.Length > 0) { var msg = string.Format("Cannot add multiple PRIMARY KEY constraints to table '{0}'", table.TableName); throw new ArgumentException(msg); } Database.AddConstraint(new UniqueConstraint(name, columns, isPK)); }; return(applier); }
private static bool NamePrimaryKey(CreateTableStatement statement) { var columnWithPrimaryKey = statement.Definition.ColumnDefinitions.FirstOrDefault(c => c.Constraints.Any(p => p is UniqueConstraintDefinition)); if (columnWithPrimaryKey == null) { return(false); } var originalConstraint = columnWithPrimaryKey.Constraints.FirstOrDefault(c => c as UniqueConstraintDefinition != null && ((UniqueConstraintDefinition)c).IsPrimaryKey) as UniqueConstraintDefinition; if (originalConstraint == null) { return(false); } var newConstraint = new UniqueConstraintDefinition(); newConstraint.Clustered = originalConstraint.Clustered; newConstraint.IsPrimaryKey = true; foreach (var o in originalConstraint.IndexOptions) { newConstraint.IndexOptions.Add(o); } newConstraint.IndexType = originalConstraint.IndexType; var referencedColumn = new ColumnWithSortOrder(); referencedColumn.Column = new ColumnReferenceExpression(); referencedColumn.Column.MultiPartIdentifier = new SchemaObjectName(); referencedColumn.Column.MultiPartIdentifier.Identifiers.Add(columnWithPrimaryKey.ColumnIdentifier); newConstraint.Columns.Add(referencedColumn); newConstraint.ConstraintIdentifier = new Identifier(); newConstraint.ConstraintIdentifier.Value = ReplaceTemplateValues(SavedSettings.Get().PrimaryKeyName, statement.SchemaObjectName.BaseIdentifier.Value, referencedColumn.Column.MultiPartIdentifier.Identifiers.Last().Value); newConstraint.ConstraintIdentifier.QuoteType = QuoteType.SquareBracket; statement.Definition.TableConstraints.Add(newConstraint); columnWithPrimaryKey.Constraints.Remove(originalConstraint); return(true); }
public override void Visit(UniqueConstraintDefinition node) { this.action(node); }
public override void ExplicitVisit(UniqueConstraintDefinition fragment) { _fragments.Add(fragment); }
private static bool NamePrimaryKey(CreateTableStatement statement) { var columnWithPrimaryKey = statement.Definition.ColumnDefinitions.FirstOrDefault( c => c.Constraints.Any(p => p is UniqueConstraintDefinition)); if (columnWithPrimaryKey == null) { return false; } var originalConstraint = columnWithPrimaryKey.Constraints.FirstOrDefault( c => c as UniqueConstraintDefinition != null && ((UniqueConstraintDefinition) c).IsPrimaryKey) as UniqueConstraintDefinition; if (originalConstraint == null) { return false; } var newConstraint = new UniqueConstraintDefinition(); newConstraint.Clustered = originalConstraint.Clustered; newConstraint.IsPrimaryKey = true; foreach (var o in originalConstraint.IndexOptions) newConstraint.IndexOptions.Add(o); newConstraint.IndexType = originalConstraint.IndexType; var referencedColumn = new ColumnWithSortOrder(); referencedColumn.Column = new ColumnReferenceExpression(); referencedColumn.Column.MultiPartIdentifier = new SchemaObjectName(); referencedColumn.Column.MultiPartIdentifier.Identifiers.Add(columnWithPrimaryKey.ColumnIdentifier); newConstraint.Columns.Add(referencedColumn); newConstraint.ConstraintIdentifier = new Identifier(); newConstraint.ConstraintIdentifier.Value = ReplaceTemplateValues(SavedSettings.Get().PrimaryKeyName, statement.SchemaObjectName.BaseIdentifier.Value, referencedColumn.Column.MultiPartIdentifier.Identifiers.Last().Value); newConstraint.ConstraintIdentifier.QuoteType = QuoteType.SquareBracket; statement.Definition.TableConstraints.Add(newConstraint); columnWithPrimaryKey.Constraints.Remove(originalConstraint); return true; }
public CreateTableStatement Convert(CTable table) { var parser = new TSql120Parser(false); string[] parts = { table.Schema.SchemaName, table.TableName }; var createTable = new CreateTableStatement(); ///set schema and table name createTable.SchemaObjectName = new SchemaObjectName(); createTable.SchemaObjectName.Identifiers.Add(new Identifier { Value = table.Schema.SchemaName }); createTable.SchemaObjectName.Identifiers.Add(new Identifier { Value = $"[{table.TableName}]" }); //add columns createTable.Definition = new TableDefinition(); foreach (var col in table.Column) { if (col.ColumnType == DbType.Object) { continue; } var dataType = new SqlDataTypeReference { SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(col.ColumnTypeRaw) }; if (col.ColumnLength > 0) { dataType.Parameters.Add(new IntegerLiteral { Value = col.ColumnLength.ToString() }); } var column = new ColumnDefinition { ColumnIdentifier = new Identifier { Value = col.ColumnName.WrapReservedAndSnakeCase(DataStoreTypes.SqlServer, table.ConvertToSnakeCase) }, DataType = dataType }; if (!string.IsNullOrEmpty(col.DefaultValue)) { IList <ParseError> errors; //var defaultValueText = "CONVERT([char](32), REPLACE(CONVERT([char](36),NEWID()),'-',''))"; var scriptDefault = parser.ParseExpression(new StringReader(col.DefaultValue), out errors); column.DefaultConstraint = new DefaultConstraintDefinition { Expression = scriptDefault }; } if (col.IsIdentity) { column.IdentityOptions = new IdentityOptions { IdentitySeed = new IntegerLiteral { Value = col.ColumnType == System.Data.DbType.Byte ? "1" : "1000" }, IdentityIncrement = new IntegerLiteral { Value = "1" } } } ; column.Constraints.Add(new NullableConstraintDefinition { Nullable = col.IsNullable }); if (col.IsUnique) { column.Constraints.Add(new UniqueConstraintDefinition()); } if (col.IsIndexed) { column.Index = new IndexDefinition { Name = new Identifier { Value = $"IX_{col.ColumnName}" }, IndexType = new IndexType { IndexTypeKind = IndexTypeKind.NonClustered } } } ; createTable.Definition.ColumnDefinitions.Add(column); } //add PK's var pks = table.GetPrimaryKeyColumns(); if (pks.Count > 0) { var primaryKeyConstraint = new UniqueConstraintDefinition { IsPrimaryKey = true }; primaryKeyConstraint.Clustered = true; // todo: use metadata foreach (var pk in table.GetPrimaryKeyColumns()) { var columnIdentifier = new MultiPartIdentifier(); columnIdentifier.Identifiers.Add(new Identifier { Value = pk.ColumnName }); var columnRefExpression = new ColumnReferenceExpression { MultiPartIdentifier = columnIdentifier }; var columnWithSortOrder = new ColumnWithSortOrder { Column = columnRefExpression }; primaryKeyConstraint.Columns.Add(columnWithSortOrder); } var pkConstraintName = $"PK_{table.Schema.SchemaName}_{table.TableName}_{pks.First().ColumnName}"; primaryKeyConstraint.ConstraintIdentifier = new Identifier { Value = pkConstraintName }; createTable.Definition.TableConstraints.Add(primaryKeyConstraint); } //add foreign keys foreach (var col in table.Column) { if (col.ForeignKeyColumn == null || col.ForeignKeyColumn.Count == 0) { continue; } foreach (var fk in col.ForeignKeyColumn) { var fkConstraintName = $"FK_{table.TableName}_{fk.Table.TableName}"; var foreignKeyConstraint = new ForeignKeyConstraintDefinition(); foreignKeyConstraint.ConstraintIdentifier = new Identifier { Value = fkConstraintName }; foreignKeyConstraint.Columns.Add(new Identifier { Value = col.ColumnName }); foreignKeyConstraint.ReferenceTableName = new SchemaObjectName(); foreignKeyConstraint.ReferenceTableName.Identifiers.Add(new Identifier { Value = fk.Table.Schema.SchemaName }); foreignKeyConstraint.ReferenceTableName.Identifiers.Add(new Identifier { Value = fk.Table.TableName }); foreignKeyConstraint.ReferencedTableColumns.Add(new Identifier { Value = fk.ColumnName }); createTable.Definition.TableConstraints.Add(foreignKeyConstraint); } } return(createTable); } } }
public void ProcessCreateTable(CreateTableStatement TblStmt) { bool isTemp = TblStmt.SchemaObjectName.BaseIdentifier.Value.StartsWith("#") || TblStmt.SchemaObjectName.BaseIdentifier.Value.StartsWith("@"); if (TblStmt.SchemaObjectName.SchemaIdentifier == null && !isTemp ) { _smells.SendFeedBack(27, TblStmt); } { foreach (ColumnDefinition colDef in TblStmt.Definition.ColumnDefinitions) { _smells.ProcessTsqlFragment(colDef); } } if (isTemp) { foreach (ConstraintDefinition constDef in TblStmt.Definition.TableConstraints) { if (constDef.ConstraintIdentifier != null) { } switch (FragmentTypeParser.GetFragmentType(constDef)) { case "UniqueConstraintDefinition": UniqueConstraintDefinition unqConst = (UniqueConstraintDefinition)constDef; if (unqConst.IsPrimaryKey) { _smells.SendFeedBack(38, constDef); } break; } } foreach (ColumnDefinition colDef in TblStmt.Definition.ColumnDefinitions) { if (colDef.DefaultConstraint != null && colDef.DefaultConstraint.ConstraintIdentifier != null) { _smells.SendFeedBack(39, colDef); } foreach (ConstraintDefinition constDef in colDef.Constraints) { if (constDef.ConstraintIdentifier != null) { } switch (FragmentTypeParser.GetFragmentType(constDef)) { case "CheckConstraintDefinition": CheckConstraintDefinition chkConst = (CheckConstraintDefinition)constDef; if (chkConst.ConstraintIdentifier != null) { _smells.SendFeedBack(40, chkConst);; } break; } } } } }