This topic describes how to publish schemas in a standard and secure manner. The R&D process consists of schema design, testing, and publishing to the production environment.

Background information

When you create projects, meet new business requirements, or optimize business operations, you may want to create tables or change schemas. For example, add or delete fields or indexes, adjust field attributes, or adjust the index composition. In this case, you can use the schema design feature of Data Management (DMS).

The schema design feature of DMS supports multiple sets of R&D environments. In the development process, the schema design feature automatically detects the R&D specifications that you must follow in designing schemas and supports collaborative development on the web page.

The schema design feature allows you to change schemas as needed. In addition, the feature ensures the consistency of related schemas that are designed based on various specifications or in different environments. This greatly improves the success rate of publishing to the production environment.

The following table describes the schema design feature.

Benefit Description
Ensures the success rate of publishing to the production environment. Before you publish a new schema to the production environment, the schema design feature verifies the schema at each node of the R&D process. This can prevent publishing failures that are caused by unverified scripts. This also ensures the success rate of publishing to the production environment.
Ensures the consistency of schemas in different environments. DMS changes the schema in each environment based on the R&D process. This can prevent the delays that are caused by failed schema change in some environments, such as testing delay and the delay in project release.
Ensures the standardization of data structure. Security rules provide nearly 40 R&D specifications for multiple operations such as creating tables, modifying table fields, and modifying table indexes. These specifications allow developers to design schemas that meet the R&D specifications of enterprises. This can prevent production failures that are caused because the published schemas do not meet the specifications. In addition, this feature reduces the workload of operations and maintenance (O&M) staff in checking specifications, increases R&D performance, and supports fast iteration and release.

Preparations

  1. Create three ApsaraDB RDS for MySQL instances: 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, and Product based on the instance names. For more information about how to register an ApsaraDB instance, see Register an ApsaraDB instance. For more information about the environment type of a database instance, see Change the environment type of an instance.
  3. Create a database for each database instance: dev_database, test_database, and product_database.
The following table describes the mappings between database instances and the nodes of the R&D process.
R&D node Schema design Testing Publishing to the production environment
Database instance name Schema_Design-dev Schema_Design-test Schema_Design-product
Environment type dev test product
Database name dev_database test_database product_database

Step 1: Configure R&D rules

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

  1. Log on to the DMS console.
  2. In the top navigation bar, move the pointer over the More icon and choose System > Security Rules.
  3. On the Security Rules tab, click Create Rule Set in the upper-left corner. In the Create Rule Set dialog box, set the parameter as required.
    Note You can customize the rules and rule set name. In this example, the rule set is named SD-DT.
    Create a rule set
  4. In the left-side navigation pane of the Details dialog box, click the Schema Design tab.
  5. On the Schema Design tab, find the rule named R & D process and click Edit in the Actions column.
    Edit button
  6. In the Change Configuration Item dialog box, set the following 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, and then set the database environment to product. In addition, 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.
    Edit the R&D process
  7. Click Submit.

Step 2: Configure security rules for database instances

This section shows you 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.
  2. On the homepage of the DMS console, right-click the Schema_Design-dev database instance in the left-side navigation pane.
  3. In the shortcut menu that appears, choose Control Mode > Security Collaboration > SD-DT.
    Select a rule set

Step 3: Design, test, and publish a schema

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

  1. Log on to the DMS console.
  2. In the top navigation bar, move the pointer over the More icon and choose Schemas > Schema Design.
  3. Click Schema Design in the upper-right corner.
    Schema Design
  4. Set the parameters as required and click Submit.
    Create Ticket
    Parameter Description
    Project Name The name of the project. This facilitates subsequent management. In this example, the project is named Schema Design Test.
    Project description The purpose or objective of the project. This reduces communication costs.
    Change Base Database The base database to which changes are made. Select the dev_database database from the drop-down list. In Step 2, the SD-DT rule set is selected for the Schema_Design-dev database instance to which the dev_database database belongs. Based on the configured R&D process in the SD-DT rule set, DMS applies the R&D process of schema design, testing, and publishing to the production environment.
    Note DMS applies the R&D process that is configured for the selected base database to the schema design ticket.
    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 changes. Click Add to add stakeholders. The specified stakeholders can view the ticket details and assist in the approval process. Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.
    After you create a ticket, the Project Homepage tab appears. The following figure shows the five nodes of the project. Schema design flowchart
  5. Configure the schema design node.
    1. Click Import Table Creation Statements.
    2. In the Import Table Creation Statements dialog box, enter the following statement in the field and click OK.Import Table Creation Statements
      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 tab, confirm the table information on the Basic Info, Column Info, and Index Info tabs.
      • Basic Info tabBasic Information tab
      • Column Info tabColumn Info tab
      • Index Info tabIndex Info tab
    4. Click Save. In the Precheck Result message, click Ignore the warning and continue to save.
    5. Click Perform Changes to Base Database.
    6. In the Perform Changes to Base Database dialog box, set the Execution Strategy parameter to Execute Now. After you confirm that the SQL statement is correct, click OK.Perform Changes to Base Database
    7. After you click OK, wait for approval. After the execution is approved, DMS writes the new schema to the dev_database database.
    8. Test the dev_database database. You can insert data in the SQLConsole of the dev_database database or submit a data change ticket. After the schema design node is verified, you can go to the next node.Insert test data

      In this example, use the following sample code to insert test data:

      insert into teacher
      (teacher_id, teacher_name, phonenumber)
      values
      ('t001', 'teacher001', '1380000001'),
      ('t002', 'teacher002', '1380000002'),
      ('t003', 'teacher003', '1380000003');
    9. On the Project Homepage tab, click Go to Next Node.
      Note Before you click Go to Next Node, you can work with your colleagues to change the 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.
      Go to Next Node
    10. In the Go to Next Node message, click Go to Next Node.
  6. Configure the testing node.
    1. On the Project Homepage tab, 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 After you click Submit for Execution, wait for approval. After the execution is approved, DMS writes the changed schema to the test_database database.
    3. Test the test_database database. You can insert data in the SQLConsole 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 Project Homepage tab, click Go to Next Node.Go to Next Node
    5. In the Go to Next Node message, click Go to Next Node.
  7. Configure the node of publishing to the production environment.
    1. On the Project Homepage tab, 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.

      After you click Submit for Execution, wait for approval. After the execution is approved, DMS writes the changed schema to the product_database database. You can also write data to the product_database database.

    3. On the Project Homepage tab, click Go to Next Node.Go to Next Node
    4. In the Go to Next Node message, click Go to Next Node.

      Then, the R&D process ends and the ticket is closed.