Skip to content

一个将思源笔记数据库查询结果以表格样式渲染的挂件 | A widget that renders the query results of the Siyuan Notes database in tabular style.

License

Notifications You must be signed in to change notification settings

Zuoqiu-Yingyi/widget-query

Repository files navigation

GitHub release (latest by date including pre-releases) GitHub Release Date GitHub License GitHub last commit GitHub repo size hits GitHub all releases All Contributors


简体中文 | English


widget-query

A widget that renders the query results of the Siyuan Notes database in tabular style.

It is now on the shelves of the Siyuan Notes Community Bazaar. If you like this widget, welcome to light up ⭐ for this project!

PREVIEW

preview

Background color status indication:

  • White:

    • Initialization.
    • Processing query.
  • Green:

    • Query success.
  • Blue:

    • Please continue to operate.
  • Yellow:

    • Query result is empty.
  • Red:

    • SQL SQL statement error.
    • Unknown error.

FUNCTION

  1. Click the Auto query check box to automatically query once the next time you open the page.

  2. Set custom attribute input for a widget block can specify the content of a SQL code block or an embedded block as a query statement.

    • For example, in other documents there is a code block or an embedded block ID which ID is 20220418210605-ibussa1, then set the custom block attribute input: 20220418210605-ibussa1 for the widget block can reference the block's SQL statement for query.
    • Only if the pre-block of the widget block is not a code block with the custom attribute type: query-code.
  3. Set custom attribute output for a widget block can specify a table block as a display block for query results.

    • For example, in other documents there is a table block ID which ID is 20220604112815-sfiwyi7, then set the custom block attribute output: 20220604112815-sfiwyi7 for the widget block can render the query results into the table.
    • Only if the post-block of the widget block is not a table block with the custom attribute type: query-table.
  4. SQL statements that conform to the regular expression ^\s*SELECT\s+\*\s+FROM\s+blocks\s+.* will enable the default block query mode.

    • This regular expression is configured in config.query.regs.blocks in /src/script/module/config.js

    • Example: SELECT * FROM blocks WHERE content LIKE '%Content block%'

    • In this mode, the query result rendering style is controlled with the following configuration options.

      Field Field Description Field Value Field Value Description
      config.query.render.* Define the rendering style of certain fields.
      Hyperlink style is like [anchor text](siyuan://blocks/block), which will not be displayed in the backlink panel.
      Block reference style is like ((block "anchor text")), which will be displayed in the backlink panel.
      'link'
      'ref'
      Render the field in hyperlink style.
      Render the field in block reference style.
      config.query.render.ial.shape Define the arrangement method of the block attribute list IAL.
      Display in rows within a column.
      Display in columns within a row.
      'rows'
      'columns'
      config.query.render.ial.fields.forced Define the forced rendering attribute names of IAL. If the array is empty, use the blacklist/whitelist to control. ['attribute name', ...]
      config.query.render.ial.fields.ignore Define the attribute names of IAL that should not be rendered (blacklist). Higher priority than whitelist. ['attribute name', ...]
      config.query.render.ial.fields.ignore Define the attribute names of IAL that can be rendered (whitelist). If empty, render all fields except for those on the blacklist. ['attribute name', ...]
      config.query.limit Define the display style of the query results for the content or markdown fields. null
      'row'
      'len'
      No limit.
      Limit by number of rows.
      Limit by length.
      config.query.maxlen Define the maximum length of the query result for the content or markdown field. Positive integer. Enabled when config.query.limit: 'len' is set.
      config.query.maxrow Define the maximum number of rows of the query result for the content or markdown field. Positive integer. Enabled when config.query.limit: 'row' is set.
      config.query.fields Define the fields to be displayed in the query results and their order of arrangement. ['field name', ...] See details for field names in blocks
      config.query.style.table.attributes Define the block attributes of the query result table, which can be used to set custom styles. [{enable: true/false, key: 'block attribute name', value: 'block attribute value'}, ...]
      config.query.style.column.* Define the style of a column in the query result table. {: style="width: 512px"} Specify the width of a column in the query result.
      config.query.style.align.* Define the alignment of a column in the query result table. :-
      :-:
      -:
      Left-align a column.
      Center-align a column.
      Right-align a column.
      config.query.filter.blocks Define a filter sequence to filter out some query results. {enable: true/flase, handlers: [(row, data) => {}, ...]} Return true for items that need to be filtered out; return false for items that need to be retained.
      config.query.handler.* Define the processing function of a specific field value. (row, ial) => {} row: current record.
      ial: the inline attribute list parsed from the current query record.
      config.query.map.* Query result mapping table, replace some query result field values with human-readable fields.
  5. Queries that do not conform to the default block query mode are normal query mode

    • Example:

      • Query the help document 请从这里开始 and all of its subordinate documents.

        SELECT
            '[' || b.content || '](siyuan://blocks/' || b.id || ')' AS __1____pre__文档标题,
            b.hpath AS __2__文档路径
        FROM
            blocks AS b
        WHERE
            b.type = 'd'
            AND b.hpath LIKE '%请从这里开始%'
        ORDER BY
            b.path
        LIMIT 10
        Document Title Document Path
        1 Getting Started /Getting Started
        2 Editor /Getting Started/Editor
        3 Layout Elements /Getting Started/Editor/Layout Elements
        4 Content Blocks /Getting Started/Content Blocks
        5 What are Content Blocks? /Getting Started/Content Blocks/What are Content Blocks?
        6 Referencing Content Blocks /Getting Started/Content Blocks/Referencing Content Blocks
        7 Navigating within Content Blocks /Getting Started/Content Blocks/Navigating within Content Blocks
        8 Content Block Types /Getting Started/Content Blocks/Content Block Types
        9 Embedding Content Blocks /Getting Started/Content Blocks/Embedding Content Blocks
        10 Converting between Document Blocks and Title Blocks /Getting Started/Content Blocks/Converting between Document Blocks and Title Blocks
      • Query all tables in Siyuan database.

        SELECT
            *
        FROM
            sqlite_master
        name rootpage sql tbl_name type
        1 stat 2 CREATE TABLE stat (key, value) stat table
        2 blocks 3 CREATE TABLE blocks (id, parent_id, root_id, hash, box, path, hpath, name, alias, memo, content, markdown, length, type, subtype, ial, sort, created, updated) blocks table
        3 spans 4 CREATE TABLE spans (id, block_id, root_id, box, path, content, markdown, type, ial) spans table
        4 assets 5 CREATE TABLE assets (id, block_id, root_id, box, docpath, path, name, title, hash) assets table
        5 attributes 6 CREATE TABLE attributes (id, name, value, type, block_id, root_id, box, path) attributes table
        6 refs 7 CREATE TABLE refs (id, def_block_id, def_block_parent_id, def_block_root_id, def_block_path, block_id, root_id, box, path, content, markdown, type) refs table
        7 file_annotation_refs 8 CREATE TABLE file_annotation_refs (id, file_path, annotation_id, block_id, root_id, box, path, content, type) file_annotation_refs table
      • Query all fields in the blocks table of Siyuan database.

        PRAGMA table_info('blocks')
        cid dflt_value name notnull pk type
        1 id
        2 1 parent_id
        3 2 root_id
        4 3 hash
        5 4 box
        6 5 path
        7 6 hpath
        8 7 name
        9 8 alias
        10 9 memo
        11 10 content
        12 11 markdown
        13 12 length
        14 13 type
        15 14 subtype
        16 15 ial
        17 16 sort
        18 17 created
        19 18 updated
      • Customized property views.

        SELECT
            '[' || b.content || '](siyuan://blocks/' || a.block_id || ')' AS __1____pre__Title,
            MAX(
                CASE
                    WHEN a.name = 'name' THEN a.value
                    ELSE NULL
                END
            ) AS __2____kbd__Name,
            MAX(
                CASE
                    WHEN a.name = 'alias' THEN REPLACE(
                        '<kbd>' || a.value || '</kbd>',
                        ',',
                        '</kbd><br/><kbd>'
                    )
                    ELSE NULL
                END
            ) AS __3____pre__Alias,
            MAX(
                CASE
                    WHEN a.name = 'memo' THEN REPLACE(
                        '<kbd>' || a.value || '</kbd>',
                        ',',
                        '</kbd><br/><kbd>'
                    )
                    ELSE NULL
                END
            ) AS __3____pre__Memo
        FROM
            attributes AS a
            INNER JOIN blocks AS b ON a.block_id = b.id
        WHERE
            (
                a.name = 'name'
                OR a.name = 'alias'
                OR a.name = 'memo'
            )
            AND b.type = 'd'
        GROUP BY
            a.block_id
        ORDER BY
            RANDOM()
        LIMIT
            10;

        Customize properties view

    • Use field aliases prefix to define query display styles.

      • __hidden__alias0:

        • This field does not appear in the query results.
      • __ref__alias1:

        • The field is rendered as a block reference.
        • example: ((<value> "<value>"))
      • __link__alias2:

        • The field is rendered as a block link.
        • example: [<value>](siyuan://blocks/<value>)
      • __raw__alias3:

        • The field is rendered as the original value (inline code style).
        • example: <value>
      • __date__alias4:

        • The field is rendered as a date.
        • example: yyyy-MM-dd
      • __time__alias5:

        • The field is rendered as a time.
        • example: HH:mm:ss
      • __datetime__alias6:

        • The field is rendered as a datetime.
        • example: yyyy-MM-dd HH:mm:ss
      • ___s__alias7:

        • The field is rendered as strikethrough.
        • example: ~~<value>~~
      • ___u__alias8:

        • The field is rendered as underline.
        • example: <u><value></u>
      • ___em__alias9:

        • The field is rendered as emphasis.
        • example: *<value>*
      • __tag__alias10:

        • The field is rendered as tag.
        • example: <kbd><value></kbd>
      • __kbd__alias11:

        • The field is rendered as keyboard.
        • example: ~<value>~
      • __sub__alias12:

        • The field is rendered as subscript.
        • example: ^<value>^
      • __sup__alias13:

        • The field is rendered as superscript.
        • example: #<value>#
      • __code__alias14:

        • The field is rendered as inline code.
        • example: <value>
      • __mark__alias15:

        • The field is rendered as mark.
        • example: ==<value>==
      • __math__alias16:

        • The field is rendered as math formula.
        • example: $<value>$
      • __strong__alias17:

        • The field is rendered as weightbold.
        • example: **<value>**
      • __pre__alias7:

        • The field is rendered as a preview (rendering the markdown row-level identifier).
        • example: <value>
    • Use field aliases prefix to define query result field order.

      • __<number>__alias8:

        • The field can be placed in front of the query style prefix field.

        • example:

          • __1____pre__alias9
          • __02____raw__alias10
    • Displays the original value of the query result by default (using inline code).

    • Example of a field alias prefix

      • SELECT
            b.id AS __00____ref__ref,
            b.id AS __01____link__link,
            b.id AS __02____pre__pre,
            b.id AS __03____raw__raw,
            b.created AS __04____date__date,
            b.created AS __05____time__time,
            b.created AS __06____datetime__datetime,
            b.id AS __07____s__s,
            b.id AS __08____u__u,
            b.id AS __09____em__em,
            b.id AS __10____tag__tag,
            b.id AS __11____kbd__kbd,
            b.id AS __12____sub__sub,
            b.id AS __13____sup__sup,
            b.id AS __14____code__code,
            b.id AS __15____mark__mark,
            b.id AS __16____math__math,
            b.id AS __17____strong__strong
        FROM
            blocks AS b
        WHERE
            id = '.root{.id}'

        Example of a field alias prefix

  6. Partial template field parsing support

    • .<prefix>{.<field>}

      • <prefix>: prefix field

        • block: Widget block.
        • parent: The parent block of the pendant block.
        • root: The document block in which the pendant block resides.
      • <field>: attribute field

        • The field name of the blocks table in the database.
      • Example: SELECT * FROM blocks WHERE id = '.root{.id}' content LIKE '%content blocks%'

        • Equivalent to SELECT * FROM blocks WHERE id = '.block{.root_id}' content LIKE '%content blocks%'
        • Query all blocks in the document where the pendant is located that contain the word content blocks

CUSTOM CONFIG

GLOBAL CUSTOM CONFIG

  1. Create a file <workspace>/data/widgets/custom.js
  2. The value defined in file <workspace>/data/widgets/custom.js overwrites the corresponding value in file <workspace>/data/widgets/Query/src/script/module/config.js.

CONFIG EXAMPLE

/**
 * File Path
 *    <workspace>/data/widgets/custom.js
 * Example function:
 *    Block attributes are displayed in columns in the default block query results.
 */

export const config = {
    query: {
        render: {
            ial: {
                shape: 'columns',
            },
        },
    },
};

For more configuration items, see config.js

BLOCK CUSTOM CONFIG

  • Set custom block attributes in the widget block(the Query button in your note).

    • Custom block attribute names are property in the config object under the file src/script/module/config.js.
    • For example, if you want to customize the query result field list, you can set the custom block attribute query-fields: ["hpath", "type", "markdown"], where the config.query.fields property will be replaced in this block.

START

The widget has been put on the shelves at SiYuan community bazaar and can be installed directly in the Bazaar.

REFERENCE & THANKS

Author Project License
leolee9086 leolee9086/cc-baselib Unknown

PS: Sort in no particular order.

CONTRIBUTORS

leolee9086
leolee9086

🐛 💻
jpanda-cn
jpanda-cn

💻 🤔
Wang Yong
Wang Yong

📖
banjuer
banjuer

🤔
Tlonglan
Tlonglan

🤔
Tim Zhang
Tim Zhang

🤔 💻
lmmxj
lmmxj

🤔
Frostime
Frostime

💻

PS: The table is generated automatically using All Contributors · GitHub, go to emoji key to see the contribution type.

CHANGE LOG

CHANGE LOG

About

一个将思源笔记数据库查询结果以表格样式渲染的挂件 | A widget that renders the query results of the Siyuan Notes database in tabular style.

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published