Completed
Push — master ( 3af08b...23b599 )
by Daniel
03:02
created

source/FKchange.php (1 issue)

Labels
Severity

Upgrade to new PHP Analysis Engine

These results are based on our legacy PHP analysis, consider migrating to our new PHP analysis engine instead. Learn more

1
<?php
2
3
/**
4
 *
5
 * The MIT License (MIT)
6
 *
7
 * Copyright (c) 2015 Daniel Popiniuc
8
 *
9
 * Permission is hereby granted, free of charge, to any person obtaining a copy
10
 * of this software and associated documentation files (the "Software"), to deal
11
 * in the Software without restriction, including without limitation the rights
12
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
13
 * copies of the Software, and to permit persons to whom the Software is
14
 * furnished to do so, subject to the following conditions:
15
 *
16
 * The above copyright notice and this permission notice shall be included in all
17
 * copies or substantial portions of the Software.
18
 *
19
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
20
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
21
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
22
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
23
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
24
 *  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
25
 * SOFTWARE.
26
 *
27
 */
28
29
namespace danielgp\fk_scale_mysql;
30
31
/**
32
 * Description of FKchange
33
 *
34
 * @author Daniel Popiniuc <[email protected]>
35
 */
36
class FKchange
37
{
38
39
    use ConfigurationMySQL,
40
        ConfigurationForAction,
41
        \danielgp\common_lib\CommonCode,
42
        \danielgp\common_lib\MySQLiAdvancedOutput;
43
44
    private $applicationSpecificArray;
45
46
    public function __construct()
47
    {
48
        $rqst = new \Symfony\Component\HttpFoundation\Request;
49
        echo $this->setApplicationHeader()
50
        . $this->buildApplicationInterface(['SuperGlobals' => $rqst->createFromGlobals()])
51
        . $this->setFooterCommon();
52
    }
53
54
    private function buildApplicationInterface($inArray)
55
    {
56
        $mysqlConfig          = $this->configuredMySqlServer();
57
        $elToModify           = $this->targetElementsToModify(['SuperGlobals' => $inArray['SuperGlobals']]);
58
        $transmitedParameters = $this->countTransmitedParameters(['db', 'tbl', 'fld', 'dt']);
59
        $mConnection          = $this->connectToMySql($mysqlConfig);
60
        $sReturn              = [];
61
        $sReturn[]            = '<div class="tabber" id="tabberFKscaleMySQL">'
62
                . '<div class="tabbertab' . ($transmitedParameters ? '' : ' tabbertabdefault')
63
                . '" id="FKscaleMySQLparameters" title="Parameters for scaling">'
64
                . $this->buildInputFormForFKscaling($mysqlConfig, ['SuperGlobals' => $inArray['SuperGlobals']])
65
                . '</div><!-- end of Parameters tab -->';
66
        $targetTableTextFlds  = $this->getForeignKeys($elToModify);
67
        $sReturn[]            = '<div class="tabbertab' . ($transmitedParameters ? ' tabbertabdefault' : '')
68
                . '" id="FKscaleMySQLresults" title="Results">';
69
        if (is_array($targetTableTextFlds)) {
70
            $sReturn[]    = $this->createDropForeignKeysAndGetTargetColumnDefinition($targetTableTextFlds);
71
            $mainColArray = $this->packParameteresForMainChangeColumn($elToModify, $targetTableTextFlds);
72
            $sReturn[]    = $this->createChangeColumn($mainColArray, [
73
                'style'                => 'color:blue;font-weight:bold;',
74
                'includeOldColumnType' => true,
75
            ]);
76
            $sReturn[]    = $this->recreateFKs($elToModify, $targetTableTextFlds);
77
        } else {
78
            if (strlen($mConnection) === 0) {
79
                $sReturn[] = '<p style="color:red;">Check if provided parameters are correct '
80
                        . 'as the combination of Database. Table and Column name were not found as a Foreign Key!</p>';
81
            } else {
82
                $sReturn[] = '<p style="color:red;">Check your "configurationMySQL.php" file '
83
                        . 'for correct MySQL connection parameters '
84
                        . 'as the current ones were not able to be used to establish a connection!</p>';
85
            }
86
        }
87
        $sReturn[] = '</div><!-- end of FKscaleMySQLresults tab -->'
88
                . '</div><!-- tabberFKscaleMySQL -->';
89
        return implode('', $sReturn);
90
    }
91
92
    private function buildInputFormForFKscaling($mysqlConfig, $inArray)
93
    {
94
        $sReturn             = [];
95
        $sReturn[]           = '<label for="dbName">Database name to analyze:</label>'
96
                . '<input type="text" id="dbName" name="db" placeholder="database name" '
97
                . $this->returnInputsCleaned('db', $inArray)
98
                . 'size="30" maxlength="64" class="labell" />';
99
        $sReturn[]           = '<label for="tblName">Table name to analyze:</label>'
100
                . '<input type="text" id="tblName" name="tbl" placeholder="table name" '
101
                . $this->returnInputsCleaned('tbl', $inArray)
102
                . ' size="30" maxlength="64" class="labell" />';
103
        $sReturn[]           = '<label for="fldName">Field name to analyze:</label>'
104
                . '<input type="text" id="fldName" name="fld" placeholder="field name" '
105
                . $this->returnInputsCleaned('fld', $inArray)
106
                . ' size="30" maxlength="64" class="labell" />';
107
        $sReturn[]           = '<label for="dataType">Data type to change to:</label>'
108
                . '<input type="text" id="dataType" name="dt" placeholder="valid data type" '
109
                . $this->returnInputsCleaned('dt', $inArray)
110
                . ' size="30" maxlength="64" class="labell" />';
111
        $sReturn[]           = '<input type="submit" value="Generate SQL queries for scaling" />';
112
        $styleForMySQLparams = 'color:green;font-weight:bold;font-style:italic;';
113
        $sReturn[]           = '<p>For security reasons the MySQL connection details are not available '
114
                . 'to be set/modified through the interface and must be set directly '
115
                . 'into the "configurationMySQL.php" file. Currently these settings are:<ul>'
116
                . '<li>Host name where MySQL server resides: <span style="' . $styleForMySQLparams . '">'
117
                . $mysqlConfig['host'] . '</span></li>'
118
                . '<li>MySQL port used: <span style="' . $styleForMySQLparams . '">'
119
                . $mysqlConfig['port'] . '</span></li>'
120
                . '<li>MySQL database to connect to: <span style="' . $styleForMySQLparams . '">'
121
                . $mysqlConfig['database'] . '</span></li>'
122
                . '<li>MySQL username used: <span style="' . $styleForMySQLparams . '">'
123
                . $mysqlConfig['username'] . '</span></li>'
124
                . '<li>MySQL password used: <span style="' . $styleForMySQLparams . '">'
125
                . 'cannot be disclosed due to security reasons</span></li>'
126
                . '</ul></p>';
127
        $thisPage            = filter_var($inArray['SuperGlobals']->server->get('PHP_SELF'), FILTER_SANITIZE_URL);
128
        return '<form method="get" action="' . $thisPage . '">'
129
                . implode('<br/>', $sReturn)
130
                . '</form>';
131
    }
132
133
    private function createChangeColumn($parameters, $aditionalFeatures = null)
134
    {
135
        return '<div style="'
136
                . (isset($aditionalFeatures['style']) ? $aditionalFeatures['style'] : 'color:blue;')
137
                . '">'
138
                . 'ALTER TABLE `' . $parameters['Database'] . '`.`' . $parameters['Table']
139
                . '` CHANGE `' . $parameters['Column'] . '` `' . $parameters['Column'] . '` '
140
                . $parameters['NewDataType'] . ' '
141
                . $this->setColumnDefinitionAditional($parameters['IsNullable'], $parameters['Default'])
142
                . (strlen($parameters['Extra']) > 0 ? ' AUTO_INCREMENT' : '')
143
                . (strlen($parameters['Comment']) > 0 ? ' COMMENT "' . $parameters['Comment'] . '"' : '')
144
                . ';'
145
                . (isset($aditionalFeatures['includeOldColumnType']) ? ' /* from '
146
                        . $parameters['OldDataType'] . ' */' : '')
147
                . '</div>';
148
    }
149
150
    private function createDropForeignKey($parameters)
151
    {
152
        return '<div style="color:red;">'
153
                . 'ALTER TABLE `' . $parameters['Database'] . '`.`' . $parameters['Table']
154
                . '` DROP FOREIGN KEY `' . $parameters['ForeignKeyName']
155
                . '`;'
156
                . '</div>';
157
    }
158
159
    private function createDropForeignKeysAndGetTargetColumnDefinition($targetTableTextFlds)
160
    {
161
        $sReturn = [];
162
        foreach ($targetTableTextFlds as $key => $value) {
163
            $sReturn[]                                    = $this->createDropForeignKey([
164
                'Database'       => $value['TABLE_SCHEMA'],
165
                'Table'          => $value['TABLE_NAME'],
166
                'ForeignKeyName' => $value['CONSTRAINT_NAME'],
167
            ]);
168
            $this->applicationSpecificArray['Cols'][$key] = $this->getMySQLlistColumns([
169
                'TABLE_SCHEMA' => $value['TABLE_SCHEMA'],
170
                'TABLE_NAME'   => $value['TABLE_NAME'],
171
                'COLUMN_NAME'  => $value['COLUMN_NAME'],
172
            ]);
173
        }
174
        return implode('', $sReturn);
175
    }
176
177
    private function createForeignKey($parameters)
178
    {
179
        return '<div style="color:green;">'
180
                . 'ALTER TABLE `' . $parameters['Database'] . '`.`' . $parameters['Table']
181
                . '` ADD CONSTRAINT `' . $parameters['ForeignKeyName'] . '` FOREIGN KEY (`'
182
                . $parameters['Column'] . '`) REFERENCES `' . $parameters['ReferencedDatabase'] . '`.`'
183
                . $parameters['ReferencedTable'] . '` (`' . $parameters['ReferencedColumn'] . '`) '
184
                . 'ON DELETE '
185
                . ($parameters['RuleDelete'] == 'NULL' ? 'SET NULL' : $parameters['RuleDelete']) . ' '
186
                . 'ON UPDATE '
187
                . ($parameters['RuleUpdate'] == 'NULL' ? 'SET NULL' : $parameters['RuleUpdate'])
188
                . ';'
189
                . '</div>';
190
    }
191
192
    private function getForeignKeys($elToModify)
193
    {
194
        $additionalFeatures = [
195
            'REFERENCED_TABLE_SCHEMA' => $elToModify['Database'],
196
            'REFERENCED_TABLE_NAME'   => $elToModify['Table'],
197
            'REFERENCED_COLUMN_NAME'  => $elToModify['Column'],
198
            'REFERENCED_TABLE_NAME'   => 'NOT NULL',
199
        ];
200
        $query              = $this->sQueryMySqlIndexes($additionalFeatures);
201
        return $this->setMySQLquery2Server($query, 'full_array_key_numbered')['result'];
202
    }
203
204
    private function packParameteresForMainChangeColumn($elToModify, $targetTableTextFlds)
205
    {
206
        $colToIdentify = [
207
            'TABLE_SCHEMA' => $elToModify['Database'],
208
            'TABLE_NAME'   => $elToModify['Table'],
209
            'COLUMN_NAME'  => $elToModify['Column'],
210
        ];
211
        $col           = $this->getMySQLlistColumns($colToIdentify);
212
        return [
213
            'Database'    => $targetTableTextFlds[0]['REFERENCED_TABLE_SCHEMA'],
214
            'Table'       => $targetTableTextFlds[0]['REFERENCED_TABLE_NAME'],
215
            'Column'      => $targetTableTextFlds[0]['REFERENCED_COLUMN_NAME'],
216
            'OldDataType' => strtoupper($col[0]['COLUMN_TYPE']) . ' '
217
            . $this->setColumnDefinitionAditional($col[0]['IS_NULLABLE'], $col[0]['COLUMN_DEFAULT'], $col[0]['EXTRA']),
218
            'NewDataType' => $elToModify['NewDataType'],
219
            'IsNullable'  => $col[0]['IS_NULLABLE'],
220
            'Default'     => $col[0]['COLUMN_DEFAULT'],
221
            'Extra'       => $col[0]['EXTRA'],
222
            'Comment'     => $col[0]['COLUMN_COMMENT'],
223
        ];
224
    }
225
226
    private function recreateFKs($elToModify, $targetTableTextFlds)
227
    {
228
        $sReturn = [];
229
        foreach ($targetTableTextFlds as $key => $value) {
230
            $sReturn[] = $this->createChangeColumn([
231
                'Database'    => $value['TABLE_SCHEMA'],
232
                'Table'       => $value['TABLE_NAME'],
233
                'Column'      => $value['COLUMN_NAME'],
234
                'NewDataType' => $elToModify['NewDataType'],
235
                'IsNullable'  => $this->applicationSpecificArray['Cols'][$key][0]['IS_NULLABLE'],
236
                'Default'     => $this->applicationSpecificArray['Cols'][$key][0]['COLUMN_DEFAULT'],
237
                'Extra'       => $this->applicationSpecificArray['Cols'][$key][0]['EXTRA'],
238
                'Comment'     => $this->applicationSpecificArray['Cols'][$key][0]['COLUMN_COMMENT'],
239
            ]);
240
            $sReturn[] = $this->createForeignKey([
241
                'Database'           => $value['TABLE_SCHEMA'],
242
                'Table'              => $value['TABLE_NAME'],
243
                'Column'             => $value['COLUMN_NAME'],
244
                'ForeignKeyName'     => $value['CONSTRAINT_NAME'],
245
                'ReferencedDatabase' => $value['REFERENCED_TABLE_SCHEMA'],
246
                'ReferencedTable'    => $value['REFERENCED_TABLE_NAME'],
247
                'ReferencedColumn'   => $value['REFERENCED_COLUMN_NAME'],
248
                'RuleDelete'         => $value['DELETE_RULE'],
249
                'RuleUpdate'         => $value['UPDATE_RULE'],
250
            ]);
251
        }
252
        return implode('', $sReturn);
253
    }
254
255
    private function returnInputsCleaned($inputFieldName, $inArray)
256
    {
257
        $sReturn = '';
258
        if (!is_null($inArray['SuperGlobals']->get($inputFieldName))) {
259
            $sReturn = 'value="'
260
                    . filter_var($inArray['SuperGlobals']->get($inputFieldName), FILTER_SANITIZE_STRING) . '" ';
261
        }
262
        return $sReturn;
263
    }
264
265
    private function setApplicationHeader()
266
    {
267
        $pageTitle   = 'Foreign Keys Scale in MySQL';
268
        $headerArray = [
269
            'css'        => [
270
                'css/fk_scale_mysql.css',
271
            ],
272
            'javascript' => [
273
                'vendor/danielgp/common-lib/js/tabber/tabber-management.min.js',
274
                'vendor/danielgp/common-lib/js/tabber/tabber.min.js',
275
            ],
276
            'lang'       => 'en-US',
277
            'title'      => $pageTitle,
278
        ];
279
        return $this->setHeaderCommon($headerArray)
280
                . '<h1>' . $pageTitle . '</h1>';
281
    }
282
283
    private function setColumnDefinitionAditional($nullableYesNo, $defaultValue = '', $extra = '')
284
    {
285
        switch ($nullableYesNo) {
286
            case 'NO':
287
                $columnDefAdtnl = 'NOT NULL DEFAULT "' . $defaultValue . '"';
288
                if (is_null($defaultValue)) {
289
                    $columnDefAdtnl = 'NOT NULL';
290
                }
291
                break;
292
            case 'YES':
293
                $columnDefAdtnl = 'DEFAULT "' . $defaultValue . '"';
294
                if ($defaultValue === null) {
295
                    $columnDefAdtnl = 'DEFAULT NULL';
296
                }
297
                break;
298
        }
299
        if ($extra == 'auto_increment') {
300
            $columnDefAdtnl .= ' AUTO_INCREMENT';
0 ignored issues
show
The variable $columnDefAdtnl does not seem to be defined for all execution paths leading up to this point.

If you define a variable conditionally, it can happen that it is not defined for all execution paths.

Let’s take a look at an example:

function myFunction($a) {
    switch ($a) {
        case 'foo':
            $x = 1;
            break;

        case 'bar':
            $x = 2;
            break;
    }

    // $x is potentially undefined here.
    echo $x;
}

In the above example, the variable $x is defined if you pass “foo” or “bar” as argument for $a. However, since the switch statement has no default case statement, if you pass any other value, the variable $x would be undefined.

Available Fixes

  1. Check for existence of the variable explicitly:

    function myFunction($a) {
        switch ($a) {
            case 'foo':
                $x = 1;
                break;
    
            case 'bar':
                $x = 2;
                break;
        }
    
        if (isset($x)) { // Make sure it's always set.
            echo $x;
        }
    }
    
  2. Define a default value for the variable:

    function myFunction($a) {
        $x = ''; // Set a default which gets overridden for certain paths.
        switch ($a) {
            case 'foo':
                $x = 1;
                break;
    
            case 'bar':
                $x = 2;
                break;
        }
    
        echo $x;
    }
    
  3. Add a value for the missing path:

    function myFunction($a) {
        switch ($a) {
            case 'foo':
                $x = 1;
                break;
    
            case 'bar':
                $x = 2;
                break;
    
            // We add support for the missing case.
            default:
                $x = '';
                break;
        }
    
        echo $x;
    }
    
Loading history...
301
        }
302
        return $columnDefAdtnl;
303
    }
304
}
305