public static async Task <SyncTable> GetTableAsync(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
string commandColumn = "select * from information_schema.TABLES where table_schema = schema() and table_name = @tableName limit 1;";
var tableNameParser = ParserName.Parse(tableName, "`");
var syncTable = new SyncTable(tableNameParser.Unquoted().ToString());
using (var sqlCommand = new MySqlCommand(commandColumn, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.Unquoted().ToString());
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = await sqlCommand.ExecuteReaderAsync().ConfigureAwait(false))
{
syncTable.Load(reader);
}
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
public static async Task <SyncTable> GetColumnsForTableAsync(SqliteConnection connection, SqliteTransaction transaction, string tableName)
{
var tableNameParser = ParserName.Parse(tableName, "`");
var tableNameString = tableNameParser.Unquoted().ToString();
string commandColumn = $"SELECT * FROM pragma_table_info('{tableName}');";
var syncTable = new SyncTable(tableNameString);
using (var sqlCommand = new SqliteCommand(commandColumn, connection))
{
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = await sqlCommand.ExecuteReaderAsync().ConfigureAwait(false))
{
syncTable.Load(reader);
}
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
internal static SyncTable PrimaryKeysForTable(MySqlConnection connection, MySqlTransaction transaction, string tableName)
{
var commandColumn = @"select * from information_schema.COLUMNS where table_schema = schema() and table_name = @tableName and column_key='PRI'";
var tableNameParser = ParserName.Parse(tableName, "`");
var syncTable = new SyncTable(tableNameParser.Unquoted().ToString());
using (var sqlCommand = new MySqlCommand(commandColumn, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameParser.Unquoted().ToString());
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = sqlCommand.ExecuteReader())
{
syncTable.Load(reader);
}
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
public async Task <IEnumerable <DbRelationDefinition> > GetRelationsAsync(DbConnection connection, DbTransaction transaction)
{
var relations = new List <DbRelationDefinition>();
var commandRelations = @"
SELECT
ke.CONSTRAINT_NAME as ForeignKey,
ke.POSITION_IN_UNIQUE_CONSTRAINT as ForeignKeyOrder,
ke.referenced_table_name as ReferenceTableName,
ke.REFERENCED_COLUMN_NAME as ReferenceColumnName,
ke.table_name TableName,
ke.COLUMN_NAME ColumnName
FROM
information_schema.KEY_COLUMN_USAGE ke
WHERE
ke.referenced_table_name IS NOT NULL
and ke.table_schema = schema()
AND ke.table_name = @tableName
ORDER BY
ke.referenced_table_name;";
var relationsList = new SyncTable(tableName.Unquoted().ToString());
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = commandRelations;
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);
}
using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
relationsList.Load(reader);
}
if (!alreadyOpened)
{
connection.Close();
}
if (relationsList != null && relationsList.Rows.Count > 0)
{
foreach (var fk in relationsList.Rows.GroupBy(row =>
new { Name = (string)row["ForeignKey"], TableName = (string)row["TableName"], ReferenceTableName = (string)row["ReferenceTableName"] }))
{
var relationDefinition = new DbRelationDefinition()
{
ForeignKey = fk.Key.Name,
TableName = fk.Key.TableName,
ReferenceTableName = fk.Key.ReferenceTableName,
};
relationDefinition.Columns.AddRange(fk.Select(dmRow =>
new DbRelationColumnDefinition
{
KeyColumnName = (string)dmRow["ColumnName"],
ReferenceColumnName = (string)dmRow["ReferenceColumnName"],
Order = Convert.ToInt32(dmRow["ForeignKeyOrder"])
}));
relations.Add(relationDefinition);
}
}
return(relations.OrderBy(t => t.ForeignKey).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 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());
}
/// <summary>
/// Get columns for table
/// </summary>
public static SyncTable ColumnsForTable(SqlConnection connection, SqlTransaction transaction, string tableName, string schemaName)
{
var commandColumn = $"Select col.name as name, " +
$"col.column_id, " +
$"typ.name as [type], " +
$"col.max_length, " +
$"col.precision, " +
$"col.scale, " +
$"col.is_nullable, " +
$"col.is_computed, " +
$"col.is_identity, " +
$"ind.is_unique, " +
$"ident_seed(sch.name + '.' + tbl.name) AS seed, " +
$"ident_incr(sch.name + '.' + tbl.name) AS step, " +
$"object_definition(col.default_object_id) AS defaultvalue " +
$" from sys.columns as col " +
$" Inner join sys.tables as tbl on tbl.object_id = col.object_id " +
$" Inner join sys.schemas as sch on tbl.schema_id = sch.schema_id " +
$" Inner Join sys.systypes typ on typ.xusertype = col.system_type_id " +
$" Left outer join sys.indexes ind on ind.object_id = col.object_id and ind.index_id = col.column_id " +
$" Where tbl.name = @tableName and sch.name = @schemaName ";
var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString();
var tableNameString = ParserName.Parse(tableName).ToString();
var schemaNameString = "dbo";
if (!string.IsNullOrEmpty(schemaName))
{
schemaNameString = ParserName.Parse(schemaName).ToString();
schemaNameString = string.IsNullOrWhiteSpace(schemaNameString) ? "dbo" : schemaNameString;
}
var syncTable = new SyncTable(tableNameNormalized);
using (var sqlCommand = new SqlCommand(commandColumn, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameString);
sqlCommand.Parameters.AddWithValue("@schemaName", schemaNameString);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = sqlCommand.ExecuteReader())
syncTable.Load(reader);
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
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());
}