database-type-mappers.md

Type Mappers

Table of Contents

1. Introduction
2. Basic Usage
3. Dates
4. To SQL
5. From SQL
6. Times With Time Zones
7. To SQL
8. From SQL
9. Times Without Time Zones
10. To SQL
11. From SQL
12. Timestamps With Time Zones
13. To SQL
14. From SQL
15. Timestamps Without Time Zones
16. To SQL
17. From SQL
18. Booleans
19. To SQL
20. From SQL
21. JSON
22. To SQL
23. From SQL

Introduction

A common task when reading and writing to a database is translating PHP values into database values and vice versa. For example, you might store a date as a DateTime in PHP, but you need to translate this into a string with a Y-m-d H:i:s format before storing it in the database. Providers contain the rules for each database provider (eg MySQL, PostgreSQL, etc) on how to translate PHP and database values. Using a combination of type mappers and providers, you can translate PHP-to-database and database-to-PHP values.

Basic Usage

Type mappers need a provider, eg MySQL or PostgreSQL, to do the conversions. You can either pass the provider in the constructor:

use Opulence\Databases\Providers\MySqlProvider;
use Opulence\Databases\Providers\Types\TypeMapper;

$typeMapper = new TypeMapper(new MySqlProvider());

Opulence provides a factory to create type mappers from providers:

use Opulence\Databases\Providers\Types\Factories\TypeMapperFactory;

$factory = new TypeMapperFactory();
// Let's assume $connection is an instance of Opulence\Databases\IConnection
$typeMapper = $factory->createTypeMapper($connection->getDatabaseProvider());

All methods accept a provider in the last parameter:

$typeMapper->toSqlTimestampWithTimeZone(new DateTime(), new MySqlProvider());

Note: For all of the following examples, the PostgreSqlProvider is used. However, you can use any provider you'd like in your application.

Dates

To SQL

$phpDate = new DateTime('1987-07-24 12:34:56');
echo $typeMapper->toSqlDate($phpDate); // "1987-07-24"

Note: This method accepts any object implementing DateTimeInterface, including DateTimeImmutable.

From SQL

$sqlDate = '1987-07-24';
$phpDate = $typeMapper->fromSqlDate($sqlDate);
echo $phpDate->format('Y-m-d'); // "1987-07-24"

Times With Time Zones

To SQL

$phpTime = new DateTime('1987-07-24 12:34:56', new DateTimeZone('UTC'));
echo $typeMapper->toSqlTimeWithTimeZone($phpTime); // "12:34:56+0000"

Note: This method accepts any object implementing DateTimeInterface, including DateTimeImmutable.

From SQL

$sqlTime = '12:34:56+0000';
$phpTime = $typeMapper->fromSqlTimeWithTimeZone($sqlTime);
echo $phpTime->format('H:i:sO'); // "12:34:56+0000"

Times Without Time Zones

To SQL

$phpTime = new DateTime('1987-07-24 12:34:56');
echo $typeMapper->toSqlTimeWithoutTimeZone($phpTime); // "12:34:56"

Note: This method accepts any object implementing DateTimeInterface, including DateTimeImmutable.

From SQL

$sqlTime = '12:34:56';
$phpTime = $typeMapper->fromSqlTimeWithoutTimeZone($sqlTime);
echo $phpTime->format('H:i:s'); // "12:34:56"

Timestamps With Time Zones

To SQL

$phpTimestamp = new DateTime('1987-07-24 12:34:56', new DateTimeZone('UTC'));
echo $typeMapper->toSqlTimestampWithTimeZone($phpTimestamp); // "1987-07-24 12:34:56+0000"

Note: This method accepts any object implementing DateTimeInterface, including DateTimeImmutable.

From SQL

$sqlTimestamp = '1987-07-24 12:34:56+0000';
$phpTimestamp = $typeMapper->fromSqlTimestampWithTimeZone($sqlTimestamp);
echo $phpTimestamp->format('Y-m-d H:i:sO'); // "1987-07-24 12:34:56+0000"

Timestamps Without Time Zones

To SQL

$phpTimestamp = new DateTime('1987-07-24 12:34:56');
echo $typeMapper->toSqlTimestampWithoutTimeZone($phpTimestamp); // "1987-07-24 12:34:56"

Note: This method accepts any object implementing DateTimeInterface, including DateTimeImmutable.

From SQL

$sqlTimestamp = '1987-07-24 12:34:56';
$phpTimestamp = $typeMapper->fromSqlTimestampWithoutTimeZone($sqlTimestamp);
echo $phpTimestamp->format('Y-m-d H:i:s'); // "1987-07-24 12:34:56"

Booleans

To SQL

$phpBoolean = false;
echo $typeMapper->toSqlBoolean($phpBoolean); // "f"

From SQL

$sqlBoolean = 't';
$phpBoolean = $typeMapper->fromSqlBoolean($sqlBoolean);
echo $phpBoolean === true; // 1

JSON

To SQL

$phpArray = ['foo' => 'bar'];
echo $typeMapper->toSqlJson($phpArray); // '{"foo":"bar"}'

From SQL

$sqlJson = '{"foo":"bar"}';
$phpArray = $typeMapper->fromSqlJson($sqlJson);
echo $phpArray['foo']; // "bar"