# Proposal: Implement View Feature

- Author(s):     AndrewDi
- Last updated:  2018-12-03
- Discussion at: https://github.com/pingcap/tidb/issues/7974

## Abstract
This proposal proposes to implement basic VIEW feature in TiDB, aimed to make SQL easier to write. VIEW's advanced feature would be considered later.

## Background
A database view is a searchable object in a database that is defined by a query. Though a view doesn’t store data, some refer to a VIEW as "virtual tables", and you can query a view like you can query a table. A view can combine data from two or more tables, using joins, and also just contain a subset of information. This makes them convenient to abstract, or hide, complicated queries.

Below is a visual depiction of a view:
  ![AnatomyOfAview](imgs/view.png)   
  reference from https://www.essentialsql.com/what-is-a-relational-database-view/
  
A view is created from a query using the "`CREATE OR REPLACE VIEW`" command. In the example below we are creating a PopularBooks view based on a query which selects all Books that have the IsPopular field checked. Following is the query:
```sql
CREATE OR replace VIEW popularbooks 
AS 
  SELECT isbn, 
         title, 
         author, 
         publishdate 
  FROM   books 
  WHERE  ispopular = 1 
```  

Once a view is created, you can use it as you query any table in a `SELECT` statement. For example, to list all the popular book titles ordered by an author, you could write:  
```sql
SELECT Author, Title FROM PopularBooks ORDER BY Author
```

In general you can use any of the SELECT clauses, such as GROUP BY, in a select statement containing a view.

## Proposal
This proposal is prepared to implement the basic VIEW feature, which contains following ddl operations:
* `CREATE OR REPLACE VIEW`
* `SELECT FROM VIEW`
* `DROP VIEW`
* `SHOW TABLE STATUS`  

All other unimplemented features will be listed for compatibility and discussed later. And we introduce `ViewInfo` to store the metadata for view and add an attribute `*ViewInfo` which named `View` to TableInfo. If `TableInfo.View` != nil, then this `TableInfo` is a view, otherwise this `TableInfo` is a base table:
```
type ViewInfo struct {
	Algorithm   ViewAlgorithm    `json:"view_algorithm"`
	Definer     UserIdentity     `json:"view_definer"`  
	Security    ViewSecurity     `json:"view_security"`
	SelectStmt  string           `json:"view_select"`
	CheckOption ViewCheckOption  `json:"view_checkoption"`
	Cols        []model.CIStr    `json:"view_cols"`
}
```
* [Algorithm](https://dev.mysql.com/doc/refman/5.7/en/view-algorithms.html)  
    The view SQL `AlGORITHM` characteristic. The value should be one of `UNDEFINED`, `MERGE` OR `TEMPTABLE`. If no `ALGORITHM` clause is present, `UNDEFINED` is the default algorithm.
    Currently, we will only implement the `MERGE` algorithm.
* [Definer](https://dev.mysql.com/doc/refman/5.7/en/create-view.html)  
    The account of the user who created the view, in `'user_name'@'host_name'` format.
* [Security](https://dev.mysql.com/doc/refman/5.7/en/create-view.html)  
    The view SQL `SECURITY` characteristic. The value is one of `DEFINER` or `INVOKER`.
* [CheckOption](https://dev.mysql.com/doc/refman/5.7/en/view-check-option.html)  
    The `WITH CHECK OPTION` clause can be given to an updatable view to prevent inserts to rows for which the `WHERE` clause in the select_statement is not true. It also prevents updates to rows for which the `WHERE` clause is true but the update would cause it to be not true (in other words, it prevents visible rows from being updated to nonvisible rows).  
    In a `WITH CHECK OPTION` clause for an updatable view, the `LOCAL` and `CASCADED` keywords determine the scope of check testing when the view is defined in terms of another view. When neither keyword is given, the default is `CASCADED`.
* SelectStmt  
    This string is the origin `SELECT` SQL statement.
* Cols  
    This `model.CIStr` array stores view's column alias names.
* TableInfo.Columns  
    `TableInfo.Columns` only stores view's column origin names.
 
## Rationale
1. Create VIEW  
   This proposal only supports the following grammar to create view:
   ```sql
    CREATE
        [OR REPLACE]
        [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
        [DEFINER = { user | CURRENT_USER }]
        [SQL SECURITY { DEFINER | INVOKER }]
        VIEW view_name [(column_list)]
        AS select_statement
        [WITH [CASCADED | LOCAL] CHECK OPTION]
    ```
    1. Parse the create view statement and build a logical plan for select clause part. If any grammar error occurs, return errors to parser.   
    2. Examine view definer's privileges. Definer should own both `CREATE_VIEW_PRIV` privilege and base table's `SELECT` privilege. We will reuse `CREATE_PRIV` privilege when implement `CREATE VIEW` feature and will support `CREATE_VIEW_PRIV` check later.  
    3. Examine create view statement. If the `ViewFieldList` clause part is empty, then we should generate view column names from SelectStmt clause. Otherwise check len(ViewFieldList) == len(Columns from SelectStmt). And then we save column names to `TableInfo.Columns`.
2. DROP VIEW  
  Implement `DROP VIEW` grammar, and delete the existing view tableinfo object. This function should reuse `DROP TABLE` code logic.
3. SELECT FROM VIEW  
    In function `func (b *PlanBuilder) buildDataSource(tn *ast.TableName) (LogicalPlan, error)`, if `tn *ast.TableName` is a view, then we build a select `LogicalPlan` from view's `SelectStmt` string. But this solution meets a problem, and here is the example:  
    ```sql
       create table t(a int,b int);
       create view v like select * from t;
       select * from t;
    ```
    Once we query from view `v`, database will rewrite view's `SelectStmt` from `select * from t` into **`select a as a,b as b from t`**.
    But, if we alter the schema of t like this:
    ```sql
       drop table t;
       create table t(c int,d int);
    ```
    Executing `select * from v` now is equivalent to **`select c as c, d as d from t`**. The result of this query will be incompatible with the character of VIEW that a VIEW should be "frozen" after being defined.
    In order to solve the problem described above, we must build a `Projection` at the top of original select's `LogicalPlan`, just like we rewrite view's `SelectStmt` from `select * from t` into **`select a as a,b as b from (select * from t)`**.  
    This is a temporary fix and we will implement TiDB to rewrite SQL with replacing all wildcard finally.  
4. Show table status  
  Modify `SHOW TABLE STATUS` function to support show view status, and we use this command to check if `CREATE VIEW` and `DROP VIEW` operation is successful. To reuse `SHOW TABLE STATUS` code logic is preferred.

## Compatibility
Support the basic VIEW feature without affecting other existing functions, and make TiDB more compatible with MySQL.

## Implementation
Here is the work items list:  

|Action  |Priority|Deadline|Notes|
| ------ | ------ | ------ |-----|
|Extract ViewAlgorithm\ViewDefiner\ViewSQLSecurity\CheckOption to CreateViewStmt struct|P1|2019/01/15|--|
|Add ViewInfo attribute to TableInfo and add ActionCreateView to ddl actionMap|P1|2019/01/15|--|
|CREATE [OR REPLACE] VIEW view_name [ALGORITHM = {UNDEFINED \| MERGE \| TEMPTABLE}] [(column_list)] AS select_statement|P1|2019/01/15|This task must be done before any other tasks.|
|SHOW TABLE STATUS|P1|2019/01/30|--|
|DROP VIEW View|P1|2019/01/30|--|
|SELECT … FROM VIEW|P1|2019/03/10|--|
|Add some test cases for CreateView and Select … From View|P1|2019/03/30|Port from MySQL test case|
|Support CREATE_VIEW_PRIV check|P2| | |
|UPDATE VIEW|P2| |Difficult|
|INSERT VIEW|P2| |Difficult, dependent on UPDATE VIEW|
|SHOW CREATE [VIEW \| TABLE]|P2| | |
|ALTER VIEW|P2| | |
|ALTER \| DROP TABLE|P2| |Check if table is a View|
|Add test cases for UPDATE \| INSERT INTO View|P2| | |
|Add INFORMATION_SCHEMA.VIEWS view|P3| | |
|CREATE [OR REPLACE] VIEW [DEFINER = { user \| CURRENT_USER }] [SQL SECURITY { DEFINER \| INVOKER }] AS SELECT_STATEMENT|P3| | |
|CREATE [OR REPLACE] VIEW [ALGORITHM = {TEMPTABLE}] AS select_statement|P3| | |
|CREATE [OR REPLACE] VIEW AS select_statement [WITH [CASCADED \| LOCAL] CHECK OPTION]|P3| | |
|Support global sql_mode system variable|P3| | |

## Open issues (if applicable)
https://github.com/pingcap/tidb/issues/7974