public static void CheckDateTimeQueries(IDatabaseAccess db, DatabaseStorage storage)
{
storage.EnterFluidMode();
var dateTime = SAMPLE_DATETIME;
var date = SAMPLE_DATETIME.Date;
storage.Store("foo", MakeRow("d", date));
storage.Store("foo", MakeRow("d", dateTime));
Assert.Equal(2, db.Cell<int>(false, "select count(*) from foo where d = {0} or d = {1}", date, dateTime));
db.Exec("delete from foo");
for(var i = -2; i <= 2; i++)
storage.Store("foo", MakeRow("d", date.AddDays(i)));
Assert.Equal(3, db.Cell<int>(false, "select count(*) from foo where d between {0} and {1}", date.AddDays(-1), date.AddDays(1)));
Assert.Equal(date, db.Cell<DateTime>(false, "select d from foo where d = {0}", date));
}
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 object ExecInsert(IDatabaseAccess db, string tableName, string autoIncrementName, IDictionary<string, object> data)
{
var hasAutoIncrement = !String.IsNullOrEmpty(autoIncrementName);
var valuesPrefix = hasAutoIncrement
? "output inserted." + QuoteName(autoIncrementName)
: null;
var sql = CommonDatabaseDetails.FormatInsertCommand(this, tableName, data.Keys, valuesPrefix: valuesPrefix);
var values = data.Values.ToArray();
if(hasAutoIncrement)
return db.Cell<object>(false, sql, values);
db.Exec(sql, values);
return null;
}
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()
);
if(String.IsNullOrEmpty(autoIncrementName))
return null;
// per-connection, http://stackoverflow.com/q/21185666
// robust to triggers, http://dba.stackexchange.com/a/25141
return db.Cell<object>(false, "select last_insert_id()");
}
public void ExecInitCommands(IDatabaseAccess db)
{
_charset = db.Cell<string>(false, "show charset like 'utf8mb4'") != null ? "utf8mb4" : "utf8";
db.Exec("set names " + _charset);
}
public static void CheckLongToDouble(IDatabaseAccess db, DatabaseStorage storage)
{
storage.EnterFluidMode();
var bigLong = Int64.MinValue + 12345;
var longID = storage.Store("foo", MakeRow("p", bigLong));
storage.Store("foo", MakeRow("p", Math.PI));
Assert.Equal(bigLong, db.Cell<long>(false, "select p from foo where id = {0}", longID));
}
public static void CheckGuidQuery(IDatabaseAccess db, DatabaseStorage storage)
{
storage.EnterFluidMode();
storage.Store("foo", MakeRow("g", SAMPLE_GUID));
Assert.Equal(SAMPLE_GUID, db.Cell<Guid>(false, "select g from foo where g = {0}", SAMPLE_GUID));
}
public static void CheckReadUncommitted(IDatabaseAccess db1, IDatabaseAccess db2)
{
db1.Exec("create table foo(f text)");
db1.Exec("insert into foo(f) values('initial')");
db1.Transaction(delegate() {
db1.Exec("update foo set f='dirty'");
db2.TransactionIsolation = IsolationLevel.ReadUncommitted;
db2.Transaction(delegate() {
Assert.Equal("dirty", db2.Cell<string>(false, "select f from foo"));
return true;
});
return true;
});
}
public static void CheckSchemaReadingKeepsCache(IDatabaseAccess db, DatabaseStorage storage)
{
db.Exec("create table foo(bar int)");
db.Exec("insert into foo(bar) values(1)");
var queryCount = 0;
db.QueryExecuting += cmd => queryCount++;
db.Cell<int>(true, "Select * from foo");
storage.GetSchema();
var savedQueryCount = queryCount;
db.Cell<int>(true, "Select * from foo");
Assert.Equal(savedQueryCount, queryCount);
}
public void CustomRank_ExistingColumn()
{
_db.Exec("create table foo(id int, p smallmoney)");
_storage.Store("foo", SharedChecks.MakeRow("p", new SqlMoney(9.9)));
Assert.Equal(9.900M, _db.Cell <object>(false, "select p from foo"));
}
public void TypedReadsUseConvertSafe()
{
Assert.Equal(DayOfWeek.Thursday, _db.Cell <DayOfWeek?>(false, "select 4"));
}
public void CustomRank_ExistingColumn()
{
_db.Exec("create table foo(id int, p point)");
_storage.Store("foo", SharedChecks.MakeRow("p", new NpgsqlPoint(54.2, 37.61667)));
Assert.Equal(54.2, _db.Cell <NpgsqlPoint>(false, "select p from foo").X);
}
public static void CheckGuidQuery(IDatabaseAccess db, DatabaseStorage storage)
{
storage.EnterFluidMode();
storage.Store("foo", MakeRow("g", SAMPLE_GUID));
Assert.Equal(SAMPLE_GUID, db.Cell <Guid>(false, "select g from foo where g = {0}", SAMPLE_GUID));
}
public void ExecInitCommands(IDatabaseAccess db)
{
_charset = db.Cell <string>(false, "show charset like 'utf8mb4'") != null ? "utf8mb4" : "utf8";
db.Exec("set names " + _charset);
}