GSAK (Geocaching Swiss Army Knife)
Sample code to generate some data from the "caches" table to a native Sqlite database.
The following code samples use this database and also assume that you have opened it at least once with the following line of code
SQLite supports the DISTINCT keyword. This makes finding things like the number of days you've been caching trivial:
This counts the number of unique dates in the SQL database.
Another example, which of the 81 diff/terr combinations have I found?
In this case the DISTINCT applies to both difficulty and terrain and will return only the unique combinations.
Sqlite also offers uses standard SQL VIEWS which behave a little like a temporary table which gets saved as a new database to disk. A VIEW is a result table from one query which can then get queried itself. Here's an example where we want to count the number of Diff/Terr combinations we have (there are easier ways to do this but this shows the power of VIEWS in SQL)
Alternatively we could do this in just one query:
Either way we are running two linked queries. The first returns all the distinct combinations and the second query counts the results of the first.
SQLite also supports the LIMIT clause. This is a very good optimization technique to fetch just a single row or a few rows. For example, to return the first cache code that is difficulty 5
Copyright 2004-2011 CWE Computer Services |