Help file update
There are two significant changes in this version of the shipped help file.
1. Added a new "category list" to help better find commands and functions
2. Split deprecated functions and commands into a new help file called "deprecated.chm"
2 - Doing this should give us the best of both worlds. That is, users that still need to know how these functions work will have a full description when they click on the associated deprecated help link. However, by moving all the actual pages to a separate help file, none of the topics and words will be included when we do a "search" in the main help file.
For example if we do a search on "SQL" you should only get hits on topics that refer to SQLite. None of the topics or pages from the old SQL engine should show up. This should remove all of the "noise" associated with old and deprecated features in GSAK
Backwards Compatibility
Although it is impossible to have everything work exactly the same using the old database engine, most old macros should run at reasonable speed without any issues. There was one feature that could not be converted over and that is the more esoteric filter join operands of XOR, ANDNOT, ORNOT and XORNOT. The more commonly used AND and OR have been converted.
Changes
Mfilter - now includes a new "Where=" clause which behaves similar to the "Expression=" (both are mutually exclusive). "Where=" is now the recommend clause to use in Mfilter statements as it will accept native SQL syntax for the Caches table and will be quicker and allow access to more variables and functions. "Expression=" is now deprecated and should be used for backwards compatibility only. That is, when using "Expression=" the expression is handled as belonging to the old database engine and needs to be converted to valid SQL "Where" syntax. "Expression=" is supported so that old macros will run without error, but it is advised that any future macros should now use "Where=" instead.
$d_Archived and $d_TempDisabled - both these database variables are now deprecated. They are supported for backwards compatibility (and will still get automatically updated when loading a GPX file). However, they have basically been replace with a new tri value string variable of $d_Status :
A = Available
T = Temporary disabled
X = Archived
$_Where - This new system variable contains your current "Where" clause (used to select records in the grid). It can be used when you want to run a select on the caches table and return only the records as shown in the grid.
$_OrderBy - This new system variable 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.
$_SqlGrid - This system variable returns the full optimized query on the caches table to return only the records in the current grid and in the same order (see the Performance section on how to use it)
Permanent delete list - This has now been changed to "Ignore list". The old list was just a plain text file called "delete.txt" stored in the same folder of the database. This file is no longer used and has been deleted and converted to a native table (called "Ignore") in the sqlite database.
Performance
In general the macro language should perform similar or better than before. However, there are exceptions. One notable exception is looping through the database and using database variables. Take the following code that uses database variables, . This will still work fine under 7.7 but it will be a little slower than 7.6
GoTo Position=Top While not($_Eol) $data = $d_code + $d_name + $d_userdata GoTo position=Next EndWhile GoTo position=Top |
However, using some of the new additions to the macro language we can simulate this using purely SQLite type syntax and the resulting macro code will run up to 8x quicker.
$status = sqlite("sql",$_SqlGrid, "sqlget=yes") while not($_sqleol) $data = sqlget("code") + sqlget("name") + sqlget("userdata") sqlnext Endwhile |
Sqlite() - Added new option "SqlGet=Yes|No". This new option allows a highly optimized way to fetch data from *any* Sqlite query. The current default behaviour for the "Sql" action is to run a query and return *all* the data into one big variable. This is not efficient and also requires extra work to extract the individual fields and records. By adding "SqlGet=Yes" we change this behaviour dramatically:
1. Records are now read one by one and you "iterate" through the SQL query in a similar fashion to the current looping of a table.
2. Values for each record are returned by using the SqlGet() function. This allows you to fetch only the variable you want. It also helps to preserve fidelity of the source data as we now don't have to worry about record and field terminators.
3. It by far the quickest way to process the results of a Sqlite query.
SqlGet(sField) : String - This new function should be used in conjunction with the SqlGet=Yes option. It will allow you to retrieve the value of any field in the current SQLite query. Note: The return value of this function is *always* a string. You will need to convert date, numeric, and boolean values to their native format if you want to use functions that require the data in these formats. However, this is no different to the existing behaviour of the "Sql" action as it too only ever returned string values.
SqlNext - This new command is to be used to iterate through a Sqlite query that has been generated with the SqlGet=Yes option.
$_SqlEol - This new system variable is similar to $_Eol but for a Sqlite query generated with the SqlGet=Yes option.
In essence the following should always be used together: SqlGet=Yes, $_SqlEol, SqlGet(), and SqlNext.
As the "caches" table is now fully converted to SQLite any updates to $d_ variables should be wrapped inside a transaction - especially if you iterating through the database and updating many records. However, it will still make a significant difference if you are just updating one record but many fields.
For example, to update all the user data fields in our caches table, our code should be:
Transaction Action=Begin Goto Position=Top While not($_Eol) do $d_UserDate = "data 1" $d_User2 = "data 2" $d_User3 = "data 3" $d_User4 = "data 4" Goto Position=Next EndWhile GoTo Position=Top Transaction Action=End |
Normally, if we didn't include the transaction code this macro would run *very* slowly - especially if you had a large number of caches.
However, I envisaged this would be a problem for existing macros (they wouldn't yet have included transactions), so I have coded the macro preprocessor to take care of this situation.
Basically it works like this:
1. Scan the macro code for *any* update of $d_ variables
2. If any found, also scan the code for any "Transaction" command
3. If item 2 is not found, then add a "Transaction action=Begin" at the very start of the macro code and add a "Transaction action=End" at the very end of the macro code.
It is not perfect, but this should take care of most of the existing macro code that updates database variables and allow those macro to run with acceptable performance without having to change anything.
Gotchas
1 - Command MoveCopy can not be run inside a transaction. This means if you start a transaction (Transaction Action=Begin) you must commit it before running the MoveCopy command, otherwise the macro will abort with an error. MoveCopy requires opening two databases at the same time and does not support transactions. It is possible that old macros may run into this "gotcha" as a result of the automatic transaction inserted by the preprocessor (as mentioned above). In this situation you will need to change your macro code to start and end the transaction at the appropriate place.