基于Vue和Quasar的前端SPA项目实战之数据库逆向（十二）
回顾
通过之前文章基于Vue和Quasar的前端SPA项目实战之动态表单（五）的介绍，实现了动态表单功能。如果是全新的项目，通过配置元数据并且创建物理表，从而自动实现业务数据的CRUD增删改查。但是如果数据库表已经存在的情况下，如何通过配置表单元数据进行管理呢？这时候数据库逆向功能就很有必要了。
简介
数据库逆向就是通过读取数据库物理表schema信息，然后生成表单元数据，可以看成”dbfirst”模式，即先有数据库表，然后根据表生成元数据，逆向表单后续操作和普通动态表单类似。
UI界面
输入物理表名称，启用”数据库逆向”功能，然后点击”加载元数据”，然后会自动填充表单字段相关元数据信息。
数据表准备
以ca_product产品为例，通过phpmyadmin创建表
创建产品表
CREATE TABLE ca_product (
id bigint UNSIGNED NOT NULL COMMENT '编号',
name varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名称',
fullTextBody text COLLATE utf8mb4_unicode_ci COMMENT '全文索引',
createdDate datetime NOT NULL COMMENT '创建时间',
lastModifiedDate datetime DEFAULT NULL COMMENT '修改时间',
code varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '编码',
brand varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '品牌',
price decimal(10,0) DEFAULT NULL COMMENT '单价',
weight decimal(10,0) DEFAULT NULL COMMENT '重量',
length decimal(10,0) DEFAULT NULL COMMENT '长',
width decimal(10,0) DEFAULT NULL COMMENT '宽',
high decimal(10,0) DEFAULT NULL COMMENT '高',
ats bigint DEFAULT NULL COMMENT '库存个数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='产品';
ALTER TABLE ca_product
ADD PRIMARY KEY (id),
ADD UNIQUE KEY UQ_CODE (code) USING BTREE;
ALTER TABLE ca_product ADD FULLTEXT KEY ft_fulltext_body (fullTextBody);
ALTER TABLE ca_product
MODIFY id bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号', AUTO_INCREMENT=1;
COMMIT;
查询schema
mysql数据库通过如下SQL语句可以查询表单、字段、索引等信息
SHOW TABLE STATUS LIKE TABLE_NAME
SHOW FULL COLUMNS FROM TABLE_NAME
SHOW INDEX FROM TABLE_NAME
表基本信息
字段信息
索引信息
API JSON
通过API https://demo.crudapi.cn/api/metadata/tables/metadata/ca_product
查询ca_product的schema信息, 格式如下：
{
"Name": "ca_product",
"Engine": "InnoDB",
"Version": 10,
"Row_format": "Dynamic",
"Rows": 0,
"Avg_row_length": 0,
"Data_length": 16384,
"Max_data_length": 0,
"Index_length": 32768,
"Data_free": 0,
"Auto_increment": 2,
"Create_time": 1628141282000,
"Update_time": 1628141304000,
"Collation": "utf8mb4_unicode_ci",
"Create_options": "",
"Comment": "产品",
"columns": [{
"Field": "id",
"Type": "bigint unsigned",
"Null": "NO",
"Key": "PRI",
"Extra": "auto_increment",
"Privileges": "select,insert,update,references",
"Comment": "编号"
}, {
"Field": "name",
"Type": "varchar(200)",
"Collation": "utf8mb4_unicode_ci",
"Null": "NO",
"Key": "",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "名称"
}, {
"Field": "fullTextBody",
"Type": "text",
"Collation": "utf8mb4_unicode_ci",
"Null": "YES",
"Key": "MUL",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "全文索引"
}, {
"Field": "createdDate",
"Type": "datetime",
"Null": "NO",
"Key": "",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "创建时间"
}, {
"Field": "lastModifiedDate",
"Type": "datetime",
"Null": "YES",
"Key": "",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "修改时间"
}, {
"Field": "code",
"Type": "varchar(200)",
"Collation": "utf8mb4_unicode_ci",
"Null": "YES",
"Key": "UNI",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "编码"
}, {
"Field": "brand",
"Type": "varchar(200)",
"Collation": "utf8mb4_unicode_ci",
"Null": "YES",
"Key": "",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "品牌"
}, {
"Field": "price",
"Type": "decimal(10,0)",
"Null": "YES",
"Key": "",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "单价"
}, {
"Field": "weight",
"Type": "decimal(10,0)",
"Null": "YES",
"Key": "",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "重量"
}, {
"Field": "length",
"Type": "decimal(10,0)",
"Null": "YES",
"Key": "",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "长"
}, {
"Field": "width",
"Type": "decimal(10,0)",
"Null": "YES",
"Key": "",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "宽"
}, {
"Field": "high",
"Type": "decimal(10,0)",
"Null": "YES",
"Key": "",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "高"
}, {
"Field": "ats",
"Type": "bigint",
"Null": "YES",
"Key": "",
"Extra": "",
"Privileges": "select,insert,update,references",
"Comment": "库存个数"
}],
"indexs": [{
"Table": "ca_product",
"Non_unique": 0,
"Key_name": "PRIMARY",
"Seq_in_index": 1,
"Column_name": "id",
"Collation": "A",
"Cardinality": 0,
"Null": "",
"Index_type": "BTREE",
"Comment": "",
"Index_comment": "",
"Visible": "YES"
}, {
"Table": "ca_product",
"Non_unique": 0,
"Key_name": "UQ_CODE",
"Seq_in_index": 1,
"Column_name": "code",
"Collation": "A",
"Cardinality": 0,
"Null": "YES",
"Index_type": "BTREE",
"Comment": "",
"Index_comment": "",
"Visible": "YES"
}, {
"Table": "ca_product",
"Non_unique": 1,
"Key_name": "ft_fulltext_body",
"Seq_in_index": 1,
"Column_name": "fullTextBody",
"Cardinality": 0,
"Null": "YES",
"Index_type": "FULLTEXT",
"Comment": "",
"Index_comment": "",
"Visible": "YES"
}]
}
核心代码
前端根据API返回的schema信息，转换成crudapi的元数据格式，并显示在UI上, 主要代码在文件metadata/table/new.vue中，通过addRowFromMetadata方法添加字段，addIndexFromMetadata添加联合索引。
addRowFromMetadata(id, t, singleIndexColumns) {
const columns = this.table.columns;
const index = columns.length + 1;
const type = t.Type.toUpperCase();
const name = t.Field;
let length = null;
let precision = null;
let scale = null;
let typeArr = type.split("(");
if (typeArr.length > 1) {
const lengthOrprecisionScale = typeArr[1].split(")")[0];
if (lengthOrprecisionScale.indexOf(",") > 0) {
precision = lengthOrprecisionScale.split(",")[0];
scale = lengthOrprecisionScale.split(",")[1];
} else {
length = lengthOrprecisionScale;
}
}
let indexType = null;
let indexStorage = null;
let indexName = null;
let indexColumn = singleIndexColumns[name];
if (indexColumn) {
if (indexColumn.Key_name === "PRIMARY") {
indexType = "PRIMARY";
} else if (indexColumn.Index_type === "FULLTEXT") {
indexType = "FULLTEXT";
indexName = indexColumn.Key_name;
} else if (indexColumn.Non_unique === 0) {
indexType = "UNIQUE";
indexName = indexColumn.Key_name;
indexStorage = indexColumn.Index_type;
} else {
indexType = "INDEX";
indexName = indexColumn.Key_name;
indexStorage = indexColumn.Index_type;
}
}
const comment = t.Comment ? t.Comment : name;
const newRow = {
id: id,
autoIncrement: (t.Extra === "auto_increment"),
displayOrder: columns.length,
insertable: true,
nullable: (t.Null === "YES"),
queryable: true,
displayable: false,
unsigned: type.indexOf("UNSIGNED") >= 0,
updatable: true,
dataType : typeArr[0].replace("UNSIGNED", "").trim(),
indexType: indexType,
indexStorage: indexStorage,
indexName: indexName,
name: name,
caption: comment,
description: comment,
length: length,
precision: precision,
scale: scale,
systemable: false
};
this.table.columns = [ ...columns.slice(0, index), newRow, ...columns.slice(index) ];
},
addIndexFromMetadata(union) {
let baseId = (new Date()).valueOf();
let newIndexs = [];
const tableColumns = this.table.columns;
console.dir(tableColumns);
for (let key in union) {
const unionLines = union[key];
const newIndexLines = [];
unionLines.forEach((item) => {
const columnName = item.Column_name;
const columnId = tableColumns.find(t => t.name === columnName).id;
newIndexLines.push({
column: {
id: columnId,
name: columnName
}
});
});
const unionLineFirst = unionLines[0];
let indexType = null;
let indexStorage = null;
if (unionLineFirst.Key_name === "PRIMARY") {
indexType = "PRIMARY";
} else if (unionLineFirst.Non_unique === 0) {
indexType = "UNIQUE";
indexStorage = unionLineFirst.Index_type;
} else {
indexType = "INDEX";
indexStorage = unionLineFirst.Index_type;
}
const indexComment = unionLineFirst.Index_comment ? unionLineFirst.Index_comment: unionLineFirst.Key_name;
const newIndex = {
id: baseId++,
isNewRow: true,
caption: indexComment,
description: indexComment,
indexStorage: indexStorage,
indexType: indexType,
name: unionLineFirst.Key_name,
indexLines: newIndexLines
}
newIndexs.push(newIndex);
}
this.table.indexs = newIndexs;
if (this.table.indexs) {
this.indexCount = this.table.indexs.length;
} else {
this.indexCount = 0;
}
}
例子
以ca_product为例子, 点击”加载元数据之后”，表字段和索引都正确地显示了。保存成功之后，已经存在的物理表ca_product会自动被元数据管理起来，后续可以通过crudapi后台继续编辑，通过数据库逆向功能，零代码实现了物理表ca_product的CRUD增删改查功能。
小结
本文主要介绍了数据库逆向功能，在数据库表单已经存在的基础上，通过数据库逆向功能，快速生成元数据，不需要一行代码，我们就可以得到已有数据库的基本crud功能，包括API和UI。类似于phpmyadmin等数据库UI管理系统，但是比数据库UI管理系统更灵活，更友好。目前数据库逆向一次只支持一个表，如果同时存在很多物理表，就需要批量操作了。后续会继续优化，实现批量数据库逆向功能。
demo演示
官网地址：https://crudapi.cn
测试地址：https://demo.crudapi.cn/crudapi/login
附源码地址
GitHub地址
https://github.com/crudapi/crudapi-admin-web
Gitee地址
https://gitee.com/crudapi/crudapi-admin-web
由于网络原因，GitHub可能速度慢，改成访问Gitee即可，代码同步更新。
Original: https://www.cnblogs.com/crudapi/p/15107120.html
Author: crudapi
Title: 「免费开源」基于Vue和Quasar的前端SPA项目crudapi后台管理系统实战之数据库逆向（十二）
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/574297/
转载文章受原作者版权保护。转载请注明原作者出处！