/// <summary> /// Reads specified table foreign keys. /// </summary> private void ApplyTablesForeignKeys(List<DbTable> tables, SQLServerVersions sqlServer) { /* * sql 2005 format SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME, O1.name) AS PKTABLE_NAME, CONVERT(SYSNAME, C1.name) AS PKCOLUMN_NAME, CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS FKTABLE_NAME, CONVERT(SYSNAME, C2.name) AS FKCOLUMN_NAME, CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE, CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME, OBJECT_NAME(F.object_id)) AS FK_NAME, CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action, F.update_referential_action FROM sys.all_objects AS O1 INNER JOIN sys.foreign_keys AS F INNER JOIN sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id */ // GENERAL command format string foreignKeySql = @"SELECT OBJECT_NAME(f.constid) AS 'ForeignKey', OBJECT_NAME(f.fkeyid) AS 'FKTable', c1.name AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable', c2.name AS 'PKColumnName' , -1 as update_referential_action, -1 as delete_referential_action FROM sysforeignkeys AS f INNER JOIN syscolumns AS c1 ON f.fkeyid = c1.id AND f.fkey = c1.colid INNER JOIN syscolumns AS c2 ON f.rkeyid = c2.id AND f.rkey = c2.colid ORDER BY c1.colid "; // NEW command format if (sqlServer > SQLServerVersions.SQL2000) { foreignKeySql = @"SELECT CONVERT(SYSNAME, DB_NAME()) AS PKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O1.schema_id)) AS PKTABLE_OWNER, CONVERT(SYSNAME, O1.name) AS 'PKTable', CONVERT(SYSNAME, C1.name) AS 'PKColumnName', CONVERT(SYSNAME, DB_NAME()) AS FKTABLE_QUALIFIER, CONVERT(SYSNAME, SCHEMA_NAME(O2.schema_id)) AS FKTABLE_OWNER, CONVERT(SYSNAME, O2.name) AS 'FKTable', CONVERT(SYSNAME, C2.name) AS 'FKColumnName', CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsUpdateCascade') WHEN 1 THEN 0 ELSE 1 END) AS UPDATE_RULE, CONVERT(SMALLINT, CASE OBJECTPROPERTY(F.OBJECT_ID, 'CnstIsDeleteCascade') WHEN 1 THEN 0 ELSE 1 END) AS DELETE_RULE, CONVERT(SYSNAME, OBJECT_NAME(F.object_id)) AS 'ForeignKey', CONVERT(SYSNAME, I.name) AS PK_NAME, CONVERT(SMALLINT, 7) AS DEFERRABILITY, F.delete_referential_action, F.update_referential_action FROM sys.all_objects AS O1 INNER JOIN sys.foreign_keys AS F INNER JOIN sys.foreign_key_columns AS K ON K.constraint_object_id = F.object_id INNER JOIN sys.indexes AS I ON F.referenced_object_id = I.object_id AND F.key_index_id = I.index_id ON O1.object_id = F.referenced_object_id INNER JOIN sys.all_objects AS O2 ON F.parent_object_id = O2.object_id INNER JOIN sys.all_columns AS C1 ON F.referenced_object_id = C1.object_id AND K.referenced_column_id = C1.column_id INNER JOIN sys.all_columns AS C2 ON F.parent_object_id = C2.object_id AND K.parent_column_id = C2.column_id ORDER BY C2.column_id"; } try { using (var adapter = new SqlDataAdapter(foreignKeySql, (SqlConnection)_dbConnection)) { adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; // description data table using (var keysData = new DataTable()) { // Just to avoid stupid "Failed to enable constraints" error! using (var tempDs = new DataSet()) { // Avoiding stupid "Failed to enable constraints" error! tempDs.EnforceConstraints = false; tempDs.Tables.Add(keysData); // Get from db adapter.Fill(keysData); } if (keysData.Rows.Count > 0) { foreach (DataRow keysDataRow in keysData.Rows) { var foreignKeyTableName = keysDataRow["FKTable"].ToString(); var primaryKeyTableName = keysDataRow["PKTable"].ToString(); var foreignKeyTable = FindTable(tables, foreignKeyTableName); var primaryKeyTable = FindTable(tables, primaryKeyTableName); // one-to-many foreign relation will be added if (primaryKeyTable != null) { // foreign key many end var manyMultiplicityKey_Local = new DbForeignKey() { ForeignKeyName = keysDataRow["ForeignKey"].ToString(), LocalColumnName = keysDataRow["PKColumnName"].ToString(), ForeignColumnName = keysDataRow["FKColumnName"].ToString(), ForeignTableName = keysDataRow["FKTable"].ToString(), Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne }; // check if it is already there if (primaryKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == manyMultiplicityKey_Local.ForeignKeyName)) continue; manyMultiplicityKey_Local.UpdateAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString())); manyMultiplicityKey_Local.DeleteAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString())); // to the list primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey_Local); // apply local column DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey_Local.LocalColumnName); manyMultiplicityKey_Local.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (foreignKeyTable != null) { // foreign table of that! manyMultiplicityKey_Local.ForeignTable = foreignKeyTable; // apply foreign column DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey_Local.ForeignColumnName); manyMultiplicityKey_Local.ForeignColumn = foreignColumn; } else { manyMultiplicityKey_Local.ForeignTable = null; manyMultiplicityKey_Local.ForeignColumn = null; } } // one-to-? foreign relation will be added if (foreignKeyTable != null) { // foreign key many end var oneMultiplicityKey_Foreign = new DbForeignKey() { ForeignKeyName = keysDataRow["ForeignKey"].ToString(), LocalColumnName = keysDataRow["FKColumnName"].ToString(), ForeignColumnName = keysDataRow["PKColumnName"].ToString(), ForeignTableName = keysDataRow["PKTable"].ToString(), Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany }; // check if it is already there if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName)) continue; oneMultiplicityKey_Foreign.UpdateAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["update_referential_action"].ToString())); oneMultiplicityKey_Foreign.DeleteAction = ConvertSqlServerForeignKeyAction(Convert.ToInt32(keysDataRow["delete_referential_action"].ToString())); // to the list foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign); // apply local column DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName); oneMultiplicityKey_Foreign.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (primaryKeyTable != null) { // foreign table of that! oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable; // apply foreign column DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName); oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn; } else { oneMultiplicityKey_Foreign.ForeignTable = null; oneMultiplicityKey_Foreign.ForeignColumn = null; } } }// all foreign keys // look for one-to-one situation! } } } } catch { // Seems this version of SQL Server doesn't support this query! // don't stop here! } }
/// <summary> /// Applies project settings to fields name /// </summary> private string NaturalizeNames_ForeignTableFieldName(DbTable table, DbForeignKey foreignKey) { if (string.IsNullOrEmpty(foreignKey.ForeignTableNameInLocalTable)) return foreignKey.ForeignTableNameInLocalTable; var newName = NaturalizeNames_TableName_Rename(foreignKey.ForeignTableNameInLocalTable); var stringCompare = StringComparison.InvariantCulture; if (_patternProject.LanguageSettings.KeywordsCaseSensitive == false) stringCompare = StringComparison.InvariantCultureIgnoreCase; // suppress pattern string replacement = _patternProject.LanguageSettings.LanguageKeywordsSuppress; int initReplacePartCount = 0; string initReplacePartStr = ""; // column name should not be the same if (newName.Equals(table.TableNameSchema, stringCompare) || newName.Equals(table.TableNameSchemaCS, stringCompare)) { var renamedName = string.Format(replacement, newName, initReplacePartStr); initReplacePartCount++; initReplacePartStr = initReplacePartCount.ToString(); // no duplicate while (table.FindColumnSchema(renamedName) != null || table.ForeignKeys.Any(x => x.ForeignTableNameInLocalTable.Equals(renamedName, stringCompare))) { renamedName = string.Format(replacement, newName, initReplacePartStr); initReplacePartCount++; initReplacePartStr = initReplacePartCount.ToString(); } newName = renamedName; } // foreign name is not changed and is a member if (newName.Equals(foreignKey.ForeignTableNameInLocalTable, stringCompare)) { var sameNameForeignKeys = table.ForeignKeys.Where(x => x.ForeignTableNameInLocalTable.Equals(newName, stringCompare)).ToList(); // no more than one occurrence, including itself if (table.FindColumnSchema(newName) != null || (sameNameForeignKeys.Count > 1 && sameNameForeignKeys.IndexOf(foreignKey) > 0)) { var renamedName = string.Format(replacement, newName, initReplacePartStr); initReplacePartCount++; initReplacePartStr = initReplacePartCount.ToString(); // no duplicate while (table.FindColumnSchema(renamedName) != null || table.ForeignKeys.Any(x => x.ForeignTableNameInLocalTable.Equals(renamedName, stringCompare))) { renamedName = string.Format(replacement, newName, initReplacePartStr); initReplacePartCount++; initReplacePartStr = initReplacePartCount.ToString(); } newName = renamedName; } } else { if (table.FindColumnSchema(newName) != null || table.ForeignKeys.Any(x => x.ForeignTableNameInLocalTable.Equals(newName, stringCompare))) { var renamedName = string.Format(replacement, newName, initReplacePartStr); initReplacePartCount++; initReplacePartStr = initReplacePartCount.ToString(); // no duplicate while (table.FindColumnSchema(renamedName) != null || table.ForeignKeys.Any(x => x.ForeignTableNameInLocalTable.Equals(renamedName, stringCompare))) { renamedName = string.Format(replacement, newName, initReplacePartStr); initReplacePartCount++; initReplacePartStr = initReplacePartCount.ToString(); } newName = renamedName; } } // checking keyword match if only foreign name is not changed if (newName.Equals(foreignKey.ForeignTableNameInLocalTable, stringCompare)) { // ignoring keywords foreach (var keyword in _patternProject.LanguageSettings.LanguageKeywords) { // keyword match if (newName.Equals(keyword, stringCompare)) { var renamedName = string.Format(replacement, newName, initReplacePartStr); initReplacePartCount++; initReplacePartStr = initReplacePartCount.ToString(); // no duplicate while (table.FindColumnSchema(renamedName) != null || table.ForeignKeys.Any(x => x.ForeignTableNameInLocalTable.Equals(renamedName, stringCompare))) { renamedName = string.Format(replacement, newName, initReplacePartStr); initReplacePartCount++; initReplacePartStr = initReplacePartCount.ToString(); } newName = renamedName; // name is chaned and check is no longer required break; } } } // foreign name is ok to be used return newName; }
/// <summary> /// Reads specified table foreign keys. /// </summary> private void ApplyTablesForeignKeys(List<DbTable> tables) { if (_dbConnection.State != ConnectionState.Open) _dbConnection.Open(); try { // Used to get columns Sql DataType const string cmd = "SELECT A.COLUMN_NAME as UNIQUE_COLUMN_NAME,C.COLUMN_NAME as CONSTRAINT_COLUMN_NAME , B.* FROM INFORMATION_SCHEMA.indexes A INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS B ON UNIQUE_CONSTRAINT_NAME = INDEX_NAME inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE C ON C.CONSTRAINT_NAME=B.CONSTRAINT_NAME"; using (var adapter = new SqlCeDataAdapter(cmd, _dbConnection.ConnectionString)) using (var foreignKeysTable = new DataTable()) { adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; adapter.Fill(foreignKeysTable); // nothing found!) if (foreignKeysTable.Rows.Count == 0) return; foreach (DataRow keyRow in foreignKeysTable.Rows) { var foreignKeyTableName = keyRow["CONSTRAINT_TABLE_NAME"].ToString(); var primaryKeyTableName = keyRow["UNIQUE_CONSTRAINT_TABLE_NAME"].ToString(); var foreignKeyTable = FindTable(tables, foreignKeyTableName); var primaryKeyTable = FindTable(tables, primaryKeyTableName); // one-to-many foreign relation will be added if (primaryKeyTable != null) { // foreign key many end var manyMultiplicityKey_Local = new DbForeignKey() { ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(), LocalColumnName = keyRow["UNIQUE_COLUMN_NAME"].ToString(), ForeignColumnName = keyRow["CONSTRAINT_COLUMN_NAME"].ToString(), ForeignTableName = keyRow["CONSTRAINT_TABLE_NAME"].ToString(), Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne }; // check if it is already there if (primaryKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == manyMultiplicityKey_Local.ForeignKeyName)) continue; manyMultiplicityKey_Local.UpdateAction = ConvertSqlCeForeignKeyAction(keyRow["UPDATE_RULE"].ToString()); manyMultiplicityKey_Local.DeleteAction = ConvertSqlCeForeignKeyAction(keyRow["DELETE_RULE"].ToString()); // to the list primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey_Local); // apply local column DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey_Local.LocalColumnName); manyMultiplicityKey_Local.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (foreignKeyTable != null) { // foreign table of that! manyMultiplicityKey_Local.ForeignTable = foreignKeyTable; // apply foreign column DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey_Local.ForeignColumnName); manyMultiplicityKey_Local.ForeignColumn = foreignColumn; } else { manyMultiplicityKey_Local.ForeignTable = null; manyMultiplicityKey_Local.ForeignColumn = null; } } // one-to-one foreign relation will be added if (foreignKeyTable != null) { // foreign key many end var oneMultiplicityKey_Foreign = new DbForeignKey() { ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(), LocalColumnName = keyRow["CONSTRAINT_COLUMN_NAME"].ToString(), ForeignColumnName = keyRow["UNIQUE_COLUMN_NAME"].ToString(), ForeignTableName = keyRow["UNIQUE_CONSTRAINT_TABLE_NAME"].ToString(), Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany }; // check if it is already there if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName)) continue; oneMultiplicityKey_Foreign.UpdateAction = ConvertSqlCeForeignKeyAction(keyRow["UPDATE_RULE"].ToString()); oneMultiplicityKey_Foreign.DeleteAction = ConvertSqlCeForeignKeyAction(keyRow["DELETE_RULE"].ToString()); // to the list foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign); // apply local column DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName); oneMultiplicityKey_Foreign.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (primaryKeyTable != null) { // foreign table of that! oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable; // apply foreign column DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName); oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn; } else { oneMultiplicityKey_Foreign.ForeignTable = null; oneMultiplicityKey_Foreign.ForeignColumn = null; } } } // all keys } } finally { _dbConnection.Close(); } }
/// <summary> /// Reads specified table foreign keys. /// </summary> private void ApplyTablesForeignKeys(List<DbTable> tables) { if (_dbConnection.State != ConnectionState.Open) _dbConnection.Open(); // Used to get columns Sql DataType using (DataTable foreignKeysTable = _dbConnection.GetSchema("ForeignKeys")) { // nothing found! if (foreignKeysTable.Rows.Count == 0) return; // find description if there is any foreach (var table in tables) { // only FOREIGN KEY foreignKeysTable.DefaultView.RowFilter = string.Format(" CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME='{0}' ", table.TableName); // Fetch the rows foreach (DataRowView keysData in foreignKeysTable.DefaultView) { // foreign key found! DataRow keyRow = keysData.Row; var foreignKeyTableName = keyRow["FKEY_TO_TABLE"].ToString(); var primaryKeyTableName = table.TableName; var foreignKeyTable = FindTable(tables, foreignKeyTableName); var primaryKeyTable = table; if (primaryKeyTable != null) { // foreign key var foreignKey = new DbForeignKey() { ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(), LocalColumnName = keyRow["FKEY_FROM_COLUMN"].ToString(), ForeignColumnName = keyRow["FKEY_TO_COLUMN"].ToString(), ForeignTableName = keyRow["FKEY_TO_TABLE"].ToString(), Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany }; // add foreign key table.ForeignKeys.Add(foreignKey); // apply local column DbColumn localColumn = table.FindColumnDb(foreignKey.LocalColumnName); foreignKey.LocalColumn = localColumn; //apply foreign table DbTable foreignTable = foreignKeyTable; // referenced key if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = foreignTable; } if (foreignTable != null) { foreignKey.ForeignTable = foreignTable; // apply foreign column DbColumn foreignColumn = foreignTable.FindColumnDb(foreignKey.ForeignColumnName); foreignKey.ForeignColumn = foreignColumn; } else { foreignKey.ForeignTable = null; foreignKey.ForeignColumn = null; } } // adding the relation to the foreign table! if (foreignKeyTable != null) { // foreign key var oneMultiplicityKey_Foreign = new DbForeignKey() { ForeignKeyName = keyRow["CONSTRAINT_NAME"].ToString(), LocalColumnName = keyRow["FKEY_TO_COLUMN"].ToString(), ForeignColumnName = keyRow["FKEY_FROM_COLUMN"].ToString(), ForeignTableName = primaryKeyTableName, Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne }; // check if it is already there if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName)) continue; // to the list foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign); // apply local column DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName); oneMultiplicityKey_Foreign.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (primaryKeyTable != null) { // foreign table of that! oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable; // apply foreign column DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName); oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn; } else { oneMultiplicityKey_Foreign.ForeignTable = null; oneMultiplicityKey_Foreign.ForeignColumn = null; } } } } } }
/// <summary> /// One table, one column or one foreignKey in table! /// </summary> string PatternContentAppliesTo_OneTable(string baseContent, List<PatternContent> patternContent, DbTable table, DbColumn column, DbForeignKey foreignKey) { string appliedContent = ""; // --------------------------------- // Only one table is applying here! // table can not be null here if (table == null) { return baseContent; } foreach (var pattern in patternContent) { string replacementName = string.Format(ReplaceConsts.PatternContentReplacer, pattern.Name); // is there a pattern for that if (baseContent.IndexOf(replacementName) == -1) continue; switch (pattern.ConditionKeyMode) { case PatternConditionKeyMode.General: // nothing! break; case PatternConditionKeyMode.TablesAll: case PatternConditionKeyMode.ViewsAll: case PatternConditionKeyMode.TablesAndViewsAll: // for one table? Meh, we do nothing! break; case PatternConditionKeyMode.TableAutoIncrement: case PatternConditionKeyMode.TableIndexConstraint: case PatternConditionKeyMode.TablePrimaryKey: case PatternConditionKeyMode.TableUniqueConstraint: appliedContent = ConditionItem_AppliesToTable(pattern, table); // base content if (!string.IsNullOrEmpty(pattern.BaseContent)) { appliedContent = pattern.BaseContent.Replace(ReplaceConsts.PatternContentInnerContents, appliedContent); } // internal pattern contents if (pattern.ConditionContents.Count > 0) { // nested call appliedContent = PatternContentAppliesTo_OneTable(appliedContent, pattern.ConditionContents, table, null, null); } // replace the content baseContent = baseContent.Replace(replacementName, appliedContent); break; case PatternConditionKeyMode.Field: case PatternConditionKeyMode.FieldCondensedType: case PatternConditionKeyMode.FieldKeyReadType: case PatternConditionKeyMode.FieldKeyType: case PatternConditionKeyMode.FieldPrimaryKey: case PatternConditionKeyMode.FieldReferencedKeyType: appliedContent = ""; // no special column is specified if (column == null) { // replace in the main content baseContent = Common.ReplaceEx(baseContent, replacementName, appliedContent, StringComparison.CurrentCulture); } else { // Apply the replacement to the pattern content string columnReplace = ConditionItem_AppliesToColumn(pattern, table, column); // The seperator if (!string.IsNullOrEmpty(columnReplace)) { // internal pattern contents // FOR EACH column if (pattern.ConditionContents.Count > 0) { // nested call columnReplace = PatternContentAppliesTo_OneTable( columnReplace, pattern.ConditionContents, table, column, null); } appliedContent += columnReplace + pattern.ItemsSeperator; } } // Remove additional ItemsSeperator if (!string.IsNullOrEmpty(pattern.ItemsSeperator) && appliedContent.EndsWith(pattern.ItemsSeperator)) appliedContent = appliedContent.Remove(appliedContent.Length - pattern.ItemsSeperator.Length, pattern.ItemsSeperator.Length); // internal pattern contents // FOR EACH column if (pattern.ConditionContents.Count > 0) { // nested call appliedContent = PatternContentAppliesTo_OneTable(appliedContent, pattern.ConditionContents, table, null, null); } // replace in the main content baseContent = Common.ReplaceEx(baseContent, replacementName, appliedContent, StringComparison.CurrentCulture); break; case PatternConditionKeyMode.FieldsAll: case PatternConditionKeyMode.FieldsCondensedTypeAll: case PatternConditionKeyMode.FieldsKeyReadTypeAll: case PatternConditionKeyMode.FieldsKeyTypeAll: case PatternConditionKeyMode.FieldsPrimaryKeyAll: case PatternConditionKeyMode.FieldsReferencedKeyTypeAll: appliedContent = ""; // fetch the columns and apply the replacement operation foreach (var tableColumn in table.SchemaColumns) { // Apply the replacement to the pattern content string columnReplace = ConditionItem_AppliesToColumn(pattern, table, tableColumn); // The seperator if (!string.IsNullOrEmpty(columnReplace)) { // internal pattern contents // FOR EACH column if (pattern.ConditionContents.Count > 0) { // nested call columnReplace = PatternContentAppliesTo_OneTable( columnReplace, pattern.ConditionContents, table, tableColumn, null); } appliedContent += columnReplace + pattern.ItemsSeperator; } } // Remove additional ItemsSeperator if (!string.IsNullOrEmpty(pattern.ItemsSeperator) && appliedContent.EndsWith(pattern.ItemsSeperator)) appliedContent = appliedContent.Remove(appliedContent.Length - pattern.ItemsSeperator.Length, pattern.ItemsSeperator.Length); // internal pattern contents // FOR EACH column if (pattern.ConditionContents.Count > 0) { // nested call appliedContent = PatternContentAppliesTo_OneTable(appliedContent, pattern.ConditionContents, table, null, null); } // replace in the main content baseContent = Common.ReplaceEx(baseContent, replacementName, appliedContent, StringComparison.CurrentCulture); break; case PatternConditionKeyMode.ForeignKeyDeleteAction: case PatternConditionKeyMode.ForeignKeyUpdateAction: case PatternConditionKeyMode.FieldForeignKey: appliedContent = ""; if (foreignKey == null) { // replace in the main content baseContent = Common.ReplaceEx(baseContent, replacementName, appliedContent, StringComparison.CurrentCulture); } else { // Apply the replacement to the pattern content string columnReplace = ConditionItem_AppliesToForeignKeyColumns(pattern, table, foreignKey); // The seperator if (!string.IsNullOrEmpty(columnReplace)) { // internal pattern contents if (pattern.ConditionContents.Count > 0) { // nested call columnReplace = PatternContentAppliesTo_OneTable( columnReplace, pattern.ConditionContents, table, null, foreignKey); } appliedContent += columnReplace + pattern.ItemsSeperator; } } // Remove additional ItemsSeperator if (!string.IsNullOrEmpty(pattern.ItemsSeperator) && appliedContent.EndsWith(pattern.ItemsSeperator)) appliedContent = appliedContent.Remove(appliedContent.Length - pattern.ItemsSeperator.Length, pattern.ItemsSeperator.Length); // internal pattern contents if (pattern.ConditionContents.Count > 0) { // nested call appliedContent = PatternContentAppliesTo_OneTable(appliedContent, pattern.ConditionContents, table, null, null); } // replace in the main content baseContent = Common.ReplaceEx(baseContent, replacementName, appliedContent, StringComparison.CurrentCulture); break; case PatternConditionKeyMode.FieldsForeignKeyAll: case PatternConditionKeyMode.TableForeignKey: appliedContent = ""; // fetch the columns and apply the replacement operation foreach (var dbForeignKey in table.ForeignKeys) { // Apply the replacement to the pattern content string columnReplace = ConditionItem_AppliesToForeignKeyColumns(pattern, table, dbForeignKey); // The seperator if (!string.IsNullOrEmpty(columnReplace)) { // internal pattern contents if (pattern.ConditionContents.Count > 0) { // nested call columnReplace = PatternContentAppliesTo_OneTable( columnReplace, pattern.ConditionContents, table, null, dbForeignKey); } appliedContent += columnReplace + pattern.ItemsSeperator; } } // Remove additional ItemsSeperator if (!string.IsNullOrEmpty(pattern.ItemsSeperator) && appliedContent.EndsWith(pattern.ItemsSeperator)) appliedContent = appliedContent.Remove(appliedContent.Length - pattern.ItemsSeperator.Length, pattern.ItemsSeperator.Length); // internal pattern contents if (pattern.ConditionContents.Count > 0) { // nested call appliedContent = PatternContentAppliesTo_OneTable(appliedContent, pattern.ConditionContents, table, null, null); } // replace in the main content baseContent = Common.ReplaceEx(baseContent, replacementName, appliedContent, StringComparison.CurrentCulture); break; default: break; } } return baseContent; }
/// <summary> /// Partial content replacer. /// </summary> string ConditionItem_AppliesToForeignKeyColumns(PatternContent partialContent, DbTable table, DbForeignKey foreignKey) { switch (partialContent.ConditionKeyMode) { case PatternConditionKeyMode.FieldsForeignKeyAll: ConditionItem theReplacer; switch (foreignKey.Multiplicity) { case DbForeignKey.ForeignKeyMultiplicity.OneToMany: theReplacer = partialContent.GetReplacement(ConditionKeyModeConsts.FieldForeignKey.MultiplicityOne); break; case DbForeignKey.ForeignKeyMultiplicity.ManyToOne: theReplacer = partialContent.GetReplacement(ConditionKeyModeConsts.FieldForeignKey.MultiplicityMany); break; default: // not defined Multiplicity return string.Empty; } // Replace the contents return Replacer_ConditionItem_AppliesToForeignKey(theReplacer.ContentText, table, foreignKey); case PatternConditionKeyMode.ForeignKeyUpdateAction: theReplacer = partialContent.GetReplacement(foreignKey.UpdateAction.ToString()); if (theReplacer == null) return string.Empty; return Replacer_ConditionItem_AppliesToForeignKey(theReplacer.ContentText, table, foreignKey); case PatternConditionKeyMode.ForeignKeyDeleteAction: theReplacer = partialContent.GetReplacement(foreignKey.DeleteAction.ToString()); if (theReplacer == null) return string.Empty; return Replacer_ConditionItem_AppliesToForeignKey(theReplacer.ContentText, table, foreignKey); default: // Ignored return string.Empty; } }
/// <summary> /// Applies foreign key column data to pattern content replacement /// </summary> string Replacer_ConditionItem_AppliesToForeignKey(string content, DbTable table, DbForeignKey foreignKey) { // NOTE: foreign keys are always for TABLEs // Checking if user has selected this table // Also checking the option! if (!_optionGenerateUnselectedForeigns && !UserHasSelectedTable(foreignKey.ForeignTableName)) { // User has not selected this foreign table return string.Empty; } // general content = Replacer_GeneratorGeneral(content); // database provider class content = Replacer_DatabaseProvider(content); // local table content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.LocalTableName, table.TableNameSchema); content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.LocalTableNameDb, table.TableName); // foreign table content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.ForeignTableNameAsField, foreignKey.ForeignTableNameInLocalTable); content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.ForeignTableName, foreignKey.ForeignTable.TableNameSchema); content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.ForeignTableNameDb, foreignKey.ForeignTable.TableName); // =================================== // local field content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.LocalFieldDataType, foreignKey.LocalColumn.DataTypeDotNet); content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.LocalFieldName, foreignKey.LocalColumn.FieldNameSchema); content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.LocalFieldNameDb, foreignKey.LocalColumn.FieldNameDb); // no oridianl, foreignContent = ReplaceExIgnoreCase(foreignContent, ReplaceConsts.LocalFieldOrdinalValue, foreignKey.LocalColumn.ColumnOrdinal.ToString()); // column description content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.LocalFieldDescription, foreignKey.LocalColumn.UserDescription); // Database field type content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.LocalFieldDbTypeSize, FieldType_ColumnDataTypeSize(foreignKey.LocalColumn)); // =================================== // Foreign field content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.ForeignFieldDataType, foreignKey.ForeignColumn.DataTypeDotNet); content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.ForeignFieldName, foreignKey.ForeignColumn.FieldNameSchema); content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.ForeignFieldNameDb, foreignKey.ForeignColumn.FieldNameDb); // no oridianl, foreignContent = ReplaceExIgnoreCase(foreignContent, ReplaceConsts.ForeignFieldOrdinalValue, foreignKey.ForeignColumn.ColumnOrdinal.ToString()); // column description content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.ForeignFieldDescription, foreignKey.ForeignColumn.UserDescription); // Database field type content = Common.ReplaceExIgnoreCase(content, ReplaceConsts.ForeignFieldDbTypeSize, FieldType_ColumnDataTypeSize(foreignKey.ForeignColumn)); return content; }
/// <summary> /// Reads specified table foreign keys. /// </summary> private void ApplyTablesForeignKeys(List<DbTable> tables) { if (Cache_ForeignKeys.Rows.Count == 0) return; // adding the foreign keys foreach (DataRow keysDataRow in Cache_ForeignKeys.Rows) { var foreignKeyTableName = keysDataRow["FOREIGN_KEY_TABLE_NAME"].ToString(); var primaryKeyTableName = keysDataRow["PRIMARY_KEY_TABLE_NAME"].ToString(); var foreignKeyConstraintName = keysDataRow["FOREIGN_KEY_CONSTRAINT_NAME"].ToString(); var primaryKeyConstraintName = keysDataRow["PRIMARY_KEY_CONSTRAINT_NAME"].ToString(); string foreignKeyColumnName = null; string primaryKeyColumnName = null; // read the columns info DataRow[] columnInfo; // reading foreign key column info columnInfo = Cache_All_Constraints.Select(string.Format("CONSTRAINT_NAME='{0}'", foreignKeyConstraintName)); if (columnInfo != null && columnInfo.Length > 0) { foreignKeyColumnName = columnInfo[0]["COLUMN_NAME"].ToString(); } // reading primary key column info columnInfo = Cache_All_Constraints.Select(string.Format("CONSTRAINT_NAME='{0}'", primaryKeyConstraintName)); if (columnInfo != null && columnInfo.Length > 0) { primaryKeyColumnName = columnInfo[0]["COLUMN_NAME"].ToString(); } // there should be column names! if (foreignKeyColumnName == null || primaryKeyColumnName == null) continue; // find schema tables model var foreignKeyTable = FindTable(tables, foreignKeyTableName); var primaryKeyTable = FindTable(tables, primaryKeyTableName); // there should be tables! if (foreignKeyTable == null || primaryKeyTable == null) continue; if (foreignKeyTable != null) { // foreign key many end var oneMultiplicityKey = new DbForeignKey() { ForeignKeyName = foreignKeyConstraintName, LocalColumnName = foreignKeyColumnName, ForeignColumnName = primaryKeyColumnName, ForeignTableName = primaryKeyTableName, Multiplicity = DbForeignKey.ForeignKeyMultiplicity.OneToMany }; // check if it is already there if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey.ForeignKeyName)) continue; //oneMultiplicityKey.UpdateAction = // ConvertOracleForeignKeyAction(keysDataRow["UPDATE_RULE"].ToString()); oneMultiplicityKey.DeleteAction = ConvertOracleForeignKeyAction(keysDataRow["DELETE_RULE"].ToString()); // to the list foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey); // apply local column DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey.LocalColumnName); oneMultiplicityKey.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (primaryKeyTable != null) { // foreign table of that! oneMultiplicityKey.ForeignTable = primaryKeyTable; // apply foreign column DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey.ForeignColumnName); oneMultiplicityKey.ForeignColumn = foreignColumn; } else { oneMultiplicityKey.ForeignTable = null; oneMultiplicityKey.ForeignColumn = null; } } if (primaryKeyTable != null) { // foreign key many end var manyMultiplicityKey = new DbForeignKey() { ForeignKeyName = primaryKeyConstraintName, LocalColumnName = primaryKeyColumnName, ForeignColumnName = foreignKeyColumnName, ForeignTableName = foreignKeyTableName, Multiplicity = DbForeignKey.ForeignKeyMultiplicity.ManyToOne }; // check if it is already there if (primaryKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == manyMultiplicityKey.ForeignKeyName)) continue; //manyMultiplicityKey.UpdateAction = // ConvertOracleForeignKeyAction(keysDataRow["UPDATE_RULE"].ToString()); manyMultiplicityKey.DeleteAction = ConvertOracleForeignKeyAction(keysDataRow["DELETE_RULE"].ToString()); // to the list primaryKeyTable.ForeignKeys.Add(manyMultiplicityKey); // apply local column DbColumn localColumn = primaryKeyTable.FindColumnDb(manyMultiplicityKey.LocalColumnName); manyMultiplicityKey.LocalColumn = localColumn; if (!localColumn.PrimaryKey) { localColumn.IsReferenceKey = true; localColumn.IsReferenceKeyTable = primaryKeyTable; } if (foreignKeyTable != null) { // foreign table of that! manyMultiplicityKey.ForeignTable = foreignKeyTable; // apply foreign column DbColumn foreignColumn = foreignKeyTable.FindColumnDb(manyMultiplicityKey.ForeignColumnName); manyMultiplicityKey.ForeignColumn = foreignColumn; } else { manyMultiplicityKey.ForeignTable = null; manyMultiplicityKey.ForeignColumn = null; } } } }