| Safe Haskell | Safe-Inferred |
|---|---|
| Language | Haskell2010 |
Torch.Language.SpiderSQL
Description
This module contains bits and pieces to work with the Spider SQL language.
TODO:
* don't accept reserved keywords in names without quoting
* aliases have to be defined in scope, otherwise fall back to table id
* don't define an alias twice in the same scope
* optionally (?) use the schema to constrain column and table names
* test the parser(s) on more examples
* pretty printing
* random generation of SpiderSQL values
Synopsis
- data SpiderSQL = SpiderSQL {}
- data Select
- = Select [Agg]
- | SelectDistinct [Agg]
- data From = From {
- fromTableUnits :: [TableUnit]
- fromCond :: Maybe Cond
- data Cond
- data ColUnit
- data OrderBy = OrderBy OrderByOrder [ValUnit]
- data OrderByOrder
- data Agg = Agg AggType ValUnit
- data TableUnit
- data ValUnit
- data Val
- data AggType
- data ColumnId
- newtype TableId = TableId String
- newtype Alias = Alias String
- isKeyword :: CharParsing m => String -> m String
- isSelect :: CharParsing m => m String
- isDistinct :: CharParsing m => m String
- isStar :: CharParsing m => m String
- isComma :: CharParsing m => m String
- isDot :: CharParsing m => m String
- isSemicolon :: CharParsing m => m String
- isEq :: CharParsing m => m String
- isGt :: CharParsing m => m String
- isLt :: CharParsing m => m String
- isGe :: CharParsing m => m String
- isLe :: CharParsing m => m String
- isNe :: CharParsing m => m String
- isIn :: CharParsing m => m String
- isLike :: CharParsing m => m String
- isBetween :: CharParsing m => m String
- isAnd :: CharParsing m => m String
- isOr :: CharParsing m => m String
- isNot :: CharParsing m => m String
- isMinus :: CharParsing m => m String
- isPlus :: CharParsing m => m String
- isTimes :: CharParsing m => m String
- isDivide :: CharParsing m => m String
- isMax :: CharParsing m => m String
- isMin :: CharParsing m => m String
- isCount :: CharParsing m => m String
- isSum :: CharParsing m => m String
- isAvg :: CharParsing m => m String
- isFrom :: CharParsing m => m String
- isJoin :: CharParsing m => m String
- isAs :: CharParsing m => m String
- isOn :: CharParsing m => m String
- isWhere :: CharParsing m => m String
- isGroupBy :: CharParsing m => m String
- isOrderBy :: CharParsing m => m String
- isAsc :: CharParsing m => m String
- isDesc :: CharParsing m => m String
- isHaving :: CharParsing m => m String
- isLimit :: CharParsing m => m String
- isIntersect :: CharParsing m => m String
- isExcept :: CharParsing m => m String
- isUnion :: CharParsing m => m String
- betweenParentheses :: CharParsing m => m a -> m a
- betweenOptionalParentheses :: CharParsing m => m a -> m a
- select :: (TokenParsing m, Monad m) => m Select
- agg :: (TokenParsing m, Monad m) => m Agg
- aggType :: CharParsing m => m AggType
- valUnit :: (TokenParsing m, Monad m) => m ValUnit
- colUnit :: (TokenParsing m, Monad m) => m ColUnit
- tableId :: CharParsing m => m TableId
- alias :: CharParsing m => m Alias
- columnId :: CharParsing m => m ColumnId
- tableUnitAlias :: TableUnit -> Maybe Alias
- tableUnitTableId :: TableUnit -> Maybe TableId
- condAliases :: Cond -> [Alias]
- condTableIds :: Cond -> [TableId]
- valUnitAliases :: ValUnit -> [Alias]
- valUnitTableIds :: ValUnit -> [TableId]
- colUnitAlias :: ColUnit -> Maybe Alias
- colUnitTableId :: ColUnit -> Maybe TableId
- valAlias :: Val -> Maybe Alias
- valTableId :: Val -> Maybe TableId
- from :: forall m. (TokenParsing m, Monad m) => m From
- tableUnit :: (TokenParsing m, Monad m) => m TableUnit
- cond :: (TokenParsing m, Monad m) => m Cond
- val :: (TokenParsing m, Monad m) => m Val
- quotedString :: CharParsing m => m String
- whereCond :: (TokenParsing m, Monad m) => m Cond
- groupBy :: (TokenParsing m, Monad m) => m [ColUnit]
- orderBy :: (TokenParsing m, Monad m) => m OrderBy
- havingCond :: (TokenParsing m, Monad m) => m Cond
- limit :: (TokenParsing m, Monad m) => m Int
- spiderSQL :: (TokenParsing m, Monad m) => m SpiderSQL
- name :: CharParsing m => m String
Documentation
Constructors
| SpiderSQL | |
Fields
| |
Constructors
| And Cond Cond | |
| Or Cond Cond | |
| Not Cond | |
| Between ValUnit Val Val | |
| Eq ValUnit Val | |
| Gt ValUnit Val | |
| Lt ValUnit Val | |
| Ge ValUnit Val | |
| Le ValUnit Val | |
| Ne ValUnit Val | |
| In ValUnit Val | |
| Like ValUnit Val |
Constructors
| ColUnit | |
Fields
| |
| DistinctColUnit | |
Fields | |
data OrderByOrder Source #
Instances
| Show OrderByOrder Source # | |
Defined in Torch.Language.SpiderSQL | |
| Eq OrderByOrder Source # | |
Defined in Torch.Language.SpiderSQL Methods (==) :: OrderByOrder -> OrderByOrder -> Bool Source # (/=) :: OrderByOrder -> OrderByOrder -> Bool Source # | |
Instances
Constructors
| Column ColUnit | |
| Minus ColUnit ColUnit | |
| Plus ColUnit ColUnit | |
| Times ColUnit ColUnit | |
| Divide ColUnit ColUnit |
Instances
isSelect :: CharParsing m => m String Source #
isDistinct :: CharParsing m => m String Source #
isStar :: CharParsing m => m String Source #
isComma :: CharParsing m => m String Source #
isDot :: CharParsing m => m String Source #
isSemicolon :: CharParsing m => m String Source #
isEq :: CharParsing m => m String Source #
isGt :: CharParsing m => m String Source #
isLt :: CharParsing m => m String Source #
isGe :: CharParsing m => m String Source #
isLe :: CharParsing m => m String Source #
isNe :: CharParsing m => m String Source #
isIn :: CharParsing m => m String Source #
isLike :: CharParsing m => m String Source #
isBetween :: CharParsing m => m String Source #
isAnd :: CharParsing m => m String Source #
isOr :: CharParsing m => m String Source #
isNot :: CharParsing m => m String Source #
isMinus :: CharParsing m => m String Source #
isPlus :: CharParsing m => m String Source #
isTimes :: CharParsing m => m String Source #
isDivide :: CharParsing m => m String Source #
isMax :: CharParsing m => m String Source #
isMin :: CharParsing m => m String Source #
isCount :: CharParsing m => m String Source #
isSum :: CharParsing m => m String Source #
isAvg :: CharParsing m => m String Source #
isFrom :: CharParsing m => m String Source #
isJoin :: CharParsing m => m String Source #
isAs :: CharParsing m => m String Source #
isOn :: CharParsing m => m String Source #
isWhere :: CharParsing m => m String Source #
isGroupBy :: CharParsing m => m String Source #
isOrderBy :: CharParsing m => m String Source #
isAsc :: CharParsing m => m String Source #
isDesc :: CharParsing m => m String Source #
isHaving :: CharParsing m => m String Source #
isLimit :: CharParsing m => m String Source #
isIntersect :: CharParsing m => m String Source #
isExcept :: CharParsing m => m String Source #
isUnion :: CharParsing m => m String Source #
betweenParentheses :: CharParsing m => m a -> m a Source #
betweenOptionalParentheses :: CharParsing m => m a -> m a Source #
select :: (TokenParsing m, Monad m) => m Select Source #
Select parser
>>>head $ parseString @[] select "select count table.*"(Select [Agg Count (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "table")), colUnitColId = Star}))],"")
>>>head $ parseString @[] select "SELECT COUNT (DISTINCT t5.title)"(Select [Agg Count (Column (DistinctColUnit {distinctColUnitAggId = NoneAggOp, distinctColUnitTable = Just (Left (Alias "t5")), distinctColUnitColdId = ColumnId "title"}))],"")
agg :: (TokenParsing m, Monad m) => m Agg Source #
Agg parser.
>>>head $ parseString @[] agg "count table.id"(Agg Count (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "table")), colUnitColId = ColumnId "id"})),"")
aggType :: CharParsing m => m AggType Source #
AggType parser.
>>>head $ parseString @[] aggType ""(NoneAggOp,"")
valUnit :: (TokenParsing m, Monad m) => m ValUnit Source #
ValUnit parser.
>>>head $ parseString @[] valUnit "t1.stadium_id"(Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "stadium_id"}),"")
>>>head . filter (null . snd) $ parseString @[] valUnit "t1.stadium_length * t1.stadium_width"(Times (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "stadium_length"}) (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "stadium_width"}),"")
colUnit :: (TokenParsing m, Monad m) => m ColUnit Source #
ColUnit parser.
>>>head $ parseString @[] colUnit "count ( distinct my_table.* )"(DistinctColUnit {distinctColUnitAggId = Count, distinctColUnitTable = Just (Left (Alias "my_table")), distinctColUnitColdId = Star},"")
columnId :: CharParsing m => m ColumnId Source #
ColumnId parser.
>>>parseString @[] columnId "*"[(Star,"")]
>>>parseString @[] columnId "c"[(ColumnId "c","")]
condAliases :: Cond -> [Alias] Source #
condTableIds :: Cond -> [TableId] Source #
valUnitAliases :: ValUnit -> [Alias] Source #
valUnitTableIds :: ValUnit -> [TableId] Source #
from :: forall m. (TokenParsing m, Monad m) => m From Source #
From parser.
>>>head $ parseString @[] from "FROM people AS t1 JOIN pets AS t2 ON t1.pet_id = t2.pet_id"(From {fromTableUnits = [Table (TableId "people") (Just (Alias "t1")),Table (TableId "pets") (Just (Alias "t2"))], fromCond = Just (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "pet_id"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t2")), colUnitColId = ColumnId "pet_id"})))},"")
>>>head $ parseString @[] from "FROM organization AS t3 JOIN author AS t1 ON t3.oid = t1.oid JOIN writes AS t4 ON t4.aid = t1.aid JOIN publication AS t5 ON t4.pid = t5.pid JOIN conference AS t2 ON t5.cid = t2.cid"(From {fromTableUnits = [Table (TableId "organization") (Just (Alias "t3")),Table (TableId "author") (Just (Alias "t1")),Table (TableId "writes") (Just (Alias "t4")),Table (TableId "publication") (Just (Alias "t5")),Table (TableId "conference") (Just (Alias "t2"))], fromCond = Just (And (And (And (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t3")), colUnitColId = ColumnId "oid"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "oid"}))) (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t4")), colUnitColId = ColumnId "aid"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "aid"})))) (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t4")), colUnitColId = ColumnId "pid"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t5")), colUnitColId = ColumnId "pid"})))) (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t5")), colUnitColId = ColumnId "cid"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t2")), colUnitColId = ColumnId "cid"}))))},"")
tableUnit :: (TokenParsing m, Monad m) => m TableUnit Source #
TableUnit parser.
>>>head $ parseString @[] tableUnit "people as t1"(Table (TableId "people") (Just (Alias "t1")),"")
cond :: (TokenParsing m, Monad m) => m Cond Source #
Cond parser.
>>>head $ parseString @[] cond "t1.stadium_id = t2.stadium_id"(Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "stadium_id"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t2")), colUnitColId = ColumnId "stadium_id"})),"")
>>>head $ parseString @[] (cond <* isToken ';') "t2.name = \"VLDB\" AND t3.name = \"University of Michigan\";"(And (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t2")), colUnitColId = ColumnId "name"})) (ValString "VLDB")) (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t3")), colUnitColId = ColumnId "name"})) (ValString "University of Michigan")),"")
val :: (TokenParsing m, Monad m) => m Val Source #
Val parser.
>>>head $ parseString @[] val "count t1.stadium_id"(ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Nothing, colUnitColId = ColumnId "count"})," t1.stadium_id")
>>>head $ parseString @[] val "(select *)"(ValSQL (SpiderSQL {spiderSQLSelect = Select [Agg NoneAggOp (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Nothing, colUnitColId = Star}))], spiderSQLFrom = From {fromTableUnits = [], fromCond = Nothing}, spiderSQLWhere = Nothing, spiderSQLGroupBy = [], spiderSQLOrderBy = Nothing, spiderSQLHaving = Nothing, spiderSQLLimit = Nothing, spiderSQLIntersect = Nothing, spiderSQLExcept = Nothing, spiderSQLUnion = Nothing}),"")
quotedString :: CharParsing m => m String Source #
Parser for quoted strings.
>>>head $ parseString @[] quotedString "\"hello world\""("hello world","")
whereCond :: (TokenParsing m, Monad m) => m Cond Source #
Parser for where clauses.
>>>head $ parseString @[] whereCond "where t1.id = t2.id"(Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "id"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t2")), colUnitColId = ColumnId "id"})),"")
groupBy :: (TokenParsing m, Monad m) => m [ColUnit] Source #
Parser for group-by clauses.
>>>head $ parseString @[] groupBy "group by count t1.id, t2.id"([ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Nothing, colUnitColId = ColumnId "count"}]," t1.id, t2.id")
orderBy :: (TokenParsing m, Monad m) => m OrderBy Source #
OrderBy Parser.
>>>head . filter (null . snd) $ parseString @[] orderBy "order by t1.stadium_id, t2.pet_id desc"(OrderBy Desc [Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "stadium_id"}),Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t2")), colUnitColId = ColumnId "pet_id"})],"")
havingCond :: (TokenParsing m, Monad m) => m Cond Source #
Parser for having clauses.
>>>head $ parseString @[] havingCond "having count(t1.customer_id) = 10"(Eq (Column (ColUnit {colUnitAggId = Count, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "customer_id"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Nothing, colUnitColId = ColumnId "10"})),"")
limit :: (TokenParsing m, Monad m) => m Int Source #
Parser for limit clauses.
>>>head $ parseString @[] limit "limit 10"(10,"")
spiderSQL :: (TokenParsing m, Monad m) => m SpiderSQL Source #
SpiderSQL parser.
>>>head $ parseString @[] (spiderSQL <* spaces <* isSemicolon) "select * ;"(SpiderSQL {spiderSQLSelect = Select [Agg NoneAggOp (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Nothing, colUnitColId = Star}))], spiderSQLFrom = From {fromTableUnits = [], fromCond = Nothing}, spiderSQLWhere = Nothing, spiderSQLGroupBy = [], spiderSQLOrderBy = Nothing, spiderSQLHaving = Nothing, spiderSQLLimit = Nothing, spiderSQLIntersect = Nothing, spiderSQLExcept = Nothing, spiderSQLUnion = Nothing},"")
>>>head $ parseString @[] (spiderSQL <* spaces <* isSemicolon) "select * from concert;"(SpiderSQL {spiderSQLSelect = Select [Agg NoneAggOp (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Nothing, colUnitColId = Star}))], spiderSQLFrom = From {fromTableUnits = [Table (TableId "concert") Nothing], fromCond = Nothing}, spiderSQLWhere = Nothing, spiderSQLGroupBy = [], spiderSQLOrderBy = Nothing, spiderSQLHaving = Nothing, spiderSQLLimit = Nothing, spiderSQLIntersect = Nothing, spiderSQLExcept = Nothing, spiderSQLUnion = Nothing},"")
>>>head $ parseString @[] (spiderSQL <* spaces <* isSemicolon) "select T2.name, count(*) from concert as t1 join stadium as t2 on t1.stadium_id = t2.stadium_id group by t1.stadium_id;"(SpiderSQL {spiderSQLSelect = Select [Agg NoneAggOp (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "T2")), colUnitColId = ColumnId "name"})),Agg NoneAggOp (Column (ColUnit {colUnitAggId = Count, colUnitTable = Nothing, colUnitColId = Star}))], spiderSQLFrom = From {fromTableUnits = [Table (TableId "concert") (Just (Alias "t1")),Table (TableId "stadium") (Just (Alias "t2"))], fromCond = Just (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "stadium_id"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t2")), colUnitColId = ColumnId "stadium_id"})))}, spiderSQLWhere = Nothing, spiderSQLGroupBy = [ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "stadium_id"}], spiderSQLOrderBy = Nothing, spiderSQLHaving = Nothing, spiderSQLLimit = Nothing, spiderSQLIntersect = Nothing, spiderSQLExcept = Nothing, spiderSQLUnion = Nothing},"")
>>>head $ parseString @[] (spiderSQL <* spaces <* isSemicolon) "SELECT COUNT ( DISTINCT t5.title ) FROM organization AS t3 JOIN author AS t1 ON t3.oid = t1.oid JOIN writes AS t4 ON t4.aid = t1.aid JOIN publication AS t5 ON t4.pid = t5.pid JOIN conference AS t2 ON t5.cid = t2.cid WHERE t2.name = \"VLDB\" AND t3.name = \"University of Michigan\";"(SpiderSQL {spiderSQLSelect = Select [Agg Count (Column (DistinctColUnit {distinctColUnitAggId = NoneAggOp, distinctColUnitTable = Just (Left (Alias "t5")), distinctColUnitColdId = ColumnId "title"}))], spiderSQLFrom = From {fromTableUnits = [Table (TableId "organization") (Just (Alias "t3")),Table (TableId "author") (Just (Alias "t1")),Table (TableId "writes") (Just (Alias "t4")),Table (TableId "publication") (Just (Alias "t5")),Table (TableId "conference") (Just (Alias "t2"))], fromCond = Just (And (And (And (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t3")), colUnitColId = ColumnId "oid"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "oid"}))) (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t4")), colUnitColId = ColumnId "aid"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t1")), colUnitColId = ColumnId "aid"})))) (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t4")), colUnitColId = ColumnId "pid"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t5")), colUnitColId = ColumnId "pid"})))) (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t5")), colUnitColId = ColumnId "cid"})) (ValColUnit (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t2")), colUnitColId = ColumnId "cid"}))))}, spiderSQLWhere = Just (And (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t2")), colUnitColId = ColumnId "name"})) (ValString "VLDB")) (Eq (Column (ColUnit {colUnitAggId = NoneAggOp, colUnitTable = Just (Left (Alias "t3")), colUnitColId = ColumnId "name"})) (ValString "University of Michigan"))), spiderSQLGroupBy = [], spiderSQLOrderBy = Nothing, spiderSQLHaving = Nothing, spiderSQLLimit = Nothing, spiderSQLIntersect = Nothing, spiderSQLExcept = Nothing, spiderSQLUnion = Nothing},"")
name :: CharParsing m => m String Source #
Auxiliary parser for table names, column names, and aliases.