How to build search_terms table rows in mysql? #784

New Issue

OVERLORD · 2026-02-04T22:16:58+03:00

OVERLORD commented

2026-02-04 22:16:58 +03:00

Originally created by @cunba-ai on GitHub (Aug 21, 2018).

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?

Originally created by @cunba-ai on GitHub (Aug 21, 2018). 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?

OVERLORD added the ❓ Question label 2026-02-04 22:16:58 +03:00

OVERLORD closed this issue

2026-02-04 22:17:03 +03:00

OVERLORD commented

2026-02-04 22:17:07 +03:00

@ssddanbrown commented on GitHub (Aug 21, 2018):

@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 commented on GitHub (Aug 21, 2018): @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/

OVERLORD commented

2026-02-04 22:17:15 +03:00

@ssddanbrown commented on GitHub (Sep 23, 2018):

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.

@ssddanbrown commented on GitHub (Sep 23, 2018): 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.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: starred/BookStack#784