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