Working with views

Chapter 17. Working with Database Objects
Working with views

Views are computed tables. You can use views to provide to database users exactly the information you want to present, in a format you can control.

Similarities between views and base tables

Views are similar to the permanent tables of the database (a permanent table is also called a base table) in many ways:

You can assign access permissions to views just as to base tables.
You can perform SELECT queries on views.
You can perform UPDATE, INSERT, and DELETE operations on some views.
You can create views based on other views.

Differences between view and permanent tables

There are some differences between views and permanent tables:

You cannot create indexes on views.
You cannot perform UPDATE, INSERT, and DELETE operations on all views.
You cannot assign integrity constraints and keys to views.
Views are recomputed each time they are invoked. Views refer to the information in base tables, but do not hold copies of that information.

Benefits of tailoring access

Views are used to tailor access to data in the database Tailoring access serves several purposes:

Improved security: by not allowing access to information that is not relevant.
Improved usability: by presenting users and application developers with data in a more easily understood form than in the base tables.
Improved consistency: by centralizing in the database the definition of common queries.

Creating views

A SELECT statement operates on one or more tables and produces a result set that is also a table: just like a base table, a result set from a SELECT query has columns and rows.

A view gives a name to a particular query, and holds the definition in the database system tables.

Example

Suppose that you frequently need to list the number of employees in each department. You can get this list with the following command:

     SELECT dept_ID, count(*)
     FROM employee
     GROUP BY dept_ID

You can create a view containing the results of this command as follows:

     CREATE VIEW DepartmentSize AS
     SELECT dept_ID, count(*)
     FROM employee
     GROUP BY dept_ID

The information in a view is not stored separately in the database. Each time you refer to the view, SQL Anywhere executes the associated SELECT statement to retrieve the appropriate data. On one hand, this is good because it means that if someone modifies the Employee table, the information in the DepartmentSize view will be automatically up to date. On the other hand, if the SELECT command is complicated it may take a long time for SQL to find the correct information every time you use the view.

To create a view in Sybase Central:

Connect to the database.
Click the Views folder for that database.
Double-click Add View.
Enter the CREATE VIEW statement in the Sybase Central editor, and click Execute Script to save the view.

For more information, see the Sybase Central online Help.

Using views

Restrictions on SELECT statements

There are some restrictions on the SELECT statements that you can use as views. In particular, you cannot use an ORDER BY clause in the SELECT query. It is a characteristic of relational tables that there is no significance to the ordering of the rows or columns, and using an ORDER BY clause would impose an order on the rows of the view. You can use the GROUP BY clause, subqueries, and joins in view definitions.

To develop a view, you should tune the SELECT query by itself until it provides exactly the results you need in the format you want. Once you have the SELECT query just right, you can add a

     CREATE VIEW viewname AS

phrase in front of the query to create the view.

Updating views

UPDATE, INSERT, and DELETE statements are allowed on some views, but not on others, depending on its associated SELECT statement.

Views containing aggregate functions, such as COUNT(*), cannot be updated. Views containing a GROUP BY clause in the SELECT statement cannot be updated. Also, views containing a UNION operation cannot be updated. In all these cases, there is no way for the database engine to translate the UPDATE into an action on the underlying tables.

Using the WITH CHECK OPTION clause

Even when INSERT and UPDATE statements are allowed against a view, it is possible that the inserted or updated row or rows in the underlying tables may not meet the requirements for the view itself: the view would have no new rows even though the INSERT or UPDATE does modify the underlying tables.

Examples using the WITH CHECK option clause

The following set of examples illustrates the meaning and usefulness of the WITH CHECK OPTION clause. This optional clause is the final clause in the CREATE VIEW statement.

To create a view displaying the employees in the sales department.

Type the following commands:

     CREATE VIEW sales_employee
     AS SELECT    emp_id,
             emp_fname,
             emp_lname,
             dept_id
     FROM employee
     WHERE dept_id = 200 ;

The contents of this view are as follows:

     SELECT *
     FROM sales_employee

emp_id emp_fname emp_lname dept_id

129 Philip Chin 200

195 Marc Dill 200

299 Rollin Overbey 200

467 James Klobucher 200

641 Thomas Powell 200

emp_id	emp_fname	emp_lname	dept_id
129	Philip	Chin	200
195	Marc	Dill	200
299	Rollin	Overbey	200
467	James	Klobucher	200
641	Thomas	Powell	200

The following UPDATE statement modifies this list, but in such a way that the modified row no longer meets the criterion for the view, and so vanishes from the view.

Transfer Philip Chin to the marketing department. This view update causes the entry to vanish from the view, as it no longer meets the view selection criterion.
```
     UPDATE sales_employee
     SET dept_id = 400
     WHERE emp_id = 129
```

&diams List all employees in the sales department.

     SELECT *
     FROM sales_employee

emp_id emp_fname emp_lname dept_id

195 Marc Dill 200

299 Rollin Overbey 200

467 James Klobucher 200

641 Thomas Powell 200

667 Mary Garcia 200

emp_id	emp_fname	emp_lname	dept_id
195	Marc	Dill	200
299	Rollin	Overbey	200
467	James	Klobucher	200
641	Thomas	Powell	200
667	Mary	Garcia	200

When a view is created WITH CHECK OPTION, any UPDATE or INSERT statement on the view is checked to ensure that the new row does match the view condition. If it does not, the operation causes an error and is rejected.

The following modified sales_employee view rejects the update statement, generating an error message "invalid value for column 'dept_id' in table 'employee'".

&diams Create a view displaying the employees in the sales department (second attempt).

     CREATE VIEW sales_employee
     AS SELECT emp_id, emp_fname, emp_lname, dept_id
     FROM employee
     WHERE dept_id = 200
     WITH CHECK OPTION;

The check option is inherited

If a view (say V2) is defined on the sales_employee view, any updates or inserts on V2 that cause the WITH CHECK OPTION criterion on sales_employee to fail are rejected, even if V2 is defined without a check option.

Modifying views

To modify a view, you need to first remove it from the database using the DROP statement, and then create a replacement using the CREATE VIEW statement.

For example, to replace the base table column names with more informative names in the DepartmentSize view, you would first drop the view using the DROP VIEW command (see "Deleting views") and then create a new view with the same name (see "Creating views"):

     CREATE VIEW DepartmentSize (Dept_ID, NumEmployees) AS
     SELECT dept_ID, count(*)
     FROM Employee
     GROUP BY dept_ID

Permissions are lost when you modify views
All permissions governing access to the view are lost when you execute the DROP VIEW command. You need to reassign permissions when you modify views.

Permissions on views

With release 5.0, a change has been made to permissions on views. Before release 5.0, permissions on the underlying tables were required in order for permissions to be granted on views. Permissions can now be granted on views without permissions on the underlying tables.

An INSERT, DELETE, or UPDATE operation is allowed either if permission on the view has been granted or if permission on the underlying tables has been granted. Previously permissions on both the table and the view were required.

UPDATE permissions can be granted only on an entire view. Unlike tables, UPDATE permissions cannot be granted on individual columns within a view.

Deleting views

To delete a view from the database, you use the DROP statement. The following command removes the DepartmentSize view:

     DROP VIEW DepartmentSize

Dropping a view in Sybase Central

To drop a view in Sybase Central, right-click the view you wish to delete and select Delete from the popup menu.

For more information, see the Sybase Central online Help.

Views in the system tables

All the information about views in a database is held in the system table SYS.SYSTABLE. The information is presented in a more readable format in the system view SYS.SYSVIEWS. For more information about these, see "SYSTABLE system table" and "SYS.SYSVIEWS".

You can use ISQL to browse the information in these tables. Type the following command in the ISQL command window to see all the columns in the SYS.SYSVIEWS view:

     SELECT * FROM SYS.SYSVIEWS

To extract a text file containing the definition of a specific view, use a command such as the following:

     SELECT viewtext FROM SYS.SYSVIEWS
         WHERE viewname = 'Marks';
     OUTPUT TO viewtext.sql FORMAT ASCII