//Creates a relationship lookup table for one or more tables
public List <CommandResult> ADD_SCAFFOLD_RELATIONSHIPS(IConnectToDB _Connect, string Name, List <TableStructure> SourceTables)
{
List <CommandResult> HoldResult = new List <CommandResult>();
ER_Generate gen = new ER_Generate();
string tableName = Name + "_REL";
HoldResult.Add(new CommandResult
{
_Successful = true,
_Response = "---Start Scaffold " + tableName + "",
_EndTime = DateTime.Now
});
HoldResult.AddRange(ER_DDL._ADD_TABLE(_Connect, tableName, "Relationship"));
HoldResult.Add(ER_DDL._ADD_COLUMN(_Connect, tableName, "OBJECT_TYPE", "Characters(120)", "", false));
for (int i = 0; i < SourceTables.Count; i++)
{
for (int ii = 0; ii < SourceTables[i].ColumnStructure.Count; ii++)
{
HoldResult.AddRange(ER_DDL._ADD_COLUMNS(_Connect, tableName, SourceTables[i].ColumnStructure));
}
for (int ii = 0; ii < SourceTables[i].RelationshipColumnStructure.Count; ii++)
{
HoldResult.AddRange(ER_DDL._ADD_COLUMNS(_Connect, tableName, SourceTables[ii].RelationshipColumnStructure));
}
}
List <string> ColumnsList2 = new List <string>();
ColumnsList2.Add(tableName + "_ID");
List <ColumnStructure> JoinedColumns = new List <ColumnStructure>();
HoldResult.AddRange(ER_DDL._ADD_KEY_UNIQUE(_Connect, tableName + "_ID", tableName, ColumnsList2));
for (int i = 0; i < SourceTables.Count; i++)
{
JoinedColumns.AddRange(SourceTables[i].ColumnStructure);
}
for (int i = 0; i < SourceTables.Count; i++)
{
JoinedColumns.AddRange(SourceTables[i].RelationshipColumnStructure);
}
HoldResult.AddRange(ER_DDL._ADD_KEY_PRIMARY(_Connect, tableName, tableName, JoinedColumns));
for (int i = 0; i < SourceTables.Count; i++)
{
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, tableName + "_" + i.ToString(), tableName, SourceTables[i]._TableName, SourceTables[i].ColumnStructure, SourceTables[i].RelationshipColumnStructure));
}
for (int i = 0; i < SourceTables.Count; i++)
{
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, tableName + "_A" + i.ToString(), tableName, SourceTables[i]._TableName, SourceTables[i].RelationshipColumnStructure, SourceTables[i].RelationshipColumnStructure));
}
HoldResult.AddRange(gen.ADD_PROCEDURE_INSERT(_Connect, tableName, ""));
HoldResult.AddRange(gen.ADD_PROCEDURE_UPDATE(_Connect, tableName, ""));
HoldResult.AddRange(gen.ADD_PROCEDURE_DELETE(_Connect, tableName, ""));
ER_Generate er_gen = new ER_Generate();
HoldResult.AddRange(er_gen.Generate_Update_Procedure_For_Source(_Connect, tableName));
//HoldResult.AddRange(er_gen.GENERATE_VIEW(_Connect, tableName, "DICTIONARY", "ALL"));
HoldResult.Add(new CommandResult
{
_Successful = true,
_Response = "---End Scaffold " + tableName + "",
_EndTime = DateTime.Now
});
return(HoldResult);
}
public List <CommandResult> ADD_SCAFFOLD_ACCESS(IConnectToDB _Connect, string TableName)
{
string[] excludedTableNames = { "IDENTITIES", "GROUPS", "ROLES", "PRIVILEGES" };
Tools.Box er_tools = new Tools.Box();
ER_Generate gen = new ER_Generate();
List <CommandResult> HoldResult = new List <CommandResult>();
string newTableName = er_tools.MaxNameLength(TableName, (128 - 16));
string Perm_Table = newTableName + "_SEC_PERM";
string Role_Table = newTableName + "_SEC_ROLE";
string Privs_Table = newTableName + "_SEC_PRIV";
string GroupRole_Table = newTableName + "_SEC_GRRO";
HoldResult.Add(new CommandResult
{
_Successful = true,
_Response = "---Start Security Scaffold " + TableName + "",
_EndTime = DateTime.Now
});
List <ColumnStructure> ColumnsList = new List <ColumnStructure>();
//Run as long as Tables isn't listed
if (Array.IndexOf(excludedTableNames, TableName.ToUpper()) < 0)
{
#region Create Tables For Permission Structures
HoldResult.AddRange(ER_DDL._ADD_TABLE(_Connect, GroupRole_Table, "Group Role"));
//HoldResult.AddRange(ER_DDL._ADD_TABLE(_Connect, Perm_Table, "Permission"));
// HoldResult.AddRange(ER_DDL._ADD_TABLE(_Connect, Privs_Table, "Privilege"));
HoldResult.AddRange(ER_DDL._ADD_TABLE(_Connect, Role_Table, "Role"));
#endregion
if (false)
{
#region Permissions Table
ColumnsList.Add(new ColumnStructure {
_Name = "OBJECT_TYPE", _DataType = "Characters(120)", _IsNull = false, _DefaultValue = ""
});
ColumnsList.Add(new ColumnStructure {
_Name = TableName + "_ID", _DataType = "bigint", _IsNull = false, _DefaultValue = ""
});
ColumnsList.Add(new ColumnStructure {
_Name = "ROLES_ID", _DataType = "bigint", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_COLUMNS(_Connect, Perm_Table, ColumnsList));
HoldResult.AddRange(gen.ADD_PROCEDURE_INSERT(_Connect, Perm_Table, ""));
#region PERMS TABLE Foreign Keys
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "ROLES_ID", _DataType = "bigint", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Perm_Table + "_1", Perm_Table, "Roles", ColumnsList, ColumnsList));
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "IDENTITIES_ID", _DataType = "bigint", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Perm_Table + "_2", Perm_Table, "Identities", ColumnsList, ColumnsList));
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "OBJECT_TYPE", _DataType = "Characters(120)", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Perm_Table + "_3", Perm_Table, "Objects", ColumnsList, ColumnsList));
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = TableName + "_ID", _DataType = "bigint", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Perm_Table + "_4", Perm_Table, TableName, ColumnsList, ColumnsList));
#endregion
#endregion
}
#region Roles Table
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "OBJECT_TYPE", _DataType = "Characters(120)", _IsNull = false, _DefaultValue = ""
});
ColumnsList.Add(new ColumnStructure {
_Name = TableName + "_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = ""
});
if (TableName.ToLower() != "applications")
{
ColumnsList.Add(new ColumnStructure {
_Name = "APPLICATIONS_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = ""
});
}
ColumnsList.Add(new ColumnStructure {
_Name = "ROLES_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_COLUMNS(_Connect, Role_Table, ColumnsList));
HoldResult.AddRange(gen.ADD_PROCEDURE_INSERT(_Connect, Role_Table, ""));
HoldResult.AddRange(gen.Generate_Update_Procedure_For_Source(_Connect, Role_Table));
HoldResult.AddRange(gen.ADD_PROCEDURE_UPDATE(_Connect, Role_Table, ""));
HoldResult.AddRange(gen.ADD_PROCEDURE_DELETE(_Connect, Role_Table, ""));
HoldResult.AddRange(ER_Generate.CreateProcedureSearchProcedure(_Connect, "Eminent IT", _Connect.Schema,
"",
"",
new List <CommandResult>(),
selectStruct: new SearchProcedureStruct
{
ProcedurePrefix = "SP_S",
GetLatestVersion = true,
ProcedureName = Role_Table + "_SEARCH",
SourceName = Role_Table
}));
#region Foreign Keys
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "ROLES_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Role_Table + "_SEC_1", Role_Table, "Roles", ColumnsList, ColumnsList));
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "OBJECT_TYPE", _DataType = "Characters(120)", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Role_Table + "_SEC_2", Role_Table, "Objects", ColumnsList, ColumnsList));
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = TableName + "_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Role_Table + "_SEC_3", Role_Table, TableName, ColumnsList, ColumnsList));
//if (TableName.ToLower() != "applications")
//{
// ColumnsList.Clear();
// ColumnsList.Add(new ColumnStructure { _Name = "APPLICATIONS_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = "" });
// HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Role_Table + "_SEC_4", Role_Table, "APPLICATIONS", ColumnsList, ColumnsList));
//}
#endregion
#endregion
if (false)
{
#region Privileges Table
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "OBJECT_TYPE", _DataType = "Characters(120)", _IsNull = false, _DefaultValue = ""
});
ColumnsList.Add(new ColumnStructure {
_Name = TableName + "_ID", _DataType = "bigint", _IsNull = false, _DefaultValue = ""
});
ColumnsList.Add(new ColumnStructure {
_Name = "PRIVILEGES_ID", _DataType = "bigint", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_COLUMNS(_Connect, Privs_Table, ColumnsList));
HoldResult.AddRange(gen.ADD_PROCEDURE_INSERT(_Connect, Privs_Table, ""));
#region Priv Table Foreign Keys
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "PRIVILEGES_ID", _DataType = "bigint", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Privs_Table + "_1", Privs_Table, "Privileges", ColumnsList, ColumnsList));
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "IDENTITIES_ID", _DataType = "bigint", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Privs_Table + "_2", Privs_Table, "Identities", ColumnsList, ColumnsList));
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "OBJECT_TYPE", _DataType = "Characters(120)", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Privs_Table + "_3", Privs_Table, "Objects", ColumnsList, ColumnsList));
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = TableName + "_ID", _DataType = "bigint", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, Privs_Table + "_4", Privs_Table, TableName, ColumnsList, ColumnsList));
#endregion
#endregion
}
#region Group Roles Table
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "OBJECT_TYPE", _DataType = "Characters(120)", _IsNull = false, _DefaultValue = ""
});
ColumnsList.Add(new ColumnStructure {
_Name = TableName + "_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = ""
});
if (TableName.ToLower() != "applications")
{
ColumnsList.Add(new ColumnStructure {
_Name = "APPLICATIONS_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = ""
});
}
ColumnsList.Add(new ColumnStructure {
_Name = "GROUPS_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = ""
});
ColumnsList.Add(new ColumnStructure {
_Name = "ROLES_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_COLUMNS(_Connect, GroupRole_Table, ColumnsList));
HoldResult.AddRange(gen.ADD_PROCEDURE_INSERT(_Connect, GroupRole_Table, ""));
HoldResult.AddRange(gen.Generate_Update_Procedure_For_Source(_Connect, GroupRole_Table));
HoldResult.AddRange(gen.ADD_PROCEDURE_UPDATE(_Connect, GroupRole_Table, ""));
HoldResult.AddRange(gen.ADD_PROCEDURE_DELETE(_Connect, GroupRole_Table, ""));
HoldResult.AddRange(ER_Generate.CreateProcedureSearchProcedure(_Connect, "Eminent IT", _Connect.Schema,
"",
"",
new List <CommandResult>(),
selectStruct: new SearchProcedureStruct
{
ProcedurePrefix = "SP_S",
GetLatestVersion = true,
ProcedureName = GroupRole_Table + "_SEARCH",
SourceName = GroupRole_Table
}));
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "ROLES_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, GroupRole_Table + "_1", GroupRole_Table, "Roles", ColumnsList, ColumnsList));
ColumnsList.Clear();
ColumnsList.Add(new ColumnStructure {
_Name = "GROUPS_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = ""
});
HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, GroupRole_Table + "_2", GroupRole_Table, "Groups", ColumnsList, ColumnsList));
//if (TableName.ToLower() != "applications")
//{
// ColumnsList.Clear();
// ColumnsList.Add(new ColumnStructure { _Name = "APPLICATIONS_UUID", _DataType = "Guid", _IsNull = false, _DefaultValue = "" });
// HoldResult.AddRange(ER_DDL._ADD_KEY_FOREIGN(_Connect, GroupRole_Table + "_SEC_3", Role_Table, "APPLICATIONS", ColumnsList, ColumnsList));
//}
#endregion
}
HoldResult.Add(new CommandResult
{
_Successful = true,
_Response = "---End Security Scaffold " + TableName + "",
_EndTime = DateTime.Now
});
return(HoldResult);
}
