Developers3

HOME DEMONSTRATIONS DOCUMENTATION SUPPORT SQL AND CHARTS DOWNLOADS

Entrance Charting for SQL Developers - Part 3

In the previous sections we saw how to chart data from SQL scripts, and how "data painting" could be used to mark data in chart and table views. This section describes another technique for "tagging" rows on a time series chart so that they can be picked out on a chart.

WHENLINES

WHENLINES are a specialized chart series type that can associated with columns containing strings. They draw a vertical line through the chart over each tick mark corresponding to a row with a non-empty label in its WHENLINES column. WHENLINES are always labelled, but you can use a string containing a single blank when you want to draw a line without a label.

Confused? Sorry. Maybe this example will help. With this data in table NEW1:

    1         100       ''

    2         200       'Oregon'

    3         300       'California'

this script:

    PLOT LINECHART

        XLABELS, RED LINE, BLUE WHENLINE

    WITH

        COLLAR

    SELECT * FROM NEW1;

draws two lines labeled Oregon and California:

A Real World WHENLINES Example

Freshmeat.net is a web site where open source projects can announce new releases. The site publishes project release statistics in an HTML table one of their web pages. With Entrance, we can "grab" that table data by copying it to the clipboard and then pasting it into a MySQL table.

After copying the data, we go back to the Entrance window and select Edit | Paste data from clipboard...:

We have created a table containing the data we need. Notice the "Release" column which is blank except on the days when there has been a product release:

We're interested in recent URL hits, so we make a line chart:

   PLOT LINECHART

       XLABELS, BLUE LINE

   SELECT Date, `URL hits` FROM freshmeat

       WHERE `Date` > '2007-05-07'

       ORDER BY Date;

then we add labelled WHENLINES to show us when releases were published:

   PLOT LINECHART

XLABELS, BLUE LINES, PLUM WHENLINES

SELECT Date, `URL hits`, substring(`Release`,3,4) FROM freshmeat

       WHERE `Date` > '2007-05-07'

       ORDER BY `Date`;

Finally we make the chart more readable by limiting the number of labels on the x axis and shortening them:

  PLOT LINECHART

ONLY 8 XLABELS, BLUE LINES, PLUM WHENLINES

SELECT substring(Date,7,4), `URL hits`, substring(`Release`,3,4)

       FROM freshmeat

       WHERE `Date` > '2007-05-07'

       ORDER BY `Date`;

Now we have a chart telling a story: Release 81.0 received a lot less attention than other releases. (The reason was that the other releases were announced on the freshmeat front page, and release 81.0 wasn't)

To learn more about Entrance charts

PLOT command syntax is described here: http://dbentrance.com/plotsyntax.html.

Part 1 of this article describes painting data using SQL UPDATEs.

Part 2 of this article describes painting data from the chart window.

There is also a general discussion of chart making here and of the Entrance color naming scheme here.

BACK TO PART 2

Please note: This web page and other Entrance documentation web pages are copyrighted material and have not been released under the terms of the GPL.

MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.

Last Modified June 2, 2007