private static string GetTableNameFromReader(SqlReader reader)
{
var tableName = reader["TABLE_NAME"].ToString();
var schemaName = reader["TABLE_SCHEMA"].ToString();
return($"{schemaName}.{tableName}");
}
public SqlMigrationDocument FromReader(SqlReader reader, SqlTable table, out Dictionary <string, byte[]> attachments, SqlDatabase.Validator validator = null)
{
_currentAttachments?.Clear();
var document = FromReaderInternal(reader, table, validator);
attachments = _currentAttachments.ToDictionary(entry => entry.Key, entry => entry.Value);
return(document);
}
private void SetForeignKeys()
{
var referentialConstraints = new Dictionary <string, string>();
using (var reader = new SqlReader(Connection, SqlQueries.SelectReferantialConstraints))
{
while (reader.Read())
{
referentialConstraints.Add(reader["CONSTRAINT_NAME"].ToString(), reader["UNIQUE_CONSTRAINT_NAME"].ToString());
}
}
foreach (var kvp in referentialConstraints)
{
string parentTableName;
var parentColumnName = new List <string>();
var childTableName = new List <string>();
using (var reader = new SqlReader(Connection, SqlQueries.SelectKeyColumnUsageWhereConstraintName))
{
reader.AddParameter("constraintName", kvp.Key);
if (reader.Read() == false)
{
continue;
}
do
{
parentTableName = GetTableNameFromReader(reader);
parentColumnName.Add(reader["COLUMN_NAME"].ToString());
}while (reader.Read());
}
using (var reader = new SqlReader(Connection, SqlQueries.SelectKeyColumnUsageWhereConstraintName))
{
reader.AddParameter("constraintName", kvp.Value);
if (reader.Read() == false)
{
continue;
}
do
{
childTableName.Add(GetTableNameFromReader(reader));
}while (reader.Read());
}
var temp = GetAllTablesByName(parentTableName);
if (temp.Any(table => parentColumnName.Where((t, i) => table.ForeignKeys.TryAdd(t, childTableName[i]) == false).Any()))
{
throw new InvalidOperationException($"Column '{parentColumnName}' cannot reference multiple tables.");
}
}
}
private static List <string> GetValuesFromColumns(SqlReader reader, List <string> childColumnName)
{
var lst = new List <string>();
foreach (var columnName in childColumnName)
{
lst.Add(reader[columnName].ToString());
}
return(lst);
}
public override SqlReader GetReader()
{
if (Reader != null)
{
return(Reader);
}
var query = InitialQuery + SqlQueries.OrderByColumns(GetColumnsReferencingParentTable());
Reader = new SqlReader(Database.ConnectionString, query);
Reader.ExecuteReader();
return(Reader);
}
public SqlReader GetReaderWhere(List <string> values)
{
var query = SqlQueries.SelectFromQueryWhere(InitialQuery, GetColumnsReferencingParentTable(), values) + SqlQueries.OrderByColumns(GetColumnsReferencingParentTable());
if (Reader != null)
{
Reader.SetCommand(query);
return(Reader);
}
Reader = new SqlReader(Database.ConnectionString, query);
Reader.ExecuteReader();
return(Reader);
}
private void SetPrimaryKeys()
{
using (var reader = new SqlReader(Connection, SqlQueries.SelectPrimaryKeys))
{
while (reader.Read())
{
var lst = GetAllTablesByName(GetTableNameFromReader(reader));
foreach (var table in lst)
{
table.PrimaryKeys.Add(reader["COLUMN_NAME"].ToString());
}
}
}
}
public static List <string> GetAllTablesNamesFromDatabase(IDbConnection connection)
{
var lst = new List <string>();
using (var reader = new SqlReader(connection, SqlQueries.SelectAllTables))
{
reader.AddParameter("tableType", "BASE TABLE");
while (reader.Read())
{
lst.Add(GetTableNameFromReader(reader));
}
}
return(lst);
}
private void SetEmbeddedDocuments(SqlReader reader, SqlMigrationDocument document, SqlTable parentTable)
{
foreach (var childTable in parentTable.EmbeddedTables)
{
var parentValues = GetValuesFromColumns(reader, parentTable.PrimaryKeys); // values of referenced columns
var childColumns = childTable.GetColumnsReferencingParentTable(); // values of referencing columns
using (var childReader = GetChildReader(parentTable, childColumns, childTable, parentValues))
{
if (childReader.HasValue() == false && childReader.Read() == false)
{
continue;
}
var continueLoop = false;
while (CompareValues(parentValues, GetValuesFromColumns(childReader, childColumns), out var isBigger) == false)
{
if (isBigger == false && childReader.Read())
{
continue;
}
continueLoop = true; // If parent value is greater than child value => childReader move to next. Otherwise => parentReader move to next
break;
}
if (continueLoop)
{
continue;
}
do
{
var innerDocument = FromReaderInternal(childReader, childTable);
document.Append(childTable.NewName, innerDocument);
if (childReader.Read() == false)
{
break;
}
} while (CompareValues(parentValues, GetValuesFromColumns(childReader, childColumns), out _));
}
}
}
private SqlMigrationDocument ValidateCanCreateDocument(SqlTable table, SqlReader reader)
{
using (reader)
{
while (reader.Read())
{
try
{
return(_database.Factory.FromReader(reader, table, out _, this));
}
catch (Exception e)
{
AddError(SqlMigrationImportResult.Error.ErrorType.UnsupportedType, e.Message, table.Name);
}
}
}
return(null);
}
public static Dictionary <string, List <string> > GetSchemaResultTablesColumns(IDbConnection connection)
{
using (var reader = new SqlReader(connection, SqlQueries.SelectColumns))
{
var temp = new Dictionary <string, List <string> >();
while (reader.Read())
{
var name = GetTableNameFromReader(reader);
if (temp.ContainsKey(name) == false)
{
temp[name] = new List <string>();
}
temp[name].Add(reader["COLUMN_NAME"].ToString());
}
return(temp);
}
}
private SqlReader ValidateQuery(SqlTable table)
{
if (table.InitialQuery.IndexOf("order by", StringComparison.OrdinalIgnoreCase) != -1)
{
AddError(SqlMigrationImportResult.Error.ErrorType.InvalidOrderBy, $"Query cannot contain an 'ORDER BY' clause ({table.Name})", table.Name);
return(null);
}
var reader = new SqlReader(_database.Connection, SqlQueries.SelectSingleRowFromQuery(table.InitialQuery));
try
{
reader.ExecuteReader();
}
catch (Exception e)
{
AddError(SqlMigrationImportResult.Error.ErrorType.InvalidQuery, $"Failed to read table '{table.Name}' using the given query. Error: {e}");
return(null);
}
return(reader);
}
private SqlMigrationDocument FromReaderInternal(SqlReader reader, SqlTable table, SqlDatabase.Validator validator = null)
{
var document = new SqlMigrationDocument(table.Name);
var id = table.NewName;
if (table.IsEmbedded == false)
{
document.SetCollection(id);
}
for (var i = 0; i < reader.FieldCount; i++)
{
var columnName = reader.GetName(i);
var isPrimaryKey = table.PrimaryKeys.Contains(columnName);
var isForeignKey = table.ForeignKeys.TryGetValue(columnName, out var foreignKeyTableName);
object value;
try
{
value = reader[i];
}
catch (Exception e)
{
if (!(e is PlatformNotSupportedException))
{
throw;
}
var isKey = isPrimaryKey || isForeignKey;
if (Options.SkipUnsupportedTypes == false)
{
var message = $"Cannot read column '{columnName}' in table '{table.Name}'. (Unsupported type: {reader.GetDataTypeName(i)}) Error: {e}";
if (validator != null)
{
validator.AddError(SqlMigrationImportResult.Error.ErrorType.UnsupportedType, message, table.Name, columnName);
}
else
{
throw new InvalidOperationException(message, e);
}
}
else if (isKey)
{
var message = $"Cannot skip unsupported KEY column '{columnName}' in table '{table.Name}'. (Unsupported type: {reader.GetDataTypeName(i)})";
if (validator != null)
{
validator.AddError(SqlMigrationImportResult.Error.ErrorType.UnsupportedType, message, table.Name, columnName);
}
else
{
throw new InvalidOperationException(message, e);
}
}
continue;
}
if (isPrimaryKey)
{
id += $"/{value}";
if (isForeignKey == false && table.IsEmbedded == false)
{
continue;
}
}
var isNullOrEmpty = value is DBNull || string.IsNullOrWhiteSpace(value.ToString());
if (Options.BinaryToAttachment && reader.GetFieldType(i) == typeof(byte[]))
{
if (isNullOrEmpty == false)
{
_currentAttachments.Add($"{columnName}_{_currentAttachments.Count}", (byte[])value);
}
}
else
{
if (isForeignKey && isNullOrEmpty == false && table.Database.TryGetNewName(foreignKeyTableName, out var newName))
{
value = $"{newName}/{value}";
}
document.Set(columnName, value, Options.TrimStrings);
}
}
document.Id = id;
if (validator == null)
{
SetEmbeddedDocuments(document, table);
}
return(document);
}
public void Dispose()
{
SqlReader.DisposeAll();
SqlConnection.ClearAllPools();
_command?.Dispose();
}
