It's been a little while, but I've gotten around to working more on my Time Tracker AIR application, first discussed here. As a recap, my intention was to build a simple time tracker that would let me enter hours for projects and clients. The real goal was to learn more about SQLite and the AIR connection to it.
I've made some updates to the application and learned a bit on the way.
The first issue I ran into was my database setup code. My original code made one table, and when the table was done being created (if it needed to be), the application entered it's normal operating mode. This became a problem when I wanted to make two tables. If you remember, I had created an asynchronous database connection. I wasn't able to run both SQL statements in one execution so I had to two executions. How would I know when to start my program? I could use a bunch of flags, but that seemed like a mess.
So what I did was compromise. I kept one database connection in asynchronous mode. This would be my main connector to the database. In my startup code though I created another connection - one that was synchronous. This let me setup my tables and begin the application proper when done.
I also discovered that you want to be sure you think about your table structure. While it is possibly to modify tables, I just simply wrote a temp "drop table" code segment that I keep commented out most of the time.
I ran into this issue again on the new Projects page. This lets me create projects that are tied to clients. On loading the panel though we need to get both clients and projects. The issue was this though - what if projects loaded very quickly, but clients didn't? If you clicked to edit a project, I wouldn't be able to display the client drop down. So for this page I used a flag. I only allow you to edit projects once clients have finished loading. (As a quick release note, the Client filter on this page doesn't work, but the Active filter does.)
I added the Hour Entry page yesterday. It lets you add hours to a project. (Again though, no validation, so be sure to pick everything.) I loved how Flex had a built in numeric stepper control. It was perfect for this. Ditto for the calendar.
I ran into one more database issue here. I wanted to show the hours you entered today. (The reports page will let you view all hours, when I get to coding it.) But I'm having issues with date comparisons and SQLite. For now it will show everything.
I've include the AIR installer and full source code in the package. Enjoy. Again, assume I wrote horrible Flex code. I'm going to blame the Saints for not making it to the playoffs for my lack of Flex skills.
Archived Comments
Do you know if the lack of remote SQL connection is part of the "BETANESS" of AIR or it will never happen without the need of a remote web language?
It will be nice to create / connect to sqlite databases on a remote central machine.
I honestly don't know. I know AIR has network abilities - so in theory someone could write the connectors one time and share it with the world.
Hmmm I will have to dig on that, it could be the thing I need. Thanks
Hey Ray,
Thanks for putting up your code. I know that whenever anyone puts code up, everyone's a critic :)
In my flex/AIR travels I came across a couple of things that may make creating multiple SQL statements a little easier. You'll need to start adding event handling to your code in order to do the asynchronous stuff without throwing an error because A has not finished when B runs (once A is done, go B, once B done, go C, etc).
Here's something I came across that I thought was a clever way of doing things that I've modified slightly to fit your model:
private var statements:Array = [];
public function init():void {
statements.push( "CREATE TABLE IF NOT EXISTS clients(" +
" id INTEGER PRIMARY KEY AUTOINCREMENT, " +
" name TEXT, " +
" description TEXT " +
");" );
statements.push( "CREATE TABLE IF NOT EXISTS projects(" +
" id INTEGER PRIMARY KEY AUTOINCREMENT, " +
" name TEXT, " +
" description TEXT, " +
" clientidfk INTEGER, " +
" active INTEGER " +
");" );
statements.push( "CREATE TABLE IF NOT EXISTS hours(" +
" id INTEGER PRIMARY KEY AUTOINCREMENT, " +
" description TEXT, " +
" clientidfk INTEGER, " +
" projectidfk INTEGER, " +
" hours NUMERIC, " +
" date DATE " +
");" );
conn.openAsync(dbFile);
executeNext();
}
private function executeNext( e:SQLEvent=null ) {
if (statements.length > 0) {
createStmt = new SQLStatement;
createStmt.sqlConnection = conn;
createStmt.text = statements.shift();
createStmt.execute( -1, new Responder( executeNext, errorDBHandler ));
} else {
conn.close();
dbReady();
}
}
The Responder class takes 2 functions, the first is for a successful SQL call, the second is for an unsuccessful call. It takes the place of the 2 listener events that you initially placed. Then it recursively calls the "executeNext()" function and shifts each statement off of the statements array, executing each one as it goes. Once it gets a result event, it calls the next one, until all statements have been called. Once all are done, it goes to dbReady().
This was borrowed from the employeeDirectory code at Adobe.
Wow. I got to say - thats nice. I'm not going to use it for now - as I want to focus on other parts of the app - but next time I will.
One other thing I found,
you forgot to add a clickHandler to the projects.clientFilter combobox, so it doesn't matter if you switch clients, it still shows all.
For the date problem you were encountering, have you tried using Date.parse(recent date) - Date.parse(older date) or is it a dB issue? I've found Date.parse will parse just about anything (all textual dates as well), and do a good job of returning values. You just have to convert it back to a date after that to find the elapsed time difference.
Sorry for all of the "and don't forget this" posts :)
Ah yes - I forgot to release note the client filter. I knew about that - my issue was (and why I didn't make it work) was that I need a simple way to clear that filter.
For the date issue - all I want to do is mod my 'get hours' sql to get records where date == sometime today. Nothing I tried worked.
One last thing,
I don't know if you've seen it or not, but Firefox now has a SQLite Manager extension which allows you to view data, edit tables/data/indicies, and run queries. Kinda nice and makes it easier to check queries rather than having to rebuild your app each time with the modified query.
(Oh yes, I found 2 errors with my previous code. It should be e:SQLResult that is passed into executeNext and executeNext needs a return datatype of "void")
Actually, I'm running another AIR app to do my queries. I forget the name (I'm downstairs with my laptop), but I'm going to blog about it later today. I'll find the FF plugin as well to let folks know about it.
a bit unrelated, but this is a fairly basic ORM for AIR which uses annotations in AS VO's to maintain the database.
http://coenraets.org/blog/2...
@dc,
Now that is *really* nice. I'll have to give that a try. Only modification I may make is to prevent all columns from being uppercased, but there are quite a few things in there I haven't yet played around with (in Flex or AIR). Nice!
Gareth - THANK YOU! Not quite sure why it is that Air doesn't support BEGIN/END transaction...
I prefer to create SQL in an embedded file - each statement postfixed by ';'. Then, this code splits the SQL into single statements and executes all step by step (error handling for clarity not included):
try {
// Separate all statements
var parts:Array = createSQL.split( ');' );
for( var i:int; i<parts.length; i++ ) {
// Only, if we really have an SQL statement
if ( '' != parts[i] ) {
var realStatement:String = parts[i] + ');';
createStmt.text = realStatement;
createStmt.execute();
}
}
} catch( error:SQLError ) {
Alert.show( 'Sorry, DB error: ' + error.details );
}
That's pretty slick. Thanks for sharing.
Additionally, here's another small utility I created: CPreferencesManager manages application preferences by means of a key/value set:
Here is how to use it to make window positions persist after relaunch of an application:
1. Create an instance:
<tracker:CPreferencesManager id="thePrefs" />
2. Attach windowMove events to your Window:
windowMove="thePrefs.setPreference('main.window.x', this.nativeWindow.x.toString() ); thePrefs.setPreference('main.window.y', this.nativeWindow.y.toString() );"
3. After creationComplete, move the window to its position of the one but last launch:
var posX:int = int( thePrefs.getPreference('main.window.x' ) );
var posY:int = int( thePrefs.getPreference('main.window.y' ) );
move( posX, posY );
The implementation:
package com.your.company
{
import flash.filesystem.File;
import flash.filesystem.FileMode;
import flash.filesystem.FileStream;
import flash.utils.ByteArray;
import flash.utils.Dictionary;
import flash.utils.IDataInput;
import flash.utils.IDataOutput;
import flash.utils.IExternalizable;
import mx.controls.Alert;
public class CPreferencesManager implements IExternalizable
{
private var _prefValues:Dictionary;
private var _prefsStream:FileStream;
public function CPreferencesManager()
{
_prefValues = new Dictionary();
loadPreferences();
}
public function loadPreferences():void {
var fp: File = File.applicationStorageDirectory;
fp = fp.resolvePath( 'prefs.xml' );
if ( fp.exists ) {
_prefsStream = new FileStream();
_prefsStream.open( fp, FileMode.READ);
readExternal( _prefsStream );
_prefsStream.close();
} else {
_prefValues[ 'appName' ] = 'tracker'; // test-code
savePreferences();
}
}
public function savePreferences():void {
var fp: File = File.applicationStorageDirectory;
fp = fp.resolvePath( 'prefs.xml' );
_prefsStream = new FileStream();
_prefsStream.open( fp, FileMode.WRITE );
writeExternal( _prefsStream );
_prefsStream.close();
}
public function setPreference( name:String, value:Object ):void {
_prefValues[ name ] = value;
savePreferences();
}
public function getPreference( name:String ):Object {
return _prefValues[ name ];
}
public function readExternal(input:IDataInput):void
{
var bytes:ByteArray = new ByteArray();
input.readBytes( bytes, 0, input.bytesAvailable );
var q:Object = bytes.readObject();
_prefValues = new Dictionary();
for( var p:Object in q ) {
_prefValues[ p ] = q[p];
}
// Alert.show( _prefValues.toString() );
}
public function writeExternal(output:IDataOutput):void
{
var bytes:ByteArray = new ByteArray();
bytes.writeObject( _prefValues );
output.writeBytes( bytes );
}
}
}
Is there any kind of AIR cookbook? Solutions like this need to be aggregated. I know there is a Flex cookbook, but does it allow you to filter by AIR solutions?
Perhaps a nice co-conspirator to cflib.org?
airlib.org?
flexairlib.org?
:)
Be my guest. :)
I just posted my solution to Flex Cookbook - but neet to fix it soon:
http://www.adobe.com/cfusio...
Why? The code
output.writeObject( _prefValues );
writes an object, but does not save it's class, therefore, one has to register its class first:
registerClassAlias( "flash.utils.Dictionary", Dictionary );
Likewise, one has to register the class before
registerClassAlias( "flash.utils.Dictionary", Dictionary );
_prefValues = input.readObject() as Dictionary;
first.
I wonder, why the lib doesn't do this itself. Now, it tends to be hard, to write an universal serializer, which maintains all class, of all linked objects.
Hm, too bad.
@Ray,
wouldn't want to take away from the fun you seem to have of building/hosting all of these sites :)
Actually, Adobe has an AIR cookbook here:
http://www.adobe.com/cfusio...