Library

Views (InterBase and Firebird)

Alexey Kovyazin, last update 13-April-2012
Those who are familiar with SQL language do not require detailed explanations of this subject, but for preserving the sequence of presentation, we will introduce a brief definition of views.

VIEW is a virtual table created on the basis of inquiry to ordinary tables. A view is implemented as the inquiry, stored on a server and executed every time when we refer to a view.

Let's consider different variants of using views. Views enable to create levels of data structure, allowing to separate implementation of data storage from their type. For example, we can create a view, which selects the data from several tables. If the clients use this view instead of referring directly to underlying tables, the database developer will be able to change the query underlying the view, to alter it (to optimize, for example), and the client will notice nothing – it will be the same view for him. Apart from the fact that they isolate implementation of data storage from the user, views allow to organize the data in more convenient and simple way. The problem of data structure "simplification" arises when a number of tables in a database grows big enough and interrelations between them – complicated. View allows to eliminate (or, on the contrary, to add) a part of the data not necessary to the concrete database client (or - necessary).

In addition, views allow to organize security in InterBase database simpler. Some users can have rights only to read / update the data in the view, but have no rights (and even no idea) about the tables underlying the view! For more detail about security in InterBase, see the chapter "Security in InterBase: users, their functions and rights " (part 4).

DDL syntax for work with views

Now we will consider the commands for creating and deleting views defined by DDL (Data Definition Language – SQL subset, see the glossary). In order to create a view in InterBase, we should use the sentence of the following syntax:

CREATE VIEW viewname [(view_column[, view_column…])] AS [WITH CHECK OPTION]; Here viewname is the name of view that must be unique within a database, and then goes a group of not always mandatory names of the fields included in the view: [(view_column [, view_column …])]. It is essential to define statement which selects the data included in the view. We will discuss the optional parameter WITH CHECK OPTION a bit later in part "Modified views".

In order to alter the view, we will have to recreate it, i.e. to delete and create again. When deleting the view, it is necessary to delete also all the dependent objects – the triggers, stored procedures and other views. This is one of the main inconveniences of work with views: necessity to recreate the tree of objects using the view (there are utilities that allow us to do it easier, for example IBAlterView, see the application "Administrator and InterBase designer tools"). We should use the following DDL command if we want to delete the view:

DROP VIEW viewname;

Examples of views

Here is an example of simple view:

CREATE VIEW MyView AS SELECT NAME, PRICE_1 FROM Table_example;

In this example, we create a view based on the query to the table Table_example that we have considered in the chapter "Tables. Primary keys and generators". In this case, the view will consist of two fields - NAME and PRICE_1, which will be selected from table Table_example without any conditions, i.e. the number of records in the view MyView will be equal to the number of records in Table_example. However, views are not always so simple. They can be based on the data from several tables and even on basis of other views. In addition, views can contain the data received on basis of different expressions - including on basis of aggregate functions. In order to consider the usage of this view application in more detail let’s create two tables joined by relation one-to-many (often such relation is called master-detail). Here is DDL-script for creating these tables:

/* Table: WISEMEN */

CREATE TABLE WISEMEN ( ID_WISEMAN INTEGER NOT NULL, WISEMAN_NAME VARCHAR(80));

/* Primary keys definition */

ALTER TABLE WISEMEN ADD CONSTRAINT PK_WISEMEN PRIMARY KEY (ID_WISEMAN);

/* Table: WISEBOOK */

CREATE TABLE WISEBOOK ( ID_BOOK INTEGER NOT NULL, ID_WISEMAN INTEGER, BOOK VARCHAR(80));

/* Primary keys definition */

ALTER TABLE WISEBOOK ADD CONSTRAINT PK_WISEBOOK PRIMARY KEY (ID_BOOK);

/* Foreign keys definition */

ALTER TABLE WISEBOOK ADD CONSTRAINT FK_WISEBOOK FOREIGN KEY (ID_WISEMAN) REFERENCES WISEMEN (ID_WISEMAN);

So, we have created two tables – WISEMEN and WISEBOOK joined by master-detail relation using a foreign key constraint – FOREIGN KEY. Let’s assume that these tables will store the information about the great Chinese wise men and their works. Now we can create a few views based on these tables. For example, let’s create the view showing how many works each wise man has:

CREATE VIEW WiseBookCount (WISEMAN, HOW_WISEBOOKS) AS SELECT M.WISEMAN_NAME, COUNT(B.BOOK) FROM WISEMEN M, WISEBOOK B WHERE (M.ID_WISEMAN = B.ID_WISEMAN) GROUP BY M.WISEMAN_NAME

Pay attention that when using any calculated expressions like aggregate functions COUNT (), SUM (), MAX (), etc., it is essential to use definite name of view fields, i.e. to give names to all fields returned by the query. As we can see from this example, these names must not necessarily coincide with the names of query fields, but their quantity must coincide with the quantity of fields returned by the query. Definition of what field returned by inquiry corresponds to what field of the view is made by a serial number - the first query field will be reflected in the first field of the view, the second - in the second, etc.

And if we would like to know, which of the wise men has written the most books? We will try to add the expression for sorting - ORDER BY to the query underlying the view. However, this attempt will be unsuccessful: usage of sorting ORDER BY in views is not allowed and when trying to create the view with the query containing ORDER BY, the error will arise. If we want to sort the results returned by the view, we will have to make it on behalf of the client:

SELECT * FROM WiseBookCount ORDER BY HOW_WISEBOOKS

Execution of this SQL-query will lead to a desirable result. Apart from the constraint for using expression ORDER BY in views, we cannot use the data set received as a result of executing stored procedures as a data source either (see the chapter "Stored procedures" below).

Perhaps, it is worth giving one more example illustrating application of views. Let’s assume that we have to output a list of wise men whose name begins with letter "K". In this case, we will use the view with conditions:

CREATE VIEW WiseMen2 (WISEMAN) AS SELECT M.WISEMAN_NAME FROM WISEMEN M WHERE M.WISEMAN_NAME LIKE 'K%'

Thus, it is easy to create views, which play a role of constantly updatable data suppliers, selecting them from a database according to definite conditions.

Modified views

We have mentioned above that there is a capability to create modified data views. It is really so - there is a capability not only to read the data from the view, but also to modify them!

There are two ways to make the view modified. The first way is applied when the view is created on basis of the unique table (or other modified view), and all the columns of the given table must allow the presence of NULL. Thus the query the view is based on cannot contain subqueries, aggregate functions, UDF, stored procedures, DISTINCT and HAVING statements. If all these conditions are fulfilled, the view automatically becomes modified, i.e. we can execute the queries DELETE, INSERT and UPDATE for it, which will alter the data in a table-source.

The list of conditions is rather impressive and greatly constrains the application of such modified views, consequently they are used quite rarely.

In order to make a modified view that violates any of above-listed conditions, the mechanism of triggers is applied. For more details about the trigger, see the chapter "Triggers" (part 1). Now we will only consider general principles of organization of data alteration in VIEW.

For implementation of updated view using triggers the following must be done. To create 3 triggers for the given view for events: BEFORE DELETE, BEFORE UPDATE and BEFORE INSERT. To describe in these triggers what should be done with the data when deleting, updating and inserting.

Then we should use the given view in modification queries - DELETE, INSERT or UPDATE. When InterBase will get this query, it will check if there are appropriate triggers for the given view, i.e. BEFORE DELETE/INSERT/UPDATE. If the trigger for executable action exists, InterBase will call it for modification of real data in the tables underlying the view (though it can be the other data – there is no text constraints of these triggers), and then will re-read the string (or strings) modification was performed over.

Thus, there is a capability to realize complex chains of updating data in views.

Option WITH CHECK OPTION was mentioned in the description of syntax of creating the view. If this option is set when creating a modified view, every data string inserted or altered in this view will be checked on a condition of getting to the view. It can be explained so: if a new record inserted by user or received as a result of updating the existing record does not satisfy the conditions of the query, which is the data supplier for VIEW, inserting of this record will be cancelled and an error will arise.

Conclusion