{% extends "base.html" %}
{% block content %}
<div class="row clearfix">
<div class="col-md-3">
<div class="panel panel-default">
<div class="panel-heading">
操作选项
</div>
<div class="panel-body">
<div class="form-group">
<h5 class="control-label text-bold">选择实例：</h5>
<div class="form-group">
<select id="instance_name" name="instance_name"
class="selectpicker show-tick form-control bs-select-hidden"
title="请选择实例:"
data-live-search="true" required>
</select>
</div>
</div>
<div class="form-group">
<div class="form-group">
<div class="checkbox">
<label>
<input id="save_sql" type="checkbox">
是否保存到文件(异步操作)
</label>
</div>
</div>
</div>
<div class="form-group">
<h5 class="control-label text-bold">解析模式：</h5>
<div class="form-group">
<div class="checkbox">
<label>
<input id="rollback" type="checkbox">
-work-type rollback
</label>
</div>
<div class="checkbox">
<label>
<input id="ignore_primary_key" type="checkbox">
-ignore-primaryKey-forInsert
</label>
</div>
<div class="checkbox">
<label>
<input id="full_columns" type="checkbox">
-full-columns
</label>
</div>
<div class="checkbox">
<label>
<input id="no_db_prefix" type="checkbox">
-do-not-add-prifixDb
</label>
</div>
<div class="checkbox">
<label>
<input id="file_per_table" type="checkbox">
-file-per-table
</label>
</div>
<div class="checkbox">
<label>
<input id="extra_info" type="checkbox">
-add-extraInfo
</label>
</div>
</div>
</div>
<div class="form-group">
<h5 class="control-label text-bold">解析线程数：</h5>
<div class="form-group">
<input type="number" class="form-control" id="threads" placeholder="默认4"
onkeyup="if(event.keyCode !=37 && event.keyCode != 39)value=value.replace(/\D/g,'')">
</div>
</div>
<div class="form-group">
<h5 class="control-label text-bold">解析范围控制：</h5>
<div class="form-group">
<input type="number" class="form-control" id="num" placeholder="页面解析行数，默认30"
onkeyup="if(event.keyCode !=37 && event.keyCode != 39)value=value.replace(/\D/g,'')">
</div>
<div class="form-group">
<select id="start_file" class="selectpicker form-control"
title="起始解析文件:"
required>
</select>
</div>
<div class="form-group">
<input type="number" class="form-control" id="start_pos" placeholder="起始解析位置(可选)"
onkeyup="if(event.keyCode !=37 && event.keyCode != 39)value=value.replace(/\D/g,'')">
</div>
<div class="form-group">
<select id="end_file" class="selectpicker form-control"
title="终止解析文件(可选):"
required>
</select>
</div>
<div class="form-group">
<input type="number" class="form-control" id="end_pos" placeholder="终止解析位置(可选)"
onkeyup="if(event.keyCode !=37 && event.keyCode != 39)value=value.replace(/\D/g,'')">
</div>
<div class="form-group">
<input type="text" class="form-control form_datetime" id="start_time"
placeholder="起始解析时间(不建议)">
</div>
<div class="form-group">
<input type="text" class="form-control form_datetime" id="stop_time"
placeholder="终止解析时间(可选)">
</div>
</div>
<div class="form-group">
<h5 class="control-label text-bold">对象过滤：</h5>
<div class="form-group">
<select id="only_schemas" class="selectpicker form-control "
data-live-search="true"
title="数据库过滤(可选):"
required>
</select>
</div>
<div class="form-group">
<select id="only_tables" class="selectpicker form-control "
data-live-search="true"
title="表过滤(可选):"
multiple="multiple" required>
</select>
</div>
<select id="sql_type" class="selectpicker form-control "
data-live-search="true"
title="类型过滤(可选):"
multiple="multiple" required>
<option value="insert">INSERT</option>
<option value="update">UPDATE</option>
<option value="delete">DELETE</option>
</select>
</div>
<div class="form-group">
<button id="my2sql" class="btn btn-danger">获取SQL</button>
</div>
</div>
</div>
</div>
<div class="col-md-9 column">
<div class="panel panel-default">
<div class="panel-heading">
SQL语句
</div>
<div class="panel-body">
<h5 class="control-label text-bold" style="color: red">
<b>前端展示数量由解析范围参数控制，如果勾选了保存到文件，则会异步获取完整的SQL文件，
保存到项目downloads目录，在开启消息通知后，执行结束会发送通知给操作人</b>
</h5>
<br>
<table id="tb-my2sql" data-toggle="table" class="table table-condensed"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;"></table>
</div>
</div>
</div>
</div>
{% endblock content %}
{% block js %}
{% load static %}
<link href="{% static 'datetimepicker/css/bootstrap-datetimepicker.css' %}" rel="stylesheet" type="text/css"/>
<script src="{% static 'daterangepicker/js/moment.min.js' %}"></script>
<script src="{% static 'datetimepicker/js/bootstrap-datetimepicker.js' %}"></script>
<script src="{% static 'datetimepicker/js/bootstrap-datetimepicker.zh-CN.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>
$(".form_datetime").datetimepicker({format: 'yyyy-mm-dd hh:ii:ss', language: 'zh-CN', autoclose: true});
//获取用户实例列表
$(function () {
$.ajax({
type: "get",
url: "/group/user_all_instances/",
dataType: "json",
data: {
db_type: ['mysql']
},
complete: function () {
},
success: function (data) {
if (data.status === 0) {
let result = data['data'];
$("#instance_name").empty();
for (let i = 0; i < result.length; i++) {
let instance = "<option value=\"" + result[i]['instance_name'] + "\">" + result[i]['instance_name'] + "</option>";
$("#instance_name").append(instance);
}
$('#instance_name').selectpicker('render');
$('#instance_name').selectpicker('refresh');
} else {
alert(data.msg);
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(errorThrown);
}
});
});
//实例变更获取数据库BINLOG
$("#instance_name").change(function () {
//获取db_name
$.ajax({
type: "get",
url: "/instance/instance_resource/",
dataType: "json",
data: {
instance_name: $("#instance_name").val(),
resource_type: "database"
},
complete: function () {
},
success: function (data) {
if (data.status === 0) {
var result = data.data;
$("#only_schemas").empty();
for (var i = 0; i < result.length; i++) {
var name = "<option value=\"" + result[i] + "\">" + result[i] + "</option>";
$("#only_schemas").append(name);
}
$('#only_schemas').selectpicker('render');
$('#only_schemas').selectpicker('refresh');
} else {
alert(data.msg);
}
}
});
//获取binlog
$.ajax({
type: "post",
url: "/binlog/list/",
dataType: "json",
data: {
instance_name: $("#instance_name").val()
},
complete: function () {
},
success: function (data) {
if (data.status === 0) {
var result = data.data;
$("#start_file").empty();
$("#end_file").empty();
for (var i = 0; i < result.length; i++) {
var name = "<option value=\"" + result[i]['Log_name'] + "\">" + result[i]['Log_name'] + ' Size:' + result[i]['File_size'] + "</option>";
$("#start_file").append(name);
$("#end_file").append(name);
}
$('#start_file').selectpicker('render');
$('#start_file').selectpicker('refresh');
$('#end_file').selectpicker('render');
$('#end_file').selectpicker('refresh');
} else {
alert(data.msg);
}
}
});
});
//start-file变更重置start-pos
$("#start_file").change(function () {
$("#start_pos").val('');
});
//end-file变更重置end-pos
$("#end_file").change(function () {
$("#end_pos").val('');
});
//数据库变更获取表名称
$("#only_schemas").change(function () {
//将数据通过ajax提交给获取db_name
$.ajax({
type: "get",
url: "/instance/instance_resource/",
dataType: "json",
data: {
instance_name: $("#instance_name").val(),
db_name: $("#only_schemas").val(),
resource_type: "table"
},
complete: function () {
},
success: function (data) {
if (data.status === 0) {
var result = data.data;
$("#only_tables").empty();
for (var i = 0; i < result.length; i++) {
var name = "<option>" + result[i] + "</option>";
$("#only_tables").append(name);
}
$("#only_tables").prepend("<option value=\"is-empty\" disabled=\"\">表过滤(可选):</option>");
$('#only_tables').selectpicker('render');
$('#only_tables').selectpicker('refresh');
} else {
alert(data.msg);
}
}
});
});
//获取语句
$("#my2sql").click(function () {
if ($("#instance_name").val() && $("#start_file").val()) {
if (!$("#end_file").val()) {
$("#end_file").val($("#start_file").val());
$('#end_file').selectpicker('render');
}
if (!$("#end_pos").val()) {
let default_end_pos = $("#end_file option:selected").text().split('Size:')[1];
$("#end_pos").val(default_end_pos);
}
$(this).addClass('disabled');
$(this).prop('disabled', true);
$.ajax({
type: "post",
url: "/binlog/my2sql/",
dataType: "json",
data: {
instance_name: $("#instance_name").val(),
save_sql: document.getElementById("save_sql").checked,
rollback: document.getElementById("rollback").checked,
extra_info: document.getElementById("extra_info").checked,
ignore_primary_key: document.getElementById("ignore_primary_key").checked,
full_columns: document.getElementById("full_columns").checked,
no_db_prefix: document.getElementById("no_db_prefix").checked,
file_per_table: document.getElementById("file_per_table").checked,
threads: $("#threads").val(),
num: $("#num").val(),
start_file: $("#start_file").val(),
start_pos: $("#start_pos").val(),
end_file: $("#end_file").val(),
end_pos: $("#end_pos").val(),
stop_time: $("#stop_time").val(),
start_time: $("#start_time").val(),
only_schemas: $("#only_schemas").val(),
only_tables: $("#only_tables").val(),
sql_type: $("#sql_type").val(),
},
complete: function () {
$("#my2sql").removeClass('disabled');
$("#my2sql").prop('disabled', false);
},
success: function (data) {
if (data.status === 0) {
$('#tb-my2sql').bootstrapTable('destroy').bootstrapTable({
escape: true,
striped: true, //是否显示行间隔色
cache: false, //是否使用缓存，默认为true，所以一般情况下需要设置一下这个属性（*）
pagination: true, //是否显示分页（*）
sortable: true, //是否启用排序
sidePagination: "client", //分页方式：client客户端分页，server服务端分页（*）
pageNumber: 1, //初始化加载第一页，默认第一页,并记录
pageSize: 100, //每页的记录行数（*）
pageList: [100, 500, 1000], //可供选择的每页的行数（*）
search: false, //是否显示表格搜索
strictSearch: false, //是否全匹配搜索
showColumns: true, //是否显示所有的列（选择显示的列）
showRefresh: false, //是否显示刷新按钮
showExport: true,
exportDataType: "all",
minimumCountColumns: 1, //最少允许的列数
clickToSelect: false, //是否启用点击选中行
uniqueId: "id", //每一行的唯一标识，一般为主键列
showToggle: true, //是否显示详细视图和列表视图的切换按钮
cardView: false, //是否显示详细视图
locale: 'zh-CN', //本地化
detailView: true, //是否显示父子表
//格式化详情
detailFormatter: function (index, row) {
var html = [];
$.each(row, function (key, value) {
if (key === 'sql') {
var sql = window.sqlFormatter.format(value);
//替换所有的换行符
sql = sql.replace(/\r\n/g, "<br>");
sql = sql.replace(/\n/g, "<br>");
//替换所有的空格
sql = sql.replace(/\s/g, " ");
html.push('<span>' + sql + '</span>');
}
});
return html.join('');
},
data: data.data,
columns: [{
title: 'SQL',
field: 'sql',
formatter: function (value, row, index) {
if (value.length > 200) {
var sql = row.sql.substr(0, 150) + '...';
return sql;
} else {
return value
}
}
}, {
title: '完整SQL',
field: 'sql',
visible: false // 默认不显示
}],
onLoadSuccess: function () {
},
onLoadError: onLoadErrorCallback,
});
} else {
alert(data.msg);
}
}
});
} else {
alert("请选择实例和起始解析文件！")
}
})
</script>
{% endblock %}