Home >
Earlier this week I blogged about a new jQuery based AIR project, Hangman. In the first blog entry I talked about the game and the jQuery code behind it. One of the main problems with the code in that entry though is that it really isn't an AIR application. I mean sure it makes use of the AIR framework. Sure it's an AIR application technically. But none of the code actually did anything - well, AIR like! We can fix that don't you think?
The biggest issue with the game as it was is the lack of a good vocabulary. I manually entered about 20 or so words in an array:
I could sit there and add words all day, but eventually I'd run out of ideas. Instead of manually entering words, why not consider a database of words? I did a bit of Googling and came across an awesome resource - the top one thousand words by frequency. This was excellent. I'd automatically get a lot more words and I'd be gauranteed to have words that - hopefully - people recognized.
I downloaded the list and trimmed the text file to just the words themselves. I could have used AIR to parse the text file, but I thought that would make the application slow to load. Why not use a database then? AIR could handle creating and seeding the database, but even this I thought would be slow (if only once). I decided to use ColdFusion to seed my database for me. I described this in a blog entry over on my own blog. To actually create the initial database I used Lita, a free AIR-based tool to edit SQLite databases.
So - now I've got a SQLite database with one thousand words. What next? How difficult is it to add to my Hangman game? The first thing I did was copy the file (remember, SQLite is file based) to my Aptana project. This would ensure the database would ship with my application. I could have used the database there, but people smarter than I (thank you David Tucker and Andrew Powell of Universal Mind!) suggested that I copy the database to the application storage directory. All AIR apps have a special folder they can use for their own needs. AIR provides a nice shortcut for this folder so it's even easier to use. My needs then are two fold:
I handled part one in the $(document).ready portion like so:
The code begins by creating a path to a file named words.db. The path includes the default application directory I mentioned earlier. I don't have to worry about finding a place specific to any file system. AIR gives me a box to play in and I just worry about the file name.
If the file doesn't exist, then I create another path to the database that ships with my AIR application. I use the copyTo operator to copy the database to the folder. Notice the try/catch. If anything goes wrong I abort the entire application.
And that's it. Really. With the simple file system support and the aliases to default paths, it's truly a trivial process. Now let's look at connecting to the database.
I won't go into the full details of connecting to databases (check the docs for that), but the process is pretty simple. I made a new SQLConnection object and pointed it to my database file. You can open connections to database in either a synchronous or asynchronous manner. Since I know I'm just selecting random words I used a synchronous connection. Once again I've try/catch wrapped the code in case something goes wrong.
Woot - almost there. The last step is to update the pickRandomWord function to use SQL to grab a word.
The function begins by creating a new SQLStatement object. The statement is connected to the SQLConnection object created earlier. The SQL is added and finally the statement is executed. The result from the statement can be treated like a structure of arrays. That's where result.data.word[N] comes in.
All in all, pretty simple. But I bet I could make it even simpler with jQuery. For example, wouldn't this rock?
Why, yes, it would I think. I whipped up a quick jQuery function that runs generic SQL and returns just the struct of arrays:
This plugin wraps up most of the logic from the previous function. I could make it even simpler and return just a simple structure when the row count is 1, but I'm not sure it makes sense to change the result just because of the number of rows.
Either way - our Hangman app is now about 200 times more intelligent with a brand spanking new database-enriched vocabulary. You can download the the application here.
The biggest issue with the game as it was is the lack of a good vocabulary. I manually entered about 20 or so words in an array:
function pickRandomWord() {
var words = ["sad","happy","dog","cat","football","baseball","music","drum","guitar","ship","tree","rat","jedi","planet","star","moon","sea","ocean","internet","trash","leaf","play","again","safari","robot","laptop","japan","key","cup","love","hate","song","cook","book","look","vault","poster"]
var picked = Math.floor(Math.random() * words.length)
return words[picked].toUpperCase();
}
I could sit there and add words all day, but eventually I'd run out of ideas. Instead of manually entering words, why not consider a database of words? I did a bit of Googling and came across an awesome resource - the top one thousand words by frequency. This was excellent. I'd automatically get a lot more words and I'd be gauranteed to have words that - hopefully - people recognized.
I downloaded the list and trimmed the text file to just the words themselves. I could have used AIR to parse the text file, but I thought that would make the application slow to load. Why not use a database then? AIR could handle creating and seeding the database, but even this I thought would be slow (if only once). I decided to use ColdFusion to seed my database for me. I described this in a blog entry over on my own blog. To actually create the initial database I used Lita, a free AIR-based tool to edit SQLite databases.
So - now I've got a SQLite database with one thousand words. What next? How difficult is it to add to my Hangman game? The first thing I did was copy the file (remember, SQLite is file based) to my Aptana project. This would ensure the database would ship with my application. I could have used the database there, but people smarter than I (thank you David Tucker and Andrew Powell of Universal Mind!) suggested that I copy the database to the application storage directory. All AIR apps have a special folder they can use for their own needs. AIR provides a nice shortcut for this folder so it's even easier to use. My needs then are two fold:
- When the application starts, copy the database to the application storage folder if it hasn't been copied yet.
- Update my logic to select a random word from the database.
I handled part one in the $(document).ready portion like so:
//I handle copying the db from local to storage dir
var installTo = air.File.applicationStorageDirectory
var installToFile = installTo.resolvePath("words.db")
if(!installToFile.exists) {
air.trace("Copying db file to "+installToFile.nativePath)
var installFromLoc = air.File.applicationDirectory
var installFromFile = installFromLoc.resolvePath("database/words.db")
air.trace("from "+installFromFile.nativePath)
try {
installFromFile.copyTo(installToFile,true)
} catch(error) {
//Total Failure...
alert(error.message+'\n'+error.details)
air.NativeApplication.nativeApplication.exit()
return
}
}
The code begins by creating a path to a file named words.db. The path includes the default application directory I mentioned earlier. I don't have to worry about finding a place specific to any file system. AIR gives me a box to play in and I just worry about the file name.
If the file doesn't exist, then I create another path to the database that ships with my AIR application. I use the copyTo operator to copy the database to the folder. Notice the try/catch. If anything goes wrong I abort the entire application.
And that's it. Really. With the simple file system support and the aliases to default paths, it's truly a trivial process. Now let's look at connecting to the database.
//connect to db
try {
dbcon = new air.SQLConnection()
var dbFile = air.File.applicationStorageDirectory.resolvePath("words.db")
dbcon.open(dbFile)
} catch(error) {
//Total Failure...
alert(error.message+'\n'+error.details)
air.NativeApplication.nativeApplication.exit()
return
}
I won't go into the full details of connecting to databases (check the docs for that), but the process is pretty simple. I made a new SQLConnection object and pointed it to my database file. You can open connections to database in either a synchronous or asynchronous manner. Since I know I'm just selecting random words I used a synchronous connection. Once again I've try/catch wrapped the code in case something goes wrong.
Woot - almost there. The last step is to update the pickRandomWord function to use SQL to grab a word.
function pickRandomWord() {
var getStmt = new air.SQLStatement()
getStmt.sqlConnection = dbcon
getStmt.text = "select word from words order by random() limit 1"
getStmt.execute()
var result = getStmt.getResult()
return result.data.word[0].toUpperCase()
}
The function begins by creating a new SQLStatement object. The statement is connected to the SQLConnection object created earlier. The SQL is added and finally the statement is executed. The result from the statement can be treated like a structure of arrays. That's where result.data.word[N] comes in.
All in all, pretty simple. But I bet I could make it even simpler with jQuery. For example, wouldn't this rock?
word = $.runSQL(dbcon, "select word from words order by random() limit 1")[0].word
return word.toUpperCase();
Why, yes, it would I think. I whipped up a quick jQuery function that runs generic SQL and returns just the struct of arrays:
jQuery.runSQL = function(con,sql) {
var getStmt = new air.SQLStatement()
getStmt.sqlConnection = con
getStmt.text = sql
getStmt.execute()
var result = getStmt.getResult()
var tableresult = []
for(var i=0;i<result.data.length;i++) {
var row = {}
for(col in result.data[i]) {
row[col] = result.data[i][col]
}
tableresult[tableresult.length] = row
}
return tableresult
};
This plugin wraps up most of the logic from the previous function. I could make it even simpler and return just a simple structure when the row count is 1, but I'm not sure it makes sense to change the result just because of the number of rows.
Either way - our Hangman app is now about 200 times more intelligent with a brand spanking new database-enriched vocabulary. You can download the the application here.
Tag Cloud
Podcasts & Screencasts
@InsideRIA on Twitter
Follow InsideRIA on Twitter
Facebook Application Development
Great ideas, Raymond, but I have just one comment.
While your jQuery.runSQL method utilizes jQuery as a namespace, all you've really done is created an additional, unnecessary dependency, since your method's internals neither require nor augment the functionality of jQuery.
Of course, if you utilized jQuery.each() or maybe jQuery.map() internally to iterate over the data, that dependency (and namespace) would make sense.
I'm still new to jQuery, so forgive any ignorance here. Why do you consider this an unnecessary dependency? I already require jQuery for the rest of the application. Are you saying I should make another file and plugin my stuff there? The trim() function, for example, is generic, and part of jQuery, so why would it not make sense to add my own functions then?
Raymond, what you've done is created a generic runSQL method that anyone using AIR can utilize, regardless of what JavaScript libraries they are using. The thing is, the instant you attach that method to the jQuery prototype, you require someone to have jQuery to be able to use it.
Someone who knows better could just change "jQuery.runSQL = function" to "function runSQL" in the definition, to remove the dependency, but not everyone knows to do that. Also, if you do just that from the start, a user who prefers MooTools or Prototype (or no library at all) can use your method as well, because they won't be saying, "Since I don't use jQuery, I clearly can't use this method".
If you have other SQL-related methods, you could create a global namespace like "sql" to attach these methods to, which would allow you to group other SQL-related methods in the future. Here's an extremely simplified version of this approach, for example:
sql = {};
sql.run = function(){ ... };
sql.othermethod = function(){ ... };
Of course, maybe you want to group your methods differently, in which case you'd use a different namespace.
What it comes down to is the question, "What are the actual benefits (and are there any drawbacks) to defining this method as jQuery.myMethod = function(){ ... }' vs just function myMethod(){ ... }; ?"
$.trim() is used internally within jQuery. I'd also like to second Ben's comment above. There's just no reason to use the jQuery namespace as a generic container for application specific functions. It creates a dependency because there's nothing in that runSQL function that even requires jQuery, and it's pretty much a global function at that point.
Even when I do have application specific functionality that does require jQuery, I prefer (and believe it is better practise) not to use the jQuery namespace as a storage location for any and all utility functions.
While I certainly appreciate the technique on display - and since this is the second in a series of posts - you already *have* jQuery in the page, but to do this piece of functionality, jQuery is completely uneccessary.
Fair comments guys, and it makes sense. I can say I had thoughts for other AIR related things that _would_ make use of jQuery (example below) but for what I did here it is totally unrelated to jQuery. In the next blog post I'll move this to a more generic AIR library.
Example: This is where I think it _would_ make sense. I want to build support for:
$("....").outputQuery(con, "select name, age from foo", "Hi, I'm {name} and I'm {age} years old.")
Where jQuery would handle running the SQL, looping over the results, using the last string as a template, replacing tokens, and finally returning a string as injected HTML into the matched item. Cool?
Exactly.
That makes sense, but even then, it is important to maintain a separation between the jQuery part of the functionality and the actual ability to run the sql. If you followed the whole namespacing model outlined above, you could use
jQuery.fn.outputQuery = function(con,sql,str) {
var result = sql.run(con,sql),
resultStr = doYourStringReplacingHere();
return this.each(function(i,n) {
$(this).text(resultStr);
});
};
In this way, we honour the fact that jQuery is a library for DOM selection, traversal, and manipulation, rather than a generic container for pretty much everything you do in your app.
(PS. re: $.trim used internally within jQuery == things I was wrong about. whoops)
I definetly wouldn't be doing sql on the front end, security first and of course what about unobtrusive design???
Um... you are aware this is an AIR application, right? It's all front end. The db is part of the product.
As for unobtrusive, can you clarify?
Ooops, sorry, now I get it, I don't really know much about AIR but I do about JQuery (use it every day and when I saw the headline I was interested). Then my comment about security does not apply (Duhhh) unless the AIR app somehow sends the info over to some backend(with a DB). The ability of the end user to see the JS code makes him capable of spoofing requests to the server and doing sql injection, etc.
Now, why do AIR apps use sql?! Wouldn’t it be better to use some sort of cool and easy to use persistence API (object oriented perhaps) or HTML 5 DB capabilities?
About the unobtrusive design, if an AIR app is 100% dependable on JS then some browser without JS could not run it, whereas with Jquery we just add an additional layer of functionality (progressive enhancement) and users without js get at least the basic functionality. (Why this? because of old browsers and mobile browsers that don’t support JS or just suck at JS)...
SQL: Oh I definitely agree then. I'd never send SQL from a client to a server. As for why AIR uses a db... err, why not? I mean I can't speak for Adobe, but the DB support I think makes sense. The HTML5 people added it, right? Building towards AIR versus HTML5 is a whole other topic though. I'm no RIA expert (just a person learning and sharing and hopefully helping others) but I'd say this is truly one of the good features of AIR development. (And keep in mind I used it in a very simple way for my application.)
Unobtrusive: Err, ok, but then again, this is an AIR app. It's not a browser app. JS _is_ enabled. Period. :) Let's pretend for a moment that i wasn't build an AIR app. With it being a game, I'd be more than happy to say that if yo don't use JS, you can't use the game. It isn't content that needs indexing by Google. It is a game that is meant to be played in a dynamic fashion. I don't think you always have to support non-JS browsers. You should - of course - where it makes sense.
My 2 cents.
I have 2 files for above situation: "page.html" and "scripts.js"
The above code with the conection to database and retrieving dates with query from table, is in the "scripts.js" and the UI interface is in "page.html"
When I run from aptana, my project ... I don't get any result. If I move all the code from "scripts.js" in "page.html" it's running very well and I have result in my page. Can advise me what to do to run from 2 separated file and not from 1?
Hmm. I assume you do in your page.html, right?
Yes, mai UI page is page.html
So you say you get no result, are you saying you don't get a running AIR app at all? Or an app that doesn't operate correctly?