MySQLiByDanielGP::getMySQLlistTables() - Code Metrics - Inspection of "few optimizations to MySQL extracting data functio..." - danielgp/common-lib - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 580071...8fc19e )

by Daniel

created 2016-02-01 20:31 UTC

MySQLiByDanielGP::getMySQLlistTables() A

↳ Parent: MySQLiByDanielGP

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Metric	Value
dl	0
loc	4
rs	10
cc	1
eloc	2
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;

/**
 * Usefull functions to get quick MySQL content
 *
 * @author Daniel Popiniuc
 */
trait MySQLiByDanielGP
{

    use DomComponentsByDanielGP,
        MySQLiMultipleExecution,
        MySQLiByDanielGPqueries,
        MySQLiByDanielGPtypes;

    /**
     * Intiates connection to MySQL
     *
     * @param array $mySQLconfig
     *
     * $mySQLconfig           = [
     * 'host'     => MYSQL_HOST,
     * 'port'     => MYSQL_PORT,
     * 'username' => MYSQL_USERNAME,
     * 'password' => MYSQL_PASSWORD,
     * 'database' => MYSQL_DATABASE,
     * ];
     */
    protected function connectToMySql($mySQLconfig)
    {
        if (is_null($this->mySQLconnection)) {
            extract($mySQLconfig);
            $this->mySQLconnection = new \mysqli($host, $username, $password, $database, $port);
            if (is_null($this->mySQLconnection->connect_error)) {
                return '';
            }
            $this->mySQLconnection = null;
            $erNo                  = $this->mySQLconnection->connect_errno;
            $erMsg                 = $this->mySQLconnection->connect_error;
            $msg                   = $this->lclMsgCmn('i18n_Feedback_ConnectionError');
            return sprintf($msg, $erNo, $erMsg, $host, $port, $username, $database);
        }
    }

    /**
     * Ensures table has special quoes 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 '';
    }

    /**
     * returns a list of MySQL databases
     *
     * @return array
     */
    protected function getMySQLactiveDatabases()
    {
        return $this->getMySQLlistDatabases(true);
    }

    /**
     * returns a list of active MySQL engines
     *
     * @return array
     */
    protected function getMySQLactiveEngines()
    {
        return $this->getMySQLlistEngines(true);
    }

    /**
     * returns the list of all MySQL generic informations
     *
     * @return array
     */
    protected function getMySQLgenericInformations()
    {
        if (is_null($this->mySQLconnection)) {
            return [];
        }
        return ['Info' => $this->mySQLconnection->server_info, 'Version' => $this->mySQLconnection->server_version];
    }

    /**
     * returns the list of all MySQL global variables
     *
     * @return array
     */
    protected function getMySQLglobalVariables()
    {
        return $this->getMySQLlistMultiple('VariablesGlobal', 'array_key_value');
    }

    /**
     * returns a list of MySQL indexes (w. choice of to choose any combination of db/table/column)
     *
     * @return array
     */
    protected function getMySQLlistColumns($filterArray = null)
    {
        return $this->getMySQLlistMultiple('Columns', 'full_array_key_numbered', $filterArray);
    }

    /**
     * returns a list of MySQL databases (w. choice of exclude/include the system ones)
     *
     * @return array
     */
    protected function getMySQLlistDatabases($excludeSystemDbs = true)
    {
        return $this->getMySQLlistMultiple('Databases', 'array_first_key_rest_values', $excludeSystemDbs);
    }

    /**
     * returns a list of MySQL engines (w. choice of return only the active ones)
     *
     * @return array
     */
    protected function getMySQLlistEngines($onlyActiveOnes = true)
    {
        return $this->getMySQLlistMultiple('Engines', 'array_first_key_rest_values', $onlyActiveOnes);
    }

    /**
     * returns a list of MySQL indexes (w. choice of to choose any combination of db/table/column)
     *
     * @return array
     */
    protected function getMySQLlistIndexes($filterArray = null)
    {
        return $this->getMySQLlistMultiple('Indexes', 'full_array_key_numbered', $filterArray);
    }

    /**
     * Return various informations (from predefined list) from the MySQL server
     *
     * @return int|array
     */
    private function getMySQLlistMultiple($returnChoice, $returnType, $additionalFeatures = null)
    {
        if (is_null($this->mySQLconnection)) {
            if ($returnType == 'value') {
                return null;
            }
            return [];
        }
        return $this->getMySQLlistMultipleFinal($returnChoice, $returnType, $additionalFeatures);
    }

    /**
     * Return various informations (from predefined list) from the MySQL server
     *
     * @return array
     */
    private function getMySQLlistMultipleFinal($returnChoice, $returnType, $additionalFeatures = null)
    {
        $queryByChoice = [
            'Columns'         => $this->sQueryMySqlColumns($additionalFeatures),
            'Databases'       => $this->sQueryMySqlActiveDatabases($additionalFeatures),
            'Engines'         => $this->sQueryMySqlActiveEngines($additionalFeatures),
            'Indexes'         => $this->sQueryMySqlIndexes($additionalFeatures),
            'ServerTime'      => $this->sQueryMySqlServerTime(),
            'Statistics'      => $this->sQueryMySqlStatistics($additionalFeatures),
            'Tables'          => $this->sQueryMySqlTables($additionalFeatures),
            'VariablesGlobal' => $this->sQueryMySqlGlobalVariables(),
        ];
        if (array_key_exists($returnChoice, $queryByChoice)) {
            return $this->setMySQLquery2Server($queryByChoice[$returnChoice], $returnType)['result'];
        }
        return [];
    }

    /**
     * Return the list of Tables from the MySQL server
     *
     * @return string
     */
    protected function getMySQLStatistics($filterArray = null)
    {
        return $this->getMySQLlistMultiple('Statistics', 'full_array_key_numbered', $filterArray);
    }

    /**
     * Return the list of Tables from the MySQL server
     *
     * @return string
     */
    protected function getMySQLlistTables($filterArray = null)
    {
        return $this->getMySQLlistMultiple('Tables', 'full_array_key_numbered', $filterArray);
    }

    /**
     * Provides a detection if given Query does contain a Parameter
     * that may require statement processing later on
     *
     * @param string $sQuery
     * @param string $paramIdentifier
     * @return boolean
     */
    protected function getMySQLqueryWithParameterIdentifier($sQuery, $paramIdentifier)
    {
        $sReturn = true;
        if (strpos($sQuery, $paramIdentifier) === false) {
            $sReturn = false;
        }
        return $sReturn;
    }

    /**
     * Return the time from the MySQL server
     *
     * @return string
     */
    protected function getMySQLserverTime()
    {
        return $this->getMySQLlistMultiple('ServerTime', 'value');
    }

    /**
     * Reads data from table into REQUEST super global
     *
     * @param string $tableName
     * @param array $filtersArray
     */
    protected function getRowDataFromTable($tableName, $filtersArray)
    {
        $query   = $this->sQueryRowsFromTable([$tableName, $this->setArrayToFilterValues($filtersArray)]);
        $rawData = $this->setMySQLquery2Server($query, 'array_pairs_key_value')['result'];
        if (!is_null($rawData)) {
            $this->initializeSprGlbAndSession();
            foreach ($rawData as $key => $value) {
                $vToSet = str_replace(['\\\\"', '\\"', "\\\\'", "\\'"], ['"', '"', "'", "'"], $value);
                $this->tCmnRequest->request->set($key, $vToSet);
            }
        }
    }

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

    /**
     * Transforms an array into usable filters
     *
     * @param array $entryArray
     * @param string $referenceTable
     * @return array
     */
    private function setArrayToFilterValues($entryArray, $referenceTable = '')
    {
        $filters  = [];
        $refTable = $this->correctTableWithQuotesAsFieldPrefix($referenceTable);
        foreach ($entryArray as $key => $value) {
            if (is_array($value)) {
                $filters[] = $this->setArrayLineArrayToFilter($key, $value, $refTable);
            } elseif (!in_array($value, ['', '%%'])) {
                $filters[] = $this->setArrayLineToFilter($key, $value);
            }
        }
        return implode(' AND ', array_diff($filters, ['']));
    }

    /**
     * Returns maximum length for a given MySQL field
     *
     * @param array $fieldDetails
     * @param boolean $outputFormated
     * @return array
     */
    protected function setFieldNumbers($fieldDetails, $outputFormated = false)
    {
        $sRtrn = $this->setFieldSpecific($fieldDetails);
        if ($outputFormated) {
            if (is_array($sRtrn)) {
                foreach ($sRtrn as $key => $value) {
                    $sRtrn[$key] = $this->setNumberFormat($value);
                }
            }
        }
        return $sRtrn;
    }

    /**
     * Establishes numbers of fields
     *
     * @param array $fieldDetails
     * @return array
     */
    private function setFieldSpecific($fieldDetails)
    {
        if (in_array($fieldDetails['DATA_TYPE'], ['char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext'])) {
            return ['M' => $fieldDetails['CHARACTER_MAXIMUM_LENGTH']];
        } elseif (in_array($fieldDetails['DATA_TYPE'], ['decimal', 'numeric'])) {
            return ['M' => $fieldDetails['NUMERIC_PRECISION'], 'd' => $fieldDetails['NUMERIC_SCALE']];
        } elseif (in_array($fieldDetails['DATA_TYPE'], ['bigint', 'int', 'mediumint', 'smallint', 'tinyint'])) {
            return $this->setFldLmtsExact($fieldDetails['DATA_TYPE']);
        }
        return $this->setFieldSpecificElse($fieldDetails);
    }

    private function setFieldSpecificElse($fieldDetails)
    {
        $map = ['date' => 10, 'datetime' => 19, 'enum' => 65536, 'set' => 64, 'time' => 8, 'timestamp' => 19];
        if (array_key_exists($fieldDetails['DATA_TYPE'], $map)) {
            return ['M' => $map[$fieldDetails['DATA_TYPE']]];
        }
        return ['M' => '???'];
    }

    private function setFldLmts($colType, $loLmt, $upLmt, $szN, $szUS)
    {
        $aReturn = ['m' => $loLmt, 'M' => $upLmt, 'l' => $szN];
        if (strpos($colType, 'unsigned') !== false) {
            $aReturn = ['m' => 0, 'M' => ($upLmt - $loLmt), 'l' => $szUS];
        }
        return $aReturn;
    }

    private function setFldLmtsExact($cTp)
    {
        $xct     = [
            'bigint'    => ['l' => -9223372036854775808, 'L' => 9223372036854775807, 's' => 21, 'sUS' => 20],
            'int'       => ['l' => -2147483648, 'L' => 2147483647, 's' => 11, 'sUS' => 10],
            'mediumint' => ['l' => -8388608, 'L' => 8388607, 's' => 9, 'sUS' => 8],
            'smallint'  => ['l' => -32768, 'L' => 32767, 's' => 6, 'sUS' => 5],
            'tinyint'   => ['l' => -128, 'L' => 127, 's' => 4, 'sUS' => 3],
        ];
        $sReturn = null;
        if (array_key_exists($cTp, $xct)) {
            $sReturn = $this->setFldLmts($cTp, $xct[$cTp]['l'], $xct[$cTp]['L'], $xct[$cTp]['s'], $xct[$cTp]['sUS']);
        }
        return $sReturn;
    }

    /**
     * Transmit Query to MySQL server and get results back
     *
     * @param string $sQuery
     * @param string $sReturnType
     * @param array $ftrs
     * @return boolean|array|string
     */
    protected function setMySQLquery2Server($sQuery, $sReturnType = null, $ftrs = null)
    {
        if (is_null($sReturnType)) {
            $this->mySQLconnection->query(html_entity_decode($sQuery));
            return '';
        } elseif (is_null($this->mySQLconnection)) {
            return ['customError' => $this->lclMsgCmn('i18n_MySQL_ConnectionNotExisting'), 'result' => null];
        }
        $result = $this->mySQLconnection->query(html_entity_decode($sQuery));
        if ($result) {
            return $this->setMySQLquery2ServerConnected(['Result' => $result, 'RType' => $sReturnType, 'F' => $ftrs]);
        }
        $erM  = [$this->mySQLconnection->errno, $this->mySQLconnection->error];
        $cErr = sprintf($this->lclMsgCmn('i18n_MySQL_QueryError'), $erM[0], $erM[1]);
        return ['customError' => $cErr, 'result' => null];
    }

    /**
     * Turns a raw query result into various structures
     * based on different predefined $parameters['returnType'] value
     *
     * @param array $parameters
     * @return array as ['customError' => '...', 'result' => '...']
     */
    private function setMySQLquery2ServerByPattern($parameters)
    {
        $aReturn = $parameters['return'];
        $vld     = $this->setMySQLqueryValidateInputs($parameters);
        if ($vld[1] !== '') {
            return ['customError' => $vld[1], 'result' => ''];
        } elseif ($parameters['returnType'] == 'value') {
            return ['customError' => $vld[1], 'result' => $parameters['QueryResult']->fetch_row()[0]];
        }
        $counter2 = 0;
        for ($counter = 0; $counter < $parameters['NoOfRows']; $counter++) {
            $line = $parameters['QueryResult']->fetch_row();
            if (in_array($parameters['returnType'], ['array_key_value', 'array_key2_value', 'array_numbered'])) {
                $rslt                        = $this->setMySQLquery2ServerByPatternKey($parameters, $line, $counter);
                $aReturn['result'][$rslt[0]] = $rslt[1];
            } elseif ($parameters['returnType'] == 'array_key_value2') {
                $aReturn['result'][$line[0]][] = $line[1];
            } else {
                $finfo = $parameters['QueryResult']->fetch_fields();
                switch ($parameters['returnType']) {
                    case 'array_first_key_rest_values':

                        foreach ($finfo as $columnCounter => $value) {
                            if ($columnCounter !== 0) {
                                $aReturn['result'][$line[0]][$value->name] = $line[$columnCounter];
                            }
                        }
                        break;
                    case 'array_pairs_key_value':
                        foreach ($finfo as $columnCounter => $value) {
                            $aReturn['result'][$value->name] = $line[$columnCounter];
                        }
                        break;
                    case 'full_array_key_numbered':
                        foreach ($finfo as $columnCounter => $value) {
                            $aReturn['result'][$counter2][$value->name] = $line[$columnCounter];
                        }
                        break;
                    case 'full_array_key_numbered_with_record_number_prefix':
                        $parameters['prefix'] = 'RecordNo';
                    // intentionally left open
                    case 'full_array_key_numbered_with_prefix':

                        foreach ($finfo as $columnCounter => $value) {
                            $aReturn['result'][$parameters['prefix']][$counter2][$value->name] = $line[$columnCounter];
                        }
                        break;
                }
                $counter2++;
            }
        }
        return ['customError' => '', 'result' => $aReturn['result']];
    }

    private function setMySQLquery2ServerByPatternKey($parameters, $line, $counter)
    {
        switch ($parameters['returnType']) {
            case 'array_key_value':
                return [$line[0], $line[1]];
            // intentionally left open
            case 'array_key2_value':
                return [$line[0] . '@' . $line[1], $line[1]];
            // intentionally left open
            case 'array_numbered':
                return [$counter, $line[0]];
            // intentionally left open
        }
    }

    protected function setMySQLquery2ServerConnected($inArray)
    {
        if ($inArray['RType'] == 'id') {
            return ['customError' => '', 'result' => $this->mySQLconnection->insert_id];
        } elseif ($inArray['RType'] == 'lines') {
            return ['result' => $inArray['Result']->num_rows, 'customError' => ''];
        }
        $parameters = [
            'NoOfColumns' => $inArray['Result']->field_count,
            'NoOfRows'    => $inArray['Result']->num_rows,
            'QueryResult' => $inArray['Result'],
            'returnType'  => $inArray['RType'],
            'return'      => ['customError' => '', 'result' => null]
        ];
        if (substr($inArray['RType'], -6) == 'prefix') {
            $parameters['prefix'] = $inArray['F']['prefix'];
        }
        return $this->setMySQLquery2ServerByPattern($parameters);
    }

    private function setMySQLqueryValidateInputs($prm)
    {
        $rMap = $this->setMySQLqueryValidationMap();
        if (array_key_exists($prm['returnType'], $rMap)) {
            $elC = [$prm['NoOfRows'], $rMap[$prm['returnType']]['r'][0], $rMap[$prm['returnType']]['r'][1]];
            if (filter_var($elC[0], FILTER_VALIDATE_INT, ['min_range' => $elC[1], 'max_range' => $elC[2]]) === false) {
                $msg = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected' . $rMap[$prm['returnType']][2]);
                return [false, sprintf($msg, $prm['NoOfColumns'])];
            }
            $elR = [$prm['NoOfColumns'], $rMap[$prm['returnType']]['c'][0], $rMap[$prm['returnType']]['c'][1]];
            if (filter_var($elR[0], FILTER_VALIDATE_INT, ['min_range' => $elR[1], 'max_range' => $elR[2]])) {
                return [true, ''];
            }
            $msg = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected' . $rMap[$prm['returnType']][1]);
            return [false, sprintf($msg, $prm['NoOfColumns'])];
        }
        return [false, $prm['returnType'] . ' is not defined!'];
    }

    private function setMySQLqueryValidationMap()
    {
        $lngKey = 'full_array_key_numbered_with_record_number_prefix';
        return [
            'array_first_key_rest_values'         => ['r' => [1, 999999], 'c' => [2, 99], 'AtLeast2ColsResultedOther'],
            'array_key_value'                     => ['r' => [1, 999999], 'c' => [2, 2], '2ColumnsResultedOther'],
            'array_key_value2'                    => ['r' => [1, 999999], 'c' => [2, 2], '2ColumnsResultedOther'],
            'array_key2_value'                    => ['r' => [1, 999999], 'c' => [2, 2], '2ColumnsResultedOther'],
            'array_numbered'                      => ['r' => [1, 999999], 'c' => [1, 1], '1ColumnResultedOther'],
            'array_pairs_key_value'               => ['r' => [1, 1], 'c' => [1, 99], '1RowManyColumnsResultedOther'],
            'full_array_key_numbered'             => ['r' => [1, 999999], 'c' => [1, 99], '1OrMoreRows0Resulted'],
            'full_array_key_numbered_with_prefix' => ['r' => [1, 999999], 'c' => [1, 99], '1OrMoreRows0Resulted'],
            $lngKey                               => ['r' => [1, 999999], 'c' => [1, 99], '1OrMoreRows0Resulted'],
            'value'                               => ['r' => [1, 1], 'c' => [1, 1], '1ResultedOther'],
        ];
    }
}


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		* Usefull functions to get quick MySQL content
33		*
34		* @author Daniel Popiniuc
35		*/
36		trait MySQLiByDanielGP
37		{
38
39		use DomComponentsByDanielGP,
40		MySQLiMultipleExecution,
41		MySQLiByDanielGPqueries,
42		MySQLiByDanielGPtypes;
43
44		/**
45		* Intiates connection to MySQL
46		*
47		* @param array $mySQLconfig
48		*
49		* $mySQLconfig = [
50		* 'host' => MYSQL_HOST,
51		* 'port' => MYSQL_PORT,
52		* 'username' => MYSQL_USERNAME,
53		* 'password' => MYSQL_PASSWORD,
54		* 'database' => MYSQL_DATABASE,
55		* ];
56		*/
57		protected function connectToMySql($mySQLconfig)
58		{
59		if (is_null($this->mySQLconnection)) {
60		extract($mySQLconfig);
61		$this->mySQLconnection = new \mysqli($host, $username, $password, $database, $port);
62		if (is_null($this->mySQLconnection->connect_error)) {
63		return '';
64		}
65		$this->mySQLconnection = null;
66		$erNo = $this->mySQLconnection->connect_errno;
67		$erMsg = $this->mySQLconnection->connect_error;
68		$msg = $this->lclMsgCmn('i18n_Feedback_ConnectionError');
69		return sprintf($msg, $erNo, $erMsg, $host, $port, $username, $database);
70		}
71		}
72
73		/**
74		* Ensures table has special quoes and DOT as final char
75		* (if not empty, of course)
76		*
77		* @param string $referenceTable
78		* @return string
79		*/
80		private function correctTableWithQuotesAsFieldPrefix($referenceTable)
81		{
82		if ($referenceTable != '') {
83		return '`' . str_replace('`', '', $referenceTable) . '`.';
84		}
85		return '';
86		}
87
88		/**
89		* returns a list of MySQL databases
90		*
91		* @return array
92		*/
93		protected function getMySQLactiveDatabases()
94		{
95		return $this->getMySQLlistDatabases(true);
96		}
97
98		/**
99		* returns a list of active MySQL engines
100		*
101		* @return array
102		*/
103		protected function getMySQLactiveEngines()
104		{
105		return $this->getMySQLlistEngines(true);
106		}
107
108		/**
109		* returns the list of all MySQL generic informations
110		*
111		* @return array
112		*/
113		protected function getMySQLgenericInformations()
114		{
115		if (is_null($this->mySQLconnection)) {
116		return [];
117		}
118		return ['Info' => $this->mySQLconnection->server_info, 'Version' => $this->mySQLconnection->server_version];
119		}
120
121		/**
122		* returns the list of all MySQL global variables
123		*
124		* @return array
125		*/
126		protected function getMySQLglobalVariables()
127		{
128		return $this->getMySQLlistMultiple('VariablesGlobal', 'array_key_value');
129		}
130
131		/**
132		* returns a list of MySQL indexes (w. choice of to choose any combination of db/table/column)
133		*
134		* @return array
135		*/
136		protected function getMySQLlistColumns($filterArray = null)
137		{
138		return $this->getMySQLlistMultiple('Columns', 'full_array_key_numbered', $filterArray);
139		}
140
141		/**
142		* returns a list of MySQL databases (w. choice of exclude/include the system ones)
143		*
144		* @return array
145		*/
146		protected function getMySQLlistDatabases($excludeSystemDbs = true)
147		{
148		return $this->getMySQLlistMultiple('Databases', 'array_first_key_rest_values', $excludeSystemDbs);
149		}
150
151		/**
152		* returns a list of MySQL engines (w. choice of return only the active ones)
153		*
154		* @return array
155		*/
156		protected function getMySQLlistEngines($onlyActiveOnes = true)
157		{
158		return $this->getMySQLlistMultiple('Engines', 'array_first_key_rest_values', $onlyActiveOnes);
159		}
160
161		/**
162		* returns a list of MySQL indexes (w. choice of to choose any combination of db/table/column)
163		*
164		* @return array
165		*/
166		protected function getMySQLlistIndexes($filterArray = null)
167		{
168		return $this->getMySQLlistMultiple('Indexes', 'full_array_key_numbered', $filterArray);
169		}
170
171		/**
172		* Return various informations (from predefined list) from the MySQL server
173		*
174		* @return int\|array
175		*/
176		private function getMySQLlistMultiple($returnChoice, $returnType, $additionalFeatures = null)
177		{
178		if (is_null($this->mySQLconnection)) {
179		if ($returnType == 'value') {
180		return null;
181		}
182		return [];
183		}
184		return $this->getMySQLlistMultipleFinal($returnChoice, $returnType, $additionalFeatures);
185		}
186
187		/**
188		* Return various informations (from predefined list) from the MySQL server
189		*
190		* @return array
191		*/
192		private function getMySQLlistMultipleFinal($returnChoice, $returnType, $additionalFeatures = null)
193		{
194		$queryByChoice = [
195		'Columns' => $this->sQueryMySqlColumns($additionalFeatures),
196		'Databases' => $this->sQueryMySqlActiveDatabases($additionalFeatures),
197		'Engines' => $this->sQueryMySqlActiveEngines($additionalFeatures),
198		'Indexes' => $this->sQueryMySqlIndexes($additionalFeatures),
199		'ServerTime' => $this->sQueryMySqlServerTime(),
200		'Statistics' => $this->sQueryMySqlStatistics($additionalFeatures),
201		'Tables' => $this->sQueryMySqlTables($additionalFeatures),
202		'VariablesGlobal' => $this->sQueryMySqlGlobalVariables(),
203		];
204		if (array_key_exists($returnChoice, $queryByChoice)) {
205		return $this->setMySQLquery2Server($queryByChoice[$returnChoice], $returnType)['result'];
206		}
207		return [];
208		}
209
210		/**
211		* Return the list of Tables from the MySQL server
212		*
213		* @return string
214		*/
215		protected function getMySQLStatistics($filterArray = null)
216		{
217		return $this->getMySQLlistMultiple('Statistics', 'full_array_key_numbered', $filterArray);
218		}
219
220		/**
221		* Return the list of Tables from the MySQL server
222		*
223		* @return string
224		*/
225		protected function getMySQLlistTables($filterArray = null)
226		{
227		return $this->getMySQLlistMultiple('Tables', 'full_array_key_numbered', $filterArray);
228		}
229
230		/**
231		* Provides a detection if given Query does contain a Parameter
232		* that may require statement processing later on
233		*
234		* @param string $sQuery
235		* @param string $paramIdentifier
236		* @return boolean
237		*/
238		protected function getMySQLqueryWithParameterIdentifier($sQuery, $paramIdentifier)
239		{
240		$sReturn = true;
241		if (strpos($sQuery, $paramIdentifier) === false) {
242		$sReturn = false;
243		}
244		return $sReturn;
245		}
246
247		/**
248		* Return the time from the MySQL server
249		*
250		* @return string
251		*/
252		protected function getMySQLserverTime()
253		{
254		return $this->getMySQLlistMultiple('ServerTime', 'value');
255		}
256
257		/**
258		* Reads data from table into REQUEST super global
259		*
260		* @param string $tableName
261		* @param array $filtersArray
262		*/
263		protected function getRowDataFromTable($tableName, $filtersArray)
264		{
265		$query = $this->sQueryRowsFromTable([$tableName, $this->setArrayToFilterValues($filtersArray)]);
266		$rawData = $this->setMySQLquery2Server($query, 'array_pairs_key_value')['result'];
267		if (!is_null($rawData)) {
268		$this->initializeSprGlbAndSession();
269		foreach ($rawData as $key => $value) {
270		$vToSet = str_replace(['\\\\"', '\\"', "\\\\'", "\\'"], ['"', '"', "'", "'"], $value);
271		$this->tCmnRequest->request->set($key, $vToSet);
272		}
273		}
274		}
275
276		/**
277		* Builds an filter string from pair of key and value, where value is array
278		*
279		* @param string $key
280		* @param array $value
281		* @param string $referenceTable
282		* @return string
283		*/
284		private function setArrayLineArrayToFilter($key, $value, $referenceTable)
285		{
286		$filters2 = implode(', ', array_diff($value, ['']));
287		if ($filters2 != '') {
288		return '(' . $referenceTable . '`' . $key . '` IN ("'
289		. str_replace(',', '","', str_replace(["'", '"'], '', $filters2)) . '"))';
290		}
291		return '';
292		}
293
294		/**
295		* Builds an filter string from pair of key and value, none array
296		*
297		* @param string $key
298		* @param int\|float\|string $value
299		* @return string
300		*/
301		private function setArrayLineToFilter($key, $value)
302		{
303		$fTemp = '=';
304		if ((substr($value, 0, 1) == '%') && (substr($value, -1) == '%')) {
305		$fTemp = 'LIKE';
306		}
307		return '(`' . $key . '` ' . $fTemp . '"' . $value . '")';
308		}
309
310		/**
311		* Transforms an array into usable filters
312		*
313		* @param array $entryArray
314		* @param string $referenceTable
315		* @return array
316		*/
317		private function setArrayToFilterValues($entryArray, $referenceTable = '')
318		{
319		$filters = [];
320		$refTable = $this->correctTableWithQuotesAsFieldPrefix($referenceTable);
321		foreach ($entryArray as $key => $value) {
322		if (is_array($value)) {
323		$filters[] = $this->setArrayLineArrayToFilter($key, $value, $refTable);
324		} elseif (!in_array($value, ['', '%%'])) {
325		$filters[] = $this->setArrayLineToFilter($key, $value);
326		}
327		}
328		return implode(' AND ', array_diff($filters, ['']));
329		}
330
331		/**
332		* Returns maximum length for a given MySQL field
333		*
334		* @param array $fieldDetails
335		* @param boolean $outputFormated
336		* @return array
337		*/
338		protected function setFieldNumbers($fieldDetails, $outputFormated = false)
339		{
340		$sRtrn = $this->setFieldSpecific($fieldDetails);
341		if ($outputFormated) {
342		if (is_array($sRtrn)) {
343		foreach ($sRtrn as $key => $value) {
344		$sRtrn[$key] = $this->setNumberFormat($value);
345		}
346		}
347		}
348		return $sRtrn;
349		}
350
351		/**
352		* Establishes numbers of fields
353		*
354		* @param array $fieldDetails
355		* @return array
356		*/
357		private function setFieldSpecific($fieldDetails)
358		{
359		if (in_array($fieldDetails['DATA_TYPE'], ['char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext'])) {
360		return ['M' => $fieldDetails['CHARACTER_MAXIMUM_LENGTH']];
361		} elseif (in_array($fieldDetails['DATA_TYPE'], ['decimal', 'numeric'])) {
362		return ['M' => $fieldDetails['NUMERIC_PRECISION'], 'd' => $fieldDetails['NUMERIC_SCALE']];
363		} elseif (in_array($fieldDetails['DATA_TYPE'], ['bigint', 'int', 'mediumint', 'smallint', 'tinyint'])) {
364		return $this->setFldLmtsExact($fieldDetails['DATA_TYPE']);
365		}
366		return $this->setFieldSpecificElse($fieldDetails);
367		}
368
369		private function setFieldSpecificElse($fieldDetails)
370		{
371		$map = ['date' => 10, 'datetime' => 19, 'enum' => 65536, 'set' => 64, 'time' => 8, 'timestamp' => 19];
372		if (array_key_exists($fieldDetails['DATA_TYPE'], $map)) {
373		return ['M' => $map[$fieldDetails['DATA_TYPE']]];
374		}
375		return ['M' => '???'];
376		}
377
378		private function setFldLmts($colType, $loLmt, $upLmt, $szN, $szUS)
379		{
380		$aReturn = ['m' => $loLmt, 'M' => $upLmt, 'l' => $szN];
381		if (strpos($colType, 'unsigned') !== false) {
382		$aReturn = ['m' => 0, 'M' => ($upLmt - $loLmt), 'l' => $szUS];
383		}
384		return $aReturn;
385		}
386
387		private function setFldLmtsExact($cTp)
388		{
389		$xct = [
390		'bigint' => ['l' => -9223372036854775808, 'L' => 9223372036854775807, 's' => 21, 'sUS' => 20],
391		'int' => ['l' => -2147483648, 'L' => 2147483647, 's' => 11, 'sUS' => 10],
392		'mediumint' => ['l' => -8388608, 'L' => 8388607, 's' => 9, 'sUS' => 8],
393		'smallint' => ['l' => -32768, 'L' => 32767, 's' => 6, 'sUS' => 5],
394		'tinyint' => ['l' => -128, 'L' => 127, 's' => 4, 'sUS' => 3],
395		];
396		$sReturn = null;
397		if (array_key_exists($cTp, $xct)) {
398		$sReturn = $this->setFldLmts($cTp, $xct[$cTp]['l'], $xct[$cTp]['L'], $xct[$cTp]['s'], $xct[$cTp]['sUS']);
399		}
400		return $sReturn;
401		}
402
403		/**
404		* Transmit Query to MySQL server and get results back
405		*
406		* @param string $sQuery
407		* @param string $sReturnType
408		* @param array $ftrs
409		* @return boolean\|array\|string
410		*/
411		protected function setMySQLquery2Server($sQuery, $sReturnType = null, $ftrs = null)
412		{
413		if (is_null($sReturnType)) {
414		$this->mySQLconnection->query(html_entity_decode($sQuery));
415		return '';
416		} elseif (is_null($this->mySQLconnection)) {
417		return ['customError' => $this->lclMsgCmn('i18n_MySQL_ConnectionNotExisting'), 'result' => null];
418		}
419		$result = $this->mySQLconnection->query(html_entity_decode($sQuery));
420		if ($result) {
421		return $this->setMySQLquery2ServerConnected(['Result' => $result, 'RType' => $sReturnType, 'F' => $ftrs]);
422		}
423		$erM = [$this->mySQLconnection->errno, $this->mySQLconnection->error];
424		$cErr = sprintf($this->lclMsgCmn('i18n_MySQL_QueryError'), $erM[0], $erM[1]);
425		return ['customError' => $cErr, 'result' => null];
426		}
427
428		/**
429		* Turns a raw query result into various structures
430		* based on different predefined $parameters['returnType'] value
431		*
432		* @param array $parameters
433		* @return array as ['customError' => '...', 'result' => '...']
434		*/
435		private function setMySQLquery2ServerByPattern($parameters)
436		{
437		$aReturn = $parameters['return'];
438		$vld = $this->setMySQLqueryValidateInputs($parameters);
439		if ($vld[1] !== '') {
440		return ['customError' => $vld[1], 'result' => ''];
441		} elseif ($parameters['returnType'] == 'value') {
442		return ['customError' => $vld[1], 'result' => $parameters['QueryResult']->fetch_row()[0]];
443		}
444		$counter2 = 0;
445		for ($counter = 0; $counter < $parameters['NoOfRows']; $counter++) {
446		$line = $parameters['QueryResult']->fetch_row();
447		if (in_array($parameters['returnType'], ['array_key_value', 'array_key2_value', 'array_numbered'])) {
448		$rslt = $this->setMySQLquery2ServerByPatternKey($parameters, $line, $counter);
449		$aReturn['result'][$rslt[0]] = $rslt[1];
450		} elseif ($parameters['returnType'] == 'array_key_value2') {
451		$aReturn['result'][$line[0]][] = $line[1];
452		} else {
453		$finfo = $parameters['QueryResult']->fetch_fields();
454		switch ($parameters['returnType']) {
455	View Code Duplication	case 'array_first_key_rest_values':
		0 ignored issues – show Duplication introduced 2016-01-05 18:02 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
456		foreach ($finfo as $columnCounter => $value) {
457		if ($columnCounter !== 0) {
458		$aReturn['result'][$line[0]][$value->name] = $line[$columnCounter];
459		}
460		}
461		break;
462		case 'array_pairs_key_value':
463		foreach ($finfo as $columnCounter => $value) {
464		$aReturn['result'][$value->name] = $line[$columnCounter];
465		}
466		break;
467		case 'full_array_key_numbered':
468		foreach ($finfo as $columnCounter => $value) {
469		$aReturn['result'][$counter2][$value->name] = $line[$columnCounter];
470		}
471		break;
472		case 'full_array_key_numbered_with_record_number_prefix':
473		$parameters['prefix'] = 'RecordNo';
474		// intentionally left open
475	View Code Duplication	case 'full_array_key_numbered_with_prefix':
		0 ignored issues – show Duplication introduced 2016-01-05 18:02 UTC by Report Bug Copy Issue Report This code seems to be duplicated across your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
476		foreach ($finfo as $columnCounter => $value) {
477		$aReturn['result'][$parameters['prefix']][$counter2][$value->name] = $line[$columnCounter];
478		}
479		break;
480		}
481		$counter2++;
482		}
483		}
484		return ['customError' => '', 'result' => $aReturn['result']];
485		}
486
487		private function setMySQLquery2ServerByPatternKey($parameters, $line, $counter)
488		{
489		switch ($parameters['returnType']) {
490		case 'array_key_value':
491		return [$line[0], $line[1]];
492		// intentionally left open
493		case 'array_key2_value':
494		return [$line[0] . '@' . $line[1], $line[1]];
495		// intentionally left open
496		case 'array_numbered':
497		return [$counter, $line[0]];
498		// intentionally left open
499		}
500		}
501
502		protected function setMySQLquery2ServerConnected($inArray)
503		{
504		if ($inArray['RType'] == 'id') {
505		return ['customError' => '', 'result' => $this->mySQLconnection->insert_id];
506		} elseif ($inArray['RType'] == 'lines') {
507		return ['result' => $inArray['Result']->num_rows, 'customError' => ''];
508		}
509		$parameters = [
510		'NoOfColumns' => $inArray['Result']->field_count,
511		'NoOfRows' => $inArray['Result']->num_rows,
512		'QueryResult' => $inArray['Result'],
513		'returnType' => $inArray['RType'],
514		'return' => ['customError' => '', 'result' => null]
515		];
516		if (substr($inArray['RType'], -6) == 'prefix') {
517		$parameters['prefix'] = $inArray['F']['prefix'];
518		}
519		return $this->setMySQLquery2ServerByPattern($parameters);
520		}
521
522		private function setMySQLqueryValidateInputs($prm)
523		{
524		$rMap = $this->setMySQLqueryValidationMap();
525		if (array_key_exists($prm['returnType'], $rMap)) {
526		$elC = [$prm['NoOfRows'], $rMap[$prm['returnType']]['r'][0], $rMap[$prm['returnType']]['r'][1]];
527		if (filter_var($elC[0], FILTER_VALIDATE_INT, ['min_range' => $elC[1], 'max_range' => $elC[2]]) === false) {
528		$msg = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected' . $rMap[$prm['returnType']][2]);
529		return [false, sprintf($msg, $prm['NoOfColumns'])];
530		}
531		$elR = [$prm['NoOfColumns'], $rMap[$prm['returnType']]['c'][0], $rMap[$prm['returnType']]['c'][1]];
532		if (filter_var($elR[0], FILTER_VALIDATE_INT, ['min_range' => $elR[1], 'max_range' => $elR[2]])) {
533		return [true, ''];
534		}
535		$msg = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected' . $rMap[$prm['returnType']][1]);
536		return [false, sprintf($msg, $prm['NoOfColumns'])];
537		}
538		return [false, $prm['returnType'] . ' is not defined!'];
539		}
540
541		private function setMySQLqueryValidationMap()
542		{
543		$lngKey = 'full_array_key_numbered_with_record_number_prefix';
544		return [
545		'array_first_key_rest_values' => ['r' => [1, 999999], 'c' => [2, 99], 'AtLeast2ColsResultedOther'],
546		'array_key_value' => ['r' => [1, 999999], 'c' => [2, 2], '2ColumnsResultedOther'],
547		'array_key_value2' => ['r' => [1, 999999], 'c' => [2, 2], '2ColumnsResultedOther'],
548		'array_key2_value' => ['r' => [1, 999999], 'c' => [2, 2], '2ColumnsResultedOther'],
549		'array_numbered' => ['r' => [1, 999999], 'c' => [1, 1], '1ColumnResultedOther'],
550		'array_pairs_key_value' => ['r' => [1, 1], 'c' => [1, 99], '1RowManyColumnsResultedOther'],
551		'full_array_key_numbered' => ['r' => [1, 999999], 'c' => [1, 99], '1OrMoreRows0Resulted'],
552		'full_array_key_numbered_with_prefix' => ['r' => [1, 999999], 'c' => [1, 99], '1OrMoreRows0Resulted'],
553		$lngKey => ['r' => [1, 999999], 'c' => [1, 99], '1OrMoreRows0Resulted'],
554		'value' => ['r' => [1, 1], 'c' => [1, 1], '1ResultedOther'],
555		];
556		}
557		}
558

danielgp / common-lib

Push — master ( 580071...8fc19e )

MySQLiByDanielGP::getMySQLlistTables() A

Complexity

Size

Duplication

Duplication Side-by-Side

Filter issues like