init.sh

#!/bin/sh


## MySQL Setup Configuration
MYSQL_DATABASE='mydb_name';
MYSQL_USERNAME='mydb_user';
INCLUDE_DEVELOPER_DEFAULTS=false;


## Get the MySQL files
MYSQL_SETUP_FILES="$(
    echo 'database/'{drop-database,install-schema,apply-defaults}'.mysql';
)";


## Check if we should include the developer defaults
if "$INCLUDE_DEVELOPER_DEFAULTS"; then
    MYSQL_SETUP_FILES="$MYSQL_SETUP_FILES"' database/apply-dev-defaults.mysql';
fi;


## Run the MySQL setup files
cat $MYSQL_SETUP_FILES |
    
    ## Replace the MySQL variables
    sed 's/$MYSQL_DATABASE/'"$MYSQL_DATABASE"'/g' |
    sed 's/$MYSQL_USERNAME/'"$MYSQL_USERNAME"'/g' |
    
    ## Pipe it to mysql(1)
    mysql -u "$MYSQL_USERNAME";


## Exit with the status code of mysql(1)
exit "$?";

install-schema.sql

-- install-schema.mysql
-- Installs the MySQL Schema in the database
-- See docs/MySQL-Guidelines.md for styleguides


-- Create the database
-- This will fail, by design, if the database already exists
CREATE DATABASE `$MYSQL_DATABASE`;
USE `$MYSQL_DATABASE`;
GRANT ALL PRIVILEGES ON `$MYSQL_DATABASE`.* TO '$MYSQL_USERNAME'@'%' WITH GRANT OPTION;


-- Disable foreign key checks while we create tables
-- This lets us create tables out of order
SET FOREIGN_KEY_CHECKS = false;


-- Define the User table
-- This table will have the minimal amount of data for logging in
CREATE TABLE User (
    
    -- Meta fields
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    
    -- Table fields
    email VARCHAR(254) NOT NULL,
    password CHAR(60) NOT NULL,
    
    -- Table constraints
    UNIQUE KEY (email)
     
);


-- Define the UserInfo table
-- This table will have the rest of the info about the user
-- e.g. team id, whether or not they are a team admin, etc
CREATE TABLE UserInfo (
    
    -- Meta fields
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign keys
    user_id SMALLINT UNSIGNED NOT NULL,
        FOREIGN KEY (user_id) REFERENCES User(id)
        ON DELETE CASCADE,
    
    team_id SMALLINT UNSIGNED DEFAULT NULL,
        FOREIGN KEY (team_id) REFERENCES Team(id),
    
    region_id TINYINT UNSIGNED DEFAULT NULL,
        FOREIGN KEY (region_id) REFERENCES Region(id),
    
    -- Table fields
    full_name VARCHAR(255) NOT NULL,
    is_female BOOLEAN DEFAULT NULL,
    is_team_admin BOOLEAN NOT NULL DEFAULT false,
    is_in_need_of_password_change BOOLEAN NOT NULL DEFAULT false,
    
    -- Table constraints
    UNIQUE KEY (user_id)
    
);


-- Define the Team table
-- This table has the team information
CREATE TABLE Team (
    
    -- Meta fields
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    owner_id SMALLINT UNSIGNED NOT NULL,
        FOREIGN KEY (owner_id) REFERENCES User(id),
    
    -- Table fields
    name VARCHAR(64) NOT NULL,
    
    -- Table constraints
    UNIQUE KEY (owner_id),
    UNIQUE KEY (name)
    
);


-- Define the Region table
-- This table contains all of the possible regions users can bike in
-- NOTE: Region(id) is an UNSIGNED TINYINT
CREATE TABLE Region (
    
    -- Meta fields
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    
    -- Table fields
    name VARCHAR(64) NOT NULL,
    
    -- Table constraints
    UNIQUE KEY (name)
    
);


-- Define the LogEntry table
-- This table will contain all of the information of distance travelled
-- NOTE: LogEntry(id) is an UNSIGNED MEDIUMINT
CREATE TABLE LogEntry (
    
    -- Meta fields
    id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    user_id SMALLINT UNSIGNED NOT NULL,
        FOREIGN KEY (user_id) REFERENCES User(id)
        ON DELETE CASCADE,
    
    -- Table fields
    meters_travelled SMALLINT UNSIGNED NOT NULL
    
);


-- Re-enable foreign key checks now that the tables are created
SET FOREIGN_KEY_CHECKS = true;