十二「免费开源」基于Vue和Quasar的前端SPA项目crudapi后台管理系统实战之数据库逆向

文章插图
本文主要介绍了数据库逆向功能，在数据库表单已经存在的基础上，通过数据库逆向功能，快速生成元数据，不需要一行代码，我们就可以得到已有数据库的基本crud功能，包括API和UI 。类似于phpmyadmin等数据库UI管理系统，但是比数据库UI管理系统更灵活，更友好。基于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;

文章插图
查询schemamysql数据库通过如下SQL语句可以查询表单、字段、索引等信息
SHOW TABLE STATUS LIKE TABLE_NAMESHOW FULL COLUMNS FROM TABLE_NAMESHOW INDEX FROM TABLE_NAME

文章插图

表基本信息

文章插图

字段信息

文章插图

索引信息
API JSON通过APIhttps://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
【十二「免费开源」基于Vue和Quasar的前端SPA项目crudapi后台管理系统实战之数据库逆向】由于网络原因，GitHub可能速度慢，改成访问Gitee即可，代码同步更新。

十二 「免费开源」基于Vue和Quasar的前端SPA项目crudapi后台管理系统实战之数据库逆向

十二「免费开源」基于Vue和Quasar的前端SPA项目crudapi后台管理系统实战之数据库逆向