1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test table_info pragma", "[pragma]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER DEFAULT 1+3, j INTEGER)"));
13
14 // PRAGMA table_info(table) returns information on the table
15 result = con.Query("PRAGMA table_info('integers');");
16 REQUIRE_NO_FAIL(*result);
17 // cid
18 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
19 // name
20 REQUIRE(CHECK_COLUMN(result, 1, {"i", "j"}));
21 // types
22 REQUIRE(CHECK_COLUMN(result, 2, {"INTEGER", "INTEGER"}));
23 // NOT NULL
24 REQUIRE(CHECK_COLUMN(result, 3, {false, false}));
25 // DEFAULT VALUE
26 REQUIRE(CHECK_COLUMN(result, 4, {"1 + 3", Value()}));
27 // PRIMARY KEY
28 REQUIRE(CHECK_COLUMN(result, 5, {false, false}));
29
30 // table_info on view also works
31 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 AS SELECT 42::INTEGER AS a, 'hello' AS b"));
32 result = con.Query("PRAGMA table_info('v1')");
33 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
34 REQUIRE(CHECK_COLUMN(result, 1, {"a", "b"}));
35 REQUIRE(CHECK_COLUMN(result, 2, {"INTEGER", "VARCHAR"}));
36 REQUIRE(CHECK_COLUMN(result, 3, {false, false}));
37 REQUIRE(CHECK_COLUMN(result, 4, {Value(), Value()}));
38 REQUIRE(CHECK_COLUMN(result, 5, {false, false}));
39
40 // view with explicit aliases
41 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v2(c) AS SELECT 42::INTEGER AS a, 'hello' AS b"));
42 result = con.Query("PRAGMA table_info('v2')");
43 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
44 REQUIRE(CHECK_COLUMN(result, 1, {"c", "b"}));
45 REQUIRE(CHECK_COLUMN(result, 2, {"INTEGER", "VARCHAR"}));
46 REQUIRE(CHECK_COLUMN(result, 3, {false, false}));
47 REQUIRE(CHECK_COLUMN(result, 4, {Value(), Value()}));
48 REQUIRE(CHECK_COLUMN(result, 5, {false, false}));
49
50 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v3(c, d) AS SELECT DATE '1992-01-01', 'hello' AS b"));
51 result = con.Query("PRAGMA table_info('v3')");
52 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
53 REQUIRE(CHECK_COLUMN(result, 1, {"c", "d"}));
54 REQUIRE(CHECK_COLUMN(result, 2, {"DATE", "VARCHAR"}));
55 REQUIRE(CHECK_COLUMN(result, 3, {false, false}));
56 REQUIRE(CHECK_COLUMN(result, 4, {Value(), Value()}));
57 REQUIRE(CHECK_COLUMN(result, 5, {false, false}));
58
59 // test table info with other schemas
60 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA test"));
61 REQUIRE_NO_FAIL(con.Query("CREATE VIEW test.v1 AS SELECT 42::INTEGER AS a, 'hello' AS b"));
62 result = con.Query("PRAGMA table_info('test.v1')");
63 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
64 REQUIRE(CHECK_COLUMN(result, 1, {"a", "b"}));
65 REQUIRE(CHECK_COLUMN(result, 2, {"INTEGER", "VARCHAR"}));
66 REQUIRE(CHECK_COLUMN(result, 3, {false, false}));
67 REQUIRE(CHECK_COLUMN(result, 4, {Value(), Value()}));
68 REQUIRE(CHECK_COLUMN(result, 5, {false, false}));
69
70 REQUIRE_FAIL(con.Query("PRAGMA table_info('nonexistant_table');"));
71}
72