Gus posted a very cool SQLite Objective-C wrapper, complete with sample code. It looks like the methods he has in there are pretty handy. Seasonality uses SQLite as it’s database engine, though at the time I was developing it, the only good wrapper I found was for SQLite 2.8.x, so I went for that. I might end up switching to SQLite 3 in a later version, but we’ll have to see. Since the database files are a completely different format between SQLite 2.8 and SQLite 3.x, there would definitely be some work involved in the upgrade.
I figured this would be a good opportunity to talk more about the database aspects of Seasonality. Seasonality actually uses 2 SQLite databases, one of them is the location database in the application bundle, and the other is stored in the users Application Support directory (~/Library/Application Support/Seasonality). The database in the application bundle is really only used as a read-only database, and it contains all of the locations and their associated data values. When you start typing in a zip code to add a location in Seasonality, the app is actually just running a quick SELECT query from the string you’ve typed so far. There are a few different tables in the location database to associate zip codes to ICAO weather locations and radar locations, but overall it’s a pretty simple database. This is actually a subset of all the data that I’ve mined over the past several months…the master is residing in a PostgreSQL database on one of my servers here, and I wrote a Perl script to select subsets of the data from Postgres and insert them into a new SQLite database to include in Seasonality.
The second database in Application Support is used to store all the saved weather data. There are several fields that are saved for future functionality if needed. Other than the temperature, dewpoint, wind and gust speeds used for the graphs right now, Seasonality saves the air pressure, relative humidity, and the visibility along with the date that data is associated with. This brings up a minor complaint of mine…SQLite is typeless, which means that it treats all values you put in the database as the same type. Integers, Floats, and Dates are all inserted as simple strings. They claim this is a feature, but when it comes to date processing it’s a major pain.
When selecting data to build a weather graph, I want to select data that is between two dates. Unfortunately, if I just use a standard date string like Tue Mar 22 22:45:16 EST 2005, it will choke when I try to do a date comparison because it will look at it as a string and, for example, Fri Mar 25 22:45:16 EST 2005 would be seen as happening before the first date because F in Fri is alphabetically before T in Tue. One way around this is to use a very large integer for the date string: 20050322224516. This works fine, and is the method that Seasonality uses to keep track of dates, but it’s slightly inconvenient because all values are above 2^32, so you need to remember to use 64 bit integers to pass dates in and out of the database.
Back to the second database, that’s all there is at the moment…just that one table with a ton of rows in it. Each row takes maybe 100 bytes, and with an average of around 30 rows a day per a location you end up with a database that is about 1Mb after a year’s time (per location). Not bad at all for the amount of data being saved. Searching it is pretty quick too…my database at the moment has about 11,000 rows in it (data for 5 locations back through the beginning of February), and searching through it takes a fraction of a second. In later versions of Seasonality, I might add a way for users to manage this data a bit better. For instance, exporting the data seems like something that user might want to do, and maybe add some AppleScript handling as well. We’ll see where it goes…
