private void CheckSchema(string kind, IDictionary <string, object> data) { var newColumns = GetColumnsFromData(data); var autoIncrementName = _keyAccess.GetAutoIncrementName(kind); if (autoIncrementName != null) { newColumns.Remove(autoIncrementName); } if (!IsKnownKind(kind)) { foreach (var name in newColumns.Keys) { ValidateNewColumnRank(name, newColumns[name], data[name]); } _db.Exec(CommonDatabaseDetails.FormatCreateTableCommand(_details, kind, autoIncrementName, newColumns)); InvalidateSchema(); } else { var oldColumns = GetSchema()[kind]; var changedColumns = new Dictionary <string, int>(); var addedColumns = new Dictionary <string, int>(); foreach (var name in newColumns.Keys) { var newRank = newColumns[name]; if (!oldColumns.ContainsKey(name)) { ValidateNewColumnRank(name, newRank, data[name]); addedColumns[name] = newRank; } else { var oldRank = oldColumns[name]; if (newRank > oldRank && Math.Max(oldRank, newRank) < CommonDatabaseDetails.RANK_STATIC_BASE) { changedColumns[name] = newRank; } } } if (changedColumns.Count <= 0 && addedColumns.Count <= 0) { return; } _details.UpdateSchema(_db, kind, _keyAccess.GetAutoIncrementName(kind), oldColumns, changedColumns, addedColumns); InvalidateSchema(); } }
public object ExecInsert(IDatabaseAccess db, string tableName, string autoIncrementName, IDictionary <string, object> data) { db.Exec( CommonDatabaseDetails.FormatInsertCommand(this, tableName, data.Keys, defaultsExpr: "VALUES ()"), data.Values.ToArray() ); return(string.IsNullOrEmpty(autoIncrementName) ? null : db.Cell <object>(false, "SELECT LAST_INSERT_ID()")); // per-connection, http://stackoverflow.com/q/21185666 // robust to triggers, http://dba.stackexchange.com/a/25141 }
public void UpdateSchema(IDatabaseAccess db, string tableName, string autoIncrementName, IDictionary <string, int> oldColumns, IDictionary <string, int> changedColumns, IDictionary <string, int> addedColumns) { CommonDatabaseDetails.FixLongToDoubleUpgrade(this, db, tableName, oldColumns, changedColumns, RANK_INT64, RANK_DOUBLE, RANK_TEXT_36); var operations = changedColumns .Select(entry => $"CHANGE {QuoteName(entry.Key)} {QuoteName(entry.Key)} {GetSqlTypeFromRank(entry.Value)}") .ToList(); operations.AddRange(addedColumns.Select(entry => $"ADD {QuoteName(entry.Key)} {GetSqlTypeFromRank(entry.Value)}")); db.Exec("ALTER TABLE " + QuoteName(tableName) + " " + string.Join(", ", operations)); }
public object ExecInsert(IDatabaseAccess db, string tableName, string autoIncrementName, IDictionary <string, object> data) { db.Exec( CommonDatabaseDetails.FormatInsertCommand(this, tableName, data.Keys), data.Values.ToArray() ); if (string.IsNullOrEmpty(autoIncrementName)) { return(null); } // per-connection, robust to triggers // http://www.sqlite.org/c3ref/last_insert_rowid.html return(db.Cell <long>(false, "SELECT LAST_INSERT_ROWID()")); }
public void UpdateSchema(IDatabaseAccess db, string tableName, string autoIncrementName, IDictionary <string, int> oldColumns, IDictionary <string, int> changedColumns, IDictionary <string, int> addedColumns) { CommonDatabaseDetails.FixLongToDoubleUpgrade(this, db, tableName, oldColumns, changedColumns, RANK_INT64, RANK_DOUBLE, RANK_TEXT_32); tableName = QuoteName(tableName); foreach (var entry in changedColumns) { db.Exec( $"ALTER TABLE {tableName} ALTER COLUMN {QuoteName(entry.Key)} {GetSqlTypeFromRank(entry.Value)}"); } foreach (var entry in addedColumns) { db.Exec($"ALTER TABLE {tableName} ADD {QuoteName(entry.Key)} {GetSqlTypeFromRank(entry.Value)}"); } }
public object ExecInsert(IDatabaseAccess db, string tableName, string autoIncrementName, IDictionary <string, object> data) { var hasAutoIncrement = !string.IsNullOrEmpty(autoIncrementName); var postfix = hasAutoIncrement ? "returning " + QuoteName(autoIncrementName) : null; var sql = CommonDatabaseDetails.FormatInsertCommand(this, tableName, data.Keys, postfix: postfix); var values = data.Values.ToArray(); if (hasAutoIncrement) { return(db.Cell <object>(false, sql, values)); } db.Exec(sql, values); return(null); }
public void UpdateSchema(IDatabaseAccess db, string tableName, string autoIncrementName, IDictionary <string, int> oldColumns, IDictionary <string, int> changedColumns, IDictionary <string, int> addedColumns) { var operations = new List <string>(); CommonDatabaseDetails.FixLongToDoubleUpgrade(this, db, tableName, oldColumns, changedColumns, RANK_INT64, RANK_DOUBLE, RANK_NUMERIC); foreach (var entry in changedColumns) { operations.Add( $"ALTER {QuoteName(entry.Key)} TYPE {GetSqlTypeFromRank(entry.Value)} USING {QuoteName(entry.Key)}::{GetSqlTypeFromRank(entry.Value)}"); } foreach (var entry in addedColumns) { operations.Add($"ADD {QuoteName(entry.Key)} {GetSqlTypeFromRank(entry.Value)}"); } db.Exec("alter table " + QuoteName(tableName) + " " + string.Join(", ", operations)); }
public string QuoteName(string name) { return(CommonDatabaseDetails.QuoteWithBackticks(name)); }