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 add 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 the R&D specifications 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 R&D specifications or in different environments are consistent and conform to the design specifications. This way, the success rate is improved when the new schema is published to a production environment.

Example

  1. Log on to the DMS console as a regular user.
  2. In the top navigation bar, choose Schemas > Schema Design.
    Schema Design
  3. On the Schema Design tab, click Schema Design in the upper-right corner.
    Note On the Schema Design tab, you can view detailed information about historical tickets for schema design.
    Schema Design button
  4. In the dialog box that appears, set the parameters as required and click Submit. The following table describes the parameters.
    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 based on the selected database.
    Change Stakeholder The stakeholders of the schema design. All 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 dialog boxes 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. In the Create Physical Table dialog box of the data_modify table, confirm the schema information.
    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 R&D specifications that are configured in the default security rules. In this example, DMS reports two errors and one warning. DMS supports nearly 40 R&D specifications to create tables and modify columns and indexes. You can configure the R&D specifications in security rules as required.
    5. Change the schema of the data_modify table as prompted and click Save.
    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 edit, preview, or delete the schema that you created.

    7. Click Create Physical 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 new stakeholders:
    You can add other developers as stakeholders to review the new schema and accelerate the ticket approval process. This facilitates collaborative development.
    1. On the Project Homepage tab, click the Change Stakeholder 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 schema is reviewed, publish the schema to the development database:
    1. On the Project Homepage tab, click Perform Changes to Base Database.
    2. In the Perform Changes to Base Database dialog box, set the Execution Strategy parameter to Execute Now. After you confirm that the SQL statements are valid, click OK.
      Note Valid values of Execution Strategy:
      • Execute Now: DMS immediately changes the schema after the ticket is approved.
      • Schedule: After the ticket is approved, DMS changes the schema at the specified point in time.
    3. After a DMS administrator approves the ticket, DMS publishes the new schema 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 schema.
      Note After the schema is verified, you can publish the schema 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 schema to a production database:
    1. On the Project Homepage tab, click Perform Changes to Target Database.
    2. In the Perform Changes to Target Database dialog box, 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 schema 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.