Custom Software Development

sqlite is missing several basic database features compared to typical SQL servers and unfortunately padding is one of them.  For a most situations though, you can use a simple hack to get the same functionality.   This trick uses a combination of concatenation and substr:

-- the statement below is almost the same as
-- select lpad(mycolumn,'0',10) from mytable
 
SELECT substr('0000000000' || mycolumn, -10, 10) FROM mytable
 
-- the statement below is almost the same as
-- select rpad(mycolumn,'0',10) from mytable
 
SELECT substr(mycolumn || '0000000000', 1, 10) FROM mytable

The statement is fairly self-explanatory, but in case it doesn’t make sense we’re simply adding a big long string of characters to the original value and then truncating it down to the desired length.  The string used for concatenation has to be at least the same length that you are padding – 10 characters is used in this example (‘0000000000′).

In most cases this workaround produces the same results as lpad/rpad except in the case where the length of your original value is greater than the length that you are padding.  In which case the original value would get truncated.  Usually when you are padding you know what the maximum length of the column anyway.

If you know of a more efficient technique, please post a comment.

If you like your code clean and with proper doc block comments, Flex Formatter is a handy plugin for Flex Builder 3.  The plugin installs in Eclipse by simply dragging the files into the plugin directory, however with Flex Builder you can install from a remote install site hosted on the google code site.

To install in Flex Builder 3:

1. Help -> Software Updates -> Find and Install…
2. Choose “Search for new features to install” and click Next
3. Click “New Remote Site” button and enter the following in the dialog:
   • Name = Flex Pretty Print Command:
   • URL = http://flexformatter.googlecode.com/svn/trunk/FlexFormatter/FlexPrettyPrintCommandUpdateSite/
4. Click Finish and complete the process of installing the plugin

After installing and restarting Flex Builder you’ll have five new icons in your toolbar.  These allow you to add doc block and clean up spacing for selected sections or an entire file.

The settings for Flex Formatter are found along with all of the other Eclipse preferences.  You can choose to have files formatted automatically on save, specify locations to exclude and a few other options.

The Auto Format settings allows you to configure the template used when cleaning your code.  This allows you to specify exactly how you like your files formatted.  This is an extremely useful feature for organizations that enforce a certain code style.

I feel like I’m constantly ranting about Flex’s implementation of Dates.  This week I just discovered a new “feature” that is even more annoying to me.

Without going over it all again, Flex dates are timezone aware and the UTC offset is automatically calculated for you.  The problem is that you are locked into displaying the timezone of the local system clock unless you hack the date values.  But when sending over the wire to the server the date is automatically converted into the server’s timezone without telling you what timezone the Date is supposed to be in. In other words, you get “8:00 PM UTC” but you have no ideas what timezone that pertains to, so you don’t know whether the Date is actually supposed to read 2:00 PM EST, 1:00 PM CST, or 11:00 AM PST. Technically those are all the same “time,” but in some cases you need to work in timezones other than your own.

Anyway, I implemented my own solution for this issue last year, but was going on the assumption that the timezone offset is based only on the local system clock.  What I found out last week is that the timezone offset will actually change if you create a Date in the past or future where DST has kicked in/out.  In other words, every Date has it’s own offset.  I’ll admit that could probably be handy however if you are trying to neutralize the timezone changes that Flex does, it only makes your job that much harder.  I had not been expecting this and so we had to do some modification to our client to correctly handle DST changes at the server.

Below is some code to demonstrate how the timezone offset changes with DST, even though your system clock is still the same.  Note that you need to set your system clock to a timezone in the US that observes daylight savings time on March 15.  (Chicago for example). If you’re in the UK the swtich is March 28, so you can change the dates to March 27 & March 28 to see the effect. Notice that the timeZoneOffset property is different between the two dates. I suppose this makes sense but it definitely creates hassles on the server side.

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml"
      layout="vertical"
      height="700"
      width="1000"
      creationComplete="onCreationComplete()">
 
 <mx:Script>
  <![CDATA[
   public function onCreationComplete():void
   {
    var d1:Date=new Date(2010, 2, 14); // march 14
    result1.text="MARCH 14: " + d1.toDateString() + " OFFSET = " + d1.timezoneOffset.toString();
 
    var d2:Date=new Date(2010, 2, 15); // march 15
    result2.text="MARCH 15: " + d2.toDateString() + " OFFSET = " + d2.timezoneOffset.toString();
   }
  ]]>
 </mx:Script>
 
 <mx:Label id="result1"/>
 <mx:Label id="result2"/>
 
</mx:WindowedApplication>

I’m interested in anybody else’s ideas or solutions as to how you work with dates across timezones in Flex. Please post a comment.

I was very excited to read Adobe’s beta release notes about AIR 2.  AIR gives developers a very easy way to develop good looking, cross-platform applications.  The problem is that, in order to ensure all applications are cross-platform, Adobe was very stingy about how much you can reach out and interact with the underlying OS.

Those of us investing time in developing AIR apps have been working with one hand tied behind our back, unable to do simple things like even launch a document in MS Word, let alone utilize the thousands of shell commands available inside the OS.  (Well, there are various alternatives and hacks to do similar things, but still…)  Simple functions like burning a CD or encoding an audio file were simply not possible because the underlying libraries that perform these functions are not accessible within the AIR sandbox – even if those libraries themselves are cross platform.  None of the other major cross-platform environments have this limitation.  AIR was designed to connect to a server via HTTP in order to do these types of things, which is great for a web-based solution but not so for a desktop solution.  So AIR sometimes gets a bad rap as a a “widget” development platform that is good for eye candy, but not able to have deep interaction with the OS.

With AIR 2 Adobe decided to lift even more of the constraints and allow developer to execute native commands.  What this means is that we might start seeing very handsome AIR apps that serve as SVN front-ends, MP3 encoders, network monitoring tools, disk utilities, etc.   AIR will be able to interact with other software on the machine such as legacy enterprise apps.  I know this was not Adobe’s original vision for AIR, but  I have always thought otherwise and I’m very glad Adobe decided to loosen the shackles.  There are several other interesting improvements in version 2, but I think the native call feature has the potential to open the floodgates for enterprise developers.

With the new functionality comes the inevitable risk that some AIR apps will only run on one platform, or they may require you to install an OS-specific component in addition to the AIR application.  It will also be possible to build an AIR app that requires a certain Windows-only or OSX-only command.  However, thoughtful developers will at least be able to make that choice for themselves.

AIR 2 is out in beta now and end users will start seeing new applications when the public release launches in the first half of 2010.

The timezone functionality in Flex (as of SDK 3.4.1) makes me wonder if this is the person who designed this behavior.  It’s clear that the functionality was designed to magically handle the difficult subject of timezone and I appreciate that.  The problem is that, athough Flex goes through the trouble of calculating the UTC time automatically, you have no clean way to obtain input or display date values in anything other than the local system time.  So in order to display dates in other timezones, you actually have to enter incorrect UTC values and “trick” flex into showing the value that you want.  Additionally the remote server communication is very confusing and inconsistent with local (AIR) db storage.  The server has no way of knowing what the real value of your Date is unless you also provide it with the local clients current timezone offset.  Meanwhile, dates saved to a local DB have no timezone info stored with them at all.  Saving dates locally (AIR) can be downright dangerous if the system timezone changes while the app is open, including daylight savings changes.

If this is something that has caused you grief, I encourage you to vote for the issue to be fixed in the Flex SDK.

Read more