The virtual column element

Virtual Columns correspond to a defined column over the database. These columns have exactly the same tag attributes and sub-elements as the normal column attribute, but in addition they have a CDATA sub-element that defines how they are derived over the normal columns of the database.

As an example consider the virtual column inventoryvalue in the Products table: 

<virtual_column id="inventoryvalue" type="int">
    <![CDATA[$VAR.unitprice * $VAR.unitsinstock]]>
</virtual_column>

This does a derived calculation to project an attribute that is a simple product. Note that while the case-sensitive $VAR substitution parameter may not always be needed it is very good practice to avoid obscure SQL problems in complex queries. This will make sure that the table variable aliases will be plugged in correctly.

A common use of a virtual column is to build a virtual key. This is necessary when you want to let users manually edit tuples in the NLI portal. An example of this is for the Eating in PIM.cphrase: 

    <table id="Eating" writes="full" lex="eating,eatings,ate,eaten">
        <column id="food" type="text" lex="food" select="false"/>
        <column id="date" type="date" default="(date('now'))" lex="when" properties="+value-stands-for-column"/>
        <column id="helping" type="real" default="(1.0)" units="portions" lex="helping,helpings,quantity"/>
        <virtual_column id="id" type="text" properties="-project">
<![CDATA[
replace(replace($VAR.food,',',''),'''','') || $VAR.date
]]>
        </virtual_column>
        <primary_key key="id"/>
        <foreign_key from="food" to="Food"/>
    </table>

Finally we can see here another user of virtual attributes to build up a meaningful primary value: 

    <table id="Person" writes="full" lex="person,people">
        <column id="id" type="int"/>
        <column id="firstname" type="text"/>
        <column id="lastname" type="text"/>
        <virtual_column id="name" type="text"<
![CDATA[
$VAR.firstname || ' ' || $VAR.lastname
]]>
        </virtual_column>
        <primary_key key="id"/>
        <primary_value key="name"/>
    </table>

Thus in this case the full name will be displayed when generating names via foreign keys.