MySQLiByDanielGP::getMySQLlistColumns() - Code Metrics - Inspection of "broken few methods into smaller pieces" - danielgp/common-lib - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 36d546...f13e43 )

by Daniel

created 2016-01-31 22:46 UTC

MySQLiByDanielGP::getMySQLlistColumns() A

↳ Parent: MySQLiByDanielGP

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
c	1
b	0
f	0
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;
    }

    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, $sReturnType);
        }
        $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' => ''];
        }
        $counter2 = 0;
        for ($counter = 0; $counter < $parameters['NoOfRows']; $counter++) {
            $line = $parameters['QueryResult']->fetch_row();
            switch ($parameters['returnType']) {
                case 'array_first_key_rest_values':
                    $finfo         = $parameters['QueryResult']->fetch_fields();
                    $columnCounter = 0;
                    foreach ($finfo as $value) {
                        if ($columnCounter != 0) {
                            $aReturn['result'][$line[0]][$value->name] = $line[$columnCounter];
                        }
                        $columnCounter++;
                    }
                    break;
                case 'array_key_value':
                    $aReturn['result'][$line[0]]                  = $line[1];
                    break;
                case 'array_key_value2':
                    $aReturn['result'][$line[0]][]                = $line[1];
                    break;
                case 'array_key2_value':
                    $aReturn['result'][$line[0] . '@' . $line[1]] = $line[1];
                    break;
                case 'array_numbered':
                    $aReturn['result'][]                          = $line[0];
                    break;
                case 'array_pairs_key_value':
                    $finfo                                        = $parameters['QueryResult']->fetch_fields();
                    $columnCounter                                = 0;
                    foreach ($finfo as $value) {
                        $aReturn['result'][$value->name] = $line[$columnCounter];
                        $columnCounter++;
                    }
                    break;
                case 'full_array_key_numbered':
                    $finfo         = $parameters['QueryResult']->fetch_fields();
                    $columnCounter = 0;
                    foreach ($finfo as $value) {
                        $aReturn['result'][$counter2][$value->name] = $line[$columnCounter];
                        $columnCounter++;
                    }
                    $counter2++;
                    break;
                case 'full_array_key_numbered_with_record_number_prefix':
                    $parameters['prefix'] = 'RecordNo';
                // intentionally left open
                case 'full_array_key_numbered_with_prefix':
                    $finfo                = $parameters['QueryResult']->fetch_fields();
                    $columnCounter        = 0;
                    foreach ($finfo as $value) {
                        $aReturn['result'][$parameters['prefix']][$counter2][$value->name] = $line[$columnCounter];
                        $columnCounter++;
                    }
                    $counter2++;
                    break;
                case 'value':
                    $aReturn['result'] = $line[0];
                    break;
            }
        }
        return ['customError' => '', 'result' => $aReturn['result']];
    }

    protected function setMySQLquery2ServerConnected($result, $sReturnType)
    {
        if ($sReturnType == 'id') {
            return ['customError' => '', 'result' => $this->mySQLconnection->insert_id];
        } elseif ($sReturnType == 'lines') {
            return ['result' => $result->num_rows, 'customError' => ''];
        }
        $prm = [
            'NoOfColumns' => $result->field_count,
            'NoOfRows'    => $result->num_rows,
            'QueryResult' => $result,
            'returnType'  => $sReturnType,
            'return'      => $aReturn

        ];
        if (substr($sReturnType, -6) == 'prefix') {
            $prm['prefix'] = $ftrs['prefix'];

        }
        return $this->setMySQLquery2ServerByPattern($prm);
    }

    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			private function setFieldSpecific($fieldDetails)
352			{
353			if (in_array($fieldDetails['DATA_TYPE'], ['char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext'])) {
354			return ['M' => $fieldDetails['CHARACTER_MAXIMUM_LENGTH']];
355			} elseif (in_array($fieldDetails['DATA_TYPE'], ['decimal', 'numeric'])) {
356			return ['M' => $fieldDetails['NUMERIC_PRECISION'], 'd' => $fieldDetails['NUMERIC_SCALE']];
357			} elseif (in_array($fieldDetails['DATA_TYPE'], ['bigint', 'int', 'mediumint', 'smallint', 'tinyint'])) {
358			return $this->setFldLmtsExact($fieldDetails['DATA_TYPE']);
359			}
360			return $this->setFieldSpecificElse($fieldDetails);
361			}
362
363			private function setFieldSpecificElse($fieldDetails)
364			{
365			$map = ['date' => 10, 'datetime' => 19, 'enum' => 65536, 'set' => 64, 'time' => 8, 'timestamp' => 19];
366			if (array_key_exists($fieldDetails['DATA_TYPE'], $map)) {
367			return ['M' => $map[$fieldDetails['DATA_TYPE']]];
368			}
369			return ['M' => '???'];
370			}
371
372			private function setFldLmts($colType, $loLmt, $upLmt, $szN, $szUS)
373			{
374			$aReturn = ['m' => $loLmt, 'M' => $upLmt, 'l' => $szN];
375			if (strpos($colType, 'unsigned') !== false) {
376			$aReturn = ['m' => 0, 'M' => ($upLmt - $loLmt), 'l' => $szUS];
377			}
378			return $aReturn;
379			}
380
381			private function setFldLmtsExact($cTp)
382			{
383			$xct = [
384			'bigint' => ['l' => -9223372036854775808, 'L' => 9223372036854775807, 's' => 21, 'sUS' => 20],
385			'int' => ['l' => -2147483648, 'L' => 2147483647, 's' => 11, 'sUS' => 10],
386			'mediumint' => ['l' => -8388608, 'L' => 8388607, 's' => 9, 'sUS' => 8],
387			'smallint' => ['l' => -32768, 'L' => 32767, 's' => 6, 'sUS' => 5],
388			'tinyint' => ['l' => -128, 'L' => 127, 's' => 4, 'sUS' => 3],
389			];
390			$sReturn = null;
391			if (array_key_exists($cTp, $xct)) {
392			$sReturn = $this->setFldLmts($cTp, $xct[$cTp]['l'], $xct[$cTp]['L'], $xct[$cTp]['s'], $xct[$cTp]['sUS']);
393			}
394			return $sReturn;
395			}
396
397			/**
398			* Transmit Query to MySQL server and get results back
399			*
400			* @param string $sQuery
401			* @param string $sReturnType
402			* @param array $ftrs
403			* @return boolean\|array\|string
404			*/
405			protected function setMySQLquery2Server($sQuery, $sReturnType = null, $ftrs = null)
			0 ignored issues – show Unused Code introduced 2016-01-31 22:49 UTC by Report Bug Copy Issue Report The parameter `$ftrs` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history...
406			{
407			if (is_null($sReturnType)) {
408			$this->mySQLconnection->query(html_entity_decode($sQuery));
409			return '';
410			} elseif (is_null($this->mySQLconnection)) {
411			return ['customError' => $this->lclMsgCmn('i18n_MySQL_ConnectionNotExisting'), 'result' => null];
412			}
413			$result = $this->mySQLconnection->query(html_entity_decode($sQuery));
414			if ($result) {
415			return $this->setMySQLquery2ServerConnected($result, $sReturnType);
416			}
417			$erM = [$this->mySQLconnection->errno, $this->mySQLconnection->error];
418			$cErr = sprintf($this->lclMsgCmn('i18n_MySQL_QueryError'), $erM[0], $erM[1]);
419			return ['customError' => $cErr, 'result' => null];
420			}
421
422			/**
423			* Turns a raw query result into various structures
424			* based on different predefined $parameters['returnType'] value
425			*
426			* @param array $parameters
427			* @return array as ['customError' => '...', 'result' => '...']
428			*/
429			private function setMySQLquery2ServerByPattern($parameters)
430			{
431			$aReturn = $parameters['return'];
432			$vld = $this->setMySQLqueryValidateInputs($parameters);
433			if ($vld[1] !== '') {
434			return ['customError' => $vld[1], 'result' => ''];
435			}
436			$counter2 = 0;
437			for ($counter = 0; $counter < $parameters['NoOfRows']; $counter++) {
438			$line = $parameters['QueryResult']->fetch_row();
439			switch ($parameters['returnType']) {
440			case 'array_first_key_rest_values':
441			$finfo = $parameters['QueryResult']->fetch_fields();
442			$columnCounter = 0;
443			foreach ($finfo as $value) {
444			if ($columnCounter != 0) {
445			$aReturn['result'][$line[0]][$value->name] = $line[$columnCounter];
446			}
447			$columnCounter++;
448			}
449			break;
450			case 'array_key_value':
451			$aReturn['result'][$line[0]] = $line[1];
452			break;
453			case 'array_key_value2':
454			$aReturn['result'][$line[0]][] = $line[1];
455			break;
456			case 'array_key2_value':
457			$aReturn['result'][$line[0] . '@' . $line[1]] = $line[1];
458			break;
459			case 'array_numbered':
460			$aReturn['result'][] = $line[0];
461			break;
462			case 'array_pairs_key_value':
463			$finfo = $parameters['QueryResult']->fetch_fields();
464			$columnCounter = 0;
465			foreach ($finfo as $value) {
466			$aReturn['result'][$value->name] = $line[$columnCounter];
467			$columnCounter++;
468			}
469			break;
470			case 'full_array_key_numbered':
471			$finfo = $parameters['QueryResult']->fetch_fields();
472			$columnCounter = 0;
473			foreach ($finfo as $value) {
474			$aReturn['result'][$counter2][$value->name] = $line[$columnCounter];
475			$columnCounter++;
476			}
477			$counter2++;
478			break;
479			case 'full_array_key_numbered_with_record_number_prefix':
480			$parameters['prefix'] = 'RecordNo';
481			// intentionally left open
482			case 'full_array_key_numbered_with_prefix':
483			$finfo = $parameters['QueryResult']->fetch_fields();
484			$columnCounter = 0;
485			foreach ($finfo as $value) {
486			$aReturn['result'][$parameters['prefix']][$counter2][$value->name] = $line[$columnCounter];
487			$columnCounter++;
488			}
489			$counter2++;
490			break;
491			case 'value':
492			$aReturn['result'] = $line[0];
493			break;
494			}
495			}
496			return ['customError' => '', 'result' => $aReturn['result']];
497			}
498
499			protected function setMySQLquery2ServerConnected($result, $sReturnType)
500			{
501			if ($sReturnType == 'id') {
502			return ['customError' => '', 'result' => $this->mySQLconnection->insert_id];
503			} elseif ($sReturnType == 'lines') {
504			return ['result' => $result->num_rows, 'customError' => ''];
505			}
506			$prm = [
507			'NoOfColumns' => $result->field_count,
508			'NoOfRows' => $result->num_rows,
509			'QueryResult' => $result,
510			'returnType' => $sReturnType,
511			'return' => $aReturn
			0 ignored issues – show Bug introduced 2016-01-31 22:49 UTC by Report Bug Copy Issue Report The variable `$aReturn` does not exist. Did you forget to declare it? This check marks access to variables or properties that have not been declared yet. While PHP has no explicit notion of declaring a variable, accessing it before a value is assigned to it is most likely a bug. Loading history...
512			];
513			if (substr($sReturnType, -6) == 'prefix') {
514			$prm['prefix'] = $ftrs['prefix'];
			0 ignored issues – show Bug introduced 2016-01-31 22:49 UTC by Report Bug Copy Issue Report The variable `$ftrs` does not exist. Did you forget to declare it? This check marks access to variables or properties that have not been declared yet. While PHP has no explicit notion of declaring a variable, accessing it before a value is assigned to it is most likely a bug. Loading history...
515			}
516			return $this->setMySQLquery2ServerByPattern($prm);
517			}
518
519			private function setMySQLqueryValidateInputs($prm)
520			{
521			$rMap = $this->setMySQLqueryValidationMap();
522			if (array_key_exists($prm['returnType'], $rMap)) {
523			$elC = [$prm['NoOfRows'], $rMap[$prm['returnType']]['r'][0], $rMap[$prm['returnType']]['r'][1]];
524			if (filter_var($elC[0], FILTER_VALIDATE_INT, ['min_range' => $elC[1], 'max_range' => $elC[2]]) === false) {
525			$msg = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected' . $rMap[$prm['returnType']][2]);
526			return [false, sprintf($msg, $prm['NoOfColumns'])];
527			}
528			$elR = [$prm['NoOfColumns'], $rMap[$prm['returnType']]['c'][0], $rMap[$prm['returnType']]['c'][1]];
529			if (filter_var($elR[0], FILTER_VALIDATE_INT, ['min_range' => $elR[1], 'max_range' => $elR[2]])) {
530			return [true, ''];
531			}
532			$msg = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected' . $rMap[$prm['returnType']][1]);
533			return [false, sprintf($msg, $prm['NoOfColumns'])];
534			}
535			return [false, $prm['returnType'] . ' is not defined!'];
536			}
537
538			private function setMySQLqueryValidationMap()
539			{
540			$lngKey = 'full_array_key_numbered_with_record_number_prefix';
541			return [
542			'array_first_key_rest_values' => ['r' => [1, 999999], 'c' => [2, 99], 'AtLeast2ColsResultedOther'],
543			'array_key_value' => ['r' => [1, 999999], 'c' => [2, 2], '2ColumnsResultedOther'],
544			'array_key_value2' => ['r' => [1, 999999], 'c' => [2, 2], '2ColumnsResultedOther'],
545			'array_key2_value' => ['r' => [1, 999999], 'c' => [2, 2], '2ColumnsResultedOther'],
546			'array_numbered' => ['r' => [1, 999999], 'c' => [1, 1], '1ColumnResultedOther'],
547			'array_pairs_key_value' => ['r' => [1, 1], 'c' => [1, 99], '1RowManyColumnsResultedOther'],
548			'full_array_key_numbered' => ['r' => [1, 999999], 'c' => [1, 99], '1OrMoreRows0Resulted'],
549			'full_array_key_numbered_with_prefix' => ['r' => [1, 999999], 'c' => [1, 99], '1OrMoreRows0Resulted'],
550			$lngKey => ['r' => [1, 999999], 'c' => [1, 99], '1OrMoreRows0Resulted'],
551			'value' => ['r' => [1, 1], 'c' => [1, 1], '1ResultedOther'],
552			];
553			}
554			}
555

danielgp / common-lib

Push — master ( 36d546...f13e43 )

MySQLiByDanielGP::getMySQLlistColumns() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like