Data Management:Publish table schemas

Preparations

1. Create three ApsaraDB RDS for MySQL instances named Schema_Design-dev, Schema_Design-test, and Schema_Design-product. For more information, see Create an ApsaraDB RDS for MySQL instance.

2. Register the three instances with DMS. When you register the instances, set the Control Mode parameter to Security Collaboration and set the Environment type parameter to Dev, Test, or Product based on the instance name. For more information about how to register an ApsaraDB instance, see Register an Alibaba Cloud database instance. For more information about the environment type of a database instance, see Change the environment type of an instance.

3. Create a database in each database instance: dev_database, test_database, and product_database.

Step 1: Configure R&D rules

By default, the R&D process in DMS consists of schema design and schema publishing to the production environment. This section describes how to create security rules and configure the R&D process of schema design, testing, and schema publishing to the production environment.

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

2. In the top navigation bar, click Security and Specifications. In the left-side navigation pane, choose Permission Center > Permission template.

   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 > Security and Specifications > Permission Center > Permission template.

3. In the left-side navigation pane, click Security Rules. On this page, click Create Rule Set. In the Create Rule Set dialog box, configure the required parameters to create a new rule set.

   Note

   You can specify a custom name for the rule set. In this example, SD-DT is used.

   

4. In the left-side pane of the Details page, click Schema Design.

5. On the Schema Design tab, find the rule whose name is R & D process and click Edit in the Actions column.

   

6. In the Change Configuration Item dialog box, configure the parameters.

   1. Change the name of the second node to Testing and change the database environment to test.

   2. Add a third node, set the name of the third node to Publishing, set the database environment to product, and set the node as an anchor.

      Note

      Anchor: the point that is used to stop the R&D process. After you set a node as the anchor and complete the node for a ticket, the ticket enters the Published state and cannot be edited.

   The following figure shows the Change Configuration Item dialog box.

   

7. Click Submit.

Step 2: Configure security rules for database instances

This section describes how to set the control mode of the Schema_Design-dev database instance whose environment type is dev to Security Collaboration and select the SD-DT rule set that is created in Step 1.

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

2. On the homepage of the DMS console, right-click the Schema_Design-dev database instance in the left-side instance list.

3. In the shortcut menu that appears, choose Control Mode > Security Collaboration > SD-DT.

Step 3: Design, test, and publish a table schema

This section describes how to use the configured R&D process to design, test, and publish a table schema for a database.

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. In the upper-right corner of the Schema Design page, click Schema Design.

4. Configure the parameters to create a schema design ticket and click Submit.

   After the ticket is created, you are navigated to the Ticket Homepage page. The project contains five nodes. The following figure shows an example.

5. Configure the schema design node.

   1. Click Import SQL Statements.

   2. In the Import SQL Statements dialog box, enter the following statements and click OK.

      CREATE TABLE `teacher` (
          `teacher_id` varchar(20) NOT NULL COMMENT 'Teacher ID',
          `teacher_name` varchar(20) NULL COMMENT 'Teacher name',
          `phonenumber` varchar(20) NULL COMMENT 'Teacher phone number',
          PRIMARY KEY (`teacher_id`)
      ) DEFAULT CHARACTER SET=utf8 COMMENT='Teacher table';

   3. On the Create Physical Table page, confirm the information about the table on the Basic Info, Column Info, and Index Info tabs.

   4. Click Save. In the Precheck Result dialog box, click Confirm Changes and Submit to Save.

   5. Click Perform Changes to Base Database.

   6. In the dialog box that appears, set the Execution Strategy parameter to Execute Now. After you confirm that the SQL statements are correct, click Submit for Execution.

   7. Wait until the execution is approved, DMS writes the new table schema to the dev_database database.

   8. Optional: You can test the dev_database database on the SQLConsole tab of the dev_database database or submit a data change ticket.

      The following sample code shows how to insert test data:

      insert into teacher
      (teacher_id, teacher_name, phonenumber)
      values
      ('t001', 'teacher001', '1380000001'),
      ('t002', 'teacher002', '1380000002'),
      ('t003', 'teacher003', '1380000003');

      After the schema design node is verified, you can go to the next node.

   9. On the Ticket Homepage page, click Go to Next Node.

      Note

      Before you go to the next node, you can work with your colleagues to change the table schema of the dev_database database multiple times and verify the changes. After you click Go to Next Node, DMS generates a create script and publishes it to the next node.

   10. In the Go to Next Node dialog box, click Go to Next Node.

6. Configure the testing node.

   1. On the Ticket Homepage page, click Perform Changes to Target Database.

   2. In the Perform Changes to Target Database dialog box, select the test_database database, set the Execution Strategy parameter to Execute Now, and then click Submit for Execution.

      Note

      Wait until the execution is approved, DMS writes the changed table schema to the test_database database.

   3. Optional: You can test the test_database database on the SQLConsole tab of the test_database database or submit a data change ticket.

      Note

      After the testing node is verified, you can go to the next node.

   4. On the Ticket Homepage page, click Go to Next Node.

   5. In the Go to Next Node dialog box, click Go to Next Node.

7. Configure the node of schema publishing to the production environment.

   1. On the Ticket Homepage page, click Perform Changes to Target Database.

   2. In the Perform Changes to Target Database dialog box, select the product_database database, set the Execution Strategy parameter to Execute Now, and then click Submit for Execution.

      Wait until the execution is approved, DMS writes the changed table schema to the product_database database. You can also write data to the product_database database.

   3. On the Ticket Homepage page, click Go to Next Node.

   4. In the Go to Next Node dialog box, click Go to Next Node.

      The schema design process ends, and the ticket is closed.

References

• For more information about the design rules in the Schema Design module, see Schema design.

• For more information about the scenarios and benefits of the schema design feature, see Design schemas.

• For more information about how to obtain the publishing details of a schema design ticket by calling an API operation, see

  ListDDLPublishRecords