Draft: Add SQLite backend support for ERP5 (ZSQLiteDA + CMFActivity)

Purpose

Make ERP5 be able to run with SQLite as a storage backend for both the SQL catalog and the activity queue, in addition to the existing MySQL backend

---

Context before the changes

ZSQLiteDA

ERP5's SQL layer was exclusively backed by MySQL through ZMySQLDA. There was no Zope database adapter for SQLite. All catalog queries, activity queue tables and connection management assumed MySQL-specific SQL syntax, type handling, and transaction semantics.

CMFActivity / ActivityTool

The activity queue had three concrete MySQL-specific implementations: SQLBase.py, SQLDict.py, SQLJoblib.py. The backend was hardwired with no abstraction layer.

---

Solution

ZSQLiteDA

A new ZSQLiteDA Zope product is introduced, implementing the same interface as ZMySQLDA (same class names: Connection, DB, DeferredConnection, ...; same module layout: db.py, DA.py).

ZSQLDA proxy product

A new Products.ZSQLDA product is introduced as a runtime-switchable facade over either ZMySQLDA or ZSQLiteDA. It exposes two sub-proxy modules:

• Products.ZSQLDA.db — proxies the chosen backend's db module
• Products.ZSQLDA.DA — proxies the chosen backend's DA module

Consumers import from Products.ZSQLDA.* and never need to know which backend is active.

CMFActivity backend proxy pattern

SQLBase.py, SQLDict.py, and SQLJoblib.py are converted into backend-agnostic proxy modules. Each exposes a configure(backend_path) function that loads the appropriate backend module and copies all its public attributes onto itself.

The MySQL and SQLite implementations are moved into parallel subpackages:

• CMFActivity/Activity/MySQL/SQLBase.py, SQLDict.py, SQLJoblib.py (was MySQLBase.py, etc.)
• CMFActivity/Activity/SQLite/SQLBase.py, SQLDict.py, SQLJoblib.py (was SQLiteBase.py, etc.)

The shared Queue.py and SQLQueue.py stay at the Activity/ level.

Final directory layout

  product/
  ├── ZMySQLDA/              ← existing MySQL backend (UNTOUCHED)
  │   ├── init.py
  │   ├── db.py
  │   └── DA.py
  ├── ZSQLiteDA/             ← new SQLite backend
  │   ├── init.py
  │   ├── db.py
  │   └── DA.py
  ├── ZSQLDA/                ← proxy facade (consumers import this)
  │   ├── init.py        ← orchestrator + _install helper
  │   ├── db/init.py     ← 2-line proxy: _install(name)
  │   └── DA/init.py     ← 2-line proxy: _install(name)
  ├── CMFActivity/
  │   ├── ActivityConnection.py
  │   ├── ActivityTool.py
  │   └── Activity/
  │       ├── init.py           ← orchestrator + _install helper
  │       ├── Queue.py              ← shared
  │       ├── SQLQueue.py           ← shared real class (no backend variant)
  │       ├── SQLBase.py            ← 2-line proxy
  │       ├── SQLDict.py            ← 2-line proxy
  │       ├── SQLJoblib.py          ← 2-line proxy
  │       ├── MySQL/
  │       │   ├── init.py
  │       │   ├── SQLBase.py        ← MySQL impl
  │       │   ├── SQLDict.py
  │       │   └── SQLJoblib.py
  │       └── SQLite/
  │           ├── init.py
  │           ├── SQLBase.py        ← SQLite impl
  │           ├── SQLDict.py
  │           └── SQLJoblib.py
  └── ERP5/
      └── ERP5Site.py        ← STORAGE_BACKENDS + monkey-patched initialize()

---

How it works

---

#### Idea1： The proxy mechanism : Abandon， This require to change the order of import, the import from Activity/ZSQLDA is only available until initialise of ERP5Site is called, it's not easy to maintenain this order and easy to break

A proxy module is a Python module that's empty at Python import time and gets populated at runtime by configure(backend_path). The pattern lives in two places (ZSQLDA/__init__.py and CMFActivity/Activity/__init__.py), each owning an _install() helper:

def _install(module_name):
    def configure(backend_path):
        backend = importlib.import_module(backend_path)
        m = sys.modules[module_name]
        for k, v in vars(backend).items():
            if not k.startswith('_'):
                setattr(m, k, v)
    sys.modules[module_name].configure = configure

Each sub-proxy file is a 2-liner that registers itself:

# e.g. CMFActivity/Activity/SQLBase.py
from . import _install
_install(__name__)

After configure(...) runs, the proxy module is equivalent to the backend module — same classes, same constants. Consumers like from Products.ZSQLDA.DA import Connection work without knowing which backend is active.

The decision flow at startup

ERP5Site.py defines a single source of truth:

STORAGE_BACKENDS = {
  'erp5_mysql_innodb_catalog': {
    'zsqlda':   'Products.ZMySQLDA',
    'activity': 'Products.CMFActivity.Activity.MySQL',
  },
  'erp5_sqlite_catalog': {
    'zsqlda':   'Products.ZSQLiteDA',
    'activity': 'Products.CMFActivity.Activity.SQLite',
  },
}

At process startup, before any request is served, Zope calls AppInitializer.initialize(). ERP5Site.py monkey-patches this hook. The replacement function looks for the storage choice in three sources, in priority order:

1. Existing ZODB site — for obj_id in app.objectIds(ERP5Site.meta_type), force-load the site's pickle with site._p_activate(), read site.dict['erp5_catalog_storage'], configure, then call ZODB.Connection.resetCaches() to discard any Broken ghosts created during the early activation.
2. zope.conf — for fresh installs.
3. os.environ['erp5_catalog_storage'] — test path; defaults to erp5_mysql_innodb_catalog.

Then it calls:

def configureAndInitialize(storage):
    backends = STORAGE_BACKENDS[storage]
    Products.ZSQLDA.configure(backends['zsqlda'])         # 'Products.ZMySQLDA'
    CMFActivity.Activity.configure(backends['activity'])  # 'Products.CMFActivity.Activity.MySQL'
    self.initialize()                                     # AppInitializer.initialize

┌─────────────────────────────────────────┬───────────────────────┬────────────────────────────┐
│                 Source                  │       Authority       │      When applicable       │
├─────────────────────────────────────────┼───────────────────────┼────────────────────────────┤
│ ZODB site's stored erp5_catalog_storage │ highest               │ existing site, any restart │
├─────────────────────────────────────────┼───────────────────────┼────────────────────────────┤
│ zope.conf <product-config initsite>     │ medium                │ fresh install with config  │
├─────────────────────────────────────────┼───────────────────────┼────────────────────────────┤
│ os.environ['erp5_catalog_storage']      │ lowest, default MySQL │ tests                      │
└─────────────────────────────────────────┴───────────────────────┴────────────────────────────┘

ZSQLDA layer

Products.ZSQLDA.configure('Products.ZMySQLDA') iterates the sub-proxies (db, DA) and, for each, calls .configure(Products.ZMySQLDA.<sub>).

Activity layer

CMFActivity.Activity.configure('Products.CMFActivity.Activity.MySQL') applies the same attribute-copy pattern to SQLBase, SQLDict, and SQLJoblib. Both facades use an identical loop:

for sub in _SUB_PROXIES:
    importlib.import_module(__name__ + '.' + sub).configure(backend + '.' + sub)

After populating the proxies, it instantiates one object per queue type and writes them into ActivityTool.activity_dict:

ActivityTool.activity_dict = {
    k: getattr(v, k)()
    for k, v in {'SQLDict': SQLDict, 'SQLQueue': SQLQueue, 'SQLJoblib': SQLJoblib}.items()
}

Concession: deferred imports

Proxy modules are empty at Python import time. Zope's product loader imports many modules before ERP5Site.initialize() runs, so any module-level from Products.ZSQLDA.DA import Connection in a consumer would fail with ImportError: cannot import name 'Connection'.

we need to deferre those imports into function bodies — they now run after configure() has populated the proxies. Six files have one-line deferrals:

• CMFActivity/ActivityTool.py — from .ActivityConnection import ActivityConnection
• CMFActivity/tests/testCMFActivity.py (twice)
• DeadlockDebugger/dumper.py
• ERP5Catalog/CatalogTool.py — from Products.ZSQLDA.DA import DeferredConnection
• ERP5Type/patches/LongRequestLogger_dumper.py
• ERP5Type/tests/ERP5TypeTestCase.py (twice)
• ERP5Type/tests/utils.py — now uses Products.ZSQLDA.configured(STORAGE_BACKENDS[storage]['zsqlda'])

The one non-deferred module-level import that works is CMFActivity/ActivityConnection.py's from Products.ZSQLDA.DA import Connection, DB — fine because ActivityConnection.py is itself lazily imported from CMFActivity/init.py's initialize() function, which runs after configure().

Potential issues

1. _p_activate() + resetCaches() is unusual

When reading erp5_catalog_storage from an existing site, ZODB loads the site's pickle. References to sub-objects (like portal_activities) become ghosts whose pickled class is erp5.portal_type.Activity Tool. But erp5.portal_type doesn't exist yet — it's created by install_products() later inside self.initialize(). ZODB substitutes its Broken class for unresolvable ghosts.

After self.initialize() finishes, erp5.portal_type works — but the cached Broken ghost remains. resetCaches() schedules a cache wipe on the next ZODB transaction, so the next access reloads portal_activities fresh with the correct class.

2. SQLBase ordering in Activity is load-bearing

_SUB_PROXIES = ('SQLBase', 'SQLDict', 'SQLJoblib') — order matters. A reorder breaks startup because SQLQueue.py reads from SQLBase at module load. Flagged in a comment.

3. Code duplication between MySQL/ and SQLite/

Each backend reimplements ~1700 lines (sum across three files). They share ~80% structure and differ in SQL strings and dialect-specific methods. The duplication was deliberately chosen over inheritance to keep MySQL byte-identical. Cost: parallel maintenance with no mechanical enforcement.

What's robust

• MySQL path is byte-identical to pre-refactor. Existing MySQL deployments cannot regress from the proxy machinery itself.
• Adding a third backend is mechanical: create Products.ZPostgresDA, create CMFActivity/Activity/Postgres/SQLBase.py + SQLDict.py + SQLJoblib.py, add one row to STORAGE_BACKENDS. No changes to existing files.
• Symmetry: ZSQLDA and Activity facades use the same pattern (full path prefix → join sub-proxy name). One pattern to learn.
• Single decision point: ERP5Site.initialize() is the only place that decides storage. Every other code path is agnostic.

---

---

Idea2：XXXXX

---