sql - 生成的 SQL 查询未返回与 sqlite3 HDBC 中相应静态查询相同的内容

Question

我在 Haskell 中生成 SQL 查询并使用 HDBC 将它们提交到 SQLite(3) 数据库。现在，这个函数返回一个查询:

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 "second"],[SqlByteString "1",SqlByteString "2",SqlByteString "6",SqlByteString "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.507488888888889",SqlByteString "0.655905050505051",SqlByteString "fourth"],[SqlByteString "3",SqlByteString "2",SqlByteString "3",SqlByteString "0.149383838383838",SqlByteString "0.312777777777778",SqlByteString "second"],[SqlByteString "3",SqlByteString "2",SqlByteString "6",SqlByteString "0.507488888888889",SqlByteString "0.655905050505051",SqlByteString "fourth"]]

现在，当我需要在查询中插入几个小的动态部分时，就像这样(抱歉，您必须滚动到字符串的末尾才能看到):

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 

[]

为什么第二个查询没有返回任何东西(或者，实际上是同样的东西)？

有什么想法吗？

编辑:

我进一步调查了一下，认为这可能与懒惰有关。好的，现在已经 reshape 为:

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

不幸的是，这对解决问题没有帮助(当我在 ghci 中 :sprint 函数的返回值时，它仍然未计算)。所以，懒惰可能是问题所在，但我不知道如何对此进行全面评估……？请问，有什么想法吗？

Answer 1

所以...只是陈述事实:

• 您的代码确实运行了，它不会产生任何语法错误或警告(这是针对 haskell 和由 haskell )
• 原始查询确实运行但没有添加 op 和 pos(其中已经有动态部分)
• 你得到一个空集(意思是，查询不返回任何行)...

如果所有这些都是真的，它让我相信查询一定是有效的但错误的。查资料？转储查询，手动运行它。让我知道。

要尝试的事情:

• 尝试回滚更改以查看它是否仍然有效(这样您就知道没有任何意外更改并验证数据是否相同)。
• 您可以尝试使用更简单的查询进行测试吗？
• 您能否尝试转储查询变量并在数据库中手动运行它(有或没有更改)？
• 是否要发布几行数据(有些行会返回，有些不会)以便我可以将其加载到临时表测试中？
• 尝试将 pos 添加到工作查询中(使用 op 硬编码)，看看是否可行
• 尝试将 op 添加到工作查询中(使用 pos 硬编码)，看看是否可行
• 确保在所有地方都以正确的顺序列出变量

出于某种原因，我一直认为这可能是转换或其他问题的数据类型问题，但我从未使用过 Haskell，所以我无法真正猜测可能发生的其他情况。

其他建议:

• format your query适本地使其易于阅读(至少有一点，所以它不是一个巨大的字符串)
• 更新您的问题以包括有关您的环境设置方式的规范(包括软件/事物的版本)
• 如果您认为问题与懒惰有关，try forcing evaluation ……？但是查询确实已经有了动态/可变部分。如果是这种情况，我不得不假设他们会遇到同样的问题，并且查询一开始就无法正常工作。
• 这会很愚蠢，但是您并没有碰巧更改从中提取的数据库，对吗？

---

sqlite> select * from temp;
temp_id     temp_name
----------  ----------
1           one
2           two
3           three

import Database.HDBC.Sqlite3 
import Database.HDBC

testdb = "C:\\Users\\Kim!\\test.db"

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 temp_id op = let 
   q = "select temp_id, temp_name from temp where temp_id = " ++ op ++ " ?";  
   a = [ toSql temp_id ] 
   in UmeQuery q a

> let a = selectPos (1::Int) "="
> let b = tRunUmeQuery a testdb 
> b
[[SqlByteString "1",SqlByteString "one"]]

> let a = selectPos (1::Int) ">"
> let b = tRunUmeQuery a testdb 
> b
[[SqlByteString "2",SqlByteString "two"],[SqlByteString "3",SqlByteString "three"]] 

快速说明:今天之前我从未接触过 Haskell 或 SQLite。我正在使用此 SQLite3 运行 Haskell Platform 2014.2.0.0 - sqlite-dll-win64-x64-201409301904.zip在 Windows 7 Professional 64 位上。

编辑:这也有效...(查询也略有不同)

import Data.List

selectPos temp_id op temp_name = let 
   q = foldl' (++)  [] [
       "select temp_id, temp_name        " ++ 
       "from   temp                      " ++
       "where  temp_id " , op , " ? or   " ++
       "       temp_name = ?             "]
   a = [ toSql (temp_id::Int), toSql temp_name ]  
   in UmeQuery q a

> let a = selectPos 1 ">" "one"
> let b = tRunUmeQuery a testdb 
> b
[[SqlByteString "1",SqlByteString "one"],[SqlByteString "2",SqlByteString "two"],[SqlByteString "3",SqlByteString "three"]] 

编辑:这有效...

sqlite> insert into temp values (4, "Word"); 
sqlite> insert into temp values (5, "Utterance");

selectPos targetlt parentlt op pos = let 
   q = " select temp_id, temp_name        \
       \ from   temp                      \
       \ where  temp_name = ?  or         \
       \        temp_name = ?  or         \
       \        temp_name = ?  or         \
       \        temp_name = ?  or         \
       \        temp_id "++op++" ?        "
   a = [toSql targetlt, toSql parentlt, 
        toSql targetlt, toSql parentlt, 
        toSql (pos::Int) ]
   in UmeQuery q a

> let a = selectPos "Word" "Utterance" "=" 2
> let b = tRunUmeQuery a testdb 
> b
[[SqlByteString "2",SqlByteString "two"],[SqlByteString "4",SqlByteString "Word"],[SqlByteString "5",SqlByteString "Utterance"]]

所以...在您在问题中发布的查询中...也有意想不到的差异...这与变量无关。这是一个单引号。不确定是否只是复制和粘贴时的拼写错误或什么。我显然无法运行您的查询，因为它确实需要大量的模拟表和数据...

编辑: 哈...我又回到了这里。我注意到您在上一个我没有使用的 selectPos 示例上方多了一行。我必须这样做才能让它工作...... [[SqlValue]] 或 IO [[SqlValue]] 因为最后一个值对我不起作用;错误(我只是在尝试，我不知道这些值中的任何一个是否真正有意义)。

selectPos :: String -> String -> String -> Integer -> UmeQuery
selectPos targetlt parentlt op pos = let 
   q = " select temp_id, temp_name        \
       \ from   temp                      \
       \ where  temp_name = ?  or         \
       \        temp_name = ?  or         \
       \        temp_name = ?  or         \
       \        temp_name != ?  or        \
       \        temp_id "++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
[[SqlByteString "1",SqlByteString "one"],[SqlByteString "2",SqlByteString "two"],[SqlByteString "3",SqlByteString "three"],[SqlByteString "4",SqlByteString "Word"],[SqlByteString "5",SqlByteString "Utterance"]] 

不管怎样...我很高兴我今天写了我的第一个 Haskell 程序...!