public void Can_get_results_from_nonreadonly_statements()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();

                var result = connection.ExecuteScalar <string>("PRAGMA journal_mode;");

                Assert.NotNull(result);
            }
        }
        public void Ctor_unsets_read_uncommitted_when_serializable()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();

                using (connection.BeginTransaction(IsolationLevel.Serializable))
                {
                    Assert.Equal(0L, connection.ExecuteScalar<long>("PRAGMA read_uncommitted;"));
                }
            }
        }
Example #3
0
        public void Ctor_unsets_read_uncommitted_when_serializable()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();

                using (connection.BeginTransaction(IsolationLevel.Serializable))
                {
                    Assert.Equal(0L, connection.ExecuteScalar <long>("PRAGMA read_uncommitted;"));
                }
            }
        }
Example #4
0
        public void Ctor_sets_read_uncommitted()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:;Cache=Shared"))
            {
                connection.Open();

                using (connection.BeginTransaction(IsolationLevel.ReadUncommitted))
                {
                    Assert.Equal(1L, connection.ExecuteScalar <long>("PRAGMA read_uncommitted;"));
                }
            }
        }
Example #5
0
        public void CreateFunction_works_when_parameter_null_and_type_byteArray()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.CreateFunction("test", (byte[] x) => x == null);

                var result = connection.ExecuteScalar <long>("SELECT test(NULL);");

                Assert.Equal(1L, result);
            }
        }
        public void Ctor_sets_read_uncommitted()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:;Cache=Shared"))
            {
                connection.Open();

                using (connection.BeginTransaction(IsolationLevel.ReadUncommitted))
                {
                    Assert.Equal(1L, connection.ExecuteScalar<long>("PRAGMA read_uncommitted;"));
                }
            }
        }
Example #7
0
        public void CreateFunction_works()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.CreateFunction("test", 1L, (long state, long x, int y) => $"{state} {x} {y}");

                var result = connection.ExecuteScalar <string>("SELECT test(2, 3);");

                Assert.Equal("1 2 3", result);
            }
        }
Example #8
0
        public void CreateFunction_works_when_parameter_empty_blob()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.CreateFunction("test", (byte[] x) => x?.Length == 0);

                var result = connection.ExecuteScalar <long>("SELECT test(X'');");

                Assert.Equal(1L, result);
            }
        }
Example #9
0
        public void CreateFunction_works_when_result_DBNull()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.CreateFunction <object>("test", () => DBNull.Value);

                var result = connection.ExecuteScalar <object>("SELECT test();");

                Assert.Equal(DBNull.Value, result);
            }
        }
Example #10
0
        public void CreateFunction_works_when_sqlite_exception()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.CreateFunction <long>("test", () => throw new SqliteException("Test", 200));

                var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <long>("SELECT test();"));

                Assert.Equal(Resources.SqliteNativeError(200, "Test"), ex.Message);
            }
        }
Example #11
0
        public void CreateFunction_works_when_null()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.CreateFunction("test", () => 1L);
                connection.CreateFunction("test", default(Func <long>));

                var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <long>("SELECT test();"));

                Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "no such function: test"), ex.Message);
            }
        }
Example #12
0
        public void CreateAggregate_works_when_params()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
                connection.CreateAggregate("test", (string a, object[] args) => a + string.Join(", ", args) + "; ");

                var result = connection.ExecuteScalar <string>("SELECT test(dummy) FROM dual;");

                Assert.Equal("X; ", result);
            }
        }
Example #13
0
        public void CreateFunction_works_when_params()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.CreateFunction(
                    "test",
                    args => string.Join(", ", args.Select(a => a?.GetType().FullName ?? "(null)")));

                var result = connection.ExecuteScalar <string>("SELECT test(1, 3.1, 'A', X'7E57', NULL);");

                Assert.Equal("System.Int64, System.Double, System.String, System.Byte[], (null)", result);
            }
        }
Example #14
0
        public void CreateCollation_with_null_comparer_works()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.CreateCollation("MY_NOCASE", (s1, s2) => string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase));
                connection.CreateCollation("MY_NOCASE", null);

                var ex = Assert.Throws <SqliteException>(
                    () => connection.ExecuteScalar <long>("SELECT 'Νικοσ' = 'ΝΙΚΟΣ' COLLATE MY_NOCASE;"));

                Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "no such collation sequence: MY_NOCASE"), ex.Message);
            }
        }
Example #15
0
        public void CreateFunction_works_when_parameter_null_but_type_long()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.CreateFunction("test", (long x) => x);

                var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <long>("SELECT test(NULL);"));

                Assert.Equal(
                    Resources.SqliteNativeError(SQLITE_ERROR, Resources.UDFCalledWithNull("test", 0)),
                    ex.Message);
            }
        }
Example #16
0
        public void CreateAggregate_works_when_sqlite_exception()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
                connection.CreateAggregate("test", (string a) => throw new SqliteException("Test", 200));

                var ex = Assert.Throws <SqliteException>(
                    () => connection.ExecuteScalar <string>("SELECT test() FROM dual;"));

                Assert.Equal(Resources.SqliteNativeError(200, "Test"), ex.Message);
            }
        }
        public void CreateAggregate_works_when_exception_during_final()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
                connection.CreateAggregate <string, string>("test", "A", a => "B", a => throw new Exception("Test"));

                var ex = Assert.Throws <SqliteException>(
                    () => connection.ExecuteScalar <string>("SELECT test() FROM dual;"));

                Assert.Equal(Resources.SqliteNativeError(raw.SQLITE_ERROR, "Test"), ex.Message);
                Assert.Equal(raw.SQLITE_ERROR, ex.SqliteErrorCode);
            }
        }
Example #18
0
        public void CreateAggregate_works_when_null()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
                connection.CreateAggregate("test", (string a) => "A");
                connection.CreateAggregate("test", default(Func <string, string>));

                var ex = Assert.Throws <SqliteException>(
                    () => connection.ExecuteScalar <long>("SELECT test() FROM dual;"));

                Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "no such function: test"), ex.Message);
            }
        }
Example #19
0
        public void CreateFunction_works_when_parameter_null_but_type_double()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.CreateFunction("test", (double x) => x);

                var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <double>("SELECT test(NULL);"));

                Assert.Equal(
                    Resources.SqliteNativeError(raw.SQLITE_ERROR, new InvalidCastException().Message),
                    ex.Message);
                Assert.Equal(raw.SQLITE_ERROR, ex.SqliteErrorCode);
            }
        }
Example #20
0
        public void CreateAggregate_works()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                connection.ExecuteNonQuery("CREATE TABLE dual2 (dummy1, dummy2); INSERT INTO dual2 (dummy1, dummy2) VALUES ('X', 1);");
                connection.CreateAggregate(
                    "test",
                    "A",
                    (string a, string x, int y) => a + x + y,
                    a => a + "Z");

                var result = connection.ExecuteScalar <string>("SELECT test(dummy1, dummy2) FROM dual2;");

                Assert.Equal("AX1Z", result);
            }
        }
Example #21
0
        public void BackupDatabase_works()
        {
            using (var connection1 = new SqliteConnection("Data Source=:memory:"))
            {
                connection1.Open();

                connection1.ExecuteNonQuery(
                    "CREATE TABLE Person (Name TEXT);" + "INSERT INTO Person VALUES ('Waldo');");

                using (var connection2 = new SqliteConnection("Data Source=:memory:"))
                {
                    connection2.Open();
                    connection1.BackupDatabase(connection2);

                    var name = connection2.ExecuteScalar <string>("SELECT Name FROM Person;");
                    Assert.Equal("Waldo", name);
                }
            }
        }
Example #22
0
        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);
                }
            }
        }
Example #23
0
        public void Collations_dont_bleed_across_connections()
        {
            sqlite3 db;

            using (var connection1 = new SqliteConnection(ConnectionString))
            {
                connection1.CreateCollation("COLLATION1", string.CompareOrdinal);
                connection1.Open();
                db = connection1.Handle !;
            }

            using var connection2 = new SqliteConnection(ConnectionString);
            connection2.Open();

            Assert.Same(db, connection2.Handle);
            var collations = connection2.ExecuteScalar <string>("SELECT group_concat(name) FROM pragma_collation_list;")
                             .Split(",");

            Assert.DoesNotContain("COLLATION1", collations);
        }
Example #24
0
        public void Write_works(
            byte[] expected,
            long initialPosition,
            byte[] buffer,
            int offset,
            int count)
        {
            using (var stream = CreateStream())
            {
                stream.Position = initialPosition;
                stream.Write(buffer, offset, count);

                Assert.Equal(initialPosition + count, stream.Position);
            }

            Assert.Equal(
                expected,
                _connection.ExecuteScalar <byte[]>(
                    $"SELECT {Column} FROM {Table} WHERE rowid = {Rowid}"));
        }
Example #25
0
        public void CreateCollation_works_with_state()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();
                var list = new List <string>();
                connection.CreateCollation(
                    "MY_NOCASE",
                    list,
                    (l, s1, s2) =>
                {
                    l.Add("Invoked");
                    return(string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase));
                });

                Assert.Equal(1L, connection.ExecuteScalar <long>("SELECT 'Νικοσ' = 'ΝΙΚΟΣ' COLLATE MY_NOCASE;"));
                var item = Assert.Single(list);
                Assert.Equal("Invoked", item);
            }
        }
Example #26
0
        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;"));
            }
        }
Example #27
0
        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.AreEqual(SQLITE_READONLY, ex.SqliteErrorCode);
            }
        }
Example #28
0
        public void ReadUncommitted_doesnt_bleed_across_connections()
        {
            sqlite3 db;

            using (var connection1 = new SqliteConnection(ConnectionString))
            {
                connection1.Open();
                db = connection1.Handle !;

                using (connection1.BeginTransaction(IsolationLevel.ReadUncommitted))
                {
                }
            }

            using var connection2 = new SqliteConnection(ConnectionString);
            connection2.Open();

            Assert.Same(db, connection2.Handle);
            var readUncommitted = connection2.ExecuteScalar <long>("PRAGMA read_uncommitted;");

            Assert.Equal(0L, readUncommitted);
        }
Example #29
0
        public void ReadUncommitted_allows_dirty_reads()
        {
            const string connectionString = "Data Source=read-uncommitted;Mode=Memory;Cache=Shared";

            using (var connection1 = new SqliteConnection(connectionString))
                using (var connection2 = new SqliteConnection(connectionString))
                {
                    connection1.Open();
                    connection2.Open();

                    connection1.ExecuteNonQuery("CREATE TABLE Data (Value); INSERT INTO Data VALUES (0);");

                    using (connection1.BeginTransaction())
                        using (connection2.BeginTransaction(IsolationLevel.ReadUncommitted))
                        {
                            connection1.ExecuteNonQuery("UPDATE Data SET Value = 1;");

                            var value = connection2.ExecuteScalar <long>("SELECT * FROM Data;");

                            Assert.Equal(1, value);
                        }
                }
        }
Example #30
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 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;"));
            }
        }
        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 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);
                }
            }
        }