sql: Complete the information_schema by adding empty table definitions

[nvanbenschoten]

#8119 introduced the framework for creating virtual schemas, and specifically, the information_schema. Subsequently, a group PRs added what was deemed to be the most immediately critical set of tables. However, the specification for the information_schema is extensive, and there are a number of other tables which can be added with relative ease. The set of all information_schema tables is:

• character_sets
• collations
• collation_character_set_applicability
• columns
• column_privileges
• constraint_column_usage
• engines
• events
• files
• global_status
• session_statuss
• global_variables
• session_variables
• key_column_usage
• optimizer_trace
• parameters
• partitions
• plugins
• processlist
• profiling
• referential_constraints
• routines
• schemata
• schema_privileges
• sequences
• statistics
• tables
• tablespaces
• table_constraints
• table_privileges
• triggers
• user_privileges
• views

The process for adding a new information_schema table is:

1. create a new virtualSchemaTable variable with a table schema definition
2. construct a population function implementation to populate this schema on demand
3. add the variable to the information_schema's table list
4. add necessary testing to testdata/information_schema

Helpful sources:

• MySQL information_schema docs
• PostgreSQL information_schema docs

[yznming]

@nvanbenschoten I'm going to create the information_schema.statistics as follow, what do you think?

CREATE TABLE information_schema.statistics (
    TABLE_CATALOG STRING NOT NULL DEFAULT '',
    TABLE_SCHEMA STRING NOT NULL DEFAULT '',
    TABLE_NAME STRING NOT NULL DEFAULT '',
    NON_UNIQUE BOOL NOT NULL DEFAULT FALSE,
    INDEX_SCHEMA STRING NOT NULL DEFAULT '',
    INDEX_NAME STRING NOT NULL DEFAULT '',
    SEQ_IN_INDEX INT NOT NULL DEFAULT 0,
    COLUMN_NAME STRING NOT NULL DEFAULT '',
    "COLLATION" STRING NOT NULL DEFAULT '',
    CARDINALITY INT NOT NULL DEFAULT 0,
    DIRECTION STRING NOT NULL DEFAULT '',
    STORING BOOL NOT NULL DEFAULT FALSE 
);

[nvanbenschoten]

That sounds reasonable @yananzhi, thanks!

[szpony]

@nvanbenschoten I'm planning to create information_schema.views as follows :

CREATE TABLE information_schema.views (
    TABLE_CATALOG STRING NOT NULL DEFAULT '',
    TABLE_SCHEMA STRING NOT NULL DEFAULT '',
    TABLE_NAME STRING NOT NULL DEFAULT '',
    VIEW_DEFINITION STRING NOT NULL DEFAULT '',
    CHECK_OPTION STRING NOT NULL DEFAULT '',
    IS_UPDATABLE BOOL NOT NULL DEFAULT FALSE,
    IS_INSERTABLE_INTO BOOL NOT NULL DEFAULT FALSE,
    IS_TRIGGER_UPDATABLE BOOL NOT NULL DEFAULT FALSE,
    IS_TRIGGER_DELETABLE BOOL NOT NULL DEFAULT FALSE,
    IS_TRIGGER_INSERTABLE_INTO BOOL NOT NULL DEFAULT FALSE
);

It refers to the postgresql document: https://www.postgresql.org/docs/9.5/static/infoschema-views.html
Look forward to your advice. Thanks.

[nvanbenschoten]

@mtMabo yep that looks good. Thanks!

[yangliang9004]

@nvanbenschoten I'm planning to create information_schema.user_privileges as follows :

CREATE  TABLE information_schema .user_privileges (
   GRANTEE STRING  NOT NULL DEFAULT '',
   TABLE_CATALOG  STRING NOT  NULL  DEFAULT '',
   PRIVILEGE_TYPE  STRING NOT NULL DEFAULT '',
   IS_GRANTABLE  BOOL NOT NULL DEFAULT FALSE
);

It refers to the mysql document: https://dev.mysql.com/doc/refman/5.7/en/user-privileges-table.html
Look forward to your advice. Thanks.

[jordanlewis]

@yangliang9004, LGTM

[radutopala]

@benesch https://github.com/radutopala/cockroach-app/issues .. found these so far, with PHP/Symfony/Doctrine.

[sum12]

@nvanbenschoten I wanted to pick up column_privileges, like this

CREATE TABLE information_schema.column_privilege (
	GRANTER STRING NOT NULL DEFAULT '',
	GRANTEE STRING NOT NULL DEFAULT '',
	TABLE_CATALOG STRING NOT NULL DEFAULT '',
	TABLE_SCHEMA STRING NOT NULL DEFAULT '',
	TABLE_NAME STRING NOT NULL DEFAULT '',
	COLUMN_NAME STRING NOT NULL DEFAULT '',
	PRIVILEGE_TYPE STRING NOT NULL DEFAULT '',
	IS_GRANTABLE BOOL NOT NULL DEFAULT FALSE
);

But the column_descriptor in ColumnDescriptor doesnot have any PriviliegeDescriptor.

Any pointers for me ?

[benesch]

CockroachDB doesn't have separate column privileges like some other databases do. Postgres before 8.4 had the same limitation, so I'd follow their old behavior here:

In PostgreSQL, you can only grant privileges on entire tables, not individual columns. Therefore, this view contains the same information as table_privileges, just represented through one row for each column in each appropriate table, but it only covers privilege types where column granularity is possible: SELECT, INSERT, UPDATE, REFERENCES.

— https://www.postgresql.org/docs/8.3/static/infoschema-column-privileges.html

…

On Fri, Dec 22, 2017 at 11:32 AM, Sumit ***@***.***> wrote: @nvanbenschoten <https://github.com/nvanbenschoten> I wanted to pick up column_privileges, like this CREATE TABLE information_schema.column_privilege ( GRANTER STRING NOT NULL DEFAULT '', GRANTEE STRING NOT NULL DEFAULT '', TABLE_CATALOG STRING NOT NULL DEFAULT '', TABLE_SCHEMA STRING NOT NULL DEFAULT '', TABLE_NAME STRING NOT NULL DEFAULT '', COLUMN_NAME STRING NOT NULL DEFAULT '', PRIVILEGE_TYPE STRING NOT NULL DEFAULT '', IS_GRANTABLE BOOL NOT NULL DEFAULT FALSE ); But the column_descriptor in ColumnDescriptor <https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/sqlbase/structured.pb.go#L447> doesnot have any PriviliegeDescriptor <https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/sqlbase/privilege.pb.go#L31> . Any pointers for me ? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#8675 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA15IM4i4ncCbv7Y2znmj1V0J3rmEY6pks5tC9mbgaJpZM4Jn6VP> .

[nvanbenschoten]

@sum12 thanks for the interest! @benesch is exactly right that we should handle this the same way Postgres did before they added column level privileges. Let me know if you plan to pick this up or if you have any other questions.

[mneverov]

hi, i'd like to work on session_variables (variable string not null, value string not null).