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(); }