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
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707// 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" "fmt" "sort" "strings" "text/template" "time"
"github.com/88250/lute/ast" "github.com/siyuan-note/logging" "github.com/siyuan-note/siyuan/kernel/av" "github.com/siyuan-note/siyuan/kernel/filesys" "github.com/siyuan-note/siyuan/kernel/treenode" "github.com/siyuan-note/siyuan/kernel/util")
func RenderAttributeViewTable(attrView *av.AttributeView, view *av.View, query string) (ret *av.Table) { ret = &av.Table{ ID: view.ID, Icon: view.Icon, Name: view.Name, Desc: view.Desc, HideAttrViewName: view.HideAttrViewName, Columns: []*av.TableColumn{}, Rows: []*av.TableRow{}, Filters: view.Table.Filters, Sorts: view.Table.Sorts, }
// 组装列 for _, col := range view.Table.Columns { key, getErr := attrView.GetKey(col.ID) if nil != getErr { // 找不到字段则在视图中删除
switch view.LayoutType { case av.LayoutTypeTable: for i, column := range view.Table.Columns { if column.ID == col.ID { view.Table.Columns = append(view.Table.Columns[:i], view.Table.Columns[i+1:]...) break } } }
logging.LogWarnf("get key [%s] failed: %s", col.ID, getErr) av.SaveAttributeView(attrView) continue }
ret.Columns = append(ret.Columns, &av.TableColumn{ ID: key.ID, Name: key.Name, Type: key.Type, Icon: key.Icon, Options: key.Options, NumberFormat: key.NumberFormat, Template: key.Template, Relation: key.Relation, Rollup: key.Rollup, Date: key.Date, Wrap: col.Wrap, Hidden: col.Hidden, Width: col.Width, Desc: key.Desc, Pin: col.Pin, Calc: col.Calc, }) }
// 生成行 rows := map[string][]*av.KeyValues{} for _, keyValues := range attrView.KeyValues { for _, val := range keyValues.Values { values := rows[val.BlockID] if nil == values { values = []*av.KeyValues{{Key: keyValues.Key, Values: []*av.Value{val}}} } else { values = append(values, &av.KeyValues{Key: keyValues.Key, Values: []*av.Value{val}}) } rows[val.BlockID] = values } }
// 过滤掉不存在的行 var notFound []string var toCheckBlockIDs []string for blockID, keyValues := range rows { blockValue := getRowBlockValue(keyValues) if nil == blockValue { notFound = append(notFound, blockID) continue }
if blockValue.IsDetached { continue }
if nil != blockValue.Block && "" == blockValue.Block.ID { notFound = append(notFound, blockID) continue }
toCheckBlockIDs = append(toCheckBlockIDs, blockID) } checkRet := treenode.ExistBlockTrees(toCheckBlockIDs) for blockID, exist := range checkRet { if !exist { notFound = append(notFound, blockID) } } for _, blockID := range notFound { delete(rows, blockID) }
// 生成行单元格 for rowID, row := range rows { var tableRow av.TableRow for _, col := range ret.Columns { var tableCell *av.TableCell for _, keyValues := range row { if keyValues.Key.ID == col.ID { tableCell = &av.TableCell{ ID: keyValues.Values[0].ID, Value: keyValues.Values[0], ValueType: col.Type, } break } } if nil == tableCell { tableCell = &av.TableCell{ ID: ast.NewNodeID(), ValueType: col.Type, } } tableRow.ID = rowID
switch tableCell.ValueType { case av.KeyTypeNumber: // 格式化数字 if nil != tableCell.Value && nil != tableCell.Value.Number && tableCell.Value.Number.IsNotEmpty { tableCell.Value.Number.Format = col.NumberFormat tableCell.Value.Number.FormatNumber() } case av.KeyTypeTemplate: // 渲染模板列 tableCell.Value = &av.Value{ID: tableCell.ID, KeyID: col.ID, BlockID: rowID, Type: av.KeyTypeTemplate, Template: &av.ValueTemplate{Content: col.Template}} case av.KeyTypeCreated: // 填充创建时间列值，后面再渲染 tableCell.Value = &av.Value{ID: tableCell.ID, KeyID: col.ID, BlockID: rowID, Type: av.KeyTypeCreated} case av.KeyTypeUpdated: // 填充更新时间列值，后面再渲染 tableCell.Value = &av.Value{ID: tableCell.ID, KeyID: col.ID, BlockID: rowID, Type: av.KeyTypeUpdated} case av.KeyTypeRelation: // 清空关联列值，后面再渲染 https://ld246.com/article/1703831044435 if nil != tableCell.Value && nil != tableCell.Value.Relation { tableCell.Value.Relation.Contents = nil } }
FillAttributeViewTableCellNilValue(tableCell, rowID, col.ID)
tableRow.Cells = append(tableRow.Cells, tableCell) } ret.Rows = append(ret.Rows, &tableRow) }
// 批量获取块属性以提升性能 var ialIDs []string for _, row := range ret.Rows { block := row.GetBlockValue() if nil != block && !block.IsDetached { ialIDs = append(ialIDs, row.ID) } } ials := BatchGetBlockAttrs(ialIDs)
// 渲染自动生成的列值，比如关联列、汇总列、创建时间列和更新时间列 avCache := map[string]*av.AttributeView{} avCache[attrView.ID] = attrView for _, row := range ret.Rows { for _, cell := range row.Cells { switch cell.ValueType { case av.KeyTypeBlock: // 对于主键可能需要填充静态锚文本 Database-bound block primary key supports setting static anchor text https://github.com/siyuan-note/siyuan/issues/10049 if nil != cell.Value.Block { for k, v := range ials[row.ID] { if k == av.NodeAttrViewStaticText+"-"+attrView.ID { cell.Value.Block.Content = v break } } } case av.KeyTypeRollup: // 渲染汇总列 rollupKey, _ := attrView.GetKey(cell.Value.KeyID) if nil == rollupKey || nil == rollupKey.Rollup { break }
relKey, _ := attrView.GetKey(rollupKey.Rollup.RelationKeyID) if nil == relKey || nil == relKey.Relation { break }
relVal := attrView.GetValue(relKey.ID, row.ID) if nil == relVal || nil == relVal.Relation { break }
destAv := avCache[relKey.Relation.AvID] if nil == destAv { destAv, _ = av.ParseAttributeView(relKey.Relation.AvID) if nil != destAv { avCache[relKey.Relation.AvID] = destAv } } if nil == destAv { break }
destKey, _ := destAv.GetKey(rollupKey.Rollup.KeyID) if nil == destKey { continue }
for _, blockID := range relVal.Relation.BlockIDs { destVal := destAv.GetValue(rollupKey.Rollup.KeyID, blockID) if nil == destVal { if destAv.ExistBlock(blockID) { // 数据库中存在行但是列值不存在是数据未初始化，这里补一个默认值 destVal = av.GetAttributeViewDefaultValue(ast.NewNodeID(), rollupKey.Rollup.KeyID, blockID, destKey.Type) } if nil == destVal { continue } } if av.KeyTypeNumber == destKey.Type { destVal.Number.Format = destKey.NumberFormat destVal.Number.FormatNumber() }
cell.Value.Rollup.Contents = append(cell.Value.Rollup.Contents, destVal.Clone()) }
cell.Value.Rollup.RenderContents(rollupKey.Rollup.Calc, destKey)
// 将汇总列的值保存到 rows 中，后续渲染模板列的时候会用到，下同 // Database table view template columns support reading relation, rollup, created and updated columns https://github.com/siyuan-note/siyuan/issues/10442 keyValues := rows[row.ID] keyValues = append(keyValues, &av.KeyValues{Key: rollupKey, Values: []*av.Value{{ID: cell.Value.ID, KeyID: rollupKey.ID, BlockID: row.ID, Type: av.KeyTypeRollup, Rollup: cell.Value.Rollup}}}) rows[row.ID] = keyValues case av.KeyTypeRelation: // 渲染关联列 relKey, _ := attrView.GetKey(cell.Value.KeyID) if nil != relKey && nil != relKey.Relation { destAv := avCache[relKey.Relation.AvID] if nil == destAv { destAv, _ = av.ParseAttributeView(relKey.Relation.AvID) if nil != destAv { avCache[relKey.Relation.AvID] = destAv } } if nil != destAv { blocks := map[string]*av.Value{} blockValues := destAv.GetBlockKeyValues() if nil != blockValues { for _, blockValue := range blockValues.Values { blocks[blockValue.BlockID] = blockValue } for _, blockID := range cell.Value.Relation.BlockIDs { if val := blocks[blockID]; nil != val { cell.Value.Relation.Contents = append(cell.Value.Relation.Contents, val) } } } } }
keyValues := rows[row.ID] keyValues = append(keyValues, &av.KeyValues{Key: relKey, Values: []*av.Value{{ID: cell.Value.ID, KeyID: relKey.ID, BlockID: row.ID, Type: av.KeyTypeRelation, Relation: cell.Value.Relation}}}) rows[row.ID] = keyValues case av.KeyTypeCreated: // 渲染创建时间 createdStr := row.ID[:len("20060102150405")] created, parseErr := time.ParseInLocation("20060102150405", createdStr, time.Local) if nil == parseErr { cell.Value.Created = av.NewFormattedValueCreated(created.UnixMilli(), 0, av.CreatedFormatNone) cell.Value.Created.IsNotEmpty = true } else { cell.Value.Created = av.NewFormattedValueCreated(time.Now().UnixMilli(), 0, av.CreatedFormatNone) }
keyValues := rows[row.ID] createdKey, _ := attrView.GetKey(cell.Value.KeyID) keyValues = append(keyValues, &av.KeyValues{Key: createdKey, Values: []*av.Value{{ID: cell.Value.ID, KeyID: createdKey.ID, BlockID: row.ID, Type: av.KeyTypeCreated, Created: cell.Value.Created}}}) rows[row.ID] = keyValues case av.KeyTypeUpdated: // 渲染更新时间 ial := ials[row.ID] if nil == ial { ial = map[string]string{} } block := row.GetBlockValue() updatedStr := ial["updated"] if "" == updatedStr && nil != block { cell.Value.Updated = av.NewFormattedValueUpdated(block.Block.Updated, 0, av.UpdatedFormatNone) cell.Value.Updated.IsNotEmpty = true } else { updated, parseErr := time.ParseInLocation("20060102150405", updatedStr, time.Local) if nil == parseErr { cell.Value.Updated = av.NewFormattedValueUpdated(updated.UnixMilli(), 0, av.UpdatedFormatNone) cell.Value.Updated.IsNotEmpty = true } else { cell.Value.Updated = av.NewFormattedValueUpdated(time.Now().UnixMilli(), 0, av.UpdatedFormatNone) } }
keyValues := rows[row.ID] updatedKey, _ := attrView.GetKey(cell.Value.KeyID) keyValues = append(keyValues, &av.KeyValues{Key: updatedKey, Values: []*av.Value{{ID: cell.Value.ID, KeyID: updatedKey.ID, BlockID: row.ID, Type: av.KeyTypeUpdated, Updated: cell.Value.Updated}}}) rows[row.ID] = keyValues } } }
// 最后单独渲染模板列，这样模板列就可以使用汇总、关联、创建时间和更新时间列的值了 // Database table view template columns support reading relation, rollup, created and updated columns https://github.com/siyuan-note/siyuan/issues/10442
var renderTemplateErr error for _, row := range ret.Rows { for _, cell := range row.Cells { switch cell.ValueType { case av.KeyTypeTemplate: // 渲染模板列 keyValues := rows[row.ID] ial := ials[row.ID] if nil == ial { ial = map[string]string{} } content, renderErr := RenderTemplateCol(ial, keyValues, cell.Value.Template.Content) cell.Value.Template.Content = content if nil != renderErr { key, _ := attrView.GetKey(cell.Value.KeyID) keyName := "" if nil != key { keyName = key.Name } renderTemplateErr = fmt.Errorf("database [%s] template field [%s] rendering failed: %s", getAttrViewName(attrView), keyName, renderErr) } } } } if nil != renderTemplateErr { util.PushErrMsg(fmt.Sprintf(util.Langs[util.Lang][44], util.EscapeHTML(renderTemplateErr.Error())), 30000) }
// 根据搜索条件过滤 query = strings.TrimSpace(query) if "" != query { // 将连续空格转换为一个空格 query = strings.Join(strings.Fields(query), " ") // 按空格分割关键字 keywords := strings.Split(query, " ") // 使用 AND 逻辑 https://github.com/siyuan-note/siyuan/issues/11535 var hitRows []*av.TableRow for _, row := range ret.Rows { hit := false for _, cell := range row.Cells { allKeywordsHit := true for _, keyword := range keywords { if !strings.Contains(strings.ToLower(cell.Value.String(true)), strings.ToLower(keyword)) { allKeywordsHit = false break } } if allKeywordsHit { hit = true break } } if hit { hitRows = append(hitRows, row) } } ret.Rows = hitRows if 1 > len(ret.Rows) { ret.Rows = []*av.TableRow{} } }
// 自定义排序 sortRowIDs := map[string]int{} if 0 < len(view.Table.RowIDs) { for i, rowID := range view.Table.RowIDs { sortRowIDs[rowID] = i } }
sort.Slice(ret.Rows, func(i, j int) bool { iv := sortRowIDs[ret.Rows[i].ID] jv := sortRowIDs[ret.Rows[j].ID] if iv == jv { return ret.Rows[i].ID < ret.Rows[j].ID } return iv < jv }) return}
func RenderTemplateCol(ial map[string]string, rowValues []*av.KeyValues, tplContent string) (ret string, err error) { if "" == ial["id"] { block := getRowBlockValue(rowValues) if nil != block && nil != block.Block { ial["id"] = block.Block.ID } } if "" == ial["updated"] { block := getRowBlockValue(rowValues) if nil != block && nil != block.Block { ial["updated"] = time.UnixMilli(block.Block.Updated).Format("20060102150405") } }
goTpl := template.New("").Delims(".action{", "}") tplFuncMap := filesys.BuiltInTemplateFuncs() SQLTemplateFuncs(&tplFuncMap) goTpl = goTpl.Funcs(tplFuncMap) tpl, err := goTpl.Parse(tplContent) if err != nil { logging.LogWarnf("parse template [%s] failed: %s", tplContent, err) return }
buf := &bytes.Buffer{} dataModel := map[string]interface{}{} // 复制一份 IAL 以避免修改原始数据 for k, v := range ial { dataModel[k] = v
// Database template column supports `created` and `updated` built-in variables https://github.com/siyuan-note/siyuan/issues/9364 createdStr := ial["id"] if "" != createdStr { createdStr = createdStr[:len("20060102150405")] } created, parseErr := time.ParseInLocation("20060102150405", createdStr, time.Local) if nil == parseErr { dataModel["created"] = created } else { errMsg := parseErr.Error() //logging.LogWarnf("parse created [%s] failed: %s", createdStr, errMsg) if strings.Contains(errMsg, "minute out of range") { // parsing time "20240709158553": minute out of range // 将分秒部分置为 0000 createdStr = createdStr[:len("2006010215")] + "0000" } else if strings.Contains(errMsg, "second out of range") { // parsing time "20240709154592": second out of range // 将秒部分置为 00 createdStr = createdStr[:len("200601021504")] + "00" } created, parseErr = time.ParseInLocation("20060102150405", createdStr, time.Local) } if nil != parseErr { logging.LogWarnf("parse created [%s] failed: %s", createdStr, parseErr) dataModel["created"] = time.Now() } updatedStr := ial["updated"] updated, parseErr := time.ParseInLocation("20060102150405", updatedStr, time.Local) if nil == parseErr { dataModel["updated"] = updated } else { dataModel["updated"] = time.Now() } }
dataModel["id_mod"] = map[string]any{} dataModel["id_mod_raw"] = map[string]any{} for _, rowValue := range rowValues { if 1 > len(rowValue.Values) { continue }
v := rowValue.Values[0] if av.KeyTypeNumber == v.Type { if nil != v.Number && v.Number.IsNotEmpty { dataModel[rowValue.Key.Name] = v.Number.Content } } else if av.KeyTypeDate == v.Type { if nil != v.Date { if v.Date.IsNotEmpty { dataModel[rowValue.Key.Name] = time.UnixMilli(v.Date.Content) } if v.Date.IsNotEmpty2 { dataModel[rowValue.Key.Name+"_end"] = time.UnixMilli(v.Date.Content2) } } } else if av.KeyTypeRollup == v.Type { if 0 < len(v.Rollup.Contents) { var numbers []float64 var contents []string for _, content := range v.Rollup.Contents { if av.KeyTypeNumber == content.Type { numbers = append(numbers, content.Number.Content) } else if av.KeyTypeMSelect == content.Type { for _, s := range content.MSelect { contents = append(contents, s.Content) } } else { contents = append(contents, content.String(true)) } }
if 0 < len(numbers) { dataModel[rowValue.Key.Name] = numbers } else { dataModel[rowValue.Key.Name] = contents } } } else if av.KeyTypeRelation == v.Type { if 0 < len(v.Relation.Contents) { var contents []string for _, content := range v.Relation.Contents { contents = append(contents, content.String(true)) } dataModel[rowValue.Key.Name] = contents } } else if av.KeyTypeBlock == v.Type { dataModel[rowValue.Key.Name+"_created"] = time.Now() if nil != v.Block { dataModel["entryCreated"] = time.UnixMilli(v.Block.Created) } dataModel["entryUpdated"] = time.Now() if nil != v.Block { dataModel["entryUpdated"] = time.UnixMilli(v.Block.Updated) } dataModel[rowValue.Key.Name] = v.String(true) } else { dataModel[rowValue.Key.Name] = v.String(true) }
// Database template fields support access to the raw value https://github.com/siyuan-note/siyuan/issues/14903 dataModel[rowValue.Key.Name+"_raw"] = v
// Database template fields support access by ID https://github.com/siyuan-note/siyuan/issues/11237 dataModel["id_mod"].(map[string]any)[rowValue.Key.ID] = dataModel[rowValue.Key.Name] dataModel["id_mod_raw"].(map[string]any)[rowValue.Key.ID] = v }
if err = tpl.Execute(buf, dataModel); err != nil { logging.LogWarnf("execute template [%s] failed: %s", tplContent, err) return } ret = buf.String() return}
func FillAttributeViewTableCellNilValue(tableCell *av.TableCell, rowID, colID string) { if nil == tableCell.Value { tableCell.Value = av.GetAttributeViewDefaultValue(tableCell.ID, colID, rowID, tableCell.ValueType) return }
tableCell.Value.Type = tableCell.ValueType switch tableCell.ValueType { case av.KeyTypeText: if nil == tableCell.Value.Text { tableCell.Value.Text = &av.ValueText{} } case av.KeyTypeNumber: if nil == tableCell.Value.Number { tableCell.Value.Number = &av.ValueNumber{} } case av.KeyTypeDate: if nil == tableCell.Value.Date { tableCell.Value.Date = &av.ValueDate{} } case av.KeyTypeSelect: if 1 > len(tableCell.Value.MSelect) { tableCell.Value.MSelect = []*av.ValueSelect{} } case av.KeyTypeMSelect: if 1 > len(tableCell.Value.MSelect) { tableCell.Value.MSelect = []*av.ValueSelect{} } case av.KeyTypeURL: if nil == tableCell.Value.URL { tableCell.Value.URL = &av.ValueURL{} } case av.KeyTypeEmail: if nil == tableCell.Value.Email { tableCell.Value.Email = &av.ValueEmail{} } case av.KeyTypePhone: if nil == tableCell.Value.Phone { tableCell.Value.Phone = &av.ValuePhone{} } case av.KeyTypeMAsset: if 1 > len(tableCell.Value.MAsset) { tableCell.Value.MAsset = []*av.ValueAsset{} } case av.KeyTypeTemplate: if nil == tableCell.Value.Template { tableCell.Value.Template = &av.ValueTemplate{} } case av.KeyTypeCreated: if nil == tableCell.Value.Created { tableCell.Value.Created = &av.ValueCreated{} } case av.KeyTypeUpdated: if nil == tableCell.Value.Updated { tableCell.Value.Updated = &av.ValueUpdated{} } case av.KeyTypeCheckbox: if nil == tableCell.Value.Checkbox { tableCell.Value.Checkbox = &av.ValueCheckbox{} } case av.KeyTypeRelation: if nil == tableCell.Value.Relation { tableCell.Value.Relation = &av.ValueRelation{} } case av.KeyTypeRollup: if nil == tableCell.Value.Rollup { tableCell.Value.Rollup = &av.ValueRollup{} } }}
func getAttributeViewContent(avID string) (content string) { if "" == avID { return }
attrView, err := av.ParseAttributeView(avID) if err != nil { logging.LogErrorf("parse attribute view [%s] failed: %s", avID, err) return }
buf := bytes.Buffer{} buf.WriteString(attrView.Name) buf.WriteByte(' ') for _, v := range attrView.Views { buf.WriteString(v.Name) buf.WriteByte(' ') }
if 1 > len(attrView.Views) { content = strings.TrimSpace(buf.String()) return }
var view *av.View for _, v := range attrView.Views { if av.LayoutTypeTable == v.LayoutType { view = v break } } if nil == view { content = strings.TrimSpace(buf.String()) return }
table := RenderAttributeViewTable(attrView, view, "") for _, col := range table.Columns { buf.WriteString(col.Name) buf.WriteByte(' ') }
for _, row := range table.Rows { for _, cell := range row.Cells { if nil == cell.Value { continue } buf.WriteString(cell.Value.String(true)) buf.WriteByte(' ') } }
content = strings.TrimSpace(buf.String()) return}
func getRowBlockValue(keyValues []*av.KeyValues) (ret *av.Value) { for _, kv := range keyValues { if av.KeyTypeBlock == kv.Key.Type && 0 < len(kv.Values) { ret = kv.Values[0] break } } return}
func getAttrViewName(attrView *av.AttributeView) string { ret := strings.TrimSpace(attrView.Name) if "" == ret { ret = util.Langs[util.Lang][105] } return ret}