Morsel

8.12

Morsel🔗ℹ

Ryan Kramer

Morsel implements the core logic of Plisqin. If you are looking for a ready-to-use alternative to SQL, look at Plisqin first. If Plisqin isn’t quite right for you, maybe Morsel could be used to create a similar library (a few things that are currently private would have to be made public).

Morsel is very generic and makes as few assumptions as possible. Arguments are rarely validated and almost nothing is considered an error.

1 Definitions🔗ℹ

A query is the value returned by (from ....). It is recognized by the query? predicate.

A join is the value returned by (join ....). It is recognized by the join? predicate.

A base query is a query or a join. It is recognized by the base-query? predicate.

A queryable is a value that represents the data source (such as a table or a view in an SQL database) that is being queried by from or join. A queryable is allowed to be any/c. In the following example, the queryable is "Album".

A tuple is a value that is instantiated by from and join. Nothing else can instantiate a tuple. It is an abstract concept and mostly opaque, but it is meant to refer to the queryable. In the following example, the tuple is the-album.

A clause can be imprecisely defined as an expression inside from or join that contributes to its content. (A more precise definition will come later.) The following example has three clauses.

(from the-album "Album"
      ; These three expressions are clauses:
      (where the-album".ReleaseYear = 1973")
      (select the-album".ArtistName")
      (select the-album".AlbumName"))

2 Queries🔗ℹ

syntax
(from tuple-id queryable-expr body ...)

body = (define (proc-id proc-stuff ...) proc-body ...)
| (define val-id val-expr)
| (attach join-id join-queryable join-body ...)
| clause-expr

Creates a query and binds tuple-id as a tuple within body.

Resolving the Queryable

If queryable-expr is a query?, then the new query inherits the queryable and the clauses of the existing query. This is called appending to a query:

> (define 2clause
    (from x "X"
          "one"
          "two"))
> (define 3clause
    ; 3clause appends to 2clause
    (from x 2clause
          "three"))
> 3clause
(from x/0 "X" "one" "two" "three")

Else if queryable-expr is an instance of gen:queryable, the resolution process restarts using the value returned by unwrap-queryable in place of queryable-expr.

Else the value of queryable-expr becomes this query’s queryable.

Special Forms

Using define within body binds proc-id or val-id in the remainder of body. It defines a procedure or a value much like Racket’s built-in define:

> (from x "X"
        (define (foo a)
          (list "hello" a))
        (define bar 42)
        (foo bar)
        (foo "world"))
(from x/0 "X" '("hello" 42) '("hello" "world"))

Using attach creates an attached join. See documentation on attach for more information.

Clauses

If a body does not match one of the special forms, it is a clause and its value is handled according to the following rules.

If the clause is void? it is discarded, as the following example demonstrates:

> (equal? (from x "X"
                (when #f "nope")
                (when #t "yep"))
          (from x "X"
                "yep"))
#t

If the clause is a clause<%>, then its clause-apply method is invoked. This allows a custom clause to make any arbitrary modification to the query’s content. (For now, these are private and undocumented but could be made public if needed: clause<%> clause? property? make-property content? content-set content-cons content-ref.)

Otherwise the clause is simply added to the query’s list of clauses.

3 Joins🔗ℹ

syntax
(join tuple-id queryable-expr maybe-to body ...)

maybe-to =
| #:to link-expr

body = (define (proc-id proc-stuff ...) proc-body ...)
| (define val-id val-expr)
| (attach join-id join-queryable join-body ...)
| clause-expr

Like from except that

It creates a join instead of a query.
Appending occurs when queryable-expr is a join? instead of a query?.

If link-expr is given, its value should satisfy (or/c tuple? join?). This enables the SQL renderer to find the "join target", which is the base query in whose scope this join belongs. For now, join-target is private and undocumented, but could be made public. It follows the links through tuples and "simple joins" (also undocumented) to reach the last link in the chain, which should be a query or non-simple join.

syntax
(attach tuple-id queryable-expr body ...)

Creates a join, links it #:to the enclosing base query, and binds tuple-id to that join in the body of the enclosing base query.

(from x "X"
      (attach y "Y"
              (join-on y".Foo = "x".Foo"))
      (select y".Bar"))
; is *almost* equal to
(from x "X"
      (define y
        (join y "Y"
              #:to x
              (join-on y".Foo = "x".Foo")))
      (select y".Bar"))

Note that by default, any use of attach is an error. It is the from and join macros that have special handling for attach.

In the previous example, I said that (attach y ....) is *almost* equal to (define y (join y ....)). The difference is that attach immediately adds the join to the enclosing query, whereas define does not. The following example shows the difference:

> (define (example-1)
    (from x "X"
          (attach y "Y"
                  (join-on y".Foo = "x".Foo"))))
> (define (example-2)
    (from x "X"
          ; y is defined but never used
          (define y
            (join y "Y"
                  #:to x
                  (join-on y".Foo = "x".Foo")))))
> (example-1)
(from x/0 "X" (attach y/1 "Y" (join-on y/1 ".Foo = " x/0 ".Foo")))
> (example-2)
(from x/0 "X")

4 Reference🔗ℹ

procedure
(query? v) → boolean?
v : any/c

A predicate that recognizes queries.

> (query? (from x "X"))
#t
> (query? (join x "X"))
#f

procedure
(join? v) → boolean?
v : any/c

A predicate that recognizes joins.

> (join? (join x "X"))
#t
> (join? (from x "X"))
#f

procedure
(base-query? v) → boolean?
v : any/c

A predicate equivalent to (or/c query? join?).

> (base-query? (from x "X"))
#t
> (base-query? (join x "X"))
#t

procedure
(tuple? v) → boolean?
v : any/c

A predicate that recognizes tuples.

> (from x "X" (tuple? x))
(from x/0 "X" #t)

procedure
(token? v) → boolean?
v : any/c

A predicate equivalent to (is-a?/c token<%>).

interface
token<%> : interface?

method
(send a-token token-kind) → symbol?
The SQL renderer uses this to decide what kind of clause each is:
> (send (select) token-kind)
'select
> (send (group-by) token-kind)
'group-by
Certain token kinds are also used by some core logic, but for now this logic is private and only consumed by the SQL renderer.
method
(send a-token token-content) → any/c
Allows Morsel to explore deeper into an object graph. When Morsel encounters a token<%> it calls this method and continues exploring whatever value is returned. Morsel knows how to explore queries, joins, pairs, and tokens.
method
(send a-token set-token-aspect! key value) → any/c
  key : any/c
  value : any/c
You can use define-token-aspect-stuff to implement this.
Used to cache the results of potentially expensive computations.
method
(send a-token get-token-aspect key
not-found-value) → any/c
  key : any/c
  not-found-value : any/c
Use define-token-aspect-stuff to implement this.

syntax
(define-token-aspect-stuff)

Should be used inside a class* definition that implements token<%>. Creates definitions for the get-token-aspect and set-token-aspect! methods.

procedure
(queryable? x) → any/c
x : any/c

A predicate that recognizes instances of gen:queryable.

value
gen:queryable : any/c

A generic interface that defines an unwrap-queryable method.

> (struct my-queryable (item) #:transparent
    #:methods gen:queryable
    [(define (unwrap-queryable me)
       (displayln (format "unwrapping ~v" me))
       (my-queryable-item me))])
> (get-queryable (my-queryable (my-queryable "hello")))
unwrapping (my-queryable (my-queryable "hello"))
unwrapping (my-queryable "hello")
"hello"
> (from x (my-queryable 'world))
unwrapping (my-queryable 'world)
(from x/0 'world)

procedure
(get-queryable x) → any/c
x : any/c

Fully unwraps a queryable.

If x is an instance of gen:queryable, then (get-queryable (unwrap-queryable x)) is returned.
Else if (or (base-query? x) (tuple? x)), then its queryable is returned.
Else x is returned.

> (get-queryable (from a (from b "Here is the queryable")))
"Here is the queryable"
> (get-queryable 'nothing-special)
'nothing-special
> (from a "ABC" 1 2 3 (get-queryable a))
(from a/0 "ABC" 1 2 3 "ABC")

5 Caveats🔗ℹ

5.1 Laziness🔗ℹ

The body of from and join is evaluated lazily, as the following example demonstrates:

> (define counter 0)
> (define the-query (from x "X"
(set! counter (add1 counter))
"hello"))
; counter is still zero
> counter
0
; printing the-query forces evaluation
> the-query
(from x/0 "X" "hello")
; now the counter has been incremented
> counter
1

However, this laziness is not guaranteed. Morsel is currently only well-defined for "purely functional" code.

5.2 Premature Tuple Access🔗ℹ

Every tuple contains a reference back to the base query that created it. This is an implementation detail. However, this creates a cycle in the data structure. Prematurely attempting to access the base query via the tuple could cause an infinite loop. If this situation is detected, an exception is raised instead:

> (from x "X"
(attach y "Y"
(equal? x y)))
Infinite loop averted. Likely cause: an equal or hash
operation on a tuple.

6 morsel-lib/sql🔗ℹ

(require morsel-lib/sql)

package: morsel-lib

procedure
(to-sql x) → string?
x : any/c

Generates SQL

> (define (example)
    (from p "Product"
          (attach c "Category"
                  (join-on c".CategoryId = "p".ProductId"))
          (select p".ProductName")
          (select c".CategoryName")))
> (displayln (to-sql (example)))
select p.ProductName
  , c.CategoryName
from Product p
inner join Category c
   on c.CategoryId = p.ProductId

procedure
(sql-token? x) → any/c
x : any/c

A predicate that recognizes instances of sql-token<%>.

interface
sql-token<%> : interface?

implements:

token<%>

method
(send a-sql-token sql-token-reduce) → any/c
When to-sql encounters an object that implements sql-token<%>, it calls this method and continues to generate SQL using the return value in place of the object.

procedure
(select x ...) → token?
  x : any/c
procedure
(where x ...) → token?
  x : any/c
procedure
(join-on x ...) → token?
  x : any/c
procedure
(group-by x ...) → token?
  x : any/c
procedure
(having x ...) → token?
  x : any/c
procedure
(order-by x ...) → token?
  x : any/c

To be used as clauses.

procedure
(scalar x ...) → token?
  x : any/c
procedure
(aggregate x ...) → token?
  x : any/c
procedure
(bool x ...) → token?
  x : any/c
procedure
(subquery x ...) → token?
  x : any/c
procedure
(sql x ...) → token?
  x : any/c
procedure
(silence x ...) → token?
  x : any/c

To be used as fragments.

procedure
(limit v) → any/c
v : exact-positive-integer?

To be used as a clause. Limits the result set to at most v rows.

> (define q (from x "X"
(limit 42)))
> (parameterize ([current-dialect (sqlite)])
(displayln (to-sql q)))
select x.*
from X x
limit 42

procedure
(offset v) → any/c
v : exact-positive-integer?

To be used as a clause. Skips past the first v rows of the result set.

> (define q (from x "X"
(offset 100)))
> (parameterize ([current-dialect (sqlite)])
(displayln (to-sql q)))
select x.*
from X x
offset 100

procedure
(distinct v) → any/c
v : any/c

To be used as a clause. Holds a boolean flag which controls whether the select list is rendered with "distinct" (default is #f).

> (displayln (to-sql (from x "X"
(distinct #t))))
select distinct x.*
from X x
> (displayln (to-sql (from x "X"
(distinct #f))))
select x.*
from X x

procedure
(join-type v) → any/c
v : (or/c #f 'inner 'left)

To be used as a clause within join or attach.

> (define q
    (from x "X"
          (attach y "Y"
                  (join-type 'left)
                  (join-on y".YID = "x".YID"))))
> (displayln (to-sql q))
select x.*
from X x
left join Y y
   on y.YID = x.YID

The default join type is #f. This means that the join will be an 'inner join unless its join-on clauses contain a 'left join, in which case it becomes a 'left join.

> (define (q jtype)
    (from x "X"
          (attach y "Y"
                  (join-type jtype)
                  (join-on y".YID = "x".YID"))
          (attach z "Z"
                  ; y appears in z's join-on clauses.
                  ; Because z's join-type is #f, it will become
                  ; a left join if y is a left join.
                  (join-on z".ZID = "y".ZID"))))
> (displayln (to-sql (q 'inner)))
select x.*
from X x
inner join Y y
   on y.YID = x.YID
inner join Z z
   on z.ZID = y.ZID
> (displayln (to-sql (q 'left)))
select x.*
from X x
left join Y y
   on y.YID = x.YID
left join Z z
   on z.ZID = y.ZID

7 morsel-lib/sql/dialect🔗ℹ

(require morsel-lib/sql/dialect)

package: morsel-lib

parameter
(current-dialect) → dialect?
(current-dialect dialect) → void?
dialect : dialect?
= #f

A parameter representing the SQL dialect that to-sql should generate.

> (define q
    (from x "X"
          (limit 10)))
> (parameterize ([current-dialect (postgres)])
    (displayln (to-sql q)))
select x.*
from X x
limit 10
> (parameterize ([current-dialect (mssql)])
    (displayln (to-sql q)))
select top 10 x.*
from X x

procedure
(dialect? x) → any/c
x : any/c

A predicate that recognizes dialects.

procedure
(mssql) → dialect?
procedure
(postgres) → dialect?
procedure
(sqlite) → dialect?

Creates a dialect representing Microsoft SQL Server, PostgreSQL, or SQLite.

procedure
(mssql? x) → any/c
  x : any/c
procedure
(postgres? x) → any/c
  x : any/c
procedure
(sqlite? x) → any/c
  x : any/c

Tests whether x is a dialect? of a certain kind.

> (mssql? (mssql))
#t
> (mssql? (postgres))
#f
> (postgres? (postgres))
#t

1	Definitions
2	Queries
3	Joins
4	Reference
5	Caveats
6	morsel-lib/ sql
7	morsel-lib/ sql/ dialect