public async Task CreateTableAsync(DbConnection connection, DbTransaction transaction)
{
var changetrackingtable = await SqlChangeTrackingManagementUtils.ChangeTrackingTableAsync(
(SqlConnection)connection, (SqlTransaction)transaction, tableName.ToString(), tableName.SchemaName);
if (changetrackingtable != null && changetrackingtable.Rows != null && changetrackingtable.Rows.Count > 0)
{
return;
}
var commandText = $"ALTER TABLE {tableName.Schema().Quoted().ToString()} ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = OFF);";
using var command = new SqlCommand(commandText, (SqlConnection)connection, (SqlTransaction)transaction);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
public Task <DbCommand> GetExistsColumnCommandAsync(string columnName, DbConnection connection, DbTransaction transaction)
{
var tbl = tableName.ToString();
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = $"select count(*) from information_schema.COLUMNS where table_schema = schema() and table_name = @tableName and column_name = @columnName;";
var parameter = command.CreateParameter();
parameter.ParameterName = "@tableName";
parameter.Value = tbl;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@columnName";
parameter.Value = columnName;
command.Parameters.Add(parameter);
return(Task.FromResult(command));
}
public Task <DbCommand> GetCreateTrackingTableCommandAsync(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
var tbl = trackingName.ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(trackingName);
stringBuilder.AppendLine($"CREATE TABLE {trackingName.Schema().Quoted().ToString()} (");
// Adding the primary key
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
var columnType = this.sqlDbMetadata.GetCompatibleColumnTypeDeclarationString(pkColumn, this.tableDescription.OriginalProvider);
var nullableColumn = pkColumn.AllowDBNull ? "NULL" : "NOT NULL";
stringBuilder.AppendLine($"{quotedColumnName} {columnType} {nullableColumn}, ");
}
// adding the tracking columns
stringBuilder.AppendLine($"[update_scope_id] [uniqueidentifier] NULL, ");
stringBuilder.AppendLine($"[timestamp] [timestamp] NULL, ");
stringBuilder.AppendLine($"[timestamp_bigint] AS (CONVERT([bigint],[timestamp])) PERSISTED, ");
stringBuilder.AppendLine($"[sync_row_is_tombstone] [bit] NOT NULL default(0), ");
stringBuilder.AppendLine($"[last_change_datetime] [datetime] NULL, ");
stringBuilder.AppendLine(");");
// Primary Keys
stringBuilder.Append($"ALTER TABLE {trackingName.Schema().Quoted().ToString()} ADD CONSTRAINT [PK_{trackingName.Schema().Unquoted().Normalized().ToString()}] PRIMARY KEY (");
var primaryKeysColumns = this.tableDescription.GetPrimaryKeysColumns().ToList();
for (int i = 0; i < primaryKeysColumns.Count; i++)
{
var pkColumn = primaryKeysColumns[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < primaryKeysColumns.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.AppendLine(");");
// Index
var indexName = trackingName.Schema().Unquoted().Normalized().ToString();
stringBuilder.AppendLine($"CREATE NONCLUSTERED INDEX [{indexName}_timestamp_index] ON {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.AppendLine($"\t [timestamp_bigint] ASC");
stringBuilder.AppendLine($"\t, [update_scope_id] ASC");
stringBuilder.AppendLine($"\t, [sync_row_is_tombstone] ASC");
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} ASC");
}
stringBuilder.Append(");");
var command = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@tableName",
Value = tbl
};
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter()
{
ParameterName = "@schemaName",
Value = schema
};
command.Parameters.Add(sqlParameter);
return(Task.FromResult((DbCommand)command));
}
private SqlCommand BuildCreateTableCommand(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
var tbl = tableName.ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(tableName);
stringBuilder.AppendLine($"CREATE TABLE {tableName.Schema().Quoted().ToString()} (");
string empty = string.Empty;
stringBuilder.AppendLine();
foreach (var column in this.tableDescription.Columns)
{
var columnName = ParserName.Parse(column).Quoted().ToString();
var columnTypeString = this.sqlDbMetadata.TryGetOwnerDbTypeString(column.OriginalDbType, column.GetDbType(), false, false, column.MaxLength, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var columnPrecisionString = this.sqlDbMetadata.TryGetOwnerDbTypePrecision(column.OriginalDbType, column.GetDbType(), false, false, column.MaxLength, column.Precision, column.Scale, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var columnType = $"{columnTypeString} {columnPrecisionString}";
var identity = string.Empty;
if (column.IsAutoIncrement)
{
var s = column.GetAutoIncrementSeedAndStep();
identity = $"IDENTITY({s.Seed},{s.Step})";
}
var nullString = column.AllowDBNull ? "NULL" : "NOT NULL";
// if we have a computed column, we should allow null
if (column.IsReadOnly)
{
nullString = "NULL";
}
string defaultValue = string.Empty;
if (this.tableDescription.OriginalProvider == SqlSyncProvider.ProviderType)
{
if (!string.IsNullOrEmpty(column.DefaultValue))
{
defaultValue = "DEFAULT " + column.DefaultValue;
}
}
stringBuilder.AppendLine($"\t{empty}{columnName} {columnType} {identity} {nullString} {defaultValue}");
empty = ",";
}
stringBuilder.AppendLine(");");
// Primary Keys
var primaryKeyNameString = tableName.Schema().Unquoted().Normalized().ToString();
stringBuilder.AppendLine($"ALTER TABLE {tableName.Schema().Quoted().ToString()} ADD CONSTRAINT [PK_{primaryKeyNameString}] PRIMARY KEY(");
for (int i = 0; i < this.tableDescription.PrimaryKeys.Count; i++)
{
var pkColumn = this.tableDescription.PrimaryKeys[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKeys.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.AppendLine(");");
// Foreign Keys
foreach (var constraint in this.tableDescription.GetRelations())
{
var tableName = ParserName.Parse(constraint.GetTable()).Quoted().Schema().ToString();
var parentTableName = ParserName.Parse(constraint.GetParentTable()).Quoted().Schema().ToString();
var relationName = NormalizeRelationName(constraint.RelationName);
stringBuilder.Append("ALTER TABLE ");
stringBuilder.Append(tableName);
stringBuilder.AppendLine(" WITH NOCHECK");
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine($"[{relationName}]");
stringBuilder.Append("FOREIGN KEY (");
empty = string.Empty;
foreach (var column in constraint.Keys)
{
var childColumnName = ParserName.Parse(column.ColumnName).Quoted().ToString();
stringBuilder.Append($"{empty} {childColumnName}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(parentTableName).Append(" (");
empty = string.Empty;
foreach (var parentdColumn in constraint.ParentKeys)
{
var parentColumnName = ParserName.Parse(parentdColumn.ColumnName).Quoted().ToString();
stringBuilder.Append($"{empty} {parentColumnName}");
empty = ", ";
}
stringBuilder.Append(" ) ");
}
string createTableCommandString = stringBuilder.ToString();
var command = new SqlCommand(createTableCommandString, (SqlConnection)connection, (SqlTransaction)transaction);
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@tableName",
Value = tbl
};
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter()
{
ParameterName = "@schemaName",
Value = schema
};
command.Parameters.Add(sqlParameter);
return(command);
}
public async Task CreateTableAsync(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
var tbl = trackingName.ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(trackingName);
stringBuilder.AppendLine("IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @tableName AND s.name = @schemaName) ");
stringBuilder.AppendLine("BEGIN");
stringBuilder.AppendLine($"CREATE TABLE {trackingName.Schema().Quoted().ToString()} (");
// Adding the primary key
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
var columnTypeString = this.sqlDbMetadata.TryGetOwnerDbTypeString(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var quotedColumnType = ParserName.Parse(columnTypeString).Quoted().ToString();
var columnPrecisionString = this.sqlDbMetadata.TryGetOwnerDbTypePrecision(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, pkColumn.Precision, pkColumn.Scale, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var columnType = $"{quotedColumnType} {columnPrecisionString}";
var nullableColumn = pkColumn.AllowDBNull ? "NULL" : "NOT NULL";
stringBuilder.AppendLine($"{quotedColumnName} {columnType} {nullableColumn}, ");
}
// adding the tracking columns
stringBuilder.AppendLine($"[update_scope_id] [uniqueidentifier] NULL, ");
stringBuilder.AppendLine($"[timestamp] [timestamp] NULL, ");
stringBuilder.AppendLine($"[timestamp_bigint] AS (CONVERT([bigint],[timestamp])) PERSISTED, ");
stringBuilder.AppendLine($"[sync_row_is_tombstone] [bit] NOT NULL default(0), ");
stringBuilder.AppendLine($"[last_change_datetime] [datetime] NULL, ");
stringBuilder.Append(");");
// Primary Keys
stringBuilder.Append($"ALTER TABLE {trackingName.Schema().Quoted().ToString()} ADD CONSTRAINT [PK_{trackingName.Schema().Unquoted().Normalized().ToString()}] PRIMARY KEY (");
var primaryKeysColumns = this.tableDescription.GetPrimaryKeysColumns().ToList();
for (int i = 0; i < primaryKeysColumns.Count; i++)
{
var pkColumn = primaryKeysColumns[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < primaryKeysColumns.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(");");
// Index
var indexName = trackingName.Schema().Unquoted().Normalized().ToString();
stringBuilder.AppendLine($"CREATE NONCLUSTERED INDEX [{indexName}_timestamp_index] ON {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.AppendLine($"\t [timestamp_bigint] ASC");
stringBuilder.AppendLine($"\t, [update_scope_id] ASC");
stringBuilder.AppendLine($"\t, [sync_row_is_tombstone] ASC");
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} ASC");
}
stringBuilder.Append(");");
stringBuilder.AppendLine("END");
using (var command = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction))
{
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@tableName",
Value = tbl
};
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter()
{
ParameterName = "@schemaName",
Value = schema
};
command.Parameters.Add(sqlParameter);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
