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("Create and drop a view over different runs", "[storage]") {
9 unique_ptr<QueryResult> result;
10 auto storage_database = TestCreatePath("storage_test");
11 auto config = GetTestConfig();
12
13 // make sure the database does not exist
14 DeleteDatabase(storage_database);
15 {
16 // create a database and insert values
17 DuckDB db(storage_database, config.get());
18 Connection con(db);
19 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA test;"));
20 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test.t (a INTEGER, b INTEGER);"));
21 REQUIRE_NO_FAIL(con.Query("CREATE VIEW test.v AS SELECT * FROM test.t;"));
22 result = con.Query("PRAGMA table_info('test.v')");
23 REQUIRE(CHECK_COLUMN(result, 1, {"a", "b"}));
24 REQUIRE(CHECK_COLUMN(result, 2, {"INTEGER", "INTEGER"}));
25
26 // drop the table that "test.v" is based on
27 REQUIRE_NO_FAIL(con.Query("DROP TABLE test.t"));
28
29 // we can still query the types and column names
30 result = con.Query("PRAGMA table_info('test.v')");
31 REQUIRE(CHECK_COLUMN(result, 1, {"a", "b"}));
32 REQUIRE(CHECK_COLUMN(result, 2, {"INTEGER", "INTEGER"}));
33
34 // but querying the view fails!
35 REQUIRE_FAIL(con.Query("SELECT * FROM test.v"));
36 }
37 // reload the database from disk
38 for (idx_t i = 0; i < 3; i++) {
39 DuckDB db(storage_database, config.get());
40 Connection con(db);
41 // the table is not there yet, but we can still check the view types
42 result = con.Query("PRAGMA table_info('test.v')");
43 REQUIRE(CHECK_COLUMN(result, 1, {"a", "b"}));
44 REQUIRE(CHECK_COLUMN(result, 2, {"INTEGER", "INTEGER"}));
45 REQUIRE_FAIL(con.Query("SELECT * FROM test.v"));
46
47 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test.t (a INTEGER, b INTEGER);"));
48 REQUIRE_NO_FAIL(con.Query("SELECT * FROM test.t"));
49 // after creating the table we can query the view again
50 REQUIRE_NO_FAIL(con.Query("SELECT * FROM test.v"));
51 result = con.Query("PRAGMA table_info('test.v')");
52 REQUIRE(CHECK_COLUMN(result, 1, {"a", "b"}));
53 REQUIRE(CHECK_COLUMN(result, 2, {"INTEGER", "INTEGER"}));
54 REQUIRE_NO_FAIL(con.Query("DROP TABLE test.t"));
55 }
56 DeleteDatabase(storage_database);
57}
58
59TEST_CASE("Test views with explicit column aliases", "[storage]") {
60 unique_ptr<QueryResult> result;
61 auto storage_database = TestCreatePath("storage_test");
62 auto config = GetTestConfig();
63
64 // make sure the database does not exist
65 DeleteDatabase(storage_database);
66 {
67 // create a database and insert values
68 DuckDB db(storage_database, config.get());
69 Connection con(db);
70 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA test;"));
71 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test.t (a INTEGER, b INTEGER);"));
72 REQUIRE_NO_FAIL(con.Query("CREATE VIEW test.v (b,c) AS SELECT * FROM test.t;"));
73 REQUIRE_NO_FAIL(con.Query("SELECT * FROM test.v"));
74 REQUIRE_NO_FAIL(con.Query("DROP TABLE test.t"));
75 }
76 // reload the database from disk
77 {
78 DuckDB db(storage_database, config.get());
79 Connection con(db);
80 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test.t (a INTEGER, b INTEGER);"));
81 REQUIRE_NO_FAIL(con.Query("SELECT * FROM test.t"));
82 REQUIRE_NO_FAIL(con.Query("SELECT b,c FROM test.v"));
83 REQUIRE_NO_FAIL(con.Query("DROP TABLE test.t"));
84 }
85 // reload again
86 {
87 DuckDB db(storage_database, config.get());
88 Connection con(db);
89 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test.t (a INTEGER, b INTEGER);"));
90 REQUIRE_NO_FAIL(con.Query("SELECT * FROM test.t"));
91 REQUIRE_NO_FAIL(con.Query("SELECT b,c FROM test.v"));
92 REQUIRE_NO_FAIL(con.Query("DROP VIEW test.v"));
93 }
94 {
95 DuckDB db(storage_database, config.get());
96 Connection con(db);
97 REQUIRE_NO_FAIL(con.Query("SELECT * FROM test.t"));
98 REQUIRE_FAIL(con.Query("SELECT b,c FROM test.v"));
99 }
100 DeleteDatabase(storage_database);
101}
102