An open-source, privacy-first, self-hosted knowledge workspace where humans and AI agents work together 开源、隐私优先、自托管的知识工作空间,让人与智能体在此协作
TypeScript agentic-aiai-agentknowledge-baselocal-firstmarkdownmcpmindmapnote-takingself-hostedsiyuanworkspace
// SiYuan - https://github.com/siyuan-note/siyuan
// Copyright (c) 2020-present, b3log.org
// SPDX-License-Identifier: AGPL-3.0-or-later
package sql
import (
"context"
gosql "database/sql"
"fmt"
"math"
"testing"
)
func TestQueryRowLimit(t *testing.T) {
testDB, err := gosql.Open("sqlite3_extended", ":memory:")
if err != nil {
t.Fatal(err)
}
testDB.SetMaxOpenConns(1)
t.Cleanup(func() { _ = testDB.Close() })
previousDB := db
db = testDB
t.Cleanup(func() { db = previousDB })
if _, err = testDB.Exec("CREATE TABLE items (n INTEGER, content TEXT); INSERT INTO items VALUES (1, 'a'), (2, 'a'), (3, 'a'), (4, 'a'), (5, 'a')"); err != nil {
t.Fatal(err)
}
for _, tc := range []struct {
stmt string
count int
truncated bool
}{
{"SELECT n FROM items ORDER BY n", 3, true},
{"SELECT n FROM items WHERE n <= 3 ORDER BY n", 3, false},
{"SELECT n FROM items WHERE n < 0", 0, false},
{"SELECT n FROM items ORDER BY n LIMIT 5", 3, true},
{"SELECT n FROM items ORDER BY n LIMIT -1", 3, true},
{"SELECT n FROM items ORDER BY n LIMIT 2", 2, false},
{"SELECT n FROM items ORDER BY n LIMIT 3 OFFSET 3", 2, false},
{"SELECT n FROM items UNION SELECT 6 ORDER BY n", 3, true},
{"WITH x AS (SELECT n FROM items LIMIT 5) SELECT n FROM x ORDER BY n", 3, true},
{"SELECT n FROM items -- comment\nORDER BY n", 3, true},
{`SELECT n FROM items WHERE content LIKE '%a%' ESCAPE '\' ORDER BY n`, 3, true},
} {
rows, info, queryErr := QueryWithRowLimitInfo(tc.stmt, 3)
if queryErr != nil || rows == nil || len(rows) != tc.count || info.Limit != 3 || info.Truncated != tc.truncated {
t.Fatalf("%s: rows=%v info=%+v err=%v", tc.stmt, rows, info, queryErr)
}
if tc.count > 0 && rows[0]["n"] != int64(1) && tc.stmt != "SELECT n FROM items ORDER BY n LIMIT 3 OFFSET 3" {
t.Fatalf("query order changed: %s: %v", tc.stmt, rows)
}
}
for _, limit := range []int{0, -1, math.MaxInt} {
if _, _, err = QueryWithRowLimitInfo("SELECT 1", limit); err == nil {
t.Fatalf("invalid limit accepted: %d", limit)
}
}
if _, _, err = QueryWithRowLimitInfo("SELECT abs(-9223372036854775808)", 3); err == nil {
t.Fatal("row iteration error was ignored")
}
rows, err := Query(`SELECT n FROM items WHERE content LIKE '%a%' ESCAPE '\' ORDER BY n`, 3)
if err != nil || len(rows) != 3 {
t.Fatalf("raw fallback exceeded default limit: %v %v", rows, err)
}
}
// 带参查询与资源内容查询同样以 limit 为上限:语句未写 LIMIT 时不得多返回一行,
// 否则以 len(rows) == limit 判定截断的调用方会误认结果未截断。
func TestRawStatementQueriesHonourRowLimit(t *testing.T) {
testDB, err := gosql.Open("sqlite3_extended", ":memory:")
if err != nil {
t.Fatal(err)
}
testDB.SetMaxOpenConns(1)
t.Cleanup(func() { _ = testDB.Close() })
previousDB, previousAssetDB := db, assetContentDB
db, assetContentDB = testDB, testDB
t.Cleanup(func() { db, assetContentDB = previousDB, previousAssetDB })
if _, err = testDB.Exec("CREATE TABLE items (n INTEGER, content TEXT); INSERT INTO items VALUES (1, 'a'), (2, 'a'), (3, 'a'), (4, 'a'), (5, 'a')"); err != nil {
t.Fatal(err)
}
for _, limit := range []int{1, 3, 5} {
rows, queryErr := queryRawStmtArgs("SELECT n FROM items WHERE n >= ? ORDER BY n", []any{1}, limit)
if queryErr != nil || len(rows) != limit {
t.Fatalf("queryRawStmtArgs limit=%d: got %d rows, err=%v", limit, len(rows), queryErr)
}
assetRows, assetErr := queryAssetContentRawStmt("SELECT n FROM items WHERE n >= ? ORDER BY n", []any{1}, limit)
if assetErr != nil || len(assetRows) != limit {
t.Fatalf("queryAssetContentRawStmt limit=%d: got %d rows, err=%v", limit, len(assetRows), assetErr)
}
assets := selectAssetContentsRawStmt("SELECT n, content, '', content, 0, 0, content FROM items WHERE n >= ? ORDER BY n", []any{1}, limit)
if len(assets) != limit {
t.Fatalf("selectAssetContentsRawStmt limit=%d: got %d rows", limit, len(assets))
}
}
// 语句自带 LIMIT 时由语句决定行数,本层不再截断。
rows, err := queryRawStmtArgs("SELECT n FROM items ORDER BY n LIMIT 2", nil, 3)
if err != nil || len(rows) != 2 {
t.Fatalf("statement limit was overridden: %d rows, err=%v", len(rows), err)
}
}
func TestBlockRawStatementRowLimitsInBothDatabases(t *testing.T) {
testDB, boxID := useEncryptedQueryTestDB(t)
previousDB := db
db = testDB
t.Cleanup(func() { db = previousDB })
for i := 1; i <= 5; i++ {
insertEncryptedQueryTestBlock(t, testDB, fmt.Sprintf("row-%d", i), "", "root", "p")
}
const statement = "SELECT * FROM blocks ORDER BY id"
for _, scenario := range []struct {
name string
run func(string, int) (int, error)
}{
{"global block args", func(stmt string, limit int) (int, error) {
rows := SelectBlocksRawStmtArgs(stmt, nil, limit)
return len(rows), nil
}},
{"global block no parse", func(stmt string, limit int) (int, error) {
return len(SelectBlocksRawStmtNoParse(stmt, limit)), nil
}},
{"encrypted block args", func(stmt string, limit int) (int, error) {
rows, err := SelectBlocksRawStmtArgsInBoxContext(context.Background(), stmt, nil, limit, boxID)
return len(rows), err
}},
{"encrypted block no parse", func(stmt string, limit int) (int, error) {
return len(SelectBlocksRawStmtNoParseInBox(stmt, limit, boxID)), nil
}},
{"encrypted map", func(stmt string, limit int) (int, error) {
rows, err := queryRawStmtForBox(boxID, stmt, limit)
return len(rows), err
}},
{"encrypted map args", func(stmt string, limit int) (int, error) {
rows, err := queryRawStmtArgsForBox(boxID, stmt, nil, limit)
return len(rows), err
}},
{"encrypted map args context", func(stmt string, limit int) (int, error) {
rows, err := queryRawStmtArgsForBoxContext(context.Background(), boxID, stmt, nil, limit)
return len(rows), err
}},
} {
t.Run(scenario.name, func(t *testing.T) {
for _, limit := range []int{1, 3, 5, 6} {
count, err := scenario.run(statement, limit)
if err != nil || count != min(limit, 5) {
t.Fatalf("limit %d: count=%d, err=%v", limit, count, err)
}
}
count, err := scenario.run(statement+" LIMIT 4", 1)
if err != nil || count != 4 {
t.Fatalf("explicit SQL limit must take precedence: count=%d, err=%v", count, err)
}
})
}
}
func TestQueryWithLimitInfo(t *testing.T) {
testDB, err := gosql.Open("sqlite3_extended", ":memory:")
if err != nil {
t.Fatal(err)
}
testDB.SetMaxOpenConns(1)
defer testDB.Close()
previousDB := db
db = testDB
defer func() { db = previousDB }()
if _, err = testDB.Exec("CREATE TABLE items (n INTEGER); INSERT INTO items VALUES (1), (2), (3), (4), (5)"); err != nil {
t.Fatal(err)
}
for _, tc := range []struct {
name, stmt string
count, limit int
truncated bool
}{
{"empty", "SELECT n FROM items WHERE n < 0", 0, 3, false},
{"below", "SELECT n FROM items WHERE n < 3", 2, 3, false},
{"equal", "SELECT n FROM items WHERE n <= 3", 3, 3, false},
{"above", "SELECT n FROM items ORDER BY n", 3, 3, true},
{"explicit below", "SELECT n FROM items LIMIT 2", 2, 0, false},
{"explicit equal", "SELECT n FROM items LIMIT 3", 3, 0, false},
{"explicit above", "SELECT n FROM items LIMIT 4", 4, 0, false},
{"explicit zero", "SELECT n FROM items LIMIT 0", 0, 0, false},
{"explicit unlimited", "SELECT n FROM items LIMIT -1", 5, 0, false},
{"offset", "SELECT n FROM items ORDER BY n LIMIT 3 OFFSET 3", 2, 0, false},
{"aggregate", "SELECT count(*) FROM items", 1, 3, false},
{"union", "SELECT n FROM items UNION SELECT 6", 3, 3, true},
{"union explicit", "SELECT n FROM items UNION SELECT 6 LIMIT 5", 5, 0, false},
{"nested", "SELECT n FROM (SELECT n FROM items LIMIT 4)", 3, 3, true},
{"cte", "WITH x AS (SELECT n FROM items LIMIT 4) SELECT * FROM x", 3, 3, true},
{"fallback", "SELECT n || ' limit ' FROM items UNION SELECT 'x'", 3, 3, true},
{"fallback below", "SELECT n || '' FROM items WHERE n < 2 UNION SELECT 'x'", 2, 3, false},
{"fallback equal", "SELECT n || '' FROM items WHERE n < 3 UNION SELECT 'x'", 3, 3, false},
{"fallback explicit", "SELECT n || '' FROM items UNION SELECT 'x' LIMIT\n4", 4, 0, false},
{"fallback nested", "SELECT n || '' FROM (SELECT n FROM items LIMIT 4) UNION SELECT 'x'", 3, 3, true},
{"fallback comment", "SELECT n || '' FROM items UNION SELECT 'x' /* LIMIT 1 */", 3, 3, true},
{"multiple", "SELECT n FROM items LIMIT 1; SELECT n FROM items", 3, 3, true},
{"multiple explicit", "SELECT n FROM items; SELECT n FROM items LIMIT 4", 4, 0, false},
{"multiple write", "SELECT 1; INSERT INTO items VALUES (6) RETURNING n", 1, 3, false},
} {
t.Run(tc.name, func(t *testing.T) {
rows, info, queryErr := QueryWithLimitInfo(tc.stmt, 3)
if queryErr != nil {
t.Fatal(queryErr)
}
if rows == nil || len(rows) != tc.count || info.Limit != tc.limit || info.Truncated != tc.truncated {
t.Fatalf("rows=%v info=%+v, want count=%d limit=%d truncated=%v", rows, info, tc.count, tc.limit, tc.truncated)
}
})
}
var count int
if err = testDB.QueryRow("SELECT count(*) FROM items WHERE n = 6").Scan(&count); err != nil || count != 1 {
t.Fatalf("write executed more than once: count=%d err=%v", count, err)
}
if _, _, err = QueryWithLimitInfo("SELECT * FROM missing_table", 3); err == nil {
t.Fatal("expected query error")
}
if _, _, err = QueryWithLimitInfo("SELECT abs(-9223372036854775808)", 3); err == nil {
t.Fatal("expected row iteration error")
}
if _, _, err = QueryWithLimitInfo("SELECT abs(-9223372036854775808) || '' UNION SELECT 'x'", 3); err == nil {
t.Fatal("expected raw row iteration error")
}
rows, _, err := QueryWithLimitInfo("SELECT n FROM items ORDER BY n LIMIT 2 OFFSET 3", 3)
if err != nil || len(rows) != 2 || rows[0]["n"] != int64(4) || rows[1]["n"] != int64(5) {
t.Fatalf("unexpected page: rows=%v err=%v", rows, err)
}
}
func TestContainsOuterLimitClause(t *testing.T) {
for _, stmt := range []string{
"SELECT * FROM t LIMIT\n3; -- trailing",
"SELECT * FROM t LiMiT/* comment */3",
"SELECT 1; SELECT * FROM t LIMIT 3;; /* trailing */",
} {
if !containsOuterLimitClause(stmt) {
t.Errorf("missing outer limit: %s", stmt)
}
}
for _, stmt := range []string{
"SELECT ' limit ', 'it''s limit ', \"limit\", `limit`, [limit] FROM t",
"SELECT * FROM (SELECT * FROM t LIMIT 3)",
"SELECT * FROM t -- LIMIT 3",
"SELECT * FROM t /* LIMIT 3 */",
"SELECT * FROM t LIMIT 3; SELECT * FROM t",
"SELECT [a' limit ] FROM t",
} {
if containsOuterLimitClause(stmt) {
t.Errorf("unexpected outer limit: %s", stmt)
}
}
}