mysql to ddl

[johnb8005]

index.js

const Parser = require('sql-ddl-to-json-schema');
const fsp = require('fs').promises;

const Utils = require('./utils');
const Types = require('../types');

const transformColumn = (c) => {
  const {name, type, options} = c;

  const columnName = name;
  const fieldName = Utils.snakeCaseToCamel(name).replace(' ', '');

  const fieldType = Types.sqlToJvmType(type.datatype);

  const isOptional = options.nullable === true || ('default' in options);

  const columnInfo = {
    arg: fieldName,
    type: fieldType,
    optional: isOptional,
    uuid: false,
  };

  if (Utils.camelCaseToSnake(fieldName) !== columnName)
    columnInfo.column = columnName;

  return columnInfo;
};

const transformEntity = (entity) => {
  const {name, columns} = entity;

  const tableName = name;
  const entityName = Utils.capitalizeFirst(Utils.snakeCaseToCamel(name));

  const params = columns.map(transformColumn);

  if (!params.find(e => e.arg === 'id')) {
    return {
      warning: `${entityName} does not define a numeric id`
    }
  }

  const tableInfo = {
    name: entityName,
    params: params.filter(e => e.arg !== 'id')
  };

  if (Utils.camelCaseToSnake(entityName) !== tableName) {
    console.log(entityName)
    tableInfo.table = tableName;
  }

  return {
    entity: tableInfo
  }
};

const jsonFromDdlFile = async filepath => {
  const sql = await fsp.readFile(filepath, "utf8");

  const parser = new Parser('mysql').feed(sql);

  const sqlAbstractSyntaxTree = parser.results;
  const compactJsonTablesArray = parser.toCompactJson(sqlAbstractSyntaxTree);

  const transformedEntities = compactJsonTablesArray.map(transformEntity)

  return {
    entities: transformedEntities.filter(e => 'entity' in e).map(w => w.entity),
    warnings: transformedEntities.filter(e => 'warning' in e).map(w => w.warning),
    inputJson: compactJsonTablesArray,
    inputJsonAst: sqlAbstractSyntaxTree
  }
};

module.exports = {jsonFromDdlFile};

utils.js

const path = require('path');

/**
 *
 * @see https://stackoverflow.com/questions/30521224/javascript-convert-pascalcase-to-underscore-case
 */
const camelCaseToSnake = (snakeStr) => snakeStr.replace(/\.?([A-Z]+)/g, function (x, y) {
  return "_" + y.toLowerCase()
}).replace(/^_/, "");

// https://stackoverflow.com/questions/40710628/how-to-convert-snake-case-to-camelcase-in-my-app
const snakeCaseToCamel = (d) => {
  return d.replace(/(\_\w)/g, (k) => {
    return k[1].toUpperCase();
  })
};

const uncapitalizeFirst = (s) => {
  const f = s[0].toLocaleLowerCase();
  return f + s.slice(1);
};

const capitalizeFirst = (s) => {
  const f = s[0].toLocaleUpperCase();
  return f + s.slice(1);
};

const addParenthesis = (x, preOrPost) => {
  const getParenthesis = p => {
    return preOrPost ? 'Some(' : ')';
  };

  if (x.optional) {
    return getParenthesis(preOrPost);
  }

  return '';
};

function ensureDirectoryExistence(filePath) {
  const dirname = path.dirname(filePath);
  if (fs.existsSync(dirname)) {
    return true;
  }
  ensureDirectoryExistence(dirname);
  fs.mkdirSync(dirname);
}

const saveFile = (filepath, content) => {
  console.log(`Writing ${filepath}`);
  ensureDirectoryExistence(filepath);
  fs.writeFile(filepath, content, err => {
    if (err) {
      return console.log(err);
    }
  });
};

module.exports = {
  camelCaseToSnake,
  snakeCaseToCamel,
  uncapitalizeFirst,
  capitalizeFirst,
  addParenthesis,
  saveFile
};

[johnb8005]

types.js

// list of JVM types that are supported
const jvmTypes = ['Int', 'Long', 'Double', 'LocalDateTime', 'LocalDate', 'Boolean', 'BigDecimal', 'String'];

const jvmToSqlType = i => {
  switch (i) {
    case 'Int':
      return 'INT';
    case 'Long':
      return 'BIGINT';
    case 'Double':
      return 'FLOAT';
    //case 'DateTime': // datetime is not accepted
    case 'LocalDateTime':
      //return 'TIMESTAMP'; //TODO: Fix ambiguity between timestamp & datetime
      return 'DATETIME';
    case 'LocalDate':
      return 'DATE';
    case 'Boolean':
      return 'BIT';
    case 'BigDecimal':
      return 'DECIMAL(12,4)';
    case 'String':
      return 'VARCHAR(512)'; //TODO: Fix hardcoded length
    default:
      console.log(`Couldn't translate "${i}" to SQL type, fallback to \`BIGINT\`.`);
      return 'BIGINT';
  }
};

const sqlToJvmType = i => {
  switch (i.toLowerCase()) {
    case 'int':
      return 'Int';
    case 'bigint':
      return 'Long';
    case 'timestamp':
      return 'LocalDateTime';
    case 'date':
      return 'LocalDate';
    case 'datetime':
      return 'LocalDateTime';
    case 'bit':
      return 'Boolean';
    case 'decimal':
      return 'BigDecimal';
    case 'float':
      return 'Double';
    case 'char':
    case 'text':
    case 'varchar':
      return 'String';
    default:
      throw `Couldn't translate "${i}" to JVM type.`
  }
};

const modelToSqlType = jvmToSqlType;
const modelToJvmType = (x) => x; // Currently equivalent

module.exports = {
  jvmTypes,
  sqlToJvmType,
  jvmToSqlType,
  modelToSqlType,
  modelToJvmType
};

[johnb8005]

types.test.js

const Types = require('./types.js');

test('jvm To Sql Type', () => {
  expect(Types.jvmToSqlType('String')).toBe('VARCHAR(512)')
});

test('Sql to jvm Type', () => {
  expect(Types.sqlToJvmType('VARCHAR')).toBe('String')
});

[johnb8005]

utils.test.js

const Utils = require('./utils.js');

test('camel case to snake case', () => {
  expect(Utils.camelCaseToSnake('tableSource',)).toBe('table_source')
});

test('snake to camel', () => {
  const a = Utils.camelCaseToSnake('motorRPM');
  expect(a).toBe('motor_rpm');

  const b = Utils.camelCaseToSnake('aLongVariable');
  expect(b).toBe('a_long_variable');
});

test('add parenthesis', () => {
  const x = {optional: true};
  const p = Utils.addParenthesis(x, true);
  const q = Utils.addParenthesis(x, false);

  expect(p).toBe('Some(');
  expect(q).toBe(')');
});

// test('random int', () => {
//   const r = Utils.randomInt(1, 10);
//
//   expect(typeof r).toBe('number');
//   expect(r).toBeGreaterThan(0);
//   expect(r).toBeLessThan(11);
// });

[johnb8005]

• [ ] create /lib/sql => PR
• [ ] add to UI (GH Pages)

[johnb8005]

mysqldump -u root -p --no-data dbname > schema.sql

https://stackoverflow.com/questions/6175473/mysql-export-schema-without-data

[f4bD3v]

the library sql-ddl-to-json-schema does fails while parsing foreign keys