tag:blogger.com,1999:blog-82569535011004476922024-03-14T08:21:59.752+11:00Formal DevelopmentOpinions on software design and developmentEli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-8256953501100447692.post-29789919811258627882017-05-13T19:56:00.001+10:002017-05-13T19:56:59.853+10:00Autonomous Cars - Completely Obvious PatentsI'm very excited about autonomous vehicles. The transition to self-driving cars will change society as much as the arrival of trains or the transition from horse drawn carriages to cars. I don't know if ten years from now my kids will need driving licenses, but I'm pretty sure my grandkids will be hard pressed to find a use for them even if they had them.<br />
One thing I'm not excited about is a new round of litigation with completely obvious patents. The Internet brought us the "like X but on the internet" software patents, autonomous cars will bring us "like X but with self-driving cars" patents.<br />
It is probably too late change this, I'm sure the patent mills are busy churning completely obvious patents to use in the next couple of decades.<br />
In this post, I'll list some of the ways self-driving cars will change the way we do things. While many of the things listed here are my own ideas I'm under no illusions that these ideas are novel, in fact, that is the whole point. Maybe if there were more, this is how things will change, posts by lay-people about new technologies we'd have less obvious patents.<br />
<br />
<h3>
The Road to Autonomous Vehicles</h3>
We won't have autonomous vehicles on all our roads overnight. The transition will take time, years certainly maybe decades.<br />
Here's one way this can happen.<br />
It will start small, autonomous self-driving options will appear in regular vehicles as luxury options and will trickle down to more basic models; companies will replace drivers with autonomous cars. At first, autonomous cars will require supervision, either a driver present in the vehicle or remotely from a control centre.<br />
Autonomous cars that communicate with one another have advantages over human drivers. Caravans of autonomous cars can travel safely at fast speeds while maintaining short distances between the cars better utilizing the road. They can also negotiate merging and traversing intersections efficiently without the need of traffic lights.<br />
At some point some roads will become self-driving only. Congested city centres, which may already limit traffic and new tolled "super-speed" lanes in highways are prime candidates. Closing roads to human drivers will allow the roads themselves to become intelligent roads able to coordinate traffic in more efficient ways.<br />
This description of the rise of autonomous cars is probably wrong or at the very least incomplete, but however it happens, I believe the end result will be more or less "intelligent" roads with only self-driving cars. Human driving will become a leisure activity, similar to horse riding. Roadway signs, traffic lights and rear view mirrors will become obsolete. Direction indicator lights might remain but only as a way to highlight to pedestrians the intent of the car.<br />
<br />
<h3>
Valet Parking and the Effect on Mass Transportation</h3>
<div>
If a car can drive itself then the first thing that comes to mind is that you no longer need to waste time parking. The car can drop you off and pick you up at the entrance and park itself. Shopping centres and similar venues will change the way their parking lots are arranged to accommodate drop-off and pick-up zones. Without the need for humans opening doors to get in and out of the car, parking lots will be able to pack more cars into a smaller space. </div>
<div>
Expect someone to patent layouts for parking lots or even the whole concept of valet parking with self-driving cars.</div>
<div>
<br /></div>
<div>
Once you think about it, however, why stop at the shopping centre? Why would I take the bus or train to work when I can have my car drop me off and drive off to park somewhere else, or back home. Most people who commute using public transport do so because parking near work is impossible or prohibitively expensive. How cities deal with this challenge depends on whether they'll be able to cope with the increased traffic; Some cities may close non-public transport altogether, but for many having cars driving in and out business districts without staying will not be a problem, it may even save money through savings in public transport upgrades that will no longer be needed and increase income from existing and new tolls. One thing is certain, ridiculously expensive parking will be a thing of the past.</div>
Expect a lot of litigation for obvious ideas based around having the car waiting for you when you need. Sync your calendar so the car knows it needs to pick you early for a school event, or to drive you to meetings? That's a patented idea (I mean system or device that allows...). Have the car take into account weather and driving conditions so it reaches you just in time, there'll be a patent for that as well. Sending a message to tell the car to come and pick you now may be OK, maybe.<br />
<br />
Another industry that will be hit by self-driving cars is the air travel industry. A plane is able to fly in two hours a distance equal to ten hours of driving, but this requires getting to the airport, checking in and out and taking a loan car at the destination. Factor in that it is not you doing the driving and suddenly flying is not as appealing at least for the shorter overland distances.<br />
<h3>
The End of Car Ownership?</h3>
<div>
Won't self-driving cars not mean we won't own cars anymore? I don't think so. Autonomous cars will spell the end of "driving services" as we know them. There will no longer be a difference between renting a car and hailing a taxi. There is a future where at the push of a button a car will be waiting for you whenever you need a ride. All this for a price competitive with car ownership. However a car you own is heavily personalized; there are things you need or may need which are always on your car such as umbrellas, coats, phone chargers, children toys, sunglasses and junk you have nowhere else to put. Autonomous cars will certainly expand the pool of people who do not need to own a car, but I don't think it'll eliminate it. Most probably people will get their first car when they start a family instead of as soon as they can afford it.</div>
<div>
On a related note, once you own a car, you might think you'll be able to use it to earn extra income. After all, once it drops you at work, why not hire it to a company, Uber-like, so it can serve as a taxi for the next seven hours that it won't be needed.<br />
I do not think this will happen; I believe most cars used for driving services will belong to the companies, not hired from individuals. Hiring your car means clearing it of all your belongings and maintain it at an acceptable level of cleanliness. Driving services will be very cheap; I can currently find ads for car rentals as low as $10 (AUD) a day. Granted this is for the most basic model, with draconian insurance and no fuel costs. A more realistic estimate may be $50 per day all inclusive. At this price point, car rental companies can pay employees, maintain offices and parking lots, service and replace cars, etc. and still be profitable. How much is the actual cost of the car in all of this? Would you go through the hassle of keeping your car in a hireable condition for a few extra dollars a day?<br />
<br />
<h3>
Moving Around</h3>
</div>
<div>
Autonomous cars will make use of satellite navigation and an ever increasing assortment of electronic means to reach their destination. Countries which embrace autonomous cars will become increasingly more proficient in reporting road closures as well as the opening of new roads. Even so, there will still be times when this fails. Unexpected road closures due to accidents, network outages, etc. may prevent the navigation framework used by autonomous cars from being aware a road is not available. How will autonomous cars cope in these occasions? The same way people cope right now, by looking at detour signs and following the instructions of traffic controllers. Autonomous cars should be able to understand these same as humans or there may be more efficient methods.</div>
<div>
Accident response crews may have electronic devices that broadcast the lane closures to nearby vehicles and construction crews may broadcast detours.</div>
<div>
Vehicles should also be capable of working "of the grid", they should be capable of remembering routes and following directions.</div>
<div>
The whole field of "the same as people used to do, but for autonomous cars" is rife for patent abuse. </div>
Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com0tag:blogger.com,1999:blog-8256953501100447692.post-45263708313771486562013-08-31T22:56:00.002+10:002013-08-31T23:01:08.948+10:00Introducing Xml Specification Compare and Bonus XPath Discovery<h3>Introducing Xml Specification Compare and Bonus XPath Discovery</h3>
<p>
A while back I faced the problem of unit testing code that generated XML according to some specification.
Using <a href="http://msdn.microsoft.com/en-us/library/system.xml.linq.xnode.deepequals.aspx">XNode.DeepEquals</a> method was not possible
as it is too restrictive; it requires all nodes (and other parts of the XML) to be the same, and in the same order.<br/>
The XML documents used as messages or configuration in many specifications do not require this rigour; XML documents may be
considered equal even if they differ in order of siblings, namespace prefixes, comments, white space, etc..
</p>
<p>
I confess I somehow managed to overlook
<a href="http://msdn.microsoft.com/en-us/library/aa302294.aspx">Microsoft's XML Diff and Patch Tool</a>
when I first looked for a solution to this problem.<br/>
As it was I solved my unit testing needs with a quick and dirty function that would generate "normalized" XML documents by
sorting and ignoring nodes and other relevant parts of the XML and deep comparing the normalized result.
</p>
<p>
While this solution was enough for my needs it was not a full solution and I resolved to write a true XML comparer suitable for specifications.<br/>
It was only after I had completed most of the coding for this comparer that I discovered the aforementioned XML Diff and Patch Tool.
The results of my efforts is the <a href="http://xmlspecificationcompare.codeplex.com">XML Specification Compare</a> library (also
available as <a href="http://www.nuget.org/packages?q=xmlspecificationcompare">Nuget packages</a>).
<p>
Performance wise my implementation and Microsoft's XML Diff and Patch Tool are roughly the same. My tests show an 8% speed advantage
to XML Specification Compare when comparing equal files. I cannot say, however, whether this is a consistent advantage or just a construct of the
test documents I selected. When comparing unequal documents XML Specification Compare has a greater speed advantage, but this is to be expected
since Microsoft's XML Diff and Patch Tool generates a diff document while XML Specification Compare stops on the first node it cannot match.
There are still some advantages to my implementation:<br/>
<ul>
<li>It's <a href="http://xmlspecificationcompare.codeplex.com/license">open source</a> and available as
<a href="http://www.nuget.org/packages/XmlSpecificationCompare.Code/">source</a> to embed in your project.</li>
<li>
It ignores placement of namespace definitions (xmlns attributes).<br/>
The following documents are considered equal in XML Specification Compare, but are, erroneously in my opinion reported as different by
Microsoft's XML Diff and Patch Tool (with all ignore options set except for ignore namespace):
</p>
<p>
<table width='100%' style="font-family:monospace">
<tr><td>
<?xml version="1.0" encoding="UTF-8"?><br/>
<root xmlns="http://default.com"><br/>
<a:elem xmlns:a="http://a.com"/><br/>
</root>
</td>
<td>
<?xml version="1.0" encoding="UTF-8"?><br/>
<root xmlns:a="http://a.com" xmlns="http://default.com"><br/>
<a:elem /><br/>
</root>
</td></tr>
</table>
<br/>
</li>
</ul>
</p>
<p>The downside of XML Specification Compare is that it currently lacks any option to turn off ignores, while Microsoft's XML Diff and Patch Tool
allows you to specify which features of the XML document to ignore. I will probably implement some of the configuration options in the future,
(an option to respect siblings order in particular).
<h3>XPath Discovery - Bonus Implementation</h3>
<p>Xml Specification Compare has classes for integration with NUnit. When an assertion failed I wanted to provide a useful error message
with a simple XPath to the node that could not be matched.</p>
<p>I tried to find an existing solution, but to date (then and now) I have not found code that would provide a nice XPath to elements, attributes or
text nodes. So I've implemented this as well.</p>
<p>The XPath discovery library supports the following Linq to XML classes: XElement, XAttribute, XText, XCData and XComment.<br/>
Given an instance of any of this classes it will generate a simple XPath of the form: /elemA[i]/elemb[j]/.../(elemN[u]|@attr|text()|comment()).
</p>
<p>The XPath discovery library is available in code only as a <a href="http://www.nuget.org/packages/XPathDiscovery.Code/">Nuget package</a>.</p>Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com1tag:blogger.com,1999:blog-8256953501100447692.post-35432019777105313772013-02-22T22:53:00.000+11:002013-03-01T19:47:30.184+11:00Software Practices and Fairy Tales<h3>
Let me tell you a fairy tale</h3>
Once upon a time, not so long ago and not really that far away there was a Manager.<br />
He was a good Manager, well liked by his subordinates and appreciated by his superiors, and he liked his job.
But lately he had been charged with a new type of Project a Software Project.<br />
This caused the Manager much anguish, for Software Projects where difficult beasts to master.
They always seemed to take longer than expected, even when taking into account that they would take longer than expected, and
even when completed the client never got what he actually wanted.<br />
So the Manager went on top of a tall office building to gaze at the city and reflect; and lo and behold he saw a skyscraper being
built, hundreds of people working in harmony toward one end.<br />
"Why," he thought "can architects build a glorious skyscraper while I struggle to produce simple programs?"<br />
And he was enlightened.<br />
Architects, after all, did not gather together hundreds of workers and tell them, I want you to build me a skyscraper. There were
months even years of preparation before even a single stone was moved. The same should be true for software:<br />
First business analysts would analyse the problem to be solved along with the client, until a scope and solution was agreed upon.<br />
Then software architects would create a model (a blue print) for the application that would implement the solution, and only then
would programmers start coding.<br />
Finally after testing everything is as it should the application would be deployed.<br />
As so it was, and from that day forward software projects where always on time, and the Manager lived stress free ever after.<br />
<br />
<h3>
The Waterfall Model Was Actually Successful</h3>
The tale above describes the software development life cycle Waterfall methodology. It is now, in the age of the Internet
and Agile methodologies, vogue to look down upon the Waterfall model as out of touch with reality and antiquated.<br />
<br />
Some detractors of the Waterfall method claim it aims to turn software development into an assembly line where programmers
mindlessly follow the architect blue print to churn out applications. In their view the Waterfall method fails because it fails
to recognize software development is a creative and complex process, and any "blue print" no matter how carefully designed is
going to differ to some degree from the actual implementation. This is a great disservice to the pioneers of software project
management.<br />
Waterfall is modeled after civil engineering and manufacturing, but it's goal is not to treat software development as an
assembly line; rather it aims to impose the same rigor necessary to bring new products to market.<br />
Building a skyscraper after all is not just a bunch of builders following an architect's master plan; behind any such enterprise
there's an army of people whose job is to deal with all the inconsistencies between the plan and reality, and still deliver a
skyscraper (hopefully without going over budget too much.)<br />
The failure of the Waterfall and related methodologies in the Internet age is due to a change in circumstances. For projects
running in Internet time it is more important to be able to deliver incremental functionality fast and cope with constantly
changing requirements, than to predict the resources and time needed to deliver the ultimate product.<br />
To claim any non Agile methodology is pure useless bureaucracy, imposed by control obsessed managers on programmers without
regard to how software is really developed, is going too far.<br />
<br />
<h3>
Everything Has a Faerie Tale</h3>
My intention in this article is not to praise or condemn the Waterfall model or any other methodology since, but rather to address
the "Fairy Tale" description that invariably accompanies methodologies and processes.<br />
<br />
Software development practices are always evolving, and any new practice, whether it is a new design concept, methodology or even
a new library is first described in using a "Fairy Tale".<br />
Let's take ORMs for instance, according to the Fairy Tale description an ORM library makes writing applications using a relational
database easy by mapping object oriented models into relational schemas in a database; but if they are so good how come untold hours
are wasted trying to make the ORM do things which using raw database access and SQL would be trivial. Does this mean the ORM promise
of data-oriented applications without SQL a, er, fairy tale? Well, in this case
<a href="http://seldo.com/weblog/2011/08/11/orm_is_an_antipattern">probably</a> or
<a href="http://www.javacodegeeks.com/2012/05/orm-haters-dont-get-it.html">maybe not</a> object relational mapping is a
<a href="http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx">complex problem</a>.<br />
<br />
When first analyzing requirements and organizing them as user stories or use cases, it is customary to start with the most common
scenario without problems; the Main Success Scenario (MSS) in use case parlance. How to deal with all the problems and edge cases
is left for later user stories; so it is not surprising "Fairy Tale" descriptions are used.<br />
The thing to remember is they are just that, fairy tales, the best possible outcome when there are no incompatibilities. There are no panaceas in software development, practices may deliver
on their Fairy Tale, but only if you take the time to learn how to apply them, when to apply them and when you are better off without them.Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com0tag:blogger.com,1999:blog-8256953501100447692.post-34789629732913511002012-09-12T19:24:00.000+10:002013-04-08T22:17:41.234+10:00Part 6: T-SQL Implementation of NORMDIST / NORM.S.DISTThis is Part 6 of a set of posts. The other posts are:<br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html">Part 1: Introduction</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-2.html">Part 2: Probability Density Function Implementation</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-3.html">Part 3: Cumulative Distribution Function Implementation 1</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-4.html">Part 4: Cumulative Distribution Function Implementation 2</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-5.html">Part 5: Cumulative Distribution Function Implementation 3</a><br />
<b>Part 6: References and Notes</b><br />
<br />
<h1>References and Notes</h1>
As stated in <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html">Part 1</a> the implementations presented in this series
of posts are my own, however I conducted no original research in their development.<br />
In this part I list the sources I used while preparing this series of posts as well as notes and additional sources of interest.<br />
<br />
<h3>Main Sources</h3>
<a href="http://en.wikipedia.org/wiki/Normal_distribution">Wikipedia article on the Normal Distribution</a> - the Wikipedia article was
my first stop when looking for information for these series of posts. It contains the basic information and formulas for the PDF and CDF
functions, but more important the
<a href="http://en.wikipedia.org/wiki/Normal_distribution#Numerical_approximations_for_the_normal_CDF">section about numerical approximations</a>
contains a reference to algorithm 26.2.17 from Abramowitz & Stegun (1964) used in <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-4.html">Part 4</a>
<br/>
<b>Milton Abramowitz and Irene Stegun (1964) - Handbook of Mathematical Functions with Formulas, Graphs, and Mathematical Tables</b> - referred in
these posts as "Abramowitz & Stegun (1964)"; this book contains a wide array of formulas and approximation in several fields. The
<a href="http://en.wikipedia.org/wiki/Abramowitz_and_Stegun">Wikipedia article</a> has more information about this work.<br />
The approximations used in <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-3.html">Part 3</a> and
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-4.html">Part 4</a> are taken from this source.
<br />
<b>W. J. Cody (1969) - Rational Chebyshev Approximations for the Error Function, Mathematics of Computation, Vol. 23, No. 107 (Jul., 1969), pp. 631-637</b> -
Referred in these posts as "W. J. Cody (1969)", I came accross this paper from one of the pioneers in numerical analysis while looking for Hart (1968)
(see Addtional References below) and decided to use his approximation for the
"ultimate" implementation in <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-5.html">Part 5</a>.<br/>
<br />
<b>Improvents in Excel 2007 and 2010</b> are discussed in the following sources:<br />
<ul>
<li><a href="http://support.microsoft.com/default.aspx?kbid=828888&product=xl2003">Description of improvements in the statistical functions in Excel 2003 and in Excel 2004 for Mac</a></li>
<li><a href="http://blogs.technet.com/b/office2010/archive/2009/10/12/functions-and-solver-improvements-in-excel-2010.aspx">Functions and Solver Improvements in Excel 2010 - Microsoft Office 2010 Engineering - Site Home - TechNet Blogs</a></li>
<li><a href="http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-components-postattachments/00-09-90-51-40/ExcelFunctionImprovements-10_2D00_05_2D00_09-_2800_1_2900_.pdf">Function Improvements in Microsoft Office Excel 2010</a></li>
</ul>
<br/>
The white paper in the last reference in particular is very interesting as it contains a primer on common approximation methods.<br/>
<br/>
<h3>Additional References</h3>
<b>Cecil Hastings, Jr. (1955) - Approximations for Digital Computers</b> - this is the source for the algoritms presented in
Abramowitz & Stegun (1964), thought the actual approximations in this book are for the erf function.<br/>
Part 1 of this book describes the methods used to develop the approximations.<br/>
<br/>
<b>John F. Hart, et al (1968) - Computer Approximations</b> - this oft sited book contains a wide array of approximations for
several functions including highly accurate ones for the erf function.Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com0tag:blogger.com,1999:blog-8256953501100447692.post-76085446419133488412012-09-12T19:23:00.000+10:002014-05-30T12:07:21.270+10:00Part 5: T-SQL Implementation of NORMDIST / NORM.S.DIST
This is Part 5 of a set of posts. The other posts are:<br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html">Part 1: Introduction</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-2.html">Part 2: Probability Density Function Implementation</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-3.html">Part 3: Cumulative Distribution Function Implementation 1</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-4.html">Part 4: Cumulative Distribution Function Implementation 2</a><br />
<b>Part 5: Cumulative Distribution Function Implementation 3</b><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-6.html">Part 6: References and Notes</a><br />
<br />
<h1>Cumulative Distribution Function Implementation 3</h1>
The cumulative distribution function has no closed form expression and can only be approximated (see <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html">Part 1</a>.) <br />
The following implementation uses a set of rational approximations from W. J. Cody (1969) (see <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-6.html">Part 6</a>
for full references.)<br />
<br />
<div><span style="font-family: Courier New; font-size: 10pt;">
<font color = "green"><i>-- =============================================</i></font>
<br/><font color = "green"><i>-- Author: Eli Algranti</i></font>
<br/><font color = "green"><i>-- Description: Standard Normal Distribution </i></font>
<br/><font color = "green"><i>-- Cumulative Distribution Function</i></font>
<br/><font color = "green"><i>-- using a rational polynomial </i></font>
<br/><font color = "green"><i>-- approximation to erf() from</i></font>
<br/><font color = "green"><i>-- W. J. Cody 1969</i></font>
<br/><font color = "green"><i>-- Copyright: Eli Algranti (c) 2012</i></font>
<br/><font color = "green"><i>--</i></font>
<br/><font color = "green"><i>-- This code is licensed under the Microsoft Public </i></font>
<br/><font color = "green"><i>-- License (Ms-Pl) (http://tsqlnormdist.codeplex.com/license)</i></font>
<br/><font color = "green"><i>-- =============================================</i></font>
<br/><font color = "blue">CREATE</font> <font color = "blue">FUNCTION</font> <font color = "maroon">[dbo]</font><font color = "silver">.</font><font color = "#FF0080"><b>[StdNormalDistributionCDF_3]</b></font> <font color = "maroon">(</font> <font color = "#8000FF">@x</font> <font color = "black"><i>FLOAT</i></font><font color = "maroon">)</font>
<br/><font color = "maroon">RETURNS</font> <font color = "black"><i>FLOAT</i></font>
<br/><font color = "blue">AS</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@Z</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "fuchsia"><i>ABS</i></font><font color = "maroon">(</font><font color = "#8000FF">@x</font><font color = "maroon">)</font><font color = "silver">/</font><font color = "fuchsia"><i>SQRT</i></font><font color = "maroon">(</font><font color = "black">2.0</font><font color = "maroon">)</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@Z2</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@Z</font><font color = "silver">*</font><font color = "#8000FF">@Z</font><font color = "silver">;</font> <font color = "green"><i>-- optimization</i></font>
<br/>
<br/> <font color = "blue">IF</font> <font color = "maroon">(</font><font color = "#8000FF">@Z</font> <font color = "silver">>=</font><font color = "black">11.0</font><font color = "maroon">)</font> <font color = "green"><i>-- value is too large no need to compute</i></font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">IF</font> <font color = "#8000FF">@x</font> <font color = "silver">></font> <font color = "black">0.0</font>
<br/> <font color = "blue">RETURN</font> <font color = "black">1.0</font><font color = "silver">;</font>
<br/> <font color = "blue">RETURN</font> <font color = "black">0.0</font><font color = "silver">;</font>
<br/> <font color = "blue">END</font>
<br/>
<br/> <font color = "green"><i>-- Compute ERF using W. J. Cody 1969</i></font>
<br/>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ERF</font> <font color = "black"><i>FLOAT</i></font><font color = "silver">;</font>
<br/>
<br/> <font color = "blue">IF</font> <font color = "maroon">(</font><font color = "#8000FF">@Z</font> <font color = "silver"><=</font> <font color = "black">0.46786</font><font color = "maroon">)</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pA0</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">3.209377589138469472562E03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pA1</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">3.774852376853020208137E02</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pA2</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.138641541510501556495E02</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pA3</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">3.161123743870565596947E00</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pA4</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.857777061846031526730E-01</font><font color = "silver">;</font>
<br/>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qA0</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">2.844236833439170622273E03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qA1</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.282616526077372275645E03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qA2</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">2.440246379344441733056E02</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qA3</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">2.360129095234412093499E01</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qA4</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.000000000000000000000E00</font><font color = "silver">;</font>
<br/>
<br/> <font color = "green"><i>-- For efficiency compute sequence of powers of @Z </i></font>
<br/> <font color = "green"><i>-- (instead of calling POWER(@Z,2), POWER(@Z,4), etc.)</i></font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZA4</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@Z2</font><font color = "silver">*</font><font color = "#8000FF">@Z2</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZA6</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@ZA4</font><font color = "silver">*</font><font color = "#8000FF">@Z2</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZA8</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@ZA6</font><font color = "silver">*</font><font color = "#8000FF">@Z2</font><font color = "silver">;</font>
<br/>
<br/>
<br/> <font color = "blue">SELECT</font> <font color = "#8000FF">@ERF</font> <font color = "silver">=</font> <font color = "#8000FF">@Z</font> <font color = "silver">*</font>
<br/> <font color = "maroon">(</font><font color = "#8000FF">@pA0</font> <font color = "silver">+</font> <font color = "#8000FF">@pA1</font><font color = "silver">*</font><font color = "#8000FF">@Z2</font> <font color = "silver">+</font> <font color = "#8000FF">@pA2</font><font color = "silver">*</font><font color = "#8000FF">@ZA4</font> <font color = "silver">+</font> <font color = "#8000FF">@pA3</font><font color = "silver">*</font><font color = "#8000FF">@ZA6</font> <font color = "silver">+</font> <font color = "#8000FF">@pA4</font><font color = "silver">*</font><font color = "#8000FF">@ZA8</font><font color = "maroon">)</font> <font color = "silver">/</font>
<br/> <font color = "maroon">(</font><font color = "#8000FF">@qA0</font> <font color = "silver">+</font> <font color = "#8000FF">@qA1</font><font color = "silver">*</font><font color = "#8000FF">@Z2</font> <font color = "silver">+</font> <font color = "#8000FF">@qA2</font><font color = "silver">*</font><font color = "#8000FF">@ZA4</font> <font color = "silver">+</font> <font color = "#8000FF">@qA3</font><font color = "silver">*</font><font color = "#8000FF">@ZA6</font> <font color = "silver">+</font> <font color = "#8000FF">@qA4</font><font color = "silver">*</font><font color = "#8000FF">@ZA8</font><font color = "maroon">)</font><font color = "silver">;</font>
<br/> <font color = "blue">END</font>
<br/> <font color = "blue">ELSE</font> <font color = "blue">IF</font> <font color = "maroon">(</font><font color = "#8000FF">@Z</font> <font color = "silver"><=</font> <font color = "black">4.0</font><font color = "maroon">)</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pB0</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.23033935479799725272E03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pB1</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">2.05107837782607146532E03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pB2</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.71204761263407058314E03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pB3</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">8.81952221241769090411E02</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pB4</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">2.98635138197400131132E02</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pB5</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">6.61191906371416294775E01</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pB6</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">8.88314979438837594118E00</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pB7</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">5.64188496988670089180E-01</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pB8</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">2.15311535474403846343E-08</font><font color = "silver">;</font>
<br/>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qB0</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.23033935480374942043E03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qB1</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">3.43936767414372163696E03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qB2</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">4.36261909014324715820E03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qB3</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">3.29079923573345962678E03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qB4</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.62138957456669018874E03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qB5</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">5.37181101862009857509E02</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qB6</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.17693950891312499305E02</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qB7</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.57449261107098347253E01</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qB8</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.00000000000000000000E00</font><font color = "silver">;</font>
<br/>
<br/> <font color = "green"><i>-- For efficiency compute sequence of powers of @Z </i></font>
<br/> <font color = "green"><i>-- (instead of calling POWER(@Z,2), POWER(@Z,3), etc.)</i></font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZB3</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@Z2</font><font color = "silver">*</font><font color = "#8000FF">@Z</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZB4</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@ZB3</font><font color = "silver">*</font><font color = "#8000FF">@Z</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZB5</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@ZB4</font><font color = "silver">*</font><font color = "#8000FF">@Z</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZB6</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@ZB5</font><font color = "silver">*</font><font color = "#8000FF">@Z</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZB7</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@ZB6</font><font color = "silver">*</font><font color = "#8000FF">@Z</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZB8</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@ZB7</font><font color = "silver">*</font><font color = "#8000FF">@Z</font><font color = "silver">;</font>
<br/>
<br/> <font color = "blue">SELECT</font> <font color = "#8000FF">@ERF</font> <font color = "silver">=</font> <font color = "black">1.0</font> <font color = "silver">-</font> <font color = "fuchsia"><i>EXP</i></font><font color = "maroon">(</font><font color = "silver">-</font><font color = "#8000FF">@Z2</font><font color = "maroon">)</font> <font color = "silver">*</font>
<br/> <font color = "maroon">(</font><font color = "#8000FF">@pB0</font> <font color = "silver">+</font> <font color = "#8000FF">@pB1</font><font color = "silver">*</font><font color = "#8000FF">@Z</font> <font color = "silver">+</font> <font color = "#8000FF">@pB2</font><font color = "silver">*</font><font color = "#8000FF">@Z2</font> <font color = "silver">+</font> <font color = "#8000FF">@pB3</font><font color = "silver">*</font><font color = "#8000FF">@ZB3</font> <font color = "silver">+</font> <font color = "#8000FF">@pB4</font><font color = "silver">*</font><font color = "#8000FF">@ZB4</font>
<br/> <font color = "silver">+</font> <font color = "#8000FF">@pB5</font><font color = "silver">*</font><font color = "#8000FF">@ZB5</font> <font color = "silver">+</font> <font color = "#8000FF">@pB6</font><font color = "silver">*</font><font color = "#8000FF">@ZB6</font> <font color = "silver">+</font> <font color = "#8000FF">@pB7</font><font color = "silver">*</font><font color = "#8000FF">@ZB7</font> <font color = "silver">+</font> <font color = "#8000FF">@pB8</font><font color = "silver">*</font><font color = "#8000FF">@ZB8</font><font color = "maroon">)</font> <font color = "silver">/</font>
<br/> <font color = "maroon">(</font><font color = "#8000FF">@qB0</font> <font color = "silver">+</font> <font color = "#8000FF">@qB1</font><font color = "silver">*</font><font color = "#8000FF">@Z</font> <font color = "silver">+</font> <font color = "#8000FF">@qB2</font><font color = "silver">*</font><font color = "#8000FF">@Z2</font> <font color = "silver">+</font> <font color = "#8000FF">@qB3</font><font color = "silver">*</font><font color = "#8000FF">@ZB3</font> <font color = "silver">+</font> <font color = "#8000FF">@qB4</font><font color = "silver">*</font><font color = "#8000FF">@ZB4</font>
<br/> <font color = "silver">+</font> <font color = "#8000FF">@qB5</font><font color = "silver">*</font><font color = "#8000FF">@ZB5</font> <font color = "silver">+</font> <font color = "#8000FF">@qB6</font><font color = "silver">*</font><font color = "#8000FF">@ZB6</font> <font color = "silver">+</font> <font color = "#8000FF">@qB7</font><font color = "silver">*</font><font color = "#8000FF">@ZB7</font> <font color = "silver">+</font> <font color = "#8000FF">@qB8</font><font color = "silver">*</font><font color = "#8000FF">@ZB8</font><font color = "maroon">)</font><font color = "silver">;</font>
<br/> <font color = "blue">END</font>
<br/> <font color = "blue">ELSE</font>
<br/> <font color = "blue">BEGIN</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pC0</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "silver">-</font><font color = "black">6.58749161529837803157E-04</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pC1</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "silver">-</font><font color = "black">1.60837851487422766278E-02</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pC2</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "silver">-</font><font color = "black">1.25781726111229246204E-01</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pC3</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "silver">-</font><font color = "black">3.60344899949804439429E-01</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pC4</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "silver">-</font><font color = "black">3.05326634961232344035E-01</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pC5</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "silver">-</font><font color = "black">1.63153871373020978498E-02</font><font color = "silver">;</font>
<br/>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qC0</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">2.33520497626869185443E-03</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qC1</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">6.05183413124413191178E-02</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qC2</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">5.27905102951428412248E-01</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qC3</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.87295284992346047209E00</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qC4</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">2.56852019228982242072E00</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@qC5</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">1.00000000000000000000E00</font><font color = "silver">;</font>
<br/>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@pi</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">3.141592653589793238462643383</font><font color = "silver">;</font>
<br/>
<br/> <font color = "green"><i>-- For efficiency compute sequence of powers of @Z </i></font>
<br/> <font color = "green"><i>-- (instead of calling POWER(@Z,-2), POWER(@Z,-3), etc.)</i></font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZC2</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "maroon">(</font><font color = "black">1</font><font color = "silver">/</font><font color = "#8000FF">@Z</font><font color = "maroon">)</font><font color = "silver">/</font><font color = "#8000FF">@Z</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZC4</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@ZC2</font><font color = "silver">*</font><font color = "#8000FF">@ZC2</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZC6</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@ZC4</font><font color = "silver">*</font><font color = "#8000FF">@ZC2</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZC8</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@ZC6</font><font color = "silver">*</font><font color = "#8000FF">@ZC2</font><font color = "silver">;</font>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@ZC10</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "#8000FF">@ZC8</font><font color = "silver">*</font><font color = "#8000FF">@ZC2</font><font color = "silver">;</font>
<br/>
<br/> <font color = "blue">SELECT</font> <font color = "#8000FF">@ERF</font> <font color = "silver">=</font> <font color = "black">1</font> <font color = "silver">-</font> <font color = "fuchsia"><i>EXP</i></font><font color = "maroon">(</font><font color = "silver">-</font><font color = "#8000FF">@Z2</font><font color = "maroon">)</font><font color = "silver">/</font><font color = "#8000FF">@Z</font> <font color = "silver">*</font> <font color = "maroon">(</font><font color = "black">1</font><font color = "silver">/</font><font color = "fuchsia"><i>SQRT</i></font><font color = "maroon">(</font><font color = "#8000FF">@pi</font><font color = "maroon">)</font> <font color = "silver">+</font> <font color = "black">1</font><font color = "silver">/</font><font color = "maroon">(</font><font color = "#8000FF">@Z2</font><font color = "maroon">)</font><font color = "silver">*</font>
<br/> <font color = "maroon">(</font><font color = "maroon">(</font><font color = "#8000FF">@pC0</font> <font color = "silver">+</font> <font color = "#8000FF">@pC1</font><font color = "silver">*</font><font color = "#8000FF">@ZC2</font> <font color = "silver">+</font> <font color = "#8000FF">@pC2</font><font color = "silver">*</font><font color = "#8000FF">@ZC4</font> <font color = "silver">+</font> <font color = "#8000FF">@pC3</font><font color = "silver">*</font><font color = "#8000FF">@ZC6</font> <font color = "silver">+</font> <font color = "#8000FF">@pC4</font><font color = "silver">*</font><font color = "#8000FF">@ZC8</font> <font color = "silver">+</font> <font color = "#8000FF">@pC5</font><font color = "silver">*</font><font color = "#8000FF">@ZC10</font><font color = "maroon">)</font> <font color = "silver">/</font>
<br/> <font color = "maroon">(</font><font color = "#8000FF">@qC0</font> <font color = "silver">+</font> <font color = "#8000FF">@qC1</font><font color = "silver">*</font><font color = "#8000FF">@ZC2</font> <font color = "silver">+</font> <font color = "#8000FF">@qC2</font><font color = "silver">*</font><font color = "#8000FF">@ZC4</font> <font color = "silver">+</font> <font color = "#8000FF">@qC3</font><font color = "silver">*</font><font color = "#8000FF">@ZC6</font> <font color = "silver">+</font> <font color = "#8000FF">@qC4</font><font color = "silver">*</font><font color = "#8000FF">@ZC8</font> <font color = "silver">+</font> <font color = "#8000FF">@qC5</font><font color = "silver">*</font><font color = "#8000FF">@ZC10</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "maroon">)</font><font color = "silver">;</font>
<br/> <font color = "blue">END</font>
<br/>
<br/> <font color = "blue">DECLARE</font> <font color = "#8000FF">@cd</font> <font color = "black"><i>FLOAT</i></font> <font color = "silver">=</font> <font color = "black">0.5</font><font color = "silver">*</font><font color = "maroon">(</font><font color = "black">1</font><font color = "silver">+</font><font color = "#8000FF">@ERF</font><font color = "maroon">)</font><font color = "silver">;</font>
<br/>
<br/> <font color = "blue">IF</font> <font color = "#8000FF">@x</font> <font color = "silver">></font> <font color = "black">0</font>
<br/> <font color = "blue">RETURN</font> <font color = "#8000FF">@cd</font><font color = "silver">;</font>
<br/>
<br/> <font color = "blue">RETURN</font> <font color = "black">1.0</font><font color = "silver">-</font><font color = "#8000FF">@cd</font><font color = "silver">;</font>
<br/> <font color = "blue">END</font>
</span></div>
<br/>
<h3>Verification Using Excel 2010 as Reference</h3>
<br />
Maximum difference 5.55111512312578E-16 at value 1.00999999999983<br />
<br />
<div style="text-align: center;">
<img border="0" height="258" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZrgwZHP_ye6rB9q-eWJm5cFfnDAmumuWeiY5Y8nZpd3bq4o9c-4ej5dU-fUTFQk6uhU47mIMbwA9aIIApvUcXOROfYlsxDiK3M22cYx9NkKph93KJYCFozAAw59HkIHaLi2jt8S8aTE0e/s1600/StdNormalDistributionCDF_3_2010.png" width="640" />
</div>
<br />
<br />
<h3>Verification Using Excel 2007 as Reference</h3>
<br />
Maximum difference 5.09148279093097E-13 at value 4.97999999999994<br />
<br />
<div style="text-align: center;">
<img border="0" height="258" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhzINhyE5hrr_48N-geZYui9HeohZAgsiToUM-TbcoQ7AHWDMH-DKtO1Bnfuikjai8FlHJqNQs8wFAZwQC6de26TXAW2jT3qqAXtEbOtCjnXr3xfH5rLXXnljXfeqFueDZoaG6yDplFTBmJ/s1600/StdNormalDistributionCDF_3_2007.png" width="640" />
</div>
<br />
<br />
<h3>Discussion</h3>
This more complex and slower implementation uses three different rational approximations to provide maximum accuracy over a wide range of values.<br />
Here at last we see the differences between Excel 2010 and Excel 2007.<br />
Using Excel 2010 as reference we note the approximation provides the maximum precision possible with a double precision float (15.5 decimal places.)<br />
Using Excel 2007 the precision drops down to 12 decimal places. The graph shows this happens for only two small bands at around 5 and -5 which is
where the Excel 2007 implementation is lacking and where Excel 2010 shows improvements.Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com2tag:blogger.com,1999:blog-8256953501100447692.post-85706402559828483102012-09-12T19:22:00.000+10:002014-05-30T12:06:18.104+10:00Part 4: T-SQL Implementation of NORMDIST / NORM.S.DISTThis is Part 4 of a set of posts. The other posts are:<br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html">Part 1: Introduction</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-2.html">Part 2: Probability Density Function Implementation</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-3.html">Part 3: Cumulative Distribution Function Implementation 1</a><br />
<b>Part 4: Cumulative Distribution Function Implementation 2</b><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-5.html">Part 5: Cumulative Distribution Function Implementation 3</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-6.html">Part 6: References and Notes</a><br />
<br />
<h1>Cumulative Distribution Function Implementation 2</h1>
The cumulative distribution function has no closed form expression and can only be approximated (see <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html">Part 1</a>.) <br />
The following implementation uses approximation 26.2.17 from Abramowitz & Stegun (1964)
(see <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-6.html">Part 6</a>) for full references.<br />
<br />
<div>
<span style="font-family: Courier New; font-size: 10pt;">
<span style="color: green; font-style: italic;">-- =============================================</span>
<br /><span style="color: green; font-style: italic;">-- Author: Eli Algranti</span>
<br /><span style="color: green; font-style: italic;">-- Description: Standard Normal Distribution </span>
<br /><span style="color: green; font-style: italic;">-- Cummulative Distribution Function</span>
<br /><span style="color: green; font-style: italic;">-- using polinomial approximation 26.2.17 </span>
<br /><span style="color: green; font-style: italic;">-- from Abramowitz & Stegun (1964)</span>
<br /><span style="color: green; font-style: italic;">-- Remarks: This function depends on </span>
<br /><span style="color: green; font-style: italic;">-- [dbo].[StdNormalDistributionPDF]</span>
<br /><span style="color: green; font-style: italic;">-- to calculate the Standard Normal</span>
<br /><span style="color: green; font-style: italic;">-- Distribution PDF for the value</span>
<br /><span style="color: green; font-style: italic;">-- Copyright: Eli Algranti (c) 2012</span>
<br /><span style="color: green; font-style: italic;">--</span>
<br /><span style="color: green; font-style: italic;">-- This code is licensed under the Microsoft Public </span>
<br /><span style="color: green; font-style: italic;">-- License (Ms-Pl) (https://tsqlnormdist.codeplex.com/license)</span>
<br /><span style="color: green; font-style: italic;">-- =============================================</span>
<br /><span style="color: blue;">CREATE</span> <span style="color: blue;">FUNCTION</span> <span style="color: maroon;">[dbo]</span><span style="color: silver;">.</span><span style="color: #ff0080; font-weight: bold;">[Stdnormaldistributioncdf_2]</span> <span style="color: maroon;">(</span><span style="color: #8000ff;">@x</span> <span style="color: black; font-style: italic;">FLOAT</span><span style="color: maroon;">)</span>
<br /><span style="color: maroon;">returns</span> <span style="color: black; font-style: italic;">FLOAT</span>
<br /><span style="color: blue;">AS</span>
<br /> <span style="color: blue;">BEGIN</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@Z</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: magenta; font-style: italic;">Abs</span><span style="color: maroon;">(</span><span style="color: #8000ff;">@x</span><span style="color: maroon;">)</span>
<br />
<br /> <span style="color: blue;">IF</span> <span style="color: maroon;">(</span> <span style="color: #8000ff;">@Z</span> <span style="color: silver;">>=</span> <span style="color: black;">15</span> <span style="color: maroon;">)</span> <span style="color: green; font-style: italic;">-- value is too large no need to compute</span>
<br /> <span style="color: blue;">BEGIN</span>
<br /> <span style="color: blue;">IF</span> <span style="color: #8000ff;">@x</span> <span style="color: silver;">></span> <span style="color: black;">0</span>
<br /> <span style="color: blue;">RETURN</span> <span style="color: black;">1</span><span style="color: silver;">;</span>
<br />
<br /> <span style="color: blue;">RETURN</span> <span style="color: black;">0</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">END</span>
<br />
<br /> <span style="color: green; font-style: italic;">-- Compute the Standard Normal Cummulative Distribution using </span>
<br /> <span style="color: green; font-style: italic;">-- polinomial approximation 26.2.17 from Abramowitz & Stegun (1964)</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@p</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: black;">0.2316419</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@b1</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: black;">0.319381530</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@b2</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: silver;">-</span><span style="color: black;">0.356563782</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@b3</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: black;">1.781477937</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@b4</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: silver;">-</span><span style="color: black;">1.821255978</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@b5</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: black;">1.330274429</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@t</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: black;">1.0</span> <span style="color: silver;">/</span> <span style="color: maroon;">(</span> <span style="color: black;">1.0</span> <span style="color: silver;">+</span> <span style="color: #8000ff;">@p</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@Z</span> <span style="color: maroon;">)</span><span style="color: silver;">;</span>
<br /> <span style="color: green; font-style: italic;">-- For efficiency compute sequence of powers of @t (instead of calling POWER(@t,2), POWER(@t,3), etc.)</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@t2</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: #8000ff;">@t</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@t</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@t3</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: #8000ff;">@t2</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@t</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@t4</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: #8000ff;">@t3</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@t</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@cd</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: black;">1.0</span> <span style="color: silver;">-</span> <span style="color: maroon;">[dbo]</span><span style="color: silver;">.</span><span style="color: #ff0080; font-weight: bold;">[Stdnormaldistributionpdf]</span><span style="color: maroon;">(</span><span style="color: #8000ff;">@Z</span><span style="color: maroon;">)</span> <span style="color: silver;">*</span> <span style="color: maroon;">(</span>
<br /> <span style="color: #8000ff;">@b1</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@t</span> <span style="color: silver;">+</span> <span style="color: #8000ff;">@b2</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@t2</span> <span style="color: silver;">+</span> <span style="color: #8000ff;">@b3</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@t3</span> <span style="color: silver;">+</span> <span style="color: #8000ff;">@b4</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@t4</span> <span style="color: silver;">+</span>
<br /> <span style="color: #8000ff;">@b5</span> <span style="color: silver;">*</span>
<br /> <span style="color: #8000ff;">@t4</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@t</span> <span style="color: maroon;">)</span>
<br />
<br /> <span style="color: blue;">IF</span> <span style="color: #8000ff;">@x</span> <span style="color: silver;">></span> <span style="color: black;">0</span>
<br /> <span style="color: blue;">RETURN</span> <span style="color: #8000ff;">@cd</span><span style="color: silver;">;</span>
<br />
<br /> <span style="color: blue;">RETURN</span> <span style="color: black;">1.0</span> <span style="color: silver;">-</span> <span style="color: #8000ff;">@cd</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">END</span>
</span>
</div>
<h3>Verification Using Excel 2010 as Reference</h3>
<br />
Maximum difference 7.4506132929919E-08 at value 0.719999999999832<br />
<br />
<div style="text-align: center;">
<img border="0" height="258" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1p57lc1YZWAxcmP3fFSPBsZzphkwxbp9akEIwM0CucTi1SvEwFV0qy43jfolNbQCGv4tYRviyQL3eC3RVOxRvFMgHCNbLYRzdloYb73ohcVw8I2znWV80bkVfxSzJiODiSvPKTJsH2yqB/s1600/StdNormalDistributionCDF_2_2010.png" width="640" />
</div>
<br />
<br />
<h3>Verification Using Excel 2007 as Reference</h3>
<br />
Maximum difference 7.4506132929919E-08 at value 0.719999999999832<br />
<br />
<div style="text-align: center;">
<img border="0" height="258" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSL0Ky1E9ujmMdX9QMbwyHUYIvHNDZXWf6if8rdKDY-kmg5BuJIdvYOwR-4MpRktOjd-BWcvTtD7b_0IHz9qGS2fesjxrX1akzoEupKFU3FaImNehsuCBx9_xfNLOhwrQ6JIUQ-5hd9X_N/s1600/StdNormalDistributionCDF_2_2007.png" width="640" />
</div>
<br />
<br />
<h3>Discussion</h3>
This approximation provides a precision of eight decimal places and surprisingly the same maximum difference for both
versions of Excel. I had to check twice.<br />
Unlike the previous implementation this one is not a pure polynomial approximation and requires the PDF to be computed.<br />
The performance of this implementation can be vastly improved if the PDF needs to be computed anyway by passing the pre-computed PDF
value as a parameter instead of computing it in the function.Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com0tag:blogger.com,1999:blog-8256953501100447692.post-72586008500355012362012-09-12T19:21:00.000+10:002014-05-30T12:03:37.397+10:00Part 3: T-SQL Implementation of NORMDIST / NORM.S.DISTThis is Part 3 of a set of posts. The other posts are:<br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html">Part 1: Introduction</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-2.html">Part 2: Probability Density Function Implementation</a><br />
<b>Part 3: Cumulative Distribution Function Implementation 1</b><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-4.html">Part 4: Cumulative Distribution Function Implementation 2</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-5.html">Part 5: Cumulative Distribution Function Implementation 3</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-6.html">Part 6: References and Notes</a><br />
<br />
<h1>
Cumulative Distribution Function Implementation 1</h1>
The cumulative distribution function has no closed form expression and can only be approximated (see <a href="http://www.blogger.com/T-SQL-NORMDIST-1.html">Part 1</a>.) <br />
The following implementation uses polynomial approximation 26.2.18 from Abramowitz & Stegun (1964)
(see <a href="http://www.blogger.com/T-SQL-NORMDIST-6.html">Part 6</a>) for full references.<br />
<br />
<div>
<span style="font-family: Courier New; font-size: 10pt;">
<span style="color: green; font-style: italic;">-- =============================================</span>
<br /><span style="color: green; font-style: italic;">-- Author: Eli Algranti</span>
<br /><span style="color: green; font-style: italic;">-- Description: Standard Normal Distribution </span>
<br /><span style="color: green; font-style: italic;">-- Cummulative Distribution Function</span>
<br /><span style="color: green; font-style: italic;">-- using polinomial approximation 26.2.18 </span>
<br /><span style="color: green; font-style: italic;">-- from Abramowitz & Stegun (1964)</span>
<br /><span style="color: green; font-style: italic;">-- Copyright: Eli Algranti (c) 2012</span>
<br /><span style="color: green; font-style: italic;">--</span>
<br /><span style="color: green; font-style: italic;">-- This code is licensed under the Microsoft Public </span>
<br /><span style="color: green; font-style: italic;">-- License (Ms-Pl) (https://tsqlnormdist.codeplex.com/license)</span>
<br /><span style="color: green; font-style: italic;">-- =============================================</span>
<br /><span style="color: blue;">CREATE</span> <span style="color: blue;">FUNCTION</span> <span style="color: maroon;">[dbo]</span><span style="color: silver;">.</span><span style="color: #ff0080; font-weight: bold;">[Stdnormaldistributioncdf_1]</span> <span style="color: maroon;">(</span><span style="color: #8000ff;">@x</span> <span style="color: black; font-style: italic;">FLOAT</span><span style="color: maroon;">)</span>
<br /><span style="color: maroon;">returns</span> <span style="color: black; font-style: italic;">FLOAT</span>
<br /><span style="color: blue;">AS</span>
<br /> <span style="color: blue;">BEGIN</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@Z</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: magenta; font-style: italic;">Abs</span><span style="color: maroon;">(</span><span style="color: #8000ff;">@x</span><span style="color: maroon;">)</span>
<br />
<br /> <span style="color: blue;">IF</span> <span style="color: maroon;">(</span> <span style="color: #8000ff;">@Z</span> <span style="color: silver;">>=</span> <span style="color: black;">15</span> <span style="color: maroon;">)</span> <span style="color: green; font-style: italic;">-- value is too large no need to compute</span>
<br /> <span style="color: blue;">BEGIN</span>
<br /> <span style="color: blue;">IF</span> <span style="color: #8000ff;">@x</span> <span style="color: silver;">></span> <span style="color: black;">0</span>
<br /> <span style="color: blue;">RETURN</span> <span style="color: black;">1</span><span style="color: silver;">;</span>
<br />
<br /> <span style="color: blue;">RETURN</span> <span style="color: black;">0</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">END</span>
<br />
<br /> <span style="color: green; font-style: italic;">-- Compute the Standard Normal Cummulative Distribution using </span>
<br /> <span style="color: green; font-style: italic;">-- polinomial approximation 26.2.18 from Abramowitz & Stegun (1964)</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@c1</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: black;">0.196854</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@c2</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: black;">0.115194</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@c3</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: black;">0.000344</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@c4</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: black;">0.019527</span><span style="color: silver;">;</span>
<br /> <span style="color: green; font-style: italic;">-- For efficiency compute sequence of powers of @Z (instead of calling POWER(@Z,2), POWER(@Z,3), etc.)</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@Z2</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: #8000ff;">@Z</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@Z</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@Z3</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: #8000ff;">@Z2</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@Z</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@cd</span> <span style="color: black; font-style: italic;">FLOAT</span> <span style="color: silver;">=</span> <span style="color: black;">1.0</span> <span style="color: silver;">-</span> <span style="color: black;">0.5</span> <span style="color: silver;">*</span> <span style="color: magenta; font-style: italic;">Power</span><span style="color: maroon;">(</span><span style="color: black;">1</span> <span style="color: silver;">+</span> <span style="color: #8000ff;">@c1</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@Z</span> <span style="color: silver;">+</span> <span style="color: #8000ff;">@c2</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@Z2</span> <span style="color: silver;">+</span> <span style="color: #8000ff;">@c3</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@Z3</span>
<br /> <span style="color: silver;">+</span>
<br /> <span style="color: #8000ff;">@c4</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@Z3</span> <span style="color: silver;">*</span> <span style="color: #8000ff;">@Z</span><span style="color: silver;">,</span> <span style="color: silver;">-</span><span style="color: black;">4</span><span style="color: maroon;">)</span>
<br />
<br /> <span style="color: blue;">IF</span> <span style="color: #8000ff;">@x</span> <span style="color: silver;">></span> <span style="color: black;">0</span>
<br /> <span style="color: blue;">RETURN</span> <span style="color: #8000ff;">@cd</span><span style="color: silver;">;</span>
<br />
<br /> <span style="color: blue;">RETURN</span> <span style="color: black;">1.0</span> <span style="color: silver;">-</span> <span style="color: #8000ff;">@cd</span><span style="color: silver;">;</span>
<br /> <span style="color: blue;">END</span>
</span>
</div>
<br />
<h3>
Verification Using Excel 2010 as Reference</h3>
<br />
Maximum difference 0.000232983371206426 at value -1.82000000000017<br />
<br />
<div style="text-align: center;">
<img border="0" height="257" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPyRmWZNOebVyYMkpWqyhduB8r0VtgUdTiGs1Eud0ZmcW0BTTXn1-hGFt1X2dBhMtL4Zq4jUrOvzyBSlEm2-wY54VH09ARnp2Ratw2JsMRToWLNydj3KBI90jWWvLfiybb9XEBUphLs_79/s1600/StdNormalDistributionCDF_1_2010.png" width="640" />
</div>
<br />
<br />
<h3>
Verification Using Excel 2007 as Reference</h3>
<br />
Maximum difference 0.000232983371206363 at value -1.82000000000017<br />
<br />
<div style="text-align: center;">
<img height="258" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEippYhShu3n5AU9vmQXYwRl4lv8b5Kta2lyn6aJgggH1c-BlkenkD8aKOUQp3cHnZUMGchSAWY0of5EIZ9N4GZNcMw9wFnExQeqxX3gCw8f97d7VdLt4j378IfDI_A1iqKoBCX8t0WmgOAZ/s1600/StdNormalDistributionCDF_1_2007.png" width="640" />
</div>
<br />
<br />
<h3>
Discussion</h3>
This approximation provides a precision of three decimal places, which is enough for many applications.<br />
The main advantage of this implementation is its simplicity, requiring just a handful of arithmetic operations.Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com2tag:blogger.com,1999:blog-8256953501100447692.post-5385513613968913182012-09-12T19:20:00.000+10:002014-05-30T12:01:59.158+10:00Part 2: T-SQL Implementation of NORMDIST / NORM.S.DISTThis is Part 2 of a set of posts. The other posts are:<br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html">Part 1: Introduction</a><br />
<b>Part 2: Probability Density Function Implementation</b><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-3.html">Part 3: Cumulative Distribution Function Implementation 1</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-4.html">Part 4: Cumulative Distribution Function Implementation 2</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-5.html">Part 5: Cumulative Distribution Function Implementation 3</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-6.html">Part 6: References and Notes</a><br />
<br />
<h1>Probability Density Function Implementation</h1>
The probability density function can be computed directly from the definition
(see <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-1.html">Part 1</a>.) <br />
<br />
<div>
<span style="font-family: Courier New; font-size: 10pt;">
<span style="color: green;"><i>-- =============================================</i></span>
<br /><span style="color: green;"><i>-- Author: Eli Algranti</i></span>
<br /><span style="color: green;"><i>-- Description: Standard Normal Distribution </i></span>
<br /><span style="color: green;"><i>-- Probability Density Function</i></span>
<br /><span style="color: green;"><i>-- Copyright: Eli Algranti (c) 2012</i></span>
<br /><span style="color: green;"><i>--</i></span>
<br /><span style="color: green;"><i>-- This code is licensed under the Microsoft Public </i></span>
<br /><span style="color: green;"><i>-- License (Ms-Pl) (https://tsqlnormdist.codeplex.com/license)</i></span>
<br /><span style="color: green;"><i>-- =============================================</i></span>
<br /><span style="color: blue;">CREATE</span> <span style="color: blue;">FUNCTION</span> <span style="color: maroon;">[dbo]</span><span style="color: silver;">.</span><span style="color: #ff0080;"><b>[StdNormalDistributionPDF]</b></span>
<br /><span style="color: maroon;">(</span>
<br /> <span style="color: #8000ff;">@x</span> <span style="color: black;"><i>FLOAT</i></span>
<br /><span style="color: maroon;">)</span>
<br /><span style="color: maroon;">RETURNS</span> <span style="color: black;"><i>FLOAT</i></span>
<br /><span style="color: blue;">AS</span>
<br /><span style="color: blue;">BEGIN</span>
<br /> <span style="color: blue;">DECLARE</span> <span style="color: #8000ff;">@pi</span> <span style="color: black;"><i>FLOAT</i></span> <span style="color: silver;">=</span> <span style="color: black;">3.141592653589793238462643383</span><span style="color: silver;">;</span>
<br />
<br /> <span style="color: green;"><i>-- The standard normal probability corresponding to @x</i></span>
<br /> <span style="color: blue;">RETURN</span> <span style="color: magenta;"><i>EXP</i></span><span style="color: maroon;">(</span><span style="color: silver;">-</span><span style="color: black;">0.5</span><span style="color: silver;">*</span><span style="color: #8000ff;">@x</span><span style="color: silver;">*</span><span style="color: #8000ff;">@x</span><span style="color: maroon;">)</span><span style="color: silver;">/</span><span style="color: magenta;"><i>SQRT</i></span><span style="color: maroon;">(</span><span style="color: black;">2.0</span><span style="color: silver;">*</span><span style="color: #8000ff;">@pi</span><span style="color: maroon;">)</span><span style="color: silver;">;</span>
<br /><span style="color: blue;">END</span>
</span></div>
<br />
<h3>Verification Using Excel 2010 as Reference</h3>
<br />
Maximum difference 4.71844785465692E-16 at value 1.02999999999983<br />
<br />
<div style="text-align: center;">
<img height="258" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtlynWze-jFodkLaKCGzr1nn-O7UVQ5iUn9ch8I6lkGuBZVb_0ZJc2Ve8DR2irgcis2QpPPWgSeEwwhMvQkkx46odFavY9taD0VJNAXMnuAN9TzRqJeQv07MrzZgeQkZfYoPliQCACfAb-/s1600/StdNormalDistributionPDF_2010.png" width="640" />
</div>
<br />
<br />
<h3>Verification Using Excel 2007 as Reference</h3>
<br />
Maximum difference 4.9960036108132E-16 at value 1.02999999999983<br />
<br />
<div style="text-align: center;">
<img height="257" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7BYWdFr3tRacHeXl4TUkuImdnjqg9eEOlk6ViAMEmiik3DiZTH-bhr_HeL0mDFsGktlnpsOPUFWDtN5ftPqKKISSuPsz9ovjhPfdzkoIZQC2v19LVUkifno06qa0YlM5DbRMbmNajQkBJ/s1600/StdNormalDistributionPDF_2007.png" width="640" />
</div>
<b>Note:</b> The graphs do not display a value for the Y axis because the values are too small.<br />
<br />
<h3>Discussion</h3>
No surprises here. The T-SQL implementation closely matches both Excel's implementations and is within the error margin for double computations.
The function may be optimized by precomputing SQRT(2*@pi).Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com1tag:blogger.com,1999:blog-8256953501100447692.post-78881734473998603532012-09-12T19:16:00.001+10:002014-05-30T11:48:43.982+10:00Part 1: T-SQL Implementation of NORMDIST / NORM.S.DISTThis is Part 1 of a set of posts. The other posts are:<br />
<b>Part 1: Introduction</b><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-2.html">Part 2: Probability Density Function Implementation</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-3.html">Part 3: Cumulative Distribution Function Implementation 1</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-4.html">Part 4: Cumulative Distribution Function Implementation 2</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-5.html">Part 5: Cumulative Distribution Function Implementation 3</a><br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-6.html">Part 6: References and Notes</a><br />
<br />
<h1>Introduction and Methods</h1>
I’m not the first to use Excel as a tool for developing an algorithm;
<a href="http://sqlblog.com/blogs/paul_nielsen/archive/2010/03/02/extensive-library-of-excel-functions-for-sql-server.aspx">nor the first</a>,
when implementing said algorithm, to be surprised that SQL Server does not ship with a library of statistical functions.<br />
In this series of posts I’ll develop a set of T-SQL functions which implement the very useful
Normal Distribution’s ‘Probability Density Function’ (PDF) and ‘Cumulative Distribution Function’ (CDF.)<br />
<br />
<h3>The Very Basics</h3>
The Standard Normal Distribution is the special case of a Normal Distribution with mean zero and standard deviation one.
The function for the Standard Normal Distribution for variable x is:<br />
<div style="text-align: center;">
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiX4vj6YrdoEEVNTgUCcE_5-DBkxnR2Y4suvfD5nvzxFqzfONnfcAC3-Mfeydttn8qv3UWkleyHMqvzrV_TJzSJp_bZY9mXF5KYCedPFS6N6mguH_NpBrRUIcJOMDfFBa1iO41cfGuKJoT0/s1600/normdist1.png" />
</div>
<br/>
The Normal Distribution PDF (for a variable x with mean µ and standard deviation s) can be expressed as a function of the Standard Normal Distribution PDF:<br />
<div style="text-align: center;">
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtBMjAeldveOV35RT8lz3bX5WnkP1eHOwE17sjIk755axHgOUSkCaxagYKFVvpPLOpn_MKUVG1RA7VjRFPYpjfp-h-TkkopCkfr6_qqwH5E17IOmoP7hK0q1hl9nkCzmQlW92pjO2ck2eX/s1600/normdist2.png" />
</div>
<br />
The function for the Standard Normal Distribution CDF is:<br />
<div style="text-align: center;">
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZgho8Ty6BnPbv4qulsGHY6zdwMT3Au7sR4yPk_b5Gim3h6ozTa7bENFF9gZq1YkYk9ckoxWhJZt4mRQ8r_4odQNIkL-LY1LAF_7Q7psU9Z0p62I1fDJ4PGNIMoYalIR0MhUt2vTG8WpGP/s1600/normdist3.png" />
</div>
<br />
Like the Normal Distribution PDF the Normal Distribution CDF can be expressed as a function of the Standard Normal Distribution CDF:<br />
<div style="text-align: center;">
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiGiocPr6cN7kkTTbCnVNKws3gNQOvB5066T2jlPF07puZSxssUh60OzB2yGaH8MnTF2aoVOmwL5kG9MQyDQSSEZZtEKEgxpUpwqqe2nX68sRwlPWAg9DIqdNWUztVs-G-D_O1ghG3bR0X/s1600/normdist4.png" />
</div>
<br />
Another way to express it is by means of the special function erf (the error function):<br />
<div style="text-align: center;">
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizvfYTynunjmpiusB28XtUMMzMSA8cxKxQHG11JAVBDPLA4ary0mQjodZ-H7mJ7tCDTqqQskDQICF85qHW0cB38194OCWq2odcAhh5OY-JhQG0Db-wdKhgWkFb4nyckmpzGvF8CZICC80c/s1600/normdist5.png" />
</div>
<br />
<br />
<h3>The Implementation</h3>
The Standard Normal Distribution PDF (and by extension the Normal Distribution PDF) can be implemented as a user defined function
directly from the definition. Such an implementation is presented in <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-2.html">Part 2</a> of this series.
The Standard Normal Distribution CDF has no closed form equivalent and can only be approximated:<br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-3.html">Part 3</a> presents an implementation of a simple (and fast) polynomial approximation with a precision
slightly better than three decimal points.<br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-4.html">Part 4</a> presents a more complex polynomial approximation implementation with a precision of seven
decimal points.<br />
Finally <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-5.html">Part 5</a> presents an implementation using a set of rational approximation. This more complex
and slower) approximation yields a precision of fifteen decimal digits, which is also the precision of a double precision floating
point value.<br />
The approximations in <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-3.html">Part 3</a> & <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-4.html">Part 4</a> are courtesy of
Abramowitz & Stegun (1964) while the implementation in <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-5.html">Part 5</a> uses an approximation of the
erf function from W. J. Cody (1969.)<br />
See <a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-6.html">Part 6</a> for a list of references and notes.<br />
<br />
<h3>Ensuring Accuracy</h3>
One of the problems when implementing mathematical functions is how to ensure the implementation is correct and the
values returned accurate.<br />
In order to test the accuracy of my implementations I've used to compute the values of doubles from -10 to 10 with 0.01
intervals and compared them to the results of the Excel 2010 implementation of the same function.
Statistical functions in Excel versions prior to Excel 2003 where criticized for their accuracy.
Microsoft greatly
<a href="http://support.microsoft.com/default.aspx?kbid=828888&product=xl2003">increased the accuracy</a> of Excel 2003 from previous
versions and made
<a href="http://blogs.technet.com/b/office2010/archive/2009/10/12/functions-and-solver-improvements-in-excel-2010.aspx">more improvements</a>
in Excel 2010.<br />
Since the trigger for the work in these series of posts was my desire to implement an algorithm prototyped in Excel,
and given that the latest Excel version addresses accuracy concerns; Excel 2010 is a good candidate to test the implementations.<br />
I've also included the results using Excel 2007 as the reference implementation.
In order to perform the comparison I wrote a small console utility called CheckError.
CheckError gets as parameter the range, interval and functions to test and then:<br />
<ul>
<li>Writes on the console the maximum difference between Excel and Sql Server implementations and at which input value it occurrs.</li>
<li>Writes files each containing the double precision floating point binary representation of:
<ul>
<li>The input values.</li>
<li>The Excel results.</li>
<li>The Sql Server results.</li>
<li>The differences between the Excel and Sql Server results.</li>
</ul>
</li>
<li>Writes a tab delimited file containing decimal string representations of the value above.</li>
</ul>
The binary files can be used to test the precision of the solution using other reference implementations (say MatLab.)
The tab delimited files should not be used for this purpose as there is a loss of precision involved in converting from
the binary representation to the decimal string representation.<br />
I used the tab delimited file to create the graphs presented along side the implementations since the loss of precision is not relevant
in this case.<br />
The source code for CheckError the T-SQL implementations, output files and Excel file with graphs can be found
at <a href="https://tsqlnormdist.codeplex.com/license">codeplex</a> and is released under the
<a href="https://tsqlnormdist.codeplex.com/license">Microsoft Public License (Ms-PL.)</a><br />
<br />
<h3>Caveat Emptor</h3>
The <a href="https://tsqlnormdist.codeplex.com/license">license</a> for the code discussed here contains the full “as is” disclaimer, however since statistical functions may be used in
all sorts of critical applications consider the following warnings if you decide to use these implementations:<br />
While the tests described above are good enough for <b>my</b> purposes they are far from a complete analysis of the implementations.<br />
In particular:<br />
<ol>
<li>I have not performed a review of available literature to discover whether the sources I’ve based my implementations on
have been disputed or corrected.</li>
<li>My implementation (or Excel 2010 which I use as the reference implementation or both) may have bugs which my testing does not reveal
and may result in an error greater than the one stated.</li>
</ol>
<br />
<h3>Note Regarding References</h3>
The implementations and testing tools presented here are my own, but no original research was undertaken in their development.<br />
<a href="http://formaldev.blogspot.com.au/2012/09/T-SQL-NORMDIST-6.html">Part 6</a> contains a full list of references and notes on where I found what information.Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com0tag:blogger.com,1999:blog-8256953501100447692.post-83186617327757776592012-07-04T21:35:00.000+10:002012-07-04T21:35:54.418+10:00Well Known Text (WTK) and Coordinates in Geography Objects<b id="internal-source-marker_0.015475803520530462" style="font-weight: normal;"><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">I’ve been working with MS-SQL Spatial objects lately and specially for Geography objects documentation seems to be somewhat erratic; specifically which number in a Well Known Text (WKT) string definition is the latitude and which is the longitude.</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">Points in WKT for Geography coordinates are defined “longitude latitude”.</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">This makes some sense as it corresponds to the “X Y” representation of coordinates of Geometry object, longitude being marked on the X axis and latitude on the Y axis of a </span><a href="http://en.wikipedia.org/wiki/Map_projection"><span style="background-color: transparent; color: #1155cc; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">North on top map</span></a><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">, except, it doesn’t.</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">That’s because everywhere else coordinates are usually represented “latitude longitude” even in the Geography classes themselves.</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">The signaure of the factory method for </span><a href="http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(MICROSOFT.SQLSERVER.TYPES.SQLGEOGRAPHY.POINT);k(SOLUTIONITEMSPROJECT);k(TargetFrameworkMoniker-%22.NETFRAMEWORK%2cVERSION%3dV4.0%22);k(DevLang-CSHARP)&rd=true"><span style="background-color: transparent; color: #1155cc; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">Geography Points</span></a><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"> is:</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="background-color: transparent; font-family: Consolas; font-size: 13px; vertical-align: baseline; white-space: pre-wrap;">public static SqlGeography Point(<br class="kix-line-break" /><span class="Apple-tab-span" style="white-space: pre;"> </span>double latitude,<br class="kix-line-break" /><span class="Apple-tab-span" style="white-space: pre;"> </span>double longitude,<br class="kix-line-break" /><span class="Apple-tab-span" style="white-space: pre;"> </span>int srid<br class="kix-line-break" />)</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">For example:</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="background-color: transparent; font-family: Consolas; font-size: 13px; vertical-align: baseline; white-space: pre-wrap;">SqlGeography.Point(-30, 10, 4326);</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">is the same as:</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="background-color: transparent; font-family: Consolas; font-size: 13px; vertical-align: baseline; white-space: pre-wrap;">SqlGeometry.STPointFromText("POINT (10 -30)");</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">Bonus fact:</span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">When specifying geographic boundaries as Geography Polygons the </span><a href="http://blogs.msdn.com/b/isaac/archive/2007/10/29/geography-and-ring-orientation.aspx"><span style="background-color: transparent; color: #1155cc; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">points should be specified counter-clockwise</span></a><span style="background-color: transparent; font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;">.</span></b>Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com0tag:blogger.com,1999:blog-8256953501100447692.post-8733949149949246682012-03-01T09:27:00.000+11:002013-02-25T21:11:39.522+11:00Date and Time<b id="internal-source-marker_0.5865382289048284"><span style="font-family: Arial; font-size: 12px; font-style: italic; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">Disclaimer: this post is about date and time on earth in human time frames, relativity concerns do not apply.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">Time information is one the most misunderstood types of data. This misunderstanding usually culminates in the question “Should time be stored as local time or as UTC?”, the answer to which is “Neither, it should be stored as time.”</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"></span></b><br />
<h2 dir="ltr">
<b id="internal-source-marker_0.5865382289048284"><span style="font-family: Arial; vertical-align: baseline; white-space: pre-wrap;">Points in time and their human representation</span></b></h2>
<b id="internal-source-marker_0.5865382289048284"><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">A point in time is a concept, a written date is the human readable representation of this concept, the name of the point in time. The point in time is the same everywhere but it may be called by different names.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">The </span><a href="http://www.nasa.gov/mission_pages/shuttle/shuttlemissions/sts135/launch/sts-135_mission-overview.html"><span style="color: #1155cc; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">Atlantis space shuttle launched for the last time</span></a><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> at “11:29 am, July 8 2011 EDT” which just means the time in Florida when the last space shuttle was launched was 11:29 in the morning. At that time it was only 8:29 in Los Angeles, and someone in Sydney would have to get up at 1:29 in the morning of the 9th to catch the liftoff live.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">“11:29 am, July 8 2011 EDT” is just a representation for the time the of the lauch, “01:29 am, July 9 2011 Sydney time” and “3:29 pm, July 9 2011 UTC” are </span><a href="http://www.timeanddate.com/worldclock/fixedtime.html?msg=STS-135+mission+launch&iso=20110708T1130&p1=2273"><span style="color: #1155cc; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">other representations</span></a><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> for the same point in time.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">The problem is that as humans we are used to dealing with non complete time representations. When someone asks us to call them “at four”, we infer, from context, to mean “today at four in the afternoon local time.” Most of our interactions with time leave some information to context. The most common omitted information is the time-zone, and unfortunately this has been brought over to computer languages.</span></b><br />
<h2 dir="ltr">
<b id="internal-source-marker_0.5865382289048284">
<span style="font-family: Arial; vertical-align: baseline; white-space: pre-wrap;">Time in Computer Programming Languages</span></b></h2>
<b id="internal-source-marker_0.5865382289048284">
<span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">Most computer programming languages’ standard libraries offer a type for storing date-time information.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">The common internal representation used to express time in these types is usually a number of some unit of time since a specific well defined date. Since this date is date zero it is called the epoch for that language/platform.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">For example the C/C++ </span><a href="http://www.cplusplus.com/reference/clibrary/ctime/time_t/"><span style="color: #1155cc; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">time_t</span></a><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> type is defined as the number of seconds since midnight January 1, 1970 (the Unix epoch;) Java’s </span><a href="http://docs.oracle.com/javase/7/docs/api/java/util/Date.html"><span style="color: #1155cc; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">Date</span></a><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> uses the same epoch but the units are milliseconds and .Net’s </span><a href="http://msdn.microsoft.com/en-us/library/system.datetime.aspx"><span style="color: #1155cc; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">DateTime</span></a><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> stores the number of 100 ns units (called ticks) since midnight January 1 of year 1 CE (the common era epoch.)</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">Using units since the epoch makes date and time calculations and comparisons simple, but it is decidedly not human readable, which is why standard libraries also provide ways for date types to be converted to and from human readable formats and here is where problems usually start.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">In C/C++ the epoch is not well defined (i.e. it does not contain timezone information) and timezone information is also absent from the </span><a href="http://www.cplusplus.com/reference/clibrary/ctime/tm/"><span style="color: #1155cc; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">tm</span></a><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> structure which is used to parse time_t variables into the components of date and time representations (month, yesr, hour, etc.mponents. It is almost imposible in C/C++ to do time translations without ending up with time_t variables using epochs in different time zones.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">Java’s Date class used to look like a wrapper for C/C++ time functions, this was fixed in JDK 1.1, which obsoleted those functions and added the </span><a href="http://docs.oracle.com/javase/7/docs/api/java/util/GregorianCalendar.html"><span style="color: #1155cc; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">GregorianCalendar</span></a><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> class to handle conversions. Java also specifies the epoch’s timezone as UTC, but since Date does not store timezone information this is merely a convention.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">.Net did almost the same blunder in version 1 and 1.1, but where a bit more frank about it and added the </span><a href="http://msdn.microsoft.com/en-us/library/shx7s921.aspx"><span style="color: #1155cc; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">DateTimeKind</span></a><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> enumeration which marks a DateTime as UTC i.e. number of ticks since UTC epoch, local i.e number ticks since the epoch in the timezone configured into the computer; or undefined i.e. timezone is unknown.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">It is not possible to reliably compare between times with different timezone epochs so a variable specifying a utc time can be compared with any other utc time variable, local time variables can only be compared with local time variables whose value is known to have originated in the same timezone. In all other cases only variables with times more than 24 hours apart can be reliably compared.</span><br /><h2 dir="ltr">
<span style="font-family: Arial; vertical-align: baseline; white-space: pre-wrap;">How should time be stored?</span></h2>
<span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">Twenty years ago data did not travel much, so there was not much concern about implications of using local dates and time in a global setting. In today’s interconnected world this is no longer the case.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">Now, pun intended, it’s time we revisit the question at the start of this post:</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">“Should time be stored as local time or as UTC?”</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">The answer remains the same “Neither, it should be stored as time.”, but to qualify this answer:</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"></span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">There is no UTC time or local time, a point in time is a point in time, UTC and local are just representations. It is possible to use a date/time variable that stores time using a UTC epoch, a local epoch or even an unspecified epoch, but only the first one is a well defined point in time; the second relies on context, namely the timezone configured in the computer; and the third is not properly defined at all.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">When using date/time types that do not offer timezone information use UTC time.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">The question now is “Is this enough?”</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">For applications that just need to record when something happens, it is indeed enough. </span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">Timezone only matters when the time needs to be formatted in a human readable representation; at other times, date/time information can live happily as an opaque date/time variable.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">There are applications where the local time of the event is important.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">When I look at a photo taken on a holiday, I’d like to know that it was taken at two in the afternoon local time. </span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">If the photo in this example contained location information theoretically that would be enough to figure out the local time, but in practice the offset from UTC in time zones changes occasionally and the period when daylight time savings are in effect for a specific timezone changes much more often. This is information that is not readily available, so the best approach is to store the time using a well defined epoch, and the offset from UTC.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">For run-time .Net provides the </span><a href="http://msdn.microsoft.com/en-us/library/system.datetimeoffset.aspx"><span style="color: #1155cc; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">DateTimeOffset</span></a><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> data type which stores a local DateTime and its offset from UTC. Java does not provide a class for storing time and timezone information, but provides the </span><a href="http://docs.oracle.com/javase/7/docs/api/java/util/SimpleTimeZone.html"><span style="color: #1155cc; font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">SimpleTimeZone</span></a><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> class which can be used to convert Date objects to local string representation (remember in Java all Date objects should be UTC.)</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">For persistent storage, data types which support timezone offset in databases should be used if available.</span><br /><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;">If everything else fails time can be persisted as a standard string representation that includes timezone offset information such as the full date and time in ISO 8601, but this approach requires parsing the strings before any computations.</span></b>Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com0tag:blogger.com,1999:blog-8256953501100447692.post-22194736406557952352011-11-08T11:56:00.000+11:002013-02-25T21:10:31.579+11:00Not everything needs to be Fluent<br />
<div style="background-color: white; font-size: 11pt; padding-bottom: 72pt; padding-left: 72pt; padding-right: 72pt; padding-top: 72pt; width: 451.3pt;">
<div class="c1" style="direction: ltr;">
Well designed <span class="c2 c5" style="color: blue; text-decoration: underline;"><a class="c11" href="http://martinfowler.com/bliki/FluentInterface.html" style="text-decoration: inherit;">Fluent interfaces</a></span> are great. The use of natural language like methods instead of properties and configuration classes make it possible to just jump in and start using complex APIs without the usual learning curve.</div>
<div class="c1" style="direction: ltr;">
The problem is that fluent interfaces have become a fad and they are appearing everywhere even in APIs where there is no real place for them.</div>
<div class="c3 c1" style="direction: ltr; height: 11pt;">
</div>
<div class="c1" style="direction: ltr;">
There are very many things I don’t like about the <span class="c2 c5" style="color: blue; text-decoration: underline;"><a class="c11" href="http://docs.amazonwebservices.com/sdkfornet/latest/apidocs/Index.html" style="text-decoration: inherit;">.Net SDK for Amazon Web Services (AWS)</a></span>, but the one thing that grates my nerves the most is the way the SDK uses fluent interfaces, but first one of the things I do like about the SDK: ‘It is consistent’.</div>
<div class="c1" style="direction: ltr;">
All the operations of the SDK are accessed the same way:</div>
<ol class="c7" start="1" style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">
<li class="c6 c1" style="direction: ltr; margin-left: 36pt; padding-left: 0pt;"><span class="c10">Create a client for the AWS service you need (or use an existing one).</span></li>
<li class="c1 c6" style="direction: ltr; margin-left: 36pt; padding-left: 0pt;"><span class="c10">Create a request class specific to that operation with information about what you want to do.</span></li>
<li class="c6 c1" style="direction: ltr; margin-left: 36pt; padding-left: 0pt;"><span class="c10">Call the method for that operation with the request class as a parameter and get a response.</span></li>
</ol>
<div class="c1" style="direction: ltr;">
So the code to upload a file to an S3 bucket (AWS’s file storage) looks like:</div>
<div class="c1" style="direction: ltr;">
<span class="c0 c4" style="color: green; font-family: Consolas; font-size: 9pt;">// Create the S3 service client</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">var</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> s3Client = </span><span class="c0 c8" style="color: #2b91af; font-family: Consolas; font-size: 9pt;">AWSClientFactory</span><span class="c0" style="font-family: Consolas; font-size: 9pt;">.CreateAmazonS3Client(accessKey, secretKey);</span></div>
<div class="c3 c1" style="direction: ltr; height: 11pt;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"></span></div>
<div class="c1" style="direction: ltr;">
<span class="c0 c4" style="color: green; font-family: Consolas; font-size: 9pt;">// Create the request object and configure it</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">var</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> putRequest = </span><span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">new</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> </span><span class="c0 c8" style="color: #2b91af; font-family: Consolas; font-size: 9pt;">PutObjectRequest</span><span class="c0" style="font-family: Consolas; font-size: 9pt;">();</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;">putRequest.WithBucketName(bucketName)</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> .WithKey(filename)</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> .WithCannedACL(</span><span class="c0 c8" style="color: #2b91af; font-family: Consolas; font-size: 9pt;">S3CannedACL</span><span class="c0" style="font-family: Consolas; font-size: 9pt;">.PublicRead)</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> .WithContentType(mimeType))</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> .WithInputStream(fileStream);</span></div>
<div class="c3 c1" style="direction: ltr; height: 11pt;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"></span></div>
<div class="c1" style="direction: ltr;">
<span class="c0 c4" style="color: green; font-family: Consolas; font-size: 9pt;">// Get the (disposable) response</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">using</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> (s3Client.PutObject(putRequest)){}</span></div>
<div class="c3 c1" style="direction: ltr; height: 11pt;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"></span></div>
<div class="c1" style="direction: ltr;">
Note that the request object uses a fluent interface to set its properties and that the code itself is too verbose and does not look very fluent. In fact you’d expect to be able to rewrite the above code thus:</div>
<div class="c1" style="direction: ltr;">
<span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">using</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> (s3Client.PutObject(</span><span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">new</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> </span><span class="c0 c8" style="color: #2b91af; font-family: Consolas; font-size: 9pt;">PutObjectRequest</span><span class="c0" style="font-family: Consolas; font-size: 9pt;">()</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> .WithBucketName(bucketName)</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> .WithKey(blobId)</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> .WithCannedACL(</span><span class="c0 c8" style="color: #2b91af; font-family: Consolas; font-size: 9pt;">S3CannedACL</span><span class="c0" style="font-family: Consolas; font-size: 9pt;">.PublicRead)</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> .WithContentType(mimeType)</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> .WithInputStream(fileStream)</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> )) {}</span></div>
<div class="c1" style="direction: ltr;">
If so, you’d be wrong. The second more fluent snippet of code does not compile, but more on that later.</div>
<div class="c1" style="direction: ltr;">
Fluid interfaces are a meta-language, a language built on top of the actual programming language and for a fluent interface to be good it should provide better semantics that the language it is replacing.</div>
<div class="c1" style="direction: ltr;">
In order to upload a file (put an object) to S3 you must absolutely specify the name of the bucket you’re uploading to, the intended name for the file in S3 and the stream containing the data being uploaded. This means the WithBucketName, WithKey and WithInputStream methods of PutObjectRequest are not optional. Calling PutObject without first calling these methods results in an exception.</div>
<div class="c1" style="direction: ltr;">
A more robust API design would have these properties as constructor parameters, so if a developer forgets to specify one of them the result will be a compile time error, not a run-time error that may or may not be caught during testing.</div>
<div class="c1" style="direction: ltr;">
Strong typed languages are less prone to bugs caused by absent-mindedness than dynamic languages, because most typos and missing/misplaced parameters are caught at compile time. Dynamic languages trade robustness for ease and speed of development, which depending on the problem domain can be a good trade off; trading robustness for stylistic reasons, is not.</div>
<div class="c1" style="direction: ltr;">
Using optional methods in a fluent interface to set properties should only be done if the properties have a sensible default value.</div>
<div class="c1" style="direction: ltr;">
Fluid interfaces are a<span class="c12" style="font-style: italic;"> facade</span>, the API equivalent of a user interface; and like good user interfaces they require a great deal of effort. The reason the second snippet of code does not compile is that the fluid interface of the .Net SD for AWS was not designed as a separate facade.</div>
<div class="c1" style="direction: ltr;">
PutObjectRequest, like all the other request objects, inherits from S3Request. There is hopefully a valid architectural design reason for this, but it breaks the fluid interface:</div>
<div class="c1" style="direction: ltr;">
WithBucketName is a method of PutObjectRequest that returns PutObjectRequest (as expected in a fluid interface,) but WithInputStream is a method of the base class and returns an S3Request that returns an S3Request.</div>
<div class="c1" style="direction: ltr;">
S3Client.PutObject expects a PutObjectRequest as its only parameter.</div>
<div class="c1" style="direction: ltr;">
The code</div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;">s3Client.PutObject(</span><span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">new</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> </span><span class="c0 c8" style="color: #2b91af; font-family: Consolas; font-size: 9pt;">PutObjectRequest</span><span class="c0" style="font-family: Consolas; font-size: 9pt;">().WithInputStream(fileStream))</span></div>
<div class="c3 c1" style="direction: ltr; height: 11pt;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"></span></div>
<div class="c1" style="direction: ltr;">
won’t compile because the type S3Client returned by WithInputStream is not assignable to the input argument of type PutObjectRequest expected by the method.</div>
<div class="c1" style="direction: ltr;">
For a similar reason:</div>
<div class="c3 c1" style="direction: ltr; height: 11pt;">
</div>
<div class="c1" style="direction: ltr;">
<span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">var</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> p =</span><span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;"> new</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> </span><span class="c0 c8" style="color: #2b91af; font-family: Consolas; font-size: 9pt;">PutObjectRequest</span><span class="c0" style="font-family: Consolas; font-size: 9pt;">().WithBucketName(bucketName).WithInputStream(fileStream);</span></div>
<div class="c3 c1" style="direction: ltr; height: 11pt;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"></span></div>
<div class="c1" style="direction: ltr;">
compiles, but:</div>
<div class="c3 c1" style="direction: ltr; height: 11pt;">
</div>
<div class="c1" style="direction: ltr;">
<span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">var</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> p =</span><span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">new</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> </span><span class="c0 c8" style="color: #2b91af; font-family: Consolas; font-size: 9pt;">PutObjectRequest</span><span class="c0" style="font-family: Consolas; font-size: 9pt;">().WithInputStream(fileStream).WithBucketName(bucketName);</span></div>
<div class="c3 c1" style="direction: ltr; height: 11pt;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"></span></div>
<div class="c1" style="direction: ltr;">
<span class="c0 c8" style="color: #2b91af; font-family: Consolas; font-size: 9pt;">does </span>not.</div>
<div class="c3 c1" style="direction: ltr; height: 11pt;">
</div>
<div class="c1" style="direction: ltr;">
In the end one has to wonder whether a fluid interface makes any sense in this case.</div>
<div class="c1" style="direction: ltr;">
A non-fluid version of the call to PutObject could consist of:</div>
<div class="c1 c3" style="direction: ltr; height: 11pt;">
</div>
<div class="c1" style="direction: ltr;">
<span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">using</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> (s3Client.PutObject(</span><span class="c0 c2" style="color: blue; font-family: Consolas; font-size: 9pt;">new</span><span class="c0" style="font-family: Consolas; font-size: 9pt;"> </span><span class="c0 c8" style="color: #2b91af; font-family: Consolas; font-size: 9pt;">PutObjectRequest</span><span class="c0" style="font-family: Consolas; font-size: 9pt;">(bucketName,</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> blobId,</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> fileStream)</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> {</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> CannedAcl = </span><span class="c0 c8" style="color: #2b91af; font-family: Consolas; font-size: 9pt;">S3CannedACL</span><span class="c0" style="font-family: Consolas; font-size: 9pt;">.PublicRead,</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> ContentType = mimeType</span></div>
<div class="c1" style="direction: ltr;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"> }) {}</span></div>
<div class="c3 c1" style="direction: ltr; height: 11pt;">
<span class="c0" style="font-family: Consolas; font-size: 9pt;"></span></div>
<div class="c1" style="direction: ltr;">
Is there really any benefit in converting this to a fluid interface?</div>
</div>
Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com0tag:blogger.com,1999:blog-8256953501100447692.post-46998287419802311202010-03-31T04:31:00.000+11:002010-03-31T05:22:13.589+11:00Error HandlingError handling is a much debated topic.<br />
A large portion of the discussion centers on the technical aspect of error handling: when to throw exceptions and when to return error status, how to incorporate error reporting facilities, etc. but the subject is much wider.<br />
In this post I’d like to explore the topic at a higher level and focus on the validation code, the code that generates the errors: Where to write validation code, and how much validation code to write so that the errors can actually be handled.<br />
<br />
<span style="font-size: medium; font-weight: bold;">There is no fail safe program</span><br />
The naive approach to validation is to regard each method (function, procedure, subroutine, etc.) as a separate entity. As such each method needs to, on the one hand validate all inputs, i.e. accept only valid inputs and reject all others with an error, and on the other hand handle all possible errors generated by called methods.<br />
The naive approach tries to achieve fail safety by attempting to cover every eventuality. This is not only unrealistic, but also impossible:<br />
<ol><li>It is not possible to validate all input.</li>
Let’s look at a simple example: binary search. For binary search to work it requires the random access list which is one of its inputs to be sorted. Can we verify the input to binary search is sorted? Not in O(log(n)) time complexity.
<li>It is not possible to handle all errors.</li>
There are whole sets of error types programmers choose to ignore: hardware failures, memory corruption, bugs in driver software, etc. Most programs won’t even try to cope with memory allocation errors (out of memory exceptions.) Some of these failures are handled by the operating system others require special hardware. Unless the program is critical, chances are it is perfectly acceptable for the program to crash under these circumstances.
<li>Validation code is not bug free.</li>
</ol>Bugs are statistical beings the more validation code written the higher the chance a bug during validation will cause the program to fail.<br />
<br />
<span style="font-size: medium; font-weight: bold;">Managing failures with contracts</span><br />
A powerful approach for designing reliable programs is called Program by Contract.<br />
Using this approach each software unit (method/class/library) in the program is designed using a Contract.<br />
<br />
The contract contains three parts:<br />
<ol><li>A set of valid entry states usually stated as pre-conditions that must be met before calling the unit, and the set of inputs recognized by the unit.</li>
<li>The action performed by the unit for each valid entry state.</li>
<li>The state of the unit (or post-condition) after the call completes.</li>
</ol>The important thing to remember about contracts is that they not only specify how the unit succeeds, but also how it fails.<br />
Let’s take the file IO operations for instance. Attempting to open a file without sufficient permission fails, but fails in an expected way (throwing a security exception or returning an appropriate error code.)<br />
What happens if the function is not called from a valid entry state, i.e. pre-conditions are not met or the input is not recognized?<br />
In this case the result is undefined, as in anything can happen.<br />
<br />
To illustrate this let’s look at the binary search function once more. The contract for binary search specifies that given a value and a random access sorted list, the search can find (in O(log(n)) time complexity) the index of the value in the list if it exists or the insertion point for the value so the list remains sorted otherwise.<br />
If the list is not sorted then binary search returns some index in the list as it is not possible to define what index is returned without knowing the exact values of the list.<br />
<br />
Using contracts allows us to write less code.<br />
If we can ensure an entry state that causes an error is never used then the error will never occur and we don’t need to write error handling code.<br />
Furthermore if we know an input is validated by a method in the façade, we don’t need to write validation code for the same input in an internal method; and approach called “perimeter defense”.<br />
How does having less validation and error handling code make for more reliable programs? It may sound counter-intuitive but reliability is not obtained through more validation.<br />
Contracts define how a software unit behaves and not having to write unnecessary code leaves time to concentrate on the two activities that do increase reliability:<br />
<ol><li>Designing the program so that preconditions are always met so only expected errors can happen and are handled.</li>
<li>Writing unit tests to verify the software units comply with the contract.</li>
</ol>There are other ways in which having less unnecessary code increases reliability. The code is more readable and as mentioned validation and error handling code is not bug free. The worst case scenario is for a program to fail due to a bug in error handling code for an error that doesn't happen.<br />
<br />
<span style="font-size: medium; font-weight: bold;">Validation code revisited</span><br />
As mentioned contracts allows removal of unneeded validation and error handling code. While we definitely do not want to write error handling code for errors that should never happen, we may still want to leave more validation code than strictly necessary in place.<br />
The first and most important reason is to make the code more reusable. Errors that can never occur in one program may be inevitable in another. Having validation code in place means the contract is less onerous on the side of the caller and we can use the code in more situations.<br />
The second reason is to prevent unacceptable harm from unforeseen errors, such as those caused by bugs. Database foreign key constraints are an example of this type of validation. Programs that use a database do not really need foreign keys. Business logic in the middle tier is usually coded with the relations in mind. In fact inserting and removing data is many times less efficient than it could be because of them.<br />
However we still use them to ensure data integrity remains even if there is a bug in the business logic.<br />
The benefits of extra validation must always be weighted against the impact on performance, reliability and readability of the overall program.Eli Algrantihttp://www.blogger.com/profile/04473826236028677409noreply@blogger.com0