App in PHP
This page contains a detailed description of the code of a test app that is available as part of the YDB PHP SDK.
Note
The article is being updated.
Initializing a database connection
To interact with YDB, create an instance of the driver, client, and session:
- The YDB driver lets the app and YDB interact at the transport layer. The driver must exist throughout the YDB access lifecycle and be initialized before creating a client or session.
- The YDB client runs on top of the YDB driver and enables the handling of entities and transactions.
- The YDB session contains information about executed transactions and prepared queries, and is part of the YDB client context.
App code snippet for driver initialization:
<?php
use YdbPlatform\Ydb\Ydb;
$config = [
// Database path
'database' => '/ru-central1/b1glxxxxxxxxxxxxxxxx/etn0xxxxxxxxxxxxxxxx',
// Database endpoint
'endpoint' => 'ydb.serverless.yandexcloud.net:2135',
// Auto discovery (dedicated server only)
'discovery' => false,
// IAM config
'iam_config' => [
// 'root_cert_file' => './CA.pem', Root CA file (uncomment for dedicated server only)
],
'credentials' => new AccessTokenAuthentication('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA') // use from reference/ydb-sdk/auth
];
$ydb = new Ydb($config);
Creating tables
Creating tables to be used in operations on a test app. This step results in the creation of DB tables of the series directory data model:
Series
Seasons
Episodes
Once the tables are created, the method for getting information about data schema objects is called and the result of its execution is output.
To create tables, use the session->createTable()
method:
protected function createTabels()
{
$this->ydb->table()->retrySession(function (Session $session) {
$session->createTable(
'series',
YdbTable::make()
->addColumn('series_id', 'UINT64')
->addColumn('title', 'UTF8')
->addColumn('series_info', 'UTF8')
->addColumn('release_date', 'UINT64')
->primaryKey('series_id')
);
}, true);
$this->print('Table `series` has been created.');
$this->ydb->table()->retrySession(function (Session $session) {
$session->createTable(
'seasons',
YdbTable::make()
->addColumn('series_id', 'UINT64')
->addColumn('season_id', 'UINT64')
->addColumn('title', 'UTF8')
->addColumn('first_aired', 'UINT64')
->addColumn('last_aired', 'UINT64')
->primaryKey(['series_id', 'season_id'])
);
}, true);
$this->print('Table `seasons` has been created.');
$this->ydb->table()->retrySession(function (Session $session) {
$session->createTable(
'episodes',
YdbTable::make()
->addColumn('series_id', 'UINT64')
->addColumn('season_id', 'UINT64')
->addColumn('episode_id', 'UINT64')
->addColumn('title', 'UTF8')
->addColumn('air_date', 'UINT64')
->primaryKey(['series_id', 'season_id', 'episode_id'])
);
}, true);
$this->print('Table `episodes` has been created.');
}
You can use the session->describeTable()
method to output information about the table structure and make sure that it was properly created:
protected function describeTable($table)
{
$data = $ydb->table()->retrySession(function (Session $session) use ($table) {
return $session->describeTable($table);
}, true);
$columns = [];
foreach ($data['columns'] as $column) {
if (isset($column['type']['optionalType']['item']['typeId'])) {
$columns[] = [
'Name' => $column['name'],
'Type' => $column['type']['optionalType']['item']['typeId'],
];
}
}
print('Table `' . $table . '`');
print_r($columns);
print('');
print('Primary key: ' . implode(', ', (array)$data['primaryKey']));
}
Adding data
Adding data to the created tables using an UPSERT
statement of YQL. A data update request is sent within a single request to the server with transaction auto-commit mode enabled.
Code snippet for data insert/update:
protected function upsertSimple()
{
$ydb->table()->retryTransaction(function (Session $session) {
$session->query('
DECLARE $series_id AS Uint64;
DECLARE $season_id AS Uint64;
DECLARE $episode_id AS Uint64;
DECLARE $title AS Utf8;
UPSERT INTO episodes (series_id, season_id, episode_id, title)
VALUES ($series_id, $season_id, $episode_id, $title);', [
'$series_id' => (new Uint64Type(2))->toTypedValue(),
'$season_id' => (new Uint64Type(6))->toTypedValue(),
'$episode_id' => (new Uint64Type(1))->toTypedValue(),
'$title' => (new Utf8Type('TBD'))->toTypedValue(),
]);
}, true);
print('Finished.');
}
Retrieving data with a Select
Retrieving data using a SELECT
statement in YQL. Handling the retrieved data selection in the app.
To execute YQL queries, use the session->query()
method.
$result = $ydb->table()->retryTransaction(function (Session $session) {
return $session->query('
DECLARE $seriesID AS Uint64;
$format = DateTime::Format("%Y-%m-%d");
SELECT
series_id,
title,
$format(DateTime::FromSeconds(CAST(release_date AS Uint32))) AS release_date
FROM series
WHERE series_id = $seriesID;', [
'$seriesID' => (new Uint64Type(1))->toTypedValue()
]);
}, true, $params);
print_r($result->rows());
Parameterized queries
Querying data using parameters. This query execution option is preferable as it allows the server to reuse the query execution plan for subsequent calls and also protects from such vulnerabilities as SQL Injection.
Here's a code sample that shows how to use prepared queries.
protected function selectPrepared($series_id, $season_id, $episode_id)
{
$result = $ydb->table()->retryTransaction(function (Session $session) use ($series_id, $season_id, $episode_id) {
$prepared_query = $session->prepare('
DECLARE $series_id AS Uint64;
DECLARE $season_id AS Uint64;
DECLARE $episode_id AS Uint64;
$format = DateTime::Format("%Y-%m-%d");
SELECT
title AS `Episode title`,
$format(DateTime::FromSeconds(CAST(air_date AS Uint32))) AS `Air date`
FROM episodes
WHERE series_id = $series_id AND season_id = $season_id AND episode_id = $episode_id;');
return $prepared_query->execute(compact(
'series_id',
'season_id',
'episode_id'
));
},true);
$this->print($result->rows());
}