使用正则表达式大写SQL关键字

背景

找了很久没找到比较好用的格式化工具库。而 Navicat、DBeaver、IDEA 等工具对 SQL 结构影响较大。所以尝试使用正则表达式替换 SQL 的关键字为大写。

实现

保留字：SELECT keyword FROM v$reserved_words;

const { padEnd } = require('lodash')

// 关键字
const KEYWORD_LIST = [
  ...['CREATE', 'DROP', 'ALTER', 'COMMENT', 'TABLE', 'COLUMN', 'SEQUENCE'],
  ...['VARCHAR2', 'NUMBER', 'DATE', 'NULL', 'SYSDATE', 'DEFAULT'],
  ...['ROWID', 'ROWNUM'],
  ...['WITH'],
  ...['SELECT', 'DISTINCT', 'AS', 'CASE', 'WHEN', 'THEN', 'ELSE', 'END'],
  ...['UPDATE', 'SET'],
  ...['DELETE'],
  ...['INSERT', 'INTO', 'VALUES'],
  ...['FROM', 'JOIN', 'ON'],
  ...['WHERE', 'AND', 'OR', 'NOT', 'IN', 'EXISTS', 'BETWEEN', 'IS'],
  ...['GROUP', 'BY', 'PARTITION', 'OVER'],
  ...['HAVING'],
  ...['ORDER', 'ASC', 'DESC'],
  ...['MERGE', 'USING', 'MATCHED']
]

// 函数
const FUNC_LIST = [
  ...['MAX', 'MIN', 'COUNT', 'AVG'],
  ...['DECODE', 'NVL', 'NVL2', 'REPLACE'],
  ...['TO_DATE', 'TO_CHAR', 'TO_NUMBER'],
  ...['ADD_MONTHS'],
  ...['ROUND', 'RTRIM', 'LTRIM', 'REPLACE'],
  ...['RANK', 'DENSE_RANK', 'ROW_NUMBER']
]

function split(str) {
  const words = []
  let i = 0
  for (let c of str.split('')) {
    const strIng = words[i] !== undefined && words[i].split('').filter(it => it === "'").length % 2 === 1
    const opt = /^(\s|\+|-|\*|\/|\(|\)|>|<|=|,|;)$/i.test(c)
    if (strIng || !opt) {
      words[i] = (words[i] || '') + c
    } else {
      words.push(c)
      i = words.length
    }
  }
  return words
}

function formatWord(word) {
  // 忽略空白字符
  if (/^\s*$/i.test(word)) return word
  for (let keyword of [...KEYWORD_LIST, ...FUNC_LIST]) {
    if (word.toUpperCase() === keyword) return keyword
  }
  return word.toLowerCase()
}

function formatLine(line) {
  // 忽略行注释
  if (/^\s*--/i.test(line)) return line

  // 字段对齐
  if (/^\s*[a-zA-Z0-9_]+\s(NUMBER|VARCHAR2\([0-9]+\)|DATE)/i.test(line)) {
    const [col, ...arr] = line.trim().split(' ')
    line = `  ${padEnd(col, 30)} ${arr.join(' ')}`
  } else if (/^\s*COMMENT ON COLUMN [a-zA-Z0-9_]+(\.[a-zA-Z0-9_]+)? IS/i.test(line)) {
    const words = line.trim().split(' ')
    const [table, column] = words[3].split('.')
    words[3] = `${table}.${padEnd(column, 30)}`
    line = words.join(' ')
  }

  // 转换大小写
  const words = split(line)
  for (let i = 0; i < words.length; i++) {
    words[i] = formatWord(words[i])
  }
  line = words.join('')

  return line
}


function format(sql) {
  sql = sql.split('\n').map(line => formatLine(line)).join('\n')
  return sql
}

module.exports = {
  format
}

const fs = require('fs')
const path = require('path')
const { format} = require('./prettify')

// 格式化
let sql = fs.readFileSync(path.join(__dirname, './source.sql'), 'UTF-8')
sql = format(sql)
fs.writeFileSync(path.join(__dirname, './target.sql'), sql)