hedera-web/rest/core/query.php

<?php

use Vn\Lib;
use Vn\Lib\Locale;
use Vn\Web\Security;
use Vn\Lib\Type;

class Query extends Vn\Web\JsonRequest {
	const PARAMS = ['sql'];
	const SECURITY = Security::INVOKER;

	function __construct($app) {
		parent::__construct($app);
		$this->utcTz = new DateTimeZone('UTC');

		$dbConf = $app->getConf()['db'];

		if (isset($dbConf['tz']))
			$this->tz = new DateTimeZone($dbConf['tz']);
		else
			$this->tz = $this->utcTz;
	}

	function run($db) {
		$results = [];
		$db->multiQuery($_REQUEST['sql']);

		do {
			$result = $db->storeResult();

			if ($result !== FALSE) {
				$results[] = $this->transformResult($result);
				$result->free();
			} else
				$results[] = TRUE;
		}
		while ($db->moreResults() && $db->nextResult());

		if ($db->checkWarnings()
		&& ($result = $db->query('SHOW WARNINGS'))) {
			$sql =
				'SELECT IFNULL(i.`description`, m.`description`) `description`, @warn `code`
					FROM `message` m
						LEFT JOIN `messageI18n` i
							ON i.`code` = m.`code` AND i.lang = #
					WHERE m.`code` = @warn';

			while ($row = $result->fetch_object()) {
				if ($row->Code == 1265
				&& ($warning = $db->getObject($sql, [Locale::get()])))
					trigger_error("{$warning->code}: {$warning->description}", E_USER_WARNING);
				else
					trigger_error("{$row->Code}: {$row->Message}", E_USER_WARNING);
			}
		}

		$db->checkError();
		return $results;
	}

	/**
	 * Transforms the database result into a JSON parseable object.
	 */
	function transformResult($result) {
		$tableMap = [];
		$columns = $result->fetch_fields();

		$resultMap =
		[
			'data'    => [],
			'columns' => [],
			'tables'  => []
		];
	
		for ($i = 0; $i < $result->field_count; $i++) {
			$column = $columns[$i];
		
			switch ($column->type) {
				case MYSQLI_TYPE_BIT:
					$type = Type::BOOLEAN;
					break;
				case MYSQLI_TYPE_TINY:
				case MYSQLI_TYPE_SHORT:
				case MYSQLI_TYPE_LONG:
				case MYSQLI_TYPE_LONGLONG:
				case MYSQLI_TYPE_INT24:
				case MYSQLI_TYPE_YEAR:
					$type = Type::INTEGER;
					break;
				case MYSQLI_TYPE_FLOAT:
				case MYSQLI_TYPE_DOUBLE:
				case MYSQLI_TYPE_DECIMAL:	
				case MYSQLI_TYPE_NEWDECIMAL:
					$type = Type::DOUBLE;
					break;
				case MYSQLI_TYPE_DATE:	
					$type = Type::DATE;
					break;
				case MYSQLI_TYPE_DATETIME:
				case MYSQLI_TYPE_TIMESTAMP:
					$type = Type::DATE_TIME;
					break;
				default;
					$type = Type::STRING;
			}

			if (!isset($tableMap[$column->table])) {
				$resultMap['tables'][] =
				[
					'name'     => $column->table,
					'orgname'  => $column->orgtable,
					'schema'   => $column->db,
					'pks'      => []
				];
				$tableIndex = count($resultMap['tables']) - 1;
				$tableMap[$column->table] = $tableIndex;	 
			} else
				$tableIndex = $tableMap[$column->table];
		
			if ($column->flags & MYSQLI_PRI_KEY_FLAG)
				$resultMap['tables'][$tableIndex]['pks'][] = $column->name;
		
			$default = $this->castValue($column->def, $type);

			$resultMap['columns'][] = [
				'type'     => $type,
				'flags'    => $column->flags,
				'def'      => $default,
				'name'     => $column->name,
				'orgname'  => $column->orgname,
				'table'    => $tableIndex
			];
		}
	
		$columns = $resultMap['columns'];

		while ($row = $result->fetch_row()) {
			for ($j = 0; $j < $result->field_count; $j++)
				$row[$j] = $this->castValue($row[$j], $columns[$j]['type']);

			$resultMap['data'][] = $row;
		}

		return $resultMap;
	}

	/**
	 * Transforms the database value into a JSON parseable value.
	 */
	function castValue($value, $type) {
		if ($value === '' && $type != Type::STRING)
			$value = NULL;

		if ($value !== NULL)
		switch ($type) {
			case Type::BOOLEAN:
				return (bool) $value;
			case Type::INTEGER:
				return (int) $value;
			case Type::DOUBLE:
				return (float) $value;
			case Type::DATE:
			case Type::DATE_TIME:
				$d = new DateTime($value, $this->tz);
				$d->setTimeZone($this->utcTz);
				return $d->format('Y-m-d\TH:i:s.v\Z');
				break;
		}
		
		return $value;
	}

}