dcsimg

Views Work In MySQL 5.0

August 25, 2004

By Peter Gulutzan

On July 15 2004, Oleksandr Byelkin of Ukraine merged his "views" code into MySQL. I have verified that the MySQL version 5.0 Linux source download supports views. This report will interest people who know MySQL, who know what views are, and who have been waiting a long time for MySQL to support views.

INSTALLING

I downloaded MySQL 5.0 source as described in the manual "2.3.3 Installing from the Development Source Tree" and built a new package on a Linux machine that I use for tests.

I started the mysqld server, started a client as user = root, and ran this initial check:

mysql> SELECT * FROM mysql.db;

I saw two new columns named Create_view_priv and Show_view_priv. (If these columns are not present, then some mysql tables need fixing and the MySQL Reference Manual 2.5.8 "Upgrading the Grant Tables" page has the necessary instructions.)

I gave myself privileges by changing the columns:

mysql> UPDATE mysql.db SET Create_view_priv='Y', Show_view_priv='Y';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

I made a sample table in the test database:

mysql> USE test;
Database changed

mysql> CREATE TABLE table1 (s1 INT, s2 CHAR(30));
Query OK, 0 rows affected (0.29 sec)

mysql> INSERT INTO table1 VALUES (1,'Row #1');
Query OK, 1 row affected (0.00 sec)

Then I was ready to test.

TEST #1

mysql> CREATE VIEW v1 AS SELECT * FROM table1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+----------------+------------+
| Tables_in_test | table_type |
+----------------+------------+
| table1         |BASE TABLE  |
| v1             |VIEW        |
+----------------+------------+
2 rows in set (0.00 sec)

mysql> SHOW CREATE VIEW v1;

+-------+-------------------------------+
+                                       |
| Table | Create Table                  |
|       |                               |
+-------+-------------------------------+
| v1    | CREATE VIEW test.v1 AS select |
|       | 'test'.'table1'.'s1' AS 's1', |
|       | 'test'.'table1'.'s2' AS 's2   |
|       | from 'test'.'table1'          |
+-------+-------------------------------|

This test showed that CREATE VIE W works. Clearly one can make an object using the standard CREATE VIEW syntax, and that object will be visible with SHOW statements.

TEST #2

mysql> SELECT * FROM v1;
+------+--------+
| s1   | s2     |
+------+--------+
|    1 | Row #1 |
+------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO v1 VALUES (5,'Row #2');
Query OK, 1 row affected (0.00 sec)

mysql> DELETE FROM v1 WHERE s1 = 5;
Query OK, 1 row affected (0.29 sec)

mysql> INSERT INTO v1 VALUES (2,'Row #2');
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE v1 SET s2 = 'Row #1-A' WHERE s1 = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT s2, s1 FROM v1;
+----------+------+
| s2       | s1   |
+----------+------+
| Row #1-A |    1 |
| Row #2   |    2 |
+----------+------+
2 rows in set (0.00 sec)

This test showed that views are truly windows through which we see the underlying base tables. Clearly one can change the original table, or see its contents, by issuing data-manipulation statements against the view.

The fact that simple views are updatable is a feature that we're proud of -- as the MySQLmanual says, our eventual goal is that theoretically-updatable views should be updatable in practice.

TEST #3

mysql> CREATE VIEW v2 AS select CONCAT(s1,s2) AS v2_s1
    -> FROM v1
    -> WHERE s1 = 1 OR s1 = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('*',v2_s1,'*')
    -> FROM v2
    -> WHERE v2_s1 <> 2
    -> ORDER BY v2_s1;
+-----------------------+
| CONCAT('*',v2_s1,'*') |
+-----------------------+
| *1Row #1-A*           |
+-----------------------+
1 row in set (0.26 sec)

This test showed miscellaneous capabilities. Clearly one can make views of views, use view columns in expressions, and use WHERE clauses in both the view and the outer query.

The above tests suffice for demonstration. You can find a much more severe test series in the source download, in the file ~/mysql-5.0/mysql-test/t/views.text.

RULES

Since views are only a few weeks old, these rules are subject to change without notice. A better description will appear in the MySQL Reference Manual when the time is ripe.

1. The CREATE VIEW syntax looks like:

   CREATE [ OR REPLACE ] VIEW view-name [(column-list)]
   AS select-statement
   [ WITH CHECK OPTION ]

The view-name is an identifier; like other identifiers it may be delimited, may be up to 64 characters long, and may be qualified.

The select-statement may contain almost any SELECT syntax that's legal in MySQL, including joins, subqueries, unions, AS clauses, or references to other views. However, it may not contain references to temporary tables, parameters, or variables.

2. The SHOW CREATE VIEW statement looks like:

   SHOW CREATE VIEW view-name

This statement is like SHOW CREATE TABLE, SHOW CREATE PROCEDURE, and so on.

3. The DROP VIEW statement looks like:

   DROP VIEW view-name [ RESTRICT | CASCADE ]

4. The ALTER VIEW statement looks like this:

   ALTER VIEW view-name
   AS select-statement

5. The GRANT CREATE VIEW statement looks like:

   GRANT CREATE VIEW ON database-name . * TO user-name

There is a new column in the mysql.db table, Create_view_priv, which depends on GRANT CREATE VIEW.

6. Simple views are updatable, that is, they may be used in UPDATE, INSERT, DELETE statements. There are restrictions, such as: the view's SELECT may not contain DISTINCT, GROUP BY, expressions in the select list, or other things which make a view non-updatable in the theoretical sense.

7. There is a new variable, sql_updatable_view_key, with possible values: 'YES' (meaning "issue error"), 'NO' ("don't issue error but issue warning"), and 'LIMIT1' ("issue error only if LIMIT 1 is used"). This controls checking of keys in underlying tables.

8. The GRANT statement for tables works for views too. So "GRANT SELECT ON v1 TO Sam;" means that Sam can SELECT from view v1. However, that does not mean that Sam can SELECT from the table that underlies v1. A view privilege applies only to the view.

Peter Gulutzan lives in Edmonton, Canada and works for MySQL AB. He holds official MySQL Certification. Peter co-authored four computer books; the latest was SQL Performance Tuning. He plans to speak at MySQL's next user conference in April 2005 in Santa Clara.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers