sqlDashboards Blog

sqlDashboards supports Graphs,Nodes and State Diagrams

sqlDashboards is specialised for real-time data visualization. Often this includes monitoring the state of orders, whether that’s baskets of goods being ordered online or financial transactions, we want to help you see what state those items are in. To support visualizing this data we’ve integrated the most powerful open source graphing: DOT and graphviz. If you install graphviz and make it available in your path, you can automatically get sqlDashboards to generate graphs from tabular data, like so:

Notice to get the labels and styling you use a column called label or style respectively. The full format is detailed on the sqlDashboards example page.

Creating Highlighted Rows

This is a quick video tutorial showing how to add highlighted rows to a dashboard as a few people had emailed asking this:

To highlight a row we use specially named sqlDashboards columns SD_ that affect how the result is displayed but are not shown in the table

1. Add a column called SD_BGCOLOR specifying a color to use for the background of that row e.g. ‘green’ or using HTML hex triplet notation e.g. #224466
2. Add a column called SD_FGCOLOR specifying the color to use for the foreground of that row

Hex triplet is a six-digit, three-byte hexadecimal number used in HTML, CSS, SVG, and other computing applications to represent colors. The bytes represent the red, green and blue components of the color. One byte represents a number in the range 00 to FF (in hexadecimal notation), or 0 to 255 in decimal notation. This represents the least (0) to the most (255) intensity of each of the color components.

Using Case When

Commonly you will probably want to use SQL’s CASE-WHEN to alternate colors depending on an existing column value. e.g.

[raw]
<br /> SELECT *,<br /> CASE WHEN price>100 THEN ‘#DDFFDD’ ELSE ‘white’ END AS SD_BGCOLOR,<br /> CASE WHEN price>100 THEN ‘#225522’ ELSE ‘red’ END AS SD_FGCOLOR<br /> FROM stock<br />
[/raw]

Sending an email with pie charts and graphs

The following steps explain how to send an email with chart image attachments from the command line. First we must install the necessary email tools in linux:

Install Email Sending on Linux


apt-get install mutt
vi ~/.muttrc


That last line, opens the configuration for editing. For gmail you will have details like this:

account-hook imap://gmail/ “set

set move = no
set timeout=15
auto_view text/html


To test this, first run “mutt”, it should display your gmail inbox. Then send an email using the command:
mutt -s "test" receiver@domain.com <<< "test message"

Install sqlChart and generate pie charts

1. Download sqlDashboards.jar
2. Copy it to your /usr/local/bin directory
3. Create an alias so we can run it with a shorter name
4. Run command to generate chart


wget http://www.sqldashboards.com/files/sqldashboards.jar
mv sqldashboards.jar /usr/local/bin/
alias sqlchart="java -cp /usr/local/bin/sqldashboards.jar com.timestored.sqldash.SqlChart"

sqlchart --servertype mysql --chart piechart --user USERNAME --password PASSWORD --database DATABASENAME--out pie.png --execute "select DATE(time),count(*) from qstudioreg group by DATE(time) ORDER BY DATE(time) DESC limit 9"


Voila we have our chart:

Send email with attachments

Use mutt to send the email, with -a to specify attachments like so:
mutt -s "sql pie chart reports" ryan@timestored.com -a pie.png <<< "Here is your database report"

Done. So we successfully configured mutt for sending email, then generated our pie chart from mysql using sqlchart then we emailed it as an attachment. If you have any problems please let me know and I'll try to help. Here's the email in my inbox:

SQL Pie Chart MYSQL

I came across this interesting post on ASCII pie charts for MySQL. I thought I’d use our command line tool sqlChart todraw the database pie chart instead.

[raw]
<br /> c:\Program Files (x86)\TimeStored.com\sqlDashboards>mysql -s mysql -u root –password=pp -D sakila<br /> -e “SELECT * FROM (SELECT table_name,data_length + index_length AS size FROM information_schema.TABLES WHERE table_schema = ‘sakila’) a<br /> WHERE SIZE IS NOT NULL ORDER BY SIZE DESC LIMIT 8”</p> <p>table_name size<br /> rental 2850816<br /> payment 2228224<br /> inventory 376832<br /> film_text 325440<br /> film_actor 278528<br /> film 278528<br /> customer 131072<br /> staff 98304</p> <p>c:\Program Files (x86)\TimeStored.com\sqlDashboards>sqlchart -s mysql -u root –password=pp -D sakila -e “SELECT *<br /> FROM (SELECT table_name,data_length + index_length AS size FROM information_schema.TABLES WHERE table_schema = ‘sakila’) a<br /> WHERE SIZE IS NOT NULL ORDER BY SIZE DESC LIMIT 8” -o c:\temp\aa\sizes.png -c piechart<br /> c:\temp\aa\sizes.png<br />
[/raw]

Simply changing our mysql command to sqlchart and adding the additional arguments
"-o c:\temp\aa\sizes.png -c piechart"
gives us the following SQL Pie Chart:

MySQL Sakila Table Sizes Pie Chart

Notice because sqlchart’s command line arguments closely resemble the mysql command line options, we can quickly change the calls from querying to graphing.