Multi-Database Support

Purpose and Scope

This document covers Maho's multi-database architecture, which provides unified database abstraction across MySQL, PostgreSQL, and SQLite through Doctrine DBAL 4.4. The system enables developers to write database-agnostic code while supporting engine-specific optimizations through specialized adapters and configuration files.

For information about database migration scripts, see 10.6 For Model and Resource Model patterns, see 4.2 For configuration system details, see 3.3

Architecture Overview

Maho has replaced legacy database components with a modern abstraction layer. The architecture is centered around Maho\Db\Adapter\AdapterInterface, which defines the contract for all database interactions lib/Maho/Db/Adapter/AbstractPdoAdapter.php30-31

Code Entity Space to System Name Mapping

The following diagram maps the high-level system concepts to the specific classes and interfaces in the Maho codebase.

Database Abstraction Layer Mapping

Sources: lib/Maho/Db/Adapter/AbstractPdoAdapter.php30-31 lib/Maho/Db/Select.php21 lib/Maho/Db/Expr.php19

Implementation Hierarchy

Maho utilizes a driver-based approach where engine-specific logic is encapsulated in dedicated PDO adapters inheriting from AbstractPdoAdapter.

Database Engine	Adapter Class	Statement Class
MySQL	Maho\Db\Adapter\Pdo\Mysql	Maho\Db\Statement\Pdo\Mysql
PostgreSQL	Maho\Db\Adapter\Pdo\Pgsql	Maho\Db\Statement\Pdo\Pgsql
SQLite	Maho\Db\Adapter\Pdo\Sqlite	Maho\Db\Statement\Pdo\Sqlite

Sources: lib/Maho/Db/Adapter/Pdo/Mysql.php20-34 lib/Maho/Db/Adapter/Pdo/Pgsql.php21-30 lib/Maho/Db/Adapter/Pdo/Sqlite.php19-28

Supported Database Engines

MySQL (Default)

The primary engine for Maho. It uses backticks (`) for identifier quoting and supports specific initialization like setting SQL_MODE to empty to maintain compatibility with legacy queries.

• Identifier Quote: ` lib/Maho/Db/Adapter/Pdo/Mysql.php123-126
• Init Connection: Executes SET SQL_MODE='' and SET time_zone = '+00:00' lib/Maho/Db/Adapter/Pdo/Mysql.php132-137
• DDL Mapping: Maps standard DDL types to MySQL equivalents (e.g., TYPE_INTEGER to int). lib/Maho/Db/Adapter/Pdo/Mysql.php44-60
• Connection Logic: Handles Unix sockets, IPv4, and IPv6 host parsing via _getHostInfo. lib/Maho/Db/Adapter/Pdo/Mysql.php69-104

PostgreSQL

PostgreSQL support includes automatic handling of client_encoding to UTF8 and enabling standard_conforming_strings.

• Identifier Quote: " lib/Maho/Db/Adapter/Pdo/Pgsql.php99-105
• Init Connection: Sets encoding, string behavior, and timezone to UTC. lib/Maho/Db/Adapter/Pdo/Pgsql.php111-118
• Blob Handling: Maps TYPE_BLOB to bytea. lib/Maho/Db/Adapter/Pdo/Pgsql.php69
• Resilience: Implements raw_query with a retry mechanism if the connection is unexpectedly closed. lib/Maho/Db/Adapter/Pdo/Pgsql.php197-204

SQLite

Optimized for development and testing. It implements several performance-tuning PRAGMA statements upon connection and registers custom PHP functions to emulate SQL features missing in SQLite.

• Custom Functions: Registers REGEXP (using preg_match), GREATEST (using max()), and LEAST (using min()) via _registerCustomFunctions to provide parity with MySQL lib/Maho/Db/Adapter/Pdo/Sqlite.php124-158
• Performance Tuning: Enables Write-Ahead Logging (WAL), memory-mapped I/O (256MB), and foreign key constraints via _initConnection lib/Maho/Db/Adapter/Pdo/Sqlite.php97-119
• Typing: Uses SQLite's dynamic typing, mapping most numeric and text types to INTEGER, REAL, NUMERIC, or TEXT. lib/Maho/Db/Adapter/Pdo/Sqlite.php41-57

Query Building and Execution

Maho uses Maho\Db\Select as a query builder wrapping Doctrine's QueryBuilder lib/Maho/Db/Select.php21 The adapter is responsible for providing the underlying connection.

Select Query Data Flow

Sources: lib/Maho/Db/Select.php21 lib/Maho/Db/Adapter/AbstractPdoAdapter.php213-217

Cross-Engine Consistency Patterns

Maho enforces cross-engine parity through specific data type migrations and behavioral changes in resource models.

Timestamp to Datetime Conversion

Maho converts physical TIMESTAMP columns to DATETIME on MySQL to ensure consistency with PostgreSQL and SQLite, which do not distinguish between the two types in the same way. This also avoids MySQL's implicit timezone conversions, as Maho forces UTC at the connection level app/code/core/Mage/Core/sql/maho_setup/maho-26.5.0.php19-42

PHP-Side Timestamp Management

To achieve cross-engine parity, the MySQL-specific ON UPDATE CURRENT_TIMESTAMP clause is being phased out in favor of explicit management in PHP via _beforeSave() methods app/code/core/Mage/Catalog/sql/maho_setup/maho-26.5.0.php17-25

Feature	MySQL Behavior	PgSQL/SQLite Behavior	Maho Solution
TIMESTAMP	TZ-aware, 1970-2038	Same as Datetime	Convert to DATETIME app/code/core/Mage/Core/sql/maho_setup/maho-26.5.0.php42-79
ON UPDATE	Native support	Not supported	Handle in PHP _beforeSave app/code/core/Mage/Catalog/sql/maho_setup/maho-26.5.0.php17-25

Transaction Management

The AdapterInterface and AbstractPdoAdapter provide platform-agnostic transaction control.

• Methods: beginTransaction(), commit(), rollBack(). lib/Maho/Db/Adapter/AbstractPdoAdapter.php262-264
• Nesting: Supports transaction nesting levels via $_transactionLevel. lib/Maho/Db/Adapter/AbstractPdoAdapter.php94
• Safety: DDL routines are identified via $_ddlRoutines to manage cache and handle engine-specific transaction behaviors. lib/Maho/Db/Adapter/AbstractPdoAdapter.php159-160

CLI Database Tools

Maho provides CLI commands for direct database interaction that support all three engines.

• db:connect: Opens the native CLI client (mysql, psql, or sqlite3) lib/MahoCLI/Commands/DBConnect.php22-25
• db:query: Executes a raw SQL query via the CLI lib/MahoCLI/Commands/DBQuery.php22-25

Sources:

• lib/Maho/Db/Adapter/AbstractPdoAdapter.php
• lib/Maho/Db/Adapter/Pdo/Mysql.php
• lib/Maho/Db/Adapter/Pdo/Pgsql.php
• lib/Maho/Db/Adapter/Pdo/Sqlite.php
• app/code/core/Mage/Core/sql/maho_setup/maho-26.5.0.php
• app/code/core/Mage/Catalog/sql/maho_setup/maho-26.5.0.php
• lib/MahoCLI/Commands/DBConnect.php
• lib/MahoCLI/Commands/DBQuery.php