Files
cflmy 9331af78e2 release(2.8.0): 合入 CHANGELOG/README/发版说明/skills prove 任务;CI 链接指向 doc/releases
- CHANGELOG.md:v2.8.0 条目(中英双语);
- README.md:版本行 2.7.1→2.8.0 + 愿景第 6 条(知识可证明);
- doc/releases/v2.8.0.md:双语发版说明(亮点/用法/已知限制);
- skills/marqdo-dev/index.mq.md:新增 prove 任务(路径/目录模式/
  require_lean/json;参数用 + 行声明升参,check 与 narrative-ratio 通过);
- 设计文档 §12.5 标记已合入;发版链接统一指向 doc/releases/v2.8.0.md。
2026-11-18 10:29:26 +08:00

243 lines
5.7 KiB
Go
Executable File

package db
import (
"fmt"
"strings"
"github.com/marqdo/marqdo/plugins/web/internal/table"
)
func parseColumnList(columns any) ([]string, error) {
switch t := columns.(type) {
case string:
parts := strings.Split(t, ",")
var cols []string
for _, p := range parts {
p = strings.TrimSpace(p)
if p == "" {
continue
}
if _, err := ident(p); err != nil {
return nil, err
}
cols = append(cols, p)
}
if len(cols) == 0 {
return nil, fmt.Errorf("fts columns is empty")
}
return cols, nil
case []any:
var cols []string
for _, v := range t {
name := cellStr(v)
if _, err := ident(name); err != nil {
return nil, err
}
cols = append(cols, name)
}
if len(cols) == 0 {
return nil, fmt.Errorf("fts columns is empty")
}
return cols, nil
case map[string]any:
rows := table.AsRows(columns)
arr, ok := rows.([]any)
if !ok {
return nil, fmt.Errorf("fts columns must be a list or CSV string")
}
var cols []string
for _, row := range arr {
if obj, ok := row.(map[string]any); ok {
name := ""
for _, k := range []string{"name", "字段", "column"} {
if v, ok := obj[k]; ok {
name = cellStr(v)
break
}
}
if name == "" {
for _, v := range obj {
name = cellStr(v)
break
}
}
if name != "" {
if _, err := ident(name); err != nil {
return nil, err
}
cols = append(cols, name)
}
} else {
name := cellStr(row)
if name != "" {
if _, err := ident(name); err != nil {
return nil, err
}
cols = append(cols, name)
}
}
}
if len(cols) == 0 {
return nil, fmt.Errorf("fts columns is empty")
}
return cols, nil
case nil:
return nil, fmt.Errorf("fts columns is empty")
default:
return nil, fmt.Errorf("fts columns must be a list or CSV string")
}
}
func ftsName(tableName string, name string) (string, error) {
name = strings.TrimSpace(name)
if name == "" {
return tableName + "_fts", nil
}
return ident(name)
}
// FtsCreate builds an FTS5 virtual table synced via content= + triggers.
// Returns {"ok":true,"table":…,"fts":…,"columns":[…]}.
func FtsCreate(url, tableName string, columns any, name string) (map[string]any, error) {
lower := strings.ToLower(ResolveURL(url))
if strings.HasPrefix(lower, "postgres://") || strings.HasPrefix(lower, "postgresql://") {
return nil, fmt.Errorf("db.fts is SQLite-only in this wave")
}
tn, err := ident(tableName)
if err != nil {
return nil, err
}
cols, err := parseColumnList(columns)
if err != nil {
return nil, err
}
fts, err := ftsName(tn, name)
if err != nil {
return nil, err
}
quoted := make([]string, len(cols))
for i, c := range cols {
quoted[i] = `"` + c + `"`
}
colSQL := strings.Join(quoted, ", ")
db, err := open(url)
if err != nil {
return nil, err
}
create := fmt.Sprintf(
`CREATE VIRTUAL TABLE IF NOT EXISTS "%s" USING fts5(%s, content="%s", content_rowid="id")`,
fts, colSQL, tn,
)
if _, err := db.Exec(create); err != nil {
return nil, err
}
ai, ad, au := fts+"_ai", fts+"_ad", fts+"_au"
for _, trg := range []string{ai, ad, au} {
if _, err := db.Exec(fmt.Sprintf(`DROP TRIGGER IF EXISTS "%s"`, trg)); err != nil {
return nil, err
}
}
newCols := make([]string, len(cols))
oldCols := make([]string, len(cols))
for i, c := range cols {
newCols[i] = `new."` + c + `"`
oldCols[i] = `old."` + c + `"`
}
newSQL := strings.Join(newCols, ", ")
oldSQL := strings.Join(oldCols, ", ")
aiSQL := fmt.Sprintf(
`CREATE TRIGGER "%s" AFTER INSERT ON "%s" BEGIN
INSERT INTO "%s"(rowid, %s) VALUES (new.id, %s);
END`,
ai, tn, fts, colSQL, newSQL,
)
adSQL := fmt.Sprintf(
`CREATE TRIGGER "%s" AFTER DELETE ON "%s" BEGIN
INSERT INTO "%s"("%s", rowid, %s) VALUES('delete', old.id, %s);
END`,
ad, tn, fts, fts, colSQL, oldSQL,
)
auSQL := fmt.Sprintf(
`CREATE TRIGGER "%s" AFTER UPDATE ON "%s" BEGIN
INSERT INTO "%s"("%s", rowid, %s) VALUES('delete', old.id, %s);
INSERT INTO "%s"(rowid, %s) VALUES (new.id, %s);
END`,
au, tn, fts, fts, colSQL, oldSQL, fts, colSQL, newSQL,
)
for _, stmt := range []string{aiSQL, adSQL, auSQL} {
if _, err := db.Exec(stmt); err != nil {
return nil, err
}
}
if _, err := db.Exec(fmt.Sprintf(`INSERT INTO "%s"("%s") VALUES('rebuild')`, fts, fts)); err != nil {
return nil, err
}
colAny := make([]any, len(cols))
for i, c := range cols {
colAny[i] = c
}
return map[string]any{
"ok": true,
"table": tn,
"fts": fts,
"columns": colAny,
}, nil
}
// Search runs FTS5 MATCH against a table created by FtsCreate.
// Returns {"rows":[…],"count":N}.
func Search(url, tableName, q string, limit int64, name string) (map[string]any, error) {
lower := strings.ToLower(ResolveURL(url))
if strings.HasPrefix(lower, "postgres://") || strings.HasPrefix(lower, "postgresql://") {
return nil, fmt.Errorf("db.search is SQLite-only in this wave")
}
tn, err := ident(tableName)
if err != nil {
return nil, err
}
fts, err := ftsName(tn, name)
if err != nil {
return nil, err
}
q = strings.TrimSpace(q)
if q == "" {
return map[string]any{"rows": []any{}, "count": float64(0)}, nil
}
if limit <= 0 {
limit = 20
} else if limit > 500 {
limit = 500
}
sqlStmt := fmt.Sprintf(
`SELECT t.*, bm25("%s") AS rank
FROM "%s"
JOIN "%s" t ON t.id = "%s".rowid
WHERE "%s" MATCH ?
ORDER BY rank
LIMIT ?`,
fts, fts, tn, fts, fts,
)
db, err := open(url)
if err != nil {
return nil, err
}
rows, err := db.Query(sqlStmt, q, limit)
if err != nil {
return nil, err
}
defer rows.Close()
list, err := scanRows(rows)
if err != nil {
return nil, err
}
if list == nil {
list = []any{}
}
return map[string]any{"rows": list, "count": float64(len(list))}, nil
}