1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
|
# name: test/sql/catalog/comment_on_extended.test
# description: Test COMMENT ON to add comment on database
# group: [catalog]
### Create some test data
statement ok
ATTACH '__TEST_DIR__/comment_on_extended_1.db' AS db1
statement ok
ATTACH '__TEST_DIR__/comment_on_extended_2.db' AS db2
statement ok
CREATE SCHEMA db1.s1;
statement ok
CREATE SCHEMA db2.s2;
statement ok
CREATE TABLE db1.s1.t1 AS SELECT 1 as c1
statement ok
CREATE TABLE db2.s2.t2 AS SELECT 2 as c2
# some extra tables to index on (since we can not comment on tables with dependencies)
statement ok
CREATE TABLE db1.s1.t3 AS SELECT 3 as c3
statement ok
CREATE TABLE db2.s2.t4 AS SELECT 4 as c4
statement ok
CREATE INDEX test_index ON db1.s1.t3 using art(c3)
statement ok
CREATE INDEX test_index ON db2.s2.t4 using art(c4)
### Confirm we have no comments yet
query I
SELECT count(*) from duckdb_tables() where comment IS NOT NULL;
----
0
query I
SELECT count(*) from duckdb_columns() where comment IS NOT NULL;
----
0
### Tables with fully qualified names
statement ok
COMMENT ON TABLE db1.s1.t1 IS 'very gezellige table 1'
query I
select comment from duckdb_tables() where table_name='t1';
----
very gezellige table 1
# just table
statement ok
USE db2.s2
statement ok
COMMENT ON TABLE t2 IS 'very gezellige table 2'
query I
select comment from duckdb_tables() where table_name='t2';
----
very gezellige table 2
# db.table
statement ok
COMMENT ON TABLE db2.t2 IS 'another very gezellige table 2'
query I
select comment from duckdb_tables() where table_name='t2';
----
another very gezellige table 2
# schema.table
statement ok
COMMENT ON TABLE s2.t2 IS 'yet another very gezellige table 2'
query I
select comment from duckdb_tables() where table_name='t2';
----
yet another very gezellige table 2
### Index with fully qualified name
statement ok
COMMENT ON INDEX db1.s1.test_index IS 'very gezellige index 1'
query I
select comment from duckdb_indexes() where index_name='test_index' and database_name='db1';
----
very gezellige index 1
# db.index
statement ok
COMMENT ON INDEX db2.test_index IS 'very gezellige index 2'
query III
select database_name, schema_name, comment from duckdb_indexes() where index_name='test_index' order by comment;
----
db1 s1 very gezellige index 1
db2 s2 very gezellige index 2
### Fully qualified column
statement error
COMMENT ON COLUMN col1 IS 'no bueno'
----
Parser Error: Invalid column reference: 'col1'
statement error
COMMENT ON COLUMN galaxy.db.schema.table.col1 IS 'no bueno'
----
Parser Error: Invalid column reference: 'galaxy.db."schema"."table".col1', too many dots
statement error
COMMENT ON COLUMN table_blablabla.col1 IS 'bla'
----
Catalog Error: Table with name table_blablabla does not exist!
statement ok
COMMENT ON COLUMN db1.s1.t1.c1 IS 'very gezellige column 1'
query IIII
select database_name, schema_name, table_name, comment from duckdb_columns() where column_name='c1' order by comment;
----
db1 s1 t1 very gezellige column 1
# Using db.tbl.col
statement ok
COMMENT ON COLUMN db2.t2.c2 IS 'very gezellige column 2'
query IIII
select database_name, schema_name, table_name, comment from duckdb_columns() where column_name='c2' order by comment;
----
db2 s2 t2 very gezellige column 2
# Using schema.tbl.col
statement ok
COMMENT ON COLUMN s2.t2.c2 IS 'another very gezellige column 2'
query IIII
select database_name, schema_name, table_name, comment from duckdb_columns() where column_name='c2' order by comment;
----
db2 s2 t2 another very gezellige column 2
# Using tbl.col
statement ok
COMMENT ON COLUMN t2.c2 IS 'yet another very gezellige column 2'
query IIII
select database_name, schema_name, table_name, comment from duckdb_columns() where column_name='c2' order by comment;
----
db2 s2 t2 yet another very gezellige column 2
|
