Content Comparison

...

Dealing with multiple server for each MySQL instanceCase

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.

SELECT `asccp_id`, `den` FROM `asccp` WHERE `asccp` IN (:asccp_id_list);

4. Making results from results and return it.

Generally, when a data access layer received a query,

1. Split a query out to make it having a simple form without JOIN expression.

2. Send queries to both databases.

3. 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