[/ Copyright (c) 2019-2024 Ruben Perez Hidalgo (rubenperez038 at gmail dot com) Distributed under the Boost Software License, Version 1.0. (See accompanying file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt) ] [section:overview Overview] [nochunk] This section briefly explains the library main classes and functions, and how to use them. [section:connection Connection objects] [reflink connection] is the most important class in the library. A connection is an I/O object, templated on a [reflink Stream] type. A `connection` contains an instance of that `Stream` type and additional state required by the protocol. `connection`'s constructor takes the same arguments as the underlying `Stream` constructor. The library defines some typedefs to make things less verbose. The most common one is [reflink tcp_ssl_connection]. In this case, `Stream` is `boost::asio::ssl::stream`, which can be constructed from a [asioreflink any_io_executor any_io_executor] and a [asioreflink ssl__context ssl::context]: [overview_connection] Typedefs for other transports are also available. See [link mysql.other_streams this section] for more info. [endsect] [section Connection establishment] The MySQL client/server protocol is session-oriented. Before anything else, you must perform session establishment, usually by calling [refmem connection connect]. This function performs two actions: * It establishes the "physical" connection, by calling `connect()` on the underlying `Stream` object. For a [reflink tcp_ssl_connection], this establishes the TCP connection. * It performs the handshake with the MySQL server. This is part of the MySQL client/server protocol. It performs authentication, sets session parameters like the default database to use, and performs the TLS handshake, if required. [refmem connection connect] takes two parameters, one for each of the above actions: * The physical endpoint where the server is listening. For TCP streams, this is a `boost::asio::ip::tcp::endpoint`. For UNIX sockets, it's a `boost::asio::local::stream_protocol::endpoint`. For TCP, we can resolve a string hostname and port into an endpoint using a `resolver` object. * [reflink handshake_params] to use for the handshake operation. This parameter doesn't depend on the `Stream` type. See [link mysql.connparams this section] for more info. [overview_connect] Note that [refmem connection connect] can only be used with socket-like streams. If your stream is not a socket, you must use the lower-level [refmem connection handshake] function. Please read [link mysql.other_streams.non_sockets this section] for more info. [endsect] [section:queries_stmts Text queries and prepared statements] The two main ways to use a connection are text queries and prepared statements. You can access both using [refmem connection execute]: [table [ [Feature] [Used for...] [Code] ] [ [ Text queries ] [ Simple queries, without parameters: * `"START TRANSACTION"` * `"SET NAMES utf8"` * `"SHOW TABLES"` ] [ [overview_query_use_case] ] ] [ [ Prepared statements ] [ Queries with parameters unknown at compile-time. ] [ [overview_statement_use_case] ] ] ] [endsect] [section The dynamic and the static interfaces] There are two different interfaces to access the rows generated by a query or statement. You can use the [reflink results] class to access rows using a dynamically-typed interface, using variant-like objects to represent values retrieved from the server. On ther other hand, [reflink static_results] is statically-typed. You specify the shape of your rows at compile-time, and the library will parse the retrieved values for you into the types you provide. You can use almost every feature in this library (including text queries and prepared statements) with both interfaces. For example, given the following table: [overview_ifaces_table] This is how you would access its contents using either of the interfaces: [table [ [Interface] [Description] [Example] ] [ [ Dynamic interface: [reflink results] ] [ Variant based[br] Available in C++11[br] [link mysql.dynamic_interface Learn more][br] [link mysql.examples.prepared_statements_cpp11 Example code] ] [ [overview_ifaces_dynamic] ] ] [ [ Static interface: [reflink static_results] ] [ Parses rows into your own types[br] Requires C++14[br] [link mysql.static_interface Learn more][br] [link mysql.examples.prepared_statements_cpp14 Example code] ] [ [describe_post][br] [overview_ifaces_static] ] ] ] [endsect] [section Resultsets] In MySQL, a ['resultset] referes to the results generated by a SQL query. When you execute a text query or a prepared statement, you get back a resultset. We've already been using resultsets: the [reflink results] and [reflink static_results] classes are in-memory representations of a resultset. A resultset is composed of three pieces of data: [variablelist [ [Rows] [ The actual rows generated by the SQL query: [refmem results rows] and [refmem static_results rows]. ] ] [ [Metadata] [ Information about the columns retrieved by the query: [refmem results meta] and [refmem static_results meta]. There is one object per retrieved column. It provides information about column names, types, uniqueness contraints... ] ] [ [Additional execution information] [ Extra info on the execution of the operation, like the number of affected rows ([refmem results affected_rows] and [refmem static_results affected_rows]) or the last auto-generated ID for `INSERT` statements ([refmem results last_insert_id] and [refmem static_results last_insert_id]). ] ] ] All SQL statements generate resultsets. Statements that generate no rows, like `INSERT`s, generate empty resultsets (i.e. `result.rows().empty() == true`). The interface to execute `SELECT`s and `INSERT`s is the same. [endsect] [section:statements Prepared statements] Until now, we've used simple text queries that did not contain any user-provided input. In the real world, most queries will contain some piece of user-provided input. One approach could be to use string concatenation to construct a SQL query from user input, and then pass it to `execute()`. Avoid this approach as much as possible, as it can lead to [*SQL injection vulnerabilities]. Instead, [*use prepared statements]. Prepared statements are server-side objects that represent a parameterized query. A statement is represented using the [reflink statement] class, which is a lightweight object holding a handle to the server-side prepared statement. Let's say you've got an inventory table, and you're writing a command-line program to get products by ID. You've got the following table definition: [overview_statements_setup] You can prepare a statement to retrieve products by ID using [refmem connection prepare_statement]: [overview_statements_prepare] You can execute the statement using [refmem connection execute]: [overview_statements_execute] We used [refmem statement bind] to provide actual parameters to the statement. You must pass as many parameters to `bind` as `?` placeholders the statement has. To learn more about prepared statements, please refer to [link mysql.prepared_statements this section]. [endsect] [section:errors Error handling] The functions we've been using communicate errors throwing exceptions. There are also non-throwing overloads that use error codes. If the server fails to fulfill a request (for example, because the provided SQL was invalid or a referenced table didn't exist), the operation is considered failed and will return an error. The server provides an error message that can be accessed using the [reflink diagnostics] class. For example: [overview_errors_sync_errc] With exceptions, this would be: [overview_errors_sync_exc] [endsect] [section:async Asynchronous functions] As with Boost.Asio, every sync operation has an async counterpart. This library follows Asio's async model, so you may use async operations with any valid Asio `CompletionToken`, including callbacks and coroutines. For example, if you can use C++20, you can write: [overview_async_coroutinescpp20] The [link mysql.examples examples section] contains material that can help you. [link mysql.async This section] also provides more info on this topic. [h4 Single outstanding operation per connection] At any given point in time, a `connection` may only have a single async operation outstanding. This is because the connection uses the underlying `Stream` object directly, without any locking or queueing. If you perform several async operations concurrently on a single connection without any serialization, the stream may interleave reads and writes from different operations, leading to undefined behavior. For example, doing the following is illegal and should be avoided: [overview_async_dont] If you need to perform queries in parallel, open more connections to the server. [endsect] [section Multi-function operations] Until now, we've been using [refmem connection execute], which executes some SQL and reads all generated data into an in-memory object. Some use cases may not fit in this simple pattern. For example: * When reading a very big resultset, it may not be efficient (or even possible) to completely load it in memory. Reading rows in batches may be more adequate. * If rows contain very long `TEXT` or `BLOB` fields, it may not be adequate to copy these values from the network buffer into the `results` object. A view-based approach may be better. For these cases, we can break the `execute()` operation into several steps, using a ['multi-function operation] (the term is coined by this library). This example reads an entire table in batches, which can be the case in an ETL process: [overview_multifn] [warning Once you start a multi-function operation with [refmem connection start_execution], the server immediately sends all rows to the client. [*You must read all rows] before engaging in further operations. Otherwise, you will encounter packet mismatches, which can lead to bugs and vulnerabilities! ] Multi-function operations are powerful but complex. Only use them when there is a strong reason to do so. Multi-function operations also work with the static interface. Please refer to [link mysql.multi_function this section] for more information on these operations. [endsect] [endsect]