automation_script/scripts/auto_update_label.js

const db = require('../util/db');

async function getAllLabels() {
    console.log('开始执行脚本');
    let map = {};

    try {
        // 获取所有表名
        const [tables] = await db.query(`
            select table_name 
            from information_schema.tables 
            where table_schema = 'owl' 
            AND table_name like 'biz_%' 
            and table_name not LIKE 'admin_%' 
            and table_name not LIKE 'user_%' 
            and table_name not LIKE 'label_%' 
            and table_name not LIKE 'label_record_%' 
            and table_name not LIKE 'label_record_detail_%';
        `);

        if (tables.length === 0) {
            console.log('没有找到符合条件的表');
            return {};
        }

        // 使用 for...of 循环来按顺序处理每个表
        for (const table of tables) {
            // 获取每个表的所有字段的名称和备注
            const [columns] = await db.query(`
                select column_name, column_comment 
                from information_schema.columns 
                where table_schema = 'owl' 
                and table_name = '${table.TABLE_NAME}' 
                order by ordinal_position;
            `);

            if (columns.length === 0) {
                continue;
            }

            columns.forEach(column => {
                // 处理注释中包含冒号的情况
                const comment = column.COLUMN_COMMENT || '';
                const processedComment = comment.split(/[：:]/, 1)[0];
                map[column.COLUMN_NAME] = processedComment;
            });
        }

        return map;

    } catch (err) {
        console.error('执行脚本时出错:', err);
        return null;
    }
}

async function getAllPagesJson() {
    try {
        // 查询所有页面json
        const [pages] = await db.query(`
            select id, \`schema\` 
            from admin_pages;
        `);

        if (pages.length === 0) {
            console.log('没有找到页面json');
            return null;
        }

        return pages.map(page => { return { id: page.id, schema: page.schema } });

    } catch (err) {
        console.error('查询页面json时出错:', err);
        return null;
    }
}


// 创建主函数并执行
async function main() {
    const dict = await getAllLabels();
    dict['id'] = '编号';
    dict['created_at'] = '创建时间';
    dict['updated_at'] = '更新时间';
    dict['deleted_at'] = '删除时间';

    for (const key in dict) {
        if (key.indexOf('_id') != -1) {
            dict[key] = key.split('_id')[0] + '关联对象';
        }
    }

    console.log(JSON.stringify(dict, null, 2));

    const pages = await getAllPagesJson();
    console.log(JSON.stringify(pages, null, 2));

    if (pages && dict) {
        for (const page of pages) {
            let { id, schema } = page;
            for (const [columnName, columnComment] of Object.entries(dict)) {
                const labelRegex = new RegExp(`"label":"${columnName}"`, 'g');
                const titleRegex = new RegExp(`"title":"${columnName}"`, 'g');
                schema = schema.replace(labelRegex, `"label":"${columnComment}"`);
                schema = schema.replace(titleRegex, `"title":"${columnComment}"`);
            }

            try {
                await db.query(`
                    UPDATE admin_pages
                    SET \`schema\` = ?
                    WHERE id = ?;
                `, [schema, id]);
                console.log(`页面ID ${id} 的schema更新成功`);
            } catch (err) {
                console.error(`更新页面ID ${id} 的schema时出错:`, err);
            }
        }
    }
}

main().catch(err => {
    console.error('主函数执行出错:', err);
});