查询不在数据库中的文档

see https://ld246.com/article/1725076848647​

效果

​​

代码

//!js
// see https://ld246.com/article/1725076848647
return (async () => {
    //////// 主逻辑区 ////////////

    // 待查询的SQL语句
    const sql = `
        select *
        from blocks
        -- 仅筛选文档块
        where type = 'd'
        -- 查询某文档下的子文档，20240810121622-4u53t7u是父文档的id
        -- and path like '/20240810121622-4u53t7u%'
        -- 不包含父文档自身，如果想包含父文档自身，把这行注释即可
        -- and id != '20240810121622-4u53t7u'
        -- 排除文档id在数据库中的文档数据
        and id not in (${await getDBDocIds()})
    `;

    // 执行SQL并返回块ids
    const result = await getDataBySql(sql);
    // 返回块标题
    formatByTitle(result);
    // 返回结果
    return result.map(item => item.id);

    ///////////// 功能函数区 ///////////////

    // 结果显示文档标题
    function formatByTitle(result) {
        const data = {};
        result.forEach(item=>{
            data[item.id] = item;
        });
        const { disconnect } = monitorBlockChanges(item, (addedNodes) => {
            addedNodes.forEach(add=>{
                add.innerHTML=`<div data-node-index="1" data-type="NodeParagraph" class="p" updated=""><div contenteditable="true" spellcheck="false"><span data-type="block-ref" data-id="${add.dataset.id}" data-subtype="d" style="">${data[add.dataset.id].content}</span><span style="margin-left:20px;color:#666;">${data[add.dataset.id].hpath}</span><span style="float:right;color:#666;">${formatDateTime(data[add.dataset.id].created)}</span></div><div class="protyle-attr" contenteditable="false">​</div></div>`
            });
            //console.log('Added nodes:', addedNodes);
            disconnect();
        });
    }

    function formatDateTime(str) {
        const year = str.substring(0, 4);
        const month = str.substring(4, 6);
        const day = str.substring(6, 8);
        const hours = str.substring(8, 10);
        const minutes = str.substring(10, 12);
        const seconds = str.substring(12);
        return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
    }

    async function getDBDocIds() {
        //查找所有数据库
        const dbs = await getDataBySql(`SELECT * FROM blocks where type ='av'`);
        const avPaths = dbs.map(av => getDataAvIdFromHtml(av.markdown));
        //查找在数据库中的所有文档id
        let dbDocIds = [];
        for (const path of avPaths) {
            const data = await getFile(`/data/storage/av/${path}.json`);
            const docIds = data.keyValues[0].values.map(item => item.block.id);
            dbDocIds.push(...docIds);
        }
        dbDocIds = [...new Set(dbDocIds)];
        return dbDocIds.map(item => `'${item}'`).join(',');
    }

    // 读取文件
    async function getFile(storagePath) {
        if(!storagePath) return {};
        const data = await fetchSyncPost('/api/file/getFile', {"path":`${storagePath}`});
        if(data.code && data.code !== 0) return {};
        return data;
    }

    // 获取avid
    function getDataAvIdFromHtml(htmlString) {
        // 使用正则表达式匹配data-av-id的值
        const match = htmlString.match(/data-av-id="([^"]+)"/);
        if (match && match[1]) {
        return match[1];  // 返回匹配的值
        }
        return "";  // 如果没有找到匹配项，则返回空
    }

    // 通过SQL查询数据
    async function getDataBySql(sql) {
        const result = await fetchSyncPost('/api/query/sql', {"stmt": sql});
        if(result.code !== 0){
            console.error("查询数据库出错", result.msg);
            return [];
        }
        return result.data;
    }

    // 请求api
    async function fetchSyncPost (url, data) {
        const init = {
            method: "POST",
        };
        if (data) {
            if (data instanceof FormData) {
                init.body = data;
            } else {
                init.body = JSON.stringify(data);
            }
        }
        try {
            const res = await fetch(url, init);
            const res2 = await res.json();
            return res2;
        } catch(e) {
            console.log(e)
            return [];
        }
    }

    // 监控嵌入块变化
    function monitorBlockChanges(element, onChange) {
        const observer = new MutationObserver(mutationsList => {
            for (const mutation of mutationsList) {
                if (mutation.type === 'childList') {
                    // 过滤 addedNodes 中包含指定类名的元素
                    const addedNodesWithClassName = Array.from(mutation.addedNodes).filter(node => {
                        return node.nodeType === Node.ELEMENT_NODE && node.classList.contains('protyle-wysiwyg__embed');
                    });
                    // 如果有符合条件的节点，则调用回调函数
                    if (addedNodesWithClassName.length > 0) {
                        onChange(addedNodesWithClassName, mutation.removedNodes);
                    }
                }
            }
        });
        observer.observe(element, { childList: true, subtree: true });
        return {
            disconnect: () => observer.disconnect()
        };
    }
})()

‍