18482579375

Committed 14 Oct 2025 01:22AM UTC coverage: 84.134% (+3.7%) from 80.483%

Build # 18482579375

Build Type

Pull #42

github

Committed by

web-flow

Commit Message

Merge f81a33b4f into 025025bcf

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

Run Details

833 of 948 new or added lines in 19 files covered. (87.87%)

75 existing lines in 4 files now uncovered.

1628 of 1935 relevant lines covered (84.13%)

15.51 hits per line

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

37.78

/src/TableMaker.php

<?php

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 \PDOException The exception that requires the database schema to be fixed. */
    public $exception;

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

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

saygoweb / anorm / 18482579375

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