1#include "catch.hpp"
2#include "duckdb/common/file_system.hpp"
3#include "test_helpers.hpp"
4
5using namespace duckdb;
6using namespace std;
7
8TEST_CASE("Test connection using a read only database", "[readonly]") {
9 auto dbdir = TestCreatePath("read_only_test");
10 unique_ptr<DuckDB> db, db2;
11 unique_ptr<Connection> con;
12 // make sure the database does not exist
13 DeleteDatabase(dbdir);
14
15 DBConfig readonly_config;
16 readonly_config.use_temporary_directory = false;
17 readonly_config.access_mode = AccessMode::READ_ONLY;
18
19 // cannot create read-only memory database
20 REQUIRE_THROWS(db = make_unique<DuckDB>(nullptr, &readonly_config));
21 // cannot create a read-only database in a new directory
22 REQUIRE_THROWS(db = make_unique<DuckDB>(dbdir, &readonly_config));
23
24 // create the database file and initialize it with data
25 db = make_unique<DuckDB>(dbdir);
26 con = make_unique<Connection>(*db);
27 REQUIRE_NO_FAIL(con->Query("CREATE TABLE integers(i INTEGER)"));
28 REQUIRE_NO_FAIL(con->Query("INSERT INTO integers VALUES (1), (2), (3), (4), (5)"));
29 con.reset();
30 db.reset();
31
32 // now connect in read-only mode
33 REQUIRE_NOTHROW(db = make_unique<DuckDB>(dbdir, &readonly_config));
34 con = make_unique<Connection>(*db);
35
36 // we can query the database
37 auto result = con->Query("SELECT * FROM integers ORDER BY i");
38 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3, 4, 5}));
39 // however, we can't perform DDL statements
40 REQUIRE_FAIL(con->Query("CREATE TABLE integers2(i INTEGER)"));
41 REQUIRE_FAIL(con->Query("ALTER TABLE integers RENAME COLUMN i TO k"));
42 REQUIRE_FAIL(con->Query("DROP TABLE integers"));
43 REQUIRE_FAIL(con->Query("CREATE SEQUENCE seq"));
44 REQUIRE_FAIL(con->Query("CREATE VIEW v1 AS SELECT * FROM integers"));
45 // neither can we insert/update/delete data
46 REQUIRE_FAIL(con->Query("INSERT INTO integers VALUES (3)"));
47 REQUIRE_FAIL(con->Query("UPDATE integers SET i=5"));
48 REQUIRE_FAIL(con->Query("DELETE FROM integers"));
49 // we can run explain queries
50 REQUIRE_NO_FAIL(con->Query("EXPLAIN SELECT * FROM integers"));
51 // and run prepared statements
52 REQUIRE_NO_FAIL(con->Query("PREPARE v1 AS SELECT * FROM integers"));
53 REQUIRE_NO_FAIL(con->Query("EXECUTE v1"));
54 REQUIRE_NO_FAIL(con->Query("DEALLOCATE v1"));
55 // we can also prepare a DDL/update statement
56 REQUIRE_NO_FAIL(con->Query("PREPARE v1 AS INSERT INTO integers VALUES ($1)"));
57 // however, executing it fails then!
58 REQUIRE_FAIL(con->Query("EXECUTE v1(3)"));
59
60 // we can create, alter and query temporary tables however
61 REQUIRE_NO_FAIL(con->Query("CREATE TEMPORARY TABLE integers2(i INTEGER)"));
62 REQUIRE_NO_FAIL(con->Query("INSERT INTO integers2 VALUES (1), (2), (3), (4), (5)"));
63 REQUIRE_NO_FAIL(con->Query("UPDATE integers2 SET i=i+1"));
64 result = con->Query("DELETE FROM integers2 WHERE i=3");
65 REQUIRE(CHECK_COLUMN(result, 0, {1}));
66
67 REQUIRE_NO_FAIL(con->Query("ALTER TABLE integers2 RENAME COLUMN i TO k"));
68 result = con->Query("SELECT k FROM integers2 ORDER BY 1");
69 REQUIRE(CHECK_COLUMN(result, 0, {2, 4, 5, 6}));
70 REQUIRE_NO_FAIL(con->Query("DROP TABLE integers2"));
71
72 // also temporary views and sequences
73 REQUIRE_NO_FAIL(con->Query("CREATE TEMPORARY SEQUENCE seq"));
74 result = con->Query("SELECT nextval('seq')");
75 REQUIRE(CHECK_COLUMN(result, 0, {1}));
76 REQUIRE_NO_FAIL(con->Query("DROP SEQUENCE seq"));
77
78 REQUIRE_NO_FAIL(con->Query("CREATE TEMPORARY VIEW v1 AS SELECT 42"));
79 result = con->Query("SELECT * FROM v1");
80 REQUIRE(CHECK_COLUMN(result, 0, {42}));
81 REQUIRE_NO_FAIL(con->Query("DROP VIEW v1"));
82
83 con.reset();
84 db.reset();
85 // FIXME: these tests currently don't work as we don't do any locking of the database directory
86 // this should be fixed with the new storage
87 // we can connect multiple read only databases to the same dbdir
88 // REQUIRE_NOTHROW(db = make_unique<DuckDB>(dbdir, true));
89 // REQUIRE_NOTHROW(db2 = make_unique<DuckDB>(dbdir, true));
90 // db.reset();
91 // db2.reset();
92
93 // // however, if there is read-only database, we can't connect a read-write database
94 // REQUIRE_NOTHROW(db = make_unique<DuckDB>(dbdir, true));
95 // REQUIRE_THROWS(db2 = make_unique<DuckDB>(dbdir));
96 // db.reset();
97 // db2.reset();
98
99 // // if we add a read-write database first, we can't add a reading database afterwards either
100 // REQUIRE_NOTHROW(db = make_unique<DuckDB>(dbdir));
101 // REQUIRE_THROWS(db2 = make_unique<DuckDB>(dbdir, true));
102 // db.reset();
103 // db2.reset();
104 DeleteDatabase(dbdir);
105}
106