18483785918

Committed 14 Oct 2025 02:39AM UTC coverage: 84.208% (+3.7%) from 80.483%

Build # 18483785918

Build Type

Pull #42

github

Committed by

web-flow

Commit Message

Merge 32f65bcdb into 025025bcf

Pull Request Pull Request #42: Implement comprehensive join optimization system to solve N+1 query problem

Run Details

847 of 962 new or added lines in 19 files covered. (88.05%)

60 existing lines in 2 files now uncovered.

1637 of 1944 relevant lines covered (84.21%)

15.67 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

37.78

/src/TableMaker.php

<?php

// phpcs:disable PSR2.Methods.MethodDeclaration.Underscore

namespace Anorm;

class TableMaker
{
    public static function fix(\Exception $exception, DataMapper $mapper, $model = null)
    {
        // TODO We could create this from an Anorm factory / container
        $maker = new TableMaker($exception, $mapper, $model);
        return $maker->_fix();
    }

    /** @var \Exception The exception that requires the database schema to be fixed. */
    public $exception;

    /** @var DataMapper The DataMapper */
    private $mapper;

    /** @var Model|null An optional model instance */
    private $model;

    public function __construct(\Exception $exception, DataMapper $mapper, $model)
    {
        $this->exception = $exception;
        $this->mapper = $mapper;
        $this->model = $model;
    }

    private function _fix()
    {
        switch ($this->exception->getCode()) {
            case '42S02': // table not found
                $this->createTable();
                break;
            case '42S22': // column not found
                $this->createColumn();
                break;
            case '23000': // integrity constraint violation (foreign key)
                $this->handleForeignKeyConstraint();
                break;
            case 'HY000': // general error (can include foreign key issues)
                if (strpos($this->exception->getMessage(), 'foreign key constraint') !== false) {
                    $this->handleForeignKeyConstraint();
                } else {
                    throw $this->exception;
                }
                break;
        }
    }

    private function createTable()
    {
        // Regex the message to get the name of the table
        $matches = [];
        if (!\preg_match("/'([^\.']*)\.([^\.']*)'/", $this->exception->getMessage(), $matches)) {
            throw new \Exception('Anorm: Could not parse PDOException', 0, $this->exception);
        }
        $tableName = $matches[2];
        // Create the table with an auto increment id as primary key.
        // Review: Should we also try and create all the columns we can now,
        // or wait until possibly later when we might have better data
        // to hint the type?
        // Current design choice is to wait until later even if it means
        // a highly iterative, multiple exception approach on the common
        // first write case.
        $sql = "CREATE TABLE `$tableName`(
            id INT(11) AUTO_INCREMENT PRIMARY KEY
        )";
        $this->mapper->pdo->query($sql);

        // After creating the table, try to create foreign key constraints
        // if this model has relationships defined
        $this->createForeignKeyConstraintsFromModel();
    }

    private function createColumn()
    {
        // Regex the message to get the name of the table
        $matches = [];
        if (!\preg_match("/column '([^\.']*)'/", $this->exception->getMessage(), $matches)) {
            throw new \Exception('Anorm: Could not parse PDOException', 0, $this->exception);
        }
        $columnName = $matches[1];
        // Add the column.
        // TODO Have a go at figuring out the type if the model is available.
        $sampleData = null;
        if ($this->model) {
            // See if we can reverse map the
            $invertMap = array_flip($this->mapper->map);
            $property = $invertMap[$columnName];
            $sampleData = $this->model->$property;
        }
        $columnFn = Anorm::$columnFn; // Redundant, but can't do this Anorm::$columnFn(...)
        $columnDefinition = $columnFn($columnName, $sampleData);
        $sql = "ALTER TABLE `" . $this->mapper->table . "` ADD $columnName $columnDefinition";
        $this->mapper->pdo->query($sql);
    }

    /**
     * Handle foreign key constraint violations by creating missing foreign keys
     */
    private function handleForeignKeyConstraint()
    {
        // Check if this is a missing foreign key constraint
        if (strpos($this->exception->getMessage(), 'Cannot add or update a child row') !== false) {
            $this->createMissingForeignKeyConstraints();
        } else {
            // For other foreign key issues, try to create the constraint
            $this->createForeignKeyConstraintsFromModel();
        }
    }

    /**
     * Create missing foreign key constraints based on relationship definitions
     */
    private function createMissingForeignKeyConstraints()
    {
        if (!$this->model || !property_exists($this->model, '_relationshipManager')) {
            return;
        }

        $relationshipManager = $this->model->_relationshipManager;
        if (!$relationshipManager) {
            return;
        }

        // Get all relationships defined in the model
        $relationships = $relationshipManager->getRelationships();

        foreach ($relationships as $relationship) {
            $this->createForeignKeyFromRelationship($relationship);
        }
    }

    /**
     * Create foreign key constraints from model relationship definitions
     */
    private function createForeignKeyConstraintsFromModel()
    {
        if (!$this->model || !property_exists($this->model, '_relationshipManager')) {
            return;
        }

        $relationshipManager = $this->model->_relationshipManager;
        if (!$relationshipManager) {
            return;
        }

        // Get all relationships and create foreign keys for them
        $relationships = $relationshipManager->getRelationships();

        foreach ($relationships as $relationship) {
            $this->createForeignKeyFromRelationship($relationship);
        }
    }

    /**
     * Create a foreign key constraint from a relationship definition
     */
    private function createForeignKeyFromRelationship($relationship)
    {
        $type = $relationship->getType();

        if ($type === 'ManyHasOne') {
            // For belongsTo relationships, create foreign key on current table
            $this->createForeignKey(
                $this->mapper->table,
                $relationship->getForeignKey(),
                $this->getTableNameFromModelClass($relationship->getRelatedModelClass()),
                $relationship->getPrimaryKey(),
                $relationship->getConstraintOptions()
            );
        } elseif ($type === 'OneHasMany') {
            // For hasMany relationships, create foreign key on related table
            $this->createForeignKey(
                $this->getTableNameFromModelClass($relationship->getRelatedModelClass()),
                $relationship->getForeignKey(),
                $this->mapper->table,
                $relationship->getPrimaryKey(),
                $relationship->getConstraintOptions()
            );
        }
        // ManyHasMany relationships don't need foreign keys on main tables
        // They use join tables which should be handled separately
    }

    /**
     * Create a foreign key constraint
     */
    private function createForeignKey($table, $column, $referencedTable, $referencedColumn, $options = [])
    {
        $constraintName = $options['constraint_name'] ?? "fk_{$table}_{$column}";
        $onDelete = $options['on_delete'] ?? 'RESTRICT';
        $onUpdate = $options['on_update'] ?? 'CASCADE';

        // Check if foreign key already exists
        if ($this->foreignKeyExists($table, $constraintName)) {
            return;
        }

        // Ensure the referenced table exists
        $this->ensureTableExists($referencedTable);

        // Ensure the column exists in the source table
        $this->ensureColumnExists($table, $column);

        // Ensure the referenced column exists in the target table
        $this->ensureColumnExists($referencedTable, $referencedColumn);

        $sql = "ALTER TABLE `{$table}`
                ADD CONSTRAINT `{$constraintName}`
                FOREIGN KEY (`{$column}`)
                REFERENCES `{$referencedTable}`(`{$referencedColumn}`)
                ON DELETE {$onDelete}
                ON UPDATE {$onUpdate}";

        try {
            $this->mapper->pdo->query($sql);
        } catch (\PDOException $e) {
            // If foreign key creation fails, it might be because the constraint already exists
            // or there are data integrity issues. Log and continue.
            error_log("Anorm: Failed to create foreign key constraint: " . $e->getMessage());
        }
    }

    /**
     * Check if a foreign key constraint exists
     */
    private function foreignKeyExists($table, $constraintName)
    {
        $sql = "SELECT COUNT(*) as count
                FROM information_schema.TABLE_CONSTRAINTS
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = ?
                AND CONSTRAINT_NAME = ?
                AND CONSTRAINT_TYPE = 'FOREIGN KEY'";

        $stmt = $this->mapper->pdo->prepare($sql);
        $stmt->execute([$table, $constraintName]);
        $result = $stmt->fetch(\PDO::FETCH_ASSOC);

        return $result['count'] > 0;
    }

    /**
     * Ensure a table exists, create it if it doesn't
     */
    private function ensureTableExists($tableName)
    {
        $sql = "CREATE TABLE IF NOT EXISTS `{$tableName}` (
            id INT(11) AUTO_INCREMENT PRIMARY KEY
        )";
        $this->mapper->pdo->query($sql);
    }

    /**
     * Ensure a column exists in a table, create it if it doesn't
     */
    private function ensureColumnExists($tableName, $columnName)
    {
        // Check if column exists
        $sql = "SELECT COUNT(*) as count
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = ?
                AND COLUMN_NAME = ?";

        $stmt = $this->mapper->pdo->prepare($sql);
        $stmt->execute([$tableName, $columnName]);
        $result = $stmt->fetch(\PDO::FETCH_ASSOC);

        if ($result['count'] == 0) {
            // Column doesn't exist, create it
            $columnDefinition = $this->getColumnDefinitionForForeignKey($columnName);
            $sql = "ALTER TABLE `{$tableName}` ADD `{$columnName}` {$columnDefinition}";
            $this->mapper->pdo->query($sql);
        }
    }

    /**
     * Get appropriate column definition for foreign key columns
     */
    private function getColumnDefinitionForForeignKey($columnName)
    {
        // Foreign key columns are typically INT(11) to match primary keys
        return "INT(11) NULL";
    }

    /**
     * Get table name from model class name
     */
    private function getTableNameFromModelClass($modelClass)
    {
        // Remove namespace and 'Model' suffix, convert to snake_case
        $className = basename(str_replace('\\', '/', $modelClass));
        $className = str_replace('Model', '', $className);

        // Convert CamelCase to snake_case and pluralize
        $tableName = strtolower(preg_replace('/([a-z])([A-Z])/', '$1_$2', $className));

        // Simple pluralization (add 's' if doesn't end with 's')
        if (substr($tableName, -1) !== 's') {
            $tableName .= 's';
        }

        return $tableName;
    }

    public static function columnDefinition($columnName, $sampleData)
    {
        if ($sampleData) {
            if (\is_numeric($sampleData)) {
                if (\is_integer($sampleData)) {
                    return "INT(11) NULL";
                }
                if (\is_float($sampleData)) {
                    return "DOUBLE NULL";
                }
            }
            if (is_object($sampleData) && get_class($sampleData) == 'Moment\Moment') {
                return "DATETIME NULL";
            }
            if (is_string($sampleData)) {
                if (preg_match('/(\d{4})-(\d{2})-(\d{2})/', $sampleData) === 1) {
                    return "DATETIME NULL";
                }
                if (strlen($sampleData) > 256) {
                    return "TEXT";
                }
                if (strlen($sampleData) > 128) {
                    return "VARCHAR(256)";
                }
            }
        }
        return 'VARCHAR(128)';
    }
}

1	<?php
2
3	// phpcs:disable PSR2.Methods.MethodDeclaration.Underscore
4
5	namespace Anorm;
6
7	class TableMaker
8	{
9	public static function fix(\Exception $exception, DataMapper $mapper, $model = null)
10	{
11	// TODO We could create this from an Anorm factory / container
12	$maker = new TableMaker($exception, $mapper, $model);	8✔
13	return $maker->_fix();	8✔
14	}
15
16	/** @var \Exception The exception that requires the database schema to be fixed. */
17	public $exception;
18
19	/** @var DataMapper The DataMapper */
20	private $mapper;
21
22	/** @var Model\|null An optional model instance */
23	private $model;
24
25	public function __construct(\Exception $exception, DataMapper $mapper, $model)
26	{
27	$this->exception = $exception;	8✔
28	$this->mapper = $mapper;	8✔
29	$this->model = $model;	8✔
30	}
31
32	private function _fix()
33	{
34	switch ($this->exception->getCode()) {	8✔
35	case '42S02': // table not found	8✔
36	$this->createTable();	3✔
37	break;	2✔
38	case '42S22': // column not found	7✔
39	$this->createColumn();	7✔
40	break;	6✔
41	case '23000': // integrity constraint violation (foreign key)	×
42	$this->handleForeignKeyConstraint();	×
43	break;	×
44	case 'HY000': // general error (can include foreign key issues)	×
45	if (strpos($this->exception->getMessage(), 'foreign key constraint') !== false) {	×
46	$this->handleForeignKeyConstraint();	×
47	} else {
48	throw $this->exception;	×
49	}
50	break;	×
51	}
52	}
53
54	private function createTable()
55	{
56	// Regex the message to get the name of the table
57	$matches = [];	3✔
58	if (!\preg_match("/'([^\.'])\.([^\.'])'/", $this->exception->getMessage(), $matches)) {	3✔
59	throw new \Exception('Anorm: Could not parse PDOException', 0, $this->exception);	1✔
60	}
61	$tableName = $matches[2];	2✔
62	// Create the table with an auto increment id as primary key.
63	// Review: Should we also try and create all the columns we can now,
64	// or wait until possibly later when we might have better data
65	// to hint the type?
66	// Current design choice is to wait until later even if it means
67	// a highly iterative, multiple exception approach on the common
68	// first write case.
69	$sql = "CREATE TABLE `$tableName`(	2✔
70	id INT(11) AUTO_INCREMENT PRIMARY KEY
71	)";	2✔
72	$this->mapper->pdo->query($sql);	2✔
73
74	// After creating the table, try to create foreign key constraints
75	// if this model has relationships defined
76	$this->createForeignKeyConstraintsFromModel();	2✔
77	}
78
79	private function createColumn()
80	{
81	// Regex the message to get the name of the table
82	$matches = [];	7✔
83	if (!\preg_match("/column '([^\.']*)'/", $this->exception->getMessage(), $matches)) {	7✔
84	throw new \Exception('Anorm: Could not parse PDOException', 0, $this->exception);	1✔
85	}
86	$columnName = $matches[1];	6✔
87	// Add the column.
88	// TODO Have a go at figuring out the type if the model is available.
89	$sampleData = null;	6✔
90	if ($this->model) {	6✔
91	// See if we can reverse map the
92	$invertMap = array_flip($this->mapper->map);	5✔
93	$property = $invertMap[$columnName];	5✔
94	$sampleData = $this->model->$property;	5✔
95	}
96	$columnFn = Anorm::$columnFn; // Redundant, but can't do this Anorm::$columnFn(...)	6✔
97	$columnDefinition = $columnFn($columnName, $sampleData);	6✔
98	$sql = "ALTER TABLE `" . $this->mapper->table . "` ADD $columnName $columnDefinition";	6✔
99	$this->mapper->pdo->query($sql);	6✔
100	}
101
102	/**
103	* Handle foreign key constraint violations by creating missing foreign keys
104	*/
105	private function handleForeignKeyConstraint()
106	{
107	// Check if this is a missing foreign key constraint
108	if (strpos($this->exception->getMessage(), 'Cannot add or update a child row') !== false) {	×
109	$this->createMissingForeignKeyConstraints();	×
110	} else {
111	// For other foreign key issues, try to create the constraint
112	$this->createForeignKeyConstraintsFromModel();	×
113	}
114	}
115
116	/**
117	* Create missing foreign key constraints based on relationship definitions
118	*/
119	private function createMissingForeignKeyConstraints()
120	{
121	if (!$this->model \|\| !property_exists($this->model, '_relationshipManager')) {	×
122	return;	×
123	}
124
125	$relationshipManager = $this->model->_relationshipManager;	×
126	if (!$relationshipManager) {	×
127	return;	×
128	}
129
130	// Get all relationships defined in the model
131	$relationships = $relationshipManager->getRelationships();	×
132
UNCOV 133	foreach ($relationships as $relationship) {	×
UNCOV 134	$this->createForeignKeyFromRelationship($relationship);	×
135	}
136	}
137
138	/**
139	* Create foreign key constraints from model relationship definitions
140	*/
141	private function createForeignKeyConstraintsFromModel()
142	{
143	if (!$this->model \|\| !property_exists($this->model, '_relationshipManager')) {	2✔
144	return;	2✔
145	}
146
147	$relationshipManager = $this->model->_relationshipManager;	×
UNCOV 148	if (!$relationshipManager) {	×
149	return;	×
150	}
151
152	// Get all relationships and create foreign keys for them
UNCOV 153	$relationships = $relationshipManager->getRelationships();	×
154
UNCOV 155	foreach ($relationships as $relationship) {	×
UNCOV 156	$this->createForeignKeyFromRelationship($relationship);	×
157	}
158	}
159
160	/**
161	* Create a foreign key constraint from a relationship definition
162	*/
163	private function createForeignKeyFromRelationship($relationship)
164	{
165	$type = $relationship->getType();	×
166
167	if ($type === 'ManyHasOne') {	×
168	// For belongsTo relationships, create foreign key on current table
169	$this->createForeignKey(	×
170	$this->mapper->table,	×
UNCOV 171	$relationship->getForeignKey(),	×
172	$this->getTableNameFromModelClass($relationship->getRelatedModelClass()),	×
173	$relationship->getPrimaryKey(),	×
174	$relationship->getConstraintOptions()	×
175	);	×
176	} elseif ($type === 'OneHasMany') {	×
177	// For hasMany relationships, create foreign key on related table
178	$this->createForeignKey(	×
UNCOV 179	$this->getTableNameFromModelClass($relationship->getRelatedModelClass()),	×
UNCOV 180	$relationship->getForeignKey(),	×
UNCOV 181	$this->mapper->table,	×
UNCOV 182	$relationship->getPrimaryKey(),	×
UNCOV 183	$relationship->getConstraintOptions()	×
UNCOV 184	);	×
185	}
186	// ManyHasMany relationships don't need foreign keys on main tables
187	// They use join tables which should be handled separately
188	}
189
190	/**
191	* Create a foreign key constraint
192	*/
193	private function createForeignKey($table, $column, $referencedTable, $referencedColumn, $options = [])
194	{
195	$constraintName = $options['constraint_name'] ?? "fk_{$table}_{$column}";	×
UNCOV 196	$onDelete = $options['on_delete'] ?? 'RESTRICT';	×
UNCOV 197	$onUpdate = $options['on_update'] ?? 'CASCADE';	×
198
199	// Check if foreign key already exists
UNCOV 200	if ($this->foreignKeyExists($table, $constraintName)) {	×
UNCOV 201	return;	×
202	}
203
204	// Ensure the referenced table exists
205	$this->ensureTableExists($referencedTable);	×
206
207	// Ensure the column exists in the source table
208	$this->ensureColumnExists($table, $column);	×
209
210	// Ensure the referenced column exists in the target table
211	$this->ensureColumnExists($referencedTable, $referencedColumn);	×
212
UNCOV 213	$sql = "ALTER TABLE `{$table}`	×
UNCOV 214	ADD CONSTRAINT `{$constraintName}`	×
215	FOREIGN KEY (`{$column}`)	×
216	REFERENCES `{$referencedTable}`(`{$referencedColumn}`)	×
UNCOV 217	ON DELETE {$onDelete}	×
UNCOV 218	ON UPDATE {$onUpdate}";	×
219
220	try {
UNCOV 221	$this->mapper->pdo->query($sql);	×
UNCOV 222	} catch (\PDOException $e) {	×
223	// If foreign key creation fails, it might be because the constraint already exists
224	// or there are data integrity issues. Log and continue.
UNCOV 225	error_log("Anorm: Failed to create foreign key constraint: " . $e->getMessage());	×
226	}
227	}
228
229	/**
230	* Check if a foreign key constraint exists
231	*/
232	private function foreignKeyExists($table, $constraintName)
233	{
UNCOV 234	$sql = "SELECT COUNT(*) as count	×
235	FROM information_schema.TABLE_CONSTRAINTS
236	WHERE TABLE_SCHEMA = DATABASE()
237	AND TABLE_NAME = ?
238	AND CONSTRAINT_NAME = ?
239	AND CONSTRAINT_TYPE = 'FOREIGN KEY'";	×
240
UNCOV 241	$stmt = $this->mapper->pdo->prepare($sql);	×
UNCOV 242	$stmt->execute([$table, $constraintName]);	×
UNCOV 243	$result = $stmt->fetch(\PDO::FETCH_ASSOC);	×
244
UNCOV 245	return $result['count'] > 0;	×
246	}
247
248	/**
249	* Ensure a table exists, create it if it doesn't
250	*/
251	private function ensureTableExists($tableName)
252	{
UNCOV 253	$sql = "CREATE TABLE IF NOT EXISTS `{$tableName}` (	×
254	id INT(11) AUTO_INCREMENT PRIMARY KEY
UNCOV 255	)";	×
UNCOV 256	$this->mapper->pdo->query($sql);	×
257	}
258
259	/**
260	* Ensure a column exists in a table, create it if it doesn't
261	*/
262	private function ensureColumnExists($tableName, $columnName)
263	{
264	// Check if column exists
265	$sql = "SELECT COUNT(*) as count	×
266	FROM information_schema.COLUMNS
267	WHERE TABLE_SCHEMA = DATABASE()
268	AND TABLE_NAME = ?
269	AND COLUMN_NAME = ?";	×
270
271	$stmt = $this->mapper->pdo->prepare($sql);	×
272	$stmt->execute([$tableName, $columnName]);	×
273	$result = $stmt->fetch(\PDO::FETCH_ASSOC);	×
274
UNCOV 275	if ($result['count'] == 0) {	×
276	// Column doesn't exist, create it
UNCOV 277	$columnDefinition = $this->getColumnDefinitionForForeignKey($columnName);	×
UNCOV 278	$sql = "ALTER TABLE `{$tableName}` ADD `{$columnName}` {$columnDefinition}";	×
UNCOV 279	$this->mapper->pdo->query($sql);	×
280	}
281	}
282
283	/**
284	* Get appropriate column definition for foreign key columns
285	*/
286	private function getColumnDefinitionForForeignKey($columnName)
287	{
288	// Foreign key columns are typically INT(11) to match primary keys
UNCOV 289	return "INT(11) NULL";	×
290	}
291
292	/**
293	* Get table name from model class name
294	*/
295	private function getTableNameFromModelClass($modelClass)
296	{
297	// Remove namespace and 'Model' suffix, convert to snake_case
UNCOV 298	$className = basename(str_replace('\\', '/', $modelClass));	×
299	$className = str_replace('Model', '', $className);	×
300
301	// Convert CamelCase to snake_case and pluralize
UNCOV 302	$tableName = strtolower(preg_replace('/([a-z])([A-Z])/', '$1_$2', $className));	×
303
304	// Simple pluralization (add 's' if doesn't end with 's')
UNCOV 305	if (substr($tableName, -1) !== 's') {	×
UNCOV 306	$tableName .= 's';	×
307	}
308
UNCOV 309	return $tableName;	×
310	}
311
312	public static function columnDefinition($columnName, $sampleData)
313	{
314	if ($sampleData) {	11✔
315	if (\is_numeric($sampleData)) {	10✔
316	if (\is_integer($sampleData)) {	4✔
317	return "INT(11) NULL";	1✔
318	}
319	if (\is_float($sampleData)) {	3✔
320	return "DOUBLE NULL";	1✔
321	}
322	}
323	if (is_object($sampleData) && get_class($sampleData) == 'Moment\Moment') {	8✔
324	return "DATETIME NULL";	1✔
325	}
326	if (is_string($sampleData)) {	7✔
327	if (preg_match('/(\d{4})-(\d{2})-(\d{2})/', $sampleData) === 1) {	7✔
328	return "DATETIME NULL";	1✔
329	}
330	if (strlen($sampleData) > 256) {	7✔
331	return "TEXT";	1✔
332	}
333	if (strlen($sampleData) > 128) {	6✔
334	return "VARCHAR(256)";	1✔
335	}
336	}
337	}
338	return 'VARCHAR(128)';	6✔
339	}
340	}

saygoweb / anorm / 18483785918

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous