# Database, Architecture and Setup

###### tags: `Ideation`
## Database

We can add more tables on the configured flowable database and possible edit the original ones to associate each instance with the submitter/moderator. Probably this will be done with the user email, after validated with the google OAuth technology.

Suggesting adding 1 more tables:
moderators

Possible database tecnology with flowable (https://flowable.com/open-source/docs/bpmn/ch03-Configuration/#supported-databases)
1. h2
2. mySQL
3. oracle - Commercial License / Proprietary License (Excluded)
4. postgresql
5. mssql - Commercial License / Proprietary License (Excluded)
6. db2 - Commercial License / Proprietary License (Excluded)

## Databases Comparison table

| Name | H2 | MySQL | PostgreSQL |
|--|--|--|--|
| Description | Full-featured RDBMS with a small footprint, either embedded into a Java application or used as a database server. | Widely used open source RDBMS | Widely used open source RDBMS |
| Primary database model | Relational DBMS | Relational DBMS | Object-Relational DBMS |
| Secondary database models | | Document store | Document store |
| Website | www.h2database.com | www.mysql.com | www.postgresql.org |
| Technical documentation | www.h2database.com/­html/­main.html | dev.mysql.com/­doc | www.postgresql.org/­docs/­manuals |
| License | Open Source | Open Source | Open Source |
| DBaaS offerings | | Azure Database for MySQL: A fully managed, scalable MySQL relational database with high availability and security built in at no extra cost | ScaleGrid: Fully managed PostgreSQL hosting with high availability, dedicated servers, and superuser control on the #1 multi-cloud Amazon RDS alternative. Azure Database for PostgreSQL: A fully managed, scalable PostgreSQL relational database with high availability and security built in at no extra cost |
| Implementation language | Java | C and C++ | C |
| Server operating systems | All OS with a Java VM | FreeBSD Linux OS X Solaris Windows | FreeBSD | HP-UX Linux NetBSD OpenBSD OS X Solaris Unix Windows |
| Data scheme | yes | yes | yes |
| Typing | yes | yes | yes |
| XML support | no | yes | yes |
| Secondary indexes | yes | yes | yes|
| SQL | yes | yes | yes |
| APIs and other access methods | JDBC, ODBC, ADO.NET, JDBC, ODBC , Proprietary native API ADO.NET | JDBC, native C library, ODBC streaming API for large objects |
| Supported programming languages | Java | Ada, C, C#, C++, D,Delphi, Eiffel, Erlang, askell, Java, JavaScript (Node.js), Objective-C, OCaml, Perl, PHP, Python, Ruby, Scheme, Tcl | .Net, C, C++, Delphi, Java , JavaScript (Node.js), Perl, PHP, Python, Tcl |
| Server-side scripts | Java Stored Procedures and User-Defined Functions | yes | user defined functions |
| Triggers | yes | yes | yes |
| Partitioning methods | none | horizontal partitioning, sharding with MySQL Cluster or MySQL Fabric | partitioning by range, list and (since PostgreSQL 11) by hash |
| Replication methods | With clustering: 2 database servers on different computers operate on identical copies of a database | Master-master replication | Master-slave replication | Master-slave replication |
| MapReduce | no | no | no |
| Consistency concepts | | Immediate Consistency | Immediate Consistency | Immediate Consistency |
| Foreign keys | yes | yes | yes |
| Transaction concepts | ACID | ACID | ACID |
| Concurrency | yes, multi-version concurrency control (MVCC) | yes | yes |
| Durability | yes | yes | yes |
| In-memory capabilities | yes | yes | no |
| User concepts | fine grained access rights according to SQL-standard | Users with fine-grained authorization concept | fine grained access rights according to SQL-standard |

More comparisons (between mysql and postgresql):
- https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/
- https://developer.okta.com/blog/2019/07/19/mysql-vs-postgres#default-installation-and-extensibility-of-postgres-and-mysql

Some conclusions from the links above:
- Postgresql is acid compliant from ground up, less vulnerable to data corruption.
- Postgresql is largely SQL compliant, which means, it is easier to move the data from one database to another.
- Postgresql performance is better on complex queries. That may be needed depending on the tipe of searches we implement.
- Mysql concurrency only available in Innodb.
- Postgresql supports many nosql features.

The main use case for our database is simple: managing the data that supports flowable's execution. However, some complex features are expected, like searches. That must be taken into account when choosing the database. Additionally, there are some non functional requirements that must be met, like high performance concurrency, scalability, data integrity, etc. **For these reasons, PostgreSQL seems to be the best option.**

## Google OAuth

Google api client library for java
https://developers.google.com/api-client-library/java/google-api-java-client/oauth2

Google has a java library available for download, to be integrated with the application. It is the most secure way for implementing google's OAuth.

General page with libraries for many different programming languages
https://developers.google.com/api-client-library

This one is for Node.js
https://github.com/google/google-api-nodejs-client

## Architecture


As for the technologies:
- **React** will be used for the Front End;
We chose this technology beacuse it suits the requirements asked in terms of the UI that needs to be developed and the team as good knowledge regarding this technology.
- **Flowable** will be used as the Process Engine;
We chose this technology in order to take advantage of an already developed mechanism that is proven to work in terms of Process Creation. That way we can focus more on important features related to Talkdesk use case, and further improve the final product so it can be used in a real scenario
- **PostgreSQL** will be used for the Database;
Flowable provides compatibility with some databases: H2, MySQL, Oracle, PostgreSQL, Mssql and Db2. However, only H2, MySQL and PostgreSQL are open-source. Among these three, we decided to exclude H2 because it doesn't persist the data between multiple executions. Finally, as the project's use cases expect the implementation of some complex queries for the implementation of searches, PostgreSQL is the best option because it has better performance for these purposes.

## Setup

You can embed the engine in your application or service by including the Flowable library, which is available as a JAR. Since it’s a JAR, you can add it easily to any Java environment: Java SE; servlet containers, such as Tomcat or Jetty, Spring; Java EE servers, such as JBoss or WebSphere, and so on

### Configuration

The configuration details are made in a file called: flowable.cfg.xml
This link provide the all setup,

https://flowable.com/open-source/docs/bpmn/ch03-Configuration/

#### Further notes about specific configurations

org.flowable.engine.impl.cfg.StandaloneProcessEngineConfiguration

JTA is a general API for managing transactions in Java. It allows you to start, commit and rollback transactions in a resource neutral way. Transactional status is typically stored in TLS (Thread Local Storage) and can be propagated to other methods in a call-stack without needing some explicit context object to be passed around. Transactional resources can join the ongoing transaction. If there is more than one resource participating in such a transaction, at least one of them has to be a so-called XA resource.

A resource local transaction is a transaction that you have with a specific single resource using its own specific API. Such a transaction typically does not propagate to other methods in a call-stack and you are required to pass some explicit context object around. In the majority of the resource local transactions it's not possible to have multiple resources participating in the same transaction.

You would use a resource local transaction in for instance low-level JDBC code in Java SE. Here the context object is expressed by an instance of java.sql.Connection. Other examples of resource local transactions are developers creating enterprise applications around 2002. Since transaction managers (used by JTA) were expensive, closed source and complicated things to setup around that era, people went with the cheaper and easier to obtain resource local variants.

You would use a JTA transaction in basically every other scenario. Very simple, small, free and open-source servers like TomEE (25MB) or GlassFish (35MB) have JTA support out of the box. There's noth

ing to setup and they Just Work.

Finally, technologies like EJB and Spring make even JTA easier to use by offering declarative transactions. In most cases it's advised to use those as they are easier, cleaner and less error prone. Both EJB and Spring can use JTA under the covers.

In conclusion, Transaction-type should be set to "RESOURCE_LOCAL" for Java SE application and to "JTA" for Java EE application. "RESOURCE_LOCAL" may work fine on some web application deployed on Tomcat, but may cause issues when you run your application under glassfish environment.

If you are working on distributed transactions you must use "JTA" as your transaction manager.
share