search

khibino / haskell-relational-record

This repository includes a joined query generator based on typefull relational algebra, and mapping tools between SQL values list and Haskell record type.
233 stars 36 forks source link

is it possible to use placeholders with more than two values? #50

Closed dwaldhalm closed 7 years ago

dwaldhalm commented 7 years ago 
productionByDateRange :: Relation (Day, Day, Int) Production
productionByDateRange = relation' . placeholder $ \ph -> do
  p <- query production
  wheres $ p ! transactiondate' .>=. ph ! fst'
  wheres $ p ! transactiondate' .<=. ph ! snd'
  wheres $ p ! carrierid'        .=.  --what to do here?
  return p
dwaldhalm commented 7 years ago

This is the alternative (of which I am aware). Is it congruent with the author's intent?

data QueryParamBeginEndCarrier =
  QueryParamBeginEndCarrier{ qpBegin     :: Day
                           , qpEnd       :: Day
                           , qpCarrierId :: Int
                           } deriving (Eq, Show, Read)

productionByDateRange :: QueryParamBeginEndCarrier -> Relation () Production
productionByDateRange QueryParamBeginEndCarrier{..} = relation $ do
  p <- query production
  wheres $ p ! transactiondate' .>=. sqlDayProjection qpBegin
  wheres $ p ! transactiondate' .<=. sqlDayProjection qpEnd
  wheres $ p ! carrierid'        .=. sqlIntProjection qpCarrierId
  return p

sqlDayProjection :: SqlProjectable p => Day -> p Day
sqlDayProjection = unsafeProjectSqlTerms . showConstantTermsSQL . show

sqlIntProjection :: SqlProjectable p => Int -> p Int32
sqlIntProjection = unsafeProjectSqlTerms . showConstantTermsSQL . show
khibino commented 7 years ago

If parameter carrier record type exists (like this case), you can use record typed placeholder as follows. This is good for combating SQL injection.

import Database.HDBC.Query.TH (makeRecordPersistableDefault)

data QueryParamBeginEndCarrier =
  QueryParamBeginEndCarrier{ qpBegin     :: Day
                           , qpEnd       :: Day
                           , qpCarrierId :: Int
                           } deriving (Eq, Show, Read)

$(makeRecordPersistableDefault ''QueryParamBeginEndCarrier)

productionByDateRange :: Relation QueryParamBeginEndCarrier Production
productionByDateRange = relation' . placeholder $ \ph -> do
  p <- query production
  wheres $ p ! transactiondate' .>=. ph ! qpBegin'
  wheres $ p ! transactiondate' .<=. ph ! qpEnd'
  wheres $ p ! carrierid'        .=. ph ! qpCarrierId'
  return p