You can use the schema design feature that is provided by Data Management (DMS) to change the schema of a table in a convenient and standardized manner.

Background information

You can use the schema design feature to create tables or change schemas when you develop a project, process a new requirement, or optimize the schema of a table. For example, you can use this feature to create or delete columns or indexes, modify column attributes, or modify index compositions.

This feature is suitable when multiple development environments are involved. For example, you can use this feature to verify a new schema script in a development environment and then publish the script to a production environment with only a few clicks. This feature automatically checks whether the SQL statements for schema changes conform to development standards during the development process. For example, this feature can be used to ensure that the columns that are used in a production environment are not deleted. This feature allows multiple users to perform collaborative development in the DMS console.

This feature allows you to change the schema of a table as required. The feature ensures that related schemas that are designed based on various development standards or in different environments are consistent and conform to the standards. This way, the success rate is improved when the new schema is published to a production environment.

Procedure

  1. Log on to the DMS console V5.0.
    Note To switch to the previous version of the DMS console, click the 5租户头像 icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
  2. In the top navigation bar, click Database Development. In the left-side navigation pane, choose Schema Change > Schema Design.
    Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose Schemas > Schema Design.
  3. On the Schema DesignTickets page, click Schema Design in the upper-right corner.
    Note On the Schema DesignTickets page, you can view detailed information about historical tickets for schema design.
    Schema Design button
  4. Set the parameters that are described in the following table and click Submit.
    Parameter Description
    Project Name The name of the project, which helps identify the project. In this example, the name is POC Demonstration.
    Project description The purpose or objective of the project, which accelerates the approval process.
    Change Base Database The database on which you want to perform schema design operations. In this example, select the poc_dev database. This database adopts the default security rules. When you create a schema design ticket, DMS creates a schema design process based on the R&D process of the default security rules. The schema design process includes the design and publishing of a schema.
    Security Rules The security rules that you want to apply. DMS automatically selects security rules after you specify a database.
    Change Stakeholder The stakeholders of the schema design. All the specified stakeholders can view the ticket details and assist developers in accelerating the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.
  5. Create physical tables:
    1. Click Import Table Creation Statements.
    2. In the Import Table Creation Statements dialog box, enter the following SQL statements and click OK.

      These SQL statements are used to create the big_table and data_modify tables. After you click OK, two Create Physical Table tabs appear.

      CREATE TABLE `big_table` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
        `name` varchar(64) NOT NULL COMMENT 'Name',
        `long_text_a` varchar(1024) DEFAULT NULL COMMENT 'Text A',
        `long_text_b` varchar(1024) DEFAULT NULL COMMENT 'Text B',
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Schema change of a large table';
      
      CREATE TABLE `data_modify` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
        `name` varchar(256) NOT NULL COMMENT 'Name',
        `phone` varchar(32) DEFAULT NULL COMMENT 'Phone number',
        `sex` varchar(32) DEFAULT NULL COMMENT 'Gender',
        `email` varchar(256) DEFAULT NULL COMMENT 'Email address',
        `desc` varchar(1024) DEFAULT NULL COMMENT 'Remarks',
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT ;
    3. On the Create Physical Table tab of the data_modify table, confirm the schema information.
      • Basic Info
      • Column Info
      • Index Info
    4. Click Save. The Precheck Result message appears.

      In the data_modify table, the Remarks and Character set parameters are left empty, and the database keyword desc is used as the column name.

      Note DMS verifies the preceding SQL statements for table creation based on the development standards that are configured in the default security rules. In this example, DMS reports two errors and one warning. DMS supports nearly 40 development standards to help you create tables and modify columns and indexes. You can configure the development standards in security rules as required.
    5. Change the schema of the data_modify table as prompted and click Save.
      • On the Basic Info tab, set the Remarks parameter to Test and the Character set parameter to utf8.
      • On the Column Info tab, rename the desc column user_desc.
    6. In the Precheck Result message, click Confirm Changes and Submit to Save.

      Then, you are navigated to the Project Homepage tab. On this tab, you can view that the data_modify table is created but not published to a development database. On the Project Homepage tab, you can modify, preview, or delete the schema that you created.

    7. Click the Create Physical Table tab of the big_table table and confirm the schema of the big_table table.
    8. After you confirm the schema, click Save.
    9. In the Precheck Result message, click Confirm Changes and Submit to Save.

      The big_table and data_modify tables are created.

  6. Add stakeholders:
    You can add other developers as stakeholders to review the new schemas and accelerate the ticket approval process. This facilitates collaborative development.
    1. On the Project Homepage tab, click the Change Stakeholder icon icon.
    2. In the Modify Change-related Personnel dialog box, click Add and select one or more stakeholders.
    3. Click Update Stakeholder.
  7. After the schemas are reviewed, publish them to the development database:
    1. On the Project Homepage tab, click Perform Changes to Base Database.
    2. In the Perform Changes to Base Database panel, set the Execution Strategy parameter to Execute Now. After you confirm that the SQL statements are valid, click OK.
      Note Valid values of the Execution Strategy parameter:
      • Execute Now: DMS immediately changes the schemas after the ticket is approved.
      • Schedule: After the ticket is approved, DMS changes the schemas at the specified point in time.
    3. After a DMS administrator approves the ticket, DMS publishes the new schemas to the development database.
      Note
      • Based on the default security rules, you must submit tickets for schema changes. You can also use custom security rules. For example, you can specify that no approval is required for schema changes in development databases. You can also specify the users who approve schema change tickets in production databases. For more information about how to approve tickets, see Approve a schema design ticket.
      • In the Approval step, you can click View Approval Details to view the approval progress.
    4. View the table details of the poc_dev database on the SQLConsole tab and verify the schemas.
      Note After the schemas are verified, you can publish the schemas to a production database.
    5. On the Project Homepage tab, click Go to Next Node.
    6. In the Go to Next Node message, click Go to Next Node.
  8. In the Release the schema step, perform the following operations to apply the new schemas to a production database:
    1. On the Project Homepage tab, click Perform Changes to Target Database.
    2. In the Perform Changes to Target Database panel, select the poc_prod database, set the Execution Strategy parameter to Execute Now, and then click OK.
      Note You can specify more than one production database.
    3. After a DMS administrator approves the ticket, DMS publishes the new schemas to the poc_prod database.
      Note For more information about how to approve tickets, see Approve a schema design ticket.
    4. On the Project Homepage tab, click Go to Next Node.
    5. In the Go to Next Node message, click Go to Next Node.

      The ticket is closed. You can click each step to view the change and publishing records.