Schema design - Data Management - Alibaba Cloud Documentation Center

This topic describes the basic configuration items, checkpoints, factors and actions, and templates in the Schema Design module. This topic also describes how to modify the default approval template and create rules.

Background information

A schema design ticket may involve multiple instances when you want to design the schema in a test environment and publish the schema in a production 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 (in a test environment).

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

However, an approval process uses the security rules that are set for the database in a production environment. For example, if you submit a ticket to design the schema of Database B, the security rules set for Instance B are used.

Basic configuration items

Data Management (DMS) offers the following basic configuration items in the Schema Design module:

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.
Warning
This feature may bring high risks. We recommend that you 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. Design: 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. Publish: The 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, which can contain up to 10 characters.
Database Environment	The environment where the node is run.
Execution Strategy	The way in which the node is run. Valid values: Immediately: The node is run immediately after it is approved. Periodically: The node is run at the time that you specify. If a node is approved before the specified point in time, it is run as scheduled. Otherwise, the node is interrupted and not run.
Can Go Back	Specifies whether a Publish node can be rolled back to the Design node.
Can Skip	Specifies whether the current 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 run and 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 remove a Publish node.

Field type configuration: the supported data types of added fields.
Index type configuration: the supported data types of added indexes.
Table creation 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.
Prohibit modifying the original field data type: You can specify whether the data types of the original fields can be modified when the original table is changed.
Prohibit deleting original fields: You can specify whether the existing fields can be deleted when the original table is changed. If existing fields are deleted, high risks may occur. We recommend that you enable this feature.
Prohibit renaming original fields: You can specify whether the existing fields are allowed to be renamed when the original table is changed. If existing fields are renamed, high risks may occur. We recommend that you enable this feature.
Table character sets: the character sets that you can use when you create a table. For example, you can specify utf8 and utf8mb4.
Default approval template for schema design: the default approval template that is used for a schema design ticket if you do not configure the Approval Rule Validation checkpoint. In the Change Configuration Item dialog box, you can click Switch Approval Template to change the approval process of the default approval template. For more information, see Procedure of changing the default approval template.
Advance ticket to the Ended state when published: If you enable this feature, after the node that is set as the anchor in the R&D process is run, the ticket will be advanced to the Ended 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.
- Save Changes and Validate Header
- Save Changes and Validate Field
- Save Changes and Validate Index
Process of applying changes: DMS provides the following five checkpoints for this process. The first four checkpoints identify the risks of schema changes that are performed by using Data Definition Language (DDL) statements, 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 various built-in templates of security rules to validate these two processes. You can use the templates or modify the templates as needed. For more information, see Procedure of creating a security rule.

Factors and actions

Factor: A factor is a built-in variable that is used to obtain the context to be validated by security rules, such as the categories of SQL statements and the number of rows in which data is affected. A factor name consists of the prefix @fac. and the display name of the factor. Each module of the Security Rules page offers different factors for different checkpoints. The following table describes the factors that are provided for the checkpoints in the Schema Change 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 that is used for comparison.
@fac.column_is_primary	Indicates whether the field serves as a primary key column. Valid values: true: The field serves as a primary key column. false: The field does not serve as a primary key column.
@fac.column_type_support_default	Indicates whether a default value can be set for the field of a data type. Valid values: true: A default value can be set. false: A default value cannot be set. For example, a default value can be set for a field of the `CHAR` type, but cannot for a field of the `TEXT` type.
@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 using DDL statements. Valid values: new: Adds one or more fields or indexes. modify: Modifies one or more fields or indexes. delete: Deletes 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 DMS performs after the conditions specified in the if statement are met. For example, DMS can perform actions to prohibit submitting a ticket, select an approval process, approve a ticket, or reject a ticket. Actions show the purpose of security rules. An action name consists of the prefix @act. and the display name of the action. Each module of the Security Rules page offers different actions for different checkpoints. The following table describes the actions that are provided for the checkpoints in the Schema Design module.

Action	Description
@act.block_submit	Blocks submitting the schema change and displays the error message. This action can be used in the process of saving changes. The format of the statement: `@act.block_submit 'Reason for blocking'`.
@act.show_warning	Displays the error message without blocking submitting the schema change. This action can be used in the process of saving changes. The format of the statement: `@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 format of the statement: `@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 format of the statement: `@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 format of the statement: `@act.forbid_submit_publish 'Reason for the rejection'`.
@act.do_not_approve	Specifies the ID of an approval template. For more information, see Configure approval processes.
@act.choose_approve_template
@act.choose_approve_template_with_reason

Templates of security rules

DMS provides you with a large number of predefined security rule templates. You can enable the templates or modify and enable the templates based on your business requirements. The following table describes the supported rule templates in the Schema Change module.

Checkpoints	Template feature
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 key for a newly created table.
	Limits the number of indexes and sends alert notifications to users if the number of indexes reaches a specified threshold.
	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.
	Limits the number of primary keys 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 key columns 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 a table is locked when its size exceeds the specified limit.
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 a production environment.
	Allows DDL statements to be executed without approval to change the schemas of databases in an test environment.
	Sets the approval processes for different risk levels in a production environment.

Procedure of changing the default approval template

Log on to the DMS console V5.0.
Move the pointer over the icon in the upper-left corner and choose All Features > Security and Specifications (DBS) > Security Rules.
Note
If you use the DMS console in normal mode, choose Security and Specifications (DBS) > Security Rules in the top navigation bar.
On the Security Rules page that appears, find the security rule set that you want to modify and click Edit in the Actions column.
In the left-side navigation pane of the Details page, click the Schema Design tab.
On the Schema Design tab, click Basic Configuration Items.
Find the Default approval template for schema design configuration item and click Edit in the Actions column.
In the Change Configuration Item dialog box that appears, click Switch Approval Template.
Find the template that you want to apply and click Select in the Actions column.
Note
You can also click Reset to Free of Approval to skip the approval for tickets.
Click Submit.

Procedure of creating a security rule

Log on to the DMS console V5.0.
Move the pointer over the icon in the upper-left corner and choose All Features > Security and Specifications (DBS) > Security Rules.
Note
If you use the DMS console in normal mode, choose Security and Specifications (DBS) > Security Rules in the top navigation bar.
On the Security Rules page that appears, find the security rule set that you want to modify and click Edit in the Actions column.
In the left-side navigation pane of the Details page, click the Schema Design tab.
On the Schema Design tab, click Create Rule next to Actions.

In the Create Rule - Schema Design dialog box that appears, set the parameters as required. The following table describes the parameters.

Parameter	Description
Checkpoints(Required)	The checkpoint for which you want to create the security rule. DMS provides 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 various 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 in the Template Database, 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 . If you load a security rule from a template in the Template Database, the statement is automatically filled in.

Click Submit.
By default, the security rule that you create is in the Disabled state. Find the created security rule and click Enable in the Actions column.
In the message that appears, click OK.