6.1 plisqin-lib

8.12

6.1 plisqin-lib🔗ℹ

package: plisqin

syntax
(from instance-id queryable-expr statement ...)

statement = (define (proc-id proc-arg ...) proc-body ...)
| (define val-id val-expr)
| (join join-id join-stuff ...)
| clause-expr

   queryable-expr :
(or/c symbol?
      table?
      query?
      Subquery?)
   clause-expr :
(or/c void? QueryClause?
      (listof (or/c void? QueryClause?)))

Creates a query?. A query consists of a queryable, a list of joins, and a list of clauses.

The queryable identifies the thing that is being queried – usually a database table or view, sometimes a subquery, never a query?. When queryable-expr is not a query?, the value of queryable-expr becomes this query’s queryable. When queryable-expr is a query?, this query inherits its queryable from the existing query. It also inherits that query’s list of joins and list of clauses; an example of this will come later.

The instance-id is bound as an instance? within each statement. More specifically, it is bound as an instanceof the queryable.

The define subforms bind proc-id or val-id within each statement that follows the definition.

The join subform binds join-id as a join? within each statement that follows the join. It also immediately adds the join to this query, guaranteeing that the join will appear in the generated SQL even if the join-id is never used anywhere else.

A clause-expr that is void? is discarded.

A clause-expr that is a list? is handled the same as if each item were an individual clause-expr. That is, the following queries are equal:

> (define q1
    (from x 'X
          (%%select x".one")
          (%%select x".two")))
> (define q2
    (from x 'X
          (list (%%select x".one")
                (void)
                (%%select x".two"))))
> (equal? q1 q2)
#t

See also the documentation on QueryClause?.

Appendable Queries

When queryable-expr is a query?, this query inherits the queryable, list of joins, and list of clauses from the existing query. Further joins and clauses are appended to these lists. The following example demonstrates appending:

> (define total
    (from x 'X
          (join y 'Y)
          (%%select x".one")
          (%%select y".two")))
> (define first-half
    (from x 'X
          (%%select x".one")))
> (define both-halves
    (from x first-half
          (join y 'Y)
          (%%select y".two")))
> (equal? total both-halves)
#t

syntax
(join instance-id queryable-expr maybe-to statement ...)

maybe-to =
| #:to link-expr

statement = (define (proc-id proc-arg ...) proc-body ...)
| (define val-id val-expr)
| (join join-stuff ...)
| clause-expr

   queryable-expr :
(or/c symbol?
      table?
      join?
      Subquery?)
   link-expr : instance?
   clause-expr :
(or/c void? JoinClause?
      (listof (or/c void? JoinClause?)))

Similar to from, but creates a join instead of a query.

The #:to option must be omitted when this join is a statement of an enclosing from or join. In that case, it is automatically linked to the enclosing query or join:

(from a 'A
      (join b 'B)
      (%%where b".foo = "a".foo"))
; is nearly equivalent to
(from a 'A
      (define b
        (join b 'B #:to a))
      (%%where b".foo = "a".foo"))

> (from a 'A
(join b 'B #:to a))
eval:20:0: join: The `#:to` option is not allowed here. This
join is automatically linked to the enclosing query or join.
in: (join b (quote B) #:to a)

Otherwise, the #:to option is usually required. The value of the link-expr specifies which query this join belongs to. In most real code, you only have one reasonable choice. In the following hypothetical example, pet is the only instance? in scope; it is the obvious and only possible link-expr:

(define/contract (Owner pet)
  (-> (instanceof Pet) (instanceof Person))
  (join p Person
        #:to pet
        (join-on (.= (PersonId p)
                     (OwnerId pet)))))

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

Predicate that recognizes queries.

> (query? (from a 'A))
#t

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

Predicate that recognizes joins.

> (define (B-given-A a)
    (join b 'B #:to a
          (%%join-on b".Foo = "a".Foo")))
> (from a 'A
        (if (join? (B-given-A a))
            (%%select "of course it is a join")
            (error "Inconceivable!")))
(from a/0 'A (select "of course it is a join"))

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

Predicate that recognizes instances. An instance can be thought of as a row of data. If you translate code to English, you might translate an instance as the [table-name] or each [table-name]. For example, the following where clause can be translated to English as "where the Age of the Person is at least 21":

(from p Person
      ; Now `p` is an `instance?` and an `(instanceof Person)`
      (where (>= (Age p)
                 (val 21))))

Additionally, every join? is also an instance.

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

Creates a predicate roughly equivalent to

(lambda (x)
(and (instance? x)
(has-same-queryable? x queryable)))

procedure
(limit n) → Limit?
n : (or/c #f nonnegative-integer?)

Places an upper bound on the number of rows that the query or join will produce.

> (aw:show-table
(from pc ProductCategory
(limit 2)))
Show TableShow SQL
select pc.*
from ProductCategory pc
limit 2
ProductCategoryID
Name
rowguid
ModifiedDate
1
Bikes
{CFBDA25C-DF71-47A7-B81B-64EE161AA37C}
2008-04-30 00:00:00
2
Components
{C657828D-D808-4ABA-91A3-AF2CE02300E9}
2008-04-30 00:00:00

The last limit clause overrides all the previous ones. The following example uses #f to clear the limit.

> (aw:show-table
   (from pc ProductCategory
         (limit 2)
         (limit #f)))
Show TableShow SQL
select pc.*
from ProductCategory pc
ProductCategoryID
Name
rowguid
ModifiedDate
1
Bikes
{CFBDA25C-DF71-47A7-B81B-64EE161AA37C}
2008-04-30 00:00:00
2
Components
{C657828D-D808-4ABA-91A3-AF2CE02300E9}
2008-04-30 00:00:00
3
Clothing
{10A7C342-CA82-48D4-8A38-46A2EB089B74}
2008-04-30 00:00:00
4
Accessories
{2BE3BE36-D9A2-4EEE-B593-ED895D97C2A6}
2008-04-30 00:00:00

procedure
(offset n) → Offset?
n : (or/c #f nonnegative-integer?)

Skips past the first n rows of the result set. If using MSSQL, you must also have at least one order-by clause or the SQL that is produced will be invalid. Offset is frequently used with limit to implement paging.

> (aw:show-table
   (from p Product
         (order-by (ProductID p))
         (offset 30)
         (limit 3)))
Show TableShow SQL
select p.*
from Product p
order by p.ProductID
limit 3
offset 30
ProductID
Name
ProductNumber
MakeFlag
FinishedGoodsFlag
Color
SafetyStockLevel
ReorderPoint
StandardCost
ListPrice
Size
SizeUnitMeasureCode
WeightUnitMeasureCode
Weight
DaysToManufacture
ProductLine
Class
Style
ProductSubcategoryID
ProductModelID
SellStartDate
SellEndDate
DiscontinuedDate
rowguid
ModifiedDate
350
Fork Crown
FC-3654
1
0
#<sql-null>
800
600
0
0.0
#<sql-null>
#<sql-null>
#<sql-null>
#<sql-null>
1
#<sql-null>
#<sql-null>
#<sql-null>
#<sql-null>
#<sql-null>
2008-04-30 00:00:00
#<sql-null>
#<sql-null>
{1CBFA85B-5C9B-4B58-9C17-95238215D926}
2014-02-08 10:01:36.827000000
351
Front Derailleur Cage
FC-3982
0
0
Silver
800
600
0
0.0
#<sql-null>
#<sql-null>
#<sql-null>
#<sql-null>
0
#<sql-null>
#<sql-null>
#<sql-null>
#<sql-null>
#<sql-null>
2008-04-30 00:00:00
#<sql-null>
#<sql-null>
{01C901E3-4323-48ED-AB9E-9BFDA28BDEF6}
2014-02-08 10:01:36.827000000
352
Front Derailleur Linkage
FL-2301
0
0
Silver
800
600
0
0.0
#<sql-null>
#<sql-null>
#<sql-null>
#<sql-null>
0
#<sql-null>
#<sql-null>
#<sql-null>
#<sql-null>
#<sql-null>
2008-04-30 00:00:00
#<sql-null>
#<sql-null>
{88ED2E08-E775-4915-B506-831600B773FD}
2014-02-08 10:01:36.827000000

The last offset clause overrides all the previous ones. A value of #f clears the offset.

procedure
(distinct distinct?) → Distinct?
distinct? : any/c

Specifies whether duplicate rows are eliminated from the result set. The default behavior is #f which allows duplicate rows. Any non-false value means that duplicate rows will be eliminated. The last distinct clause overrides all previous ones.

The following query would return hundreds of rows (one for each Product) if the distinct flag was #f instead:

> (aw:show-table
   (from p Product
         (select (Color p))
         (distinct #t)))
Show TableShow SQL
select distinct p.Color as Color
from Product p
Color
#<sql-null>
Black
Silver
Red
White
Blue
Multi
Yellow
Grey
Silver/Black

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

A clause that can be used in a join to specify what should happen if the join does not find any matching records. An 'inner join will filter out any rows for which this join did not match. A 'left join will not filter any rows from the result set, but any attempts to access a column via this join will produce dbnull.

If a join has multiple join-type clauses, the last one overrides all the previous ones.

Left Join Propogation

The default join-type is #f, which means that left joins should be propogated. That is, this join may be considered a left join if any of its join-on clauses contain a join that is considered left. Otherwise this join will be considered an inner join.

This is necessary because an instance? might be a left join. The following procedure accepts subcat which must be an (instanceof ProductSubcategory). When subcat is a left join (or when it is considered a left join), the returned join will also be considered left. Otherwise the returned join will be considered inner.

(define/contract (get-category subcat)
  (-> (instanceof ProductSubcategory)
      (and/c join?
             (instanceof ProductCategory)))
  (join pc ProductCategory #:to subcat
        (join-on (.= (ProductCategoryID pc)
                     (?? (ProductCategoryID subcat) /void)))))
(from p Product
      ; The Product:ProductSubcategory relationship is inherently optional.
      ; That is, some Products truly do not have Subcategories.
      (join subcat ProductSubcategory
            (join-type 'left)
            (join-on (.= (ProductSubcategoryID subcat)
                         (?? (ProductSubcategoryID p) /void))))
      ; The ProductSubcategory:ProductCategory relationship is not
      ; inherently optional, but the left-ness of `subcat` propogates
      ; to the return value of (get-category subcat)
      (join cat (get-category subcat))
      ....)

Best Practices

Reusable joins (those that are returned by procedures) should never remove rows from the result set. Otherwise the call site of the reusable join might accidentally remove rows without intending to. Accidentally removing rows from the result set is a more difficult mistake to notice than the alternative, which is having some dbnulls propogate to the final result set (if the strict nullchecking doesn’t catch it first.)

Reusable joins should never use the 'inner join type, even when they represent a non-optional relationship. In the previous example, get-subcategory represents the ProductSubcategory:ProductCategory relationship which is not optional; specifically, every ProductSubcategory has exactly 1 ProductCategory. But get-subcategory still uses the the default join type of #f instead of 'inner, because its subcat argument might be a left join.

syntax
(define-schema schema-id table-spec ...)

table-spec = (table table-id item-spec ...)

item-spec = #:column column-spec ...
| #:property proc-spec ...
| #:has-one proc-spec ...
| #:has-group proc-spec ...

proc-spec = [proc-id proc-body]

column-spec = proc-id
| [proc-id as-name* type* nullability* dbname*]

as-name* =
| #:as as-name

type* =
| #:type type

nullability* =
| #:null nullability

dbname* =
| #:dbname dbname

Used to encode a database schema into Racket procedures. See Using define-schema for an extended example.

Defines each table-id as a table?. Note that each table-id can also be used as a proc-id, meaning that some tables are also procedures.

Each proc-id is defined as a procedure that takes one argument. The argument must be an instanceof a table that encloses the proc-id, otherwise a contract violation will be raised. Multiple tables may enclose the same proc-id and the procedure will dispatch to the correct case based on the argument value.

> (define-schema test-schema
    (table A
           #:property
           [foo (%%scalar "foo-given-A")])
    (table B
           #:property
           [foo (%%scalar "foo-given-B")])
    (table C))
> (foo A)
(scalar "foo-given-A")
> (foo B)
(scalar "foo-given-B")
; foo is not defined for C
> (foo C)
foo: contract violation
  expected: one of (B A)
  given: C

Recall that each proc-id is a procedure that takes one argument. Within the corresponding proc-body, the special form this will be bound to that single argument. So this is always an instanceof the table that encloses it.

Currently, #:property, #:has-one, and #:has-group are treated identically; they are only for code readability at this time. You should use #:property for procedures that return a Scalar? (or one of its subtypes). You should use #:has-group for procedures that return grouped joins. You should use #:has-one for procedures that return joins such that "this:joined-table" is a "many:1" or "1:1" relationship. A future version of Plisqin may add warnings if it can detect that your procedures are returning something other than the keyword indicates.

The #:column definitions are different than the others. They have no proc-body, so this cannot be used. The return value is something like the following:

(>> (%%scalar this
              (>> (%%sql "." dbname)
                  #:null nullability)
              #:cast type
              #:as as-name))

The default dbname is proc-id quoted. The default type is Scalar?. The default nullability is maybe. The default as-name is proc-id quoted.

The schema-id is a procedure that can be used as a REPL tool to explore your schema definition. Using this procedure outside of the REPL is strongly discouraged. You can do three useful things with it:

This procedure works on datums, not syntax objects. If you have renamed any tables or procedures (e.g. with prefix-in), you still need to use the original ids with this procedure.

> (define-schema test-schema
    (table A #:column x y z)
    (table B #:column x z)
    (table C #:column y z))
; --- 1 ---
; See which procedures accept a certain table.
; If I have an (instanceof B), I can use this to
; see all the procedures which will accept it:
> (test-schema '(_ B))
'(x z)
; --- 2 ---
; See which tables a certain proc will accept.
; This lists the tables that `y` accepts:
> (test-schema '(y _))
'(A C)
; --- 3 ---
; List all the tables:
> (test-schema 'tables)
'(A B C)

syntax
this

For use within define-schema. Any other use is a syntax error.

Within define-schema, this will always be an instanceof the table that encloses it.

this May Be Null

We know that this is always an instance?. We also know that every join? is an instance?. We also know that left joins are nullable. Therefore, every time we use this, we should remember that it might be nullable. Specifically, if we are using this within a strict comparison, we should make sure to use a fallback. If we don’t, it is just a matter of time before someone passes a left join into our procedure and gets an error that might surprise them.

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

Predicate that recognizes tables from define-schema.

> (define-schema test-schema
(table Tbl #:column foo))
> (table? Tbl)
#t

A table? can be used an instanceof itself. This should only be used on your REPL, to explore things like "if I had an instance of Tbl, what would foo return?"

> ((instanceof Tbl) Tbl)
#t
> (foo Tbl)
(scalar Tbl (sql "." 'foo))

In real queries, every instance must be created using from or join. If you try to use a table as an instance, SQL cannot be generated:

> (to-sql
   (from t Tbl
         ; Good - `t` is an instance created by `from`
         (select (foo t))
         ; BAD! - don't use `Tbl` as an instance in real code
         (select (foo Tbl))))
cannot render Tbl

procedure
(>> token
[ #:type Type
#:as as-name
#:null nullability
#:fallback /fallback]) → Token?
  token : Token?
  Type : type? = <no-change>
  as-name : (or/c symbol? string?) = <no-change>
  nullability : nullability? = <no-change>
  /fallback : fallback? = <no-change>

Returns a copy of the given token with the specified modifications.

The #:cast option assigns a new type:

> (Bool? (>> (%%sql "foo = bar") #:cast Bool?))
#t

The #:as option assigns a new "as-name", which is recognized by select and specifies how the column of the result set should be named.

> (aw:show-table
   (from p Product
         (limit 1)
         (select (>> (val "Hello!") #:as 'Greeting))))
Show TableShow SQL
select 'Hello!' as Greeting
from Product p
limit 1
Greeting
Hello!

Future versions of Plisqin may introduce "as-name propogation" in which certain token constructions preserve the as-name of a child token. For example, (coalesce foo (val 0)) might inherit the as-name of foo.

The #:null option assigns a new nullability.

> (let ([raw (%%sql "foo")])
(list (nullability raw)
(nullability (>> raw #:null no))))
(list maybe no)

The #:fallback option assigns a new fallback. It is more common to use ?? to assign fallbacks.

syntax
(define-statement (id arg ...) body ...+)

arg = plain-arg
| kw-arg

plain-arg = arg-id
| [arg-id Type-expr]
| [arg-id Type-expr default-expr]

kw-arg = keyword plain-arg

Type-expr : type?

Defines an uncompiled statement, which is a normal procedure with some extra metadata stashed away that compile-statements can use.

(define-statement (foo a
                       [b Number?]
                       [c String? "c-default"]
                       #:d d
                       #:e [e Number?]
                       #:f [f String? "f-default"])
  (list a b c d e f))
; produces code similar to
(define (foo a
             b
             [c (%%val "c-default")]
             #:d d
             #:e e
             #:f [f (%%val "f-default")])
  (list a b c d e f))
(begin
  ; this part is illustrative, not real code:
  (module+ metadata-for-compile-statements
    (define meta:foo
      (foo (param a Scalar?)
           (param b Number?)
           (param c String?)
           #:d (param d Scalar?)
           #:e (param e Number?)
           #:f (param f String?)))))

In the code above, the hypothetical (param b Number?) expression creates a placeholder token having type Number? and representing an SQL parameter. The a and d parameters were assigned the Scalar? type, which is the default when Type-expr is absent.

syntax
(compile-statements #:module module-path
                    #:dialect dialect-expr
                    maybe-provide)

maybe-provide =
| #:provide? #t
| #:provide? #f

   dialect-expr : dialect?

Produces an unbound statement for each uncompiled statement in the given module-path. Each unbound statement is a procedure with the same arity as its corresponding uncompiled statement, but its arguments are parameter values that will get passed to the database when the statement is executed. The unbound statement returns a db:statement? suitable for use by db:query-rows and similar procedures.

; This module contains a `get-category` uncompiled statement:
> (compile-statements #:module plisqin-examples/adventure-works/statements
                      #:dialect (sqlite)
                      #:provide? #f)
; Now `get-category` has been compiled to an unbound statement:
> (displayln get-category)
#<unbound-statement: get-category
select cat.ProductCategoryID as ProductCategoryID
  , cat.Name as Name
  , cat.ModifiedDate as ModifiedDate
from ProductCategory cat
where (?1 /*name*/ is not null and (cat.Name = ?1 /*name*/))>
; We just need to supply the parameter values and we can
; execute it against a real database!
> (define conn (aw:connect-adventure-works))
> (db:query conn (get-category #:name "Bikes"))
(rows-result
'(((name . "ProductCategoryID") (decltype . "integer_id"))
   ((name . "Name") (decltype . "text"))
   ((name . "ModifiedDate") (decltype . "text")))
'(#(1 "Bikes" "2008-04-30 00:00:00")))
> (db:disconnect conn)

The SQL is generated only once, using the given dialect-expr.

Unless #:provide? is #f, each unbound statement will be provided. This option is really only intended for this documentation; your code probably has no need to ever use this option.

procedure
(to-sql x) → string?
x : unsafe-content?

> (displayln
   (to-sql
    (from a 'Album
          (%%where a".ReleaseYear = 1973"))))
select a.*
from Album a
where a.ReleaseYear = 1973

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

Predicate that recognizes intervals.

> (interval? (days 2))
#t

See also the date math examples at date+.

procedure
(unsafe-content? x) → any/c
x : any/c

Used as a contract by the unsafe variant to fail fast when given values that definitely cannot be rendered to-sql. A value that is unsafe-content? can probably be rendered to SQL, but this is not guaranteed.

Equivalent to (or/c Token? query? join? instance? interval? string? number? symbol? (listof unsafe-content?))

procedure
(2bool? x) → any/c
x : any/c
=
(and (Boolish? x)
(eq? no (nullability x)))

Predicate that recognizes 2-valued booleans (true and false). It will not contain the unknown boolean value. Used by the strict variant to avoid 3-valued logic. See Nullability for more information.

6.1.1 Nullability🔗ℹ

value
/void : fallback?
value
/minval : fallback?
value
/maxval : fallback?
value
/any : fallback?

A fallback. Fallbacks are recognized by the comparison operators to disambiguate what should happen if dbnull is encountered. See Fallback Meanings specifically, and Nullability more generally.

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

Predicate that recognizes fallbacks.

> (fallback? /void)
#t

procedure
(fallback x) → (or/c #f fallback?)
x : any/c

Returns the fallback of x or #f if none exists.

> (define my-token (%%sql "foo"))
> (fallback my-token)
#f
> (fallback (?? my-token /minval))
/minval

value
yes : nullability?
value
no : nullability?
value
maybe : nullability?

See nullability.

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

Predicate that recognizes the nullabilities.

> (nullability? yes)
#t
> (nullability? no)
#t
> (nullability? maybe)
#t

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

Returns the nullability of the given item. Literal values are never null:

> (nullability (val 3))
no
> (nullability (val "hi"))
no

Racket numbers are never null, but Racket strings might be. This is because Racket strings usually represent an arbitrary piece of SQL that Plisqin knows nothing about:

> (nullability 3)
no
> (nullability "case when 1=1 then null else null end")
maybe
> (nullability "case when 1=1 then 'foo' else 'bar' end")
maybe

The token constructors infer their own nullability from their contents. Some (eg coalesce) have special rules, but most just take the "worst" nullability that was found. From worst to best: yes, maybe, no.

> (define content (%%sql "foo = 1"))
> (nullability (%%where (>> content #:null no)))
no
> (nullability (%%where (>> content #:null yes)))
yes

6.1.2 Token Types🔗ℹ

(require plisqin-lib/types)

package: plisqin

Plisqin’s types are plain Racket values. They start with a capital letter.

> Subquery?
Subquery?
> (type? Subquery?)
#t

Types can be used as predicates, therefore they can also be used as contracts.

> (Number? (val 20))
#t
> (Token? (%%where "x.foo = 'bar'"))
#t

Most types have at least one supertype. For example, Scalar? is a supertype of Number?.

> (type-supertypes Number?)
(list Scalar?)
> (Number? (val 20))
#t
> (Scalar? (val 20))
#t

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

Predicate that recognizes types.

> (type? Token?)
#t
> (type? Datetime?)
#t

procedure
(type-supertypes t) → (listof type?)
t : type?

Returns the supertypes of the given type.

> (type-supertypes Datetime?)
(list Scalar?)

value
Token? : type?

#:supertypes []

The root of the type hierarchy.

value
Scalar? : type?

#:supertypes [Token?]

In general, every data type that a database column can have should correspond to Scalar? or one of its subtypes. To use some PostgreSQL examples, "varchar(50)" corresponds to String?, and "bigint" corresponds to Number?. Less common data types such as "cidr" correspond to Scalar? because Plisqin has no more appropriate subtype for them.

value
Boolish? : type?

#:supertypes [Scalar?]

The supertype of Bit? and Bool?. Most procedures that accept a Boolish? argument really want a Bool? but will tolerate a Bit?. The following example shows where converting a bit to a bool by comparing it against zero.

> (displayln
   (to-sql
    (from x 'X
          (where (>> (val 0) #:cast Bit?)))))
select x.*
from X x
where 0 <> 0
> (displayln
   (to-sql
    (from x 'X
          (where (>> (val 1) #:cast Bit?)))))
select x.*
from X x
where 1 <> 0

value
Bit? : type?

#:supertypes [Boolish?]

MSSQL and SQLite do not have a boolean data type for columns. MSSQL databases typically use the "bit" data type while SQLite databases typically use a regular number. The Bit? type is designed to be compatible with these patterns, and with any numeric field that desires the following semantics:

See Nullability for an explanation of "the unknown boolean value" and how to avoid strange three-valued logic.

A null value represents the unknown boolean value.
A zero value represents false.
Any other value represents true.

See example on Boolish?.

value
Bool? : type?

#:supertypes [Boolish?]

Represents a boolean expression. Corresponds to the "boolean" data type in PostgreSQL. In MSSQL and SQLite, the only way to obtain a Bool? is as a return value (for example, of a comparison). Columns in MSSQL and SQLite should never be declared as Bool?; consider using Bit? instead.

MSSQL and Bool -> Scalar Conversion

In MSSQL, a boolean expression cannot be used as a scalar. For example, "select 42=42" is an error in MSSQL. But Bool? is a subtype of Scalar? (indirectly). This tension is resolved by generating SQL that converts a bool to a bit when a scalar is needed, as in the following example:

> (define q
    (from x 'X
          (define a-bool (.= (val 42)
                             (val 42)))
          (where a-bool)
          (select a-bool)))
> (parameterize ([current-dialect (mssql)])
    (displayln (to-sql q)))
select cast(case when (42 = 42) then 1 else 0 end as bit)
from X x
where (42 = 42)
; non-MSSQL dialects are much easier:
> (displayln (to-sql q))
select (42 = 42)
from X x
where (42 = 42)

value
Datetime? : type?

#:supertypes [Scalar?]

Represents a datetime type in your database such as "timestamp" or "datetime2".

value
Number? : type?

#:supertypes [Scalar?]

Represents a numeric type in your database such as "bigint" or "decimal(10, 4)".

value
String? : type?

#:supertypes [Scalar?]

Represents a string type in your database such as "char(10)" or "varchar(max)".

value
Subquery? : type?

#:supertypes [Token?]

Represents a subquery. Returned by subquery and %%subquery.

value
Clause? : type?

#:supertypes [Token?]

The supertype of all clauses.

value
JoinClause? : type?

#:supertypes [Clause?]

The supertype of all clauses that can be used inside join. When used as a contract, it is equivalent to (or/c QueryClause? JoinOn? JoinType?).

value
QueryClause? : type?

#:supertypes [Clause?]

The supertype of all clauses that can be used inside from.

> (andmap QueryClause?
          (list (%%where 1)
                (%%group-by 1)
                (%%having 1)
                (%%order-by 1)
                (%%select 1)
                (limit 1)
                (offset 1)
                (distinct #t)))
#t

value
Select? : type?

#:supertypes [JoinClause? QueryClause?]

The return type of select. You should never cast to this type.

value
Where? : type?

#:supertypes [JoinClause? QueryClause?]

The return type of where. You should never cast to this type.

value
GroupBy? : type?

#:supertypes [JoinClause? QueryClause?]

The return type of group-by. You should never cast to this type.

value
Having? : type?

#:supertypes [JoinClause? QueryClause?]

The return type of having. You should never cast to this type.

value
OrderBy? : type?

#:supertypes [JoinClause? QueryClause?]

The return type of order-by. You should never cast to this type.

value
JoinOn? : type?

#:supertypes [JoinClause?]

The return type of join-on. You should never cast to this type.

value
Limit? : type?

#:supertypes [JoinClause? QueryClause?]

The return type of limit. You should never cast to this type.

value
Offset? : type?

#:supertypes [JoinClause? QueryClause?]

The return type of offset. You should never cast to this type.

value
Distinct? : type?

#:supertypes [JoinClause? QueryClause?]

The return type of distinct. You should never cast to this type.

value
JoinType? : type?

#:supertypes [JoinClause?]

The return type of join-type. You should never cast to this type.

6.1.3 Dialects🔗ℹ

(require plisqin-lib/dialect)

package: plisqin

parameter
(current-dialect) → (or/c #f dialect?)
(current-dialect dialect) → void?
dialect : (or/c #f dialect?)

This parameter controls which flavor of SQL to generate.

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

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

Predicate that recognizes dialects.

> (dialect? (postgres))
#t
> (dialect? (mssql))
#t
> (dialect? (sqlite))
#t

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

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

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

Tests whether x is a dialect? representing PostgreSQL, Microsoft SQL Server, or SQLite.

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

1	Read Me First
2	Using define-schema
3	Aggregates
4	Nullability
5	Refactoring Recipes
6	Reference
7	Plisqin as a Research Language

6.1	plisqin-lib
6.2	plisqin-lib/ unsafe
6.3	plisqin-lib/ loose
6.4	plisqin-lib/ strict
6.5	plisqin-examples/ adventure-works