eXtremeSQL Views

It can be useful to create and manipulate Views of tables. To define a view it is necessary to define table "Views" in the database schema as follows:

 
    class Views
    {
        string name;    // name of the view
        string body;    // table name and fields present in the view
 
        tree<name> pk;
    };
     

Views are created in the standard SQL manner by executing a statement like the following:

 
    CREATE VIEW name AS select-expression;
     

For example, the following statements demonstrate using a view with xSQL:

 
    XSQL>create view SV as select sid from S;
    XSQL>insert into S (sid,sname) values (1,1);
    XSQL>select * from SV;
    sid
    ------------------------------------------------------------------------------
    1
     
    XSQL>drop view SV;
     

Things to bear in mind about views in eXtremeSQL:

1. Views are implemented as a nested select; so the above query would be translated to

     
    select * from (select sid from S);
     

The eXtremeSQL optimizer does not always efficiently transform nested queries. So sometimes a query with views will be less efficient than one written manually without views.

2. Information about created views is stored in the “Views” table. If a view definition needs to be persistent, define the table Views as "persistent". (Clearly this is relevant for persistent databases only.)

3. Views are read-only - Updateable views are not supported. This means that it is not possible to do something like:

 
    insert into SV  (sid) values (1);