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 "math"
23 "regexp"
24 "sort"
25 "strconv"
26 "strings"
27
28 "github.com/88250/lute/ast"
29 "github.com/88250/vitess-sqlparser/sqlparser"
30 "github.com/emirpasic/gods/sets/hashset"
31 sqlparser2 "github.com/rqlite/sql"
32 "github.com/siyuan-note/logging"
33 "github.com/siyuan-note/siyuan/kernel/treenode"
34)
35
36func QueryEmptyContentEmbedBlocks() (ret []*Block) {
37 stmt := "SELECT * FROM blocks WHERE type = 'query_embed' AND content = ''"
38 rows, err := query(stmt)
39 if err != nil {
40 logging.LogErrorf("sql query [%s] failed: %s", stmt, err)
41 return
42 }
43 defer rows.Close()
44 for rows.Next() {
45 if block := scanBlockRows(rows); nil != block {
46 ret = append(ret, block)
47 }
48 }
49 return
50}
51
52func queryBlockHashes(rootID string) (ret map[string]string) {
53 stmt := "SELECT id, hash FROM blocks WHERE root_id = ?"
54 rows, err := query(stmt, rootID)
55 if err != nil {
56 logging.LogErrorf("sql query [%s] failed: %s", stmt, err)
57 return
58 }
59 defer rows.Close()
60 ret = map[string]string{}
61 for rows.Next() {
62 var id, hash string
63 if err = rows.Scan(&id, &hash); err != nil {
64 logging.LogErrorf("query scan field failed: %s", err)
65 return
66 }
67 ret[id] = hash
68 }
69 return
70}
71
72func QueryRootBlockByCondition(condition string, limit int) (ret []*Block) {
73 sqlStmt := "SELECT *, length(hpath) - length(replace(hpath, '/', '')) AS lv FROM blocks WHERE type = 'd' AND " + condition + " ORDER BY box DESC,lv ASC LIMIT " + strconv.Itoa(limit)
74 rows, err := query(sqlStmt)
75 if err != nil {
76 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
77 return
78 }
79 defer rows.Close()
80 for rows.Next() {
81 var block Block
82 var sepCount int
83 if err = rows.Scan(&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, &sepCount); err != nil {
84 logging.LogErrorf("query scan field failed: %s", err)
85 return
86 }
87 ret = append(ret, &block)
88 }
89 return
90}
91
92func (block *Block) IsContainerBlock() bool {
93 switch block.Type {
94 case "d", "b", "l", "i", "s":
95 return true
96 }
97 return false
98}
99
100func queryBlockChildrenIDs(id string) (ret []string) {
101 ret = append(ret, id)
102 childIDs := queryBlockIDByParentID(id)
103 for _, childID := range childIDs {
104 ret = append(ret, queryBlockChildrenIDs(childID)...)
105 }
106 return
107}
108
109func queryBlockIDByParentID(parentID string) (ret []string) {
110 sqlStmt := "SELECT id FROM blocks WHERE parent_id = ?"
111 rows, err := query(sqlStmt, parentID)
112 if err != nil {
113 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
114 return
115 }
116 defer rows.Close()
117 for rows.Next() {
118 var id string
119 rows.Scan(&id)
120 ret = append(ret, id)
121 }
122 return
123}
124
125func QueryBlockAliases(rootID string) (ret []string) {
126 sqlStmt := "SELECT alias FROM blocks WHERE root_id = ? AND alias != ''"
127 rows, err := query(sqlStmt, rootID)
128 if err != nil {
129 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
130 return
131 }
132 defer rows.Close()
133 var aliasesRows []string
134 for rows.Next() {
135 var name string
136 rows.Scan(&name)
137 aliasesRows = append(aliasesRows, name)
138 }
139
140 for _, aliasStr := range aliasesRows {
141 aliases := strings.Split(aliasStr, ",")
142 for _, alias := range aliases {
143 var exist bool
144 for _, retAlias := range ret {
145 if retAlias == alias {
146 exist = true
147 }
148 }
149 if !exist {
150 ret = append(ret, alias)
151 }
152 }
153 }
154 return
155}
156
157func queryNames(searchIgnoreLines []string) (ret []string) {
158 ret = []string{}
159 sqlStmt := "SELECT name FROM blocks WHERE name != ''"
160 buf := bytes.Buffer{}
161 for _, line := range searchIgnoreLines {
162 buf.WriteString(" AND ")
163 buf.WriteString(line)
164 }
165 sqlStmt += buf.String()
166 sqlStmt += " LIMIT ?"
167 rows, err := query(sqlStmt, 10240)
168 if err != nil {
169 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
170 return
171 }
172 defer rows.Close()
173
174 var namesRows []string
175 for rows.Next() {
176 var name string
177 rows.Scan(&name)
178 namesRows = append(namesRows, name)
179 }
180
181 set := hashset.New()
182 for _, namesStr := range namesRows {
183 names := strings.Split(namesStr, ",")
184 for _, name := range names {
185 if "" == strings.TrimSpace(name) {
186 continue
187 }
188 set.Add(name)
189 }
190 }
191 for _, v := range set.Values() {
192 ret = append(ret, v.(string))
193 }
194 return
195}
196
197func queryAliases(searchIgnoreLines []string) (ret []string) {
198 ret = []string{}
199 sqlStmt := "SELECT alias FROM blocks WHERE alias != ''"
200 buf := bytes.Buffer{}
201 for _, line := range searchIgnoreLines {
202 buf.WriteString(" AND ")
203 buf.WriteString(line)
204 }
205 sqlStmt += buf.String()
206 sqlStmt += " LIMIT ?"
207 rows, err := query(sqlStmt, 10240)
208 if err != nil {
209 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
210 return
211 }
212 defer rows.Close()
213
214 var aliasesRows []string
215 for rows.Next() {
216 var alias string
217 rows.Scan(&alias)
218 aliasesRows = append(aliasesRows, alias)
219 }
220
221 set := hashset.New()
222 for _, aliasStr := range aliasesRows {
223 aliases := strings.Split(aliasStr, ",")
224 for _, alias := range aliases {
225 if "" == strings.TrimSpace(alias) {
226 continue
227 }
228 set.Add(alias)
229 }
230 }
231 for _, v := range set.Values() {
232 ret = append(ret, v.(string))
233 }
234 return
235}
236
237func queryDocIDsByTitle(title string, excludeIDs []string) (ret []string) {
238 ret = []string{}
239 notIn := "('" + strings.Join(excludeIDs, "','") + "')"
240
241 sqlStmt := "SELECT id FROM blocks WHERE type = 'd' AND content LIKE ? AND id NOT IN " + notIn + " LIMIT ?"
242 if caseSensitive {
243 sqlStmt = "SELECT id FROM blocks WHERE type = 'd' AND content = ? AND id NOT IN " + notIn + " LIMIT ?"
244 }
245 rows, err := query(sqlStmt, title, 32)
246 if err != nil {
247 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
248 return
249 }
250 defer rows.Close()
251
252 set := hashset.New()
253 for rows.Next() {
254 var id string
255 rows.Scan(&id)
256 set.Add(id)
257 }
258
259 for _, v := range set.Values() {
260 ret = append(ret, v.(string))
261 }
262 return
263}
264
265func queryDocTitles(searchIgnoreLines []string) (ret []string) {
266 ret = []string{}
267 sqlStmt := "SELECT content FROM blocks WHERE type = 'd'"
268 buf := bytes.Buffer{}
269 for _, line := range searchIgnoreLines {
270 buf.WriteString(" AND ")
271 buf.WriteString(line)
272 }
273 sqlStmt += buf.String()
274 rows, err := query(sqlStmt)
275 if err != nil {
276 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
277 return
278 }
279 defer rows.Close()
280
281 var docNamesRows []string
282 for rows.Next() {
283 var name string
284 rows.Scan(&name)
285 docNamesRows = append(docNamesRows, name)
286 }
287
288 set := hashset.New()
289 for _, nameStr := range docNamesRows {
290 names := strings.Split(nameStr, ",")
291 for _, name := range names {
292 if "" == strings.TrimSpace(name) {
293 continue
294 }
295 set.Add(name)
296 }
297 }
298 for _, v := range set.Values() {
299 ret = append(ret, v.(string))
300 }
301 return
302}
303
304func QueryBlockNamesByRootID(rootID string) (ret []string) {
305 sqlStmt := "SELECT DISTINCT name FROM blocks WHERE root_id = ? AND name != ''"
306 rows, err := query(sqlStmt, rootID)
307 if err != nil {
308 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
309 return
310 }
311 defer rows.Close()
312 for rows.Next() {
313 var name string
314 rows.Scan(&name)
315 ret = append(ret, name)
316 }
317 return
318}
319
320func QueryBookmarkBlocksByKeyword(bookmark string) (ret []*Block) {
321 sqlStmt := "SELECT * FROM blocks WHERE ial LIKE ?"
322 rows, err := query(sqlStmt, "%bookmark=%")
323 if err != nil {
324 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
325 return
326 }
327 defer rows.Close()
328 for rows.Next() {
329 if block := scanBlockRows(rows); nil != block {
330 ret = append(ret, block)
331 }
332 }
333 return
334}
335
336func QueryBookmarkBlocks() (ret []*Block) {
337 sqlStmt := "SELECT * FROM blocks WHERE ial LIKE ?"
338 rows, err := query(sqlStmt, "%bookmark=%")
339 if err != nil {
340 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
341 return
342 }
343 defer rows.Close()
344 for rows.Next() {
345 if block := scanBlockRows(rows); nil != block {
346 ret = append(ret, block)
347 }
348 }
349 return
350}
351
352func QueryBookmarkLabels() (ret []string) {
353 ret = []string{}
354 sqlStmt := "SELECT * FROM blocks WHERE ial LIKE ?"
355 rows, err := query(sqlStmt, "%bookmark=%")
356 if err != nil {
357 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
358 return
359 }
360 defer rows.Close()
361 labels := map[string]bool{}
362 for rows.Next() {
363 if block := scanBlockRows(rows); nil != block {
364 if v := ialAttr(block.IAL, "bookmark"); "" != v {
365 labels[v] = true
366 }
367 }
368 }
369
370 for label := range labels {
371 ret = append(ret, label)
372 }
373 sort.Strings(ret)
374 return
375}
376
377func QueryNoLimit(stmt string) (ret []map[string]interface{}, err error) {
378 return queryRawStmt(stmt, math.MaxInt)
379}
380
381func Query(stmt string, limit int) (ret []map[string]interface{}, err error) {
382 // Kernel API `/api/query/sql` support `||` operator https://github.com/siyuan-note/siyuan/issues/9662
383 // 这里为了支持 || 操作符，使用了另一个 sql 解析器，但是这个解析器无法处理 UNION https://github.com/siyuan-note/siyuan/issues/8226
384 // 考虑到 UNION 的使用场景不多，这里还是以支持 || 操作符为主
385 p := sqlparser2.NewParser(strings.NewReader(stmt))
386 parsedStmt2, err := p.ParseStatement()
387 if err != nil {
388 if !strings.Contains(stmt, "||") {
389 // 这个解析器无法处理 || 连接字符串操作符
390 parsedStmt, err2 := sqlparser.Parse(stmt)
391 if nil != err2 {
392 return queryRawStmt(stmt, limit)
393 }
394
395 switch parsedStmt.(type) {
396 case *sqlparser.Select:
397 limitClause := getLimitClause(parsedStmt, limit)
398 slct := parsedStmt.(*sqlparser.Select)
399 slct.Limit = limitClause
400 stmt = sqlparser.String(slct)
401 case *sqlparser.Union:
402 // Kernel API `/api/query/sql` support `UNION` statement https://github.com/siyuan-note/siyuan/issues/8226
403 limitClause := getLimitClause(parsedStmt, limit)
404 union := parsedStmt.(*sqlparser.Union)
405 union.Limit = limitClause
406 stmt = sqlparser.String(union)
407 default:
408 return queryRawStmt(stmt, limit)
409 }
410 } else {
411 return queryRawStmt(stmt, limit)
412 }
413 } else {
414 switch parsedStmt2.(type) {
415 case *sqlparser2.SelectStatement:
416 slct := parsedStmt2.(*sqlparser2.SelectStatement)
417 if nil == slct.LimitExpr {
418 slct.LimitExpr = &sqlparser2.NumberLit{Value: strconv.Itoa(limit)}
419 }
420 stmt = slct.String()
421 default:
422 return queryRawStmt(stmt, limit)
423 }
424 }
425
426 ret = []map[string]interface{}{}
427 rows, err := query(stmt)
428 if err != nil {
429 logging.LogWarnf("sql query [%s] failed: %s", stmt, err)
430 return
431 }
432 defer rows.Close()
433
434 cols, _ := rows.Columns()
435 if nil == cols {
436 return
437 }
438
439 for rows.Next() {
440 columns := make([]interface{}, len(cols))
441 columnPointers := make([]interface{}, len(cols))
442 for i := range columns {
443 columnPointers[i] = &columns[i]
444 }
445
446 if err = rows.Scan(columnPointers...); err != nil {
447 return
448 }
449
450 m := make(map[string]interface{})
451 for i, colName := range cols {
452 val := columnPointers[i].(*interface{})
453 m[colName] = *val
454 }
455 ret = append(ret, m)
456 }
457 return
458}
459
460func ToBlocks(result []map[string]interface{}) (ret []*Block) {
461 for _, row := range result {
462 b := &Block{
463 ID: row["id"].(string),
464 ParentID: row["parent_id"].(string),
465 RootID: row["root_id"].(string),
466 Hash: row["hash"].(string),
467 Box: row["box"].(string),
468 Path: row["path"].(string),
469 HPath: row["hpath"].(string),
470 Name: row["name"].(string),
471 Alias: row["alias"].(string),
472 Memo: row["memo"].(string),
473 Tag: row["tag"].(string),
474 Content: row["content"].(string),
475 FContent: row["fcontent"].(string),
476 Markdown: row["markdown"].(string),
477 Length: int(row["length"].(int64)),
478 Type: row["type"].(string),
479 SubType: row["subtype"].(string),
480 IAL: row["ial"].(string),
481 Sort: int(row["sort"].(int64)),
482 Created: row["created"].(string),
483 Updated: row["updated"].(string),
484 }
485 ret = append(ret, b)
486 }
487 return
488}
489
490func getLimitClause(parsedStmt sqlparser.Statement, limit int) (ret *sqlparser.Limit) {
491 switch parsedStmt.(type) {
492 case *sqlparser.Select:
493 slct := parsedStmt.(*sqlparser.Select)
494 if nil != slct.Limit {
495 ret = slct.Limit
496 }
497 case *sqlparser.Union:
498 union := parsedStmt.(*sqlparser.Union)
499 if nil != union.Limit {
500 ret = union.Limit
501 }
502 }
503
504 if nil == ret || nil == ret.Rowcount {
505 ret = &sqlparser.Limit{
506 Rowcount: &sqlparser.SQLVal{
507 Type: sqlparser.IntVal,
508 Val: []byte(strconv.Itoa(limit)),
509 },
510 }
511 }
512 return
513}
514
515func queryRawStmt(stmt string, limit int) (ret []map[string]interface{}, err error) {
516 rows, err := query(stmt)
517 if err != nil {
518 if strings.Contains(err.Error(), "syntax error") {
519 return
520 }
521 return
522 }
523 defer rows.Close()
524
525 cols, err := rows.Columns()
526 if err != nil || nil == cols {
527 return
528 }
529
530 noLimit := !containsLimitClause(stmt)
531 var count, errCount int
532 for rows.Next() {
533 columns := make([]interface{}, len(cols))
534 columnPointers := make([]interface{}, len(cols))
535 for i := range columns {
536 columnPointers[i] = &columns[i]
537 }
538
539 if err = rows.Scan(columnPointers...); err != nil {
540 return
541 }
542
543 m := make(map[string]interface{})
544 for i, colName := range cols {
545 val := columnPointers[i].(*interface{})
546 m[colName] = *val
547 }
548
549 ret = append(ret, m)
550 count++
551 if (noLimit && limit < count) || 0 < errCount {
552 break
553 }
554 }
555 return
556}
557
558func SelectBlocksRawStmtNoParse(stmt string, limit int) (ret []*Block) {
559 return selectBlocksRawStmt(stmt, limit)
560}
561
562func SelectBlocksRawStmt(stmt string, page, limit int) (ret []*Block) {
563 parsedStmt, err := sqlparser.Parse(stmt)
564 if err != nil {
565 return selectBlocksRawStmt(stmt, limit)
566 }
567
568 switch parsedStmt.(type) {
569 case *sqlparser.Select:
570 slct := parsedStmt.(*sqlparser.Select)
571 if nil == slct.Limit {
572 slct.Limit = &sqlparser.Limit{
573 Rowcount: &sqlparser.SQLVal{
574 Type: sqlparser.IntVal,
575 Val: []byte(strconv.Itoa(limit)),
576 },
577 }
578 slct.Limit.Offset = &sqlparser.SQLVal{
579 Type: sqlparser.IntVal,
580 Val: []byte(strconv.Itoa((page - 1) * limit)),
581 }
582 } else {
583 if nil != slct.Limit.Rowcount && 0 < len(slct.Limit.Rowcount.(*sqlparser.SQLVal).Val) {
584 limit, _ = strconv.Atoi(string(slct.Limit.Rowcount.(*sqlparser.SQLVal).Val))
585 if 0 >= limit {
586 limit = 32
587 }
588 }
589
590 slct.Limit.Rowcount = &sqlparser.SQLVal{
591 Type: sqlparser.IntVal,
592 Val: []byte(strconv.Itoa(limit)),
593 }
594 slct.Limit.Offset = &sqlparser.SQLVal{
595 Type: sqlparser.IntVal,
596 Val: []byte(strconv.Itoa((page - 1) * limit)),
597 }
598 }
599
600 stmt = sqlparser.String(slct)
601 default:
602 return
603 }
604
605 stmt = strings.ReplaceAll(stmt, "\\'", "''")
606 stmt = strings.ReplaceAll(stmt, "\\\"", "\"")
607 stmt = strings.ReplaceAll(stmt, "\\\\*", "\\*")
608 stmt = strings.ReplaceAll(stmt, "from dual", "")
609 rows, err := query(stmt)
610 if err != nil {
611 if strings.Contains(err.Error(), "syntax error") {
612 return
613 }
614 logging.LogWarnf("sql query [%s] failed: %s", stmt, err)
615 return
616 }
617 defer rows.Close()
618 for rows.Next() {
619 if block := scanBlockRows(rows); nil != block {
620 ret = append(ret, block)
621 }
622 }
623 return
624}
625
626func SelectBlocksRegex(stmt string, exp *regexp.Regexp, name, alias, memo, ial bool, page, pageSize int) (ret []*Block) {
627 rows, err := query(stmt)
628 if err != nil {
629 logging.LogErrorf("sql query [%s] failed: %s", stmt, err)
630 return
631 }
632 defer rows.Close()
633 count := 0
634 for rows.Next() {
635 count++
636 if count <= (page-1)*pageSize {
637 continue
638 }
639
640 var block Block
641 if err := rows.Scan(&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 {
642 logging.LogErrorf("query scan field failed: %s\n%s", err, logging.ShortStack())
643 return
644 }
645
646 hitContent := exp.MatchString(block.Content)
647 hitName := name && exp.MatchString(block.Name)
648 hitAlias := alias && exp.MatchString(block.Alias)
649 hitMemo := memo && exp.MatchString(block.Memo)
650 hitIAL := ial && exp.MatchString(block.IAL)
651 if hitContent || hitName || hitAlias || hitMemo || hitIAL {
652 if hitContent {
653 block.Content = exp.ReplaceAllString(block.Content, "__@mark__${0}__mark@__")
654 }
655 if hitName {
656 block.Name = exp.ReplaceAllString(block.Name, "__@mark__${0}__mark@__")
657 }
658 if hitAlias {
659 block.Alias = exp.ReplaceAllString(block.Alias, "__@mark__${0}__mark@__")
660 }
661 if hitMemo {
662 block.Memo = exp.ReplaceAllString(block.Memo, "__@mark__${0}__mark@__")
663 }
664 if hitIAL {
665 block.IAL = exp.ReplaceAllString(block.IAL, "__@mark__${0}__mark@__")
666 }
667
668 ret = append(ret, &block)
669 if len(ret) >= pageSize {
670 break
671 }
672 }
673 }
674 return
675}
676
677func selectBlocksRawStmt(stmt string, limit int) (ret []*Block) {
678 rows, err := query(stmt)
679 if err != nil {
680 if strings.Contains(err.Error(), "syntax error") {
681 return
682 }
683 return
684 }
685 defer rows.Close()
686
687 noLimit := !containsLimitClause(stmt)
688 var count, errCount int
689 for rows.Next() {
690 count++
691 if block := scanBlockRows(rows); nil != block {
692 ret = append(ret, block)
693 } else {
694 logging.LogWarnf("raw sql query [%s] failed", stmt)
695 errCount++
696 }
697
698 if (noLimit && limit < count) || 0 < errCount {
699 break
700 }
701 }
702 return
703}
704
705func scanBlockRows(rows *sql.Rows) (ret *Block) {
706 var block Block
707 if err := rows.Scan(&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 {
708 logging.LogErrorf("query scan field failed: %s\n%s", err, logging.ShortStack())
709 return
710 }
711 ret = &block
712 putBlockCache(ret)
713 return
714}
715
716func scanBlockRow(row *sql.Row) (ret *Block) {
717 var block Block
718 if err := row.Scan(&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 {
719 if sql.ErrNoRows != err {
720 logging.LogErrorf("query scan field failed: %s\n%s", err, logging.ShortStack())
721 }
722 return
723 }
724 ret = &block
725 putBlockCache(ret)
726 return
727}
728
729func GetChildBlocks(parentID, condition string, limit int) (ret []*Block) {
730 blockIDs := queryBlockChildrenIDs(parentID)
731 var params []string
732 for _, id := range blockIDs {
733 params = append(params, "\""+id+"\"")
734 }
735
736 ret = []*Block{}
737 sqlStmt := "SELECT * FROM blocks AS ref WHERE ref.id IN (" + strings.Join(params, ",") + ")"
738 if "" != condition {
739 sqlStmt += " AND " + condition
740 }
741 sqlStmt += " LIMIT " + strconv.Itoa(limit)
742 rows, err := query(sqlStmt)
743 if err != nil {
744 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
745 return
746 }
747 defer rows.Close()
748 for rows.Next() {
749 if block := scanBlockRows(rows); nil != block {
750 ret = append(ret, block)
751 }
752 }
753 return
754}
755
756func GetAllChildBlocks(rootIDs []string, condition string, limit int) (ret []*Block) {
757 ret = []*Block{}
758 sqlStmt := "SELECT * FROM blocks AS ref WHERE ref.root_id IN ('" + strings.Join(rootIDs, "','") + "')"
759 if "" != condition {
760 sqlStmt += " AND " + condition
761 }
762 sqlStmt += " LIMIT " + strconv.Itoa(limit)
763 rows, err := query(sqlStmt)
764 if err != nil {
765 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
766 return
767 }
768 defer rows.Close()
769 for rows.Next() {
770 if block := scanBlockRows(rows); nil != block {
771 ret = append(ret, block)
772 }
773 }
774 return
775}
776
777func GetBlock(id string) (ret *Block) {
778 ret = getBlockCache(id)
779 if nil != ret {
780 return
781 }
782 row := queryRow("SELECT * FROM blocks WHERE id = ?", id)
783 if nil == row {
784 return
785 }
786 ret = scanBlockRow(row)
787 if nil != ret {
788 putBlockCache(ret)
789 }
790 return
791}
792
793func GetRootUpdated() (ret map[string]string, err error) {
794 rows, err := query("SELECT root_id, updated FROM `blocks` WHERE type = 'd'")
795 if err != nil {
796 logging.LogErrorf("sql query failed: %s", err)
797 return
798 }
799 defer rows.Close()
800
801 ret = map[string]string{}
802 for rows.Next() {
803 var rootID, updated string
804 rows.Scan(&rootID, &updated)
805 ret[rootID] = updated
806 }
807 return
808}
809
810func GetDuplicatedRootIDs(blocksTable string) (ret []string) {
811 rows, err := query("SELECT DISTINCT root_id FROM `" + blocksTable + "` GROUP BY id HAVING COUNT(*) > 1")
812 if err != nil {
813 logging.LogErrorf("sql query failed: %s", err)
814 return
815 }
816 defer rows.Close()
817 for rows.Next() {
818 var id string
819 rows.Scan(&id)
820 ret = append(ret, id)
821 }
822 return
823}
824
825func GetAllRootBlocks() (ret []*Block) {
826 stmt := "SELECT * FROM blocks WHERE type = 'd'"
827 rows, err := query(stmt)
828 if err != nil {
829 logging.LogErrorf("sql query [%s] failed: %s", stmt, err)
830 return
831 }
832 defer rows.Close()
833 for rows.Next() {
834 if block := scanBlockRows(rows); nil != block {
835 ret = append(ret, block)
836 }
837 }
838 return
839}
840
841func GetBlocks(ids []string) (ret []*Block) {
842 var notHitIDs []string
843 cached := map[string]*Block{}
844 for _, id := range ids {
845 b := getBlockCache(id)
846 if nil != b {
847 cached[id] = b
848 } else {
849 notHitIDs = append(notHitIDs, id)
850 }
851 }
852
853 if 1 > len(notHitIDs) {
854 for _, id := range ids {
855 ret = append(ret, cached[id])
856 }
857 return
858 }
859
860 length := len(notHitIDs)
861 stmtBuilder := bytes.Buffer{}
862 stmtBuilder.WriteString("SELECT * FROM blocks WHERE id IN (")
863 var args []interface{}
864 for i, id := range notHitIDs {
865 args = append(args, id)
866 stmtBuilder.WriteByte('?')
867 if i < length-1 {
868 stmtBuilder.WriteByte(',')
869 }
870 }
871 stmtBuilder.WriteString(")")
872 sqlStmt := stmtBuilder.String()
873 rows, err := query(sqlStmt, args...)
874 if err != nil {
875 logging.LogErrorf("sql query [%s] failed: %s", sqlStmt, err)
876 return
877 }
878 defer rows.Close()
879 for rows.Next() {
880 if block := scanBlockRows(rows); nil != block {
881 putBlockCache(block)
882 cached[block.ID] = block
883 }
884 }
885 for _, id := range ids {
886 ret = append(ret, cached[id])
887 }
888 return
889}
890
891func GetContainerText(container *ast.Node) string {
892 buf := &bytes.Buffer{}
893 buf.Grow(4096)
894 leaf := treenode.FirstLeafBlock(container)
895 if nil == leaf {
896 return ""
897 }
898
899 ast.Walk(leaf, func(n *ast.Node, entering bool) ast.WalkStatus {
900 if !entering {
901 return ast.WalkContinue
902 }
903 switch n.Type {
904 case ast.NodeText, ast.NodeLinkText, ast.NodeFileAnnotationRefText, ast.NodeCodeBlockCode, ast.NodeMathBlockContent:
905 buf.Write(n.Tokens)
906 case ast.NodeTextMark:
907 buf.WriteString(n.Content())
908 case ast.NodeBlockRef:
909 if anchor := n.ChildByType(ast.NodeBlockRefText); nil != anchor {
910 buf.WriteString(anchor.Text())
911 } else if anchor = n.ChildByType(ast.NodeBlockRefDynamicText); nil != anchor {
912 buf.WriteString(anchor.Text())
913 } else {
914 text := GetRefText(n.TokensStr())
915 buf.WriteString(text)
916 }
917 return ast.WalkSkipChildren
918 }
919 return ast.WalkContinue
920 })
921 return buf.String()
922}
923
924func containsLimitClause(stmt string) bool {
925 return strings.Contains(strings.ToLower(stmt), " limit ") ||
926 strings.Contains(strings.ToLower(stmt), "\nlimit ") ||
927 strings.Contains(strings.ToLower(stmt), "\tlimit ")
928}