前端 vue 项目中使用sql-formatter结合codemirror实现sql编辑器中的SQL代码格式化功能、自动匹配大小写功能、高亮功能
GitHub地址:https://github.com/mijing-web/sql-editor
前几天公司项目有个需求是要前端做个类似HUE的sql编辑器,需要实现sql语句的格式化功能、自动匹配大小写、代码高亮等功能(重任交给了我,但当时HUE我是第一次听说)。现部门是做大数据的,所以各种数据各种sql各种hive,对于我一个纯前端妹子来讲对什么后台sql语句之类的和前端没关系的我都没什么太大的兴趣~~
原本领导说是让我研究HUE源码,提取js,但是发现这个HUE是基于python开发的一款web管理器,python对于我来说…huhahaha…~ - ~…HUE源码拿来看过了,后来我放弃了。
后面网上找资料,认识到sql-formatter这个插件,但是网上找的各种资料几乎都是复制粘贴,并且这个单是实现了sql格式化功能,但我们功能还必须要实现自动匹配大小写和关键字高亮等功能。于是后面各种调研认识到了vue-codemirror插件,可以实现自动匹配大小写和高亮效果。嗯,于是认真研究了一把,把两者结合在一起。我想要的效果实现啦~~
1.安装npm安装sql-formatter、vue-codemirror 插件
npm install --save sql-formatter
npm install --save vue-codemirror
2.使用方式
封装的子组件SqlEditor
<template>
<div>
<textarea
ref="mycode"
class="codesql"
v-model="value"
>
</textarea>
</div>
</template>
<script>
import "codemirror/theme/ambiance.css";
import "codemirror/lib/codemirror.css";
import "codemirror/addon/hint/show-hint.css";
let CodeMirror = require("codemirror/lib/codemirror");
require("codemirror/addon/edit/matchbrackets");
require("codemirror/addon/selection/active-line");
require("codemirror/mode/sql/sql");
require("codemirror/addon/hint/show-hint");
require("codemirror/addon/hint/sql-hint");
export default {
data() {
return {
editor: null
}
},
props: {
value: {
type: String,
default: ''
},
sqlStyle: {
type: String,
default: 'default'
},
readOnly: {
type: [Boolean, String]
}
},
watch: {
newVal (newV, oldV) {
if (this.editor) {
this.$emit('changeTextarea', this.editor.getValue())
}
}
},
computed: {
newVal () {
if (this.editor) {
return this.editor.getValue()
}
}
},
mounted(){
let mime = 'text/x-mariadb'
//let theme = 'ambiance'//设置主题,不设置的会使用默认主题
this.editor = CodeMirror.fromTextArea(this.$refs.mycode, {
value: this.value,
mode: mime,//选择对应代码编辑器的语言,我这边选的是数据库,根据个人情况自行设置即可
indentWithTabs: true,
smartIndent: true,
lineNumbers: true,
matchBrackets: true,
cursorHeight: 1,
lineWrapping: true,
readOnly: this.readOnly,
//theme: theme,
// autofocus: true,
extraKeys: {
'Ctrl': 'autocomplete'},//自定义快捷键
hintOptions: {
//自定义提示选项
// 当匹配只有一项的时候是否自动补全
completeSingle: false,
// tables: {
// users: ['name', 'score', 'birthDate'],
// countries: ['name', 'population', 'size']
// }
}
})
//代码自动提示功能,记住使用cursorActivity事件不要使用change事件,这是一个坑,那样页面直接会卡死
this.editor.on('inputRead', () => {
this.editor.showHint()
})
},
methods: {
setVal () {
if (this.editor) {
if (this.value === '') {
this.editor.setValue('')
} else {
this.editor.setValue(this.value)
}
}
}
}
}
</script>
<style>
.CodeMirror {
color: black;
direction: ltr;
line-height: 22px;
}
// 这句为了解决匹配框显示有问题而加
.CodeMirror-hints{
z-index: 9999 !important;
}
</style>
父组件调用
<template>
<div>
<SqlEditor ref="sqleditor"
:value="basicInfoForm.sqlMain"
@changeTextarea="changeTextarea($event)"
/>
<el-button type="primary" size="small" class="sql-btn" @click="formaterSql (basicInfoForm.sqlMain)">格式化sql</el-button>
</div>
</template>
<script>
import sqlFormatter from 'sql-formatter'
import SqlEditor from '@/components/SqlEditor'
export default {
components: {
SqlEditor
},
data() {
return{
basicInfoForm:{
sqlMain: ''
}
}
},
methods:{
changeTextarea (val){
this.$set(this.basicInfoForm, 'sqlMain', val)
},
formaterSql (val) {
let dom = this.$refs.sqleditor
dom.editor.setValue(sqlFormatter.format(dom.editor.getValue()))
},
},
}
</script>
最后再附上效果图~
支持格式:sql、pl/sql、n1ql、db2、
功能是完成了 ,但是sql-formatter插件格式化出来的效果和我们想要的格式还是有差距,一些复杂的sql校验和HUE并不能完全匹配,甚至后台校验大部分都不能通过。想想也只能改源码了,找到sql-formatter的源码,进行了一些源码修改,最终搞定。
网上看到过很多只实现了格式化功能或者只实现了高亮、大小写匹配功能,没有三者同时实现的,代码和思路不太符合我的需要,并且几乎每个人的版本都是复制粘贴的一模一样的内容,今天我附上自己纯手敲齐全代码,欢迎参考点赞~
GitHub地址:https://github.com/mijing-web/sql-editor
如果你觉得本文对你有帮助,欢迎转载和点赞,转载麻烦请注明出处,谢谢~~~~ ^ _ ^ ~~~
后记:
经一些踩坑网友反馈,现增加两条内容:一是部分反馈的报错问题,二是修改后的源码问题做个总结;
(一)报错问题:
1.vue.esm.js?efeb:628 [Vue warn]: Error in v-on handler: “TypeError: Cannot read properties of undefined (reading ‘format’)”
这个问题我之前使用时没用遇到,所以有人反馈时开始以为大家有没用引入的东西,后来也有技术大佬在评论区给了解决方案:(1)降低版本,降至3.0或者2.0;(2)更改sqlFormatter名字。
我本地从新尝试了下sqlFormatter下载的是最新版本,也出现了和大家相同的报错,我是降低了sqlFormatter的版本,方法(2)更改名字我没实验成功(着实尴尬)。
2.有人反馈说光标一直处于中间位置,这个在我今天实验时也出现了这个问题,但是后来发现是我的项目根样式控制了编辑器的样式,也就是#app。所以出现这个问题还是需要检查大家自己的样式问题。本身它的封装是靠左显示的。
(二)由于源码包很大,原本没有附上,但是有很多小伙伴都私聊了,并且大多都是比较着急使用,有时间个人工作忙或者星期天时间不能及时回复,多少会耽误大家的时间,所以还是贴上修改后的源码。(源码修改的只是部分语法解析,具体情况还是要根据大家项目需求而改。当下的源码是满足了我当时的项目需求的sql格式校验,如果大家有其他的校验方式可以自行找源码修改下~)
已修改后的源码如下:
(function webpackUniversalModuleDefinition(root, factory) {
if(typeof exports === 'object' && typeof module === 'object')
module.exports = factory();
else if(typeof define === 'function' && define.amd)
define([], factory);
else if(typeof exports === 'object')
exports["sqlFormatter"] = factory();
else
root["sqlFormatter"] = factory();
})(this, function() {
return /******/ (function(modules) {
// webpackBootstrap
/******/ // The module cache
/******/ var installedModules = {
};
/******/ // The require function
/******/ function __webpack_require__(moduleId) {
/******/ // Check if module is in cache
/******/ if(installedModules[moduleId])
/******/ return installedModules[moduleId].exports;
/******/ // Create a new module (and put it into the cache)
/******/ var module = installedModules[moduleId] = {
/******/ exports: {
},
/******/ id: moduleId,
/******/ loaded: false
/******/ };
/******/ // Execute the module function
/******/ modules[moduleId].call(module.exports, module, module.exports, __webpack_require__);
/******/ // Flag the module as loaded
/******/ module.loaded = true;
/******/ // Return the exports of the module
/******/ return module.exports;
/******/ }
/******/ // expose the modules object (__webpack_modules__)
/******/ __webpack_require__.m = modules;
/******/ // expose the module cache
/******/ __webpack_require__.c = installedModules;
/******/ // __webpack_public_path__
/******/ __webpack_require__.p = "";
/******/ // Load entry module and return exports
/******/ return __webpack_require__(0);
/******/ })
/************************************************************************/
/******/ ([
/* 0 */
/***/ (function(module, exports, __webpack_require__) {
"use strict";
exports.__esModule = true;
var _Db2Formatter = __webpack_require__(24);
var _Db2Formatter2 = _interopRequireDefault(_Db2Formatter);
var _N1qlFormatter = __webpack_require__(25);
var _N1qlFormatter2 = _interopRequireDefault(_N1qlFormatter);
var _PlSqlFormatter = __webpack_require__(26);
var _PlSqlFormatter2 = _interopRequireDefault(_PlSqlFormatter);
var _StandardSqlFormatter = __webpack_require__(27);
var _StandardSqlFormatter2 = _interopRequireDefault(_StandardSqlFormatter);
function _interopRequireDefault(obj) {
return obj && obj.__esModule ? obj : {
"default": obj }; }
exports["default"] = {
/**
* Format whitespaces in a query to make it easier to read.
*
* @param {String} query
* @param {Object} cfg
* @param {String} cfg.language Query language, default is Standard SQL
* @param {String} cfg.indent Characters used for indentation, default is " " (2 spaces)
* @param {Object} cfg.params Collection of params for placeholder replacement
* @return {String}
*/
format: function format(query, cfg) {
cfg = cfg || {
};
switch (cfg.language) {
case "db2":
return new _Db2Formatter2["default"](cfg).format(query);
case "n1ql":
return new _N1qlFormatter2["default"](cfg).format(query);
case "pl/sql":
return new _PlSqlFormatter2["default"](cfg).format(query);
case "sql":
case undefined:
return new _StandardSqlFormatter2["default"](cfg).format(query);
default:
throw Error("Unsupported SQL dialect: " + cfg.language);
}
}
};
module.exports = exports["default"];
/***/ }),
/* 1 */
/***/ (function(module, exports, __webpack_require__) {
var freeGlobal = __webpack_require__(12);
/** Detect free variable `self`. */
var freeSelf = typeof self == 'object' && self && self.Object === Object && self;
/** Used as a reference to the global object. */
var root = freeGlobal || freeSelf || Function('return this')();
module.exports = root;
/***/ }),
/* 2 */
/***/ (function(module, exports, __webpack_require__) {
var Symbol = __webpack_require__(9),
getRawTag = __webpack_require__(48),
objectToString = __webpack_require__(57);
/** `Object#toString` result references. */
var nullTag = '[object Null]',
undefinedTag = '[object Undefined]';
/** Built-in value references. */
var symToStringTag = Symbol ? Symbol.toStringTag : undefined;
/**
* The base implementation of `getTag` without fallbacks for buggy environments.
*
* @private
* @param {*} value The value to query.
* @returns {string} Returns the `toStringTag`.
*/
function baseGetTag(value) {
if (value == null) {
return value === undefined ? undefinedTag : nullTag;
}
return (symToStringTag && symToStringTag in Object(value))
? getRawTag(value)
: objectToString(value);
}
module.exports = baseGetTag;
/***/ }),
/* 3 */
/***/ (function(module, exports, __webpack_require__) {
var baseIsNative = __webpack_require__(39),
getValue = __webpack_require__(50);
/**
* Gets the native function at `key` of `object`.
*
* @private
* @param {Object} object The object to query.
* @param {string} key The key of the method to get.
* @returns {*} Returns the function if it's native, else `undefined`.
*/
function getNative(object, key) {
var value = getValue(object, key);
return baseIsNative(value) ? value : undefined;
}
module.exports = getNative;
/***/ }),
/* 4 */
/***/ (function(module, exports, __webpack_require__) {
"use strict";
exports.__esModule = true;
var _trimEnd = __webpack_require__(74);
var _trimEnd2 = _interopRequireDefault(_trimEnd);
var _tokenTypes = __webpack_require__(8);
var _tokenTypes2 = _interopRequireDefault(_tokenTypes);
var _Indentation = __webpack_require__(21);
var _Indentation2 = _interopRequireDefault(_Indentation);
var _InlineBlock = __webpack_require__(22);
var _InlineBlock2 = _interopRequireDefault(_InlineBlock);
var _Params = __webpack_require__(23);
var _Params2 = _interopRequireDefault(_Params);
function _interopRequireDefault(obj) {
return obj && obj.__esModule ? obj : {
"default": obj }; }
function _classCallCheck(instance, Constructor) {
if (!(instance instanceof Constructor)) {
throw new TypeError("Cannot call a class as a function"); } }
var Formatter = function () {
/**
* @param {Object} cfg
* @param {Object} cfg.indent
* @param {Object} cfg.params
* @param {Tokenizer} tokenizer
*/
function Formatter(cfg, tokenizer) {
_classCallCheck(this, Formatter);
this.cfg = cfg || {
};
this.indentation = new _Indentation2["default"](this.cfg.indent);
this.inlineBlock = new _InlineBlock2["default"]();
this.params = new _Params2["default"](this.cfg.params);
this.tokenizer = tokenizer;
this.previousReservedWord = {
};
this.tokens = [];
this.index = 0;
}
/**
* Formats whitespaces in a SQL string to make it easier to read.
*
* @param {String} query The SQL query string
* @return {String} formatted query
*/
Formatter.prototype.format = function format(query) {
this.tokens = this.tokenizer.tokenize(query);
var formattedQuery = this.getFormattedQueryFromTokens();
return formattedQuery.trim();
};
Formatter.prototype.getFormattedQueryFromTokens = function getFormattedQueryFromTokens() {
var _this = this;
var formattedQuery = "";
this.tokens.forEach(function (token, index) {
_this.index = index;
if (token.type === _tokenTypes2["default"].WHITESPACE) {
// ignore (we do our own whitespace formatting)
} else if (token.type === _tokenTypes2["default"].LINE_COMMENT) {
formattedQuery = _this.formatLineComment(token, formattedQuery);
} else if (token.type === _tokenTypes2["default"].BLOCK_COMMENT) {
formattedQuery = _this.formatBlockComment(token, formattedQuery);
} else if (token.type === _tokenTypes2["default"].RESERVED_TOPLEVEL) {
formattedQuery = _this.formatToplevelReservedWord(token, formattedQuery);
_this.previousReservedWord = token;
} else if (token.type === _tokenTypes2["default"].RESERVED_NEWLINE) {
formattedQuery = _this.formatNewlineReservedWord(token, formattedQuery);
_this.previousReservedWord = token;
}else if (token.type === _tokenTypes2["default"].RESERVED) {
formattedQuery = _this.formatWithSpaces(token, formattedQuery);
_this.previousReservedWord = token;
} else if (token.type === _tokenTypes2["default"].OPEN_PAREN) {
formattedQuery = _this.formatOpeningParentheses(token, formattedQuery);
} else if (token.type === _tokenTypes2["default"].CLOSE_PAREN) {
formattedQuery = _this.formatClosingParentheses(token, formattedQuery);
} else if (token.type === _tokenTypes2["default"].PLACEHOLDER) {
formattedQuery = _this.formatPlaceholder(token, formattedQuery);
} else if (token.value === '$') {
formattedQuery = _this.formatNewWithSpaces(token, formattedQuery);
} else if (token.value === ",") {
formattedQuery = _this.formatComma(token, formattedQuery);
} else if (token.value === ":") {
formattedQuery = _this.formatWithSpaceAfter(token, formattedQuery);
} else if (token.value === ".") {
formattedQuery = _this.formatWithoutSpaces(token, formattedQuery);
} else if (token.value === ";") {
formattedQuery = _this.formatQuerySeparator(token, formattedQuery);
} else {
formattedQuery = _this.formatWithSpaces(token, formattedQuery);
}
});
return formattedQuery;
};
Formatter.prototype.formatLineComment = function formatLineComment(token, query) {
return this.addNewline(query + token.value);
};
Formatter.prototype.formatBlockComment = function formatBlockComment(token, query) {
return this.addNewline(this.addNewline(query) + this.indentComment(token.value));