JDB/Columns

Data is stored in columns, where each column is a mapped noun. The mapfiles are self-describing, i.e. contain the jmf header.

Hidden Columns: autoid, tranid

Each table starts with two columns, autoid and tranid:

• autoid is a sequence number starting from 0, and uniquely identifies a record. It is preserved on any update.
• tranid is the database transaction number in which the record was written or updated. It is primarily intended to support rollback and to allow a task to read a snapshot of the database.

These columns may be referenced as any other column, however for most purposes, they are hidden - they do not appear in the list of columns denoted by *, and corresponding values should not be given in updates. For example:

   Reads__locD 'from r'
+---+------+-----+
|rno|name  |popul|
+---+------+-----+
|4  |Naples|1000 |
|5  |Rome  |2000 |
|6  |Naples|2000 |
+---+------+-----+

   Reads__locD 'autoid,tranid,rno,name,popul from r'
+------+------+---+------+-----+
|autoid|tranid|rno|name  |popul|
+------+------+---+------+-----+
|1     |1     |4  |Naples|1000 |
|2     |1     |5  |Rome  |2000 |
|3     |1     |6  |Naples|2000 |
+------+------+---+------+-----+

   Insert__locD 'r';<6;'Paris';3000
   Reads__locD 'autoid,tranid,rno,name,popul from r'
+------+------+---+------+-----+
|autoid|tranid|rno|name  |popul|
+------+------+---+------+-----+
|1     |1     |4  |Naples|1000 |
|2     |1     |5  |Rome  |2000 |
|3     |1     |6  |Naples|2000 |
|4     |5     |6  |Paris |3000 |
+------+------+---+------+-----+

Names

Column names may be any character string except for autoid and tranid which are reserved, and except that blanks are not permitted. Column names are not used for file names; instead, the corresponding mapped files are named 'cX' for some integer X.

Unique Columns

One or more columns may be designated as unique, i.e. no duplicate rows for those columns in combination.

Foreign Key Columns

A column can reference another table as a foreign key. The corresponding data in the referencing column is the referenced table's autoid.

In particular:

• A referencing column may contain only values present in corresponding records of the referenced table
• Changing values in a referenced table automatically changes values returned for the referencing column.

The exact mechanism depends on the number of unique columns in the referenced table:

• if no unique columns, then the referencing column is simply the referenced tables autoid.
• if one unique column, then the referencing column is treated as if it were that unique column. This is the most common case.
• if more than one unique column (a compound key), the referencing column is read as the referenced tables autoid, while insert/update must give a list of the referenced columns.

There are three ways you can refer to columns in tables that are linked with foreign keys.

• The canonical way is a list of referencing columns in the form: col.col...col . For example, in the sed database, column popul in table r can be referenced from table t as enum.secnum.rnum.popul. Note that this method gives a unique reference.

• A column can be referenced by name only if it is unique in the database, as in column popul in the sed database.
• A column can be referenced in the form table.name.

Note that the last two references work correctly only when there is a single route from the original table to the column, i.e. when no table has two columns both referencing the same table as foreign key.

Example 1

The typical case is where a foreign key column references a single unique column in another table. For example, in the sandp database, table sp has column sid referencing s.sid, and column pid referencing p.pid:

   ShowCols__locD 'sp'
+-----+------+------+------+------+---------+
|table|column|type  |unique|parent|parentkey|
+-----+------+------+------+------+---------+
|sp   |sid   |autoid|1     |s     |sid      |
|sp   |pid   |autoid|1     |p     |pid      |
|sp   |qty   |int   |0     |      |         |
+-----+------+------+------+------+---------+

   Reads__locD 'from sp where sid=S2'
+---+---+---+
|sid|pid|qty|
+---+---+---+
|S2 |P1 |300|
|S2 |P2 |400|
+---+---+---+

Example 2

Table t in the sed example has a foreign key enum referenced to table e with no unique column. In this case, enum is table e's autoid:

   Reads__locD 'from t'
+---+----+-------+------+----+
|tno|enum|tname  |tsize |tcat|
+---+----+-------+------+----+
|11 |2   |Desk   |small |A   |
|12 |4   |Cabinet|medium|B   |
|13 |1   |Chair  |small |A   |
|14 |2   |Cabinet|medium|R   |
+---+----+-------+------+----+

Example 3

Table s in the sed example has a foreign key rnum referencing table r with two unique columns. In this case, rnum is read as table r's autoid, but must be given as a pair of values:

   Reads__locD 'from s'
+---+-----+----+-----------+
|sno|sdiv |rnum|name       |
+---+-----+----+-----------+
|31 |Auto |1   |Sales      |
|31 |Truck|3   |Clerical   |
|33 |Truck|2   |Engineering|
|34 |Auto |3   |Clerical   |
|35 |Auto |1   |Marketing  |
+---+-----+----+-----------+

   Insert__locD 's';<36;'Auto';(5;'Rome');'Legal'
   Reads__locD 'from s where sno=36'
+---+----+----+-----+
|sno|sdiv|rnum|name |
+---+----+----+-----+
|36 |Auto|2   |Legal|
+---+----+----+-----+
   Reads__locD '** from s where sno=36'
+---+----+---+------+-----+-----+
|sno|sdiv|rno|r.name|popul|name |
+---+----+---+------+-----+-----+
|36 |Auto|5  |Rome  |2000 |Legal|
+---+----+---+------+-----+-----+

Example 4

In the sed database, column popul in table r can be referenced from table t in three ways. Note that the resulting column header is the same as the reference used:

   Reads__locD '*,enum.secnum.rnum.popul from t where enum=2'   NB. canonical method
+---+----+-------+------+----+----------------------+
|tno|enum|tname  |tsize |tcat|enum.secnum.rnum.popul|
+---+----+-------+------+----+----------------------+
|11 |2   |Desk   |small |A   |2000                  |
|14 |2   |Cabinet|medium|R   |2000                  |
+---+----+-------+------+----+----------------------+
   
   Reads__locD '*,popul from t where enum=2'                    NB. popul is a unique name in sed
+---+----+-------+------+----+-----+
|tno|enum|tname  |tsize |tcat|popul|
+---+----+-------+------+----+-----+
|11 |2   |Desk   |small |A   |2000 |
|14 |2   |Cabinet|medium|R   |2000 |
+---+----+-------+------+----+-----+

   Reads__locD '*,r.popul from t where enum=2'                  NB. table.column
+---+----+-------+------+----+-------+
|tno|enum|tname  |tsize |tcat|r.popul|
+---+----+-------+------+----+-------+
|11 |2   |Desk   |small |A   |2000   |
|14 |2   |Cabinet|medium|R   |2000   |
+---+----+-------+------+----+-------+

Datatypes

Char columns are stored as character matrices. Varchar and binary columns are stored as boxed lists. Other columns are stored as open lists.