Mac - Sqlite functions

GSAK (Geocaching Swiss Army Knife)

SQLite Functions

SQLite ships with its own inbuild functions. They are grouped into 3 types. Core functions, Aggregate functions, and DateTime functions.

SQLite also provides the infrastructure to build custom functions that can be used in native SQL queries. GSAK has harnessed this power to add functions that are specific to GSAK. To prevent confusion with native SQLite functions, all GSAK functions begin with "g_"

Current GSAK functions include:

g_Regex() - Matching using regular expressions.Same definition and syntax as used in the native macro function RegEx()

g_RegexCount() - Count the number of times a regular expression is matched. Same definition and syntax as used in the native macro function RegExCount()

g_Upper(sData) - Return the MS ANSI uppercase string of sData. For more information see SQLite Collation

g_Contains(sFind,sData) : boolean - MS Ansi Case insensitive search. For more information see SQLite Collation

g_Distance(sLat1,sLon1,sLat2,sLon2,sUnits) : real - calculate the distance between two points. Assuming we have created a "caches" table with latitude and longitude fields and the records are sorted in the correct oder, we can calculate the total distance in miles betwen all these waypoints with the following code:

$TotalDistance = SqLite("sql","SELECT sum(g_Distance(t1.latitude,t1.longitude,t2.latitude,t2.longitude,'M')) FROM caches AS t1, caches AS t2 WHERE t1.rowid = t2.rowid - 1")