This package has been deprecated

Author message:

not

ims-db-migrations

Development Environment Databases

Uses Vagrant to create an Ubuntu box with MongoDB, MySQL, RabbitMQ, Redis, and Elasticsearch installed, and loads development data migrations.

Pre-requisites

Before launching your DB environment, you must install VirtualBox 5.x or VMWare as well as Vagrant. All of these software packages provide easy-to-use visual installers for all popular operating systems.

To use the VMware provider, you will need to purchase both VMware Fusion / Workstation and the VMware Vagrant plug-in. Though it is not free, VMware can provide faster shared folder performance out of the box. VirtualBox is free.

Note: If you are using Windows, you may need to enable hardware virtualization (VT-x). It can usually be enabled via your BIOS. If you are using Hyper-V on a UEFI system you may additionally need to disable Hyper-V in order to access VT-x.

• Install Vagrant
  • Should be a straightforward download and install.
  • 11/10/16 - For Mac Vagrant 1.8+ and VirtualBox 5.1+, you may also need to sudo rm /opt/vagrant/embedded/bin/curl. Otherwise try lower versions like Vagrant 1.7.4 and VirtualBox 5.0.28. Vagrant's own curl lib tends to fail at vagrant up when downloading the box.
• Install VirtualBox
  • Should be a straightforward download and install.
• Note: MongoDB, MySQL, and RabbitMQ installed on virtual machine during vagrant set-up
  • MongoDB
  • MySQL
  • RabbitMQ

Setup

A note about db-migrate: Do not install globally. There is a big syntax shift between 0.9, 0.10 and 0.10 beta.

# Install
  $ npm install

# Copy database configs, customize if necessary
  $ cp mongo/database.dist.json mongo/database.json
  $ cp mysql_main/database.dist.json mysql_main/database.json

# Preferred Install/Start - runs `vagrant up` and refreshes migrations for you
  $ ./run.sh

  # Install version 2 - Each command independently
    $ vagrant up
    $ node node_modules/db-migrate/bin/db-migrate up --config "./mysql_main/database.json" -m "./mysql_main/migrations"

# Refresh databases
  $ ./refresh.sh

End of the vagrant up provision (long terminal text after vagrant up) should end with JSON outputs if mongo was correctly established. If no JSON and/or vagrant up closes with something like "Vagrant exited with exit code 0" - run vagrant provision and that should fix it!

Note: The mongo user creation step is called after an arbitrary 20 seconds to allow the service time to restart after changing the etc/mongod.conf - depending on your machine this may still not be long enough. You can either elongate the sleep time in the mongo-install.sh file or use vagrant ssh followed by mongo to run lines 8-10 starting at the db.createUser to achieve the same effect.

Services

MySQL

MySQL is installed as part of the Vagrant Provision.

# Run "vagrant up" from this directory to get MySQL up (and "vagrant suspend || vagrant halt" to bring it down)

Connect via command line

mysql -h 33.33.33.10 -uroot -proot -P 3306

Connect via MySQL Workbench, configure a new connection

host: 33.33.33.1
port: 3306
user: root
password: root

MySQL created with "ImpactDataX" as Database and "root" as the User. Configure your app to use it, like with appserv2 in config.json file

"mysql_main":{
    "host":"33.33.33.1",
    "port": 3306,
    "user":"root",
    "password":"root",
    "database":"ImpactDataX"
},

How to make a MySQL migration

Naming Convention

• Numbers
  • Numbers indicate run cardinality - Whether certain migrations should be run before others. For example, some listbuilder tables like lists depend on the users table being created first.
• A
  • A indicates that the migration contains an ALTER command, modyfying one of the existing tables.

# http://umigrate.readthedocs.org/projects/db-migrate/en/v0.9.x/

# this makes an empty migration called "sample_migration_name"
node node_modules/db-migrate/bin/db-migrate create sample_migration_name --config "./mysql_main/database.json" -m "./mysql_main/migrations"

# paste mysql workbench dumps into the sql files

Run all MySQL migrations

node node_modules/db-migrate/bin/db-migrate up --config "./mysql_main/database.json" -m "./mysql_main/migrations"

How to seed MySQL

# run a migration that includes data

Revert MySQL

Notes:

• Down
  • Only rolls back the most recent migration, 1 at a time.
• Reset
  • Deletes all databases and removes them from the migrations table.

node node_modules/db-migrate/bin/db-migrate down --config "./mysql_main/database.json" -m "./mysql_main/migrations"

# or

node node_modules/db-migrate/bin/db-migrate reset --config "./mysql_main/database.json" -m "./mysql_main/migrations"

GUI

MySQL Workbench and Sequel Pro are both recommended.

MongoDB

MongoDB is installed as part of the Vagrant Provision.

Connect

# From your terminal: 
mongo listbuilder --host 33.33.33.10 -umyUserAdmin -pImpact123 --authenticationDatabase "admin"

# From Vagrant box (i.e. after running `vagrant ssh`)
mongo listbuilder -u "myUserAdmin" -p "Impact123" --authenticationDatabase "admin"

Vagrant Provision Creates:

• Users:
  • admin (root)
  • listbuilder (read/Write on listbuilder DB only)
  • reports (read/Write on reports DB only)
• Databases:
  • listbuilder
  • reports

Make a database (listbuilder and reports are created for you)

use newDatabase

Create a user (myUserAdmin, lisbuilder, and reports users created for you)

db.createUser(
    {
      user: "userName",
      pwd: "passwordValue",
      roles: [
         { role: "readWrite", db: "databaseUserHasPermissionsOn" }
      ]
    })

Reference the MongoDB in the dev area of your project.

# ap2 listbuilder sample config.json for mongodb

"mongodb":{
    "host":"33.33.33.10",
    "port":27017,
    "user":"myUserAdmin",
    "password":"Impact123",
    "database":"admin"        # authentication Database
},

Patch to put listbuilder and reports users in admin collection instead of directly on collections

vagrant ssh

mongo listbuilder -u "myUserAdmin" -p "Impact123" --authenticationDatabase "admin"

# clear previous

use listbuilder
db.dropUser("listbuilder")

use reports
db.dropUser("reports")

# create users on admin collection instead

use admin

db.createUser({ 
  user: "listbuilder",
  pwd: "impact123",
  roles: [ 
    { role: "read", db: "admin" },
        { role: "readWrite", db: "listbuilder" },
    ] 
})

db.createUser({ 
  user: "reports",
  pwd: "impact123",
  roles: [ 
    { role: "read", db: "admin" },
        { role: "readWrite", db: "reports" },
    ] 
})

How to make a MongoDB migration

Seed it by putting insert/createCollection statements in logically names JS files

mongo
load('./Projects/ims-db-migrations/mongo/migrations/_addresses.js')
db.addresses.find()

The following does not work

# http://umigrate.readthedocs.org/projects/db-migrate/en/v0.9.x/

# node node_modules/db-migrate/bin/db-migrate create lbconsumer --config "./mongo/database.json" -m "./mongo/migrations"
# node node_modules/db-migrate/bin/db-migrate up --config "./mongo/database.json" -m "./mongo/migrations"


# update JS file

exports.up = function (db, callback) {
  db.createTable('pets', {
    id: { type: 'int', primaryKey: true },
    name: 'string'
  }, callback);
};

exports.down = function (db, callback) {
  db.dropTable('pets', callback);
};

Mongo Tools

Mongo Dump

mongodump --db=listbuilder --host 33.33.33.10 -umyUserAdmin -pImpact123 --authenticationDatabase "admin" -o mongodump

Mongo Top

mongotop -h 33.33.33.10:27017 -u 'myUserAdmin' -p 'Impact123' --authenticationDatabase 'admin'

Mongo Stat

mongostat -h 33.33.33.10:27017 -u 'myUserAdmin' -p 'Impact123' --authenticationDatabase 'admin'

GUI

Robomongo seems to be a pretty functional GUI for accessing the DB in a visual way.

RabbitMQ (June 2016 - RB)

Admin Panel

You can access the RabbitMQ admin panel via a web browser using the url http://33.33.33.10:15672/ OR localhost:15672/ and the traditional admin username/password pair.

As this serves as our messaging/task running service, there are no seed to pre-load, but an example implementation has been provided in the rabbitMQ subdirectory.

Recommended usage:

With the vagrant box running, initialize as many listeners as you like using separate terminals using the following command: $ DEBUG=* node ./rabbitMQ/worker.js You should see terminal output showing a successful connection and that the worker is waiting for an input.

Then you can run an instance of $ DEBUG=* node ./rabbitMQ/send.js and see the message sent from one terminal and recieved round-robin by the workers you started up before.

Redis (June 2016 - RB)

Looking into using Redis in the site-services repo as a cache/DB service to replace flat file storage of business logic.

If you install the redis-cli (installed during redis install brew install redis) you can access the service from the host machine using: $ redis-cli and confirm connection using the $ ping -> PONG response.

Not many GUI options, but after trying a few FastoRedis seems ok.

Elasticsearch (August 2016 - MT)

Basic install of Elasticsearch. No GUI option yet.

Custom config values (in this repo /elasticsearch/elasticsearch.yml):

cluster.name: es-ims-db-migrations
node.name: es-node-1 
network.host: 33.33.33.10

You can test if Elasticsearch is running/responding by issuing the following command:

curl 'http://33.33.33.10:9200/?pretty'

Some common commands:

Check overall health of the elasticsearch cluster:

curl -X GET 'http://33.33.33.10:9200/_cluster/health?pretty'

Get status of all indicies:

curl -X GET 'http://33.33.33.10:9200/_all/_stats?pretty'

Get stats about individual nodes (for more detailed debugging/info):

curl -X GET 'http://33.33.33.10:9200/_nodes/stats?pretty'