public static void RefreshCheckConstraints(DBTable t)
{
var sql = string.Format(@"
select
cs.name
, cs.definition
, cs.is_disabled
, fail_message = (select top 1 ep.value from sys.extended_properties ep where ep.major_id = cs.object_id and ep.minor_id = 0 and ep.name = 'MS_Description')
from
sys.check_constraints cs
join sys.tables t on cs.parent_object_id = t.object_id
join sys.schemas s on t.schema_id = s.schema_id
where
s.name = '{0}' and t.name = '{1}'
", t.Schema.Name, t.Name);
var cmd = new DBCommand{ Sql = sql, Owner = t, Description = "Get Check Constraints" };
t.CheckConstraints.Clear();
t.CheckConstraints.AddRange(DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBCheckConstraint
{
Name = x["name"].ToString(),
Expression = x["definition"].ToString(),
Enabled = !(bool)x["is_disabled"],
FailMessage = x["fail_message"].ToString(),
Action = DB.DBAction.Alter,
State = EngineManager.DBObject.DBObjectState.Intact
}).ToList());
}
public static void RefreshDefaultConstraints(DBTable t)
{
var sql = string.Format(@"
select
ds.name
, ds.definition
, ds.type_desc
, [column] = c.name
from
sys.default_constraints ds
join sys.tables t on ds.parent_object_id = t.object_id
join sys.schemas s on t.schema_id = s.schema_id
join sys.columns c on c.object_id = t.object_id and c.column_id = ds.parent_column_id
where
s.name = '{0}' and t.name = '{1}'
", t.Schema.Name, t.Name);
var cmd = new DBCommand { Sql = sql, Owner = t, Description = "Get Default Constraints" };
t.DefaultConstraints.Clear();
t.DefaultConstraints.AddRange(DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBDefaultConstraint
{
Name = x["name"].ToString(),
Expression = x["definition"].ToString(),
Column = t.Columns.FirstOrDefault(c => c.Name == x["column"].ToString()),
Parent = t,
Action = DB.DBAction.Alter,
Connection = t.Connection,
State = EngineManager.DBObject.DBObjectState.Intact
}).ToList()
);
}
public static void RefreshPolicies(DBTable t)
{
var sql = string.Format(@"
select
SchemaName = s.name,
ViewName = v.name,
Definition = m.definition
from
sys.schemas s
join sys.views v on v.schema_id = s.schema_id
join sys.sql_modules m on m.object_id = v.object_id
where
s.name in ('select', 'update', 'delete')
and v.name = '{0}_{1}'", t.Schema, t.Name);
var cmd = new DBCommand{ Sql = sql, Owner = t, Description = "Get Table Policies" };
t.Policies.Clear();
t.Policies.AddRange(DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBView()
{
OnAlreadyExists = DBObject.DBOnExists.PerformActionWithNoExistenceCheck,
Name = x["ViewName"].ToString(),
Schema = t.Schema.Database.Schemas.FirstOrDefault(s => s.Name == x["SchemaName"].ToString()),
DefinitionSQL = x["Definition"].ToString(),
Action = DB.DBAction.Alter,
Connection = t.Connection,
State = EngineManager.DBObject.DBObjectState.Intact
}).ToList());
}
public static void RefreshTriggers(DBTable t)
{
var sql = string.Format(@"
select
SchemaName = s.name,
ObjectName = o.name,
ObjectType = o.type_desc,
Definition = m.definition
from
sys.schemas s
join sys.tables t on t.schema_id = s.schema_id
join sys.triggers o on o.parent_id = t.object_id
join sys.sql_modules m on m.object_id = o.object_id
where
(s.name = '{0}')
and (t.name = '{1}')", t.Schema.Name, t.Name);
var cmd = new DBCommand{ Sql = sql, Owner = t, Description = "Get Table Triggers" };
t.Triggers.Clear();
t.Triggers.AddRange(DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBTrigger()
{
OnAlreadyExists = DBObject.DBOnExists.PerformActionWithNoExistenceCheck,
Name = x["ObjectName"].ToString(),
DefinitionSQL = x["Definition"].ToString(),
Action = DB.DBAction.Alter,
State = EngineManager.DBObject.DBObjectState.Intact
}).ToList()
);
}
private void btn_apply_Click(object sender, EventArgs e)
{
foreach (var c in DBObject.Connection.Project.Commands.Where(c => c.State == DBCommand.CommandState.New).ToList())
{
DBProjectManager.Execute(c);
}
}
private void btn_apply_Click(object sender, EventArgs e)
{
foreach (var c in Role.Connection.Project.Commands)
{
DBProjectManager.Execute(c);
}
}
private void toolStripButton_save_all_Click(object sender, EventArgs e)
{
listBox_messages.Items.Clear();
var project = (tree_existing.Nodes[0].Tag as DBProject);
foreach (var c in project.Connections)
{
c.sql_messages.Clear();
}
var pending_commands = project.Commands.Where(x => x.State == DBCommand.CommandState.New).ToList();
var executed_successfully = new List <DBCommand>();
foreach (var cmd in pending_commands)
{
DBProjectManager.Execute(cmd);
listBox_messages.Items.AddRange(cmd.Owner.Connection.sql_messages.ToArray());
if (cmd.State == DBCommand.CommandState.Executed)
{
executed_successfully.Add(cmd);
}
}
foreach (var executed_cmd in executed_successfully)
{
project.Commands.Remove(executed_cmd);
}
}
private void toolStripButton_save_Click(object sender, EventArgs e)
{
if (tree_existing.Nodes[0] != null)
{
if (propertyGrid1.SelectedObject is DBObject)
{
var current_db_obj = propertyGrid1.Tag as DBObject;
var pending_commands = (tree_existing.Nodes[0].Tag as DBProject).Commands.Where(x => x.State == DBCommand.CommandState.New);
var this_object_pending_commands = pending_commands.Where(x => x.Owner == propertyGrid1.SelectedObject as DBObject).ToList();
//var executed_successfully = new List<DBCommand>();
foreach (var c in this_object_pending_commands)
{
DBProjectManager.Execute(c);
//if(cmd.State == DBCommand.CommandState.Executed)
//{
// executed_successfully.Add(cmd);
//}
}
//foreach(var executed_cmd in executed_successfully)
//{
// this_object_pending_commands.Remove(executed_cmd);
//}
}
}
}
public static void RefreshBusinessProcedures(DBTable t)
{
var sql = string.Format(@"select
SchemaName = s.name,
ProcedureName = p.name,
Definition = m.definition
from
sys.schemas s
join sys.procedures p on p.schema_id = s.schema_id
join sys.sql_modules m on m.object_id = p.object_id
where
s.name = 'business'
and p.name like '{0}[_]{1}[_][_]%'", t.Schema, t.Name);
var cmd = new DBCommand{ Sql = sql, Owner = t, Description = "Get Business Procedures" };
t.Business.Clear();
t.Business.AddRange(DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBStoredProcedure()
{
OnAlreadyExists = DBObject.DBOnExists.PerformActionWithNoExistenceCheck,
Name = x["ProcedureName"].ToString(),
Schema = t.Schema.Database.Schemas.FirstOrDefault(s => s.Name == x["SchemaName"].ToString()),
DefinitionSQL = x["Definition"].ToString(),
Action = DB.DBAction.Alter,
State = EngineManager.DBObject.DBObjectState.Intact
}).ToList());
}
private void btn_ok_Click(object sender, EventArgs e)
{
//execute the changes
foreach (var c in InitialRole.Connection.Project.Commands.Where(c => c.State == DBCommand.CommandState.New).ToList())
{
DBProjectManager.Execute(c);
}
}
public static void RefreshCache(DBTable t)
{
var sql = string.Format("exec engine.extensions_CreateExtension '{0}_{1}'", t.Schema.Name, t.Name);
var db_cmd = new DBCommand { Owner = t, Sql = sql, Description = "Refresh Cache" };
t.Connection.Project.Commands.Add(db_cmd);
DBProjectManager.Execute(db_cmd);
MessageBox.Show("Extension Created.");
}
public static void CreateGenericTrigger(DBTable t)
{
var sql = string.Format("exec engine.secure_CreateGenericTrigger '{0}', '{1}'", t.Schema.Name, t.Name);
var db_cmd = new DBCommand { Owner = t, Sql = sql, Description = "Create Generic Trigger" };
t.Connection.Project.Commands.Add(db_cmd);
DBProjectManager.Execute(db_cmd);
MessageBox.Show("Generic Trigger Created.");
}
public static void EnableAllTriggers(DB db)
{
var sql = string.Format("use {0}; enable trigger all on database;", db.FullName);
DBProjectManager.Execute(new DBCommand {
Owner = db, Sql = sql, Description = "Enable database triggers"
});
//MessageBox.Show("Database Triggers Enabled Successfully.");
}
public static void RefreshTableProperties(DBTable t)
{
var sql = string.Format(@"
select *
from
(
select TableName = t.name, property_name = ep.name, property_value = ep.value
from sys.schemas s
join sys.tables t on s.schema_id = t.schema_id
join sys.extended_properties ep on ep.major_id = t.object_id and ep.minor_id = 0
where s.name = '{0}' and t.name = '{1}'
) d
pivot
(
max(property_value)
for property_name in (title, iconclass, MS_Description, row_name, row_title, row_group, row_iconclass, row_image, row_labeltype, row_color, row_badge, row_date, row_subtitle, migration_data_source, migration_structure_source, migration_data_query, migration_structure_query, use_history, lookup_tooltip_columns)
) p
order by TableName
", t.Schema.Name, t.Name);
var cmd = new DBCommand{ Sql = sql, Owner = t, Description = "Get Table Properties" };
var row = DBProjectManager.ExecuteQuery(cmd).AsEnumerable().FirstOrDefault();
if (row != null)
{
var cols = t.SecureView != null ? t.SecureView.Columns : t.Columns;
t.Title = row["title"].ToString();
t.IconClass = new DBIconClass { Class = row["iconclass"].ToString(), Value = row["iconclass"].ToString() };
t.Description = row["MS_Description"].ToString();
t.RowBadge = cols.FirstOrDefault(c => c.Name == row["row_badge"].ToString());
t.RowSubTitle = cols.FirstOrDefault(c => c.Name == row["row_subtitle"].ToString());
t.RowDate = cols.FirstOrDefault(c => c.Name == row["row_date"].ToString());
t.RowIconClass = cols.FirstOrDefault(c => c.Name == row["row_iconclass"].ToString());
t.RowImage = cols.FirstOrDefault(c => c.Name == row["row_image"].ToString());
t.RowLabelType = cols.FirstOrDefault(c => c.Name == row["row_labeltype"].ToString());
t.RowLabelType = cols.FirstOrDefault(c => c.Name == row["row_color"].ToString());
t.RowName = cols.FirstOrDefault(c => c.Name == row["row_name"].ToString());
t.RowTitle = cols.FirstOrDefault(c => c.Name == row["row_title"].ToString());
t.RowGroup = cols.FirstOrDefault(c => c.Name == row["row_group"].ToString());
t.UseHistory = !string.IsNullOrEmpty(row["use_history"].ToString()) && bool.Parse(row["use_history"].ToString());
t.MigrationDataSource = !string.IsNullOrEmpty(row["migration_data_source"].ToString()) ? (EngineManager.DBTable.MigrationDataSources)Enum.Parse(typeof(EngineManager.DBTable.MigrationDataSources), row["migration_data_source"].ToString()) : EngineManager.DBTable.MigrationDataSources.Development;
t.MigrationDataQuery = row["migration_data_query"].ToString();
t.LookupTooltipColumns = row["lookup_tooltip_columns"].ToString().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
}
}
public static void RefreshUsers(DB db)
{
const string sql = @"select name, type from sys.database_principals where type in( 'u', 's') and is_fixed_role = 0 order by name";
var cmd = new DBCommand {
Owner = db, Sql = sql
};
db.Users.AddRange(
DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBUser()
{
Name = x["name"].ToString(),
Action = DB.DBAction.Alter,
State = DBObject.DBObjectState.Intact,
}).ToList()
);
}
public static void RefreshTables(DBSchema s)
{
var sql = string.Format(@"select TableName = t.name
, title = isnull( (select ep.value from sys.extended_properties ep where ep.major_id = t.object_id and ep.minor_id = 0 and ep.name = 'title') , t.name)
from sys.schemas s
join sys.tables t on s.schema_id = t.schema_id
where s.name = '{0}'
order by t.name", s.Name);
var cmd = new DBCommand {
Sql = sql, Owner = s, Description = "Get Tables"
};
s.Tables.AddRange(DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBTable()
{
OnAlreadyExists = DBObject.DBOnExists.DoNothingElsePerformAction,
Name = x["TableName"].ToString(),
Action = DB.DBAction.Alter,
Title = x["title"].ToString()
}).ToList());
}
public static void Refresh(DBConnection c)
{
const string sql = "select name from sys.databases order by name";
var cmd = new DBCommand {
Sql = sql, Owner = new DBObject {
Connection = c
}, Description = "Get Databases"
};
c.Databases.Clear();
c.Databases.AddRange(DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DB()
{
Name = x["name"].ToString(),
Connection = c
}).ToList());
var initial_db = c.Databases.FirstOrDefault(x => x.Name == c.InitialDatabase);
DBManager.Refresh(initial_db);
}
public static void RefreshPrimaryKey(DBTable t)
{
var sql = string.Format(@"
select
pk_name = i.name
, col_name = c.name
from
sys.schemas s
join sys.tables t on t.schema_id = s.schema_id
join sys.indexes i on i.object_id = t.object_id
join sys.index_columns ic on ic.index_id = i.index_id and ic.object_id = t.object_id
join sys.columns c on t.object_id = c.object_id and c.column_id = ic.column_id
where
i.is_primary_key = 1
and s.name = '{0}'
and t.name = '{1}'
", t.Schema.Name, t.Name);
var cmd = new DBCommand{ Sql = sql, Owner = t, Description = "Get Table Primary Key Constraint" };
t.PrimaryKey = DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBPKConstraint
{
Name = x["pk_name"].ToString(),
Columns = new MyBindingList<DBColumn> { t.Columns.FirstOrDefault(c => c.Name == x["col_name"].ToString()) },
Action = DB.DBAction.Alter,
State = EngineManager.DBObject.DBObjectState.Intact
}).FirstOrDefault();
//set flag in pk columns
if (t.PrimaryKey != null)
foreach (var c in t.Columns)
if (t.PrimaryKey.Columns.Contains(c))
{
c.State = EngineManager.DBObject.DBObjectState.None;
c.IsPrimaryKey = true;
c.State = EngineManager.DBObject.DBObjectState.Intact;
}
}
public static void RefreshSchemas(DB db)
{
const string sql = @"SELECT name [SchemaName],
[SchemaType] = case when name in('cache','class','db', 'delete', 'dynamic', 'engine', 'filters', 'functions', 'history', 'log', 'migration','procedures', 'query', 'requests', 'search', 'secure', 'security', 'select', 'survey','update','views', 'workflow', 'computed', 'extensions', 'process', 'unprocess', 'sql', 'business') then 'engine' else 'user' end
FROM sys.schemas
WHERE schema_id between 5 and 16383 or schema_id = 1
order by name";
var cmd = new DBCommand {
Owner = db, Sql = sql
};
db.Schemas.AddRange(
DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x =>
new DBSchema()
{
Name = x["SchemaName"].ToString(),
SchemaType = x["SchemaType"].ToString() == "engine" ? DBSchema.DBSchemaTypes.Engine : DBSchema.DBSchemaTypes.User,
Action = DB.DBAction.Create
}).ToList()
);
}
public static void RefreshTriggers(DB db)
{
const string sql = @"SELECT name [Name], Definition = m.definition
FROM sys.triggers t
join sys.sql_modules m on t.object_id = m.object_id
WHERE t.parent_id = 0
order by name";
var cmd = new DBCommand {
Owner = db, Sql = sql
};
db.DatabaseTriggers.AddRange(
DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBDatabaseTrigger()
{
Name = x["Name"].ToString(),
DefinitionSQL = x["Definition"].ToString(),
Action = DB.DBAction.Alter,
State = DBObject.DBObjectState.Intact,
}).ToList()
);
}
public static void RefreshProcedures(DBSchema s)
{
var sql = string.Format(@"select Name = o.name, Definition = m.definition
from sys.schemas s
join sys.objects o on s.schema_id = o.schema_id
join sys.sql_modules AS m ON o.object_id = m.object_id
where s.name = '{0}' and o.type = 'P'
order by o.Name", s.Name);
var cmd = new DBCommand {
Sql = sql, Owner = s, Description = "Get Procedures"
};
s.StoredProcedures.AddRange(DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBStoredProcedure()
{
OnAlreadyExists = DBObject.DBOnExists.PerformActionWithNoExistenceCheck,
Name = x["Name"].ToString(),
DefinitionSQL = x["Definition"].ToString(),
Action = DB.DBAction.Alter
}).ToList()
);
}
private void NewLookupTableFormClosing(object sender, FormClosingEventArgs e)
{
var form = (sender as InputForm);
if (form.DialogResult == DialogResult.Cancel)
{
return;
}
var lookup_table = form.Object as DBTable;
var sql = lookup_table.GetSQL();
lookup_table.Connection.Project.Commands.Add(new DBCommand {
Sql = sql, Owner = lookup_table, Description = "Create New Lookup Table"
});
//set custom properties
lookup_table.RowIconClass = lookup_table.Columns.Where(c => c.UserDataType == "iconclass").FirstOrDefault();
lookup_table.RowColor = lookup_table.Columns.Where(c => c.UserDataType == "color").FirstOrDefault();
lookup_table.DefaultSort = lookup_table.Columns.Where(c => c.UserDataType == "displayorder").FirstOrDefault();
//pk
var pk_col = lookup_table.Columns.Where(c => c.Name == "id").FirstOrDefault();
pk_col.IsPrimaryKey = true;
//execute the commands..
foreach (var c in lookup_table.Connection.Project.Commands.Where(c => c.Owner == lookup_table).ToList())
{
DBProjectManager.Execute(c);
}
DBTableManager.Refresh(lookup_table);
//show the designer..
CreateNewForm(lookup_table);
}
public static void RefreshForeignKeys(DBTable t)
{
var sql = string.Format(@"
select
ConstraintName = t1.fk_name,
SchemaName = t1.fk_schema,
TableName = t1.fk_table,
ColumnName = t1.fk_column,
referencedSchema = t1.pk_schema,
referencedTable = t1.pk_table,
referencedColumn = t1.pk_column,
t1.update_action,
t1.delete_action,
respect_read = cast(case when respect like '%R%' then 1 else 0 end as bit),
respect_create = cast(case when respect like '%C%' then 1 else 0 end as bit),
respect_update = cast(case when respect like '%U%' then 1 else 0 end as bit),
respect_delete = cast(case when respect like '%D%' then 1 else 0 end as bit),
cascade_lookup = cast(case when respect like '%L%' then 1 else 0 end as bit),
refresh_parents_on_change = cast(case when respect like '%S%' then 1 else 0 end as bit),
respect,
title,
is_lookup,
is_constraint = cast(1 as bit),
is_disabled,
relation_type = 'constraint',
for_finding_child = cast(1 as bit),
for_finding_parent = cast(1 as bit)
from
(
SELECT
fk_name = kc.name,
pk_column_id = concat(pk_schema.name,'.',pktable.name, '.', pkcolumn.name),
fk_column_id = concat(fk_schema.name,'.',fktable.name, '.', fkcolumn.name),
fk_schema.name as [fk_schema]
, fktable.name AS fk_table
, fkcolumn.name AS fk_column
, pk_schema.name as [pk_schema]
, pktable.name AS pk_table
, pkcolumn.name AS pk_column
, kc.delete_referential_action_desc [delete_action]
, kc.update_referential_action_desc [update_action]
, respect = cast(ep.value as nvarchar(max))
, title = cast(ept.value as nvarchar(max))
, is_lookup = cast(epl.value as nvarchar(max))
, kc.is_disabled
FROM
sys.foreign_key_columns AS fkc INNER JOIN
sys.tables AS fktable ON fkc.parent_object_id = fktable.object_id INNER JOIN
sys.columns AS fkcolumn ON fkc.parent_column_id = fkcolumn.column_id AND fktable.object_id = fkcolumn.object_id INNER JOIN
sys.tables AS pktable ON fkc.referenced_object_id = pktable.object_id INNER JOIN
sys.columns AS pkcolumn ON fkc.referenced_column_id = pkcolumn.column_id AND pktable.object_id = pkcolumn.object_id
INNER JOIN sys.schemas fk_schema ON fktable.schema_id = fk_schema.schema_id
INNER JOIN sys.schemas pk_schema ON pktable.schema_id = pk_schema.schema_id
INNER JOIN sys.foreign_keys kc on fkc.constraint_object_id = kc.object_id
left join sys.extended_properties ep on ep.major_id = fkc.constraint_object_id and ep.name = 'MS_Description'
left join sys.extended_properties ept on ept.major_id = fkc.constraint_object_id and ept.name = 'title'
left join sys.extended_properties epl on epl.major_id = fkc.constraint_object_id and epl.name = 'is_lookup'
where
(fk_schema.name = '{0}' and fktable.name = '{1}')
-- or (pk_schema.name = '{0}' and pktable.name = '{1}')
) as t1", t.Schema, t.Name);
var cmd = new DBCommand{ Sql = sql, Owner = t, Description = "Get Foreign Key Constraints" };
t.ForeignKeys.AddRange(DBProjectManager.ExecuteQuery(cmd).AsEnumerable().Select(x => new DBFKConstraint()
{
OnAlreadyExists = DBObject.DBOnExists.DropBeforePerformingAction,
Name = x["ConstraintName"].ToString(),
Schema = t.Schema.Database.Schemas.FirstOrDefault(s => s.Name == x["SchemaName"].ToString()),
ReferenceSchemaName = x["referencedSchema"].ToString(),
ReferenceTableName = x["referencedTable"].ToString(),
ReferenceColumnName = x["referencedColumn"].ToString(),
Column = t.Columns.FirstOrDefault(z => z.Name == x["ColumnName"].ToString()),
RespectRead = (bool)x["respect_read"],
RespectCreate = (bool)x["respect_create"],
RespectUpdate = (bool)x["respect_update"],
RespectDelete = (bool)x["respect_delete"],
RefreshParent = (bool)x["refresh_parents_on_change"],
CascadeLookup = (bool)x["cascade_lookup"],
Title = x["title"].ToString(),
IsLookup = x["is_lookup"].ToString() != "" && bool.Parse(x["is_lookup"].ToString()),
OnDelete = x["delete_action"].ToString() == "CASCADE" ? DBFKConstraint.CascadeModes.Cascade :
x["delete_action"].ToString() == "SET_NULL" ? DBFKConstraint.CascadeModes.SetNull :
x["delete_action"].ToString() == "SET_DEFAULT" ? DBFKConstraint.CascadeModes.SetDefault : DBFKConstraint.CascadeModes.DoNothing,
OnUpdate = x["update_action"].ToString() == "CASCADE" ? DBFKConstraint.CascadeModes.Cascade :
x["update_action"].ToString() == "SET_NULL" ? DBFKConstraint.CascadeModes.SetNull :
x["update_action"].ToString() == "SET_DEFAULT" ? DBFKConstraint.CascadeModes.SetDefault : DBFKConstraint.CascadeModes.DoNothing,
Action = DB.DBAction.Alter
}).ToList()
);
}
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);
}
private void toolStripButton_execute_Click(object sender, EventArgs e)
{
Command.Sql = syntaxEditor1.Text;
DBProjectManager.Execute(Command);
this.Close();
}
