MySQLiByDanielGPqueries::sQueryToDeleteSingleIdentifier() - Code Metrics - Inspection of "applied a better normalization" - danielgp/common-lib - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( bbb9f2...aa806b )

by Daniel

created 2016-01-15 20:24 UTC

sQueryToDeleteSingleIdentifier() A

↳ Parent: MySQLiByDanielGPqueries

Complexity

Conditions	1
Paths	1

Size

Total Lines	7
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Importance

Changes	4
Bugs	0	Features	0

Metric	Value
c	4
b	0
f	0
dl	0
loc	7
rs	9.4285
cc	1
eloc	5
nc	1
nop	1

<?php

/**
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2015 Daniel Popiniuc
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 *  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 *
 */

namespace danielgp\common_lib;

/**
 * Queries for the MySQL module
 *
 * @author Daniel Popiniuc
 */
trait MySQLiByDanielGPqueries
{

    private function sCleanParameters(&$parameters)
    {
        if (is_array($parameters)) {
            $tmpArray = [];
            foreach ($parameters as &$value) {
                $tmpArray[] = filter_var($value, FILTER_SANITIZE_STRING);
            }
            $parameters = $tmpArray;
        } else {
            $parameters = filter_var($parameters, FILTER_SANITIZE_STRING);
        }
    }

    /**
     * Internal function to manage concatenation for filters
     *
     * @param type $filterValue
     * @return string
     */
    private function sGlueFilterValueIntoWhereString($filterValue)
    {
        if (is_array($filterValue)) {
            return 'IN ("' . implode('", "', $filterValue) . '")';
        }
        $kFields = [
            'CONNECTION_ID()|CURDATE()|CURRENT_USER|CURRENT_USER()|CURRENT_DATETIME|DATABASE()|NOW()|USER()',
            'IS NULL|IS NOT NULL',
            'NOT NULL|NULL',
        ];
        if (in_array($filterValue, explode('|', $kFields[0]))) {
            return '= ' . $filterValue;
        } elseif (in_array($filterValue, explode('|', $kFields[1]))) {
            return $filterValue;
        } elseif (in_array($filterValue, explode('|', $kFields[2]))) {
            return 'IS ' . $filterValue;
        }
        return '= "' . $filterValue . '"';
    }

    /**
     * Internal function to concatenate filters
     *
     * @param array $filters
     * @return type
     */
    private function sGlueFiltersIntoWhereArrayFilter($filters)
    {
        return '(' . implode(') AND (', $filters) . ')';
    }

    /**
     * Internal function to manage the filters passed to the query
     *
     * @param array $filterArray
     * @param string $tableToApplyFilterTo
     * @return string
     */
    private function sManageDynamicFilters($filterArray = null, $tableToApplyFilterTo = '')
    {
        $filters = [];
        if (!is_null($filterArray) && is_array($filterArray)) {
            foreach ($filterArray as $key => $value) {
                $filters[] = '`' . $tableToApplyFilterTo . '`.`' . $key . '` '
                        . $this->sGlueFilterValueIntoWhereString($value);
            }
        }
        if (count($filters) > 0) {
            return 'WHERE ' . $this->sGlueFiltersIntoWhereArrayFilter($filters) . ' ';
        }
        return '';
    }

    /**
     * Query to list Databases
     *
     * @param type $excludeSystemDbs
     * @return type
     */
    protected function sQueryMySqlActiveDatabases($excludeSystemDbs = true)
    {
        $stdDbs = ['information_schema', 'mysql', 'performance_schema', 'sys'];
        return 'SELECT '
                . '`SCHEMA_NAME` As `Db`, '
                . '`DEFAULT_CHARACTER_SET_NAME` AS `DbCharset`, '
                . '`DEFAULT_COLLATION_NAME` AS `DbCollation` '
                . 'FROM `information_schema`.`SCHEMATA` '
                . ($excludeSystemDbs ? 'WHERE `SCHEMA_NAME` NOT IN ("' . implode('", "', $stdDbs) . '") ' : '')
                . 'GROUP BY `SCHEMA_NAME`;';
    }

    /**
     * Query to list MySQL engines
     *
     * @param string $onlyActiveOnes
     * @return type
     */
    protected function sQueryMySqlActiveEngines($onlyActiveOnes = true)
    {
        return 'SELECT '
                . '`ENGINE` AS `Engine`, '
                . '`SUPPORT` AS `Support`, '
                . '`COMMENT` AS `Comment` '
                . 'FROM `information_schema`.`ENGINES` '
                . ($onlyActiveOnes ? 'WHERE (`SUPPORT` IN ("DEFAULT", "YES")) ' : '')
                . 'GROUP BY `ENGINE`;';
    }

    protected function sQueryMySqlColumns($filterArray = null)
    {
        return 'SELECT `C`.`TABLE_SCHEMA` '
                . ', `C`.`TABLE_NAME` '
                . ', `C`.`COLUMN_NAME` '
                . ', `C`.`ORDINAL_POSITION` '
                . ', `C`.`COLUMN_DEFAULT` '
                . ', `C`.`IS_NULLABLE` '
                . ', `C`.`DATA_TYPE` '
                . ', `C`.`CHARACTER_MAXIMUM_LENGTH` '
                . ', `C`.`NUMERIC_PRECISION` '
                . ', `C`.`NUMERIC_SCALE` '
                . ', `C`.`DATETIME_PRECISION` '
                . ', `C`.`CHARACTER_SET_NAME` '
                . ', `C`.`COLLATION_NAME` '
                . ', `C`.`COLUMN_TYPE` '
                . ', `C`.`COLUMN_KEY` '
                . ', `C`.`COLUMN_COMMENT` '
                . ', `C`.`EXTRA` '
                . 'FROM `information_schema`.`COLUMNS` `C` '
                . 'LEFT JOIN `information_schema`.`KEY_COLUMN_USAGE` `KCU` ON ((' . implode(') AND (', [
                    '`C`.`TABLE_SCHEMA` = `KCU`.`TABLE_SCHEMA`',
                    '`C`.`TABLE_NAME` = `KCU`.`TABLE_NAME`',
                    '`C`.`COLUMN_NAME` = `KCU`.`COLUMN_NAME`',
                ]) . ')) '
                . $this->sManageDynamicFilters($filterArray, 'C')
                . 'GROUP BY `C`.`TABLE_SCHEMA`, `C`.`TABLE_NAME`, `C`.`COLUMN_NAME` '
                . 'ORDER BY `C`.`TABLE_SCHEMA`, `C`.`TABLE_NAME`, `C`.`ORDINAL_POSITION`;';
    }

    protected function sQueryGenericSelectKeyValue($parameters)
    {
        $this->sCleanParameters($parameters);
        return 'SELECT' . $parameters[0] . ',' . $parameters[1] . 'FROM' . $parameters[2]
                . 'GROUP BY' . $parameters[1] . ';';
    }

    /**
     * Query to list Global Variables
     *
     * @return string
     */
    protected function sQueryMySqlGlobalVariables()
    {
        return 'SHOW GLOBAL VARIABLES;';
    }

    /**
     * Query
     *
     * @param array $filterArray
     * @return string
     */
    protected function sQueryMySqlIndexes($filterArray = null)
    {
        return 'SELECT `KCU`.`CONSTRAINT_SCHEMA` '
                . ', `KCU`.`CONSTRAINT_NAME` '
                . ', `KCU`.`TABLE_SCHEMA` '
                . ', `KCU`.`TABLE_NAME` '
                . ', `KCU`.`COLUMN_NAME` '
                . ', `C`.`ORDINAL_POSITION` AS `COLUMN_POSITION` '
                . ', `KCU`.`ORDINAL_POSITION` '
                . ', `KCU`.`POSITION_IN_UNIQUE_CONSTRAINT` '
                . ', `KCU`.`REFERENCED_TABLE_SCHEMA` '
                . ', `KCU`.`REFERENCED_TABLE_NAME` '
                . ', `KCU`.`REFERENCED_COLUMN_NAME` '
                . ', `RC`.`UPDATE_RULE` '
                . ', `RC`.`DELETE_RULE` '
                . 'FROM `information_schema`.`KEY_COLUMN_USAGE` `KCU` '
                . 'INNER JOIN `information_schema`.`COLUMNS` `C` ON ((' . implode(') AND (', [
                    '`C`.`TABLE_SCHEMA` = `KCU`.`TABLE_SCHEMA`',
                    '`C`.`TABLE_NAME` = `KCU`.`TABLE_NAME`',
                    '`C`.`COLUMN_NAME` = `KCU`.`COLUMN_NAME`',
                ]) . ')) '
                . 'LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` `RC` ON ((' . implode(') AND (', [
                    '`KCU`.`CONSTRAINT_SCHEMA` = `RC`.`CONSTRAINT_SCHEMA`',
                    '`KCU`.`CONSTRAINT_NAME` = `RC`.`CONSTRAINT_NAME`',
                ]) . ')) '
                . $this->sManageDynamicFilters($filterArray, 'KCU')
                . 'ORDER BY `KCU`.`TABLE_SCHEMA`, `KCU`.`TABLE_NAME`'
                . $this->xtraSoring($filterArray, 'COLUMN_NAME') . ';';
    }

    /**
     * The MySQL server time
     *
     * @return string
     */
    protected function sQueryMySqlServerTime()
    {
        return 'SELECT NOW();';
    }

    protected function sQueryMySqlStatisticTSFSPE()
    {
        return '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`TRIGGERS` `T` '
                . 'WHERE (`T`.`EVENT_OBJECT_SCHEMA` = `S`.`SCHEMA_NAME`)) '
                . 'AS `Triggers`, '
                . '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`ROUTINES` `R` '
                . 'WHERE (`R`.`ROUTINE_SCHEMA` = `S`.`SCHEMA_NAME`) AND (`R`.`ROUTINE_TYPE` = "Function")) '
                . 'AS `Stored Functions`, '
                . '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`ROUTINES` `R` '
                . 'WHERE (`R`.`ROUTINE_SCHEMA` = `S`.`SCHEMA_NAME`) AND (`R`.`ROUTINE_TYPE` = "Procedure")) '
                . 'AS `Stored Procedure`, '
                . '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`EVENTS` `E` '
                . 'WHERE (`E`.`EVENT_SCHEMA` = `S`.`SCHEMA_NAME`)) '
                . 'AS `Events` ';
    }

    protected function sQueryMySqlStatisticTVC()
    {
        return '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`TABLES` `T` '
                . 'WHERE (`T`.`TABLE_SCHEMA` = `S`.`SCHEMA_NAME`) AND (`T`.`TABLE_TYPE` = "BASE TABLE")) '
                . 'AS `Tables`, '
                . '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`TABLES` `T` '
                . 'WHERE (`T`.`TABLE_SCHEMA` = `S`.`SCHEMA_NAME`) AND (`T`.`TABLE_TYPE` = "VIEW")) '
                . 'AS `Views`, '
                . '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`COLUMNS` `C` '
                . 'WHERE (`C`.`TABLE_SCHEMA` = `S`.`SCHEMA_NAME`)) '
                . 'AS `Columns`, ';
    }

    protected function sQueryMySqlStatistics($filterArray = null)
    {
        return 'SELECT '
                . '`S`.`SCHEMA_NAME`, '
                . $this->sQueryMySqlStatisticTVC()
                . $this->sQueryMySqlStatisticTSFSPE()
                . 'FROM `information_schema`.`SCHEMATA` `S` '
                . 'WHERE (`S`.`SCHEMA_NAME` NOT IN ("information_schema", "mysql", "performance_schema", "sys")) '
                . str_replace('WHERE|AND', $this->sManageDynamicFilters($filterArray, 'S'))
                . 'ORDER BY `S`.`SCHEMA_NAME`;';
    }

    /**
     * Query to get list of tables
     *
     * @param type $filterArray
     * @return string
     */
    protected function sQueryMySqlTables($filterArray = null)
    {
        return 'SELECT `T`.`TABLE_SCHEMA` '
                . ', `T`.`TABLE_NAME` '
                . ', `T`.`TABLE_TYPE` '
                . ', `T`.`ENGINE` '
                . ', `T`.`VERSION` '
                . ', `T`.`ROW_FORMAT` '
                . ', `T`.`AUTO_INCREMENT` '
                . ', `T`.`TABLE_COLLATION` '
                . ', `T`.`CREATE_TIME` '
                . ', `T`.`CREATE_OPTIONS` '
                . ', `T`.`TABLE_COMMENT` '
                . 'FROM `information_schema`.`TABLES` `T` '
                . $this->sManageDynamicFilters($filterArray, 'T')
                . $this->xtraSoring($filterArray, 'TABLE_SCHEMA')
                . ';';
    }

    /**
     * Get all the row details from a table based on given filter
     *
     * @param array $parameters
     * @return string
     */
    protected function sQueryRowsFromTable($parameters)
    {
        $this->sCleanParameters($parameters);
        return 'SELECT * '
                . 'FROM `' . $parameters[0] . '` '
                . 'WHERE ' . $parameters[1] . ';';
    }

    protected function sQueryToDeleteSingleIdentifier($parameters)
    {
        $this->sCleanParameters($parameters);
        return 'DELETE '
                . 'FROM `' . $parameters[0] . '` '
                . 'WHERE `' . $parameters[1] . '` = "' . $parameters[2] . '";';
    }

    private function xtraSoring($filterArray, $filterValueToDecide)
    {
        $defaults = [
            'COLUMN_NAME'  => [
                ', `C`.`ORDINAL_POSITION`, `KCU`.`CONSTRAINT_NAME`',
                '',
            ],
            'TABLE_SCHEMA' => [
                'ORDER BY `T`.`TABLE_SCHEMA`, `T`.`TABLE_NAME`',
                'ORDER BY `T`.`TABLE_NAME`',
            ],
        ];
        if (!is_null($filterArray) && is_array($filterArray) && array_key_exists($filterValueToDecide, $filterArray)) {
            return $defaults[$filterValueToDecide][1];
        }
        return $defaults[$filterValueToDecide][0];
    }
}


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\common_lib;
30
31			/**
32			* Queries for the MySQL module
33			*
34			* @author Daniel Popiniuc
35			*/
36			trait MySQLiByDanielGPqueries
37			{
38
39			private function sCleanParameters(&$parameters)
40			{
41			if (is_array($parameters)) {
42			$tmpArray = [];
43			foreach ($parameters as &$value) {
44			$tmpArray[] = filter_var($value, FILTER_SANITIZE_STRING);
45			}
46			$parameters = $tmpArray;
47			} else {
48			$parameters = filter_var($parameters, FILTER_SANITIZE_STRING);
49			}
50			}
51
52			/**
53			* Internal function to manage concatenation for filters
54			*
55			* @param type $filterValue
56			* @return string
57			*/
58			private function sGlueFilterValueIntoWhereString($filterValue)
59			{
60			if (is_array($filterValue)) {
61			return 'IN ("' . implode('", "', $filterValue) . '")';
62			}
63			$kFields = [
64			'CONNECTION_ID()\|CURDATE()\|CURRENT_USER\|CURRENT_USER()\|CURRENT_DATETIME\|DATABASE()\|NOW()\|USER()',
65			'IS NULL\|IS NOT NULL',
66			'NOT NULL\|NULL',
67			];
68			if (in_array($filterValue, explode('\|', $kFields[0]))) {
69			return '= ' . $filterValue;
70			} elseif (in_array($filterValue, explode('\|', $kFields[1]))) {
71			return $filterValue;
72			} elseif (in_array($filterValue, explode('\|', $kFields[2]))) {
73			return 'IS ' . $filterValue;
74			}
75			return '= "' . $filterValue . '"';
76			}
77
78			/**
79			* Internal function to concatenate filters
80			*
81			* @param array $filters
82			* @return type
83			*/
84			private function sGlueFiltersIntoWhereArrayFilter($filters)
85			{
86			return '(' . implode(') AND (', $filters) . ')';
87			}
88
89			/**
90			* Internal function to manage the filters passed to the query
91			*
92			* @param array $filterArray
93			* @param string $tableToApplyFilterTo
94			* @return string
95			*/
96			private function sManageDynamicFilters($filterArray = null, $tableToApplyFilterTo = '')
97			{
98			$filters = [];
99			if (!is_null($filterArray) && is_array($filterArray)) {
100			foreach ($filterArray as $key => $value) {
101			$filters[] = '`' . $tableToApplyFilterTo . '`.`' . $key . '` '
102			. $this->sGlueFilterValueIntoWhereString($value);
103			}
104			}
105			if (count($filters) > 0) {
106			return 'WHERE ' . $this->sGlueFiltersIntoWhereArrayFilter($filters) . ' ';
107			}
108			return '';
109			}
110
111			/**
112			* Query to list Databases
113			*
114			* @param type $excludeSystemDbs
115			* @return type
116			*/
117			protected function sQueryMySqlActiveDatabases($excludeSystemDbs = true)
118			{
119			$stdDbs = ['information_schema', 'mysql', 'performance_schema', 'sys'];
120			return 'SELECT '
121			. '`SCHEMA_NAME` As `Db`, '
122			. '`DEFAULT_CHARACTER_SET_NAME` AS `DbCharset`, '
123			. '`DEFAULT_COLLATION_NAME` AS `DbCollation` '
124			. 'FROM `information_schema`.`SCHEMATA` '
125			. ($excludeSystemDbs ? 'WHERE `SCHEMA_NAME` NOT IN ("' . implode('", "', $stdDbs) . '") ' : '')
126			. 'GROUP BY `SCHEMA_NAME`;';
127			}
128
129			/**
130			* Query to list MySQL engines
131			*
132			* @param string $onlyActiveOnes
133			* @return type
134			*/
135			protected function sQueryMySqlActiveEngines($onlyActiveOnes = true)
136			{
137			return 'SELECT '
138			. '`ENGINE` AS `Engine`, '
139			. '`SUPPORT` AS `Support`, '
140			. '`COMMENT` AS `Comment` '
141			. 'FROM `information_schema`.`ENGINES` '
142			. ($onlyActiveOnes ? 'WHERE (`SUPPORT` IN ("DEFAULT", "YES")) ' : '')
143			. 'GROUP BY `ENGINE`;';
144			}
145
146			protected function sQueryMySqlColumns($filterArray = null)
147			{
148			return 'SELECT `C`.`TABLE_SCHEMA` '
149			. ', `C`.`TABLE_NAME` '
150			. ', `C`.`COLUMN_NAME` '
151			. ', `C`.`ORDINAL_POSITION` '
152			. ', `C`.`COLUMN_DEFAULT` '
153			. ', `C`.`IS_NULLABLE` '
154			. ', `C`.`DATA_TYPE` '
155			. ', `C`.`CHARACTER_MAXIMUM_LENGTH` '
156			. ', `C`.`NUMERIC_PRECISION` '
157			. ', `C`.`NUMERIC_SCALE` '
158			. ', `C`.`DATETIME_PRECISION` '
159			. ', `C`.`CHARACTER_SET_NAME` '
160			. ', `C`.`COLLATION_NAME` '
161			. ', `C`.`COLUMN_TYPE` '
162			. ', `C`.`COLUMN_KEY` '
163			. ', `C`.`COLUMN_COMMENT` '
164			. ', `C`.`EXTRA` '
165			. 'FROM `information_schema`.`COLUMNS` `C` '
166			. 'LEFT JOIN `information_schema`.`KEY_COLUMN_USAGE` `KCU` ON ((' . implode(') AND (', [
167			'`C`.`TABLE_SCHEMA` = `KCU`.`TABLE_SCHEMA`',
168			'`C`.`TABLE_NAME` = `KCU`.`TABLE_NAME`',
169			'`C`.`COLUMN_NAME` = `KCU`.`COLUMN_NAME`',
170			]) . ')) '
171			. $this->sManageDynamicFilters($filterArray, 'C')
172			. 'GROUP BY `C`.`TABLE_SCHEMA`, `C`.`TABLE_NAME`, `C`.`COLUMN_NAME` '
173			. 'ORDER BY `C`.`TABLE_SCHEMA`, `C`.`TABLE_NAME`, `C`.`ORDINAL_POSITION`;';
174			}
175
176			protected function sQueryGenericSelectKeyValue($parameters)
177			{
178			$this->sCleanParameters($parameters);
179			return 'SELECT' . $parameters[0] . ',' . $parameters[1] . 'FROM' . $parameters[2]
180			. 'GROUP BY' . $parameters[1] . ';';
181			}
182
183			/**
184			* Query to list Global Variables
185			*
186			* @return string
187			*/
188			protected function sQueryMySqlGlobalVariables()
189			{
190			return 'SHOW GLOBAL VARIABLES;';
191			}
192
193			/**
194			* Query
195			*
196			* @param array $filterArray
197			* @return string
198			*/
199			protected function sQueryMySqlIndexes($filterArray = null)
200			{
201			return 'SELECT `KCU`.`CONSTRAINT_SCHEMA` '
202			. ', `KCU`.`CONSTRAINT_NAME` '
203			. ', `KCU`.`TABLE_SCHEMA` '
204			. ', `KCU`.`TABLE_NAME` '
205			. ', `KCU`.`COLUMN_NAME` '
206			. ', `C`.`ORDINAL_POSITION` AS `COLUMN_POSITION` '
207			. ', `KCU`.`ORDINAL_POSITION` '
208			. ', `KCU`.`POSITION_IN_UNIQUE_CONSTRAINT` '
209			. ', `KCU`.`REFERENCED_TABLE_SCHEMA` '
210			. ', `KCU`.`REFERENCED_TABLE_NAME` '
211			. ', `KCU`.`REFERENCED_COLUMN_NAME` '
212			. ', `RC`.`UPDATE_RULE` '
213			. ', `RC`.`DELETE_RULE` '
214			. 'FROM `information_schema`.`KEY_COLUMN_USAGE` `KCU` '
215			. 'INNER JOIN `information_schema`.`COLUMNS` `C` ON ((' . implode(') AND (', [
216			'`C`.`TABLE_SCHEMA` = `KCU`.`TABLE_SCHEMA`',
217			'`C`.`TABLE_NAME` = `KCU`.`TABLE_NAME`',
218			'`C`.`COLUMN_NAME` = `KCU`.`COLUMN_NAME`',
219			]) . ')) '
220			. 'LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` `RC` ON ((' . implode(') AND (', [
221			'`KCU`.`CONSTRAINT_SCHEMA` = `RC`.`CONSTRAINT_SCHEMA`',
222			'`KCU`.`CONSTRAINT_NAME` = `RC`.`CONSTRAINT_NAME`',
223			]) . ')) '
224			. $this->sManageDynamicFilters($filterArray, 'KCU')
225			. 'ORDER BY `KCU`.`TABLE_SCHEMA`, `KCU`.`TABLE_NAME`'
226			. $this->xtraSoring($filterArray, 'COLUMN_NAME') . ';';
227			}
228
229			/**
230			* The MySQL server time
231			*
232			* @return string
233			*/
234			protected function sQueryMySqlServerTime()
235			{
236			return 'SELECT NOW();';
237			}
238
239			protected function sQueryMySqlStatisticTSFSPE()
240			{
241			return '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`TRIGGERS` `T` '
242			. 'WHERE (`T`.`EVENT_OBJECT_SCHEMA` = `S`.`SCHEMA_NAME`)) '
243			. 'AS `Triggers`, '
244			. '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`ROUTINES` `R` '
245			. 'WHERE (`R`.`ROUTINE_SCHEMA` = `S`.`SCHEMA_NAME`) AND (`R`.`ROUTINE_TYPE` = "Function")) '
246			. 'AS `Stored Functions`, '
247			. '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`ROUTINES` `R` '
248			. 'WHERE (`R`.`ROUTINE_SCHEMA` = `S`.`SCHEMA_NAME`) AND (`R`.`ROUTINE_TYPE` = "Procedure")) '
249			. 'AS `Stored Procedure`, '
250			. '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`EVENTS` `E` '
251			. 'WHERE (`E`.`EVENT_SCHEMA` = `S`.`SCHEMA_NAME`)) '
252			. 'AS `Events` ';
253			}
254
255			protected function sQueryMySqlStatisticTVC()
256			{
257			return '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`TABLES` `T` '
258			. 'WHERE (`T`.`TABLE_SCHEMA` = `S`.`SCHEMA_NAME`) AND (`T`.`TABLE_TYPE` = "BASE TABLE")) '
259			. 'AS `Tables`, '
260			. '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`TABLES` `T` '
261			. 'WHERE (`T`.`TABLE_SCHEMA` = `S`.`SCHEMA_NAME`) AND (`T`.`TABLE_TYPE` = "VIEW")) '
262			. 'AS `Views`, '
263			. '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`COLUMNS` `C` '
264			. 'WHERE (`C`.`TABLE_SCHEMA` = `S`.`SCHEMA_NAME`)) '
265			. 'AS `Columns`, ';
266			}
267
268			protected function sQueryMySqlStatistics($filterArray = null)
269			{
270			return 'SELECT '
271			. '`S`.`SCHEMA_NAME`, '
272			. $this->sQueryMySqlStatisticTVC()
273			. $this->sQueryMySqlStatisticTSFSPE()
274			. 'FROM `information_schema`.`SCHEMATA` `S` '
275			. 'WHERE (`S`.`SCHEMA_NAME` NOT IN ("information_schema", "mysql", "performance_schema", "sys")) '
276			. str_replace('WHERE\|AND', $this->sManageDynamicFilters($filterArray, 'S'))
277			. 'ORDER BY `S`.`SCHEMA_NAME`;';
278			}
279
280			/**
281			* Query to get list of tables
282			*
283			* @param type $filterArray
284			* @return string
285			*/
286			protected function sQueryMySqlTables($filterArray = null)
287			{
288			return 'SELECT `T`.`TABLE_SCHEMA` '
289			. ', `T`.`TABLE_NAME` '
290			. ', `T`.`TABLE_TYPE` '
291			. ', `T`.`ENGINE` '
292			. ', `T`.`VERSION` '
293			. ', `T`.`ROW_FORMAT` '
294			. ', `T`.`AUTO_INCREMENT` '
295			. ', `T`.`TABLE_COLLATION` '
296			. ', `T`.`CREATE_TIME` '
297			. ', `T`.`CREATE_OPTIONS` '
298			. ', `T`.`TABLE_COMMENT` '
299			. 'FROM `information_schema`.`TABLES` `T` '
300			. $this->sManageDynamicFilters($filterArray, 'T')
301			. $this->xtraSoring($filterArray, 'TABLE_SCHEMA')
302			. ';';
303			}
304
305			/**
306			* Get all the row details from a table based on given filter
307			*
308			* @param array $parameters
309			* @return string
310			*/
311			protected function sQueryRowsFromTable($parameters)
312			{
313			$this->sCleanParameters($parameters);
314			return 'SELECT * '
315			. 'FROM `' . $parameters[0] . '` '
316			. 'WHERE ' . $parameters[1] . ';';
317			}
318
319			protected function sQueryToDeleteSingleIdentifier($parameters)
320			{
321			$this->sCleanParameters($parameters);
322			return 'DELETE '
323			. 'FROM `' . $parameters[0] . '` '
324			. 'WHERE `' . $parameters[1] . '` = "' . $parameters[2] . '";';
325			}
326
327			private function xtraSoring($filterArray, $filterValueToDecide)
328			{
329			$defaults = [
330			'COLUMN_NAME' => [
331			', `C`.`ORDINAL_POSITION`, `KCU`.`CONSTRAINT_NAME`',
332			'',
333			],
334			'TABLE_SCHEMA' => [
335			'ORDER BY `T`.`TABLE_SCHEMA`, `T`.`TABLE_NAME`',
336			'ORDER BY `T`.`TABLE_NAME`',
337			],
338			];
339			if (!is_null($filterArray) && is_array($filterArray) && array_key_exists($filterValueToDecide, $filterArray)) {
340			return $defaults[$filterValueToDecide][1];
341			}
342			return $defaults[$filterValueToDecide][0];
343			}
344			}
345

danielgp / common-lib

Push — master ( bbb9f2...aa806b )

sQueryToDeleteSingleIdentifier() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like