public MySqlBuilderTrackingTable(DmTable tableDescription, DbConnection connection, DbTransaction transaction = null)
{
this.connection = connection as MySqlConnection;
this.transaction = transaction as MySqlTransaction;
this.tableDescription = tableDescription;
(this.tableName, this.trackingName) = MySqlBuilder.GetParsers(this.tableDescription);
this.mySqlDbMetadata = new MySqlDbMetadata();
}
public MySqlBuilderTable(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup)
{
this.tableDescription = tableDescription;
this.setup = setup;
this.tableName = tableName;
this.trackingName = trackingName;
this.mySqlDbMetadata = new MySqlDbMetadata();
}
public MySqlBuilderProcedure(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup)
{
this.tableDescription = tableDescription;
this.setup = setup;
this.tableName = tableName;
this.trackingName = trackingName;
this.mySqlObjectNames = new MySqlObjectNames(this.tableDescription, tableName, trackingName, this.setup);
this.mySqlDbMetadata = new MySqlDbMetadata();
}
public MySqlBuilderTrackingTable(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup, DbConnection connection, DbTransaction transaction = null)
{
this.connection = connection as MySqlConnection;
this.transaction = transaction as MySqlTransaction;
this.tableDescription = tableDescription;
this.tableName = tableName;
this.trackingName = trackingName;
this.mySqlDbMetadata = new MySqlDbMetadata();
}
public MySqlBuilderProcedure(SyncTable tableDescription, DbConnection connection, DbTransaction transaction = null)
{
this.connection = connection as MySqlConnection;
this.transaction = transaction as MySqlTransaction;
this.tableDescription = tableDescription;
(this.tableName, this.trackingName) = MyTableSqlBuilder.GetParsers(tableDescription);
this.mySqlObjectNames = new MySqlObjectNames(this.tableDescription);
this.mySqlDbMetadata = new MySqlDbMetadata();
}
public MySqlSyncAdapter(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup, DbConnection connection, DbTransaction transaction) : base(tableDescription, setup)
{
var sqlc = connection as MySqlConnection;
this.connection = sqlc ?? throw new InvalidCastException("Connection should be a MySqlConnection");
this.transaction = transaction as MySqlTransaction;
this.mySqlDbMetadata = new MySqlDbMetadata();
this.mySqlObjectNames = new MySqlObjectNames(TableDescription, tableName, trackingName, Setup);
}
public MySqlSyncAdapter(DmTable tableDescription, DbConnection connection, DbTransaction transaction) : base(tableDescription)
{
var sqlc = connection as MySqlConnection;
this.connection = sqlc ?? throw new InvalidCastException("Connection should be a MySqlConnection");
this.transaction = transaction as MySqlTransaction;
this.mySqlDbMetadata = new MySqlDbMetadata();
this.mySqlObjectNames = new MySqlObjectNames(TableDescription);
}
public MySqlBuilderProcedure(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup, DbConnection connection, DbTransaction transaction = null)
{
this.connection = connection as MySqlConnection;
this.transaction = transaction as MySqlTransaction;
this.tableDescription = tableDescription;
this.setup = setup;
this.tableName = tableName;
this.trackingName = trackingName;
this.mySqlObjectNames = new MySqlObjectNames(this.tableDescription, tableName, trackingName, this.setup);
this.mySqlDbMetadata = new MySqlDbMetadata();
}
internal MySqlParameter GetMySqlParameter(SyncColumn column)
{
var mySqlDbMetadata = new MySqlDbMetadata();
var parameterName = ParserName.Parse(column, "`").Unquoted().Normalized().ToString();
var sqlParameter = new MySqlParameter
{
ParameterName = $"{MySqlBuilderProcedure.MYSQL_PREFIX_PARAMETER}{parameterName}",
DbType = column.GetDbType(),
IsNullable = column.AllowDBNull
};
#if MARIADB
(byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.GetDbType(), false, false, column.MaxLength, column.Precision, column.Scale, this.tableDescription.OriginalProvider, MariaDB.MariaDBSyncProvider.ProviderType);
#elif MYSQL
(byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.GetDbType(), false, false, column.MaxLength, column.Precision, column.Scale, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
#endif
if ((sqlParameter.DbType == DbType.Decimal || sqlParameter.DbType == DbType.Double ||
sqlParameter.DbType == DbType.Single || sqlParameter.DbType == DbType.VarNumeric) && precision > 0)
{
sqlParameter.Precision = precision;
if (scale > 0)
{
sqlParameter.Scale = scale;
}
}
else if (column.MaxLength > 0)
{
sqlParameter.Size = (int)column.MaxLength;
}
else if (sqlParameter.DbType == DbType.Guid)
{
sqlParameter.Size = 36;
}
else
{
sqlParameter.Size = -1;
}
return(sqlParameter);
}
private static void ParseDataTypeSize(DmRow row, string size)
{
MySqlDbMetadata metadata = new MySqlDbMetadata();
size = size.Trim('(', ')');
string[] parts = size.Split(',');
if (!metadata.IsNumericType(row["DATA_TYPE"].ToString()))
{
row["CHARACTER_MAXIMUM_LENGTH"] = Int32.Parse(parts[0]);
// will set octet length in a minute
}
else
{
row["NUMERIC_PRECISION"] = Int32.Parse(parts[0]);
if (parts.Length == 2)
{
row["NUMERIC_SCALE"] = Int32.Parse(parts[1]);
}
}
}
private static string GetDataTypeDefaults(string type, DmRow row)
{
MySqlDbMetadata metadata = new MySqlDbMetadata();
string format = "({0},{1})";
object precision = row["NUMERIC_PRECISION"];
if (metadata.IsNumericType(type) && string.IsNullOrEmpty((string)row["NUMERIC_PRECISION"]))
{
row["NUMERIC_PRECISION"] = 10;
row["NUMERIC_SCALE"] = 0;
if (!metadata.SupportScale(type))
{
format = "({0})";
}
return(String.Format(format, row["NUMERIC_PRECISION"],
row["NUMERIC_SCALE"]));
}
return(String.Empty);
}
internal static MySqlParameter GetMySqlParameter(this DmColumn column)
{
MySqlDbMetadata mySqlDbMetadata = new MySqlDbMetadata();
var parameterName = ParserName.Parse(column).Unquoted().Normalized().ToString();
MySqlParameter sqlParameter = new MySqlParameter
{
ParameterName = $"{MySqlBuilderProcedure.MYSQL_PREFIX_PARAMETER}{parameterName}",
DbType = column.DbType,
IsNullable = column.AllowDBNull
};
(byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.DbType, false, false, column.MaxLength, column.Precision, column.Scale, column.Table.OriginalProvider, MySqlSyncProvider.ProviderType);
if ((sqlParameter.DbType == DbType.Decimal || sqlParameter.DbType == DbType.Double ||
sqlParameter.DbType == DbType.Single || sqlParameter.DbType == DbType.VarNumeric) && precision > 0)
{
sqlParameter.Precision = precision;
if (scale > 0)
{
sqlParameter.Scale = scale;
}
}
else if (column.MaxLength > 0)
{
sqlParameter.Size = (int)column.MaxLength;
}
else if (sqlParameter.DbType == DbType.Guid)
{
sqlParameter.Size = 36;
}
else
{
sqlParameter.Size = -1;
}
return(sqlParameter);
}
internal static MySqlParameter GetMySqlParameter(this DmColumn column)
{
MySqlDbMetadata mySqlDbMetadata = new MySqlDbMetadata();
MySqlParameter sqlParameter = new MySqlParameter
{
ParameterName = $"in{column.ColumnName}",
DbType = column.DbType,
IsNullable = column.AllowDBNull
};
(byte precision, byte scale) = mySqlDbMetadata.TryGetOwnerPrecisionAndScale(column.OriginalDbType, column.DbType, false, false, column.Precision, column.Scale, column.Table.OriginalProvider, MySqlSyncProvider.ProviderType);
if ((sqlParameter.DbType == DbType.Decimal || sqlParameter.DbType == DbType.Double ||
sqlParameter.DbType == DbType.Single || sqlParameter.DbType == DbType.VarNumeric) && precision > 0)
{
sqlParameter.Precision = precision;
if (scale > 0)
{
sqlParameter.Scale = scale;
}
}
else if (column.MaxLength > 0)
{
sqlParameter.Size = (int)column.MaxLength;
}
else if (sqlParameter.DbType == DbType.Guid)
{
sqlParameter.Size = 36;
}
else
{
sqlParameter.Size = -1;
}
return(sqlParameter);
}
public MySqlSyncAdapter(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup) : base(tableDescription, setup)
{
this.mySqlDbMetadata = new MySqlDbMetadata();
this.mySqlObjectNames = new MySqlObjectNames(TableDescription, tableName, trackingName, Setup);
}
public async Task <IEnumerable <SyncColumn> > GetColumnsAsync(DbConnection connection, DbTransaction transaction)
{
string commandColumn = "select * from information_schema.COLUMNS where table_schema = schema() and table_name = @tableName";
var columns = new List <SyncColumn>();
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = commandColumn;
var parameter = command.CreateParameter();
parameter.ParameterName = "@tableName";
parameter.Value = tableName.Unquoted().ToString();
command.Parameters.Add(parameter);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
var syncTable = new SyncTable(this.tableName.Unquoted().ToString());
using var reader = await command.ExecuteReaderAsync().ConfigureAwait(false);
syncTable.Load(reader);
reader.Close();
var mySqlDbMetadata = new MySqlDbMetadata();
foreach (var c in syncTable.Rows.OrderBy(r => Convert.ToUInt64(r["ordinal_position"])))
{
var typeName = c["data_type"].ToString();
var name = c["column_name"].ToString();
var isUnsigned = c["column_type"] != DBNull.Value && ((string)c["column_type"]).Contains("unsigned");
var maxLengthLong = c["character_maximum_length"] != DBNull.Value ? Convert.ToInt64(c["character_maximum_length"]) : 0;
//// Gets the datastore owner dbType
//var datastoreDbType = (MySqlDbType)mySqlDbMetadata.ValidateOwnerDbType(typeName, isUnsigned, false, maxLengthLong);
//// once we have the datastore type, we can have the managed type
//var columnType = mySqlDbMetadata.ValidateType(datastoreDbType);
var sColumn = new SyncColumn(name)
{
OriginalTypeName = typeName,
Ordinal = Convert.ToInt32(c["ordinal_position"]),
MaxLength = maxLengthLong > int.MaxValue ? int.MaxValue : (int)maxLengthLong,
Precision = c["numeric_precision"] != DBNull.Value ? Convert.ToByte(c["numeric_precision"]) : (byte)0,
Scale = c["numeric_scale"] != DBNull.Value ? Convert.ToByte(c["numeric_scale"]) : (byte)0,
AllowDBNull = (string)c["is_nullable"] != "NO",
DefaultValue = c["COLUMN_DEFAULT"].ToString()
};
var extra = c["extra"] != DBNull.Value ? ((string)c["extra"]).ToLowerInvariant() : null;
if (!string.IsNullOrEmpty(extra) && (extra.Contains("auto increment") || extra.Contains("auto_increment")))
{
sColumn.IsAutoIncrement = true;
sColumn.AutoIncrementSeed = 1;
sColumn.AutoIncrementStep = 1;
}
sColumn.IsUnsigned = isUnsigned;
sColumn.IsUnique = c["column_key"] != DBNull.Value && ((string)c["column_key"]).ToLowerInvariant().Contains("uni");
columns.Add(sColumn);
}
if (!alreadyOpened)
{
connection.Close();
}
return(columns.ToArray());
}
public async Task <IEnumerable <SyncColumn> > GetColumnsAsync(DbConnection connection, DbTransaction transaction)
{
string commandColumn = "select * from information_schema.COLUMNS where table_schema = schema() and table_name = @tableName";
var columns = new List <SyncColumn>();
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = commandColumn;
var parameter = command.CreateParameter();
parameter.ParameterName = "@tableName";
parameter.Value = tableName.Unquoted().ToString();
command.Parameters.Add(parameter);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
var syncTable = new SyncTable(this.tableName.Unquoted().ToString());
using var reader = await command.ExecuteReaderAsync().ConfigureAwait(false);
syncTable.Load(reader);
reader.Close();
var mySqlDbMetadata = new MySqlDbMetadata();
foreach (var c in syncTable.Rows.OrderBy(r => Convert.ToUInt64(r["ordinal_position"])))
{
var maxLengthLong = c["character_maximum_length"] != DBNull.Value ? Convert.ToInt64(c["character_maximum_length"]) : 0;
var sColumn = new SyncColumn(c["column_name"].ToString())
{
OriginalTypeName = c["data_type"].ToString(),
Ordinal = Convert.ToInt32(c["ordinal_position"]),
MaxLength = maxLengthLong > int.MaxValue ? int.MaxValue : (int)maxLengthLong,
Precision = c["numeric_precision"] != DBNull.Value ? Convert.ToByte(c["numeric_precision"]) : (byte)0,
Scale = c["numeric_scale"] != DBNull.Value ? Convert.ToByte(c["numeric_scale"]) : (byte)0,
AllowDBNull = (string)c["is_nullable"] != "NO",
DefaultValue = c["COLUMN_DEFAULT"].ToString(),
ExtraProperty1 = c["column_type"] != DBNull.Value ? c["column_type"].ToString() : null,
IsUnsigned = c["column_type"] != DBNull.Value && ((string)c["column_type"]).Contains("unsigned"),
IsUnique = c["column_key"] != DBNull.Value && ((string)c["column_key"]).ToLowerInvariant().Contains("uni")
};
var extra = c["extra"] != DBNull.Value ? ((string)c["extra"]).ToLowerInvariant() : null;
if (!string.IsNullOrEmpty(extra) && (extra.Contains("auto increment") || extra.Contains("auto_increment")))
{
sColumn.IsAutoIncrement = true;
sColumn.AutoIncrementSeed = 1;
sColumn.AutoIncrementStep = 1;
}
if (!string.IsNullOrEmpty(extra) && extra.Contains("generated"))
{
var generationExpression = c["generation_expression"] != DBNull.Value ? ((string)c["generation_expression"]) : null;
if (!string.IsNullOrEmpty(generationExpression) && !string.IsNullOrEmpty(extra) && extra.Contains("generated"))
{
var virtualOrStored = extra.Contains("virtual") ? "VIRTUAL" : "STORED";
var exp = $"GENERATED ALWAYS AS ({generationExpression}) {virtualOrStored}";
sColumn.DefaultValue = exp;
sColumn.IsCompute = true;
sColumn.AllowDBNull = false;
}
}
columns.Add(sColumn);
}
if (!alreadyOpened)
{
connection.Close();
}
return(columns.ToArray());
}
