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