How to build search_terms table rows in mysql?

[yoyokko]

I am transferring documents from doku.wiki to BookStack.
Refer : https://blog.phoenixlzx.com/2018/03/23/migrate-dokuwiki-to-bookstack/

Here my import.js which import xhtml page to BookStack database and changed datetime field default value to NOW in mysql table structure.

    "use strict";

    const util = require('util');
    const mysql = require('mysql');
    const fs = require("fs");
    const path = require("path");
    const htmlToText = require('html-to-text');

    async function execute_sql(connection, sql) {
        return new Promise((resolve, reject) => {
            connection.query(sql, (error, results, fields) => {
                resolve([error, results, fields]);
            })
        })
    }

    async function findBookIdByName(connection, bookName) {
        var sql = util.format('SELECT id FROM books WHERE `name` = "%s"', bookName);
        var [error, results, fields] = await execute_sql(connection, sql);
        return results[0] && results[0].id;
    }

    async function findChapterIdByNameInBook(connection, bookId, chapterName) {
        var sql = util.format('SELECT id FROM chapters WHERE `book_id` = %d AND `name` = "%s"', bookId, chapterName);
        var [error, results, fields] = await execute_sql(connection, sql);
        return results[0] && results[0].id;
    }

    async function findPageByNameInBookChapter(connection, bookId, chapterId, pageName) {
        var sql = util.format('SELECT id FROM pages WHERE `book_id` = %d AND `chapter_id` = %d AND `name` = "%s"', bookId, chapterId, pageName);
        var [error, results, fields] = await execute_sql(connection, sql);
        return results[0] && results[0].id;
    }

    async function createBook(connection, bookName) {
        var book_id = await findBookIdByName(connection, bookName);
        if (book_id) {
            return book_id;
        }
        var sql = util.format('INSERT INTO books(`name`, `slug`,  `created_by`) VALUES("%s", "%s", 3)', bookName, bookName)
        var [error, results, fields] = await execute_sql(connection, sql);
        // find book id
        return results.insertId;
    }

    async function createChapter(connection, bookId, chapterName) {
        var chapterId = await findChapterIdByNameInBook(connection, bookId, chapterName);
        if (chapterId) {
            return chapterId;
        }
        var sql = util.format('SELECT MAX(priority) as priority FROM chapters WHERE `book_id` = %d', bookId);
        var [error, results, fields] = await execute_sql(connection, sql);
        var priority = results && results[0].priority;
        if (undefined === priority) {
            priority = 1;
        }
        sql = util.format('INSERT INTO chapters(`book_id`, `name`, `slug`, `created_by`, `priority`) VALUES("%d", "%s", "%s", 3, %d)', bookId, chapterName, chapterName, priority);
        [error, results, fields] = await execute_sql(connection, sql);

        // find chapter id
        return results.insertId;
    }

    async function createPage(connection, bookId, chapterId, pageHtmlPath) {
        var pageHtml = fs.readFileSync(pageHtmlPath, "utf8");
        var pageText = htmlToText.fromString(pageHtml);
        var pageName = pageText.substr(0, pageText.indexOf('\n'));
        if (pageName.length != 0) {
            pageHtml = pageHtml.substr(pageHtml.indexOf('\n', 1) + 1);
            pageText = pageText.substr(pageText.indexOf('\n') + 1);
        } else {
            pageName = path.basename(pageHtmlPath, 'html');
        }
        pageName = pageName.replace(/ /g, '')
        console.log('\t' + pageName);
        var pageId = await findPageByNameInBookChapter(connection, bookId, chapterId, pageName);
        if (pageId) {
            return pageId;
        }
        var sql = util.format('INSERT INTO pages(`book_id`, `chapter_id`, `name`, `slug`, `html`, `text`, `created_by`) VALUES("%d", "%d", "%s", "%s", %s, %s, 3)', 
                bookId, chapterId, pageName, pageName, connection.escape(pageHtml), connection.escape(pageText));
        var [error, results, fields] = await execute_sql(connection, sql);
        if (error) {
            console.log(error);
        }
        return results.insertId;
    }

    function readDirSync(path) {
        var folders = [],
            files = [];
        var pa = fs.readdirSync(path);
        pa.forEach(function (ele, index) {
            var info = fs.statSync(path + "/" + ele)
            if (info.isDirectory()) {
                folders.push(ele);
            } else if (ele.endsWith('html')) {
                files.push(ele);
            }
        });
        return [folders, files];
    }

    async function load_wiki_books(connection) {
        var books = {};
        var root = path.join(__dirname, 'output');

        var [books, files] = readDirSync(root);
        for (var idx in books) {
            var book = books[idx];
            if (['uploads', 'test', 'user', 'wiki', 'standards', 'resource'].includes(book)) {
                continue;
            }
            // create book
            console.log(book);
            var bookId = await createBook(connection, book);
            var [chapters, book_pages] = readDirSync(path.join(root, book));
            for (var idx in book_pages) {
                var p = book_pages[idx];
                if (['sidebar.html'].includes(p)) {
                    continue;
                }
                // create page in book
                console.log("  |--" + p);
                createPage(connection, bookId, 0, path.join(root, book, p));
            };

            for (var idx in chapters) {
                var chapter = chapters[idx];
                // create chapter
                console.log("  |--" + chapter);
                var chapterId = await createChapter(connection, bookId, chapter);
                var [nouse, chapter_pages] = readDirSync(path.join(root, book, chapter));
                for (var idx in chapter_pages) {
                    // create page in chapter
                    var p = chapter_pages[idx];
                    if (['sidebar.html'].includes(p)) {
                        continue;
                    }
                    console.log("    |--" + p);
                    createPage(connection, bookId, chapterId, path.join(root, book, chapter, p));
                };
            };
        };
    }


    async function main() {
        var connection = mysql.createConnection({
            host: '',
            port: ,
            user: '',
            password: '',
            database: ''
        });

        connection.connect();
        await load_wiki_books(connection);
        connection.end();
    }

    main();

Everything works fine except search function. I need to reedit the page to make it searchable.

How can I build the search_terms table from code without reedit or simulate the post method?

[ssddanbrown]

@yoyokko Running php artisan bookstack:regenerate-search should rebuild the search index.

A full list of BookStack based commands can be seen here:
https://www.bookstackapp.com/docs/admin/commands/

[ssddanbrown]

Since it's been a month I'm going to assume the above worked for you so I'll close this issue. If you're still having issues rebuilding the search terms feel free to comment and this can be re-opened.