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
|
|||
301 | } |
||
302 | return $columnDefAdtnl; |
||
303 | } |
||
304 | } |
||
305 |
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:
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
Check for existence of the variable explicitly:
Define a default value for the variable:
Add a value for the missing path: