Schema Design and Release

The Schema Design and Release feature enables multi-environment development process management, facilitating smooth progress in development across different environments. It ensures that all necessary validations, changes, and approvals are completed before the code is deployed to the production environment.

Feature Introduction

In the development process of an enterprise, there are usually multiple stages progressively advancing. Typically, there are at least two key stages: development testing environment and production environment. Due to considerations of the security of the production environment, only SQL scripts that have been executed and successfully validated in the development testing environment are allowed to be released to the production environment. On the other hand, the development testing phase may undergo multiple rounds of modifications. To ensure the integrity of changes, it is necessary to ensure that all changes are applied to the production environment in sequence, otherwise, it may lead to missed changes and issues such as application deployment failure.

The Schema Design and Release feature consolidates all change tasks made by developers to the baseline data source in the development testing environment into a single SQL script, recording their execution sequence and dependencies. When submitting changes to non-baseline data sources, administrators can require only SQL script execution, thereby preventing change errors due to mistakes or omissions.

Through the Schema Design and Release feature, project administrators can have a clear understanding of the execution status and progress of the entire process. All developers can also view the execution status of each task at any time, facilitating the discovery and resolution of issues and ensuring smooth progress of the process.

Notes

Please refer to the precautions in the content from '../ref/_ref-precautions.md'.

Step One: Configure Schema Design and Release Process

NineData comes with default process configurations, consisting of two nodes: Publish to Dev and Publish to Prod. These nodes allow development team members to skip the current node, edit SQL executed in the previous node, and revert to the previous node.

You can edit the default Schema Design and Release process according to actual business needs or create a new one based on this process. This article will use adding a new Schema Design and Release process as an example to explain the configuration method.

1. Log in to the NineData console.

2. In the left navigation bar, click DevOps > Policy & Process.

3. Click on the Schema R&D Configuration tab, then click on Create Process in the upper right corner to open the Create Process window. After completing the configuration according to the table below, click on OK.

   You can also click on Duplicate under the Actions column on the right side of the existing process.

   Parameter	Description
   Duplicate Schema Design & Release Flow	Select an existing Schema Design and Release process to create a new one based on that process. If you use the Duplicate method, this field will be automatically filled.
   Name	Enter the name of the Schema Design and Release process, up to 32 characters.
   Description	Enter a description of this Schema Design and Release process.

4. Click on the newly created Name, or click on Details under Actions column of the process name to open the Schema R&D Details page.

5. Since the current process is copied from another process, and the data source corresponding to the first node must be globally unique, the first step must be to modify the data source corresponding to the node with No. set to 1, otherwise, other content cannot be edited. Click on Edit on the right side of this node, modify the Datasource parameter, and then click OK.

6. Click on Add Node on the right side of the page to add nodes to the process. For example, in the default Publish to Dev and Publish to Prod, add a Test Environment node. Configure it according to the table below, then click OK.

   Parameter	Description
   Node Name	Enter the name of the node to be added. For example: Test Environment.
   Datasource	Select the data source corresponding to this node, supporting addition based on By Envir. or By Datasource. Note that the data source or environment cannot be added repeatedly with the same data source or environment as the first node. By Envir.: All data sources belonging to this environment can be changed in this node. By Datasource: You can select the target database (Schema) corresponding to the target data source, accurately controlling the data sources that this node can change. You can click on Add Datasource to add multiple data sources.
   Node Config	Allow Rollback: Whether to allow rollback from the current node to the previous node. Modify Alter SQL: Whether to allow users to edit SQL scripts when making changes to non-baseline databases in the standard process. Allow Skip: Whether to allow skipping the current node and directly advancing to the next node without executing any changes.

7. After creating the nodes, you can sort, edit, and delete nodes on the page.

   • Sorting: Hover over the row of the target node, then click on the icon on the left side of No. to move the node up.
   • Editing: Click Edit under Actions column on the right side of the target node to edit its details.
   • Deleting: Click Delete under Actions column on the right side of the target node to delete it.

   Switch

   On the Schema R&D Details page, you can use shortcut switches to quickly edit the configurations of each node (Allow Rollback, Modify Alter SQL, Allow Skip) without entering the editing page of each node.

8. Return to the Schema R&D Configuration page, turn on the switch under the Status column of the target process to activate it.

Step Two: Create Schema Design and Release

1. Log in to the NineData console.

2. In the left navigation bar, click on DevOps > Schema Design & Release.

3. Click on Create Schema Design & Release on the right side of the page.

4. On the Create Schema Design & Release page, configure according to the table below, then click Create Schema Design & Release.

   Parameter	Description
   Name	Enter the name of the flow. To reduce communication costs, please use meaningful names as much as possible. Up to 64 characters are supported.
   Process Template (Optional)	After selecting a Process Template, only the data sources bound to that template will be displayed in Base Datasource. If this field is left blank and Base Datasource is selected first, the flow template bound to that data source will be automatically selected.
   Base Datasource	The baseline data source for the Schema Design and Release process. Support for Datasourceor Database Grouping. All changes will revolve around this data source, and all SQL executed in the baseline data source will be automatically organized into SQL scripts according to the execution sequence by the system. Based on the process configuration, administrators can require that changes to non-baseline databases can only be based on this SQL script, and no other changes are allowed to be executed.
   Database	Select a database as the baseline database for the Schema Design and Release process.
   Executor	After the change approval in the Schema Design and Release process, the executor of the SQL statement. Note: The options in the Executor list depend on the following two situations: If the current data source is configured with development specifications: The options in the list are based on the SQL Task Executor Config rule configured in the current specification. This rule is located on the SQL Task & Console tab. For how to configure development specifications, please refer to Edit Specifications. If the current data source is not configured with development specifications: The options in the list are users who have the permission for the current data source. For more information about permissions, please refer to Configure User Permissions.
   Collaborator (optional)	Select collaborators for the current process, who can participate in the current Schema Design and Release process to drive development work together.
   Note (optional)	Explanation of unfinished items in the current Schema Design and Release process.
   Alter SQL	Enter the change SQL to be executed or upload a file containing the change SQL. SQL Text: Enter the SQL statement to be executed directly in the text box. SQL File: Click on Upload File, select and upload the file containing the SQL statements to be executed. Note: After uploading, hover over the file name to the right of the file name and you can choose (preview file) or (delete file).

5. The page will automatically redirect to the task details page of the current Schema Design and Release process. NineData automatically creates a SQL change task for the baseline database. You need to progress this change task in Task List (Submit Approval → Execute). Only after the task status changes to Success, you can continue with the following steps.

Step Three: Using Schema Design and Release Process

The Schema Design and Release process can have multiple nodes, which need to be progressed step by step according to the administrator's configuration. Taking the default scenario as an example, there are two nodes: Publish to Dev and Publish to Prod. Each node has a task list, and all Collaborator of this process can submit SQL tasks here to make changes to the database.

Step Three (1): Submitting Tasks

1. Log in to the NineData console.

2. In the left navigation bar, click on DevOps > Schema Design & Release.

3. Click on the target Pipeline ID or Pipeline ID on the right side of Actions column.

4. In Task List, click on Submit, and configure according to the table below in the pop-up window, then click OK.

   Parameter	Description
   Target	Select the data source for the change.
   Target DB	Select the database for the change.
   Executor	Executor of the current task after approval. Note: The options in the Executor list depend on the following two situations: If the current data source is configured with development specifications: The options in the list are based on the SQL Task Executor Config rule configured in the current specification. This rule is located on the SQL Task & Console tab. For how to configure development specifications, please refer to Edit Specifications. If the current data source is not configured with development specifications: The options in the list are users who have the permission for the current data source. For more information about permissions, please refer to Configure User Permissions.
   Alter SQL Source	Optional when neither Target nor Target DB is selected as the baseline data source. In this case, you need to select the data source where the task has been executed below, and the executed SQL script will be displayed in the code box below.
   Alter SQL	Optional when both Target and Target DB are selected as the baseline data source. Enter the change SQL to be executed or upload a file containing the change SQL. SQL Text: Enter the SQL statement to be executed directly in the text box. SQL File: Click on Upload File, select and upload the file containing the SQL statements to be executed. Note: After uploading, hover over the file name to the right of the file name and you can choose (preview file) or (delete file).

After the task submission is completed, all sub-steps of the task (such as withdrawal, approval, execution, pause, restart, etc.) are consistent with the SQL Task.

Step Three(2): Advancing the Process

Once all task statuses in the current node reach the final state (i.e., Success), you can advance the development pipeline to the next node. If the administrator has enabled Allow Skip in Step 1, even if there are no tasks in the current process, you can force the process to advance to the next process.

1. Log in to the NineData Console.

2. In the left navigation pane, click Datasource > Policy & Process.

   tip

   If you can't find SQL Task, please ensure that your console is in organization mode. For information on how to switch from personal mode to organization mode, see Switch to Organization.

3. Click the Schema R&D Configuration tab, then click on the target Pipeline ID or Pipeline ID under the Actions column.

4. In the Task List, click on Push to Next, and in the confirmation window that appears, click Push to Next again to advance the process to the next node. If there are no more nodes ahead, the development pipeline ends.

Step Three(3): Rollback Process

If the administrator has enabled Allow Rollback in Step One, and there are no tasks in the current node (non-initial node) or all tasks have reached a final state, the process can be rolled back to the previous node.

1. Log in to the NineData console.
2. In the left navigation bar, click on DevOps > Schema Design & Release.
3. Click on the target Pipeline ID or Pipeline ID on the right side of Actions column.
4. In Task List, click on Push to Next on the right of > Rollback to Prev., and then click Rollback to Prev. again in the confirmation window that pops up to roll back the process to the previous node.

Optional Step: Terminate Schema Design and Release Process

During the execution of the Schema Design and Release process, administrators cannot adjust the configuration related to nodes of the process, such as adding nodes, adjusting node order, renaming nodes, deleting nodes, etc. Therefore, if you need to adjust these configurations, you need to terminate the process first.

1. Log in to the NineData console.
2. In the left navigation bar, click on DevOps > Schema Design & Release.
3. Click on the target Pipeline ID or Pipeline ID on the right side of Actions column.
4. In Task List, click on Terminate in the upper right corner of the page, then click Terminate again in the confirmation window that pops up.