Creating a Trigger Function to Synchronize account.account Data Between Odoo Databases

Creating a Trigger Function to Synchronize account.account Data Between Odoo Databases

This article explains how to create a PostgreSQL trigger function to synchronize account.account master data from an Odoo 14 database to an Odoo 17 database. Additionally, it includes converting account.account_type data between the two versions.

Overview

• Purpose: Automate data synchronization for account.account between two Odoo instances (14 and 17) with transformations for account.account_type.
• Key Challenges:
  • account_type fields may have been updated or renamed between Odoo versions.
  • Ensuring consistency across databases.

Steps to Create the Trigger Function

1. Setup Database Connections

Ensure both databases (Odoo 14 and Odoo 17) are accessible:

• Odoo 14 Database: Source database.
• Odoo 17 Database: Destination database.

Use PostgreSQL's foreign data wrapper (FDW) for cross-database access, or manage the connections in your script using Python (psycopg2).

2. Account Type Mapping

Map the account_type field between the two versions, assuming some changes between Odoo 14 and 17. Here's an example:

You can adjust the mapping as needed.

3. Create the Trigger Function

Below is the SQL function to handle synchronization:

sqlSalin kode-- Step 1: Create the trigger function
CREATE OR REPLACE FUNCTION sync_account_to_odoo17()
RETURNS TRIGGER AS $$
DECLARE
    mapped_account_type TEXT;
BEGIN
    -- Map account types from Odoo 14 to Odoo 17
    CASE NEW.account_type
        WHEN 'asset' THEN mapped_account_type := 'current_asset';
        WHEN 'liability' THEN mapped_account_type := 'current_liability';
        WHEN 'income' THEN mapped_account_type := 'revenue';
        WHEN 'expense' THEN mapped_account_type := 'operating_expense';
        WHEN 'equity' THEN mapped_account_type := 'equity';
        ELSE mapped_account_type := NULL; -- Handle unmapped types
    END CASE;

    -- Insert or update the record in the Odoo 17 database
    PERFORM dblink_exec(
        'dbname=odoo17 user=your_user password=your_password host=your_host',
        $$
        INSERT INTO account_account (id, code, name, account_type, company_id, currency_id)
        VALUES ($1, $2, $3, $4, $5, $6)
        ON CONFLICT (id) DO UPDATE
        SET code = EXCLUDED.code,
            name = EXCLUDED.name,
            account_type = EXCLUDED.account_type,
            company_id = EXCLUDED.company_id,
            currency_id = EXCLUDED.currency_id;
        $$,
        NEW.id, NEW.code, NEW.name, mapped_account_type, NEW.company_id, NEW.currency_id
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Step 2: Attach the trigger to the account_account table
CREATE TRIGGER after_account_insert_update
AFTER INSERT OR UPDATE ON account_account
FOR EACH ROW EXECUTE FUNCTION sync_account_to_odoo17();

Explanation of the Code

1. Mapping Account Types:
   The CASE statement translates the account_type field from Odoo 14 to Odoo 17.
2. dblink_exec for Remote Database Updates:
   • INSERT INTO Statement: Inserts data into the destination (account_account table in Odoo 17).
   • ON CONFLICT Clause: Ensures that updates occur if the record already exists.
3. Trigger Attachment:
   • AFTER INSERT OR UPDATE ensures synchronization when a record is created or modified in Odoo 14.

4. Testing the Function

1. Insert or update a record in the Odoo 14 database:

   sqlSalin kodeINSERT INTO account_account (id, code, name, account_type, company_id, currency_id)
   VALUES (1, '1001', 'Cash', 'asset', 1, NULL);
   

2. Check the Odoo 17 database to ensure the record has been synchronized.

Additional Notes

• Permissions: Ensure the dblink extension is enabled and the user has appropriate permissions on both databases.
• Error Handling: Add logging to capture and debug synchronization issues.
• Optimization: For large datasets, consider batch synchronization instead of triggers.

By implementing this function, you automate the synchronization of account.account master data between Odoo 14 and 17 while handling changes in account_type. This ensures data consistency and minimizes manual interventions.