"Free and Open Source" Front-end SPA project crudapi back-end management system based on Vue and Quasar database reverse engineering (12)

Database reverse engineering of front-end SPA project based on Vue and Quasar (12)

review

Through the introduction of the previous article Vue and Quasar-based front-end SPA project actual combat dynamic form (5) , the dynamic form function is realized. If it is a brand new project, by configuring metadata and creating physical tables, the CRUD addition, deletion, modification, and checking of business data can be automatically realized. But if the database table already exists, how to manage it by configuring the table unit data? At this time, the database reverse function is necessary.

Introduction

Database reverse is to read the database physical table schema information, and then generate table unit data, which can be regarded as a "dbfirst" mode, that is, there is a database table first, and then metadata is generated based on the table. The subsequent operations of the reverse form are similar to ordinary dynamic forms.

UI interface

Enter the name of the physical table, enable the "Reverse Database" function, and then click "Load Metadata", then the relevant metadata information of the form fields will be filled in automatically.

Data sheet preparation

Take the ca_product product as an example, create a table through phpmyadmin

Create product table

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;

Query schema

The mysql database can query information such as forms, fields, indexes, etc. through the following SQL statements

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

Table basic information

Field information

Index information

API JSON

Via API https://demo.crudapi.cn/api/metadata/tables/metadata/ca_product
Query the schema information of ca_product, the format is as follows:

{
  "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"
  }]
}

Core code

According to the schema information returned by the API, the front end converts it into the metadata format of crudapi and displays it on the UI. The main code is in the file metadata/table/new.vue. Fields are added through the addRowFromMetadata method, and the joint index is added by 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;
  }
}

example

Take ca_product as an example, click "After loading metadata", the table fields and indexes are displayed correctly. After the save is successful, the existing physical table ca_product will be automatically managed by metadata, and you can continue to edit it through the crudapi background. Through the database reverse function, the zero code realizes the CRUD addition, deletion, modification, and query function of the physical table ca_product.

summary

This article mainly introduces the database reverse function. On the basis of the existing database form, the database reverse function can quickly generate metadata. Without a line of code, we can get the basic crud functions of the existing database, including API and UI. Similar to database UI management systems such as phpmyadmin, but more flexible and friendly than database UI management systems. The current database reverse engineering only supports one table at a time. If there are many physical tables at the same time, batch operations are required. We will continue to optimize in the future to realize the batch database reverse function.

demo

Official website address: https://crudapi.cn
Test address: https://demo.crudapi.cn/crudapi/login

Attached source code address

GitHub address

https://github.com/crudapi/crudapi-admin-web

Gitee address

https://gitee.com/crudapi/crudapi-admin-web

Due to network reasons, GitHub may be slow, just change to visit Gitee, and the code will be updated synchronously.