3 Table Queries🔗ℹ

3.1 Select🔗ℹ

The columns value defines the resulting projection and is either a list of column names to include, or #t which returns all rows.

The from value (the only required value) is the source table to query.

The where value is a procedure that takes a number of value/c values and returns a boolean?. The number of arguments is equal to the number of columns in the table and are provided in-order. This means that even the columns projects a subset of columns all the original columns are available to the filter function.

The order-by value is either the name of a column or a pair of column name and order-by-direction/c that determines the sort operation to apply to the results.

The limit value provides a maximum number of rows to return in the results.

The offset value determines the number of rows that will be excluded in the results.

The as-table-name value is used as the name of the resulting table.

Example 1 - select all

In this case the SQL ’*’ is represented as the value #t as the value for columns. As this is the default value for the parameter it can simply be ommited.

Note that the from parameter is always required.

(select #:from names-list)

Example 2 - projection

Projection, the alteration of the table structure, can be achieved by providing a list of column-name/cs.

(select '(name code-point) #:from names-list)

Example 3 - selection

Selection, the filtering of the table rows, is achieved by providing a filter predicate. It is called with the values of the row as individual parameters and will ignore the row if the predicate returns #f.

Note that the parameters to the predicate are the complete, pre-projection, list in the same order.

(select '(name code-point)

#:from names-list

#:where (λ (cp . _) (< cp 256)))

Example 4 - sorting

Sorting the projection and selection results is simple, adding an order-by clause.

Right now the implementation only supports sorting by a single column.

(select '(name code-point)

#:from names-list

#:order-by 'name)

Example 5 - sorting with explicit ordering

The need to sort by either ascending or descending order is similar except that the select procedure now takes a pair rather than a single column-name/c.

(select '(name code-point)

#:from names-list

#:order-by '(name . desc))

Example 6 - create table as

In SQL the results of a select statement are a temporary value that has to be explicitly converted to be treated as a table.

Scone uses the same table structure for stored tables and in-memory values; therefore, the results of a select procedure are already a table. To provide a meaningful name for the table the #:as keyword sets the name property of the result’s table definition.

(select '(name code-point)

#:from names-list

#:order-by '(name . desc)

#:as 'ascii)

Example 7 - paginated results

The offset (where to start) and limit (number of rows) together are useful in producing pages of results.

(define page-limit 25)

(define (page-offset page-num) (* (sub1 page-num) page-size))

(select '(name code-point)

#:from names-list

#:order-by '(name . desc)

#:limit page-limit

#:offset (page-offset 3))

3.2 Pivot🔗ℹ

3.3 Describe🔗ℹ

Example

> (define names-list           (read-table-from-file "./tests/names-list.scone"))

> (describe names-list)

CREATE EXTERNAL TABLE unnamed_1 (     code_point number,     name string,     syntax symbol,     decomposition LISTOF number,     aliases LISTOF string,     formal_aliases LISTOF string,     xref LISTOF string, ) STORED AS scone WITH HEADER ROW;

3.4 Query Contracts🔗ℹ

3.5 Query Defaults🔗ℹ