乐趣区

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

jiezi

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

查问 schema

mysql 数据库通过如下 SQL 语句能够查问表单、字段、索引等信息 

SHOW TABLE STATUS LIKE TABLE_NAME
SHOW FULL COLUMNS FROM TABLE_NAME
SHOW 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 即可，代码同步更新。

退出移动版