MySQLiByDanielGPqueries::sQueryMySqlStatisticPattern() - Code Metrics - danielgp/common-lib - Measure and Improve Code Quality continuously with Scrutinizer

sQueryMySqlStatisticPattern() A
last analyzed 2018-08-28 23:38 UTC

↳ Parent: MySQLiByDanielGPqueries

Complexity

Conditions	3
Paths	4

Size

Total Lines	8
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes

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

<?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
{

    use MySQLiByDanielGPqueriesBasic;

    /**
     * Ensures table has special quotes and DOT as final char
     * (if not empty, of course)
     *
     * @param string $referenceTable
     * @return string
     */
    private function correctTableWithQuotesAsFieldPrefix($referenceTable)
    {
        if ($referenceTable != '') {
            return '`' . str_replace('`', '', $referenceTable) . '`.';
        }
        return '';
    }

    /**
     * prepares the query to detect FKs
     *
     * @param array $value
     * @return string
     */
    protected function getForeignKeysQuery($value)
    {
        $flt = [
            'TABLE_SCHEMA' => $value['REFERENCED_TABLE_SCHEMA'],
            'TABLE_NAME'   => $value['REFERENCED_TABLE_NAME'],
            'DATA_TYPE'    => ['char', 'varchar', 'text'],
        ];
        if (array_key_exists('LIMIT', $value)) {
            $flt['LIMIT'] = $value['LIMIT'];
        }
        return $this->sQueryMySqlColumns($flt);
    }

    /**
     * Internal function to manage concatenation for filters
     *
     * @param array|string $filterValue
     * @return string
     */
    private function sGlueFilterValIntoWhereStr($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 string
     */
    private function sGlueFiltersIntoWhereArrayFilter($filters)
    {
        return '(' . implode(') AND (', $filters) . ')';
    }

    /**
     * Internal function to manage the filters passed to the query
     *
     * @param null|array $filterArray
     * @param string $tableToApplyFilterTo
     * @return string
     */
    private function sManageDynamicFilters($filterArray = null, $tableToApplyFilterTo = '')
    {
        if (is_null($filterArray)) {
            return '';
        }
        $fltr = [];
        unset($filterArray['LIMIT']);
        foreach ($filterArray as $key => $value) {
            $fltr[] = '`' . $tableToApplyFilterTo . '`.`' . $key . '` ' . $this->sGlueFilterValIntoWhereStr($value);
        }
        return $this->sManageDynamicFiltersFinal($fltr);
    }

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

    private function sManageLimit($filters)
    {
        if (array_key_exists('LIMIT', $filters)) {
            return implode(' ', [
                'LIMIT',
                $filters['LIMIT'],
            ]);
        }
        return '';
    }

    protected function sQueryMySqlColumns($filterArray = null)
    {
        $filterArray = (is_array($filterArray) ? $filterArray : ['' => '']);
        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` '
            . $this->sManageLimit($filterArray)
            . ';';
    }

    private 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`';
    }

    /**
     * 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') . ';';
    }

    private 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`';
    }

    private function sQueryMySqlStatisticPattern($tblName, $lnkDbCol, $adtnlCol = null, $adtnlFltr = null)
    {
        $tblAls  = substr($tblName, 0, 1);
        $colName = (is_null($adtnlCol) ? $tblName : $adtnlFltr);
        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($colName)) . '`';
    }

    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 array $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') . ';';
    }

    /**
     * Builds an filter string from pair of key and value, where value is array
     *
     * @param string $key
     * @param array $value
     * @param string $referenceTable
     * @return string
     */
    private function setArrayLineArrayToFilter($key, $value, $referenceTable)
    {
        $filters2 = implode(', ', array_diff($value, ['']));
        if ($filters2 != '') {
            return '(' . $referenceTable . '`' . $key . '` IN ("'
                . str_replace(',', '","', str_replace(["'", '"'], '', $filters2)) . '"))';
        }
        return '';
    }

    /**
     * Builds an filter string from pair of key and value, none array
     *
     * @param string $key
     * @param int|float|string $value
     * @return string
     */
    private function setArrayLineToFilter($key, $value)
    {
        $fTemp = '=';
        if ((substr($value, 0, 1) == '%') && (substr($value, -1) == '%')) {
            $fTemp = 'LIKE';
        }
        return '(`' . $key . '` ' . $fTemp . '"' . $value . '")';
    }

    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			use MySQLiByDanielGPqueriesBasic;
40
41			/**
42			* Ensures table has special quotes and DOT as final char
43			* (if not empty, of course)
44			*
45			* @param string $referenceTable
46			* @return string
47			*/
48			private function correctTableWithQuotesAsFieldPrefix($referenceTable)
49			{
50			if ($referenceTable != '') {
51			return '`' . str_replace('`', '', $referenceTable) . '`.';
52			}
53			return '';
54			}
55
56			/**
57			* prepares the query to detect FKs
58			*
59			* @param array $value
60			* @return string
61			*/
62			protected function getForeignKeysQuery($value)
63			{
64			$flt = [
65			'TABLE_SCHEMA' => $value['REFERENCED_TABLE_SCHEMA'],
66			'TABLE_NAME' => $value['REFERENCED_TABLE_NAME'],
67			'DATA_TYPE' => ['char', 'varchar', 'text'],
68			];
69			if (array_key_exists('LIMIT', $value)) {
70			$flt['LIMIT'] = $value['LIMIT'];
71			}
72			return $this->sQueryMySqlColumns($flt);
73			}
74
75			/**
76			* Internal function to manage concatenation for filters
77			*
78			* @param array\|string $filterValue
79			* @return string
80			*/
81			private function sGlueFilterValIntoWhereStr($filterValue)
82			{
83			if (is_array($filterValue)) {
84			return 'IN ("' . implode('", "', $filterValue) . '")';
85			}
86			return $this->sGlueFilterValueIntoWhereStringFinal($filterValue);
87			}
88
89			private function sGlueFilterValueIntoWhereStringFinal($filterValue)
90			{
91			$kFields = [
92			'CONNECTION_ID()\|CURDATE()\|CURRENT_USER\|CURRENT_USER()\|CURRENT_DATETIME\|DATABASE()\|NOW()\|USER()',
93			'IS NULL\|IS NOT NULL',
94			'NOT NULL\|NULL',
95			];
96			if (in_array($filterValue, explode('\|', $kFields[0]))) {
97			return '= ' . $filterValue;
98			} elseif (in_array($filterValue, explode('\|', $kFields[1]))) {
99			return $filterValue;
100			} elseif (in_array($filterValue, explode('\|', $kFields[2]))) {
101			return 'IS ' . $filterValue;
102			}
103			return '= "' . $filterValue . '"';
104			}
105
106			/**
107			* Internal function to concatenate filters
108			*
109			* @param array $filters
110			* @return string
111			*/
112			private function sGlueFiltersIntoWhereArrayFilter($filters)
113			{
114			return '(' . implode(') AND (', $filters) . ')';
115			}
116
117			/**
118			* Internal function to manage the filters passed to the query
119			*
120			* @param null\|array $filterArray
121			* @param string $tableToApplyFilterTo
122			* @return string
123			*/
124			private function sManageDynamicFilters($filterArray = null, $tableToApplyFilterTo = '')
125			{
126			if (is_null($filterArray)) {
127			return '';
128			}
129			$fltr = [];
130			unset($filterArray['LIMIT']);
131			foreach ($filterArray as $key => $value) {
132			$fltr[] = '`' . $tableToApplyFilterTo . '`.`' . $key . '` ' . $this->sGlueFilterValIntoWhereStr($value);
133			}
134			return $this->sManageDynamicFiltersFinal($fltr);
135			}
136
137			private function sManageDynamicFiltersFinal($filters)
138			{
139			if (count($filters) > 0) {
140			$sReturn = ['WHERE', $this->sGlueFiltersIntoWhereArrayFilter($filters)];
141			return implode(' ', $sReturn) . ' ';
142			}
143			return '';
144			}
145
146			private function sManageLimit($filters)
147			{
148			if (array_key_exists('LIMIT', $filters)) {
149			return implode(' ', [
150			'LIMIT',
151			$filters['LIMIT'],
152			]);
153			}
154			return '';
155			}
156
157			protected function sQueryMySqlColumns($filterArray = null)
158			{
159			$filterArray = (is_array($filterArray) ? $filterArray : ['' => '']);
160			return 'SELECT '
161			. '`C`.`TABLE_SCHEMA`, '
162			. $this->sQueryMySqlColumnsColumns() . ' '
163			. 'FROM `information_schema`.`COLUMNS` `C` '
164			. 'LEFT JOIN `information_schema`.`KEY_COLUMN_USAGE` `KCU` ON ((' . implode(') AND (', [
165			'`C`.`TABLE_SCHEMA` = `KCU`.`TABLE_SCHEMA`',
166			'`C`.`TABLE_NAME` = `KCU`.`TABLE_NAME`',
167			'`C`.`COLUMN_NAME` = `KCU`.`COLUMN_NAME`',
168			]) . ')) '
169			. $this->sManageDynamicFilters($filterArray, 'C')
170			. 'GROUP BY `C`.`TABLE_SCHEMA`, `C`.`TABLE_NAME`, `C`.`COLUMN_NAME` '
171			. 'ORDER BY `C`.`TABLE_SCHEMA`, `C`.`TABLE_NAME`, `C`.`ORDINAL_POSITION` '
172			. $this->sManageLimit($filterArray)
173			. ';';
174			}
175
176			private function sQueryMySqlColumnsColumns()
177			{
178			return '`C`.`TABLE_NAME`, `C`.`COLUMN_NAME`, `C`.`ORDINAL_POSITION` '
179			. ', `C`.`COLUMN_DEFAULT`, `C`.`IS_NULLABLE`, `C`.`DATA_TYPE`, `C`.`CHARACTER_MAXIMUM_LENGTH` '
180			. ', `C`.`NUMERIC_PRECISION`, `C`.`NUMERIC_SCALE`, `C`.`DATETIME_PRECISION` '
181			. ', `C`.`CHARACTER_SET_NAME`, `C`.`COLLATION_NAME`, `C`.`COLUMN_TYPE` '
182			. ', `C`.`COLUMN_KEY`, `C`.`COLUMN_COMMENT`, `C`.`EXTRA`';
183			}
184
185			/**
186			* Query
187			*
188			* @param array $filterArray
189			* @return string
190			*/
191			protected function sQueryMySqlIndexes($filterArray = null)
192			{
193			return 'SELECT '
194			. '`KCU`.`CONSTRAINT_SCHEMA`, '
195			. $this->sQueryMySqlIndexesColumns() . ' '
196			. 'FROM `information_schema`.`KEY_COLUMN_USAGE` `KCU` '
197			. 'INNER JOIN `information_schema`.`COLUMNS` `C` ON ((' . implode(') AND (', [
198			'`C`.`TABLE_SCHEMA` = `KCU`.`TABLE_SCHEMA`',
199			'`C`.`TABLE_NAME` = `KCU`.`TABLE_NAME`',
200			'`C`.`COLUMN_NAME` = `KCU`.`COLUMN_NAME`',
201			]) . ')) '
202			. 'LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` `RC` ON ((' . implode(') AND (', [
203			'`KCU`.`CONSTRAINT_SCHEMA` = `RC`.`CONSTRAINT_SCHEMA`',
204			'`KCU`.`CONSTRAINT_NAME` = `RC`.`CONSTRAINT_NAME`',
205			]) . ')) '
206			. $this->sManageDynamicFilters($filterArray, 'KCU')
207			. 'ORDER BY `KCU`.`TABLE_SCHEMA`, `KCU`.`TABLE_NAME`'
208			. $this->xtraSoring($filterArray, 'COLUMN_NAME') . ';';
209			}
210
211			private function sQueryMySqlIndexesColumns()
212			{
213			return '`KCU`.`CONSTRAINT_NAME`, `KCU`.`TABLE_SCHEMA`, `KCU`.`TABLE_NAME`, '
214			. '`KCU`.`COLUMN_NAME`, `C`.`ORDINAL_POSITION` AS `COLUMN_POSITION`, `KCU`.`ORDINAL_POSITION`, '
215			. '`KCU`.`POSITION_IN_UNIQUE_CONSTRAINT`, `KCU`.`REFERENCED_TABLE_SCHEMA`, '
216			. '`KCU`.`REFERENCED_TABLE_NAME`, `KCU`.`REFERENCED_COLUMN_NAME`, '
217			. '`RC`.`UPDATE_RULE`, `RC`.`DELETE_RULE`';
218			}
219
220			private function sQueryMySqlStatisticPattern($tblName, $lnkDbCol, $adtnlCol = null, $adtnlFltr = null)
221			{
222			$tblAls = substr($tblName, 0, 1);
223			$colName = (is_null($adtnlCol) ? $tblName : $adtnlFltr);
224			return '(SELECT COUNT(*) AS `No. of records` FROM `information_schema`.`' . $tblName . '` `' . $tblAls . '` '
225			. 'WHERE (`' . $tblAls . '`.`' . $lnkDbCol . '` = `S`.`SCHEMA_NAME`)'
226			. (!is_null($adtnlCol) ? ' AND (`' . $tblAls . '`.`' . $adtnlCol . '` = "' . $adtnlFltr . '")' : '')
227			. ') AS `' . ucwords(strtolower($colName)) . '`';
228			}
229
230			protected function sQueryMySqlStatistics($filterArray = null)
231			{
232			return 'SELECT '
233			. '`S`.`SCHEMA_NAME`, '
234			. $this->sQueryMySqlStatisticPattern('TABLES', 'TABLE_SCHEMA', 'TABLE_TYPE', 'BASE TABLE') . ', '
235			. $this->sQueryMySqlStatisticPattern('TABLES', 'TABLE_SCHEMA', 'TABLE_TYPE', 'VIEW') . ', '
236			. $this->sQueryMySqlStatisticPattern('COLUMNS', 'TABLE_SCHEMA') . ', '
237			. $this->sQueryMySqlStatisticPattern('TRIGGERS', 'EVENT_OBJECT_SCHEMA') . ', '
238			. $this->sQueryMySqlStatisticPattern('ROUTINES', 'ROUTINE_SCHEMA', 'ROUTINE_TYPE', 'Function') . ', '
239			. $this->sQueryMySqlStatisticPattern('ROUTINES', 'ROUTINE_SCHEMA', 'ROUTINE_TYPE', 'Procedure') . ', '
240			. $this->sQueryMySqlStatisticPattern('EVENTS', 'EVENT_SCHEMA') . ' '
241			. 'FROM `information_schema`.`SCHEMATA` `S` '
242			. 'WHERE (`S`.`SCHEMA_NAME` NOT IN ("information_schema", "mysql", "performance_schema", "sys")) '
243			. str_replace('WHERE', 'AND', $this->sManageDynamicFilters($filterArray, 'S'))
244			. 'ORDER BY `S`.`SCHEMA_NAME`;';
245			}
246
247			/**
248			* Query to get list of tables
249			*
250			* @param array $filterArray
251			* @return string
252			*/
253			protected function sQueryMySqlTables($filterArray = null)
254			{
255			return 'SELECT '
256			. '`T`.`TABLE_SCHEMA`, `T`.`TABLE_NAME`, `T`.`TABLE_TYPE`, `T`.`ENGINE`, `T`.`VERSION` '
257			. ', `T`.`ROW_FORMAT`, `T`.`AUTO_INCREMENT`, `T`.`TABLE_COLLATION`, `T`.`CREATE_TIME` '
258			. ', `T`.`CREATE_OPTIONS`, `T`.`TABLE_COMMENT` '
259			. 'FROM `information_schema`.`TABLES` `T` '
260			. $this->sManageDynamicFilters($filterArray, 'T')
261			. $this->xtraSoring($filterArray, 'TABLE_SCHEMA') . ';';
262			}
263
264			/**
265			* Builds an filter string from pair of key and value, where value is array
266			*
267			* @param string $key
268			* @param array $value
269			* @param string $referenceTable
270			* @return string
271			*/
272			private function setArrayLineArrayToFilter($key, $value, $referenceTable)
273			{
274			$filters2 = implode(', ', array_diff($value, ['']));
275			if ($filters2 != '') {
276			return '(' . $referenceTable . '`' . $key . '` IN ("'
277			. str_replace(',', '","', str_replace(["'", '"'], '', $filters2)) . '"))';
278			}
279			return '';
280			}
281
282			/**
283			* Builds an filter string from pair of key and value, none array
284			*
285			* @param string $key
286			* @param int\|float\|string $value
287			* @return string
288			*/
289			private function setArrayLineToFilter($key, $value)
290			{
291			$fTemp = '=';
292			if ((substr($value, 0, 1) == '%') && (substr($value, -1) == '%')) {
293			$fTemp = 'LIKE';
294			}
295			return '(`' . $key . '` ' . $fTemp . '"' . $value . '")';
296			}
297
298			private function xtraSoring($filterArray, $filterValueToDecide)
299			{
300			$defaults = [
301			'COLUMN_NAME' => [
302			', `C`.`ORDINAL_POSITION`, `KCU`.`CONSTRAINT_NAME`',
303			'',
304			],
305			'TABLE_SCHEMA' => [
306			'ORDER BY `T`.`TABLE_SCHEMA`, `T`.`TABLE_NAME`',
307			'ORDER BY `T`.`TABLE_NAME`',
308			],
309			];
310			if (!is_null($filterArray) && is_array($filterArray) && array_key_exists($filterValueToDecide, $filterArray)) {
311			return $defaults[$filterValueToDecide][1];
312			}
313			return $defaults[$filterValueToDecide][0];
314			}
315			}
316

danielgp / common-lib

sQueryMySqlStatisticPattern() A last analyzed 2018-08-28 23:38 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

sQueryMySqlStatisticPattern() A
last analyzed 2018-08-28 23:38 UTC