Генерирам SQL заявки в Haskell и ги изпращам в база данни SQLite(3), използвайки HDBC. Сега тази функция връща заявка:
import Database.HDBC.Sqlite3
import Database.HDBC
data UmeQuery = UmeQuery String [SqlValue] deriving Show
tRunUmeQuery :: UmeQuery -> FilePath -> IO [[SqlValue]]
tRunUmeQuery (UmeQuery q args) dbFile = do
conn <- connectSqlite3 dbFile
stat <- prepare conn q
s <- execute stat args
res <- fetchAllRows' stat
disconnect conn
return $ res
selectPos targetlt parentlt op pos = let
q= "select TARGET.* from levels tl, labeltypes tlt, segments TARGET,
(select TARGET.session_id session_id,SECONDARY.labeltype_id labeltype_id,
SECONDARY.label_id label_id,min(TARGET.label_id) min_childlabel_id from
levels tl, labeltypes tlt, segments TARGET, segments SECONDARY, labeltypes slt,
levels sl where TARGET.session_id = SECONDARY.session_id and ((SECONDARY.start
<= TARGET.start and TARGET.end <= SECONDARY.end) or (TARGET.start <= SECONDARY.start
and SECONDARY.end <= TARGET.end)) and tl.name = ? and sl.name = ? and SECONDARY.label '
!= '' and tl.id = tlt.level_id and sl.id = slt.level_id and tlt.id = TARGET.labeltype_id
and slt.id = SECONDARY.labeltype_id group by TARGET.session_id, TARGET.labeltype_id,
SECONDARY.label_id) SUMMARY, segments SECONDARY, labeltypes slt, levels sl where
TARGET.session_id = SECONDARY.session_id and TARGET.session_id = SUMMARY.session_id
and ((SECONDARY.start <= TARGET.start and TARGET.end <= SECONDARY.end) or (TARGET.start
<= SECONDARY.start and SECONDARY.end <= TARGET.end)) and tl.name = ? and sl.name = ?
and tl.id = tlt.level_id and tlt.id = TARGET.labeltype_id and SUMMARY.labeltype_id =
SECONDARY.labeltype_id and SUMMARY.label_id = SECONDARY.label_id and sl.id = slt.level_id
and slt.id = SECONDARY.labeltype_id and (TARGET.label_id - SUMMARY.min_childlabel_id +1) = 2 "
a = [toSql targetlt, toSql parentlt, toSql targetlt, toSql parentlt ]
in UmeQuery q a
което, когато се приложи към базата данни, връща правилното нещо:
> let a =selectPos "Word" "Utterance" "=" 2
> let b = tRunUmeQuery a testdb
> b
изходи:
[[SqlByteString "1",SqlByteString "2",SqlByteString "3",SqlByteString "0.149383838383838",SqlByteString "0.312777777777778",SqlByteString "втори"],[SqlByteString "1",SqlByteString "SqlByteqBly",String "2",SqlByteString "SqlByteqBly", "0.507488888888889",SqlByteString "0.655905050505051",SqlByteString "fourth"],[SqlByteString "2",SqlByteString "2",SqlByteString "3",SqlByteString "0.149383838383838",SqlByteString "0.312777777777778",SqlByteString "second"],[SqlByteString " 2 ", SqlByTestring" 2 ", SqlByTestring" 6 ", SqlByTestring" 0.50748888888889 ", SqlByTestring" 0.655905050505051 ", SqlByTestring" Четвърти "], [SqlByTestring" 3 ", SqlByTestring" 1 -ти ". SqlByteString "0.312777777777778",SqlByteString "втори"],[SqlByteString "3",SqlByteString "2",SqlByteString "6",SqlByteString "0.507488888888889",SqlByteString "0.65590ByteString","]четири"50505Sql050
Сега, когато трябва да вмъкна няколко малки динамични части в заявката, като това (съжалявам, трябва да превъртите до края на низа, за да видите това):
selectPos targetlt parentlt op pos = let
q= "select TARGET.* from levels tl, labeltypes tlt, segments TARGET,
(select TARGET.session_id session_id,SECONDARY.labeltype_id labeltype_id,
SECONDARY.label_id label_id,min(TARGET.label_id) min_childlabel_id from
levels tl, labeltypes tlt, segments TARGET, segments SECONDARY, labeltypes slt,
levels sl where TARGET.session_id = SECONDARY.session_id and ((SECONDARY.start
<= TARGET.start and TARGET.end <= SECONDARY.end) or (TARGET.start <= SECONDARY.start
and SECONDARY.end <= TARGET.end)) and tl.name = ? and sl.name = ? and SECONDARY.label
!= '' and tl.id = tlt.level_id and sl.id = slt.level_id and tlt.id = TARGET.labeltype_id
and slt.id = SECONDARY.labeltype_id group by TARGET.session_id, TARGET.labeltype_id,
SECONDARY.label_id) SUMMARY, segments SECONDARY, labeltypes slt, levels sl where
TARGET.session_id = SECONDARY.session_id and TARGET.session_id = SUMMARY.session_id
and ((SECONDARY.start <= TARGET.start and TARGET.end <= SECONDARY.end) or (TARGET.start
<= SECONDARY.start and SECONDARY.end <= TARGET.end)) and tl.name = ? and sl.name = ?
and tl.id = tlt.level_id and tlt.id = TARGET.labeltype_id and SUMMARY.labeltype_id =
SECONDARY.labeltype_id and SUMMARY.label_id = SECONDARY.label_id and sl.id = slt.level_id
and slt.id = SECONDARY.labeltype_id and (TARGET.label_id - SUMMARY.min_childlabel_id +1) "
++ op ++ " ? "
a = [toSql targetlt, toSql parentlt, toSql targetlt, toSql parentlt , toSql pos]
in UmeQuery q a
и правя същото, получавам:
> let a =selectPos "Word" "Utterance" "=" 2
> let b = tRunUmeQuery a testdb
> b
[]
Защо втората заявка не връща нищо (или всъщност същото)?
Някакви идеи?
Редактиране:
Проучих това допълнително, мислейки, че това може да е свързано по някакъв начин с мързелив. Добре, сега е преоформено на това:
selectPos :: String -> String -> String -> Integer -> [[SqlValue]]
selectPos targetlt parentlt op pos = let
q= foldl' (++) [] ["select TARGET.* from levels tl, labeltypes tlt, segments TARGET,
(select TARGET.session_id session_id,SECONDARY.labeltype_id labeltype_id,SECONDARY.label_id
label_id,min(TARGET.label_id) min_childlabel_id from levels tl, labeltypes tlt, segments
TARGET, segments SECONDARY, labeltypes slt, levels sl where TARGET.session_id = SECONDARY.session_id "
,matchstring , " and tl.name = ? and sl.name = ? and SECONDARY.label != '' and tl.id = tlt.level_id
and sl.id = slt.level_id and tlt.id = TARGET.labeltype_id and slt.id = SECONDARY.labeltype_id
group by TARGET.session_id, TARGET.labeltype_id, SECONDARY.label_id) SUMMARY, segments SECONDARY,
labeltypes slt, levels sl where TARGET.session_id = SECONDARY.session_id and TARGET.session_id =
SUMMARY.session_id " , matchstring , " and tl.name = ? and sl.name = ? and tl.id = tlt.level_id
and tlt.id = TARGET.labeltype_id and SUMMARY.labeltype_id = SECONDARY.labeltype_id and SUMMARY.label_id
= SECONDARY.label_id and sl.id = slt.level_id and slt.id = SECONDARY.labeltype_id and
(TARGET.label_id - SUMMARY.min_childlabel_id +1) " , op , " ? "]
a = [toSql targetlt, toSql parentlt, toSql targetlt, toSql parentlt , toSql (pos :: Integer)]
in UmeQuery q a
За съжаление, това не помага на проблема (и когато :sprint върнатата стойност на функцията в ghci, тя все още не е оценена). Така че мързелът може да е проблемът по някакъв начин, но не знам как да направя това напълно оценено..? Моля, някакви идеи?