typescript-mysql-model/lib/model-builder.js

"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
const change_case = require("change-case");
const LIST_TYPE_VIEW = "VIEW";
const LIST_TYPE_TABLE = "BASE TABLE";
class ModelBuilder {
    constructor(knex, databaseName) {
        this.knex = knex;
        this.databaseName = databaseName;
    }
    static init(knex) {
        return new ModelBuilder(knex).renderDatabaseSchema();
    }
    /**
     * Return a copy of an object but with all keys lower case
     */
    static keysToLower(obj) {
        const newobj = {};
        Object.keys(obj).forEach(key => newobj[key.toLowerCase()] = obj[key]);
        return newobj;
    }
    /**
     * Return the length of a type, eg varchar(255)=>255
     * 0 if not parsable
     * @param type
     */
    static getMysqlLength(strType) {
        const strip = strType.replace(/\D/g, "");
        return strip.length === 0 ? 0 : parseInt(strip, undefined);
    }
    /**
     * Return the length of a type, eg varchar(255)=>varchar
     * @param type
     */
    static stripMysqlLength(type) {
        const pos = type.indexOf("(");
        if (pos > -1) {
            return type.substr(0, pos);
        }
        return type;
    }
    static getEnumValues(type) {
        if (type.startsWith("enum")) {
            const strList = type.replace("enum(", "").replace(")", "");
            const list = strList.split(",").map(s => s.replace("'", "").replace("'", ""));
            return list;
        }
        return null;
    }
    async renderDatabaseSchema() {
        if (!this.databaseName) {
            this.databaseName = await this.getDatabaseName();
        }
        const schema = {
            storedProcedures: await this.renderStoredProcedures(),
            tables: await this.renderTableModel(),
            views: await this.renderViewModel()
        };
        return schema;
    }
    async getDatabaseName() {
        const resp = await this.knex.raw("SELECT DATABASE() as db");
        return resp[0][0].db;
    }
    /**
     * return a select query to list all tables or views from a database
     */
    listFromDatabase(listType) {
        if (listType !== "BASE TABLE" && listType !== "VIEW") {
            throw new Error("Illegal listtype");
        }
        const select = "`information_schema`.`TABLES`.`TABLE_NAME` AS `tname`";
        const from = "`information_schema`.`TABLES`";
        const dbClause = "`information_schema`.`TABLES`.`TABLE_SCHEMA` = '" + this.databaseName + "'";
        const baseTable = "`information_schema`.`TABLES`.`TABLE_TYPE` = '" + listType + "'";
        return `SELECT ${select} FROM ${from} WHERE ${dbClause} AND ${baseTable} `;
    }
    async listViews() {
        const rows = await this.knex.raw(this.listFromDatabase(LIST_TYPE_VIEW));
        return rows[0].map(item => item.tname);
    }
    /**
     * Lists all the tables in current database
     */
    async listTables() {
        const rows = await this.knex.raw(this.listFromDatabase(LIST_TYPE_TABLE));
        return rows[0].map(item => item.tname);
    }
    columnArrayToDatabaseSchema(colArrMap) {
        const schema = {};
        for (const tableName in colArrMap) {
            colArrMap[tableName] = colArrMap[tableName].map((col, i) => {
                col = ModelBuilder.keysToLower(col);
                col.length = ModelBuilder.getMysqlLength(col.type);
                col.enumValues = ModelBuilder.getEnumValues(col.type);
                col.isPrimary = col.key === "PRI";
                col.index = i;
                col.type = ModelBuilder.stripMysqlLength(col.type);
                return col;
            });
            const newTable = {};
            colArrMap[tableName].forEach(col => newTable[col.field] = col);
            schema[tableName] = newTable;
        }
        return schema;
    }
    /**
     * List all columns for a table given table name
     */
    async listColumns(tableName) {
        return await this.knex.raw("SHOW COLUMNS FROM " + tableName).then(colData => colData[0]);
    }
    async renderModel(tables) {
        // TODO list all in one query instead of one query per table
        const columnArrayMap = {};
        const promises = tables.map(async (tableName) => {
            columnArrayMap[tableName] = await this.listColumns(tableName);
        });
        await Promise.all(promises);
        return this.columnArrayToDatabaseSchema(columnArrayMap);
    }
    async listStoredProcedures() {
        const SHOW_DB_QUERY = `SHOW PROCEDURE STATUS WHERE Db = ?`;
        const sps = await this.knex.raw(SHOW_DB_QUERY, [this.databaseName]);
        return sps[0].map(sp => sp.Name);
    }
    async listStoredProcedureParams() {
        const LIST_PARAM_QUERY = `SELECT * FROM information_schema.parameters WHERE specific_schema = ?`;
        const params = await this.knex.raw(LIST_PARAM_QUERY, [this.databaseName]);
        return params[0].map(item => {
            const copy = {};
            for (const key in item) {
                copy[change_case.camelCase(key)] = item[key];
            }
            return copy;
        });
    }
    async renderStoredProcedures() {
        const storedProcedures = await this.listStoredProcedures();
        const mapped = await this.listStoredProcedureParams();
        const storedProcedureDictionary = {};
        storedProcedures.forEach(spName => storedProcedureDictionary[spName] = { name: spName, parameters: {} });
        mapped.forEach(item => storedProcedureDictionary[item.specificName].parameters[item.parameterName] = item);
        return storedProcedureDictionary;
    }
    async renderViewModel() {
        const tables = await this.listViews();
        return await this.renderModel(tables);
    }
    async renderTableModel() {
        const tables = await this.listTables();
        return this.renderModel(tables);
    }
}
exports.default = ModelBuilder;
//# sourceMappingURL=model-builder.js.map