基于Vue和Quasar前端SPA项目实战数据库逆向（十二）回顾之前的文章基于Vue和Quasar的前端SPA项目实战动态表单介绍（五），实现动态表单功能。如果是全新的项目，通过配置元数据，创建物理表，可以自动实现业务数据的CRUD增删改查。但是如果数据库表已经存在，如何通过配置表单元数据来管理呢？这时候数据库的逆向功能就很有必要了。介绍数据库逆向是读取数据库物理表的schema信息，然后生成表单元数据，可以看成是“dbfirst”模式，即先有数据库表，再根据生成元数据桌子。反向表单的后续操作与普通动态表单类似。在UI界面输入物理表名，启用“数据库倒序”功能，点击“加载元数据”，表单域中的相关元数据信息将被自动填充。数据表准备以ca_product产品为例，通过phpmyadmin建表创建产品表CREATETABLE`ca_product`(`id`bigintUNSIGNEDNOTNULLCOMMENT'number',`name`varchar(200)COLLATEutf8mb4_unicode_ciNOTNULLCOMMENT'name',`fullTextBody`textCOLLATEutf8mb4_unicode_ciCOMMENT'全文索引',`createdDate`datetimeNOTNULLCOMMENT'创建时间',`lastModifiedDate`datetimeDEFAULTNULLCOMMENT'修改时间',`code`varchar(200)COLLATEutf8mb4_unicode_ciDEFAULTNULLencoding'',`brand`varchar(200)COLLATEutf8mb4_unicode_ciDEFAULTNULLCOMMENT'brand',`price`decimal(10,0)DEFAULTNULLCOMMENT'unitprice',`weight`decimal(10,0)DEFAULTNULLCOMMENT'weight',`length`decimal(10,0)DEFAULTNULLCOMMENT'long',`width`decimal(10,0)DEFAULTNULLCOMMENT'width',`high`decimal(10,0)DEFAULTNULLCOMMENT'height',`ats`bigintDEFAULTNULLCOMMENT'stockquantity')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_unicode_ciCOMMENT='product';更改表`ca_product`添加主键（`id`），添加唯一键`UQ_CODE`（`code`）使用B树；ALTERTABLE`ca_product`ADDFULLTEXTKEY`ft_fulltext_body`(`fullTextBody`);ALTERTABLE`ca_product`MODIFY`id`bigintUNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'Number',AUTO_INCREMENT=1;SQL语句可以查询表格、字段、索引等信息SHOWTABLESTATUSLIKETABLE_NAMESHOWFULLCOLUMNSFROMTABLE_NAMESHOWINDEXFROMTABLE_NAME表基本信息字段信息索引信息APIJSONviaAPIhttps://demo.crudapi.cn/api/metadata/tables/metadata/ca_product查询ca_product的schema信息，格式如下：{"Name":"ca_product","Engine":"InnoDB","Version":10,"Row_format":"动态”，“行”：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,"Collat??ion":"utf8mb4_unicode_ci","Create_options":"","Comment":"Product","columns":[{"Field":"id","Type":"bigint"unsigned","Null":"NO","Key":"PRI","Extra":"auto_increment","Privileges":"select,insert,update,references","Comment":"编号"},{"Field":"name","Type":"varchar(200)","Collat??ion":"utf8mb4_unicode_ci","Null":"NO","Key":"","Extra":"","Privileges":"select,insert,update,references","Comment":"名称"},{"Field":"fullTextBody","Type":"text","Collat??ion":"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)","Collat??ion":"utf8mb4_unicode_ci","Null":"YES","Key":"UNI","Extra":"","Privileges":"select,insert,update,references","Comment":"编码"},{"Field":"brand","Type":"varchar(200)","Collat??ion":"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“：”，“权限”：“选择,insert,update,references","Comment":"宽"},{"Field":"high","Type":"decimal(10,0)","Null":"YES","Key":"","Extra":"","Privileges":"select,insert,update,references","Comment":"high"},{"Field":"ats","Type":"bigint","Null":"YES","Key":"","Extra":"","Privileges":"select,insert,update,references","Comment":"库存个数"}],"索引”：[{“表”：“ca_product”，“Non_unique”：0，“Key_name”：“PRIMARY”，“Seq_in_index”：1，“Column_name”：“id”，“排序规则”：“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","Collat??ion":"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){常量列=this.table.columns;constindex=columns.length+1;consttype=t.Type.toUpperCase();constname=t.Field;让长度=空；让精度=空；让比例=空；让typeArr=type.split("(");如果(typeArr.length>1){constlengthOrprecisionScale=typeArr[1].split(")")[0];如果(lengthOrprecisionScale.indexOf(",")>0){precision=lengthOrprecisionScale.split(",")[0];scale=lengthOrprecisionScale.split(",")[1];}else{length=lengthOrprecisionScale;}}让indexType=null；让indexStorage=null；让indexName=null;让indexColumn=singleIndexColumns[名称];if(indexColumn){if(indexColumn.Key_name==="PRIMARY"){indexType="PRIMARY";}elseif(indexColumn.Index_type==="FULLTEXT"){indexType="FULLTEXT";indexName=indexColumn.Key_name;}elseif(indexColumn.Non_unique===0){indexType="唯一";indexName=indexColumn.Key_name;indexStorage=indexColumn.Index_type;}else{indexType="INDEX";indexName=indexColumn.Key_name;indexStorage=indexColumn.Index_type;}}constcomment=t.Comment？t.评论：姓名；constnewRow={id:id,autoIncrement:(t.Extra==="auto_increment"),displayOrder:columns.length,insertable:true,nullable:(t.Null==="YES"),queryable:true,可显示：假，无符号：type.indexOf("UNSIGNED")>=0，可更新：true，数据类型：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){让baseId=(newDate()).valueOf();让newIndexs=[];consttableColumns=this.table.columns;console.dir(tableColumns);for(letkeyinunion){constunionLines=union[key];constnewIndexLines=[];unionLines.forEach((item)=>{constcolumnName=item.Column_name;constcolumnId=tableColumns.find(t=>t.name===columnName).id;newIndexLines.push({column:{id:columnId,名称：列名}});});constunionLineFirst=unionLines[0];让indexType=null；让indexStorage=null；if(unionLineFirst.Key_name==="PRIMARY"){indexType="PRIMARY";}elseif(unionLineFirst.Non_unique===0){indexType="UNIQUE";indexStorage=unionLineFirst.Index_type;}else{indexType="INDEX";indexStorage=unionLineFirst.Index_type;}constindexComment=unionLineFirst.Index_comment？unionLineFirst.Index_comment:unionLineFirst.Key_name;constnewIndex={id:baseId++,isNewRow:true,caption:indexComment,description:indexComment,indexStorage:indexStorage,indexType:indexType,name:unionLineFirst.Key_name,indexLines:newIndexLines}newIndexs.push(;}this.table.indexs=复制代码newIndexs;if(this.table.indexs){this.indexCount=this.table.indexs.length;}else{this.indexCount=0;}}例子以ca_product为例，点击“Afterloadingthemetadata”，表字段和索引正确显示，保存成功后，现有物理表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-webGitee地址https://gitee.com/crudapi/crudapi-admin-web由于网络原因，GitHub可能会比较慢，改访问Gitee即可，代码会同步更新。
