Using An External Database For Admin-UI

I ran across this great project in GitHub called Admin-UI.  This project solves a great problem by compiling Cloud Foundry operations data into one spot.  It also collects metric data like number of users, apps, orgs, spaces, etc.  The metrics data is perfect information to show adoption and growth to leadership.

When I deployed this application into my foundation, everything worked great and I was collecting daily metrics.  After the foundation was updated with new patches and the app instance restarted, I noticed the metrics were wiped out.  Looking more into the app, the stats data is being kept on a local sqlite database instance.  When the app instance redeployed, the sqlite database was not kept.  I couldn't have my data be wiped out so I decided to move this stats data to an external database.  Here is how I did it.

Database Setup
In my foundation, I can bind to a MySQL database so I decided to try creating a database there and using it for the app.  I manually created the database using the following mysql commands.

create database databasename;
create user 'username'@'%' identified by 'password';
grant all privileges on databasename.* to 'username'@'%';
flush privileges;

Of course use whatever databasename, username, and password you want in your setup.  Now that we have a blank database, we need to update the config file the admin-ui app will use.  An example of the config file is located in the project at config/default.yml.  The line that needs to be updated in the file is called db_uri.  The default setting is "db_uri: sqlite://data/store.db".  It now needs to read like the following example.

db_uri: mysql2://username:password@ip_address:3306/databasename

One other modification I made before we deploy the app to the foundation is in the file called; 20140530_new_initial_schema.rb.  The file is located in the project at db/migrations.  This database migration script will set the schema for the database during the deployment.  I changed the the column called "timestamp" from Timestamp to BigInt.  Below is the change you need to make.

Old:
Timestamp :timestamp

New:
BigInt :timestamp

The reason I changed this column type is due to how the timestamp needs to be formatted.  When pulling the current stats in the app, the time is formatted into milliseconds.  To be able to hold that big of a number, the type needed to be changed.

Deploy The App
Now that the database has been created, the config is updated, and the database migration script has been modified, you can deploy the app to the foundation.  It will use the ruby buildpack during the deploy.  I recommend that you have the latest ruby buildpack installed before deploying.

After the deploy has finished, you can verify the MySQL database instance has the correct tables in there by running the following mysql commands.  You can also verify that the timestamp column type has been updated to BigInt.

use databasename;
show tables;
show columns from stats;

Updating Timestamp to Milliseconds
Before collecting stats in the database, we want to make sure that the time is converted to milliseconds.  If it is not in milliseconds, it will not display properly on the website.  The conversion can be performed before an insert into the database by using a trigger in MySQL.  Here is a sample trigger you can use (Make sure you are using the admin-ui database instance in MySQL before running these commands).

DELIMITER //
DROP TRIGGER IF EXISTS stats_insert_trigger//
CREATE TRIGGER stats_insert_trigger
BEFORE INSERT ON stats
FOR EACH ROW
BEGIN
IF NEW.timestamp = '0000-00-00 00:00:00' THEN
SET NEW.timestamp = unix_timestamp(NOW()) * 1000;
END IF;
END;//
DELIMITER ;

Make sure to press Enter after that last line to set the delimiter back to a semicolon.  Now you can test a new database entry by selecting "Create Stats" on the Stats tab of the Admin-UI.  Click OK on the pop-up.  You will have a new line in the database in the stats table.  To view this new line, you can run the following mysql command.

use databasename;
select * from stats;

You will see that the timestamp column now has the time in milliseconds.

Summary
With the stats data being held in an external database, the app can be restarted or redeployed in the foundation without losing any information.  I have not tried using a different external database like Postgres.  I used what I had available at the time.  If you get Postgres to work, let me know.

~RRRII