Dataphor - All tables are typed variables

Everything you work with in D4, every object you define, is a variable.
And since everything is a variable it must typed. Understanding variables
and their types is fundamental to working in the D4 language. There are
5 types: Scalar (like integers and strings), Row, Table, List, and Cursor.
Working with types is not hard. It's all logic and quite simple at that.

Table types

It seems many people have a hard time understanding a table as a variable.
But once you wrap your head around it you'll realize it's just common sense
applied to a database. All we're doing is what you do with a number.

This declares the variable named MyNum to be of type integer.

var MyNum:Integer;

This assigns the number 1 to the variable. So now we can work with MyNum
and its current value of 1.

MyNum:=1;

Why shouldn't we be able to do the same thing with a table?
For example:

var MyLTable:table {ID:Integer,Y:String } ;

We have just defined the variable MyLTable to be a type of table
and specifically a table with column ID of type Integer and column
Y of type String. The '{ID:Integer,Y:String }' is the the heading
and is all that is necessary to define this particular table type.

Just as we assigned the value 1 to the integer variable MyNum we
can assign values to the table variable MyLTable.

The values we assign to a table are 'rows'. So we use the row type
for the assignment.

Suppose we try this:

var MyLTable:table {ID:Integer,Y:String } ;
MyLTable := row { 1 ID,'A' Y };

But this won't work. We are attempting to assign a type row to a
table type. This is analogous to trying to assign a string to a variable
of integer. The two types are incompatible and the assignment wouldn't
work.

var MyNum:Integer;
MyNum:='A';         //This is a type mismatch and will throw an error.

It only makes sense that we can assign a table to a variable of a table
type (and of the same type).
(Of course we can 'insert' into a table which we'll see see later and
'update' a table). This batch of 3 statements shows a type of table
with rows defined with the same heading (column names and data types)
as variable MyLTable on the right of the := that we can assign to MyLTable.
Even the table on the right side of the := is a table variable.

var MyLTable:table {ID:Integer,Y:String } ;
MyLTable := table
                 {
                  row { 1 ID,'A' Y },
                  row { 2 ID,'B' Y }
                  };
select MyLTable;

ID Y
-- -
1  A
2  B

Note that if we want to assign a variable a row value and not a table we
simply define the variable as a row type (with a heading like the heading
required for a table).

var MyLRow:row {ID:Integer,Y:String } ;
MyLRow := row { 1 ID,'A' Y };
select MyLRow;

ID Y
-- -
1  A

The variables defined so far are memory variables. They exist only for
the duration of the batch they are in. Persisted tables can, of course,
be defined. Dataphor uses the concept of a device. A device is an sql
database that Dataphor uses to store table data. For example Sql Server,
Oracle or DB2.
The 'create table' statement is used to persist data in an sql database.
For example, the table SqlServerTable1 uses an Sql Server 2005 database
as data storage.

create table SqlServerTable1
 {
  ID:Integer,
  Y:String,
  key{ID}
 };
 
The SqlServerTable1 table, like the MyLTable table, is a variable. Every
table defined, regardless of where the data is stored, is a table 'variable'.
Also note that only the columns and their data type determine the type of
the table. Other information like keys are not part of the type definition.

Lets assign (persisted) rows to SqlServerTable1.

SqlServerTable1:= table{row{1 ID,'A' Y},row{2 ID,'B' Y}};
select SqlServerTable1; 

ID Y
-- -
1  A
2  B

Lets redefine the definition of the SqlServerTable1 table (variable).

SqlServerTable1:= table{row{3 ID,'A' Y},row{4 ID,'B' Y}};
select SqlServerTable1;

ID Y
-- -
3  A
4  B

Changing the assignment of the table is no different than changing
the assignment of a number to a variable.

var MyNum:Integer;
MyNum:=1;
select MyNum; //1
MyNum:=2;
select MyNum;
2

Always remember that when I refer to a table in D4 it is a table variable.

There are wonderful benefits to this concept. For example, just as
two integer type variables can be compared so too can two table variables.
But you must think in terms of types. When is such a comparison appropriate,
when does it make sense? It only makes sense when the two tables are of the
same type. Just as it only makes sense to compare two integer variables.
Two tables are equal if they have the same columns and corresponding data
types and have the same set of rows (values).

For example, the below select statement displays the rows of MyLTable
since the comparison of tables in the where statement is true (for each row
of MyTable). The two tables are of the same type ({ID:Integer,Y:String})
and have the same rows (values).

var MyLTable:table {ID:Integer,Y:String} ;
MyLTable := table
                 {
                  row {1 ID,'A' Y},
                  row {2 ID,'B' Y}
                  };
var MyLTable1:table {ID:Integer,Y:String} ;
MyLTable1 := table
                 {
                  row {1 ID,'A' Y},
                  row {2 ID,'B' Y}
                  };                 
select MyLTable
 where MyLTable=MyLTable1; //MyLTable=MyLTable1 is true.

ID Y
-- -
1  A
2  B 

Here the two tables are of the same type but do not have the same rows. The
where statement evaluates to false for each row of MyLTable and no rows
are selected.

var MyLTable:table {ID:Integer,Y:String } ;
MyLTable := table
                 {
                  row {1 ID,'A' Y},
                  row {2 ID,'B' Y}
                  };
var MyLTable1:table {ID:Integer,Y:String } ;
MyLTable1 := table { row {1 ID,'A' Y} };                 
select MyLTable
 where MyLTable=MyLTable1; MyLTable=MyLTable1 is false.

ID Y
-- -


Now look at this comparison.

var MyLTable:table {ID:Integer,Y:String } ;
MyLTable := table
                 {
                  row {1 ID,'A' Y},
                  row {2 ID,'B' Y}
                  };
var MyLTable1:table {ID:Integer,Z:String } ;
MyLTable1 := table
                 {
                  row {1 ID,'A' Z},
                  row {2 ID,'B' Z}
                  };                 
select MyLTable
 where MyLTable=MyLTable1; //An invalid comparison, two different table types.
                           //The compiler will raise an error because of the
                           //type 'mismatch'.

Here the two tables are of different type:
{ID:Integer,Y:String} vs.{ID:Integer,Z:String}
One table has a column Y the other a column Z so the comparison
itself is invalid. In other words, it's simply not 'logical' to
compare two different types!

But again we're working with table variables, not a table that is a
file (sql) or a table that is a value (literal). A variable can be
changed which means its type can be changed. One way to change the
type of a table is simply to change a column name. Here we make
the table comparison valid by changing the type of table MyLTable1
to the type of table MyLTable by renaming column Z to Y.

var MyLTable:table {ID:Integer,Y:String} ;
MyLTable := table
                 {
                  row {1 ID,'A' Y},
                  row {2 ID,'B' Y}
                  };
var MyLTable1:table {ID:Integer,Z:String} ;
MyLTable1 := table
                 {
                  row {1 ID,'A' Z},
                  row {2 ID,'B' Z}
                  } ;                 
select MyLTable
 where MyLTable=(MyLTable1 rename {Z Y}); //Now we can compare the tables
                                          //because they are the same type
                                          //(Same column names and data types).

ID Y
-- -
1  A
2  B


Inserting into a table

Previously we created table SqlServerTable1 as a persisted table where
the data is stored in Sql Server. We left it with 2 rows.

select SqlServerTable1;

ID Y
-- -
3  A
4  B

We can of course insert into the table. Here we insert a row.

insert row {10 ID, 'C' Y} into SqlServerTable1;

Inserting a row makes perfect sense. What about inserting multiple rows?

insert
       row {11 ID,'D' Y},
       row {12 ID,'E' Y}
                  into SqlServerTable1;

This does not work. We can insert A row into a table. If we want to insert
multiple rows we can insert each row separately.

insert row {11 ID,'D' Y} into SqlServerTable1;       
insert row {12 ID,'E' Y} into SqlServerTable1;

If we want to insert consecutive rows with a single insert we have to
use the appropriate type to insert into the table. And that type is
a table. So we can insert one table into another.

insert
  table
      {
       row {11 ID,'D' Y},
       row {12 ID,'E' Y}
      }
                  into SqlServerTable1;

Not only do you have to think in terms of types but of the relationship
between types. It is types, their relationships and variables that are
fundamental to a 'relational' database and distinguish D4 from an sql
database, a non-relational database.

The data types of a column of a table

Each column in a tables heading has to have a corresponding data type. This
is usually a Dataphor provided system scalar type. Dataphor provides the
following scalar data types: Decimal, Long, Integer, Short, Byte, Boolean,
String, TimeSpan, DateTime, Date, Time, Money and Binary. But a column type
need not be restricted to these 'scalar' types. For example a 'row' type and
'list' type are as logical as a scalar type. Any 'type' supported by Dataphor
should logically be avaliable for the type of a column. It's that simple :)

Because Sql Server has no support for a 'list' type or 'row' type we
can't create a persisted table in the sql database with these types. But
we can create a persisted table in Dataphor known as a 'session' table.
A session table is like a temporary table (#) in Sql Server. It will
disappear when the session ends. (There are ways to persist such types
in an sql database but that is beyond the scope of this article ☻ ).

Consider session table MySTable with a list and row type and an appropriate
insert statement for a inserting a row.

create session table MySTable
{
  A:Integer,
  B:String,
  LList:list(String),
  LRow:row{X:Integer,Y:String},
  key{A}
}; 
insert row{1 A, 'A1' B, {'J','K','L'} LList, row{10 X,'R1' Y} LRow} into MySTable;

We can extend the idea of the LRow column by using a row type for the columns
of LRow. LRow can be a row consisting of columns which are themselves rows
(we can nest the row type).

create session table MySTable1
{
  A:Integer,
  B:String,
  LRow:row{ R1:row{X1:Integer,Y1:String} , R2:row{X2:Integer,Y2:String} },
  key{A}
}; 
insert row { 1 A,'A1' B,row{ row{10 X1,'S1' Y1} R1,row{20 X2,'S2' Y2} R2 } LRow }
                              into MySTable1;


(I leave the significant benefits of working with tables that contain list
 and row types and just how to work with them for other articles and future
 articles).

The fact that all tables are typed and are variables allows a table to
be passed as a parameter to a procedure just as you would pass a variable
of type integer. For further info on this see:
http://beyondsql.blogspot.com/2007/06/dataphor-13-passing-table-as-parameter.html
http://beyondsql.blogspot.com/2007/08/dataphor-creating-super-function.html