DBPlot

DBPlot is some ruby glue between MySQL and R. It generates and runs R based on a SQL-like DSL. It is still very early in development. The goal is to make it simpler to get common plots out of a database. For more complex examples, there will be an export mode, which will let you save the output R source file for further editing.

Installation

sudo gem install dbplot

Usage

Usage: dbplot [options]
      --help                       This message
  -v, --verbose                    Run verbosely
  -h, --host HOST                  MySQL Host
  -u, --user USER                  MySQL User
  -p, --password PASSWORD          MySQL Password
  -d, --database DATABASE          MySQL Database
  -q, --query QUERY                dbplot query
      --version                    Print version info and exit
      --dry-run                    Print but do not execute. Implies -v.

Non-interactive

dbplot -d database -u username -p password -q "plot yvar vs xvar from tablename"

This would generate and execute a MySQL query (select yvar, xvar from tablename), pass it to R, and use ggplot2 to generate the appropriate plot (qplot(xvar, yvar, data=data.from.mysql)) and save it to a pdf (by default, out.pdf).

Interactive

If you skip the -q flag, you’ll land on a dbplot prompt, which lets you execute multiple dbplot commands. dbplot commands can be multiple lines and will not evaluate until a line ends with a semicolon (much like the mysql command). Type “exit” or “quit” to get out.

Syntax

PLOT y_variable [AS y_variable_alias] VS x_variable [AS x_variable_alias] FROM table_name
  [COLOR BY color_variable [AS color_variable_alias]]
  [FACET BY facet_variable [AS facet_variable_alias]]
  [INTO output_filename.pdf]

Project Goals:

Support as much of common interactions between a database and ggplot2, including joins, faceting, grouping, coloring, summarization, different geoms, etc.

Lock down the security so this can be run in a sandbox through a web interface; users could type in queries and get plots back against a database.