A Really Bad Idea -- Adding Clutter to A Language

A DBA suggested that I read up on "Practical API Design: Confessions of a Java Framework Architect".

Apparently the DBA had read the phrase "direct compiler support of versioning of APIs" in a review of the book and -- immediately -- become terribly confused.

I can see why a DBA would be confused. From a DBA's point of view all data, all processing and all management-- all of it -- is intimately tied to a single tool. The idea behind Big Relational is to conflate configuration management, quality assurance, programming and the persistent data so that the product is inescapable.

[The idea is so pervasive that not using the RDBMS has to be called a "movement", as in "NoSQL Movement". It's not a new idea -- it's old wine in new bottles -- but Big Relational has become so pervasive that avoiding the database makes some folks feel like renegades.]

Adding to the confusion is the reality that DBA's live in a world where version management is difficult. What is an API version number when applied to the database? Can a table have a version? Can a schema have a version?

[IMO, the answer is yes, database designs -- metadata -- can easily be versioned. There's no support in the database product. But it's easy to do with simple naming conventions.]

For a DBA -- who's mind-set is often twisted into "one product hegemony" and "versioning is hard" -- the phrase "direct compiler support of versioning of APIs" maps to "direct tool/database/everything support of versioning." Nirvana.

All Things in Moderation

A relevant quote from the book is much more sensible than this fragment of a review. "Some parts of the solution should be in the compiler, or at least reflected in the sources, and processed by some annotation processor later."

API versioning is not a good idea for adding to a programming language. At all. It's entirely a management discipline. There's no sensible avenue for "language" support of versioning. It can make sense to carry version information in the source, via annotations or comments. But to augment a language to support management can't work out well in the long run.

Why not?

Rule 1. Programming Languages are Turing Complete. And Nothing More. Syntactic sugar is okay, if it can be proven to be built on the Turing complete core language. Extra "features" like version control are well outside the minimal set of features required to be Turing complete. So far outside that they make a completeness proof hard because there's this extra stuff that doesn't matter to the proof.

Therefore: Don't Add Features. The language is the language. Add features via a toolset or a annotation processor or somewhere else. Your API revision junk will only make the proof of completeness that much more complex; and the proof won't touch the "features".

Rule 2. Today's Management Practice is Only A Fad. Version numbering for API's with a string of Major.Minor.Release.Patch is simply a trendy fad. No one seems to have a consistent understanding of what those numbers mean. Further, some tools (like subversion) simply using monotonically increasing numbers -- no dots.

Someday, someone will come up with an XML Feature Summary (XFS) for describing features and aspects of the the API, and numbers will be dropped as uselessly vague and replaced with a complex namespace of features and feature enumeration and a URI referencing an RDF that identifies the feature set. Numbers will be replaced with URI's.

Therefore: Don't Canonize Today's Management Practice in the Language. When the current practice has faded from memory, we don't want to have to retool our programming languages.

What To Do?

What we do for API version control is -- well -- hard work. Annotations are good. A tool that scrapes out the annotations to create a "profile" of the API might be handy.

In Python (and other dynamic languages) it's a much simpler problem than it is in static languages like Java and C++. Indeed, API version management may be one of the reasons for the slow shift from static to dynamic languages.

If we try to fold in complex language features for API version support, we introduce bugs and problems. Then -- when management practice drifts to a new way of handling API's -- we're stuck with bad language features. We can't simply deprecate them, we have to find a new language that has similar syntax, but lacks the old-bad API management features.

Distutils

Python distutils has a nice "Requires", "Provides" and "Obsoletes" specification that's part of the installation script. This is a handy level of automation: the unit of configuration management (the module) is identified at a high level using simple numbers. More than this is probably ill-advised.

And -- of course -- this isn't part of the Python language. It's just a tool.

What Innovation Looks Like

Check out "End User 2.0: When Employees Have All The Answers" in InformationWeek. This is about adoption of non-approved technology. Think iPad.

This shows what innovation looks like when it happens.

1. There's no process for innovation.

2. There's no "permission to fail". Folks just fail or succeed without anyone's support or permission.

3. It's disruptive. Many IT departments don't know how to cope with USB drives, iPads and related leading-edge technology. So these things are simply banned. (Ever walked past a sign that says "No Recording Devices Allowed Beyond This Point" with your iPhone?)

Here's one great quote: "Policies around regulatory compliance, reliability, budget approvals, and support all give IT teams reasons to resist technology driven by end users."

Technology innovation is happening. It is disruptive. Therefore, IT tends to resist the disruption.

The best stall tactic: "Security". If IT lifts up security as an issue, they can resist technology innovation effectively.

Other Disruptive Change

This happens everywhere. It isn't just the iPad. All disruptive, innovative change is met with serious resistance.

Agile Methods. Some IT departments resist agile methods because -- obviously -- the lack of a comprehensive and detailed project plan is a problem. Failure to plan is a plan for failure. The idea of building intentional flexibility into predicting the future is rejected. It's too disruptive to the IT chain of command to reduce the need for project managers.

Dynamic or Functional Programming Languages. It was painful to adopt Java (or C#). Adopting another, different language like Python is insanity. Obviously. Anyone in "Big IT" who is a serious Java or C# developer can tell you that a dynamic language is obviously unsuitable for production use. Mostly, the reasons boil down to "it's different"; different is too disruptive.

N0SQL Data Management. Clearly, the relational database is the only form of persistence that can possibly be used. It is perfect in every way. It can be used as a message queue (because adopting an actual message queue is too much work). It can be used for temporary or transient data. It can be used for non-relational objects like XML documents. Any suggestion that we use something other than a database is often met with derision. Clearly, a non-SQL database is disruptive to the orderly flow of data.

Simplified Architecture. [This is code for "No Stored Procedures".] Since stored procedures have been used with mixed success, some folks argue that they should be used more. On the other hand, it seems peculiar to me to intentionally fork application logic into two places -- application code and database. It seems to add complexity with no value. Lots of DBA's try to explain that some logic is "lower-level" or "more closely associated with the data" or "is a more 'essential' business rule." There's no dividing line here that makes stored procedures necessary or useful.

Try to prevent the problems associated with stored procedures and you will receive a $#!+-storm of abuse. Every time. Reducing the use of stored procedures is a disruptive change. An innovation. A bad thing.

[Want proof of the non-essential nature of stored procedures? Watch what happens when to upgrade or replace an application and migrate your data. Did you need the stored procedures? No, you left those behind. You only kept the data.]

Sustaining Innovation or Placating Management

Computerworld, July 26, 2010 cover story was "Sustaining Innovation". The magazine was chock-full of thoughts on innovation.

Much of it was good, but some of it is just appeasement to management types who are focused on cost and schedule.

"Establish processes and ownership. Organizations should have a process for taking a promising idea and testing it out..."

Innovation is disruptive. Having a process seeks to minimize disruption. Indeed, most processes are there to block real innovation and constraint the energies into pre-approved areas of innovation.

Which do you want? Innovative change or a nice, controlled process so that things don't change?

The number one item -- in the side bar -- is brilliant. "Give employees the right to fail."

Epic advice. And almost impossible to follow, hence the appeasement item. Permission to fail means -- well -- failures and money "wasted". Cost and schedule shot in an attempt to improve.

Juxtaposition

There's an interesting juxtaposition with another article in the same issue. "When Good Projects Go Bad".

This: "Push for due diligence at the start of a project." This is the perfect way to stifle innovation.

I realize the two articles are almost unrelated, but they're side-by-side in the print edition. Any cowardly project manager knows that we can't really give people permission to fail. We need to have a well-defined process, perform due diligence, and then assure that all projects are either denied up front or are a ringing success.

It's easy to avoid the important lesson on innovation (innovation == failure) and focus on the appeasement words: "process" and "due diligence".

Transformation Pipelines

My laptop chartplotter software (GPSNavX) is marvelous for visualizing a route. But, there are elements to route planning that it doesn't handle gracefully.

Specifically, it doesn't provide useful elapsed time calculation at all. While the TTG and ETA (Time to Go and Estimated Time of Arrival) for the next waypoint are essential, they aren't enough. On a sailboat, you need to know the complete sequence of planned arrival times so that you can gauge the overall impact of wind and tide vagaries on the trip.

As the trip progresses, the schedule variance allows informed decision-making. Tack? Hole up for the night? Motor?

GPSNavX has a number of marvelous export capabilities -- as GPX, KML or CSV data. These contain a list of waypoints and little more. We need to enrich this data to produce an overall schedule.

Enriching The Data

Fundamentally, we have a number of enrichment stages. The functional programming features of Python make this complex sequence of enrichment stages into a very tidy, and adaptable application.

    if variance is None: variance= chesapeake
with open(route_file,'rb') as source:
  rte= csv.reader(source)
  with open(schedule_file,'wb') as target:
      rte_rhumb= csv.writer( target )
      rte_rhumb.writerow(
          ["Name", "Lat", "Lon", "Desc",
          "Distance (nm)", "True Bearing", "Magnetic Bearing",
          "Distance Run", "Elapsed HH:MM", ]
          )
      for sched in gen_schedule( gen_mag_bearing( gen_rhumb( gen_route_points( rte ) ), variance), speed ):
          rte_rhumb.writerow(
              [sched.point.name, sched.point.lat, sched.point.lon, sched.point.desc,
              sched.distance,
              int(sched.true_bearing.deg) if sched.true_bearing is not None else None,
              int(sched.magnetic.deg) if sched.magnetic is not None else None,
              sched.running, sched.elapsed_hm, ]
              )

Essentially, the core of this is a simple composition of generator functions.

for sched in gen_schedule( gen_mag_bearing( gen_rhumb( gen_route_points( rte ) ), variance), speed ):

We have a number of individual transformations to look at. Each of those follows a common pattern. The transformations are:

• gen_route_points( rte )
• gen_rhumb( route_points_iter )
• gen_mag_bearing( rhumb_iter, declination )
• gen_schedule( rhumb_mag_iter, speed= 5.0 )

There's a bunch of fancy math involved in the rhumb line and distance between two points on the surface of the earth. For my purposes, plane sailing is fine. I don't need great circle routes because I'm not going far.

See Calculate distance, bearing and more between Latitude/Longitude points for a clear and useful treatment of the math. Bowditch's American Practical Navigator, chapter 24, provides alternate methods using table lookup and interpolation.

Seeding the Pipeline

Here, for example, is the first step of the pipeline. Creating a RoutePoint from the four values found in the CSV or GPX route file.

RoutePoint= namedtuple( 'RoutePoint', 'name,lat,lon,desc,point' )

def gen_route_points( rte ):
for name,lat,lon,desc in rte:
   point= navigation.LatLon( lat, lon )
   yield RoutePoint( name, lat, lon, desc, point )

Since this is a generator function, it can use an iterator and be used by an iterator. The source iterator can be a csv.reader. Or it can be the result of XML parsing -- just so long as it matches the interface specification of being an iterator over a 4-tuple.

Rhumb-Line Calculation

We'll enrich the data. But we won't update an object. We'll stick closely to the philosophy of immutable objects (i.e., named tuples) which are modified by a generator function.

RoutePoint_Rhumb= namedtuple( 'RoutePoint', 'point,distance,bearing' )

def gen_rhumb( route_points_iter ):
p1= route_points_iter.next()
for p2 in route_points_iter:
    r, theta= navigation.range_bearing( p1.point, p2.point )
    yield RoutePoint_Rhumb( p1, r, theta )
    p1= p2
yield RoutePoint_Rhumb( p2, None, None )

The essential calculations are in a separate module, navigation. What we've done, however, is merge information from adjacent values so that we can transform a simple list of points into a list of pairs of points: the from and to for each leg of the trip. Between the two points, we compute the simple rhumb line, the distance and bearing.

True to Magnetic Conversion

We need to enrich our waypoint rhumb-line information with magnetic compass information. The true course needs a declination or variance value added to it. Again, we're just creating new objects from existing objects, using immutable named tuples.

RoutePoint_Rhumb_Magnetic= namedtuple( 'RoutePoint', 'point,distance,true_bearing,magnetic' )

def gen_mag_bearing( rhumb_iter, declination ): # A/k/a Variation
 for rp_rhumb in rhumb_iter:
     if rp_rhumb.bearing is None:
         yield RoutePoint_Rhumb_Magnetic(rp_rhumb.point, None, None, None)
     else:
         magnetic= rp_rhumb.bearing+declination(rp_rhumb.point)
         yield RoutePoint_Rhumb_Magnetic(rp_rhumb.point, rp_rhumb.distance, rp_rhumb.bearing, magnetic )

In this case, we're simply including a declination calculation. While the model is available from IUGG, we can often use averages or approximations. And -- in the Chesapeake -- the approximation is simply to add 11 degrees.

Spot-on accuracy doesn't matter, since we're driving a sailboat. The compass isn't very accurate; the boat motion makes it hard to read precisely; and current as well as leeway have profound effects. It is helpful to have magnetic courses in the schedule instead of true courses. Further, the chartplotter will be computing the final CTS (Course to Steer).

So this stage in the pipeline might be optional.

Distance Run and Elapsed Time

The final distance run and elapsed time is pretty simple. We're creating a new tuple from existing tuples.

SchedulePoint = namedtuple( 'RoutePoint', 'point,distance,true_bearing,magnetic,running,elapsed_min,elapsed_hm' )

def gen_schedule( rhumb_mag_iter, speed= 5.0 ):
  distance = 0.0
  for rp in rhumb_mag_iter:
      if rp.true_bearing is None:
          yield SchedulePoint( rp.point, rp.distance, rp.true_bearing, rp.magnetic, None, None, None )
      else:
          distance += rp.distance
          elapsed_min= 60.*distance/speed
          h, m = divmod( int(elapsed_min), 60 )
          elapsed_hm = "{0:02d}h {1:02d}m".format( h, m )
          yield SchedulePoint( rp.point, rp.distance, rp.true_bearing, rp.magnetic, distance, elapsed_min, elapsed_hm )

This gives us a tuple that includes the original way point, the next waypoint, the distance, true bearing, magnetic bearing, total distance run, and elapsed time.

The amount of programming is minimal. The overall design seems reasonably flexible and built from small, easy-to-validate pieces.

Python's functional programming features -- particularly generator functions and named tuples -- seem to make it pleasant to write this kind of transformation pipeline.

Legacy Data Preservation

Extracting legacy data can be really, really hard. However, it's of central importance because data lives forever. Application "logic" and "business rules" come and go.

Today's case study is a dusty old Dell Inspiron Laptop running Windows 98 with Chartview software.

Problem 1. Chartview. No extract or export capability, except to a GPS via a serial port. I guess I could solder up a PC serial connector to a serial-USB interface so my Mac could read the stream of NMEA 0183 messages that contain routes and waypoints. But that seems complex for a one-time transfer.

Problem 2. Windows 98. Won't mount any USB device I own. No solid-state disk, no rotating disk. Nothing. I have the original install CD with all the extra drivers. Didn't help.

Problem 3. Dell Laptop. DVD player, floppy disk drive, and a USB port that Windows 98 doesn't seem to know what to do with. No ethernet, only a modem connection.

How do we preserve the waypoints and routes on this ancient Dell so that we can replace it with a nice, new Standard Horizon CP 300i and MacBook Pro running GPSNavX?

Raw Data

To get the raw data, I pulled the disk drive, mounted it in an IDE-USB enclosure and pulled the relevant routes and waypoints files. Now we have something we can work with.

The file formats are undocumented, but the data's not complex, making it easy to explore. Also, we can look at the old Chartview GUI to see the data and compare it with the raw bytes on the file.

Modern software is more properly normalized, simplifying the conversion. The legacy Chartview route data included each waypoint -- unrelated to the master list of waypoints -- along with bearing and range information, as well as compass deviation and projected speed. Really. A modern GPX file as used by GPSNavX or iNavX only needs the waypoints. Nothing else. New software will correctly calculate range and bearing to next waypoint as well as lookup the magnetic deviation from standard tables. So we don't need to preserve all of the data.

Pass 1

The first pass is to write simple "hex dump" utility in Python to see what's even in the files.

Something like this seems to allow enough flexibility to get a good view of the record sizes and field contents in the file.

def hex_print( bytes, offset=0 ):
for section in xrange(0,len(bytes)+31,32):
    block= bytes[section:section+32]
    print( '    ', ' '.join( '{0:3d}'.format(x+section) for x in xrange(32) ) )
    print( "{0:4d}".format(offset+section), ' '.join( '{0:3d}'.format(ord(x)) for x in block ) )
    print( '    ', ' '.join(  "  {0}".format(x) if 32 <= ord(x) < 128 else '   ' for x in block ) )
    print()

def hex_dump( file, size=32 ):
offset= 0
with open(file,'rb') as data:
    print( '    ', ' '.join( '{0:3d}'.format(x) for x in xrange(size) ) )
    block= data.read(size)
    while block:
        hex_print( block, offset )
        block= data.read(size)
        offset += size

Once we have a sense of what's going on, we can use the Python struct module to get the real data.

Pass 2

In the case of Chartview marks, we have a complex, but manageable structure definition. Some of the field sizes are conjectures. It's possible that all those filler bytes are some kind of word or x386 paragraph alignment; it's also possible that I've misinterpreted some of the less relevant numeric fields. The two double-precision values, however, are rock solid.

mark_structure =   "=b 4x 6s 45x 502s   d   d 10x h h h 10x 8s x 6s x 8s x 6s 31x  f  f 32x"
Mark= namedtuple( 'Mark',
 "record, name, text, lat, lon, display_name, enable_mark, anchor_mark,"
 "dt1, tm1, dt2, tm2, arrival_radius, max_xte" )

Given this structure and the associated named tuple, we can write a pleasant (and highly reusable) generator function.

def gen_items( file, structure, record_class ):
 size= struct.calcsize( structure )
 with open(file,'rb') as data:
     block= data.read(size)
     while len(block) == size:
         raw= record_class( *struct.unpack(structure,block) )
         yield raw
         block= data.read(size)

This makes for a simple application to extract the marks. An application can reformat them into GPX or CSV format.

Something like this is a good starting point.

def print_marks( file ):
  for mark in gen_items( file, mark_structure, Mark ):
      print( strip(mark.name), mark.lat, mark.lon )

We can easily write a version which includes the formatting required to get the latitudes and longitudes into a format acceptable by GPSNavX or X-Traverse.

Routes

Routes are more complex than marks because they have a header, followed by the details as a sequence of individual waypoints. Since Chartview doesn't normalize these things, each route can have duplicate waypoints, making it very difficult to get them loaded into an application that normalizes properly.

How many WP1's can you have? The answer should be "one". Each additional WP1 is a problem. But it's a small problem. For the WPx points, we simply disambiguate them a route number. There seemed to have been a limit of 15 routes, so we can just expand WPx to WPx-rr, where rr is the route number.

Bottom Line

Data is preserved. Legacy PC and GPS can be chucked (or sold on eBay to a collector).

It's important to note that data outlives application software. This is a universal truth -- data lasts forever, applications come and go. Highly optimized data structures (like the legacy Chartview files) are a bad policy. Highly usable data structures (like GPX files) are more valuable.

Python does a marvelous job of making a potentially horrifying data conversion into something like a few evenings trying to find the key pieces of data in the legacy files. Perhaps the hardest part was tracking down single and double-precision floating-point numbers. But once they were found -- matching known latitudes and longitudes -- it was clear sailing.

Using SCons

In looking at Application Lifecycle Management (see "ALM Tools"), I had found that SCons appears to be pretty popular. It's not as famous as all the make variants, or Apache Ant or Apache Maven, but it seems to have a niche in the forest of Build Automation Software.

While it looks nice, parts of SCons are confusing. I struggled until I found a simple use case.

More: Empirical Comparison of SCons and GNU Make.

"SCons proved to be more accurate, mostly due to its stateful, content-based signature model.

On the other hand, GNU Make proved to be more resource friendly, especially regard- ing the memory footprint. SCons needs to address this problem to be a viable alternative to Make when building large software projects."

[Also, it appears that a lot of build and test automation have been reframed as "Continuous Integration". Which isn't really a bad thing. But it can be confusing because there are too many categories into which general-purpose tools can be fit.]

While SCons looks cool, I haven't had a huge need for it at work. Working in Python, there's no real "build". Instead our continuous integration boils down to unit testing. Our "build" is an SVN checkin. Our deployment is an SVN checkout and `python setup.py install`.

At some point, I would like to create an SConstruct file that runs our integration test suite. But it's trapped at a low priority.

SCons and Sphinx

I did find an SConscript example that automated a document build using Sphinx. This

sphinx-scons was quite cool. However, it was challenging to customize. The SCons documentation requires real work to understand. I could see the value, but it was a lot of work.

I'm hoping that No Starch Press finds someone to write a tidy introduction to SCons.

SCons and RST and LaTeX (oh, my!)

Sphinx has made me a total fanboi of ReStructured Text. While I know MS Word and iWorks Pages quite well, I have no patience with all the pointing and clicking. Getting consistency is requires consistent pointing and clicking; some people can do it, but some of us find that manual pointing and clicking is sometimes irreproducible. Semantic markup is a huge pain in the neck because we have to stop typing to click on the proper style hint for the various words.

I also know DocBook XML and LaTeX quite well. I've used very cool XML editors including XML Mind XML Editor (which is very nice.) I no longer have any patience with any of these tools because there's too much GUI.

RST is fun because you write in plain text. There are a few directives and a few bits of inline roles for semantic markup. But your work can focus on the content, leaving presentation aside. A command-line tool (with templates) emits HTML or LaTeX or whatever. The style considerations can be (a) secondary and (b) completely consistent.

RST will easily produce complex LaTeX from plain text. What a joy. LaTeX, of course, isn't the goal, it's just an intermediate result that leads to DVI which leads -- eventually -- to a PDF.

Because of the Unicode and font selection on the Mac, I'm a user of XeTeX and XeLaTeX. I have some problems with getting my copy of Blackadder ITC to work, but generally I'm able to write without much fussing around.

SCons has a great deal of the TeX/DVI/PDF tool chain already installed. However, it doesn't have either the rst2latex script or the XeTeX tools.

An SConscript

While my first attempts to understand SCons didn't work out well, looking at RST and XeLaTex was a much better use case.

I wound up with this.

rst2latex = Builder( action="rst2latex.py $SOURCES >$TARGET",

suffix='.tex', src_suffix='.rst',

)

xelatex = Builder( action=["xelatex $SOURCES", "xelatex $SOURCES"],

suffix='.pdf', src_suffix='.tex',

)

env = Environment(ENV=os.environ,

BUILDERS = { 'rst2latex' : rst2latex, 'xelatex':xelatex }

)

env.rst2latex('someDoc')

env.xelatex('someDoc' )

env.rst2latex('anotherDoc')

env.xelatex('anotherDoc')

Getting this to work was quite pleasant. I can see how I could further optimize the document production pipeline by combining the two Builders.

[And yes, the xelatex step is run twice to guarantee that the references are correct.]

Now, I can get away with write, run `scons` and review the resulting PDF. It's fast and it produces a nice-looking PDF with very little work and no irreproducible pointing and clicking.

Given this baseline, I can now dig into SCons for ways to make this slightly simpler.

Oracle Analytics and Functional Programming

As a hypothetical, I was asked about doing Oracle Analytic functions in Python.

[Sidebar: Politics. The question came from a DBA in a C# shop. That's why it's just hypothetical. Politically, they could never make use of this information. In C# world, Functional Programming is often scary and evil. It does exist -- obviously -- but it's called "Oracle Analytics", which makes it acceptable. If functional programming was called "functional programming" it would be unacceptable.]

[Sidebar: SQL. A big apples-to-oranges issue here is that the Oracle analytic functions can be composed in subqueries to write complex reporting queries. When you implement these functions in Python, you appear to lose some SQL "features" for report-writing. Actually, you move the SQL functional programming into Python functional programming. Everyone complains when they have to give up SQL. The true haterz say that using Python's functional programming for database processing subverts the database and leads to moral decay and the end of Western Civilization. Godwin's Law then applies.]

Specific functions lifted up to me were RANK, FIRST, LAST, ROW_NUMBER and DENSE_RANK. All of these are relatively simple functional programming examples.

GROUP BY

First -- and foremost -- SQL GROUP-BY can be slow. No one ever wants to hear this. The true haterz will claim that it's supposed to be faster and provide a bunch of non-reasons. Please benchmark.

Every database has to provide a GROUP-BY implementation that's perfectly general; it has to sort. A procedural program can sometimes do the same operation much more quickly because it doens't have to be perfectly general; we can make different memory use tradeoffs than the the database does.

For a fast GROUP-BY, use a hash map of some kind. Read all the rows in the simplest, fastest array fetch possible. Here's how you can do a blindingly fast SUM/GROUP-BY.

from collections import defaultdict

from decimal import Decimal

groups = defaultdict( Decimal )

for row in some_query_result_set:

groups[row['key']] += groups[row['value']]

Writing code like this is based on the assumption that the number of groups is small enough to fit into memory. That assumption allows us to avoid a sort. The database can't make this assumption, and can't easily use this kind of data structure.

Functional Programming

The analytical functions are nice applications of a functional style of programming. The ROW_NUMBER is already part of Python: it's the internal enumerate function.

We can use enumerate to implement FIRST.

def first( the_limit, some_query ):

for number, row in enumerate( some_query ):

if number >= the_limit:

break

yield row

This first function can be then used in another loop.

for row in first( 100, some_query ):

process( row )

LAST is more complex because there's no easy way to skip ahead. Instead we have to buffer some rows.

def last( the_limit, some_query ):

queue = []

for row in some_query:

if len(queue) == the_limit:

queue.pop( 0 )

queue.append( row )

for row in queue:

yield row

This can be sped up a little by using an explicit iterator object and breaking the `for row in some_query` loop into two phases to hoist the nearly-constant if-statement.

These can be composed with Python's sorted function.

for row in first( 10, sorted( some_query, key=lambda row: row['key'] ) ):

process( row )

This is elegant, but may only draw a tie in a race against the database. Python has the potential advantage of in-memory sorting. Oracle, however, is clever enough to use in-memory sorting on small sets of data, offsetting Python's advantage.

More Complex Functions

The more complex functions of RANK and DENSE_RANK require two-phase processing of rows. If we assume that we can fit the rows in memory, this isn't very complex at all. Indeed, the rank function is just a glorified order-by of a subset of the original data. However, it does require a potentially slow sort step.

ranked = enumerate ( sorted( some_query,

key = lambda row: row['key'] ) )

Okay. So RANK isn't so complex after all. Functional programming rocks.

DENSE_RANKED is confusing to me, but it look like the key phrase is "Rows with equal values for the ranking criteria receive the same rank." This means that the simple built-in enumerate isn't appropriate, and we need a key-aware enumeration.

def dense_rank( some_query, key ):

query_iter= iter(some_query)

rank= 1

current = query_iter.next()

yield rank, current

for row in query_iter:

if key(current) != key(row):

rank += 1

current= row

yield rank, row

Composition

One of the strong suits of SQL is that it allows us to define a functional-programming composition. Rather than write a lot of looping, we specify a series of functions which are composed and applied to our data.

For example, using FIRST and DENSE_RANK can be done like this.

for row in first( 10, dense_rank( some_ordered_query, key=lambda row: row['key'] ) ):

process( row )

This functional programming composition is -- BTW -- precisely what SQL specifies. SQL describes incremental processing of each row through a kind of pipeline that does map, filter, reduce and sort algorithms on the row.

The ORDER-BY clause is an initial sort.

The WHERE clause is an initial filter. It may involve a mapping if there are calculations in the various parts of the WHERE clause.

The GROUP-BY clause is a reduction into groups.

The HAVING clause is a second filter, applied to the groups. It may involve a mapping if there are calculations in the various parts of the HAVING clause.

Finally the SELECT clause is a mapping that does calculations on the resulting collection of rows.

The analytic functions, like subqueries, are simple complex mapping operations that involve other query pipelines.