GSAK (Geocaching Swiss Army Knife)
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
Note1 - GSAK automatically attaches a memory database called "gsak_mem" to the current database connection. This database is used internally (for speed optimization) but macro authors can also use it if they wish. For speed optimization I would recommend creating temporary working tables in the gsak_mem database (providing they are not too large). To use this database with the current database connection, just qualify any table with that database name. For example, to create a table of codes in the current filter:
Note2 - Notice in the example given in Note1, to restrict the created table to only the records in the current/grid we have used the where of "rowid in (select * from gridtemp)". Gridtemp is a GSAK system table that always contains the rowids of the caches that are in our current filter/grid. So any time you need to write a query that only operates on caches that are in your current filter/grid, just add this where clause.
Copyright 2004-2011 CWE Computer Services |