基于Vue和Quasar的前端SPA项目实战之数据库逆向(十二)

2022-04-26 00:00:00 功能 数据 数据库 表单 逆向

基于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,) DEFAULT NULL COMMENT '单价',  `weight` decimal(10,) DEFAULT NULL COMMENT '重量',  `length` decimal(10,) DEFAULT NULL COMMENT '长',  `width` decimal(10,) DEFAULT NULL COMMENT '宽',  `high` decimal(10,) 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_NAMESHOW FULL COLUMNS FROM TABLE_NAMESHOW INDEX FROM TABLE_NAME

 表基本信息

 字段信息

 索引信息

API JSON

通过API /api/metadata/tables/metadata/ca_product查询ca_product的schema信息, 格式如下:

{  "Name": "ca_product",  "Engine": "InnoDB",  "Version": 10,  "Row_format": "Dynamic",  "Rows": ,  "Avg_row_length": ,  "Data_length": 16384,  "Max_data_length": ,  "Index_length": 32768,  "Data_free": ,  "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": ,    "Key_name": "PRIMARY",    "Seq_in_index": 1,    "Column_name": "id",    "Collation": "A",    "Cardinality": ,    "Null": "",    "Index_type": "BTREE",    "Comment": "",    "Index_comment": "",    "Visible": "YES"  }, {    "Table": "ca_product",    "Non_unique": ,    "Key_name": "UQ_CODE",    "Seq_in_index": 1,    "Column_name": "code",    "Collation": "A",    "Cardinality": ,    "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": ,    "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(")")[]; if (lengthOrprecisionScale.indexOf(",") > ) { precision = lengthOrprecisionScale.split(",")[]; 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 === ) { 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") >= , updatable: true, dataType : typeArr[].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(, 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[]; let indexType = null; let indexStorage = null; if (unionLineFirst.Key_name === "PRIMARY") { indexType = "PRIMARY"; } else if (unionLineFirst.Non_unique === ) { 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 = ; }}

例子

以ca_product为例子, 点击“加载元数据之后”,表字段和索引都正确的显示了。保存成功之后,已经存在的物理表ca_product会自动被元数据管理起来,后续可以通过crudapi后台继续编辑,通过数据库逆向功能,零代码实现了物理表ca_product的CRUD增删改查功能。

小结

本文主要介绍了数据库逆向功能,在数据库表单已经存在的基础上,通过数据库逆向功能,快速生成元数据,不需要一行代码,我们就可以得到已有数据库的基本crud功能,包括API和UI。类似于phpmyadmin等数据库UI管理系统,但是比数据库UI管理系统更灵活,更友好。目前数据库逆向一次只支持一个表,如果同时存在很多物理表,就需要批量操作了。后续会继续优化,实现批量数据库逆向功能。


来自:https://mp.weixin.qq.com/s/dOeLCnIAq4YHTm5-8OrZJQ


相关文章