github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/comment_on_column_test.go (about)

     1  // Copyright 2018 The Cockroach Authors.
     2  //
     3  // Use of this software is governed by the Business Source License
     4  // included in the file licenses/BSL.txt.
     5  //
     6  // As of the Change Date specified in that file, in accordance with
     7  // the Business Source License, use of this software will be governed
     8  // by the Apache License, Version 2.0, included in the file
     9  // licenses/APL.txt.
    10  
    11  package sql_test
    12  
    13  import (
    14  	"context"
    15  	gosql "database/sql"
    16  	"testing"
    17  
    18  	"github.com/cockroachdb/cockroach/pkg/sql/tests"
    19  	"github.com/cockroachdb/cockroach/pkg/testutils/serverutils"
    20  	"github.com/cockroachdb/cockroach/pkg/util/leaktest"
    21  	"github.com/cockroachdb/errors"
    22  )
    23  
    24  func TestCommentOnColumn(t *testing.T) {
    25  	defer leaktest.AfterTest(t)()
    26  
    27  	params, _ := tests.CreateTestServerParams()
    28  	s, db, _ := serverutils.StartServer(t, params)
    29  	defer s.Stopper().Stop(context.Background())
    30  
    31  	if _, err := db.Exec(`
    32  		CREATE DATABASE d;
    33  		SET DATABASE = d;
    34  		CREATE TABLE t (c1 INT, c2 INT, c3 INT);
    35  	`); err != nil {
    36  		t.Fatal(err)
    37  	}
    38  
    39  	testCases := []struct {
    40  		exec   string
    41  		query  string
    42  		expect gosql.NullString
    43  	}{
    44  		{
    45  			`COMMENT ON COLUMN t.c1 IS 'foo'`,
    46  			`SELECT col_description(attrelid, attnum) FROM pg_attribute WHERE attrelid = 't'::regclass AND attname = 'c1'`,
    47  			gosql.NullString{String: `foo`, Valid: true},
    48  		},
    49  		{
    50  			`TRUNCATE t`,
    51  			`SELECT col_description(attrelid, attnum) FROM pg_attribute WHERE attrelid = 't'::regclass AND attname = 'c1'`,
    52  			gosql.NullString{String: `foo`, Valid: true},
    53  		},
    54  		{
    55  			`ALTER TABLE t RENAME COLUMN c1 TO c1_1`,
    56  			`SELECT col_description(attrelid, attnum) FROM pg_attribute WHERE attrelid = 't'::regclass AND attname = 'c1_1'`,
    57  			gosql.NullString{String: `foo`, Valid: true},
    58  		},
    59  		{
    60  			`COMMENT ON COLUMN t.c1_1 IS NULL`,
    61  			`SELECT col_description(attrelid, attnum) FROM pg_attribute WHERE attrelid = 't'::regclass AND attname = 'c1_1'`,
    62  			gosql.NullString{Valid: false},
    63  		},
    64  		{
    65  			`COMMENT ON COLUMN t.c3 IS 'foo'`,
    66  			`SELECT col_description(attrelid, attnum) FROM pg_attribute WHERE attrelid = 't'::regclass AND attname = 'c3'`,
    67  			gosql.NullString{String: `foo`, Valid: true},
    68  		},
    69  		{
    70  			`ALTER TABLE t DROP COLUMN c2`,
    71  			`SELECT col_description(attrelid, attnum) FROM pg_attribute WHERE attrelid = 't'::regclass AND attname = 'c3'`,
    72  			gosql.NullString{String: `foo`, Valid: true},
    73  		},
    74  	}
    75  
    76  	for _, tc := range testCases {
    77  		if _, err := db.Exec(tc.exec); err != nil {
    78  			t.Fatal(err)
    79  		}
    80  
    81  		row := db.QueryRow(tc.query)
    82  		var comment gosql.NullString
    83  		if err := row.Scan(&comment); err != nil {
    84  			t.Fatal(err)
    85  		}
    86  		if tc.expect != comment {
    87  			t.Fatalf("expected comment %v, got %v", tc.expect, comment)
    88  		}
    89  	}
    90  }
    91  
    92  func TestCommentOnColumnTransaction(t *testing.T) {
    93  	defer leaktest.AfterTest(t)()
    94  
    95  	params, _ := tests.CreateTestServerParams()
    96  	s, db, _ := serverutils.StartServer(t, params)
    97  	defer s.Stopper().Stop(context.Background())
    98  
    99  	if _, err := db.Exec(`
   100  		CREATE DATABASE d;
   101  		SET DATABASE = d;
   102  		CREATE TABLE t (c INT);
   103  		BEGIN;
   104  		ALTER TABLE t ADD COLUMN x INT;
   105  		COMMENT ON COLUMN t.x IS 'foo';
   106  		COMMIT;
   107  	`); err != nil {
   108  		t.Fatal(err)
   109  	}
   110  }
   111  
   112  func TestCommentOnColumnWhenDropTable(t *testing.T) {
   113  	defer leaktest.AfterTest(t)()
   114  
   115  	params, _ := tests.CreateTestServerParams()
   116  	s, db, _ := serverutils.StartServer(t, params)
   117  	defer s.Stopper().Stop(context.Background())
   118  
   119  	if _, err := db.Exec(`
   120  		CREATE DATABASE d;
   121  		SET DATABASE = d;
   122  		CREATE TABLE t (c INT);
   123  	`); err != nil {
   124  		t.Fatal(err)
   125  	}
   126  
   127  	if _, err := db.Exec(`COMMENT ON COLUMN t.c IS 'foo'`); err != nil {
   128  		t.Fatal(err)
   129  	}
   130  
   131  	if _, err := db.Exec(`DROP TABLE t`); err != nil {
   132  		t.Fatal(err)
   133  	}
   134  
   135  	row := db.QueryRow(`SELECT comment FROM system.comments LIMIT 1`)
   136  	var comment string
   137  	err := row.Scan(&comment)
   138  	if !errors.Is(err, gosql.ErrNoRows) {
   139  		if err != nil {
   140  			t.Fatal(err)
   141  		}
   142  
   143  		t.Fatal("comment remain")
   144  	}
   145  }
   146  
   147  func TestCommentOnColumnWhenDropColumn(t *testing.T) {
   148  	defer leaktest.AfterTest(t)()
   149  
   150  	params, _ := tests.CreateTestServerParams()
   151  	s, db, _ := serverutils.StartServer(t, params)
   152  	defer s.Stopper().Stop(context.Background())
   153  
   154  	if _, err := db.Exec(`
   155  		CREATE DATABASE d;
   156  		SET DATABASE = d;
   157  		CREATE TABLE t (c INT);
   158  	`); err != nil {
   159  		t.Fatal(err)
   160  	}
   161  
   162  	if _, err := db.Exec(`COMMENT ON COLUMN t.c IS 'foo'`); err != nil {
   163  		t.Fatal(err)
   164  	}
   165  
   166  	if _, err := db.Exec(`ALTER TABLE t DROP COLUMN c`); err != nil {
   167  		t.Fatal(err)
   168  	}
   169  
   170  	row := db.QueryRow(`SELECT comment FROM system.comments LIMIT 1`)
   171  	var comment string
   172  	err := row.Scan(&comment)
   173  	if !errors.Is(err, gosql.ErrNoRows) {
   174  		if err != nil {
   175  			t.Fatal(err)
   176  		}
   177  
   178  		t.Fatal("comment remain")
   179  	}
   180  }