simple-sql-parser 0.1.0.0 → 0.2.0
raw patch · 18 files changed
+2469/−1383 lines, 18 files
Files
- Language/SQL/SimpleSQL/Fixity.lhs +39/−32
- Language/SQL/SimpleSQL/Parser.lhs +227/−98
- Language/SQL/SimpleSQL/Pretty.lhs +105/−46
- Language/SQL/SimpleSQL/Syntax.lhs +136/−60
- README +1/−1
- changelog +29/−0
- simple-sql-parser.cabal +18/−8
- tools/Language/SQL/SimpleSQL/FullQueries.lhs +39/−0
- tools/Language/SQL/SimpleSQL/GroupBy.lhs +233/−0
- tools/Language/SQL/SimpleSQL/Postgres.lhs +269/−0
- tools/Language/SQL/SimpleSQL/QueryExprComponents.lhs +202/−0
- tools/Language/SQL/SimpleSQL/QueryExprs.lhs +18/−0
- tools/Language/SQL/SimpleSQL/ScalarExprs.lhs +304/−0
- tools/Language/SQL/SimpleSQL/TableRefs.lhs +104/−0
- tools/Language/SQL/SimpleSQL/TestTypes.lhs +26/−0
- tools/Language/SQL/SimpleSQL/Tests.lhs +36/−463
- tools/Language/SQL/SimpleSQL/Tpch.lhs +683/−0
- tools/Tpch.lhs +0/−675
Language/SQL/SimpleSQL/Fixity.lhs view
@@ -64,7 +64,7 @@ > fixFixities fs se = > runIdentity $ toSql <$> HSE.applyFixities (toHSEFixity fs) (toHaskell se) -Now have to convert all our scalar exprs to haskell and back again.+Now have to convert all our scalar exprs to Haskell and back again. Have to come up with a recipe for each ctor. Only continue if you have a strong stomach. Probably would have been less effort to just write the fixity code.@@ -73,41 +73,39 @@ > toHaskell e = case e of > BinOp e0 op e1 -> HSE.InfixApp > (toHaskell e0)-> (HSE.QVarOp $ sym op)+> (HSE.QVarOp $ sym $ name op) > (toHaskell e1) > Iden {} -> str ('v':show e) > StringLit {} -> str ('v':show e) > NumLit {} -> str ('v':show e)-> App n es -> HSE.App (var ('f':n)) $ ltoh es+> App n es -> HSE.App (var ('f':name n)) $ ltoh es > Parens e0 -> HSE.Paren $ toHaskell e0 > IntervalLit {} -> str ('v':show e)-> Iden2 {} -> str ('v':show e) > Star -> str ('v':show e)-> Star2 {} -> str ('v':show e) > AggregateApp nm d es od ->-> HSE.App (var ('a':nm))-> $ HSE.List [str $ show (d,map snd od)+> HSE.App (var ('a':name nm))+> $ HSE.List [str $ show (d,orderInf od) > ,HSE.List $ map toHaskell es-> ,HSE.List $ map (toHaskell . fst) od]-> WindowApp nm es pb od ->-> HSE.App (var ('w':nm))-> $ HSE.List [str $ show (map snd od)+> ,HSE.List $ orderExps od]+> WindowApp nm es pb od r ->+> HSE.App (var ('w':name nm))+> $ HSE.List [str $ show (orderInf od, r) > ,HSE.List $ map toHaskell es > ,HSE.List $ map toHaskell pb-> ,HSE.List $ map (toHaskell . fst) od]+> ,HSE.List $ orderExps od] > PrefixOp nm e0 ->-> HSE.App (HSE.Var $ sym nm) (toHaskell e0)+> HSE.App (HSE.Var $ sym $ name nm) (toHaskell e0) > PostfixOp nm e0 ->-> HSE.App (HSE.Var $ sym ('p':nm)) (toHaskell e0)+> HSE.App (HSE.Var $ sym ('p':name nm)) (toHaskell e0) > SpecialOp nm es ->-> HSE.App (var ('s':nm)) $ HSE.List $ map toHaskell es+> HSE.App (var ('s':name nm)) $ HSE.List $ map toHaskell es > -- map the two maybes to lists with either 0 or 1 element > Case v ts el -> HSE.App (var "$case") > (HSE.List [ltoh $ maybeToList v-> ,HSE.List $ map (ltoh . (\(a,b) -> [a,b])) ts+> ,HSE.List $ map (ltoh . (\(a,b) -> b:a)) ts > ,ltoh $ maybeToList el]) > Cast e0 tn -> HSE.App (str ('c':show tn)) $ toHaskell e0-> CastOp {} -> str ('v':show e)+> TypedLit {} -> str ('v':show e) > SubQueryExpr {} -> str ('v': show e) > In b e0 (InList l) -> > HSE.App (str ('i':show b))@@ -118,43 +116,49 @@ > str = HSE.Lit . HSE.String > var = HSE.Var . HSE.UnQual . HSE.Ident > sym = HSE.UnQual . HSE.Symbol+> name n = case n of+> QName q -> '"' : q+> Name m -> m+> orderExps = map (toHaskell . (\(OrderField a _ _) -> a))+> orderInf = map (\(OrderField _ b c) -> (b,c)) ++ > toSql :: HSE.Exp -> ScalarExpr > toSql e = case e of > HSE.InfixApp e0 (HSE.QVarOp (HSE.UnQual (HSE.Symbol n))) e1 ->-> BinOp (toSql e0) n (toSql e1)+> BinOp (toSql e0) (unname n) (toSql e1) > HSE.Lit (HSE.String ('v':l)) -> read l > HSE.App (HSE.Var (HSE.UnQual (HSE.Ident ('f':i))))-> (HSE.List es) -> App i $ map toSql es+> (HSE.List es) -> App (unname i) $ map toSql es > HSE.Paren e0 -> Parens $ toSql e0 > HSE.App (HSE.Var (HSE.UnQual (HSE.Ident ('a':i)))) > (HSE.List [HSE.Lit (HSE.String vs) > ,HSE.List es > ,HSE.List od]) ->-> let (d,dir) = read vs-> in AggregateApp i d (map toSql es)-> $ zip (map toSql od) dir+> let (d,oinf) = read vs+> in AggregateApp (unname i) d (map toSql es)+> $ sord oinf od > HSE.App (HSE.Var (HSE.UnQual (HSE.Ident ('w':i)))) > (HSE.List [HSE.Lit (HSE.String vs) > ,HSE.List es > ,HSE.List pb > ,HSE.List od]) ->-> let dir = read vs-> in WindowApp i (map toSql es)-> (map toSql pb)-> $ zip (map toSql od) dir+> let (oinf,r) = read vs+> in WindowApp (unname i) (map toSql es) (map toSql pb)+> (sord oinf od) r > HSE.App (HSE.Var (HSE.UnQual (HSE.Symbol ('p':nm)))) e0 ->-> PostfixOp nm $ toSql e0+> PostfixOp (unname nm) $ toSql e0 > HSE.App (HSE.Var (HSE.UnQual (HSE.Symbol nm))) e0 ->-> PrefixOp nm $ toSql e0+> PrefixOp (unname nm) $ toSql e0 > HSE.App (HSE.Var (HSE.UnQual (HSE.Ident ('s':nm)))) (HSE.List es) ->-> SpecialOp nm $ map toSql es+> SpecialOp (unname nm) $ map toSql es > HSE.App (HSE.Var (HSE.UnQual (HSE.Ident "$case"))) > (HSE.List [v,ts,el]) ->-> Case (ltom v) (pairs ts) (ltom el)+> Case (ltom v) (whens ts) (ltom el) > HSE.App (HSE.Lit (HSE.String ('c':nm))) e0 -> > Cast (toSql e0) (read nm) > HSE.App (HSE.Lit (HSE.String ('i':nm)))@@ -165,10 +169,13 @@ > in In b (toSql e0) sq > _ -> err e > where+> sord = zipWith (\(i0,i1) ce -> OrderField (toSql ce) i0 i1) > ltom (HSE.List []) = Nothing > ltom (HSE.List [ex]) = Just $ toSql ex > ltom ex = err ex-> pairs (HSE.List l) = map (\(HSE.List [a,b]) -> (toSql a, toSql b)) l-> pairs ex = err ex+> whens (HSE.List l) = map (\(HSE.List (t:ws)) -> (map toSql ws, toSql t)) l+> whens ex = err ex > err :: Show a => a -> e > err a = error $ "simple-sql-parser: internal fixity error " ++ show a+> unname ('"':nm) = QName nm+> unname n = Name n
Language/SQL/SimpleSQL/Parser.lhs view
@@ -12,6 +12,7 @@ > import Data.Char > import Text.Parsec hiding (ParseError) > import qualified Text.Parsec as P+> import Text.Parsec.Perm > import Language.SQL.SimpleSQL.Syntax > import Language.SQL.SimpleSQL.Fixity@@ -19,23 +20,33 @@ The public API functions. > -- | Parses a query expr, trailing semicolon optional.-> parseQueryExpr :: FilePath -- ^ filename to use in errors-> -> Maybe (Int,Int) -- ^ line number and column number to use in errors-> -> String -- ^ the SQL source to parse+> parseQueryExpr :: FilePath+> -- ^ filename to use in errors+> -> Maybe (Int,Int)+> -- ^ line number and column number to use in errors+> -> String+> -- ^ the SQL source to parse > -> Either ParseError QueryExpr > parseQueryExpr = wrapParse topLevelQueryExpr -> -- | Parses a list of query exprs, with semi colons between them. The final semicolon is optional.-> parseQueryExprs :: FilePath -- ^ filename to use in errors-> -> Maybe (Int,Int) -- ^ line number and column number to use in errors-> -> String -- ^ the SQL source to parse+> -- | Parses a list of query exprs, with semi colons between+> -- them. The final semicolon is optional.+> parseQueryExprs :: FilePath+> -- ^ filename to use in errors+> -> Maybe (Int,Int)+> -- ^ line number and column number to use in errors+> -> String+> -- ^ the SQL source to parse > -> Either ParseError [QueryExpr] > parseQueryExprs = wrapParse queryExprs > -- | Parses a scalar expression.-> parseScalarExpr :: FilePath -- ^ filename to use in errors-> -> Maybe (Int,Int) -- ^ line number and column number to use in errors-> -> String -- ^ the SQL source to parse+> parseScalarExpr :: FilePath+> -- ^ filename to use in errors+> -> Maybe (Int,Int)+> -- ^ line number and column number to use in errors+> -> String+> -- ^ the SQL source to parse > -> Either ParseError ScalarExpr > parseScalarExpr = wrapParse scalarExpr @@ -57,10 +68,15 @@ > -- | Type to represent parse errors. > data ParseError = ParseError-> {peErrorString :: String -- ^ contains the error message-> ,peFilename :: FilePath -- ^ filename location for the error-> ,pePosition :: (Int,Int) -- ^ line number and column number location for the error-> ,peFormattedError :: String -- ^ formatted error with the position, error message and source context+> {peErrorString :: String+> -- ^ contains the error message+> ,peFilename :: FilePath+> -- ^ filename location for the error+> ,pePosition :: (Int,Int)+> -- ^ line number and column number location for the error+> ,peFormattedError :: String+> -- ^ formatted error with the position, error+> -- message and source context > } deriving (Eq,Show) ------------------------------------------------@@ -84,37 +100,41 @@ or interval '5' month +wrap the whole lot in try, in case we get something like this:+interval '3 days'+which parses as a typed literal+ > interval :: P ScalarExpr-> interval = try (keyword_ "interval") >>+> interval = try (keyword_ "interval" >> > IntervalLit > <$> stringLiteral > <*> identifierString-> <*> optionMaybe (try $ parens integerLiteral)+> <*> optionMaybe (try $ parens integerLiteral)) > literal :: P ScalarExpr > literal = number <|> estring <|> interval == identifiers -Uses the identifierString 'lexer'. See this function for notes on identifiers.--> identifier :: P ScalarExpr-> identifier = Iden <$> identifierString+Uses the identifierString 'lexer'. See this function for notes on+identifiers. -Identifier with one dot in it. This should be extended to any amount-of dots.+> name :: P Name+> name = choice [QName <$> quotedIdentifier+> ,Name <$> identifierString] -> dottedIden :: P ScalarExpr-> dottedIden = Iden2 <$> identifierString-> <*> (symbol "." *> identifierString)+> identifier :: P ScalarExpr+> identifier = Iden <$> name == star -used in select *, select x.*, and agg(*) variations.+used in select *, select x.*, and agg(*) variations, and some other+places as well. Because it is quite general, the parser doesn't+attempt to check that the star is in a valid context, it parses it OK+in any scalar expression context. > star :: P ScalarExpr-> star = choice [Star <$ symbol "*"-> ,Star2 <$> (identifierString <* symbol "." <* symbol "*")]+> star = Star <$ symbol "*" == function application, aggregates and windows @@ -129,10 +149,9 @@ > aggOrApp :: P ScalarExpr > aggOrApp = > makeApp-> <$> identifierString+> <$> name > <*> parens ((,,) <$> try duplicates-> <*> choice [(:[]) <$> try star-> ,commaSep scalarExpr']+> <*> choice [commaSep scalarExpr'] > <*> try (optionMaybe orderBy)) > where > makeApp i (Nothing,es,Nothing) = App i es@@ -158,10 +177,36 @@ > try (keyword_ "over") > *> parens (WindowApp f es > <$> option [] partitionBy-> <*> option [] orderBy)+> <*> option [] orderBy+> <*> optionMaybe frameClause) > where > partitionBy = try (keyword_ "partition") >> > keyword_ "by" >> commaSep1 scalarExpr'+> frameClause =+> mkFrame <$> choice [FrameRows <$ keyword_ "rows"+> ,FrameRange <$ keyword_ "range"]+> <*> frameStartEnd+> frameStartEnd =+> choice+> [try (keyword_ "between") >>+> mkFrameBetween <$> frameLimit True+> <*> (keyword_ "and" *> frameLimit True)+> ,mkFrameFrom <$> frameLimit False]+> -- use the bexpression style from the between parsing for frame between+> frameLimit useB =+> choice+> [Current <$ try (keyword_ "current") <* keyword_ "row"+> ,try (keyword_ "unbounded") >>+> choice [UnboundedPreceding <$ keyword_ "preceding"+> ,UnboundedFollowing <$ keyword_ "following"]+> ,do+> e <- if useB then scalarExprB else scalarExpr+> choice [Preceding e <$ keyword_ "preceding"+> ,Following e <$ keyword_ "following"]+> ]+> mkFrameBetween s e rs = FrameBetween rs s e+> mkFrameFrom s rs = FrameFrom rs s+> mkFrame rs c = c rs > windowSuffix _ = fail "" > app :: P ScalarExpr@@ -177,7 +222,8 @@ > <* keyword_ "end" > where > swhen = keyword_ "when" *>-> ((,) <$> scalarExpr' <*> (keyword_ "then" *> scalarExpr'))+> ((,) <$> commaSep1 scalarExpr'+> <*> (keyword_ "then" *> scalarExpr')) == miscellaneous keyword operators @@ -194,16 +240,16 @@ > parensCast = try (keyword_ "cast") >> > parens (Cast <$> scalarExpr' > <*> (keyword_ "as" *> typeName))-> prefixCast = try (CastOp <$> typeName+> prefixCast = try (TypedLit <$> typeName > <*> stringLiteral) extract(id from expr) > extract :: P ScalarExpr > extract = try (keyword_ "extract") >>-> parens (makeOp <$> identifierString+> parens (makeOp <$> name > <*> (keyword_ "from" *> scalarExpr'))-> where makeOp n e = SpecialOp "extract" [Iden n, e]+> where makeOp n e = SpecialOp (Name "extract") [Iden n, e] substring(x from expr to expr) @@ -215,7 +261,7 @@ > <*> (keyword_ "from" *> scalarExpr') > <*> (keyword_ "for" *> scalarExpr') > )-> where makeOp a b c = SpecialOp "substring" [a,b,c]+> where makeOp a b c = SpecialOp (Name "substring") [a,b,c] in: two variations: a in (expr0, expr1, ...)@@ -247,7 +293,7 @@ > betweenSuffix :: ScalarExpr -> P ScalarExpr > betweenSuffix e =-> makeOp <$> opName+> makeOp <$> (Name <$> opName) > <*> return e > <*> scalarExpr'' True > <*> (keyword_ "and" *> scalarExpr'' True)@@ -281,12 +327,22 @@ > <$ try (keyword_ "double" <* keyword_ "precision") > ,TypeName "character varying" > <$ try (keyword_ "character" <* keyword_ "varying")-> ,TypeName <$> identifierString]+> ,TypeName <$> identifierString] >>= optionSuffix precision+> where+> precision t = try (parens (commaSep integerLiteral)) >>= makeWrap t+> makeWrap (TypeName t) [a] = return $ PrecTypeName t a+> makeWrap (TypeName t) [a,b] = return $ PrecScaleTypeName t a b+> makeWrap _ _ = fail "there must be one or two precision components" -== scalar parens +== scalar parens and row ctor+ > sparens :: P ScalarExpr-> sparens = Parens <$> parens scalarExpr'+> sparens =+> ctor <$> parens (commaSep1 scalarExpr')+> where+> ctor [a] = Parens a+> ctor as = SpecialOp (Name "rowctor") as == operator parsing@@ -303,8 +359,10 @@ > binOpSymbolNames :: [String] > binOpSymbolNames = > ["=", "<=", ">=", "!=", "<>", "<", ">"-> ,"*", "/", "+", "-"-> ,"||"]+> ,"*", "/", "+", "-", "%"+> ,"||", "."+> ,"^", "|", "&"+> ] > binOpKeywordNames :: [String] > binOpKeywordNames = ["and", "or", "like", "overlaps"]@@ -328,7 +386,7 @@ > prefixUnOpKeywordNames = ["not"] > prefixUnOpSymbolNames :: [String]-> prefixUnOpSymbolNames = ["+", "-"]+> prefixUnOpSymbolNames = ["+", "-", "~"] There aren't any single keyword postfix operators currently supported. Maybe all these 'is's can be left factored?@@ -347,7 +405,7 @@ > prefixUnaryOp :: P ScalarExpr > prefixUnaryOp =-> PrefixOp <$> opSymbol <*> scalarExpr'+> PrefixOp <$> (Name <$> opSymbol) <*> scalarExpr' > where > opSymbol = choice (map (try . symbol) prefixUnOpSymbolNames > ++ map (try . keyword) prefixUnOpKeywordNames)@@ -361,7 +419,7 @@ > try $ choice $ map makeOp opPairs > where > opPairs = flip map postfixOpKeywords $ \o -> (o, words o)-> makeOp (o,ws) = try $ PostfixOp o e <$ keywords_ ws+> makeOp (o,ws) = try $ PostfixOp (Name o) e <$ keywords_ ws > keywords_ = try . mapM_ keyword_ All the binary operators are parsed as same precedence and left@@ -369,7 +427,7 @@ > binaryOperatorSuffix :: Bool -> ScalarExpr -> P ScalarExpr > binaryOperatorSuffix bExpr e0 =-> BinOp e0 <$> opSymbol <*> factor+> BinOp e0 <$> (Name <$> opSymbol) <*> factor > where > opSymbol = choice > (map (try . symbol) binOpSymbolNames@@ -388,7 +446,8 @@ > ++ prefixUnOpKeywordNames ++ prefixUnOpSymbolNames > ++ postfixOpKeywords > -- these are the ops with the highest precedence in order-> highPrec = [infixl_ ["*","/"]+> highPrec = [infixl_ ["."]+> ,infixl_ ["*","/", "%"] > ,infixl_ ["+", "-"] > ,infixl_ ["<=",">=","!=","<>","||","like"] > ]@@ -428,7 +487,7 @@ > ,subquery > ,prefixUnaryOp > ,try app-> ,try dottedIden+> ,try star > ,identifier > ,sparens] @@ -458,21 +517,25 @@ expression tree (for efficiency and code clarity). > scalarExpr :: P ScalarExpr-> scalarExpr =-> choice [try star-> ,fixFixities sqlFixities <$> scalarExpr']+> scalarExpr = fixFixities sqlFixities <$> scalarExpr' +expose the b expression for window frame clause range between++> scalarExprB :: P ScalarExpr+> scalarExprB = fixFixities sqlFixities <$> scalarExpr'' True++ ------------------------------------------------- = query expressions == select lists -> selectItem :: P (Maybe String, ScalarExpr)-> selectItem = flip (,) <$> scalarExpr <*> optionMaybe (try alias)-> where alias = optional (try (keyword_ "as")) *> identifierString+> selectItem :: P (Maybe Name, ScalarExpr)+> selectItem = flip (,) <$> scalarExpr <*> optionMaybe (try als)+> where als = optional (try (keyword_ "as")) *> name -> selectList :: P [(Maybe String,ScalarExpr)]+> selectList :: P [(Maybe Name,ScalarExpr)] > selectList = commaSep1 selectItem == from@@ -490,35 +553,43 @@ > tref = nonJoinTref >>= optionSuffix joinTrefSuffix > nonJoinTref = choice [try (TRQueryExpr <$> parens queryExpr) > ,TRParens <$> parens tref-> ,TRSimple <$> identifierString]+> ,TRLateral <$> (try (keyword_ "lateral")+> *> nonJoinTref)+> ,try (TRFunction <$> name+> <*> parens (commaSep scalarExpr))+> ,TRSimple <$> name] > >>= optionSuffix aliasSuffix-> aliasSuffix j =-> let tableAlias = optional (try $ keyword_ "as") *> identifierString-> columnAliases = optionMaybe $ try $ parens-> $ commaSep1 identifierString-> in option j (TRAlias j <$> try tableAlias <*> try columnAliases)+> aliasSuffix j = option j (TRAlias j <$> alias) > joinTrefSuffix t = (do > nat <- option False $ try (True <$ try (keyword_ "natural")) > TRJoin t <$> joinType > <*> nonJoinTref > <*> optionMaybe (joinCondition nat)) > >>= optionSuffix joinTrefSuffix-> joinType = choice-> [JCross <$ try (keyword_ "cross")-> ,JInner <$ try (keyword_ "inner")-> ,choice [JLeft <$ try (keyword_ "left")-> ,JRight <$ try (keyword_ "right")-> ,JFull <$ try (keyword_ "full")]-> <* optional (try $ keyword_ "outer")]-> <* keyword "join"+> joinType =+> choice [choice+> [JCross <$ try (keyword_ "cross")+> ,JInner <$ try (keyword_ "inner")+> ,choice [JLeft <$ try (keyword_ "left")+> ,JRight <$ try (keyword_ "right")+> ,JFull <$ try (keyword_ "full")]+> <* optional (try $ keyword_ "outer")]+> <* keyword "join"+> ,JInner <$ keyword_ "join"] > joinCondition nat = > choice [guard nat >> return JoinNatural > ,try (keyword_ "on") >> > JoinOn <$> scalarExpr > ,try (keyword_ "using") >>-> JoinUsing <$> parens (commaSep1 identifierString)+> JoinUsing <$> parens (commaSep1 name) > ] +> alias :: P Alias+> alias = Alias <$> try tableAlias <*> try columnAliases+> where+> tableAlias = optional (try $ keyword_ "as") *> name+> columnAliases = optionMaybe $ try $ parens $ commaSep1 name+ == simple other parts Parsers for where, group by, having, order by and limit, which are@@ -533,35 +604,68 @@ > swhere :: P ScalarExpr > swhere = keywordScalarExpr "where" -> sgroupBy :: P [ScalarExpr]+> sgroupBy :: P [GroupingExpr] > sgroupBy = try (keyword_ "group") > *> keyword_ "by"-> *> commaSep1 scalarExpr+> *> commaSep1 groupingExpression+> where+> groupingExpression =+> choice+> [try (keyword_ "cube") >>+> Cube <$> parens (commaSep groupingExpression)+> ,try (keyword_ "rollup") >>+> Rollup <$> parens (commaSep groupingExpression)+> ,GroupingParens <$> parens (commaSep groupingExpression)+> ,try (keyword_ "grouping") >> keyword_ "sets" >>+> GroupingSets <$> parens (commaSep groupingExpression)+> ,SimpleGroup <$> scalarExpr+> ] > having :: P ScalarExpr > having = keywordScalarExpr "having" -> orderBy :: P [(ScalarExpr,Direction)]+> orderBy :: P [OrderField] > orderBy = try (keyword_ "order") *> keyword_ "by" *> commaSep1 ob > where-> ob = (,) <$> scalarExpr-> <*> option Asc (choice [Asc <$ keyword_ "asc"-> ,Desc <$ keyword_ "desc"])+> ob = OrderField+> <$> scalarExpr+> <*> option Asc (choice [Asc <$ keyword_ "asc"+> ,Desc <$ keyword_ "desc"])+> <*> option NullsOrderDefault+> (try (keyword_ "nulls" >>+> choice [NullsFirst <$ keyword "first"+> ,NullsLast <$ keyword "last"])) -> limit :: P ScalarExpr-> limit = keywordScalarExpr "limit"+allows offset and fetch in either order++ postgresql offset without row(s) and limit instead of fetch also +> offsetFetch :: P (Maybe ScalarExpr, Maybe ScalarExpr)+> offsetFetch = permute ((,) <$?> (Nothing, Just <$> offset)+> <|?> (Nothing, Just <$> fetch))+ > offset :: P ScalarExpr-> offset = keywordScalarExpr "offset"+> offset = try (keyword_ "offset") *> scalarExpr+> <* option () (try $ choice [try (keyword_ "rows"),keyword_ "row"]) +> fetch :: P ScalarExpr+> fetch = choice [ansiFetch, limit]+> where+> ansiFetch = try (keyword_ "fetch") >>+> choice [keyword_ "first",keyword_ "next"]+> *> scalarExpr+> <* choice [keyword_ "rows",keyword_ "row"]+> <* keyword_ "only"+> limit = try (keyword_ "limit") *> scalarExpr+ == common table expressions > with :: P QueryExpr > with = try (keyword_ "with") >>-> With <$> commaSep1 withQuery <*> queryExpr+> With <$> option False (try (True <$ keyword_ "recursive"))+> <*> commaSep1 withQuery <*> queryExpr > where > withQuery =-> (,) <$> (identifierString <* optional (try $ keyword_ "as"))+> (,) <$> (alias <* optional (try $ keyword_ "as")) > <*> parens queryExpr == query expression@@ -572,10 +676,11 @@ > queryExpr :: P QueryExpr > queryExpr = > choice [with-> ,select >>= optionSuffix queryExprSuffix]+> ,choice [values,table, select]+> >>= optionSuffix queryExprSuffix] > where > select = try (keyword_ "select") >>-> Select+> mkSelect > <$> (fromMaybe All <$> duplicates) > <*> selectList > <*> option [] from@@ -583,8 +688,12 @@ > <*> option [] sgroupBy > <*> optionMaybe having > <*> option [] orderBy-> <*> optionMaybe limit-> <*> optionMaybe offset+> <*> offsetFetch+> mkSelect d sl f w g h od (ofs,fe) =+> Select d sl f w g h od ofs fe+> values = try (keyword_ "values")+> >> Values <$> commaSep (parens (commaSep scalarExpr))+> table = try (keyword_ "table") >> Table <$> name > queryExprSuffix :: QueryExpr -> P QueryExpr > queryExprSuffix qe =@@ -633,10 +742,14 @@ > symbol_ :: String -> P () > symbol_ s = symbol s *> return () +TODO: now that keyword has try in it, a lot of the trys above can be+removed+ > keyword :: String -> P String-> keyword s = (map toLower <$> string s)-> <* notFollowedBy (char '_' <|> alphaNum)-> <* whiteSpace+> keyword s = try $ do+> i <- identifierRaw+> guard (map toLower i == map toLower s)+> return i > keyword_ :: String -> P () > keyword_ s = keyword s *> return ()@@ -646,22 +759,28 @@ support quoting other other sorts of identifiers yet. There is a blacklist of keywords which aren't supported as identifiers. -> identifierString :: P String-> identifierString = do-> s <- (:) <$> letterOrUnderscore-> <*> many letterDigitOrUnderscore <* whiteSpace-> guard (s `notElem` blacklist)-> return s+the identifier raw doesn't check the blacklist since it is used by the+keyword parser also++> identifierRaw :: P String+> identifierRaw = (:) <$> letterOrUnderscore+> <*> many letterDigitOrUnderscore <* whiteSpace > where > letterOrUnderscore = char '_' <|> letter > letterDigitOrUnderscore = char '_' <|> alphaNum +> identifierString :: P String+> identifierString = do+> s <- identifierRaw+> guard (map toLower s `notElem` blacklist)+> return s+ > blacklist :: [String] > blacklist = > ["select", "as", "from", "where", "having", "group", "order"-> ,"limit", "offset"+> ,"limit", "offset", "fetch" > ,"inner", "left", "right", "full", "natural", "join"-> ,"cross", "on", "using"+> ,"cross", "on", "using", "lateral" > ,"when", "then", "case", "end", "in" > ,"except", "intersect", "union"] @@ -672,11 +791,21 @@ could be tuned differently for each place the identifierString/ identifier parsers are used to only blacklist the bare minimum. +> quotedIdentifier :: P String+> quotedIdentifier = char '"' *> manyTill anyChar (symbol_ "\"")++ String literals: limited at the moment, no escaping \' or other variations. > stringLiteral :: P String-> stringLiteral = char '\'' *> manyTill anyChar (symbol_ "'")+> stringLiteral = (char '\'' *> manyTill anyChar (char '\'')+> >>= optionSuffix moreString) <* whiteSpace+> where+> moreString s0 = try $ do+> void $ char '\''+> s <- manyTill anyChar (char '\'')+> optionSuffix moreString (s0 ++ "'" ++ s) number literals
Language/SQL/SimpleSQL/Pretty.lhs view
@@ -28,21 +28,23 @@ = scalar expressions > scalarExpr :: ScalarExpr -> Doc-> scalarExpr (StringLit s) = quotes $ text s+> scalarExpr (StringLit s) = quotes $ text $ doubleUpQuotes s+> where doubleUpQuotes [] = []+> doubleUpQuotes ('\'':cs) = '\'':'\'':doubleUpQuotes cs+> doubleUpQuotes (c:cs) = c:doubleUpQuotes cs+ > scalarExpr (NumLit s) = text s > scalarExpr (IntervalLit v u p) = > text "interval" <+> quotes (text v) > <+> text u > <+> maybe empty (parens . text . show ) p-> scalarExpr (Iden i) = text i-> scalarExpr (Iden2 q i) = text q <> text "." <> text i+> scalarExpr (Iden i) = name i > scalarExpr Star = text "*"-> scalarExpr (Star2 q) = text q <> text "." <> text "*" -> scalarExpr (App f es) = text f <> parens (commaSep (map scalarExpr es))+> scalarExpr (App f es) = name f <> parens (commaSep (map scalarExpr es)) > scalarExpr (AggregateApp f d es od) =-> text f+> name f > <> parens ((case d of > Just Distinct -> text "distinct" > Just All -> text "all"@@ -50,50 +52,70 @@ > <+> commaSep (map scalarExpr es) > <+> orderBy od) -> scalarExpr (WindowApp f es pb od) =-> text f <> parens (commaSep $ map scalarExpr es)+> scalarExpr (WindowApp f es pb od fr) =+> name f <> parens (commaSep $ map scalarExpr es) > <+> text "over" > <+> parens ((case pb of > [] -> empty > _ -> text "partition by" > <+> nest 13 (commaSep $ map scalarExpr pb))-> <+> orderBy od)+> <+> orderBy od+> <+> maybe empty frd fr)+> where+> frd (FrameFrom rs fp) = rsd rs <+> fpd fp+> frd (FrameBetween rs fps fpe) =+> rsd rs <+> text "between" <+> fpd fps+> <+> text "and" <+> fpd fpe+> rsd rs = case rs of+> FrameRows -> text "rows"+> FrameRange -> text "range"+> fpd UnboundedPreceding = text "unbounded preceding"+> fpd UnboundedFollowing = text "unbounded following"+> fpd Current = text "current row"+> fpd (Preceding e) = scalarExpr e <+> text "preceding"+> fpd (Following e) = scalarExpr e <+> text "following" -> scalarExpr (SpecialOp nm [a,b,c]) | nm `elem` ["between", "not between"] =+> scalarExpr (SpecialOp nm [a,b,c]) | nm `elem` [Name "between"+> ,Name "not between"] = > sep [scalarExpr a-> ,text nm <+> scalarExpr b-> ,nest (length nm + 1)-> $ text "and" <+> scalarExpr c]+> ,name nm <+> scalarExpr b+> ,nest (length (unname nm) + 1) $ text "and" <+> scalarExpr c] -> scalarExpr (SpecialOp "extract" [a,n]) =+> scalarExpr (SpecialOp (Name "extract") [a,n]) = > text "extract" <> parens (scalarExpr a > <+> text "from" > <+> scalarExpr n) -> scalarExpr (SpecialOp "substring" [a,s,e]) =+> scalarExpr (SpecialOp (Name "substring") [a,s,e]) = > text "substring" <> parens (scalarExpr a > <+> text "from" > <+> scalarExpr s > <+> text "for" > <+> scalarExpr e) +> scalarExpr (SpecialOp (Name "rowctor") as) =+> parens $ commaSep $ map scalarExpr as+ > scalarExpr (SpecialOp nm es) =-> text nm <+> parens (commaSep $ map scalarExpr es)+> name nm <+> parens (commaSep $ map scalarExpr es) -> scalarExpr (PrefixOp f e) = text f <+> scalarExpr e-> scalarExpr (PostfixOp f e) = scalarExpr e <+> text f-> scalarExpr e@(BinOp _ op _) | op `elem` ["and", "or"] =+> scalarExpr (PrefixOp f e) = name f <+> scalarExpr e+> scalarExpr (PostfixOp f e) = scalarExpr e <+> name f+> scalarExpr e@(BinOp _ op _) | op `elem` [Name "and", Name "or"] = > -- special case for and, or, get all the ands so we can vcat them > -- nicely > case ands e of > (e':es) -> vcat (scalarExpr e'-> : map ((text op <+>) . scalarExpr) es)+> : map ((name op <+>) . scalarExpr) es) > [] -> empty -- shouldn't be possible > where > ands (BinOp a op' b) | op == op' = ands a ++ ands b > ands x = [x]+> -- special case for . we don't use whitespace+> scalarExpr (BinOp e0 (Name ".") e1) =+> scalarExpr e0 <> text "." <> scalarExpr e1 > scalarExpr (BinOp e0 f e1) =-> scalarExpr e0 <+> text f <+> scalarExpr e1+> scalarExpr e0 <+> name f <+> scalarExpr e1 > scalarExpr (Case t ws els) = > sep $ [text "case" <+> maybe empty scalarExpr t]@@ -102,17 +124,17 @@ > ++ [text "end"] > where > w (t0,t1) =-> text "when" <+> nest 5 (scalarExpr t0)+> text "when" <+> nest 5 (commaSep $ map scalarExpr t0) > <+> text "then" <+> nest 5 (scalarExpr t1) > e el = text "else" <+> nest 5 (scalarExpr el) > scalarExpr (Parens e) = parens $ scalarExpr e-> scalarExpr (Cast e (TypeName tn)) =+> scalarExpr (Cast e tn) = > text "cast" <> parens (sep [scalarExpr e > ,text "as"-> ,text tn])+> ,typeName tn]) -> scalarExpr (CastOp (TypeName tn) s) =-> text tn <+> quotes (text s)+> scalarExpr (TypedLit tn s) =+> typeName tn <+> quotes (text s) > scalarExpr (SubQueryExpr ty qe) = > (case ty of@@ -132,10 +154,25 @@ > InList es -> commaSep $ map scalarExpr es > InQueryExpr qe -> queryExpr qe) +> unname :: Name -> String+> unname (QName n) = "\"" ++ n ++ "\""+> unname (Name n) = n++> name :: Name -> Doc+> name (QName n) = doubleQuotes $ text n+> name (Name n) = text n++> typeName :: TypeName -> Doc+> typeName (TypeName t) = text t+> typeName (PrecTypeName t a) = text t <+> parens (text $ show a)+> typeName (PrecScaleTypeName t a b) =+> text t <+> parens (text (show a) <+> comma <+> text (show b))++ = query expressions > queryExpr :: QueryExpr -> Doc-> queryExpr (Select d sl fr wh gb hv od lm off) =+> queryExpr (Select d sl fr wh gb hv od off fe) = > sep [text "select" > ,case d of > All -> empty@@ -146,8 +183,9 @@ > ,grpBy gb > ,maybeScalarExpr "having" hv > ,orderBy od-> ,maybeScalarExpr "limit" lm-> ,maybeScalarExpr "offset" off+> ,maybe empty (\e -> text "offset" <+> scalarExpr e <+> text "rows") off+> ,maybe empty (\e -> text "fetch next" <+> scalarExpr e+> <+> text "rows only") fe > ] > queryExpr (CombineQueryExpr q1 ct d c q2) = > sep [queryExpr q1@@ -162,18 +200,28 @@ > Corresponding -> text "corresponding" > Respectively -> empty > ,queryExpr q2]-> queryExpr (With withs qe) =-> text "with"+> queryExpr (With rc withs qe) =+> text "with" <+> (if rc then text "recursive" else empty) > <+> vcat [nest 5 > (vcat $ punctuate comma $ flip map withs $ \(n,q) ->-> text n <+> text "as" <+> parens (queryExpr q))+> alias n <+> text "as" <+> parens (queryExpr q)) > ,queryExpr qe]+> queryExpr (Values vs) =+> text "values"+> <+> nest 7 (commaSep (map (parens . commaSep . map scalarExpr) vs))+> queryExpr (Table t) = text "table" <+> name t -> selectList :: [(Maybe String, ScalarExpr)] -> Doc++> alias :: Alias -> Doc+> alias (Alias nm cols) =+> text "as" <+> name nm+> <+> maybe empty (parens . commaSep . map name) cols++> selectList :: [(Maybe Name, ScalarExpr)] -> Doc > selectList is = commaSep $ map si is > where-> si (al,e) = scalarExpr e <+> maybe empty alias al-> alias al = text "as" <+> text al+> si (al,e) = scalarExpr e <+> maybe empty als al+> als al = text "as" <+> name al > from :: [TableRef] -> Doc > from [] = empty@@ -181,11 +229,11 @@ > sep [text "from" > ,nest 5 $ vcat $ punctuate comma $ map tr ts] > where-> tr (TRSimple t) = text t-> tr (TRAlias t a cs) =-> sep [tr t-> ,text "as" <+> text a-> <+> maybe empty (parens . commaSep . map text) cs]+> tr (TRSimple t) = name t+> tr (TRLateral t) = text "lateral" <+> tr t+> tr (TRFunction f as) =+> name f <> parens (commaSep $ map scalarExpr as)+> tr (TRAlias t a) = sep [tr t, alias a] > tr (TRParens t) = parens $ tr t > tr (TRQueryExpr q) = parens $ queryExpr q > tr (TRJoin t0 jt t1 jc) =@@ -205,7 +253,7 @@ > ,text "join"] > joinCond (Just (JoinOn e)) = text "on" <+> scalarExpr e > joinCond (Just (JoinUsing es)) =-> text "using" <+> parens (commaSep $ map text es)+> text "using" <+> parens (commaSep $ map name es) > joinCond Nothing = empty > joinCond (Just JoinNatural) = empty @@ -214,18 +262,29 @@ > (\e -> sep [text k > ,nest (length k + 1) $ scalarExpr e]) -> grpBy :: [ScalarExpr] -> Doc+> grpBy :: [GroupingExpr] -> Doc > grpBy [] = empty > grpBy gs = sep [text "group by"-> ,nest 9 $ commaSep $ map scalarExpr gs]+> ,nest 9 $ commaSep $ map ge gs]+> where+> ge (SimpleGroup e) = scalarExpr e+> ge (GroupingParens g) = parens (commaSep $ map ge g)+> ge (Cube es) = text "cube" <> parens (commaSep $ map ge es)+> ge (Rollup es) = text "rollup" <> parens (commaSep $ map ge es)+> ge (GroupingSets es) = text "grouping sets" <> parens (commaSep $ map ge es) -> orderBy :: [(ScalarExpr,Direction)] -> Doc+> orderBy :: [OrderField] -> Doc > orderBy [] = empty > orderBy os = sep [text "order by" > ,nest 9 $ commaSep $ map f os] > where-> f (e,Asc) = scalarExpr e-> f (e,Desc) = scalarExpr e <+> text "desc"+> f (OrderField e d n) =+> scalarExpr e+> <+> (if d == Asc then empty else text "desc")+> <+> (case n of+> NullsOrderDefault -> empty+> NullsFirst -> text "nulls" <+> text "first"+> NullsLast -> text "nulls" <+> text "last") = utils
Language/SQL/SimpleSQL/Syntax.lhs view
@@ -1,25 +1,33 @@ -> -- | The AST for SQL queries+> -- | The AST for SQL queries. > module Language.SQL.SimpleSQL.Syntax > (-- * Scalar expressions > ScalarExpr(..)+> ,Name(..) > ,TypeName(..) > ,Duplicates(..)+> ,OrderField(..) > ,Direction(..)+> ,NullsOrder(..) > ,InThing(..) > ,SubQueryExprType(..)+> ,Frame(..)+> ,FrameRows(..)+> ,FramePos(..) > -- * Query expressions > ,QueryExpr(..) > ,makeSelect > ,CombineOp(..) > ,Corresponding(..)+> ,Alias(..)+> ,GroupingExpr(..) > -- ** From > ,TableRef(..) > ,JoinType(..) > ,JoinCondition(..) > ) where -> -- | Represents a scalar expression+> -- | Represents a scalar expression. > data ScalarExpr > = -- | a numeric literal optional decimal point, e+- > -- integral exponent, e.g@@ -37,58 +45,66 @@ > -- * 12.34e-6 > NumLit String > -- | string literal, currently only basic strings between-> -- single quotes without escapes (no single quotes in strings-> -- then)+> -- single quotes with a single quote escaped using '' > | StringLit String > -- | text of interval literal, units of interval precision, > -- e.g. interval 3 days (3)-> | IntervalLit String String (Maybe Int)+> | IntervalLit+> {ilLiteral :: String -- ^ literal text+> ,ilUnits :: String -- ^ units+> ,ilPrecision :: Maybe Int -- ^ precision+> } > -- | identifier without dots-> | Iden String-> -- | identifier with one dot-> | Iden2 String String-> -- | star+> | Iden Name+> -- | star, as in select *, t.*, count(*) > | Star-> -- | star with qualifier, e.g t.*-> | Star2 String > -- | function application (anything that looks like c style > -- function application syntactically)-> | App String [ScalarExpr]+> | App Name [ScalarExpr] > -- | aggregate application, which adds distinct or all, and > -- order by, to regular function application-> | AggregateApp String (Maybe Duplicates)-> [ScalarExpr]-> [(ScalarExpr,Direction)]+> | AggregateApp+> {aggName :: Name -- ^ aggregate function name+> ,aggDistinct :: Maybe Duplicates -- ^ distinct+> ,aggArgs :: [ScalarExpr]-- ^ args+> ,aggOrderBy :: [OrderField] -- ^ order by+> } > -- | window application, which adds over (partition by a order > -- by b) to regular function application. Explicit frames are > -- not currently supported-> | WindowApp String [ScalarExpr] [ScalarExpr] [(ScalarExpr,Direction)]+> | WindowApp+> {wnName :: Name -- ^ window function name+> ,wnArgs :: [ScalarExpr] -- ^ args+> ,wnPartition :: [ScalarExpr] -- ^ partition by+> ,wnOrderBy :: [OrderField] -- ^ order by+> ,wnFrame :: Maybe Frame -- ^ frame clause+> } > -- | Infix binary operators. This is used for symbol operators > -- (a + b), keyword operators (a and b) and multiple keyword > -- operators (a is similar to b)-> | BinOp ScalarExpr String ScalarExpr+> | BinOp ScalarExpr Name ScalarExpr > -- | Prefix unary operators. This is used for symbol > -- operators, keyword operators and multiple keyword operators-> | PrefixOp String ScalarExpr+> | PrefixOp Name ScalarExpr > -- | Postfix unary operators. This is used for symbol > -- operators, keyword operators and multiple keyword operators-> | PostfixOp String ScalarExpr+> | PostfixOp Name ScalarExpr > -- | Used for ternary, mixfix and other non orthodox > -- operators, including the function looking calls which use > -- keywords instead of commas to separate the arguments, > -- e.g. substring(t from 1 to 5)-> | SpecialOp String [ScalarExpr]-> -- | case expression. both flavours supported. Multiple-> -- condition when branches not currently supported (case when-> -- a=4,b=5 then x end)-> | Case (Maybe ScalarExpr) -- test value-> [(ScalarExpr,ScalarExpr)] -- when branches-> (Maybe ScalarExpr) -- else value+> | SpecialOp Name [ScalarExpr]+> -- | case expression. both flavours supported+> | Case+> {caseTest :: Maybe ScalarExpr -- ^ test value+> ,caseWhens :: [([ScalarExpr],ScalarExpr)] -- ^ when branches+> ,caseElse :: Maybe ScalarExpr -- ^ else value+> } > | Parens ScalarExpr > -- | cast(a as typename) > | Cast ScalarExpr TypeName > -- | prefix 'typed literal', e.g. int '42'-> | CastOp TypeName String+> | TypedLit TypeName String > -- | exists, all, any, some subqueries > | SubQueryExpr SubQueryExprType QueryExpr > -- | in list literal and in subquery, if the bool is false it@@ -96,17 +112,25 @@ > | In Bool ScalarExpr InThing > deriving (Eq,Show,Read) +> -- | Represents an identifier name, which can be quoted or unquoted.+> data Name = Name String+> | QName String+> deriving (Eq,Show,Read)+ > -- | Represents a type name, used in casts.-> data TypeName = TypeName String deriving (Eq,Show,Read)+> data TypeName = TypeName String+> | PrecTypeName String Int+> | PrecScaleTypeName String Int Int+> deriving (Eq,Show,Read) > -- | Used for 'expr in (scalar expression list)', and 'expr in-> -- (subquery)' syntax+> -- (subquery)' syntax. > data InThing = InList [ScalarExpr] > | InQueryExpr QueryExpr > deriving (Eq,Show,Read) -> -- | A subquery in a scalar expression+> -- | A subquery in a scalar expression. > data SubQueryExprType > = -- | exists (query expr) > SqExists@@ -120,6 +144,35 @@ > | SqAny > deriving (Eq,Show,Read) +> -- | Represents one field in an order by list.+> data OrderField = OrderField ScalarExpr Direction NullsOrder+> deriving (Eq,Show,Read)++> -- | Represents 'nulls first' or 'nulls last' in an order by clause.+> data NullsOrder = NullsOrderDefault+> | NullsFirst+> | NullsLast+> deriving (Eq,Show,Read)++> -- | Represents the frame clause of a window+> -- this can be [range | rows] frame_start+> -- or [range | rows] between frame_start and frame_end+> data Frame = FrameFrom FrameRows FramePos+> | FrameBetween FrameRows FramePos FramePos+> deriving (Eq,Show,Read)++> -- | Represents whether a window frame clause is over rows or ranges.+> data FrameRows = FrameRows | FrameRange+> deriving (Eq,Show,Read)++> -- | represents the start or end of a frame+> data FramePos = UnboundedPreceding+> | Preceding ScalarExpr+> | Current+> | Following ScalarExpr+> | UnboundedFollowing+> deriving (Eq,Show,Read)+ > -- | Represents a query expression, which can be: > -- > -- * a regular select;@@ -128,51 +181,44 @@ > -- > -- * a common table expression (with); > ---> -- * a values expression (not yet supported);+> -- * a values expression; > -- > -- * or the table syntax - 'table t', shorthand for 'select * from-> -- t' (not yet supported).+> -- t'. > data QueryExpr > = Select > {qeDuplicates :: Duplicates-> ,qeSelectList :: [(Maybe String,ScalarExpr)]+> ,qeSelectList :: [(Maybe Name,ScalarExpr)] > -- ^ the column aliases and the expressions > ,qeFrom :: [TableRef] > ,qeWhere :: Maybe ScalarExpr-> ,qeGroupBy :: [ScalarExpr]+> ,qeGroupBy :: [GroupingExpr] > ,qeHaving :: Maybe ScalarExpr-> ,qeOrderBy :: [(ScalarExpr,Direction)]-> ,qeLimit :: Maybe ScalarExpr+> ,qeOrderBy :: [OrderField] > ,qeOffset :: Maybe ScalarExpr+> ,qeFetch :: Maybe ScalarExpr > } > | CombineQueryExpr-> {qe1 :: QueryExpr+> {qe0 :: QueryExpr > ,qeCombOp :: CombineOp > ,qeDuplicates :: Duplicates > ,qeCorresponding :: Corresponding-> ,qe2 :: QueryExpr+> ,qe1 :: QueryExpr > }-> | With [(String,QueryExpr)] QueryExpr+> | With+> {qeWithRecursive :: Bool+> ,qeViews :: [(Alias,QueryExpr)]+> ,qeQueryExpression :: QueryExpr}+> | Values [[ScalarExpr]]+> | Table Name > deriving (Eq,Show,Read) TODO: add queryexpr parens to deal with e.g. (select 1 union select 2) union select 3 I'm not sure if this is valid syntax or not. -> -- | represents the Distinct or All keywords, which can be used-> -- before a select list, in an aggregate/window function-> -- application, or in a query expression set operator-> data Duplicates = Distinct | All deriving (Eq,Show,Read)--> -- | The direction for a column in order by.-> data Direction = Asc | Desc deriving (Eq,Show,Read)-> -- | Query expression set operators-> data CombineOp = Union | Except | Intersect deriving (Eq,Show,Read)-> -- | Corresponding, an option for the set operators-> data Corresponding = Corresponding | Respectively deriving (Eq,Show,Read)--> -- | helper/'default' value for query exprs to make creating query-> -- expr values a little easier+> -- | Helper/'default' value for query exprs to make creating query+> -- expr values a little easier. > makeSelect :: QueryExpr > makeSelect = Select {qeDuplicates = All > ,qeSelectList = []@@ -181,30 +227,60 @@ > ,qeGroupBy = [] > ,qeHaving = Nothing > ,qeOrderBy = []-> ,qeLimit = Nothing-> ,qeOffset = Nothing}+> ,qeOffset = Nothing+> ,qeFetch = Nothing} ++> -- | Represents the Distinct or All keywords, which can be used+> -- before a select list, in an aggregate/window function+> -- application, or in a query expression set operator.+> data Duplicates = Distinct | All deriving (Eq,Show,Read)++> -- | The direction for a column in order by.+> data Direction = Asc | Desc deriving (Eq,Show,Read)+> -- | Query expression set operators.+> data CombineOp = Union | Except | Intersect deriving (Eq,Show,Read)+> -- | Corresponding, an option for the set operators.+> data Corresponding = Corresponding | Respectively deriving (Eq,Show,Read)++> -- | Represents an item in a group by clause.+> data GroupingExpr+> = GroupingParens [GroupingExpr]+> | Cube [GroupingExpr]+> | Rollup [GroupingExpr]+> | GroupingSets [GroupingExpr]+> | SimpleGroup ScalarExpr+> deriving (Eq,Show,Read)+ > -- | Represents a entry in the csv of tables in the from clause. > data TableRef = -- | from t-> TRSimple String+> TRSimple Name > -- | from a join b > | TRJoin TableRef JoinType TableRef (Maybe JoinCondition) > -- | from (a) > | TRParens TableRef > -- | from a as b(c,d)-> | TRAlias TableRef String (Maybe [String])+> | TRAlias TableRef Alias > -- | from (query expr) > | TRQueryExpr QueryExpr+> -- | from function(args)+> | TRFunction Name [ScalarExpr]+> -- | from lateral t+> | TRLateral TableRef > deriving (Eq,Show,Read) -TODO: add function table ref+> -- | Represents an alias for a table valued expression, used in with+> -- queries and in from alias, e.g. select a from t u, select a from t u(b),+> -- with a(c) as select 1, select * from a.+> data Alias = Alias Name (Maybe [Name])+> deriving (Eq,Show,Read) -> -- | The type of a join+> -- | The type of a join. > data JoinType = JInner | JLeft | JRight | JFull | JCross > deriving (Eq,Show,Read) > -- | The join condition. > data JoinCondition = JoinOn ScalarExpr -- ^ on expr-> | JoinUsing [String] -- ^ using (column list)+> | JoinUsing [Name] -- ^ using (column list) > | JoinNatural -- ^ natural join was used > deriving (Eq,Show,Read)
README view
@@ -1,5 +1,5 @@ A parser for SQL queries in Haskell. -Homepage: http://jakewheat.github.io/simple_sql_parser/+Homepage: http://jakewheat.github.io/simple-sql-parser/ Contact: jakewheatmail@gmail.com
+ changelog view
@@ -0,0 +1,29 @@+0.2.0.0+ last update on commit a53578c6c27d94a33d104d404547cc02b4ad36c3+ '' quotes in string literal+ parse simple interval literal e.g. "interval '1 week'"+ support . in identifiers as a dot operator+ support quoted identifiers+ partial support for explicit window frames+ support multiple test expressions in when branches in case expressions+ rename CastOp to TypedLit+ support typenames with precision and scale in casts+ support nulls first and nulls last in order by+ support grouping expressions: group by (), grouping sets, cube,+ rollup and parens+ support with recursive+ support values table literal+ support 'table t' syntax+ rename fields qe1 and qe1 in combinequeryexpr to qe0 and qe1+ add support for functions in from clause+ add support for lateral in from clause+ support column aliases in common table expressions+ refactor the tests and add lots more+ parse * in any scalar context instead of trying to restrict it+ support row ctor without 'row' e.g. (a,b) = (c,d)+ add % ^ | & ~ operators+ support ansi standard syntax for offset n rows and fetch first n+ rows only+ fix keyword parsing to be case insensitive+0.1.0.0+ initial version
simple-sql-parser.cabal view
@@ -1,9 +1,9 @@ name: simple-sql-parser-version: 0.1.0.0+version: 0.2.0 synopsis: A parser for SQL queries-description: A parser for SQL queries+description: A parser for SQL queries. Please see the homepage for more information <http://jakewheat.github.io/simple-sql-parser/>. -homepage: http://jakewheat.github.io/simple_sql_parser/+homepage: http://jakewheat.github.io/simple-sql-parser/ license: BSD3 license-file: LICENSE author: Jake Wheat@@ -11,13 +11,13 @@ copyright: Copyright Jake Wheat 2013 category: Database,Language build-type: Simple-extra-source-files: README,LICENSE+extra-source-files: README,LICENSE,changelog cabal-version: >=1.10-bug-reports: https://github.com/JakeWheat/simple_sql_parser/issues+bug-reports: https://github.com/JakeWheat/simple-sql-parser/issues source-repository head type: git- location: https://github.com/JakeWheat/simple_sql_parser.git+ location: https://github.com/JakeWheat/simple-sql-parser.git library exposed-modules: Language.SQL.SimpleSQL.Pretty,@@ -52,8 +52,18 @@ Language.SQL.SimpleSQL.Parser, Language.SQL.SimpleSQL.Syntax, Language.SQL.SimpleSQL.Fixity,++ Language.SQL.SimpleSQL.FullQueries,+ Language.SQL.SimpleSQL.GroupBy,+ Language.SQL.SimpleSQL.Postgres,+ Language.SQL.SimpleSQL.QueryExprComponents,+ Language.SQL.SimpleSQL.QueryExprs,+ Language.SQL.SimpleSQL.ScalarExprs,+ Language.SQL.SimpleSQL.TableRefs,+ Language.SQL.SimpleSQL.TestTypes, Language.SQL.SimpleSQL.Tests,- Tpch- other-extensions: TupleSections+ Language.SQL.SimpleSQL.Tpch++ other-extensions: TupleSections,OverloadedStrings default-language: Haskell2010 ghc-options: -Wall
+ tools/Language/SQL/SimpleSQL/FullQueries.lhs view
@@ -0,0 +1,39 @@++Some tests for parsing full queries.++> {-# LANGUAGE OverloadedStrings #-}+> module Language.SQL.SimpleSQL.FullQueries (fullQueriesTests) where++> import Language.SQL.SimpleSQL.TestTypes+> import Language.SQL.SimpleSQL.Syntax+++> fullQueriesTests :: TestItem+> fullQueriesTests = Group "queries" $ map (uncurry TestQueryExpr)+> [("select count(*) from t"+> ,makeSelect+> {qeSelectList = [(Nothing, App "count" [Star])]+> ,qeFrom = [TRSimple "t"]+> }+> )++> ,("select a, sum(c+d) as s\n\+> \ from t,u\n\+> \ where a > 5\n\+> \ group by a\n\+> \ having count(1) > 5\n\+> \ order by s"+> ,makeSelect+> {qeSelectList = [(Nothing, Iden "a")+> ,(Just "s"+> ,App "sum" [BinOp (Iden "c")+> "+" (Iden "d")])]+> ,qeFrom = [TRSimple "t", TRSimple "u"]+> ,qeWhere = Just $ BinOp (Iden "a") ">" (NumLit "5")+> ,qeGroupBy = [SimpleGroup $ Iden "a"]+> ,qeHaving = Just $ BinOp (App "count" [NumLit "1"])+> ">" (NumLit "5")+> ,qeOrderBy = [OrderField (Iden "s") Asc NullsOrderDefault]+> }+> )+> ]
+ tools/Language/SQL/SimpleSQL/GroupBy.lhs view
@@ -0,0 +1,233 @@++Here are the tests for the group by component of query exprs++> {-# LANGUAGE OverloadedStrings #-}+> module Language.SQL.SimpleSQL.GroupBy (groupByTests) where++> import Language.SQL.SimpleSQL.TestTypes+> import Language.SQL.SimpleSQL.Syntax+++> groupByTests :: TestItem+> groupByTests = Group "groupByTests"+> [simpleGroupBy+> ,newGroupBy+> ,randomGroupBy+> ]++> simpleGroupBy :: TestItem+> simpleGroupBy = Group "simpleGroupBy" $ map (uncurry TestQueryExpr)+> [("select a,sum(b) from t group by a"+> ,makeSelect {qeSelectList = [(Nothing, Iden "a")+> ,(Nothing, App "sum" [Iden "b"])]+> ,qeFrom = [TRSimple "t"]+> ,qeGroupBy = [SimpleGroup $ Iden "a"]+> })++> ,("select a,b,sum(c) from t group by a,b"+> ,makeSelect {qeSelectList = [(Nothing, Iden "a")+> ,(Nothing, Iden "b")+> ,(Nothing, App "sum" [Iden "c"])]+> ,qeFrom = [TRSimple "t"]+> ,qeGroupBy = [SimpleGroup $ Iden "a"+> ,SimpleGroup $ Iden "b"]+> })+> ]++test the new group by (), grouping sets, cube and rollup syntax (not+sure which sql version they were introduced, 1999 or 2003 I think).++> newGroupBy :: TestItem+> newGroupBy = Group "newGroupBy" $ map (uncurry TestQueryExpr)+> [("select * from t group by ()", ms [GroupingParens []])+> ,("select * from t group by grouping sets ((), (a))"+> ,ms [GroupingSets [GroupingParens []+> ,GroupingParens [SimpleGroup $ Iden "a"]]])+> ,("select * from t group by cube(a,b)"+> ,ms [Cube [SimpleGroup $ Iden "a", SimpleGroup $ Iden "b"]])+> ,("select * from t group by rollup(a,b)"+> ,ms [Rollup [SimpleGroup $ Iden "a", SimpleGroup $ Iden "b"]])+> ]+> where+> ms g = makeSelect {qeSelectList = [(Nothing,Star)]+> ,qeFrom = [TRSimple "t"]+> ,qeGroupBy = g}++> randomGroupBy :: TestItem+> randomGroupBy = Group "randomGroupBy" $ map ParseQueryExpr+> ["select * from t GROUP BY a"+> ,"select * from t GROUP BY GROUPING SETS((a))"+> ,"select * from t GROUP BY a,b,c"+> ,"select * from t GROUP BY GROUPING SETS((a,b,c))"+> ,"select * from t GROUP BY ROLLUP(a,b)"+> ,"select * from t GROUP BY GROUPING SETS((a,b),\n\+> \(a),\n\+> \() )"+> ,"select * from t GROUP BY ROLLUP(b,a)"+> ,"select * from t GROUP BY GROUPING SETS((b,a),\n\+> \(b),\n\+> \() )"+> ,"select * from t GROUP BY CUBE(a,b,c)"+> ,"select * from t GROUP BY GROUPING SETS((a,b,c),\n\+> \(a,b),\n\+> \(a,c),\n\+> \(b,c),\n\+> \(a),\n\+> \(b),\n\+> \(c),\n\+> \() )"+> ,"select * from t GROUP BY ROLLUP(Province, County, City)"+> ,"select * from t GROUP BY ROLLUP(Province, (County, City))"+> ,"select * from t GROUP BY ROLLUP(Province, (County, City))"+> ,"select * from t GROUP BY GROUPING SETS((Province, County, City),\n\+> \(Province),\n\+> \() )"+> ,"select * from t GROUP BY GROUPING SETS((Province, County, City),\n\+> \(Province, County),\n\+> \(Province),\n\+> \() )"+> ,"select * from t GROUP BY a, ROLLUP(b,c)"+> ,"select * from t GROUP BY GROUPING SETS((a,b,c),\n\+> \(a,b),\n\+> \(a) )"+> ,"select * from t GROUP BY a, b, ROLLUP(c,d)"+> ,"select * from t GROUP BY GROUPING SETS((a,b,c,d),\n\+> \(a,b,c),\n\+> \(a,b) )"+> ,"select * from t GROUP BY ROLLUP(a), ROLLUP(b,c)"+> ,"select * from t GROUP BY GROUPING SETS((a,b,c),\n\+> \(a,b),\n\+> \(a),\n\+> \(b,c),\n\+> \(b),\n\+> \() )"+> ,"select * from t GROUP BY ROLLUP(a), CUBE(b,c)"+> ,"select * from t GROUP BY GROUPING SETS((a,b,c),\n\+> \(a,b),\n\+> \(a,c),\n\+> \(a),\n\+> \(b,c),\n\+> \(b),\n\+> \(c),\n\+> \() )"+> ,"select * from t GROUP BY CUBE(a,b), ROLLUP(c,d)"+> ,"select * from t GROUP BY GROUPING SETS((a,b,c,d),\n\+> \(a,b,c),\n\+> \(a,b),\n\+> \(a,c,d),\n\+> \(a,c),\n\+> \(a),\n\+> \(b,c,d),\n\+> \(b,c),\n\+> \(b),\n\+> \(c,d),\n\+> \(c),\n\+> \() )"+> ,"select * from t GROUP BY a, ROLLUP(a,b)"+> ,"select * from t GROUP BY GROUPING SETS((a,b),\n\+> \(a) )"+> ,"select * from t GROUP BY Region,\n\+> \ROLLUP(Sales_Person, WEEK(Sales_Date)),\n\+> \CUBE(YEAR(Sales_Date), MONTH (Sales_Date))"+> ,"select * from t GROUP BY ROLLUP (Region, Sales_Person, WEEK(Sales_Date),\n\+> \YEAR(Sales_Date), MONTH(Sales_Date) )"++> ,"SELECT WEEK(SALES_DATE) AS WEEK,\n\+> \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\+> \SALES_PERSON, SUM(SALES) AS UNITS_SOLD\n\+> \FROM SALES\n\+> \WHERE WEEK(SALES_DATE) = 13\n\+> \GROUP BY WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON\n\+> \ORDER BY WEEK, DAY_WEEK, SALES_PERSON"++> ,"SELECT WEEK(SALES_DATE) AS WEEK,\n\+> \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\+> \SALES_PERSON, SUM(SALES) AS UNITS_SOLD\n\+> \FROM SALES\n\+> \WHERE WEEK(SALES_DATE) = 13\n\+> \GROUP BY GROUPING SETS ( (WEEK(SALES_DATE), SALES_PERSON),\n\+> \(DAYOFWEEK(SALES_DATE), SALES_PERSON))\n\+> \ORDER BY WEEK, DAY_WEEK, SALES_PERSON"++> ,"SELECT WEEK(SALES_DATE) AS WEEK,\n\+> \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\+> \SALES_PERSON, SUM(SALES) AS UNITS_SOLD\n\+> \FROM SALES\n\+> \WHERE WEEK(SALES_DATE) = 13\n\+> \GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )\n\+> \ORDER BY WEEK, DAY_WEEK, SALES_PERSON"++> ,"SELECT WEEK(SALES_DATE) AS WEEK,\n\+> \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\+> \SALES_PERSON, SUM(SALES) AS UNITS_SOLD\n\+> \FROM SALES\n\+> \WHERE WEEK(SALES_DATE) = 13\n\+> \GROUP BY CUBE ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )\n\+> \ORDER BY WEEK, DAY_WEEK, SALES_PERSON"++> ,"SELECT SALES_PERSON,\n\+> \MONTH(SALES_DATE) AS MONTH,\n\+> \SUM(SALES) AS UNITS_SOLD\n\+> \FROM SALES\n\+> \GROUP BY GROUPING SETS ( (SALES_PERSON, MONTH(SALES_DATE)),\n\+> \()\n\+> \)\n\+> \ORDER BY SALES_PERSON, MONTH"++> ,"SELECT WEEK(SALES_DATE) AS WEEK,\n\+> \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\+> \SUM(SALES) AS UNITS_SOLD\n\+> \FROM SALES\n\+> \GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) )\n\+> \ORDER BY WEEK, DAY_WEEK"++> ,"SELECT MONTH(SALES_DATE) AS MONTH,\n\+> \REGION,\n\+> \SUM(SALES) AS UNITS_SOLD\n\+> \FROM SALES\n\+> \GROUP BY ROLLUP ( MONTH(SALES_DATE), REGION )\n\+> \ORDER BY MONTH, REGION"++> ,"SELECT WEEK(SALES_DATE) AS WEEK,\n\+> \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\+> \MONTH(SALES_DATE) AS MONTH,\n\+> \REGION,\n\+> \SUM(SALES) AS UNITS_SOLD\n\+> \FROM SALES\n\+> \GROUP BY GROUPING SETS ( ROLLUP( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ),\n\+> \ROLLUP( MONTH(SALES_DATE), REGION ) )\n\+> \ORDER BY WEEK, DAY_WEEK, MONTH, REGION"++> ,"SELECT R1, R2,\n\+> \WEEK(SALES_DATE) AS WEEK,\n\+> \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\+> \MONTH(SALES_DATE) AS MONTH,\n\+> \REGION, SUM(SALES) AS UNITS_SOLD\n\+> \FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)\n\+> \GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE),\n\+> \DAYOFWEEK(SALES_DATE))),\n\+> \(R2,ROLLUP( MONTH(SALES_DATE), REGION ) ))\n\+> \ORDER BY WEEK, DAY_WEEK, MONTH, REGION"++> {-,"SELECT COALESCE(R1,R2) AS GROUP,\n\+> \WEEK(SALES_DATE) AS WEEK,\n\+> \DAYOFWEEK(SALES_DATE) AS DAY_WEEK,\n\+> \MONTH(SALES_DATE) AS MONTH,\n\+> \REGION, SUM(SALES) AS UNITS_SOLD\n\+> \FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)\n\+> \GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE),\n\+> \DAYOFWEEK(SALES_DATE))),\n\+> \(R2,ROLLUP( MONTH(SALES_DATE), REGION ) ))\n\+> \ORDER BY GROUP, WEEK, DAY_WEEK, MONTH, REGION"-}+> -- as group - needs more subtle keyword blacklisting++> ,"SELECT MONTH(SALES_DATE) AS MONTH,\n\+> \REGION,\n\+> \SUM(SALES) AS UNITS_SOLD,\n\+> \MAX(SALES) AS BEST_SALE,\n\+> \CAST(ROUND(AVG(DECIMAL(SALES)),2) AS DECIMAL(5,2)) AS AVG_UNITS_SOLD\n\+> \FROM SALES\n\+> \GROUP BY CUBE(MONTH(SALES_DATE),REGION)\n\+> \ORDER BY MONTH, REGION"++> ]
+ tools/Language/SQL/SimpleSQL/Postgres.lhs view
@@ -0,0 +1,269 @@++Here are some tests taken from the SQL in the postgres manual. Almost+all of the postgres specific syntax has been skipped, this can be+revisited when the dialect support is added.++> module Language.SQL.SimpleSQL.Postgres (postgresTests) where++> import Language.SQL.SimpleSQL.TestTypes+> --import Language.SQL.SimpleSQL.Syntax++> postgresTests :: TestItem+> postgresTests = Group "postgresTests" $ map ParseQueryExpr++lexical syntax section++TODO: get all the commented out tests working++> [-- "SELECT 'foo'\n\+> -- \'bar';" -- this should parse as select 'foobar'+> -- ,+> "SELECT name, (SELECT max(pop) FROM cities\n\+> \ WHERE cities.state = states.name)\n\+> \ FROM states;"+> ,"SELECT ROW(1,2.5,'this is a test');"++> ,"SELECT ROW(t.*, 42) FROM t;"+> ,"SELECT ROW(t.f1, t.f2, 42) FROM t;"+> ,"SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));"++> ,"SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same');"++> ,"SELECT ROW(table.*) IS NULL FROM table;"++> ,"SELECT true OR somefunc();"++> ,"SELECT somefunc() OR true;"++queries section++> ,"SELECT * FROM t1 CROSS JOIN t2;"+> ,"SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;"+> ,"SELECT * FROM t1 INNER JOIN t2 USING (num);"+> ,"SELECT * FROM t1 NATURAL INNER JOIN t2;"+> ,"SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;"+> ,"SELECT * FROM t1 LEFT JOIN t2 USING (num);"+> ,"SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;"+> ,"SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;"+> ,"SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';"+> ,"SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';"++> ,"SELECT * FROM some_very_long_table_name s\n\+> \JOIN another_fairly_long_name a ON s.id = a.num;"+> ,"SELECT * FROM people AS mother JOIN people AS child\n\+> \ ON mother.id = child.mother_id;"+> ,"SELECT * FROM my_table AS a CROSS JOIN my_table AS b;"+> ,"SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b;"+> ,"SELECT * FROM getfoo(1) AS t1;"+> ,"SELECT * FROM foo\n\+> \ WHERE foosubid IN (\n\+> \ SELECT foosubid\n\+> \ FROM getfoo(foo.fooid) z\n\+> \ WHERE z.fooid = foo.fooid\n\+> \ );"+> {-,"SELECT *\n\+> \ FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')\n\+> \ AS t1(proname name, prosrc text)\n\+> \ WHERE proname LIKE 'bytea%';"-} -- types in the alias??++> ,"SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;"+> ,"SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;"++> {-,"SELECT p1.id, p2.id, v1, v2\n\+> \FROM polygons p1, polygons p2,\n\+> \ LATERAL vertices(p1.poly) v1,\n\+> \ LATERAL vertices(p2.poly) v2\n\+> \WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;"-} -- <-> operator?++> {-,"SELECT p1.id, p2.id, v1, v2\n\+> \FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,\n\+> \ polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2\n\+> \WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;"-}++> ,"SELECT m.name\n\+> \FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true\n\+> \WHERE pname IS NULL;"+++> ,"SELECT * FROM fdt WHERE c1 > 5"++> ,"SELECT * FROM fdt WHERE c1 IN (1, 2, 3)"++> ,"SELECT * FROM fdt WHERE c1 IN (SELECT c1 FROM t2)"++> ,"SELECT * FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)"++> ,"SELECT * FROM fdt WHERE c1 BETWEEN \n\+> \ (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100"++> ,"SELECT * FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)"++> ,"SELECT * FROM test1;"++> ,"SELECT x FROM test1 GROUP BY x;"+> ,"SELECT x, sum(y) FROM test1 GROUP BY x;"+> ,"SELECT product_id, p.name, (sum(s.units) * p.price) AS sales\n\+> \ FROM products p LEFT JOIN sales s USING (product_id)\n\+> \ GROUP BY product_id, p.name, p.price;"++> ,"SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;"+> ,"SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';"+> ,"SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit\n\+> \ FROM products p LEFT JOIN sales s USING (product_id)\n\+> \ WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'\n\+> \ GROUP BY product_id, p.name, p.price, p.cost\n\+> \ HAVING sum(p.price * s.units) > 5000;"++> ,"SELECT a, b, c FROM t"++> ,"SELECT tbl1.a, tbl2.a, tbl1.b FROM t"++> ,"SELECT tbl1.*, tbl2.a FROM t"++> ,"SELECT a AS value, b + c AS sum FROM t"++> ,"SELECT a \"value\", b + c AS sum FROM t"++> ,"SELECT DISTINCT select_list t"++> ,"VALUES (1, 'one'), (2, 'two'), (3, 'three');"++> ,"SELECT 1 AS column1, 'one' AS column2\n\+> \UNION ALL\n\+> \SELECT 2, 'two'\n\+> \UNION ALL\n\+> \SELECT 3, 'three';"++> ,"SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);"++> ,"WITH regional_sales AS (\n\+> \ SELECT region, SUM(amount) AS total_sales\n\+> \ FROM orders\n\+> \ GROUP BY region\n\+> \ ), top_regions AS (\n\+> \ SELECT region\n\+> \ FROM regional_sales\n\+> \ WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)\n\+> \ )\n\+> \SELECT region,\n\+> \ product,\n\+> \ SUM(quantity) AS product_units,\n\+> \ SUM(amount) AS product_sales\n\+> \FROM orders\n\+> \WHERE region IN (SELECT region FROM top_regions)\n\+> \GROUP BY region, product;"++> ,"WITH RECURSIVE t(n) AS (\n\+> \ VALUES (1)\n\+> \ UNION ALL\n\+> \ SELECT n+1 FROM t WHERE n < 100\n\+> \)\n\+> \SELECT sum(n) FROM t"++> ,"WITH RECURSIVE included_parts(sub_part, part, quantity) AS (\n\+> \ SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'\n\+> \ UNION ALL\n\+> \ SELECT p.sub_part, p.part, p.quantity\n\+> \ FROM included_parts pr, parts p\n\+> \ WHERE p.part = pr.sub_part\n\+> \ )\n\+> \SELECT sub_part, SUM(quantity) as total_quantity\n\+> \FROM included_parts\n\+> \GROUP BY sub_part"++> ,"WITH RECURSIVE search_graph(id, link, data, depth) AS (\n\+> \ SELECT g.id, g.link, g.data, 1\n\+> \ FROM graph g\n\+> \ UNION ALL\n\+> \ SELECT g.id, g.link, g.data, sg.depth + 1\n\+> \ FROM graph g, search_graph sg\n\+> \ WHERE g.id = sg.link\n\+> \)\n\+> \SELECT * FROM search_graph;"++> {-,"WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (\n\+> \ SELECT g.id, g.link, g.data, 1,\n\+> \ ARRAY[g.id],\n\+> \ false\n\+> \ FROM graph g\n\+> \ UNION ALL\n\+> \ SELECT g.id, g.link, g.data, sg.depth + 1,\n\+> \ path || g.id,\n\+> \ g.id = ANY(path)\n\+> \ FROM graph g, search_graph sg\n\+> \ WHERE g.id = sg.link AND NOT cycle\n\+> \)\n\+> \SELECT * FROM search_graph;"-} -- ARRAY++> {-,"WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (\n\+> \ SELECT g.id, g.link, g.data, 1,\n\+> \ ARRAY[ROW(g.f1, g.f2)],\n\+> \ false\n\+> \ FROM graph g\n\+> \ UNION ALL\n\+> \ SELECT g.id, g.link, g.data, sg.depth + 1,\n\+> \ path || ROW(g.f1, g.f2),\n\+> \ ROW(g.f1, g.f2) = ANY(path)\n\+> \ FROM graph g, search_graph sg\n\+> \ WHERE g.id = sg.link AND NOT cycle\n\+> \)\n\+> \SELECT * FROM search_graph;"-} -- ARRAY++> ,"WITH RECURSIVE t(n) AS (\n\+> \ SELECT 1\n\+> \ UNION ALL\n\+> \ SELECT n+1 FROM t\n\+> \)\n\+> \SELECT n FROM t LIMIT 100;"++select page reference++> ,"SELECT f.title, f.did, d.name, f.date_prod, f.kind\n\+> \ FROM distributors d, films f\n\+> \ WHERE f.did = d.did"++> ,"SELECT kind, sum(len) AS total\n\+> \ FROM films\n\+> \ GROUP BY kind\n\+> \ HAVING sum(len) < interval '5 hours';"++> ,"SELECT * FROM distributors ORDER BY name;"+> ,"SELECT * FROM distributors ORDER BY 2;"++> ,"SELECT distributors.name\n\+> \ FROM distributors\n\+> \ WHERE distributors.name LIKE 'W%'\n\+> \UNION\n\+> \SELECT actors.name\n\+> \ FROM actors\n\+> \ WHERE actors.name LIKE 'W%';"++> ,"WITH t AS (\n\+> \ SELECT random() as x FROM generate_series(1, 3)\n\+> \ )\n\+> \SELECT * FROM t\n\+> \UNION ALL\n\+> \SELECT * FROM t"++> ,"WITH RECURSIVE employee_recursive(distance, employee_name, manager_name) AS (\n\+> \ SELECT 1, employee_name, manager_name\n\+> \ FROM employee\n\+> \ WHERE manager_name = 'Mary'\n\+> \ UNION ALL\n\+> \ SELECT er.distance + 1, e.employee_name, e.manager_name\n\+> \ FROM employee_recursive er, employee e\n\+> \ WHERE er.employee_name = e.manager_name\n\+> \ )\n\+> \SELECT distance, employee_name FROM employee_recursive;"++> ,"SELECT m.name AS mname, pname\n\+> \FROM manufacturers m, LATERAL get_product_names(m.id) pname;"++> ,"SELECT m.name AS mname, pname\n\+> \FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true;"++> ,"SELECT 2+2;"++> ,"SELECT distributors.* WHERE distributors.name = 'Westward';"++> ]
+ tools/Language/SQL/SimpleSQL/QueryExprComponents.lhs view
@@ -0,0 +1,202 @@++These are the tests for the query expression components apart from the+table refs which are in a separate file.+++These are a few misc tests which don't fit anywhere else.++> {-# LANGUAGE OverloadedStrings #-}+> module Language.SQL.SimpleSQL.QueryExprComponents (queryExprComponentTests) where++> import Language.SQL.SimpleSQL.TestTypes+> import Language.SQL.SimpleSQL.Syntax+++> queryExprComponentTests :: TestItem+> queryExprComponentTests = Group "queryExprComponentTests"+> [duplicates+> ,selectLists+> ,whereClause+> ,having+> ,orderBy+> ,offsetFetch+> ,combos+> ,withQueries+> ,values+> ,tables+> ]++++> duplicates :: TestItem+> duplicates = Group "duplicates" $ map (uncurry TestQueryExpr)+> [("select a from t" ,ms All)+> ,("select all a from t" ,ms All)+> ,("select distinct a from t", ms Distinct)+> ]+> where+> ms d = makeSelect+> {qeDuplicates = d+> ,qeSelectList = [(Nothing,Iden "a")]+> ,qeFrom = [TRSimple "t"]}++> selectLists :: TestItem+> selectLists = Group "selectLists" $ map (uncurry TestQueryExpr)+> [("select 1",+> makeSelect {qeSelectList = [(Nothing,NumLit "1")]})++> ,("select a"+> ,makeSelect {qeSelectList = [(Nothing,Iden "a")]})++> ,("select a,b"+> ,makeSelect {qeSelectList = [(Nothing,Iden "a")+> ,(Nothing,Iden "b")]})++> ,("select 1+2,3+4"+> ,makeSelect {qeSelectList =+> [(Nothing,BinOp (NumLit "1") "+" (NumLit "2"))+> ,(Nothing,BinOp (NumLit "3") "+" (NumLit "4"))]})++> ,("select a as a, /*comment*/ b as b"+> ,makeSelect {qeSelectList = [(Just "a", Iden "a")+> ,(Just "b", Iden "b")]})++> ,("select a a, b b"+> ,makeSelect {qeSelectList = [(Just "a", Iden "a")+> ,(Just "b", Iden "b")]})+> ]++> whereClause :: TestItem+> whereClause = Group "whereClause" $ map (uncurry TestQueryExpr)+> [("select a from t where a = 5"+> ,makeSelect {qeSelectList = [(Nothing,Iden "a")]+> ,qeFrom = [TRSimple "t"]+> ,qeWhere = Just $ BinOp (Iden "a") "=" (NumLit "5")})+> ]++> having :: TestItem+> having = Group "having" $ map (uncurry TestQueryExpr)+> [("select a,sum(b) from t group by a having sum(b) > 5"+> ,makeSelect {qeSelectList = [(Nothing, Iden "a")+> ,(Nothing, App "sum" [Iden "b"])]+> ,qeFrom = [TRSimple "t"]+> ,qeGroupBy = [SimpleGroup $ Iden "a"]+> ,qeHaving = Just $ BinOp (App "sum" [Iden "b"])+> ">" (NumLit "5")+> })+> ]++> orderBy :: TestItem+> orderBy = Group "orderBy" $ map (uncurry TestQueryExpr)+> [("select a from t order by a"+> ,ms [OrderField (Iden "a") Asc NullsOrderDefault])++> ,("select a from t order by a, b"+> ,ms [OrderField (Iden "a") Asc NullsOrderDefault+> ,OrderField (Iden "b") Asc NullsOrderDefault])++> ,("select a from t order by a asc"+> ,ms [OrderField (Iden "a") Asc NullsOrderDefault])++> ,("select a from t order by a desc, b desc"+> ,ms [OrderField (Iden "a") Desc NullsOrderDefault+> ,OrderField (Iden "b") Desc NullsOrderDefault])++> ,("select a from t order by a desc nulls first, b desc nulls last"+> ,ms [OrderField (Iden "a") Desc NullsFirst+> ,OrderField (Iden "b") Desc NullsLast])++> ]+> where+> ms o = makeSelect {qeSelectList = [(Nothing,Iden "a")]+> ,qeFrom = [TRSimple "t"]+> ,qeOrderBy = o}++> offsetFetch :: TestItem+> offsetFetch = Group "offsetFetch" $ map (uncurry TestQueryExpr)+> [-- ansi standard+> ("select a from t offset 5 rows fetch next 10 rows only"+> ,ms (Just $ NumLit "5") (Just $ NumLit "10"))+> ,("select a from t offset 5 rows;"+> ,ms (Just $ NumLit "5") Nothing)+> ,("select a from t fetch next 10 row only;"+> ,ms Nothing (Just $ NumLit "10"))+> ,("select a from t offset 5 row fetch first 10 row only"+> ,ms (Just $ NumLit "5") (Just $ NumLit "10"))+> -- postgres+> ,("select a from t limit 10 offset 5"+> ,ms (Just $ NumLit "5") (Just $ NumLit "10"))+> ]+> where+> ms o l = makeSelect+> {qeSelectList = [(Nothing,Iden "a")]+> ,qeFrom = [TRSimple "t"]+> ,qeOffset = o+> ,qeFetch = l}++> combos :: TestItem+> combos = Group "combos" $ map (uncurry TestQueryExpr)+> [("select a from t union select b from u"+> ,CombineQueryExpr ms1 Union All Respectively ms2)++> ,("select a from t intersect select b from u"+> ,CombineQueryExpr ms1 Intersect All Respectively ms2)++> ,("select a from t except all select b from u"+> ,CombineQueryExpr ms1 Except All Respectively ms2)++> ,("select a from t union distinct corresponding \+> \select b from u"+> ,CombineQueryExpr ms1 Union Distinct Corresponding ms2)++> ,("select a from t union select a from t union select a from t"+> -- TODO: union should be left associative. I think the others also+> -- so this needs to be fixed (new optionSuffix variation which+> -- handles this)+> ,CombineQueryExpr ms1 Union All Respectively+> (CombineQueryExpr ms1 Union All Respectively ms1))+> ]+> where+> ms1 = makeSelect+> {qeSelectList = [(Nothing,Iden "a")]+> ,qeFrom = [TRSimple "t"]}+> ms2 = makeSelect+> {qeSelectList = [(Nothing,Iden "b")]+> ,qeFrom = [TRSimple "u"]}+++> withQueries :: TestItem+> withQueries = Group "with queries" $ map (uncurry TestQueryExpr)+> [("with u as (select a from t) select a from u"+> ,With False [(Alias "u" Nothing, ms1)] ms2)++> ,("with u(b) as (select a from t) select a from u"+> ,With False [(Alias "u" (Just ["b"]), ms1)] ms2)++> ,("with x as (select a from t),\n\+> \ u as (select a from x)\n\+> \select a from u"+> ,With False [(Alias "x" Nothing, ms1), (Alias "u" Nothing,ms3)] ms2)++> ,("with recursive u as (select a from t) select a from u"+> ,With True [(Alias "u" Nothing, ms1)] ms2)+> ]+> where+> ms c t = makeSelect+> {qeSelectList = [(Nothing,Iden c)]+> ,qeFrom = [TRSimple t]}+> ms1 = ms "a" "t"+> ms2 = ms "a" "u"+> ms3 = ms "a" "x"++> values :: TestItem+> values = Group "values" $ map (uncurry TestQueryExpr)+> [("values (1,2),(3,4)"+> ,Values [[NumLit "1", NumLit "2"]+> ,[NumLit "3", NumLit "4"]])+> ]++> tables :: TestItem+> tables = Group "tables" $ map (uncurry TestQueryExpr)+> [("table tbl", Table "tbl")+> ]
+ tools/Language/SQL/SimpleSQL/QueryExprs.lhs view
@@ -0,0 +1,18 @@++These are the tests for the queryExprs parsing which parses multiple+query expressions from one string.++> module Language.SQL.SimpleSQL.QueryExprs (queryExprsTests) where++> import Language.SQL.SimpleSQL.TestTypes+> import Language.SQL.SimpleSQL.Syntax++> queryExprsTests :: TestItem+> queryExprsTests = Group "query exprs" $ map (uncurry TestQueryExprs)+> [("select 1",[ms])+> ,("select 1;",[ms])+> ,("select 1;select 1",[ms,ms])+> ,(" select 1;select 1; ",[ms,ms])+> ]+> where+> ms = makeSelect {qeSelectList = [(Nothing,NumLit "1")]}
+ tools/Language/SQL/SimpleSQL/ScalarExprs.lhs view
@@ -0,0 +1,304 @@++Tests for parsing scalar expressions++> {-# LANGUAGE OverloadedStrings #-}+> module Language.SQL.SimpleSQL.ScalarExprs (scalarExprTests) where++> import Language.SQL.SimpleSQL.TestTypes+> import Language.SQL.SimpleSQL.Syntax++> scalarExprTests :: TestItem+> scalarExprTests = Group "scalarExprTests"+> [literals+> ,identifiers+> ,star+> ,dots+> ,app+> ,caseexp+> ,operators+> ,parens+> ,subqueries+> ,aggregates+> ,windowFunctions+> ]++> literals :: TestItem+> literals = Group "literals" $ map (uncurry TestScalarExpr)+> [("3", NumLit "3")+> ,("3.", NumLit "3.")+> ,("3.3", NumLit "3.3")+> ,(".3", NumLit ".3")+> ,("3.e3", NumLit "3.e3")+> ,("3.3e3", NumLit "3.3e3")+> ,(".3e3", NumLit ".3e3")+> ,("3e3", NumLit "3e3")+> ,("3e+3", NumLit "3e+3")+> ,("3e-3", NumLit "3e-3")+> ,("'string'", StringLit "string")+> ,("'string with a '' quote'", StringLit "string with a ' quote")+> ,("'1'", StringLit "1")+> ,("interval '3' day", IntervalLit "3" "day" Nothing)+> ,("interval '3' day (3)", IntervalLit "3" "day" $ Just 3)+> ,("interval '3 weeks'", TypedLit (TypeName "interval") "3 weeks")+> ]++> identifiers :: TestItem+> identifiers = Group "identifiers" $ map (uncurry TestScalarExpr)+> [("iden1", Iden "iden1")+> --,("t.a", Iden2 "t" "a")+> ,("\"quoted identifier\"", Iden $ QName "quoted identifier")+> ]++> star :: TestItem+> star = Group "star" $ map (uncurry TestScalarExpr)+> [("*", Star)+> --,("t.*", Star2 "t")+> --,("ROW(t.*,42)", App "ROW" [Star2 "t", NumLit "42"])+> ]++> dots :: TestItem+> dots = Group "dot" $ map (uncurry TestScalarExpr)+> [("t.a", BinOp (Iden "t") "." (Iden "a"))+> ,("t.*", BinOp (Iden "t") "." Star)+> ,("a.b.c", BinOp (BinOp (Iden "a") "." (Iden "b")) "." (Iden "c"))+> ,("ROW(t.*,42)", App "ROW" [BinOp (Iden "t") "." Star, NumLit "42"])+> ]++> app :: TestItem+> app = Group "app" $ map (uncurry TestScalarExpr)+> [("f()", App "f" [])+> ,("f(a)", App "f" [Iden "a"])+> ,("f(a,b)", App "f" [Iden "a", Iden "b"])+> ]++> caseexp :: TestItem+> caseexp = Group "caseexp" $ map (uncurry TestScalarExpr)+> [("case a when 1 then 2 end"+> ,Case (Just $ Iden "a") [([NumLit "1"]+> ,NumLit "2")] Nothing)++> ,("case a when 1 then 2 when 3 then 4 end"+> ,Case (Just $ Iden "a") [([NumLit "1"], NumLit "2")+> ,([NumLit "3"], NumLit "4")] Nothing)++> ,("case a when 1 then 2 when 3 then 4 else 5 end"+> ,Case (Just $ Iden "a") [([NumLit "1"], NumLit "2")+> ,([NumLit "3"], NumLit "4")]+> (Just $ NumLit "5"))++> ,("case when a=1 then 2 when a=3 then 4 else 5 end"+> ,Case Nothing [([BinOp (Iden "a") "=" (NumLit "1")], NumLit "2")+> ,([BinOp (Iden "a") "=" (NumLit "3")], NumLit "4")]+> (Just $ NumLit "5"))++> ,("case a when 1,2 then 10 when 3,4 then 20 end"+> ,Case (Just $ Iden "a") [([NumLit "1",NumLit "2"]+> ,NumLit "10")+> ,([NumLit "3",NumLit "4"]+> ,NumLit "20")]+> Nothing)++> ]++> operators :: TestItem+> operators = Group "operators"+> [binaryOperators+> ,unaryOperators+> ,casts+> ,miscOps]++> binaryOperators :: TestItem+> binaryOperators = Group "binaryOperators" $ map (uncurry TestScalarExpr)+> [("a + b", BinOp (Iden "a") "+" (Iden "b"))+> -- sanity check fixities+> -- todo: add more fixity checking++> ,("a + b * c"+> ,BinOp (Iden "a") "+"+> (BinOp (Iden "b") "*" (Iden "c")))++> ,("a * b + c"+> ,BinOp (BinOp (Iden "a") "*" (Iden "b"))+> "+" (Iden "c"))+> ]++> unaryOperators :: TestItem+> unaryOperators = Group "unaryOperators" $ map (uncurry TestScalarExpr)+> [("not a", PrefixOp "not" $ Iden "a")+> ,("not not a", PrefixOp "not" $ PrefixOp "not" $ Iden "a")+> ,("+a", PrefixOp "+" $ Iden "a")+> ,("-a", PrefixOp "-" $ Iden "a")+> ]+++> casts :: TestItem+> casts = Group "operators" $ map (uncurry TestScalarExpr)+> [("cast('1' as int)"+> ,Cast (StringLit "1") $ TypeName "int")++> ,("int '3'"+> ,TypedLit (TypeName "int") "3")++> ,("cast('1' as double precision)"+> ,Cast (StringLit "1") $ TypeName "double precision")++> ,("cast('1' as float(8))"+> ,Cast (StringLit "1") $ PrecTypeName "float" 8)++> ,("cast('1' as decimal(15,2))"+> ,Cast (StringLit "1") $ PrecScaleTypeName "decimal" 15 2)+++> ,("double precision '3'"+> ,TypedLit (TypeName "double precision") "3")+> ]++> subqueries :: TestItem+> subqueries = Group "unaryOperators" $ map (uncurry TestScalarExpr)+> [("exists (select a from t)", SubQueryExpr SqExists ms)+> ,("(select a from t)", SubQueryExpr SqSq ms)++> ,("a in (select a from t)"+> ,In True (Iden "a") (InQueryExpr ms))++> ,("a not in (select a from t)"+> ,In False (Iden "a") (InQueryExpr ms))++> ,("a > all (select a from t)"+> ,BinOp (Iden "a") ">" (SubQueryExpr SqAll ms))++> ,("a = some (select a from t)"+> ,BinOp (Iden "a") "=" (SubQueryExpr SqSome ms))++> ,("a <= any (select a from t)"+> ,BinOp (Iden "a") "<=" (SubQueryExpr SqAny ms))+> ]+> where+> ms = makeSelect+> {qeSelectList = [(Nothing,Iden "a")]+> ,qeFrom = [TRSimple "t"]+> }++> miscOps :: TestItem+> miscOps = Group "unaryOperators" $ map (uncurry TestScalarExpr)+> [("a in (1,2,3)"+> ,In True (Iden "a") $ InList $ map NumLit ["1","2","3"])++> ,("a between b and c", SpecialOp "between" [Iden "a"+> ,Iden "b"+> ,Iden "c"])++> ,("a not between b and c", SpecialOp "not between" [Iden "a"+> ,Iden "b"+> ,Iden "c"])++> ,("a is null", PostfixOp "is null" (Iden "a"))+> ,("a is not null", PostfixOp "is not null" (Iden "a"))+> ,("a is true", PostfixOp "is true" (Iden "a"))+> ,("a is not true", PostfixOp "is not true" (Iden "a"))+> ,("a is false", PostfixOp "is false" (Iden "a"))+> ,("a is not false", PostfixOp "is not false" (Iden "a"))+> ,("a is unknown", PostfixOp "is unknown" (Iden "a"))+> ,("a is not unknown", PostfixOp "is not unknown" (Iden "a"))+> ,("a is distinct from b", BinOp (Iden "a") "is distinct from"(Iden "b"))++> ,("a is not distinct from b"+> ,BinOp (Iden "a") "is not distinct from" (Iden "b"))++> ,("a like b", BinOp (Iden "a") "like" (Iden "b"))+> ,("a not like b", BinOp (Iden "a") "not like" (Iden "b"))+> ,("a is similar to b", BinOp (Iden "a") "is similar to" (Iden "b"))++> ,("a is not similar to b"+> ,BinOp (Iden "a") "is not similar to" (Iden "b"))++> ,("a overlaps b", BinOp (Iden "a") "overlaps" (Iden "b"))+> ,("extract(day from t)", SpecialOp "extract" [Iden "day", Iden "t"])++> ,("substring(x from 1 for 2)"+> ,SpecialOp "substring" [Iden "x", NumLit "1", NumLit "2"])++> ,("(1,2)"+> ,SpecialOp "rowctor" [NumLit "1", NumLit "2"])++> ]++> aggregates :: TestItem+> aggregates = Group "aggregates" $ map (uncurry TestScalarExpr)+> [("count(*)",App "count" [Star])++> ,("sum(a order by a)"+> ,AggregateApp "sum" Nothing [Iden "a"]+> [OrderField (Iden "a") Asc NullsOrderDefault])++> ,("sum(all a)"+> ,AggregateApp "sum" (Just All) [Iden "a"] [])++> ,("count(distinct a)"+> ,AggregateApp "count" (Just Distinct) [Iden "a"] [])+> ]++> windowFunctions :: TestItem+> windowFunctions = Group "windowFunctions" $ map (uncurry TestScalarExpr)+> [("max(a) over ()", WindowApp "max" [Iden "a"] [] [] Nothing)+> ,("count(*) over ()", WindowApp "count" [Star] [] [] Nothing)++> ,("max(a) over (partition by b)"+> ,WindowApp "max" [Iden "a"] [Iden "b"] [] Nothing)++> ,("max(a) over (partition by b,c)"+> ,WindowApp "max" [Iden "a"] [Iden "b",Iden "c"] [] Nothing)++> ,("sum(a) over (order by b)"+> ,WindowApp "sum" [Iden "a"] []+> [OrderField (Iden "b") Asc NullsOrderDefault] Nothing)++> ,("sum(a) over (order by b desc,c)"+> ,WindowApp "sum" [Iden "a"] []+> [OrderField (Iden "b") Desc NullsOrderDefault+> ,OrderField (Iden "c") Asc NullsOrderDefault] Nothing)++> ,("sum(a) over (partition by b order by c)"+> ,WindowApp "sum" [Iden "a"] [Iden "b"]+> [OrderField (Iden "c") Asc NullsOrderDefault] Nothing)++> ,("sum(a) over (partition by b order by c range unbounded preceding)"+> ,WindowApp "sum" [Iden "a"] [Iden "b"]+> [OrderField (Iden "c") Asc NullsOrderDefault]+> $ Just $ FrameFrom FrameRange UnboundedPreceding)++> ,("sum(a) over (partition by b order by c range 5 preceding)"+> ,WindowApp "sum" [Iden "a"] [Iden "b"]+> [OrderField (Iden "c") Asc NullsOrderDefault]+> $ Just $ FrameFrom FrameRange $ Preceding (NumLit "5"))++> ,("sum(a) over (partition by b order by c range current row)"+> ,WindowApp "sum" [Iden "a"] [Iden "b"]+> [OrderField (Iden "c") Asc NullsOrderDefault]+> $ Just $ FrameFrom FrameRange Current)++> ,("sum(a) over (partition by b order by c rows 5 following)"+> ,WindowApp "sum" [Iden "a"] [Iden "b"]+> [OrderField (Iden "c") Asc NullsOrderDefault]+> $ Just $ FrameFrom FrameRows $ Following (NumLit "5"))++> ,("sum(a) over (partition by b order by c range unbounded following)"+> ,WindowApp "sum" [Iden "a"] [Iden "b"]+> [OrderField (Iden "c") Asc NullsOrderDefault]+> $ Just $ FrameFrom FrameRange UnboundedFollowing)++> ,("sum(a) over (partition by b order by c \n\+> \range between 5 preceding and 5 following)"+> ,WindowApp "sum" [Iden "a"] [Iden "b"]+> [OrderField (Iden "c") Asc NullsOrderDefault]+> $ Just $ FrameBetween FrameRange+> (Preceding (NumLit "5"))+> (Following (NumLit "5")))++> ]++> parens :: TestItem+> parens = Group "parens" $ map (uncurry TestScalarExpr)+> [("(a)", Parens (Iden "a"))+> ,("(a + b)", Parens (BinOp (Iden "a") "+" (Iden "b")))+> ]
+ tools/Language/SQL/SimpleSQL/TableRefs.lhs view
@@ -0,0 +1,104 @@++These are the tests for parsing focusing on the from part of query+expression++> {-# LANGUAGE OverloadedStrings #-}+> module Language.SQL.SimpleSQL.TableRefs (tableRefTests) where++> import Language.SQL.SimpleSQL.TestTypes+> import Language.SQL.SimpleSQL.Syntax+++> tableRefTests :: TestItem+> tableRefTests = Group "tableRefTests" $ map (uncurry TestQueryExpr)+> [("select a from t"+> ,ms [TRSimple "t"])++> ,("select a from f(a)"+> ,ms [TRFunction "f" [Iden "a"]])++> ,("select a from t,u"+> ,ms [TRSimple "t", TRSimple "u"])++these lateral queries make no sense but the syntax is valid++> ,("select a from lateral a"+> ,ms [TRLateral $ TRSimple "a"])++> ,("select a from lateral a,b"+> ,ms [TRLateral $ TRSimple "a", TRSimple "b"])++> ,("select a from a, lateral b"+> ,ms [TRSimple "a", TRLateral $ TRSimple "b"])++> ,("select a from a natural join lateral b"+> ,ms [TRJoin (TRSimple "a") JInner+> (TRLateral $ TRSimple "b")+> (Just JoinNatural)])++> -- the lateral binds on the outside of the join which is incorrect+> ,("select a from lateral a natural join lateral b"+> ,ms [TRJoin (TRLateral $ TRSimple "a") JInner+> (TRLateral $ TRSimple "b")+> (Just JoinNatural)])+++> ,("select a from t inner join u on expr"+> ,ms [TRJoin (TRSimple "t") JInner (TRSimple "u")+> (Just $ JoinOn $ Iden "expr")])++> ,("select a from t join u on expr"+> ,ms [TRJoin (TRSimple "t") JInner (TRSimple "u")+> (Just $ JoinOn $ Iden "expr")])++> ,("select a from t left join u on expr"+> ,ms [TRJoin (TRSimple "t") JLeft (TRSimple "u")+> (Just $ JoinOn $ Iden "expr")])++> ,("select a from t right join u on expr"+> ,ms [TRJoin (TRSimple "t") JRight (TRSimple "u")+> (Just $ JoinOn $ Iden "expr")])++> ,("select a from t full join u on expr"+> ,ms [TRJoin (TRSimple "t") JFull (TRSimple "u")+> (Just $ JoinOn $ Iden "expr")])++> ,("select a from t cross join u"+> ,ms [TRJoin (TRSimple "t")+> JCross (TRSimple "u") Nothing])++> ,("select a from t natural inner join u"+> ,ms [TRJoin (TRSimple "t") JInner (TRSimple "u")+> (Just JoinNatural)])++> ,("select a from t inner join u using(a,b)"+> ,ms [TRJoin (TRSimple "t") JInner (TRSimple "u")+> (Just $ JoinUsing ["a", "b"])])++> ,("select a from (select a from t)"+> ,ms [TRQueryExpr $ ms [TRSimple "t"]])++> ,("select a from t as u"+> ,ms [TRAlias (TRSimple "t") (Alias "u" Nothing)])++> ,("select a from t u"+> ,ms [TRAlias (TRSimple "t") (Alias "u" Nothing)])++> ,("select a from t u(b)"+> ,ms [TRAlias (TRSimple "t") (Alias "u" $ Just ["b"])])++> ,("select a from (t cross join u) as u"+> ,ms [TRAlias (TRParens $+> TRJoin (TRSimple "t") JCross (TRSimple "u") Nothing)+> (Alias "u" Nothing)])+> -- todo: not sure if the associativity is correct++> ,("select a from t cross join u cross join v",+> ms [TRJoin+> (TRJoin (TRSimple "t")+> JCross (TRSimple "u") Nothing)+> JCross (TRSimple "v") Nothing])+> ]+> where+> ms f = makeSelect {qeSelectList = [(Nothing,Iden "a")]+> ,qeFrom = f}
+ tools/Language/SQL/SimpleSQL/TestTypes.lhs view
@@ -0,0 +1,26 @@++This is the types used to define the tests as pure data. See the+Tests.lhs module for the 'interpreter'.++> module Language.SQL.SimpleSQL.TestTypes where++> import Language.SQL.SimpleSQL.Syntax++> import Data.String++> data TestItem = Group String [TestItem]+> | TestScalarExpr String ScalarExpr+> | TestQueryExpr String QueryExpr+> | TestQueryExprs String [QueryExpr]++this just checks the sql parses without error, mostly just a+intermediate when I'm too lazy to write out the parsed AST. These+should all be TODO to convert to a testqueryexpr test.++> | ParseQueryExpr String+> deriving (Eq,Show)++hack to make the tests a bit simpler++> instance IsString Name where+> fromString = Name
tools/Language/SQL/SimpleSQL/Tests.lhs view
@@ -1,483 +1,54 @@ +TODO:++split into multiple files:+scalar expressions+tablerefs+other queryexpr parts: not enough to split into multiple files+full queries+tpch tests++ > module Language.SQL.SimpleSQL.Tests > (testData > ,tests > ,TestItem(..) > ) where -> import Language.SQL.SimpleSQL.Syntax-> import Language.SQL.SimpleSQL.Pretty-> import Language.SQL.SimpleSQL.Parser-> import qualified Test.HUnit as H-> import Tpch > import Test.Framework > import Test.Framework.Providers.HUnit--> data TestItem = Group String [TestItem]-> | TestScalarExpr String ScalarExpr-> | TestQueryExpr String QueryExpr-> | TestQueryExprs String [QueryExpr]-> | ParseQueryExpr String-> deriving (Eq,Show)--> scalarExprParserTests :: TestItem-> scalarExprParserTests = Group "scalarExprParserTests"-> [literals-> ,identifiers-> ,star-> ,app-> ,caseexp-> ,operators-> ,parens-> ,subqueries-> ,aggregates-> ,windowFunctions-> ]--> literals :: TestItem-> literals = Group "literals" $ map (uncurry TestScalarExpr)-> [("3", NumLit "3")-> ,("3.", NumLit "3.")-> ,("3.3", NumLit "3.3")-> ,(".3", NumLit ".3")-> ,("3.e3", NumLit "3.e3")-> ,("3.3e3", NumLit "3.3e3")-> ,(".3e3", NumLit ".3e3")-> ,("3e3", NumLit "3e3")-> ,("3e+3", NumLit "3e+3")-> ,("3e-3", NumLit "3e-3")-> ,("'string'", StringLit "string")-> ,("'1'", StringLit "1")-> ,("interval '3' day", IntervalLit "3" "day" Nothing)-> ,("interval '3' day (3)", IntervalLit "3" "day" $ Just 3)-> ]--> identifiers :: TestItem-> identifiers = Group "identifiers" $ map (uncurry TestScalarExpr)-> [("iden1", Iden "iden1")-> ,("t.a", Iden2 "t" "a")-> ]--> star :: TestItem-> star = Group "star" $ map (uncurry TestScalarExpr)-> [("*", Star)-> ,("t.*", Star2 "t")-> ]--> app :: TestItem-> app = Group "app" $ map (uncurry TestScalarExpr)-> [("f()", App "f" [])-> ,("f(a)", App "f" [Iden "a"])-> ,("f(a,b)", App "f" [Iden "a", Iden "b"])-> ]--> caseexp :: TestItem-> caseexp = Group "caseexp" $ map (uncurry TestScalarExpr)-> [("case a when 1 then 2 end"-> ,Case (Just $ Iden "a") [(NumLit "1"-> ,NumLit "2")] Nothing)-> ,("case a when 1 then 2 when 3 then 4 end"-> ,Case (Just $ Iden "a") [(NumLit "1", NumLit "2")-> ,(NumLit "3", NumLit "4")] Nothing)-> ,("case a when 1 then 2 when 3 then 4 else 5 end"-> ,Case (Just $ Iden "a") [(NumLit "1", NumLit "2")-> ,(NumLit "3", NumLit "4")] (Just $ NumLit "5"))-> ,("case when a=1 then 2 when a=3 then 4 else 5 end"-> ,Case Nothing [(BinOp (Iden "a") "=" (NumLit "1"), NumLit "2")-> ,(BinOp (Iden "a") "=" (NumLit "3"), NumLit "4")]-> (Just $ NumLit "5"))-> ]--> operators :: TestItem-> operators = Group "operators"-> [binaryOperators-> ,unaryOperators-> ,casts-> ,miscOps]--> binaryOperators :: TestItem-> binaryOperators = Group "binaryOperators" $ map (uncurry TestScalarExpr)-> [("a + b", BinOp (Iden "a") "+" (Iden "b"))-> -- sanity check fixities-> -- todo: add more fixity checking-> ,("a + b * c"-> ,BinOp (Iden "a") "+"-> (BinOp (Iden "b") "*" (Iden "c")))-> ,("a * b + c"-> ,BinOp (BinOp (Iden "a") "*" (Iden "b"))-> "+" (Iden "c"))-> ]--> unaryOperators :: TestItem-> unaryOperators = Group "unaryOperators" $ map (uncurry TestScalarExpr)-> [("not a", PrefixOp "not" $ Iden "a")-> ,("not not a", PrefixOp "not" $ PrefixOp "not" $ Iden "a")-> ,("+a", PrefixOp "+" $ Iden "a")-> ,("-a", PrefixOp "-" $ Iden "a")-> ]---> casts :: TestItem-> casts = Group "operators" $ map (uncurry TestScalarExpr)-> [("cast('1' as int)"-> ,Cast (StringLit "1") $ TypeName "int")-> ,("int '3'"-> ,CastOp (TypeName "int") "3")-> ,("cast('1' as double precision)"-> ,Cast (StringLit "1") $ TypeName "double precision")-> ,("double precision '3'"-> ,CastOp (TypeName "double precision") "3")-> ]--> subqueries :: TestItem-> subqueries = Group "unaryOperators" $ map (uncurry TestScalarExpr)-> [("exists (select a from t)", SubQueryExpr SqExists ms)-> ,("(select a from t)", SubQueryExpr SqSq ms)-> ,("a in (select a from t)"-> ,In True (Iden "a") (InQueryExpr ms))-> ,("a not in (select a from t)"-> ,In False (Iden "a") (InQueryExpr ms))-> ,("a > all (select a from t)"-> ,BinOp (Iden "a") ">" (SubQueryExpr SqAll ms))-> ,("a = some (select a from t)"-> ,BinOp (Iden "a") "=" (SubQueryExpr SqSome ms))-> ,("a <= any (select a from t)"-> ,BinOp (Iden "a") "<=" (SubQueryExpr SqAny ms))-> ]-> where-> ms = makeSelect-> {qeSelectList = [(Nothing,Iden "a")]-> ,qeFrom = [TRSimple "t"]-> }--> miscOps :: TestItem-> miscOps = Group "unaryOperators" $ map (uncurry TestScalarExpr)-> [("a in (1,2,3)"-> ,In True (Iden "a") $ InList $ map NumLit ["1","2","3"])-> ,("a between b and c", SpecialOp "between" [Iden "a"-> ,Iden "b"-> ,Iden "c"])-> ,("a not between b and c", SpecialOp "not between" [Iden "a"-> ,Iden "b"-> ,Iden "c"])-> ,("a is null", PostfixOp "is null" (Iden "a"))-> ,("a is not null", PostfixOp "is not null" (Iden "a"))-> ,("a is true", PostfixOp "is true" (Iden "a"))-> ,("a is not true", PostfixOp "is not true" (Iden "a"))-> ,("a is false", PostfixOp "is false" (Iden "a"))-> ,("a is not false", PostfixOp "is not false" (Iden "a"))-> ,("a is unknown", PostfixOp "is unknown" (Iden "a"))-> ,("a is not unknown", PostfixOp "is not unknown" (Iden "a"))-> ,("a is distinct from b", BinOp (Iden "a") "is distinct from"(Iden "b"))-> ,("a is not distinct from b"-> ,BinOp (Iden "a") "is not distinct from" (Iden "b"))-> ,("a like b", BinOp (Iden "a") "like" (Iden "b"))-> ,("a not like b", BinOp (Iden "a") "not like" (Iden "b"))-> ,("a is similar to b", BinOp (Iden "a") "is similar to" (Iden "b"))-> ,("a is not similar to b"-> ,BinOp (Iden "a") "is not similar to" (Iden "b"))-> ,("a overlaps b", BinOp (Iden "a") "overlaps" (Iden "b"))-> ,("extract(day from t)", SpecialOp "extract" [Iden "day", Iden "t"])-> ,("substring(x from 1 for 2)"-> ,SpecialOp "substring" [Iden "x", NumLit "1", NumLit "2"])-> ]--> aggregates :: TestItem-> aggregates = Group "aggregates" $ map (uncurry TestScalarExpr)-> [("count(*)",App "count" [Star])-> ,("sum(a order by a)"-> ,AggregateApp "sum" Nothing [Iden "a"] [(Iden "a", Asc)])-> ,("sum(all a)"-> ,AggregateApp "sum" (Just All) [Iden "a"] [])-> ,("count(distinct a)"-> ,AggregateApp "count" (Just Distinct) [Iden "a"] [])-> ]+> import qualified Test.HUnit as H -> windowFunctions :: TestItem-> windowFunctions = Group "windowFunctions" $ map (uncurry TestScalarExpr)-> [("max(a) over ()", WindowApp "max" [Iden "a"] [] [])-> ,("count(*) over ()", WindowApp "count" [Star] [] [])-> ,("max(a) over (partition by b)"-> ,WindowApp "max" [Iden "a"] [Iden "b"] [])-> ,("max(a) over (partition by b,c)"-> ,WindowApp "max" [Iden "a"] [Iden "b",Iden "c"] [])-> ,("sum(a) over (order by b)"-> ,WindowApp "sum" [Iden "a"] [] [(Iden "b", Asc)])-> ,("sum(a) over (order by b desc,c)"-> ,WindowApp "sum" [Iden "a"] [] [(Iden "b", Desc)-> ,(Iden "c", Asc)])-> ,("sum(a) over (partition by b order by c)"-> ,WindowApp "sum" [Iden "a"] [Iden "b"] [(Iden "c", Asc)])-> -- todo: check order by options, add frames-> ]+> --import Language.SQL.SimpleSQL.Syntax+> import Language.SQL.SimpleSQL.Pretty+> import Language.SQL.SimpleSQL.Parser -> parens :: TestItem-> parens = Group "parens" $ map (uncurry TestScalarExpr)-> [("(a)", Parens (Iden "a"))-> ,("(a + b)", Parens (BinOp (Iden "a") "+" (Iden "b")))-> ]+> import Language.SQL.SimpleSQL.TestTypes -> queryExprParserTests :: TestItem-> queryExprParserTests = Group "queryExprParserTests"-> [duplicates-> ,selectLists-> ,from-> ,whereClause-> ,groupByClause-> ,having-> ,orderBy-> ,limit-> ,combos-> ,withQueries-> ,fullQueries-> ]+> import Language.SQL.SimpleSQL.FullQueries+> import Language.SQL.SimpleSQL.GroupBy+> import Language.SQL.SimpleSQL.Postgres+> import Language.SQL.SimpleSQL.QueryExprComponents+> import Language.SQL.SimpleSQL.QueryExprs+> import Language.SQL.SimpleSQL.TableRefs+> import Language.SQL.SimpleSQL.ScalarExprs+> import Language.SQL.SimpleSQL.Tpch -> duplicates :: TestItem-> duplicates = Group "duplicates" $ map (uncurry TestQueryExpr)-> [("select a from t" ,ms All)-> ,("select all a from t" ,ms All)-> ,("select distinct a from t", ms Distinct)-> ]-> where-> ms d = makeSelect-> {qeDuplicates = d-> ,qeSelectList = [(Nothing,Iden "a")]-> ,qeFrom = [TRSimple "t"]}--> selectLists :: TestItem-> selectLists = Group "selectLists" $ map (uncurry TestQueryExpr)-> [("select 1",-> makeSelect {qeSelectList = [(Nothing,NumLit "1")]})-> ,("select a"-> ,makeSelect {qeSelectList = [(Nothing,Iden "a")]})-> ,("select a,b"-> ,makeSelect {qeSelectList = [(Nothing,Iden "a")-> ,(Nothing,Iden "b")]})-> ,("select 1+2,3+4"-> ,makeSelect {qeSelectList =-> [(Nothing,BinOp (NumLit "1") "+" (NumLit "2"))-> ,(Nothing,BinOp (NumLit "3") "+" (NumLit "4"))]})-> ,("select a as a, /*comment*/ b as b"-> ,makeSelect {qeSelectList = [(Just "a", Iden "a")-> ,(Just "b", Iden "b")]})-> ,("select a a, b b"-> ,makeSelect {qeSelectList = [(Just "a", Iden "a")-> ,(Just "b", Iden "b")]})-> ]--> from :: TestItem-> from = Group "from" $ map (uncurry TestQueryExpr)-> [("select a from t"-> ,ms [TRSimple "t"])-> ,("select a from t,u"-> ,ms [TRSimple "t", TRSimple "u"])-> ,("select a from t inner join u on expr"-> ,ms [TRJoin (TRSimple "t") JInner (TRSimple "u")-> (Just $ JoinOn $ Iden "expr")])-> ,("select a from t left join u on expr"-> ,ms [TRJoin (TRSimple "t") JLeft (TRSimple "u")-> (Just $ JoinOn $ Iden "expr")])-> ,("select a from t right join u on expr"-> ,ms [TRJoin (TRSimple "t") JRight (TRSimple "u")-> (Just $ JoinOn $ Iden "expr")])-> ,("select a from t full join u on expr"-> ,ms [TRJoin (TRSimple "t") JFull (TRSimple "u")-> (Just $ JoinOn $ Iden "expr")])-> ,("select a from t cross join u"-> ,ms [TRJoin (TRSimple "t")-> JCross (TRSimple "u") Nothing])-> ,("select a from t natural inner join u"-> ,ms [TRJoin (TRSimple "t") JInner (TRSimple "u")-> (Just JoinNatural)])-> ,("select a from t inner join u using(a,b)"-> ,ms [TRJoin (TRSimple "t") JInner (TRSimple "u")-> (Just $ JoinUsing ["a", "b"])])-> ,("select a from (select a from t)"-> ,ms [TRQueryExpr $ ms [TRSimple "t"]])-> ,("select a from t as u"-> ,ms [TRAlias (TRSimple "t") "u" Nothing])-> ,("select a from t u"-> ,ms [TRAlias (TRSimple "t") "u" Nothing])-> ,("select a from t u(b)"-> ,ms [TRAlias (TRSimple "t") "u" $ Just ["b"]])-> ,("select a from (t cross join u) as u"-> ,ms [TRAlias (TRParens $-> TRJoin (TRSimple "t") JCross (TRSimple "u") Nothing)-> "u" Nothing])-> -- todo: not sure if the associativity is correct-> ,("select a from t cross join u cross join v",-> ms [TRJoin-> (TRJoin (TRSimple "t")-> JCross (TRSimple "u") Nothing)-> JCross (TRSimple "v") Nothing])-> ]-> where-> ms f = makeSelect {qeSelectList = [(Nothing,Iden "a")]-> ,qeFrom = f}--> whereClause :: TestItem-> whereClause = Group "whereClause" $ map (uncurry TestQueryExpr)-> [("select a from t where a = 5"-> ,makeSelect {qeSelectList = [(Nothing,Iden "a")]-> ,qeFrom = [TRSimple "t"]-> ,qeWhere = Just $ BinOp (Iden "a") "=" (NumLit "5")})-> ]--> groupByClause :: TestItem-> groupByClause = Group "groupByClause" $ map (uncurry TestQueryExpr)-> [("select a,sum(b) from t group by a"-> ,makeSelect {qeSelectList = [(Nothing, Iden "a")-> ,(Nothing, App "sum" [Iden "b"])]-> ,qeFrom = [TRSimple "t"]-> ,qeGroupBy = [Iden "a"]-> })-> ,("select a,b,sum(c) from t group by a,b"-> ,makeSelect {qeSelectList = [(Nothing, Iden "a")-> ,(Nothing, Iden "b")-> ,(Nothing, App "sum" [Iden "c"])]-> ,qeFrom = [TRSimple "t"]-> ,qeGroupBy = [Iden "a",Iden "b"]-> })-> ]--> having :: TestItem-> having = Group "having" $ map (uncurry TestQueryExpr)-> [("select a,sum(b) from t group by a having sum(b) > 5"-> ,makeSelect {qeSelectList = [(Nothing, Iden "a")-> ,(Nothing, App "sum" [Iden "b"])]-> ,qeFrom = [TRSimple "t"]-> ,qeGroupBy = [Iden "a"]-> ,qeHaving = Just $ BinOp (App "sum" [Iden "b"])-> ">" (NumLit "5")-> })-> ]--> orderBy :: TestItem-> orderBy = Group "orderBy" $ map (uncurry TestQueryExpr)-> [("select a from t order by a"-> ,ms [(Iden "a", Asc)])-> ,("select a from t order by a, b"-> ,ms [(Iden "a", Asc), (Iden "b", Asc)])-> ,("select a from t order by a asc"-> ,ms [(Iden "a", Asc)])-> ,("select a from t order by a desc, b desc"-> ,ms [(Iden "a", Desc), (Iden "b", Desc)])-> ]-> where-> ms o = makeSelect {qeSelectList = [(Nothing,Iden "a")]-> ,qeFrom = [TRSimple "t"]-> ,qeOrderBy = o}--> limit :: TestItem-> limit = Group "limit" $ map (uncurry TestQueryExpr)-> [("select a from t limit 10"-> ,ms (Just $ NumLit "10") Nothing)-> ,("select a from t limit 10 offset 10"-> ,ms (Just $ NumLit "10") (Just $ NumLit "10"))-> ]-> where-> ms l o = makeSelect-> {qeSelectList = [(Nothing,Iden "a")]-> ,qeFrom = [TRSimple "t"]-> ,qeLimit = l-> ,qeOffset = o}--> combos :: TestItem-> combos = Group "combos" $ map (uncurry TestQueryExpr)-> [("select a from t union select b from u"-> ,CombineQueryExpr ms1 Union All Respectively ms2)-> ,("select a from t intersect select b from u"-> ,CombineQueryExpr ms1 Intersect All Respectively ms2)-> ,("select a from t except all select b from u"-> ,CombineQueryExpr ms1 Except All Respectively ms2)-> ,("select a from t union distinct corresponding \-> \select b from u"-> ,CombineQueryExpr ms1 Union Distinct Corresponding ms2)-> ,("select a from t union select a from t union select a from t"-> -- is this the correct associativity? -> ,CombineQueryExpr ms1 Union All Respectively-> (CombineQueryExpr ms1 Union All Respectively ms1))-> ]-> where-> ms1 = makeSelect-> {qeSelectList = [(Nothing,Iden "a")]-> ,qeFrom = [TRSimple "t"]}-> ms2 = makeSelect-> {qeSelectList = [(Nothing,Iden "b")]-> ,qeFrom = [TRSimple "u"]}---> withQueries :: TestItem-> withQueries = Group "with queries" $ map (uncurry TestQueryExpr)-> [("with u as (select a from t) select a from u"-> ,With [("u", ms1)] ms2)-> ,("with x as (select a from t),\n\-> \ u as (select a from x)\n\-> \select a from u"-> ,With [("x", ms1), ("u",ms3)] ms2)-> ]-> where-> ms c t = makeSelect-> {qeSelectList = [(Nothing,Iden c)]-> ,qeFrom = [TRSimple t]}-> ms1 = ms "a" "t"-> ms2 = ms "a" "u"-> ms3 = ms "a" "x"---> fullQueries :: TestItem-> fullQueries = Group "queries" $ map (uncurry TestQueryExpr)-> [("select count(*) from t"-> ,makeSelect-> {qeSelectList = [(Nothing, App "count" [Star])]-> ,qeFrom = [TRSimple "t"]-> }-> )-> ,("select a, sum(c+d) as s\n\-> \ from t,u\n\-> \ where a > 5\n\-> \ group by a\n\-> \ having count(1) > 5\n\-> \ order by s"-> ,makeSelect-> {qeSelectList = [(Nothing, Iden "a")-> ,(Just "s"-> ,App "sum" [BinOp (Iden "c")-> "+" (Iden "d")])]-> ,qeFrom = [TRSimple "t", TRSimple "u"]-> ,qeWhere = Just $ BinOp (Iden "a") ">" (NumLit "5")-> ,qeGroupBy = [Iden "a"]-> ,qeHaving = Just $ BinOp (App "count" [NumLit "1"])-> ">" (NumLit "5")-> ,qeOrderBy = [(Iden "s", Asc)]-> }-> )-> ]--> queryExprsParserTests :: TestItem-> queryExprsParserTests = Group "query exprs" $ map (uncurry TestQueryExprs)-> [("select 1",[ms])-> ,("select 1;",[ms])-> ,("select 1;select 1",[ms,ms])-> ,("select 1;select 1;",[ms,ms])-> ,(" select 1;select 1; ",[ms,ms])-> ]-> where-> ms = makeSelect {qeSelectList = [(Nothing,NumLit "1")]}--> tpchTests :: TestItem-> tpchTests =-> Group "parse tpch"-> $ map (ParseQueryExpr . snd) tpchQueries+Order the tests to start from the simplest first. This is also the+order on the generated documentation. > testData :: TestItem > testData = > Group "parserTest"-> [scalarExprParserTests-> ,queryExprParserTests-> ,queryExprsParserTests+> [scalarExprTests+> ,queryExprComponentTests+> ,queryExprsTests+> ,tableRefTests+> ,groupByTests+> ,fullQueriesTests+> ,postgresTests > ,tpchTests > ] @@ -514,7 +85,8 @@ > let str' = pp got > let egot' = parser "" Nothing str' > case egot' of-> Left e' -> H.assertFailure $ "pp roundtrip " ++ peFormattedError e'+> Left e' -> H.assertFailure $ "pp roundtrip "+> ++ peFormattedError e' > Right got' -> H.assertEqual "pp roundtrip" expected got' > toPTest :: (Eq a, Show a) =>@@ -530,5 +102,6 @@ > let str' = pp got > let egot' = parser "" Nothing str' > case egot' of-> Left e' -> H.assertFailure $ "pp roundtrip " ++ peFormattedError e'+> Left e' -> H.assertFailure $ "pp roundtrip "+> ++ peFormattedError e' > Right _got' -> return ()
+ tools/Language/SQL/SimpleSQL/Tpch.lhs view
@@ -0,0 +1,683 @@++Some tests for parsing the tpch queries++The changes made to the official syntax are:+1. replace the set rowcount with ansi standard fetch first n rows only+2. replace the create view, query, drop view sequence with a query+ using a common table expression++> module Language.SQL.SimpleSQL.Tpch (tpchTests,tpchQueries) where++> import Language.SQL.SimpleSQL.TestTypes++> tpchTests :: TestItem+> tpchTests =+> Group "parse tpch"+> $ map (ParseQueryExpr . snd) tpchQueries++> tpchQueries :: [(String,String)]+> tpchQueries =+> [("Q1","\n\+> \select\n\+> \ l_returnflag,\n\+> \ l_linestatus,\n\+> \ sum(l_quantity) as sum_qty,\n\+> \ sum(l_extendedprice) as sum_base_price,\n\+> \ sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,\n\+> \ sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,\n\+> \ avg(l_quantity) as avg_qty,\n\+> \ avg(l_extendedprice) as avg_price,\n\+> \ avg(l_discount) as avg_disc,\n\+> \ count(*) as count_order\n\+> \from\n\+> \ lineitem\n\+> \where\n\+> \ l_shipdate <= date '1998-12-01' - interval '63' day (3)\n\+> \group by\n\+> \ l_returnflag,\n\+> \ l_linestatus\n\+> \order by\n\+> \ l_returnflag,\n\+> \ l_linestatus")+> ,("Q2","\n\+> \select\n\+> \ s_acctbal,\n\+> \ s_name,\n\+> \ n_name,\n\+> \ p_partkey,\n\+> \ p_mfgr,\n\+> \ s_address,\n\+> \ s_phone,\n\+> \ s_comment\n\+> \from\n\+> \ part,\n\+> \ supplier,\n\+> \ partsupp,\n\+> \ nation,\n\+> \ region\n\+> \where\n\+> \ p_partkey = ps_partkey\n\+> \ and s_suppkey = ps_suppkey\n\+> \ and p_size = 15\n\+> \ and p_type like '%BRASS'\n\+> \ and s_nationkey = n_nationkey\n\+> \ and n_regionkey = r_regionkey\n\+> \ and r_name = 'EUROPE'\n\+> \ and ps_supplycost = (\n\+> \ select\n\+> \ min(ps_supplycost)\n\+> \ from\n\+> \ partsupp,\n\+> \ supplier,\n\+> \ nation,\n\+> \ region\n\+> \ where\n\+> \ p_partkey = ps_partkey\n\+> \ and s_suppkey = ps_suppkey\n\+> \ and s_nationkey = n_nationkey\n\+> \ and n_regionkey = r_regionkey\n\+> \ and r_name = 'EUROPE'\n\+> \ )\n\+> \order by\n\+> \ s_acctbal desc,\n\+> \ n_name,\n\+> \ s_name,\n\+> \ p_partkey\n\+> \fetch first 100 rows only")+> ,("Q3","\n\+> \ select\n\+> \ l_orderkey,\n\+> \ sum(l_extendedprice * (1 - l_discount)) as revenue,\n\+> \ o_orderdate,\n\+> \ o_shippriority\n\+> \ from\n\+> \ customer,\n\+> \ orders,\n\+> \ lineitem\n\+> \ where\n\+> \ c_mktsegment = 'MACHINERY'\n\+> \ and c_custkey = o_custkey\n\+> \ and l_orderkey = o_orderkey\n\+> \ and o_orderdate < date '1995-03-21'\n\+> \ and l_shipdate > date '1995-03-21'\n\+> \ group by\n\+> \ l_orderkey,\n\+> \ o_orderdate,\n\+> \ o_shippriority\n\+> \ order by\n\+> \ revenue desc,\n\+> \ o_orderdate\n\+> \ fetch first 10 rows only")+> ,("Q4","\n\+> \ select\n\+> \ o_orderpriority,\n\+> \ count(*) as order_count\n\+> \ from\n\+> \ orders\n\+> \ where\n\+> \ o_orderdate >= date '1996-03-01'\n\+> \ and o_orderdate < date '1996-03-01' + interval '3' month\n\+> \ and exists (\n\+> \ select\n\+> \ *\n\+> \ from\n\+> \ lineitem\n\+> \ where\n\+> \ l_orderkey = o_orderkey\n\+> \ and l_commitdate < l_receiptdate\n\+> \ )\n\+> \ group by\n\+> \ o_orderpriority\n\+> \ order by\n\+> \ o_orderpriority")+> ,("Q5","\n\+> \ select\n\+> \ n_name,\n\+> \ sum(l_extendedprice * (1 - l_discount)) as revenue\n\+> \ from\n\+> \ customer,\n\+> \ orders,\n\+> \ lineitem,\n\+> \ supplier,\n\+> \ nation,\n\+> \ region\n\+> \ where\n\+> \ c_custkey = o_custkey\n\+> \ and l_orderkey = o_orderkey\n\+> \ and l_suppkey = s_suppkey\n\+> \ and c_nationkey = s_nationkey\n\+> \ and s_nationkey = n_nationkey\n\+> \ and n_regionkey = r_regionkey\n\+> \ and r_name = 'EUROPE'\n\+> \ and o_orderdate >= date '1997-01-01'\n\+> \ and o_orderdate < date '1997-01-01' + interval '1' year\n\+> \ group by\n\+> \ n_name\n\+> \ order by\n\+> \ revenue desc")+> ,("Q6","\n\+> \ select\n\+> \ sum(l_extendedprice * l_discount) as revenue\n\+> \ from\n\+> \ lineitem\n\+> \ where\n\+> \ l_shipdate >= date '1997-01-01'\n\+> \ and l_shipdate < date '1997-01-01' + interval '1' year\n\+> \ and l_discount between 0.07 - 0.01 and 0.07 + 0.01\n\+> \ and l_quantity < 24")+> ,("Q7","\n\+> \ select\n\+> \ supp_nation,\n\+> \ cust_nation,\n\+> \ l_year,\n\+> \ sum(volume) as revenue\n\+> \ from\n\+> \ (\n\+> \ select\n\+> \ n1.n_name as supp_nation,\n\+> \ n2.n_name as cust_nation,\n\+> \ extract(year from l_shipdate) as l_year,\n\+> \ l_extendedprice * (1 - l_discount) as volume\n\+> \ from\n\+> \ supplier,\n\+> \ lineitem,\n\+> \ orders,\n\+> \ customer,\n\+> \ nation n1,\n\+> \ nation n2\n\+> \ where\n\+> \ s_suppkey = l_suppkey\n\+> \ and o_orderkey = l_orderkey\n\+> \ and c_custkey = o_custkey\n\+> \ and s_nationkey = n1.n_nationkey\n\+> \ and c_nationkey = n2.n_nationkey\n\+> \ and (\n\+> \ (n1.n_name = 'PERU' and n2.n_name = 'IRAQ')\n\+> \ or (n1.n_name = 'IRAQ' and n2.n_name = 'PERU')\n\+> \ )\n\+> \ and l_shipdate between date '1995-01-01' and date '1996-12-31'\n\+> \ ) as shipping\n\+> \ group by\n\+> \ supp_nation,\n\+> \ cust_nation,\n\+> \ l_year\n\+> \ order by\n\+> \ supp_nation,\n\+> \ cust_nation,\n\+> \ l_year")+> ,("Q8","\n\+> \ select\n\+> \ o_year,\n\+> \ sum(case\n\+> \ when nation = 'IRAQ' then volume\n\+> \ else 0\n\+> \ end) / sum(volume) as mkt_share\n\+> \ from\n\+> \ (\n\+> \ select\n\+> \ extract(year from o_orderdate) as o_year,\n\+> \ l_extendedprice * (1 - l_discount) as volume,\n\+> \ n2.n_name as nation\n\+> \ from\n\+> \ part,\n\+> \ supplier,\n\+> \ lineitem,\n\+> \ orders,\n\+> \ customer,\n\+> \ nation n1,\n\+> \ nation n2,\n\+> \ region\n\+> \ where\n\+> \ p_partkey = l_partkey\n\+> \ and s_suppkey = l_suppkey\n\+> \ and l_orderkey = o_orderkey\n\+> \ and o_custkey = c_custkey\n\+> \ and c_nationkey = n1.n_nationkey\n\+> \ and n1.n_regionkey = r_regionkey\n\+> \ and r_name = 'MIDDLE EAST'\n\+> \ and s_nationkey = n2.n_nationkey\n\+> \ and o_orderdate between date '1995-01-01' and date '1996-12-31'\n\+> \ and p_type = 'STANDARD ANODIZED BRASS'\n\+> \ ) as all_nations\n\+> \ group by\n\+> \ o_year\n\+> \ order by\n\+> \ o_year")+> ,("Q9","\n\+> \ select\n\+> \ nation,\n\+> \ o_year,\n\+> \ sum(amount) as sum_profit\n\+> \ from\n\+> \ (\n\+> \ select\n\+> \ n_name as nation,\n\+> \ extract(year from o_orderdate) as o_year,\n\+> \ l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount\n\+> \ from\n\+> \ part,\n\+> \ supplier,\n\+> \ lineitem,\n\+> \ partsupp,\n\+> \ orders,\n\+> \ nation\n\+> \ where\n\+> \ s_suppkey = l_suppkey\n\+> \ and ps_suppkey = l_suppkey\n\+> \ and ps_partkey = l_partkey\n\+> \ and p_partkey = l_partkey\n\+> \ and o_orderkey = l_orderkey\n\+> \ and s_nationkey = n_nationkey\n\+> \ and p_name like '%antique%'\n\+> \ ) as profit\n\+> \ group by\n\+> \ nation,\n\+> \ o_year\n\+> \ order by\n\+> \ nation,\n\+> \ o_year desc")+> ,("Q10","\n\+> \ select\n\+> \ c_custkey,\n\+> \ c_name,\n\+> \ sum(l_extendedprice * (1 - l_discount)) as revenue,\n\+> \ c_acctbal,\n\+> \ n_name,\n\+> \ c_address,\n\+> \ c_phone,\n\+> \ c_comment\n\+> \ from\n\+> \ customer,\n\+> \ orders,\n\+> \ lineitem,\n\+> \ nation\n\+> \ where\n\+> \ c_custkey = o_custkey\n\+> \ and l_orderkey = o_orderkey\n\+> \ and o_orderdate >= date '1993-12-01'\n\+> \ and o_orderdate < date '1993-12-01' + interval '3' month\n\+> \ and l_returnflag = 'R'\n\+> \ and c_nationkey = n_nationkey\n\+> \ group by\n\+> \ c_custkey,\n\+> \ c_name,\n\+> \ c_acctbal,\n\+> \ c_phone,\n\+> \ n_name,\n\+> \ c_address,\n\+> \ c_comment\n\+> \ order by\n\+> \ revenue desc\n\+> \ fetch first 20 rows only")+> ,("Q11","\n\+> \ select\n\+> \ ps_partkey,\n\+> \ sum(ps_supplycost * ps_availqty) as value\n\+> \ from\n\+> \ partsupp,\n\+> \ supplier,\n\+> \ nation\n\+> \ where\n\+> \ ps_suppkey = s_suppkey\n\+> \ and s_nationkey = n_nationkey\n\+> \ and n_name = 'CHINA'\n\+> \ group by\n\+> \ ps_partkey having\n\+> \ sum(ps_supplycost * ps_availqty) > (\n\+> \ select\n\+> \ sum(ps_supplycost * ps_availqty) * 0.0001000000\n\+> \ from\n\+> \ partsupp,\n\+> \ supplier,\n\+> \ nation\n\+> \ where\n\+> \ ps_suppkey = s_suppkey\n\+> \ and s_nationkey = n_nationkey\n\+> \ and n_name = 'CHINA'\n\+> \ )\n\+> \ order by\n\+> \ value desc")+> ,("Q12","\n\+> \ select\n\+> \ l_shipmode,\n\+> \ sum(case\n\+> \ when o_orderpriority = '1-URGENT'\n\+> \ or o_orderpriority = '2-HIGH'\n\+> \ then 1\n\+> \ else 0\n\+> \ end) as high_line_count,\n\+> \ sum(case\n\+> \ when o_orderpriority <> '1-URGENT'\n\+> \ and o_orderpriority <> '2-HIGH'\n\+> \ then 1\n\+> \ else 0\n\+> \ end) as low_line_count\n\+> \ from\n\+> \ orders,\n\+> \ lineitem\n\+> \ where\n\+> \ o_orderkey = l_orderkey\n\+> \ and l_shipmode in ('AIR', 'RAIL')\n\+> \ and l_commitdate < l_receiptdate\n\+> \ and l_shipdate < l_commitdate\n\+> \ and l_receiptdate >= date '1994-01-01'\n\+> \ and l_receiptdate < date '1994-01-01' + interval '1' year\n\+> \ group by\n\+> \ l_shipmode\n\+> \ order by\n\+> \ l_shipmode")+> ,("Q13","\n\+> \ select\n\+> \ c_count,\n\+> \ count(*) as custdist\n\+> \ from\n\+> \ (\n\+> \ select\n\+> \ c_custkey,\n\+> \ count(o_orderkey)\n\+> \ from\n\+> \ customer left outer join orders on\n\+> \ c_custkey = o_custkey\n\+> \ and o_comment not like '%pending%requests%'\n\+> \ group by\n\+> \ c_custkey\n\+> \ ) as c_orders (c_custkey, c_count)\n\+> \ group by\n\+> \ c_count\n\+> \ order by\n\+> \ custdist desc,\n\+> \ c_count desc")+> ,("Q14","\n\+> \ select\n\+> \ 100.00 * sum(case\n\+> \ when p_type like 'PROMO%'\n\+> \ then l_extendedprice * (1 - l_discount)\n\+> \ else 0\n\+> \ end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue\n\+> \ from\n\+> \ lineitem,\n\+> \ part\n\+> \ where\n\+> \ l_partkey = p_partkey\n\+> \ and l_shipdate >= date '1994-12-01'\n\+> \ and l_shipdate < date '1994-12-01' + interval '1' month")+> ,("Q15","\n\+> \ /*create view revenue0 (supplier_no, total_revenue) as\n\+> \ select\n\+> \ l_suppkey,\n\+> \ sum(l_extendedprice * (1 - l_discount))\n\+> \ from\n\+> \ lineitem\n\+> \ where\n\+> \ l_shipdate >= date '1995-06-01'\n\+> \ and l_shipdate < date '1995-06-01' + interval '3' month\n\+> \ group by\n\+> \ l_suppkey;*/\n\+> \ with\n\+> \ revenue0 as\n\+> \ (select\n\+> \ l_suppkey as supplier_no,\n\+> \ sum(l_extendedprice * (1 - l_discount)) as total_revenue\n\+> \ from\n\+> \ lineitem\n\+> \ where\n\+> \ l_shipdate >= date '1995-06-01'\n\+> \ and l_shipdate < date '1995-06-01' + interval '3' month\n\+> \ group by\n\+> \ l_suppkey)\n\+> \ select\n\+> \ s_suppkey,\n\+> \ s_name,\n\+> \ s_address,\n\+> \ s_phone,\n\+> \ total_revenue\n\+> \ from\n\+> \ supplier,\n\+> \ revenue0\n\+> \ where\n\+> \ s_suppkey = supplier_no\n\+> \ and total_revenue = (\n\+> \ select\n\+> \ max(total_revenue)\n\+> \ from\n\+> \ revenue0\n\+> \ )\n\+> \ order by\n\+> \ s_suppkey")+> ,("Q16","\n\+> \ select\n\+> \ p_brand,\n\+> \ p_type,\n\+> \ p_size,\n\+> \ count(distinct ps_suppkey) as supplier_cnt\n\+> \ from\n\+> \ partsupp,\n\+> \ part\n\+> \ where\n\+> \ p_partkey = ps_partkey\n\+> \ and p_brand <> 'Brand#15'\n\+> \ and p_type not like 'MEDIUM BURNISHED%'\n\+> \ and p_size in (39, 26, 18, 45, 19, 1, 3, 9)\n\+> \ and ps_suppkey not in (\n\+> \ select\n\+> \ s_suppkey\n\+> \ from\n\+> \ supplier\n\+> \ where\n\+> \ s_comment like '%Customer%Complaints%'\n\+> \ )\n\+> \ group by\n\+> \ p_brand,\n\+> \ p_type,\n\+> \ p_size\n\+> \ order by\n\+> \ supplier_cnt desc,\n\+> \ p_brand,\n\+> \ p_type,\n\+> \ p_size")+> ,("Q17","\n\+> \ select\n\+> \ sum(l_extendedprice) / 7.0 as avg_yearly\n\+> \ from\n\+> \ lineitem,\n\+> \ part\n\+> \ where\n\+> \ p_partkey = l_partkey\n\+> \ and p_brand = 'Brand#52'\n\+> \ and p_container = 'JUMBO CAN'\n\+> \ and l_quantity < (\n\+> \ select\n\+> \ 0.2 * avg(l_quantity)\n\+> \ from\n\+> \ lineitem\n\+> \ where\n\+> \ l_partkey = p_partkey\n\+> \ )")+> ,("Q18","\n\+> \ select\n\+> \ c_name,\n\+> \ c_custkey,\n\+> \ o_orderkey,\n\+> \ o_orderdate,\n\+> \ o_totalprice,\n\+> \ sum(l_quantity)\n\+> \ from\n\+> \ customer,\n\+> \ orders,\n\+> \ lineitem\n\+> \ where\n\+> \ o_orderkey in (\n\+> \ select\n\+> \ l_orderkey\n\+> \ from\n\+> \ lineitem\n\+> \ group by\n\+> \ l_orderkey having\n\+> \ sum(l_quantity) > 313\n\+> \ )\n\+> \ and c_custkey = o_custkey\n\+> \ and o_orderkey = l_orderkey\n\+> \ group by\n\+> \ c_name,\n\+> \ c_custkey,\n\+> \ o_orderkey,\n\+> \ o_orderdate,\n\+> \ o_totalprice\n\+> \ order by\n\+> \ o_totalprice desc,\n\+> \ o_orderdate\n\+> \ fetch first 100 rows only")+> ,("Q19","\n\+> \ select\n\+> \ sum(l_extendedprice* (1 - l_discount)) as revenue\n\+> \ from\n\+> \ lineitem,\n\+> \ part\n\+> \ where\n\+> \ (\n\+> \ p_partkey = l_partkey\n\+> \ and p_brand = 'Brand#43'\n\+> \ and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')\n\+> \ and l_quantity >= 3 and l_quantity <= 3 + 10\n\+> \ and p_size between 1 and 5\n\+> \ and l_shipmode in ('AIR', 'AIR REG')\n\+> \ and l_shipinstruct = 'DELIVER IN PERSON'\n\+> \ )\n\+> \ or\n\+> \ (\n\+> \ p_partkey = l_partkey\n\+> \ and p_brand = 'Brand#25'\n\+> \ and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')\n\+> \ and l_quantity >= 10 and l_quantity <= 10 + 10\n\+> \ and p_size between 1 and 10\n\+> \ and l_shipmode in ('AIR', 'AIR REG')\n\+> \ and l_shipinstruct = 'DELIVER IN PERSON'\n\+> \ )\n\+> \ or\n\+> \ (\n\+> \ p_partkey = l_partkey\n\+> \ and p_brand = 'Brand#24'\n\+> \ and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')\n\+> \ and l_quantity >= 22 and l_quantity <= 22 + 10\n\+> \ and p_size between 1 and 15\n\+> \ and l_shipmode in ('AIR', 'AIR REG')\n\+> \ and l_shipinstruct = 'DELIVER IN PERSON'\n\+> \ )")+> ,("Q20","\n\+> \ select\n\+> \ s_name,\n\+> \ s_address\n\+> \ from\n\+> \ supplier,\n\+> \ nation\n\+> \ where\n\+> \ s_suppkey in (\n\+> \ select\n\+> \ ps_suppkey\n\+> \ from\n\+> \ partsupp\n\+> \ where\n\+> \ ps_partkey in (\n\+> \ select\n\+> \ p_partkey\n\+> \ from\n\+> \ part\n\+> \ where\n\+> \ p_name like 'lime%'\n\+> \ )\n\+> \ and ps_availqty > (\n\+> \ select\n\+> \ 0.5 * sum(l_quantity)\n\+> \ from\n\+> \ lineitem\n\+> \ where\n\+> \ l_partkey = ps_partkey\n\+> \ and l_suppkey = ps_suppkey\n\+> \ and l_shipdate >= date '1994-01-01'\n\+> \ and l_shipdate < date '1994-01-01' + interval '1' year\n\+> \ )\n\+> \ )\n\+> \ and s_nationkey = n_nationkey\n\+> \ and n_name = 'VIETNAM'\n\+> \ order by\n\+> \ s_name")+> ,("Q21","\n\+> \ select\n\+> \ s_name,\n\+> \ count(*) as numwait\n\+> \ from\n\+> \ supplier,\n\+> \ lineitem l1,\n\+> \ orders,\n\+> \ nation\n\+> \ where\n\+> \ s_suppkey = l1.l_suppkey\n\+> \ and o_orderkey = l1.l_orderkey\n\+> \ and o_orderstatus = 'F'\n\+> \ and l1.l_receiptdate > l1.l_commitdate\n\+> \ and exists (\n\+> \ select\n\+> \ *\n\+> \ from\n\+> \ lineitem l2\n\+> \ where\n\+> \ l2.l_orderkey = l1.l_orderkey\n\+> \ and l2.l_suppkey <> l1.l_suppkey\n\+> \ )\n\+> \ and not exists (\n\+> \ select\n\+> \ *\n\+> \ from\n\+> \ lineitem l3\n\+> \ where\n\+> \ l3.l_orderkey = l1.l_orderkey\n\+> \ and l3.l_suppkey <> l1.l_suppkey\n\+> \ and l3.l_receiptdate > l3.l_commitdate\n\+> \ )\n\+> \ and s_nationkey = n_nationkey\n\+> \ and n_name = 'INDIA'\n\+> \ group by\n\+> \ s_name\n\+> \ order by\n\+> \ numwait desc,\n\+> \ s_name\n\+> \ fetch first 100 rows only")+> ,("Q22","\n\+> \ select\n\+> \ cntrycode,\n\+> \ count(*) as numcust,\n\+> \ sum(c_acctbal) as totacctbal\n\+> \ from\n\+> \ (\n\+> \ select\n\+> \ substring(c_phone from 1 for 2) as cntrycode,\n\+> \ c_acctbal\n\+> \ from\n\+> \ customer\n\+> \ where\n\+> \ substring(c_phone from 1 for 2) in\n\+> \ ('41', '28', '39', '21', '24', '29', '44')\n\+> \ and c_acctbal > (\n\+> \ select\n\+> \ avg(c_acctbal)\n\+> \ from\n\+> \ customer\n\+> \ where\n\+> \ c_acctbal > 0.00\n\+> \ and substring(c_phone from 1 for 2) in\n\+> \ ('41', '28', '39', '21', '24', '29', '44')\n\+> \ )\n\+> \ and not exists (\n\+> \ select\n\+> \ *\n\+> \ from\n\+> \ orders\n\+> \ where\n\+> \ o_custkey = c_custkey\n\+> \ )\n\+> \ ) as custsale\n\+> \ group by\n\+> \ cntrycode\n\+> \ order by\n\+> \ cntrycode")+> ]
− tools/Tpch.lhs
@@ -1,675 +0,0 @@--test data for tpch queries---> {-# LANGUAGE OverloadedStrings #-}--> module Tpch (tpchQueries) where->--> tpchQueries :: [(String,String)]-> tpchQueries =-> [("Q1","\n\-> \select\n\-> \ l_returnflag,\n\-> \ l_linestatus,\n\-> \ sum(l_quantity) as sum_qty,\n\-> \ sum(l_extendedprice) as sum_base_price,\n\-> \ sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,\n\-> \ sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,\n\-> \ avg(l_quantity) as avg_qty,\n\-> \ avg(l_extendedprice) as avg_price,\n\-> \ avg(l_discount) as avg_disc,\n\-> \ count(*) as count_order\n\-> \from\n\-> \ lineitem\n\-> \where\n\-> \ l_shipdate <= date '1998-12-01' - interval '63' day (3)\n\-> \group by\n\-> \ l_returnflag,\n\-> \ l_linestatus\n\-> \order by\n\-> \ l_returnflag,\n\-> \ l_linestatus")-> ,("Q2","\n\-> \select\n\-> \ s_acctbal,\n\-> \ s_name,\n\-> \ n_name,\n\-> \ p_partkey,\n\-> \ p_mfgr,\n\-> \ s_address,\n\-> \ s_phone,\n\-> \ s_comment\n\-> \from\n\-> \ part,\n\-> \ supplier,\n\-> \ partsupp,\n\-> \ nation,\n\-> \ region\n\-> \where\n\-> \ p_partkey = ps_partkey\n\-> \ and s_suppkey = ps_suppkey\n\-> \ and p_size = 15\n\-> \ and p_type like '%BRASS'\n\-> \ and s_nationkey = n_nationkey\n\-> \ and n_regionkey = r_regionkey\n\-> \ and r_name = 'EUROPE'\n\-> \ and ps_supplycost = (\n\-> \ select\n\-> \ min(ps_supplycost)\n\-> \ from\n\-> \ partsupp,\n\-> \ supplier,\n\-> \ nation,\n\-> \ region\n\-> \ where\n\-> \ p_partkey = ps_partkey\n\-> \ and s_suppkey = ps_suppkey\n\-> \ and s_nationkey = n_nationkey\n\-> \ and n_regionkey = r_regionkey\n\-> \ and r_name = 'EUROPE'\n\-> \ )\n\-> \order by\n\-> \ s_acctbal desc,\n\-> \ n_name,\n\-> \ s_name,\n\-> \ p_partkey\n\-> \limit 100")-> ,("Q3","\n\-> \ select\n\-> \ l_orderkey,\n\-> \ sum(l_extendedprice * (1 - l_discount)) as revenue,\n\-> \ o_orderdate,\n\-> \ o_shippriority\n\-> \ from\n\-> \ customer,\n\-> \ orders,\n\-> \ lineitem\n\-> \ where\n\-> \ c_mktsegment = 'MACHINERY'\n\-> \ and c_custkey = o_custkey\n\-> \ and l_orderkey = o_orderkey\n\-> \ and o_orderdate < date '1995-03-21'\n\-> \ and l_shipdate > date '1995-03-21'\n\-> \ group by\n\-> \ l_orderkey,\n\-> \ o_orderdate,\n\-> \ o_shippriority\n\-> \ order by\n\-> \ revenue desc,\n\-> \ o_orderdate\n\-> \ limit 10")-> ,("Q4","\n\-> \ select\n\-> \ o_orderpriority,\n\-> \ count(*) as order_count\n\-> \ from\n\-> \ orders\n\-> \ where\n\-> \ o_orderdate >= date '1996-03-01'\n\-> \ and o_orderdate < date '1996-03-01' + interval '3' month\n\-> \ and exists (\n\-> \ select\n\-> \ *\n\-> \ from\n\-> \ lineitem\n\-> \ where\n\-> \ l_orderkey = o_orderkey\n\-> \ and l_commitdate < l_receiptdate\n\-> \ )\n\-> \ group by\n\-> \ o_orderpriority\n\-> \ order by\n\-> \ o_orderpriority")-> ,("Q5","\n\-> \ select\n\-> \ n_name,\n\-> \ sum(l_extendedprice * (1 - l_discount)) as revenue\n\-> \ from\n\-> \ customer,\n\-> \ orders,\n\-> \ lineitem,\n\-> \ supplier,\n\-> \ nation,\n\-> \ region\n\-> \ where\n\-> \ c_custkey = o_custkey\n\-> \ and l_orderkey = o_orderkey\n\-> \ and l_suppkey = s_suppkey\n\-> \ and c_nationkey = s_nationkey\n\-> \ and s_nationkey = n_nationkey\n\-> \ and n_regionkey = r_regionkey\n\-> \ and r_name = 'EUROPE'\n\-> \ and o_orderdate >= date '1997-01-01'\n\-> \ and o_orderdate < date '1997-01-01' + interval '1' year\n\-> \ group by\n\-> \ n_name\n\-> \ order by\n\-> \ revenue desc")-> ,("Q6","\n\-> \ select\n\-> \ sum(l_extendedprice * l_discount) as revenue\n\-> \ from\n\-> \ lineitem\n\-> \ where\n\-> \ l_shipdate >= date '1997-01-01'\n\-> \ and l_shipdate < date '1997-01-01' + interval '1' year\n\-> \ and l_discount between 0.07 - 0.01 and 0.07 + 0.01\n\-> \ and l_quantity < 24")-> ,("Q7","\n\-> \ select\n\-> \ supp_nation,\n\-> \ cust_nation,\n\-> \ l_year,\n\-> \ sum(volume) as revenue\n\-> \ from\n\-> \ (\n\-> \ select\n\-> \ n1.n_name as supp_nation,\n\-> \ n2.n_name as cust_nation,\n\-> \ extract(year from l_shipdate) as l_year,\n\-> \ l_extendedprice * (1 - l_discount) as volume\n\-> \ from\n\-> \ supplier,\n\-> \ lineitem,\n\-> \ orders,\n\-> \ customer,\n\-> \ nation n1,\n\-> \ nation n2\n\-> \ where\n\-> \ s_suppkey = l_suppkey\n\-> \ and o_orderkey = l_orderkey\n\-> \ and c_custkey = o_custkey\n\-> \ and s_nationkey = n1.n_nationkey\n\-> \ and c_nationkey = n2.n_nationkey\n\-> \ and (\n\-> \ (n1.n_name = 'PERU' and n2.n_name = 'IRAQ')\n\-> \ or (n1.n_name = 'IRAQ' and n2.n_name = 'PERU')\n\-> \ )\n\-> \ and l_shipdate between date '1995-01-01' and date '1996-12-31'\n\-> \ ) as shipping\n\-> \ group by\n\-> \ supp_nation,\n\-> \ cust_nation,\n\-> \ l_year\n\-> \ order by\n\-> \ supp_nation,\n\-> \ cust_nation,\n\-> \ l_year")-> ,("Q8","\n\-> \ select\n\-> \ o_year,\n\-> \ sum(case\n\-> \ when nation = 'IRAQ' then volume\n\-> \ else 0\n\-> \ end) / sum(volume) as mkt_share\n\-> \ from\n\-> \ (\n\-> \ select\n\-> \ extract(year from o_orderdate) as o_year,\n\-> \ l_extendedprice * (1 - l_discount) as volume,\n\-> \ n2.n_name as nation\n\-> \ from\n\-> \ part,\n\-> \ supplier,\n\-> \ lineitem,\n\-> \ orders,\n\-> \ customer,\n\-> \ nation n1,\n\-> \ nation n2,\n\-> \ region\n\-> \ where\n\-> \ p_partkey = l_partkey\n\-> \ and s_suppkey = l_suppkey\n\-> \ and l_orderkey = o_orderkey\n\-> \ and o_custkey = c_custkey\n\-> \ and c_nationkey = n1.n_nationkey\n\-> \ and n1.n_regionkey = r_regionkey\n\-> \ and r_name = 'MIDDLE EAST'\n\-> \ and s_nationkey = n2.n_nationkey\n\-> \ and o_orderdate between date '1995-01-01' and date '1996-12-31'\n\-> \ and p_type = 'STANDARD ANODIZED BRASS'\n\-> \ ) as all_nations\n\-> \ group by\n\-> \ o_year\n\-> \ order by\n\-> \ o_year")-> ,("Q9","\n\-> \ select\n\-> \ nation,\n\-> \ o_year,\n\-> \ sum(amount) as sum_profit\n\-> \ from\n\-> \ (\n\-> \ select\n\-> \ n_name as nation,\n\-> \ extract(year from o_orderdate) as o_year,\n\-> \ l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount\n\-> \ from\n\-> \ part,\n\-> \ supplier,\n\-> \ lineitem,\n\-> \ partsupp,\n\-> \ orders,\n\-> \ nation\n\-> \ where\n\-> \ s_suppkey = l_suppkey\n\-> \ and ps_suppkey = l_suppkey\n\-> \ and ps_partkey = l_partkey\n\-> \ and p_partkey = l_partkey\n\-> \ and o_orderkey = l_orderkey\n\-> \ and s_nationkey = n_nationkey\n\-> \ and p_name like '%antique%'\n\-> \ ) as profit\n\-> \ group by\n\-> \ nation,\n\-> \ o_year\n\-> \ order by\n\-> \ nation,\n\-> \ o_year desc")-> ,("Q10","\n\-> \ select\n\-> \ c_custkey,\n\-> \ c_name,\n\-> \ sum(l_extendedprice * (1 - l_discount)) as revenue,\n\-> \ c_acctbal,\n\-> \ n_name,\n\-> \ c_address,\n\-> \ c_phone,\n\-> \ c_comment\n\-> \ from\n\-> \ customer,\n\-> \ orders,\n\-> \ lineitem,\n\-> \ nation\n\-> \ where\n\-> \ c_custkey = o_custkey\n\-> \ and l_orderkey = o_orderkey\n\-> \ and o_orderdate >= date '1993-12-01'\n\-> \ and o_orderdate < date '1993-12-01' + interval '3' month\n\-> \ and l_returnflag = 'R'\n\-> \ and c_nationkey = n_nationkey\n\-> \ group by\n\-> \ c_custkey,\n\-> \ c_name,\n\-> \ c_acctbal,\n\-> \ c_phone,\n\-> \ n_name,\n\-> \ c_address,\n\-> \ c_comment\n\-> \ order by\n\-> \ revenue desc\n\-> \ limit 20")-> ,("Q11","\n\-> \ select\n\-> \ ps_partkey,\n\-> \ sum(ps_supplycost * ps_availqty) as value\n\-> \ from\n\-> \ partsupp,\n\-> \ supplier,\n\-> \ nation\n\-> \ where\n\-> \ ps_suppkey = s_suppkey\n\-> \ and s_nationkey = n_nationkey\n\-> \ and n_name = 'CHINA'\n\-> \ group by\n\-> \ ps_partkey having\n\-> \ sum(ps_supplycost * ps_availqty) > (\n\-> \ select\n\-> \ sum(ps_supplycost * ps_availqty) * 0.0001000000\n\-> \ from\n\-> \ partsupp,\n\-> \ supplier,\n\-> \ nation\n\-> \ where\n\-> \ ps_suppkey = s_suppkey\n\-> \ and s_nationkey = n_nationkey\n\-> \ and n_name = 'CHINA'\n\-> \ )\n\-> \ order by\n\-> \ value desc")-> ,("Q12","\n\-> \ select\n\-> \ l_shipmode,\n\-> \ sum(case\n\-> \ when o_orderpriority = '1-URGENT'\n\-> \ or o_orderpriority = '2-HIGH'\n\-> \ then 1\n\-> \ else 0\n\-> \ end) as high_line_count,\n\-> \ sum(case\n\-> \ when o_orderpriority <> '1-URGENT'\n\-> \ and o_orderpriority <> '2-HIGH'\n\-> \ then 1\n\-> \ else 0\n\-> \ end) as low_line_count\n\-> \ from\n\-> \ orders,\n\-> \ lineitem\n\-> \ where\n\-> \ o_orderkey = l_orderkey\n\-> \ and l_shipmode in ('AIR', 'RAIL')\n\-> \ and l_commitdate < l_receiptdate\n\-> \ and l_shipdate < l_commitdate\n\-> \ and l_receiptdate >= date '1994-01-01'\n\-> \ and l_receiptdate < date '1994-01-01' + interval '1' year\n\-> \ group by\n\-> \ l_shipmode\n\-> \ order by\n\-> \ l_shipmode")-> ,("Q13","\n\-> \ select\n\-> \ c_count,\n\-> \ count(*) as custdist\n\-> \ from\n\-> \ (\n\-> \ select\n\-> \ c_custkey,\n\-> \ count(o_orderkey)\n\-> \ from\n\-> \ customer left outer join orders on\n\-> \ c_custkey = o_custkey\n\-> \ and o_comment not like '%pending%requests%'\n\-> \ group by\n\-> \ c_custkey\n\-> \ ) as c_orders (c_custkey, c_count)\n\-> \ group by\n\-> \ c_count\n\-> \ order by\n\-> \ custdist desc,\n\-> \ c_count desc")-> ,("Q14","\n\-> \ select\n\-> \ 100.00 * sum(case\n\-> \ when p_type like 'PROMO%'\n\-> \ then l_extendedprice * (1 - l_discount)\n\-> \ else 0\n\-> \ end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue\n\-> \ from\n\-> \ lineitem,\n\-> \ part\n\-> \ where\n\-> \ l_partkey = p_partkey\n\-> \ and l_shipdate >= date '1994-12-01'\n\-> \ and l_shipdate < date '1994-12-01' + interval '1' month")-> ,("Q15","\n\-> \ /*create view revenue0 (supplier_no, total_revenue) as\n\-> \ select\n\-> \ l_suppkey,\n\-> \ sum(l_extendedprice * (1 - l_discount))\n\-> \ from\n\-> \ lineitem\n\-> \ where\n\-> \ l_shipdate >= date '1995-06-01'\n\-> \ and l_shipdate < date '1995-06-01' + interval '3' month\n\-> \ group by\n\-> \ l_suppkey;*/\n\-> \ with\n\-> \ revenue0 as\n\-> \ (select\n\-> \ l_suppkey as supplier_no,\n\-> \ sum(l_extendedprice * (1 - l_discount)) as total_revenue\n\-> \ from\n\-> \ lineitem\n\-> \ where\n\-> \ l_shipdate >= date '1995-06-01'\n\-> \ and l_shipdate < date '1995-06-01' + interval '3' month\n\-> \ group by\n\-> \ l_suppkey)\n\-> \ select\n\-> \ s_suppkey,\n\-> \ s_name,\n\-> \ s_address,\n\-> \ s_phone,\n\-> \ total_revenue\n\-> \ from\n\-> \ supplier,\n\-> \ revenue0\n\-> \ where\n\-> \ s_suppkey = supplier_no\n\-> \ and total_revenue = (\n\-> \ select\n\-> \ max(total_revenue)\n\-> \ from\n\-> \ revenue0\n\-> \ )\n\-> \ order by\n\-> \ s_suppkey")-> ,("Q16","\n\-> \ select\n\-> \ p_brand,\n\-> \ p_type,\n\-> \ p_size,\n\-> \ count(distinct ps_suppkey) as supplier_cnt\n\-> \ from\n\-> \ partsupp,\n\-> \ part\n\-> \ where\n\-> \ p_partkey = ps_partkey\n\-> \ and p_brand <> 'Brand#15'\n\-> \ and p_type not like 'MEDIUM BURNISHED%'\n\-> \ and p_size in (39, 26, 18, 45, 19, 1, 3, 9)\n\-> \ and ps_suppkey not in (\n\-> \ select\n\-> \ s_suppkey\n\-> \ from\n\-> \ supplier\n\-> \ where\n\-> \ s_comment like '%Customer%Complaints%'\n\-> \ )\n\-> \ group by\n\-> \ p_brand,\n\-> \ p_type,\n\-> \ p_size\n\-> \ order by\n\-> \ supplier_cnt desc,\n\-> \ p_brand,\n\-> \ p_type,\n\-> \ p_size")-> ,("Q17","\n\-> \ select\n\-> \ sum(l_extendedprice) / 7.0 as avg_yearly\n\-> \ from\n\-> \ lineitem,\n\-> \ part\n\-> \ where\n\-> \ p_partkey = l_partkey\n\-> \ and p_brand = 'Brand#52'\n\-> \ and p_container = 'JUMBO CAN'\n\-> \ and l_quantity < (\n\-> \ select\n\-> \ 0.2 * avg(l_quantity)\n\-> \ from\n\-> \ lineitem\n\-> \ where\n\-> \ l_partkey = p_partkey\n\-> \ )")-> ,("Q18","\n\-> \ select\n\-> \ c_name,\n\-> \ c_custkey,\n\-> \ o_orderkey,\n\-> \ o_orderdate,\n\-> \ o_totalprice,\n\-> \ sum(l_quantity)\n\-> \ from\n\-> \ customer,\n\-> \ orders,\n\-> \ lineitem\n\-> \ where\n\-> \ o_orderkey in (\n\-> \ select\n\-> \ l_orderkey\n\-> \ from\n\-> \ lineitem\n\-> \ group by\n\-> \ l_orderkey having\n\-> \ sum(l_quantity) > 313\n\-> \ )\n\-> \ and c_custkey = o_custkey\n\-> \ and o_orderkey = l_orderkey\n\-> \ group by\n\-> \ c_name,\n\-> \ c_custkey,\n\-> \ o_orderkey,\n\-> \ o_orderdate,\n\-> \ o_totalprice\n\-> \ order by\n\-> \ o_totalprice desc,\n\-> \ o_orderdate\n\-> \ limit 100")-> ,("Q19","\n\-> \ select\n\-> \ sum(l_extendedprice* (1 - l_discount)) as revenue\n\-> \ from\n\-> \ lineitem,\n\-> \ part\n\-> \ where\n\-> \ (\n\-> \ p_partkey = l_partkey\n\-> \ and p_brand = 'Brand#43'\n\-> \ and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')\n\-> \ and l_quantity >= 3 and l_quantity <= 3 + 10\n\-> \ and p_size between 1 and 5\n\-> \ and l_shipmode in ('AIR', 'AIR REG')\n\-> \ and l_shipinstruct = 'DELIVER IN PERSON'\n\-> \ )\n\-> \ or\n\-> \ (\n\-> \ p_partkey = l_partkey\n\-> \ and p_brand = 'Brand#25'\n\-> \ and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')\n\-> \ and l_quantity >= 10 and l_quantity <= 10 + 10\n\-> \ and p_size between 1 and 10\n\-> \ and l_shipmode in ('AIR', 'AIR REG')\n\-> \ and l_shipinstruct = 'DELIVER IN PERSON'\n\-> \ )\n\-> \ or\n\-> \ (\n\-> \ p_partkey = l_partkey\n\-> \ and p_brand = 'Brand#24'\n\-> \ and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')\n\-> \ and l_quantity >= 22 and l_quantity <= 22 + 10\n\-> \ and p_size between 1 and 15\n\-> \ and l_shipmode in ('AIR', 'AIR REG')\n\-> \ and l_shipinstruct = 'DELIVER IN PERSON'\n\-> \ )")-> ,("Q20","\n\-> \ select\n\-> \ s_name,\n\-> \ s_address\n\-> \ from\n\-> \ supplier,\n\-> \ nation\n\-> \ where\n\-> \ s_suppkey in (\n\-> \ select\n\-> \ ps_suppkey\n\-> \ from\n\-> \ partsupp\n\-> \ where\n\-> \ ps_partkey in (\n\-> \ select\n\-> \ p_partkey\n\-> \ from\n\-> \ part\n\-> \ where\n\-> \ p_name like 'lime%'\n\-> \ )\n\-> \ and ps_availqty > (\n\-> \ select\n\-> \ 0.5 * sum(l_quantity)\n\-> \ from\n\-> \ lineitem\n\-> \ where\n\-> \ l_partkey = ps_partkey\n\-> \ and l_suppkey = ps_suppkey\n\-> \ and l_shipdate >= date '1994-01-01'\n\-> \ and l_shipdate < date '1994-01-01' + interval '1' year\n\-> \ )\n\-> \ )\n\-> \ and s_nationkey = n_nationkey\n\-> \ and n_name = 'VIETNAM'\n\-> \ order by\n\-> \ s_name")-> ,("Q21","\n\-> \ select\n\-> \ s_name,\n\-> \ count(*) as numwait\n\-> \ from\n\-> \ supplier,\n\-> \ lineitem l1,\n\-> \ orders,\n\-> \ nation\n\-> \ where\n\-> \ s_suppkey = l1.l_suppkey\n\-> \ and o_orderkey = l1.l_orderkey\n\-> \ and o_orderstatus = 'F'\n\-> \ and l1.l_receiptdate > l1.l_commitdate\n\-> \ and exists (\n\-> \ select\n\-> \ *\n\-> \ from\n\-> \ lineitem l2\n\-> \ where\n\-> \ l2.l_orderkey = l1.l_orderkey\n\-> \ and l2.l_suppkey <> l1.l_suppkey\n\-> \ )\n\-> \ and not exists (\n\-> \ select\n\-> \ *\n\-> \ from\n\-> \ lineitem l3\n\-> \ where\n\-> \ l3.l_orderkey = l1.l_orderkey\n\-> \ and l3.l_suppkey <> l1.l_suppkey\n\-> \ and l3.l_receiptdate > l3.l_commitdate\n\-> \ )\n\-> \ and s_nationkey = n_nationkey\n\-> \ and n_name = 'INDIA'\n\-> \ group by\n\-> \ s_name\n\-> \ order by\n\-> \ numwait desc,\n\-> \ s_name\n\-> \ limit 100")-> ,("Q22","\n\-> \ select\n\-> \ cntrycode,\n\-> \ count(*) as numcust,\n\-> \ sum(c_acctbal) as totacctbal\n\-> \ from\n\-> \ (\n\-> \ select\n\-> \ substring(c_phone from 1 for 2) as cntrycode,\n\-> \ c_acctbal\n\-> \ from\n\-> \ customer\n\-> \ where\n\-> \ substring(c_phone from 1 for 2) in\n\-> \ ('41', '28', '39', '21', '24', '29', '44')\n\-> \ and c_acctbal > (\n\-> \ select\n\-> \ avg(c_acctbal)\n\-> \ from\n\-> \ customer\n\-> \ where\n\-> \ c_acctbal > 0.00\n\-> \ and substring(c_phone from 1 for 2) in\n\-> \ ('41', '28', '39', '21', '24', '29', '44')\n\-> \ )\n\-> \ and not exists (\n\-> \ select\n\-> \ *\n\-> \ from\n\-> \ orders\n\-> \ where\n\-> \ o_custkey = c_custkey\n\-> \ )\n\-> \ ) as custsale\n\-> \ group by\n\-> \ cntrycode\n\-> \ order by\n\-> \ cntrycode")-> ]