private NpgsqlCommand BuildTableCommand()
{
var stringBuilder = new StringBuilder($"CREATE TABLE IF NOT EXISTS {_tableName.QuotedString} (");
var empty = string.Empty;
stringBuilder.AppendLine();
foreach (var column in _tableDescription.Columns)
{
var columnName = new ObjectNameParser(column.ColumnName.ToLowerInvariant(), "\"", "\"");
var stringType = _mySqlDbMetadata.TryGetOwnerDbTypeString(column.OriginalDbType, column.DbType, false,
false, _tableDescription.OriginalProvider, PostgreSqlSyncProvider.ProviderType);
var stringPrecision = _mySqlDbMetadata.TryGetOwnerDbTypePrecision(column.OriginalDbType, column.DbType,
false, false, column.MaxLength, column.Precision, column.Scale, _tableDescription.OriginalProvider,
PostgreSqlSyncProvider.ProviderType);
var columnType = $"{stringType} {stringPrecision}";
var identity = string.Empty;
if (column.AutoIncrement)
{
var s = column.GetAutoIncrementSeedAndStep();
if (s.Seed > 1 || s.Step > 1)
{
throw new NotSupportedException("can't establish a seed / step in MySql autoinc value");
}
identity = $"AUTO_INCREMENT";
}
var nullString = column.AllowDBNull ? "NULL" : "NOT NULL";
// if we have a readonly column, we may have a computed one, so we need to allow null
if (column.ReadOnly)
{
nullString = "NULL";
}
stringBuilder.AppendLine($"\t{empty}{columnName.QuotedString} {columnType} {identity} {nullString}");
empty = ",";
}
stringBuilder.Append("\t,PRIMARY KEY (");
var i = 0;
// It seems we need to specify the increment column in first place
foreach (var pkColumn in _tableDescription.PrimaryKey.Columns.OrderByDescending(pk => pk.AutoIncrement))
{
var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName.ToLowerInvariant(), "\"", "\"")
.QuotedObjectName;
stringBuilder.Append(quotedColumnName);
if (i < _tableDescription.PrimaryKey.Columns.Length - 1)
{
stringBuilder.Append(", ");
}
i++;
}
//for (int i = 0; i < this.tableDescription.PrimaryKey.Columns.Length; i++)
//{
// DmColumn pkColumn = this.tableDescription.PrimaryKey.Columns[i];
// var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName.ToLowerInvariant(), "\"", "\"").QuotedObjectName;
// stringBuilder.Append(quotedColumnName);
// if (i < this.tableDescription.PrimaryKey.Columns.Length - 1)
// stringBuilder.Append(", ");
//}
stringBuilder.Append(")");
stringBuilder.Append(")");
return(new NpgsqlCommand(stringBuilder.ToString()));
}
public string CreateTableCommandText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE TABLE {_trackingName.QuotedString} (");
// Adding the primary key
foreach (var pkColumn in _tableDescription.PrimaryKey.Columns)
{
var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "\"", "\"").QuotedString;
var columnTypeString = _mySqlDbMetadata.TryGetOwnerDbTypeString(pkColumn.OriginalDbType,
pkColumn.DbType, false, false, _tableDescription.OriginalProvider,
PostgreSqlSyncProvider.ProviderType);
var unQuotedColumnType = new ObjectNameParser(columnTypeString, "\"", "\"").UnquotedString;
var columnPrecisionString = _mySqlDbMetadata.TryGetOwnerDbTypePrecision(pkColumn.OriginalDbType,
pkColumn.DbType, false, false, pkColumn.MaxLength, pkColumn.Precision, pkColumn.Scale,
_tableDescription.OriginalProvider, PostgreSqlSyncProvider.ProviderType);
var columnType = $"{unQuotedColumnType} {columnPrecisionString}";
stringBuilder.AppendLine($"{quotedColumnName} {columnType} NOT NULL, ");
}
// adding the tracking columns
stringBuilder.AppendLine($"\"create_scope_id\" UUID NULL, ");
stringBuilder.AppendLine($"\"update_scope_id\" UUID NULL, ");
stringBuilder.AppendLine($"\"create_timestamp\" INT8 NULL, ");
stringBuilder.AppendLine($"\"update_timestamp\" INT8 NULL, ");
stringBuilder.AppendLine($"\"timestamp\" INT8 NULL, ");
stringBuilder.AppendLine($"\"sync_row_is_tombstone\" INT2 NOT NULL default 0, ");
stringBuilder.AppendLine($"\"last_change_datetime\" TIMESTAMP NULL, ");
if (Filters != null && Filters.Count > 0)
{
foreach (var filter in Filters)
{
var columnFilter = _tableDescription.Columns[filter.ColumnName];
if (columnFilter == null)
{
throw new InvalidExpressionException(
$"Column {filter.ColumnName} does not exist in Table {_tableDescription.TableName.ToLowerInvariant()}");
}
var isPk = _tableDescription.PrimaryKey.Columns.Any(dm =>
_tableDescription.IsEqual(dm.ColumnName.ToLowerInvariant(),
filter.ColumnName.ToLowerInvariant()));
if (isPk)
{
continue;
}
var quotedColumnName = new ObjectNameParser(columnFilter.ColumnName, "\"", "\"").QuotedString;
var columnTypeString = _mySqlDbMetadata.TryGetOwnerDbTypeString(columnFilter.OriginalDbType,
columnFilter.DbType, false, false, _tableDescription.OriginalProvider,
PostgreSqlSyncProvider.ProviderType);
var unQuotedColumnType = new ObjectNameParser(columnTypeString, "\"", "\"").UnquotedString;
var columnPrecisionString = _mySqlDbMetadata.TryGetOwnerDbTypePrecision(columnFilter.OriginalDbType,
columnFilter.DbType, false, false, columnFilter.MaxLength, columnFilter.Precision,
columnFilter.Scale, _tableDescription.OriginalProvider, PostgreSqlSyncProvider.ProviderType);
var columnType = $"{unQuotedColumnType} {columnPrecisionString}";
var nullableColumn = columnFilter.AllowDBNull ? "NULL" : "NOT NULL";
stringBuilder.AppendLine($"{quotedColumnName} {columnType} {nullableColumn}, ");
}
}
stringBuilder.Append(" PRIMARY KEY (");
for (var i = 0; i < _tableDescription.PrimaryKey.Columns.Length; i++)
{
var pkColumn = _tableDescription.PrimaryKey.Columns[i];
var quotedColumnName = new ObjectNameParser(pkColumn.ColumnName, "\"", "\"").QuotedObjectName;
stringBuilder.Append(quotedColumnName);
if (i < _tableDescription.PrimaryKey.Columns.Length - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append("))");
return(stringBuilder.ToString());
}
