JDB/Methods/Records
JDB: Layout Columns | Queries Σ | API: Structural Records | Client/Server | Implementation | Examples
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 | +---+-----+----+-----------+