3 Table Queries
(require scone/query) | package: scone |
3.1 Select
procedure
(select columns #:from from [ #:where where #:order-by order-by #:limit limit #:offset offset #:as as-table-name]) → table? columns : projection-clause/c from : from-clause/c where : selection-clause/c = #f order-by : order-by-clause/c = #f limit : limit-clause/c = #f offset : offset-clause/c = 0 as-table-name : as-table-clause/c = #f
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.
SELECT * FROM names_list; |
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; |
(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.
SELECT name, code_point |
FROM names_list |
WHERE code_point < 256; |
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.
SELECT name, code_point |
FROM names_list |
ORDER BY name; |
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; |
(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.
CREATE TABLE ascii |
AS (SELECT name, code_point |
FROM names_list |
ORDER BY name DESC); |
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.
SELECT name, code_point |
FROM names_list |
ORDER BY name DESC |
LIMIT 25 |
OFFSET 50; |
(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
procedure
(pivot-table->columnar table) → columnar-table?
table : table?
procedure
(pivot-columnar->table table) → table?
table : columnar-table?
3.3 Describe
procedure
table : table-type/c out : output-port? = (current-output-port)
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
contract
(order-by-direction/c value) → boolean?
value : any/c
contract
(from-clause/c value) → boolean?
value : any/c
contract
(projection-clause/c value) → boolean?
value : any/c
contract
(selection-clause/c value) → boolean?
value : any/c
contract
(order-by-clause/c value) → boolean?
value : any/c
contract
(limit-clause/c value) → boolean?
value : any/c
contract
(offset-clause/c value) → boolean?
value : any/c
contract
(as-table-clause/c value) → boolean?
value : any/c
3.5 Query Defaults
parameter
(default-order-by-direction order-by-direction) → void? order-by-direction : order-by-direction/c
= 'asc