Skip to content

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

Xiaowu Zhang requested to merge xiaowu.zhang/erp5:feat/modulize into master

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

Final directory layout

  product/ZSQLDA/ — the pluggable DB adapter

  This is the old ZMySQLDA product, renamed and restructured into shared base + per-backend subpackages.

  product/ZSQLDA/
  ├── __init__.py            ★ rewritten — initialize() registers BOTH backends
  ├── DABase.py              ★ NEW — backend-agnostic Connection, conn pool, browser UI
  │                                   (defines factory() hook; subclasses fill it in)
  ├── table_info.dtml          shared ZMI table-browser template
  ├── icons/                   shared icon set → published as misc_/ZSQLDA/<key>
  │   ├── bin.gif  date.gif  datetime.gif ......
  │
  ├── MySQL/                  ◆ moved from old ZMySQLDA/ (MySQLdb-based)
  │   ├── __init__.py
  │   ├── DA.py              ◆ Connection(DABase.Connection); factory()→db.DB
  │   │                          + manage_addZMySQLConnection
  │   ├── db.py                MySQLdb low-level driver (moved verbatim)
  │   ├── connectionAdd.dtml   ZMI "add connection" form
  │   ├── connectionEdit.dtml
  │   ├── browse.dtml
  │   ├── CHANGES.txt  DEPENDENCIES.txt  README.txt  VERSION.txt
  │
  ├── SQLite/                ★ NEW — stdlib sqlite3 backend
  │   ├── __init__.py
  │   ├── DA.py             ★ Connection(DABase.Connection); factory()→db.DB
  │   │                          + manage_addZSQLiteConnection
  │   ├── db.py             ★ sqlite3 driver: query()/string_literal()/
  │   │                          tables()/columns()/TM txn + MySQL-compat shims
  │   ├── connectionAdd.dtml
  │   ├── connectionEdit.dtml
  │   └── browse.dtml
  │
  |.......

  ★ new/rewritten   ◆ moved from ZMySQLDA. Note the deleted product/ZMySQLDA/DA.py — its Connection/DB glue is now split between DABase.py and each backend's DA.py.

  product/CMFActivity/ — the activity-queue engine

  Same pattern: the three queue implementations are split into per-backend subpackages under Activity/, with shared base classes kept at the Activity/ top level.

  product/CMFActivity/
  ├── __init__.py            ★ registers MySQL + SQLite ActivityConnection classes
  ├── ActivityConnection.py  ★ rewritten — MySQL/SQLiteActivityConnection + *DB
  │                                   (each subclasses the matching ZSQLDA connection)
  ├── ActivityTool.py        ★ activity_dict() sniffs connection class → picks backend;
  │                                   maybeMigrateConnectionClass() heals old/Broken conns
  │
  ├── Activity/
  │   ├── __init__.py
  │   ├── Queue.py             base Queue (unchanged)
  │   ├── SQLBase.py         ★ reduced to SHARED logic (MySQLdb / SQLLock removed)
  │   ├── SQLDict.py         ★ shared — SQL inlining replaced by _select* hooks
  │   ├── SQLQueue.py        ★ shared base
  │   ├── SQLJoblib.py       ★ shared base
  │   │
  │   ├── MySQL/             ★ NEW — MySQL-specific queue SQL
  │   │   ├── __init__.py
  │   │   ├── SQLBase.py        (GET_LOCK, getNow, UNION chunking, FOR UPDATE/SKIP LOCKED)
  │   │   ├── SQLDict.py
  │   │   ├── SQLQueue.py
  │   │   └── SQLJoblib.py
  │   │
  │   └── SQLite/            ★ NEW — SQLite-specific queue SQL
  │       ├── __init__.py
  │       ├── SQLBase.py
  │       ├── SQLDict.py
  │       ├── SQLQueue.py
  │       └── SQLJoblib.py
  │
  ├── dtml/
  │   ├── connectionAdd_mysql.dtml    ◆ renamed from connectionAdd.dtml
  │   ├── connectionAdd_sqlite.dtml   ★ NEW
  │   ├── ActiveProcess_add.dtml  explainActivityTool.dtml
  │   ├── manageActivities.dtml   manageActivitiesAdvanced.dtml  manageLoadBalancing.dtml
  │.......

TODO

  • [CMFActivity/MySQL&SQLite]
  • [ZSQLDA/MySQL&SQLite]
  • [erp5_mysql_innodb_catalog&erp5_sqlite_catalog]
Edited by Xiaowu Zhang

Merge request reports