Mac - Sqlite overview

GSAK (Geocaching Swiss Army Knife)

SQLite Overview

SQLite is now the choice of SQL engine for GSAK.

SQLite is very wildly used, has a small foot print, and is open source. Testing shows it is blazingly fast, and implementation is not overly complicated (though it is all relative) .Table joins are fast and support for SQL standards is excellent. A general knowlege of SQL is assumed, so I won't be including the SQL syntax specifics in the GSAK help file. You can find all this and more at the home page of SQLite here http://www.sqlite.org

There are a few "sql consoles" around and I the one I have been using can be found here

SQLite is very widely used, so now we have a SQL engine that is robust and hopefully the bugs ironed out. It does have a few minor quirks, but on the whole it does seem to be the "silver bullet" to fix all the problems of the existing SQL implementation (pre version 7.2.3)

For some examples of using Sqlite see this topic

Asociated Functions:

Sqlite() - The main portal to the Sqlite database engine
DbToSqlite() - Generate a Sqlite table from existing GSAK tables
SqlToHtm() - Quick and easy generation of a HTML report using SQL

Associated system variables:

$_SqlRows - Return the number of rows generated by the last "SQL" action of the Sqlite() function. The most common use would be know the number or rows returned by a "select" statement.

$_SQL - Is a special system variable to help overcome the problem of different countries having the , (comma) as the decimal separator. This becomes especially relevant when using the SQL engine. SQLite (and the old sql() function) demands that all numerical operations use the . (period) as the decimal point. The main problem here is when you convert a GSAK numeric variable to a string, the string representation includes the decimal separator as per your current international settings. For example, your International settings are for Germany:

$test = "$d_Difficulty"

Notice the code snipped has enclosed the variable $_Difficulty in double quotes. This converts the difficulty on the fly to a string, and $test is allocated a variable type of string. If the difficulty is 1.5 then $test would show as 1,5

This becomes a real issue when generating SQL statements. For example, to select all caches in your SQL database where the difficulty is equal to the difficulty of your current cache (1.5 for this example) in the GSAK grid view, the syntax would be:

$data = sqlite("sql","Select * from caches where difficulty = $d_difficulty","")

However, this will fail because $d_difficulty will be converted on the fly to a string value of 1,5

The $_sql system variable fixes this problem by always forcing all numerics to be converted with a . (period) regardless of the International setting.

So if you are going to write macros using SQL that will be shared by other users your code in the previous example should be:

$_Sql = "Select * from caches where difficulty = $d_difficulty"
$data = sqlite("sql",$_sql,"")