/// <summary>
/// Get informations of columns
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
private TableColumnDefinition[] GetColumns(IDbConnection connection)
{
using (var cmd = new SQLiteCommand($"PRAGMA table_info({this.Name});"))
{
var table = new DataTable();
cmd.Connection = (SQLiteConnection)connection;
try
{
var adp = new SQLiteDataAdapter(cmd);
adp.Fill(table);
return(table.Rows
.OfType <DataRow>()
.Select(x => TableColumnDefinition.FromArray(x.ItemArray))
.ToArray());
}
catch (Exception)
{
throw;
}
}
}
/// <summary>
/// Migrate the table
/// </summary>
/// <param name="connection"></param>
/// <param name="existingColumns"></param>
/// <param name="tableInformations"></param>
/// <returns></returns>
private bool Migrate(IDbConnection connection, TableColumnDefinition[] existingColumns,
TableInformation[] tableInformations)
{
var isModified = false;
var eventArg = new MigratingEventArgs(tableInformations);
var converters = eventArg.Converters;
var convertDate = false;
// Convert DateTime representation from text to unix time
foreach (var column in existingColumns)
{
if (column.Type.ToLower().Contains("datetime"))
{
converters[column.Name] = $"strftime('%s',{column.Name})";
convertDate = true;
}
}
this.Migrating?.Invoke(this, eventArg);
if (existingColumns == null)
{
existingColumns = new TableColumnDefinition[0];
}
// New properties
var addedProperties = this.properties.Value
.ToDictionary(x => x.Key, x => x.Value);
foreach (var column in existingColumns)
{
if (addedProperties.ContainsKey(column.Name))
{
addedProperties.Remove(column.Name);
}
}
// Add columns
if (addedProperties.Count > 0)
{
using (var transaction = connection.BeginTransaction())
{
try
{
foreach (var column in addedProperties)
{
var schema = $"{column.Key} {TypeConversion[column.Value].ToString()}";
if (this.columnOptions.ContainsKey(column.Key))
{
schema = $"{schema} {this.columnOptions[column.Key]}";
}
connection.Execute
($"ALTER TABLE {this.Name} ADD COLUMN {schema}",
null, transaction);
}
transaction.Commit();
isModified = true;
}
catch
{
transaction.Rollback();
}
}
}
//// Removed properties
//var removedProperties = existingColumns
// .Where(x => !this.properties.Value.ContainsKey(x.Name))
// .ToArray();
// Remove columns
if (convertDate || existingColumns.Any(x => !this.properties.Value.ContainsKey(x.Name)))
{
using (var transaction = connection.BeginTransaction())
{
try
{
// Removing columns is not suppoted in SQLite
// Clone table
var tmpName = $"tmp_{this.Name}";
connection.Execute
($"ALTER TABLE {this.Name} RENAME TO {tmpName}",
null, transaction);
// Create new table
this.Create(connection, transaction);
var selector = this.properties.Value
.Where(x => !x.Key.Equals(IdName))
.OrderBy(x => x.Key)
.Select(x => converters.GetValueOrAlternative(x.Key, x.Key))
.Join(",\n ");
connection.Execute
($"INSERT INTO {this.Name}({IdName},\n {this.targets.Value}) "
+ $"SELECT {IdName},\n {selector} FROM {tmpName}",
null, transaction);
connection.Execute($"DROP TABLE {tmpName}",
null, transaction);
transaction.Commit();
isModified = true;
}
catch
{
transaction.Rollback();
}
try
{
connection.Execute($"VACUUM");
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
}
}
}
this.MakeInsertSchema(connection);
return(isModified);
}