Contents - Index

SQLite Examples




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")


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:
 

$status = Sqlite("sql","drop table if exists gsak_mem.DbCodes")
$status = Sqlite("sql","create table gsak_mem.DbCodes as select code from caches where rowid in (select * from gridtemp)")


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.