Early review: Lua macros can change caches (API version 3+) (feedback is welcome)

[nagisml]

Hi @AgreeDK @urs-beeli @GeePa67 and everybody else

I'd like an early review and early feedback of a new set of macro functions before I open a PR. It's not final; I'd rather change direction now than after a PR review. The branch is nagisml/OpenSAK → feature/lua-cache-write (based on beta), and everything is documented in the generated API reference:

📄 API documentation, version 3. Start with Changing caches.

Until now, macros could mostly read (only corrected coordinates and moving/copying caches changed anything). With this branch, macros can change most cache data. That's a big step, so I'm mainly asking for a judgement on two questions:

1. Is the right set of write functions available? Is anything important missing?
2. Is anything too risky and better removed again? I'm happy to drop or restrict a function.

What's new in API version 3

Function	What it does
opensak.update(code, fields)	Change fields of one cache (name, D/T, found, user flag, user data, color, note, corrected coordinates, …)
opensak.insert{...}	Add a new cache (code, name, type, lat, lon + optional fields)
opensak.sql_write(query, params)	Run one SQL INSERT or UPDATE on the cache tables see #1033
opensak.transaction(fn)	Keep all changes made in fn together, or undo all of them on an error (like GSAK's Transaction command)
opensak.in_transaction()	Whether the macro is inside a transaction (like GSAK's InTransaction())

Existing write functions, for context: set_corrected, clear_corrected, create_database, switch_database, move_caches, copy_caches.

Example:

local n = opensak.transaction(function()
    local n = 0
    for c in opensak.caches{ found = true, fields = {"code"} } do
        opensak.update(c.code, { user_flag = true })
        n = n + 1
    end
    return n
end)
print(n .. " caches flagged")

Safeguards built in

• The user must approve each database. Before a macro's first change to a database, OpenSAK shows its own dialog (not one the macro controls): Deny, Until OpenSAK closes, or Always. "Always" approvals are listed in Settings → Folder permissions and can be removed there. This also applies to the older set_corrected / clear_corrected.
• Only the active database can be changed. Other databases stay read-only for macros.
• Nothing can be deleted. There's no delete function, sql_write refuses DELETE, and a trigger catches the rows INSERT OR REPLACE would delete.
• Protected fields: the cache code and everything OpenSAK maintains itself (distance, bearing, log/found/waypoint/trackable counts, last log dates, import metadata, location data) cannot be written. Trying to is an error, not silently ignored.
• Derived data stays consistent: after every change OpenSAK recalculates distance/bearing, counts and log dates of the caches involved.
• sql_write limits: only INSERT/UPDATE on caches, user_notes, waypoints, logs, attributes, trackables. No schema changes, no ATTACH, no writing PRAGMAs, no BEGIN/COMMIT (use opensak.transaction()), 60 s timeout per statement. This is enforced by SQLite's authorizer, not by inspecting the SQL text.
• Transactions can't stay open: an error, a cancelled macro or the instruction limit rolls back. Nested transactions work, and a failed write inside a transaction undoes only that write.

Where I'm unsure — opinions welcome

1. sql_write: keep, restrict or remove? It's the most powerful function. It can change many caches at once, including logs and waypoints. Should it be limited to caches / user_notes, or removed in favour of update / insert only?
2. Writing logs, waypoints, attributes, trackables via SQL. Useful (e.g. importing own logs or waypoints from a CSV), but these tables are normally filled only by imports. Too risky?
3. insert{} for new caches. Fine, or should macros only change existing caches?
4. Approval model. Is per database, with Deny / Until close / Always, enough? Should "Always" exist at all for write access, or should there also be an approval per macro?
5. Protected fields. Is the list right? Is something writable that shouldn't be, or protected that users would want to change?
6. No delete at all. Keep it that way, or is a (confirmed) delete needed?
7. Anything missing compared to what you'd do with GSAK macros?

Related

• [Epic] Macro & Scripting Support (Lua-based macro system) #938 — macro epic
• Lua macros: file write access, approval dialog and hardening of folder permissions (follow-up to #949) #978 — write access, approval dialog and hardening of folder permissions
• DB schema: database defaults for NOT NULL columns, so a raw SQL INSERT only needs the real data #1033 — database defaults so a raw SQL INSERT INTO caches only needs 5 instead of 21 columns (sql_write works without it, but opensak.insert{} is the easier way to add a cache for now)

There's no PR yet. Comments here or on the branch are both fine. Thanks for taking a look!

[GeePa67]

I will give it more thought, but my initial comments are:

1. I want to be able to create logs. I have an existing macro fir GSAK fir adding found logs to lab caches. The reason us that other macris that generate statistics expect a found log. However, sites like lab2gpx just gave the found flag for the lab points but no actual found log.
2. Can transactions be closed/committed or cancelled by the macro?
3. Can temp tables be created? I know there are a number of GSAK macros that do this.
4. Not being able to create a separate database and attach it to the Opensak database may be an issue. I know some macros do this. Particularly the very popular findstatgen macro for generating profile statistics. This along with the badgegen macro may be the most complicated GSAK macros out there.

[nagisml]

Hi @GeePa67

Thanks a lot for the valuable feedback.

1. This would be possible with SQL in the proposal above
   local n = opensak.sql_write([[ INSERT INTO logs (cache_id, log_type, log_date, finder, text, text_encoded, logged_by_owner) SELECT id, ?, ?, ?, ?, 0, 0 FROM caches WHERE gc_code = ?]], { "Found it", "2026-10-10 14:30:00.000000", "myUsername", "TFTC", "GC12345" }) -- n == 0 if the cache is not in the database
   or we can add a own function like following (not yet in the proposal TODO (Included in proposal as of 11.10.26))
   opensak.add_log(code, { type = ..., date = ..., finder = ..., text = ... })

2. Yes, but not with separate commit/rollback calls like GSAK's Transaction action=End|Rollback. A transaction is tied to the function passed to opensak.transaction

GSAK	OpenSAK
Transaction action=Begin	opensak.transaction(function() …
Transaction action=End (commit)	the function returns: normally at its end, or early with return
Transaction action=Rollback	error(...) inside the function
InTransaction()	opensak.in_transaction()

Commit early

opensak.transaction(function()
    opensak.update("GC1", { user_flag = true })
    if done then return end          -- commits what was done so far
    opensak.update("GC2", { user_flag = true })
end)

Cancel (roll back) without ending the macro. Wrap the call in pcall. The error undoes everything, and the macro carries on:

local ok, err = pcall(opensak.transaction, function()
    opensak.update("GC1", { user_flag = true })
    if not opensak.confirm("Keep the changes?") then error("cancelled", 0) end
end)
-- ok == false, err == "cancelled", GC1 unchanged

Without pcall, the error rolls back and then also stops the macro with that message.

Undo only part. A nested pcall(opensak.transaction, …) inside the outer one rolls back only its own changes. The outer transaction keeps the rest.

3. Question is what you understand under a TEMP table

A real new table within the current OSAK DB is not allowed
Current behaviour

Call	Result
opensak.sql("CREATE TEMP TABLE t(x)")	refused: "SQL in macros is read-only (SELECT only)"
opensak.sql_write("CREATE TEMP TABLE t(x)")	refused: only INSERT/UPDATE on the six cache tables
opensak.sql_write("INSERT INTO temp.…")	refused, which also protects OpenSAK's own internal temp table

What works instead

• A WITH clause (CTE) for an intermediate result inside one statement, e.g. opensak.sql("WITH t(x) AS (VALUES (1),(2)) SELECT x FROM t").
• Lua tables for intermediate data across several statements: lookups by code, counters, lists. Often this is what a GSAK macro used a temp table for
• The database = ... option for reading another database, instead of the ATTACH + temp table pattern some GSAK macros use.

4. Lets see step by step there is more then just creating a own macro-owned database which is not yet covered.
   So the proposal would be to allow "Creating and handling a private, writable SQLite database?" TODO?

[urs-beeli]

@nagisml Thanks for your well described outline. As I said elsewhere, I am currently travelling for several weeks and don't really have time and access to go through my macros systematically, so all feedback is out of my head.

• only active db can be changed: I am not yet sure if that is too limiting, but in most use cases this can probably be solved by ensuring that the db that needs to be changed is the active one, and any other db is opened in read-only mode (though I am not sure if/how that is supported at the moment because I seem to recall reading that ATTACH is not supported). I don't think I have any use cases where more than one DB needs to be written to, but having more than one db open at the same time is a common thing in my macros

• I am not sure if I understood how bulk updates would work, take your example where you set the user flag using a lua-for-loop. Would it be possible to do this as an sql_write() call along the lines of update caches set userflag=true where code in (......)

As to your questions:

1. *sql_write - it sounds useful, I would keep it for the time being
2. writing logs, waypoints, attributes, trackables via sql -> as a macro author, I'm in favour of giving the author as much power as possible, so I would implement it as described
3. insert for new caches I would certainly allow that
4. approval mode personally, I feel that it is the users responsibility to decide if they trust that macro's author at the time of macro installation. Based on this, I don't really see the need to grant per-db, per-macro permissions
5. protected fields the choice of fields make sense. You write that these are automatically updated after every write. In GSAK, there was a specific macro command to resync those fields once direct sql manipulation was finished. It would probably be enough if these fields were recalculated at the end of the transaction (just before the commit) instead of after every sql modification and offer the possibility to manually trigger recalculation within the transaction, if a later query depended on these calculated fields
6. not supporting delete I think the GSAK macro language supported deleting entries in every table (and certainly allowed it using sql), but personally, I don't think I've ever used that
7. what is missing
   • creating my own tables besides the standard tables: I have a few macros where I created a new tables that had nothing in common with the GSAK data structure, apart from a "code" field that was used as a foreign key to the caches table. However, to be honest, I can't recall by heart where I used that
   • creating my own tables in a standalone database: this is something I have definitely done multiple times and not having the possibility would be an unnecessary restriction in my eyes. What is the rationale for not allowing the creation of "free" tables in a database? If it has something to do with the ORM or is just a measure to protect the "vital data" mayb this would be a solution: You could introduce a flag in each database that either marks it as a "standard" db (i.e. one with a caches-table and all the other related tables). In such a db a macro would not be allowed to create additional tables. In addition, the macro language could support the creation of a "general" db that would be totally empty on creation. In these DBs the macro could then be allowed full control, creating its own tables, indices, etc.
   • temp_tables I would need to check the GSAK help file, but if I recall correctly these tables could be created in-memory with full freedom of their content and were often used to efficiently collect, sort and collate data from normal databases. I am using this in some macros, mainly because the support for list- and array-variables in GSAK is rather limited and cumbersome, so just delegating that into one of those temp tables is actually much easier. However, with the full power of LUA in the macros, such temp tables could potentially be replaced using LUAs own objects/arrays (though I am a bit rusty on how the sorting on arrays works in LUA).

I hope this feedback is helpful.

[nagisml]

Hi @urs-beeli
I'm very happy you took the time to leave such valuable input, even during your very busy weeks. Thanks a lot!

Here my answers. I also updated the proposal branch and docu as on comments received, see next comment

"only active db can be changed":

A new feature opensak.macro_db() creates a macro based DB3. Maybe that helps already. Otherwise if we have the reuqimrent to ATTACH multiple OSAK based DBs together we need to include that. TODO?

bulk updates

Yes. opensak.sql_write() takes one whole UPDATE statement, so a bulk change doesn't need a Lua loop. The flag example in the docs can be done in one call:

local n = opensak.sql_write("UPDATE caches SET user_flag = 1 WHERE found = 1")
print(n .. " caches flagged")

Notes:

• The names are the database column names (user_flag, gc_code, found, …), not the Lua field names (code, …). opensak.columns("caches") lists them.
• Booleans are stored as 1 / 0.
• One sql_write() call is one transaction: either all rows change or none do. OpenSAK then recalculates the derived columns (distance, counts, log dates) of every cache the statement touched. The grid refreshes too.
• The return value is the number of rows changed.
• You need the user's write permission once per database, as for opensak.update().

2 writing logs, waypoints, attributes, trackables via sql

By SQL was already possible now additionally as opensak function. See next comment

3 insert for new caches

Thanks possible by opensak function and SQL but SQL is at the moment blocked by #1033

4 approval mode

I would agree to the statement, but to introduce a little security the first write to a opensak DB asks for a approval (by DB) after that there is no restriction anymore. So I guess this is acceptable and provides a little warning to the user.

5 protected fields or better RSYNC

I measured on by DB and did some optimizations. See next comment

Defer derived-column refresh to transaction commit; add opensak.resync()

Inside opensak.transaction() the columns OpenSAK maintains (distance,
bearing, log/waypoint/trackable counts, log dates) are no longer
recalculated after every write. The touched caches are collected and
recalculated once, just before the commit. This covers sql_write() and
the ORM writes (update, insert, insert_/update_<child>).
opensak.resync() recalculates them on demand inside a transaction.

Codes of caches added by SQL INSERT are still normalised immediately,
so the cache can be found by its code inside the transaction. A
rolled-back transaction or savepoint leaves nothing pending, and a
failed recalculation undoes the whole transaction. Outside a
transaction, every write is still recalculated before its own commit.

2000 insert_log calls on one cache in a transaction: 4.5 s -> 1.8 s.

6 not supporting delete

Will be taken up later as it needs to handle reference integrity. Eg if a SQL deletes a cache record we need to ensure that logs etc is also removed.

How a macro delete could handle it: TODO?

Function API (opensak.delete(code), opensak.delete_log(id), …): go through the ORM like the GUI does. Logs, waypoints, attributes, trackables and the note go with the cache automatically.
Raw SQL (sql_write("DELETE FROM caches WHERE …")): the macro connection already has OpenSAK's own temp triggers, including one that currently blocks every delete. That one would become a BEFORE DELETE ON caches trigger that first deletes the cache's child rows. A bulk SQL delete would then cascade just like the GUI.
Two more things a delete needs:

Recalculation: deleting a log, waypoint or trackable changes the cache's counts and log dates. The existing triggers would record the cache from OLD.cache_id, so the recalculation works the same way (at the commit inside a transaction).
The open app: the cache list and detail panel must drop deleted caches, which needs a new "caches removed" notice to the host.

7 creating my own tables besides the standard tables:

Proposal using the new opensak.macro_db() for that. I would need feedback if that works

7 creating my own tables in a standalone database:

Covered by the new opensak.macro_db()

7 temp_tables

As you wrote either by LUA tables or now by session temp tables as provided by sqlite

[nagisml]

Delete requirement now extracted to #1035

[GeePa67]

You might want to get feedback from Mike Wood (lignumaqua) as well on this. He has authored some of the more sophisticated GSAK macros. His findstatgen macro creates a FoundStatsSQLLite.db3, for example. There is also a number of ATTACH statements in the code. I don't know all of how it is used and what it contains and maybe there is another way to do what is being done in Lua, but Mike would be the best to give a view on it.

[nagisml]

Hi @GeePa67 @urs-beeli @AgreeDK

Thanks again for the feedback. The branch has been updated based on your feedback

Lua macros: new SQL and write features (feature/lua-cache-write)

Four new commits on this branch. The API reference has been updated with all functions, parameters and examples: docs/macros/api.md. The VS Code autocompletion stub (macros/types/opensak.lua) is updated as well.

1. Temporary tables in opensak.sql()

Macros can now create and use their own TEMP tables, views and indexes. They live only while the macro runs, are never written to the database file, and need no write permission. They can be used in any query, including opensak.sql_write() and inside opensak.transaction().

opensak.sql("CREATE TEMP TABLE todo (code TEXT PRIMARY KEY)")
opensak.sql("INSERT INTO todo SELECT gc_code FROM caches WHERE found = 0 AND country = 'Switzerland'")
opensak.sql_write("UPDATE caches SET user_flag = 1 WHERE gc_code IN (SELECT code FROM todo)")

A temp table may not use the name of an existing table (e.g. caches). Everything else stays read-only for opensak.sql().

2. Logs, waypoints, attributes and trackables without SQL

The rows belonging to a cache can now be read, added and changed the same way as caches with opensak.update() / opensak.insert{}:

Read	Add	Change
opensak.logs(code)	opensak.insert_log(code, fields)	opensak.update_log(id, fields)
opensak.waypoints(code)	opensak.insert_waypoint(code, fields)	opensak.update_waypoint(id, fields)
opensak.attributes(code)	opensak.insert_attribute(code, fields)	opensak.update_attribute(id, fields)
opensak.trackables(code)	opensak.insert_trackable(code, fields)	opensak.update_trackable(id, fields)

-- add a note log and a final waypoint
local id = opensak.insert_log("GC12345", { type = "Write note", date = "2026-10-11", text = "Final solved" })
opensak.insert_waypoint("GC12345", { prefix = "FN", type = "Final Location", lat = 47.36872, lon = 8.54093 })

-- change rows found by reading them first (each row has an id)
for _, w in ipairs(opensak.waypoints("GC12345")) do
    if w.prefix == "PK" then opensak.update_waypoint(w.id, { comment = "free parking" }) end
end

• false clears a field, as for caches.
• OpenSAK updates the cache's counts and log dates itself.
• The usual write permission applies.
• The read functions also take the database option.

3. A database per macro: opensak.macro_db()

opensak.macro_db() creates <macro name>.db3 next to the macro file (if needed) and attaches it as schema macro. In it the macro has full access: CREATE/DROP/ALTER tables, indexes, views and triggers, INSERT/UPDATE/DELETE, and PRAGMA macro.user_version. The data stays between runs. One statement can combine the active database and the macro database.

opensak.macro_db()
opensak.sql("CREATE TABLE IF NOT EXISTS macro.seen (code TEXT PRIMARY KEY, first_seen TEXT)")
opensak.sql("INSERT OR IGNORE INTO macro.seen SELECT gc_code, date('now') FROM caches")

-- caches that are new since the macro first ran
for _, r in ipairs(opensak.sql(
        "SELECT c.gc_code, c.name FROM caches c JOIN macro.seen s ON s.code = c.gc_code "
        .. "WHERE s.first_seen = date('now')")) do
    print(r.gc_code, r.name)
end

• Create objects with the macro. prefix, since unqualified names refer to the active database first.
• Needs no permission. The macro must be saved, because the file is named after it.
• Changes inside opensak.transaction() are kept or undone together with those of the active database.
• A plain SQL ATTACH stays refused, so a macro can't open any other file on disk.

4. Faster transactions and opensak.resync()

Inside opensak.transaction(), the columns OpenSAK maintains (distance, bearing, log/waypoint/trackable counts, log dates) are now recalculated once, just before the commit, instead of after every write. This is a big speed-up for loops with many writes, e.g. 2000 insert_log calls on one cache: 4.5 s → 1.8 s.

Until the commit, reads inside the transaction still see the old values. Call opensak.resync() when a later read depends on them:

opensak.transaction(function()
    for i = 1, 3 do opensak.insert_log("GC12345", { type = "Write note" }) end
    opensak.resync()                                   -- recalculate now
    print(opensak.cache("GC12345").log_count)          -- already includes the 3 new logs
end)

Outside a transaction nothing changes: each write is recalculated before its own commit.

Bulk updates with sql_write()

Not new, but a common question: a bulk change doesn't need a Lua loop. One statement is one transaction and is recalculated in one go:

opensak.sql_write("UPDATE caches SET user_flag = 1 WHERE found = 1")

local codes = opensak.codes()                          -- caches of the current filter
opensak.sql_write("UPDATE caches SET user_flag = 1 WHERE gc_code IN ("
    .. string.rep("?", #codes, ", ") .. ")", codes)

[AgreeDK]

Thanks @nagisml for opening this as a discussion before the PR, and for turning the feedback around so quickly. Thanks also to @GeePa67 and @urs-beeli (especially while travelling!) for the input. This is exactly the kind of review I hoped we'd get from people who actually write GSAK macros.

Overall I'm very positive. The direction is right, and the safeguards (SQLite authorizer instead of text inspection, rollback on error/cancel, protected derived fields, no ATTACH) are well thought out. A few thoughts from my side:

1. sql_write: keep it
I agree with keeping it. It's a deliberate change from the original concept doc, which proposed read-only SQL, but the use cases here (bulk updates, importing own logs/waypoints) make a good case for it. I'll update the concept doc (§9b) so the trade-off is written down rather than implied.

2. Logs, waypoints, attributes, trackables and insert{}: fine
The dedicated insert_log / insert_waypoint / … functions are a nice addition. I'd like the docs to steer authors towards those first and treat sql_write as the power tool for bulk work.

3. Approval model: this is where I'd like to tighten things a bit
I understand @urs-beeli's point that trusting a macro is ultimately the user's decision. My concern is that "Always" is granted per database. Once a database is approved permanently, any macro run later can write to it without a prompt, including one downloaded from a forum post a year later. Since we can't control how macros are shared, I'd prefer one of these:

• tie "Always" to the combination of macro + database, or
• keep "Always" per database, but show which macro is writing in the prompt and in Settings → Folder permissions.

The first option is my preference. It costs the user one extra click per new macro, which I think is acceptable for write access. Happy to hear arguments either way.

4. opensak.macro_db()
This looks like a good answer to the "own tables / standalone DB" need. Two questions:

• Since it creates a file on disk without asking, does it fit into the folder-permission model from Lua macros: file write access, approval dialog and hardening of folder permissions (follow-up to #949) #978? Should it at least be listed somewhere so users can see which macro databases exist?
• What happens if the macro file is read-only or lives in a folder the user can't write to?

5. Delete
Agree with splitting it out to #1035 and leaving it out of this PR.

6. ATTACH / complex GSAK macros
@GeePa67's suggestion to involve Mike Wood (@lignumaqua) is a good one. findstatgen and badgegen are probably the best real-world test of whether macro_db() + the database = option cover what ATTACH was used for in GSAK. I'll reach out to Mike and ask him to take a look.

From my side you're welcome to open the PR when you're ready. The approval-model question doesn't need to block that, but I'd like it settled before we merge.

[nagisml]

@AgreeDK thanks a lot for the feedback

Following updates have been done on the branch.

• Added a new feature flag to be flexible on realeasing
• rel 2. Updated docu to prefer OpenSAK functions before pure SQL
• rel 4 Agreed on the permission model. macro_db() will check write access to the macro's folder through the same folder-permission check as file writes. The macros folder is permitted by default, so usually nothing changes, and elsewhere you get the usual approval dialog. It will also print the path when it first creates the file. I'd rather not keep a separate list: the database always sits next to the macro and is named after it (foo.lua → foo.db3), which is easier to find than a list that goes stale when macros are moved. An unwritable folder fails with a clear error. A read-only existing .db3 can be read but not written; both cases will be in the docs.

Left out for the moment

• rel 3. to allow discussions first. For myself I can live with all 3 solutions. GSAK has an installation system which does not block anything but is also an additional "approval" by the user

As before find it here: docs/macros/api.md.

[AgreeDK]

I reached out to Mike Wood (@lignumaqua) as @GeePa67 suggested. Mike has written some of the most advanced GSAK macros (FindStatGen and others), and he kindly gave detailed answers. Here's a summary, because it adds a dimension we haven't fully covered yet.

1. Mike suggests a companion database per cache database
Instead of (or alongside) one database per macro, Mike suggests a parallel database for each cache database, in the same folder:

• keyed by the cache code, often one row per cache
• shared by all macros, which can create their own tables and columns freely
• attached automatically for macro access
• the main database keeps INSERT/UPDATE-only access and no custom tables

His reasons:

• Macros sometimes build on each other's data, e.g. one macro uses a custom field another macro created. That's not very common, but it does happen.
• A single macro database shared by all cache databases could grow huge, for example with translated descriptions across 50,000-cache databases.
• In GSAK, custom tables ended up in the main database only because nobody stopped it. He agrees they're better kept separate.

2. FindStatGen is the other pattern, which macro_db() already fits
FSG keeps its own separate database in the macros folder. Its data is organised by found logs, not caches, so the same cache can appear several times when found more than once. It also stores the macro's options and many other tables. That's close to what opensak.macro_db() does today.

The gap: FSG temporarily attaches several standard cache databases while it runs. Found and placed caches are often kept in separate databases. In the current proposal, ATTACH is blocked and the database = option reads one database per call. This matches @urs-beeli's point about having several databases open at once.

(Porting FSG itself isn't realistic. It's 20,000+ lines and depends on a server back end on gsak.net for maps and charts. But it's a good test of the pattern.)

3. Whatever we choose must be included in backups (Mike's point, and relevant to the backup epic #942).

Questions for discussion, mainly @nagisml:

1. Should we have both?
   • a per-cache-database companion DB, shared by all macros and keyed by cache code (Mike's model), and
   • macro_db(), private per macro, for data that isn't per cache, like FSG?
2. Should macros be able to attach other OpenSAK cache databases read-only, as a controlled replacement for ATTACH? Write access would stay limited to the active database.
3. The companion DB must follow its main database on copy, rename, delete and backup. What should happen to its rows when caches are moved or copied between databases?
4. This overlaps with Full GSAK Field Compatibility ([Epic] Full GSAK Field Compatibility (roadmap #3) #1028). Mike confirmed that GSAK custom field definitions live in CustomDefs in gsak.db3, and the values in a Custom table per database. If imported GSAK custom fields also go into the companion DB, the macro system and the field import would share one design rather than two.

Mike also sent a sample FSG database so we can see a real-world schema. It may contain personal log data, so I'll share it privately rather than here. @nagisml, I'll send it to you directly if it's useful.

[nagisml]

@AgreeDK and Mike thanks for the proposals.
1. Both: yes. The per-cache companion DB was already on my personal ideas list. Keep macro_db() private per macro for data that isn't per cache, like FSG.

Following some ideas maybe not all to be taken up but to be discussed when feasable

I don't think every table in the companion DB has to be keyed by cache code, though. A macro may want to store metadata there, and OpenSAK macro authors may have other data that is independent of the caches table. So I'd let a table be declared either cache-linked (keyed by cache code) or free (no link to caches). OpenSAK only does the cache-related housekeeping for cache-linked tables.

There's an important difference in ownership. The companion DB belongs to OpenSAK. A macro_db() belongs to its macro. So OpenSAK has to manage the companion DB properly, which means we need extra functions for it:

• Ownership tagging: every table in the companion DB records its owner (macro id, or opensak for core data like custom fields) and whether it is cache-linked. Without this, the cleanup functions below can't work. (not sure we can ensure that within the code for macros)
• Maintenance: VACUUM, integrity_check, and orphan cleanup for cache-linked tables, meaning rows whose cache code no longer exists in the main DB. Orphans will happen. In WAL mode a commit across attached databases is not atomic as a whole, so a crash between the two writes can leave rows behind. A cleanup pass when the database opens fixes that.
• Size limits: show the size per owner, warn above a threshold, and maybe set a quota per macro. (maybe to complex to implement?)
• Isolation: a corrupted or oversized side DB must not crash OpenSAK or damage the main DB.
  • The main DB never depends on the companion's schema.
  • The companion is attached only when it's needed. eg at least one custom field
  • If the integrity check fails, OpenSAK detaches it and renames it to *.corrupt. It then starts an empty one and tells the user. The main DB is never touched.
  • Views that join the companion fall back to the plain cache data if the companion can't be attached.
• GUI: a maintenance dialog that lists data per owner (tables, row count, size), with "delete this macro's records" and "drop this macro's tables". (not sure that is possible in that details)
• Removed macros: when a macro is uninstalled, ask whether to keep or delete its data. Data that is kept shows up as "orphaned owner" in the maintenance dialog, so it can be cleaned up later. ( This needs first a install system for macros)
• There are surely more topics to validate, e.g. schema versioning of the companion DB, and how macro permissions (read vs. write per owner) apply.

For me this also means custom fields are part of this concept, so they live in the companion DB, not in the main DB. OpenSAK owns them. Macros can read them, and can write only with permission. Next to the default ATTACH we'd then provide a view like GSAK's CachesAll (e.g. caches_all) that joins the caches with their custom fields. That way ported GSAK filters and macros (FindNum > 100 AND Parking <> '') work almost unchanged. The real-columns-per-field design from the draft still applies; only the location changes.

docs/architecture/gsak-fields.md currently puts custom_fields / custom_values in the main DB, so it needs an update once we agree on this.

2. Yes. Attaching other cache databases read-only, maybe even any DB, is on the feature list. I'd do it in a follow-up rather than in this branch, as a controlled API, while the macro SQL authorizer keeps denying raw ATTACH. Happy to open a new issue for it.

3. OpenSAK has to handle this complexity, not the macros, but only for tables that have a real cache connection (cache-linked tables). Their rows follow the cache by its code. On copy they are copied, and on move they are moved. If a custom field doesn't exist in the target database yet, its definition is created there first, so no values are lost. Free tables and macro_db() data stay where they are.

4. Agree, see the custom fields part of point 1.

ps happy if you share the FSG DB, I have an own which I can also use for 2. tests. But just to mention there are more functions to be created before some complex macros can be ported. I've added my own (AI generated) GSAk Macro comapre at the end of the docu docs/macros/api.md. I'm very happy if @AgreeDK @GeePa67 @urs-beeli @lignumaqua and of course everybody else would help with a review and maybe a judgment on priorities

[nagisml]

Related to 2. ATTACH a new issue was opened #1074

[AgreeDK]

Thanks @nagisml, the macro_db() solution looks good to me. Reusing the folder-permission check and keeping foo.lua → foo.db3 predictable is better than a separate list. Agreed.

On the approval model (3), your GSAK comparison helped me settle it. In GSAK, installing a macro was the trust decision. OpenSAK has no install step, since you just drop a .lua file in the folder, so the first write is the closest thing we have to that moment.

So my proposal is to make the approval per macro instead of per database:

• The first time a macro tries to change data, OpenSAK asks: "Allow macro foo.lua to change your OpenSAK databases?" with Deny / Until OpenSAK closes / Always.
• "Always" covers all databases for that macro and is listed by macro name in Settings → Folder permissions, where it can be removed.
• The "only the active database can be changed" rule stays as it is.

This gives fewer prompts than today for people with many databases, matches @urs-beeli's view that trust is decided once per macro, and closes the gap where any later macro could write to a database that was approved earlier.

The one open detail is whether an "Always" approval should reset if the macro file is replaced or moved. Keying it on the file path seems fine to me for now. I'd rather not re-prompt every time someone edits their own macro.

@GeePa67 @urs-beeli, any objections?