private void AddCreateUniqueConstraintBatch(Index index, QuoteType quoteType)
{
List <ColumnWithSortOrder> columns = new List <ColumnWithSortOrder>();
foreach (var member in index.Members)
{
Column column = index.Parent.Columns.FirstOrDefault(c => c.Name == member.Column);
columns.Add(
ScriptFactory.ColumnWithSortOrder(
ScriptFactory.ColumnReferenceExpression(
ScriptFactory.MultiPartIdentifier(
ScriptFactory.Identifier(member.Column, quoteType)), ColumnType.Regular), SortOrder.Ascending));
}
UniqueConstraintDefinition uniqueConstraint = ScriptFactory.UniqueConstraintDefinition(
ScriptFactory.Identifier(index.Name, quoteType),
false,
index.IsClustered,
null,
null,
null,
columns
);
AddBatch(ScriptFactory.AlterTableAddTableElement(
ScriptFactory.TableDefinition(
null,
null,
new List <ConstraintDefinition> {
uniqueConstraint
},
null),
Generator.GenerateSchemaObjectName(index.Parent, quoteType)));
}
public override void ExplicitVisit(UniqueConstraintDefinition node)
{
if (node.ConstraintIdentifier != null)
{
_buffer.Append("constraint ");
node.ConstraintIdentifier.Accept(this);
}
if (node.IsPrimaryKey)
{
_buffer.Append(" primary key (");
}
else
{
_buffer.Append(" unique (");
}
for (int index = 0; index < node.Columns.Count; ++index)
{
node.Columns[index].Accept(this);
if (index < node.Columns.Count - 1)
{
_buffer.Append(", ");
}
}
_buffer.Append(")");
}
protected override object InternalVisit(UniqueConstraintDefinition node)
{
Action <Table, Column> applier = (table, column) =>
{
var isPK = node.IsPrimaryKey;
/*
* INFO(Richo): We can get the columns from two places: the "column" arg or the node's "Columns" property.
* The node's property takes precedence, but it could come empty if the constraint was specified inline.
* In those cases we rely on the "column" argument.
*/
Column[] columns;
if (node.Columns == null || node.Columns.Count() == 0)
{
columns = new[] { column };
}
else
{
columns = node.Columns
.Select(c => table.GetColumn(Visit <string[]>(c.Column.MultiPartIdentifier).Last()))
.ToArray();
}
var name = node.ConstraintIdentifier?.Value;
if (string.IsNullOrWhiteSpace(name))
{
string id = string.Join("", Guid.NewGuid().ToByteArray().Select(b => b.ToString("X2")));
if (isPK)
{
name = string.Format("PK_{0}_{1}", table.TableName, id);
}
else
{
name = string.Format("UC_{0}_{1}_{2}", table.TableName,
string.Join("_", columns.Select(c => c.ColumnName)), id);
}
}
if (isPK && table.PrimaryKey.Length > 0)
{
var msg = string.Format("Cannot add multiple PRIMARY KEY constraints to table '{0}'", table.TableName);
throw new ArgumentException(msg);
}
Database.AddConstraint(new UniqueConstraint(name, columns, isPK));
};
return(applier);
}
private static bool NamePrimaryKey(CreateTableStatement statement)
{
var columnWithPrimaryKey = statement.Definition.ColumnDefinitions.FirstOrDefault(c => c.Constraints.Any(p => p is UniqueConstraintDefinition));
if (columnWithPrimaryKey == null)
{
return(false);
}
var originalConstraint = columnWithPrimaryKey.Constraints.FirstOrDefault(c => c as UniqueConstraintDefinition != null && ((UniqueConstraintDefinition)c).IsPrimaryKey) as UniqueConstraintDefinition;
if (originalConstraint == null)
{
return(false);
}
var newConstraint = new UniqueConstraintDefinition();
newConstraint.Clustered = originalConstraint.Clustered;
newConstraint.IsPrimaryKey = true;
foreach (var o in originalConstraint.IndexOptions)
{
newConstraint.IndexOptions.Add(o);
}
newConstraint.IndexType = originalConstraint.IndexType;
var referencedColumn = new ColumnWithSortOrder();
referencedColumn.Column = new ColumnReferenceExpression();
referencedColumn.Column.MultiPartIdentifier = new SchemaObjectName();
referencedColumn.Column.MultiPartIdentifier.Identifiers.Add(columnWithPrimaryKey.ColumnIdentifier);
newConstraint.Columns.Add(referencedColumn);
newConstraint.ConstraintIdentifier = new Identifier();
newConstraint.ConstraintIdentifier.Value =
ReplaceTemplateValues(SavedSettings.Get().PrimaryKeyName, statement.SchemaObjectName.BaseIdentifier.Value, referencedColumn.Column.MultiPartIdentifier.Identifiers.Last().Value);
newConstraint.ConstraintIdentifier.QuoteType = QuoteType.SquareBracket;
statement.Definition.TableConstraints.Add(newConstraint);
columnWithPrimaryKey.Constraints.Remove(originalConstraint);
return(true);
}
public override void Visit(UniqueConstraintDefinition node) { this.action(node); }
public override void ExplicitVisit(UniqueConstraintDefinition fragment)
{
_fragments.Add(fragment);
}
private static bool NamePrimaryKey(CreateTableStatement statement)
{
var columnWithPrimaryKey = statement.Definition.ColumnDefinitions.FirstOrDefault( c => c.Constraints.Any(p => p is UniqueConstraintDefinition));
if (columnWithPrimaryKey == null)
{
return false;
}
var originalConstraint = columnWithPrimaryKey.Constraints.FirstOrDefault( c => c as UniqueConstraintDefinition != null && ((UniqueConstraintDefinition) c).IsPrimaryKey) as UniqueConstraintDefinition;
if (originalConstraint == null)
{
return false;
}
var newConstraint = new UniqueConstraintDefinition();
newConstraint.Clustered = originalConstraint.Clustered;
newConstraint.IsPrimaryKey = true;
foreach (var o in originalConstraint.IndexOptions)
newConstraint.IndexOptions.Add(o);
newConstraint.IndexType = originalConstraint.IndexType;
var referencedColumn = new ColumnWithSortOrder();
referencedColumn.Column = new ColumnReferenceExpression();
referencedColumn.Column.MultiPartIdentifier = new SchemaObjectName();
referencedColumn.Column.MultiPartIdentifier.Identifiers.Add(columnWithPrimaryKey.ColumnIdentifier);
newConstraint.Columns.Add(referencedColumn);
newConstraint.ConstraintIdentifier = new Identifier();
newConstraint.ConstraintIdentifier.Value =
ReplaceTemplateValues(SavedSettings.Get().PrimaryKeyName, statement.SchemaObjectName.BaseIdentifier.Value, referencedColumn.Column.MultiPartIdentifier.Identifiers.Last().Value);
newConstraint.ConstraintIdentifier.QuoteType = QuoteType.SquareBracket;
statement.Definition.TableConstraints.Add(newConstraint);
columnWithPrimaryKey.Constraints.Remove(originalConstraint);
return true;
}
public CreateTableStatement Convert(CTable table)
{
var parser = new TSql120Parser(false);
string[] parts = { table.Schema.SchemaName, table.TableName };
var createTable = new CreateTableStatement();
///set schema and table name
createTable.SchemaObjectName = new SchemaObjectName();
createTable.SchemaObjectName.Identifiers.Add(new Identifier {
Value = table.Schema.SchemaName
});
createTable.SchemaObjectName.Identifiers.Add(new Identifier {
Value = $"[{table.TableName}]"
});
//add columns
createTable.Definition = new TableDefinition();
foreach (var col in table.Column)
{
if (col.ColumnType == DbType.Object)
{
continue;
}
var dataType = new SqlDataTypeReference
{
SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(col.ColumnTypeRaw)
};
if (col.ColumnLength > 0)
{
dataType.Parameters.Add(new IntegerLiteral {
Value = col.ColumnLength.ToString()
});
}
var column = new ColumnDefinition
{
ColumnIdentifier = new Identifier {
Value = col.ColumnName.WrapReservedAndSnakeCase(DataStoreTypes.SqlServer, table.ConvertToSnakeCase)
},
DataType = dataType
};
if (!string.IsNullOrEmpty(col.DefaultValue))
{
IList <ParseError> errors;
//var defaultValueText = "CONVERT([char](32), REPLACE(CONVERT([char](36),NEWID()),'-',''))";
var scriptDefault = parser.ParseExpression(new StringReader(col.DefaultValue), out errors);
column.DefaultConstraint = new DefaultConstraintDefinition {
Expression = scriptDefault
};
}
if (col.IsIdentity)
{
column.IdentityOptions = new IdentityOptions
{
IdentitySeed = new IntegerLiteral {
Value = col.ColumnType == System.Data.DbType.Byte ? "1" : "1000"
},
IdentityIncrement = new IntegerLiteral {
Value = "1"
}
}
}
;
column.Constraints.Add(new NullableConstraintDefinition {
Nullable = col.IsNullable
});
if (col.IsUnique)
{
column.Constraints.Add(new UniqueConstraintDefinition());
}
if (col.IsIndexed)
{
column.Index = new IndexDefinition
{
Name = new Identifier {
Value = $"IX_{col.ColumnName}"
},
IndexType = new IndexType
{
IndexTypeKind = IndexTypeKind.NonClustered
}
}
}
;
createTable.Definition.ColumnDefinitions.Add(column);
}
//add PK's
var pks = table.GetPrimaryKeyColumns();
if (pks.Count > 0)
{
var primaryKeyConstraint = new UniqueConstraintDefinition {
IsPrimaryKey = true
};
primaryKeyConstraint.Clustered = true; // todo: use metadata
foreach (var pk in table.GetPrimaryKeyColumns())
{
var columnIdentifier = new MultiPartIdentifier();
columnIdentifier.Identifiers.Add(new Identifier {
Value = pk.ColumnName
});
var columnRefExpression = new ColumnReferenceExpression
{
MultiPartIdentifier = columnIdentifier
};
var columnWithSortOrder = new ColumnWithSortOrder {
Column = columnRefExpression
};
primaryKeyConstraint.Columns.Add(columnWithSortOrder);
}
var pkConstraintName = $"PK_{table.Schema.SchemaName}_{table.TableName}_{pks.First().ColumnName}";
primaryKeyConstraint.ConstraintIdentifier = new Identifier {
Value = pkConstraintName
};
createTable.Definition.TableConstraints.Add(primaryKeyConstraint);
}
//add foreign keys
foreach (var col in table.Column)
{
if (col.ForeignKeyColumn == null || col.ForeignKeyColumn.Count == 0)
{
continue;
}
foreach (var fk in col.ForeignKeyColumn)
{
var fkConstraintName = $"FK_{table.TableName}_{fk.Table.TableName}";
var foreignKeyConstraint = new ForeignKeyConstraintDefinition();
foreignKeyConstraint.ConstraintIdentifier = new Identifier {
Value = fkConstraintName
};
foreignKeyConstraint.Columns.Add(new Identifier {
Value = col.ColumnName
});
foreignKeyConstraint.ReferenceTableName = new SchemaObjectName();
foreignKeyConstraint.ReferenceTableName.Identifiers.Add(new Identifier
{
Value = fk.Table.Schema.SchemaName
});
foreignKeyConstraint.ReferenceTableName.Identifiers.Add(new Identifier
{
Value = fk.Table.TableName
});
foreignKeyConstraint.ReferencedTableColumns.Add(new Identifier {
Value = fk.ColumnName
});
createTable.Definition.TableConstraints.Add(foreignKeyConstraint);
}
}
return(createTable);
}
}
}
public void ProcessCreateTable(CreateTableStatement TblStmt)
{
bool isTemp = TblStmt.SchemaObjectName.BaseIdentifier.Value.StartsWith("#") ||
TblStmt.SchemaObjectName.BaseIdentifier.Value.StartsWith("@");
if (TblStmt.SchemaObjectName.SchemaIdentifier == null &&
!isTemp
)
{
_smells.SendFeedBack(27, TblStmt);
}
{
foreach (ColumnDefinition colDef in TblStmt.Definition.ColumnDefinitions)
{
_smells.ProcessTsqlFragment(colDef);
}
}
if (isTemp)
{
foreach (ConstraintDefinition constDef in TblStmt.Definition.TableConstraints)
{
if (constDef.ConstraintIdentifier != null)
{
}
switch (FragmentTypeParser.GetFragmentType(constDef))
{
case "UniqueConstraintDefinition":
UniqueConstraintDefinition unqConst = (UniqueConstraintDefinition)constDef;
if (unqConst.IsPrimaryKey)
{
_smells.SendFeedBack(38, constDef);
}
break;
}
}
foreach (ColumnDefinition colDef in TblStmt.Definition.ColumnDefinitions)
{
if (colDef.DefaultConstraint != null && colDef.DefaultConstraint.ConstraintIdentifier != null)
{
_smells.SendFeedBack(39, colDef);
}
foreach (ConstraintDefinition constDef in colDef.Constraints)
{
if (constDef.ConstraintIdentifier != null)
{
}
switch (FragmentTypeParser.GetFragmentType(constDef))
{
case "CheckConstraintDefinition":
CheckConstraintDefinition chkConst = (CheckConstraintDefinition)constDef;
if (chkConst.ConstraintIdentifier != null)
{
_smells.SendFeedBack(40, chkConst);;
}
break;
}
}
}
}
}
