syntax (val x [type])

Here [type] denotes an optional argument, not literal brackets.

A variant of (%%val x [type]). This variant protects against SQL injection by requiring x to be a literal string, number, or boolean.

Examples:

> (val "a string literal")

(scalar "'a string literal'")

> (let ([not-a-string-literal "hi"])     (val not-a-string-literal))

val: contract violation

expected: a literal (string, numeric, or boolean)

given: "hi"

procedure (?? token-1 [token-N ...+] [/fallback])

token-1   :   Token?    token-N   :   Token?    /fallback   :   fallback?

This procedure is typically used to attach a fallback, but it can also be used as a synonym for coalesce if no fallback is given. More precisely, this procedure has 3 cases:

1. When given two arguments and the last argument is a fallback?, it simply attaches the fallback to the first argument:

   (?? token-1 /fallback)

   ; is equivalent to

   (>> token-1 #:fallback /fallback)

2. When given more than two arguments and the last argument is a fallback?, it attaches the fallback to the result of coalesce:

   (?? token-1 token-N ...+ /fallback)

   ; is equivalent to

   (>> (coalesce token-1 token-N ...+) #:fallback /fallback)

3. When the last argument is not a fallback?, it is simply a synonym for coalesce:

   (?? token-1 token-N ...+)

   ; is equivalent to

   (coalesce token-1 token-N ...+)

You may need to check the documentation on coalesce to know the return type of cases 2 and 3.

procedure select  :  (token-constructor  [Scalar? -> Select?])

If a query or join has no select clauses, then all the columns of its queryable are returned. Otherwise, each select clause adds a column to the result set

> (aw:show-table

(from pc ProductCategory

(select (ProductCategoryID pc))

(select (Name pc))

(select (val "hello world"))))

Show TableShow SQL select pc.ProductCategoryID as ProductCategoryID   , pc.Name as Name   , 'hello world' from ProductCategory pc ProductCategoryID Name 'hello world' 1 Bikes hello world 2 Components hello world 3 Clothing hello world 4 Accessories hello world

procedure where  :  (token-constructor  [2bool? -> Where?])

Eliminates rows from the result set that do not satisfy the given condition.

> (aw:show-table

(from pc ProductCategory

(where (.= (Name pc)

(val "Bikes")))))

Show TableShow SQL select pc.* from ProductCategory pc where (pc.Name = 'Bikes') ProductCategoryID Name rowguid ModifiedDate 1 Bikes {CFBDA25C-DF71-47A7-B81B-64EE161AA37C} 2008-04-30 00:00:00

procedure group-by  :  (token-constructor  [Scalar? -> GroupBy?])

Adds a grouping key to the result set. See example on having or read the Aggregates section for more complete details.

procedure having  :  (token-constructor  [2bool? -> Having?])

Can only be used when group-by clauses are also present. Eliminates rows from the result set that do not satisfy the given condition.

> (aw:show-table

(from subcat ProductSubcategory

(group-by (ProductCategoryID subcat))

(having (.> (count subcat)

(val 10)))

(select (ProductCategoryID subcat))

(select (>> (count subcat) #:as 'NumSubcategories))))

Show TableShow SQL select subcat.ProductCategoryID as ProductCategoryID   , count(*) as NumSubcategories from ProductSubcategory subcat group by subcat.ProductCategoryID having (count(*) > 10) ProductCategoryID NumSubcategories 2 14 4 12

Note that where is applied to individual rows, while having is applied to groups. You can imagine that your database engine first applies any where clauses, then applies any group-by clauses, then applies any having clauses.

procedure order-by  :  (token-constructor  [(or/c 'asc 'desc) Scalar? -> OrderBy?]  [Scalar? -> OrderBy?])

Specifies how the result set should be ordered. If any previous order-by clauses exist, this one will be used to break any ties. If the first argument is 'asc or 'desc, it specifies ascending or descending; otherwise the default is ascending.

> (aw:show-table

(from p Product

(limit 5)

(order-by 'desc (Color p))

(order-by 'desc (ProductID p))))

Show TableShow SQL select p.* from Product p order by p.Color desc, p.ProductID desc limit 5 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 976 Road-350-W Yellow, 48 BK-R79Y-48 1 1 Yellow 100 75 1082.51 1700.99 48 CM LB 16.42 4 R M W 2 27 2013-05-30 00:00:00 #<sql-null> #<sql-null> {EC4284DC-85FA-44A8-89EC-77FC9B71720A} 2014-02-08 10:01:36.827000000 975 Road-350-W Yellow, 44 BK-R79Y-44 1 1 Yellow 100 75 1082.51 1700.99 44 CM LB 16.13 4 R M W 2 27 2013-05-30 00:00:00 #<sql-null> #<sql-null> {0C61E8AF-003D-4E4B-B5B7-02F01A26BE26} 2014-02-08 10:01:36.827000000 974 Road-350-W Yellow, 42 BK-R79Y-42 1 1 Yellow 100 75 1082.51 1700.99 42 CM LB 15.77 4 R M W 2 27 2013-05-30 00:00:00 #<sql-null> #<sql-null> {80BD3F8B-42C7-43D8-91F5-9FB6175287AF} 2014-02-08 10:01:36.827000000 973 Road-350-W Yellow, 40 BK-R79Y-40 1 1 Yellow 100 75 1082.51 1700.99 40 CM LB 15.35 4 R M W 2 27 2013-05-30 00:00:00 #<sql-null> #<sql-null> {237B16D9-53F2-4FD4-BEFE-48209E57AEC3} 2014-02-08 10:01:36.827000000 965 Touring-3000 Yellow, 62 BK-T18Y-62 1 1 Yellow 100 75 461.4448 742.35 62 CM LB 30.00 4 T L U 3 36 2013-05-30 00:00:00 #<sql-null> #<sql-null> {D28B3872-5173-40A4-B12F-655524386CC7} 2014-02-08 10:01:36.827000000

procedure join-on  :  (token-constructor  [2bool? -> JoinOn?])

This clause can only be used inside join; not from. Specifies how rows from this join should be matched to the parent query.

> (aw:show-table

(from subcat ProductSubcategory

(join cat ProductCategory

(join-on (.= (ProductCategoryID cat)

(ProductCategoryID subcat))))

(%%select subcat".Name as SubcategoryName")

(%%select cat".Name as CategoryName")

(limit 5)))

Show TableShow SQL select subcat.Name as SubcategoryName   , cat.Name as CategoryName from ProductSubcategory subcat inner join ProductCategory cat    on (cat.ProductCategoryID = subcat.ProductCategoryID) limit 5 SubcategoryName CategoryName Mountain Bikes Bikes Road Bikes Bikes Touring Bikes Bikes Handlebars Components Bottom Brackets Components

procedure avg  :  (token-constructor  [Number? -> Number?])

Produces the average of the given expression, relative to each group. The following example uses traditional aggregation, but every aggregate in Plisqin also works with grouped join aggregation. Read Aggregates for complete understanding.

> (aw:show-table

(from p Product

(group-by (Color p))

(select (Color p))

(select (count p))

(select (avg (ListPrice p)))

(select (min (ListPrice p)))

(select (max (ListPrice p)))

(select (sum (ListPrice p)))))

Show TableShow SQL select p.Color as Color   , count(*)   , avg(p.ListPrice)   , min(p.ListPrice)   , max(p.ListPrice)   , sum(p.ListPrice) from Product p group by p.Color Color count(*) avg(p.ListPrice) min(p.ListPrice) max(p.ListPrice) sum(p.ListPrice) #<sql-null> 248 16.864193548387078 0.0 229.49 4182.319999999995 Black 93 725.1210752688169 0.0 3374.99 67436.25999999997 Blue 26 923.6792307692305 34.99 2384.07 24015.659999999993 Grey 1 125.0 125.0 125.0 125.0 Multi 8 59.865 8.99 89.99 478.92 Red 38 1401.9499999999991 34.99 3578.27 53274.09999999997 Silver 43 850.305348837209 0.0 3399.99 36563.12999999998 Silver/Black 7 64.01857142857143 40.49 80.99 448.13 White 4 9.245000000000001 8.99 9.5 36.980000000000004 Yellow 36 959.0913888888887 53.99 2384.07 34527.28999999999

procedure min  :  (token-constructor  [String? -> String?]  [Datetime? -> Datetime?]  [Number? -> Number?])

Like avg, but produces the minimum value. See example on avg.

procedure max  :  (token-constructor  [String? -> String?]  [Datetime? -> Datetime?]  [Number? -> Number?])

Like avg, but produces the maximum value. See example on avg.

procedure sum  :  (token-constructor  [Number? -> Number?])

Like avg, but produces the sum. See example on avg.

procedure count  :  (token-constructor  [instance? -> Number?]  [Scalar? -> Number?]  [(or/c 'distinct) Scalar? -> Number?])

When given an instance?, counts how many rows are in each group. An example of this can be seen on avg.

When given a Scalar?, counts how many non-dbnull values are in each group. When the scalar is preceded by 'distinct, counts how many unique non-dbnull values are in each group.

In this example, (count (ProductID p)) is equivalent to (count p) because the ProductID is non-nullable.

> (aw:show-table

(from p Product

(select (>> (count p)

#:as 'Num_Products))

(select (>> (count (ProductID p))

#:as 'Num_Product_IDs))

(select (>> (count (ProductSubcategoryID p))

#:as 'Num_Products_With_Subcategories))

(select (>> (count 'distinct (ProductSubcategoryID p))

#:as 'Num_Unique_Subcategories))))

Show TableShow SQL select count(*) as Num_Products   , count(p.ProductID) as Num_Product_IDs   , count(p.ProductSubcategoryID) as Num_Products_With_Subcategories   , count(distinct p.ProductSubcategoryID) as Num_Unique_Subcategories from Product p Num_Products Num_Product_IDs Num_Products_With_Subcategories Num_Unique_Subcategories 504 504 295 37

procedure exists  :  (token-constructor  [(or/c query? Subquery?) -> Bool?])

Produces true when the given argument has any rows, false otherwise. The following where clause filters out Products which do not have any corresponding rows in the SalesOrderDetail table.

> (aw:show-table

(from prd Product

(limit 5)

(select (ProductName prd))

(select (ProductNumber prd))

(where (exists (from dtl SalesOrderDetail

(where (.= (ProductID dtl)

(ProductID prd))))))))

Show TableShow SQL select prd.Name as ProductName   , prd.ProductNumber as ProductNumber from Product prd where exists (   select dtl.*   from SalesOrderDetail dtl   where (dtl.ProductID = prd.ProductID) ) limit 5 ProductName ProductNumber Sport-100 Helmet, Red HL-U509-R Sport-100 Helmet, Black HL-U509 Mountain Bike Socks, M SO-B909-M Mountain Bike Socks, L SO-B909-L Sport-100 Helmet, Blue HL-U509-B

procedure subquery  :  (token-constructor  [query? -> Subquery?])

Represents a subquery.

This strict variant is really only useful for blocking the appending behavior of from. That is, if q is a query?, then (from x q ....) will append more clauses to q. If you want to treat q as a subquery instead, you need to wrap it: (from x (subquery q) ....)

The unsafe variant, %%subquery, is probably more useful. A common pattern is to create a subquery with exactly one row, as follows:

> (aw:show-table

(from x (%%subquery "select 1 as one")

(select (val "hello"))

(select (val "world"))))

Show TableShow SQL select 'hello'   , 'world' from (select 1 as one) x 'hello' 'world' hello world

procedure coalesce  :  (token-constructor  [String? String? ...+ -> String?]  [Datetime? Datetime? ...+ -> Datetime?]  [Number? Number? ...+ -> Number?])

Produces the first non-dbnull value from the given arguments. If the nullability of any argument is no, then the nullability of the returned token is also no.

The following example uses coalesce to define maxDiscount as "the maximum DiscountPct from the group of Special Offers, or zero when there are no Special Offers (and the maximum is dbnull)."

> (aw:show-table

(from p Product

(%%where (ProductID p)" in (514, 680, 707, 725)")

(select (ProductName p))

(select (ListPrice p))

(join offersG (SpecialOffersG p))

(define maxDiscount

(coalesce (max (DiscountPct offersG))

(val 0)))

(select (>> maxDiscount #:as 'BestDiscount))

(select (>> (.* (ListPrice p)

(.- (val 1) maxDiscount))

#:as 'BestPrice))))

Show TableShow SQL select p.Name as ProductName   , p.ListPrice as ListPrice   , coalesce(offer.__INJECT1, 0) as BestDiscount   , (p.ListPrice * (1 - coalesce(offer.__INJECT1, 0))) as BestPrice from Product p left join (   select sop.ProductID as __INJECT0     , max(offer.DiscountPct) as __INJECT1   from SpecialOffer offer   inner join SpecialOfferProduct sop      on (sop.SpecialOfferID = offer.SpecialOfferID)   group by sop.ProductID ) offer    on (offer.__INJECT0 = p.ProductID) where p.ProductID in (514, 680, 707, 725) ProductName ListPrice BestDiscount BestPrice LL Mountain Seat Assembly 133.34 0 133.34 HL Road Frame - Black, 58 1431.5 0.0 1431.5 Sport-100 Helmet, Red 34.99 0.15 29.741500000000002 LL Road Frame - Red, 44 337.22 0.35 219.193

If you do not use coalesce, dbnull will propogate through the expressions built using maxDiscount:

> (aw:show-table

(from p Product

(%%where (ProductID p)" in (514, 680, 707, 725)")

(select (ProductName p))

(select (ListPrice p))

(join offersG (SpecialOffersG p))

(define maxDiscount

(max (DiscountPct offersG)))

(select (>> maxDiscount #:as 'BestDiscount))

(select (>> (.* (ListPrice p)

(.- (val 1) maxDiscount))

#:as 'BestPrice))))

Show TableShow SQL select p.Name as ProductName   , p.ListPrice as ListPrice   , offer.__INJECT1 as BestDiscount   , (p.ListPrice * (1 - offer.__INJECT2)) as BestPrice from Product p left join (   select sop.ProductID as __INJECT0     , max(offer.DiscountPct) as __INJECT1     , max(offer.DiscountPct) as __INJECT2   from SpecialOffer offer   inner join SpecialOfferProduct sop      on (sop.SpecialOfferID = offer.SpecialOfferID)   group by sop.ProductID ) offer    on (offer.__INJECT0 = p.ProductID) where p.ProductID in (514, 680, 707, 725) ProductName ListPrice BestDiscount BestPrice LL Mountain Seat Assembly 133.34 #<sql-null> #<sql-null> HL Road Frame - Black, 58 1431.5 0.0 1431.5 Sport-100 Helmet, Red 34.99 0.15 29.741500000000002 LL Road Frame - Red, 44 337.22 0.35 219.193

procedure round  :  (token-constructor  [Number? -> Number?]  [Number? number? -> Number?])

The first argument is a Number? to be rounded. The second argument specifies how many decimal digits should be retained; this defaults to zero.

> (aw:show-table

(from x (%%subquery "select 1 as one")

(select (round (val 12.3456)))

(select (round (val 12.3456) 2))))

Show TableShow SQL select round(12.3456, 0)   , round(12.3456, 2) from (select 1 as one) x round(12.3456, 0) round(12.3456, 2) 12.0 12.35

procedure date+  :  (token-constructor  [Datetime? interval? ...+ -> Datetime?])

Adds each interval to the given datetime. The following example shows that the order of the intervals matters:

> (aw:show-table

(from x (%%subquery "select 1 as one")

(define feb-01

(val "2019-02-01" Datetime?))

(select (date+ feb-01 (months 1) (days 29)))

(select (date+ feb-01 (days 29) (months 1)))))

Show TableShow SQL select datetime('2019-02-01', '+1 month', '+29 day')   , datetime('2019-02-01', '+29 day', '+1 month') from (select 1 as one) x datetime('2019-02-01', '+1 month', '+29 day') datetime('2019-02-01', '+29 day', '+1 month') 2019-03-30 00:00:00 2019-04-02 00:00:00

For this reason, Plisqin provides no way to add intervals to each other. One possible implementation would preserve the sequence of intervals; another would eagerly combine them into one interval.

Note also that intervals may be dynamic, as in the following query:

> (aw:show-table

(from pc ProductCategory

(select (ProductCategoryID pc))

(select (ModifiedDate pc))

(select (>> (date+ (ModifiedDate pc)

(years (ProductCategoryID pc)))

#:as 'AddSomeYears))))

Show TableShow SQL select pc.ProductCategoryID as ProductCategoryID   , pc.ModifiedDate as ModifiedDate   , datetime(pc.ModifiedDate, +(pc.ProductCategoryID) || ' year') as AddSomeYears from ProductCategory pc ProductCategoryID ModifiedDate AddSomeYears 1 2008-04-30 00:00:00 2009-04-30 00:00:00 2 2008-04-30 00:00:00 2010-04-30 00:00:00 3 2008-04-30 00:00:00 2011-04-30 00:00:00 4 2008-04-30 00:00:00 2012-04-30 00:00:00

procedure date-  :  (token-constructor  [Datetime? interval? ...+ -> Datetime?])

Equivalent to calling date+ with each interval negated.

procedure years  :  (token-constructor  [Number? -> interval?]  [number? -> interval?])

Creates an interval?. See example on date+.

procedure months  :  (token-constructor  [Number? -> interval?]  [number? -> interval?])

Creates an interval?. See example on date+.

procedure days  :  (token-constructor  [Number? -> interval?]  [number? -> interval?])

Creates an interval?. See example on date+.

procedure hours  :  (token-constructor  [Number? -> interval?]  [number? -> interval?])

Creates an interval?. See example on date+.

procedure minutes  :  (token-constructor  [Number? -> interval?]  [number? -> interval?])

Creates an interval?. See example on date+.

procedure seconds  :  (token-constructor  [Number? -> interval?]  [number? -> interval?])

Creates an interval?. See example on date+.

procedure and  :  (token-constructor  [2bool? ...+ -> Bool?])

Boolean "and" – Produces true when all arguments are true. Usually prefixed .and.

procedure or  :  (token-constructor  [2bool? ...+ -> Bool?])

Boolean "or" – Produces true when any argument is true. Usually prefixed .or.

procedure not  :  (token-constructor  [2bool? -> Bool?])

Boolean "not" – Produces true when the argument is false. Usually prefixed .not.

procedure =  :  (token-constructor  [Number? Number? -> Bool?]  [String? String? -> Bool?]  [Datetime? Datetime? -> Bool?])

"Equals" – Produces true when its arguments are equal. Usually prefixed .=.

procedure <>  :  (token-constructor  [Number? Number? -> Bool?]  [String? String? -> Bool?]  [Datetime? Datetime? -> Bool?])

"Does not Equal" – Produces true when its arguments are not equal. Usually prefixed .<>.

procedure <  :  (token-constructor  [Number? Number? -> Bool?]  [String? String? -> Bool?]  [Datetime? Datetime? -> Bool?])

"Less Than" – Produces true when the first argument is less than the second. Usually prefixed .<.

procedure <=  :  (token-constructor  [Number? Number? -> Bool?]  [String? String? -> Bool?]  [Datetime? Datetime? -> Bool?])

"Less Than or Equal To" – Produces true when the first argument is less than or equal to the second. Usually prefixed .<=.

procedure >  :  (token-constructor  [Number? Number? -> Bool?]  [String? String? -> Bool?]  [Datetime? Datetime? -> Bool?])

"Greater Than" – Produces true when the first argument is greater than the second. Usually prefixed .>.

procedure >=  :  (token-constructor  [Number? Number? -> Bool?]  [String? String? -> Bool?]  [Datetime? Datetime? -> Bool?])

"Greater Than or Equal To" – Produces true when the first argument is greater than or equal to the second. Usually prefixed .>=.

procedure like  :  (token-constructor  [Number? Number? -> Bool?]  [String? String? -> Bool?]  [Datetime? Datetime? -> Bool?])

Usually prefixed .like. Returns true if the first argument matches the second. The use of % to match zero or more characters is guaranteed to be supported. Other matching patterns may exist; see "like" in your database’s documentation for more details.

> (aw:show-table

(from p Product

(where (.like (ProductNumber p)

(val "CS-%")))

(select (ProductName p))

(select (ProductNumber p))))

Show TableShow SQL select p.Name as ProductName   , p.ProductNumber as ProductNumber from Product p where (p.ProductNumber like 'CS-%') ProductName ProductNumber Chain Stays CS-2812 LL Crankset CS-4759 ML Crankset CS-6583 HL Crankset CS-9183

procedure not-like  :  (token-constructor  [Number? Number? -> Bool?]  [String? String? -> Bool?]  [Datetime? Datetime? -> Bool?])

Inverse of .like. Returns true if the first argument does not match the second. Usually prefixed .not-like.

procedure is  :  (token-constructor  [(or/c 'null Number?) (or/c 'null Number?) -> Bool?]  [(or/c 'null String?) (or/c 'null String?) -> Bool?]  [(or/c 'null Datetime?) (or/c 'null Datetime?) -> Bool?])

An equality test in which dbnull is considered equal to dbnull. The value 'null can be used as a constant representing dbnull. Assuming that foo and bar are both Scalar?s, the truth table is:

• (.is 'null 'null) – always true

• (.is foo 'null) – true when foo is dbnull

• (.is 'null bar) – true when bar is dbnull

• (.is foo bar) – true when foo equals bar, or when both foo and bar are dbnull

Unlike other comparisons, .is ignores any fallbacks because the comparison behavior of dbnull is already completely specified.

> (aw:show-table

(from p Product

(limit 3)

(select (ProductName p))

(select (ProductNumber p))

(select (Color p))

(where (.is (Color p) 'null))))

Show TableShow SQL select p.Name as ProductName   , p.ProductNumber as ProductNumber   , p.Color as Color from Product p where (p.Color is null) limit 3 ProductName ProductNumber Color Adjustable Race AR-5381 #<sql-null> Bearing Ball BA-8327 #<sql-null> BB Ball Bearing BE-2349 #<sql-null>

> (aw:show-table

(from p Product

(limit 3)

(select (ProductName p))

(select (ProductNumber p))

(select (Color p))

(where (.is (Color p) (val "Silver")))))

Show TableShow SQL select p.Name as ProductName   , p.ProductNumber as ProductNumber   , p.Color as Color from Product p where (p.Color is not null and (p.Color = 'Silver')) limit 3 ProductName ProductNumber Color Chainring Bolts CB-2903 Silver Chainring Nut CN-6137 Silver Freewheel FH-2981 Silver

procedure is-not

:  (token-constructor  [(or/c 'null Number?) (or/c 'null Number?) -> Bool?]  [(or/c 'null String?) (or/c 'null String?) -> Bool?]  [(or/c 'null Datetime?) (or/c 'null Datetime?) -> Bool?])

The expression (.is-not a b) is always equivalent to (.not (.is a b)).

procedure +  :  (token-constructor  [Number? ...+ -> Number?])

Numeric addition. Usually prefixed .+.

procedure -  :  (token-constructor  [Number? ...+ -> Number?])

Numeric subtraction. Usually prefixed .-.

procedure *  :  (token-constructor  [Number? ...+ -> Number?])

Numeric multiplication. Usually prefixed .*

procedure /  :  (token-constructor  [Number? ...+ -> Number?])

Numeric division. Usually prefixed ./

> (aw:show-table

(from pc ProductCategory

(define pcid

(ProductCategoryID pc))

(select pcid)

(select (.+ pcid (val 1)))

(select (.- pcid (val 1)))

(select (.* pcid (val 10)))

(select (./ pcid (val 2)))))

Show TableShow SQL select pc.ProductCategoryID as ProductCategoryID   , (pc.ProductCategoryID + 1)   , (pc.ProductCategoryID - 1)   , (pc.ProductCategoryID * 10)   , (pc.ProductCategoryID / 2) from ProductCategory pc ProductCategoryID (pc.ProductCategoryID + 1) (pc.ProductCategoryID - 1) (pc.ProductCategoryID * 10) (pc.ProductCategoryID / 2) 1 2 0 10 0 2 3 1 20 1 3 4 2 30 1 4 5 3 40 2