# DB Schema Report

DB Schema For Development Environment

Project Code: X43

## gitlab-ci prerequisites

1.  gitlab CI variables
    - CI_GIT_TOKEN
    - GITLAB_USER_EMAIL
    - GITLAB_USER_NAME

2.  gitlab runner

---

## File Naming Convention (Project Code: X43)
001 - Schema level

1XX - Table (auto generate from DEV_7XX)

3XX - Init data (auto generate from DEV_890)

5XX - Stored procedure (auto generate from DEV_8XX, except DEEV_890)

6XX - Migration

DEV_7XX - Version patch**

DEV_8XX - Release patch**

DEV_890 - Release patch init data**

DEV_899 - Release patch grant user account access**

910 - Grant user account access (auto generate from DEV_899)

990 - Generate init data for file 3XX

##### ** Maintain by developer
##### ** Note: During version release, The 7XX, 8XX files will be created and clear after version released.
##### ** If there is any new changes, developer should copy from the 3XX, 5XX, 910 files and rename it to DEV_8XX_*.sql
* File 3XX_*.sql --> DEV_890_*.sql
* File 5XX_*.sql --> DEV_8XX_*.sql
* File 910_*.sql --> DEV_899_*.sql

## Release Version
1. Create a release branch.
2. Remove the DEV_MODE.txt to enable release mode.
3. Developer will move the release content to new 7XX or 8XX files.
4. Once the content has been confirmed, run `make test` to ensure no syntax error.
5. Commit and create merge request to `master` branch.
6. On the `master` branch, run `make release` to generate the 1XX, 3XX, 5XX, and 910 files. It will generate init data for 300 files by 990 file execution.
7. Commit with commit message that contain `run-major-release`, `run-minor-release`, `run-patch-release`, or `run-release`. The `run-release` is an alias to `run-patch-release`.

## Create Version Tag
Write commit message that contain `run-tag`, a pre-release version tag will be created.

## View Full Script
`make view-init`

## View Patch Script
`make view-patch FROM=<version> TO=<version>`

## View Patch Script File
`make view-patch-file FROM=<version> TO=<version>`

## Lint & Test Locally
`make test`

##### ** In a development branch, the `DEV_MODE.txt` file should be created in order for the test process to use `DEV_7XX_*.sql` and `DEV_8XX_*.sql` files.

##### ** Prerequisite: `docker`, `docker-compose` & `jq` must be installed

## View All Version
`make versions`

## Generate release tables, stored procedures, init data, and grant permission files
`make release`

## Check duplicated stored procedure in 8XX file
`make dup`

## MYSQL console
`make cli`

## Initialization Process
Only range of files start with prefix (`001, 1XX, 3XX, 5XX, 6XX, 910`) will be include for the initialization process.

## Patch Process
Only range of files start with prefix (`7XX`, `8XX`) will be include for the patch process.
The `7XX` is a `version patch` file, it should reflect the changes from previous version. Basically for Table changes only. Please note, 7XX & 8XX files (except 890 & ) will be auto-deleted once version has been released.
The `8XX` is a `release patch` file, it should reflect the changes of current release. Basically for SP changes
The `890` is a init data file
The `899` is for grant database user permission command

## Developer work's
Tips: Easily lookup for your changes at any specific time range

```
SELECT db,name, created,modified,definer 
FROM mysql.proc 
WHERE db like 'sig_%'
AND definer = 'ali@%'
order by modified desc;
```

## RollBack Script
Developer need to prepare a RB script for each new or going to modify database object if automation deployment failed we are still able to restore to current state.
Developer need to set prefix for all the rollback file with prefix (`8xx_RB`) and (`7xx_RB`) 

## Init Data Script
Init data script have to define with prefix (`890` or `990`)

---
devbot chat: 0