GnuCash incompatible with concurrent access

[dckc]

the dbi (SQL) backend which comes with GnuCash 2.4 is currently not designed for true multi-user access. -- GnuCash FAQ

I have to stop access from GnuCash to get access from other tools.

Design

• GnuCash + guile actors for personal finance automation? #51

[dckc]

Brainstorming Approaches to the issue

ack: "we" = me with a generative AI chatbot. I'm conflicted about the ethics of using this stuff, but I'm trying it out.

We initially considered several approaches to address the concurrent access challenge:

1. Application-Level Locking/Coordination: We thought about having Finquick respect GnuCash's gnlock by only performing write operations when the lock was clear, or operating in a read-only mode when GnuCash is active. But GnuCash takes the lock when we open the file and doesn't give it up until we close it.

2. Automated GnuCash Control: We considered scripting the operating system to close GnuCash before Finquick's "pull" operations and re-open it afterward. While this automates manual steps, it still relies on GnuCash being closed, interrupting the user workflow.

3. Direct Database Manipulation: We briefly explored directly writing to the underlying database (SQLite, PostgreSQL, MySQL) while GnuCash was open. However, we quickly identified this as highly risky due to GnuCash's in-memory data model and the potential for data corruption and inconsistencies, which GnuCash's gnlock mechanism is designed to prevent.

4. GnuCash Internal Scripting (Scheme/Python): This emerged as the most promising path to eliminate the stop/start workaround and achieve our goal of continuous data integration. By running scripts within an open GnuCash instance, the script operates within GnuCash's own lock. This allows modifications without external conflicts, leveraging GnuCash's own internal API for data integrity and eliminating the need to stop and start the application. We decided to focus on Scheme due to the user's team's familiarity with it, and its native integration with GnuCash's core.

Current Workflow Challenges

To focus the search for solutions, we reflected on a workflow that currently suffers from this issue:

1. Lossy Bank/Card Ingestion: SheetSync is a service that continuously ingests data from Bank/Card accounts into a Google sheet.
2. Categorization: We then use the Google sheet to categorize these newly imported transactions.
3. OFX Import/Sync: SheetSync doesn't reliably reproduce account balances sufficient to reconcile with statements. So we download OFX (or CSV which we convert to OFX) and import into GnuCash.
4. Push-Txids: We use Finquick to read transaction split guids from GnuCash and push them into the corresponding rows in the spreadsheet. This step works while GnuCash is open.
5. Pull-Categories: This is the problematic step. We use a finquick script to write category updates (splits) back to GnuCash. This operation currently requires us to close GnuCash to avoid gnlock conflicts and potential data corruption, and then restart GnuCash afterward. This stop/start cycle is the primary inefficiency we aim to eliminate.

Sometimes we update the db while GnuCash is running and holding the lock; it works most of the time, but we don't want to rely on it.

For this workflow, internal scripting seems promising.

Progress on GnuCash Scheme Scripting:

We embarked on a challenging but fruitful journey to establish a working Scheme scripting environment within GnuCash, which has revealed unique API behaviors along the way.

Key Scripting Discoveries for Internal GnuCash Scripting:

1. Confirmed Code Execution & Menu Integration:

   • We initially attempted to use standard gnc:alert or gnc:display-message for a simple "Hello World," but these functions proved unavailable.

   • Discovery: We successfully implemented "Hello World" as a GnuCash Report.

   • Lesson Learned: We used gnc:define-report. We discoverd HTML rendering functions (gnc:html-markup-h1), and the renderer function itself takes only one argument (report-obj). We also found that we must encapsulate content within gnc:make-html-document > gnc:html-document-add-object! > gnc:make-html-text.

   • File Locations: We confirmed that GnuCash looks for configuration in ~/.config/gnucash/config-user.scm and for scripts in ~/.local/share/gnucash/. Our setup uses symlinks to manage these files from a central ~/qtrx/gnucash-config/ directory:

     lrwxrwxrwx 1 connolly connolly 50 May 23 01:39 /home/connolly/.config/gnucash/config-user.scm -> /home/connolly/qtrx/gnucash-config/config-user.scm
     lrwxrwxrwx 1 connolly connolly 52 May 23 02:36 /home/connolly/.local/share/gnucash/get-book-info.scm -> /home/connolly/qtrx/gnucash-config/get-book-info.scm
     lrwxrwxrwx 1 connolly connolly 59 May 23 02:06 /home/connolly/.local/share/gnucash/hello_gnucash_report.scm -> /home/connolly/qtrx/gnucash-config/hello_gnucash_report.scm
     

2. API Naming Inconsistencies & Introspection Limitations:

   • A significant hurdle has been the unpredictable naming conventions of core GnuCash Scheme API functions. For instance, gnc:current-book was unbound, but we found gnc-get-current-book to be the correct function for accessing the current financial book object.

   • Crucially, the standard Scheme bound? function is unavailable, which prevents us from safely checking if a function exists before calling it. This leads to crashes on unbound calls and necessitates a methodical, trial-and-error approach (or direct source code inspection) to discover correct function names.

3. MAJOR BREAKTHROUGH: We Found xaccSplitSetAccount!

   • Through targeted searching (specifically, an ack search revealing its use in qif-import/qif-to-gnc.scm and other files within /usr/share/guile/site/3.0/gnucash and /usr/share/guile/3.0), we identified the definitive function for modifying a transaction's split account: xaccSplitSetAccount. This function uses an xacc prefix and CamelCase naming, indicating a specific internal API. This is a critical step towards programmatically updating categories.

Current State:

We now have a confirmed framework for executing Scheme code within GnuCash
and accessing the core QofBook object (gnc-get-current-book). Most importantly,
we know how to update a split's account (xaccSplitSetAccount). This capability is fundamental to automating the category synchronization process.

This works, modulo gnc-book-get-filename:

;; get-book-info-menu.scm
;; A script to get current book info, registered via gnc-add-scm-extension.

(use-modules (gnucash engine))      ; For gnc:current-book, gnc:book-file-name
(use-modules (gnucash app-utils))   ; For gnc:message, if it works for logging.
(use-modules (gnucash core-utils))  ; For N_

;; Define a logging function that tries GnuCash's internal message system first
(define (log-message msg)
        (display (string-append "SCRIPT LOG: " msg "\n"))
        (force-output))

;; This is the function that defines the action for your menu item
(define (run-get-book-info window) ; The lambda in make-menu-item receives the window object
  (let ((book (gnc-get-current-book)))
    (if book
        (let ((filename (gnc-book-get-filename book))) ; <<< TRY THIS: gnc-book-get-filename
          (if filename
              (log-message (string-append "Current GnuCash file: " filename))
              (log-message "Current GnuCash file name could not be retrieved (maybe not saved?).")))
        (log-message "No GnuCash file is currently open."))))

;; Register the menu item
(gnc-add-scm-extension
 (gnc:make-menu-item
  (N_ "Get My Book Info")          ; Name that appears in the menu
  "0d9fe0a6-de1b-4de5-a27c-1919cd9fe484" ; *** IMPORTANT: Generate a NEW unique GUID ***
                                        ; Use 'uuidgen' in a terminal.
  (N_ "Displays info about the currently open GnuCash book") ; Tooltip/Description
  (list (N_ "Tools"))             ; Path: "Tools" menu
  (lambda (window)                      ; The action function when clicked
    (run-get-book-info window))))        ; Call your defined action function

Next Steps:

To complete the category-updating script, we plan to identify the precise function names for the remaining core operations within this specific GnuCash build:

• Finding Transactions: How to retrieve a list of transactions from the QofBook (e.g., to find uncategorized transactions from Finquick).

• Accessing Splits: How to get the individual splits from a QofTransaction object.

• Looking Up Accounts: How to find a QofAccount object by its name (for assigning new categories).

• Saving the Book: The function to persist all changes we make to the script to the GnuCash file.

We will discover these remaining functions through targeted testing and, if necessary, further inspection of the GnuCash source code's Guile bindings.