sqlquery.html

{% extends "base.html" %}

{% block content %}
    <div id="div_id" class="container-fluid">
        <div class="row clearfix">
            <form id="form-sqlquery" action="/sqlquery/" method="post" class="form-horizontal" role="form">
                {% csrf_token %}
                <div class="col-md-9 column">
                    <pre id="sql_content_editor" class="ace_editor " style="min-height:350px"></pre>
                </div>
                <div class="col-md-3 column">
                    <div class="form-group">
                        <select id="cluster_name" name="cluster_name"
                                class="selectpicker show-tick form-control bs-select-hidden" data-live-search="true"
                                data-placeholder="请选择集群:" required>
                            <option value="is-empty" disabled="" selected="selected">请选择集群:</option>
                            {% for cluster_name in listAllClusterName %}
                                {% if cluster_name != 'mycat' %}
                                    <option value="{{ cluster_name }}">{{ cluster_name }}</option>
                                {% endif %}
                            {% endfor %}
                        </select>
                    </div>
                    <div class="form-group">
                        <select id="db_name" name="db_name" class="form-control selectpicker show-tick bs-select-hidden"
                                data-live-search="true" data-placeholder="请选择数据库:" required>
                            <option value="is-empty" disabled="" selected="selected">请选择数据库:</option>
                        </select>
                    </div>
                    <div class="form-group">
                        <select id="table_name" name="table_name"
                                class="form-control selectpicker show-tick bs-select-hidden" data-live-search="true"
                                data-name="查看表结构" data-placeholder="查看表结构:" required>
                            <option value="is-empty" disabled="" selected="selected">查看表结构:</option>
                        </select>
                    </div>
                    <div class="form-group">
                        <select id="limit_num" name="limit_num"
                                class="form-control selectpicker show-tick bs-select-hidden"
                                data-placeholder="返回行数:" required>
                            <option value="is-empty" disabled="">返回行数:</option>
                            <option value=100 selected="selected">100</option>
                            <option value=500>500</option>
                            <option value=1000>1000</option>
                            <option value=0>max(最大限制行数)</option>
                        </select>
                    </div>
                    <div class="form-group">
                        <input id="btn-format" type="button" class="btn btn-info" value="美化"/>
                        <input id="btn-explain" type="button" class="btn btn-warning" value="执行计划"/>
                        <input id="btn-sqlquery" type="button" class="btn btn-success" value="SQL查询"/>
                    </div>
                </div>
                <div class="text-info">
                    <li>支持注释行，可选择指定语句执行，默认执行第一条;</li>
                    <li>查询结果行数限制见权限管理，会选择查询涉及表的最小limit值</li>
                </div>
            </form>
        </div>
    </div>
    <br>
    <!-- Nav tabs -->
    <ul id="nav-tabs" class="nav nav-tabs" role="tablist">
        <li class="active" id="sqllog_tab">
            <a href="#sql_log_result" role="tab" data-toggle="tab">查询历史</a>
        </li>
        <button class="btn btn-default btn-sm pull-right" onclick="tab_remove()">
            <span class="glyphicon glyphicon-minus"></span>
        </button>
        <button class="btn btn-default btn-sm pull-right" onclick="tab_add()">
            <span class="glyphicon glyphicon-plus"></span>
        </button>
    </ul>
    <!-- Tab panes -->
    <div id="tab-content" class="tab-content">
        <div id="sql_log_result" role="tabpanel" class="tab-pane fade in active table-responsive">
            <table id="sql-log" data-toggle="table" class="table table-condensed"></table>
        </div>
    </div>
{% endblock content %}

{% block js %}
    {% load staticfiles %}
    <script src="{% static 'ace/ace.js' %}"></script>
    <script src="{% static 'ace/ext-language_tools.js' %}"></script>
    <script src="{% static 'ace/mode-sql.js' %}"></script>
    <script src="{% static 'ace/theme-github.js' %}"></script>
    <script src="{% static 'ace/snippets/sql.js' %}"></script>
    <script src="{% static 'ace/ace_init.js' %}"></script>
    <script src="{% static 'bootstrap-table/js/bootstrap-table-export.min.js' %}"></script>
    <script src="{% static 'bootstrap-table/js/tableExport.min.js' %}"></script>
    <script src="{% static 'dist/js/sql-formatter.min.js' %}"></script>
    <!-- 查询历史  -->
    <script>
        //获取查询列表
        function get_querylog() {
            //采取异步请求
            //初始化table
            $('#sql-log').bootstrapTable('destroy').bootstrapTable({
                method: 'post',
                contentType: "application/x-www-form-urlencoded",
                url: "/querylog/",
                striped: true,                      //是否显示行间隔色
                cache: false,                       //是否使用缓存，默认为true，所以一般情况下需要设置一下这个属性（*）
                pagination: true,                   //是否显示分页（*）
                sortable: true,                     //是否启用排序
                sortOrder: "asc",                   //排序方式
                sidePagination: "server",           //分页方式：client客户端分页，server服务端分页（*）
                pageNumber: 1,                      //初始化加载第一页，默认第一页,并记录
                pageSize: 14,                       //每页的记录行数（*）
                pageList: [10, 30, 50, 100],        //可供选择的每页的行数（*）
                showExport: true,                   //是否显示导出按钮
                exportOptions: {
                    ignoreColumn: [0],  //忽略某些列的索引数组
                    fileName: 'sqllog'  //文件名称设置
                },
                search: true,                       //是否显示表格搜索
                strictSearch: false,                //是否全匹配搜索
                showColumns: true,                  //是否显示所有的列（选择显示的列）
                showRefresh: true,                  //是否显示刷新按钮
                minimumCountColumns: 2,             //最少允许的列数
                clickToSelect: true,                //是否启用点击选中行
                uniqueId: "id",                     //每一行的唯一标识，一般为主键列
                showToggle: true,                   //是否显示详细视图和列表视图的切换按钮
                cardView: false,                    //是否显示详细视图
                detailView: true,                   //是否显示父子表
                locale: 'zh-CN',                    //本地化
                toolbar: "#toolbar",                //指明自定义的toolbar
                queryParamsType: 'limit',
                //获取查询列表请求服务数据时所传参数
                queryParams:
                    function (params) {
                        return {
                            limit: params.limit,
                            offset: params.offset,
                            search: params.search
                        }
                    },
                //格式化详情
                detailFormatter: function (index, row) {
                    var html = [];
                    $.each(row, function (key, value) {
                        if (key === 'sqllog') {
                            var sql = window.sqlFormatter.format(value);
                            //替换所有的换行符
                            sql = sql.replace(/\r\n/g, "<br>");
                            sql = sql.replace(/\n/g, "<br>");
                            //替换所有的空格
                            sql = sql.replace(/\s/g, "&nbsp;");
                            html.push('<span>' + sql + '</span>');
                        }
                    });
                    return html.join('');
                },
                columns: [{
                    title: '用户名',
                    field: 'username'
                }, {
                    title: '集群',
                    field: 'cluster_name'
                }, {
                    title: '数据库',
                    field: 'db_name'
                }, {
                    title: '查询时间',
                    field: 'create_time'
                }, {
                    title: 'sql语句',
                    field: 'sqllog',
                    formatter: function (value, row, index) {
                        if (value.length > 50) {
                            var sql = value.substr(0, 50) + '...';
                            return sql;
                        }
                        else {
                            return value
                        }
                    }
                }, {
                    title: '返回行数',
                    field: 'effect_row'
                }, {
                    title: '耗时(秒)',
                    field: 'cost_time'
                }],
                onLoadSuccess: function () {
                },
                onLoadError: function () {
                    alert("数据加载失败！");
                },
                onSearch: function (e) {
                    //传搜索参数给服务器
                    queryParams(e)
                },
                responseHandler: function (res) {
                    //在ajax获取到数据，渲染表格之前，修改数据源
                    return res;
                }
            });
        }
    </script>
    <!-- 执行结果  -->
    <script>
        //添加执行结果页面
        function tab_add() {
            var tab_number = sessionStorage.getItem('tab_num');

            //增加执行结果tab页
            var li = document.createElement("li"); //创建li
            li.setAttribute("id", "execute_result_tab" + (Number(tab_number) + 1));
            li.setAttribute("role", "presentation");

            var href_a = document.createElement("a"); //创建li中的链接a
            href_a.setAttribute("href", "#sqlquery_result" + (Number(tab_number) + 1));
            href_a.setAttribute("role", "tab");
            href_a.setAttribute("data-toggle", "tab");
            href_a.innerHTML = "执行结果" + (Number(tab_number) + 1); //链接显示文本（相当于标签标题）
            li.appendChild(href_a);//将a添加到li
            $("#nav-tabs").append(li);//li添加到ul

            //执行结果tab数量加1
            sessionStorage.setItem('tab_num', Number(tab_number) + 1);
            //重新获取tab数
            tab_number = sessionStorage.getItem('tab_num');

            //增加查询结果显示div
            var div =
                "<div id=\"sqlquery_result" + tab_number + "\" role=\"tabpanel\" class=\"tab-pane fade table-responsive\">\n" +
                "    <div id=\"cost_time" + tab_number + "\" class=\"navbar-text\" >\n" +
                "        <small>查询时间 : <strong id=\"time" + tab_number + "\"> sec </strong></small>\n" +
                "    </div>\n" +
                "    <div id=\"masking_cost_time" + tab_number + "\" class=\"navbar-text\" >\n" +
                "        <small>脱敏时间 : <strong id=\"masking_time" + tab_number + "\"> sec </strong></small>\n" +
                "    </div>\n" +
                "    <table id=\"query_result" + tab_number + "\" data-toggle=\"table\" class=\"table table-condensed\"\n" +
                "           style=\"table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;\"></table>\n" +
                "</div>\t";
            $("#tab-content").append(div);//div添加到div

            //激活添加的tab
            $("#nav-tabs a:last").tab('show')

        }

        function tab_remove() {
            var tab_number = sessionStorage.getItem('tab_num');
            var active_li_id = sessionStorage.getItem('active_li_id');

            if (active_li_id === 'sqllog_tab') {
                //alert("查询历史tab不允许删除")
            }
            //非查询历史时，删除当前激活的tab
            else if (active_li_id.match(/^execute_result_tab*/)) {
                //sqlquery_result的tab数量大于0才执行
                if (Number(tab_number) > 0) {
                    var n = active_li_id.split("execute_result_tab")[1];
                    $("#" + active_li_id).remove();
                    $("#" + 'sqlquery_result' + n).remove();

                    //激活最后一个tab
                    $("#nav-tabs a:last").tab('show');
                    sessionStorage.setItem('tab_num', Number(tab_number) - 1);
                    //页面只剩下最后一个查询tab，则激活历史查询页
                }
            }
        }

        //表单验证
        function sqlquery_validate() {
            var result = true;
            var select_sqlContent = editor.session.getTextRange(editor.getSelectionRange());
            if (select_sqlContent) {
                var sqlContent = select_sqlContent
            }
            else {
                var sqlContent = editor.getValue();

            }

            var cluster_name = $("#cluster_name").val();
            var db_name = $("#db_name").val();

            if (sqlContent === null || sqlContent.trim() === "") {
                alert("SQL内容不能为空！");
                return result = false;
            } else if (cluster_name === null || cluster_name === $("#cluster_name").attr("data-placeholder")) {
                alert("请选择集群！");
                return result = false;
            } else if (db_name === null || db_name === $("#db_name").attr("data-placeholder")) {
                alert("请选择数据库！");
                return result = false;
            }
            return result;
        }

        //先做表单验证，验证成功再成功提交查询请求
        $("#btn-sqlquery").click(function () {
                if (sqlquery_validate()) {
                    $('input[type=button]').addClass('disabled');
                    $('input[type=button]').prop('disabled', true);
                    sqlquery();
                }
            }
        );

        //先做表单验证，验证成功再成功提交执行计划查看
        $("#btn-explain").click(function () {
                if (sqlquery_validate()) {
                    $('input[type=button]').addClass('disabled');
                    $('input[type=button]').prop('disabled', true);
                    sqlquery('explain')
                }
            }
        );

        //先做表单验证，验证成功再成功提交格式化sql
        $("#btn-format").click(function () {
                var select_sqlContent = editor.session.getTextRange(editor.getSelectionRange());
                if (select_sqlContent) {
                    var sqlContent = select_sqlContent
                }
                else {
                    var sqlContent = editor.getValue();

                }
                var sqlContent = window.sqlFormatter.format(sqlContent);
                editor.setValue(sqlContent);
                editor.clearSelection();
            }
        );

        //将数据通过ajax提交给后端进行检查
        function sqlquery(sql) {
            var columns = [];
            var select_sqlContent = editor.session.getTextRange(editor.getSelectionRange());
            if (select_sqlContent) {
                sqlContent = select_sqlContent
            }
            else {
                var sqlContent = editor.getValue();

            }
            //查看执行计划
            if (sql === 'explain') {
                sqlContent = 'explain ' + sqlContent
            }
            //查看表结构
            else if (sql === 'show create table') {
                var table_name = $("#table_name").val();
                sqlContent = "show create table " + table_name + ";"
            }
            //处理SQL，去除注释、空行等
            sqlContent = sqlContent.replace(/^--\s+.*|^#\s+.*/g, '');
            sqlContent = sqlContent.replace(/[\r\n\f]{2,}/g, '\n');
            sqlContent = sqlContent.trim();
            //提交请求
            $.ajax({
                type: "post",
                url: "/query/",
                dataType: "json",
                data: {
                    cluster_name: $("#cluster_name").val(),
                    db_name: $("#db_name").val(),
                    tb_name: $("#table_name").val(),
                    sql_content: sqlContent,
                    limit_num: $("#limit_num").val()
                },
                complete: function () {
                    $('input[type=button]').removeClass('disabled');
                    $('input[type=button]').prop('disabled', false);
                },
                success: function (data) {
                    if (data.status === 0 || data.status === 2) {
                        //获取当前的标签页,如果当前不在执行结果页，则默认新增一个页面
                        var active_li_id = sessionStorage.getItem('active_li_id');

                        if (active_li_id.match(/^execute_result_tab*/)) {
                            //查看表结构默认打开新窗口
                            if (sql === 'show create table') {
                                tab_add();
                                n = sessionStorage.getItem('tab_num');
                            }
                            else {
                                var n = active_li_id.split("execute_result_tab")[1];
                            }
                        }
                        else {
                            tab_add();
                            n = sessionStorage.getItem('tab_num');
                        }

                        var result = data.data;
                        //查询报错失败
                        if (result['Error']) {
                            alertStyle = "alert-danger";
                            $("#" + ('query_result' + n)).bootstrapTable('destroy').bootstrapTable({
                                columns: [{
                                    field: 'error',
                                    title: 'Error'
                                }],
                                data: [{
                                    error: 'mysql返回异常：' + result['Error']
                                }]
                            })
                        }
                        //inception检测报错
                        else if (data.status === 2) {
                            var errer_info = data.msg;
                            //替换所有的换行符
                            errer_info = errer_info.replace(/\r\n/g, "<br>");
                            errer_info = errer_info.replace(/\n/g, "<br>");
                            //替换所有的空格
                            errer_info = errer_info.replace(/\s/g, "&nbsp;");
                            alertStyle = "alert-danger";
                            $("#" + ('query_result' + n)).bootstrapTable('destroy').bootstrapTable({
                                columns: [{
                                    field: 'error',
                                    title: 'Error'
                                }],
                                data: [{
                                    error: errer_info
                                }]
                            })
                        }
                        else if (result['column_list']) {
                            //异步获取要动态生成的列
                            $.each(result['column_list'], function (i, column) {
                                columns.push({
                                    "field": i,
                                    "title": column,
                                    "sortable": true,
                                    "formatter": function (value, row, index) {
                                        //return value;
                                        return $('<div/>').text(value).html();
                                    }
                                });
                            });
                            /*//插入选择框
                            columns.unshift({
                                title: '',
                                field: 'checkbox',
                                checkbox: true
                            });*/
                            if (sqlContent.match(/^show\s+create\s+table/)) {
                                //初始化表结构显示
                                $("#" + ("query_result" + n)).bootstrapTable('destroy').bootstrapTable({
                                        data: result['rows'],
                                        columns: [{
                                            title: 'Create Table',
                                            field: 1,
                                            formatter: function (value, row, index) {
                                                var sql = window.sqlFormatter.format(value);
                                                //替换所有的换行符
                                                sql = sql.replace(/\r\n/g, "<br>");
                                                sql = sql.replace(/\n/g, "<br>");
                                                //替换所有的空格
                                                sql = sql.replace(/\s/g, "&nbsp;");
                                                return sql;

                                            }
                                        }
                                        ],
                                        locale: 'zh-CN'
                                    }
                                );
                            }
                            else {
                                //初始化查询结果
                                $("#" + ('query_result' + n)).bootstrapTable('destroy').bootstrapTable({
                                    data: result['rows'],
                                    columns: columns,
                                    showExport: true,
                                    exportDataType: "all",
                                    //exportTypes: ['sql'],
                                    exportOptions: {
                                        //ignoreColumn: [0],  //忽略某些列的索引数组
                                        fileName: 'export_result'  //文件名称设置
                                    },
                                    showColumns: true,
                                    showToggle: true,
                                    clickToSelect: true,
                                    striped: true,
                                    pagination: true,
                                    pageSize: 30,
                                    pageList: [30, 50, 100, 500, 1000],
                                    locale: 'zh-CN'
                                });
                            }
                            //执行时间和脱敏时间赋值
                            $("#" + ('time') + n).text(result['cost_time'] + ' sec');
                            $("#" + ('masking_time') + n).text(result['masking_cost_time'] + ' sec');
                        }
                    } else {
                        alert("status: " + data.status + "\nmsg: " + data.msg);
                    }
                },
                error: function (XMLHttpRequest, textStatus, errorThrown) {
                    alert(errorThrown);
                }
            });
        }
    </script>
    <!-- common -->
    <script>
        // 集群变更获取数据库
        $("#cluster_name").change(function () {
            sessionStorage.setItem('sql_query_cluster_name', $("#cluster_name").val());
            //将数据通过ajax提交给获取db_name
            $.ajax({
                type: "post",
                url: "/getdbNameList/",
                dataType: "json",
                data: {
                    cluster_name: $("#cluster_name").val()
                },
                complete: function () {
                },
                success: function (data) {
                    if (data.status === 0) {
                        var result = data.data;
                        var dbs = [];
                        $("#db_name").empty();
                        for (var i = 0; i < result.length; i++) {
                            var name = "<option value=\"" + result[i] + "\">" + result[i] + "</option>";
                            $("#db_name").append(name);
                            dbs.push({
                                name: result[i],
                                value: result[i],
                                caption: result[i],
                                meta: 'databases',
                                score: '100'
                            })
                        }
                        $("#db_name").prepend("<option value=\"is-empty\" disabled=\"\" selected=\"selected\">请选择数据库:</option>");
                        $('#db_name').selectpicker('render');
                        $('#db_name').selectpicker('refresh');
                        //自动补全提示
                        setCompleteData(dbs)
                    } else {
                        alert("status: " + data.status + "\nmsg: " + data.msg + data.data);
                    }
                },
                error: function (XMLHttpRequest, textStatus, errorThrown) {
                    alert(errorThrown);
                }
            });
        });


        //数据库变更获取表名称
        $("#db_name").change(function () {
            //将数据通过ajax提交给获取db_name
            $.ajax({
                type: "post",
                url: "/getTableNameList/",
                dataType: "json",
                data: {
                    cluster_name: $("#cluster_name").val(),
                    db_name: $("#db_name").val()
                },
                complete: function () {
                },
                success: function (data) {
                    if (data.status === 0) {
                        var result = data.data;
                        $("#table_name").empty();
                        for (var i = 0; i < result.length; i++) {
                            var name = "<option>" + result[i] + "</option>";
                            $("#table_name").append(name);
                        }
                        $("#table_name").prepend("<option value=\"is-empty\" disabled=\"\" selected=\"selected\">查看表结构:</option>");
                        $('#table_name').selectpicker('render');
                        $('#table_name').selectpicker('refresh');
                        //自动补全提示
                        setTablesCompleteData(result)
                    } else {
                        alert("status: " + data.status + "\nmsg: " + data.msg + data.data);
                    }
                },
                error: function (XMLHttpRequest, textStatus, errorThrown) {
                    alert(errorThrown);
                }
            });
        });

        //获取表结构
        $("#table_name").change(function () {
            sqlquery('show create table');
            //自动补全提示
            setColumnsCompleteData()
        });

        //激活标签页时保存当前标签页的id
        $(function () {
            $("#nav-tabs").on('shown.bs.tab', "li", function (e) {
                //当前激活的标签id
                sessionStorage.setItem('active_li_id', $(e.target).parents().attr('id'));
                if ($(e.target).parents().attr('id') === 'sqllog_tab') {
                    get_querylog();
                }
            });
        });

        //初始化
        $(document).ready(function () {
            //重置执行结果的tab数量
            sessionStorage.setItem('tab_num', 0);
            //设置当前激活的标签id
            sessionStorage.setItem('active_li_id', 'sqllog_tab');
            //默认获取查询历史
            get_querylog();

            //填充集群名
            $('#cluster_name').selectpicker();
            var myObject = {};
            myObject.sql_query_cluster_name = sessionStorage.getItem('sql_query_cluster_name');
            if (myObject.sql_query_cluster_name) {
                $("#cluster_name").selectpicker('val', myObject.sql_query_cluster_name).trigger("change");
            }
        });
    </script>
{% endblock %}