MySQLiByDanielGP::setMySQLquery2ServerByPatternKey() - Code Metrics - Inspection of "attempt to organize MySQL extracting data function..." - danielgp/common-lib - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( a8bd12...580071 )

by Daniel

created 2016-02-01 18:33 UTC

setMySQLquery2ServerByPatternKey() B

↳ Parent: MySQLiByDanielGP

Complexity

Conditions	5
Paths	5

Size

Total Lines	19
Code Lines	15

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
c	1
b	0
f	0
dl	0
loc	19
rs	8.8571
cc	5
eloc	15
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 '';
            }
            $this->mySQLconnection = null;
            $erNo                  = $this->mySQLconnection->connect_errno;
            $erMsg                 = $this->mySQLconnection->connect_error;
            $msg                   = $this->lclMsgCmn('i18n_Feedback_ConnectionError');
            return sprintf($msg, $erNo, $erMsg, $host, $port, $username, $database);
        }
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    /**
     * Turns a raw query result into various structures
     * based on different predefined $parameters['returnType'] value
     *
     * @param array $parameters
     * @return array as ['customError' => '...', 'result' => '...']
     */
    private function setMySQLquery2ServerByPattern($parameters)
    {
        $aReturn = $parameters['return'];
        $vld     = $this->setMySQLqueryValidateInputs($parameters);
        if ($vld[1] !== '') {
            return ['customError' => $vld[1], 'result' => ''];
        } elseif (in_array($parameters['returnType'], ['array_key_value', 'array_key_value2', 'array_key2_value'])) {
            return ['customError' => $vld[1], 'result' => $this->setMySQLquery2ServerByPatternKey($parameters)];
        }
        $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_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']];
    }

    private function setMySQLquery2ServerByPatternKey($parameters)
    {
        $aReturn = [];
        for ($counter = 0; $counter < $parameters['NoOfRows']; $counter++) {
            $line = $parameters['QueryResult']->fetch_row();
            switch ($parameters['returnType']) {
                case 'array_key_value':
                    $aReturn[$line[0]]                  = $line[1];
                    break;
                case 'array_key_value2':
                    $aReturn[$line[0]][]                = $line[1];
                    break;
                case 'array_key2_value':
                    $aReturn[$line[0] . '@' . $line[1]] = $line[1];
                    break;
            }
        }
        return $aReturn;
    }

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

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

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


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

danielgp / common-lib

Push — master ( a8bd12...580071 )

setMySQLquery2ServerByPatternKey() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like