public static List <TableConstraint> GetConstraints()
{
try
{
OpenConnection();
Query = "select top 20 CONSTRAINT_CATALOG as 'Constraint Catalog', CONSTRAINT_NAME as 'Constraint Name', TABLE_CATALOG as 'Table Catalog' from [Demo Database NAV (5-0)].INFORMATION_SCHEMA.TABLE_CONSTRAINTS order by TABLE_NAME";
SqlCommand cmd = new SqlCommand(Query, connection);
dataReader = cmd.ExecuteReader();
constraints = new List <TableConstraint>();
while (dataReader.Read())
{
TableConstraint t = new TableConstraint();
t.TableConstraintCatalog = dataReader["Constraint Catalog"].ToString();
t.Name = dataReader["Constraint Name"].ToString();
t.TableCatalog = dataReader["Table Catalog"].ToString();
constraints.Add(t);
}
return(constraints);
}
finally
{
CloseConnection();
}
}
public override string ToQuery()
{
switch (Type)
{
case DataTypeOperation.From:
return(string.Format("create type [{0}].[{1}] from {2}", SchemaName, Name, From));
case DataTypeOperation.Assembly:
return(string.Format("create type [{0}].[{1}] external name {2}.[{3}]", SchemaName, Name, AssemblyName, ClassName));
case DataTypeOperation.Table:
var columnDefinitionList = new List <string>();
foreach (Column column in AsTable)
{
columnDefinitionList.Add(column.GetColumnDefinition(false));
}
string columnDefinitionClause = string.Join(", ", columnDefinitionList.ToArray());
var tableConstraintClause = string.Join(", ", TableConstraint.ToArray());
columnDefinitionClause = string.Format("({0} {1})", columnDefinitionClause, tableConstraintClause);
var query = string.Format("create type [{0}].[{1}] as table {2}", SchemaName, Name, columnDefinitionClause);
return(query);
default:
throw new ArgumentOutOfRangeException();
}
}
private void CompareTableConstraints(TableConstraint tc1, TableConstraint tc2)
{
//Assert.AreEqual(tc1.IsDeferrable, tc2.IsDeferrable);
//Assert.AreEqual(tc1.IsInitiallyDeferred, tc2.IsInitiallyDeferred);
Assert.AreEqual(tc1.Name, tc2.Name);
Assert.AreEqual(tc1.Owner.Name, tc2.Owner.Name);
Assert.AreEqual(tc1.Table.Name, tc2.Table.Name);
}
public TableConstraintDefinitionImpl(SQLVendorImpl vendor, String name, ConstraintCharacteristics?chars, TableConstraint constraint)
: base(vendor)
{
ArgumentValidator.ValidateNotNull(nameof(constraint), constraint);
this._name = name;
this._chars = chars;
this._constraint = constraint;
}
private void CloneTableConstraint(Table newTable, TableConstraint sourceConstraint)
{
var checkConstraint = sourceConstraint as CheckConstraint;
if (checkConstraint != null)
{
var c = newTable.CreateCheckConstraint(checkConstraint.Name, (SqlExpression)checkConstraint.Condition.Clone());
CopyDbName(c, checkConstraint);
return;
}
var defaultConstraint = sourceConstraint as DefaultConstraint;
if (defaultConstraint != null)
{
var c = newTable.CreateDefaultConstraint(defaultConstraint.Name, newTable.TableColumns[defaultConstraint.Column.Name]);
c.NameIsStale = defaultConstraint.NameIsStale;
CopyDbName(c, defaultConstraint);
}
//foreign keys are handled by special method
var foreignKey = sourceConstraint as ForeignKey;
if (foreignKey != null)
{
return;
}
var uniqueConstraint = sourceConstraint as UniqueConstraint;
if (uniqueConstraint != null)
{
var primaryKey = sourceConstraint as PrimaryKey;
if (primaryKey != null)
{
var columns = primaryKey.Columns.Select(c => newTable.TableColumns[c.Name]).ToArray();
var pk = newTable.CreatePrimaryKey(primaryKey.Name, columns);
CopyDbName(pk, primaryKey);
}
else
{
var columns = uniqueConstraint.Columns.Select(c => newTable.TableColumns[c.Name]).ToArray();
var uc = newTable.CreateUniqueConstraint(uniqueConstraint.Name, columns);
CopyDbName(uc, uniqueConstraint);
}
return;
}
throw new ArgumentOutOfRangeException("sourceConstraint", Strings.ExUnexpectedTypeOfParameter);
}
private static void ApplyTableParameters(
[NotNull] ITest test,
[NotNull] IList <TableConstraint> sortedTableParameters)
{
int tableCount = sortedTableParameters.Count;
if (tableCount == 0)
{
// Geodatabase Topology / Geometric Network tests
}
else if (tableCount != test.InvolvedTables.Count)
{
throw new InvalidOperationException(
string.Format("Error in implementation of {0}:\n" +
" {0} instance contains {1} tables, expected are {2}",
test.GetType(), test.InvolvedTables.Count, tableCount));
}
if (tableCount > 0)
{
Assert.NotNull(test.InvolvedTables, "involved tables is null");
for (var tableIndex = 0; tableIndex < tableCount; tableIndex++)
{
TableConstraint value = sortedTableParameters[tableIndex];
ITable table = value.Table;
if (table != test.InvolvedTables[tableIndex])
{
throw new InvalidOperationException(
string.Format(
"Error in implementation of {0}: table #{1} in instance is {2}, expected is {3}",
test.GetType(), tableIndex,
((IDataset)test.InvolvedTables[tableIndex]).Name,
((IDataset)table).Name));
}
if (StringUtils.IsNotEmpty(value.FilterExpression))
{
test.SetConstraint(tableIndex, value.FilterExpression);
}
test.SetSqlCaseSensitivity(tableIndex, value.QaSqlIsCaseSensitive);
}
}
}
private void CompareTables(Table t1, Table t2)
{
Assert.IsNotNull(t1);
Assert.IsNotNull(t2);
Assert.AreEqual(t1.Filegroup, t2.Filegroup);
Assert.AreEqual(t1.Schema.Name, t2.Schema.Name);
Assert.AreEqual(t1.TableColumns.Count, t2.TableColumns.Count);
Assert.AreEqual(t1.TableConstraints.Count, t2.TableConstraints.Count);
foreach (TableColumn c1 in t1.TableColumns)
{
TableColumn c2 = t2.TableColumns[c1.Name];
Assert.IsNotNull(c2);
CompareTableColumns(c1, c2);
}
foreach (Index i1 in t1.Indexes)
{
Index i2 = t2.Indexes[i1.Name];
Assert.IsNotNull(i2);
CompareTableIndexes(i1, i2);
}
foreach (TableConstraint tc1 in t1.TableConstraints)
{
TableConstraint tc2 = t2.TableConstraints[tc1.Name];
Assert.IsNotNull(tc2);
if (tc1 is CheckConstraint)
{
CompareCheckConstraints(tc1 as CheckConstraint, tc2 as CheckConstraint);
}
else if (tc1 is PrimaryKey)
{
ComparePrimaryKeys(tc1 as PrimaryKey, tc2 as PrimaryKey);
}
else if (tc1 is UniqueConstraint)
{
CompareUniqueConstraints(tc1 as UniqueConstraint, tc2 as UniqueConstraint);
}
else if (tc1 is ForeignKey)
{
CompareForeignKeys(tc1 as ForeignKey, tc2 as ForeignKey);
}
}
}
public List<Script> GetConstraintAlterScripts(TableConstraint oldConstraint, TableConstraint newConstraint)
{
List<Script> scripts = new List<Script>();
if (oldConstraint != null)
{
scripts.Add(this.scriptGenerator.DropCheckConstraint(oldConstraint));
}
scripts.Add(this.scriptGenerator.AddCheckConstraint(newConstraint));
if (!string.IsNullOrEmpty(newConstraint.Comment))
{
this.SetTableChildComment(scripts, this.scriptGenerator, newConstraint, true);
}
return scripts;
}
public abstract Script AddCheckConstraint(TableConstraint constraint);
public override Script AddCheckConstraint(TableConstraint constraint)
{
return(new CreateDbObjectScript <TableConstraint>($"ALTER TABLE {this.GetQuotedFullTableName(constraint)} ADD CONSTRAINT {this.GetQuotedString(constraint.Name)} CHECK ({constraint.Definition});"));
}
public SchemaInfo GetSchemaInfo(SchemaDesignerInfo schemaDesignerInfo)
{
SchemaInfo schemaInfo = new SchemaInfo();
Table table = new Table();
ObjectHelper.CopyProperties(schemaDesignerInfo.TableDesignerInfo, table);
schemaInfo.Tables.Add(table);
#region Columns
TablePrimaryKey primaryKey = null;
foreach (TableColumnDesingerInfo column in schemaDesignerInfo.TableColumnDesingerInfos)
{
TableColumn tableColumn = new TableColumn();
ObjectHelper.CopyProperties(column, tableColumn);
if (!tableColumn.IsUserDefined)
{
ColumnManager.SetColumnLength(this.dbInterpreter.DatabaseType, tableColumn, column.Length);
}
if (column.IsPrimary)
{
if (primaryKey == null)
{
primaryKey = new TablePrimaryKey()
{
Owner = table.Owner, TableName = table.Name, Name = IndexManager.GetPrimaryKeyDefaultName(table)
};
}
IndexColumn indexColumn = new IndexColumn()
{
ColumnName = column.Name, IsDesc = false, Order = primaryKey.Columns.Count + 1
};
if (!schemaDesignerInfo.IgnoreTableIndex)
{
TableIndexDesignerInfo indexDesignerInfo = schemaDesignerInfo.TableIndexDesingerInfos
.FirstOrDefault(item => item.Type == IndexType.Primary.ToString() && item.Columns.Any(t => t.ColumnName == column.Name));
if (indexDesignerInfo != null)
{
primaryKey.Name = indexDesignerInfo.Name;
primaryKey.Comment = indexDesignerInfo.Comment;
IndexColumn columnInfo = indexDesignerInfo.Columns.FirstOrDefault(item => item.ColumnName == column.Name);
if (columnInfo != null)
{
indexColumn.IsDesc = columnInfo.IsDesc;
}
if (indexDesignerInfo.ExtraPropertyInfo != null)
{
primaryKey.Clustered = indexDesignerInfo.ExtraPropertyInfo.Clustered;
}
}
}
primaryKey.Columns.Add(indexColumn);
}
TableColumnExtraPropertyInfo extralProperty = column.ExtraPropertyInfo;
if (column.IsIdentity)
{
if (extralProperty != null)
{
table.IdentitySeed = extralProperty.Seed;
table.IdentityIncrement = extralProperty.Increment;
}
else
{
table.IdentitySeed = 1;
table.IdentityIncrement = 1;
}
}
if (extralProperty?.Expression != null)
{
tableColumn.ComputeExp = extralProperty.Expression;
}
schemaInfo.TableColumns.Add(tableColumn);
}
if (primaryKey != null)
{
schemaInfo.TablePrimaryKeys.Add(primaryKey);
}
#endregion
#region Indexes
if (!schemaDesignerInfo.IgnoreTableIndex)
{
foreach (TableIndexDesignerInfo indexDesignerInfo in schemaDesignerInfo.TableIndexDesingerInfos)
{
if (!indexDesignerInfo.IsPrimary)
{
TableIndex index = new TableIndex()
{
Owner = indexDesignerInfo.Owner, TableName = indexDesignerInfo.TableName
};
index.Name = indexDesignerInfo.Name;
index.IsUnique = indexDesignerInfo.Type == IndexType.Unique.ToString();
index.Clustered = indexDesignerInfo.Clustered;
index.Comment = indexDesignerInfo.Comment;
index.Columns.AddRange(indexDesignerInfo.Columns);
int order = 1;
index.Columns.ForEach(item => { item.Order = order++; });
schemaInfo.TableIndexes.Add(index);
}
}
}
#endregion
#region Foreign Keys
if (!schemaDesignerInfo.IgnoreTableForeignKey)
{
foreach (TableForeignKeyDesignerInfo keyDesignerInfo in schemaDesignerInfo.TableForeignKeyDesignerInfos)
{
TableForeignKey foreignKey = new TableForeignKey()
{
Owner = keyDesignerInfo.Owner, TableName = keyDesignerInfo.TableName
};
foreignKey.Name = keyDesignerInfo.Name;
foreignKey.ReferencedTableName = keyDesignerInfo.ReferencedTableName;
foreignKey.UpdateCascade = keyDesignerInfo.UpdateCascade;
foreignKey.DeleteCascade = keyDesignerInfo.DeleteCascade;
foreignKey.Comment = keyDesignerInfo.Comment;
foreignKey.Columns.AddRange(keyDesignerInfo.Columns);
int order = 1;
foreignKey.Columns.ForEach(item => { item.Order = order++; });
schemaInfo.TableForeignKeys.Add(foreignKey);
}
}
#endregion
#region Constraint
if (!schemaDesignerInfo.IgnoreTableConstraint)
{
foreach (TableConstraintDesignerInfo constraintDesignerInfo in schemaDesignerInfo.TableConstraintDesignerInfos)
{
TableConstraint constraint = new TableConstraint()
{
Owner = constraintDesignerInfo.Owner, TableName = constraintDesignerInfo.TableName
};
constraint.Name = constraintDesignerInfo.Name;
constraint.Definition = constraintDesignerInfo.Definition;
constraint.Comment = constraintDesignerInfo.Comment;
schemaInfo.TableConstraints.Add(constraint);
}
}
#endregion
return(schemaInfo);
}
public abstract Script DropCheckConstraint(TableConstraint constraint);
/// <inheritdoc/>
/// <exception cref="NotSupportedException">Method is not supported.</exception>
protected override IPathNode VisitTableConstraint(TableConstraint constraint)
{
throw new NotSupportedException();
}
public async Task <ContentSaveResult> GenerateChangeScripts(SchemaDesignerInfo schemaDesignerInfo, bool isNew)
{
string validateMsg;
Table table = null;
try
{
bool isValid = this.ValidateModel(schemaDesignerInfo, out validateMsg);
if (!isValid)
{
return(this.GetFaultSaveResult(validateMsg));
}
TableDesignerGenerateScriptsData scriptsData = new TableDesignerGenerateScriptsData();
SchemaInfo schemaInfo = this.GetSchemaInfo(schemaDesignerInfo);
table = schemaInfo.Tables.First();
scriptsData.Table = table;
DbScriptGenerator scriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(this.dbInterpreter);
List <Script> scripts = new List <Script>();
if (isNew)
{
ScriptBuilder scriptBuilder = scriptGenerator.GenerateSchemaScripts(schemaInfo);
scripts = scriptBuilder.Scripts;
}
else
{
#region Alter Table
scripts = new List <Script>();
TableDesignerInfo tableDesignerInfo = schemaDesignerInfo.TableDesignerInfo;
SchemaInfoFilter filter = new SchemaInfoFilter()
{
Strict = true
};
filter.TableNames = new string[] { tableDesignerInfo.OldName };
filter.DatabaseObjectType = DatabaseObjectType.Table
| DatabaseObjectType.TableColumn
| DatabaseObjectType.TablePrimaryKey
| DatabaseObjectType.TableForeignKey
| DatabaseObjectType.TableIndex
| DatabaseObjectType.TableConstraint;
if (this.dbInterpreter.DatabaseType == DatabaseType.Oracle)
{
this.dbInterpreter.Option.IncludePrimaryKeyWhenGetTableIndex = true;
}
SchemaInfo oldSchemaInfo = await this.dbInterpreter.GetSchemaInfoAsync(filter);
Table oldTable = oldSchemaInfo.Tables.FirstOrDefault();
if (oldTable == null)
{
return(this.GetFaultSaveResult($"Table \"{tableDesignerInfo.OldName}\" is not existed"));
}
if (tableDesignerInfo.OldName != tableDesignerInfo.Name)
{
scripts.Add(scriptGenerator.RenameTable(new Table()
{
Owner = tableDesignerInfo.Owner, Name = tableDesignerInfo.OldName
}, tableDesignerInfo.Name));
}
if (!this.IsStringEquals(tableDesignerInfo.Comment, oldTable.Comment))
{
oldTable.Comment = tableDesignerInfo.Comment;
scripts.Add(scriptGenerator.SetTableComment(oldTable, string.IsNullOrEmpty(oldTable.Comment)));
}
#region Columns
List <TableColumnDesingerInfo> columnDesingerInfos = schemaDesignerInfo.TableColumnDesingerInfos;
List <TableColumn> oldColumns = oldSchemaInfo.TableColumns;
List <TableDefaultValueConstraint> defaultValueConstraints = null;
if (this.dbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
defaultValueConstraints = await(this.dbInterpreter as SqlServerInterpreter).GetTableDefautValueConstraintsAsync(filter);
}
foreach (TableColumnDesingerInfo columnDesignerInfo in columnDesingerInfos)
{
TableColumn oldColumn = oldColumns.FirstOrDefault(item => item.Name.ToLower() == columnDesignerInfo.Name.ToLower());
TableColumn newColumn = schemaInfo.TableColumns.FirstOrDefault(item => item.Name == columnDesignerInfo.Name);
if (oldColumn == null)
{
scripts.Add(scriptGenerator.AddTableColumn(table, newColumn));
}
else
{
if (!this.IsValueEqualsIgnoreCase(columnDesignerInfo.OldName, columnDesignerInfo.Name))
{
scripts.Add(scriptGenerator.RenameTableColumn(table, oldColumn, newColumn.Name));
}
bool isDefaultValueEquals = this.IsStringEquals(ValueHelper.GetTrimedDefaultValue(oldColumn.DefaultValue), newColumn.DefaultValue);
if (!SchemaInfoHelper.IsTableColumnEquals(this.dbInterpreter.DatabaseType, oldColumn, newColumn) ||
!isDefaultValueEquals)
{
if (!isDefaultValueEquals)
{
if (this.dbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
SqlServerScriptGenerator sqlServerScriptGenerator = scriptGenerator as SqlServerScriptGenerator;
TableDefaultValueConstraint defaultValueConstraint = defaultValueConstraints?.FirstOrDefault(item => item.Owner == oldTable.Owner && item.TableName == oldTable.Name && item.ColumnName == oldColumn.Name);
if (defaultValueConstraint != null)
{
scripts.Add(sqlServerScriptGenerator.DropDefaultValueConstraint(defaultValueConstraint));
}
scripts.Add(sqlServerScriptGenerator.AddDefaultValueConstraint(newColumn));
}
}
Script alterColumnScript = scriptGenerator.AlterTableColumn(table, newColumn);
if (this.dbInterpreter.DatabaseType == DatabaseType.Oracle)
{
if (!oldColumn.IsNullable && !newColumn.IsNullable)
{
alterColumnScript.Content = Regex.Replace(alterColumnScript.Content, "NOT NULL", "", RegexOptions.IgnoreCase);
}
else if (oldColumn.IsNullable && newColumn.IsNullable)
{
alterColumnScript.Content = Regex.Replace(alterColumnScript.Content, "NULL", "", RegexOptions.IgnoreCase);
}
}
scripts.Add(alterColumnScript);
}
else if (!this.IsStringEquals(columnDesignerInfo.Comment, oldColumn.Comment))
{
scripts.Add(scriptGenerator.SetTableColumnComment(table, newColumn, string.IsNullOrEmpty(oldColumn.Comment)));
}
}
}
foreach (TableColumn oldColumn in oldColumns)
{
if (!columnDesingerInfos.Any(item => item.Name == oldColumn.Name))
{
scripts.Add(scriptGenerator.DropTableColumn(oldColumn));
}
}
#endregion
#region Primary Key
TablePrimaryKey oldPrimaryKey = oldSchemaInfo.TablePrimaryKeys.FirstOrDefault();
TablePrimaryKey newPrimaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault();
bool primaryKeyChanged = !SchemaInfoHelper.IsPrimaryKeyEquals(oldPrimaryKey, newPrimaryKey, schemaDesignerInfo.IgnoreTableIndex, true);
Action alterPrimaryKey = () =>
{
if (oldPrimaryKey != null)
{
scripts.Add(scriptGenerator.DropPrimaryKey(oldPrimaryKey));
}
scripts.Add(scriptGenerator.AddPrimaryKey(newPrimaryKey));
if (!string.IsNullOrEmpty(newPrimaryKey.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newPrimaryKey, true);
}
};
if (primaryKeyChanged)
{
alterPrimaryKey();
}
else if (!ValueHelper.IsStringEquals(oldPrimaryKey?.Comment, newPrimaryKey?.Comment))
{
if (this.dbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
this.SetTableChildComment(scripts, scriptGenerator, newPrimaryKey, string.IsNullOrEmpty(oldPrimaryKey?.Comment));
}
else
{
alterPrimaryKey();
}
}
#endregion
#region Index
if (!schemaDesignerInfo.IgnoreTableIndex)
{
IEnumerable <TableIndex> oldIndexes = oldSchemaInfo.TableIndexes.Where(item => !item.IsPrimary);
IEnumerable <TableIndexDesignerInfo> indexDesignerInfos = schemaDesignerInfo.TableIndexDesingerInfos.Where(item => !item.IsPrimary);
foreach (TableIndexDesignerInfo indexDesignerInfo in indexDesignerInfos)
{
TableIndex newIndex = schemaInfo.TableIndexes.FirstOrDefault(item => item.Name == indexDesignerInfo.Name);
TableIndex oldIndex = oldIndexes.FirstOrDefault(item => item.Name == indexDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(indexDesignerInfo.OldName, indexDesignerInfo.Name) &&
this.IsValueEqualsIgnoreCase(indexDesignerInfo.OldType, indexDesignerInfo.Type)
)
{
if (oldIndex != null && this.IsStringEquals(oldIndex.Comment, newIndex.Comment) && SchemaInfoHelper.IsIndexColumnsEquals(oldIndex.Columns, newIndex.Columns))
{
continue;
}
}
if (oldIndex != null)
{
scripts.Add(scriptGenerator.DropIndex(oldIndex));
}
scripts.Add(scriptGenerator.AddIndex(newIndex));
if (!string.IsNullOrEmpty(newIndex.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newIndex, true);
}
}
foreach (TableIndex oldIndex in oldIndexes)
{
if (!indexDesignerInfos.Any(item => item.Name == oldIndex.Name))
{
scripts.Add(scriptGenerator.DropIndex(oldIndex));
}
}
}
#endregion
#region Foreign Key
if (!schemaDesignerInfo.IgnoreTableForeignKey)
{
List <TableForeignKey> oldForeignKeys = oldSchemaInfo.TableForeignKeys;
IEnumerable <TableForeignKeyDesignerInfo> foreignKeyDesignerInfos = schemaDesignerInfo.TableForeignKeyDesignerInfos;
foreach (TableForeignKeyDesignerInfo foreignKeyDesignerInfo in foreignKeyDesignerInfos)
{
TableForeignKey newForeignKey = schemaInfo.TableForeignKeys.FirstOrDefault(item => item.Name == foreignKeyDesignerInfo.Name);
TableForeignKey oldForeignKey = oldForeignKeys.FirstOrDefault(item => item.Name == foreignKeyDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(foreignKeyDesignerInfo.OldName, foreignKeyDesignerInfo.Name) &&
foreignKeyDesignerInfo.UpdateCascade == oldForeignKey.UpdateCascade &&
foreignKeyDesignerInfo.DeleteCascade == oldForeignKey.DeleteCascade)
{
if (oldForeignKey != null && this.IsStringEquals(oldForeignKey.Comment, newForeignKey.Comment) &&
SchemaInfoHelper.IsForeignKeyColumnsEquals(oldForeignKey.Columns, newForeignKey.Columns))
{
continue;
}
}
if (oldForeignKey != null)
{
scripts.Add(scriptGenerator.DropForeignKey(oldForeignKey));
}
scripts.Add(scriptGenerator.AddForeignKey(newForeignKey));
if (!string.IsNullOrEmpty(newForeignKey.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newForeignKey, true);
}
}
foreach (TableForeignKey oldForeignKey in oldForeignKeys)
{
if (!foreignKeyDesignerInfos.Any(item => item.Name == oldForeignKey.Name))
{
scripts.Add(scriptGenerator.DropForeignKey(oldForeignKey));
}
}
}
#endregion
#region Constraint
if (!schemaDesignerInfo.IgnoreTableConstraint)
{
List <TableConstraint> oldConstraints = oldSchemaInfo.TableConstraints;
IEnumerable <TableConstraintDesignerInfo> constraintDesignerInfos = schemaDesignerInfo.TableConstraintDesignerInfos;
foreach (TableConstraintDesignerInfo constraintDesignerInfo in constraintDesignerInfos)
{
TableConstraint newConstraint = schemaInfo.TableConstraints.FirstOrDefault(item => item.Name == constraintDesignerInfo.Name);
TableConstraint oldConstraint = oldConstraints.FirstOrDefault(item => item.Name == constraintDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(constraintDesignerInfo.OldName, constraintDesignerInfo.Name))
{
if (oldConstraint != null && this.IsStringEquals(oldConstraint.Comment, newConstraint.Comment))
{
continue;
}
}
if (oldConstraint != null)
{
scripts.Add(scriptGenerator.DropCheckConstraint(oldConstraint));
}
scripts.Add(scriptGenerator.AddCheckConstraint(newConstraint));
if (!string.IsNullOrEmpty(newConstraint.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newConstraint, true);
}
}
foreach (TableConstraint oldConstraint in oldConstraints)
{
if (!constraintDesignerInfos.Any(item => item.Name == oldConstraint.Name))
{
scripts.Add(scriptGenerator.DropCheckConstraint(oldConstraint));
}
}
}
#endregion
#endregion
}
scriptsData.Scripts.AddRange(scripts);
return(new ContentSaveResult()
{
IsOK = true, ResultData = scriptsData
});
}
catch (Exception ex)
{
return(this.GetFaultSaveResult(ExceptionHelper.GetExceptionDetails(ex)));
}
}
private TableConstraint parseTableConstraint()
{
TableConstraint con = new TableConstraint();
if (fCurrentToken.Equals("CONSTRAINT"))
{
ReadNextToken(); // skip "constraint"
con.Name = fCurrentToken.Value.TrimQuotation();
ReadNextToken();
}
if (fCurrentToken.Equals("CHECK"))
{
con.ConstraintType = ConstraintTypes.Check;
ReadNextToken();
con.Check = ParseExpression();
}
if (fCurrentToken.Equals("DEFAULT"))
{
con.ConstraintType = ConstraintTypes.Default;
ReadNextToken();
con.Default = ParseExpression();
}
if (fCurrentToken.Equals("FOREIGN"))
{
con.ConstraintType = ConstraintTypes.ForeignKey;
ReadNextToken();
SkipExpected("KEY");
SkipExpected("(");
con.Columns = parseListString();
SkipExpected(")");
SkipExpected("REFERENCES");
con.RefTable = fCurrentToken.Value.TrimQuotation();
ReadNextToken();
if (fCurrentToken.Equals("("))
{
SkipExpected("(");
con.RefColumns = parseListString();
SkipExpected(")");
}
}
if (fCurrentToken.Equals("PRIMARY"))
{
con.ConstraintType = ConstraintTypes.PrimaryKey;
ReadNextToken();
SkipExpected("KEY");
SkipExpected("(");
con.Columns = parseListString();
SkipExpected(")");
}
if (fCurrentToken.Equals("UNIQUE"))
{
con.ConstraintType = ConstraintTypes.Unique;
ReadNextToken();
SkipExpected("(");
con.Columns = parseListString();
SkipExpected(")");
}
return con;
}
public virtual TableConstraintDefinition NewTableConstraintDefinition(TableConstraint constraint, ConstraintCharacteristics?constraintChars = null, String name = null)
{
return(new TableConstraintDefinitionImpl(this.vendor, name, constraintChars, constraint));
}
public override Script DropCheckConstraint(TableConstraint constraint)
{
return(new DropDbObjectScript <TableConstraint>(this.GetDropConstraintSql(constraint)));
}
public async Task<ContentSaveResult> GenerateChangeScripts(SchemaDesignerInfo schemaDesignerInfo, bool isNew)
{
string validateMsg;
Table table = null;
try
{
bool isValid = this.ValidateModel(schemaDesignerInfo, out validateMsg);
if (!isValid)
{
return this.GetFaultSaveResult(validateMsg);
}
TableDesignerGenerateScriptsData scriptsData = new TableDesignerGenerateScriptsData();
SchemaInfo schemaInfo = this.GetSchemaInfo(schemaDesignerInfo);
table = schemaInfo.Tables.First();
scriptsData.Table = table;
List<Script> scripts = new List<Script>();
if (isNew)
{
ScriptBuilder scriptBuilder = this.scriptGenerator.GenerateSchemaScripts(schemaInfo);
scripts.AddRange(scriptBuilder.Scripts);
}
else
{
#region Alter Table
TableDesignerInfo tableDesignerInfo = schemaDesignerInfo.TableDesignerInfo;
SchemaInfoFilter filter = new SchemaInfoFilter() { Strict = true };
filter.TableNames = new string[] { tableDesignerInfo.OldName };
filter.DatabaseObjectType = DatabaseObjectType.Table
| DatabaseObjectType.TableColumn
| DatabaseObjectType.TablePrimaryKey
| DatabaseObjectType.TableForeignKey
| DatabaseObjectType.TableIndex
| DatabaseObjectType.TableConstraint;
if (this.dbInterpreter.DatabaseType == DatabaseType.Oracle)
{
this.dbInterpreter.Option.IncludePrimaryKeyWhenGetTableIndex = true;
}
SchemaInfo oldSchemaInfo = await this.dbInterpreter.GetSchemaInfoAsync(filter);
Table oldTable = oldSchemaInfo.Tables.FirstOrDefault();
if (oldTable == null)
{
return this.GetFaultSaveResult($"Table \"{tableDesignerInfo.OldName}\" is not existed");
}
if (tableDesignerInfo.OldName != tableDesignerInfo.Name)
{
scripts.Add(this.scriptGenerator.RenameTable(new Table() { Owner = tableDesignerInfo.Owner, Name = tableDesignerInfo.OldName }, tableDesignerInfo.Name));
}
if (!this.IsStringEquals(tableDesignerInfo.Comment, oldTable.Comment))
{
oldTable.Comment = tableDesignerInfo.Comment;
scripts.Add(this.scriptGenerator.SetTableComment(oldTable, string.IsNullOrEmpty(oldTable.Comment)));
}
#region Columns
List<TableColumnDesingerInfo> columnDesingerInfos = schemaDesignerInfo.TableColumnDesingerInfos;
List<TableColumn> oldColumns = oldSchemaInfo.TableColumns;
List<TableDefaultValueConstraint> defaultValueConstraints = await this.GetTableDefaultConstraints(filter);
foreach (TableColumnDesingerInfo columnDesignerInfo in columnDesingerInfos)
{
TableColumn oldColumn = oldColumns.FirstOrDefault(item => item.Name.ToLower() == columnDesignerInfo.Name.ToLower());
TableColumn newColumn = schemaInfo.TableColumns.FirstOrDefault(item => item.Name == columnDesignerInfo.Name);
if (oldColumn == null)
{
scripts.Add(this.scriptGenerator.AddTableColumn(table, newColumn));
}
else
{
if (!this.IsValueEqualsIgnoreCase(columnDesignerInfo.OldName, columnDesignerInfo.Name))
{
scripts.Add(this.scriptGenerator.RenameTableColumn(table, oldColumn, newColumn.Name));
}
scripts.AddRange(this.GetColumnAlterScripts(oldTable, table, oldColumn, newColumn, defaultValueConstraints));
}
}
foreach (TableColumn oldColumn in oldColumns)
{
if (!columnDesingerInfos.Any(item => item.Name == oldColumn.Name))
{
scripts.Add(this.scriptGenerator.DropTableColumn(oldColumn));
}
}
#endregion
#region Primary Key
TablePrimaryKey oldPrimaryKey = oldSchemaInfo.TablePrimaryKeys.FirstOrDefault();
TablePrimaryKey newPrimaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault();
scripts.AddRange(this.GetPrimaryKeyAlterScripts(oldPrimaryKey, newPrimaryKey, schemaDesignerInfo.IgnoreTableIndex));
#endregion
#region Index
if (!schemaDesignerInfo.IgnoreTableIndex)
{
IEnumerable<TableIndex> oldIndexes = oldSchemaInfo.TableIndexes.Where(item => !item.IsPrimary);
IEnumerable<TableIndexDesignerInfo> indexDesignerInfos = schemaDesignerInfo.TableIndexDesingerInfos.Where(item => !item.IsPrimary);
foreach (TableIndexDesignerInfo indexDesignerInfo in indexDesignerInfos)
{
TableIndex newIndex = schemaInfo.TableIndexes.FirstOrDefault(item => item.Name == indexDesignerInfo.Name);
TableIndex oldIndex = oldIndexes.FirstOrDefault(item => item.Name == indexDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(indexDesignerInfo.OldName, indexDesignerInfo.Name)
&& this.IsValueEqualsIgnoreCase(indexDesignerInfo.OldType, indexDesignerInfo.Type)
)
{
if (oldIndex != null && this.IsStringEquals(oldIndex.Comment, newIndex.Comment) && SchemaInfoHelper.IsIndexColumnsEquals(oldIndex.Columns, newIndex.Columns))
{
continue;
}
}
scripts.AddRange(this.GetIndexAlterScripts(oldIndex, newIndex));
}
foreach (TableIndex oldIndex in oldIndexes)
{
if (!indexDesignerInfos.Any(item => item.Name == oldIndex.Name))
{
scripts.Add(this.scriptGenerator.DropIndex(oldIndex));
}
}
}
#endregion
#region Foreign Key
if (!schemaDesignerInfo.IgnoreTableForeignKey)
{
List<TableForeignKey> oldForeignKeys = oldSchemaInfo.TableForeignKeys;
IEnumerable<TableForeignKeyDesignerInfo> foreignKeyDesignerInfos = schemaDesignerInfo.TableForeignKeyDesignerInfos;
foreach (TableForeignKeyDesignerInfo foreignKeyDesignerInfo in foreignKeyDesignerInfos)
{
TableForeignKey newForeignKey = schemaInfo.TableForeignKeys.FirstOrDefault(item => item.Name == foreignKeyDesignerInfo.Name);
TableForeignKey oldForeignKey = oldForeignKeys.FirstOrDefault(item => item.Name == foreignKeyDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(foreignKeyDesignerInfo.OldName, foreignKeyDesignerInfo.Name) &&
foreignKeyDesignerInfo.UpdateCascade == oldForeignKey.UpdateCascade &&
foreignKeyDesignerInfo.DeleteCascade == oldForeignKey.DeleteCascade)
{
if (oldForeignKey != null && this.IsStringEquals(oldForeignKey.Comment, newForeignKey.Comment)
&& SchemaInfoHelper.IsForeignKeyColumnsEquals(oldForeignKey.Columns, newForeignKey.Columns))
{
continue;
}
}
scripts.AddRange(this.GetForeignKeyAlterScripts(oldForeignKey, newForeignKey));
}
foreach (TableForeignKey oldForeignKey in oldForeignKeys)
{
if (!foreignKeyDesignerInfos.Any(item => item.Name == oldForeignKey.Name))
{
scripts.Add(this.scriptGenerator.DropForeignKey(oldForeignKey));
}
}
}
#endregion
#region Constraint
if (!schemaDesignerInfo.IgnoreTableConstraint)
{
List<TableConstraint> oldConstraints = oldSchemaInfo.TableConstraints;
IEnumerable<TableConstraintDesignerInfo> constraintDesignerInfos = schemaDesignerInfo.TableConstraintDesignerInfos;
foreach (TableConstraintDesignerInfo constraintDesignerInfo in constraintDesignerInfos)
{
TableConstraint newConstraint = schemaInfo.TableConstraints.FirstOrDefault(item => item.Name == constraintDesignerInfo.Name);
TableConstraint oldConstraint = oldConstraints.FirstOrDefault(item => item.Name == constraintDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(constraintDesignerInfo.OldName, constraintDesignerInfo.Name))
{
if (oldConstraint != null && this.IsStringEquals(oldConstraint.Comment, newConstraint.Comment))
{
continue;
}
}
scripts.AddRange(this.GetConstraintAlterScripts(oldConstraint, newConstraint));
}
foreach (TableConstraint oldConstraint in oldConstraints)
{
if (!constraintDesignerInfos.Any(item => item.Name == oldConstraint.Name))
{
scripts.Add(this.scriptGenerator.DropCheckConstraint(oldConstraint));
}
}
}
#endregion
#endregion
}
scriptsData.Scripts.AddRange(scripts);
return new ContentSaveResult() { IsOK = true, ResultData = scriptsData };
}
catch (Exception ex)
{
return this.GetFaultSaveResult(ExceptionHelper.GetExceptionDetails(ex));
}
}
public override Script DropCheckConstraint(TableConstraint constraint)
{
return(new Script(""));
}
