Contents - Index

SQLite Overview


SQLite is now the choice of SQL engine for GSAK, and the sqlite database format is used for all GSAK databases.

SQLite is very widely used, has a small foot print, and is open source. Testing shows it is blazing 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 knowledge 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

My recommended "sql console" for SQLite 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

Associated Functions:

Sqlite() - The main portal to the Sqlite database engine
SqlToHtm() - Quick and easy generation of a HTML report using SQL
SqlGet() - Optimized read of a SQLite result query
DateToSql() - Convert a native GSAK macro date variable, to SQL format
SqlQuote() - Prepare SQL strings with escaped quotes
SqlSort() - Sort grid using SQL Order by.
SqlToDatel() - Prepare SQL strings with escaped quotes

Sqlite also enables you to create "custom functions" that can be used internally just like the core SQLite functions. A list of GSAK specific custom functions can be found here. 

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. Note: This variable is not populated/valid when using the optimized "SqlGet" option. 

$_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 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,"")


Note: The $_SQL system variable also has special meaning when used to build your Where= Statement for Mfilter. For more information see the "where" section of the mfilter command. 

$_CurrentDataPath - The fully qualified path (without the trailing back slash) to your current database folder. Particularly handy when wanting to access the GSAK sqlite database from within a macro.
$_OrderBy - Contains the "Order By" clause used to sort the current grid. It can be used when you want to run a select on the caches table and return the records in the same sequence as the current grid. It can also be used to save and restore your current sort sequence:

$CurrentSort = $_OrderBy
#Change our sort sequence to do some work in our desired sequence
SQLSort OrderBy=Terrain Asc, Difficulty desc
# do some work here
#....
# Now finally restore our current sort sequence
SQLSort OrderBy=$CurrentSort


$_SqlEol - (Boolean) True or False reflecting the status of the last SqlNext command (use via SqlGet() )

$_SqlGrid - returns the syntax for a full optimized query on the caches table to return only the records in the current grid and in the same order. Example, build short list of the grid filter in the current order:
 

$status = sqlite("sql",$_SqlGrid, "sqlget=yes")
$data = ""
while not($_sqleol)
  $data = $data + sqlget("code") + sqlget("name") + sqlget("userdata") + $_Crlf
  sqlnext
Endwhile