SQLite Tableπ i
This library is intended to simplify the process of transforming
a list-of-lists or list-of-vectors or list-of-sequences representation
of data into a SQLite table, and then to perform database operations
like SELECT and JOIN on them.
If the operation you’re looking for is missing, you can always sidestep
the library and operate on the generated tables directly using the db
library.
Note on storage: This library stores all of its permanent tables in
"/tmp/racket-tables.sqlite". That’s just hard-coded. Yikes!
data
#:permanentpermanent
#:use-existinguse-existing?) →
table?
column-labels:(list-ofstring?)
data:(sequence/c(sequence/cany/c))
permanent:permanent?
use-existing?:boolean?
Creates a sqlite table from the given data, using the given column labels.
If a string is supplied as the #:permanent argument, the table
is created as a permanent table, in the library’s storage file. If the
symbol '_ is provided as a permanent table name, it’s created
as a permanent table with a name that is a number randomly chosen between
0 and 10000000000.0. If no #:permanent argument is supplied,
the table is created as a temporary table with a name assigned from a
sequential pool.
If the #:use-existing argument is present and not #f, then
the call will simply return an existing table. This is useful if, for instance,
your code is written in a declarative way, and you don’t want the program to
re-generate every table every time it’s run. Please note that no checking is
performed, so if the inputs to the make-table call change, the use of
#:use-existing could lead to bugs.
'(#("bob"38)
#("annie"49)
#("bob"612)))
Given a table name, return
the table. Names matching the regular expression
#px"^temp_[0-9]+$" are looked up as temporary tables.
Returns the number of
rows in a table.
cols
#:wherewhere-constraints
#:group-bygroup-by-columns)
→(sequence/c(vectorfany/c))
cols:(listofcolspec?)
where-constraints:(listofwhere-clause?)
group-by-columns:(listofsymbol?)
Given a table and a list of column
specifications (including aggregate specifications such as
'(count)), perform a SELECT. There is very limited support
for WHERE constraints, specifically using the <, <=, and
= operators, and for GROUP BY, allowing the naming of columns
on which to group.
Here’s an example, in the form of a pair of test cases:
(definet1
(list(list345"p")
(list8872"q")
(list1882"q")
(list1872"q"))))
'(#(187)
#(34)
#(887)))
'(#(4)
#(87)))
table-b
join-cols
#:permanentpermanent
#:use-existinguse-existing?) →
table?
join-cols:(listofsymbol?)
permanent:permanent?
use-existing?:boolean?
Creates a new table (actually a VIEW) by performing an inner-join on
the two tables, using the specified columns. The
#:permanent
and
#:use-existing arguments are treated as they are in
make-table .
Given
a table and a column name, produces a sequence of the unique values appearing in
that column of the table. This is useful in producing grouped results. For example:
(table-select table'(student-id)#:where`((=team-name,team))))
Determines whether a value is a table.
Currently, tables are represented simply as strings, specifically the name
of the table in the database.
Undocumented functions:
(contract-out [make-table-from-select
(->* (table? (listof colspec?))
#:group-by (listof symbol?)
[in-table-column (-> table?
[table-ref (-> table? symbol? symbol? any/c
[table-ref1 (->* (table? symbol? symbol? any/c)
[natural-join (->* (table? table?)
[back-door/rows (-> string? boolean? any/c)]))