Data Management:Design schemas

Scenarios

When you develop projects, process new business requirements, or optimize business operations, you may need to create tables and change schemas. For example, you may need to create or delete fields or indexes, modify field attributes, or adjust index composition. In these scenarios, you can use the schema design feature of DMS.

Feature description

• Automatically checks whether the SQL statements for schema changes conform to specific R&D standards during the custom R&D process. For example, you can use this feature to ensure that the fields that are used in the production environment are not deleted.

• Allows you to publish a verified schema change script from one environment to another one with a few clicks. This feature is applicable when multiple environments are involved and ensures schema consistency among these environments. This way, the success rate is improved when the new schema is published to the production environment.

• Allows multiple users to collaboratively change a schema in the DMS console.

Usage notes

• You cannot submit a schema design ticket to delete existing tables. You can delete the tables that are created after you create the ticket.

• After changes are applied to a database, the metadata is also synchronized.

  Note

  You can execute the show create table table_name or desc table_name statement on the SQLConsole tab to check whether the changes take effect.

Procedure

1. Log on to the DMS console V5.0.

2. In the top navigation bar, choose Database Development > Schema Change > Schema Design.

   Note

   If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner of the DMS console and choose All functions > Database Development > Schema Change > Schema Design.

3. On the Schema Design tab, click Schema Design in the upper-right corner.

4. Configure the parameters for creating a schema design ticket and click Submit. The following table describes the parameters.

   Parameter	Required	Description
   Change Base Database	Yes	The database on which you want to perform schema design operations. In this example, a database in Security Collaboration mode is selected. The type of the environment to which the database belongs must conform to the R&D standards that are configured in the security rules of the database. For more information about the R&D process, see Schema design.
   Change Stakeholder	Yes	The stakeholders of the schema design. All the specified stakeholders can view the ticket details and assist developers in accelerating the development and approval processes. Note Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.

5. Create a physical table.

   • Use the GUI

     1. Click Create Physical Table.

     2. Enter the basic information about the table and modify the fields and indexes of the table.

     3. Click Save.

   • Execute SQL statements

     1. Click Import SQL Statements.

     2. In the dialog box that appears, enter the CREATE TABLE or ALTER TABLE statements as required and click OK.

        For example, you can execute the following statement to create a table named data_modify:

        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',
          `remarks` varchar(1024) DEFAULT NULL COMMENT 'Remarks',
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Personal information';

     3. On the Create Physical Table: data_modify tab, confirm the table information and schema and click Save.

   Note

   In addition to the preceding methods, you can create a physical table on the SQLConsole tab. For more information, see the "Create a table" section of the Manage a database on the SQLConsole tab topic.

6. In the Precheck Result message, view the precheck result.

   • If the precheck is passed, click Confirm Changes and Submit to Save.

   • If only a warning is displayed, click Ignore the warning and continue to save.. Alternatively, click Close, modify the table schema based on the R&D standards, and then click Save.

   • If an error is reported, check the error cause, click Close, modify the table schema based on the R&D standards, and then click Save.

   Note

   • DMS verifies the preceding SQL statements for table creation or modification based on the R&D standards that are configured in the default security rules, and then reports errors and warnings, if any.

   • DMS provides nearly 40 R&D standards to help you create tables and modify fields and indexes. You can configure the R&D standards in security rules as required.

   After the precheck is passed, you are navigated to the Project Homepage tab. On the Tables Changed in Project tab, you can view the table that you modified or created and modify or delete other tables as required.

7. Apply the schema changes to the base database.

   1. Click Perform Changes to Base Database.

   2. In the Perform Changes to Base Database panel, view the base database, execution strategy, and scheme change script, and click Submit.

   After the schema change operation is approved, the system automatically applies the schema changes to the base database.

   Note

   • You can check whether the schema changes meet your expectations on the SQLConsole tab of the base database.

   • 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 can approve schema change tickets in production databases.

8. Click Go to Next Node. In the message that appears, click Go to Next Node.

9. Apply the schema changes to the 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 destination database, configure the Execution Strategy parameter, and then click Submit.

      Note

      You can also click Add Multiple Databases to execute the script in multiple production databases.

      After the schema change operation is approved, the system automatically applies the schema changes to the production database. You can check whether the schema changes meet your expectations on the SQLConsole tab of the production database.

   3. Click Go to Next Node. In the message that appears, click Go to Next Node.

      After the ticket is closed, you can click each step to view the change and publishing records.