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