Skip to content
Snippets Groups Projects
README.md 6.05 KiB
Newer Older
# Using local, ephemeral Oracle databases 
This codebase demonstrates how we can containerize our Oracle database so it can be used for local development and to run CI jobs with an ephemeral database instance. It also shows how the deployment process can be leveraged by using Liquibase in a 'deploy' CI job.
A simplified 'membership' API was created to exemplify how tests will be executed in the CI.

## Using local instance for development
### Create and prepare Oracle database within Docker container
The following docker services are necessary to raise an Oracle database container with the required structure:
- *oracle-db*: This service is responsible for creating a docker container with an Oracle database.
- *db-prepare-users*: This service creates the users (ATLAS_AUTHDB, ATLAS_PUB_TRACK, ATLAS_AUTHDB_HISTORY, GLANCE_PHOTO_SERVICE, GLANCE_PHOTO_HIST) and gives all grants EXCEPT 'references', which can only be given after the tables and constraints were created.
- *db-create-baseline*: This service creates the baseline for every schema i.e. creates sequences, tables, views, mvs, triggers etc.
- *db-grant-references*: This service grants 'references' between ATLAS_AUTHDB and ATLAS_PUB_TRACK, enabling foreign keys to be created referencing columns in one schema to another.
- *db-create-constraints*: This service creates constraints (PK, FK), indexes and MV logs.
- *db-insert-base-member-data*: This service runs the DML necessary to insert the first member into the database.

### Connecting to the database
You can connect to the database using your favorite IDE. Here is how to do it with Datagrip: 
1. Create a new Oracle data source
2. Set these values to be connected as system (DBA privileges):
- URL: jdbc:oracle:thin:@localhost:1521:FREE
- User: system
- Password: ephemeral-db-password

Similarly, you can do the same to connect as any of the users created by the db-prepare-users service, just note that you should change the user accordingly.

### Create PHP container 
To exemplify the 'test' stage of the CI in a scenario closer to 'real life', a simple membership API was built. This API does CRUD operations and has unit, integration and use case tests. The docker service *backend* can be executed so that the PHP container, along with the necessary dependencies, is created.

Tests can be executed within the container by running the following commands (make sure to be in the same directory as composer.json):
composer test-unit
composer test-integration
composer test-use-cases
### Orchestrating services
Since creating the Oracle container, running the migrations and creating the PHP container are all orchestrated via docker compose and each service knows which other service they need to wait be healthy/completed before being executed, running the following command is sufficient to create our application:

```bash
docker compose up
This is equivalent to running the following commands:
```bash
docker compose up backend
docker compose up oracle-db
# Run after the previous one is healthy
docker compose run db-prepare-users
# Run after the previous one is completed
docker compose run db-create-baseline
# Run after the previous one is completed
docker compose run db-grant-references
# Run after the previous one is completed
docker compose run db-create-constraints
# Run after the previous one is completed
docker compose run db-insert-base-member-data
```
# CI/CD
Gitlab's pipeline was configured to enable integration tests leveraging an ephemeral database instance. Also, a stage will be responsible for deploying migrations into the actual integration and production databases.
## Pipeline configuration
Three stages were created:
- **build**: This stage installs the PHP dependencies with composer and adds them to a cache folder, enabling them to persist between jobs.
- **test**: This stage runs the three-layered test structure that our code has, where each layer runs in a separate job. Unit and use-case tests are **not** expected to interact with the database, so they use the normal PHP image from our registry. In the other hand, the integration test job connects to the ephemeral database. This job then consists of three steps: 1) creating a docker container with Oracle's image; 2) running the migrations on this database; 3) running the integration tests agains this database. To enable this, a different Dockerfile had to be created containing PHP and Liquibase (check php-liquibase-docker/Dockerfile). Using this as the main job's image, the job also employs a service so that a joint container running an Oracle image is created. Then, the script waits for the Oracle service to be ready, runs the migrations and executes the tests.
- **deploy**: This stage is responsible for executing the migrations on the integration or production database, depending on which branch the merge was done.
## Extra configuration
For CI efficiency, it's important to push the oracle image to gitlab's registry:

```bash
docker tag container-registry.oracle.com/database/free:23.3.0.0 gitlab-registry.cern.ch/gabrielaleks/localdb/oracle
docker push gitlab-registry.cern.ch/gabrielaleks/localdb/oracle
```

We should also push a custom php-liquibase image. Make sure to follow the procedure:

```bash
docker login gitlab-registry.cern.ch 
cd path-to-dockerfile
docker build -t gitlab-registry.cern.ch/gabrielaleks/localdb/php-liquibase .
docker push gitlab-registry.cern.ch/gabrielaleks/localdb/php-liquibase
```

For the build stage it's important to have set Gitlab's secrets (get the values from another repository):
GIT_USERNAME=***
GIT_PASSWORD=***
# Orchestrating deployment with Liquibase
...

# To do list
List of items to tackle:
- Better way to handle grants?
- Data insertion for minimal system usage -> in progress
- Volume for persistence -> evaluate time to raise container with volume
- Separate database DDL migrations from data insertions (used for testing). There will also be data insertion/updating but only for production environment
- sleep 10 in ci is not optimal -> something equivalent to depends_on?
- Is it necessary to push oracle image to gitlab's registry? Is it faster that way?