public void AddCreateForeignKeyBatch(ForeignKey key, QuoteType quoteType = QuoteType.NotQuoted) { var first = key.ForeignKeyColumns.FirstOrDefault(); var pkTable = key.Parent.Parent.Tables.FirstOrDefault(t => t.Name == key.PkTable); ForeignKeyConstraintDefinition uniqueConstraint = ScriptFactory.ForeignKeyConstraintDefinition( ScriptFactory.Identifier(key.Name, quoteType), false, Generator.GenerateSchemaObjectName(pkTable, quoteType), DeleteUpdateAction.NotSpecified, DeleteUpdateAction.NotSpecified, key.ForeignKeyColumns.Select(fkc => ScriptFactory.Identifier(fkc.FkColumn, quoteType)), key.ForeignKeyColumns.Select(fkc => ScriptFactory.Identifier(fkc.PkColumn, quoteType)) ); AddBatch(ScriptFactory.AlterTableAddTableElement( ScriptFactory.TableDefinition( null, null, new List <ConstraintDefinition> { uniqueConstraint }, null), Generator.GenerateSchemaObjectName(key.Parent, quoteType))); }
public override void ExplicitVisit(ForeignKeyConstraintDefinition node) { if (node.ConstraintIdentifier != null) { node.ConstraintIdentifier.Accept(this); } _buffer.Append(" foreign key ("); for (int index = 0; index < node.Columns.Count; ++index) { node.Columns[index].Accept(this); if (index < node.Columns.Count - 1) { _buffer.Append(", "); } } _buffer.Append(") references "); node.ReferenceTableName.Accept(this); _buffer.Append(" ("); for (int index = 0; index < node.ReferencedTableColumns.Count; ++index) { node.ReferencedTableColumns[index].Accept(this); if (index < node.ReferencedTableColumns.Count - 1) { _buffer.Append(", "); } } _buffer.Append(")"); }
public void SetUp() { _storageProviderID = "DefaultStorageProvider"; _storageProviderDefinition = new UnitTestStorageProviderStubDefinition(_storageProviderID); _infrastructureStoragePropertyDefinitionProviderMock = MockRepository.GenerateStrictMock <IInfrastructureStoragePropertyDefinitionProvider>(); _storagePropertyDefinitionResolverMock = MockRepository.GenerateStrictMock <IStoragePropertyDefinitionResolver>(); _storageNameProviderMock = MockRepository.GenerateStrictMock <IStorageNameProvider>(); _foreignKeyConstraintDefinitionFactoryMock = MockRepository.GenerateStrictMock <IForeignKeyConstraintDefinitionFactory>(); _factory = new RdbmsStorageEntityDefinitionFactory( _infrastructureStoragePropertyDefinitionProviderMock, _foreignKeyConstraintDefinitionFactoryMock, _storagePropertyDefinitionResolverMock, _storageNameProviderMock, _storageProviderDefinition); _testModel = new RdbmsPersistenceModelLoaderTestHelper(); _fakeObjectIDStorageProperty = ObjectIDStoragePropertyDefinitionObjectMother.ObjectIDProperty; _fakeStorageProperty1 = SimpleStoragePropertyDefinitionObjectMother.CreateStorageProperty("Test1"); _fakeTimestampStorageProperty = SimpleStoragePropertyDefinitionObjectMother.TimestampProperty; _fakeForeignKeyConstraint = new ForeignKeyConstraintDefinition( "FakeForeignKeyConstraint", new EntityNameDefinition(null, "Test"), new[] { StoragePropertyDefinitionTestHelper.GetIDColumnDefinition(_fakeObjectIDStorageProperty) }, new[] { _fakeStorageProperty1.ColumnDefinition }); }
public override void SetUp() { base.SetUp(); _factoryStub = MockRepository.GenerateStub <IForeignKeyConstraintScriptElementFactory>(); _builder = new ForeignKeyConstraintScriptBuilder(_factoryStub, new SqlCommentScriptElementFactory()); _tableName = new EntityNameDefinition(null, "Table"); _constraint1 = new ForeignKeyConstraintDefinition("FK1", _tableName, new ColumnDefinition[0], new ColumnDefinition[0]); _constraint2 = new ForeignKeyConstraintDefinition("FK2", _tableName, new ColumnDefinition[0], new ColumnDefinition[0]); _constraint3 = new ForeignKeyConstraintDefinition("FK3", _tableName, new ColumnDefinition[0], new ColumnDefinition[0]); _tableDefinition1 = TableDefinitionObjectMother.Create( SchemaGenerationFirstStorageProviderDefinition, _tableName, null, new[] { _constraint1 }); _tableDefinition2 = TableDefinitionObjectMother.Create( SchemaGenerationFirstStorageProviderDefinition, _tableName, null, new[] { _constraint2, _constraint3 }); _fakeElement1 = MockRepository.GenerateStub <IScriptElement>(); _fakeElement2 = MockRepository.GenerateStub <IScriptElement>(); _fakeElement3 = MockRepository.GenerateStub <IScriptElement>(); }
public void SetUp() { _storageProviderDefinition = new UnitTestStorageProviderStubDefinition("DefaultStorageProvider"); _referencingColumn = ColumnDefinitionObjectMother.CreateColumn("COL1"); _referencedColumn = ColumnDefinitionObjectMother.CreateColumn("COL2"); _referencedTableName = "TableName"; _constraint = new ForeignKeyConstraintDefinition( "Test", new EntityNameDefinition(null, _referencedTableName), new[] { _referencingColumn }, new[] { _referencedColumn }); }
public IScriptElement GetCreateElement(ForeignKeyConstraintDefinition constraintDefinition, EntityNameDefinition tableName) { ArgumentUtility.CheckNotNull("constraintDefinition", constraintDefinition); ArgumentUtility.CheckNotNull("tableName", tableName); return(new ScriptStatement( string.Format( "ALTER TABLE [{0}].[{1}] ADD\r\n{2}", tableName.SchemaName ?? DefaultSchema, tableName.EntityName, GetConstraintDeclaration(constraintDefinition)))); }
private string GetConstraintDeclaration(ForeignKeyConstraintDefinition foreignKeyConstraintDefinition) { var referencedColumnNameList = GetColumnNameList(foreignKeyConstraintDefinition.ReferencedColumns); var referencingColumnNameList = GetColumnNameList(foreignKeyConstraintDefinition.ReferencingColumns); return(string.Format( " CONSTRAINT [{0}] FOREIGN KEY ({1}) REFERENCES [{2}].[{3}] ({4})", foreignKeyConstraintDefinition.ConstraintName, referencingColumnNameList, foreignKeyConstraintDefinition.ReferencedTableName.SchemaName ?? DefaultSchema, foreignKeyConstraintDefinition.ReferencedTableName.EntityName, referencedColumnNameList)); }
public IScriptElement GetDropElement(ForeignKeyConstraintDefinition constraintDefinition, EntityNameDefinition tableName) { ArgumentUtility.CheckNotNull("constraintDefinition", constraintDefinition); ArgumentUtility.CheckNotNull("tableName", tableName); return(new ScriptStatement( string.Format( "IF EXISTS (SELECT * FROM sys.objects fk INNER JOIN sys.objects t ON fk.parent_object_id = t.object_id WHERE fk.type = 'F' AND " + "fk.name = '{2}' AND schema_name (t.schema_id) = '{0}' AND t.name = '{1}')\r\n" + " ALTER TABLE [{0}].[{1}] DROP CONSTRAINT {2}", tableName.SchemaName ?? DefaultSchema, tableName.EntityName, constraintDefinition.ConstraintName))); }
public override void SetUp() { base.SetUp(); _factory = new SqlForeignKeyConstraintScriptElementFactory(); _column1 = ColumnDefinitionObjectMother.CreateColumn("Column1"); _column2 = ColumnDefinitionObjectMother.CreateColumn("Column2"); _table1 = new EntityNameDefinition(null, "TableName1"); _table2 = new EntityNameDefinition("SchemaName", "TableName2"); _constraint1 = new ForeignKeyConstraintDefinition("FK1", _table1, new[] { _column1 }, new[] { _column2 }); _constraint2 = new ForeignKeyConstraintDefinition("FK2", _table2, new[] { _column1, _column2 }, new[] { _column2, _column1 }); }
public override void Visit(ForeignKeyConstraintDefinition node) { base.Visit(node); this.Nodes.Add(node); }
protected override object InternalVisit(ForeignKeyConstraintDefinition node) { Action <Table, Column> applier = (table, ignored) => { var constraintName = node.ConstraintIdentifier.Value; Table refTable; { var refTableName = Visit <string>(node.ReferenceTableName); refTable = Database.GetTable(refTableName); if (refTable == null) { var msg = string.Format("Foreign key '{0}' references invalid table '{1}'", constraintName, refTableName); throw new NullReferenceException(msg); } } Column[] parents = node.ReferencedTableColumns .Select(c => { var dc = refTable.GetColumn(c.Value); if (dc == null) { var msg = string.Format("Foreign key '{0}' references invalid column '{1}' in referenced table '{2}'", constraintName, c.Value, refTable.TableName); throw new NullReferenceException(msg); } return(dc); }) .ToArray(); Column[] children = node.Columns .Select(c => { var dc = table.GetColumn(c.Value); if (dc == null) { var msg = string.Format("Foreign key '{0}' references invalid column '{1}' in referencing table '{2}'", constraintName, c.Value, table.TableName); throw new NullReferenceException(msg); } return(dc); }) .ToArray(); var fk = new ForeignKeyConstraint(constraintName, parents, children); fk.DeleteRule = GetDeleteUpdateRule(node.DeleteAction); fk.UpdateRule = GetDeleteUpdateRule(node.UpdateAction); if ((fk.DeleteRule == Rule.SetNull || fk.UpdateRule == Rule.SetNull) && children.Any(c => !c.AllowDBNull)) { var msg = string.Format("Cannot create the foreign key \"{0}\" with the SET NULL referential action, " + "because one or more referencing columns are not nullable.", constraintName); throw new ConstraintException(msg); } else if ((fk.DeleteRule == Rule.SetDefault || fk.UpdateRule == Rule.SetDefault) && children.Any(c => !c.AllowDBNull && c.DefaultValue == null)) { var msg = string.Format("Cannot create the foreign key \"{0}\" with the SET DEFAULT referential action, " + "because one or more referencing not-nullable columns lack a default constraint.", constraintName); throw new ConstraintException(msg); } Database.AddConstraint(fk); }; return(applier); }
public override void Visit(ForeignKeyConstraintDefinition node) { this.action(node); }
public override void ExplicitVisit(ForeignKeyConstraintDefinition fragment) { _fragments.Add(fragment); }
public override void Visit(ForeignKeyConstraintDefinition node) { base.Visit(node); this.Nodes.Add(node); }
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 override void Visit(ForeignKeyConstraintDefinition v) { sw.WriteLine("<COL>"); String s = ""; foreach (var col in v.Columns) s = s + col.Value + ","; s = s.TrimEnd(','); sw.WriteLine(s); sw.WriteLine("</COL>"); sw.WriteLine("<Ref>"); v.ReferenceTableName.Accept(this); sw.WriteLine("<COL>"); s = ""; foreach (var col in v.ReferencedTableColumns) s = s + col.Value + ","; s = s.TrimEnd(','); sw.WriteLine(s); sw.WriteLine("</COL>"); sw.WriteLine("</Ref>"); }