4 Reference

7.7

4 Reference

package: plisqin

syntax
(from id queryable statement ...)
syntax
(join id queryable statement ...)

statement = (define val-id val-expr)
| (define (proc-id args ...) body ...)
| (join-attach join-id rest-of-join ...)
| (join        join-id rest-of-join ...)
| statement-expr

   queryable : queryable?
   statement-expr : statement-expr?

The from and join macros are very similar in that they are both "query-building macros." Queries and joins are so similar that most of the time when I say "query" I really mean "query or join". You might say that "join is a subtype of query, except when it isn’t."

The id argument is introduced as an identifier which refers to the query being constructed. Its value is a source? and it is in scope for all the statements. It is analogous to an alias in SQL.

The queryable argument defines the initial value of the query, before any of the statements are applied. It must be a value that satisfies the queryable? contract. See the documentation on queryable? for further information.

Next come zero or more statements. Each statement may be a special form (define or join-attach), or it may be an expression.

The define statement behaves similar to Racket’s built-in define. The val-id or proc-id that is introduced remains in scope for the rest of the statements.

The join and join-attach statements do exactly the same thing. In other words, when join appears as a statement, it gets treated as if it were join-attach. (The only reason join-attach exists is to make writing this documentation easier.)

The join-attach statement does three things. First, it creates a join as (join join-id rest-of-join ...). Second, it attaches that join to the enclosing query. Third, it makes that attached join available via join-id for the rest of the statements. Read more about the difference between attached and detached joins at Attached vs Detached Joins.

If a statement does not match one of the special forms, it is a statement-expr which must satisfy the statement-expr? contract.

procedure
(to-sql token) → string?
token : sql-token?

Renders the token to an SQL string.

> (displayln (to-sql (from foo "Foo"
(select foo".Bar"))))
select
foo.Bar
from Foo foo

syntax
(def-table constructor [table-name] [default-alias] [tester?])

constructor = id

tester? = id

table-name : (or/c #f string?)
default-alias : (or/c #f string?)

Defines two procedures: constructor and tester?. The constructor takes an optional alias argument and constructs a source?:

> (def-table Foo)
> (Foo)
'(source "_foo" "Foo" -64)
> (Foo "custom-alias")
'(source "custom-alias" "Foo" -65)

The tester? tests whether its single argument is a query? or join? or source? with the correct table name:

> (def-table Foo)
> (and (Foo? (Foo))
       (Foo? (Foo "f"))
       (Foo? (from f Foo))
       (Foo? (join f Foo))
       (Foo? (join f "Foo")))
#t
> (Foo? (join f Rental))
#f

All arguments except the first are optional. If table-name is #f, it will simply convert constructor to a string. If default-alias is #f, it will prepend an underscore to the table name, and maybe lowercase the first character. These declarations are all equivalent:

(def-table MyTable)
(def-table MyTable #f)
(def-table MyTable #f #f)
(def-table MyTable #f #f MyTable?)
(def-table MyTable "MyTable" "_myTable" MyTable?)

syntax
(table constructor [table-name] [default-alias] [tester?])

Depreacted. Alias of def-table.

syntax
(field-cases (id arg)
[test-expr then-body] ...)

TODO - deprecate and redocument as def/append!.

This is kind of like cond but with some important differences. It can only be used to define a 1-argument procedure. The test-exprs are evaluated in reverse order and as soon as one returns a truthy value, the corresponding then-body is evaluated and returned. Also, if id has already been defined by field-cases, the definitions append. For example:

> (field-cases (twice x)
[(integer? x) (* x 2)])
; we can append more conditions to the existing definition of twice:
> (field-cases (twice x)
[(string? x) (format "~a ~a" x x)])
> (twice 3)
6
> (twice "pizza")
"pizza pizza"

This appending works even across files. This is to support having one file containing generated code and another containing manual overrides. Here I will override the integer? case while leaving the string? case intact:

> (field-cases (twice x)
[(integer? x)
(format "Two times ~a is ~a" x (* 2 x))])
> (twice 3)
"Two times 3 is 6"
> (twice "pizza")
"pizza pizza"

The above examples are contrived. For the most complete explanation of how field-cases is meant to be used, read Layer 1 - Schema as Procedures. For some quick but still realistic examples, look at the definitions of ItemTypeId or Item-of/s or anything from that example schema.

syntax
(attach-callstacks)

For Racketeers: this works by customizing the caller’s #%app, so I think it is accurate to say that it remains in effect for the remainder of the module.

Turns callstack tracking on. When callstack tracking is not enabled, this error message shows the values of the problematic joins, which can be hard to read:

> (aggregate
(Rentals-of/g (Item))
(Rentals-of/g (Copy)))
Aggregate expression has 2 targets. At most one target is
allowed.
Target 1 was:
'(join InnerJoin (query (source "_rental" "Rental" 1)
((GroupBy ((Scalar ((join InnerJoin (query (source "_copy"
"Copy" 2) () () #hash()) ((JoinOn ((source "_copy" "Copy" 2)
".CopyId = " (source "_rental" "Rental" 1) ".CopyId"))))
".ItemId"))))) () #has...
Target 2 was:
'(join InnerJoin (query (source "_rental" "Rental" 1)
((GroupBy ((Scalar ((source "_rental" "Rental" 1)
".CopyId"))))) () #hash()) ((JoinOn ((Scalar ((source
"_rental" "Rental" 1) ".CopyId")) " = " (Scalar ((source
"_copy" "Copy" -2) ".CopyId"))))))

The following example turns on callstack tracking, and raises the same error. I’ll also add another layer to the callstack for illustrative purposes. Now instead of showing the value, the error message shows the callstack that produced the value:

> (attach-callstacks)
> (define (foo) (Rentals-of/g (Copy)))
> (aggregate
   (Rentals-of/g (Item))
   (foo))
Aggregate expression has 2 targets. At most one target is
allowed.
Target 1 was:
  #<syntax:eval:8:0 (Rentals-of/g (Item))>
Target 2 was:
  #<syntax:eval:8:0 (foo)>
  #<syntax:eval:7:0 (Rentals-of/g (Copy))>

4.1 Fragments

A fragment is a list of tokens with a fragment-kind? attached. The fragment kind tells Plisqin what part of an SQL query it is.

procedure
(select tokens ...) → select?
  tokens : token-list?
procedure
(where tokens ...) → where?
  tokens : token-list?
procedure
(join-on tokens ...) → join-on?
  tokens : token-list?
procedure
(group-by tokens ...) → group-by?
  tokens : token-list?
procedure
(having tokens ...) → having?
  tokens : token-list?
procedure
(scalar tokens ...) → scalar?
  tokens : token-list?
procedure
(aggregate tokens ...) → aggregate?
  tokens : token-list?
procedure
(bool tokens ...) → bool?
  tokens : token-list?
procedure
(subquery tokens ...) → subquery?
  tokens : token-list?
procedure
(sql tokens ...) → sql?
  tokens : token-list?

Constructs an SQL fragment.

procedure
(order-by maybe-dir tokens ...) → order-by?
maybe-dir : (or/c 'asc 'desc sql-token? (listof sql-token?))
tokens : token-list?

A clause that controls how rows are sorted in the result set. The first argument is allowed to be 'asc or 'desc meaning "ascending" or "descending" respectively. If the sort direction is not specified, is defaults to ascending.

procedure
(select? x) → boolean?
  x : any/c
procedure
(where? x) → boolean?
  x : any/c
procedure
(join-on? x) → boolean?
  x : any/c
procedure
(group-by? x) → boolean?
  x : any/c
procedure
(order-by? x) → boolean?
  x : any/c
procedure
(having? x) → boolean?
  x : any/c
procedure
(scalar? x) → boolean?
  x : any/c
procedure
(aggregate? x) → boolean?
  x : any/c
procedure
(bool? x) → boolean?
  x : any/c
procedure
(subquery? x) → boolean?
  x : any/c
procedure
(sql? x) → boolean?
  x : any/c
procedure
(fragment? x) → boolean?
  x : any/c

Tests whether the argument is a fragment of the correct kind. If any of these returns true, then fragment? will also return true. (Fragment is the supertype.)

> (where? (where "1=1"))
#t
> (group-by? (where "1=1"))
#f
> (fragment? (where "1=1"))
#t
> (fragment? "1=1")
#f

4.2 SQL Functions

procedure
(count tokens ... [#:distinct? distinct?]) → aggregate?
  tokens : token-list?
  distinct? : any/c = #f
procedure
(avg tokens ... [#:distinct? distinct?]) → aggregate?
  tokens : token-list?
  distinct? : any/c = #f
procedure
(max tokens ... [#:distinct? distinct?]) → aggregate?
  tokens : token-list?
  distinct? : any/c = #f
procedure
(min tokens ... [#:distinct? distinct?]) → aggregate?
  tokens : token-list?
  distinct? : any/c = #f
procedure
(sum tokens ... [#:distinct? distinct?]) → aggregate?
  tokens : token-list?
  distinct? : any/c = #f

These are the aggregate functions that PostgreSQL and MS SQL Server share in common. When there are no grouped joins, they do pretty much what you would expect:

> (display
   (to-sql
    (from c "information_schema.columns"
          (select (sum c".ordinal_position")))))
select
  sum(c.ordinal_position)
from information_schema.columns c
> (display
   (to-sql
    (from c "information_schema.columns"
          (select (sum c".ordinal_position" #:distinct? #t)))))
select
  sum(distinct c.ordinal_position)
from information_schema.columns c

When there is a grouped join involved, they can cause an automatic injection:

> (define (columns-of/g t)
    (join c "information_schema.columns"
          (group-by (scalar c".table_name"))
          (join-on (scalar c".table_name")
                   " = "
                   (scalar t".table_name"))))
> (display
   (to-sql
    (from t "information_schema.tables"
          (join c (columns-of/g t))
          (select (max c".ordinal_position"))
          (select t".table_name"))))
select
  c.__INJECT2
  , t.table_name
from information_schema.tables t
inner join (
    select
      c.table_name as __INJECT1
      , max(c.ordinal_position) as __INJECT2
    from information_schema.columns c
    group by c.table_name) c
on c.__INJECT1 = t.table_name
> (display
   (to-sql
    (from t "information_schema.tables"
          (join c (columns-of/g t))
          (select (max c".ordinal_position" #:distinct? #t))
          (select t".table_name"))))
select
  c.__INJECT2
  , t.table_name
from information_schema.tables t
inner join (
    select
      c.table_name as __INJECT1
      , max(distinct c.ordinal_position) as __INJECT2
    from information_schema.columns c
    group by c.table_name) c
on c.__INJECT1 = t.table_name

The count function gets some special treatment that the others don’t. Specifically, count is allowed to accept a single token that refers to a query or a join:

> (display (to-sql (from t "information_schema.tables"
(select (count t)))))
select
count(*)
from information_schema.tables t

This special case is reported as an error for the other aggregate functions. This is because it is possible to count rows, but it is not possible to sum or average them. For example, (avg t) does not have a reasonable meaning here:

> (display (to-sql (from t "information_schema.tables"
                         (select (avg t)))))
tokens: contract violation
  expected: a scalar-looking SQL expression
  given: '(source "t" "information_schema.tables" 1)

procedure
(exists tokens ...) → bool?
tokens : token-list?

Represents the SQL "exists" function.

> (display (to-sql (from x "X"
(where (exists "select 1 as ONE")))))
select x.*
from X x
where exists (select 1 as ONE)

There is special handling for a single argument that is a query:

> (display (to-sql (from x "X"
                         (where (exists (from y "Y"))))))
select x.*
from X x
where exists (
    select y.*
    from Y y)

There is also special handling a single argument that is a join. The join gets converted to a query. The details of this conversion are documented at Join-to-Query Conversion, here is an example:

> (display (to-sql (from x "X"
                         (where (exists (join y "Y"
                                              (join-on y".foo = "x".bar")))))))
select x.*
from X x
where exists (
    select y.*
    from Y y
    where y.foo = x.bar)

4.3 Data Model

value
fragment-kind? : contract?

(or/c 'Select
      'Where
      'JoinOn
      'GroupBy
      'OrderBy
      'Having
      'Scalar
      'Aggregate
      'Bool
      'Subquery
      'Sql
      'Silence)

value
join-type? : contract?

(or/c 'InnerJoin
      'LeftJoin
      'CrossApply
      'OuterApply)

value
sql-token? : contract?

The smallest part of SQL that Plisqin is concerned with. Tokens are typically collected into fragments.

(or/c token?
      string?
      number?
      'db-now
      'concat)

value
token-list? : contract?

A list x such that (flatten x) satisfies the contract (listof sql-token?). When token-list? is used with define/contract it will automatically perform the flattening. This is most commonly used with rest arguments, like this:

> (require racket)
> (define/contract (autoflatten . tokens)
    (->* () () #:rest token-list? (listof sql-token?))
    ; 'tokens' has already been flattened by its contract here:
    tokens)
> (autoflatten "a" '(1 (2 3)) "z")
'("a" 1 2 3 "z")

Basically any procedure that accepts a list of tokens works the same way:

> (where "a" '(1 (2 (3 4))) "z")
'(Where ("a" 1 2 3 4 "z"))
> (order-by "a" '(7 8 (9)) "z")
'(OrderBy ("a" 7 8 9 "z"))

value
queryable? : contract

This is a contract that defines how you are allowed to start a query or join. Specifically, in (from a b) or (join a b), the value of b must satisfy this contract.

(or/c source?
      procedure?
      string?
      query?
      join?
      subquery?)

A source? is typically obtained by applying the "constructor procedure" defined by table. In these examples, Rental is such a procedure:

(from r (Rental))
(from r (Rental "my-custom-alias"))

You can also pass the constructor procedure directly instead of applying it. In this case, the identifier that precedes it is used as the alias:

(from abc Rental)
; is equivalent to:
(from abc (Rental "abc"))

A string? value represents the table name. The identifier that precedes it is used as the alias. So this example shows a query of "MyTable" with an alias of "jkl":

(from jkl "MyTable")

When the value is a query?, the query or join appends to that value. Some examples:

(from a (from b "B"
              (select "1 as ONE"))
      (select "2 as TWO"))
; is equivalent to:
(from b "B"
      (select "1 as ONE")
      (select "2 as TWO"))

And

(join a (from b "B"
              (select "1 as ONE"))
      (join-on "2=2"))
; is equivalent to:
(join b "B"
      (select "1 as ONE")
      (join-on "2=2"))

When the value is a join?, it is a special appending scenario. A join appends to a join with no surprises. But when a query appends to a join, it first converts the join to a query. You can read more about this at Join-Query Conversion, but the main point is that join-on clauses get converted to where clauses:

(from a (join b "B"
              (join-on "1=1"))
      (select a".foo"))
; is equivalent to:
(from b "B"
      (where "1=1")
      (select b".foo"))

Finally, if the value is a subquery?, it suppresses the appending behavior. Showing the SQL is the best way to explain this:

> (define (my-sub)
    (from x "X"
          (select x".foo")))
; queries append by default:
> (display (to-sql (from a (my-sub))))
select
  x.foo
from X x
; use a subquery to prevent appending:
> (display (to-sql (from a (subquery (my-sub)))))
select a.*
from (
    select
      x.foo
    from X x) a

You can also use subquery to make a "literal" subquery, which I sometimes use when I want a result set that has exactly one row:

(from one-row (subquery "select 1 as ONE_ROW")
      (join a "A" 'LeftJoin
            (join-on a".ID = 3"))
      (join b "B" 'LeftJoin
            (join-on b".ID = 9"))
      (select (coalesce
               (scalar a".Name")
               (scalar b".Name")
               "neither A nor B was found")))

value
statement? : contract?

A statement is a value that can be applied to a query to produce a new query.

(or/c clause?
join-type?)

value
statement-expr? : contract?

(or/c statement? (listof statement?))

value
binding? : contract?
value
join? : contract?
value
source? : contract?

The name binding? was a poor choice. TODO Better names would be attached-join? and detached-join?; and join? would be true if either is true.

A binding? is an attached join.
A join? is a detached join.
A source? is the value of the identifier introduced by an enclosing from or join.

> (displayln
   (to-sql
    (from
     ignored-id
     (join x "X"
           (define y (join y "Y"))
           (join z "Z")
           (define (what-is it)
             (list
              (if (binding? it)
                  (select it".is-binding")
                  (select it".is-not-binding"))
              (if (join? it)
                  (select it".is-join")
                  (select it".is-not-join"))
              (if (source? it)
                  (select it".is-source")
                  (select it".is-not-source"))))
           (what-is x)
           (what-is y)
           (what-is z)))))
select
  x.is-not-binding
  , x.is-not-join
  , x.is-source
  , y.is-not-binding
  , y.is-join
  , y.is-not-source
  , z.is-binding
  , z.is-not-join
  , z.is-not-source
from X x
inner join Z z
on 1=1
inner join Y y
on 1=1

value
query? : contract?

A query is the value constructed by from.

> (query? (from a "A"))
#t
> (query? (join a "A"))
#f

value
injection? : contract?

An injection is the value created by inject.

4.4 Terminology

I have created 3 prepositions for my own naming convention, as well as for this documentation. They are all pronounced "of" but carry some extra meaning:

"The Item of/s the Copy" means "the Item that belongs to the Copy, and take note that there is at most 1 such Item." The /s refers to the singular nature of this relationship.
"The Copies of/g the Item" means "the group of Copies that belongs to the Item, and take note that there is exactly 1 such group. (But the group may be empty.)" The /g refers to the grouped nature of this relationship.
"The Copies of/p the Item" means "the Copies that belong to the Item, and take note that each Copy will make an individual appearance in the result set." The /p refers to the plural nature of this relationship.

4.5 #lang plisqin

TODO write this section.

** Dots are read differently: foo.bar gets read as foo .bar. You can use bars to suppress this: foo.bar.

** What gets required by default in #lang plisqin.

** The {curly-brace rewrite rules}. Note that a+b is not the same as a + b.

Pattern	Result	Notes
LITERAL	'LITERAL	When LITERAL is one of null, asc, desc
x .y	{y x}	Also works for x.y which is read as x .y
x ?? y	{coalesce x y}
x \|\| y	{\|\| x y}	String concatenation. TODO think about this precedence...
x OP y	{OP x y}	When OP is one of *, /
x OP y	{OP x y}	When OP is one of +, -
x CMP y	{CMP x y}	When CMP is one of =, <>, like, not-like, is, is-not, <, <=, >, >=

TODO put examples here, to make the motivation clear. Such as {order-by desc x.Foo} is rewritten to (order-by 'desc (Foo x))

top ← prev up next →

1	Introduction
2	Getting Started
3	Walkthrough
4	Reference
5	Video Rental Example Schema
6	For Developers
7	Preview of Plisqin 0.2

4.1	Fragments
4.2	SQL Functions
4.3	Data Model
4.4	Terminology
4.5	#lang plisqin