Sharding Score Database

Motivation:

When it imports OAGIS components of new release to upgrade/patch Score in end-user sides, it needs to provide some complex DB script/program due to a primary key conflict in between Developer’s and End User’s.

Proposal:

Sequence diagram

Directory records example:

Username	Role	Shard Node

Username	Role	Shard Node
Serm	EndUser	enduser
Sermdev	Developer	developer

Question) Would one user be able to have multiple roles (e.g., ‘Serm’ user could be used for both ‘Developer’ and ‘End User’)?

Case of one database instance

Shard Node	Endpoint

Shard Node	Endpoint
enduser	mysql://localhost:3306/enduser
developer	mysql://localhost:3306/developer

Dealing with multiple database in one MySQL instance

Case of two database instance

Shard Node	Endpoint

Shard Node	Endpoint
enduser	mysql://172.12.3.1:3306/oagi
developer	mysql://172.12.3.2:3306/oagi

Dealing with multiple server for each MySQL instance

Migration Plan

Checklist:

Are there end users' components created by oagis? (e.g. UEGs)

Data Access Layer

In order to provide a location transparency, we will introduce a data access layer in Score.

Example case of retrieving Top-level BIE info in End User

Example) Retrieving user extension ASCCs

A query in users' perspective

SELECT `acc`.`acc_id`, acc`.`den`, `asccp`.`asccp_id`, `asccp`.`den`
FROM `acc`
     JOIN `ascc` ON `acc`.`acc_id` = `ascc`.`from_acc_id`
     JOIN `asccp` ON `ascc`.`to_asccp_id` = `asccp`.`asccp_id`
WHERE
     `acc`.`object_class_term` = 'Extension';

What to do in a data access layer

1. Send the following queries to both oagi_dev and oagi_eu databases

SELECT `acc_id`, `den` FROM `acc` WHERE `object_class_term` = 'Extension';

2. Collect acc_id_list from results and send the following query to both databases with collected results.

SELECT `to_asccp_id` FROM `ascc` WHERE `from_acc_id` IN (:acc_id_list);

3. Collect asccp_id_list from results and send the following query to both databases with collected results.

4. Making results from results and return it.

Generally, when a data access layer received a query,

Split a query out to make it having a simple form without JOIN expression.
Send queries to both databases.
Collect results and return it.

What about XA transaction?

We believe that there is no chance to be made an isolated transaction for both databases in any Score actions. Thus, no need to consider about XA transaction.

Implementations:

Hibernate Shards (No longer supported/maintained)
MySQL NDB Cluster / MariaDB Galera Cluster (MySQL Communities' solution)
- Pros
  - There is a community maintaining the products
  - Need less efforts to change existing queries
- Cons
  - Need to check licenses
  - Need to figure out what limitations it has
  - Hard to convert other databases in future
Apache ShardingSphere (JDBC/Proxy approach)
- Pros
  - No need to change database softwares like MySQL to MySQL Cluster
  - Applicable in any ORM framework based on JDBC, such as JPA, Hibernate, Mybatis, Spring JDBC Template or direct use of JDBC
  - Multiple database engines supported (MySQL, Oracle, SQLServer, PostgreSQL and any SQL92 followed databases in accordance with the documentation)
- Cons
  - Highly likely a lots of limitations (But it won’t be a problem since we don’t have complex queries such as a hierarchical query)
  - Performance
  - Need to make another docker instance for this layer

connectCenter Working Group