# DB Guideline

## 1. Purpose
To optimize the updating process of multiple DBs, reduce the possibility of human errors as well as data inconsistencies, and lastly reduce the time needed to update DB

Existing DB List
* Prod: KR001, KR004, KR005, KR006, KR007, KR008
* Stag: OK003,
* Dev: KD001

## 2. SQL file rules
**1. Basic**
* SQL file content
* Add USE DB, Site DB default DB should be [CasinoCash.KD001].
* GO command must be added after USE DB
* The query does not need to add database name. ex:
SELECT [column1], [column2] FROM [schema_name].[table_name]
* Changes to different DB must be split into different files. Example, the queries of One series DB and Site DB needs to be separated into different files.
* Different types of changes also needs to be split into different files, such as:
* Data
* Table
* Stored Procedure
* View
* Function

**2. SQL File in Git**
* File naming
* File extension is *.sql
* Specify the order of execution
* If the *.sql files need to be executed in sequence, add the sequence number in from of the file such as:
01_xxx.sql
02_xxx.sql
...
10_xxx.sql
* File encoding
* UTF-8

* Create directory by task
* Create directory according to the task and place the *.sql file in the directory. If there are multiple files, the order is defined by the file name.

Example:

**2. Site DB update**
* All DB
For update to all DB, **DB:** can be set to **KRALL** or no **DB:**

Example:

With DB:

    /*
    DB:KRALL
    */

    USE [CasinoCash.KD001]
    GO

    {queries}

Without

    USE [CasinoCash.KD001]
    GO

    {queries}

* Designated DB
If only need to update certain DB, set the DB name that needs to be updated separated with comma.

    /*
    DB:KR001,KR002
    */

    USE [CasinoCash.KD001]
    GO

    {queries}

**3. One DB update**
No need to add any comment
Example:

USE [CasinoConfigCenter]
GO

{queries}

**4. Schema and Data changes**
* Schema (CREATE, ALTER)
Includes: Stored Procedure, Function, Trigger, View, Table, Table Column, Table Index

SQL file does not require additional formatting.

* Data (INSERT, UPDATE, DELETE)
The content of the data change file needs to add transactions to ensure data consistency and to handle exception. It is not necessary to add GO

USE DB
GO

BEGIN TRY
    BEGIN TRAN
	
    {queries, does not need GO command}
	
    COMMIT TRAN
    PRINT N'success.'
END TRY
BEGIN CATCH 
    ROLLBACK TRAN
    PRINT N'fail.'
    PRINT CONCAT('ERROR_LINE:',ERROR_LINE(),' ,ERROR_MESSAGE:',ERROR_MESSAGE())
END CATCH