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()); }