This is intended to focus on the migration of data from existing applications to a new Database Schema in the MariaDB cloud database. The following sections of this article outline the recommended steps for migrating all or part of your existing applications from the existing DB schema to new schema (s). Before getting into the details here is a short background on the topic.
Before version 21.3, Appian Cloud database didn’t provide the option or provision to create additional Schema with the phpMyAdmin tool for MySQL / MariaDB databases. This has been updated as of 21.3 and hence all new applications being built on top of 21.3 would have this feature to add new schemas in MariaDB via phpMyAdmin. In this article we would like to elaborate on the options which existing customers would have in order to migrate their applications from an existing single schema in MariaDB to one/multiple new schemas in MariaDB.
We recommend creating a new schema only for applications that require segregated user access to the database. For common data between applications, all users should access from a common schema.
Using new schemas for existing applications and migrating data to the new schema is a complex activity. Updating your application and writing the required SQL scripts requires careful planning, development, and database knowledge.
The high level steps involved in the process are :
For the application you want to migrate to the new schema, identify the database objects
Once the data has been migrated to the new Schema, the most important part of the process is to make sure that all the old references in the application objects are changed to point to the new Schema using the new Connected System Data Source. These objects include:
Thoroughly test the new functionality on your lower environment before deploying the changes to higher environments.
Here is an example of how to use the new features of Appian 21.3 to create additional schema using phpMyAdmin.
In the figure above a new schema is created by executing a stored procedure highlighted in Red. The result is a new schema named “Schemaone” in the example. The connection details are highlighted in Green.
The newly created schema now appears on the landing page for phpMyAdmin as shown below.
Here are the details on how one can secure the credentials of a new DB schema using the feature of Data Source Connected System. As shown below in the figure, there are new connected system options for all the major DB systems like Oracle. MariaDB, MySQL, DB2. The connected system then ensures that the authorization and authentication parameters are securely stored in the Appian platform.
Once the Connected system is configured the data source would be available in all the objects which refer to a data source such as: data store, Records while configuring a data source, Query Database smart service, etc.
Here are the set of steps which are carried out in a sample application in Appian for migrating the tables to a new DB schema.
Lock all the source tables:
LOCK TABLES Appian.table1 WRITE, LOCK TABLES Appian.table2 WRITE, LOCK TABLES Appian.table3 WRITE, ..... LOCK TABLES Appian.tableN WRITE;
Data population statements
INSERT INTO newSchema.table1 SELECT * FROM Appian.table1; INSERT INTO newSchema.table2 SELECT * FROM Appian.table2; INSERT INTO newSchema.table3 SELECT * FROM Appian.table3; .... INSERT INTO newSchema.tableN SELECT * FROM Appian.tableN;
6. At the end of the script after section 4 (create foreign keys), drop all the source tables and then unlock them.drop table Appian.table1;
DROP TABLE Appian.table2; DROP TABLE Appian.table3; .... DROP TABLE Appian.tableN; UNLOCK TABLES;
7. Run this script in phpMyAdmin.
8. Update all design objects that reference a data source to the newly created CSDS object.
9.Test.
Here are the steps to migrate the application to the higher environments