Dataphor - Intelligent views

This is in response to the thread:

Saturday, June 16, 2007 10:10 AM
microsoft.public.sqlserver.programming
How to make a view as input???
http://tinyurl.com/2o5q8h

MS Sql Server 2005 Bol on View:
 'Creates a virtual table that represents the data in one or more tables
  in an alternative way.' 
  
This is a good and accurate defintion. It is a 'representation' of a 
select statement. But it does not 'capture' the relationship(s) of the
representation. It is unaware of any key(s) and most importantly unaware
of any constraints between tables, ie. reference(s). It thus suffers from
a deficit disorder as far as application development goes. This retardation
causes developers to assume responsibilities the db should handle amongst
which is the use of the nightmare t-sql trigger.

Here is a very simple example of intelligent view behavior for application
development. It uses the D4 language of Dataphor and uses sql server 2005
as the data respository.

create table MyOrders
{
 order_nbr:Integer,
 some_col:Integer,
 key{order_nbr}
}; 

create table MyOrderDetails
 {
  order_nbr:Integer,
  sku:Integer,
  item_price:Integer,
  key{order_nbr,sku},
  reference MyDetails_MyOrders{ order_nbr } 
  references MyOrders { order_nbr }
 };

insert table
 {
  row{1 order_nbr, 15 some_col},
  row{2,30},
  row{3,14}
 } into MyOrders;
 
insert table
 {
  row{1 order_nbr, 1 sku, 500 item_price},
  row{1, 2, 205},
  row{2, 1, 490},
  row{3, 1, 480}
 } into MyOrderDetails ; 

This is a natural join. The sql "on MyOrders.order_nbr=MyOrderDetails.order_nbr"
is implied by a natural join.

create view MyOrdersView
  MyOrders join MyOrderDetails 

The keys of each table and the constraint (reference) between the
tables are used to resolve each insert into the view. The view inherits
the keys of each table and the reference between the tables.

insert row{1 order_nbr,3 sku,253 item_price} into MyOrdersView;
insert row{2 order_nbr,2 sku,321 item_price} into MyOrdersView;
insert row{4 order_nbr,1 sku,42 some_col,321 item_price} into MyOrdersView;

select MyOrdersView;
order_nbr some_col sku item_price 
--------- -------- --- ---------- 
1         15       1   500        
1         15       2   205        
1         15       3   253        
2         30       1   490        
2         30       2   321        
3         14       1   480        
4         42       1   321        

An insert should be resolved to a delete followed by an insert from the
view to each base table, ie. an update.
(The "adorn" is meta-data attached to the insert allowing the update
 behavior to take place thru the view).

insert row{1 order_nbr,3 sku,25 some_col,632 item_price} into MyOrdersView
                           adorn with { PropagateInsert = "Ensure" };

select MyOrdersView;                         
order_nbr some_col sku item_price 
--------- -------- --- ---------- 
1         25       1   500        
1         25       2   205        
1         25       3   632        
2         30       1   490        
2         30       2   321        
3         14       1   480        
4         42       1   321           

select MyOrders;
order_nbr some_col 
--------- -------- 
1         25       
2         30       
3         14       
4         42       

select MyOrderDetails;
order_nbr sku item_price 
--------- --- ---------- 
1         1   500        
1         2   205        
1         3   632        
2         1   490        
2         2   321        
3         1   480        
4         1   321        

Before one asks the question of whether or not there is intelligence
in the universe, developers should ask if there is intelligence in
their application tools -:)

bye for now,
steve