Informix Views

Views

A view is a logical representation of physical columns from one or multiple tables. A view looks and acts like a table, but it really isn't a physical table that resides on disk. Referred to by Informix as a virtual table, a view is a great way to present specific information to specific users, without placing an entire table's data in the open or keeping multiple versions of the data for different groups of users. Figure 16.1 shows how users view data from single or multiple tables through a view.

Views provide a means to restrict certain columns from users. Sometimes tables contain tons of information that is useful to only a small segment of users. Most of the time, only one or two columns are actually accessed. A view can show and allow access to those few columns. This method makes the tables seem less overwhelming to users who don't fully understand all the data columns.

Some data contained in a table may be sensitive to specific users for legal or moral reasons. You can use a view to allow users access to some of the data contained in sensitive tables while restricting access to other columns. For example, an employee table might contain information on each employee's address, phone number, who to notify in case of an emergency, and salary. Obviously, employees should not have access to other employees' salaries, but contact information could be important.

Views can represent derived values as easily as they can show stored column data. Any computations allowed by SQL can be represented in a view with a virtual column. For example, you can create a current balance column in a view to represent the sum of all current orders, subtracting any payments received. Every time the database makes an order or payment, the balance column in the view is updated.

You can represent derived data values within a view. By using the columns from one or more tables, the data placed in a view can be a derived value by using additions, subtraction, or any other mathematical function. For example, you can create a view showing the items, quantity, and value of orders placed by the customer Joe's Pizza, where the value is equal to the quantity multiplied against the price.

Creating a View

To create a view, the user attempting the create must have at least select privileges on all the tables with columns that are represented in the view. The two parts to the view creation statement are view naming and column selection.

A view's name must be a unique name of up to 18 characters. This name is used to access the view after it is created:

CREATE VIEW view_name AS

To assign columns to a view, use a standard SQL SELECT statement. The keywords ORDER BY and UNION are not allowed within a view's select statement, but all other select keywords are allowed.

Data type values are automatically inherited from the original columns to the new view columns. The names of the columns can also be inherited to the view unless specified as something different. When creating a virtual column, you must specify a name for the new column. When a column name is specified, the CREATE VIEW statement requires that all column names be specified, regardless of whether the names are different:

CREATE VIEW view_name (column list) AS

SELECT columns FROM tables

The first example sets up a standard view for the addresses of all employees who are currently employed full-time. No columns are renamed, so no column list is required.

CREATE VIEW empl_address AS

SELECT name, street, city, zip

FROM employee_info

WHERE current = Y AND

work_status = F

The next create sets up a view for all customers' ordering and payment activity. A virtual column is also created. You must list all columns because the virtual column needs a name. This view also joins two tables to retrieve the information:

CREATE VIEW customer_bal

(cust_id, last_order, last_payment, current_balance) AS

SELECT cust_id, total_order, total_payment,

total_order - total_payment

FROM order_table, payment_table

WHERE order_table.cust_id = payment_table.cust_id

You can use other views' columns to build other views. The next create example determines the total outstanding balance owed to the company from the customer_view view. You can also use aggregate SQL commands to create virtual columns. Aggregate commands include SUM, MIN, MAX, AVG, and COUNT. The SUM command is used to add all the balances together:

CREATE VIEW total_balance (balance) AS

SELECT SUM(current_balance)

FROM customer_view

The next create example sets up a view on all the column data related to specific rows. All sales made by salesperson 12, Joe, are listed in the view:

CREATE VIEW joes_sales AS

SELECT *

FROM sales

WHERE sales_person = 12

Accessing and Using Views

The creator of a view is considered the owner; owners and DBA-level users can grant and revoke access to the view to other users. You can restrict access to an entire table but give users access to the table's data through a view. This forces the users to use the view to access the data.

To restrict standard users from accessing the entire employee table but still allow them to access their addresses, you use the following example:

REVOKE ALL ON employee_info;

CREATE VIEW empl_address AS

SELECT name, street, city, zip

FROM employee_info;

GRANT SELECT, UPDATE ON empl_address TO PUBLIC;

Working with a view is just like accessing a table. Use the view name instead of the table name in all SQL commands. Some restrictions related to views are not found with individual tables. First, no indexes can be created on a view. Any table-related indexes are used when accessing the indexed data through a view. Any columns or tables used in a view must be present. If a table or view is dropped, any views that use the table or column are also dropped. Views that contain joins or aggregates can be accessed only with SELECT statements because a join or aggregate view takes different data items from different places and makes it look like it's all from one place. Informix cannot determine how a change to data in a view relates back to the original tables. Almost the same situation applies to virtual columns; because virtual columns are derived from multiple data sources, it is impossible to insert or update that value in a view. It is possible to delete the row from a view that contains a virtual column because Informix can trace back to the original column and keys.

Check Option

As mentioned in the previous discussion on creating views, you can create a view with information related to a specific data value or row. As in the joes_sales example, a view can contain a subset of a table or table's data.

The joes_sales example showed the following code:

CREATE VIEW joes_sales

(sales_person, customer, sub_length, price) AS

SELECT *

FROM sales

WHERE sales_person = 12;

If this view is available for Joe to use, he might want to insert his new sales directly through this view rather than use the entire sales table. If Joe sells newspaper subscriptions and he makes a sale of a one month subscription to Mary Jones at $9.99, this information is placed in the sales table through the joes_sales view:

INSERT INTO joes_sales

VALUES (12, "Mary Jones", 1, 9.99);

If Joe makes a mistake and uses the wrong sales_person number, his sale is credited to someone else:

INSERT INTO joes_sales

VALUES (11, "Mary Jones", 1, 9.99);

Although he uses joes_sales, the insert for sales_person 11, Jake, succeeds back to the sales table. Joe can check his view:

SELECT * FROM joes_sales;

The entry for 11 does not show up because the view is limited to sales_person 12. Jake can see the entry if he has his own view:

SELECT * FROM jakes_sales;

Users with direct access to the sales table can also see the entry:

SELECT * FROM sales;

To prevent this problem, use the keyword WITH CHECK OPTION when creating the view. The WITH CHECK OPTION allows inserts, updates, and deletes to occur only when they meet the view select criteria:

CREATE VIEW joes_sales

(sales_person, customer, sub_length, price) AS

SELECT *

FROM sales

WHERE sales_person = 12

WITH CHECK OPTION;

When Joe tries to insert his sales with the wrong sales_person number, he receives an error message.

Dropping a View

An owner of the view or a DBA can drop an existing view. When you drop a view, you do not lose the data, columns, and tables; only the view to that data is gone. The data still resides in the underlying tables and columns of the database. On the other hand, if the actual tables and columns are dropped, any views that use those tables and columns are automatically dropped. In views such as joes_sales, if Joe has no sales in the sales table, the view continues to exist, but it contains zero rows.

To drop a view, use the DROP VIEW command:

DROP VIEW view_name;

The following example uses DROP VIEW:

DROP VIEW joes_sales;

Changing a View

You cannot use an ALTER to change the layout of a view. If you need to change a view layout, you must drop and re-create the view with the new layout.

To verify the current view layouts, use the sysviews and sysdepends system tables. The sysviews system table contains the actual CREATE VIEW statement originally entered.

To see all the views currently in the database server, use

SELECT * FROM sysviews;

The sysdepends system table contains information on each view and the tables or other views that provide the data that makes up the original view.

To see all the dependencies each view contains, use

SELECT * FROM sysdepends;

When a view is dropped, its information no longer resides in the sysviews or sysdepends tables. It is a good idea to save a copy of the preceding queries as a backup listing of all the views to use as a reference when creating or re-creating a view.