The Orchard Databases
========

The "build" process for a database should bring a target database to a specified version.  Our build tool is [liquibase](http://www.liquibase.org).  Versions are specified via changelog files using [liquibase](http://www.liquibase.org/documentation/changes/index.html) syntax.

## Requirements

* Maven

## Mac Installation

This installation assumes you have [homebrew](http://brew.sh) installed

    brew install maven

## Windows Installation

   [install maven](http://maven.apache.org/guides/getting-started/windows-prerequisites.html)

## Linux Installation (Centos7 - no Maven)

If you just want to install the liquibase command line utility without Maven, just retrieve the liquibase files and extract them.

    mkdir ~/liquibase
    cd ~/liquibase
    wget https://github.com/liquibase/liquibase/releases/download/liquibase-parent-3.5.3/liquibase-3.5.3-bin.tar.gz
    gzip -d liquibase-3.5.3-bin.tar.gz
    tar xvf liquibase-3.5.3-bin.tar

Create a symlink to liquibase

    ln -s ~/liquibase/liquibase /usr/local/bin/liquibase

Or, if you don't have admin access to create symlinks on your machine, create an alias
in your `.bash_profile` or `.zshrc`:

    alias liquibase='~/liquibase/liquibase'


## Linux Installation (Centos7 - with Maven)

First install Maven.  At the time of this writing the most recent version is 3.2.3, but feel free to update to the latest.

    wget http://mirrors.advancedhosters.com/apache/maven/maven-3/3.2.3/binaries/apache-maven-3.2.3-bin.tar.gz
    gzip -d apache-maven-3.2.3-bin.tar.gz
    tar xvf apache-maven-3.2.3-bin.tar
    mv apache-maven-3.2.3/ /opt/maven
    ln -s /opt/maven/bin/mvn /usr/bin/mvn

Then install liquibase. At the time of this writing the most recent version is 3.3.5, but feel free to update to the latest.

    mkdir ~/liquibase
    cd ~/liquibase
    wget https://github.com/liquibase/liquibase/releases/download/liquibase-parent-3.3.5/liquibase-3.3.5-bin.tar.gz
    gzip -d liquibase-3.3.5-bin.tar.gz
    tar xvf liquibase-3.3.5-bin.tar
    ln -s ~/liquibase/liquibase /usr/local/bin/liquibase


Test the installation:

    mvn --version
    liquibase --version

### Usage


#### Setup

For the database you are attempting to build, copy the shadow properties file to a non-shadow file:

    cd ${database}/build
    cp db.properties.shadow db.properties

Then fill in your database credentials and host information.

#### Creating a new file

Create a new file according to the [Formatting and Best Practices](#formatting-and-best-practices).


    changelog/ddl/db-changelog-you-want-to-run.sql

#### Executing an update file

**WARNING**:  This will execute your sql against the database in the properties file.  Be careful when working with shared databases (such as the 192.138.31.138 art_relations db).

    mvn initialize liquibase:update -DchangeLogFile=changelog/ddl/db-changelog-you-want-to-run.sql

#### Executing a rollback file

**WARNING**:  This will execute your sql against the database in the properties file.  Be careful when working with shared databases (such as the 192.138.31.138 art_relations db).

    mvn initialize liquibase:rollback -DchangeLogFile=changelog/ddl/db-changelog-you-want-to-run.sql -Dliquibase.rollbackCount=1

#### Preparing your file for deployment

**WARNING**:  This will execute your sql against the database in the properties file.  Be careful when working with shared databases (such as the 192.138.31.138 art_relations db).

    mvn initialize liquibase:updateTestingRollback -DchangeLogFile=changelog/ddl/db-changelog-you-want-to-run.sql

This will automatically perform an update, then a rollback (automatically determining the rollback count), then an update again (leaving your changes deployed and the DATABASECHANGELOG table updated).

#### Debugging

Debug your liquibase scripts by using

    -DlogLevel=debug

eg.

    mvn initialize liquibase:update -DchangeLogFile=changelog/ddl/db-changelog-you-want-to-run.sql -DlogLevel=debug
    mvn initialize liquibase:rollback -DchangeLogFile=changelog/ddl/db-changelog-you-want-to-run.sql -Dliquibase.rollbackCount=1 -DlogLevel=debug

#### A note about creating ACL entries

A very common task for developers is to add a new ACL entry.  You can find detailed instructions and a convenient template [here](/docs/Adding a new ACL.md)

### Server Information

* Production MySQL server 5.6.26
* AWS Redshift under the hood is PSQL server 8.0.2

### Formatting and Best Practices

See @jessmchung's [Lunch n Learn: Making Changes to the Database](https://docs.google.com/a/theorchard.com/presentation/d/1vZF2peaqbeN3TcTxTNotuek_p5LtVWkbdfA2I1eObdo/edit?usp=sharing) for a quick walk through of General Do's & Don'ts when making a DB Change. 

#### SQL Formatting

##### Must:
* Capitalize [MySQL](http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html) reserved words
* Capitalize [AWS Redshift](http://docs.aws.amazon.com/redshift/latest/dg/r_pg_keywords.html) reserved words
* Never begin new lines with commas
* Have main keywords start a new line
* Prefix rollback tables with "ROLLBACK-"

##### Should:
* Use short, meaningful table aliases
* Try to not prefix table names (e.g. foo not tbl_foo)
* Try to keep table names singular
* Always specify DEFINER for triggers, stored procedures, functions, etc.

Database        | DEFINER
--------------- | ----------------------
art_relations   | 'theorchard'@'10.10.%'
direct_delivery | 'theorchard_dd'@'%'


#### File names and structure

* Name files with their JIRA issue number and something meaningful, for example:
  art_relations/build/changelog/ddl/FRNT-555_alter-track.sql
* Put DDL changes in a database folder's ddl folder
* Put DML changes in a database folder's dml folder

#### Liquibase best practices

* Before submitting a Pull Request *try executing your changes using liquibase*
* [liquibase best practices](http://www.liquibase.org/bestpractices.html)
* [sql format syntax](http://www.liquibase.org/documentation/sql_format.html)

### Working with a new database

* Make sure you have created a new database on QA and Production and have tested your DDL in a dev instance.
* Create a new directory in this repository with the name of your database, ex. `ows_video`
* Create a new directory under `ows_video` titled `build`. 
* Under `build`:
  * Create `database.properties.shadow` file copied from another project.
  * Create `pom.xml` copied from another project. Be sure to rename the database under `<artifactId>`.
  * *Note:* You cannot start submitting DDL or DML for QA and Production until these steps have been completed.
  `db-deploy` jobs in Jenkins will not work until these files have been added.

### Deploying DB Changes

The rest of the section has been moved to Notion: https://www.notion.so/Database-Dos-Don-ts-3742829fd69d49469cba7e22ca80bcad