App in C++

This page contains a detailed description of the code of a test app that is available as part of the YDB C++ SDK.

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:

  1.     auto connectionParams = TConnectionsParams()
  2.         .SetEndpoint(endpoint)
  3.         .SetDatabase(database)
  4.         .SetAuthToken(GetEnv("YDB_TOKEN"));
  6.     TDriver driver(connectionParams);

C++ - 图1

App code snippet for creating a client:

  1.     TClient client(driver);

C++ - 图2

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 CreateTable method:

  1. //! Creates sample tables with CrateTable API.
  2.     ThrowOnError(client.RetryOperationSync([path](TSession session) {
  3.         auto seriesDesc = TTableBuilder()
  4.             .AddNullableColumn("series_id", EPrimitiveType::Uint64)
  5.             .AddNullableColumn("title", EPrimitiveType::Utf8)
  6.             .AddNullableColumn("series_info", EPrimitiveType::Utf8)
  7.             .AddNullableColumn("release_date", EPrimitiveType::Uint64)
  8.             .SetPrimaryKeyColumn("series_id")
  9.             .Build();
  11.         return session.CreateTable(JoinPath(path, "series"), std::move(seriesDesc)).GetValueSync();
  12.     }));

C++ - 图3

Use the describeTable method to output information about the table structure and make sure that it was properly created.

  1.     TMaybe<TTableDescription> desc;
  3.     ThrowOnError(client.RetryOperationSync([path, name, &desc](TSession session) {
  4.         auto result = session.DescribeTable(JoinPath(path, name)).GetValueSync();
  6.         if (result.IsSuccess()) {
  7.             desc = result.GetTableDescription();
  8.         }
  10.         return result;
  11.     }));
  13.     Cout << "> Describe table: " << name << Endl;
  14.     for (auto& column : desc->GetColumns()) {
  15.         Cout << "Column, name: " << column.Name << ", type: " << FormatType(column.Type) << Endl;
  16.     }

C++ - 图4

The given code snippet outputs the following text to the console at startup:

  1. > Describe table: series
  2. Column, name: series_id, type: Uint64?
  3. Column, name: title, type: Utf8?
  4. Column, name: series_info, type: Utf8?
  5. Column, name: release_date, type: Uint64?

C++ - 图5

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 inserting and updating data:

  1. //! Shows basic usage of mutating operations.
  2. static TStatus UpsertSimpleTransaction(TSession session, const TString& path) {
  3.     auto query = Sprintf(R"(
  4.         PRAGMA TablePathPrefix("%s");
  6.         UPSERT INTO episodes (series_id, season_id, episode_id, title) VALUES
  7.             (2, 6, 1, "TBD");
  8.     )", path.c_str());
  10.     return session.ExecuteDataQuery(query,
  11.         TTxControl::BeginTx(TTxSettings::SerializableRW()).CommitTx()).GetValueSync();
  12. }

C++ - 图6

PRAGMA TablePathPrefix adds a specified prefix to the database table paths. It uses standard file system path concatenation: i.e., it supports parent folder referencing and requires no trailing slash. For example:

  1. PRAGMA TablePathPrefix = "/cluster/database";
  2. SELECT * FROM episodes;

C++ - 图7

For more information about PRAGMA YQL, see the YQL documentation.

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 ExecuteDataQuery method.
The SDK lets you explicitly control the execution of transactions and configure the transaction execution mode using the TTxControl class.

In the code snippet below, the transaction is started with the TTxControl::BeginTx method. With TTxSettings, set the SerializableRW transaction execution mode. When all the queries in the transaction are completed, the transaction is automatically completed by explicitly setting CommitTx(). The query described using the YQL syntax is passed to the ExecuteDataQuery method for execution.

  1. //! Shows basic usage of YDB data queries and transactions.
  2. static TStatus SelectSimpleTransaction(TSession session, const TString& path,
  3.     TMaybe<TResultSet>& resultSet)
  4. {
  5.     auto query = Sprintf(R"(
  6.         PRAGMA TablePathPrefix("%s");
  8.         SELECT series_id, title, DateTime::ToDate(DateTime::FromDays(release_date)) AS release_date
  9.         FROM series
  10.         WHERE series_id = 1;
  11.     )", path.c_str());
  13.     auto txControl =
  14.         // Begin new transaction with SerializableRW mode
  15.         TTxControl::BeginTx(TTxSettings::SerializableRW())
  16.         // Commit transaction at the end of the query
  17.         .CommitTx();
  19.     // Executes data query with specified transaction control settings.
  20.     auto result = session.ExecuteDataQuery(query, txControl).GetValueSync();
  22.     if (result.IsSuccess()) {
  23.         // Index of result set corresponds to its order in YQL query
  24.         resultSet = result.GetResultSet(0);
  25.     }
  27.     return result;
  28. }

C++ - 图8

Processing execution results

The TResultSetParser class is used for processing query results.
The code snippet below shows how to process query results using the parser object:

  1.     TResultSetParser parser(*resultSet);
  2.     if (parser.TryNextRow()) {
  3.         Cout << "> SelectSimple:" << Endl << "Series"
  4.             << ", Id: " << parser.ColumnParser("series_id").GetOptionalUint64()
  5.             << ", Title: " << parser.ColumnParser("title").GetOptionalUtf8()
  6.             << ", Release date: " << parser.ColumnParser("release_date").GetOptionalString()
  7.             << Endl;
  8.     }

C++ - 图9

The given code snippet outputs the following text to the console at startup:

  1. > SelectSimple:
  2. series, Id: 1, title: IT Crowd, Release date: 2006-02-03

C++ - 图10

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.

The code snippet shows the use of parameterized queries and the GetParamsBuilder to generate parameters and pass them to the ExecuteDataQuery method.

  1. //! Shows usage of parameters in data queries.
  2. static TStatus SelectWithParamsTransaction(TSession session, const TString& path,
  3.     ui64 seriesId, ui64 seasonId, TMaybe<TResultSet>& resultSet)
  4. {
  5.     auto query = Sprintf(R"(
  6.         PRAGMA TablePathPrefix("%s");
  8.         DECLARE $seriesId AS Uint64;
  9.         DECLARE $seasonId AS Uint64;
  11.         SELECT sa.title AS season_title, sr.title AS series_title
  12.         FROM seasons AS sa
  13.         INNER JOIN series AS sr
  14.         ON sa.series_id = sr.series_id
  15.         WHERE sa.series_id = $seriesId AND sa.season_id = $seasonId;
  16.     )", path.c_str());
  18.     // Type of parameter values should be exactly the same as in DECLARE statements.
  19.     auto params = session.GetParamsBuilder()
  20.         .AddParam("$seriesId")
  21.             .Uint64(seriesId)
  22.             .Build()
  23.         .AddParam("$seasonId")
  24.             .Uint64(seasonId)
  25.             .Build()
  26.         .Build();
  28.     auto result = session.ExecuteDataQuery(
  29.         query,
  30.         TTxControl::BeginTx(TTxSettings::SerializableRW()).CommitTx(),
  31.         std::move(params)).GetValueSync();
  33.     if (result.IsSuccess()) {
  34.         resultSet = result.GetResultSet(0);
  35.     }
  37.     return result;
  38. }

C++ - 图11

The given code snippet outputs the following text to the console at startup:

  1. > SelectWithParams:
  2. Season, title: Season 3, series title: Silicon Valley
  3. Finished preparing query: PreparedSelectTransaction

C++ - 图12

Parameterized prepared queries

Parameterized prepared queries are saved as templates where specially formatted names are replaced by relevant parameter values each time you execute the query. Use parameterized queries to improve performance by reducing how often queries that only differ in parameter values are compiled and recompiled. The prepared query is stored in the session context.

Code snippet for parameterized prepared queries:

  1. //! Shows usage of prepared queries.
  2. static TStatus PreparedSelectTransaction(TSession session, const TString& path,
  3.     ui64 seriesId, ui64 seasonId, ui64 episodeId, TMaybe<TResultSet>& resultSet)
  4. {
  5.     // Once prepared, query data is stored in the session and identified by QueryId.
  6.     // Local query cache is used to keep track of queries, prepared in current session.
  7.     auto query = Sprintf(R"(
  8.         PRAGMA TablePathPrefix("%s");
  10.         DECLARE $seriesId AS Uint64;
  11.         DECLARE $seasonId AS Uint64;
  12.         DECLARE $episodeId AS Uint64;
  14.         SELECT *
  15.         FROM episodes
  16.         WHERE series_id = $seriesId AND season_id = $seasonId AND episode_id = $episodeId;
  17.     )", path.c_str());
  19.     // Prepare query or get result from query cache
  20.     auto prepareResult = session.PrepareDataQuery(query).GetValueSync();
  21.     if (!prepareResult.IsSuccess()) {
  22.         return prepareResult;
  23.     }
  25.     if (!prepareResult.IsFromCache()) {
  26.         Cerr << "+Finished preparing query: PreparedSelectTransaction" << Endl;
  27.     }
  29.     auto dataQuery = prepareResult.GetQuery();
  31.     auto params = dataQuery.GetParamsBuilder()
  32.         .AddParam("$seriesId")
  33.             .Uint64(seriesId)
  34.             .Build()
  35.         .AddParam("$seasonId")
  36.             .Uint64(seasonId)
  37.             .Build()
  38.         .AddParam("$episodeId")
  39.             .Uint64(episodeId)
  40.             .Build()
  41.         .Build();
  43.     auto result = dataQuery.Execute(TTxControl::BeginTx(TTxSettings::SerializableRW()).CommitTx(),
  44.         std::move(params)).GetValueSync();
  46.     if (result.IsSuccess()) {
  47.         resultSet = result.GetResultSet(0);
  48.     }
  50.     return result;
  51. }

C++ - 图13

The given code snippet outputs the following text to the console at startup:

  1. > PreparedSelect:
  2. Episode 7, title: To Build a Better Beta, Air date: Sun Jun 05, 2016

C++ - 图14

You can use the GetPreparedQuery method to check whether there is a prepared query in the session. If there’s no prepared query in the session context yet, you can prepare one using PrepareDataQuery and save it for use within the current session via AddPreparedQuery.

Multistep transactions

Multiple commands are executed within a single multistep transaction. The client-side code can be run between query executions. Using a transaction ensures that select queries made in its context are consistent with each other.

The first step is to prepare and execute the first query:

  1. //! Shows usage of transactions consisting of multiple data queries with client logic between them.
  2. static TStatus MultiStepTransaction(TSession session, const TString& path, ui64 seriesId, ui64 seasonId,
  3.     TMaybe<TResultSet>& resultSet)
  4. {
  5.     auto query1 = Sprintf(R"(
  6.         PRAGMA TablePathPrefix("%s");
  8.         DECLARE $seriesId AS Uint64;
  9.         DECLARE $seasonId AS Uint64;
  11.         SELECT first_aired AS from_date FROM seasons
  12.         WHERE series_id = $seriesId AND season_id = $seasonId;
  13.     )", path.c_str());
  15.     auto params1 = session.GetParamsBuilder()
  16.         .AddParam("$seriesId")
  17.             .Uint64(seriesId)
  18.             .Build()
  19.         .AddParam("$seasonId")
  20.             .Uint64(seasonId)
  21.             .Build()
  22.         .Build();
  24.     // Execute first query to get the required values to the client.
  25.     // Transaction control settings don't set CommitTx flag to keep transaction active
  26.     // after query execution.
  27.     auto result = session.ExecuteDataQuery(
  28.         query1,
  29.         TTxControl::BeginTx(TTxSettings::SerializableRW()),
  30.         std::move(params1)).GetValueSync();
  32.     if (!result.IsSuccess()) {
  33.         return result;
  34.     }

C++ - 图15

To continue working within the current transaction, you need to get the current transaction ID:

  1.     // Get active transaction id
  2.     auto tx = result.GetTransaction();
  4.     TResultSetParser parser(result.GetResultSet(0));
  5.     parser.TryNextRow();
  6.     auto date = parser.ColumnParser("from_date").GetOptionalUint64();
  8.     // Perform some client logic on returned values
  9.     auto userFunc = [] (const TInstant fromDate) {
  10.         return fromDate + TDuration::Days(15);
  11.     };
  13.     TInstant fromDate = TInstant::Days(*date);
  14.     TInstant toDate = userFunc(fromDate);

C++ - 图16

The next step is to create the next query that uses the results of code execution on the client side:

  1.     // Construct next query based on the results of client logic
  2.     auto query2 = Sprintf(R"(
  3.         PRAGMA TablePathPrefix("%s");
  5.         DECLARE $seriesId AS Uint64;
  6.         DECLARE $fromDate AS Uint64;
  7.         DECLARE $toDate AS Uint64;
  9.         SELECT season_id, episode_id, title, air_date FROM episodes
  10.         WHERE series_id = $seriesId AND air_date >= $fromDate AND air_date <= $toDate;
  11.     )", path.c_str());
  13.     auto params2 = session.GetParamsBuilder()
  14.         .AddParam("$seriesId")
  15.             .Uint64(seriesId)
  16.             .Build()
  17.         .AddParam("$fromDate")
  18.             .Uint64(fromDate.Days())
  19.             .Build()
  20.         .AddParam("$toDate")
  21.             .Uint64(toDate.Days())
  22.             .Build()
  23.         .Build();
  25.     // Execute second query.
  26.     // Transaction control settings continues active transaction (tx) and
  27.     // commits it at the end of second query execution.
  28.     result = session.ExecuteDataQuery(
  29.         query2,
  30.         TTxControl::Tx(*tx).CommitTx(),
  31.         std::move(params2)).GetValueSync();
  33.     if (result.IsSuccess()) {
  34.         resultSet = result.GetResultSet(0);
  35.     }
  37.     return result;
  38. }

C++ - 图17

The given code snippets output the following text to the console at startup:

  1. > MultiStep:
  2. Episode 1, Season: 5, title: Grow Fast or Die Slow, Air date: Sun Mar 25, 2018
  3. Episode 2, Season: 5, title: Reorientation, Air date: Sun Apr 01, 2018
  4. Episode 3, Season: 5, title: Chief Operating Officer, Air date: Sun Apr 08, 2018

C++ - 图18

Managing transactions

Transactions are managed through TCL Begin and Commit calls.

In most cases, instead of explicitly using Begin and Commit calls, it’s better to use transaction control parameters in execute calls. This helps you avoid unnecessary requests to YDB and run your queries more efficiently.

Code snippet for BeginTransaction and tx.Commit() calls:

  1. // Show usage of explicit Begin/Commit transaction control calls.
  2. // In most cases it's better to use transaction control settings in ExecuteDataQuery calls instead
  3. // to avoid additional hops to YDB cluster and allow more efficient execution of queries.
  4. static TStatus ExplicitTclTransaction(TSession session, const TString& path, const TInstant& airDate) {
  5.     auto beginResult = session.BeginTransaction(TTxSettings::SerializableRW()).GetValueSync();
  6.     if (!beginResult.IsSuccess()) {
  7.         return beginResult;
  8.     }
  10.     // Get newly created transaction id
  11.     auto tx = beginResult.GetTransaction();
  13.     auto query = Sprintf(R"(
  14.         PRAGMA TablePathPrefix("%s");
  16.         DECLARE $airDate AS Date;
  18.         UPDATE episodes SET air_date = DateTime::ToDays($airDate) WHERE title = "TBD";
  19.     )", path.c_str());
  21.     auto params = session.GetParamsBuilder()
  22.         .AddParam("$airDate")
  23.             .Date(airDate)
  24.             .Build()
  25.         .Build();
  27.     // Execute data query.
  28.     // Transaction control settings continues active transaction (tx)
  29.     auto updateResult = session.ExecuteDataQuery(query,
  30.         TTxControl::Tx(tx),
  31.         std::move(params)).GetValueSync();
  33.     if (!updateResult.IsSuccess()) {
  34.         return updateResult;
  35.     }
  37.     // Commit active transaction (tx)
  38.     return tx.Commit().GetValueSync();
  39. }

C++ - 图19