onedeuxtriseigo.nullpo.dev
A privacy-first, self-hosted, fully open source personal knowledge management software, written in typescript and golang. (PERSONAL FORK)
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 "bytes"
21 "database/sql"
22 "sort"
23 "strings"
24
25 "github.com/88250/gulu"
26 "github.com/88250/lute/parse"
27 "github.com/emirpasic/gods/sets/hashset"
28 "github.com/siyuan-note/logging"
29 "github.com/siyuan-note/siyuan/kernel/search"
30)
31
32func GetRefDuplicatedDefRootIDs() (ret []string) {
33 rows, err := query("SELECT DISTINCT def_block_root_id FROM `refs` GROUP BY def_block_id, def_block_root_id, block_id HAVING COUNT(*) > 1")
34 if err != nil {
35 logging.LogErrorf("sql query failed: %s", err)
36 return
37 }
38 defer rows.Close()
39 for rows.Next() {
40 var id string
41 rows.Scan(&id)
42 ret = append(ret, id)
43 }
44 return
45}
46
47func QueryVirtualRefKeywords(name, alias, anchor, doc bool, searchIgnoreLines, refSearchIgnoreLines []string) (ret []string) {
48 if name {
49 ret = append(ret, queryNames(searchIgnoreLines)...)
50 }
51 if alias {
52 ret = append(ret, queryAliases(searchIgnoreLines)...)
53 }
54 if anchor {
55 ret = append(ret, queryRefTexts(refSearchIgnoreLines)...)
56 }
57 if doc {
58 ret = append(ret, queryDocTitles(searchIgnoreLines)...)
59 }
60 ret = gulu.Str.RemoveDuplicatedElem(ret)
61 sort.SliceStable(ret, func(i, j int) bool {
62 return len(ret[i]) >= len(ret[j])
63 })
64 return
65}
66
67func queryRefTexts(refSearchIgnoreLines []string) (ret []string) {
68 ret = []string{}
69 sqlStmt := "SELECT DISTINCT content FROM refs WHERE 1 = 1"
70 buf := bytes.Buffer{}
71 for _, line := range refSearchIgnoreLines {
72 buf.WriteString(" AND ")
73 buf.WriteString(line)
74 }
75 sqlStmt += buf.String()
76 sqlStmt += " LIMIT 10240"
77 rows, err := query(sqlStmt)
78 if err != nil {
79 logging.LogErrorf("sql query failed: %s", sqlStmt, err)
80 return
81 }
82 defer rows.Close()
83
84 set := hashset.New()
85 for rows.Next() {
86 var refText string
87 rows.Scan(&refText)
88 if "" == strings.TrimSpace(refText) {
89 continue
90 }
91 set.Add(refText)
92 }
93 for _, refText := range set.Values() {
94 ret = append(ret, refText.(string))
95 }
96 return
97}
98
99func QueryRefCount(defIDs []string) (ret map[string]int) {
100 ret = map[string]int{}
101 ids := strings.Join(defIDs, "','")
102 ids = "('" + ids + "')"
103 rows, err := query("SELECT def_block_id, COUNT(*) AS ref_cnt FROM refs WHERE def_block_id IN " + ids + " GROUP BY def_block_id")
104 if err != nil {
105 logging.LogErrorf("sql query failed: %s", err)
106 return
107 }
108 defer rows.Close()
109 for rows.Next() {
110 var id string
111 var cnt int
112 if err = rows.Scan(&id, &cnt); err != nil {
113 logging.LogErrorf("query scan field failed: %s", err)
114 return
115 }
116 ret[id] = cnt
117 }
118 return
119}
120
121func QueryRootChildrenRefCount(defRootID string) (ret map[string]int) {
122 ret = map[string]int{}
123 rows, err := query("SELECT def_block_id, COUNT(*) AS ref_cnt FROM refs WHERE def_block_root_id = ? GROUP BY def_block_id", defRootID)
124 if err != nil {
125 logging.LogErrorf("sql query failed: %s", err)
126 return
127 }
128 defer rows.Close()
129 for rows.Next() {
130 var id string
131 var cnt int
132 if err = rows.Scan(&id, &cnt); err != nil {
133 logging.LogErrorf("query scan field failed: %s", err)
134 return
135 }
136 ret[id] = cnt
137 }
138 return
139}
140
141func QueryRootBlockRefCount() (ret map[string]int) {
142 ret = map[string]int{}
143
144 rows, err := query("SELECT def_block_root_id, COUNT(DISTINCT block_id) AS ref_cnt FROM refs GROUP BY def_block_root_id")
145 if err != nil {
146 logging.LogErrorf("sql query failed: %s", err)
147 return
148 }
149 defer rows.Close()
150 for rows.Next() {
151 var id string
152 var cnt int
153 if err = rows.Scan(&id, &cnt); err != nil {
154 logging.LogErrorf("query scan field failed: %s", err)
155 return
156 }
157 ret[id] = cnt
158 }
159 return
160}
161
162func QueryDefRootBlocksByRefRootID(refRootID string) (ret []*Block) {
163 rows, err := query("SELECT * FROM blocks WHERE id IN (SELECT DISTINCT def_block_root_id FROM refs WHERE root_id = ?)", refRootID)
164 if err != nil {
165 logging.LogErrorf("sql query failed: %s", err)
166 return
167 }
168 defer rows.Close()
169 for rows.Next() {
170 if block := scanBlockRows(rows); nil != block {
171 ret = append(ret, block)
172 }
173 }
174 return
175}
176
177func QueryRefRootBlocksByDefRootIDs(defRootIDs []string) (ret map[string][]*Block) {
178 ret = map[string][]*Block{}
179
180 stmt := "SELECT r.def_block_root_id, b.* FROM refs AS r, blocks AS b ON r.def_block_root_id IN ('" + strings.Join(defRootIDs, "','") + "')" + " AND b.id = r.root_id"
181 rows, err := query(stmt)
182 if err != nil {
183 logging.LogErrorf("sql query failed: %s", err)
184 return
185 }
186 defer rows.Close()
187 for rows.Next() {
188 var block Block
189 var defRootID string
190 if err := rows.Scan(&defRootID, &block.ID, &block.ParentID, &block.RootID, &block.Hash, &block.Box, &block.Path, &block.HPath, &block.Name, &block.Alias, &block.Memo, &block.Tag, &block.Content, &block.FContent, &block.Markdown, &block.Length, &block.Type, &block.SubType, &block.IAL, &block.Sort, &block.Created, &block.Updated); err != nil {
191 logging.LogErrorf("query scan field failed: %s\n%s", err, logging.ShortStack())
192 return
193 }
194
195 if nil == ret[defRootID] {
196 ret[defRootID] = []*Block{&block}
197 } else {
198 ret[defRootID] = append(ret[defRootID], &block)
199 }
200 }
201 return
202}
203
204func GetRefText(defBlockID string) (ret string) {
205 ret = getRefText(defBlockID)
206 ret = strings.ReplaceAll(ret, search.SearchMarkLeft, "")
207 ret = strings.ReplaceAll(ret, search.SearchMarkRight, "")
208 return
209}
210
211func getRefText(defBlockID string) string {
212 block := GetBlock(defBlockID)
213 if nil == block {
214 if strings.HasPrefix(defBlockID, "assets") {
215 return defBlockID
216 }
217 return "block not found"
218 }
219
220 if "" != block.Name {
221 return block.Name
222 }
223
224 switch block.Type {
225 case "d":
226 return block.Content
227 case "query_embed":
228 return "Query Embed Block " + block.Markdown
229 case "av":
230 return "Database " + block.Markdown
231 case "iframe":
232 return "IFrame " + block.Markdown
233 case "tb":
234 return "Thematic Break"
235 case "video":
236 return "Video " + block.Markdown
237 case "audio":
238 return "Audio " + block.Markdown
239 }
240
241 if block.IsContainerBlock() {
242 subTree := parse.Parse("", []byte(block.Markdown), luteEngine.ParseOptions)
243 return GetContainerText(subTree.Root)
244 }
245 return block.Content
246}
247
248func QueryBlockDefIDsByRefText(refText string, excludeIDs []string) (ret []string) {
249 ret = queryDefIDsByDefText(refText, excludeIDs)
250 ret = append(ret, queryDefIDsByNameAlias(refText, excludeIDs)...)
251 ret = append(ret, queryDocIDsByTitle(refText, excludeIDs)...)
252 ret = gulu.Str.RemoveDuplicatedElem(ret)
253 return
254}
255
256func queryDefIDsByDefText(keyword string, excludeIDs []string) (ret []string) {
257 ret = []string{}
258 notIn := "('" + strings.Join(excludeIDs, "','") + "')"
259 q := "SELECT DISTINCT(def_block_id) FROM refs WHERE content LIKE ? AND def_block_id NOT IN " + notIn
260 if caseSensitive {
261 q = "SELECT DISTINCT(def_block_id) FROM refs WHERE content = ? AND def_block_id NOT IN " + notIn
262 }
263 rows, err := query(q, keyword)
264 if err != nil {
265 logging.LogErrorf("sql query failed: %s", err)
266 return
267 }
268 defer rows.Close()
269 for rows.Next() {
270 var id string
271 if err = rows.Scan(&id); err != nil {
272 logging.LogErrorf("query scan field failed: %s", err)
273 return
274 }
275 ret = append(ret, id)
276 }
277 return
278}
279
280func queryDefIDsByNameAlias(keyword string, excludeIDs []string) (ret []string) {
281 ret = []string{}
282 notIn := "('" + strings.Join(excludeIDs, "','") + "')"
283 rows, err := query("SELECT DISTINCT(id), name, alias FROM blocks WHERE (name = ? OR alias LIKE ?) AND id NOT IN "+notIn, keyword, "%"+keyword+"%")
284 if err != nil {
285 logging.LogErrorf("sql query failed: %s", err)
286 return
287 }
288 defer rows.Close()
289 for rows.Next() {
290 var id, name, alias string
291 if err = rows.Scan(&id, &name, &alias); err != nil {
292 logging.LogErrorf("query scan field failed: %s", err)
293 return
294 }
295 if name == keyword {
296 ret = append(ret, id)
297 continue
298 }
299
300 var hitAlias bool
301 aliases := strings.Split(alias, ",")
302 for _, a := range aliases {
303 if "" == a {
304 continue
305 }
306 if keyword == a {
307 hitAlias = true
308 }
309 }
310 if strings.Contains(alias, keyword) && !hitAlias {
311 continue
312 }
313 ret = append(ret, id)
314 }
315 return
316}
317
318func QueryChildRefDefIDsByRootDefID(rootDefID string) (ret map[string][]string) {
319 ret = map[string][]string{}
320 rows, err := query("SELECT block_id, def_block_id FROM refs WHERE def_block_root_id = ?", rootDefID)
321 if err != nil {
322 logging.LogErrorf("sql query failed: %s", err)
323 return
324 }
325 defer rows.Close()
326 for rows.Next() {
327 var defID, refID string
328 if err = rows.Scan(&defID, &refID); err != nil {
329 logging.LogErrorf("query scan field failed: %s", err)
330 return
331 }
332 if nil == ret[defID] {
333 ret[defID] = []string{refID}
334 } else {
335 ret[defID] = append(ret[defID], refID)
336 }
337 }
338 return
339}
340
341func QueryChildDefIDsByRootDefID(rootDefID string) (ret []string) {
342 ret = []string{}
343 rows, err := query("SELECT DISTINCT(def_block_id) FROM refs WHERE def_block_root_id = ?", rootDefID)
344 if err != nil {
345 logging.LogErrorf("sql query failed: %s", err)
346 return
347 }
348 defer rows.Close()
349 for rows.Next() {
350 var id string
351 if err = rows.Scan(&id); err != nil {
352 logging.LogErrorf("query scan field failed: %s", err)
353 return
354 }
355 ret = append(ret, id)
356 }
357 return
358}
359
360func QueryRefIDsByDefID(defID string, containChildren bool) (refIDs []string) {
361 refIDs = []string{}
362 var rows *sql.Rows
363 var err error
364 if containChildren {
365 rows, err = query("SELECT DISTINCT block_id FROM refs WHERE def_block_root_id = ?", defID)
366 } else {
367 rows, err = query("SELECT DISTINCT block_id FROM refs WHERE def_block_id = ?", defID)
368 }
369 if err != nil {
370 logging.LogErrorf("sql query failed: %s", err)
371 return
372 }
373 defer rows.Close()
374 for rows.Next() {
375 var id string
376 if err = rows.Scan(&id); err != nil {
377 logging.LogErrorf("query scan field failed: %s", err)
378 return
379 }
380 refIDs = append(refIDs, id)
381 }
382 return
383}
384
385func QueryRefsRecent(onlyDoc bool, typeFilter string, ignoreLines []string) (ret []*Ref) {
386 stmt := "SELECT r.* FROM refs AS r, blocks AS b WHERE b.id = r.def_block_id AND b.type IN " + typeFilter
387 if onlyDoc {
388 stmt = "SELECT r.* FROM refs AS r, blocks AS b WHERE b.id = r.def_block_id AND b.type = 'd'"
389 }
390 if 0 < len(ignoreLines) {
391 // Support ignore search results https://github.com/siyuan-note/siyuan/issues/10089
392 buf := bytes.Buffer{}
393 for _, line := range ignoreLines {
394 buf.WriteString(" AND ")
395 buf.WriteString(line)
396 }
397 stmt += buf.String()
398 }
399 stmt += " GROUP BY r.def_block_id ORDER BY r.id DESC LIMIT 32"
400 rows, err := query(stmt)
401 if err != nil {
402 logging.LogErrorf("sql query failed: %s", err)
403 return
404 }
405 defer rows.Close()
406 for rows.Next() {
407 ref := scanRefRows(rows)
408 ret = append(ret, ref)
409 }
410 return
411}
412
413func QueryRefsByDefID(defBlockID string, containChildren bool) (ret []*Ref) {
414 var rows *sql.Rows
415 var err error
416 if containChildren {
417 blockIDs := queryBlockChildrenIDs(defBlockID)
418 var params []string
419 for _, id := range blockIDs {
420 params = append(params, "\""+id+"\"")
421 }
422 rows, err = query("SELECT * FROM refs WHERE def_block_id IN (" + strings.Join(params, ",") + ")")
423 } else {
424 rows, err = query("SELECT * FROM refs WHERE def_block_id = ?", defBlockID)
425 }
426 if err != nil {
427 logging.LogErrorf("sql query failed: %s", err)
428 return
429 }
430 defer rows.Close()
431 for rows.Next() {
432 ref := scanRefRows(rows)
433 ret = append(ret, ref)
434 }
435 return
436}
437
438func QueryRefsByDefIDRefID(defBlockID, refBlockID string) (ret []*Ref) {
439 stmt := "SELECT * FROM refs WHERE def_block_id = ? AND block_id = ?"
440 rows, err := query(stmt, defBlockID, refBlockID)
441 if err != nil {
442 logging.LogErrorf("sql query failed: %s", err)
443 return
444 }
445 defer rows.Close()
446 for rows.Next() {
447 ref := scanRefRows(rows)
448 ret = append(ret, ref)
449 }
450 return
451}
452
453func DefRefs(condition string, limit int) (ret []map[*Block]*Block) {
454 ret = []map[*Block]*Block{}
455 stmt := "SELECT ref.*, r.block_id || '@' || r.def_block_id AS rel FROM blocks AS ref, refs AS r WHERE ref.id = r.block_id"
456 if "" != condition {
457 stmt += " AND " + condition
458 }
459
460 rows, err := query(stmt)
461 if err != nil {
462 logging.LogErrorf("sql query failed: %s", err)
463 return
464 }
465 defer rows.Close()
466 refs := map[string]*Block{}
467 for rows.Next() {
468 var ref Block
469 var rel string
470 if err = rows.Scan(&ref.ID, &ref.ParentID, &ref.RootID, &ref.Hash, &ref.Box, &ref.Path, &ref.HPath, &ref.Name, &ref.Alias, &ref.Memo, &ref.Tag, &ref.Content, &ref.FContent, &ref.Markdown, &ref.Length, &ref.Type, &ref.SubType, &ref.IAL, &ref.Sort, &ref.Created, &ref.Updated,
471 &rel); err != nil {
472 logging.LogErrorf("query scan field failed: %s", err)
473 return
474 }
475 refs[rel] = &ref
476 }
477
478 rows, err = query("SELECT def.* FROM blocks AS def, refs AS r WHERE def.id = r.def_block_id LIMIT ?", limit)
479 if err != nil {
480 logging.LogErrorf("sql query failed: %s", err)
481 return
482 }
483 defer rows.Close()
484 defs := map[string]*Block{}
485 for rows.Next() {
486 if def := scanBlockRows(rows); nil != def {
487 defs[def.ID] = def
488 }
489 }
490
491 for rel, ref := range refs {
492 defID := strings.Split(rel, "@")[1]
493 def := defs[defID]
494 if nil == def {
495 continue
496 }
497 defRef := map[*Block]*Block{}
498 defRef[def] = ref
499 ret = append(ret, defRef)
500 }
501 return
502}
503
504func scanRefRows(rows *sql.Rows) (ret *Ref) {
505 var ref Ref
506 if err := rows.Scan(&ref.ID, &ref.DefBlockID, &ref.DefBlockParentID, &ref.DefBlockRootID, &ref.DefBlockPath, &ref.BlockID, &ref.RootID, &ref.Box, &ref.Path, &ref.Content, &ref.Markdown, &ref.Type); err != nil {
507 logging.LogErrorf("query scan field failed: %s", err)
508 return
509 }
510 ret = &ref
511 return
512}