add new version in pb_* table and trigger

[wolfsolver]

related to moneymanagerex/mmex-sync#39

---

Ticket: Implement Local Sync Schema Versioning and Migrations (Database Version 2)

Description

To handle local database updates and migrations for synchronization technical components (like triggers and metadata tables) without conflicting with the main MMEX app's user_version, we need to implement a dedicated local sync versioning system.

Specifically, we need to upgrade the local sync schema to Version 2 to update the deletion triggers to use INSERT OR IGNORE instead of INSERT.

---

Technical Explanation & Database Schema

To track the sync schema version independently, we use a key-value metadata table called pb_sync_info where KEY is the Primary Key. This guarantees a single-row constraint for our metadata keys (such as VERSION) and prevents duplicate rows from leaking on application startup when executing INSERT OR IGNORE.

SQL Setup:

-- 1. Create the sync info table
CREATE TABLE IF NOT EXISTS "pb_sync_info" (
    "KEY" TEXT PRIMARY KEY,
    "VAL" TEXT NOT NULL
);

-- 2. Insert initial version '1' if the key 'VERSION' does not exist
INSERT OR IGNORE INTO pb_sync_info (KEY, VAL) VALUES ('VERSION', '1');

---

Migration Steps (Version 1 to 2)

During the database startup/connection sequence:

1. Read the current sync version:

   SELECT VAL FROM pb_sync_info WHERE KEY = 'VERSION';

   (If the query returns no rows or the table doesn't exist, assume version is 1 after creating it).

2. Execute the Migration from v1 to v2:
   If currentVersion < 2:

   • Drop the existing delete triggers for all synchronized tables to prepare them for regeneration:

     DROP TRIGGER IF EXISTS TRG_<table_name>_DELETE;

   • Update the version value to '2':

     UPDATE pb_sync_info SET VAL = '2' WHERE KEY = 'VERSION';

3. Recreate the triggers (Normal flow):
   The standard trigger initialization code will then recreate the deleted triggers using the updated INSERT OR IGNORE logic:

   CREATE TRIGGER IF NOT EXISTS TRG_<table_name>_DELETE
   BEFORE DELETE ON <table_name>
   FOR EACH ROW
   WHEN OLD.pb_id IS NOT NULL
   BEGIN
       INSERT OR IGNORE INTO pb_DELETED_RECORDS_LOG (TABLE_NAME, PB_ID) 
       VALUES ('<table_name>', OLD.pb_id);
   END;