SQLite Table

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!

procedure
(make-table column-labels
data
#:permanent permanent
#:use-existing use-existing?) → table?
  column-labels : (list-of string?)
  data : (sequence/c (sequence/c any/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.

Example of make-table’s use:

(make-table '(student a b)
            '(#("bob" 3 8)
              #("annie" 4 9)
              #("bob" 6 12)))

procedure
(find-table name) → table?
name : string?

Given a table name, return the table. Names matching the regular expression #px"^temp_[0-9]+$" are looked up as temporary tables.

procedure
(table-size table) → natural?
table : table?

Returns the number of rows in a table.

procedure
(table-select table
cols
#:where where-constraints
#:group-by group-by-columns)
→ (sequence/c (vectorf any/c))
  table : table?
  cols : (listof colspec?)
  where-constraints : (listof where-clause?)
  group-by-columns : (listof symbol?)

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:

(define t1
  (make-table '(a b zagbar quux)
              (list (list 3 4 5 "p")
                    (list 8 87 2 "q")
                    (list 1 88 2 "q")
                    (list 1 87 2 "q"))))

(check-equal? (table-select t1 '(a (min b)) #:group-by '(a))
              '(#(1 87)
                #(3 4)
                #(8 87)))

(check-equal? (table-select t1 '(b) #:where '((< 2 a)))
              '(#(4)
                #(87)))

procedure
(inner-join table-a
table-b
join-cols
#:permanent permanent
#:use-existing use-existing?) → table?
  table-a : table?
  table-b : table?
  join-cols : (listof symbol?)
  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.

procedure
(in-table-column table column) → (sequence/c any/c)
table : table?
column : symbol?

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:

(for/list ([team (in-table-column table 'team)])
(table-select table '(student-id) #:where `((= team-name ,team))))

procedure
(table? t) → boolean?
t : any/c

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:

(provide

(contract-out [make-table-from-select

(->* (table? (listof colspec?))

(#:where any/c

#:group-by (listof symbol?)

#:permanent permanent?

#:use-existing boolean?)

table?)]

[in-table-column (-> table?

symbol?

(sequence/c any/c))]

[table-ref (-> table? symbol? symbol? any/c

(sequence/c any/c))]

[table-ref1 (->* (table? symbol? symbol? any/c)

(any/c)

any/c)]

[natural-join (->* (table? table?)

(#:permanent permanent?

#:use-existing boolean?)

table?)]

[back-door/rows (-> string? boolean? any/c)]))