Database Layer

Purpose and Scope

The Database Layer provides data persistence and retrieval mechanisms for all Maho entities. This layer abstracts database operations through a multi-tiered architecture consisting of models, resource models, collections, and a database abstraction layer powered by Doctrine DBAL 4.4.

Maho has modernized the database layer to support multiple database engines (MySQL, PostgreSQL, and SQLite) while maintaining the classic Model-Resource-Collection pattern.

This document covers the overall database architecture, model patterns, and data access mechanisms. For specific topics:

• Multi-database support (MySQL, PostgreSQL, SQLite) - see Multi-Database Support
• Detailed model/resource model patterns - see Models and Resource Models
• Entity-Attribute-Value architecture for flexible attributes - see EAV System
• Data indexing for performance optimization - see Indexing System

---

Database Architecture Overview

Maho's architecture separates business logic from data access. Models contain the logic, while Resource Models handle the actual SQL execution via a modernized abstraction layer.

Data Flow Diagram

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 lib/Maho/Db/Adapter/AbstractPdoAdapter.php30-65 app/code/core/Mage/Core/Controller/Response/Http.php20-25

---

Factory Pattern for Database Objects

Maho uses the factory pattern via the Mage static class to instantiate database-related objects. This allows for class rewrites and flexible dependency management via XML configuration.

Bridging Intent to Code

Key Factory Methods:

Method	Purpose	Returns
Mage::getModel()	New model instance	Mage_Core_Model_Abstract
Mage::getResourceModel()	New resource or collection	Mage_Core_Model_Resource_Db_Abstract
Mage::getSingleton()	Shared model instance	Mage_Core_Model_Abstract

Sources: app/code/core/Mage/Catalog/Model/Product.php1-20 app/code/core/Mage/Catalog/Model/Product/Image.php16-17

---

Database Abstraction and Multi-DB Support

Maho abstracts database interactions through Maho\Db\Adapter\AbstractPdoAdapter. This base class leverages Doctrine DBAL to provide a consistent interface across different SQL dialects.

Connection Adapters:

Class	Driver	Key Feature
Maho\Db\Adapter\Pdo\Mysql	pdo_mysql	Standard MySQL support using backtick (`) quotes lib/Maho/Db/Adapter/Pdo/Mysql.php122-125
Maho\Db\Adapter\Pdo\Pgsql	pdo_pgsql	PostgreSQL support with bytea for blobs and double quote (") identifiers lib/Maho/Db/Adapter/Pdo/Pgsql.php68-104
Maho\Db\Adapter\Pdo\Sqlite	pdo_sqlite	SQLite support with custom REGEXP, GREATEST, and LEAST PHP-based functions lib/Maho/Db/Adapter/Pdo/Sqlite.php123-157

DDL Type Mapping: Adapters define how Maho's internal DDL types map to platform-specific types. For example, TYPE_DATETIME maps to datetime in MySQL lib/Maho/Db/Adapter/Pdo/Mysql.php55 timestamp in PostgreSQL lib/Maho/Db/Adapter/Pdo/Pgsql.php66 and TEXT in SQLite lib/Maho/Db/Adapter/Pdo/Sqlite.php51

Sources: lib/Maho/Db/Adapter/Pdo/Mysql.php44-60 lib/Maho/Db/Adapter/Pdo/Pgsql.php45-71 lib/Maho/Db/Adapter/Pdo/Sqlite.php41-57 lib/Maho/Db/Adapter/AbstractPdoAdapter.php210-227

---

Schema and Data Migrations

Maho uses setup scripts to manage database schema. Modernization efforts in Maho focus on cross-engine parity, such as converting TIMESTAMP columns to DATETIME to ensure consistent behavior across MySQL, PostgreSQL, and SQLite.

Example: Cross-Engine Consistency In app/code/core/Mage/Core/sql/maho_setup/maho-26.5.0.php, physical TIMESTAMP columns are converted to DATETIME for MySQL to match the behavior of other engines and unblock surgical column modifications: app/code/core/Mage/Core/sql/maho_setup/maho-26.5.0.php19-42

Example: Explicit Value Management Maho is moving away from database-level ON UPDATE CURRENT_TIMESTAMP clauses, instead managing these values in PHP via _beforeSave() for cross-engine parity: app/code/core/Mage/Core/sql/maho_setup/maho-26.5.0.php26-36 app/code/core/Mage/Log/sql/maho_setup/maho-26.5.0.php15-20

Sources: app/code/core/Mage/Core/sql/maho_setup/maho-26.5.0.php42-79 app/code/core/Maho/CatalogLinkRule/sql/maho_setup/maho-26.5.0.php15-25

---

Query Building with Maho\Db\Select

Maho provides a query builder, Maho\Db\Select, which wraps the database adapter to provide a fluent interface for constructing SQL queries. It handles parts like DISTINCT, COLUMNS, FROM, and various JOIN types.

Constant	SQL Part
self::DISTINCT	SELECT DISTINCT lib/Maho/Db/Select.php102-110
self::FROM	FROM table_name lib/Maho/Db/Select.php115-127
self::INNER_JOIN	INNER JOIN lib/Maho/Db/Select.php140-143
self::WHERE	WHERE condition lib/Maho/Db/Select.php24

Sources: lib/Maho/Db/Select.php17-53 lib/Maho/Db/Select.php65-78

---

Related Subsystems

• Multi-Database Support — Detail MySQL, PostgreSQL, and SQLite support, including SQL conversion and platform-specific handling
• Models and Resource Models — Explain model/resource model separation, collections, and database abstraction
• EAV System — Deep dive into Entity-Attribute-Value architecture, attribute types, and management
• Indexing System — Cover indexer architecture, partial indexing, and performance optimization