UpdateDatabase::updateDatabaseStructure() - Code Metrics - mikopbx/Core - Measure and Improve Code Quality continuously with Scrutinizer

UpdateDatabase::updateDatabaseStructure() A
last analyzed 2025-05-20 09:51 UTC

↳ Parent: UpdateDatabase

Complexity

Conditions	2
Paths	4

Size

Total Lines	8
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	6
c	0
b	0
f	0
dl	0
loc	8
rs	10
cc	2
nc	4
nop	0

<?php

/*
 * MikoPBX - free phone system for small business
 * Copyright © 2017-2023 Alexey Portnov and Nikolay Beketov
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License along with this program.
 * If not, see <https://www.gnu.org/licenses/>.
 */

namespace MikoPBX\Core\System\Upgrade;

use MikoPBX\Common\Models\PbxSettings;
use MikoPBX\Common\Providers\MainDatabaseProvider;
use MikoPBX\Common\Providers\ModelsAnnotationsProvider;
use MikoPBX\Common\Providers\ModelsMetadataProvider;
use MikoPBX\Core\System\Processes;
use MikoPBX\Core\System\SystemMessages;
use MikoPBX\Core\System\Util;
use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Di\Injectable;
use ReflectionClass;
use Throwable;

use function MikoPBX\Common\Config\appPath;

/**
 * Class UpdateDatabase
 *
 *
 * @property \Phalcon\Config\Config config
 *
 *  @package MikoPBX\Core\System\Upgrade
 */
class UpdateDatabase extends Injectable
{
    /**
     * Updates database structure according to models annotations
     */
    public function updateDatabaseStructure(): void
    {
        try {
            MainDatabaseProvider::recreateDBConnections(); // after storage remount
            $this->updateDbStructureByModelsAnnotations();
            MainDatabaseProvider::recreateDBConnections(); // if we change anything in structure
        } catch (Throwable $e) {
            SystemMessages::echoWithSyslog('Errors within database upgrade process ' . $e->getMessage());
        }
    }

    /**
     *
     * Step by step goes by models annotations and apply structure changes
     *
     * @return void
     */
    private function updateDbStructureByModelsAnnotations(): void
    {
        $modelsDir = appPath('src/Common/Models');
        $results   = glob("$modelsDir/*.php", GLOB_NOSORT);
        foreach ($results as $file) {
            $className        = pathinfo($file)['filename'];
            $moduleModelClass = "MikoPBX\\Common\\Models\\$className";
            try {
                $this->createUpdateDbTableByAnnotations($moduleModelClass);
            } catch (Throwable $exception) {
                // Log errors encountered during table update
                SystemMessages::echoWithSyslog('Errors within update table ' . $className . ' ' . $exception->getMessage());
            }
        }

        // Update permissions for custom modules
        $this->updatePermitCustomModules();
    }

    /**
     * Update the permissions for custom modules.
     * https://github.com/mikopbx/Core/issues/173
     *
     * @return void
     */
    private function updatePermitCustomModules(): void
    {
        $modulesDir = $this->config->path('core.modulesDir');
        $findPath  = Util::which('find');
        $chownPath = Util::which('chown');
        $chmodPath = Util::which('chmod');

        // Set execute permissions for files in the modules' binary directories
        Processes::mwExec("$findPath $modulesDir/*/*bin/ -type f -exec $chmodPath +x {} \;");

        // Set ownership of the modules directory to www:www
        Processes::mwExec("$chownPath -R www:www $modulesDir/*");
    }

    /**
     * Create, update DB structure by code description
     *
     * @param $modelClassName string class name with namespace
     *                        i.e. MikoPBX\Common\Models\Extensions or Modules\ModuleSmartIVR\Models\Settings
     *
     * @return bool
     */
    public function createUpdateDbTableByAnnotations(string $modelClassName): bool
    {
        $result = true;

        // Check if the model class exists and has properties
        if (
            ! class_exists($modelClassName)
            || count(get_class_vars($modelClassName)) === 0
        ) {
            return true;
        }


        // Test if the model class is abstract
        try {
            $reflection = new ReflectionClass($modelClassName);
            if ($reflection->isAbstract()) {
                return true;
            }
        } catch (Throwable $exception) {
            return false;
        }

        // Get the model instance
        $model                 = new $modelClassName();

        // Get the connection service name
        $connectionServiceName = $model->getReadConnectionService();

        // Check if the connection service name is empty
        if (empty($connectionServiceName)) {
            return false;
        }

        // Get the connection service and metadata provider
        $connectionService = $this->di->getShared($connectionServiceName);
        $metaData          = $this->di->get(ModelsMetadataProvider::SERVICE_NAME);
        $metaData->reset();

        // Get the model annotations
        //https://docs.phalcon.io/4.0/ru-ru/annotations
        $modelAnnotation = $this->di->get(ModelsAnnotationsProvider::SERVICE_NAME)->get($model);

        // Initialize table name, structure and indexes
        $tableName       = $model->getSource();
        $table_structure = [];
        $indexes         = [];

        // Create columns list by code annotations
        $newColNames       = $metaData->getAttributes($model);
        $previousAttribute = '';
        foreach ($newColNames as $attribute) {
            $table_structure[$attribute] = [
                'type'      => Column::TYPE_VARCHAR,
                'after'     => $previousAttribute,
                'notNull'   => false,
                'isNumeric' => false,
                'primary'   => false,
            ];
            $previousAttribute           = $attribute;
        }

        // Set data types
        $propertiesAnnotations = $modelAnnotation->getPropertiesAnnotations();
        if ($propertiesAnnotations !== false) {
            $attributeTypes = $metaData->getDataTypes($model);
            foreach ($attributeTypes as $attribute => $type) {
                $table_structure[$attribute]['type'] = $type;
                // Try to find size of field
                if (array_key_exists($attribute, $propertiesAnnotations)) {
                    $propertyDescription = $propertiesAnnotations[$attribute];
                    if (
                        $propertyDescription->has('Column')
                        && $propertyDescription->get('Column')->hasArgument('length')
                    ) {
                        $table_structure[$attribute]['size'] = $propertyDescription->get('Column')->getArgument(
                            'length'
                        );
                    }
                }
            }
        }

        // Change type for numeric columns
        $numericAttributes = $metaData->getDataTypesNumeric($model);
        foreach ($numericAttributes as $attribute => $value) {
            $table_structure[$attribute]['type']      = Column::TYPE_INTEGER;
            $table_structure[$attribute]['isNumeric'] = true;
        }

        // Set not null for columns
        $notNull = $metaData->getNotNullAttributes($model);
        foreach ($notNull as $attribute) {
            $table_structure[$attribute]['notNull'] = true;
        }

        // Set default values for initial save, later it fill at Models\ModelBase\beforeValidationOnCreate
        $defaultValues = $metaData->getDefaultValues($model);
        foreach ($defaultValues as $key => $value) {
            if ($value !== null) {
                $table_structure[$key]['default'] = $value;
            }
        }

        // Set primary keys
        // $primaryKeys = $metaData->getPrimaryKeyAttributes($model);
        // foreach ($primaryKeys as $attribute) {
        //     $indexes[$attribute] = new Index($attribute, [$attribute], 'UNIQUE');
        // }

        // Set bind types
        $bindTypes = $metaData->getBindTypes($model);
        foreach ($bindTypes as $attribute => $value) {
            $table_structure[$attribute]['bindType'] = $value;
        }

        // Find auto incremental column, usually it is ID column
        $keyFiled = $metaData->getIdentityField($model);
        if ($keyFiled) {
            unset($indexes[$keyFiled]);
            $table_structure[$keyFiled] = [
                'type'          => Column::TYPE_INTEGER,
                'notNull'       => true,
                'autoIncrement' => true,
                'primary'       => true,
                'isNumeric'     => true,
                'first'         => true,
            ];
        }

        // Some exceptions
        if ($modelClassName === PbxSettings::class) {
            $keyFiled = 'key';
            unset($indexes[$keyFiled]);
            $table_structure[$keyFiled] = [
                'type'          => Column::TYPE_VARCHAR,
                'notNull'       => true,
                'autoIncrement' => false,
                'primary'       => true,
                'isNumeric'     => false,
                'first'         => true,
            ];
        }

        // Create additional indexes
        $modelClassAnnotation = $modelAnnotation->getClassAnnotations();
        if (
            $modelClassAnnotation !== null
            && $modelClassAnnotation->has('Indexes')
        ) {
            $additionalIndexes = $modelClassAnnotation->get('Indexes')->getArguments();
            foreach ($additionalIndexes as $index) {
                $indexName           = "i_{$tableName}_{$index['name']}";
                $indexes[$indexName] = new Index($indexName, $index['columns'], $index['type']);
            }
        }

        // Create new table structure
        $columns = [];
        foreach ($table_structure as $colName => $colType) {
            $columns[] = new Column($colName, $colType);
        }

        $columnsNew = [
            'columns' => $columns,
            'indexes' => $indexes,
        ];

        // Let's describe the directory for storing temporary tables and data
        $tempDir = $this->di->getShared('config')->path('core.tempDir');
        $sqliteTempStore = $connectionService->fetchColumn('PRAGMA temp_store');
        $sqliteTempDir   = $connectionService->fetchColumn('PRAGMA temp_store_directory');
        $connectionService->execute('PRAGMA temp_store = FILE;');
        $connectionService->execute("PRAGMA temp_store_directory = '$tempDir';");

        // Starting the transaction
        $connectionService->begin();

        if (! $connectionService->tableExists($tableName)) {
            $msg = ' - UpdateDatabase: Create new table: ' . $tableName . ' ';
            SystemMessages::echoWithSyslog($msg);
            $result = $connectionService->createTable($tableName, '', $columnsNew);
            SystemMessages::echoResult($msg);
        } else {
            // Table exists, we have to check/upgrade its structure
            $currentColumnsArr = $connectionService->describeColumns($tableName, '');

            if ($this->isTableStructureNotEqual($currentColumnsArr, $columns)) {
                $msg = ' - UpdateDatabase: Upgrade table: ' . $tableName . ' ';
                SystemMessages::echoWithSyslog($msg);
                // Create new table and copy all data
                $currentStateColumnList = [];
                $oldColNames            = []; // Old columns names
                $countColumnsTemp       = count($currentColumnsArr);
                for ($k = 0; $k < $countColumnsTemp; $k++) {
                    $currentStateColumnList[$k] = $currentColumnsArr[$k]->getName();
                    $oldColNames[]              = $currentColumnsArr[$k]->getName();
                }

                // Create temporary clone on current table with all columns and date
                // Delete original table
                $gluedColumns = implode(',', $currentStateColumnList);
                $query        = "CREATE TEMPORARY TABLE {$tableName}_backup($gluedColumns); 
INSERT INTO {$tableName}_backup SELECT $gluedColumns FROM $tableName; 
DROP TABLE  $tableName";
                $result       = $result && $connectionService->execute($query);

                // Create new table with new columns structure
                $result = $result && $connectionService->createTable($tableName, '', $columnsNew);

                // Copy data from temporary table to newly created
                $newColumnNames  = array_intersect($newColNames, $oldColNames);
                $gluedNewColumns = implode(',', $newColumnNames);
                $result          = $result && $connectionService->execute(
                    "INSERT INTO $tableName ( $gluedNewColumns) SELECT $gluedNewColumns  FROM {$tableName}_backup;"
                );

                // Drop temporary table
                $result = $result && $connectionService->execute("DROP TABLE {$tableName}_backup;");
                SystemMessages::echoResult($msg);
            }
        }


        if ($result) {
            $result = $this->updateIndexes($tableName, $connectionService, $indexes);
        }

        if ($result) {
            $result = $connectionService->commit();
        } else {
            SystemMessages::sysLogMsg('createUpdateDbTableByAnnotations', "Error: Failed on create/update table {$tableName}", LOG_ERR);
            $connectionService->rollback();
        }

        // Restoring PRAGMA values
        $connectionService->execute("PRAGMA temp_store = $sqliteTempStore;");
        $connectionService->execute("PRAGMA temp_store_directory = '$sqliteTempDir';");
        return $result;
    }

    /**
     * Compares database structure with metadata info
     *
     * @param $currentTableStructure
     * @param $newTableStructure
     *
     * @return bool
     */
    private function isTableStructureNotEqual($currentTableStructure, $newTableStructure): bool
    {
        // 1. Check fields count
        if (count($currentTableStructure) !== count($newTableStructure)) {
            return true;
        }

        $comparedSettings = [
            'getName',
            'getType',
            'getTypeReference',
            'getTypeValues',
            'getSize',
            'getScale',
            'isUnsigned',
            'isNotNull',
            'isPrimary',
            'isAutoIncrement',
            'isNumeric',
            'isFirst',
            'getAfterPosition',
            //'getBindType',
            'getDefault',
            'hasDefault',
        ];

        // 2. Check fields types
        foreach ($newTableStructure as $index => $newField) {
            $oldField = $currentTableStructure[$index];
            foreach ($comparedSettings as $compared_setting) {
                if ($oldField->$compared_setting() !== $newField->$compared_setting()) {
                    // Sqlite transform "1" to ""1"" in default settings, but it is normal
                    if (
                        $compared_setting === 'getDefault'
                        && $oldField->$compared_setting() === '"' . $newField->$compared_setting() . '"'
                    ) {
                        continue;
                    }

                    // Description for "length" is integer, but table structure store it as string
                    if (
                        $compared_setting === 'getSize'
                        && (string)$oldField->$compared_setting() === (string)$newField->$compared_setting()
                    ) {
                        continue;
                    }

                    return true; // find different columns
                }
            }
        }

        return false;
    }


    /**
     * Updates indexes on database
     *
     * @param string $tableName
     * @param mixed  $connectionService DependencyInjection connection service used to read data
     * @param array  $indexes
     *
     * @return bool
     */
    private function updateIndexes(string $tableName, mixed $connectionService, array $indexes): bool
    {
        $result         = true;
        $currentIndexes = $connectionService->describeIndexes($tableName);

        // Drop not exist indexes
        foreach ($currentIndexes as $indexName => $currentIndex) {
            if (
                stripos($indexName, 'sqlite_autoindex') === false
                && ! array_key_exists($indexName, $indexes)
            ) {
                $msg = " - UpdateDatabase: Delete index: $indexName ";
                SystemMessages::echoWithSyslog($msg);
                $result += $connectionService->dropIndex($tableName, '', $indexName);
                SystemMessages::echoResult($msg);
            }
        }

        // Add/update exist indexes
        foreach ($indexes as $indexName => $describedIndex) {
            if (array_key_exists($indexName, $currentIndexes)) {
                $currentIndex = $currentIndexes[$indexName];
                if ($describedIndex->getColumns() !== $currentIndex->getColumns()) {
                    $msg = " - UpdateDatabase: Update index: $indexName ";
                    SystemMessages::echoWithSyslog($msg);
                    $result += $connectionService->dropIndex($tableName, '', $indexName);
                    $result += $connectionService->addIndex($tableName, '', $describedIndex);
                    SystemMessages::echoResult($msg);
                }
            } else {
                $msg = " - UpdateDatabase: Add new index: $indexName ";
                SystemMessages::echoWithSyslog($msg);
                $result += $connectionService->addIndex($tableName, '', $describedIndex);
                SystemMessages::echoResult($msg);
            }
        }

        return $result;
    }
}


1			<?php
2
3			/*
4			* MikoPBX - free phone system for small business
5			* Copyright © 2017-2023 Alexey Portnov and Nikolay Beketov
6			*
7			* This program is free software: you can redistribute it and/or modify
8			* it under the terms of the GNU General Public License as published by
9			* the Free Software Foundation; either version 3 of the License, or
10			* (at your option) any later version.
11			*
12			* This program is distributed in the hope that it will be useful,
13			* but WITHOUT ANY WARRANTY; without even the implied warranty of
14			* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15			* GNU General Public License for more details.
16			*
17			* You should have received a copy of the GNU General Public License along with this program.
18			* If not, see <https://www.gnu.org/licenses/>.
19			*/
20
21			namespace MikoPBX\Core\System\Upgrade;
22
23			use MikoPBX\Common\Models\PbxSettings;
24			use MikoPBX\Common\Providers\MainDatabaseProvider;
25			use MikoPBX\Common\Providers\ModelsAnnotationsProvider;
26			use MikoPBX\Common\Providers\ModelsMetadataProvider;
27			use MikoPBX\Core\System\Processes;
28			use MikoPBX\Core\System\SystemMessages;
29			use MikoPBX\Core\System\Util;
30			use Phalcon\Db\Column;
31			use Phalcon\Db\Index;
32			use Phalcon\Di\Injectable;
33			use ReflectionClass;
34			use Throwable;
35
36			use function MikoPBX\Common\Config\appPath;
37
38			/**
39			* Class UpdateDatabase
40			*
41			*
42			* @property \Phalcon\Config\Config config
43			*
44			* @package MikoPBX\Core\System\Upgrade
45			*/
46			class UpdateDatabase extends Injectable
47			{
48			/**
49			* Updates database structure according to models annotations
50			*/
51			public function updateDatabaseStructure(): void
52			{
53			try {
54			MainDatabaseProvider::recreateDBConnections(); // after storage remount
55			$this->updateDbStructureByModelsAnnotations();
56			MainDatabaseProvider::recreateDBConnections(); // if we change anything in structure
57			} catch (Throwable $e) {
58			SystemMessages::echoWithSyslog('Errors within database upgrade process ' . $e->getMessage());
59			}
60			}
61
62			/**
63			*
64			* Step by step goes by models annotations and apply structure changes
65			*
66			* @return void
67			*/
68			private function updateDbStructureByModelsAnnotations(): void
69			{
70			$modelsDir = appPath('src/Common/Models');
71			$results = glob("$modelsDir/*.php", GLOB_NOSORT);
72			foreach ($results as $file) {
73			$className = pathinfo($file)['filename'];
74			$moduleModelClass = "MikoPBX\\Common\\Models\\$className";
75			try {
76			$this->createUpdateDbTableByAnnotations($moduleModelClass);
77			} catch (Throwable $exception) {
78			// Log errors encountered during table update
79			SystemMessages::echoWithSyslog('Errors within update table ' . $className . ' ' . $exception->getMessage());
80			}
81			}
82
83			// Update permissions for custom modules
84			$this->updatePermitCustomModules();
85			}
86
87			/**
88			* Update the permissions for custom modules.
89			* https://github.com/mikopbx/Core/issues/173
90			*
91			* @return void
92			*/
93			private function updatePermitCustomModules(): void
94			{
95			$modulesDir = $this->config->path('core.modulesDir');
96			$findPath = Util::which('find');
97			$chownPath = Util::which('chown');
98			$chmodPath = Util::which('chmod');
99
100			// Set execute permissions for files in the modules' binary directories
101			Processes::mwExec("$findPath $modulesDir//bin/ -type f -exec $chmodPath +x {} \;");
102
103			// Set ownership of the modules directory to www:www
104			Processes::mwExec("$chownPath -R www:www $modulesDir/*");
105			}
106
107			/**
108			* Create, update DB structure by code description
109			*
110			* @param $modelClassName string class name with namespace
111			* i.e. MikoPBX\Common\Models\Extensions or Modules\ModuleSmartIVR\Models\Settings
112			*
113			* @return bool
114			*/
115			public function createUpdateDbTableByAnnotations(string $modelClassName): bool
116			{
117			$result = true;
118
119			// Check if the model class exists and has properties
120			if (
121			! class_exists($modelClassName)
122			\|\| count(get_class_vars($modelClassName)) === 0
123			) {
124			return true;
125			}
126
127
128			// Test if the model class is abstract
129			try {
130			$reflection = new ReflectionClass($modelClassName);
131			if ($reflection->isAbstract()) {
132			return true;
133			}
134			} catch (Throwable $exception) {
135			return false;
136			}
137
138			// Get the model instance
139			$model = new $modelClassName();
140
141			// Get the connection service name
142			$connectionServiceName = $model->getReadConnectionService();
143
144			// Check if the connection service name is empty
145			if (empty($connectionServiceName)) {
146			return false;
147			}
148
149			// Get the connection service and metadata provider
150			$connectionService = $this->di->getShared($connectionServiceName);
151			$metaData = $this->di->get(ModelsMetadataProvider::SERVICE_NAME);
152			$metaData->reset();
153
154			// Get the model annotations
155			//https://docs.phalcon.io/4.0/ru-ru/annotations
156			$modelAnnotation = $this->di->get(ModelsAnnotationsProvider::SERVICE_NAME)->get($model);
157
158			// Initialize table name, structure and indexes
159			$tableName = $model->getSource();
160			$table_structure = [];
161			$indexes = [];
162
163			// Create columns list by code annotations
164			$newColNames = $metaData->getAttributes($model);
165			$previousAttribute = '';
166			foreach ($newColNames as $attribute) {
167			$table_structure[$attribute] = [
168			'type' => Column::TYPE_VARCHAR,
169			'after' => $previousAttribute,
170			'notNull' => false,
171			'isNumeric' => false,
172			'primary' => false,
173			];
174			$previousAttribute = $attribute;
175			}
176
177			// Set data types
178			$propertiesAnnotations = $modelAnnotation->getPropertiesAnnotations();
179			if ($propertiesAnnotations !== false) {
180			$attributeTypes = $metaData->getDataTypes($model);
181			foreach ($attributeTypes as $attribute => $type) {
182			$table_structure[$attribute]['type'] = $type;
183			// Try to find size of field
184			if (array_key_exists($attribute, $propertiesAnnotations)) {
185			$propertyDescription = $propertiesAnnotations[$attribute];
186			if (
187			$propertyDescription->has('Column')
188			&& $propertyDescription->get('Column')->hasArgument('length')
189			) {
190			$table_structure[$attribute]['size'] = $propertyDescription->get('Column')->getArgument(
191			'length'
192			);
193			}
194			}
195			}
196			}
197
198			// Change type for numeric columns
199			$numericAttributes = $metaData->getDataTypesNumeric($model);
200			foreach ($numericAttributes as $attribute => $value) {
201			$table_structure[$attribute]['type'] = Column::TYPE_INTEGER;
202			$table_structure[$attribute]['isNumeric'] = true;
203			}
204
205			// Set not null for columns
206			$notNull = $metaData->getNotNullAttributes($model);
207			foreach ($notNull as $attribute) {
208			$table_structure[$attribute]['notNull'] = true;
209			}
210
211			// Set default values for initial save, later it fill at Models\ModelBase\beforeValidationOnCreate
212			$defaultValues = $metaData->getDefaultValues($model);
213			foreach ($defaultValues as $key => $value) {
214			if ($value !== null) {
215			$table_structure[$key]['default'] = $value;
216			}
217			}
218
219			// Set primary keys
220			// $primaryKeys = $metaData->getPrimaryKeyAttributes($model);
221			// foreach ($primaryKeys as $attribute) {
222			// $indexes[$attribute] = new Index($attribute, [$attribute], 'UNIQUE');
223			// }
224
225			// Set bind types
226			$bindTypes = $metaData->getBindTypes($model);
227			foreach ($bindTypes as $attribute => $value) {
228			$table_structure[$attribute]['bindType'] = $value;
229			}
230
231			// Find auto incremental column, usually it is ID column
232			$keyFiled = $metaData->getIdentityField($model);
233			if ($keyFiled) {
234			unset($indexes[$keyFiled]);
235			$table_structure[$keyFiled] = [
236			'type' => Column::TYPE_INTEGER,
237			'notNull' => true,
238			'autoIncrement' => true,
239			'primary' => true,
240			'isNumeric' => true,
241			'first' => true,
242			];
243			}
244
245			// Some exceptions
246			if ($modelClassName === PbxSettings::class) {
247			$keyFiled = 'key';
248			unset($indexes[$keyFiled]);
249			$table_structure[$keyFiled] = [
250			'type' => Column::TYPE_VARCHAR,
251			'notNull' => true,
252			'autoIncrement' => false,
253			'primary' => true,
254			'isNumeric' => false,
255			'first' => true,
256			];
257			}
258
259			// Create additional indexes
260			$modelClassAnnotation = $modelAnnotation->getClassAnnotations();
261			if (
262			$modelClassAnnotation !== null
263			&& $modelClassAnnotation->has('Indexes')
264			) {
265			$additionalIndexes = $modelClassAnnotation->get('Indexes')->getArguments();
266			foreach ($additionalIndexes as $index) {
267			$indexName = "i_{$tableName}_{$index['name']}";
268			$indexes[$indexName] = new Index($indexName, $index['columns'], $index['type']);
269			}
270			}
271
272			// Create new table structure
273			$columns = [];
274			foreach ($table_structure as $colName => $colType) {
275			$columns[] = new Column($colName, $colType);
276			}
277
278			$columnsNew = [
279			'columns' => $columns,
280			'indexes' => $indexes,
281			];
282
283			// Let's describe the directory for storing temporary tables and data
284			$tempDir = $this->di->getShared('config')->path('core.tempDir');
285			$sqliteTempStore = $connectionService->fetchColumn('PRAGMA temp_store');
286			$sqliteTempDir = $connectionService->fetchColumn('PRAGMA temp_store_directory');
287			$connectionService->execute('PRAGMA temp_store = FILE;');
288			$connectionService->execute("PRAGMA temp_store_directory = '$tempDir';");
289
290			// Starting the transaction
291			$connectionService->begin();
292
293			if (! $connectionService->tableExists($tableName)) {
294			$msg = ' - UpdateDatabase: Create new table: ' . $tableName . ' ';
295			SystemMessages::echoWithSyslog($msg);
296			$result = $connectionService->createTable($tableName, '', $columnsNew);
297			SystemMessages::echoResult($msg);
298			} else {
299			// Table exists, we have to check/upgrade its structure
300			$currentColumnsArr = $connectionService->describeColumns($tableName, '');
301
302			if ($this->isTableStructureNotEqual($currentColumnsArr, $columns)) {
303			$msg = ' - UpdateDatabase: Upgrade table: ' . $tableName . ' ';
304			SystemMessages::echoWithSyslog($msg);
305			// Create new table and copy all data
306			$currentStateColumnList = [];
307			$oldColNames = []; // Old columns names
308			$countColumnsTemp = count($currentColumnsArr);
309			for ($k = 0; $k < $countColumnsTemp; $k++) {
310			$currentStateColumnList[$k] = $currentColumnsArr[$k]->getName();
311			$oldColNames[] = $currentColumnsArr[$k]->getName();
312			}
313
314			// Create temporary clone on current table with all columns and date
315			// Delete original table
316			$gluedColumns = implode(',', $currentStateColumnList);
317			$query = "CREATE TEMPORARY TABLE {$tableName}_backup($gluedColumns);
318			INSERT INTO {$tableName}_backup SELECT $gluedColumns FROM $tableName;
319			DROP TABLE $tableName";
320			$result = $result && $connectionService->execute($query);
321
322			// Create new table with new columns structure
323			$result = $result && $connectionService->createTable($tableName, '', $columnsNew);
324
325			// Copy data from temporary table to newly created
326			$newColumnNames = array_intersect($newColNames, $oldColNames);
327			$gluedNewColumns = implode(',', $newColumnNames);
328			$result = $result && $connectionService->execute(
329			"INSERT INTO $tableName ( $gluedNewColumns) SELECT $gluedNewColumns FROM {$tableName}_backup;"
330			);
331
332			// Drop temporary table
333			$result = $result && $connectionService->execute("DROP TABLE {$tableName}_backup;");
334			SystemMessages::echoResult($msg);
335			}
336			}
337
338
339			if ($result) {
340			$result = $this->updateIndexes($tableName, $connectionService, $indexes);
341			}
342
343			if ($result) {
344			$result = $connectionService->commit();
345			} else {
346			SystemMessages::sysLogMsg('createUpdateDbTableByAnnotations', "Error: Failed on create/update table {$tableName}", LOG_ERR);
347			$connectionService->rollback();
348			}
349
350			// Restoring PRAGMA values
351			$connectionService->execute("PRAGMA temp_store = $sqliteTempStore;");
352			$connectionService->execute("PRAGMA temp_store_directory = '$sqliteTempDir';");
353			return $result;
354			}
355
356			/**
357			* Compares database structure with metadata info
358			*
359			* @param $currentTableStructure
360			* @param $newTableStructure
361			*
362			* @return bool
363			*/
364			private function isTableStructureNotEqual($currentTableStructure, $newTableStructure): bool
365			{
366			// 1. Check fields count
367			if (count($currentTableStructure) !== count($newTableStructure)) {
368			return true;
369			}
370
371			$comparedSettings = [
372			'getName',
373			'getType',
374			'getTypeReference',
375			'getTypeValues',
376			'getSize',
377			'getScale',
378			'isUnsigned',
379			'isNotNull',
380			'isPrimary',
381			'isAutoIncrement',
382			'isNumeric',
383			'isFirst',
384			'getAfterPosition',
385			//'getBindType',
386			'getDefault',
387			'hasDefault',
388			];
389
390			// 2. Check fields types
391			foreach ($newTableStructure as $index => $newField) {
392			$oldField = $currentTableStructure[$index];
393			foreach ($comparedSettings as $compared_setting) {
394			if ($oldField->$compared_setting() !== $newField->$compared_setting()) {
395			// Sqlite transform "1" to ""1"" in default settings, but it is normal
396			if (
397			$compared_setting === 'getDefault'
398			&& $oldField->$compared_setting() === '"' . $newField->$compared_setting() . '"'
399			) {
400			continue;
401			}
402
403			// Description for "length" is integer, but table structure store it as string
404			if (
405			$compared_setting === 'getSize'
406			&& (string)$oldField->$compared_setting() === (string)$newField->$compared_setting()
407			) {
408			continue;
409			}
410
411			return true; // find different columns
412			}
413			}
414			}
415
416			return false;
417			}
418
419
420			/**
421			* Updates indexes on database
422			*
423			* @param string $tableName
424			* @param mixed $connectionService DependencyInjection connection service used to read data
425			* @param array $indexes
426			*
427			* @return bool
428			*/
429			private function updateIndexes(string $tableName, mixed $connectionService, array $indexes): bool
430			{
431			$result = true;
432			$currentIndexes = $connectionService->describeIndexes($tableName);
433
434			// Drop not exist indexes
435			foreach ($currentIndexes as $indexName => $currentIndex) {
436			if (
437			stripos($indexName, 'sqlite_autoindex') === false
438			&& ! array_key_exists($indexName, $indexes)
439			) {
440			$msg = " - UpdateDatabase: Delete index: $indexName ";
441			SystemMessages::echoWithSyslog($msg);
442			$result += $connectionService->dropIndex($tableName, '', $indexName);
443			SystemMessages::echoResult($msg);
444			}
445			}
446
447			// Add/update exist indexes
448			foreach ($indexes as $indexName => $describedIndex) {
449			if (array_key_exists($indexName, $currentIndexes)) {
450			$currentIndex = $currentIndexes[$indexName];
451			if ($describedIndex->getColumns() !== $currentIndex->getColumns()) {
452			$msg = " - UpdateDatabase: Update index: $indexName ";
453			SystemMessages::echoWithSyslog($msg);
454			$result += $connectionService->dropIndex($tableName, '', $indexName);
455			$result += $connectionService->addIndex($tableName, '', $describedIndex);
456			SystemMessages::echoResult($msg);
457			}
458			} else {
459			$msg = " - UpdateDatabase: Add new index: $indexName ";
460			SystemMessages::echoWithSyslog($msg);
461			$result += $connectionService->addIndex($tableName, '', $describedIndex);
462			SystemMessages::echoResult($msg);
463			}
464			}
465
466			return $result;
467			}
468			}
469

mikopbx / Core

UpdateDatabase::updateDatabaseStructure() A last analyzed 2025-05-20 09:51 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

UpdateDatabase::updateDatabaseStructure() A
last analyzed 2025-05-20 09:51 UTC