QueryExpressions.lhs

[[query-expressions]]
= Query expressions

We can now start on the 'select' parser. In the SQL standard, it
refers to these things as 'query expressions' to distinguish then from
'value expressions', so we will reuse this language here.

The subset of SQL we will support is this:

TODO: write lots of examples here

select queries only, no union, intersect or except. No common table
expressions.

we will support all the value expressions that the value expression
parser above supports

we will support select lists with optional aliases, with the 'as'
optional in the alias

we will support the * as in 'select * from t', and the variation
'select t.* from t', but not the alias version select * as (a,b,c)
from t.

we support two part dotted identifiers in value expressions, but no
other sort (such as 3-part dotted value expression identifiers, or
schema qualified table names or function names).

for the from clause, we will only support optional 'from table_name'.

supports where

we will support regular group by lists, but not the new group by
options in SQL2003 (group by (), grouping sets, cube, rollup).

we will support having

we support order by, with multiple columns, but not explicit asc or
desc , and no 'nulls first' or 'nulls last' syntax.


No support for offset and fetch first, or variations.

> {-# LANGUAGE TupleSections #-}
> module QueryExpressions where
>
> --import Text.Groom (groom)
> --import qualified Text.Parsec as P
> import Text.Parsec.String (Parser)
> import Text.Parsec (try,optionMaybe, optional, sepBy1,option)
> import Control.Applicative ((<$>),(*>),(<*>))
> import Control.Monad (void,guard)
> --import Debug.Trace
> --import Data.List (intercalate)
> import Data.Maybe ()
> import qualified Test.HUnit as H
> import FunctionsAndTypesForParsing

> import ValueExpressions (ValueExpr(..), valueExpr, identifier, symbol, keyword, comma)

Here is the datatype for query expressions to get started with. In
this tutorial, we will only support an optional single table in the
from clause, and this will be expanded in the next tutorial.

TODO: rearrange the from/where/groupby/having/orderby to a separate
datatype which is optional in a select, and the from part is mandatory
in this new type. This follows the standard and is more accurate
syntax.

> data QueryExpr
>     = Select
>       {qeSelectList :: [(ValueExpr,Maybe String)]
>       ,qeFrom :: Maybe String
>       ,qeWhere :: Maybe ValueExpr
>       ,qeGroupBy :: [ValueExpr]
>       ,qeHaving :: Maybe ValueExpr
>       ,qeOrderBy :: [ValueExpr]
>       } deriving (Eq,Show)

Here is a default value which can be used to easily construct query
expression values.

> makeSelect :: QueryExpr
> makeSelect = Select {qeSelectList = []
>                     ,qeFrom = Nothing
>                     ,qeWhere = Nothing
>                     ,qeGroupBy = []
>                     ,qeHaving = Nothing
>                     ,qeOrderBy = []}

== select lists

Let's start with something simple:

```
select [value expr]
```

TODO: shorten the names of these examples

> singleSelectItemTests :: [(String,QueryExpr)]
> singleSelectItemTests =
>     [("select 1", makeSelect {qeSelectList = [(NumLit 1,Nothing)]})]

Here are a couple of wrappers for `symbol` and `keyword` which wrap
them with void.

> keyword_ :: String -> Parser ()
> keyword_ = void . keyword

> symbol_ :: String -> Parser ()
> symbol_ = void . symbol

> singleSelectItem :: Parser QueryExpr
> singleSelectItem = do
>     keyword_ "select"
>     e <- valueExpr []
>     return $ makeSelect {qeSelectList = [(e,Nothing)]}

You can use the old test runner to check these:

```
*QueryExpressions> H.runTestTT $ H.TestList $ map (makeTest singleSelectItem) parseSingleSelectItemTestData
Cases: 1  Tried: 1  Errors: 0  Failures: 0
Counts {cases = 1, tried = 1, errors = 0, failures = 0}
```

Let's rewrite it in the Applicative and mostly point free style.

> singleSelectItemApplicative :: Parser QueryExpr
> singleSelectItemApplicative =
>     (\sl -> makeSelect {qeSelectList = sl})
>     <$> (keyword_ "select" *> (((:[]) . (,Nothing)) <$> valueExpr []))

That didn't go so well. Using a wrapper:

> singleSelectItemApplicative' :: Parser QueryExpr
> singleSelectItemApplicative' =
>     ms <$> (keyword_ "select" *> valueExpr [])
>   where
>     ms e = makeSelect {qeSelectList = [(e,Nothing)]}

Now let's write something that supports multiple value expressions,
e.g.

```
select 1+2, 3+4;
```

> multipleSelectItemsTests :: [(String,QueryExpr)]
> multipleSelectItemsTests =
>     [("select a"
>      ,makeSelect {qeSelectList = [(Iden "a",Nothing)]})
>     ,("select a,b"
>      ,makeSelect {qeSelectList = [(Iden "a",Nothing)
>                                  ,(Iden "b",Nothing)]})
>     ,("select 1+2,3+4"
>      ,makeSelect {qeSelectList =
>                      [(BinOp (NumLit 1) "+" (NumLit 2),Nothing)
>                      ,(BinOp (NumLit 3) "+" (NumLit 4),Nothing)]})
>     ]

> selectMultipleItems :: Parser QueryExpr
> selectMultipleItems = do
>     keyword_ "select"
>     es <- commaSep1 (valueExpr [])
>     return $ makeSelect {qeSelectList = map (,Nothing) es}

> commaSep1 :: Parser a -> Parser [a]
> commaSep1 = (`sepBy1` comma)

=== aliases

We can write names for the columns produced from a select list using
the keyword `as`, and we can miss out the `as`:

```sql
select a as a1, b as b1, f(c) as c1;

-- no as
select a a1, b b1;
```

> selectListTests :: [(String,QueryExpr)]
> selectListTests =
>     [("select a as a1, b as b1"
>      ,makeSelect {qeSelectList = [(Iden "a", Just "a1")
>                                  ,(Iden "b", Just "b1")]})
>     ,("select a a1, b b1"
>      ,makeSelect {qeSelectList = [(Iden "a", Just "a1")
>                                  ,(Iden "b", Just "b1")]})
>     ] ++ multipleSelectItemsTests
>       ++ singleSelectItemTests

Finally, here is the select list parser and the helper for select
items:

> selectItem0 :: Parser (ValueExpr, Maybe String)
> selectItem0 = (,) <$> valueExpr [] <*> optionMaybe (try alias)
>   where alias = optional (keyword_ "as") *> identifier

> selectList0 :: Parser [(ValueExpr, Maybe String)]
> selectList0 = keyword_ "select" *> commaSep1 selectItem0

> queryExpr0 :: Parser QueryExpr
> queryExpr0 = mkSelect <$> selectList0
>   where mkSelect sl = makeSelect {qeSelectList = sl}

== from clause

> from :: Parser String
> from = keyword_ "from" *> identifier

> fromTests :: [(String,QueryExpr)]
> fromTests =
>     [("select a from t"
>      ,makeSelect {qeSelectList = [(Iden "a",Nothing)]
>                  ,qeFrom = (Just "t")})]

> queryExpr1 :: Parser QueryExpr
> queryExpr1 = mkSelect
>              <$> selectList0
>              <*> optionMaybe from
>   where mkSelect sl fr = makeSelect {qeSelectList = sl
>                                     ,qeFrom = fr}

```
*QueryExpressions> H.runTestTT $ H.TestList $ map (makeTest queryExpr1) (selectListTests ++ fromTests)
 ### Failure in: 6:select a from t
(line 1, column 16):
unexpected end of input
expecting digit, letter, "_", "--" or "/*"
Cases: 7  Tried: 7  Errors: 0  Failures: 1
Counts {cases = 7, tried = 7, errors = 0, failures = 1}
```

This is a keyword issue again. We are parsing the `from` as if it was
a column alias and then getting stuck.

> blackListIdentifier :: [String] -> Parser String
> blackListIdentifier bl = do
>     i <- identifier
>     guard (i `notElem` bl)
>     return i

> selectItem :: Parser (ValueExpr, Maybe String)
> selectItem = (,) <$> valueExpr [] <*> optionMaybe (try alias)
>   where alias = optional (keyword_ "as") *> blackListIdentifier ["from"]

> selectList :: Parser [(ValueExpr, Maybe String)]
> selectList = keyword_ "select" *> commaSep1 selectItem

> queryExpr2 :: Parser QueryExpr
> queryExpr2 = mkSelect
>              <$> selectList
>              <*> optionMaybe from
>   where mkSelect sl fr = makeSelect {qeSelectList = sl
>                                     ,qeFrom = fr}

That did the job for now.

== where

The where, group by, having, and order by parsers are simple.

> whereTests :: [(String,QueryExpr)]
> whereTests =
>     [("select a from t where a = 5"
>      ,makeSelect {qeSelectList = [(Iden "a",Nothing)]
>                  ,qeFrom = Just "t"
>                  ,qeWhere = Just $ BinOp (Iden "a") "=" (NumLit 5)})
>     ]

> whereClause :: Parser ValueExpr
> whereClause = keyword_ "where" *> valueExpr []

> queryExpr3 :: Parser QueryExpr
> queryExpr3 = mkSelect
>              <$> selectList
>              <*> optionMaybe from
>              <*> optionMaybe whereClause
>   where mkSelect sl fr wh =
>             makeSelect {qeSelectList = sl
>                        ,qeFrom = fr
>                        ,qeWhere = wh}

== group by

> groupByTests :: [(String,QueryExpr)]
> groupByTests =
>     [("select a,sum(b) from t group by a"
>      ,makeSelect {qeSelectList = [(Iden "a",Nothing)
>                                  ,(App "sum" [Iden "b"],Nothing)]
>                  ,qeFrom = Just "t"
>                  ,qeGroupBy = [Iden "a"]
>                  })
>     ,("select a,b,sum(c) from t group by a,b"
>      ,makeSelect {qeSelectList = [(Iden "a",Nothing)
>                                  ,(Iden "b",Nothing)
>                                  ,(App "sum" [Iden "c"],Nothing)]
>                  ,qeFrom = Just "t"
>                  ,qeGroupBy = [Iden "a",Iden "b"]
>                  })
>     ]

> groupByClause :: Parser [ValueExpr]
> groupByClause = keyword_ "group" *> keyword_ "by"
>                 *> commaSep1 (valueExpr [])

> queryExpr4 :: Parser QueryExpr
> queryExpr4 = mkSelect
>              <$> selectList
>              <*> optionMaybe from
>              <*> optionMaybe whereClause
>              <*> option [] groupByClause
>   where mkSelect sl fr wh gr =
>             makeSelect {qeSelectList = sl
>                        ,qeFrom = fr
>                        ,qeWhere = wh
>                        ,qeGroupBy = gr}

== having

> havingTests :: [(String,QueryExpr)]
> havingTests =
>   [("select a,sum(b) from t group by a having sum(b) > 5"
>      ,makeSelect {qeSelectList = [(Iden "a",Nothing)
>                                  ,(App "sum" [Iden "b"],Nothing)]
>                  ,qeFrom = (Just "t")
>                  ,qeGroupBy = [Iden "a"]
>                  ,qeHaving = Just $ BinOp (App "sum" [Iden "b"]) ">" (NumLit 5)
>                  })
>   ]

> having :: Parser ValueExpr
> having = keyword_ "having" *> (valueExpr [])

> queryExpr5 :: Parser QueryExpr
> queryExpr5 = mkSelect
>              <$> selectList
>              <*> optionMaybe from
>              <*> optionMaybe whereClause
>              <*> option [] groupByClause
>              <*> optionMaybe having
>   where mkSelect sl fr wh gr hv =
>             makeSelect {qeSelectList = sl
>                        ,qeFrom = fr
>                        ,qeWhere = wh
>                        ,qeGroupBy = gr
>                        ,qeHaving = hv}

Looking nice so far. Did you run the tests for each stage?


== order by

> orderByTests :: [(String,QueryExpr)]
> orderByTests =
>     [("select a from t order by a"
>      ,ms [Iden "a"])
>     ,("select a from t order by a, b"
>      ,ms [Iden "a", Iden "b"])
>     ]
>   where
>     ms o = makeSelect {qeSelectList = [(Iden "a",Nothing)]
>                       ,qeFrom = (Just "t")
>                       ,qeOrderBy = o}

> orderBy :: Parser [ValueExpr]
> orderBy = keyword_ "order" *> keyword_ "by"
>           *> commaSep1 (valueExpr [])

> queryExpr6 :: Parser QueryExpr
> queryExpr6 = Select
>              <$> selectList
>              <*> optionMaybe from
>              <*> optionMaybe whereClause
>              <*> option [] groupByClause
>              <*> optionMaybe having
>              <*> option [] orderBy

```
*QueryExpressions> H.runTestTT $ H.TestList $ map (makeTest queryExpr6) (selectListTests ++ fromTests ++ whereTests ++ groupByTests ++ havingTests ++ orderByTests)
Cases: 13  Tried: 13  Errors: 0  Failures: 0
Counts {cases = 13, tried = 13, errors = 0, failures = 0}
```

TODO: talk about putting the maybes/default values inside from, where, etc??