一个基于html和js的sql编辑器

### 前言
上一个编辑器因为光标定位等问题，开发失败。于是转变了思路。将展示和输入两个部分分开了。
改造为了上面盖有一层透明的文本框，下面则是一层用于显示的div元素。

### 更新
##### 2024-04-17
1. 修改了编辑器输入框的逻辑。
2. 追加数据表格。

### 源代码
```html
<!DOCTYPE html>
<html>
<head>
    <title>sql运行器</title>
    <link rel="Shortcut Icon" href="{{ url_for('static', filename='favicon.ico') }}" type="image/x-icon" />
    <link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='style/style.css') }}">
</head>
<body>
    <div id = "app" class="app">
        <div class = "top_box"> 
            <div class="left_box">
                <div id = "sql_list" class="sql_list"> </div>
            </div>
            <div class="center_box">
                <div class="menu_box">
                    <div></div>
                    <div><button id="execute_sql">△执行sql</button></div>
                    <div><button id="save_sql">○缓存sql</button></div>
                    <div id="edit_msg"></div>
                </div>
                <div class="edit_box">
                    <div class="sql_input_edit_box"><text-area id="sql-input-edit" class="sql_input_edit" Spellcheck="false"></text-area></div>
                    <pre id="sql-input" class="sql_editor"></pre>
                </div>
            </div>
            <div class="right_box"></div>
        </div>
        <div class = "below_box">
            <div class="msg_box">
                <div class="msg_box_title">消息框<span>状态：<span id="zt"></span></span><span> </span><span>状态码：<span id="ztm"></span></span></div>
                <div id="mes" class="mes"></div>
            </div>
        </div>
       
    </div>
    
    <script>
        const TokenTypes = {
            SELECT: "SELECT", 
            FROM: "FROM", 
            WHERE: "WHERE", 
            INNER: "INNER",
            JOIN: "JOIN", 
            LEFT: "LEFT", 
            RIGHT: "RIGHT",
            ON: "ON", 
            GROUP:"GROUP",
            ORDER:"ORDER",
            BY: "BY", 
            AS:"AS" ,
            COMMENT:"COMMENT",
            HAVING:"HAVING",
            STRING:"STRING",
            NUMBER:"NUMBER",
            LEFT_PAREN:"LEFT_PAREN", //( 
            RIGHT_PAREN:"RIGHT_PAREN", //)
            COMMA:"COMMA", // ,
            DOT:"DOT", // .
            SEMICOLON:"SEMICOLON", //;
            SPACE:"SPACE", // 空格
            BACKQUOTE:"BACKQUOTE", // `反引号
            IDENTIFIER:"IDENTIFIER",
            ENTER:"ENTER",
            CREATE:"CREATE",
            TABLE:"TABLE",
            XOA:"XOA",
            EOF:"EOF",
            DROP:"DROP",
            WITH:"WITH",
            FUN:"FUN"
        };
        const keywords = {
            'SELECT':TokenTypes.SELECT,
            'FROM':TokenTypes.FROM,
            'WHERE':TokenTypes.WHERE,
            'INNER':TokenTypes.INNER,
            'JOIN':TokenTypes.JOIN,
            'LEFT':TokenTypes.LEFT,
            'RIGHT':TokenTypes.RIGHT,
            'ON':TokenTypes.ON,
            'GROUP':TokenTypes.GROUP,
            'ORDER':TokenTypes.ORDER,
            'BY':TokenTypes.BY,
            'AS':TokenTypes.AS,
            'COMMENT':TokenTypes.COMMENT,
            'HAVING':TokenTypes.HAVING,
            'LEFT_PAREN':TokenTypes.LEFT_PAREN,
            'RIGHT_PAREN':TokenTypes.RIGHT_PAREN,
            'COMMA':TokenTypes.COMMA,
            'DOT':TokenTypes.DOT,
            'SEMICOLON':TokenTypes.SEMICOLON,
            'EOF':TokenTypes.EOF,
            "CREATE":TokenTypes.CREATE,
            "TABLE":TokenTypes.TABLE,
            'BACKQUOTE':TokenTypes.BACKQUOTE,
            "DROP":TokenTypes.DROP,
            "WITH":TokenTypes.WITH
        };
        class Token {
            /** @type {TokenTypes} */
            type;
            /** @type {string} */
            lexeme;
            /** @type {Object} */
            literal;
            /** @type {int} */
            line;
            constructor(type, lexeme, literal, line) {
                this.type = type;
                this.lexeme = lexeme;
                this.literal = literal;
                this.line = line;
            }
            toString() {
                return this.type + " " + this.lexeme + " " + line;
            }
        }
        /**
         * Scanner
         * 词法扫描器
        */
        class Scanner {
            start = 0;
            current = 0;
            line = 0;
            tokens = [];
            /** @type {String} */
            source = null;
            constructor(source) {
                this.source = source;
            }
            scanTokens(){
                while(!this.isAtEnd() ){
                    this.start = this.current;
                    this.scanToken();
                }
                this.tokens.push(new Token(TokenTypes.EOF,"",null,this.line))
                return this.tokens
            }
            scanToken() {
                let c;
                c = this.advance();
                if (c == "("){
                    this.addToken(TokenTypes.LEFT_PAREN,c);
                }
                else if (c == ")"){
                    this.addToken(TokenTypes.RIGHT_PAREN,c);
                }
                else if (c == "."){
                    this.addToken(TokenTypes.DOT,c);
                }else if (c == ","){
                    this.addToken(TokenTypes.COMMA,c);
                }
                else if (c == ";"){
                    this.addToken(TokenTypes.SEMICOLON,c);
                }
                else if (["\"","'"].includes(c)){
                    this.string(c);
                }
                else if (c == " "){
                    this.addToken(TokenTypes.SPACE,"<div class='space'></div>");// 
                }else if (c == "`"){
                    this.addToken(TokenTypes.BACKQUOTE,c);
                }
                else if (c == '\xA0'){
                    this.addToken(TokenTypes.SPACE,'\xA0');
                }
                else if(c == "\n"){
                    // this.match("\n")
                    this.addToken(TokenTypes.ENTER,'<pre>\n</pre>');
                }
                else{
                    if(this.isDigit(c)){
                        this.number();
                    }else if(this.isAlphaNumeric(c)){
                        this.identifier();
                    }else if(c == ""){
                        
                    }
                    else{
                        // throw ""+this.line+" " + c +" 非法字符！"
                    }
                }
            }
            
            // 移动指针
            advance(){
                this.current+=1;
                return this.source.charAt(this.current-1);
            }
            
            // 结束
            isAtEnd(){
                return this.current > this.source.length;
            }
            // 添加token
            addToken(type, literal = null) {
                let text = this.source.slice(this.start,this.current);
                this.tokens.push(new Token(type,text,literal,this.line))
            }
            // 取指针当前指向字符
            peek(){
                if(!this.isAtEnd()){
                    return this.source.charAt(this.current);
                }
                return "\0";
            }
            // 取指针下一位字符
            nextPeek(){
                if(!this.isAtEnd()){
                    return this.source.charAt(this.current+1);
                }
                return "\0";
            }
            //  判断连续字符
            match(c){
                if(!this.isAtEnd() && this.peek()==c){
                    this.advance()
                    return true
                }
                return false
            }
            // 判断是否为数值
            isDigit(c){
                return c >= "0" && c <= "9";
            }
            //判断是否为字符串(包含汉字)
            isAlpha(c){
                // return ("a" <= c && c <= "z") || ("A" <= c && c <= "Z") || (12288 <= c.charCodeAt() && c.charCodeAt() <= 12351)|| c == "+" || c == "-" || c == "=" || c == "*"  || c == "_" || ("一"<=c &&  c <= "龥")
                return c != " " && c != "\n" && c != "(" && c != ")"
            }
            // 判断是否为字符串或数值
            isAlphaNumeric(c){
                return this.isAlpha(c) || this.isDigit(c);
            }
            // 处理字符串
            string(c){
                while(this.peek() != c && !this.isAtEnd()){
                    if(this.peek() == "\n"){
                        this.line += 1;
                    }
                    this.advance();
                }
                if(this.isAtEnd()){
                    // throw "Unterminated string.";
                }
                this.advance();
                this.addToken(TokenTypes.STRING, this.source.slice(this.start+1,this.current-1));
            }
            // 处理数值
            number(){
                while (this.isDigit(this.peek())){
                    this.advance();
                }
                if (this.peek() == "." && this.isDigit(this.peekNext())){
                    this.current+=1
                    while(this.isDigit(this.peek())){
                        this.advance()
                    }
                }                    
                this.addToken(TokenTypes.NUMBER,this.source.slice(this.start,this.current))
            }
            identifier(){
                while(this.isAlphaNumeric(this.peek()) && !this.isAtEnd()){
                    this.advance();
                }
                let value = this.source.slice(this.start,this.current)
                if(keywords[value.toUpperCase()] != undefined){
                    this.addToken(keywords[keywords[value.toUpperCase()]],keywords[keywords[value.toUpperCase()]]);
                }
                else if(this.peek()=="("){
                    this.addToken(TokenTypes.FUN,keywords[keywords[value]]);
                }else{
                    this.addToken(TokenTypes.IDENTIFIER,keywords[keywords[value]]);
                }
                
            }
        }
        /**
         * Parser
         * 句法分析器 暂时先不用
        */
        class Parser {
                tokens;
                current;
                constructor(tokens) {
                    this.tokens = tokens;
                    this.current = 0;
                }
                parser(){
                    while(!this.isAtEnd()){
                        this.expression();
                    }
                }
                isAtEnd(){
                    return this.current > this.tokens.length
                }
        }
        /**
         * Redrew
         * 语句重绘
        */
        class Redraw {
            tokens;
            current;
            doms="";
            line="";
            lines=[];
            color_key = {
                "SELECT":"rgb(190, 46, 221)","FROM":"rgb(190, 46, 221)",
                "WHERE":"rgb(190, 46, 221)","INNER":"rgb(190, 46, 221)",
                "JOIN":"rgb(190, 46, 221)","LEFT":"rgb(190, 46, 221)",
                "RIGHT":"rgb(190, 46, 221)","ON":"rgb(190, 46, 221)",
                "GROUP":"rgb(190, 46, 221)","ORDER":"rgb(190, 46, 221)",
                "BY":"rgb(190, 46, 221)","AS":"rgb(190, 46, 221)",
                "COMMENT":"rgb(190, 46, 221)","HAVING":"rgb(190, 46, 221)",
                "LEFT_PAREN":"rgb(192, 57, 43)","RIGHT_PAREN":"rgb(192, 57, 43)",
                "COMMA":"rgb(241, 196, 15)","DOT":"rgb(236, 240, 241)",
                "SEMICOLON":"rgb(190, 46, 221)","EOF":"rgb(190, 46, 221)",
                "STRING":"rgb(39, 174, 96)","NUMBER":"rgb(41, 128, 185)",
                "CREATE":"rgb(190, 46, 221)","TABLE":"rgb(190, 46, 221)",
                "BACKQUOTE":"rgb(190, 46, 221)","DROP":"rgb(190, 46, 221)",
                "WITH":"rgb(190, 46, 221)","FUN":"rgb(9, 132, 227)"
            }
            constructor(tokens){
                this.tokens = tokens;
                this.current = 0;
                this.init()
            }
            init(){
                this.current = 0;
                this.doms="";
                this.line = "";
                this.lines = [];
            }
            redraw(){
                    this.init()
                    while(!this.isAtEnd()){
                        this.draw();
                    }
                    this.lines.push(this.line)
                    for(let i in this.lines){
                        let dom = this.lines[i];
                        if(dom == "<span></span>") dom = "<span></span><br>"
                        this.doms+=("<div>"+dom+"</div>")
                    }
                    return this.doms;
            }
            draw(){
                let token = this.advance()
                if(token.type == TokenTypes.EOF){
                    return;
                }
                if(token.type == TokenTypes.IDENTIFIER){
                    this.line+= this.createSpan(token.lexeme,this.color_key[token.type]);
                }else if(token.type == TokenTypes.NUMBER){
                    this.line+= this.createSpan(token.lexeme,this.color_key[token.type]);
                }else if(token.type == TokenTypes.STRING){
                    this.line+= this.createSpan(token.lexeme,this.color_key[token.type]);
                }else if(token.type == TokenTypes.IDENTIFIER){
                    this.line+= this.createSpan(token.lexeme,this.color_key[token.type]);
                }else if(token.type == TokenTypes.ENTER){
                    this.lines.push(this.line)
                    this.line = "<span></span>"
                }else if(token.type == TokenTypes.SPACE){
                    this.line+= this.createSpan(token.literal,this.color_key[token.type]);
                }
                else{
                    this.line+= this.createSpan(token.lexeme,this.color_key[token.type]);
                }   
            }
            createSpan(value,color){
                return `<span contenteditable="true" style = "color:${color}">${value}</span>`
            }
            createDiv(value,color){
                return `<div><span contenteditable="true" style = "color:${color}">${value}</span></div>`
            }
            advance(){
                this.current+=1;
                return this.tokens[this.current-1];
            }
            isAtEnd(){
                return this.current >= this.tokens.length
            }
        }
        function stringToAsc(ss){
            let s = ""
            for(let i=0;i<ss.length;i++){
                s += " - "+ss.charCodeAt(i);
            }
            return s
        }
        
        let sqlInput = document.getElementById('sql-input');
        let sqlInputEdit = document.getElementById('sql-input-edit');
        function inputSetPlay(){
            let data  = sqlInputEdit.value;
            let scanner = new Scanner(data);
            let tokens = scanner.scanTokens()
            console.log(tokens);
            let redraw = new Redraw(tokens)
            let selection = window.getSelection();
            let range = document.createRange();
            sqlInput.innerHTML = redraw.redraw()
            sqlInputEdit.style.height = `${sqlInput.scrollHeight}px`;
        }
        sqlInputEdit.addEventListener('input', (e) => {
            inputSetPlay()
        }
        );
        Object.defineProperty(sqlInputEdit, '_value', {
            configurable: true,
            set: function(value) {
                this.value = value;
                inputSetPlay();
            },
            get: function() {
                return this.value;
            }
        });
    </script>
    
    <script>
        /**
         * Loading
         * 用于创建一个加载框
         */
        class Loading{
            title = "";
            dom = "";
            body = document.body;
            constructor(title="加载中..."){
                this.title = title;
                this.dom = this.createLoadDom()
            }
            createLoadDom(){
                // 加载窗口容器
                let box = document.createElement("div")
                box.style.position = "fixed"
                box.style.display = "flex"
                box.style.flexDirection = "column"
                box.style.justifyContent = "center"
                box.style.alignItems = "center"
                box.style.background = "rgba(0,0,0,1)"
                box.style.width = "100vw"
                box.style.height = "100vh"
                box.style.top = "0px"
                box.style.zIndex = "2000"
                box.style.display = "None"
                // 加载窗口提示字符
                let text = document.createElement("div")
                text.innerText = this.title
                text.style.fontSize = "1.4em"
                text.style.color = "#fff"
                text.style.marginBottom = "0.4em"
                // 加载动画
                document.styleSheets[0].insertRule(
                    `@keyframes n_line_gd {
                        0% {left: -20%;}
                        20% {left: 0%;} 
                        40% {left: 50%;} 
                        50% {left: 100%;}
                        60% {left: 50%;} 
                        80% {left: 0%;} 
                        100% {left: -20%;} 
                    }
                    `,
                    0
                );
                let l_line = document.createElement("div")
                l_line.style.width = "20%"
                l_line.style.height = "22px"
                l_line.style.border = "1px solid #fff"
                l_line.style.padding = "1px 1px 1px 1px"
                l_line.style.overflow = "hidden"
                let n_line = document.createElement("div")
                n_line.style.width = "20%"
                n_line.style.height = "22px"
                n_line.style.backgroundColor = "#fff"
                n_line.style.position = "relative"
                n_line.style.left = "-20px"
                n_line.style.animation = "n_line_gd 2s linear infinite"
                l_line.appendChild(n_line)
                box.appendChild(text)
                box.appendChild(l_line)
                this.body.appendChild(box)
                return box
            }
            play(callback=()=>{},...args){
                this.dom.style.display = "flex"
                setTimeout(()=>{
                    callback(...args)
                },2)
            }
            end(){
                this.dom.style.display = "None"
            }
        }
        let ld = new Loading()
        let edit_msg = document.querySelector("#edit_msg");
        document.querySelector("#sql-input-edit").addEventListener('keydown',function(event){
            if (event.keyCode === 13 && event.ctrlKey == "1" ) {
                send(showData)
            }
        }
        )
        document.querySelector("#execute_sql").addEventListener('click',function(event){
            send(showData)
        });
        function send(callback=()=>{}){
            let value = document.querySelector("#sql-input-edit").value;
            ld.play(sendSql(value,callback)) // 启动加载效果
        }
        class Table{
            constructor(data){
                this.meta = data.meta
                this.value = data.data
                this.rows = data.data.length
                this.cols = data.meta.length
            }
            // 构建html
            buildHtml(){
                let row_cur = 0;
                let col_cur = 0;
                let body_html = ""
                let self = this
                function isAtRowEnd(){
                    return row_cur >= self.rows;
                }
                function isAtColEnd(){
                    return col_cur >= self.cols;
                }
                // 构建表格表头
                for(let num = 0;num<this.cols;num++){
                    body_html+=`<div class="table_head cell" id="table_head_${num}">${this.meta[num]}</div>`
                }
                body_html=`<div class="row"><div class="row_ruler" id = "row_ruler_null"></div>${body_html}</div>`
                // 构建表格主体以及行号
                while(!isAtRowEnd()){
                    col_cur = 0;
                    let row_html = ""
                    while(!isAtColEnd()){
                        row_html+=`<div onfocus="cellFocus('cell_${row_cur}_${col_cur}')" onblur="cellBlur('cell_${row_cur}_${col_cur}')" class="cell cell_row_${row_cur} cell_col_${col_cur}" id="cell_${row_cur}_${col_cur}" tabindex="0" >${this.value[row_cur][col_cur]}</div>`
                        col_cur++;
                    }
                    body_html+=`<div class="row" id="row_${row_cur}"><div class="row_ruler" id = "row_ruler_${row_cur}">${row_cur}</div>${row_html}</div>`
                    row_cur++;
                }
                return body_html
            }
        }
        function cellFocus(self){
            self = document.querySelector("#"+self);
            let self_evenlistener= self.addEventListener('keydown',function(e){
                if(e.ctrlKey == true && e.key == "c"){
                    let content = self.innerText;
                    navigator.clipboard.writeText(content)
                }
            })
        }
        function cellBlur(self){}
        function sendSql(sql,callback=()=>{}){
            const options = {
                method: 'POST',
                headers: {'content-type': 'application/json'},
                body: JSON.stringify({"sql":sql})
            };
            fetch('http://127.0.0.1:9001/sqlExcute', options)
            .then(response => response.json())
            .then(response => {
                callback(response)
                ld.end() // 关闭加载效果
            })
            .catch(err => {console.error(err)
                ld.end() // 关闭加载效果
            });
        }
        function showData(response){
            let data_box = document.getElementById("mes")
                let zt = document.getElementById("zt")
                let ztm = document.getElementById("ztm")
                zt.innerText = response.msg
                ztm.innerText = response.code
                if(response.code == "200"){ 
                    data = response.data
                    let table = new Table(data);
                    data_box.innerHTML = table.buildHtml();
                }
                else if (response.code == "400"){
                    data_box.innerHTML = response.data
                }
        }
        send(()=>{
            alert("启动成功")
        })
    </script>
    
    <script>
        class SaveSql{
            constructor(){
                this.sql_arr = new Array()
                this.loadSql()
            }
            setSaveSql(sql){
                this.sql_arr.push(sql)
                this.saveSql()
            }
            getSaveSql(index){
                return this.sql_arr[index]
            }
            removeSaveSql(index){
                this.sql_arr.splice(index,1)
                this.saveSql()
            }
            saveSql() {  
                // 将当前对象状态转换为 JSON 字符串  
                let data = JSON.stringify(this);  
                // 将字符串保存到 localStorage  
                localStorage.setItem("save_sql", data);  
            }  
            
            loadSql() {  
                // 尝试从 localStorage 获取已保存的字符串  
                let savedData = localStorage.getItem("save_sql");  
                if (savedData) {  
                    // 将 JSON 字符串解析回对象状态  
                    let savedObj = JSON.parse(savedData);  
                    console.log(savedObj);
                    // 更新当前对象
                    Object.assign(this, savedObj)
                }  
            }  
        }
        let saveSql = new SaveSql()
        function removeSaveSqlDom(index){
            let dom = document.createElement("div")
            dom.innerText="删除"
            dom.style.width = "50px"
            dom.style.height = "100%"
            dom.style.display = "flex"
            dom.style.justifyContent = "center";
            dom.style.alignItems = "center";
            dom.addEventListener("click",()=>{
                saveSql.removeSaveSql(index)
                setSqlList()
            })
            return dom
        }
        function setSqlList(){
            document.querySelector("#sql_list").innerHTML = ""
            saveSql.sql_arr.forEach((value,key) => {
                        let dom = document.createElement("div")
                        dom.setAttribute("id",key)
                        dom.setAttribute("class","sql_save_item")
                        dom.style.width = "calc(100% - 50px)"
                        dom.style.height = "100%"
                        dom.style.height = "100%"
                        dom.innerText = value
                        let box_dom = document.createElement("div")
                        box_dom.setAttribute("id",key)
                        box_dom.setAttribute("class","sql_save_item_box")
                        box_dom.appendChild(dom)
                        box_dom.appendChild(removeSaveSqlDom(key))
                        dom.addEventListener("click",()=>{
                            document.querySelector("#sql-input-edit")._value = saveSql.getSaveSql(key)
                        })
                        document.querySelector("#sql_list").appendChild(box_dom)
                    })
        }
        setSqlList()
        document.querySelector("#save_sql").addEventListener('click',function(event){
            saveSql.setSaveSql(document.querySelector("#sql-input-edit").value);
            setSqlList()
        });
    </script>
</body>
</html>
```