Views

What is a View?

Views look and act like tables, but the data in views is materialized on execution using a view definition query that itself references concrete tables. The data is stored in the tables the views reference not the view itself.

How to use Views

Views allow you to derive tables using SQL instead of storing a copy of all the data you might want to derive. As a simple example, in a table of employee's salaries, you may store yearly salary but when using the table to calculate monthly salary, you use a view that divides the yearly salary by 12.

Note, accessing views is be slower than accessing a table itself because the database must compute the values returned.

Difference between MySQL Views and Dolt Views

There is no difference between MySQL and Dolt views. They are functionally equivalent.

Interaction with Dolt Version Control

Dolt view definitions are versioned in the dolt_schemas system table.

If you would like to use a current view with an different version of the data, as of syntax works with views. To use a past definition of the view, you must checkout a branch at the commit of the definition you would like to use.

Example

mysql> create table salaries (name varchar(255), salary int, primary key(name));
mysql> insert into salaries values ('Jim', 120000), ('Bob', 240000), ('Sally', 360000);
mysql> create view monthly_salaries as select name, salary/12 as monthly_pay from salaries; 
mysql> select * from monthly_salaries order by monthly_pay asc;
+-------+-------------+
| name  | monthly_pay |
+-------+-------------+
| Jim   | 10000       |
| Bob   | 20000       |
| Sally | 30000       |
+-------+-------------+

Using as of with Views

mysql> call dolt_add('-A');
mysql> call dolt_commit('-am', 'Created table and view');
+----------------------------------+
| hash                             |
+----------------------------------+
| trj7dm02r8c94nqpbphjgolhhsk37rkj |
+----------------------------------+
mysql> insert into salaries values ('Tim', 480000);
mysql> select * from monthly_salaries order by monthly_pay asc;
+-------+-------------+
| name  | monthly_pay |
+-------+-------------+
| Jim   | 10000       |
| Bob   | 20000       |
| Sally | 30000       |
| Tim   | 40000       |
+-------+-------------+
mysql> select * from monthly_salaries as of 'HEAD' order by monthly_pay asc;
+-------+-------------+
| name  | monthly_pay |
+-------+-------------+
| Jim   | 10000       |
| Bob   | 20000       |
| Sally | 30000       |
+-------+-------------+