Skip to content

An open-source, privacy-first, self-hosted knowledge workspace where humans and AI agents work together 开源、隐私优先、自托管的知识工作空间,让人与智能体在此协作

5.0 KB
Raw Blame
// SiYuan - https://github.com/siyuan-note/siyuan
// Copyright (c) 2020-present, b3log.org
// SPDX-License-Identifier: AGPL-3.0-or-later

package sql

import (
	"database/sql"
	"errors"
	"math"
	"strconv"
	"strings"

	"github.com/88250/vitess-sqlparser/sqlparser"
	"github.com/siyuan-note/logging"
)

func QueryAssetContentNoLimit(stmt string) (ret []map[string]any, err error) {
	if err = CheckSingleStatement(stmt); err != nil {
		return
	}
	if err = CheckAssetContentReadonlyStatement(stmt); err != nil {
		return
	}
	return queryAssetContentRawStmt(stmt, nil, math.MaxInt)
}

// QueryAssetContentNoLimitArgs 使用绑定参数查询资源文件内容数据库。
func QueryAssetContentNoLimitArgs(stmt string, args ...any) (ret []map[string]any, err error) {
	return queryAssetContentRawStmt(stmt, args, math.MaxInt)
}

func queryAssetContentRawStmt(stmt string, args []any, limit int) (ret []map[string]any, err error) {
	rows, err := queryAssetContent(stmt, args...)
	if err != nil {
		if strings.Contains(err.Error(), "syntax error") {
			return
		}
		return
	}
	defer rows.Close()

	cols, err := rows.Columns()
	if err != nil || nil == cols {
		return
	}

	noLimit := !containsLimitClause(stmt)
	var count int
	for rows.Next() {
		columns := make([]any, len(cols))
		columnPointers := make([]any, len(cols))
		for i := range columns {
			columnPointers[i] = &columns[i]
		}

		if err = rows.Scan(columnPointers...); err != nil {
			return
		}

		m := make(map[string]any)
		for i, colName := range cols {
			val := columnPointers[i].(*any)
			m[colName] = *val
		}

		ret = append(ret, m)
		count++
		if noLimit && count >= limit {
			break
		}
	}
	return
}

func SelectAssetContentsRawStmt(stmt string, page, limit int) (ret []*AssetContent) {
	if CheckSingleStatement(stmt) != nil || CheckAssetContentReadonlyStatement(stmt) != nil {
		return
	}
	parsedStmt, err := sqlparser.Parse(stmt)
	if err != nil {
		return selectAssetContentsRawStmt(stmt, nil, limit)
	}

	switch parsedStmt.(type) {
	case *sqlparser.Select:
		slct := parsedStmt.(*sqlparser.Select)
		if nil == slct.Limit {
			slct.Limit = &sqlparser.Limit{
				Rowcount: &sqlparser.SQLVal{
					Type: sqlparser.IntVal,
					Val:  []byte(strconv.Itoa(limit)),
				},
			}
			slct.Limit.Offset = &sqlparser.SQLVal{
				Type: sqlparser.IntVal,
				Val:  []byte(strconv.Itoa((page - 1) * limit)),
			}
		} else {
			if nil != slct.Limit.Rowcount && 0 < len(slct.Limit.Rowcount.(*sqlparser.SQLVal).Val) {
				limit, _ = strconv.Atoi(string(slct.Limit.Rowcount.(*sqlparser.SQLVal).Val))
				if 0 >= limit {
					limit = 32
				}
			}

			slct.Limit.Rowcount = &sqlparser.SQLVal{
				Type: sqlparser.IntVal,
				Val:  []byte(strconv.Itoa(limit)),
			}
			slct.Limit.Offset = &sqlparser.SQLVal{
				Type: sqlparser.IntVal,
				Val:  []byte(strconv.Itoa((page - 1) * limit)),
			}
		}

		stmt = sqlparser.String(slct)
	default:
		return
	}

	stmt = strings.ReplaceAll(stmt, "\\'", "''")
	stmt = strings.ReplaceAll(stmt, "\\\"", "\"")
	stmt = strings.ReplaceAll(stmt, "\\\\*", "\\*")
	stmt = strings.ReplaceAll(stmt, "from dual", "")
	rows, err := queryAssetContent(stmt)
	if err != nil {
		if strings.Contains(err.Error(), "syntax error") {
			return
		}
		logging.LogWarnf("sql query [%s] failed: %s", stmt, err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		if ac := scanAssetContentRows(rows); nil != ac {
			ret = append(ret, ac)
		}
	}
	return
}

func SelectAssetContentsRawStmtNoParse(stmt string, limit int) (ret []*AssetContent) {
	if CheckSingleStatement(stmt) != nil || CheckAssetContentReadonlyStatement(stmt) != nil {
		return
	}
	return selectAssetContentsRawStmt(stmt, nil, limit)
}

// SelectAssetContentsRawStmtNoParseArgs 使用绑定参数查询资源文件内容,并保留调用方构造的 SQL。
func SelectAssetContentsRawStmtNoParseArgs(stmt string, args []any, limit int) (ret []*AssetContent) {
	return selectAssetContentsRawStmt(stmt, args, limit)
}

func selectAssetContentsRawStmt(stmt string, args []any, limit int) (ret []*AssetContent) {
	rows, err := queryAssetContent(stmt, args...)
	if err != nil {
		if strings.Contains(err.Error(), "syntax error") {
			return
		}
		return
	}
	defer rows.Close()

	noLimit := !containsLimitClause(stmt)
	var count, errCount int
	for rows.Next() {
		count++
		if ac := scanAssetContentRows(rows); nil != ac {
			ret = append(ret, ac)
		} else {
			logging.LogWarnf("raw sql query [%s] failed", stmt)
			errCount++
		}

		if (noLimit && count >= limit) || 0 < errCount {
			break
		}
	}
	return
}

func scanAssetContentRows(rows *sql.Rows) (ret *AssetContent) {
	var ac AssetContent
	if err := rows.Scan(&ac.ID, &ac.Name, &ac.Ext, &ac.Path, &ac.Size, &ac.Updated, &ac.Content); err != nil {
		logging.LogErrorf("query scan field failed: %s\n%s", err, logging.ShortStack())
		return
	}
	ret = &ac
	return
}

func queryAssetContent(query string, args ...any) (*sql.Rows, error) {
	query = strings.TrimSpace(query)
	if "" == query {
		return nil, errors.New("statement is empty")
	}
	if nil == assetContentDB {
		return nil, errors.New("database is nil")
	}
	return assetContentDB.Query(query, args...)
}