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