{% extends "base.html" %}
{% block content %}
<!-- 自定义操作按钮-->
<div class="form-group ">
<form action="/data_dictionary/export/">
<div id="toolbar" class="form-inline">
<div class="form-group">
<select id="instance_name" class="form-control selectpicker "
name="instance_name"
title="请选择实例"
data-live-search="true">
<optgroup id="optgroup-mysql" label="MySQL"></optgroup>
<optgroup id="optgroup-mssql" label="MsSQL"></optgroup>
<optgroup id="optgroup-oracle" label="Oracle"></optgroup>
</select>
</div>
<div class="form-group">
<select id="db_name" class="form-control selectpicker "
name="db_name"
title="请选择数据库"
data-live-search="true">
</select>
</div>
{% if perms.sql.data_dictionary_export %}
<div class="form-group">
<button id="btn_export_dict" type="submit" disabled="disabled" class="btn btn-default">
<span class="glyphicon glyphicon-export" aria-hidden="true"></span>
导出
</button>
</div>
{% endif %}
</div>
</form>
</div>
<div id="jumpbox" class="modindex-jumpbox">
</div>
<table id="indexTable" class="indextable modindextable">
</table>
<div class="modal fade" id="showModal">
<div class="modal-dialog modal-lg">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal"><span
aria-hidden="true">×</span><span class="sr-only">Close</span></button>
<h4 class="modal-title">表元数据展示</h4>
</div>
<div class="modal-body" id="modal-body-mysql" >
<div class="panel panel-default">
<div class="panel-heading">表信息</div>
<div class="panel-body">
<div class="table-responsive">
<table id="meta_data" class="table table-striped table-hover"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">
<tbody>
<tr>
<th>表名</th>
<td id="table_name"></td>
<th>引擎</th>
<td id="engine"></td>
<th>行格式</th>
<td id="row_format"></td>
<th>表行数</th>
<td id="table_rows"></td>
</tr>
<tr>
<th>平均行长度</th>
<td id="avg_row_length"></td>
<th>最大行长度</th>
<td id="max_data_length"></td>
<th>数据长度（K）</th>
<td id="data_length"></td>
<th>索引长度（K）</th>
<td id="index_length"></td>
</tr>
<tr>
<th>数据总大小（K）</th>
<td id="data_total"></td>
<th>碎片大小</th>
<td id="data_free"></td>
<th>当前自增值</th>
<td id="auto_increment"></td>
<th>表的校验码</th>
<td id="table_collation"></td>
</tr>
<tr>
<th>创建时间</th>
<td id="create_time"></td>
<th>更新时间</th>
<td id="update_time"></td>
<th>检查时间</th>
<td id="check_time"></td>
<th>表说明</th>
<td id="table_comment"></td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class="panel panel-default">
<div class="panel-heading">列信息</div>
<div class="panel-body">
<div class="table-responsive">
<table id="field-list-mysql" data-toggle="table" class="table table-striped table-hover"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">
</table>
</div>
</div>
</div>
<div class="panel panel-default">
<div class="panel-heading">索引信息</div>
<div class="panel-body">
<div class="table-responsive">
<table id="index-list-mysql" data-toggle="table" class="table table-striped table-hover"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">
</table>
</div>
</div>
</div>
<div class="panel panel-default">
<div class="panel-heading">建表语句</div>
<div class="panel-body">
<div class="table-responsive">
<table id="create-table-sql" data-toggle="table" class="table table-striped table-hover"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">
</table>
</div>
</div>
</div>
</div> <!--modal-body-->
<div class="modal-body" id="modal-body-mssql" >
<div class="panel panel-default">
<div class="panel-heading">表信息</div>
<div class="panel-body">
<div class="table-responsive">
<table id="meta_data" class="table table-striped table-hover"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">
<tbody>
<tr>
<th>表名</th>
<td id="table_name"></td>
<th>表行数</th>
<td id="table_rows"></td>
</tr>
<tr>
<th>数据长度（K）</th>
<td id="data_length"></td>
<th>索引长度（K）</th>
<td id="index_length"></td>
</tr>
<tr>
<th>数据总大小（K）</th>
<td id="data_total"></td>
<th>碎片大小</th>
<td id="data_free"></td>
</tr>
<tr>
<th>创建时间</th>
<td id="create_time"></td>
<th>更新时间</th>
<td id="update_time"></td>
</tr>
<tr>
<th>当前自增值</th>
<td id="auto_increment"></td>
<th>表说明</th>
<td id="table_comment"></td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class="panel panel-default">
<div class="panel-heading">列信息</div>
<div class="panel-body">
<div class="table-responsive">
<table id="field-list-mssql" data-toggle="table" class="table table-striped table-hover"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">
</table>
</div>
</div>
</div>
<div class="panel panel-default">
<div class="panel-heading">索引信息</div>
<div class="panel-body">
<div class="table-responsive">
<table id="index-list-mssql" data-toggle="table" class="table table-striped table-hover"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">
</table>
</div>
</div>
</div>
</div> <!--modal-body-->
<div class="modal-body" id="modal-body-oracle" >
<div class="panel panel-default">
<div class="panel-heading">表信息</div>
<div class="panel-body">
<div class="table-responsive">
<table id="meta_data" class="table table-striped table-hover"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">
<tbody>
<tr>
<th>表名</th>
<td id="TABLE_NAME"></td>
<th>表类型</th>
<td id="TABLE_TYPE"></td>
</tr>
<tr>
<th>段类型</th>
<td id="SEGMENT_TYPE"></td>
<th>压缩</th>
<td id="COMPRESSION"></td>
</tr>
<tr></tr>
<th>表空间</th>
<td id="TABLESPACE_NAME"></td>
<th>表中的记录数</th>
<td id="NUM_ROWS"></td>
</tr>
<tr>
<th>表中数据所占的数据块数</th>
<td id="BLOCKS"></td>
<th>表中的空块数</th>
<td id="EMPTY_BLOCKS"></td>
</tr>
<tr>
<th>数据块中平均的使用空间</th>
<td id="AVG_SPACE"></td>
<th>表中行连接和行迁移的数量</th>
<td id="CHAIN_CNT"></td>
</tr>
<tr>
<th>每条记录的平均长度</th>
<td id="AVG_ROW_LEN"></td>
<th>上次统计信息搜集的时间</th>
<td id="LAST_ANALYZED"></td>
</tr>
<tr>
<th>表注释</th>
<td id="COMMENTS"></td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
<div class="panel panel-default">
<div class="panel-heading">列信息</div>
<div class="panel-body">
<div class="table-responsive">
<table id="field-list-oracle" data-toggle="table" class="table table-striped table-hover"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">
</table>
</div>
</div>
</div>
<div class="panel panel-default">
<div class="panel-heading">索引信息</div>
<div class="panel-body">
<div class="table-responsive">
<table id="index-list-oracle" data-toggle="table" class="table table-striped table-hover"
style="table-layout:inherit;white-space:nowrap;overflow:hidden;text-overflow:ellipsis;">
</table>
</div>
</div>
</div>
</div> <!--modal-body-->
</div><!-- /.modal-content -->
</div><!-- /.modal-dialog -->
</div><!-- /.modal -->
{% endblock content %}
{% block js %}
{% load static %}
<script src="{% static 'bootstrap-table/js/bootstrap-table-export.min.js' %}"></script>
<script src="{% static 'bootstrap-table/js/tableExport.min.js' %}"></script>
<script>
var instance_result ="";
$(document).ready(function () {
//获取用户实例列表
$(function () {
$.ajax({
type: "get",
url: "/group/user_all_instances/",
dataType: "json",
data: {
db_type: ['mysql', 'mssql', 'oracle']
},
complete: function () {
if ($('#instance_name').val()) {
$('#instance_name').selectpicker().trigger("change");
}
},
success: function (data) {
if (data.status === 0) {
let result = data['data'];
instance_result = data['data'];
$("#optgroup-mysql").empty();
$("#optgroup-mssql").empty();
$("#optgroup-oracle").empty();
console.log(instance_result);
for (let i = 0; i < result.length; i++) {
let instance_name = "<option value=\"" + result[i]['instance_name'] + "\">" + result[i]['instance_name'] + "</option>";
if (result[i]['db_type'] === 'mysql') {
$("#optgroup-mysql").append(instance_name);
} else if (result[i]['db_type'] === 'mssql') {
$("#optgroup-mssql").append(instance_name);
} else if (result[i]['db_type'] === 'oracle') {
$("#optgroup-oracle").append(instance_name);
}
}
$('#instance_name').selectpicker('render');
$('#instance_name').selectpicker('refresh');
} else {
alert(data.msg);
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(errorThrown);
}
});
});
});
//实例变动获取库
$("#instance_name").change(function () {
$('#db_name').empty();
$.ajax({
type: "get",
url: "/instance/instance_resource/",
dataType: "json",
data: {
instance_name: $("#instance_name").val(),
resource_type: "database"
},
success: function (data) {
if (data.status === 0) {
var result = data.data;
for (var i = 0; i < result.length; i++) {
var name = "<option value=\"" + result[i] + "\">" + result[i] + "</option>";
$("#db_name").append(name);
}
} else {
alert(data.msg);
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(errorThrown);
},
complete: function () {
$('#db_name').selectpicker('render');
$('#db_name').selectpicker('refresh');
// 管理员激活导出按钮
if ("{{ request.user.is_superuser }}" === 'True') {
$('#btn_export_dict').removeClass('disabled');
$('#btn_export_dict').prop('disabled', false);
}
}
});
});
//库变动获取表
$("#db_name").change(function () {
get_table_list()
});
// 获取表
function get_table_list() {
var instance_name = $("#instance_name").val();
var db_name = $('#db_name').val();
var inst_db_type_ind = instance_result.findIndex((value)=>value.instance_name==instance_name);
var inst_db_type = instance_result[inst_db_type_ind]['db_type']
if (instance_name === "" || db_name === "") {
alert("请先选择实例和数据库！");
} else {
$.ajax({
type: "get",
url: "/data_dictionary/table_list/",
dataType: "json",
data: {
instance_name: instance_name,
db_name: db_name,
db_type: inst_db_type
},
success: function (data) {
if (data.status === 0) {
// 激活导出按钮
$('#btn_export_dict').removeClass('disabled');
$('#btn_export_dict').prop('disabled', false);
$('#jumpbox').empty();
$('#indexTable').empty();
var result = data.data;
for (var k in result) {
var jumpBoxStr = '<a href="#cap-' + k + '" style="margin-left:0.4em;"><strong>' + k + '</strong></a>';
$('#jumpbox').append(jumpBoxStr);
$('#indexTable').append('<tr class="cap" id="cap-' + k + '">' +
'<td></td>' +
'<td><strong>' + k + '</strong></td>' +
'<td></td>' +
'</tr>');
for (var i = 0; i < result[k].length; i++) {
var indexTableStr = '<tr>' +
'<td></td>' +
'<td><a href="javascript:;" onclick="showTableInfo(\'' + instance_name + '\',\'' + db_name + '\',\'' + result[k][i][0] + '\')" >' +
'<code class="xref">' + result[k][i][0] + '</code>' +
'</a></td>' +
'<td>' + result[k][i][1] + '</td>' +
'</tr>';
// console.log(indexTableStr);
$('#indexTable').append(indexTableStr);
}
}
$('#indexTable').prepend('<tr class="pcap"><td> </td></tr>')
} else {
alert(data.msg);
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(errorThrown);
},
complete: function () {
}
});
}
}
// 根据传入的参数显示哪一种数据库的modal body
function showModalBody(db_type) {
$(".modal-body").hide();
$("#modal-body-" + db_type).show();
}
// 展示表信息
function showTableInfo(ins_name, db_name, tb_name) {
// console.log(ins_name, db_name, tb_name);
var inst_db_type_ind = instance_result.findIndex((value)=>value.instance_name==ins_name);
var inst_db_type = instance_result[inst_db_type_ind]['db_type'];
$.ajax({
type: "get",
url: "/data_dictionary/table_info/",
dataType: "json",
data: {
instance_name: ins_name,
db_name: db_name,
tb_name: tb_name,
db_type: inst_db_type
},
success: function (data) {
if (data.status === 0) {
var result = data.data;
if (inst_db_type === 'mysql') {
showModalBody('mysql');
//console.log("hide_oracle");
$("#create-table-sql").bootstrapTable('destroy').bootstrapTable({
data: result['create_sql'],
columns: [{
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, " ");
return sql;
}
}],
locale: 'zh-CN'
});
} else if (inst_db_type === 'oracle') {
showModalBody('oracle');
//console.log("hide_mysql");
} else if (inst_db_type === 'mssql') {
showModalBody('mssql');
}
var meta_data_columns = result['meta_data']['column_list'];
var meta_data_values = result['meta_data']['rows'];
for (var i = 0; i < meta_data_columns.length; i++) {
//console.log(meta_data_columns[i], meta_data_values[i]);
$('#meta_data #' + meta_data_columns[i]).text(meta_data_values[i])
}
var columns_field = [];
$.each(result['desc']['column_list'], function (i, column) {
columns_field.push({
"field": i,
"title": column,
"sortable": true
});
});
$("#field-list-" + inst_db_type).bootstrapTable('destroy').bootstrapTable({
escape: true,
data: result['desc']['rows'],
columns: columns_field,
showExport: true,
exportDataType: "all",
exportTypes: ['json', 'sql', 'csv', 'txt', 'xml', 'xlsx'],
exportOptions: {
//ignoreColumn: [0], //忽略某些列的索引数组
fileName: 'field' //文件名称设置
},
showColumns: true,
showToggle: true,
clickToSelect: true,
striped: true,
pagination: true,
pageSize: 30,
pageList: [30, 50, 100, 500, 1000],
locale: 'zh-CN'
});
var columns_index = [];
$.each(result['index']['column_list'], function (i, column) {
columns_index.push({
"field": i,
"title": column,
"sortable": true
});
});
$("#index-list-" + inst_db_type).bootstrapTable('destroy').bootstrapTable({
escape: true,
data: result['index']['rows'],
columns: columns_index,
showExport: false,
showColumns: false,
showToggle: false,
clickToSelect: true,
striped: true,
pagination: true,
pageSize: 30,
pageList: [30, 50, 100, 500, 1000],
locale: 'zh-CN'
});
//console.log("desc_rows" ,result['desc']['rows'])
$('#showModal').modal('show');
} else {
alert(data.msg);
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(errorThrown);
},
complete: function () {
}
});
}
</script>
{% endblock %}