A privacy-first, self-hosted, fully open source personal knowledge management software, written in typescript and golang. (PERSONAL FORK)
onedeuxtriseigo.nullpo.dev
1// SiYuan - Refactor your thinking
2// Copyright (c) 2020-present, b3log.org
3//
4// This program is free software: you can redistribute it and/or modify
5// it under the terms of the GNU Affero General Public License as published by
6// the Free Software Foundation, either version 3 of the License, or
7// (at your option) any later version.
8//
9// This program is distributed in the hope that it will be useful,
10// but WITHOUT ANY WARRANTY; without even the implied warranty of
11// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12// GNU Affero General Public License for more details.
13//
14// You should have received a copy of the GNU Affero General Public License
15// along with this program. If not, see <https://www.gnu.org/licenses/>.
16
17package sql
18
19import (
20 "database/sql"
21 "strconv"
22 "strings"
23
24 "github.com/88250/vitess-sqlparser/sqlparser"
25 "github.com/siyuan-note/logging"
26)
27
28type Span struct {
29 ID string
30 BlockID string
31 RootID string
32 Box string
33 Path string
34 Content string
35 Markdown string
36 Type string
37 IAL string
38}
39
40func SelectSpansRawStmt(stmt string, limit int) (ret []*Span) {
41 parsedStmt, err := sqlparser.Parse(stmt)
42 if err != nil {
43 //logging.LogErrorf("select [%s] failed: %s", stmt, err)
44 return
45 }
46 switch parsedStmt.(type) {
47 case *sqlparser.Select:
48 slct := parsedStmt.(*sqlparser.Select)
49 if nil == slct.Limit {
50 slct.Limit = &sqlparser.Limit{
51 Rowcount: &sqlparser.SQLVal{
52 Type: sqlparser.IntVal,
53 Val: []byte(strconv.Itoa(limit)),
54 },
55 }
56 }
57
58 stmt = sqlparser.String(slct)
59 default:
60 return
61 }
62
63 stmt = strings.ReplaceAll(stmt, "\\'", "''")
64 stmt = strings.ReplaceAll(stmt, "\\\"", "\"")
65 stmt = strings.ReplaceAll(stmt, "\\\\*", "\\*")
66 stmt = strings.ReplaceAll(stmt, "from dual", "")
67 rows, err := query(stmt)
68 if err != nil {
69 if strings.Contains(err.Error(), "syntax error") {
70 return
71 }
72 logging.LogWarnf("sql query [%s] failed: %s", stmt, err)
73 return
74 }
75 defer rows.Close()
76 for rows.Next() {
77 span := scanSpanRows(rows)
78 ret = append(ret, span)
79 }
80 return
81}
82
83func QueryTagSpansByLabel(label string) (ret []*Span) {
84 stmt := "SELECT * FROM spans WHERE type LIKE '%tag%' AND content LIKE '%" + label + "%' GROUP BY block_id"
85 rows, err := query(stmt)
86 if err != nil {
87 logging.LogErrorf("sql query failed: %s", err)
88 return
89 }
90 defer rows.Close()
91 for rows.Next() {
92 span := scanSpanRows(rows)
93 ret = append(ret, span)
94 }
95 return
96}
97
98func QueryTagSpansByKeyword(keyword string, limit int) (ret []*Span) {
99 // 标签搜索支持空格分隔关键字 Tag search supports space-separated keywords https://github.com/siyuan-note/siyuan/issues/14580
100 keywords := strings.Split(keyword, " ")
101 contentLikes := ""
102 for _, k := range keywords {
103 if contentLikes != "" {
104 contentLikes += " AND "
105 }
106 contentLikes += "content LIKE '%" + k + "%'"
107 }
108 stmt := "SELECT * FROM spans WHERE type LIKE '%tag%' AND (" + contentLikes + ") GROUP BY markdown LIMIT " + strconv.Itoa(limit)
109 rows, err := query(stmt)
110 if err != nil {
111 logging.LogErrorf("sql query failed: %s", err)
112 return
113 }
114 defer rows.Close()
115 for rows.Next() {
116 span := scanSpanRows(rows)
117 ret = append(ret, span)
118 }
119 return
120}
121
122func QueryTagSpans(p string) (ret []*Span) {
123 stmt := "SELECT * FROM spans WHERE type LIKE '%tag%'"
124 if "" != p {
125 stmt += " AND path = '" + p + "'"
126 }
127 rows, err := query(stmt)
128 if err != nil {
129 logging.LogErrorf("sql query failed: %s", err)
130 return
131 }
132 defer rows.Close()
133 for rows.Next() {
134 span := scanSpanRows(rows)
135 ret = append(ret, span)
136 }
137 return
138}
139
140func scanSpanRows(rows *sql.Rows) (ret *Span) {
141 var span Span
142 if err := rows.Scan(&span.ID, &span.BlockID, &span.RootID, &span.Box, &span.Path, &span.Content, &span.Markdown, &span.Type, &span.IAL); err != nil {
143 logging.LogErrorf("query scan field failed: %s", err)
144 return
145 }
146 ret = &span
147 return
148}