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

关注

发布于: 3 小时前

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

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

回顾

通过之前文章https://xie.infoq.cn/article/55bd372c8a9e4d7400945398d的介绍，实现了动态表单功能。如果是全新的项目，通过配置元数据并且创建物理表，从而自动实现业务数据的 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_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

由于网络原因，GitHub 可能速度慢，改成访问 Gitee 即可，代码同步更新。

发布于: 3 小时前阅读数: 2

原文链接:【http://xie.infoq.cn/article/08a24e62ce8440202bb04c993】。

crudapi

关注

crudapi是crud+api组合，表示增删改查接口 2019.06.19 加入

使用crudapi可以告别枯燥无味的增删改查代码，让您更加专注业务，节约大量成本，从而提高工作效率。crudapi的目标是让处理数据变得更简单!官网：https://crudapi.cn 演示：https://demo.crudapi.cn/crudapi/login

发布

暂无评论

创作场景

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

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

回顾

简介

UI 界面

数据表准备

创建产品表

查询 schema

API JSON

核心代码

例子

小结

demo 演示

附源码地址

GitHub 地址

Gitee 地址

crudapi

评论