MySQLiByDanielGP::setMySQLqueryValidateInputs() - Code Metrics - Inspection of "optimizations to reduce lines detected as duplicat..." - danielgp/common-lib - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( c981de...36d546 )

by Daniel

created 2016-01-31 15:11 UTC

MySQLiByDanielGP::setMySQLqueryValidateInputs() B

↳ Parent: MySQLiByDanielGP

Complexity

Conditions	5
Paths	5

Size

Total Lines	22
Code Lines	16

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
c	1
b	0
f	0
dl	0
loc	22
rs	8.6737
cc	5
eloc	16
nc	5
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 '';
            }
            $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);
        }
    }

    /**
     * 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)) {
            return $this->mySQLconnection->query(html_entity_decode($sQuery));
        } elseif (is_null($this->mySQLconnection)) {
            return ['customError' => $this->lclMsgCmn('i18n_MySQL_ConnectionNotExisting'), 'result' => null];
        }
        $aReturn = ['customError' => '', 'result' => null];
        $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];
                        return $aReturn;
                    }
                    $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();
            }
            return $aReturn;
        }
        $erNo  = $this->mySQLconnection->errno;
        $erMsg = $this->mySQLconnection->error;
        return ['customError' => sprintf($this->lclMsgCmn('i18n_MySQL_QueryError'), $erNo, $erMsg), '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;
            }
        }
        return $aReturn;
    }

    private function setMySQLqueryValidateInputs($prm)
    {
        $rMap = $this->setMySQLqueryValidationMap();
        if (array_key_exists($prm['returnType'], $rMap)) {
            $nrInt = $prm['NoOfColumns'];
            $min   = $rMap[$prm['returnType']]['c'][0];
            $max   = $rMap[$prm['returnType']]['c'][1];
            if (filter_var($nrInt, FILTER_VALIDATE_INT, ['min_range' => $min, 'max_range' => $max])) {
                if (array_key_exists('r', $rMap[$prm['returnType']])) {
                    $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]])) {
                        return [true, ''];
                    }
                } else {
                    return [true, ''];
                }
            }
            $msg = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected' . $rMap[$prm['returnType']][2]);
            return [false, sprintf($msg, $prm['NoOfColumns'])];
        }
        return [false, $prm['returnType'] . ' is not defined!'];
    }

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


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		* 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)
406		{
407		if (is_null($sReturnType)) {
408		return $this->mySQLconnection->query(html_entity_decode($sQuery));
409		} elseif (is_null($this->mySQLconnection)) {
410		return ['customError' => $this->lclMsgCmn('i18n_MySQL_ConnectionNotExisting'), 'result' => null];
411		}
412		$aReturn = ['customError' => '', 'result' => null];
413		$result = $this->mySQLconnection->query(html_entity_decode($sQuery));
414		if ($result) {
415		switch (strtolower($sReturnType)) {
416		case 'array_first_key_rest_values':
417		case 'array_key_value':
418		case 'array_key_value2':
419		case 'array_key2_value':
420		case 'array_numbered':
421		case 'array_pairs_key_value':
422	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...
423		$aReturn = $this->setMySQLquery2ServerByPattern([
424		'NoOfColumns' => $result->field_count,
425		'NoOfRows' => $result->num_rows,
426		'QueryResult' => $result,
427		'returnType' => $sReturnType,
428		'return' => $aReturn
429		]);
430		break;
431		case 'full_array_key_numbered_with_record_number_prefix':
432	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...
433		$aReturn = $this->setMySQLquery2ServerByPattern([
434		'NoOfColumns' => $result->field_count,
435		'NoOfRows' => $result->num_rows,
436		'QueryResult' => $result,
437		'returnType' => $sReturnType,
438		'prefix' => $ftrs['prefix'],
439		'return' => $aReturn
440		]);
441		break;
442		case 'id':
443		$aReturn['result'] = $this->mySQLconnection->insert_id;
444		break;
445		case 'lines':
446		$aReturn['result'] = $result->num_rows;
447		break;
448		case 'value':
449		if (($result->num_rows == 1) && ($result->field_count == 1)) {
450		$aReturn['result'] = $result->fetch_row()[0];
451		return $aReturn;
452		}
453		$msg = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected1ResultedOther');
454		$aReturn['customError'] = sprintf($msg, $result->num_rows);
455		break;
456		default:
457		$msg = $this->lclMsgCmn('i18n_MySQL_QueryInvalidReturnTypeSpecified');
458		$aReturn['customError'] = sprintf($msg, $sReturnType, __FUNCTION__);
459		break;
460		}
461		if (is_object($result)) {
462		$result->close();
463		}
464		return $aReturn;
465		}
466		$erNo = $this->mySQLconnection->errno;
467		$erMsg = $this->mySQLconnection->error;
468		return ['customError' => sprintf($this->lclMsgCmn('i18n_MySQL_QueryError'), $erNo, $erMsg), 'result' => null];
469		}
470
471		/**
472		* Turns a raw query result into various structures
473		* based on different predefined $parameters['returnType'] value
474		*
475		* @param array $parameters
476		* @return array as ['customError' => '...', 'result' => '...']
477		*/
478		private function setMySQLquery2ServerByPattern($parameters)
479		{
480		$aReturn = $parameters['return'];
481		$vld = $this->setMySQLqueryValidateInputs($parameters);
482		if ($vld[1] !== '') {
483		return ['customError' => $vld[1], 'result' => ''];
484		}
485		$counter2 = 0;
486		for ($counter = 0; $counter < $parameters['NoOfRows']; $counter++) {
487		$line = $parameters['QueryResult']->fetch_row();
488		switch ($parameters['returnType']) {
489		case 'array_first_key_rest_values':
490		$finfo = $parameters['QueryResult']->fetch_fields();
491		$columnCounter = 0;
492		foreach ($finfo as $value) {
493		if ($columnCounter != 0) {
494		$aReturn['result'][$line[0]][$value->name] = $line[$columnCounter];
495		}
496		$columnCounter++;
497		}
498		break;
499		case 'array_key_value':
500		$aReturn['result'][$line[0]] = $line[1];
501		break;
502		case 'array_key_value2':
503		$aReturn['result'][$line[0]][] = $line[1];
504		break;
505		case 'array_key2_value':
506		$aReturn['result'][$line[0] . '@' . $line[1]] = $line[1];
507		break;
508		case 'array_numbered':
509		$aReturn['result'][] = $line[0];
510		break;
511		case 'array_pairs_key_value':
512		$finfo = $parameters['QueryResult']->fetch_fields();
513		$columnCounter = 0;
514		foreach ($finfo as $value) {
515		$aReturn['result'][$value->name] = $line[$columnCounter];
516		$columnCounter++;
517		}
518		break;
519		case 'full_array_key_numbered':
520		$finfo = $parameters['QueryResult']->fetch_fields();
521		$columnCounter = 0;
522		foreach ($finfo as $value) {
523		$aReturn['result'][$counter2][$value->name] = $line[$columnCounter];
524		$columnCounter++;
525		}
526		$counter2++;
527		break;
528		case 'full_array_key_numbered_with_record_number_prefix':
529		$parameters['prefix'] = 'RecordNo';
530		// intentionally left open
531		case 'full_array_key_numbered_with_prefix':
532		$finfo = $parameters['QueryResult']->fetch_fields();
533		$columnCounter = 0;
534		foreach ($finfo as $value) {
535		$aReturn['result'][$parameters['prefix']][$counter2][$value->name] = $line[$columnCounter];
536		$columnCounter++;
537		}
538		$counter2++;
539		break;
540		}
541		}
542		return $aReturn;
543		}
544
545		private function setMySQLqueryValidateInputs($prm)
546		{
547		$rMap = $this->setMySQLqueryValidationMap();
548		if (array_key_exists($prm['returnType'], $rMap)) {
549		$nrInt = $prm['NoOfColumns'];
550		$min = $rMap[$prm['returnType']]['c'][0];
551		$max = $rMap[$prm['returnType']]['c'][1];
552		if (filter_var($nrInt, FILTER_VALIDATE_INT, ['min_range' => $min, 'max_range' => $max])) {
553		if (array_key_exists('r', $rMap[$prm['returnType']])) {
554		$elC = [$prm['NoOfRows'], $rMap[$prm['returnType']]['r'][0], $rMap[$prm['returnType']]['r'][1]];
555		if (filter_var($elC[0], FILTER_VALIDATE_INT, ['min_range' => $elC[1], 'max_range' => $elC[2]])) {
556		return [true, ''];
557		}
558		} else {
559		return [true, ''];
560		}
561		}
562		$msg = $this->lclMsgCmn('i18n_MySQL_QueryResultExpected' . $rMap[$prm['returnType']][2]);
563		return [false, sprintf($msg, $prm['NoOfColumns'])];
564		}
565		return [false, $prm['returnType'] . ' is not defined!'];
566		}
567
568		private function setMySQLqueryValidationMap()
569		{
570		return [
571		'array_first_key_rest_values' => ['c' => [2, 99], 'AtLeast2ColsResultedOther'],
572		'array_key_value' => ['c' => [2, 2], '2ColumnsResultedOther'],
573		'array_key_value2' => ['c' => [2, 2], '2ColumnsResultedOther'],
574		'array_key2_value' => ['c' => [2, 2], '2ColumnsResultedOther'],
575		'array_numbered' => ['c' => [1, 1], '1ColumnResultedOther'],
576		'array_pairs_key_value' => [
577		'r' => [1, 1],
578		'c' => [1, 99],
579		'1RowManyColumnsResultedOther',
580		],
581		'full_array_key_numbered' => ['c' => [1, 99], '1OrMoreRows0Resulted'],
582		'full_array_key_numbered_with_prefix' => ['c' => [1, 99], '1OrMoreRows0Resulted'],
583		'full_array_key_numbered_with_record_number_prefix' => ['c' => [1, 99], '1OrMoreRows0Resulted'],
584		];
585		}
586		}
587

danielgp / common-lib

Push — master ( c981de...36d546 )

MySQLiByDanielGP::setMySQLqueryValidateInputs() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like