Flex, AIR, and SQLITE Newbie

I’m not a great programmer–I’ve always been the vision guy, and someone else dealt with most or all of the programming. But two months ago I decided to take over my own project and learn Flex from the ground up, just as Adobe is working on the next gen Flex/Air platform.

Initially I was able to make things happen fairly quickly–until I really wanted to control things. Microsoft would have tons of samples, tons of examples, and tons of people in the community contributing. And tons of 3rd-party objects to fill in the holes. Not Adobe.

Adobe could learn a lot from Microsoft, and I suspect there’s an arrogance informed by the Java world that informs some of the decisions to leave things out like a ROWS abstraction in the DataGrid object.

Yes, that’s right, rows are largely implicit–a set of column data at a certain index in your array. And then–I guess in an effort to maddeningly enforce the MVC (model-view-control) approach, there’s this concept of itemrenderers. Your columns and rows aren’t enough, you need a bucket that’s loosely and temporarily assigned to the cell value. And then when the grid has moved past the data, the bucket gets recycled. So unless your bucket management is really detailed and explicit, your cell values can, well, move around. Nice. Nothing like row.value.

Sure, I can build my own, but I don’t want to build fundamentals, I want to use the fundamentals to build solutions.

I’m finishing up the first part of the project in the next week–or month, as it goes when learning something completely new–and I’ve run into the frustrating relationship between SQLITE , AIR, and Actionscript handling of dates. They all handle them differently, yet here I am, using these three pieces together, hoping to find date sanity somewhere.

The documentation is lousy. The examples are lousy and incomplete. The expert blog posts are pretty good, except that the solution is lousy. Date functions should be obvious and easy, and date handling should be consistent from coding to database.

But no, not in AIR. The recommended format is Julian date format, which AIR converts to a double. That’s right, it stores it as a number. So if you want to read the date, you’re looking at 2455206.01260417, which is the date-time from, oh, can’t you tell? Yes, you have to apply a little date math. Julian date math. meaning you’re working with milliseconds…oh forget it.

This isn’t that hard, Adobe. We’re people, we’re human. We’re not all Java/Spring castoffs. If you want AIR/Flex to thrive, give us some simpler, more understandable, more convenient abstractions. Don’t make me create my own date utilities, or search for someone else’s Dates are basic, and you blew it with dates.

And sqlite, oh sqlite. This is where open source doesn’t make any sense. Sqlite has been around for quite a few years, yet it still has no DATE type, only date functions. So if you want to Order by taskDate and you’ve stored your date as text (and not as a double), sqlite of course will treat it as text and order it incorrectly for dates. So you have to apply date functions.

So today’s quandary is this: if I want to store the date value as a date, and read it as a date, and not store it as a double (which is what AIR does whether I want it to or not), how to I do this? I’ve set the data type as DATE. SQLite handles it fine–it’s undocumented. But Air sees that column type and converts the values I pass to it to a double. That’s just great. Because 2455206.01260417 is so nice to read.

Ugh.

If you want to learn more, here are some informative but not particularly useful links if you want to be able to read your dates while looking directly at the raw data.

AIR Julian Date explanation
http://www.verysimple.com/blog/category/web-servers/apache/

Adobe date column affinity explanation
http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html

Something else…
http://flexoop.com/2009/01/flex-date-utils-daysinyear-isleapyear/

Writing helps me think through issues, and sometimes clarifies them. Today7 I’m converting all of my date variables to Date types to take a crack again at doing it “right”, instead of storing everything as strings and coercing everywhere the values need to be treated as dates. I think the way to get around the storage as a double is to create a body double column and store the date as text in that column, so the date column has a readable translation.

Ouch–it’s almost 2455206.01360417 and I’m late. Gotta run.