MySQLiByDanielGP::getMySQLlistMultiple() - Code Metrics - Inspection of "code optimization to reduce complexity and enhance..." - danielgp/common-lib - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 3c703a...fdf671 )

by Daniel

created 2016-01-30 22:34 UTC

MySQLiByDanielGP::getMySQLlistMultiple() B

↳ Parent: MySQLiByDanielGP

Complexity

Conditions	4
Paths	4

Size

Total Lines	23
Code Lines	17

Duplication

Lines	0
Ratio	0 %

Importance

Changes	5
Bugs	0	Features	1

Metric	Value
c	5
b	0
f	1
dl	0
loc	23
rs	8.7972
cc	4
eloc	17
nc	4
nop	3

<?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 '';
            }
            $erNo                  = $this->mySQLconnection->connect_errno;
            $erMsg                 = $this->mySQLconnection->connect_error;
            $this->mySQLconnection = null;
            $msg                   = $this->lclMsgCmn('i18n_Feedback_ConnectionError');
            return sprintf($msg, $erNo, $erMsg, $host, $port, $username, $database);
        }
    }

    /**
     * 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 string
     */
    private function getMySQLlistMultiple($returnChoice, $returnType, $additionalFeatures = null)
    {
        if (is_null($this->mySQLconnection)) {
            if ($returnType == 'value') {
                return null;
            }
            return [];
        }
        $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->get($key, $vToSet);
            }
        }
    }

    /**
     * Transforms an array into usable filters
     *
     * @param array $entryArray
     * @param string $referenceTable
     * @return array
     */
    private function setArrayToFilterValues($entryArray, $referenceTable = '')
    {
        $filters = '';
        if ($referenceTable != '') {
            $referenceTable = '`' . $referenceTable . '`.';
        }
        foreach ($entryArray as $key => $value) {
            if (is_array($value)) {
                $filters2 = '';
                foreach ($value as $value2) {
                    if ($value2 != '') {
                        if ($filters2 != '') {
                            $filters2 .= ',';
                        }
                        $filters2 .= '"' . $value2 . '"';
                    }
                }
                if ($filters2 != '') {
                    if ($filters != '') {
                        $filters .= ' AND ';
                    }
                    $filters .= ' ' . $referenceTable . '`' . $key
                            . '` IN ("' . str_replace(',', '","', str_replace(["'", '"'], '', $filters2))
                            . '")';
                }
            } else {
                if (($filters != '') && (!in_array($value, ['', '%%']))) {
                    $filters .= ' AND ';
                }
                if (!in_array($value, ['', '%%'])) {
                    if ((substr($value, 0, 1) == '%') && (substr($value, -1) == '%')) {
                        $filters .= ' ' . $key . ' LIKE "' . $value . '"';
                    } else {
                        $filters .= ' ' . $key . ' = "' . $value . '"';
                    }
                }
            }
        }
        return $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']);
        }
        $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)
    {
        $aReturn = [
            'customError' => '',
            'result'      => null
        ];
        if (is_null($sReturnType)) {
            return $this->mySQLconnection->query(html_entity_decode($sQuery));
        } elseif (is_null($this->mySQLconnection)) {
            $aReturn['customError'] = $this->lclMsgCmn('i18n_MySQL_ConnectionNotExisting');
        } else {
            $result = $this->mySQLconnection->query(html_entity_decode($sQuery));
            if ($result) {
                switch (strtolower($sReturnType)) {
                    case 'array_first_key_rest_values':
                    case 'array_key_value':
                    case 'array_key_value2':
                    case 'array_key2_value':
                    case 'array_numbered':
                    case 'array_pairs_key_value':
                    case 'full_array_key_numbered':

                        $aReturn           = $this->setMySQLquery2ServerByPattern([
                            'NoOfColumns' => $result->field_count,
                            'NoOfRows'    => $result->num_rows,
                            'QueryResult' => $result,
                            'returnType'  => $sReturnType,
                            'return'      => $aReturn
                        ]);
                        break;
                    case 'full_array_key_numbered_with_record_number_prefix':
                    case 'full_array_key_numbered_with_prefix':

                        $aReturn           = $this->setMySQLquery2ServerByPattern([
                            'NoOfColumns' => $result->field_count,
                            'NoOfRows'    => $result->num_rows,
                            'QueryResult' => $result,
                            'returnType'  => $sReturnType,
                            'prefix'      => $ftrs['prefix'],
                            'return'      => $aReturn
                        ]);
                        break;
                    case 'id':
                        $aReturn['result'] = $this->mySQLconnection->insert_id;
                        break;
                    case 'lines':
                        $aReturn['result'] = $result->num_rows;
                        break;
                    case 'value':
                        if (($result->num_rows == 1) && ($result->field_count == 1)) {
                            $aReturn['result'] = $result->fetch_row()[0];
                        } else {
                            $msg                    = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected1ResultedOther');
                            $aReturn['customError'] = sprintf($msg, $result->num_rows);
                        }
                        break;
                    default:
                        $msg                    = $this->lclMsgCmn('i18n_MySQL_QueryInvalidReturnTypeSpecified');
                        $aReturn['customError'] = sprintf($msg, $sReturnType, __FUNCTION__);
                        break;
                }
                if (is_object($result)) {
                    $result->close();
                }
            } else {
                $erNo                   = $this->mySQLconnection->errno;
                $erMsg                  = $this->mySQLconnection->error;
                $aReturn['customError'] = sprintf($this->lclMsgCmn('i18n_MySQL_QueryError'), $erNo, $erMsg);
            }
        }
        return $aReturn;
    }

    /**
     * Turns a raw query result into various structures
     * based on different predefined $parameters['returnType'] value
     *
     * @param array $parameters
     * @return array as ['customError' => '...', 'result' => '...']
     */
    protected function setMySQLquery2ServerByPattern($parameters)
    {
        $aReturn    = $parameters['return'];
        $buildArray = false;
        switch ($parameters['returnType']) {
            case 'array_first_key_rest_values':

                if ($parameters['NoOfColumns'] >= 2) {
                    $buildArray = true;
                } else {
                    $msg                    = $this->lclMsgCmn('QueryResultExpectedAtLeast2ColsResultedOther');
                    $aReturn['customError'] = sprintf($msg, $parameters['NoOfColumns']);
                }
                break;
            case 'array_key_value':
            case 'array_key_value2':
            case 'array_key2_value':

                if ($parameters['NoOfColumns'] == 2) {
                    $buildArray = true;
                } else {
                    $msg                    = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected2ColumnsResultedOther');
                    $aReturn['customError'] = sprintf($msg, $parameters['NoOfColumns']);
                }
                break;
            case 'array_numbered':

                if ($parameters['NoOfColumns'] == 1) {
                    $buildArray = true;
                } else {
                    $msg                    = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected1ColumnResultedOther');
                    $aReturn['customError'] = sprintf($msg, $parameters['NoOfColumns']);
                }
                break;
            case 'array_pairs_key_value':
                if (($parameters['NoOfRows'] == 1) && ($parameters['NoOfColumns'] > 1)) {
                    $buildArray = true;
                } else {
                    $shorterLclString       = 'i18n_MySQL_QueryResultExpected1RowManyColumnsResultedOther';
                    $msg                    = $this->lclMsgCmn($shorterLclString);
                    $aReturn['customError'] = sprintf($msg, $parameters['NoOfRows'], $parameters['NoOfColumns']);
                }
                break;
            case 'full_array_key_numbered':
            case 'full_array_key_numbered_with_prefix':
            case 'full_array_key_numbered_with_record_number_prefix':
                if ($parameters['NoOfColumns'] == 0) {
                    $aReturn['customError'] = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected1OrMoreRows0Resulted');
                    if (in_array($parameters['returnType'], [
                                'full_array_key_numbered_with_prefix',
                                'full_array_key_numbered_with_record_number_prefix',
                            ])) {
                        $aReturn['result'][$parameters['prefix']] = null;
                    }
                } else {
                    $buildArray = true;
                }
                break;
            default:
                $aReturn['customError'] = $parameters['returnType'] . ' is not defined!';
                break;
        }
        if ($buildArray) {
            $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;
                }
            }
        }
        return $aReturn;
    }
}


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

danielgp / common-lib

Push — master ( 3c703a...fdf671 )

MySQLiByDanielGP::getMySQLlistMultiple() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like