code:
<?php

/**
 * This file is part of the Nette Framework (https://nette.org)
 * Copyright (c) 2004 David Grudl (https://davidgrudl.com)
 */

namespace Nette\Utils;

use Nette;

/**
 * Validation utilities.
 */
class Validators extends Nette\Object
{
	protected static $validators = array(
		'bool' => 'is_bool',
		'boolean' => 'is_bool',
		'int' => 'is_int',
		'integer' => 'is_int',
		'float' => 'is_float',
		'number' => NULL, // is_int || is_float,
		'numeric' => array(__CLASS__, 'isNumeric'),
		'numericint' => array(__CLASS__, 'isNumericInt'),
		'string' => 'is_string',
		'unicode' => array(__CLASS__, 'isUnicode'),
		'array' => 'is_array',
		'list' => array('Nette\Utils\Arrays', 'isList'),
		'object' => 'is_object',
		'resource' => 'is_resource',
		'scalar' => 'is_scalar',
		'callable' => array(__CLASS__, 'isCallable'),
		'null' => 'is_null',
		'email' => array(__CLASS__, 'isEmail'),
		'url' => array(__CLASS__, 'isUrl'),
		'uri' => array(__CLASS__, 'isUri'),
		'none' => array(__CLASS__, 'isNone'),
		'type' => array(__CLASS__, 'isType'),
		'identifier' => array(__CLASS__, 'isPhpIdentifier'),
		'pattern' => NULL,
		'alnum' => 'ctype_alnum',
		'alpha' => 'ctype_alpha',
		'digit' => 'ctype_digit',
		'lower' => 'ctype_lower',
		'upper' => 'ctype_upper',
		'space' => 'ctype_space',
		'xdigit' => 'ctype_xdigit',
	);

	protected static $counters = array(
		'string' => 'strlen',
		'unicode' => array('Nette\Utils\Strings', 'length'),
		'array' => 'count',
		'list' => 'count',
		'alnum' => 'strlen',
		'alpha' => 'strlen',
		'digit' => 'strlen',
		'lower' => 'strlen',
		'space' => 'strlen',
		'upper' => 'strlen',
		'xdigit' => 'strlen',
	);

	/**
	 * Throws exception if a variable is of unexpected type.
	 * @param  mixed
	 * @param  string  expected types separated by pipe
	 * @param  string  label
	 * @return void
	 */
	public static function assert($value, $expected, $label = 'variable')
	{
		if (!static::is($value, $expected)) {
			$expected = str_replace(array('|', ':'), array(' or ', ' in range '), $expected);
			if (is_array($value)) {
				$type = 'array(' . count($value) . ')';
			} elseif (is_object($value)) {
				$type = 'object ' . get_class($value);
			} elseif (is_string($value) && strlen($value) < 40) {
				$type = "string '$value'";
			} else {
				$type = gettype($value);
			}
			throw new AssertionException("The $label expects to be $expected, $type given.");
		}
	}

	/**
	 * Throws exception if an array field is missing or of unexpected type.
	 * @param  array
	 * @param  string  item
	 * @param  string  expected types separated by pipe
	 * @param  string
	 * @return void
	 */
	public static function assertField($arr, $field, $expected = NULL, $label = "item '%' in array")
	{
		self::assert($arr, 'array', 'first argument');
		if (!array_key_exists($field, $arr)) {
			throw new AssertionException('Missing ' . str_replace('%', $field, $label) . '.');

		} elseif ($expected) {
			static::assert($arr[$field], $expected, str_replace('%', $field, $label));
		}
	}

	/**
	 * Finds whether a variable is of expected type.
	 * @param  mixed
	 * @param  string  expected types separated by pipe with optional ranges
	 * @return bool
	 */
	public static function is($value, $expected)
	{
		foreach (explode('|', $expected) as $item) {
			list($type) = $item = explode(':', $item, 2);
			if (isset(static::$validators[$type])) {
				if (!call_user_func(static::$validators[$type], $value)) {
					continue;
				}
			} elseif ($type === 'number') {
				if (!is_int($value) && !is_float($value)) {
					continue;
				}
			} elseif ($type === 'pattern') {
				if (preg_match('|^' . (isset($item[1]) ? $item[1] : '') . '\z|', $value)) {
					return TRUE;
				}
				continue;
			} elseif (!$value instanceof $type) {
				continue;
			}

			if (isset($item[1])) {
				$length = $value;
				if (isset(static::$counters[$type])) {
					$length = call_user_func(static::$counters[$type], $value);
				}
				$range = explode('..', $item[1]);
				if (!isset($range[1])) {
					$range[1] = $range[0];
				}
				if (($range[0] !== '' && $length < $range[0]) || ($range[1] !== '' && $length > $range[1])) {
					continue;
				}
			}
			return TRUE;
		}
		return FALSE;
	}

	/**
	 * Finds whether a value is an integer.
	 * @return bool
	 */
	public static function isNumericInt($value)
	{
		return is_int($value) || is_string($value) && preg_match('#^-?[0-9]+\z#', $value);
	}

	/**
	 * Finds whether a string is a floating point number in decimal base.
	 * @return bool
	 */
	public static function isNumeric($value)
	{
		return is_float($value) || is_int($value) || is_string($value) && preg_match('#^-?[0-9]*[.]?[0-9]+\z#', $value);
	}

	/**
	 * Finds whether a value is a syntactically correct callback.
	 * @return bool
	 */
	public static function isCallable($value)
	{
		return $value && is_callable($value, TRUE);
	}

	/**
	 * Finds whether a value is an UTF-8 encoded string.
	 * @param  string
	 * @return bool
	 */
	public static function isUnicode($value)
	{
		return is_string($value) && preg_match('##u', $value);
	}

	/**
	 * Finds whether a value is "falsy".
	 * @return bool
	 */
	public static function isNone($value)
	{
		return $value == NULL; // intentionally ==
	}

	/**
	 * Finds whether a variable is a zero-based integer indexed array.
	 * @param  array
	 * @return bool
	 */
	public static function isList($value)
	{
		return Arrays::isList($value);
	}

	/**
	 * Is a value in specified range?
	 * @param  mixed
	 * @param  array  min and max value pair
	 * @return bool
	 */
	public static function isInRange($value, $range)
	{
		return (!isset($range[0]) || $range[0] === '' || $value >= $range[0])
			&& (!isset($range[1]) || $range[1] === '' || $value <= $range[1]);
	}

	/**
	 * Finds whether a string is a valid email address.
	 * @param  string
	 * @return bool
	 */
	public static function isEmail($value)
	{
		$atom = "[-a-z0-9!#$%&'*+/=?^_`{|}~]"; // RFC 5322 unquoted characters in local-part
		$alpha = "a-z\x80-\xFF"; // superset of IDN
		return (bool) preg_match("(^
			(\"([ !#-[\\]-~]*|\\\\[ -~])+\"|$atom+(\\.$atom+)*)  # quoted or unquoted
			@
			([0-9$alpha]([-0-9$alpha]{0,61}[0-9$alpha])?\\.)+    # domain - RFC 1034
			[$alpha]([-0-9$alpha]{0,17}[$alpha])?                # top domain
		\\z)ix", $value);
	}

	/**
	 * Finds whether a string is a valid http(s) URL.
	 * @param  string
	 * @return bool
	 */
	public static function isUrl($value)
	{
		$alpha = "a-z\x80-\xFF";
		return (bool) preg_match("(^
			https?://(
				(([-_0-9$alpha]+\\.)*                       # subdomain
					[0-9$alpha]([-0-9$alpha]{0,61}[0-9$alpha])?\\.)?  # domain
					[$alpha]([-0-9$alpha]{0,17}[$alpha])?   # top domain
				|\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}  # IPv4
				|\[[0-9a-f:]{3,39}\]                        # IPv6
			)(:\\d{1,5})?                                   # port
			(/\\S*)?                                        # path
		\\z)ix", $value);
	}

	/**
	 * Finds whether a string is a valid URI according to RFC 1738.
	 * @param  string
	 * @return bool
	 */
	public static function isUri($value)
	{
		return (bool) preg_match('#^[a-z\d+\.-]+:\S+\z#i', $value);
	}

	/**
	 * Checks whether the input is a class, interface or trait.
	 * @param  string
	 * @return bool
	 */
	public static function isType($type)
	{
		return class_exists($type) || interface_exists($type) || (PHP_VERSION_ID >= 50400 && trait_exists($type));
	}

	/**
	 * Checks whether the input is a valid PHP identifier.
	 * @return bool
	 */
	public static function isPhpIdentifier($value)
	{
		return is_string($value) && preg_match('#^[a-zA-Z_\x7f-\xff][a-zA-Z0-9_\x7f-\xff]*\z#', $value);
	}

}


code:
<?php

/**
 * This file is part of the Nette Framework (https://nette.org)
 * Copyright (c) 2004 David Grudl (https://davidgrudl.com)
 */

namespace Nette;

/**
 * The exception that is thrown when the value of an argument is
 * outside the allowable range of values as defined by the invoked method.
 */
class ArgumentOutOfRangeException extends \InvalidArgumentException
{
}

/**
 * The exception that is thrown when a method call is invalid for the object's
 * current state, method has been invoked at an illegal or inappropriate time.
 */
class InvalidStateException extends \RuntimeException
{
}

/**
 * The exception that is thrown when a requested method or operation is not implemented.
 */
class NotImplementedException extends \LogicException
{
}

/**
 * The exception that is thrown when an invoked method is not supported. For scenarios where
 * it is sometimes possible to perform the requested operation, see InvalidStateException.
 */
class NotSupportedException extends \LogicException
{
}

/**
 * The exception that is thrown when a requested method or operation is deprecated.
 */
class DeprecatedException extends NotSupportedException
{
}

/**
 * The exception that is thrown when accessing a class member (property or method) fails.
 */
class MemberAccessException extends \LogicException
{
}

/**
 * The exception that is thrown when an I/O error occurs.
 */
class IOException extends \RuntimeException
{
}

/**
 * The exception that is thrown when accessing a file that does not exist on disk.
 */
class FileNotFoundException extends IOException
{
}

/**
 * The exception that is thrown when part of a file or directory cannot be found.
 */
class DirectoryNotFoundException extends IOException
{
}

/**
 * The exception that is thrown when an argument does not match with the expected value.
 */
class InvalidArgumentException extends \InvalidArgumentException
{
}

/**
 * The exception that is thrown when an illegal index was requested.
 */
class OutOfRangeException extends \OutOfRangeException
{
}

/**
 * The exception that is thrown when a value (typically returned by function) does not match with the expected value.
 */
class UnexpectedValueException extends \UnexpectedValueException
{
}

/**
 * The exception that is thrown when static class is instantiated.
 */
class StaticClassException extends \LogicException
{
}

namespace Nette\Utils;

/**
 * The exception that is thrown when an image error occurs.
 */
class ImageException extends \Exception
{
}

/**
 * The exception that indicates invalid image file.
 */
class UnknownImageFileException extends ImageException
{
}

/**
 * The exception that indicates error of JSON encoding/decoding.
 */
class JsonException extends \Exception
{
}

/**
 * The exception that indicates error of the last Regexp execution.
 */
class RegexpException extends \Exception
{
}

/**
 * The exception that indicates assertion error.
 */
class AssertionException extends \Exception
{
}

repo_name: Cerx/ToDo

license: mit


code:
language: php
php:
    - 5.5
    - 5.6
    - 7.0
    - hhvm

matrix:
    fast_finish: true

sudo: false

cache:
    directories:
        - $HOME/.composer/cache
        - vendor

before_script:
    # Create php.ini & sections.ini
    - cp ./tests/php.unix-sample.ini ./tests/php.ini

    - if [ "$TRAVIS_PHP_VERSION" == "7.0" ]; then cat ~/.phpenv/versions/$(phpenv version-name)/etc/conf.d/xdebug.ini >> ./tests/php.ini; fi
    - if [ "$TRAVIS_PHP_VERSION" == "7.0" ]; then NTESTER_FLAGS="--coverage ./coverage.xml --coverage-src ./src"; else TESTER_FLAGS=""; fi

    - if [ "$TRAVIS_PHP_VERSION" == "hhvm" ]; then cp ./tests/databases.hhvm.ini ./tests/databases.ini; fi
    - if ! [ "$TRAVIS_PHP_VERSION" == "hhvm" ]; then cp ./tests/databases.sample.ini ./tests/databases.ini; fi

    # Create MySQL & Postgre database
    - psql -c 'CREATE DATABASE nextras_dbal_test' -U postgres
    - mysql -e 'CREATE DATABASE nextras_dbal_test;'

    - mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

    # Install dependencies
    - if ! [ "$TRAVIS_PHP_VERSION" == "hhvm" ]; then phpenv config-rm xdebug.ini; fi
    - travis_retry composer update --no-interaction

script: ./tests/run.sh -s $NTESTER_FLAGS ./tests/cases

after_script:
    - if [ "$TRAVIS_PHP_VERSION" == "7.0" ]; then
      wget https://github.com/satooshi/php-coveralls/releases/download/v1.0.1/coveralls.phar
      && php coveralls.phar --verbose --config tests/.coveralls.yml
      || true;
      fi

after_failure:
    # Print *.actual content & log content
    - for i in $(find tests -name \*.actual); do echo "--- $i"; cat $i; echo; echo; done
    - for i in $(find tests -name \*.log); do echo "--- $i"; cat $i; echo; echo; done


nextras/dbal

type: library

description: Nextras database abstraction layer

keywords: database nextras dbal

license: MIT

Nextras\Dbal\: src/


code:
DateTime TimeZones Support
##########################

Let us recapitulate some facts:

- databases *do not store* timezone information in columns;
- database connections can work in different timezone than php environment;
- databases have two types of columns for time stamps:
	- with timezone conversion (MySQL: timestamp, PgSQL: timestamptz);
	- without timezone conversion (MySQL: datetime, PgSQL: timestamp);
- for columns **with timezone conversion**:
	- MySQL:
		- writing: passed time stamp is interpreted as it is in connection timezone;
		- reading: time stamp is converted to connection timezone;
	- PgSQL:
		- writing: passed time stamp can contain timezone; otherwise passed time stamp is interpreted as it is in connection timezone;
		- reading: time stamp is converted to connection timezone;
- for column **without timezone conversion**:
	- MySQL and PgSQL stores passed timestamp as is; reads it as is;

------------------

Dbal offers you three options to configure:

- **application time zone** - is timezone in which every read time stamp from database will be transformed; by default this timezone equals to php default timezone;
- **connection time zone** - is timezone for database connection communication; by default it equals to php default timezone;
- **simple storage time zone** - is timezone for column types without automatic timezone conversion; this setting is introduced mainly for backward compatibility, if you migrate from another database abstraction layer, your column values are probably stored in different timezone.

Dbal comes with two query modifiers which are designed for two possible time stamp types:

- **`%dt` modifier** - converts DateTime object to connection working timezone; you can change the timezone by `connectionTz` setting; should be applied to column types with automatic timezone conversion support (timestamp in MySQL, timestamptz in PgSQL);
- **`%dts` modifier** - converts DateTime object for simple time stamp column types; by default converts them into UTC timezone, you can change it by `simpleStorageTz` setting; should be applied to column types without automatic timezone conversion support (datetime in MySQL, timestamp in PgSQL);

------------------

For better understanding see the workflow chart:

[* datetime.png *]


code:
Nextras\Dbal
############

Connection
==========

The connection instance is the main object which provides API for working with your database. Connection's constructor accepts a configuration array. The possible keys depend on the current driver, although some configuration keys are the same for all drivers:

|* driver           | driver name, use `mysqli` or `pgsql`
|* host             | database server name
|* username         | username for authentication
|* password         | password for authentication
|* database         | name of database
|* charset          | charset encoding of connection
|* applicationTz    | time zone for returned DateTime objects
|* connectionTz     | time zone for connection
|* simpleStorageTz  | time zone for simple time stamp type

/--php
$connection = new Nextras\Dbal\Connection([
	'driver'   => 'mysqli',
	'host'     => 'localhost',
	'username' => 'root',
	'password' => '****',
	'database' => 'test',
]);
\--

By default, the connection is lazy; it connects to database when needed. You can force the connection by calling `connect()` method; you can also `disconnect()` or `reconnect()` your connection. Use `ping()` method to stay in touch.

------------

Querying
========

Use `query()` method to run SQL queries. Query method accepts "pseudo SQL" statement. Dbal support parameter placeholders, which are passed separately and will be properly escaped and sanitized. Take a look on [Parameter Placeholders | param-placeholders] chapter.

/--php
$connection->query('SELECT * FROM foo WHERE id = %i', 1);
// SELECT * FROM foo WHERE id = 1

$connection->query('SELECT * FROM foo WHERE title = %s', 'foo" OR 1=1');
// SELECT * FROM foo WHERE title = "foo\" OR 1=1"
\--

"Pseudo SQL" supports `[]` square brackets for easily escaping of column names. However, if you pass a column name as parameter retrieved from user, use the proper `%column` placeholder.

/--php
$connection->query('SELECT * FROM [foo] WHERE %column = %i', 'id', 1);
// SELECT * FROM `foo` WHERE `id` = 1
\--

To retrieve the last inserted id use `getLastInsertedId()` method. For PostgreSQL, the method accepts a sequence name. The number of affected rows is available through `getAffectedRows()` method.

Each `query()` returns a new `Nextras\Dbal\Result\Result` object. Result object allows you to iterate over the fetched data and fetch each row into `Nextras\Dbal\Result\Row` object:

/--php
$users = $connection->query('SELECT * FROM [users]');
foreach ($users as $row) {
	$row->name;
}
\--

Result object implements `SeekableIterator`. You can use `fetch()` method to fetch a row, `fetchField()` to fetch the first field form the first row, or `fetchAll()` to return array of rows' objects.

/--php
$maximum = $connection->query('SELECT MAX([age]) FROM [users]')->fetchField();
\--

------------

Transactions
============

The connection object provides convenient API for working with transactions. You can easily `beginTransaction()`, `commitTransaction()` and `rollbackTransaction()`. Usually, you need to react to an exception by calling rollback method. For such use case there is a `transactional()` method that make its callback atomic.

/--php
$connection->transactional(function(Connection $connection) {
	$connection->query('INSERT INTO users %values', [
		'name' => 'new user'
	]);
	$connection->query('INSERT INTO urls %values', [
		'url' => 'new-user',
		'user_id' => $connection->getLastInsertedId();
	]);
});
\--


code:
- [Connection | default]
- [Param Placeholders | param-placeholders]
- [Query Builder | query-builder]
- [DateTime TimeZones | datetime]

/---div .[tuts]
Tutorials:
- [MySQL Timezone Support | timezones-mysql-support]
\--


code:
Parameter Placeholders
######################

Dbal allows you easy way to escape and build your SQL query. You can use powerful placeholders:

|* `%s`, `%?s`, `%s[]`          | string   | not nullable, nullable, array of
|* `%i`, `%?i`, `%i[]`          | integer  | not nullable, nullable, array of
|* `%f`, `%?f`, `%f[]`          | float    | not nullable, nullable, array of
|* `%b`, `%?b`, `%b[]`          | boolean  | not nullable, nullable, array of
|* `%dt`, `%?dt`, `%dt[]`       | datetime | not nullable, nullable, array of
|* `%dts`, `%?dts`, `%dts[]`    | datetime | datetime with [conversion to simple storage time zone | datetime]
|* `%blob`, `%?blob`, `%blob[]` | binary string | not nullable, nullable, array of
|* `%any`, `%any[]`             |               | any value scalary value, array of scalar values
|* `%_like`, `%like_`, `%_like_`| string   | like left, like right, like both sides

All modifiers require receiving an argument of the specific modifier data type - eg. `%f` accepts only floats and integers.

/--php
$connection->query('id = %i AND name IN (%?s, %?s)', 1, NULL, 'foo');
// `id` = 1 AND name IN (NULL, 'foo')

$connection->query('name LIKE %_like_', $query);
// escapes query and adds % to both sides
// name LIKE '%escaped query expression%'
\--

Other available modifiers:

|* `%table`, `%table[]`    | escapes string as table name, surrounding parentheses are not added to `%table[]` modifier
|* `%column`, `%column[]`  | escapes string as column name, surrounding parentheses are not added to `%column[]` modifier
|* `%ex`                   | expands array as processor arguments
|* `%raw`                  | inserts string argument as is

`%ex` modifier expands passed array as arguments of new `query()` method call.

/--php
$connection->query('%ex', ['id = %i', 1]);
// equals to
$connection->query('id = %i', 1);
\--

Last modifiers are connected with array processing:

|* `%and`                  | AND condition
|* `%or`                   | OR condition
|* `%values`, `%values[]`  | expands array for INSERT clause, multi insert
|* `%set`                  | expands array for SET clause

Let's examine `%and` and `%or` behavior. If array key is numeral and value is array, value is expanded with `%ex` modifier.

/--php
$connection->query('%and', [
	'city' => 'Winterfell',
	'age'  => 23,
]);
// `city` = 'Winterfell' AND `age` = 23

$connection->query('%or', [
	'city' => 'Winterfell',
	'age'  => [23, 25],
]);
// `city` = 'Winterfell' OR `age` IN (23, 25)

$connection->query('%or', [
	'city' => 'Winterfell',
	['[age] IN %i[]', [23, 25]],
]);
// `city` = 'Winterfell' OR `age` IN (23, 25)
\--

Examples for inserting and updating:

/--php
$connection->query('INSERT INTO [users] %values', [
    'name' => 'Jon Snow'
]);
// INSERT INTO `users` (`name`) VALUES ('Jon Snow')

$connection->query('INSERT INTO [users] %values[]', [
    ['name' => 'Jon Snow'],
    ['name' => 'The Imp'],
]);
// INSERT INTO `users` (`name`) VALUES ('Jon Snow'), ('The Imp')

$connection->query('UPDATE [users] %set WHERE [id] = %i', [
    'name' => 'Jon Snow'
], 1);
// UPDATE `users` SET `name` = 'Jon Snow' WHERE `id` = 1
\--


code:
Query Builder
#############

Query builder is tool for constructing SQL query. It allows you to define SQL query with fluent API.

From clause
===========

Let's create Query builder object. Set source table name by `from()` method. You can pass alias. The first argument is considered as SQL expression, you you have to escape table name manually with `[]` brackets, or use modifier. The second argument is optional table alias, other arguments are arguments for used modifiers.

/--php
$builder = $connection->createQueryBuilder();

// table useres, aliased a
$builder->from('users', 'a');

// escaping table name
$builder->from('[orders]', 'o');

// or pass it as argument
$builder->from('%table', 'o', $tableName);

// table as result of stored function/procedure
$builder->from('my_orders(%i, %i)', 'orders', $userId, $groupId);
\--

------------

Where, having and group by clause
=================================

You can add conditions using `andWhere()` or `orWhere()` methods. `*Where` methods accepts query expression, all other function arguments are optional and are considered as arguments for modifiers passed in expression.

/--php
$builder->andWhere('order_id = %i', $oderId);
        ->orWhere('user_id IN %i[] AND group_id = %i', $userIds, $groupId);

// will produce
// WHERE (order_id = %i) OR (user_id IN %i[] AND group_id = %i)
\--

Methods `andHaving()` and `orHaving()` have the same signature and logic as where methods. Group by expressions are not connected with logic conjunctions, therefore method is called `addGroupBy()`. The group by method has signature as others, accepts expression and optinal arguments.

Let's see an example: depending on `$cond` we build query which will retrieve daily number of issues created/resolved in last week and was them more than 10..
/--php
$column = $cond ? 'created_at' : 'resolved_at';

$builder = $connection->createQueryBuilder();
$builder->select('DATE(%column), COUNT(*)', $column);
$builder->from('issues');
$builder->andWhere('%column > NOW() - INTERVAL 1 WEEK', $column);
$builder->addGroupBy('DATE(%column)', $column);
$builder->andHaving('COUNT(*) > 10');
\--

Query builder has alternative methods named `where()`, `groupBy()` and `having()` which remove previously defined conditions and add new one:

/--php
$builder = $connection->createQueryBuilder();
$builder->from('issues');
$builder->where('created_at > NOW()');
$builder->where('created_at < NOW()'); // replace previous conditions

// will produce
// SELECT * FROM issues WHERE created_at < NOW();
\--

------------

Select, orderBy and limit clause
================================

Builder have also methods for defining select, order by and limit clauses. Use appropriate methods: `addSelect()`, `select()`, `addOrderBy()`, `orderBy()`, and `limitBy()`. Select and order by methods accepts modifier arguments as earlier mentioned methods.

/--php
$builder->addSelect('id, %column, [another_ecpaced_column]', $myColumn);
$builder->addSelect('COALESCE(colmun)');

$builder->addOrderBy('FIELD(type, %s, %s, %s)', "type1", "type2", "type3");

$builder->limitBy(20); // selects the first 20 results
$builder->limitBy(20, 10); // sets offset to 1O
\--

------------

Joins
=====

Choose from `innerJoin()`, `leftJoin()`, and `rightJoin()` methods. Each of them has the same signature. Arguments:
- from source name - from which table should be join created,
- to expression - target expression, do not forget to escape target table name,
- to alias - alias of target, you can leave it NULL,
- on expression - ON clause expression,
- arguments for expressions.

The from source name is present for better validation. It disallows you to build join on table, which was not defined.

/--php
$builder->from('[authors]', 'a');
$builder->leftJoin('a', '[books]', 'b', '[a.id] = [b.authorId] AND [b.title] = %s',
                   $title);

// will produce
// FROM [authors] AS [a]
// LEFT JOIN [books] AS [b] ON ([a.id] = [b.authorId] AND [b.title] = %s)
\--

------

Execution
=========

To get builder SQL call `getQuerySql()` method. Passed arguments are available through `getQueryParameters()` method. To execute query use `queryArgs()` method on connection.

/--php
$result = $this->connection->queryArgs(
	$builder->getQuerySql(),
	$builder->getQueryParameters()
);
\--


code:
Named Timezone Support in MySQL
###############################

MySQL, by default, does not "support" named timezones, therefore you could get errors similar to `Unknown or incorrect time zone: 'Europe/Prague'`. In fact, MySQL just does not know the correct timeshift for this name and allows you to import the configuration.

-------

Linux
=====

Run this command, where `root` is the user name which has access to `mysql` database.

/--code
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
\--

-------

Windows
=======

For MySQL **up to 5.6** downlad tables from http://dev.mysql.com/downloads/timezones.html. Unzip the archive and copy tables to your mysql database in data dir (eg. `C:\<your mysql dir>\data\mysql`).

For MySQL **5.7+** there is an archive with prepared SQL queries. Run them in context of mysql database.


code:
Nextras Dbal
============

[![Build Status](https://travis-ci.org/nextras/dbal.svg?branch=master)](https://travis-ci.org/nextras/dbal)
[![Downloads this Month](https://img.shields.io/packagist/dm/nextras/dbal.svg?style=flat)](https://packagist.org/packages/nextras/dbal)
[![Stable version](http://img.shields.io/packagist/v/nextras/dbal.svg?style=flat)](https://packagist.org/packages/nextras/dbal)
[![Code coverage](https://img.shields.io/coveralls/nextras/dbal.svg?style=flat)](https://coveralls.io/r/nextras/dbal)
[![HHVM Status](http://img.shields.io/hhvm/nextras/dbal.svg?style=flat)](http://hhvm.h4cc.de/package/nextras/dbal)

### Installation

Use composer:

```bash
$ composer require nextras/dbal
```

### Docs & sources

- [Documentation](https://nextras.org/dbal/docs)

### Thanks

This project is inspired by great libraries:
- [Doctrine\DBAL](http://www.doctrine-project.org) - [GitHub](https://github.com/doctrine/dbal)
- [dibi](http://dibiphp.org) - [GitHub](https://github.com/dg/dibi)
- [Nette\Database](http://nette.org) - [GitHub](https://github.com/nette/database)

### License

MIT. See full [license](license.md).


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Bridges\NetteDI;

use Nette\DI\CompilerExtension;
use Nextras\Dbal\Bridges\NetteTracy\BluescreenQueryPanel;
use Nextras\Dbal\Bridges\NetteTracy\ConnectionPanel;
use Nextras\Dbal\Connection;
use Tracy\Debugger;

class DbalExtension extends CompilerExtension
{
	public function loadConfiguration()
	{
		$config = $this->getConfig();
		$this->setupConnection($config);
	}

	protected function setupConnection(array $config)
	{
		$builder = $this->getContainerBuilder();

		$definition = $builder->addDefinition($this->prefix('connection'))
			->setClass(Connection::class)
			->setArguments([
				'config' => $config,
			])
			->setAutowired(isset($config['autowired']) ? $config['autowired'] : TRUE);

		if (isset($config['debugger'])) {
			$debugger = $config['debugger'];
		} else {
			$debugger = class_exists('Tracy\Debugger', FALSE) && Debugger::$productionMode === Debugger::DEVELOPMENT;
		}

		if ($debugger) {
			$definition->addSetup('@Tracy\BlueScreen::addPanel', [BluescreenQueryPanel::class . '::renderBluescreenPanel']);
			$definition->addSetup(ConnectionPanel::class . '::install', ['@self']);
		}
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Bridges\NetteTracy;

use Nextras\Dbal\QueryException;

class BluescreenQueryPanel
{
	public static function renderBluescreenPanel($exception)
	{
		if (!$exception instanceof QueryException || !($query = $exception->getSqlQuery())) {
			return null;
		}

		return [
			'tab' => 'SQL',
			'panel' => '
' . ConnectionPanel::highlight($query) . "
",
		];
	}
}


code:
<?php

namespace Nextras\Dbal\Bridges\NetteTracy;

?>

Queries: <?php echo $count ?>

SQL query	Time (ms)

		<?php
		foreach ($queries as $query):
			list($connection, $sql, $elapsedTime) = $query;
		?>

		<?php echo $sql ?>
		<?php echo $elapsedTime ? sprintf('%0.3f', $elapsedTime * 1000) : '' ?>

		<?php endforeach ?>

	<?php if (count($queries) < $count): ?>
...and more
<?php endif ?>


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Bridges\NetteTracy;

use Nextras\Dbal\Connection;
use Nextras\Dbal\Result\Result;
use Tracy\Debugger;
use Tracy\IBarPanel;

class ConnectionPanel implements IBarPanel
{
	/** @var int */
	private $maxQueries = 100;

	/** @var int */
	private $count = 0;

	/** @var float */
	private $totalTime;

	/** @var array */
	private $queries = [];

	public static function install(Connection $connection)
	{
		Debugger::getBar()->addPanel(new ConnectionPanel($connection));
	}

	public function __construct(Connection $connection)
	{
		$connection->onQuery[] = [$this, 'logQuery'];
	}

	public function logQuery(Connection $connection, $sql, Result $result = NULL)
	{
		$this->count++;
		if ($this->count > $this->maxQueries) {
			return;
		}

		$this->queries[] = [
			$connection,
			$sql,
			$result ? $result->getElapsedTime() : NULL,
		];

		if ($result) {
			$this->totalTime += $result->getElapsedTime();
		}
	}

	public function getTab()
	{
		$count = $this->count;
		$totalTime = $this->totalTime;

		ob_start();
		require __DIR__ . '/ConnectionPanel.tab.phtml';
		return ob_get_clean();
	}

	public function getPanel()
	{
		$count = $this->count;
		$queries = $this->queries;
		$queries = array_map(function($row) {
			$row[1] = self::highlight($row[1]);
			return $row;
		}, $queries);

		ob_start();
		require __DIR__ . '/ConnectionPanel.panel.phtml';
		return ob_get_clean();
	}

	public static function highlight($sql)
	{
		static $keywords1 = 'SELECT|(?:ON\s+DUPLICATE\s+KEY)?UPDATE|INSERT(?:\s+INTO)?|REPLACE(?:\s+INTO)?|SHOW|DELETE|CALL|UNION|FROM|WHERE|HAVING|GROUP\s+BY|ORDER\s+BY|LIMIT|OFFSET|SET|VALUES|LEFT\s+JOIN|INNER\s+JOIN|TRUNCATE';
		static $keywords2 = 'ALL|DISTINCT|DISTINCTROW|IGNORE|AS|USING|ON|AND|OR|IN|IS|NOT|NULL|[RI]?LIKE|REGEXP|TRUE|FALSE';

		$sql = " $sql ";
		$sql = htmlSpecialChars($sql, ENT_IGNORE, 'UTF-8');
		$sql = preg_replace_callback("#(/\\*.+?\\*/)|(?<=[\\s,(])($keywords1)(?=[\\s,)])|(?<=[\\s,(=])($keywords2)(?=[\\s,)=])#is", function($matches) {
			if (!empty($matches[1])) { // comment
				return '' . $matches[1] . '';
			} elseif (!empty($matches[2])) { // most important keywords
				return '' . $matches[2] . '';
			} elseif (!empty($matches[3])) { // other keywords
				return '' . $matches[3] . '';
			}
		}, $sql);

		return trim($sql);
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal;

use Nextras\Dbal\Drivers\IDriver;
use Nextras\Dbal\Platforms\IPlatform;
use Nextras\Dbal\QueryBuilder\QueryBuilder;
use Nextras\Dbal\Result\Result;

class Connection
{
	/** @var callable[]: function(Connection $connection) */
	public $onConnect = [];

	/** @var callable[]: function(Connection $connection) */
	public $onDisconnect = [];

	/** @var callable[]: function(Connection $connection, string $query, Result $result) */
	public $onQuery = [];

	/** @var array */
	private $config;

	/** @var IDriver */
	private $driver;

	/** @var IPlatform */
	private $platform;

	/** @var SqlProcessor */
	private $sqlPreprocessor;

	/** @var bool */
	private $connected;

	/**
	 * @param  array $config see drivers for supported options
	 */
	public function __construct(array $config)
	{
		$config = $this->processConfig($config);
		$this->config = $config;
		$this->driver = $this->createDriver($config);
		$this->sqlPreprocessor = new SqlProcessor($this->driver);
		$this->connected = $this->driver->isConnected();
	}

	/**
	 * Connects to a database.
	 * @return void
	 * @throws ConnectionException
	 */
	public function connect()
	{
		if ($this->connected) {
			return;
		}
		$this->driver->connect($this->config);
		$this->connected = TRUE;
		$this->fireEvent('onConnect', [$this]);
	}

	/**
	 * Disconnects from a database.
	 * @return void
	 */
	public function disconnect()
	{
		if (!$this->connected) {
			return;
		}
		$this->driver->disconnect();
		$this->connected = FALSE;
		$this->fireEvent('onDisconnect', [$this]);
	}

	/**
	 * Reconnects to a database.
	 * @return void
	 */
	public function reconnect()
	{
		$this->disconnect();
		$this->connect();
	}

	/**
	 * Reconnects to a database with new configration.
	 * @param  array $config
	 */
	public function reconnectWithConfig(array $config)
	{
		$this->disconnect();
		$this->config = $this->processConfig($config);
		$this->driver = $this->createDriver($this->config);
		$this->sqlPreprocessor = new SqlProcessor($this->driver);
		$this->connect();
	}

	/**
	 * @return IDriver
	 */
	public function getDriver()
	{
		return $this->driver;
	}

	/**
	 * Returns connection configuration.
	 * @return array
	 */
	public function getConfig()
	{
		return $this->config;
	}

	/**
	 * Executes a query.
	 * @param  mixed ...$args
	 * @return Result|NULL
	 * @throws QueryException
	 */
	public function query(/*...$args*/)
	{
		$this->connected || $this->connect();
		$args = func_get_args();
		$sql = $this->sqlPreprocessor->process($args);

		$result = $this->driver->query($sql);

		$this->fireEvent('onQuery', [$this, $sql, $result]);
		return $result;
	}

	/**
	 * @param  string|array $query
	 * @param  array $args
	 * @return Result|NULL
	 * @throws QueryException
	 */
	public function queryArgs($query, array $args = [])
	{
		if (!is_array($query)) {
			array_unshift($args, $query);
		} else {
			$args = $query;
		}
		return call_user_func_array([$this, 'query'], $args);
	}

	/**
	 * Returns last inserted ID.
	 * @param  string|NULL $sequenceName
	 * @return int|string
	 */
	public function getLastInsertedId($sequenceName = NULL)
	{
		$this->connected || $this->connect();
		return $this->driver->getLastInsertedId($sequenceName);
	}

	/**
	 * Returns number of affected rows.
	 * @return int
	 */
	public function getAffectedRows()
	{
		$this->connected || $this->connect();
		return $this->driver->getAffectedRows();
	}

	/**
	 * @return IPlatform
	 */
	public function getPlatform()
	{
		if ($this->platform === NULL) {
			$this->connected || $this->connect();
			$this->platform = $this->driver->createPlatform($this);
		}

		return $this->platform;
	}

	/**
	 * Creates new QueryBuilder instance.
	 * @return QueryBuilder
	 */
	public function createQueryBuilder()
	{
		return new QueryBuilder($this->driver);
	}

	/**
	 * Performs operation in a transaction.
	 * @param  callable $callback function(Connection $conn): void
	 * @throws \Exception
	 */
	public function transactional(callable $callback)
	{
		$this->beginTransaction();
		try {
			$callback($this);
			$this->commitTransaction();

		} catch (\Exception $e) {
			$this->rollbackTransaction();
			throw $e;
		}
	}

	/**
	 * Starts a transaction.
	 * @return void
	 * @throws DriverException
	 */
	public function beginTransaction()
	{
		$this->connected || $this->connect();
		$this->driver->beginTransaction();
		$this->fireEvent('onQuery', [$this, '::TRANSACTION BEGIN::']);
	}

	/**
	 * Commits the current transaction.
	 * @return void
	 * @throws DriverException
	 */
	public function commitTransaction()
	{
		$this->driver->commitTransaction();
		$this->fireEvent('onQuery', [$this, '::TRANSACTION COMMIT::']);
	}

	/**
	 * Cancels any uncommitted changes done during the current transaction.
	 * @return void
	 * @throws DriverException
	 */
	public function rollbackTransaction()
	{
		$this->driver->rollbackTransaction();
		$this->fireEvent('onQuery', [$this, '::TRANSACTION ROLLBACK::']);
	}

	/**
	 * Pings a database connection and tries to reconnect it if it is broken.
	 * @return bool
	 */
	public function ping()
	{
		try {
			$this->connected || $this->connect();
			return $this->driver->ping();

		} catch (DriverException $e) {
			return FALSE;
		}
	}

	/**
	 * Processes config: fills defaults, creates aliases, processes dynamic values.
	 * @param  array $config
	 * @return array
	 */
	private function processConfig(array $config)
	{
		if (!isset($config['dbname']) && isset($config['database'])) {
			$config['dbname'] = $config['database'];
		}
		if (!isset($config['user']) && isset($config['username'])) {
			$config['user'] = $config['username'];
		}
		if (!isset($config['simpleStorageTz'])) {
			$config['simpleStorageTz'] = 'UTC';
		}
		if (!isset($config['connectionTz']) || $config['connectionTz'] === IDriver::TIMEZONE_AUTO_PHP_NAME) {
			$config['connectionTz'] = date_default_timezone_get();
		} elseif ($config['connectionTz'] === IDriver::TIMEZONE_AUTO_PHP_OFFSET) {
			$config['connectionTz'] = date('P');
		}
		if (!isset($config['sqlMode'])) { // only for MySQL
			$config['sqlMode'] = 'TRADITIONAL';
		}
		return $config;
	}

	/**
	 * Creates a IDriver instance.
	 * @param  array $config
	 * @return IDriver
	 */
	private function createDriver(array $config)
	{
		if (empty($config['driver'])) {
			throw new InvalidStateException('Undefined driver. Choose from: mysqli, pgsql.');

		} elseif ($config['driver'] instanceof IDriver) {
			return $config['driver'];

		} else {
			$name = ucfirst($config['driver']);
			$class = "Nextras\\Dbal\\Drivers\\{$name}\\{$name}Driver";
			return new $class;
		}
	}

	/**
	 * @param  string $event
	 * @param  array $args
	 * @return void
	 */
	private function fireEvent($event, array $args)
	{
		foreach ($this->$event as $callback) {
			call_user_func_array($callback, $args);
		}
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Drivers;

use Nextras\Dbal\Connection;
use Nextras\Dbal\QueryException;
use Nextras\Dbal\Platforms\IPlatform;
use Nextras\Dbal\Result\Result;

interface IDriver
{
	const TYPE_BOOL = 1;
	const TYPE_DATETIME = 2;
	const TYPE_DATETIME_SIMPLE = 3;
	const TYPE_IDENTIFIER = 4;
	const TYPE_STRING = 5;
	const TYPE_DATE_INTERVAL = 6;
	const TYPE_BLOB = 7;

	const TIMEZONE_AUTO_PHP_NAME = 'auto';
	const TIMEZONE_AUTO_PHP_OFFSET = 'auto-offset';

	/**
	 * Connects the driver to database.
	 * @param  array $params
	 */
	public function connect(array $params);

	/**
	 * Disconnects from the database.
	 */
	public function disconnect();

	/**
	 * Returns true, if there is created connection.
	 * @return bool
	 */
	public function isConnected();

	/**
	 * Returns connection resource.
	 * @return mixed
	 */
	public function getResourceHandle();

	/**
	 * Runs query and returns the result. Returns NULL if query does not select data.
	 * @param  string $query
	 * @return Result|NULL
	 */
	public function query($query);

	/**
	 * Returns the last inseted id.
	 * @param  string|NULL $sequenceName
	 * @return mixed
	 */
	public function getLastInsertedId($sequenceName = NULL);

	/**
	 * Returns number of affected rows.
	 * @return int
	 */
	public function getAffectedRows();

	/**
	 * Creates database plafrom.
	 * @param  Connection $connection
	 * @return IPlatform
	 */
	public function createPlatform(Connection $connection);

	/**
	 * Returns server version in X.Y.Z format.
	 * @return string
	 */
	public function getServerVersion();

	/**
	 * Pings server.
	 * @return bool
	 */
	public function ping();

	/**
	 * Begins a transaction.
	 * @throws QueryException
	 */
	public function beginTransaction();

	/**
	 * Commits a transaction.
	 * @throws QueryException
	 */
	public function commitTransaction();

	/**
	 * Rollback a transaction.
	 * @throws QueryException
	 */
	public function rollbackTransaction();

	/**
	 * Converts database value to php boolean.
	 * @param  string $value
	 * @param  mixed $nativeType
	 * @return mixed
	 */
	public function convertToPhp($value, $nativeType);

	/**
	 * @param  string $value
	 * @return string
	 */
	public function convertStringToSql($value);

	/**
	 * @param  string $value
	 * @param  int $mode -1 = left, 0 = both, 1 = right
	 * @return mixed
	 */
	public function convertLikeToSql($value, $mode);

	/**
	 * @param  bool $value
	 * @return string
	 */
	public function convertBoolToSql($value);

	/**
	 * @param  string $value
	 * @return string
	 */
	public function convertIdentifierToSql($value);

	/**
	 * @param  \DateTime|\DateTimeImmutable|\DateTimeInterface $value
	 * @return string
	 */
	public function convertDateTimeToSql($value);

	/**
	 * @param  \DateTime|\DateTimeImmutable|\DateTimeInterface $value
	 * @return string
	 */
	public function convertDateTimeSimpleToSql($value);

	/**
	 * @param  \DateInterval $value
	 * @return string
	 */
	public function convertDateIntervalToSql($value);

	/**
	 * @param  string $value
	 * @return string
	 */
	public function convertBlobToSql($value);

	/**
	 * Adds driver-specific limit clause to the query.
	 * @param  string $query
	 * @param  int|NULL $limit
	 * @param  int|NULL $offset
	 * @return string
	 */
	public function modifyLimitQuery($query, $limit, $offset);
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Drivers;

use Nextras\Dbal\InvalidStateException;

interface IResultAdapter
{
	/** @const field types */
	const TYPE_DRIVER_SPECIFIC = 1;
	const TYPE_STRING   = 2;
	const TYPE_INT      = 4;
	const TYPE_FLOAT    = 8;
	const TYPE_BOOL     = 16;
	const TYPE_DATETIME = 32;
	const TYPE_AS_IS    = 64;

	/**
	 * @param  int $index
	 * @throws InvalidStateException
	 */
	public function seek($index);

	/**
	 * @return array
	 */
	public function fetch();

	/**
	 * Returns rowset set column types, array of [type, nativeType]
	 * @return array
	 */
	public function getTypes();
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Drivers\Mysqli;

use DateInterval;
use DateTimeZone;
use mysqli;
use Nextras\Dbal\Connection;
use Nextras\Dbal\ConnectionException;
use Nextras\Dbal\DriverException;
use Nextras\Dbal\Drivers\IDriver;
use Nextras\Dbal\ForeignKeyConstraintViolationException;
use Nextras\Dbal\InvalidArgumentException;
use Nextras\Dbal\NotNullConstraintViolationException;
use Nextras\Dbal\NotSupportedException;
use Nextras\Dbal\Platforms\MySqlPlatform;
use Nextras\Dbal\QueryException;
use Nextras\Dbal\Result\Result;
use Nextras\Dbal\UniqueConstraintViolationException;

class MysqliDriver implements IDriver
{
	/** @var mysqli */
	private $connection;

	/** @var DateTimeZone Timezone for columns without timezone handling (datetime). */
	private $simpleStorageTz;

	/** @var DateTimeZone Timezone for database connection. */
	private $connectionTz;

	public function __destruct()
	{
		$this->disconnect();
	}

	public function connect(array $params)
	{
		$host   = isset($params['host']) ? $params['host'] : ini_get('mysqli.default_host');
		$port   = isset($params['port']) ? $params['port'] : (ini_get('mysqli.default_port') ?: 3306);
		$dbname = isset($params['dbname']) ? $params['dbname'] : '';
		$socket = isset($params['unix_socket']) ? $params['unix_socket'] : (ini_get('mysqli.default_socket') ?: NULL);
		$flags  = isset($params['flags']) ? $params['flags'] : 0;

		$this->connection = new mysqli();

		if (!@$this->connection->real_connect($host, $params['user'], $params['password'], $dbname, $port, $socket, $flags)) {
			throw $this->createException(
				$this->connection->connect_error,
				$this->connection->connect_errno,
				@$this->connection->sqlstate ?: 'HY000'
			);
		}

		$this->processInitialSettings($params);
	}

	public function disconnect()
	{
		if ($this->connection) {
			$this->connection->close();
			$this->connection = NULL;
		}
	}

	public function isConnected()
	{
		return $this->connection !== NULL;
	}

	/**
	 * @return mysqli
	 */
	public function getResourceHandle()
	{
		return $this->connection;
	}

	public function query($query)
	{
		$time = microtime(TRUE);
		$result = @$this->connection->query($query);
		$time = microtime(TRUE) - $time;

		if ($result === FALSE) {
			throw $this->createException(
				$this->connection->error,
				$this->connection->errno,
				$this->connection->sqlstate,
				$query
			);
		}

		if ($result === TRUE) {
			return NULL;
		}

		return new Result(new MysqliResultAdapter($result), $this, $time);
	}

	public function getLastInsertedId($sequenceName = NULL)
	{
		return $this->connection->insert_id;
	}

	public function getAffectedRows()
	{
		return $this->connection->affected_rows;
	}

	public function createPlatform(Connection $connection)
	{
		return new MySqlPlatform($connection);
	}

	public function getServerVersion()
	{
		$version = $this->connection->server_version;
		$majorVersion = floor($version / 10000);
		$minorVersion = floor(($version - $majorVersion * 10000) / 100);
		$patchVersion = floor($version - $majorVersion * 10000 - $minorVersion * 100);
		return $majorVersion . '.' . $minorVersion . '.' . $patchVersion;
	}

	public function ping()
	{
		return $this->connection->ping();
	}

	public function beginTransaction()
	{
		$this->query('START TRANSACTION');
	}

	public function commitTransaction()
	{
		$this->query('COMMIT');
	}

	public function rollbackTransaction()
	{
		$this->query('ROLLBACK');
	}

	protected function processInitialSettings(array $params)
	{
		if (isset($params['charset'])) {
			$charset = $params['charset'];
		} elseif (($version = $this->getServerVersion()) && version_compare($version, '5.5.3', '>=')) {
			$charset = 'utf8mb4';
		} else {
			$charset = 'utf8';
		}

		$this->connection->set_charset($charset);

		if (isset($params['sqlMode'])) {
			$this->query('SET sql_mode = ' . $this->convertStringToSql($params['sqlMode']));
		}

		$this->simpleStorageTz = new DateTimeZone($params['simpleStorageTz']);
		$this->connectionTz = new DateTimeZone($params['connectionTz']);
		$this->query('SET time_zone = ' . $this->convertStringToSql($this->connectionTz->getName()));
	}

	public function convertToPhp($value, $nativeType)
	{
		if ($nativeType === MYSQLI_TYPE_DATETIME || $nativeType === MYSQLI_TYPE_DATE) {
			return $value . ' ' . $this->simpleStorageTz->getName();

		} elseif ($nativeType === MYSQLI_TYPE_TIMESTAMP) {
			return $value . ' ' . $this->connectionTz->getName();

		} elseif ($nativeType === MYSQLI_TYPE_LONGLONG) {
			// called only on 32bit
			return is_float($tmp = $value * 1) ? $value : $tmp;

		} elseif ($nativeType === MYSQLI_TYPE_TIME) {
			preg_match('#^(-?)(\d+):(\d+):(\d+)#', $value, $m);
			$value = new DateInterval("PT{$m[2]}H{$m[3]}M{$m[4]}S");
			$value->invert = $m[1] ? 1 : 0;
			return $value;

		} elseif ($nativeType === MYSQLI_TYPE_BIT) {
			// called only under HHVM
			return ord($value);

		} else {
			throw new NotSupportedException("MysqliDriver does not support '{$nativeType}' type conversion.");
		}
	}

	public function convertStringToSql($value)
	{
		return "'" . $this->connection->escape_string($value) . "'";
	}

	public function convertLikeToSql($value, $mode)
	{
		$value = addcslashes(str_replace('\\', '\\\\', $value), "\x00\n\r\\'%_");
		return ($mode <= 0 ? "'%" : "'") . $value . ($mode >= 0 ? "%'" : "'");
	}

	public function convertBoolToSql($value)
	{
		return $value ? '1' : '0';
	}

	public function convertIdentifierToSql($value)
	{
		return str_replace('`*`', '*', '`' . str_replace(['`', '.'], ['``', '`.`'], $value) . '`');
	}

	public function convertDatetimeToSql($value)
	{
		if ($value->getTimezone()->getName() !== $this->connectionTz->getName()) {
			if ($value instanceof \DateTimeImmutable) {
				$value = $value->setTimezone($this->connectionTz);
			} else {
				$value = clone $value;
				$value->setTimezone($this->connectionTz);
			}
		}
		return "'" . $value->format('Y-m-d H:i:s') . "'";
	}

	public function convertDatetimeSimpleToSql($value)
	{
		if ($value->getTimezone()->getName() !== $this->simpleStorageTz->getName()) {
			if ($value instanceof \DateTimeImmutable) {
				$value = $value->setTimezone($this->simpleStorageTz);
			} else {
				$value = clone $value;
				$value->setTimeZone($this->simpleStorageTz);
			}
		}
		return "'" . $value->format('Y-m-d H:i:s') . "'";
	}

	public function convertDateIntervalToSql($value)
	{
		$totalHours = ((int) $value->format('%a')) * 24 + $value->h;
		if ($totalHours >= 839) {
			// see https://dev.mysql.com/doc/refman/5.0/en/time.html
			throw new InvalidArgumentException('Mysql cannot store interval bigger than 839h:59m:59s.');
		}
		return $value->format("%r{$totalHours}:%S:%I");
	}

	public function convertBlobToSql($value)
	{
		return "_binary'" . $this->connection->escape_string($value) . "'";
	}

	public function modifyLimitQuery($query, $limit, $offset)
	{
		if ($limit !== NULL || $offset !== NULL) {
			// 18446744073709551615 is maximum of unsigned BIGINT
			// see http://dev.mysql.com/doc/refman/5.0/en/select.html
			$query .= ' LIMIT ' . ($limit !== NULL ? (int) $limit : '18446744073709551615');
		}

		if ($offset !== NULL) {
			$query .= ' OFFSET ' . (int) $offset;
		}

		return $query;
	}

	/**
	 * This method is based on Doctrine\DBAL project.
	 * @link www.doctrine-project.org
	 */
	protected function createException($error, $errorNo, $sqlState, $query = NULL)
	{
		if (in_array($errorNo, [1216, 1217, 1451, 1452, 1701], TRUE)) {
			return new ForeignKeyConstraintViolationException($error, $errorNo, $sqlState, NULL, $query);

		} elseif (in_array($errorNo, [1062, 1557, 1569, 1586], TRUE)) {
			return new UniqueConstraintViolationException($error, $errorNo, $sqlState, NULL, $query);

		} elseif (in_array($errorNo, [1044, 1045, 1046, 1049, 1095, 1142, 1143, 1227, 1370, 2002, 2005], TRUE)) {
			return new ConnectionException($error, $errorNo, $sqlState);

		} elseif (in_array($errorNo, [1048, 1121, 1138, 1171, 1252, 1263, 1566], TRUE)) {
			return new NotNullConstraintViolationException($error, $errorNo, $sqlState, NULL, $query);

		} elseif ($query !== NULL) {
			return new QueryException($error, $errorNo, $sqlState, NULL, $query);

		} else {
			return new DriverException($error, $errorNo, $sqlState);
		}
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Drivers\Mysqli;

use mysqli_result;
use Nextras\Dbal\Drivers\IResultAdapter;
use Nextras\Dbal\InvalidStateException;

class MysqliResultAdapter implements IResultAdapter
{
	/** @var array */
	protected static $types = [
		MYSQLI_TYPE_TIME        => self::TYPE_DRIVER_SPECIFIC,
		MYSQLI_TYPE_DATE        => 33, // self::TYPE_DRIVER_SPECIFIC | self::TYPE_DATETIME,
		MYSQLI_TYPE_DATETIME    => 33, // self::TYPE_DRIVER_SPECIFIC | self::TYPE_DATETIME,
		MYSQLI_TYPE_TIMESTAMP   => 33, // self::TYPE_DRIVER_SPECIFIC | self::TYPE_DATETIME,

		MYSQLI_TYPE_BIT         => self::TYPE_INT, // returned as int
		MYSQLI_TYPE_INT24       => self::TYPE_INT,
		MYSQLI_TYPE_INTERVAL    => self::TYPE_INT,
		MYSQLI_TYPE_TINY        => self::TYPE_INT,
		MYSQLI_TYPE_SHORT       => self::TYPE_INT,
		MYSQLI_TYPE_LONG        => self::TYPE_INT,
		MYSQLI_TYPE_LONGLONG    => self::TYPE_INT,
		MYSQLI_TYPE_YEAR        => self::TYPE_INT,

		MYSQLI_TYPE_DECIMAL     => self::TYPE_FLOAT,
		MYSQLI_TYPE_NEWDECIMAL  => self::TYPE_FLOAT,
		MYSQLI_TYPE_DOUBLE      => self::TYPE_FLOAT,
		MYSQLI_TYPE_FLOAT       => self::TYPE_FLOAT,

		MYSQLI_TYPE_STRING      => self::TYPE_AS_IS,
	];

	/** @var mysqli_result */
	private $result;

	public function __construct(mysqli_result $result)
	{
		$this->result = $result;
		if (defined('HHVM_VERSION')) {
			self::$types[MYSQLI_TYPE_BIT] = self::TYPE_DRIVER_SPECIFIC;
		}

		if (PHP_INT_SIZE < 8) {
			self::$types[MYSQLI_TYPE_LONGLONG] = self::TYPE_DRIVER_SPECIFIC;
		}
	}

	public function __destruct()
	{
		$this->result->free();
	}

	public function seek($index)
	{
		if ($this->result->num_rows !== 0 && !@$this->result->data_seek($index)) { // @ - intentionally for HHVM, which triggers E_WARNING
			throw new InvalidStateException("Unable to seek in row set to {$index} index.");
		}
	}

	public function fetch()
	{
		return $this->result->fetch_assoc();
	}

	public function getTypes()
	{
		$types = [];
		$count = $this->result->field_count;

		for ($i = 0; $i < $count; $i++) {
			$field = (array) $this->result->fetch_field_direct($i);
			$types[$field['name']] = [
				0 => isset(self::$types[$field['type']]) ? self::$types[$field['type']] : self::TYPE_AS_IS,
				1 => $field['type'],
			];
		}

		return $types;
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Drivers\Pgsql;

use DateInterval;
use DateTimeZone;
use Nextras\Dbal\Connection;
use Nextras\Dbal\ConnectionException;
use Nextras\Dbal\DriverException;
use Nextras\Dbal\Drivers\IDriver;
use Nextras\Dbal\ForeignKeyConstraintViolationException;
use Nextras\Dbal\InvalidArgumentException;
use Nextras\Dbal\NotNullConstraintViolationException;
use Nextras\Dbal\NotSupportedException;
use Nextras\Dbal\Platforms\PostgreSqlPlatform;
use Nextras\Dbal\QueryException;
use Nextras\Dbal\Result\Result;
use Nextras\Dbal\UniqueConstraintViolationException;

class PgsqlDriver implements IDriver
{
	/** @var resource */
	private $connection;

	/** @var DateTimeZone Timezone for columns without timezone handling (timestamp, datetime, time). */
	private $simpleStorageTz;

	/** @var DateTimeZone Timezone for database connection. */
	private $connectionTz;

	/** @var resource */
	private $affectedRows;

	public function __destruct()
	{
		$this->disconnect();
	}

	public function connect(array $params)
	{
		static $knownKeys = [
			'host', 'hostaddr', 'port', 'dbname', 'user', 'password',
			'connect_timeout', 'options', 'sslmode', 'service',
		];

		$connectionString = '';
		foreach ($knownKeys as $key) {
			if (isset($params[$key])) {
				$connectionString .= $key . '=' . $params[$key] . ' ';
			}
		}

		set_error_handler(function($code, $message) {
			restore_error_handler();
			throw $this->createException($message, $code, NULL);
		}, E_ALL);

		$this->connection = pg_connect($connectionString, PGSQL_CONNECT_FORCE_NEW);

		restore_error_handler();

		$this->simpleStorageTz = new DateTimeZone($params['simpleStorageTz']);
		$this->connectionTz = new DateTimeZone($params['connectionTz']);
		$this->query('SET TIME ZONE ' . pg_escape_literal($this->connectionTz->getName()));
	}

	public function disconnect()
	{
		if ($this->connection) {
			pg_close($this->connection);
			$this->connection = NULL;
		}
	}

	public function isConnected()
	{
		return $this->connection !== NULL;
	}

	public function getResourceHandle()
	{
		return $this->connection;
	}

	public function query($query)
	{
		if (!pg_send_query($this->connection, $query)) {
			throw $this->createException(pg_last_error($this->connection), 0, NULL);
		}

		$time = microtime(TRUE);
		$resource = pg_get_result($this->connection);
		$time = microtime(TRUE) - $time;

		if ($resource === FALSE) {
			throw $this->createException(pg_last_error($this->connection), 0, NULL);
		}

		$state = pg_result_error_field($resource, PGSQL_DIAG_SQLSTATE);
		if ($state !== NULL) {
			throw $this->createException(pg_result_error($resource), 0, $state, $query);
		}

		$this->affectedRows = pg_affected_rows($resource);
		return new Result(new PgsqlResultAdapter($resource), $this, $time);
	}

	public function getLastInsertedId($sequenceName = NULL)
	{
		if (empty($sequenceName)) {
			throw new InvalidArgumentException('PgsqlDriver require to pass sequence name for getLastInsertedId() method.');
		}
		$sql = 'SELECT CURRVAL(' . pg_escape_literal($this->connection, $sequenceName) . ')';
		return $this->query($sql)->fetchField();
	}

	public function getAffectedRows()
	{
		return $this->affectedRows;
	}

	public function createPlatform(Connection $connection)
	{
		return new PostgreSqlPlatform($connection);
	}

	public function getServerVersion()
	{
		return pg_version($this->connection)['server'];
	}

	public function ping()
	{
		return pg_ping($this->connection);
	}

	public function beginTransaction()
	{
		$this->query('START TRANSACTION');
	}

	public function commitTransaction()
	{
		$this->query('COMMIT');
	}

	public function rollbackTransaction()
	{
		$this->query('ROLLBACK');
	}

	public function convertToPhp($value, $nativeType)
	{
		static $trues = ['true', 't', 'yes', 'y', 'on', '1'];

		if ($nativeType === 'bool') {
			return in_array(strtolower($value), $trues, TRUE);

		} elseif ($nativeType === 'time' || $nativeType === 'date' || $nativeType === 'timestamp') {
			return $value . ' ' . $this->simpleStorageTz->getName();

		} elseif ($nativeType === 'int8') {
			// called only on 32bit
			return is_float($tmp = $value * 1) ? $value : $tmp;

		} elseif ($nativeType === 'interval') {
			return DateInterval::createFromDateString($value);

		} elseif ($nativeType === 'bit' || $nativeType === 'varbit') {
			return bindec($value);

		} elseif ($nativeType === 'bytea') {
			return pg_unescape_bytea($value);

		} else {
			throw new NotSupportedException("PgsqlDriver does not support '{$nativeType}' type conversion.");
		}
	}

	public function convertStringToSql($value)
	{
		return pg_escape_literal($this->connection, $value);
	}

	public function convertLikeToSql($value, $mode)
	{
		$value = strtr($value, [
			"'" => "''",
			'\\' => '\\\\',
			'%' => '\\%',
			'_' => '\\_',
		]);
		return ($mode <= 0 ? "'%" : "'") . $value . ($mode >= 0 ? "%'" : "'");
	}

	public function convertBoolToSql($value)
	{
		return $value ? 'TRUE' : 'FALSE';
	}

	public function convertIdentifierToSql($value)
	{
		$parts = explode('.', $value);
		foreach ($parts as &$part) {
			if ($part !== '*') {
				$part = pg_escape_identifier($this->connection, $part);
			}
		}
		return implode('.', $parts);
	}

	public function convertDateTimeToSql($value)
	{
		if ($value->getTimezone()->getName() !== $this->connectionTz->getName()) {
			if ($value instanceof \DateTimeImmutable) {
				$value = $value->setTimezone($this->connectionTz);
			} else {
				$value = clone $value;
				$value->setTimezone($this->connectionTz);
			}
		}
		return "'" . $value->format('Y-m-d H:i:s') . "'::timestamptz";
	}

	public function convertDateTimeSimpleToSql($value)
	{
		if ($value->getTimezone()->getName() !== $this->simpleStorageTz->getName()) {
			if ($value instanceof \DateTimeImmutable) {
				$value = $value->setTimezone($this->simpleStorageTz);
			} else {
				$value = clone $value;
				$value->setTimezone($this->simpleStorageTz);
			}
		}
		return "'" . $value->format('Y-m-d H:i:s') . "'::timestamp";
	}

	public function convertDateIntervalToSql($value)
	{
		return $value->format('P%yY%mM%dDT%hH%iM%sS');
	}

	public function convertBlobToSql($value)
	{
		return "'" . pg_escape_bytea($this->connection, $value) . "'";
	}

	public function modifyLimitQuery($query, $limit, $offset)
	{
		if ($limit !== NULL) {
			$query .= ' LIMIT ' . (int) $limit;
		}
		if ($offset !== NULL) {
			$query .= ' OFFSET ' . (int) $offset;
		}
		return $query;
	}

	/**
	 * This method is based on Doctrine\DBAL project.
	 * @link www.doctrine-project.org
	 */
	protected function createException($error, $errorNo, $sqlState, $query = NULL)
	{
		// see codes at http://www.postgresql.org/docs/9.4/static/errcodes-appendix.html
		if ($sqlState === '0A000' && strpos($error, 'truncate') !== FALSE) {
			// Foreign key constraint violations during a TRUNCATE operation
			// are considered "feature not supported" in PostgreSQL.
			return new ForeignKeyConstraintViolationException($error, $errorNo, $sqlState, NULL, $query);

		} elseif ($sqlState === '23502') {
			return new NotNullConstraintViolationException($error, $errorNo, $sqlState, NULL, $query);

		} elseif ($sqlState === '23503') {
			return new ForeignKeyConstraintViolationException($error, $errorNo, $sqlState, NULL, $query);

		} elseif ($sqlState === '23505') {
			return new UniqueConstraintViolationException($error, $errorNo, $sqlState, NULL, $query);

		} elseif ($sqlState === NULL && stripos($error, 'pg_connect()') !== FALSE) {
			return new ConnectionException($error, $errorNo, $sqlState);

		} elseif ($query !== NULL) {
			return new QueryException($error, $errorNo, $sqlState, NULL, $query);

		} else {
			return new DriverException($error, $errorNo, $sqlState);
		}
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Drivers\Pgsql;

use Nextras\Dbal\Drivers\IResultAdapter;
use Nextras\Dbal\InvalidStateException;

class PgsqlResultAdapter implements IResultAdapter
{
	/**
	 * @var array
	 * @see http://www.postgresql.org/docs/9.4/static/datatype.html
	 */
	protected static $types = [
		'bool'        => self::TYPE_DRIVER_SPECIFIC,
		'bit'         => self::TYPE_DRIVER_SPECIFIC,
		'varbit'      => self::TYPE_DRIVER_SPECIFIC,
		'bytea'       => self::TYPE_DRIVER_SPECIFIC,
		'interval'    => self::TYPE_DRIVER_SPECIFIC,
		'time'        => self::TYPE_DRIVER_SPECIFIC,
		'date'        => 33, // self::TYPE_DRIVER_SPECIFIC | self::TYPE_DATETIME,
		'timestamp'   => 33, // self::TYPE_DRIVER_SPECIFIC | self::TYPE_DATETIME,

		'int8'        => self::TYPE_INT,
		'int4'        => self::TYPE_INT,
		'int2'        => self::TYPE_INT,

		'numeric'     => self::TYPE_FLOAT,
		'float4'      => self::TYPE_FLOAT,
		'float8'      => self::TYPE_FLOAT,

		'timetz'      => self::TYPE_DATETIME,
		'timestamptz' => self::TYPE_DATETIME,
	];

	/** @var resource */
	private $result;

	public function __construct($result)
	{
		$this->result = $result;

		if (PHP_INT_SIZE < 8) {
			self::$types['int8'] = self::TYPE_DRIVER_SPECIFIC;
		}
	}

	public function __destruct()
	{
		pg_free_result($this->result);
	}

	public function seek($index)
	{
		if (pg_num_rows($this->result) !== 0 && !pg_result_seek($this->result, $index)) {
			throw new InvalidStateException("Unable to seek in row set to {$index} index.");
		}
	}

	public function fetch()
	{
		return pg_fetch_array($this->result, NULL, PGSQL_ASSOC) ?: NULL;
	}

	public function getTypes()
	{
		$types = [];
		$count = pg_num_fields($this->result);

		for ($i = 0; $i < $count; $i++) {
			$nativeType = pg_field_type($this->result, $i);
			$types[pg_field_name($this->result, $i)] = [
				0 => isset(self::$types[$nativeType]) ? self::$types[$nativeType] : self::TYPE_AS_IS,
				1 => $nativeType,
			];
		}

		return $types;
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace  Nextras\Dbal;

/**
 * @internal
 */
abstract class LazyHashMapBase
{
	/** @var callable */
	private $callback;

	public function __construct($callback)
	{
		$this->callback = $callback;
	}

	/**
	 * @param  string $key
	 * @return mixed
	 */
	protected function load($key)
	{
		return call_user_func($this->callback, $key);
	}

}

/**
 * @internal
 */
final class LazyHashMap extends LazyHashMapBase
{

	/**
	 * @param  string $key
	 * @return mixed
	 */
	public function __get($key)
	{
		return $this->$key = $this->load($key);
	}

}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Platforms;

use Nette\Caching\Cache;
use Nextras\Dbal\Connection;

class CachedPlatform implements IPlatform
{
	/** @var IPlatform */
	private $platform;

	/** @var Cache */
	private $cache;

	public function __construct(Connection $connection, Cache $cache)
	{
		$this->platform = $connection->getPlatform();
		$this->cache = $cache;
	}

	public function getName()
	{
		return $this->platform->getName();
	}

	public function getTables()
	{
		return $this->cache->load('tables', function () {
			return $this->platform->getTables();
		});
	}

	public function getColumns($table)
	{
		return $this->cache->load('columns.' . $table, function () use ($table) {
			return $this->platform->getColumns($table);
		});
	}

	public function getForeignKeys($table)
	{
		return $this->cache->load('foreign_keys.' . $table, function () use ($table) {
			return $this->platform->getForeignKeys($table);
		});
	}

	public function getPrimarySequenceName($table)
	{
		return $this->cache->load('sequence.' . $table, function () use ($table) {
			return $this->platform->getPrimarySequenceName($table);
		});
	}

	public function clearCache()
	{
		$this->cache->clean();
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Platforms;

interface IPlatform
{
	/**
	 * Returns platform name.
	 * @return string
	 */
	public function getName();

	/**
	 * Returns list of tables names indexed by table name.
	 * @return array
	 */
	public function getTables();

	/**
	 * Returns list of table columns metadata, indexed by column name.
	 * @return array
	 */
	public function getColumns($table);

	/**
	 * Returns list of table foreign keys, indexed by column name.
	 * @return array
	 */
	public function getForeignKeys($table);

	/**
	 * Returns primary sequence name for the table.
	 * If not supported nor present, returns a null.
	 * @param  string $table
	 * @return string|null
	 */
	public function getPrimarySequenceName($table);
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Platforms;

use Nextras\Dbal\Connection;

class MySqlPlatform implements IPlatform
{
	/** @var Connection */
	private $connection;

	public function __construct(Connection $connection)
	{
		$this->connection = $connection;
	}

	public function getName()
	{
		return 'mysql';
	}

	public function getTables()
	{
		$tables = [];
		foreach ($this->connection->query('SHOW FULL TABLES') as $row) {
			$row = array_values($row->toArray());
			$tables[$row[0]] = [
				'name' => $row[0],
				'is_view' => isset($row[1]) && $row[1] === 'VIEW',
			];
		}
		return $tables;
	}

	public function getColumns($table)
	{
		$columns = [];
		foreach ($this->connection->query('SHOW FULL COLUMNS FROM %table', $table) as $row) {
			$type = explode('(', $row->Type);
			$columns[$row->Field] = [
				'name' => $row->Field,
				'type' => strtoupper($type[0]),
				'size' => isset($type[1]) ? (int) $type[1] : NULL,
				'default' => $row->Default,
				'is_primary' => $row->Key === 'PRI',
				'is_autoincrement' => $row->Extra === 'auto_increment',
				'is_unsigned' => (bool) strstr($row->Type, 'unsigned'),
				'is_nullable' => $row->Null === 'YES',
			];
		}
		return $columns;
	}

	public function getForeignKeys($table)
	{
		$result = $this->connection->query('
			SELECT
				CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
			FROM
				information_schema.KEY_COLUMN_USAGE
			WHERE
				TABLE_SCHEMA = DATABASE()
				AND REFERENCED_TABLE_NAME IS NOT NULL
				AND TABLE_NAME = %s
		', $table);

		$keys = [];
		foreach ($result as $row) {
			$keys[$row->COLUMN_NAME] = [
				'name' => $row->CONSTRAINT_NAME,
				'column' => $row->COLUMN_NAME,
				'ref_table' => $row->REFERENCED_TABLE_NAME,
				'ref_column' => $row->REFERENCED_COLUMN_NAME,
			];
		}
		return $keys;
	}

	public function getPrimarySequenceName($table)
	{
		return null;
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Platforms;

use Nextras\Dbal\Connection;

class PostgreSqlPlatform implements IPlatform
{
	/** @var Connection */
	private $connection;

	public function __construct(Connection $connection)
	{
		$this->connection = $connection;
	}

	public function getName()
	{
		return 'postgresql';
	}

	public function getTables()
	{
		$result = $this->connection->query("
			SELECT
				DISTINCT ON (c.relname)
				c.relname::varchar AS name,
				c.relkind = 'v' AS is_view,
				n.nspname::varchar || '.' || c.relname::varchar AS full_name
			FROM
				pg_catalog.pg_class AS c
				JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
			WHERE
				c.relkind IN ('r', 'v')
				AND n.nspname = ANY (pg_catalog.current_schemas(FALSE))
			ORDER BY
				c.relname
		");

		$tables = [];
		foreach ($result as $row) {
			$tables[$row->name] = $row->toArray();
		}
		return $tables;
	}

	public function getColumns($table)
	{
		$result = $this->connection->query("
			SELECT
				a.attname::varchar AS name,
				upper(t.typname) AS type,
				CASE WHEN a.atttypmod = -1 THEN NULL ELSE a.atttypmod -4 END AS size,
				pg_catalog.pg_get_expr(ad.adbin, 'pg_catalog.pg_attrdef'::regclass)::varchar AS default,
				coalesce(co.contype = 'p', FALSE) AS is_primary,
				coalesce(co.contype = 'p' AND strpos(ad.adsrc, 'nextval') = 1, FALSE) AS is_autoincrement,
				FALSE AS is_unsigned,
				NOT (a.attnotnull OR t.typtype = 'd' AND t.typnotnull) AS is_nullable,
				substring(pg_catalog.pg_get_expr(ad.adbin, 'pg_catalog.pg_attrdef'::regclass) from %s) AS sequence
			FROM
				pg_catalog.pg_attribute AS a
				JOIN pg_catalog.pg_class AS c ON a.attrelid = c.oid
				JOIN pg_catalog.pg_type AS t ON a.atttypid = t.oid
				LEFT JOIN pg_catalog.pg_attrdef AS ad ON ad.adrelid = c.oid AND ad.adnum = a.attnum
				LEFT JOIN pg_catalog.pg_constraint AS co ON co.connamespace = c.relnamespace AND contype = 'p' AND co.conrelid = c.oid AND a.attnum = ANY(co.conkey)
			WHERE
				c.relkind IN ('r', 'v')
				AND c.oid = %s::regclass
				AND a.attnum > 0
				AND NOT a.attisdropped
			ORDER BY
				a.attnum
		", "nextval[(]'\"?([^'\"]+)", $table);

		$columns = [];
		foreach ($result as $row) {
			$columns[$row->name] = $row->toArray();
		}
		return $columns;
	}

	public function getForeignKeys($table)
	{
		$result = $this->connection->query("
			SELECT
				co.conname::varchar AS name,
				al.attname::varchar AS column,
				nf.nspname || '.' || cf.relname::varchar AS ref_table,
				af.attname::varchar AS ref_column
			FROM
				pg_catalog.pg_constraint AS co
				JOIN pg_catalog.pg_class AS cl ON co.conrelid = cl.oid
				JOIN pg_catalog.pg_class AS cf ON co.confrelid = cf.oid
				JOIN pg_catalog.pg_namespace AS nf ON nf.oid = cf.relnamespace
				JOIN pg_catalog.pg_attribute AS al ON al.attrelid = cl.oid AND al.attnum = %raw
				JOIN pg_catalog.pg_attribute AS af ON af.attrelid = cf.oid AND af.attnum = %raw
			WHERE
				co.contype = 'f'
				AND cl.oid = %s::regclass
				AND nf.nspname = ANY (pg_catalog.current_schemas(FALSE))
		", 'co.conkey[1]', 'co.confkey[1]', $table);

		$keys = [];
		foreach ($result as $row) {
			$keys[$row->column] = $row->toArray();
		}
		return $keys;
	}

	public function getPrimarySequenceName($table)
	{
		foreach ($this->getColumns($table) as $column) {
			if ($column['is_primary']) {
				return $column['sequence'];
			}
		}
		return null;
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\QueryBuilder;

use Nextras\Dbal\Drivers\IDriver;
use Nextras\Dbal\InvalidArgumentException;
use Nextras\Dbal\InvalidStateException;

class QueryBuilder
{
	/** @const */
	const TYPE_SELECT = 1;
	const TYPE_INSERT = 2;
	const TYPE_UPDATE = 3;
	const TYPE_DELETE = 4;

	/** @var int */
	private $type = self::TYPE_SELECT;

	/** @var array */
	private $args = [
		'select' => NULL,
		'from' => NULL,
		'join' => NULL,
		'where' => NULL,
		'group' => NULL,
		'having' => NULL,
		'order' => NULL,
	];

	/** @var array|NULL */
	private $select;

	/** @var array|NULL */
	private $from;

	/** @var array|NULL */
	private $join;

	/** @var array|NULL */
	private $where;

	/** @var array|NULL */
	private $group;

	/** @var array|NULL */
	private $having;

	/** @var array|NULL */
	private $order;

	/** @var array|NULL */
	private $limit;

	/** @var string */
	private $generatedSql;

	public function __construct(IDriver $driver)
	{
		$this->driver = $driver;
	}

	public function getQuerySql()
	{
		if ($this->generatedSql !== NULL) {
			return $this->generatedSql;
		}

		switch ($this->type) {
			case self::TYPE_SELECT:
			default:
				$sql = $this->getSqlForSelect();
				break;
		}

		$this->generatedSql = $sql;
		return $sql;
	}

	public function getQueryParameters()
	{
		return array_merge(
			(array) $this->args['select'],
			(array) $this->args['from'],
			(array) $this->args['join'],
			(array) $this->args['where'],
			(array) $this->args['group'],
			(array) $this->args['having'],
			(array) $this->args['order']
		);
	}

	private function getSqlForSelect()
	{
		$query =
			'SELECT ' . ($this->select !== NULL ? implode(', ', $this->select) : '*')
			. ' FROM ' . $this->getFromClauses()
			. ($this->where !== NULL  ? ' WHERE ' . ($this->where) : '')
			. ($this->group           ? ' GROUP BY ' . implode(', ', $this->group) : '')
			. ($this->having !== NULL ? ' HAVING ' . ($this->having) : '')
			. ($this->order           ? ' ORDER BY ' . implode(', ', $this->order) : '');

		if ($this->limit) {
			$query = $this->driver->modifyLimitQuery($query, $this->limit[0], $this->limit[1]);
		}

		return $query;
	}

	private function getFromClauses()
	{
		$knownAliases = array_flip($this->getKnownAliases());

		$query = $this->from[0] . ($this->from[1] ? " AS [{$this->from[1]}]" : '');
		foreach ((array) $this->join as $join) {
			if (!isset($knownAliases[$join['from']])) {
				throw new InvalidStateException("Unknown alias '{$join['from']}'.");
			}

			$query .= ' '
				. $join['type'] . " JOIN {$join['table']} " . ($join['alias'] ? "AS [{$join['alias']}] " : '')
				. 'ON (' . $join['on'] . ')';
		}

		return $query;
	}

	public function getClause($part)
	{
		if (!isset($this->args[$part]) && !array_key_exists($part, $this->args)) {
			throw new InvalidArgumentException("Unknown '$part' clause type.");
		}

		return [$this->$part, $this->args[$part]];
	}

	public function from($fromExpression, $alias = NULL)
	{
		$this->dirty();
		$this->type = self::TYPE_SELECT;
		$this->from = [$fromExpression, $alias];
		$this->pushArgs('from', array_slice(func_get_args(), 2));
		return $this;
	}

	public function getFromAlias()
	{
		if ($this->from === NULL) {
			throw new InvalidStateException('From clause has not been set.');
		}

		return $this->from[1];
	}

	public function innerJoin($fromAlias, $toExpression, $toAlias, $onExpression)
	{
		return $this->join('INNER', $fromAlias, $toExpression, $toAlias, $onExpression, array_slice(func_get_args(), 4));
	}

	public function leftJoin($fromAlias, $toExpression, $toAlias, $onExpression)
	{
		return $this->join('LEFT', $fromAlias, $toExpression, $toAlias, $onExpression, array_slice(func_get_args(), 4));
	}

	public function rightJoin($fromAlias, $toExpression, $toAlias, $onExpression)
	{
		return $this->join('RIGHT', $fromAlias, $toExpression, $toAlias, $onExpression, array_slice(func_get_args(), 4));
	}

	public function getJoin($toAlias)
	{
		return isset($this->join[$toAlias]) ? $this->join[$toAlias] : NULL;
	}

	private function join($type, $fromAlias, $toExpression, $toAlias, $onExpression, $args)
	{
		$this->dirty();
		$this->join[$toAlias] = [
			'type' => $type,
			'from' => $fromAlias,
			'table' => $toExpression,
			'alias' => $toAlias,
			'on' => $onExpression,
		];
		$this->pushArgs('join', $args);
		return $this;
	}

	/**
	 * Sets expression as SELECT clause. Passing NULL sets clause to the default state.
	 * @param  string|NULL $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function select($expression)
	{
		if (!($expression === NULL || is_string($expression))) {
			throw new InvalidArgumentException('Select expression has to be a string or NULL.');
		}
		$this->dirty();
		$this->select = $expression === NULL ? NULL : [$expression];
		$this->args['select'] = array_slice(func_get_args(), 1);
		return $this;
	}

	/**
	 * Adds expression to SELECT clause.
	 * @param  string $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function addSelect($expression)
	{
		if (!is_string($expression)) {
			throw new InvalidArgumentException('Select expression has to be a string.');
		}
		$this->dirty();
		$this->select[] = $expression;
		$this->pushArgs('select', array_slice(func_get_args(), 1));
		return $this;
	}

	/**
	 * Sets expression as WHERE clause. Passing NULL sets clause to the default state.
	 * @param  string|NULL $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function where($expression)
	{
		if (!($expression === NULL || is_string($expression))) {
			throw new InvalidArgumentException('Where expression has to be a string or NULL.');
		}
		$this->dirty();
		$this->where = $expression;
		$this->args['where'] = array_slice(func_get_args(), 1);
		return $this;
	}

	/**
	 * Adds expression with AND to WHERE clause.
	 * @param  string $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function andWhere($expression)
	{
		if (!is_string($expression)) {
			throw new InvalidArgumentException('Where expression has to be a string.');
		}
		$this->dirty();
		$this->where = $this->where ? '(' . $this->where . ') AND (' . $expression . ')' : $expression;
		$this->pushArgs('where', array_slice(func_get_args(), 1));
		return $this;
	}

	/**
	 * Adds expression with OR to WHERE clause.
	 * @param  string $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function orWhere($expression)
	{
		if (!is_string($expression)) {
			throw new InvalidArgumentException('Where expression has to be a string.');
		}
		$this->dirty();
		$this->where = $this->where ? '(' . $this->where . ') OR (' . $expression . ')' : $expression;
		$this->pushArgs('where', array_slice(func_get_args(), 1));
		return $this;
	}

	/**
	 * Sets expression as GROUP BY clause. Passing NULL sets clause to the default state.
	 * @param  string|NULL $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function groupBy($expression)
	{
		if (!($expression === NULL || is_string($expression))) {
			throw new InvalidArgumentException('Group by expression has to be a string or NULL.');
		}
		$this->dirty();
		$this->group = $expression === NULL ? NULL : [$expression];
		$this->args['group'] = array_slice(func_get_args(), 1);
		return $this;
	}

	/**
	 * Adds expression to GROUP BY clause.
	 * @param  string $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function addGroupBy($expression)
	{
		if (!is_string($expression)) {
			throw new InvalidArgumentException('Group by expression has to be a string.');
		}
		$this->dirty();
		$this->group[] = $expression;
		$this->pushArgs('group', array_slice(func_get_args(), 1));
		return $this;
	}

	/**
	 * Sets expression as HAVING clause. Passing NULL sets clause to the default state.
	 * @param  string|NULL $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function having($expression)
	{
		if (!($expression === NULL || is_string($expression))) {
			throw new InvalidArgumentException('Having expression has to be a string or NULL.');
		}
		$this->dirty();
		$this->having = $expression;
		$this->args['having'] = array_slice(func_get_args(), 1);
		return $this;
	}

	/**
	 * Adds expression with AND to HAVING clause.
	 * @param  string $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function andHaving($expression)
	{
		if (!is_string($expression)) {
			throw new InvalidArgumentException('Having expression has to be a string.');
		}
		$this->dirty();
		$this->having = $this->having ? '(' . $this->having . ') AND (' . $expression . ')' : $expression;
		$this->pushArgs('having', array_slice(func_get_args(), 1));
		return $this;
	}

	/**
	 * Adds expression with OR to HAVING clause.
	 * @param  string $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function orHaving($expression)
	{
		if (!is_string($expression)) {
			throw new InvalidArgumentException('Having expression has to be a string.');
		}
		$this->dirty();
		$this->having = $this->having ? '(' . $this->having . ') OR (' . $expression . ')' : $expression;
		$this->pushArgs('having', array_slice(func_get_args(), 1));
		return $this;
	}

	/**
	 * Sets expression as ORDER BY clause. Passing NULL sets clause to the default state.
	 * @param  string|NULL $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function orderBy($expression)
	{
		if (!($expression === NULL || is_string($expression))) {
			throw new InvalidArgumentException('Order by expression has to be a string or NULL.');
		}
		$this->dirty();
		$this->order = $expression === NULL ? NULL : [$expression];
		$this->args['order'] = array_slice(func_get_args(), 1);
		return $this;
	}

	/**
	 * Adds expression to ORDER BY clause.
	 * @param  string $expression
	 * @param  mixed ...$arg
	 * @return self
	 */
	public function addOrderBy($expression)
	{
		if (!is_string($expression)) {
			throw new InvalidArgumentException('Order by expression has to be a string.');
		}
		$this->dirty();
		$this->order[] = $expression;
		$this->pushArgs('order', array_slice(func_get_args(), 1));
		return $this;
	}

	/**
	 * Sets LIMIT and OFFSET clause.
	 * @param  int|NULL $limit
	 * @param  int|NULL $offset
	 * @return self
	 */
	public function limitBy($limit, $offset = NULL)
	{
		$this->dirty();
		$this->limit = $limit || $offset ? [$limit, $offset] : NULL;
		return $this;
	}

	/**
	 * Returns true if LIMIT or OFFSET clause is set.
	 * @return bool
	 */
	public function hasLimitOffsetClause()
	{
		return $this->limit !== NULL;
	}

	/**
	 * Returns limit and offset clause arguments.
	 * @return array|NULL
	 */
	public function getLimitOffsetClause()
	{
		return $this->limit;
	}

	private function dirty()
	{
		$this->generatedSql = NULL;
	}

	private function pushArgs($type, array $args)
	{
		$this->args[$type] = array_merge((array) $this->args[$type], $args);
	}

	/**
	 * @return string[]
	 */
	private function getKnownAliases()
	{
		$knownAliases = [];
		if (isset($this->from)) {
			$knownAliases[] = isset($this->from[1]) ? $this->from[1] : $this->from[0];
		}
		foreach ((array) $this->join as $join) {
			$knownAliases[] = isset($join['alias']) ? $join['alias'] : $join['table'];
		}

		return $knownAliases;
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Result;

use DateTimeZone;
use Nextras\Dbal\Drivers\IDriver;
use Nextras\Dbal\Drivers\IResultAdapter;
use Nextras\Dbal\InvalidArgumentException;
use Nextras\Dbal\Utils\DateTime;

class Result implements \SeekableIterator
{
	/** @var IResultAdapter */
	private $adapter;

	/** @var int */
	private $iteratorIndex;

	/** @var Row|NULL */
	private $iteratorRow;

	/** @var IDriver */
	private $driver;

	/** @var string[] list of columns which should be casted to int */
	private $toIntColumns;

	/** @var string[] list of columns which should be casted to float */
	private $toFloatColumns;

	/** @var string[] list of columns which should be casted to string */
	private $toStringColumns;

	/** @var string[] list of columns which should be casted to bool */
	private $toBoolColumns;

	/** @var string[] list of columns which should be casted to DateTime */
	private $toDateTimeColumns;

	/** @var array[] list of columns which should be casted using driver-specific logic */
	private $toDriverColumns;

	/** @var DateTimeZone */
	private $applicationTimeZone;

	/** @var float */
	private $elapsedTime;

	public function __construct(IResultAdapter $adapter, IDriver $driver, $elapsedTime)
	{
		$this->adapter = $adapter;
		$this->driver = $driver;
		$this->applicationTimeZone = new DateTimeZone(date_default_timezone_get());
		$this->initColumnConversions();
		$this->elapsedTime = $elapsedTime;
	}

	/**
	 * @return IResultAdapter
	 */
	public function getAdapter()
	{
		return $this->adapter;
	}

	/**
	 * Enables and disables value normalization.
	 * @param  bool $enabled
	 */
	public function setValueNormalization($enabled = FALSE)
	{
		if ($enabled === TRUE) {
			$this->initColumnConversions();
		} else {
			$this->toIntColumns = [];
			$this->toFloatColumns = [];
			$this->toStringColumns = [];
			$this->toBoolColumns = [];
			$this->toDateTimeColumns = [];
			$this->toDriverColumns = [];
		}
	}

	/**
	 * @return Row|NULL
	 */
	public function fetch()
	{
		$data = $this->adapter->fetch();
		$row = ($data === NULL ? NULL : new Row($this->normalize($data)));
		$this->iteratorIndex++;
		return $this->iteratorRow = $row;
	}

	/**
	 * @return mixed|NULL
	 */
	public function fetchField()
	{
		if ($row = $this->fetch()) { // = intentionally
			foreach ($row as $value) {
				return $value;
			}
		}

		return NULL;
	}

	/**
	 * @return Row[]
	 */
	public function fetchAll()
	{
		return iterator_to_array($this);
	}

	/**
	 * @param  string|NULL $key
	 * @param  string|NULL $value
	 * @return array
	 */
	public function fetchPairs($key = NULL, $value = NULL)
	{
		if ($key === NULL && $value === NULL) {
			throw new InvalidArgumentException('Result::fetchPairs() requires defined key or value.');
		}

		$return = [];
		$this->seek(0);

		if ($key === NULL) {
			while ($row = $this->fetch()) {
				$return[] = $row->{$value};
			}
		} elseif ($value === NULL) {
			while ($row = $this->fetch()) {
				$return[is_object($row->{$key}) ? (string) $row->{$key} : $row->{$key}] = $row;
			}
		} else {
			while ($row = $this->fetch()) {
				$return[is_object($row->{$key}) ? (string) $row->{$key} : $row->{$key}] = $row->{$value};
			}
		}

		return $return;
	}

	/**
	 * @return float
	 */
	public function getElapsedTime()
	{
		return $this->elapsedTime;
	}

	protected function initColumnConversions()
	{
		$this->toIntColumns = [];
		$this->toFloatColumns = [];
		$this->toStringColumns = [];
		$this->toBoolColumns = [];
		$this->toDateTimeColumns = [];
		$this->toDriverColumns = [];

		$types = $this->adapter->getTypes();
		foreach ($types as $key => $typePair) {
			list($type, $nativeType) = $typePair;

			if ($type & IResultAdapter::TYPE_STRING) {
				$this->toStringColumns[] = $key;

			} elseif ($type & IResultAdapter::TYPE_INT) {
				$this->toIntColumns[] = $key;

			} elseif ($type & IResultAdapter::TYPE_FLOAT) {
				$this->toFloatColumns[] = $key;

			} elseif ($type & IResultAdapter::TYPE_BOOL) {
				$this->toBoolColumns[] = $key;

			} elseif ($type & IResultAdapter::TYPE_DATETIME) {
				$this->toDateTimeColumns[] = $key;
			}

			if ($type & IResultAdapter::TYPE_DRIVER_SPECIFIC) {
				$this->toDriverColumns[] = [$key, $nativeType];
			}
		}
	}

	protected function normalize($data)
	{
		foreach ($this->toDriverColumns as $meta) {
			list($column, $nativeType) = $meta;
			if ($data[$column] !== NULL) {
				$data[$column] = $this->driver->convertToPhp($data[$column], $nativeType);
			}
		}

		foreach ($this->toIntColumns as $column) {
			if ($data[$column] !== NULL) {
				$data[$column] = (int) $data[$column];
			}
		}

		foreach ($this->toFloatColumns as $column) {
			if ($data[$column] !== NULL) {
				$data[$column] = (float) $data[$column];
			}
		}

		foreach ($this->toBoolColumns as $column) {
			if ($data[$column] !== NULL) {
				$data[$column] = (bool) $data[$column];
			}
		}

		foreach ($this->toStringColumns as $column) {
			if ($data[$column] !== NULL) {
				$data[$column] = (string) $data[$column];
			}
		}

		foreach ($this->toDateTimeColumns as $column) {
			if ($data[$column] !== NULL) {
				$data[$column] = (new DateTime($data[$column]))->setTimezone($this->applicationTimeZone);
			}
		}

		return $data;
	}

	// === SeekableIterator ============================================================================================

	public function key()
	{
		return $this->iteratorIndex;
	}

	public function current()
	{
		return $this->iteratorRow;
	}

	public function next()
	{
		$this->fetch();
	}

	public function valid()
	{
		return $this->iteratorRow !== NULL;
	}

	public function rewind()
	{
		$this->seek(0);
		$this->fetch();
	}

	public function seek($index)
	{
		$this->adapter->seek($index);
		$this->iteratorIndex = $index - 1;
	}
}


code:
<?php

/**
 * This file is part of the Nextras\Dbal library.
 * @license    MIT
 * @link       https://github.com/nextras/dbal
 */

namespace Nextras\Dbal\Result;

use Nextras\Dbal\InvalidArgumentException;
use Nextras\Dbal\Utils\Typos;

class Row
{
	/**
	 * @param  array $data
	 */
	public function __construct(array $data)
	{
		foreach ($data as $key => $value) {
			$this->$key = $value;
		}
	}

	/**
	 * @return array
	 */
	public function toArray()
	{
		return (array) $this;
	}

	/**
	 * @param  string $name
	 * @return mixed
	 */
	public function __get($name)
	{
		$closest = Typos::getClosest($name, array_keys($this->toArray()), 3);
		throw new InvalidArgumentException("Column '$name' does not exist" . ($closest ? ", did you mean '$closest'?" :  "."));
	}
}