Additional Database Support - Support More DB Dielcts (mysql)

[Yedidyar]

e.g

import { KnexDataProvider } from "remult/remult-knex";

export async function getEntityTypescriptFromSqlServer(db: KnexDataProvider, table: string) {
    let cols = '';
    let props = [];
    props.push("allowApiCrud: true");

[jycouet]

The full code

import { KnexDataProvider } from "remult/remult-knex";

export async function getEntityTypescriptFromSqlServer(db: KnexDataProvider, table: string) {
    let cols = '';
    let props = [];
    props.push("allowApiCrud: true");
    if (table.toLocaleLowerCase() != table) {
        //  props.push("dbName: '\"" + table + "\"'");
    }

    let first: string = undefined!;
    for (const { COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, NUMERIC_PRECISION, CHARACTER_MAXIMUM_LENGTH } of
        await db.knex('INFORMATION_SCHEMA.COLUMNS').select()
            .where('TABLE_NAME', table).orderBy("ORDINAL_POSITION")) {

        let decorator = '@Fields.string';
        let decoratorArgs = '';

        let type = '';
        let defaultVal = "''";
        switch (DATA_TYPE) {
            case "decimal":
            case "real":
            case "int":
            case "smallint":
            case "tinyint":
            case "bigint":
            case "float":
            case "numeric":
            case "NUMBER":
            case "money":
                if (NUMERIC_PRECISION === 0)
                    decorator = "@Fields.integer";
                else
                    decorator = "@Fields.number";
                defaultVal = '0';
                break;
            case "nchar":
            case "nvarchar":
            case "ntext":
            case "NVARCHAR2":
            case "text":
            case "varchar":
            case "VARCHAR2":

                break;
            case "char":
            case "CHAR":
                console.log({
                    CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT, DATA_TYPE,
                    COLUMN_NAME
                })
                if (CHARACTER_MAXIMUM_LENGTH == 8 && COLUMN_DEFAULT == "('00000000')") {
                    decorator = "@Fields.dateOnly";
                    type = 'Date';
                }
                break;
            case "DATE":
            case "datetime":
            case "datetime2":
                decorator = "@Fields.date"
                type = 'Date';
                break;
            case "bit":
                decorator = "@Fields.boolean"
                break;
            default:
                console.log(DATA_TYPE);
                break;

        }

        if (false && (COLUMN_NAME.toLocaleLowerCase() != COLUMN_NAME || COLUMN_NAME == 'order'))
            decoratorArgs = `{ dbName: '"${COLUMN_NAME}"' }`;
        if (!first)
            first = COLUMN_NAME;
        cols += '\n\n\t' + decorator + `(${decoratorArgs})\n\t` + COLUMN_NAME;
        if (!defaultVal) {
            cols += '!';
            cols += ': ';
            cols += type;
        }
        if (defaultVal)
            cols += ' = ' + defaultVal;
        cols += ';';
    }
    props.push(`defaultOrderBy: { ${first}: "asc" }`);
    let r =
        `import { Entity, Fields, EntityBase } from "remult";
@Entity<${table}>("${table}", { \n\t${props.join(',\n\t')} \n}) 
export class ${table} extends EntityBase {` + cols + '\n}'.replace('\t', '');
    return r;
}

/*
TABLE_CATALOG: 'Syata_Data_Afcon',
[NODE-API-SERVER]     TABLE_SCHEMA: 'dbo',
[NODE-API-SERVER]     TABLE_NAME: 'Contacts_And_Customers',
[NODE-API-SERVER]     COLUMN_NAME: 'cc_Tel1_cdate',
[NODE-API-SERVER]     ORDINAL_POSITION: 55,
[NODE-API-SERVER]     COLUMN_DEFAULT: "('')",
[NODE-API-SERVER]     IS_NULLABLE: 'NO',
[NODE-API-SERVER]     DATA_TYPE: 'varchar',
[NODE-API-SERVER]     CHARACTER_MAXIMUM_LENGTH: 23,
[NODE-API-SERVER]     CHARACTER_OCTET_LENGTH: 23,
[NODE-API-SERVER]     NUMERIC_PRECISION: null,
[NODE-API-SERVER]     NUMERIC_PRECISION_RADIX: null,
[NODE-API-SERVER]     NUMERIC_SCALE: null,
[NODE-API-SERVER]     DATETIME_PRECISION: null,
[NODE-API-SERVER]     CHARACTER_SET_CATALOG: null,
[NODE-API-SERVER]     CHARACTER_SET_SCHEMA: null,
[NODE-API-SERVER]     CHARACTER_SET_NAME: 'cp1255',
[NODE-API-SERVER]     COLLATION_CATALOG: null,
[NODE-API-SERVER]     COLLATION_SCHEMA: null,
[NODE-API-SERVER]     COLLATION_NAME: 'Hebrew_CI_AS',
[NODE-API-SERVER]     DOMAIN_CATALOG: null,
[NODE-API-SERVER]     DOMAIN_SCHEMA: null,
[NODE-API-SERVER]     DOMAIN_NAME: null
*/

[noam-honig]

Here are the relevant tables for mysql: I think it's: INFORMATION_SCHEMA.TABLES INFORMATION_SCHEMA.Columns

See: INFORMATION_SCHEMA.TABLES

Here's a code sandbox with a working mysql db: https://codesandbox.io/p/sandbox/docker-demo-with-maria-db-g42rqz