Working with Flex, AIR, and SQL (2)
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.
Comments
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.
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 :)
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.
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")
http://coenraets.org/blog/2007/10/annotating-actio...
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!
It will be nice to create / connect to sqlite databases on a remote central machine.