seq搜索引擎优化至少包括那几步?(基于Vue和Quasar的前端SPA项目实战之数据库逆向（十二）)

　　seq搜索引擎优化至少包括那几步?(基于Vue和Quasar的前端SPA项目实战之数据库逆向（十二）)

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

　　通过引入之前的文章“开源免费”动态表单设计器（五）基于Vue和Quasar），实现动态表单功能。如果是一个全新的项目，通过配置元数据，创建物理表，这样就可以自动实现业务数据的CRUD增删改查。但是如果数据库表已经存在，如何通过配置表单元数据来管理呢？这时候就需要数据库逆向功能了。

　　介绍

　　数据库逆向是读取数据库物理表schema信息，然后生成表单元数据，可以认为是“dbfirst”模式，即先有数据库表，然后根据表生成元数据。逆向形式的后续操作与普通动态形式类似。

　　用户界面界面

　　

　　数据库反向

　　输入物理表名，启用“数据库反向”功能，然后点击“加载元数据”，表单字段相关的元数据信息就会自动填充。

　　数据表准备

　　以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;

　　

　　phpmyadmin

　　查询模式

　　MySQL数据库可以通过以下SQL语句查询表单、字段、索引等信息

　　SHOW TABLE STATUS LIKE TABLE_NAME

SHOW FULL COLUMNS FROM TABLE_NAME

SHOW INDEX FROM TABLE_NAME

　　

　　表基本信息

　　表基本信息

　　

　　字段信息

　　字段信息

　　

　　索引信息

　　API JSON

　　通过API查询ca_product的schema信息：/api/metadata/tables/metadata/ca_product，格式如下：

　　{

"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为例，点击“加载元数据”后，表格字段和索引显示正确。保存成功后，现有物理表 ca_product 将由元数据自动管理，您可以通过 crudapi 后台继续对其进行编辑。通过数据库逆向功能，零码实现物理表ca_product的CRUD增删改查功能。

　　概括

　　本文主要介绍数据库反向功能。在现有数据库形式的基础上，通过数据库反向功能，可以快速生成元数据。无需一行代码，我们就可以获取现有数据库的基本 crud 功能，包括 API 和 UI。类似于phpmyadmin等数据库UI管理系统，但比数据库UI管理系统更加灵活友好。目前，数据库一次只反转一个表。如果同时有很多物理表，需要进行批量操作。后续优化将继续实现批量数据库的逆向功能。

　　温馨提示：点击原文链接逆向基于Vue和Quasar的前端SPA项目的数据库（十二）|crudapi可以去官网查看源码！