Temporal Data Slides

17 10 2009

For whatever reason, some folks thought that having to listen to me for 45 minutes just wasn’t enough. So here are my slides.


XPath style filtering in E4X

15 10 2009

One area of Flex/ActionScript that seems under documented is how to do XPath style filtering with E4X. It works pretty much the same way. But with a syntax is just different enough that I’m left looking back thru my code every time I need to do it. Now I’ll look here instead 🙂

-- E4X
root.foo.(@attribute == 'bar').baz
-- XPath
/root/foo[@attribute == 'bar']/baz

Thinking in Sets of Sets

6 10 2009

Over the summer, I built the Chronos temporal toolkit for Oracle. And for the last while I’ve been porting it to Postgres and working on the documentation.

The SQL standard defines several data types to model instances of times. But rarely does anything happen instantaneously. Often what we need to model is a period of time; for instance an airline flight, a hotel stay, a bus route, an employee’s shift, a doctor’s appointment, a leave request. And yet there is no standard defined to handle this. So instead we use some combination of start and end time, an anchor time and interval, or an anchor time and a number representing seconds, minutes or days. Read the rest of this entry »

Internal Data Representation

3 10 2009

Have you ever wondered how your database represents the timestamp you just entered or how many bytes it takes to store a boolean or interval?

Oracle has a handy dump function that lets us peer into the internal representation of our data. Dump takes any type of input and returns a varchar2 describing the data type, length and the internal representation. Output might look like this:
‘Typ=96 Len=4: 84,101,99,104’
Read the rest of this entry »

Flex Gantt Charts

31 07 2009

There are a few examples of Gantt charts in Flex out there. And Doug McCune has a pretty good article on the topic.

But none of the existing implementations really met my needs (I’m picky). Most seemed more like bar charts than gantt charts and I wanted something specifically time based. So I spent the afternoon creating my own.

My design goals were to have a component:

  • That could be used stand alone or as and itemRenderer in DataGrid
  • Could be used in an appointment scheduling app, so clicking on an event should fire an event that lets me know exactly what was clicked and clicking on the time line should let me know at exactly what time (on the timeline) the user clicked.
  • Provided the user with feedback about where they were on the timeline. So tick marks and tooltips were essential.
  • Could be used to display ranges from seconds to years.
  • Was configurable and stylable.

So here is what I came up with. I created an actionscript class that extended the UIComponent class and implemented IListItemRenderer and IDataRenderer (so it could be used as an item renderer).

The component creates two layers then. The _bg layer for all of the tick marks and the _fg layer to display all of the events.  Drawing the _fg layer, adding the rectangles for each event and adding tooltips and click event handlers to each was pretty straight forward and you can check it out in the source code. I did want to show what I did when creating the background layer.

private function drawBackground():void
  while(_bg.numChildren > 0) {
  if(_startDt != null && _endDt != null) {
    var range:Number = (_endDt.time - _startDt.time) / 1000;
    var tickSize:Number = calcTickSize(range);
    var tickCnt:int = width / tickSize;
    var dt:Date;
    var tickColor:Number = getStyle('tickColor');
    var tickAlpha:Number = getStyle('tickAlpha');

    _bg.graphics.lineStyle(1, tickColor, tickAlpha);

    var tooltipBox:UIComponent = new UIComponent();
    tooltipBox.graphics.beginFill(0xFFFFFF, 0.0); //clear
    tooltipBox.graphics.drawRect(this.width - (tickSize / 4),0, tickSize / 4, this.height);
    tooltipBox.toolTip = DateUtils.format(_endDt, _endpointFmt);

    for( var i:int=0; i<tickCnt; i++ ) {
      tooltipBox = new UIComponent();
      tooltipBox.graphics.beginFill(0xFFFFFF, 0.0); //clear
      tooltipBox.graphics.drawRect(tickSize * i - tickSize / 4, 0, tickSize / 2, this.height);
      dt = new Date(_startDt.time + (_secondsPerTick * i * 1000));
      tooltipBox.toolTip = (i==0) ? DateUtils.format(dt, _endpointFmt)
        : DateUtils.format(dt, _tickFmt);
    if (i > 0) {
      // draw tick mark
      tooltipBox.graphics.lineStyle(1, tickColor, tickAlpha);
      tooltipBox.graphics.moveTo(tickSize * i, 0);
      tooltipBox.graphics.lineTo(tickSize * i, height);

So lets step through it. The first thing we have to do is clear out anything that is already in the bg layer so we don’t draw over top of it. Next we get the range (in seconds) of our timeline, and call the calcTickSize() function. This just analyzes the range and decides how far apart the tick marks should be and the display format for the tooltip display.

After when know how many tick marks we need, we loop through adding a tick mark surounded by a  transparent box so when the user mouses over a tick mark they can see what time it represents. And a clear box at each end of the chart displays a detailed date/time for that end.

Gantt Charts in Adobe Flex

Gantt Charts in Adobe Flex

Click image to load Flex app.

Porting Postgres favorites to Oracle

30 07 2009

For the last month or so, I have been creating temporal extensions for both Postgres and Oracle. Of the two, the Postgres version was easier to implement because the array functionality in Postgres is the best of any dbms out there. So for other developers who split their time between Postgres and Oracle, here is how to create the equivalent of array_agg(), unnest() and generate_series() in Oracle.
Read the rest of this entry »

Adobe Flex TimeInput Control

19 06 2009

So in a previous article I explained why working with time is difficult in Flex (but you probably already know that if you Googled your way here), and we created a helper class to make working with dates and times in Flex easier.

Now we will make a custom input control so time input and validation can be as slick as date entry. To do this we will extend the TextInput class to do the following:

  • restrict the input so that it will only accept characters found in time strings
  • add a value_commit handler so that it parses the text input after it has been changed
  • add a key_down handler so that we can increment and decrement the time when the directional arrows are pressed
  • format the time once it has been parsed
  • behave properly when used as an itemRenderer or itemEditor
Custom TimeInput control

Custom TimeInput control

Click on image to launch the Flex application. View source is enabled.