Demonstrate how a join operation between two tables would work, e.g. getCoursesForInstructors (should return a list of courses, except for courses in the recycle bin, for a given list of instructors)

How to group transactions is business logic and ideally should not be defined in the db layer.

A simple way to rationalise this, say we need to apply a transaction that does operation A on courses then operation B on instructors. If the transaction is to be defined in the db layer, we need to write this specific method in db layer.

Transaction tx = ...
operationAOnCourses()
operationBOnInstructors()
tx.commit()

As you can see, we are now forced to move the business logic to the db layer.

• Include Hibernate as a dependency in build.gradle
• Configure Hibernate programmatically with basic configurations

• Implement Create on Courses table
• Implement Read on Courses table
• Implement Update on Courses table
• Implement Delete on Courses table

Runs a basic Postgres instance in Docker, with a user and named database and password, without any tables.

This issue keeps track of all the tasks related to the PoC:

• Setting up ORM (#11)
• Implement CRUD on Course and Instructor with transactions (#13 and #15)
• Implement the logic layer based on the interoperability design laid out here ( #12)
• Demonstrate how integration and system level tests can be set up with the new database ( #18)
• Demonstrate how database migrations will be done when the data model changes e.g. adding or removing of fields (#16)

Explore how to write component tests (e.g. for course) against SQL DB. Run tests for CoursesDb, CoursesLogic, and a few course-only APIs.

Updated:
The current most viable solution is to use throwaway instances of databases running in Docker containers.

There are two options we can consider:

• Option A: OpenTable Embedded PostgreSQL Component
  • This library is maintained by OpenTable, Inc. It uses TestContainers under the hood, but adds value with builders, the api compatibility, the wrapping around Flyway to allow users to adopt a smooth transition to a Docker-based test approach.
• Option B: TestContainers
  • We can also directly use TestContainers, a well-known Java library that supports JUnit tests, providing lightweight, throwaway instances of common databases
  • We will need to integrate Flyway with TestContainers ourselves

Explore how we would write E2E tests against SQL DB.

To migrate existing data from Datastore to SQL, a migration script is needed. We can also look into having the script create the relation if it does not yet exist in the database, so that we do not have to create all the relations manually in the database first.

Testing GH actions

Credentials (e.g. username, password) should be hidden from any files that will be publicly visible in a commit. We can hide these credentials in environment variables.

Useful links:

• https://docs.docker.com/compose/environment-variables/
• https://blog.alec.coffee/running-sql-migrations-before-booting-docker-compose-services

Many conditions can cause exceptions to be thrown while using Hibernate. These can be mapping errors, infrastructure problems, SQL errors, data integrity violations, session problems, and transaction errors.

These exceptions mostly extend from HibernateException.

Furthermore, most of these are unrecoverable. As a result, retrying the operation would not help. This means we have to abandon the current session on encountering them.

Useful links:

• https://www.baeldung.com/hibernate-exceptions

Explore how to define indexes on columns of a table.

In order to experiment with JOIN operations, we need at least two tables.

• Implement Create on Instructors table
• Implement Read on Instructors table
• Implement Update on Instructors table
• Implement Delete on Instructors table

Create package for the new logic and storage layer:

• teammates.storage.sqlentity.*: entities to be persisted to SQL
• teammates.storage.sql.*Db: DB layer for CRUD for dedicated entities
• teammates.logic.sql.*Logic: logic layer
• teammates.logic.api.LogicNew: facade logic