The view element

C-Phrase has its own view element because we want C-Phrase administrators to be able to define views solely within C-Phrase without requiring any action on the part of the administrator of the underlying database. Still, if you have administration rites over the underlying database, consider defining your views (or materialized views) there.

The definition of the view element is:

<!ELEMENT view (column|primary_key|primary_value|foreign-key|#PCDATA)*>
<!ATTLIST view
          id CDATA #REQUIRED
          lex CDATA #IMPLIED
          select CDATA #IMPLIED
          silent CDATA #IMPLIED
          alive CDATA #IMPLIED
          description CDATA #IMPLIED>

In C-Phrase view has syntax that corresponds almost exactly to table, except that the view contains CDATA that contains the SQL definition of the view over the underlying database. This SQL must be written in a dialect compatible with the underlying database. Also, the view definition must be able to be calculated over the tables of the underlying database. You may not use views defined in C-Phrase in the definition of views.

There are many good reasons to support views in C-Phrase. The following sections present example uses.

Views Performing Calculations

Here we show a view definition for performing calculations. Consider that we have the following tables defined.

    <table id="Food" lex="food,foods">
        <column id="id" type="int"/>
        <column id="name" type="text"/>
        <column id="calories" type="int"/>

        <primary_key key="id"/>
        <primary_value key="name"/>
    </table>

    <table id="Eating" lex="food eaten,foods eaten,ate">
        <column id="food" type="int"/>
        <column id="time" type="timestamp" default="(datetime('now','localtime'))" lex="when"/>
        <column id="quantity" type="real" default="1.0"/>

        <foreign_key from="food" to="Food"/>
        <primary_value key="time"/>
    </table>

There are foods, with their calories, and eating events which record eating of a specific quantity of a food at a particular time. What we would like to do is generate daily calorie reports that calculate the total calories eaten per day starting at 3AM and ending at 3AM. To achieve this we declare the following view:

    <view id="Daily" lex="daily calorie report,daily calorie reports">
        <column id="calories" type="int"/>
        <column id="date" type="date"/>
        <primary_value key="date"/>
        <![CDATA[
    SELECT SUM(calories * quantity) AS calories,date(time, '-3 hours') AS date
           FROM Eating, food
           WHERE food = id GROUP BY date(time, '-3 hours')
        ]]>
    </view>

Note that the view is defined using the SQL dialect of the underlying database, in this case SQLite.

Views for Ellipsis

Here we see an example of a view that allows for highly elliptical questions for the types of products that customers buy:

<view id="bought_categories" lex="buying record,buying records,bought,were bought by">
        <column id="customerid" type="text"/>
        <column id="categoryid" type="int"/>
        <foreign_key from="customerid" to="customers"/>
        <foreign_key from="categoryid" to="categories"/>
<![CDATA[
SELECT DISTINCT X1."customerid",X5."categoryid"
 FROM "customers" AS X1, "orders" AS X2, "orderdetails" AS X3, "products" AS X4, "categories" AS X5
 WHERE
 X1."customerid"=X2."customerid"
 AND X2."orderid"=X3."orderid"
 AND X3."productid"=X4."productid"
 AND X4."categoryid"=X5."categoryid"]]>
    </view>
    

So with this view defined, the user can ask highly elliptical questions like 'customers in Sweden that bought beverages'

Views for Conceptual Modeling

Finally, in the database you are accessing, information might be highly abstracted (e.g. a triple-store) or might simply be a non-normalized wide table (AKA a universal relation). Complex views can build up a conceptual model over such a database.