MySQLiByDanielGPqueries::sQueryMySqlStatisticPattern() - Code Metrics - Inspection of "MySQL statistics method was completely rewritten t..." - danielgp/common-lib - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 8c3828...8dbe44 )

by Daniel

created 2016-01-15 22:21 UTC

sQueryMySqlStatisticPattern() A

↳ Parent: MySQLiByDanielGPqueries

Complexity

Conditions	3
Paths	2

Size

Total Lines	8
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

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

<?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) . '")';
        }
        return $this->sGlueFilterValueIntoWhereStringFinal($filterValue);
    }

    private function sGlueFilterValueIntoWhereStringFinal($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);
            }
        }
        return $this->sManageDynamicFiltersFinal($filters);
    }

    private function sManageDynamicFiltersFinal($filters)
    {
        if (count($filters) > 0) {
            $sReturn = ['WHERE', $this->sGlueFiltersIntoWhereArrayFilter($filters)];
            return implode(' ', $sReturn) . ' ';
        }
        return '';
    }

    /**
     * Query to list Databases
     *
     * @param type $excludeSystemDbs
     * @return type
     */
    protected function sQueryMySqlActiveDatabases($excludeSystemDbs = true)
    {
        $sDBs = 'WHERE '
                . '`SCHEMA_NAME` NOT IN ("'
                . implode('", "', ['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 ? $sDBs : '')
                . '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`, '
                . $this->sQueryMySqlColumnsColumns() . ' '
                . '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 sQueryMySqlColumnsColumns()
    {
        return '`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`';
    }

    protected function sQueryGenericSelectKeyValue($parameters)
    {
        $this->sCleanParameters($parameters);
        return implode(' ', [
            '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`, '
                . $this->sQueryMySqlIndexesColumns() . ' '
                . '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') . ';';
    }

    protected function sQueryMySqlIndexesColumns()
    {
        return '`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`';
    }

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

    private function sQueryMySqlStatisticPattern($tblName, $lnkDbCol, $adtnlCol = null, $adtnlFltr = null)
    {
        $tblAls = substr($tblName, 0, 1);
        return '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`' . $tblName . '` `' . $tblAls . '` '
                . 'WHERE (`' . $tblAls . '`.`' . $lnkDbCol . '` = `S`.`SCHEMA_NAME`)'
                . (!is_null($adtnlCol) ? ' AND (`' . $tblAls . '`.`' . $adtnlCol . '` = "' . $adtnlFltr . '")' : '')
                . ') AS `' . ucwords(strtolower((is_null($adtnlCol) ? $tblName : $adtnlFltr))) . '`';
    }

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

danielgp / common-lib

Push — master ( 8c3828...8dbe44 )

sQueryMySqlStatisticPattern() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like