发表评论
367 次浏览

A+

所属分类：Web前端

摘要

通过之前文章基于Vue和Quasar的前端SPA项目实战之动态表单（五）的介绍，实现了动态表单功能。如果是全新的项目，通过配置元数据并且创建物理表，从而自动实现业务数据的CRUD增删改查。但是如果数据库表已经存在的情况下，如何通过配置表单元数据进行管理呢？这时候数据库逆向功能就很有必要了。

便宜好用的国外VPS推荐

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

回顾

简介

数据库逆向就是通过读取数据库物理表schema信息，然后生成表单元数据，可以看成“dbfirst”模式，即先有数据库表，然后根据表生成元数据，逆向表单后续操作和普通动态表单类似。

UI界面

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

输入物理表名称，启用“数据库逆向”功能，然后点击“加载元数据”，然后会自动填充表单字段相关元数据信息。

数据表准备

以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;

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

查询schema

mysql数据库通过如下SQL语句可以查询表单、字段、索引等信息

SHOW TABLE STATUS LIKE TABLE_NAME SHOW FULL COLUMNS FROM TABLE_NAME SHOW INDEX FROM TABLE_NAME

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

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

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

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;   } }

例子

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

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

小结

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