|
|
Simple-Talk columnist
Published
Sunday, August 23, 2009 8:21 PM
‘Ridiculous,’ he commented, ‘you’ll be saying it is possible to draw graphs from stored procedures next!’ I’d been talking to the guy about keeping an open mind about what was possible at the database level. He didn’t like my 'can-do' attitude to database development. I didn’t bother to tell him it was a ‘dun-it’ attitude: I’d been producing graphs from stored procedures for years.
I still have a guilty twinge when I confess to doing so, as if it were some unspeakable practice that polite society was not quite ready for: but there are reasons for doing this.
In the dark days of old, when a ‘Development Environment’ meant a dark room full of empty coffee cups and congealing pizza on paper plates, the best way of developing any complex application, was to first create a console on which one could display the state of the machine, along with debug information and diagnostic data. Even now, with sophisticated ready-made tools to help, I still do this when designing commercial database applications, but now I use web technology so I can access the system remotely, and I am not reliant on other people to flesh things out. I don’t do it instead of Reporting Services, of course, it is as well as, at a different time in the development process, and for a different audience: mainly myself.
This sort of monitor is guarded by an interface that allows only standard reports, served from a handful of stored procedures. The ASP.NET scripting is almost nothing but a means to deliver the graph so it can be rendered by the browser.
If I can save a few minutes from the task of polling production systems, then the effort of providing all the status information you need on a few screens is worthwhile. For a rapid assimilation of data, and for spotting potential trouble, nothing beats the graphical representation of data.
One would normally choose to use Reporting Services, of if using PHP, one of the excellent graphing packages such as JPGraph,( http://www.aditus.nu/jpgraph/index.php) but occasionally a bought-in product is either too complex for the job, presents security issues, or doesn’t quite do the task you want.
HTML may seem an unpromising material with which to draw a graph, especially if driven from a stored procedure. This is not entirely the case. It can be used for block graphs, but as I’m generally using block graphs for monitoring purposes, this isn’t really a big issue
Let us imagine we want to draw a block graph of visits to a site over a month. HTML allows you to position entities at absolute positions in the browser window With CSS, the only HTML entities one really needs are DIVs and SPANs. One can assign CSS classes to them so that they will behave like all the other tags (other than table-tags) that you’ve become used to. You can also draw blocks with a wide range of fills and borders. For this exercise we’ll just use DIVs and some old-fashioned HRs so as to keep things simple
The principle is that we have code (normally a stored procedure) that generates the entire script for the HTML page. There are several alternative ways of getting this to the browser. You can save the results to a file via a number of methods and send it to the site via FTP (nice and secure, but no good for ephemeral data), or use a server script such as PHP or ASP.NET.
Here is the graph that we'll produce.
....and here, in its simplest form, is the SQL that produces it. Normally, it would be a stored procedure, and I generally hand it to the calling application as a VARCHAR output variable, but I thought I ought to keep things bare-bones here.
SET NOCOUNT ON
DECLARE @SampleData TABLE
(
TheID INT IDENTITY(1, 1),
theDate DATETIME,
Visits INT
)
--we use dummy data of visits per day to a site
INSERT INTO @sampleData SELECT '01 Mar 2009', 15470
INSERT INTO @sampleData SELECT '02 Mar 2009', 15750
INSERT INTO @sampleData SELECT '03 Mar 2009', 14630
INSERT INTO @sampleData SELECT '04 Mar 2009', 8110
INSERT INTO @sampleData SELECT '05 Mar 2009', 9950
INSERT INTO @sampleData SELECT '06 Mar 2009', 13970
INSERT INTO @sampleData SELECT '07 Mar 2009', 14020
INSERT INTO @sampleData SELECT '08 Mar 2009', 20930
INSERT INTO @sampleData SELECT '09 Mar 2009', 14810
INSERT INTO @sampleData SELECT '10 Mar 2009', 13850
INSERT INTO @sampleData SELECT '11 Mar 2009', 8090
INSERT INTO @sampleData SELECT '12 Mar 2009', 7890
INSERT INTO @sampleData SELECT '13 Mar 2009', 10440
INSERT INTO @sampleData SELECT '14 Mar 2009', 11400
INSERT INTO @sampleData SELECT '15 Mar 2009', 12600
INSERT INTO @sampleData SELECT '16 Mar 2009', 12220
INSERT INTO @sampleData SELECT '17 Mar 2009', 15810
INSERT INTO @sampleData SELECT '18 Mar 2009', 9720
INSERT INTO @sampleData SELECT '19 Mar 2009', 8350
INSERT INTO @sampleData SELECT '20 Mar 2009', 11480
INSERT INTO @sampleData SELECT '21 Mar 2009', 11030
INSERT INTO @sampleData SELECT '22 Mar 2009', 13470
INSERT INTO @sampleData SELECT '23 Mar 2009', 19010
INSERT INTO @sampleData SELECT '24 Mar 2009', 17050
INSERT INTO @sampleData SELECT '25 Mar 2009', 8950
INSERT INTO @sampleData SELECT '26 Mar 2009', 9520
INSERT INTO @sampleData SELECT '27 Mar 2009', 12460
INSERT INTO @sampleData SELECT '28 Mar 2009', 14020
INSERT INTO @sampleData SELECT '29 Mar 2009', 12370
INSERT INTO @sampleData SELECT '30 Mar 2009', 13470
INSERT INTO @sampleData SELECT '31 Mar 2009', 16310
--declare all our constants and variables
DECLARE @YHeight INT,
@XLength INT,
@LeftMargin INT,
@BottomMargin INT,
@gap INT,
@BlockWidth INT,
@Scale INT,
@MaxScale INT,
@ii INT,
@iiMax INT,
@BlockInterval INT,
@verticalLabelOffset INT,
@Multiplier INT,
@MaxData INT,
@Points NUMERIC(9,2)
--Firstly we set the basic parameters, which we treat as constants
SELECT @YHeight = 500, --the height of the graph in Pixels
@XLength = 800, --the width of the graph in Pixels
@verticalLabelOffset = -10,--the offset for the Y labels
@LeftMargin = 40, --the left marging in Pixels
@BottomMargin = 30, --the bottom margin
@Gap = 8, --the gap between blocks
@Points = 20 -- the number of label points (roughly!)
--and a little helper table to decide on Y axis labels
DECLARE @ReasonableScale TABLE ( multiplier CHAR )
INSERT INTO @ReasonableScale ( Multiplier )--the units that we label
SELECT '1'
UNION ALL
SELECT '2'
UNION ALL
SELECT '5'
--now we calculate the scale from the data
SELECT @MaxData = MAX(visits)
FROM @sampleData
SELECT @multiplier = FLOOR(CEILING(LEFT(@MaxData, 2) / @points) * @MaxData
/ LEFT(@MaxData, 2))
/* this makes me squirm! The next bit makes me want to put my hands over my eyes. Do not do this at home! */
SELECT TOP 1
@multiplier = ( r.multiplier + REPLICATE('0', LEN(@multiplier) - 1) )
FROM @ReasonableScale r
ORDER BY ABS(@multiplier - ( r.multiplier + REPLICATE('0',
LEN(@multiplier) - 1) )) ASC
/* somehow, we have worked out the scale and the label points */
--so now we can calculate block widths and the vertical scale for the blocks
SELECT
@BlockWidth=((@XLength-@Leftmargin)/COUNT(*))-@gap,
@BlockInterval=@BlockWidth+@Gap,
@Scale=CEILING(@Multiplier*@Points/@yHeight*1.00) FROM @sampleData
--and draw the YAxis labels and lines
DECLARE @yaxes TABLE (html VARCHAR(2555))
--for putting in the HTML
SELECT @ii=0,
@iiMax=20*@Multiplier
WHILE @ii<=@iiMax
BEGIN
IF @ii>@MaxData
BREAK ;
INSERT INTO @YAxes (html)
SELECT '<div class="YAxisLabel" style="bottom:'
+CONVERT(VARCHAR(4), (@ii/@scale)+@VerticalLabelOffset
+@bottomMargin)+'px">'+CONVERT(VARCHAR(8), @ii)
+'</div>'
IF (@ii%(2*@Multiplier)=0
AND @ii>0)
INSERT INTO @YAxes (html)
SELECT '<hr class="AxisLine" style="bottom:'
+CONVERT(VARCHAR(4), (@ii/@scale)+@bottomMargin)
+'px">'
SELECT @ii=@ii+@Multiplier
END
--now we can define each CSS class and calculate the positioning and dimensions
SELECT [html] = '
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Sample Activity chart</title>
<style type="text/css">
<!--
div #graph{
font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
}
#graph .axes{
position: absolute;
bottom: '+CONVERT(VARCHAR(5), @BottomMargin)+'px;
height: '+CONVERT(VARCHAR(5), (@ii/@scale)+@bottomMargin)+'px;
width: '+CONVERT(VARCHAR(5), @XLength-@LeftMargin)+'px;
border-top: 1px solid Silver;
border-right: 1px solid Silver;
border-left: 2px solid;
border-bottom: 2px solid;
left:'+CONVERT(VARCHAR(5), @LeftMargin)+'px;
}
#graph .title
{
position: absolute;
background-color: White;
bottom: '+CONVERT(VARCHAR(5), @YHeight-30)+' px;
height: 30px;
width:500px;
left:'+CONVERT(VARCHAR(5), (@XLength-500)/2)
+'px;
text-align: center;
font: italic normal normal 20px Verdana Geneva Arial Helvetica sans-serif;
}
#graph .column{
position: absolute;
background-color: #efefef;
bottom: '+CONVERT(VARCHAR(5), @BottomMargin+1)+'px;
height: 100px;
width: '+CONVERT(VARCHAR(15), @BlockWidth)
+'px;
border: 1px solid;
font-size: 1px;
}
#graph .XAxisLabel{
position: absolute;
bottom: 10px;
font: italic normal normal 14px Verdana Geneva Arial Helvetica sans-serif;
}
#graph .YAxisLabel{
position: absolute;
bottom: 0px;
left:0px;
text-align: right;
font: italic normal normal 11px Verdana Geneva Arial Helvetica sans-serif;
width:'+CONVERT(VARCHAR(5), @LeftMargin-3)+'px;
}
#graph .AxisLine{
position: absolute;
bottom: 100px;
left: '+CONVERT(VARCHAR(5), @LeftMargin+1)+'px;
width:'+CONVERT(VARCHAR(5), @XLength-@leftmargin)+'px;
font-size: 15px;
border: none;
color: silver;
height: 1px;
}
-->
</style>
</head>
<body>
<div id="graph">
<!-- here is the basic axes and borders of the graph-->
<div class=axes> </div>
'
UNION ALL
SELECT html
FROM @YAxes--the vertical axis labels and lines
UNION ALL
--do the title
SELECT [html] = '<div class=title>Activity chart from '
+CONVERT(CHAR(11), MIN(theDate), 113)+' to '
+CONVERT(CHAR(11), MAX(TheDate), 113)+'</div>'
FROM @SampleData
UNION ALL
--the indvidual columns or blocks
SELECT [html] = '<div class="column" style="left:'+CONVERT(VARCHAR(8), 1
+@LeftMargin+((TheID-1)*@BlockInterval))++'px; height: '
+CONVERT(VARCHAR(4), visits/@scale)+'px;'+'"> </div>'
FROM @SampleData
UNION ALL
SELECT [html] = '<div class="XAxisLabel" style="left:'
+CONVERT(VARCHAR(8), @LeftMargin+((TheID-1)*@BlockInterval))+'px;">'
+CONVERT(CHAR(6), TheDate, 113)+'</div>'
FROM @SampleData
WHERE TheID%4=0
UNION ALL
SELECT [html] = '
</body>
</HTML>'
This produced a simple little HTML page like this. (remember to execute it with the output set to text, and the number of characters displayed in each column set to something reasonable.) When this is rendered
within a browser, it will give you the graph in the illustration above
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Sample Activity chart</title>
<style type="text/css">
<!--
div #graph{
font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
}
#graph .axes{
position: absolute;
bottom: 30px;
height: 555px;
width: 760px;
border-top: 1px solid Silver;
border-right: 1px solid Silver;
border-left: 2px solid;
border-bottom: 2px solid;
left:40px;
}
#graph .title
{
position: absolute;
background-color: White;
bottom: 470 px;
height: 30px;
width:500px;
left:150px;
text-align: center;
font: italic normal normal 20px Verdana Geneva Arial Helvetica sans-serif;
}
#graph .column{
position: absolute;
background-color: #efefef;
bottom: 31px;
height: 100px;
width: 16px;
border: 1px solid;
font-size: 1px;
}
#graph .XAxisLabel{
position: absolute;
bottom: 10px;
font: italic normal normal 14px Verdana Geneva Arial Helvetica sans-serif;
}
#graph .YAxisLabel{
position: absolute;
bottom: 0px;
left:0px;
text-align: right;
font: italic normal normal 11px Verdana Geneva Arial Helvetica sans-serif;
width:37px;
}
#graph .AxisLine{
position: absolute;
bottom: 100px;
left: 41px;
width:760px;
font-size: 15px;
border: none;
color: silver;
height: 1px;
}
-->
</style>
</head>
<body>
<div id="graph">
<!-- here is the basic axes and borders of the graph-->
<div class=axes> </div>
<div class="YAxisLabel" style="bottom:20px">0</div>
<div class="YAxisLabel" style="bottom:45px">1000</div>
<div class="YAxisLabel" style="bottom:70px">2000</div>
<hr class="AxisLine" style="bottom:80px">
<div class="YAxisLabel" style="bottom:95px">3000</div>
<div class="YAxisLabel" style="bottom:120px">4000</div>
<hr class="AxisLine" style="bottom:130px">
<div class="YAxisLabel" style="bottom:145px">5000</div>
<div class="YAxisLabel" style="bottom:170px">6000</div>
<hr class="AxisLine" style="bottom:180px">
<div class="YAxisLabel" style="bottom:195px">7000</div>
<div class="YAxisLabel" style="bottom:220px">8000</div>
<hr class="AxisLine" style="bottom:230px">
<div class="YAxisLabel" style="bottom:245px">9000</div>
<div class="YAxisLabel" style="bottom:270px">10000</div>
<hr class="AxisLine" style="bottom:280px">
<div class="YAxisLabel" style="bottom:295px">11000</div>
<div class="YAxisLabel" style="bottom:320px">12000</div>
<hr class="AxisLine" style="bottom:330px">
<div class="YAxisLabel" style="bottom:345px">13000</div>
<div class="YAxisLabel" style="bottom:370px">14000</div>
<hr class="AxisLine" style="bottom:380px">
<div class="YAxisLabel" style="bottom:395px">15000</div>
<div class="YAxisLabel" style="bottom:420px">16000</div>
<hr class="AxisLine" style="bottom:430px">
<div class="YAxisLabel" style="bottom:445px">17000</div>
<div class="YAxisLabel" style="bottom:470px">18000</div>
<hr class="AxisLine" style="bottom:480px">
<div class="YAxisLabel" style="bottom:495px">19000</div>
<div class="YAxisLabel" style="bottom:520px">20000</div>
<hr class="AxisLine" style="bottom:530px">
<div class=title>Activity chart from 01 Mar 2009 to 31 Mar 2009</div>
<div class="column" style="left:41px; height: 386px;"> </div>
<div class="column" style="left:65px; height: 393px;"> </div>
<div class="column" style="left:89px; height: 365px;"> </div>
<div class="column" style="left:113px; height: 202px;"> </div>
<div class="column" style="left:137px; height: 248px;"> </div>
<div class="column" style="left:161px; height: 349px;"> </div>
<div class="column" style="left:185px; height: 350px;"> </div>
<div class="column" style="left:209px; height: 523px;"> </div>
<div class="column" style="left:233px; height: 370px;"> </div>
<div class="column" style="left:257px; height: 346px;"> </div>
<div class="column" style="left:281px; height: 202px;"> </div>
<div class="column" style="left:305px; height: 197px;"> </div>
<div class="column" style="left:329px; height: 261px;"> </div>
<div class="column" style="left:353px; height: 285px;"> </div>
<div class="column" style="left:377px; height: 315px;"> </div>
<div class="column" style="left:401px; height: 305px;"> </div>
<div class="column" style="left:425px; height: 395px;"> </div>
<div class="column" style="left:449px; height: 243px;"> </div>
<div class="column" style="left:473px; height: 208px;"> </div>
<div class="column" style="left:497px; height: 287px;"> </div>
<div class="column" style="left:521px; height: 275px;"> </div>
<div class="column" style="left:545px; height: 336px;"> </div>
<div class="column" style="left:569px; height: 475px;"> </div>
<div class="column" style="left:593px; height: 426px;"> </div>
<div class="column" style="left:617px; height: 223px;"> </div>
<div class="column" style="left:641px; height: 238px;"> </div>
<div class="column" style="left:665px; height: 311px;"> </div>
<div class="column" style="left:689px; height: 350px;"> </div>
<div class="column" style="left:713px; height: 309px;"> </div>
<div class="column" style="left:737px; height: 336px;"> </div>
<div class="column" style="left:761px; height: 407px;"> </div>
<div class="XAxisLabel" style="left:112px;">04 Mar</div>
<div class="XAxisLabel" style="left:208px;">08 Mar</div>
<div class="XAxisLabel" style="left:304px;">12 Mar</div>
<div class="XAxisLabel" style="left:400px;">16 Mar</div>
<div class="XAxisLabel" style="left:496px;">20 Mar</div>
<div class="XAxisLabel" style="left:592px;">24 Mar</div>
<div class="XAxisLabel" style="left:688px;">28 Mar</div>
</body>
</HTML>
Now, as you can imagine, my code doesn't look like this. I've simplified things down. I'd do the weekends in a different color, I'd probably do a moving average, I'd usually have a stored procedure that rendered several graphs, taking the data as XML. Once you get started, it is amazing what you can do to enhance the graph to make it easier to spot problems, and other things that need investigating. With this sort of reporting system, I've managed to detect attempts at intrusion, and several database issues that I've caught before they became problems. Sure, it has been very useful, but I still feel a twinge of guilt when I explain what I've done.
Somehow writing graphs in stored procedures doesn't seem natural.
|
|