February 25th, 2017 by ryan
1.42 is now release and available to download. The biggest change is that saved files will no longer have usernames/pasaswords stored in them. This is to allow sharing amongst a team. Now sqlDashboards stores one default login as part of the software and attempts to use that with any opened files. If the login does not work it prompts the user. This can be changed under:
Full Change List:
- Allow saving .das without username/password to allow sharing. Prompt user on file open if cant connect to server.
- Bugfix: Allow resizing of windows within sqlDashboards even when “No table returned” or query contains error.
- Allow setting File->title and use file name if file is Untitled.
- If query is wrong and missing arg or something. Report why, report the reason.
- KDB Database Only: Stop wrapping JDBC queries as we dont want kdb to use the standard SQL handler. We want to use the q) handler.
November 20th, 2016 by ryan
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
- 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
- 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.
June 28th, 2016 by ryan
sqlDashboards 1.41 has been released and is available to download.
- Support custom JDBC drivers and Authentication Services
- Fix refresh rate display bug when widget is selected
- Fix sqlchart.bat to allow running from any current path
The custom JDBC/security has been asked for a couple of times but it’s inclusion at this stage is experimental. We do have a few customers using it with qStudio and documentation is available here. If this interests you get in touch.
February 15th, 2016 by ryan
SqlDashboards 1.40 has been released and can now be downloaded.
Major changes include:
- Allow user specified refresh rates
- Speed optimization to prevent chart redraws when query result unchanged.
- Added built-in demo for MySQL
- Fixed sd_ column prefix case bug
- Fix UTF-8 bug on saving .das files.
- Fixed sqlChart watermark bug on linux
December 11th, 2015 by ryan
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
That last line, opens the configuration for editing. For gmail you will have details like this:
account-hook imap://gmail/ “set
set imap_user = 'email@example.com'
set imap_pass = 'PASSWORD'
set smtp_url = 'smtp://firstname.lastname@example.org@smtp.gmail.com:587/'
set smtp_pass = 'PASSWORD'
set from = 'email@example.com'
set realname = 'John'
set folder = 'imaps://imap.gmail.com:993'
set mbox= '+Inbox'
set spoolfile= '+Inbox'
set postponed= '+[Gmail]/Drafts'
set trash = '+[Google Mail]/Trash'
set move = no
To test this, first run “mutt”, it should display your gmail inbox. Then send an email using the command:
mutt -s "test" firstname.lastname@example.org <<< "test message"
Install sqlChart and generate pie charts
- Download sqlDashboards.jar
- Copy it to your /usr/local/bin directory
- Create an alias so we can run it with a shorter name
- Run command to generate chart
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" email@example.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:
April 20th, 2014 by ryan
Let’s look at how we can display real-time and historical sensor data for Household Electrical usage using sqlDashboards.
The historical data is taken from the UCI Machine Learning Repository. This is a single CSV file with 1 minute readings between December 2006 and November 2010. i.e.
The Sensor Data Sql Dashboard
Time Series Data Format
The data came in the following format:
Where the columns have the following meaning:
- date: Date in format dd/mm/yyyy
- time: time in format hh:mm:ss
- global_active_power: household global minute-averaged active power (in kilowatt)
- global_reactive_power: household global minute-averaged reactive power (in kilowatt)
- voltage: minute-averaged voltage (in volt)
- global_intensity: household global minute-averaged current intensity (in ampere)
- sub_metering_1: energy sub-metering No. 1 (in watt-hour of active energy). It corresponds to the kitchen, containing mainly a dishwasher, an oven and a microwave (hot plates are not electric but gas powered).
- sub_metering_2: energy sub-metering No. 2 (in watt-hour of active energy). It corresponds to the laundry room, containing a washing-machine, a tumble-drier, a refrigerator and a light.
- sub_metering_3: energy sub-metering No. 3 (in watt-hour of active energy). It corresponds to an electric water-heater and an air-conditioner.
Creating the SQL Dashboard Charts
To create the live charts I:
- Imported the data to MySQL database.
- Created a java process to insert live sensor data.
- Opened sqlDashboards
- Added a time series monthly graph and using the sql query:
select STR_TO_DATE(DATE_FORMAT(Date, '%Y-%m-01'),'%Y-%m'),AVG(Global_active_power),AVG(Global_reactive_power) FROM power GROUP BY DATE_FORMAT(Date, '%Y-%m-01')
- Added a daily time series rollup graph and using the sql query:
CREATE TABLE daily AS select Date,AVG(Global_active_power),AVG(Global_reactive_power) FROM power WHERE Date>'2009-11-11' GROUP BY Date;
- Added a bar chart showing the different meter readings as bar charts per year:
select CAST(YEAR(Date) AS CHAR(4)) AS Year,AVG(Sub_metering_1),AVG(Sub_metering_2),AVG(Sub_metering_3) from power GROUP BY YEAR(Date)
- Added further queries to show the live data for today…
This gave me the final dashboard:
April 18th, 2014 by ryan
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.
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.