{-# LANGUAGE QuasiQuotes, TemplateHaskell, TypeFamilies, OverloadedStrings #-}
{-# LANGUAGE GADTs, FlexibleContexts #-}
{-# LANGUAGE EmptyDataDecls #-}
{-# LANGUAGE FlexibleInstances, MultiParamTypeClasses #-}
{-# LANGUAGE ScopedTypeVariables, GeneralizedNewtypeDeriving, DeriveGeneric #-}
module Test1 where
import qualified Database.Persist as P
import Database.Persist.TH
import Control.Monad.IO.Class (liftIO)
import Control.Monad.Logger
import Control.Monad.Trans.Resource (runResourceT,MonadResource)
import Control.Monad.Trans.Reader (ask)
import Data.Text (Text)
import qualified Data.Text as T
import Database.Persist.ODBC
--import Data.Conduit
import Data.Aeson
import System.Environment (getArgs)
import qualified Database.Esqueleto as E
import Database.Esqueleto (select,where_,(^.),from,Value(..))
import Control.Applicative ((<$>),(<*>))
import Data.Int
import Debug.Trace
import Control.Monad (when)
share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
G0
g1 String
g2 String
g3 String
deriving Show Eq
G1
g1 String maxlen=20
g2 String
g3 String
Primary g1
deriving Show Eq
G2
g1 String maxlen=20
g2 String maxlen=20
g3 String
Primary g1 g2
deriving Show Eq
G3
g1 G0Id
g2 String
g3 String maxlen=20
Primary g1 g3 -- what does this mean? g1 is a foreign key
deriving Show Eq
TestA
name1 String
name2 String
Unique MyNames name1 name2
deriving Show Eq
TestBool
mybool Bool
deriving Show Eq
TestTwoStrings
firstname String
lastname String
deriving Show Eq
TableOne json
nameone String
deriving Show Eq
TableTwo json
nametwo String
deriving Show Eq
TableThree json
namethree String
deriving Show Eq
TableFour
namefour Int
deriving Show Eq
TableMany json
refone TableOneId
reftwo TableTwoId
Primary refone reftwo
deriving Show Eq
TableManyMany json
refone TableOneId
reftwo TableTwoId
refthree TableThreeId
Primary refone reftwo refthree
deriving Show Eq
TableU
refone TableOneId
name String
Unique SomeName name
|]
updatePersistValue :: Update v -> PersistValue
updatePersistValue (Update a1 v a2) = trace ("updatePersistValue a2="++show a2) $ toPersistValue v
main :: IO ()
main = do
[arg] <- getArgs
let (dbtype',dsn) =
case arg of -- odbc system dsn
"d" -> (DB2,"dsn=db2_test")
"p" -> (Postgres,"dsn=pg_test")
"m" -> (MySQL,"dsn=mysql_test")
"s" -> (MSSQL True,"dsn=mssql_test; Trusted_Connection=True") -- mssql 2012 [full limit and offset support]
"so" -> (MSSQL False,"dsn=mssql_test; Trusted_Connection=True") -- mssql pre 2012 [limit support only]
"o" -> (Oracle False,"dsn=oracle_test") -- pre oracle 12c [no support for limit and offset]
"on" -> (Oracle True,"dsn=oracle_test") -- >= oracle 12c [full limit and offset support]
"q" -> (Sqlite False,"dsn=sqlite_test")
"qn" -> (Sqlite True,"dsn=sqlite_test")
xs -> error $ "unknown option:choose p m s so o on d q qn found[" ++ xs ++ "]"
runResourceT $ runNoLoggingT $ withODBCConn Nothing dsn $ runSqlConn $ do
conn <- ask
let dbtype::DBType
dbtype=read $ T.unpack $ connRDBMS conn
liftIO $ putStrLn $ "original:" ++ show dbtype' ++ " calculated:" ++ show dbtype
liftIO $ putStrLn "\nbefore migration\n"
runMigration migrateAll
liftIO $ putStrLn "after migration"
doesq
when True $ dostuff dbtype
dostuff dbtype = do
z1 <- insert $ TableOne "test1 aa"
z2 <- insert $ TableOne "test1 bb"
a1 <- insert $ TableOne "test1 cc"
liftIO $ putStrLn $ "a1=" ++ show a1
a2 <- insert $ TableTwo "test2"
liftIO $ putStrLn $ "a2=" ++ show a2
aa <- selectList ([]::[Filter TableOne]) []
liftIO $ putStrLn $ "aa=" ++ show aa
let b=encode $ head aa
liftIO $ putStrLn $ "\ntojson for tableone " ++ show b
let c=decode' b::Maybe (Entity TableOne)
liftIO $ putStrLn $ "\nfromjson for tableone " ++ show c
a3 <- insert $ TableMany a1 a2
liftIO $ putStrLn $ "a3=" ++ show a3
bb <- selectList ([]::[Filter TableMany]) []
liftIO $ putStrLn $ "bb=" ++ show bb ++"\n\n" ++ show (toPersistValue z1)
zz <- selectList [TableManyRefone <. a1] [Asc TableManyRefone]
liftIO $ putStrLn $ "\n\n!!!TESTING FILTER zz=" ++ show zz ++"\n\n"
let x=toJSON $ head bb
liftIO $ putStrLn $ "\njson for a single tablemany " ++ show x
let b=encode $ head bb
liftIO $ putStrLn $ "\ntojson for a single tablemany " ++ show b
let c=decode' b::Maybe (Entity TableMany)
liftIO $ putStrLn $ "fromjson for tablemany " ++ show c
let b1=encode bb
liftIO $ putStrLn $ "\ntojson for a list of tablemany " ++ show b1
let c1=decode' b1::Maybe [Entity TableMany]
liftIO $ putStrLn $ "\nfromjson for list of tablemany " ++ show c1
liftIO $ putStrLn $ "dude=" ++ show (updatePersistValue (TableManyRefone =. z1))
p5 <- get a3
liftIO $ putStrLn $ "after get!!!"
liftIO $ print p5
update a3 [TableManyRefone =. z1]
p5 <- get a1
liftIO $ putStrLn $ "after normal get!!!"
liftIO $ print p5
c1 <- insert $ TableOne "testa"
c2 <- insert $ TableTwo "testb"
c3 <- insert $ TableThree "testc"
m3 <- insert $ TableManyMany c1 c2 c3
liftIO $ putStrLn $ "m3=" ++ show m3
liftIO $ putStrLn $ "before get zzz"
zzz <- get m3
liftIO $ putStrLn $ "before delete zzz=" ++ show zzz
delete m3
liftIO $ putStrLn $ "after delete m3"
--zzz <- get m3
--liftIO $ putStrLn $ "after get again : should have failed zzz=" ++ show zzz
bb <- selectList ([]::[Filter TableManyMany]) []
liftIO $ putStrLn $ "bb=" ++ show bb ++"\n\n"
{-
xs <- select $
from $ \ln -> do
where_ (ln ^. TableManyRefone E.<=. E.val a1)
E.orderBy [E.asc (ln ^. TableManyRefone)]
-- E.limit 3
-- E.offset 2
return ln
liftIO $ putStrLn $ show (length xs) ++ " rows: limit=3 offset=2 xs=" ++ show xs
-}
xs <- select $
from $ \ln -> do
where_ (ln ^. TableOneNameone E.<=. E.val "test1 bb")
E.orderBy [E.asc (ln ^. TableOneNameone)]
-- E.limit 4
-- E.offset 1
return ln
liftIO $ putStrLn $ show (length xs) ++ " rows: limit=2 offset=3 xs=" ++ show xs
a11 <- updateGet a1 [TableOneNameone =. "freee"]
liftIO $ putStrLn $ "a11=" ++ show a11
case dbtype of
Oracle False -> liftIO $ putStrLn $ "oracle so no selectfirst"
_ -> do
a22 <- selectFirst [TableOneNameone ==. "freee"] [Desc TableOneNameone]
liftIO $ putStrLn $ "a22=" ++ show a22
a33 <- count [TableOneNameone >=. "a"]
liftIO $ putStrLn $ "a33=" ++ show a33
m3 <- insert $ TableManyMany c1 c2 c3
a11 <- updateGet m3 [TableManyManyRefone =. c1]
liftIO $ putStrLn $ "a11=" ++ show a11
case dbtype of
Oracle False -> liftIO $ putStrLn $ "oracle so no selectfirst"
_ -> do
a22 <- selectFirst [TableManyManyReftwo ==. c2] [Desc TableManyManyRefone]
liftIO $ putStrLn $ "a22=" ++ show a22
a33 <- count [TableManyManyReftwo <=. c2]
liftIO $ putStrLn $ "a33=" ++ show a33
a33 <- count ([]::[Filter TableOne])
liftIO $ putStrLn $ "before =" ++ show a33
updateWhere [TableOneNameone ==. "freee"] [TableOneNameone =. "dude"]
deleteWhere [TableOneNameone ==. "dude"]
a33 <- count ([]::[Filter TableOne])
liftIO $ putStrLn $ "after =" ++ show a33
p4 <- selectList [TableOneNameone >=. "a"] []
liftIO $ print p4
liftIO $ putStrLn $ "before selectKeys List 111"
p4 <- selectKeysList [TableOneNameone >=. "a"] []
liftIO $ print p4
liftIO $ putStrLn $ "before selectKeys List 222"
p4 <- selectKeysList [TableManyManyReftwo <=. c2] []
liftIO $ print p4
--doesq::IO ()
doesq = do
deleteWhere ([]::[Filter G3])
deleteWhere ([]::[Filter G2])
deleteWhere ([]::[Filter G1])
deleteWhere ([]::[Filter G0])
g1 <- insert $ G1 "aaa1" "bbb1" "ccc1"
liftIO $ putStrLn $ "g1=" ++ show g1
g2 <- insert $ G2 "aaa2" "bbb2" "ccc2"
liftIO $ putStrLn $ "g2=" ++ show g2
gg1 <- selectList [] [Desc G1G1]
gg2 <- selectList [] [Desc G2G3]
liftIO $ putStrLn $ "gg1=" ++ show gg1
liftIO $ putStrLn $ "gg2=" ++ show gg2
g0 <- insert $ G0 "aaa0" "bbb0" "ccc0"
liftIO $ putStrLn $ "g1=" ++ show g1
g3 <- insert $ G3 g0 "bbb3" "ccc3"
liftIO $ putStrLn $ "g3=" ++ show g3
gg3a <- selectList [] [Desc G3G1]
liftIO $ putStrLn $ "gg3a=" ++ show gg3a
gg3b <- selectList [] [Desc G3G3]
liftIO $ putStrLn $ "gg3b=" ++ show gg3b