public static string GetTypeTableCodeDescription(this Table table)
{
try
{
if (table == null)
{
return(string.Empty);
}
if (table.TypedTable == TypedTableConstants.None)
{
return(string.Empty);
}
var column = table.GetColumns().FirstOrDefault(x => StringHelper.Match(x.Name, "name", true));
if (column != null)
{
return(ValidationHelper.MakeCodeIdentifer(column.Name));
}
column = table.GetColumns().FirstOrDefault(x => StringHelper.Match(x.Name, "description", true));
if (column != null)
{
return(ValidationHelper.MakeCodeIdentifer(column.Name));
}
return(string.Empty);
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// Find all relationships that have a specific child table
/// </summary>
/// <param name="table">The table from which all relations begin</param>
/// <param name="includeHierarchy">Determines if the return includes all tables in the inheritence tree</param>
/// <returns></returns>
public ReadOnlyCollection <Relation> FindByChildTable(Table table, bool includeHierarchy)
{
try
{
var retval = new List <Relation>();
var tableList = new List <Table>();
var columnList = new List <Column>();
if (includeHierarchy)
{
tableList.AddRange(table.GetTableHierarchy());
foreach (var t in tableList)
{
foreach (var column in (from x in t.GetColumnsFullHierarchy(true) select x))
{
columnList.Add(column);
}
}
}
else
{
tableList = new List <Table>();
tableList.Add(table);
columnList.AddRange(table.GetColumns());
}
foreach (Relation relation in this)
{
var childTable = (Table)relation.ChildTableRef.Object;
foreach (ColumnRelationship columnRelationship in relation.ColumnRelationships)
{
foreach (var column in columnList)
{
if (tableList.Contains(childTable))
{
if (!retval.Contains(relation))
{
retval.Add(relation);
}
}
}
}
}
return(retval.AsReadOnly());
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// Find all relationships that have a specific parent table
/// </summary>
/// <param name="table">The table from which all relations begin</param>
/// <param name="includeHierarchy">Determines if the return includes all tables in the inheritence tree</param>
/// <returns></returns>
public ReadOnlyCollection <ViewRelation> FindByParentTable(Table table, bool includeHierarchy)
{
var tableList = new List <Table>();
var columnList = new List <Column>();
if (includeHierarchy)
{
tableList.AddRange(new List <Table>(table.GetTableHierarchy()));
foreach (var t in tableList)
{
foreach (var column in (from x in t.GetColumnsFullHierarchy(true) select x))
{
columnList.Add(column);
}
}
}
else
{
tableList = new List <Table>();
tableList.Add(table);
columnList.AddRange(table.GetColumns());
}
var retval = new List <ViewRelation>();
foreach (ViewRelation relation in this)
{
var parentTable = (Table)relation.ParentTableRef.Object;
foreach (ViewColumnRelationship columnRelationship in relation.ColumnRelationships)
{
foreach (var column in columnList)
{
if (tableList.Contains(parentTable))
{
if (!retval.Contains(relation))
{
retval.Add(relation);
}
}
}
}
}
return(retval.AsReadOnly());
}
private void AppendMappingFragment(Table table)
{
if (table.IsTenant)
{
sb.AppendFormat(" <MappingFragment StoreEntitySet=\"{0}\">", _model.TenantPrefix + "_" + table.DatabaseName);
}
else
{
sb.AppendFormat(" <MappingFragment StoreEntitySet=\"{0}\">", table.DatabaseName);
}
sb.AppendLine();
foreach (var currentColumn in table.GetColumns().Where(x => x.Generated).OrderBy(x => x.Name))
{
//var typeTable = _relatedTypeTableCache[currentColumn];
//if (typeTable != null)
//{
// sb.AppendLine(" <ComplexProperty Name=\"" + typeTable.PascalName + "\" TypeName=\"" + this.GetLocalNamespace() + ".Entity." + typeTable.PascalName + "Wrapper\">");
// sb.AppendLine(" <ScalarProperty Name=\"Value\" ColumnName=\"" + currentColumn.DatabaseName + "\" />");
// sb.AppendLine(" </ComplexProperty>");
//}
//else
{
sb.AppendFormat(" <ScalarProperty Name=\"{0}\" ColumnName=\"{1}\" />", currentColumn.PascalName, currentColumn.DatabaseName).AppendLine();
}
}
//No auditing for Associative table
if (!table.AssociativeTable)
{
if (table.AllowCreateAudit)
{
sb.AppendFormat(" <ScalarProperty Name=\"{0}\" ColumnName=\"{1}\" />", _model.Database.CreatedByPascalName, _model.Database.CreatedByDatabaseName).AppendLine();
sb.AppendFormat(" <ScalarProperty Name=\"{0}\" ColumnName=\"{1}\" />", _model.Database.CreatedDatePascalName, _model.Database.CreatedDateDatabaseName).AppendLine();
}
if (table.AllowModifiedAudit)
{
sb.AppendFormat(" <ScalarProperty Name=\"{0}\" ColumnName=\"{1}\" />", _model.Database.ModifiedByPascalName, _model.Database.ModifiedByDatabaseName).AppendLine();
sb.AppendFormat(" <ScalarProperty Name=\"{0}\" ColumnName=\"{1}\" />", _model.Database.ModifiedDatePascalName, _model.Database.ModifiedDateDatabaseName).AppendLine();
}
//For Descendents with NO generated CRUD layer do NOT add timestamp column as concurrency is handled by the ancestor with EF
if (table.AllowTimestamp)
{
if (!_model.Database.UseGeneratedCRUD && table.ParentTable != null)
{
//Do Nothing
}
else
{
sb.AppendFormat(" <ScalarProperty Name=\"{0}\" ColumnName=\"{1}\" />", _model.Database.TimestampPascalName, _model.Database.TimestampDatabaseName).AppendLine();
}
}
}
sb.AppendFormat(" </MappingFragment>").AppendLine();
}
public static string GetSQLCreateAuditTable(ModelRoot model, Table table)
{
if (table.TypedTable == TypedTableConstants.EnumOnly)
return string.Empty;
var dateTimeString = (model.SQLServerType == nHydrate.Generator.Common.GeneratorFramework.SQLServerTypeConstants.SQL2005) ? "[DateTime]" : "[DateTime2]";
var sb = new StringBuilder();
var tableName = "__AUDIT__" + Globals.GetTableDatabaseName(model, table);
sb.AppendLine("if not exists(select * from sysobjects where name = '" + tableName + "' and xtype = 'U')");
sb.AppendLine("CREATE TABLE [" + table.GetSQLSchema() + "].[" + tableName + "] (");
sb.AppendLine("\t[__rowid] [INT] NOT NULL IDENTITY,");
sb.AppendLine("\t[__action] [INT] NOT NULL,");
sb.AppendLine("\t[__insertdate] " + dateTimeString + " CONSTRAINT [DF__" + table.DatabaseName + "__AUDIT] DEFAULT " + model.GetSQLDefaultDate() + " NOT NULL,");
if (table.AllowCreateAudit || table.AllowModifiedAudit)
sb.AppendLine("\t[" + model.Database.ModifiedByDatabaseName + "] [NVarchar] (50) NULL,");
var columnList = table.GetColumns().Where(x => x.Generated).ToList();
foreach (var column in columnList)
{
if (!(column.DataType == System.Data.SqlDbType.Text || column.DataType == System.Data.SqlDbType.NText || column.DataType == System.Data.SqlDbType.Image))
{
sb.Append("\t" + AppendColumnDefinition(column, allowDefault: false, allowIdentity: false, forceNull: true, allowFormula: false, allowComputed: false));
if (columnList.IndexOf(column) < columnList.Count - 1) sb.Append(",");
sb.AppendLine();
}
}
sb.Append(")");
sb.AppendLine();
return sb.ToString();
}
public static string GetSqlDropTable(Table t)
{
if (t.TypedTable == TypedTableConstants.EnumOnly)
return string.Empty;
var sb = new StringBuilder();
#region Delete Parent Relations
for (var ii = t.ParentRoleRelations.Count - 1; ii >= 0; ii--)
{
var parentR = (Relation) t.ParentRoleRelations[ii];
var parentT = (Table) parentR.ParentTableRef.Object;
var childT = (Table) parentR.ChildTableRef.Object;
for (var jj = parentT.ParentRoleRelations.Count - 1; jj >= 0; jj--)
{
//Relation chlidR = (Relation)parentT.ParentRoleRelations[jj];
if (parentR.ParentTableRef.Object == t)
{
var objectNameFK = "FK_" +
parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, childT) +
"_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, parentT);
sb.AppendLine("--REMOVE FOREIGN KEY");
sb.AppendLine("if exists(select * from sys.objects where name = '" + objectNameFK + "' and type = 'F' and type_desc = 'FOREIGN_KEY_CONSTRAINT')");
sb.AppendLine("ALTER TABLE [" + childT.GetSQLSchema() + "].[" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectNameFK + "]");
sb.AppendLine();
}
}
}
#endregion
#region Delete Child Relations
for (var ii = t.ChildRoleRelations.Count - 1; ii >= 0; ii--)
{
var childR = (Relation) t.ChildRoleRelations[ii];
var parentT = (Table) childR.ParentTableRef.Object;
var childT = (Table) childR.ChildTableRef.Object;
for (var jj = parentT.ParentRoleRelations.Count - 1; jj >= 0; jj--)
{
var parentR = (Relation) parentT.ParentRoleRelations[jj];
if (parentR.ChildTableRef.Object == t)
{
var objectNameFK = "FK_" +
parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, childT) +
"_" + Globals.GetTableDatabaseName((ModelRoot) t.Root, parentT);
objectNameFK = objectNameFK.ToUpper();
sb.AppendLine("--REMOVE FOREIGN KEY");
sb.AppendLine("if exists(select * from sys.objects where name = '" + objectNameFK + "' and type = 'F' and type_desc = 'FOREIGN_KEY_CONSTRAINT')");
sb.AppendLine("ALTER TABLE [" + childT.GetSQLSchema() + "].[" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectNameFK + "]");
sb.AppendLine();
}
}
}
#endregion
#region Delete Primary Key
var objectNamePK = "PK_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, t);
sb.AppendLine("--DELETE PRIMARY KEY FOR TABLE [" + t.DatabaseName + "]");
sb.AppendLine("if exists(select * from sys.objects where name = '" + objectNamePK + "' and type = 'PK' and type_desc = 'PRIMARY_KEY_CONSTRAINT')");
sb.AppendLine("ALTER TABLE [" + t.GetSQLSchema() + "].[" + t.DatabaseName + "] DROP CONSTRAINT [" + objectNamePK + "]");
sb.AppendLine();
#endregion
#region Delete Unique Constraints
foreach (var c in t.GetColumns().Where(x => x.IsUnique))
{
var indexName = "IX_" + t.Name.Replace("-", "") + "_" + c.Name.Replace("-", string.Empty);
indexName = indexName.ToUpper();
sb.AppendLine("--DELETE UNIQUE CONTRAINT");
sb.AppendLine("if exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'UQ')");
sb.AppendLine("ALTER TABLE [" + t.DatabaseName + "] DROP CONSTRAINT [" + indexName + "]");
sb.AppendLine();
}
#endregion
#region Delete Indexes
foreach (var c in t.GetColumns().Where(x => !x.IsUnique))
{
var indexName = "IX_" + t.Name.Replace("-", "") + "_" + c.Name.Replace("-", string.Empty);
indexName = indexName.ToUpper();
sb.AppendLine("--DELETE UNIQUE CONTRAINT");
sb.AppendLine("if exists(select * from sysobjects where name = '" + indexName + "' and xtype = 'UQ')");
sb.AppendLine("ALTER TABLE [" + t.DatabaseName + "] DROP CONSTRAINT [" + indexName + "]");
sb.AppendLine();
indexName = CreateIndexName(t, c);
indexName = indexName.ToUpper();
sb.AppendLine("--DELETE INDEX");
sb.AppendLine("if exists (select * from sys.indexes where name = '" + indexName + "')");
sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + t.DatabaseName + "]");
sb.AppendLine();
}
#endregion
//Drop the actual table
sb.AppendLine("--DELETE TABLE [" + t.DatabaseName + "]");
sb.AppendLine("if exists (select * from sysobjects where name = '" + t.DatabaseName + "' and xtype = 'U')");
sb.AppendLine("DROP TABLE [" + t.DatabaseName + "]");
return sb.ToString();
}
public static string GetSQLCreateTableSecurityFunction(Table table, ModelRoot model, bool isInternal)
{
var function = table.Security;
var sb = new StringBuilder();
var objectName = ValidationHelper.MakeDatabaseIdentifier("__security__" + table.Name);
sb.AppendLine("if exists(select * from sys.objects where name = '" + objectName + "' and type in('FN','IF','TF'))");
sb.AppendLine("drop function [" + table.GetSQLSchema() + "].[" + objectName + "]");
if (isInternal)
{
sb.AppendLine("--MODELID: " + function.Key);
}
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("CREATE FUNCTION [" + table.GetSQLSchema() + "].[" + objectName + "]");
sb.AppendLine("(");
if (function.Parameters.Count > 0)
sb.Append(BuildFunctionParameterList(function.GetParameters()));
sb.AppendLine(")");
sb.AppendLine("RETURNS TABLE AS RETURN (");
var realColumns = table.GetColumns().Select(x => x.DatabaseName).ToList();
var facadeColumns = table.GetColumns().Select(x => x.GetCodeFacade()).ToList();
if (table.AllowCreateAudit)
{
realColumns.Add(model.Database.CreatedByColumnName);
realColumns.Add(model.Database.CreatedDateColumnName);
facadeColumns.Add(model.Database.CreatedByColumnName);
facadeColumns.Add(model.Database.CreatedDateColumnName);
}
if (table.AllowModifiedAudit)
{
realColumns.Add(model.Database.ModifiedByColumnName);
realColumns.Add(model.Database.ModifiedDateColumnName);
facadeColumns.Add(model.Database.ModifiedByColumnName);
facadeColumns.Add(model.Database.ModifiedDateColumnName);
}
if (table.AllowTimestamp)
{
realColumns.Add(model.Database.TimestampColumnName);
facadeColumns.Add(model.Database.TimestampColumnName);
}
var ql = new List<string>();
for (var ii = 0; ii < realColumns.Count; ii++)
{
ql.Add("[" + realColumns[ii] + "] AS [" + facadeColumns[ii] + "]");
}
sb.AppendLine("WITH Z AS (");
sb.AppendLine(function.SQL);
sb.AppendLine(")");
sb.AppendLine("select " + string.Join(",", ql) + " from Z");
sb.AppendLine(")");
if (isInternal)
{
sb.AppendLine("--MODELID,BODY: " + function.Key);
}
sb.AppendLine("GO");
sb.AppendLine();
return sb.ToString();
}
public static string GetSqlRenameTable(Table oldTable, Table newTable)
{
//RENAME TABLE
var sb = new StringBuilder();
sb.AppendLine("--RENAME TABLE '" + oldTable.DatabaseName + "' TO '" + newTable.DatabaseName + "'");
sb.AppendLine("if exists(select * from sysobjects where name = '" + oldTable.DatabaseName + "' and xtype = 'U')");
sb.AppendLine("exec sp_rename '" + oldTable.DatabaseName + "', '" + newTable.DatabaseName + "';");
sb.AppendLine("GO");
sb.AppendLine();
if (newTable.EnforcePrimaryKey)
{
//RENAME PRIMARY KEY (it will be readded in create script)
var oldIndexName = "PK_" + oldTable.DatabaseName.ToUpper();
var newIndexName = "PK_" + newTable.DatabaseName.ToUpper();
sb.AppendLine("--RENAME PRIMARY KEY FOR TABLE '" + oldTable.DatabaseName + "'");
sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename '" + oldIndexName + "', '" + newIndexName + "';");
sb.AppendLine();
}
//Rename all FK
foreach (var relation in newTable.GetRelationsWhereChild())
{
var oldIndexName = "FK_" + relation.RoleName + "_" + oldTable.DatabaseName + "_" + relation.ParentTable.DatabaseName;
oldIndexName = oldIndexName.ToUpper();
var newIndexName = "FK_" + relation.RoleName + "_" + newTable.DatabaseName + "_" + relation.ParentTable.DatabaseName;
newIndexName = newIndexName.ToUpper();
sb.AppendLine("--RENAME FK [" + newTable.DatabaseName + "].[" + oldIndexName + "]");
sb.AppendLine("if exists (select * from sys.foreign_keys where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename @objname='" + newTable.GetSQLSchema() + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='OBJECT';");
sb.AppendLine();
}
//Rename all indexes for this table's fields
foreach (var column in newTable.GetColumns())
{
var oldColumn = oldTable.GetColumns().FirstOrDefault(x => x.Key == column.Key);
if (oldColumn != null)
{
var oldIndexName = CreateIndexName(oldTable, oldColumn);
var newIndexName = CreateIndexName(newTable, column);
sb.AppendLine("--RENAME INDEX [" + newTable.DatabaseName + "].[" + oldIndexName + "]");
sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename @objname='" + newTable.GetSQLSchema() + "." + newTable.DatabaseName + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='INDEX';");
sb.AppendLine();
}
}
//rename all indexes for this table
foreach (var index in newTable.TableIndexList)
{
var oldIndex = oldTable.TableIndexList.FirstOrDefault(x => x.Key == index.Key);
if (oldIndex != null)
{
var oldIndexName = GetIndexName(oldTable, oldIndex);
var newIndexName = GetIndexName(newTable, index);
sb.AppendLine("--RENAME INDEX [" + newTable.DatabaseName + "].[" + oldIndexName + "]");
sb.AppendLine("if exists (select * from sys.indexes where name = '" + oldIndexName + "')");
sb.AppendLine("exec sp_rename @objname='" + newTable.GetSQLSchema() + "." + newTable.DatabaseName + "." + oldIndexName + "', @newname='" + newIndexName + "', @objtype='INDEX';");
sb.AppendLine();
}
}
var model = newTable.Root as ModelRoot;
//Change the default name for all audit fields
if (oldTable.AllowCreateAudit)
{
var defaultName = ("DF__" + oldTable.DatabaseName + "_" + model.Database.CreatedDateColumnName).ToUpper();
var defaultName2 = ("DF__" + newTable.DatabaseName + "_" + model.Database.CreatedDateColumnName).ToUpper();
sb.AppendLine("--CHANGE THE DEFAULT NAME FOR CREATED AUDIT");
sb.AppendLine("if exists (select * from sys.default_constraints where name = '" + defaultName + "')");
sb.AppendLine("exec sp_rename @objname='" + defaultName + "', @newname='" + defaultName2 + "';");
sb.AppendLine();
}
if (oldTable.AllowModifiedAudit)
{
var defaultName = ("DF__" + oldTable.DatabaseName + "_" + model.Database.ModifiedDateColumnName).ToUpper();
var defaultName2 = ("DF__" + newTable.DatabaseName + "_" + model.Database.ModifiedDateColumnName).ToUpper();
sb.AppendLine("--CHANGE THE DEFAULT NAME FOR MODIFIED AUDIT");
sb.AppendLine("if exists (select * from sys.default_constraints where name = '" + defaultName + "')");
sb.AppendLine("exec sp_rename @objname='" + defaultName + "', @newname='" + defaultName2 + "';");
sb.AppendLine();
}
return sb.ToString();
}
