Something went wrong. Try again.
A privacy-first, self-hosted, fully open source personal knowledge management software, written in typescript and golang. (PERSONAL FORK)
Something went wrong. Try again.
Go
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513// SiYuan - Refactor your thinking// Copyright (c) 2020-present, b3log.org//// This program is free software: you can redistribute it and/or modify// it under the terms of the GNU Affero General Public License as published by// the Free Software Foundation, either version 3 of the License, or// (at your option) any later version.//// This program is distributed in the hope that it will be useful,// but WITHOUT ANY WARRANTY; without even the implied warranty of// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the// GNU Affero General Public License for more details.//// You should have received a copy of the GNU Affero General Public License// along with this program. If not, see <https://www.gnu.org/licenses/>.
package sql
import ( "bytes" "database/sql" "sort" "strings"
"github.com/88250/gulu" "github.com/88250/lute/parse" "github.com/emirpasic/gods/sets/hashset" "github.com/siyuan-note/logging" "github.com/siyuan-note/siyuan/kernel/search")
func GetRefDuplicatedDefRootIDs() (ret []string) { 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") if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { var id string rows.Scan(&id) ret = append(ret, id) } return}
func QueryVirtualRefKeywords(name, alias, anchor, doc bool, searchIgnoreLines, refSearchIgnoreLines []string) (ret []string) { if name { ret = append(ret, queryNames(searchIgnoreLines)...) } if alias { ret = append(ret, queryAliases(searchIgnoreLines)...) } if anchor { ret = append(ret, queryRefTexts(refSearchIgnoreLines)...) } if doc { ret = append(ret, queryDocTitles(searchIgnoreLines)...) } ret = gulu.Str.RemoveDuplicatedElem(ret) sort.SliceStable(ret, func(i, j int) bool { return len(ret[i]) >= len(ret[j]) }) return}
func queryRefTexts(refSearchIgnoreLines []string) (ret []string) { ret = []string{} sqlStmt := "SELECT DISTINCT content FROM refs WHERE 1 = 1" buf := bytes.Buffer{} for _, line := range refSearchIgnoreLines { buf.WriteString(" AND ") buf.WriteString(line) } sqlStmt += buf.String() sqlStmt += " LIMIT 10240" rows, err := query(sqlStmt) if err != nil { logging.LogErrorf("sql query failed: %s", sqlStmt, err) return } defer rows.Close()
set := hashset.New() for rows.Next() { var refText string rows.Scan(&refText) if "" == strings.TrimSpace(refText) { continue } set.Add(refText) } for _, refText := range set.Values() { ret = append(ret, refText.(string)) } return}
func QueryRefCount(defIDs []string) (ret map[string]int) { ret = map[string]int{} ids := strings.Join(defIDs, "','") ids = "('" + ids + "')" rows, err := query("SELECT def_block_id, COUNT(*) AS ref_cnt FROM refs WHERE def_block_id IN " + ids + " GROUP BY def_block_id") if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { var id string var cnt int if err = rows.Scan(&id, &cnt); err != nil { logging.LogErrorf("query scan field failed: %s", err) return } ret[id] = cnt } return}
func QueryRootChildrenRefCount(defRootID string) (ret map[string]int) { ret = map[string]int{} rows, err := query("SELECT def_block_id, COUNT(*) AS ref_cnt FROM refs WHERE def_block_root_id = ? GROUP BY def_block_id", defRootID) if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { var id string var cnt int if err = rows.Scan(&id, &cnt); err != nil { logging.LogErrorf("query scan field failed: %s", err) return } ret[id] = cnt } return}
func QueryRootBlockRefCount() (ret map[string]int) { ret = map[string]int{}
rows, err := query("SELECT def_block_root_id, COUNT(DISTINCT block_id) AS ref_cnt FROM refs GROUP BY def_block_root_id") if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { var id string var cnt int if err = rows.Scan(&id, &cnt); err != nil { logging.LogErrorf("query scan field failed: %s", err) return } ret[id] = cnt } return}
func QueryDefRootBlocksByRefRootID(refRootID string) (ret []*Block) { rows, err := query("SELECT * FROM blocks WHERE id IN (SELECT DISTINCT def_block_root_id FROM refs WHERE root_id = ?)", refRootID) if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { if block := scanBlockRows(rows); nil != block { ret = append(ret, block) } } return}
func QueryRefRootBlocksByDefRootIDs(defRootIDs []string) (ret map[string][]*Block) { ret = map[string][]*Block{}
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" rows, err := query(stmt) if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { var block Block var defRootID string 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 { logging.LogErrorf("query scan field failed: %s\n%s", err, logging.ShortStack()) return }
if nil == ret[defRootID] { ret[defRootID] = []*Block{&block} } else { ret[defRootID] = append(ret[defRootID], &block) } } return}
func GetRefText(defBlockID string) (ret string) { ret = getRefText(defBlockID) ret = strings.ReplaceAll(ret, search.SearchMarkLeft, "") ret = strings.ReplaceAll(ret, search.SearchMarkRight, "") return}
func getRefText(defBlockID string) string { block := GetBlock(defBlockID) if nil == block { if strings.HasPrefix(defBlockID, "assets") { return defBlockID } return "block not found" }
if "" != block.Name { return block.Name }
switch block.Type { case "d": return block.Content case "query_embed": return "Query Embed Block " + block.Markdown case "av": return "Database " + block.Markdown case "iframe": return "IFrame " + block.Markdown case "tb": return "Thematic Break" case "video": return "Video " + block.Markdown case "audio": return "Audio " + block.Markdown }
if block.IsContainerBlock() { subTree := parse.Parse("", []byte(block.Markdown), luteEngine.ParseOptions) return GetContainerText(subTree.Root) } return block.Content}
func QueryBlockDefIDsByRefText(refText string, excludeIDs []string) (ret []string) { ret = queryDefIDsByDefText(refText, excludeIDs) ret = append(ret, queryDefIDsByNameAlias(refText, excludeIDs)...) ret = append(ret, queryDocIDsByTitle(refText, excludeIDs)...) ret = gulu.Str.RemoveDuplicatedElem(ret) return}
func queryDefIDsByDefText(keyword string, excludeIDs []string) (ret []string) { ret = []string{} notIn := "('" + strings.Join(excludeIDs, "','") + "')" q := "SELECT DISTINCT(def_block_id) FROM refs WHERE content LIKE ? AND def_block_id NOT IN " + notIn if caseSensitive { q = "SELECT DISTINCT(def_block_id) FROM refs WHERE content = ? AND def_block_id NOT IN " + notIn } rows, err := query(q, keyword) if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { var id string if err = rows.Scan(&id); err != nil { logging.LogErrorf("query scan field failed: %s", err) return } ret = append(ret, id) } return}
func queryDefIDsByNameAlias(keyword string, excludeIDs []string) (ret []string) { ret = []string{} notIn := "('" + strings.Join(excludeIDs, "','") + "')" rows, err := query("SELECT DISTINCT(id), name, alias FROM blocks WHERE (name = ? OR alias LIKE ?) AND id NOT IN "+notIn, keyword, "%"+keyword+"%") if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { var id, name, alias string if err = rows.Scan(&id, &name, &alias); err != nil { logging.LogErrorf("query scan field failed: %s", err) return } if name == keyword { ret = append(ret, id) continue }
var hitAlias bool aliases := strings.Split(alias, ",") for _, a := range aliases { if "" == a { continue } if keyword == a { hitAlias = true } } if strings.Contains(alias, keyword) && !hitAlias { continue } ret = append(ret, id) } return}
func QueryChildRefDefIDsByRootDefID(rootDefID string) (ret map[string][]string) { ret = map[string][]string{} rows, err := query("SELECT block_id, def_block_id FROM refs WHERE def_block_root_id = ?", rootDefID) if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { var defID, refID string if err = rows.Scan(&defID, &refID); err != nil { logging.LogErrorf("query scan field failed: %s", err) return } if nil == ret[defID] { ret[defID] = []string{refID} } else { ret[defID] = append(ret[defID], refID) } } return}
func QueryChildDefIDsByRootDefID(rootDefID string) (ret []string) { ret = []string{} rows, err := query("SELECT DISTINCT(def_block_id) FROM refs WHERE def_block_root_id = ?", rootDefID) if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { var id string if err = rows.Scan(&id); err != nil { logging.LogErrorf("query scan field failed: %s", err) return } ret = append(ret, id) } return}
func QueryRefIDsByDefID(defID string, containChildren bool) (refIDs []string) { refIDs = []string{} var rows *sql.Rows var err error if containChildren { rows, err = query("SELECT DISTINCT block_id FROM refs WHERE def_block_root_id = ?", defID) } else { rows, err = query("SELECT DISTINCT block_id FROM refs WHERE def_block_id = ?", defID) } if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { var id string if err = rows.Scan(&id); err != nil { logging.LogErrorf("query scan field failed: %s", err) return } refIDs = append(refIDs, id) } return}
func QueryRefsRecent(onlyDoc bool, typeFilter string, ignoreLines []string) (ret []*Ref) { stmt := "SELECT r.* FROM refs AS r, blocks AS b WHERE b.id = r.def_block_id AND b.type IN " + typeFilter if onlyDoc { stmt = "SELECT r.* FROM refs AS r, blocks AS b WHERE b.id = r.def_block_id AND b.type = 'd'" } if 0 < len(ignoreLines) { // Support ignore search results https://github.com/siyuan-note/siyuan/issues/10089 buf := bytes.Buffer{} for _, line := range ignoreLines { buf.WriteString(" AND ") buf.WriteString(line) } stmt += buf.String() } stmt += " GROUP BY r.def_block_id ORDER BY r.id DESC LIMIT 32" rows, err := query(stmt) if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { ref := scanRefRows(rows) ret = append(ret, ref) } return}
func QueryRefsByDefID(defBlockID string, containChildren bool) (ret []*Ref) { var rows *sql.Rows var err error if containChildren { blockIDs := queryBlockChildrenIDs(defBlockID) var params []string for _, id := range blockIDs { params = append(params, "\""+id+"\"") } rows, err = query("SELECT * FROM refs WHERE def_block_id IN (" + strings.Join(params, ",") + ")") } else { rows, err = query("SELECT * FROM refs WHERE def_block_id = ?", defBlockID) } if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { ref := scanRefRows(rows) ret = append(ret, ref) } return}
func QueryRefsByDefIDRefID(defBlockID, refBlockID string) (ret []*Ref) { stmt := "SELECT * FROM refs WHERE def_block_id = ? AND block_id = ?" rows, err := query(stmt, defBlockID, refBlockID) if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() for rows.Next() { ref := scanRefRows(rows) ret = append(ret, ref) } return}
func DefRefs(condition string, limit int) (ret []map[*Block]*Block) { ret = []map[*Block]*Block{} 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" if "" != condition { stmt += " AND " + condition }
rows, err := query(stmt) if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() refs := map[string]*Block{} for rows.Next() { var ref Block var rel string 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, &rel); err != nil { logging.LogErrorf("query scan field failed: %s", err) return } refs[rel] = &ref }
rows, err = query("SELECT def.* FROM blocks AS def, refs AS r WHERE def.id = r.def_block_id LIMIT ?", limit) if err != nil { logging.LogErrorf("sql query failed: %s", err) return } defer rows.Close() defs := map[string]*Block{} for rows.Next() { if def := scanBlockRows(rows); nil != def { defs[def.ID] = def } }
for rel, ref := range refs { defID := strings.Split(rel, "@")[1] def := defs[defID] if nil == def { continue } defRef := map[*Block]*Block{} defRef[def] = ref ret = append(ret, defRef) } return}
func scanRefRows(rows *sql.Rows) (ret *Ref) { var ref Ref 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 { logging.LogErrorf("query scan field failed: %s", err) return } ret = &ref return}