public void Trash(string kind, object key)
{
if (_isFluidMode && !IsKnownKind(kind))
{
return;
}
var(sql, parameters) = CreateSimpleByKeyArguments("DELETE", kind, key);
_db.Exec(sql, parameters);
}
public void Schema()
{
_db.Exec(@"create table foo(
id int,
b tinyint,
i int,
l bigint,
d float(53),
t1 nvarchar(32),
t2 nvarchar(4000),
t3 nvarchar(MAX),
dt datetime2,
dto datetimeoffset,
g uniqueidentifier,
bl varbinary(MAX),
x1 bit,
x2 decimal,
x3 float(10),
x4 varchar(32),
x5 nvarchar(33),
x6 datetime,
x7 varbinary(123),
x8 int not null,
x9 int default 123
)");
var schema = _storage.GetSchema();
Assert.Equal(1, schema.Count);
var cols = schema["foo"];
Assert.False(cols.ContainsKey("id"));
Assert.Equal(MsSqlDetails.RANK_BYTE, cols["b"]);
Assert.Equal(MsSqlDetails.RANK_INT32, cols["i"]);
Assert.Equal(MsSqlDetails.RANK_INT64, cols["l"]);
Assert.Equal(MsSqlDetails.RANK_DOUBLE, cols["d"]);
Assert.Equal(MsSqlDetails.RANK_TEXT_32, cols["t1"]);
Assert.Equal(MsSqlDetails.RANK_TEXT_4000, cols["t2"]);
Assert.Equal(MsSqlDetails.RANK_TEXT_MAX, cols["t3"]);
Assert.Equal(MsSqlDetails.RANK_STATIC_DATETIME, cols["dt"]);
Assert.Equal(MsSqlDetails.RANK_STATIC_DATETIME_OFFSET, cols["dto"]);
Assert.Equal(MsSqlDetails.RANK_STATIC_GUID, cols["g"]);
Assert.Equal(MsSqlDetails.RANK_STATIC_BLOB, cols["bl"]);
foreach (var i in Enumerable.Range(1, 9))
{
Assert.Equal(CommonDatabaseDetails.RANK_CUSTOM, cols["x" + i]);
}
}
public void Trash(string kind, object key)
{
if (_isFluidMode && !IsKnownKind(kind))
{
return;
}
var args = CreateSimpleByKeyArguments("delete", kind, key);
_db.Exec(args.Item1, args.Item2);
}
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(String.Format("alter table {0} alter column {1} {2}", tableName, QuoteName(entry.Key), GetSqlTypeFromRank(entry.Value)));
}
foreach (var entry in addedColumns)
{
db.Exec(String.Format("alter table {0} add {1} {2}", tableName, QuoteName(entry.Key), GetSqlTypeFromRank(entry.Value)));
}
}
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 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 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 void UpdateSchema(IDatabaseAccess db, string tableName, string autoIncrementName, IDictionary <string, int> oldColumns, IDictionary <string, int> changedColumns, IDictionary <string, int> addedColumns)
{
if (changedColumns.Count > 0)
{
throw new NotSupportedException();
}
foreach (var entry in addedColumns)
{
db.Exec("alter table " + QuoteName(tableName) + " add " + QuoteName(entry.Key) + " " + GetSqlTypeFromRank(entry.Value));
}
}
public void Schema()
{
var details = new SQLiteDetails();
_db.Exec("create table t (id, a)");
var schema = _storage.GetSchema();
Assert.Equal(1, schema.Count);
var t = schema["t"];
Assert.False(t.ContainsKey("id"));
Assert.Equal(SQLiteDetails.RANK_ANY, t["a"]);
Assert.Null(details.GetSqlTypeFromRank(t["a"]));
}
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 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)
{
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 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 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)
{
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 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 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(String.Format("alter {0} type {1} using {0}::{1}", QuoteName(entry.Key), GetSqlTypeFromRank(entry.Value)));
}
foreach (var entry in addedColumns)
{
operations.Add(String.Format("add {0} {1}", QuoteName(entry.Key), GetSqlTypeFromRank(entry.Value)));
}
db.Exec("alter table " + QuoteName(tableName) + " " + String.Join(", ", operations));
}
public void Schema()
{
_db.Exec("create table t (id, a)");
var schema = _storage.GetSchema();
Assert.Equal(1, schema.Count);
var t = schema["t"];
Assert.False(t.ContainsKey("id"));
Assert.Equal(SQLiteDetails.RANK_ANY, t["a"]);
}
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)
{
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 Check(object before, object after)
{
var id = _storage.Store("foo", new Dictionary <string, object> {
{ "p", before }
});
try {
var loaded = _storage.Load("foo", id);
Assert.Equal(after, loaded.GetSafe("p"));
if (after != null)
{
Assert.Equal(after.GetType(), loaded["p"].GetType());
}
} finally {
_db.Exec("drop table foo");
_storage.InvalidateSchema();
}
}
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 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 void UpdateSchema(IDatabaseAccess db, string tableName, string autoIncrementName, IDictionary<string, int> oldColumns, IDictionary<string, int> changedColumns, IDictionary<string, int> addedColumns)
{
if(changedColumns.Count > 0)
throw new NotSupportedException();
foreach(var entry in addedColumns)
db.Exec("alter table " + QuoteName(tableName) + " add " + QuoteName(entry.Key) + " " + GetSqlTypeFromRank(entry.Value));
}
public void Schema()
{
var details = new MariaDbDetails();
_db.Exec(@"create table t(
id int,
ti1 TinyInt(123),
ti2 TINYINT,
ti3 bool,
ti4 Boolean,
i1 integer(123),
i2 Integer,
i3 INT,
bi1 bigint(123),
bi2 BIGINT,
d1 Double,
d2 DOUBLE PRECISION,
t1 varchar(8),
t2 varchar(16),
t3 varchar(32),
t4 varchar(36),
t5 varchar(64),
t6 varchar(128),
t7 varchar(190),
t8 varchar(256),
t9 varchar(512),
t10 LongText,
dt1 datetime,
b1 longblob,
x1 smallint,
x2 mediumint,
x3 double(3,2),
x4 float,
x5 decimal,
x6 date,
x7 timestamp,
x8 char(36),
x9 varchar(123),
x10 binary,
x11 blob,
x12 text,
x13 int unsigned,
x14 int not null,
x15 int default '123'
)");
var schema = _storage.GetSchema();
Assert.Equal(1, schema.Count);
var t = schema["t"];
Assert.False(t.ContainsKey("id"));
Assert.Equal(MariaDbDetails.RANK_INT8, t["ti1"]);
Assert.Equal("TINYINT", details.GetSqlTypeFromRank(t["ti1"]));
Assert.Equal(MariaDbDetails.RANK_INT8, t["ti2"]);
Assert.Equal("TINYINT", details.GetSqlTypeFromRank(t["ti2"]));
Assert.Equal(MariaDbDetails.RANK_INT8, t["ti3"]);
Assert.Equal("TINYINT", details.GetSqlTypeFromRank(t["ti3"]));
Assert.Equal(MariaDbDetails.RANK_INT8, t["ti4"]);
Assert.Equal("TINYINT", details.GetSqlTypeFromRank(t["ti4"]));
Assert.Equal(MariaDbDetails.RANK_INT32, t["i1"]);
Assert.Equal("INT", details.GetSqlTypeFromRank(t["i1"]));
Assert.Equal(MariaDbDetails.RANK_INT32, t["i2"]);
Assert.Equal("INT", details.GetSqlTypeFromRank(t["i2"]));
Assert.Equal(MariaDbDetails.RANK_INT32, t["i3"]);
Assert.Equal("INT", details.GetSqlTypeFromRank(t["i3"]));
Assert.Equal(MariaDbDetails.RANK_INT64, t["bi1"]);
Assert.Equal("BIGINT", details.GetSqlTypeFromRank(t["bi1"]));
Assert.Equal(MariaDbDetails.RANK_INT64, t["bi2"]);
Assert.Equal("BIGINT", details.GetSqlTypeFromRank(t["bi2"]));
Assert.Equal(MariaDbDetails.RANK_DOUBLE, t["d1"]);
Assert.Equal("DOUBLE", details.GetSqlTypeFromRank(t["d1"]));
Assert.Equal(MariaDbDetails.RANK_DOUBLE, t["d2"]);
Assert.Equal("DOUBLE", details.GetSqlTypeFromRank(t["d2"]));
Assert.Equal(MariaDbDetails.RANK_TEXT_8, t["t1"]);
Assert.Equal("VARCHAR(8)", details.GetSqlTypeFromRank(t["t1"]));
Assert.Equal(MariaDbDetails.RANK_TEXT_16, t["t2"]);
Assert.Equal("VARCHAR(16)", details.GetSqlTypeFromRank(t["t2"]));
Assert.Equal(MariaDbDetails.RANK_TEXT_32, t["t3"]);
Assert.Equal("VARCHAR(32)", details.GetSqlTypeFromRank(t["t3"]));
Assert.Equal(MariaDbDetails.RANK_TEXT_36, t["t4"]);
Assert.Equal("VARCHAR(36)", details.GetSqlTypeFromRank(t["t4"]));
Assert.Equal(MariaDbDetails.RANK_TEXT_64, t["t5"]);
Assert.Equal("VARCHAR(64)", details.GetSqlTypeFromRank(t["t5"]));
Assert.Equal(MariaDbDetails.RANK_TEXT_128, t["t6"]);
Assert.Equal("VARCHAR(128)", details.GetSqlTypeFromRank(t["t6"]));
Assert.Equal(MariaDbDetails.RANK_TEXT_190, t["t7"]);
Assert.Equal("VARCHAR(190)", details.GetSqlTypeFromRank(t["t7"]));
Assert.Equal(MariaDbDetails.RANK_TEXT_256, t["t8"]);
Assert.Equal("VARCHAR(256)", details.GetSqlTypeFromRank(t["t8"]));
Assert.Equal(MariaDbDetails.RANK_TEXT_512, t["t9"]);
Assert.Equal("VARCHAR(512)", details.GetSqlTypeFromRank(t["t9"]));
Assert.Equal(MariaDbDetails.RANK_TEXT_MAX, t["t10"]);
Assert.Equal("LONGTEXT", details.GetSqlTypeFromRank(t["t10"]));
Assert.Equal(MariaDbDetails.RANK_STATIC_DATETIME, t["dt1"]);
Assert.Equal("DATETIME", details.GetSqlTypeFromRank(t["dt1"]));
Assert.Equal(MariaDbDetails.RANK_STATIC_BLOB, t["b1"]);
Assert.Equal("LONGBLOB", details.GetSqlTypeFromRank(t["b1"]));
foreach (var i in Enumerable.Range(1, 15))
{
Assert.Equal(CommonDatabaseDetails.RANK_CUSTOM, t["x" + i]);
}
}
public void Schema()
{
_db.Exec(@"create table t(
id int,
ti1 TinyInt(123),
ti2 TINYINT,
ti3 bool,
ti4 Boolean,
i1 integer(123),
i2 Integer,
i3 INT,
bi1 bigint(123),
bi2 BIGINT,
d1 Double,
d2 DOUBLE PRECISION,
t1 varchar(36),
t2 VarChar(191),
t3 LongText,
dt1 datetime,
b1 longblob,
x1 smallint,
x2 mediumint,
x3 double(3,2),
x4 float,
x5 decimal,
x6 date,
x7 timestamp,
x8 char(36),
x9 varchar(123),
x10 binary,
x11 blob,
x12 text,
x13 int unsigned,
x14 int not null,
x15 int default '123'
)");
var schema = _storage.GetSchema();
Assert.Equal(1, schema.Count);
var t = schema["t"];
Assert.False(t.ContainsKey("id"));
Assert.Equal(MariaDbDetails.RANK_INT8, t["ti1"]);
Assert.Equal(MariaDbDetails.RANK_INT8, t["ti2"]);
Assert.Equal(MariaDbDetails.RANK_INT8, t["ti3"]);
Assert.Equal(MariaDbDetails.RANK_INT8, t["ti4"]);
Assert.Equal(MariaDbDetails.RANK_INT32, t["i1"]);
Assert.Equal(MariaDbDetails.RANK_INT32, t["i2"]);
Assert.Equal(MariaDbDetails.RANK_INT32, t["i3"]);
Assert.Equal(MariaDbDetails.RANK_INT64, t["bi1"]);
Assert.Equal(MariaDbDetails.RANK_INT64, t["bi2"]);
Assert.Equal(MariaDbDetails.RANK_DOUBLE, t["d1"]);
Assert.Equal(MariaDbDetails.RANK_DOUBLE, t["d2"]);
Assert.Equal(MariaDbDetails.RANK_TEXT_36, t["t1"]);
Assert.Equal(MariaDbDetails.RANK_TEXT_191, t["t2"]);
Assert.Equal(MariaDbDetails.RANK_TEXT_MAX, t["t3"]);
Assert.Equal(MariaDbDetails.RANK_STATIC_DATETIME, t["dt1"]);
Assert.Equal(MariaDbDetails.RANK_STATIC_BLOB, t["b1"]);
foreach (var i in Enumerable.Range(1, 15))
{
Assert.Equal(CommonDatabaseDetails.RANK_CUSTOM, t["x" + i]);
}
}
public void Schema()
{
var details = new MsSqlDetails();
_db.Exec(@"create table foo(
id int,
b tinyint,
i int,
l bigint,
d float(53),
t1 nvarchar(8),
t2 nvarchar(16),
t3 nvarchar(32),
t4 nvarchar(64),
t5 nvarchar(128),
t6 nvarchar(256),
t7 nvarchar(512),
t8 nvarchar(4000),
t9 nvarchar(MAX),
dt datetime2,
dto datetimeoffset,
g uniqueidentifier,
bl varbinary(MAX),
x1 bit,
x2 decimal,
x3 float(10),
x4 varchar(32),
x5 nvarchar(33),
x6 datetime,
x7 varbinary(123),
x8 int not null,
x9 int default 123
)");
var schema = _storage.GetSchema();
Assert.Equal(1, schema.Count);
var cols = schema["foo"];
Assert.False(cols.ContainsKey("id"));
Assert.Equal(MsSqlDetails.RANK_BYTE, cols["b"]);
Assert.Equal("TINYINT", details.GetSqlTypeFromRank(cols["b"]));
Assert.Equal(MsSqlDetails.RANK_INT32, cols["i"]);
Assert.Equal("INT", details.GetSqlTypeFromRank(cols["i"]));
Assert.Equal(MsSqlDetails.RANK_INT64, cols["l"]);
Assert.Equal("BIGINT", details.GetSqlTypeFromRank(cols["l"]));
Assert.Equal(MsSqlDetails.RANK_DOUBLE, cols["d"]);
Assert.Equal("FLOAT(53)", details.GetSqlTypeFromRank(cols["d"]));
Assert.Equal(MsSqlDetails.RANK_TEXT_8, cols["t1"]);
Assert.Equal("NVARCHAR(8)", details.GetSqlTypeFromRank(cols["t1"]));
Assert.Equal(MsSqlDetails.RANK_TEXT_16, cols["t2"]);
Assert.Equal("NVARCHAR(16)", details.GetSqlTypeFromRank(cols["t2"]));
Assert.Equal(MsSqlDetails.RANK_TEXT_32, cols["t3"]);
Assert.Equal("NVARCHAR(32)", details.GetSqlTypeFromRank(cols["t3"]));
Assert.Equal(MsSqlDetails.RANK_TEXT_64, cols["t4"]);
Assert.Equal("NVARCHAR(64)", details.GetSqlTypeFromRank(cols["t4"]));
Assert.Equal(MsSqlDetails.RANK_TEXT_128, cols["t5"]);
Assert.Equal("NVARCHAR(128)", details.GetSqlTypeFromRank(cols["t5"]));
Assert.Equal(MsSqlDetails.RANK_TEXT_256, cols["t6"]);
Assert.Equal("NVARCHAR(256)", details.GetSqlTypeFromRank(cols["t6"]));
Assert.Equal(MsSqlDetails.RANK_TEXT_512, cols["t7"]);
Assert.Equal("NVARCHAR(512)", details.GetSqlTypeFromRank(cols["t7"]));
Assert.Equal(MsSqlDetails.RANK_TEXT_4000, cols["t8"]);
Assert.Equal("NVARCHAR(4000)", details.GetSqlTypeFromRank(cols["t8"]));
Assert.Equal(MsSqlDetails.RANK_TEXT_MAX, cols["t9"]);
Assert.Equal("NVARCHAR(MAX)", details.GetSqlTypeFromRank(cols["t9"]));
Assert.Equal(MsSqlDetails.RANK_STATIC_DATETIME, cols["dt"]);
Assert.Equal("DATETIME2", details.GetSqlTypeFromRank(cols["dt"]));
Assert.Equal(MsSqlDetails.RANK_STATIC_DATETIME_OFFSET, cols["dto"]);
Assert.Equal("DATETIMEOFFSET", details.GetSqlTypeFromRank(cols["dto"]));
Assert.Equal(MsSqlDetails.RANK_STATIC_GUID, cols["g"]);
Assert.Equal("UNIQUEIDENTIFIER", details.GetSqlTypeFromRank(cols["g"]));
Assert.Equal(MsSqlDetails.RANK_STATIC_BLOB, cols["bl"]);
Assert.Equal("VARBINARY(MAX)", details.GetSqlTypeFromRank(cols["bl"]));
foreach (var i in Enumerable.Range(1, 9))
{
Assert.Equal(CommonDatabaseDetails.RANK_CUSTOM, cols["x" + i]);
}
}
public void ExecInitCommands(IDatabaseAccess db)
{
_charset = db.Cell <string>(false, "show charset like 'utf8mb4'") != null ? "utf8mb4" : "utf8";
db.Exec("set names " + _charset);
}
public void Schema()
{
_db.Exec(@"create table foo(
id serial,
b boolean,
i Integer,
l BigInt,
d DOUBLE precision,
n Numeric,
t text,
ts timestamp,
tz timestamptz,
g uuid,
bl bytea,
b2 BOOL,
i2 INT4,
l2 INT8,
d2 FLOAT8,
ts2 timestamp without time zone,
tz2 timestamp with time zone,
x1 integer not null,
x2 integer default 123,
x3 numeric(10),
x4 numeric(10, 5),
x5 json,
x6 varchar(255)
)");
var cols = _storage.GetSchema()["foo"];
Assert.False(cols.ContainsKey("id"));
Assert.Equal(PgSqlDetails.RANK_BOOLEAN, cols["b"]);
Assert.Equal(PgSqlDetails.RANK_BOOLEAN, cols["b2"]);
Assert.Equal(PgSqlDetails.RANK_INT32, cols["i"]);
Assert.Equal(PgSqlDetails.RANK_INT32, cols["i2"]);
Assert.Equal(PgSqlDetails.RANK_INT64, cols["l"]);
Assert.Equal(PgSqlDetails.RANK_INT64, cols["l2"]);
Assert.Equal(PgSqlDetails.RANK_DOUBLE, cols["d"]);
Assert.Equal(PgSqlDetails.RANK_DOUBLE, cols["d2"]);
Assert.Equal(PgSqlDetails.RANK_NUMERIC, cols["n"]);
Assert.Equal(PgSqlDetails.RANK_TEXT, cols["t"]);
Assert.Equal(PgSqlDetails.RANK_STATIC_DATETIME, cols["ts"]);
Assert.Equal(PgSqlDetails.RANK_STATIC_DATETIME, cols["ts2"]);
Assert.Equal(PgSqlDetails.RANK_STATIC_DATETIME_OFFSET, cols["tz"]);
Assert.Equal(PgSqlDetails.RANK_STATIC_DATETIME_OFFSET, cols["tz2"]);
Assert.Equal(PgSqlDetails.RANK_STATIC_GUID, cols["g"]);
Assert.Equal(PgSqlDetails.RANK_STATIC_BLOB, cols["bl"]);
foreach (var i in Enumerable.Range(1, 6))
{
Assert.Equal(CommonDatabaseDetails.RANK_CUSTOM, cols["x" + i]);
}
}
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 void ExecInitCommands(IDatabaseAccess db)
{
_charset = db.Cell<string>(false, "show charset like 'utf8mb4'") != null ? "utf8mb4" : "utf8";
db.Exec("set names " + _charset);
}
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_TEXT_36);
foreach(var entry in changedColumns)
operations.Add(String.Format("change {0} {0} {1}", QuoteName(entry.Key), GetSqlTypeFromRank(entry.Value)));
foreach(var entry in addedColumns)
operations.Add(String.Format("add {0} {1}", QuoteName(entry.Key), GetSqlTypeFromRank(entry.Value)));
db.Exec("alter table " + QuoteName(tableName) + " " + String.Join(", ", operations));
}
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 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(String.Format("alter table {0} alter column {1} {2}", tableName, QuoteName(entry.Key), GetSqlTypeFromRank(entry.Value)));
foreach(var entry in addedColumns)
db.Exec(String.Format("alter table {0} add {1} {2}", tableName, QuoteName(entry.Key), GetSqlTypeFromRank(entry.Value)));
}
public void Caching_SameQueriesDifferByFetchType()
{
var sql = "select 1";
Assert.Null(Record.Exception(delegate() {
_db.Col <int>(true, sql);
_db.Cell <int>(true, sql);
_db.Row(true, sql);
_db.Rows(true, sql);
_db.Exec(sql);
_db.ColIterator <int>(sql).Last();
_db.RowsIterator(sql).Last();
}));
}
