JDB/Methods/Records

Specifying Records and Columns

Where required, records are specified as one of:

• sql query expression

• * meaning all records

Columns are specified as any combination of:

• column names

• table.column names

• * meaning all columns in the current table, except autoid, tranid

• ** meaning all columns readable from the current table, except autoid, tranid and any duplicates (i.e. not both a referencing foreign key and its referenced foreign key)

• *+ as ** but restricted to key columns only

Columns must be in the current table or a referenced table (reachable from the current table).

Order is a comma-delimited list of column names from the result, each optionally followed by desc for descending.

Change Methods

Methods that change values are wrapped in transactions. Several records and tables may be updated in a single transaction. On any failure, all changes in that transaction are rolled back.

The main method is Alter which takes a list of updates, each item of which is a triple:

method;table;values

where:

• method is one of insert, update, delete
• table is the table to be updated
• values are as appropriate for the method

For convenience, there are corresponding cover functions, Insert, Delete, Update that perform a single transaction on a single table.

For example, if locD is the database locale and sp is a table, then the following are equivalent and each performs a single update:

Alter__locD <'insert';'sp';<'S5';'P1';300
Insert__locD 'sp';<'S5';'P1';300

Insert

Insert new records. The values are a boxed list with one item per column, and each item must have the same length. Autoid and tranid columns should not be given. For example, in the sed database:

   Insert__locD 'r';<7;'Berlin';3000
   Reads__locD 'from r'
+---+------+-----+
|rno|name  |popul|
+---+------+-----+
|4  |Naples|1000 |
|5  |Rome  |2000 |
|6  |Naples|2000 |
|7  |Berlin|3000 |
+---+------+-----+

Where a column references a table with more than one unique column, values for that column are given as a boxed list. The values are then replaced with the corresponding autoid:

   Insert__locD 's';<36;'Auto';(6;'Naples');'Legal'
   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  |
|36 |Auto |2   |Legal      |
|36 |Auto |3   |Legal      |
+---+-----+----+-----------+

Delete

Delete records. The argument is the table and rows to delete:

Delete__locD 's';'sno=35'

Update

Update record:

   Update__locD 's';<'Admin';'sno=34';'name'
   Reads__locD 'from s where sno=34'
+---+----+----+-----+
|sno|sdiv|rnum|name |
+---+----+----+-----+
|34 |Auto|3   |Admin|
+---+----+----+-----+

Read Methods

There are two read methods, to read raw data or formatted data:

Read

Read raw data. Returns a two-column table of name;value pairs:

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

Reads

Read formatted, as a two-row table of names on values in columns:

   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  |
+---+-----+----+-----------+