Materialize/test/sqllogictest/comment.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

# Start from a pristine server
reset-server

statement ok
CREATE TABLE a ( x int8, y text, z jsonb );

query T
SELECT obj_description((SELECT oid FROM mz_tables WHERE name = 'a'), 'pg_class')
----
NULL

query TTT
SHOW OBJECTS
----
a  table  (empty)

statement ok
COMMENT ON TABLE a IS 'foo_table';

query TTT
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
table  NULL  foo_table

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----
0  foo_table

query TTT
SHOW OBJECTS
----
a  table  foo_table

query TT
SELECT obj_description((SELECT oid FROM mz_tables WHERE name = 'a'), 'pg_class'),
       obj_description((SELECT oid FROM mz_tables WHERE name = 'a'), 'notexist')
----
foo_table  NULL

statement ok
COMMENT ON COLUMN a.y IS 'load_bearing';

query TTT rowsort
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
table  NULL  foo_table
table  2  load_bearing

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----
0  foo_table
2  load_bearing

query T
SELECT col_description((SELECT oid FROM mz_tables WHERE name = 'a'), 2)
----
load_bearing

query T
SELECT col_description((SELECT oid FROM mz_tables WHERE name = 'a'), 1)
----
NULL

query TT
SHOW TABLES;
----
a foo_table

query TT rowsort
SELECT name, comment FROM (SHOW COLUMNS FROM a);
----
x (empty)
z (empty)
y load_bearing

statement ok
CREATE TABLE b ( ts timestamptz );

statement ok
COMMENT ON COLUMN b.ts IS 'utc_timestamp';

query TTT rowsort
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
table  NULL  foo_table
table  2  load_bearing
table  1  utc_timestamp

query IT rowsort
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----
0  foo_table
2  load_bearing
1  utc_timestamp

statement ok
COMMENT ON TABLE b IS 'foo_table';

query TT
SHOW TABLES;
----
a foo_table
b foo_table

statement ok
DROP TABLE a;

query TTT rowsort
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
table  NULL foo_table
table  1  utc_timestamp

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----
0  foo_table
1  utc_timestamp

statement ok
COMMENT ON TABLE b IS NULL

query TT
SHOW TABLES;
----
b (empty)

statement ok
COMMENT ON COLUMN b.ts IS NULL

query TTT
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----

statement error unknown catalog item 'c'
COMMENT ON TABLE c IS 'does_not_exist';

statement error column "b.does_not_exist" does not exist
COMMENT ON COLUMN b.does_not_exist IS 'foo';

statement ok
CREATE INDEX b_idx ON b (ts);

statement ok
COMMENT ON INDEX b_idx IS 'speed_up';

query TT
SELECT name, comment FROM (SHOW INDEXES);
----
b_idx speed_up

statement ok
CREATE VIEW c (col_1, col_2) AS VALUES ('a', 'b');

statement ok
COMMENT ON VIEW c IS 'this_is_a_view';

query TTT
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
index NULL  speed_up
view  NULL  this_is_a_view

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----
0  speed_up
0  this_is_a_view

query T
SELECT obj_description((SELECT oid FROM mz_indexes WHERE name = 'b_idx'), 'pg_class')
----
speed_up

statement ok
DROP TABLE b CASCADE;

statement ok
COMMENT ON COLUMN c.col_1 IS 'this_works';

query TT
SELECT name, comment FROM (SHOW VIEWS);
----
c this_is_a_view

query TT rowsort
SELECT name, comment FROM (SHOW COLUMNS FROM c);
----
col_2 (empty)
col_1 this_works

query TTT
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
view  1  this_works
view  NULL  this_is_a_view

query IT rowsort
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----
1  this_works
0  this_is_a_view

statement ok
CREATE MATERIALIZED VIEW mv ( x ) AS SELECT 1

statement ok
COMMENT ON COLUMN mv.x IS 'comment_mat_view_col';

statement ok
COMMENT ON MATERIALIZED VIEW mv IS 'mat_foo';

query TT
SELECT name, comment FROM (SHOW MATERIALIZED VIEWS);
----
mv mat_foo

query TT
SELECT name, comment FROM (SHOW COLUMNS FROM mv);
----
x comment_mat_view_col

query TTT rowsort
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
view  1  this_works
view  NULL  this_is_a_view
materialized-view  NULL  mat_foo
materialized-view  1  comment_mat_view_col

query IT rowsort
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----
0  mat_foo
1  this_works
0  this_is_a_view
1  comment_mat_view_col

query TTT rowsort
SHOW OBJECTS
----
c  view  this_is_a_view
mv  materialized-view  mat_foo

query T
SELECT obj_description((SELECT oid FROM mz_views WHERE name = 'c'), 'pg_class')
----
this_is_a_view

query T
SELECT col_description((SELECT oid FROM mz_views WHERE name = 'c'), 1)
----
this_works

query TTTT
SELECT obj_description((SELECT oid FROM mz_views WHERE name = 'c'), 'pg_class'),
       col_description((SELECT oid FROM mz_views WHERE name = 'c'), 1),
       obj_description(
        (SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 'pg_class'),
       col_description((SELECT oid FROM mz_materialized_views WHERE name = 'mv'), 1)
----
this_is_a_view  this_works  mat_foo  comment_mat_view_col

statement ok
DROP VIEW c;

statement ok
DROP MATERIALIZED VIEW mv;

query TTT
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----

statement ok
CREATE CLUSTER comment_cluster REPLICAS (r1 (SIZE '1'), r2 (SIZE '1'));

statement ok
COMMENT ON CLUSTER comment_cluster IS 'careful_now';

query TT rowsort
SELECT name, comment FROM (SHOW CLUSTERS);
----
mz_probe  (empty)
mz_system  (empty)
mz_support  (empty)
quickstart  (empty)
mz_analytics  (empty)
mz_catalog_server  (empty)
comment_cluster  careful_now

statement ok
COMMENT ON CLUSTER REPLICA comment_cluster.r2 IS 'second_replicator';

query TTT
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
cluster NULL careful_now
cluster-replica NULL second_replicator

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----

statement ok
DROP CLUSTER REPLICA comment_cluster.r2;

statement ok
CREATE SOURCE my_webhook IN CLUSTER comment_cluster FROM WEBHOOK BODY FORMAT TEXT;

statement ok
COMMENT ON SOURCE my_webhook IS 'all_the_data';

statement ok
COMMENT ON COLUMN my_webhook.body IS 'json_blob';

query TTT rowsort
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
source  1  json_blob
source  NULL  all_the_data
cluster  NULL  careful_now

query TT
SELECT obj_description((SELECT oid FROM mz_sources WHERE name = 'my_webhook'), 'pg_class'),
       col_description((SELECT oid FROM mz_sources WHERE name = 'my_webhook'), 1)
----
all_the_data  json_blob

query IT rowsort
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----
1  json_blob
0  all_the_data

query TT
SELECT name, comment FROM (SHOW SOURCES);
----
my_webhook  all_the_data

query TTT
SHOW OBJECTS
----
my_webhook  source  all_the_data

statement ok
CREATE TYPE int4_list AS LIST (ELEMENT TYPE = int4);

statement ok
COMMENT ON TYPE int4_list IS 'supercool_list';

statement ok
CREATE TYPE custom_type AS (x integer, y int4_list);

statement ok
CREATE TYPE custom_map_type AS MAP (KEY TYPE = text, VALUE TYPE = custom_type)

statement ok
COMMENT ON TYPE custom_map_type IS 'custom_map_type_comment';

statement error cannot be depended upon
COMMENT ON COLUMN custom_map_type.key IS 'comment_on_key';

query TT rowsort
SHOW TYPES;
----
custom_map_type  custom_map_type_comment
custom_type  (empty)
int4_list  supercool_list

statement ok
CREATE TYPE custom_list_type AS LIST (ELEMENT TYPE = custom_type)

statement ok
COMMENT ON TYPE custom_list_type IS 'custom_list_type_comment';

statement error cannot be depended upon
COMMENT ON COLUMN custom_list_type.element IS 'comment_on_element';

statement error cannot be depended upon
COMMENT ON COLUMN custom_map_type.key IS 'comment_on_key';

statement ok
COMMENT ON TYPE custom_type IS 'custom_type_comment';

query TTT rowsort
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
source   1      json_blob
type     NULL   supercool_list
source   NULL   all_the_data
cluster  NULL   careful_now
type     NULL   custom_type_comment
type     NULL   custom_map_type_comment
type     NULL   custom_list_type_comment

query TTT
SHOW OBJECTS
----
custom_list_type  type  custom_list_type_comment
custom_map_type  type  custom_map_type_comment
custom_type  type  custom_type_comment
int4_list  type  supercool_list
my_webhook  source  all_the_data

statement ok
DROP TYPE custom_map_type;

statement ok
DROP TYPE custom_list_type;

statement ok
COMMENT ON COLUMN custom_type.x IS 'custom_type_x_comment';

statement ok
COMMENT ON COLUMN custom_type.y IS 'custom_type_y_comment';

query TTT rowsort
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
source   1      json_blob
type     NULL   supercool_list
source   NULL   all_the_data
cluster  NULL   careful_now
type     NULL   custom_type_comment
type     1      custom_type_x_comment
type     2      custom_type_y_comment

query IT rowsort
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----
1 json_blob
0 all_the_data
0 supercool_list
0 custom_type_comment
1 custom_type_x_comment
2 custom_type_y_comment

query TTT rowsort
SHOW OBJECTS
----
custom_type  type  custom_type_comment
int4_list  type  supercool_list
my_webhook  source  all_the_data

query TT
SELECT obj_description((SELECT oid FROM mz_types WHERE name = 'custom_type'), 'pg_type'),
       col_description((SELECT oid FROM mz_types WHERE name = 'custom_type'), 1)
----
custom_type_comment  NULL

statement ok
DROP CLUSTER comment_cluster CASCADE;

statement ok
CREATE SECRET my_secret AS 'foobar';

statement ok
COMMENT ON SECRET my_secret IS 'supersecret';

query TTT rowsort
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
secret NULL supersecret
type NULL supercool_list
type     NULL   custom_type_comment
type     1      custom_type_x_comment
type     2      custom_type_y_comment

query IT rowsort
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----
0  supercool_list
0 custom_type_comment
1 custom_type_x_comment
2 custom_type_y_comment

query T
SELECT obj_description((SELECT oid FROM mz_secrets WHERE name = 'my_secret'), 'pg_class')
----
NULL

statement ok
CREATE DATABASE comment_on_db;

statement ok
CREATE SCHEMA comment_on_schema;

statement ok
COMMENT ON DATABASE comment_on_db IS 'this_is_my_db';

statement ok
COMMENT ON SCHEMA comment_on_schema IS 'this_is_my_schema';

query TTT
SHOW OBJECTS
----
custom_type  type  custom_type_comment
int4_list  type  supercool_list
my_secret  secret  supersecret

statement ok
DROP SECRET my_secret;

statement ok
DROP TYPE custom_type;

statement ok
DROP TYPE int4_list;

query TTT
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
database NULL this_is_my_db
schema NULL this_is_my_schema

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----
0  this_is_my_schema

query TT
SELECT obj_description(
        (SELECT oid FROM mz_databases WHERE name = 'comment_on_db'), 'pg_class'),
       obj_description(
        (SELECT oid FROM mz_schemas WHERE name = 'comment_on_schema'), 'pg_namespace')
----
NULL  this_is_my_schema

statement ok
DROP DATABASE comment_on_db;

statement ok
DROP SCHEMA comment_on_schema;

# Test RBAC.

statement ok
CREATE ROLE student;

statement ok
COMMENT ON ROLE student IS 'limited_role';

query TTT
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
role NULL limited_role

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----

statement ok
CREATE TABLE foo ( x int8 );

simple conn=student,user=student
COMMENT ON TABLE foo IS 'comment_from_student';
----
db error: ERROR: must be owner of TABLE materialize.public.foo

statement ok
CREATE ROLE teacher;

simple conn=mz_system,user=mz_system
GRANT CREATEROLE ON SYSTEM TO student;
----
COMPLETE 0

simple conn=student,user=student
COMMENT ON ROLE teacher IS 'foo';
----
COMPLETE 0

query TTT rowsort
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
role NULL foo
role NULL limited_role

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----

simple conn=mz_system,user=mz_system
REVOKE CREATEROLE ON SYSTEM FROM student;
----
COMPLETE 0

# To comment on a Role you must have the CREATEROLE privilege.
simple conn=student,user=student
COMMENT ON ROLE teacher IS 'updated_teacher_comment';
----
db error: ERROR: permission denied for SYSTEM
DETAIL: The 'student' role needs CREATEROLE privileges on SYSTEM

statement ok
DROP ROLE student;

statement ok
DROP ROLE teacher;

query TTT
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----

statement error must be owner of DATABASE materialize
COMMENT ON DATABASE materialize IS 'main_db';

statement error must be owner of SCHEMA materialize.public
COMMENT ON SCHEMA public IS 'everyone_has_access';

simple conn=mz_system,user=mz_system
COMMENT ON DATABASE materialize IS 'main_db';
----
COMPLETE 0

query TTT
SELECT object_type, object_sub_id, comment FROM mz_internal.mz_comments WHERE id NOT LIKE 's%';
----
database  NULL  main_db

query IT
SELECT objsubid, description FROM pg_description WHERE objoid >= 20000;
----