zms.zmssqldb

zmssqldb.py - Relational Database Content Type for ZMS

The ZMSSqlDb class provides a comprehensive abstraction layer for managing relational database-backed content in ZMS. It enables direct database access through configured database adapters (supporting both traditional ZRDB and SQLAlchemy-based connections), executes parameterized SQL queries with proper escaping, and marshals result sets as Python objects for use in ZMS templates and Python scripts.

Key Capabilities:

• Database Connectivity: Maintains connections to multiple database backends (MySQL, PostgreSQL, Oracle, SQLite) via Zope database adapters (DA).
• Schema Reflection: Automatically discovers and introspects database schemas, creating entity models from table and column metadata. Supports custom entity definitions stored in persistent XML models.
• Query Execution: Provides high-level query methods (query(), execute()) with automatic parameter substitution, type-based SQL quoting, and result normalization into column/record dictionaries.
• CRUD Operations: Implements record-set management through recordSet_Insert(), recordSet_Update(), recordSet_Delete() with support for:
  • Auto-incrementing and timestamp columns
  • Foreign-key references with optional lazy loading
  • Single and multi-select relationships
  • Intersection table synchronization
  • Blob (file/image) storage with filesystem persistence
• Filtering and Sorting: Builds dynamic WHERE and ORDER BY clauses from user input, with table-level filter expressions and per-column operators (LIKE, =, NULL, etc.).
• Configuration UI: Provides ZMI forms to:
  • Select and configure database connections
  • Define custom entity interfaces and column stereotypes
  • Set access control rules (insert, update, delete, select)
  • Import/export model definitions as XML
• Model Persistence: Stores schema customizations (labels, stereotypes, validation) in a persistent model_xml attribute, allowing schema extensions beyond physical database structure.

Integration Points:

• Used by ZMS content types to expose database records as queryable content
• Integrates with ZMS authentication/authorization framework
• Supports inter-ZMS communication via HTTP blob transfer
• Extends zmscustom.ZMSCustom for full ZMS content management features

License: GNU General Public License v2 or later, Organization: ZMS Publishing