欢迎光临
我的个人博客网站

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


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

回顾

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

简介

数据库逆向就是通过读取数据库物理表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管理系统更灵活,更友好。目前数据库逆向一次只支持一个表,如果同时存在很多物理表,就需要批量操作了。后续会继续优化,实现批量数据库逆向功能。

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

赞(0) 打赏
未经允许不得转载:张拓的天空 » 「免费开源」基于Vue和Quasar的前端SPA项目crudapi后台管理系统实战之数据库逆向(十二)
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

专业的IT技术经验分享 更专业 更方便

联系我们本站主机

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏