Views

Views are stored SELECT queries. If you repeatedly use the same query, it is worthwhile creating it as a view.

To make resource links easily accessible, let's order them alphabetically and create hyperlinks to each letter of the alphabet. With this in mind, take a look at the alphabet view shown in Listing 15-2.

Listing 15-2. Views

CREATE VIEW alphabet AS
    SELECT DISTINCT UPPER(SUBSTR(linktext,1,1)) AS letter
    FROM tblresources
    WHERE reviewed = 1
    ORDER BY letter;
CREATE VIEW specific_link AS
    SELECT id, url,
    (precedingcopy || ' ' || linktext || ' ' || followingcopy)
    AS copy
    FROM tblresources;

The alphabet view creates a row of links as pictured at the top of Figure 15-1.

Rather than repeat the SQL statement that makes up the alphabet view, we can instead simply SELECT * FROM alphabet using the name of the view in the FROM clause.

The second view, specific_link, also shown in Listing 15-2, demonstrates how a view can be "updated" when used in conjunction with a trigger. We will return to this view in the following discussion about triggers, but do note the use of || as the string concatenation operator.

As you can see, SQLite defines its own string manipulation functions. For a complete list of functions and operators, see www.sqlite.org/lang_expr.html.