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, map 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. At this time, Data Management (DMS) comes in handy with its schema design feature.

The schema design feature of DMS supports multiple sets of R&D environment. In the development process, DMS 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 you want. 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.

Characteristic 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 avoid publishing failures that are caused by unverified scripts and therefore ensures the success rate of publishing to the production environment.
Ensures the consistency of schemas in different environments DMS changes a schema in each environment based on the R&D process. This can avoid 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, which involve multiple operations such as creating tables, modifying table fields, and modifying table indexes. For more information, see Usage of security rules. These specifications allow developers to design schemas that meet the R&D specifications of enterprises. This can avoid production failures that are caused by the publishing of schemas that do not meet the specifications. In addition, this feature reduces the workload of 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. Add the three instances to DMS. When you add the instances, set the Control Mode parameter to Security Collaboration and set the Environment type parameter to Dev, Test, and Product based on their instance names. For more information about how to add 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, choose System Management > Security > Security Rules.
    Security Rules
  3. On the Security Rules page, 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. After you create a rule set, the Details page appears. In the left-side navigation pane of the Details page, click Schema Design.
  5. On the Schema Design tab, find 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: Set 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.
  2. On the homepage of the DMS console, right-click the Schema_Design-dev database instance in the left-side instance list.
  3. From the shortcut menu, 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, choose Schemas > Schema Design.
    Schema Design
  3. On the Schema Design page, click Schema Design in the upper-right corner.
    Schema Design button
  4. Configure a schema design ticket and click Create Ticket.
    Schema Design page
    Parameter Description
    Project Name The name of the project. This facilitates subsequent management. In this example, the project is named Schema Design Test.
    Business Background The purpose or objective of the project. This helps reduce the cost of communication.
    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 to be applied. DMS automatically selects security rules based on the selected base database.
    Change Stakeholder The stakeholders of changes. Click Add to add stakeholders who can view the ticket and assist in performing the changes. Except for DMS administrators and database administrators (DBAs), users that are irrelevant to the ticket cannot 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 and click OK.Import Table Creation Statements dialog box
      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 Information, Field, and Index tabs.
      • Basic Information tabBasic Information tab
      • Field tabField tab
      • Index tabIndex tab
    4. Click Save. In the Precheck Result message, click Confirm Changes and Submit to Save.
    5. On the Project Homepage tab, 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 Submit for Execution.Perform Changes to Base Database dialog box
    7. After you click Submit for Execution, wait for the 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. For more information, see Manage a database on the SQLConsole tab and Common data change. 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 and publishes a create script to the next node.
      Go to Next Node button
    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 the 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. For more information, see Manage a database on the SQLConsole tab and Common data change.
      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 button
    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 the 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 button
    4. In the Go to Next Node message, click Go to Next Node.

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