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 Execute(DBCommand c)
{
if (c.State == DBCommand.CommandState.New || c.State == DBCommand.CommandState.Failed)
{
var data = c.Owner.Connection.GetDataTable(c.Sql);
c.State = data != null ? DBCommand.CommandState.Executed : DBCommand.CommandState.Failed;
}
}
private void create_db_command()
{
var cmd = new DBCommand
{
Sql = GetSQL(),
Owner = DBColumn,
Description = "Column Permission Change"
};
DBColumn.Connection.Project.Commands.Add(cmd);
}
public static DBCommand GetCommand(DBObject o, DB.DBAction a)
{
var previous_action = o.Action;
o.Action = a;
var cmd = new DBCommand {
Owner = o, Sql = o.GetSQL(), Description = string.Format("{0} {1}", a, o)
};
o.Action = previous_action;
return(cmd);
}
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 DataTable ExecuteQuery(DBCommand c, DBConnection conn = null) //query
{
var connection = c.Owner.Connection ?? conn;
if (connection != null)
{
var data = c.Owner.Connection.GetDataTable(c.Sql);
c.State = data != null ? DBCommand.CommandState.Executed : DBCommand.CommandState.Failed;
return(data);
}
c.State = DBCommand.CommandState.Failed;
return(null);
}
private void listBox_users_DragDrop(object sender, DragEventArgs e)
{
var user = e.Data.GetData(typeof(DBUser)) as DBUser;
if (user != null)
{
if (SelectedRole.Members.Where(m => m.Name == user.Name).Count() == 1)
{
var sql = string.Format("ALTER ROLE [{0}] DROP MEMBER [{1}]", SelectedRole.Name, user.Name);
var cmd = new DBCommand {
Owner = SelectedRole, Description = "Drop role member", Sql = sql
};
SelectedRole.Connection.Project.Commands.Add(cmd);
SelectedRole.Members.Remove(user);
}
}
}
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 DBCommand CreateExtendedProperty(DBObject o, string name, string old_value, string new_value)
{
var cmd = new DBCommand();
if (old_value != new_value)
{
if (new_value == "none")
{
new_value = "";
}
var property = new DBExtendedProperty()
{
Name = name, Action = DB.DBAction.Create, Value = new_value, Owner = o
};
cmd = new DBCommand()
{
PropertyName = name,
Owner = o,
Sql = property.GetSQL(),
Description = string.Format("Change {0} from '{1}' to '{2}'", name, old_value, new_value)
};
var existing_cmd = o.Connection.Project.Commands.FirstOrDefault(c => c.Owner == o && c.PropertyName == cmd.PropertyName && c.State == DBCommand.CommandState.New);
if (existing_cmd != null) //update existing non executed command.
{
existing_cmd.Sql = cmd.Sql;
existing_cmd.Description = cmd.Description;
}
else //otherwise create a new one
{
o.Connection.Project.Commands.Add(cmd);
}
}
return(cmd);
}
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 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()
);
}
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 RefreshExtensions(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 ('computed', 'views', 'secure')
and v.name = '{0}_{1}'", t.Schema.Name, t.Name);
var cmd = new DBCommand{ Sql = sql, Owner = t, Description = "Get Table Extensions" };
t.Extensions.Clear();
t.Extensions.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());
//objects for views
t.ExtendedView = t.Extensions.FirstOrDefault(x => x.Schema.Name == "views");
t.ComputedView = t.Extensions.FirstOrDefault(x => x.Schema.Name == "computed");
t.SecureView = t.Extensions.FirstOrDefault(x => x.Schema.Name == "secure");
foreach (var e in t.Extensions)
DBObjectManager.RefreshColumns(e);
}
private void create_db_command()
{
var cmd = new DBCommand
{
Sql = GetSQL(),
Owner = DBSchemaObject,
Description = "Permission Change"
};
DBSchemaObject.Connection.Project.Commands.Add(cmd);
//if view definition on secure view
if (PermissionName == PermissionNames.View_Definition && DBSchemaObject.Schema.Name == "secure")
{
var schema_name = this.DBSchemaObject.Name.Substring(0, this.DBSchemaObject.Name.IndexOf('_'));
var table_name = this.DBSchemaObject.Name.Substring(this.DBSchemaObject.Name.IndexOf('_') + 1);
var p = new DBObjectPermission
{
DBSchemaObject = new DBTable
{
Name = table_name,
Schema = new DBSchema {
Name = schema_name
},
Connection = this.DBSchemaObject.Connection
},
State = DBObjectState.New,
OnAlreadyExists = DBOnExists.PerformActionWithNoExistenceCheck,
PermissionName = PermissionNames.View_Definition,
Role = this.Role
};
p.Grant = this.Grant; //this will trigger command creation..
}
}
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);
}
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()
);
}