Tables and databases

The tables required for the following functions are passed to the function as a resource. The table is read and prepared internally; various fields can be read via table and field names.

The implementation of an sqlite interface for accessing a local database instance with definable search queries is being planned.

Search query "selectRow"

The selectRow() function searches sequentially through all rows in a simple table for a match with the specified keys and returns the row index found. If no match is found, -1 is returned. A search is only carried out if at least one of the requested key values changes.

This row index can be used in getField() to read any values from the data fields of the table.

m1 = selectRow(key1, ..., { table: <str> // mandatory
                           , colSep: <regex>
                           , anyKey: <var>
                           , key: <str> // mandatory (*)
                           , keys: [<str>] // mandatory (*)
                           });
// (*) exactly one of key or keys must be defined

Property	Value	Description
table	`<str>`	Required: Name of a text resource that defines the content of the table. The data elements are interpreted as variants and therefore provide the best possible data type. Floating point numbers are to be written with a point as a decimal separator.
colSep	`<regex>`	Regular Expression, which defines the column separators. Default: `"[,;\|\t]"`, universally usable for a single character specified in `[]`.
anyKey	`<var>`	A data element of this value returns a positive result for any key value, default: `"*"`
key	`<str>`	Required (, ): A data field of the table in which the first and only key value key1* is searched for.
keys	`[<str>]`	Required (, ): A vector with several data fields that are to be applied in the specified order to the key values key1, key2, key3*, ... are to be applied.

(*) Exactly one of the properties key or keys must be used to select the key column(s).

(**) By default, an exact match of the key value with the entries in the table column is always searched for. The identifier of the field can optionally be preceded by the following comparison operators to modify the query; spaces may be inserted between the operator and the identifier:

Operator	Meaning	Example	Execution as...
		`KEY` is a field identifier in `keys:[...]`	`keyN` is the key value passed as a parameter
`==`	Exact match (default)	`'KEY'` `'==KEY'`	`keyN == Table[KEY]`
`*=`	for `<str>`: contains	`*=KEY`	`keyN contains Table[KEY]`
`&=`	for `<uint>\|<int>`: all bits of the mask set	`'&=KEY'`	`bAnd(keyN, Table[KEY]) == Table[KEY]`
`&>`	for `<uint>\|<int>`: at least one bit of the mask set	`'&>KEY'`	`bAnd(keyN, Table[KEY]) > 0`
`&0`	for `<uint>\|<int>`: no bit of the mask set	`'&0 KEY'`	`bAnd(keyN, Table[KEY]) == 0`
`<=`	Less than or equal to	`'<=KEY'`	`keyN <= Table[KEY]`
`<`	Less than	`'<KEY'`	`keyN < Table[KEY]`
`>=`	Greater than or equal to	`'>=KEY'`	`keyN >= Table[KEY]`
`>`	Greater than	`'>KEY'`	`keyN < Table[KEY]`

For the data type <str>, the comparisons refer to the alphabetical order.

Formatting the table

The table is loaded from a text resource (ASCII, UTF-8)
Column separators are defined via the colSep property.
The same table can be used in several selectRow() functions. The colSep specification must be identical for all of them, as it is not specified which of the functions interprets the table first (and therefore also for all other instances).
The first line contains the field names, which are then used to define the key columns and result fields.
Blank lines are ignored.
Lines that begin with 3 identical characters from the group _ - = ~ * are interpreted as a separator and ignored.
Each data field is read and processed individually as a variant. There are therefore no fixed data types for a column.
The search is always performed sequentially through all lines.

Read table entry "getField"

The function getField() reads the associated data element from the data field field from a table table interpreted with selectRow() for the row index rowIdx found. The data type is determined by the data element as a variable.

If rowIdx < 0 or outside the valid range, the variable defined with notFound is returned.

v1 = getField(rowIdx, { table: <str> // mandatory
                      , field: <str> // mandatory
                      , notFound: <var>
                      });

Property	Value	Description
table	`<str>`	Required: Name of a text resource that defines the content of the table. This must have been interpreted and loaded in a `selectRow()` function.
field	`<str>`	Required: Name of a data field to read the result value from.
notFound	`<var>`	This variable is returned as a result if rowIdx does not denote a valid search result. Default: false.