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

盛夏的果实

基于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;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RUujeiyn-1628213709857)(https://help.crudapi.cn/img/crudapi-admin-web/dbfirst/product.png)]
查询schema
mysql数据库通过如下SQL语句可以查询表单、字段、索引等信息

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

表基本信息

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M5maOeWG-1628213709860)(https://help.crudapi.cn/img/crudapi-admin-web/dbfirst/column.png)]
字段信息

索引信息

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即可，代码同步更新。
）

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

相关帖子

数据库系统设计：分区

Linux下常见的开源数据库，你知道几个？

golang实现文件上传并转存数据库功能详解

MYSQL最朴素的监控方式

MySql主从同步介绍

MySQL数据库如何给表设置约束详解

MySQL由浅入深掌握连接查询

SQLite 基本命令使用方式

uniapp & vue 父子、子父、兄弟组件之间的传值

vue图片剪裁上传组件

盛夏的果实 LV4