All Products
Search
Document Center

Schema Design

Last Updated: May 29, 2020

Background

Generally, a schema design ticket may involve multiple instances when you want to design the schema in an offline environment or publish data in an online environment. In this case, the basic configuration items, design rules, and risk identification rules in the Schema Design module are set to the security rules that are used to apply changes to the base database, which is the database in an offline environment.

Note:

  • Design rules in the Schema Design module include the following options: Save Changes and Validate Header, Save Changes and Validate Field, and Save Changes and Validate Index, as shown in the following figure.
    Design rules
  • Risk identification rules in the Schema Design module include the following options: Table Creation Risk Control, Field Change Risk Control, Index Change Risk Control, and SQL Execution Risk Control, as shown in the following figure.
    Risk identification rules

However, an approval process uses the security rules that are set for the database in an online environment. For example, if you submit a schema design ticket to design the schema of database B, the security rules set for instance B, which is deployed in an online environment, are used.

Basic configuration items

DMS offers the following basic configuration items in the Schema Design module:

  • Enable non-peer Publishing: specifies whether to enable non-peer publishing. By default, data changes to a table can only be published to a table with the same name in another database. After you enable non-peer publishing, you can perform data changes on any table.

    Note: This feature may bring high risks. We recommend that you proceed with caution and enable this feature only for special requirements.

  • R & D process: the whole process of a schema design ticket. It is the most important configuration item in the Schema Design module. The following table describes the parameters.

    Parameter Description
    Step The type of the node. Valid values: Design and Publish.
    • The design node in the R&D process is generated by default and cannot be removed. It determines the environment where the schema change is designed.
    • A publish node in the R&D process is used to publish the schema change after the change is designed. You can set multiple publish nodes.
    Node Name The name of the node. The node name can be up to 10 characters in length.
    Database Environment The environment where the node is executed.
    Execution Strategy
    • The way in which the node is executed. Valid values:
    • Immediately: The node is executed immediately after being approved.
    • Periodically: The node is executed at the time that you specify. If a node is approved before the specified point in time, it is executed as scheduled. Otherwise, the node is interrupted and not executed.
    Can Go Back Specifies whether the publish node can be rolled back to the design node.
    Can Skip Specifies whether the node can be skipped.
    Anchor The point that is used to stop the schema change process. If you set a node as the anchor, after the node is published, the nodes that follow the anchor cannot be executed, that is, the schema cannot be changed. At this time, the ticket enters the Published state.
    Actions The operation that you can perform on a publish node. You can delete a publish node as required.
  • Field type configuration: the supported data types of added fields.

  • Index type configuration: the supported data types of added indexes.
  • Create a table template: the template used to create a table. The configured fields and indexes are loaded by default when a table is created. The template is not required. You can determine whether to use a template to create a table based on your business requirements.
  • It is forbidden to modify the original field data type: specifies whether the data types of the original fields can be modified when the original table is changed.
  • Prohibit deleting original fields: specifies whether the existing fields are allowed to be deleted when the original table is changed. It is highly risky to delete the original fields. We recommend that you enable this feature.
  • Prohibit renaming original fields: specifies whether the existing fields are allowed to be renamed when the original table is changed. It is highly risky to rename the original fields. We recommend that you enable this feature.
  • Table character set license configuration: the range of character sets that are allowed to be used when you create a table. For example, you can specify utf8 and utf8mb4.
  • Default approval template for Structural design: the default approval template used for a schema design ticket if you do not configure the Approval Rule Validation checkpoint. You can also change the default approval template. For more information, see Procedure of changing the default approval template.
  • When published, the work order will automatically advance to the end state: the point that is used to stop the schema change process. If you enable this feature, after the node that is set as the anchor in the R&D process is successfully executed, the system automatically turns the ticket to the Finished state. To use this feature, you must set the last node in the R&D process as the anchor.

Checkpoints

The Schema Design module involves the following two processes:

  • Process of saving changes: DMS offers the following three checkpoints for this process, which validate the table headings, fields, and indexes respectively.
  • Save Changes and Validate Header
  • Save Changes and Validate Field
  • Save Changes and Validate Index
  • Process of applying changes: DMS offers the following five checkpoints for this process. The first four checkpoints identify the risks that rise from changing schemas without locking tables, and the last checkpoint assigns an approval process to each type of risk.
  • Table Creation Risk Control
  • Field Change Risk Control
  • Index Change Risk Control
  • SQL Execution Risk Control
  • Approval Rule Validation

DMS provides you with various system built-in templates of security rules to validate these two types of processes. You can directly use the templates or modify the templates based on your business requirements. For more information, see Procedure of creating a security rule.

Factors and actions

  • Factor: A factor is a system built-in variable that is used to obtain the context to be validated by security rules, such as the subcategories of SQL statements and the number of rows in which data is affected. A factor name starts with @fac., appended with the display name of the factor type. Each module of the Security Rules page offers different factors for different checkpoints. The following table describes the supported factors in the Schema Design module.

    Factor Description
    @fac.table_kind The type of the table whose schema is to be changed. Valid values:
    • new: a newly created table.
    • old: an existing table.
    @fac.column_kind The type of the field to be changed. Valid values:
    • new: a newly created field.
    • old: an existing field.
    @fac.xxxx_old The value of an existing field or index used for comparison.
    @fac.column_is_primary Indicates whether the field serves as a primary key. Valid values:
    • true: The field serves as a primary key.
    • false: The field does not serve as a primary key.
    @fac.column_type_support_default Indicates whether the data type of the field supports a default value. Valid values:
    • true: Support a default value.
    • false: Do not support a default value.
    For example, a field of the CHAR type supports a default value, whereas a field of the TEXT type does not.
    @fac.index_kind The type of the index to be changed. Valid values:
    • new: a newly created index.
    • old: an existing index.
    @fac.index_column_count The number of fields in the index.
    @fac.change_type The type of the schema change performed by data definition language (DDL) statements. Valid values:
    • new: Add one or more fields or indexes.
    • modify: Modify one or more fields or indexes.
    • delete: Delete one or more fields or indexes.
    @fac.altered_table_size The size of the table whose schema is to be changed. Unit: MB.
    @fac.online_execute Indicates whether the schema change can be performed in an online environment. Valid values:
    • true: The schema change can be performed in an online environment.
    • false: The schema change cannot be performed in an online environment.
    @fac.change_risk_level The risk level of the schema change. Valid values:
    • high
    • middle
    • low
    @fac.env_type The type of the environment. The value is the display name of the environment type, such as DEV and PRODUCT. For more information, see Change the environment type of an instance.
  • Action: An action is the operation that the system performs after the conditions specified in the if statement are met. For example, the system can perform the relevant action to forbid the submission of a ticket, select an approval process, approve a ticket, or reject a ticket. Actions show the purpose of setting security rules. An action name starts with @act., appended with the display name of the action type. Each module of the Security Rules page offers different actions for different checkpoints. The following table describes the supported actions in the Schema Design module.

    Action Description
    @act.block_submit Blocks the submission of the schema change and displays the error message. This action can be used in the process of saving changes. The statement is in the following format: @act.block_submit 'Reason for blocking the submission'.
    @act.show_warning Displays the error message without blocking the submission of the schema change. This action can be used in the process of saving changes. The statement is in the following format: @act.show_warning 'Error message'.
    @act.mark_middle_risk Specifies that the schema change is at medium risk. This action can be used in the process of identifying the risk level. The statement is in the following format: @act.mark_middle_risk 'Reason for the identification'.
    @act.mark_high_risk Specifies that the schema change is at high risk. This action can be used in the process of identifying the risk level. The statement is in the following format: @act.mark_high_risk 'Reason for the identification'.
    @act.forbid_submit_publish Rejects the ticket. This action can be used in the process of setting the approval process. The statement is in the following format: @act.forbid_submit_publish 'Reason for the rejection'.
    @act.do_not_approve Specifies the approval template. For more information, see Approval processes.
    @act.choose_approve_template
    @act.choose_approve_template_with_reason

Templates of security rules

DMS provides you with various system built-in templates of security rules. You can directly use the templates or modify the templates based on your business requirements. The following table describes the supported rule templates in the Schema Design module.

Checkpoints Feature of template
Save Changes and Validate Header Specifies that the name of a newly created table cannot contain the specified keywords.
Limits the length of the name of a newly created table.
Requires a description for a newly created table.
Requires lowercase letters for the name of a newly created table.
Requires a primary key for a newly created table.
Requires a unique index for a newly created table.
Limits the number of indexes and sends alert notifications to users if the threshold is exceeded.
Requires the specified fields for a newly created table.
Save Changes and Validate Field Specifies that the name of a newly created field cannot contain the specified keywords.
Requires a description for a newly created field.
Requires uppercase or lowercase letters for the name of a newly created field.
Requires a default value for a non-null field that is newly created in an existing table.
Requires a default value for an existing field whose status is changed from nullable to non-null.
Specifies that a newly created primary key field must be of the INTEGER type.
Specifies that all fields of a newly created table must be assigned with values.
Limits the length of a newly created field of the CHAR type.
Limits the length of a newly created field of the VARCHAR type.
Save Changes and Validate Index Specifies that the name of a newly created index cannot contain the specified keywords.
Requires uppercase or lowercase letters for the name of a newly created index.
Specifies that a newly created index must be a unique index.
Specifies that a newly created index must be a normal index.
Specifies that a newly created index must be of the FULLTEXT type.
Specifies that a newly created index must be of the SPATIAL type.
Limits the number of newly created fields that serve as primary keys.
Limits the number of newly created index fields.
Table Creation Risk Control
Limits the number of table shards in a logical table.
Requires a primary key for a table.
Limits the number of indexes in a table.
Field Change Risk Control Specifies that the specified fields cannot be deleted.
Limits the data type of a newly created field.
Specifies that a field cannot be renamed as the specified name.
Specifies that a field cannot be changed to the specified data type.
Index Change Risk Control Specifies that the primary keys of a table cannot be deleted.
Specifies that the indexes of a table cannot be deleted.
Specifies that the primary keys of a table cannot be changed to indexes.
Specifies that the primary keys cannot contain the specified fields.
Specifies that the normal indexes cannot be changed.
Limits the type of a newly created index.
SQL Execution Risk Control Specifies that an instance cannot be a core instance.
Specifies that a table cannot use the specified storage engine.
Specifies that the schema cannot be changed without table locking for tables of the specified size.
Approval Rule Validation Forbids DDL statements from being used to change the schemas of databases.
Forbids DDL statements from being used to change the schemas of databases in an online environment.
Allows DDL statements to be executed without approval to change the schemas of databases in an offline environment.
Sets the approval processes for different risk levels in an online environment.

Procedure of changing the default approval template

  1. Log on to the DMS console.
  2. In the top navigation bar, choose System Management > Security > Security Rules.
  3. On the Security Rules page that appears, find the target rule set and click Edit in the Actions column.
  4. On the Details page that appears, click the Schema Design tab.
  5. On the Schema Design tab, the basic configuration items appear by default.
  6. Find the Default approval template for Structural design configuration item and click Edit in the Actions column.
    Change the default approval template - Schema design
  7. In the Change Configuration Item dialog box that appears, click Switch Approval Template.
  8. In the Switch Approval Template dialog box that appears, find the target template and click Select in the Actions column.

    Note: You can also click Reset to Free of Approval to skip the approval for tickets.

  9. Click Submit.

Procedure of creating a security rule

  1. Log on to the DMS console.
  2. In the top navigation bar, choose System Management > Security > Security Rules.
  3. On the Security Rules page that appears, find the target rule set and click Edit in the Actions column.
  4. On the Details page that appears, click the Schema Design tab.
  5. On the Schema Design tab, click Create Rule next to Actions.
    Create a security rule - Schema design
  6. In the Create Rule - Schema Design dialog box that appears, set the parameter as required. The following table describes the parameters.

    Parameter Description
    Checkpoints (Required) The checkpoint under which you want to create the security rule. DMS provides you with various checkpoints in the Schema Design module. For more information, see Checkpoints.
    Template Database (Optional) The template based on which you want to create the security rule. DMS provides you with various system built-in templates of security rules. After you select a checkpoint from the Checkpoints drop-down list, you can click Load from Template Database to select a template. For more information about the available templates, see Templates of security rules.
    Rule Name (Required) The custom name of the security rule. If you load a security rule from a template, the rule name is automatically filled in.
    Rule DSL (Required) The domain-specific language (DSL) statement used to set the security rule. For more information, see DSL syntax for security rules. If you load a security rule from a template, the statement is automatically filled in.
  7. Click Submit.

  8. Find the created security rule and click Enable in the Actions column. By default, the created security rule is in the Disabled state.
  9. In the message that appears, click OK.