enhancement of vschema alter to better support authoritative columns list

[inexplicable]

This is a propose to further enhancement of: https://github.com/vitessio/vitess/issues/3459

Feature Description

We'd like to have more ALTER VSCHEMA ON {table} syntax to:

1. set a table with authoritative_columns_list enabled in vschema
2. add/drop a column from a table which is authoritative_columns_list enabled in vschema The motivation is to allow application to mix vschema ddl as above with common sql ddls that either creates new table, or add/remove columns from a table w/o doing a whole vchema swap, while the authoritative_columns_list feature is on. At Pinterest, we're using the authoritative_columns_list feature, and the most common DDL that impacts the vschema tables are CREATE TABLE, ALTER TABLE ADD COLUMN.

Use Case(s)

1. after a CREATE TABLE ddl, we could enable authoritative columns list feature by:
   • ALTER VSCHEMA ON {table} ADD COLUMN c1 {type}
   • ALTER VSCHEMA ON {table} ADD COLUMN c2 {type}
   • ...
   • ALTER VSCHEMA SET TABLE {table} AUTHORITATIVE
2. after an ALTER TABLE {table} ADD c1 {type}
   • ALTER VSCHEMA ON {table} ADD COLUMN c1 {type}
3. after an ALTER TABLE {table} DROP c1
   • ALTER VSCHEMA ON {table} DROP COLUMN c1

given above support, an application schema change with authoritative_columns_list won't have to go through an entire vschema swap, just need to use the additional vschema ddl. that could also facilitate the DBA review process, as they don't have to review one .sql, and .json separately. lastly, there's a trickiness with the columns change with authoritative_columns_list:

1. when adding a column, we want the vschema column add to happen after the table schema change
2. when dropping a column, we want the vschema column to be droppped before the table schema change the sequence is very difficult to be enforced if running .sql & .json change in separation, but as the vschema ddl could get mixed into the *.sql ddl, the sequence of the statement could reflect the above accordingly

[demmer]

As @sougou mentioned in Slack, the long-term goal is for vtgates to learn the table schema from each tablet and thereby deprecate the need for authoritative columns in the vschema: https://vitess.slack.com/archives/C0PQY0PTK/p1550613442253300

I don't see an open issue to link to (@sougou you should create one) but regardless, that is a fairly involved project so in the meantime I think it's reasonable to add this support.

One suggestion:

Instead of:

• ALTER VSCHEMA SET TABLE {table} AUTHORITATIVE

I think it'd be more consistent and future proof to have:

• ALTER VSCHEMA ON {table} SET authoritative=TRUE
• ALTER VSCHEMA ON {table} SET authoritative=FALSE

This implies that AUTHORITATIVE does not need to be a new keyword since the SET statement would contain an expression, and also affords for future extensibility if there are other things like "authoritative" we want to set/unset on the vschema.

[inexplicable]

agree that ALTER VSCHEMA ON {table} SET authoritative={TRUE|FALSE} is a better bet. i'd also be excited to see vtgate better handles the vschema, but not seeing it in the near term i guess? thanks for the feedback, i'll take some time, and prep a PR