GSAK (Geocaching Swiss Army Knife)

Contents - Index

SQLite Examples

Sample code to generate some data from the "caches" table to a native Sqlite database.

$result = dbtosqlite("caches","Code,Name,CacheType,Container,Difficulty,Terrain,Bearing,Found,PlacedBy,FoundByMeDate,Distance","c:\temp\test.db3")

The following code samples use this database and also assume that you have opened it at least once with the following line of code

$result = sqlite("open","c:\temp\test.db3")

SQLite supports the DISTINCT keyword. This makes finding things like the number of days you've been caching trivial:

$days = sqlite("sql","SELECT COUNT(DISTINCT foundbymedate) FROM Caches")

This counts the number of unique dates in the SQL database.
Another example, which of the 81 diff/terr combinations have I found?

$result = sqlite("sql","SELECT DISTINCT difficulty, terrain FROM Caches")

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)

$result = sqlite("sql","CREATE VIEW diffterr AS SELECT DISTINCT difficulty, terrain FROM Caches")
$combinations = sqlite("sql","SELECT count(*) from diffterr")

Alternatively we could do this in just one query:

SELECT count(*) FROM (SELECT DISTINCT difficulty, terrain FROM FoundStatsSQL)

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

$result = sqlite("sql","select code from caches where difficulty = 5 limit 1")


Copyright 2004-2011 CWE Computer Services  
Privacy Policy Contact