internal SqliteTransaction(SqliteConnection connection, IsolationLevel isolationLevel)
{
_connection = connection;
_isolationLevel = isolationLevel;
if (isolationLevel == IsolationLevel.ReadUncommitted)
{
if (connection.ConnectionStringBuilder.CacheMode != CacheMode.Shared)
{
throw new ArgumentException(Strings.FormatInvalidIsolationLevelForUnsharedCache(isolationLevel));
}
connection.ExecuteNonQuery("PRAGMA read_uncommitted = 1;");
}
else if (isolationLevel == IsolationLevel.Serializable)
{
connection.ExecuteNonQuery("PRAGMA read_uncommitted = 0;");
}
else if (isolationLevel != IsolationLevel.Unspecified)
{
throw new ArgumentException(Strings.FormatInvalidIsolationLevel(isolationLevel));
}
// TODO: Register transaction hooks to detect when a user manually completes a transaction created using this API
connection.ExecuteNonQuery("BEGIN;");
}
public void ExecuteReader_honors_CommandTimeout()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("CREATE TABLE Data (Value); INSERT INTO Data VALUES (0);");
using (connection.ExecuteReader("SELECT * FROM Data;"))
{
var command = connection.CreateCommand();
command.CommandText = "DROP TABLE Data;";
command.CommandTimeout = 1;
var stopwatch = Stopwatch.StartNew();
Assert.Throws <SqliteException>(() => command.ExecuteNonQuery());
stopwatch.Stop();
Assert.InRange(stopwatch.ElapsedMilliseconds, 1000, 1999);
}
}
}
private void Open_works_when_password_supported()
{
using (var connection1 = new SqliteConnection("Data Source=encrypted.db;Password=password"))
{
connection1.Open();
// NB: The file is only encrypted after writing
connection1.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS dual (dummy)");
using (var connection2 = new SqliteConnection("Data Source=encrypted.db"))
{
var stateChangeRaised = false;
connection2.StateChange += (sender, e) => stateChangeRaised = true;
var ex = Assert.Throws <SqliteException>(() => connection2.Open());
Assert.Equal(SQLITE_NOTADB, ex.SqliteErrorCode);
Assert.False(stateChangeRaised);
Assert.Equal(ConnectionState.Closed, connection2.State);
}
}
}
public void NextResult_skips_DML_statements()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("CREATE TABLE Test(Value);");
var sql = @"
SELECT 1;
INSERT INTO Test VALUES(1);
SELECT 2;";
using (var reader = connection.ExecuteReader(sql))
{
var hasResults = reader.NextResult();
Assert.True(hasResults);
var hasData = reader.Read();
Assert.True(hasData);
Assert.Equal(2L, reader.GetInt64(0));
}
}
}
public void Open_decrypts_lazily_when_no_password()
{
try
{
using var connection1 = new SqliteConnection("Data Source=encrypted2.db;Password=password");
connection1.Open();
// NB: The file is only encrypted after writing
connection1.ExecuteNonQuery(
"CREATE TABLE IF NOT EXISTS data (value); INSERT INTO data (value) VALUES (1);");
using var connection2 = new SqliteConnection("Data Source=encrypted2.db");
connection2.Open();
connection2.ExecuteNonQuery("PRAGMA key = 'password';");
var value = connection2.ExecuteScalar <long>("SELECT value FROM data;");
Assert.Equal(1L, value);
}
finally
{
File.Delete("encrypted2.db");
}
}
public void EnableExtensions_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
var sql = "SELECT load_extension('unknown');";
var ex = Assert.Throws<SqliteException>(() => connection.ExecuteNonQuery(sql));
var originalError = ex.Message;
connection.EnableExtensions();
ex = Assert.Throws<SqliteException>(() => connection.ExecuteNonQuery(sql));
var enabledError = ex.Message;
connection.EnableExtensions(enable: false);
ex = Assert.Throws<SqliteException>(() => connection.ExecuteNonQuery(sql));
var disabledError = ex.Message;
Assert.NotEqual(originalError, enabledError);
Assert.Equal(originalError, disabledError);
}
}
public void Open_works_when_memory_shared()
{
var connectionString = "Data Source=people;Mode=Memory;Cache=Shared";
using (var connection1 = new SqliteConnection(connectionString))
{
connection1.Open();
connection1.ExecuteNonQuery(
"CREATE TABLE Person (Name TEXT);" +
"INSERT INTO Person VALUES ('Waldo');");
using (var connection2 = new SqliteConnection(connectionString))
{
connection2.Open();
var name = connection2.ExecuteScalar<string>("SELECT Name FROM Person;");
Assert.Equal("Waldo", name);
}
}
}
public void Open_works_when_readonly()
{
using (var connection = new SqliteConnection("Data Source=readonly.db"))
{
connection.Open();
if (connection.ExecuteScalar<long>("SELECT COUNT(*) FROM sqlite_master WHERE name = 'Idomic';") == 0)
{
connection.ExecuteNonQuery("CREATE TABLE Idomic (Word TEXT);");
}
}
using (var connection = new SqliteConnection("Data Source=readonly.db;Mode=ReadOnly"))
{
connection.Open();
var ex = Assert.Throws<SqliteException>(
() => connection.ExecuteNonQuery("INSERT INTO Idomic VALUES ('arimfexendrapuse');"));
Assert.Equal(SQLITE_READONLY, ex.SqliteErrorCode);
}
}
public void ExecuteReader_skips_DML_statements()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("CREATE TABLE Test(Value);");
var command = connection.CreateCommand();
command.CommandText = @"
INSERT INTO Test VALUES(1);
SELECT 1;";
using (var reader = command.ExecuteReader())
{
var hasData = reader.Read();
Assert.True(hasData);
Assert.Equal(1L, reader.GetInt64(0));
}
}
}
public void GetSchemaTable_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery(
"CREATE TABLE Person (ID INTEGER PRIMARY KEY, FirstName TEXT, LastName TEXT NOT NULL, Code INT UNIQUE);");
connection.ExecuteNonQuery("INSERT INTO Person VALUES(101, 'John', 'Dee', 123);");
connection.ExecuteNonQuery("INSERT INTO Person VALUES(105, 'Jane', 'Doe', 456);");
using (var reader = connection.ExecuteReader("SELECT LastName, ID, Code, ID+1 AS IncID FROM Person;"))
{
var schema = reader.GetSchemaTable();
Assert.True(schema.Columns.Contains("ColumnName"));
Assert.True(schema.Columns.Contains("ColumnOrdinal"));
Assert.True(schema.Columns.Contains("ColumnSize"));
Assert.True(schema.Columns.Contains("NumericPrecision"));
Assert.True(schema.Columns.Contains("NumericScale"));
Assert.True(schema.Columns.Contains("IsUnique"));
Assert.True(schema.Columns.Contains("IsKey"));
Assert.True(schema.Columns.Contains("BaseServerName"));
Assert.True(schema.Columns.Contains("BaseCatalogName"));
Assert.True(schema.Columns.Contains("BaseColumnName"));
Assert.True(schema.Columns.Contains("BaseSchemaName"));
Assert.True(schema.Columns.Contains("BaseTableName"));
Assert.True(schema.Columns.Contains("DataType"));
Assert.True(schema.Columns.Contains("DataTypeName"));
Assert.True(schema.Columns.Contains("AllowDBNull"));
Assert.True(schema.Columns.Contains("IsAliased"));
Assert.True(schema.Columns.Contains("IsExpression"));
Assert.True(schema.Columns.Contains("IsAutoIncrement"));
Assert.True(schema.Columns.Contains("IsLong"));
Assert.Equal(4, schema.Rows.Count);
Assert.Equal("LastName", schema.Rows[0]["ColumnName"]);
Assert.Equal(0, schema.Rows[0]["ColumnOrdinal"]);
Assert.Equal(DBNull.Value, schema.Rows[0]["ColumnSize"]);
Assert.Equal(DBNull.Value, schema.Rows[0]["NumericPrecision"]);
Assert.Equal(DBNull.Value, schema.Rows[0]["NumericScale"]);
Assert.False((bool)schema.Rows[0]["IsUnique"]);
Assert.False((bool)schema.Rows[0]["IsKey"]);
Assert.Equal("", schema.Rows[0]["BaseServerName"]);
Assert.Equal("main", schema.Rows[0]["BaseCatalogName"]);
Assert.Equal("LastName", schema.Rows[0]["BaseColumnName"]);
Assert.Equal(DBNull.Value, schema.Rows[0]["BaseSchemaName"]);
Assert.Equal("Person", schema.Rows[0]["BaseTableName"]);
Assert.Equal(typeof(string), schema.Rows[0]["DataType"]);
Assert.Equal("TEXT", schema.Rows[0]["DataTypeName"]);
Assert.False((bool)schema.Rows[0]["AllowDBNull"]);
Assert.False((bool)schema.Rows[0]["IsAliased"]);
Assert.False((bool)schema.Rows[0]["IsExpression"]);
Assert.False((bool)schema.Rows[0]["IsAutoIncrement"]);
Assert.Equal(DBNull.Value, schema.Rows[0]["IsLong"]);
Assert.Equal("ID", schema.Rows[1]["ColumnName"]);
Assert.Equal(1, schema.Rows[1]["ColumnOrdinal"]);
Assert.Equal(DBNull.Value, schema.Rows[1]["ColumnSize"]);
Assert.Equal(DBNull.Value, schema.Rows[1]["NumericPrecision"]);
Assert.Equal(DBNull.Value, schema.Rows[1]["NumericScale"]);
Assert.False((bool)schema.Rows[1]["IsUnique"]);
Assert.True((bool)schema.Rows[1]["IsKey"]);
Assert.Equal("", schema.Rows[1]["BaseServerName"]);
Assert.Equal("main", schema.Rows[1]["BaseCatalogName"]);
Assert.Equal("ID", schema.Rows[1]["BaseColumnName"]);
Assert.Equal(DBNull.Value, schema.Rows[1]["BaseSchemaName"]);
Assert.Equal("Person", schema.Rows[1]["BaseTableName"]);
Assert.Equal(typeof(long), schema.Rows[1]["DataType"]);
Assert.Equal("INTEGER", schema.Rows[1]["DataTypeName"]);
Assert.True((bool)schema.Rows[1]["AllowDBNull"]);
Assert.False((bool)schema.Rows[1]["IsAliased"]);
Assert.False((bool)schema.Rows[1]["IsExpression"]);
Assert.False((bool)schema.Rows[1]["IsAutoIncrement"]);
Assert.Equal(DBNull.Value, schema.Rows[1]["IsLong"]);
Assert.Equal("Code", schema.Rows[2]["ColumnName"]);
Assert.Equal(2, schema.Rows[2]["ColumnOrdinal"]);
Assert.Equal(DBNull.Value, schema.Rows[2]["ColumnSize"]);
Assert.Equal(DBNull.Value, schema.Rows[2]["NumericPrecision"]);
Assert.Equal(DBNull.Value, schema.Rows[2]["NumericScale"]);
Assert.True((bool)schema.Rows[2]["IsUnique"]);
Assert.False((bool)schema.Rows[2]["IsKey"]);
Assert.Equal("", schema.Rows[2]["BaseServerName"]);
Assert.Equal("main", schema.Rows[2]["BaseCatalogName"]);
Assert.Equal("Code", schema.Rows[2]["BaseColumnName"]);
Assert.Equal(DBNull.Value, schema.Rows[2]["BaseSchemaName"]);
Assert.Equal("Person", schema.Rows[2]["BaseTableName"]);
Assert.Equal(typeof(long), schema.Rows[2]["DataType"]);
Assert.Equal("INT", schema.Rows[2]["DataTypeName"]);
Assert.True((bool)schema.Rows[2]["AllowDBNull"]);
Assert.False((bool)schema.Rows[2]["IsAliased"]);
Assert.False((bool)schema.Rows[2]["IsExpression"]);
Assert.False((bool)schema.Rows[2]["IsAutoIncrement"]);
Assert.Equal(DBNull.Value, schema.Rows[2]["IsLong"]);
Assert.Equal("IncID", schema.Rows[3]["ColumnName"]);
Assert.Equal(3, schema.Rows[3]["ColumnOrdinal"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["ColumnSize"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["NumericPrecision"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["NumericScale"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["IsUnique"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["IsKey"]);
Assert.Equal("", schema.Rows[3]["BaseServerName"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["BaseCatalogName"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["BaseColumnName"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["BaseSchemaName"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["BaseTableName"]);
Assert.Equal(typeof(long), schema.Rows[3]["DataType"]);
Assert.Equal("INTEGER", schema.Rows[3]["DataTypeName"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["AllowDBNull"]);
Assert.True((bool)schema.Rows[3]["IsAliased"]);
Assert.True((bool)schema.Rows[3]["IsExpression"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["IsAutoIncrement"]);
Assert.Equal(DBNull.Value, schema.Rows[3]["IsLong"]);
}
}
}
public void IsolationLevel_is_infered_when_unspecified()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("PRAGMA read_uncommitted = 1;");
using (var transaction = connection.BeginTransaction())
{
Assert.Equal(IsolationLevel.ReadUncommitted, transaction.IsolationLevel);
}
}
}
private static void CreateTestTable(SqliteConnection connection)
{
connection.ExecuteNonQuery(@"
CREATE TABLE TestTable (
TestColumn INTEGER
)");
}
public void Dispose_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
CreateTestTable(connection);
using (var transaction = connection.BeginTransaction())
{
connection.ExecuteNonQuery("INSERT INTO TestTable VALUES (1);");
transaction.Dispose();
Assert.Null(connection.Transaction);
Assert.Null(transaction.Connection);
}
Assert.Equal(0L, connection.ExecuteScalar<long>("SELECT COUNT(*) FROM TestTable;"));
}
}