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>
view has syntax that corresponds almost exactly to
except that the view contains
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.