Draft: Add SQLite backend support for ERP5 (ZSQLDA + CMFActivity + erp5_sqlite_catalog)
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