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