Version 7.7.2 sees the introduction of support for direct Sqlite updates to the database in the macro language.
I can't protect the SQLite database from every wayward update (lets face it, you could already damage it badly now via SQLiteSpy), so there will also need to be some common sense used by macro authors here.However, I will try to lay out an environment and "rules of engagement" for updates so we minimize the possibility of database corruption.
Initially, only updates to the "Caches" and "CachesMemo" tables will be supported, but the long term goal will be to also support the other database tables.
Usage Rules:
1. Only use one update statement per SQL (can't have multiple statements using the ';' ). Though it is fine for this one statement to update multiple database variables.
2. Updates not permitted in HTML custom format macros or when using the special tags %macro= or %mtag=
3. Your update SQL line must always begin "update caches" or "update CacheMemo" (with exactly one space, but not case sensitive)
4. You can only update a subset of all the database variables in these tables (see list at end)
5. You must use the RESYNC command after running a direct update sqlite query.
6. Direct Sqlite updates are only permitted to your currently open GSAK database.
The RESYNC command should be used after doing direct updates to the database via SQLite. In its simplest form it will just refresh the grid (update the screen and refilter for any changes made). On a more detailed note, it will also make changes to the database to "keep it in sync".
"keep it in sync" is somewhat synonymous to running "Database=>Repair/defrag" (without the defrag part). However as a full repair could be rather time consuming and possibly eliminate the speed gains of doing the direct update, the command does a "smart" repair. For example, why would you need to do a full repair just because you updated user data 3.
That is, when writing macro code that updates the database, GSAK will interrogate your UPDATE statements to scan for the fields you are updating. For example, if you updated the name, then RESYNC should also update smart names (first checking that smart names are enabled for that database). If you updated the found status, then RESYNC should run similar code to Global replace to make sure your found status is still in sync with your found logs. Similar updates would be done for other columns that need it as the result of these updates.
Here is a simple (contrived) example of how to write such code. Let us say you wanted to update user3 with the literal "close" for caches <= distance 50 and "far" if > 50
Transaction Action=Begin $status = sqlite("sql","Update caches set user3 = 'close' where distance <= 50") $status = sqlite("sql","Update caches set user3 = 'far' where distance > 50") Transaction Action=End Resync |
Notice that you don't have to issue the RESYNC command after every update. However, I do suggest you group your required updates and then run the command directly after the last update in the group.
Allowed database variables to update (remove the "$d_" when using in your SQLite query):
