internal void CreateFK(DBColumn ref_column)
{
if (this.Parent is DBTable)
{
var fk = new DBFKConstraint
{
Action = DB.DBAction.Add,
Column = this,
ReferenceColumn = ref_column,
Connection = this.Parent.Connection,
Schema = this.Owner.Schema,
Parent = this.Parent,
Name = string.Format("FK_{0}_{1}_{2}_ref_{3}_{4}_{5}", this.Owner.Name, this.Parent.Name, this.Name, ref_column.Schema.Name, ref_column.Parent.Name, ref_column.Name),
State = DBObject.DBObjectState.New
};
var command = new DBCommand {
Sql = fk.GetSQL(), Description = "Create FK", Owner = this
};
this.Connection.Project.Commands.Add(command);
}
}
public void CreateLookupTable()
{
var owner = (Column.Parent as DBTable);
var sql = string.Format("if object_id('[{0}].[{1}]') is null select * into [{0}].[{1}] from ({2}) t ", Column.Owner.Schema.Name, LookupTableName, GetPreview());
var cmd_create_table = new DBCommand {
Owner = owner, Sql = sql, Description = "Create Lookup Table"
};
owner.Connection.Project.Commands.Add(cmd_create_table);
DBProjectManager.Execute(cmd_create_table);
//refresh so now the schema has the table..
DBSchemaManager.Refresh(Column.Schema);
var lookup_table = Column.Schema.Tables.FirstOrDefault(t => t.Name == LookupTableName);
DBTableManager.Refresh(lookup_table);
lookup_table.Action = DB.DBAction.Alter;
var pk_col = lookup_table.Columns.FirstOrDefault(x => x.Name == "id");
pk_col.Action = DB.DBAction.Alter;
pk_col.Nullable = false;
var cmd_not_null = pk_col.Connection.Project.Commands.FirstOrDefault(c => c.Owner == pk_col);
if (cmd_not_null != null)
{
DBProjectManager.Execute(cmd_not_null);
}
//create the PK
lookup_table.PrimaryKey = new DBPKConstraint
{
Action = DB.DBAction.Add,
Parent = lookup_table,
Schema = lookup_table.Schema,
Connection = lookup_table.Connection,
Name = "PK_" + LookupTableName
};
lookup_table.PrimaryKey.Columns.Add(pk_col);
var cmd = new DBCommand {
Owner = lookup_table, Sql = lookup_table.PrimaryKey.GetSQL(), Description = "Create PK"
};
lookup_table.Connection.Project.Commands.Add(cmd);
DBProjectManager.Execute(cmd);
//table.Refresh();
//reference by FK
var fk = new DBFKConstraint
{
Name = "FK_" + owner.Schema.Name + "_" + owner.Name + "_" + Column.Name + "_ref_" + lookup_table.Schema.Name + "_" + lookup_table.Name + "_id",
Action = DB.DBAction.Add,
Parent = Column.Parent,
Schema = Column.Owner.Schema,
Column = Column,
Connection = Column.Connection,
OnUpdate = DBFKConstraint.CascadeModes.Cascade,
ReferenceColumn = pk_col
};
owner.ForeignKeys.Add(fk);
var cmd_fk = new DBCommand {
Owner = owner, Sql = fk.GetSQL(), Description = "Create FK"
};
lookup_table.Connection.Project.Commands.Add(cmd_fk);
DBProjectManager.Execute(cmd_fk);
}