serviette 0.1.0.4 → 0.1.0.5
raw patch · 4 files changed
+160/−62 lines, 4 filesdep +text-show
Dependencies added: text-show
Files
- Data/ApiDataTypes.hs +48/−34
- Data/Serviette.hs +102/−18
- README.md +6/−7
- serviette.cabal +4/−3
Data/ApiDataTypes.hs view
@@ -16,7 +16,7 @@ -- | Represents db table name data TableName = TableName Text- deriving (Show, Generic)+ deriving (Show, Generic, Eq) -- | Represents db table column name data ColumnName = ColumnName Text@@ -29,6 +29,7 @@ -- | Represents the main action for the sql query (SELECT,INSERT, UPDATE, DELETE) data Action = Action Text deriving (Show, Generic)+ -- | Represents return format (for now this is only raw sql string) data Format = Format { getFormat :: Int@@ -37,9 +38,16 @@ -- | Represents Date field value type DateVal = Text --- | Represents field value which can be integer, string or date+-- | Represents field value which can be integer, text or date data FieldValue = IntField Int | TextField Text | DateField DateVal deriving (Show, Generic) +-- | Represents set fields for the sql insert query+data SetField = SetField+ { columnName :: ColumnName+ , setFieldValue :: FieldValue+ } deriving (Show, Generic)++ -- | Represents join table for the sql query data JoinTable = JoinTable { tablename :: TableName@@ -62,73 +70,79 @@ { format :: Int , action :: Action , selectName :: TableName- , joinTables :: [JoinTable]- , whereCondition :: [WhereCondition]+ , set :: Maybe [SetField]+ , joinTables :: Maybe [JoinTable]+ , whereCondition :: Maybe [WhereCondition] } deriving (Show, Generic) -- | Represents type that is the result of the json "computation" data SqlResultQuery = SqlResultQuery { getAction :: Action , getSelectTable :: TableName- , getJoins :: [JoinTable]- , getWhereCondition :: [WhereCondition]+ , getSetFields :: Maybe [SetField]+ , getJoins :: Maybe [JoinTable]+ , getWhereCondition :: Maybe [WhereCondition] } deriving (Show, Generic) +data SqlResponse = SqlResponse+ { response :: Text+ , errors :: Text+ , warnings :: Text+ } deriving (Show, Generic) --- | Instances+-- | Aeson Instances --- | Json instance for TableName instance FromJSON TableName---- | Json instance for TableName instance ToJSON TableName --- | aeson instance for Action instance FromJSON Action---- | aeson instance for Action instance ToJSON Action --- | aeson instance for Operator instance FromJSON Operator---- | aeson instance for Operator instance ToJSON Operator --- | aeson instance for JoinTable-instance FromJSON JoinTable+instance ToJSON SetField+instance FromJSON SetField where+ parseJSON (Object v)+ = SetField+ <$> v .: "columnName"+ <*> ( IntField <$> (v .: "setFieldValue")+ <|> TextField <$> (v .: "setFieldValue")+ <|> DateField <$> (v .: "setFieldValue")+ )+ parseJSON _ = mzero --- | aeson instance for JoinTable++instance FromJSON JoinTable instance ToJSON JoinTable --- | aeson instance for ColumnName instance FromJSON ColumnName---- | aeson instance for ColumnName instance ToJSON ColumnName --- | aeson instance for SqlQuery-instance FromJSON SqlQuery+-- instance FromJSON SqlQuery+instance FromJSON SqlQuery where+ parseJSON (Object v)+ = SqlQuery+ <$> v .: "format"+ <*> v .: "action"+ <*> v .: "selectName"+ <*> v .:? "set"+ <*> v .:? "joinTables"+ <*> v .:? "whereCondition"+ parseJSON _ = mzero --- | aeson instance for SqlQuery instance ToJSON SqlQuery --- | aeson instance for SqlResultQuery instance FromJSON SqlResultQuery---- | aeson instance for SqlResultQuery instance ToJSON SqlResultQuery --- | aeson instance for FieldValue-instance FromJSON FieldValue+instance FromJSON SqlResponse+instance ToJSON SqlResponse --- | aeson instance for FieldValue+instance FromJSON FieldValue instance ToJSON FieldValue --- | aeson instance for WhereCondition instance ToJSON WhereCondition---- | aeson instance for WhereCondition instance FromJSON WhereCondition where parseJSON (Object v) = WhereCondition
Data/Serviette.hs view
@@ -4,74 +4,158 @@ -- next version will implement database query result in the json format -- as well as errors in the json structure if any -module Data.Serviette (SqlQuery, SqlResultQuery, rawSqlStr) where +module Data.Serviette (rawSqlStr) where+ import Data.ApiDataTypes import Data.Text hiding (concat, foldl, map)+import Data.Aeson+import TextShow+import Data.Maybe+import Data.ByteString.Lazy hiding (append, foldl) --- | Various Getters- -- | Extracts the action and appends space extractAction :: Action -> Text-extractAction (Action t) = append t " "+extractAction (Action t) =+ if t == "SELECT"+ then append t " "+ else if t == "DELETE"+ then append t " FROM "+ else if t == "INSERT"+ then append t " INTO "+ else error "Action parameter id wrong" + -- | Extracts table name to Text extractTableName :: TableName -> Text extractTableName (TableName t) = t + -- | Extracts table column name to Text extractColumnName :: ColumnName -> Text extractColumnName (ColumnName t) = t + -- | Extracts Operator to Text extractOperator :: Operator -> Text extractOperator (Operator t) = t + -- | Fetches the Action from SqlQuery getActionArg :: SqlQuery -> Action getActionArg q = action q + -- | Gets the main table that Action is performed on getSelectTableArg :: SqlQuery -> TableName getSelectTableArg q = selectName q ++-- | Retrieves the set list from the SqlQuery+getSetFieldsArg :: SqlQuery -> Maybe [SetField]+getSetFieldsArg q = set q++ -- | Retrieves the join list from the SqlQuery-getJoinTableArg :: SqlQuery -> [JoinTable]+getJoinTableArg :: SqlQuery -> Maybe [JoinTable] getJoinTableArg q = joinTables q + -- | Gets the where condition list-getWhereConditionArg :: SqlQuery -> [WhereCondition]+getWhereConditionArg :: SqlQuery -> Maybe [WhereCondition] getWhereConditionArg q = whereCondition q + -- | Retrieves the result Format getFormatArg :: SqlQuery -> Int getFormatArg q = getFormat $ Format $ format q + -- | Formats the join table list+formatSetStr :: SetField -> Text+formatSetStr j = (foldl append "" (" set " : [(extractColumnName $ columnName j) , " = " , (formatFieldValue $ setFieldValue j) , " ," ]))+++-- | Formats the join table list formatJoinStr :: JoinTable -> Text formatJoinStr j = foldl append "" (" join " : [(extractTableName $ tablename j) , " on " , (extractColumnName $ field j) , " " ,(extractOperator $ operator j) , " " , (extractTableName $ withTable j) , "." , (extractColumnName $ withField j), " " ]) + -- | Fetches field value depending on field type-formatFieldValue :: FieldValue -> String+formatFieldValue :: FieldValue -> Text formatFieldValue a = case a of- IntField x -> show x- TextField x -> show x- DateField x -> show x+ IntField x -> showt x+ TextField x -> showt x+ DateField x -> showt x + -- | Formats WhereCondition to Text formatWhereConditionStr :: WhereCondition -> Text-formatWhereConditionStr j = foldl append " " (" where " : [ (extractTableName $ whereTableName j), "." , (extractColumnName $ whereField j) , " " ,(extractOperator $ whereOperator j) , " " , (pack $ formatFieldValue $ whereFieldValue j)])+formatWhereConditionStr j = foldl append " " (" where " : [ (extractTableName $ whereTableName j), "." , (extractColumnName $ whereField j) , " " ,(extractOperator $ whereOperator j) , " " , (formatFieldValue $ whereFieldValue j)]) + -- | Creates final SqlResultQuery type-formatToSqlResultQueryType sql = SqlResultQuery (getActionArg sql) (getSelectTableArg sql) (getJoinTableArg sql) (getWhereConditionArg sql)+formatToSqlResultQueryType sql = SqlResultQuery (getActionArg sql) (getSelectTableArg sql) (getSetFieldsArg sql) (getJoinTableArg sql) (getWhereConditionArg sql) --- | Returns raw sql query string-rawSqlStr :: SqlQuery -> Text-rawSqlStr s =- foldl append "" [(extractAction $ getAction sql) ,(extractTableName $ getSelectTable sql) , joins , whereConditions ]- where joins = foldl append "" $ fmap formatJoinStr $ getJoins sql- whereConditions = foldl append "" $ fmap formatWhereConditionStr $ getWhereCondition sql+getErrors :: SqlQuery -> Text+getErrors s = t+ where+ t =+ case action s of+ Action "SELECT"+ | isJust (set s) -> " Do not use SET in SELECT query "+ | Data.Text.null (extractTableName $ selectName s) -> " You are missing the FROM table name in SELECT statement "+ | otherwise -> ""+ Action "DELETE"+ | isJust (joinTables s) -> " Do not use joins in DELETE query "+ | otherwise -> ""+ Action "UPDATE"+ | isJust (joinTables s) -> " Do not use joins in UPDATE query "+ | otherwise -> ""+ Action "INSERT"+ | isJust (joinTables s) -> " Do not use joins in UPDATE query "+ | otherwise -> ""++++getWarnings :: SqlQuery -> Text+getWarnings s = t+ where+ t =+ case action s of+ Action "SELECT"+ | isNothing (whereCondition s) ->+ " You are probably missing WHERE statement "+ | otherwise -> ""+ Action "DELETE"+ | isNothing (whereCondition s) ->+ " You are probably missing WHERE statement "+ | otherwise -> ""+ Action "UPDATE"+ | isNothing (whereCondition s) -> " You are missing WHERE statement "+ | otherwise -> ""+ Action "INSERT"+ | isNothing (set s) -> " You are missing the SET statement "+ | otherwise -> ""+++++-- | Returns raw sql ByteString+rawSqlStr :: SqlQuery -> ByteString+rawSqlStr s = encode $ SqlResponse {response = alltext, errors = getErrors s , warnings = getWarnings s}+ where+ alltext = foldl append "" [(extractAction $ getAction sql) ,(extractTableName $ getSelectTable sql) , setFields , joins , whereConditions ]+ setFields = case getSetFields sql of+ Just x -> Data.Text.init $ foldl append "" $ fmap formatSetStr x+ Nothing -> ""+ joins = case getJoins sql of+ Just x -> foldl append "" $ fmap formatJoinStr x+ Nothing -> ""+ whereConditions = case getWhereCondition sql of+ Just x -> foldl append "" $ fmap formatWhereConditionStr x+ Nothing -> "" sql = formatToSqlResultQueryType s
README.md view
@@ -1,4 +1,5 @@-# SERVIETTE - JSON to SQL +# SERVIETTE - JSON to SQL [](https://travis-ci.org/v0d1ch/serviette)+ Library for generating SQL queries from JSON. Send the json in the expected format and receive raw sql string. ### Why ?@@ -20,14 +21,12 @@ } ```` -If `format` is set to 1 you will get raw sql string back:+If `format` is set to 1 you will get json response: ````-SELECT users join addresses on userid = users.id join posts on userid = users.id where users.id > 1+{"response":"SELECT users join addresses on userid = users.id join posts on userid = users.id where users.id > 1","warnings":"","errors":""} ````--### Work in progress-This works for `SELECT` statements, others need to be implemented as well as error and warning messages if the json does not contain what it should. + You can find this lib on [hackage](https://hackage.haskell.org/package/serviette)-Example usage with Yesod is [here](https://github.com/v0d1ch/serviette-yesod-example)+Serviette backend is [here](https://github.com/v0d1ch/serviette-yesod-example)
serviette.cabal view
@@ -1,7 +1,7 @@ name: serviette-version: 0.1.0.4-synopsis: JSON to raw Sql string -description: Use json to query the database and receive results+version: 0.1.0.5+synopsis: JSON to Sql +description: Use json to generate simple sql queries. I needed this for the work project and decided to implement it also in haskell. This could be used to query the database directly from frontend. homepage: https://github.com/v0d1ch/serviette#readme license: BSD3 license-file: LICENSE@@ -20,6 +20,7 @@ , aeson , bytestring >= 0.9 && < 0.11 , text >= 0.11 && < 2.0+ , text-show , generic-deriving