The TABLE ACCESS service allows components to read and write to MySQL tables owned by the component.

The table access service is meant to be used to operate on "well known" tables, with a known name and structure. It is not meant to access arbitrary tables in a generic way.

The overall service consists of the following parts:

The table access factory (s_mysql_table_access_factory_v1)
The table access (s_mysql_table_access_v1)
The table access index (s_mysql_table_access_index_v1)
The table access scan (s_mysql_table_access_scan_v1)
The table access update (s_mysql_table_access_update_v1)

To manipulate table columns in a type safe manner, and to decouple data types from the general service, each data type supported is manipulated using the following parts:

The field access for integer types (s_mysql_field_integer_access_v1)
The field access for varchar types (s_mysql_field_varchar_access_v1)

In addition, to manipulate fields in a generic way, each field can be read from and written to using 'any':

The field access for any types (s_mysql_field_any_access_v1)

All these parts are related, and share the Table_access type.

To read from, or write to, a MySQL table from a component, the component code must, in that order:

create a table access object, using the factory,
populate the table access object, with all the tables to open,
call the begin operation,
check that the table DDL for each table corresponds to the component expectations.

At this point, the tables are ready for use. Supported operations are:

full table scan,
index open,
index scans,
index fetch,
insert, update or delete on a row.

For table access sessions that update data, the session can be committed.

To complete the session, either commit or rollback, then destroy the session object.

The table access service only supports basic DML operations (no DDL).

Please note that, in all the code examples below:

the variables "*_srv" points to the proper service part, without details,
proper error handling is missing.

This is deliberate, to have legible code examples.

Factory

The entry point to the table access service is the factory part (s_mysql_table_access_factory_v1).

Example below:

MYSQL_THD thd;
Table_access ta;
current_thd_srv->get(&thd);
ta = srv->create_table_access(thd, 3);
// session using up to 3 tables here
srv->destroy_table_access(ta);

Add tables

Every table involved in the table access session must be added explicitly, using s_mysql_table_access_v1

Table can be read from or written to.

Example below:

size_t ticket_customer;
size_t ticket_order;
size_t ticket_order_item;
 
// Add table shop.customer
ticket_customer = srv->add_table(ta,
                                 "shop", 4,
                                 "customer", 8,
                                 TA_READ);
 
// Add table shop.order
ticket_order = srv->add_table(ta,
                              "shop", 4,
                              "order", 5,
                              TA_WRITE);
 
// Add table shop.order_line
ticket_order_line = srv->add_table(ta,
                                   "shop", 4,
                                   "order_line", 10,
                                   TA_WRITE);

The result of add_table is a ticket that will be used later to retrieve the table once opened. These tickets should be preserved in the calling code.

Begin

An important property of the table access service is that all tables used in a given table access session are opened together, and locked together.

This part is critical for the mysqld server operation:

all the metadata locks involved must be acquired at the same time,
it prevents deadlocks between concurrent client sessions.

The component code should be prepared to handle errors, as all tables might not exist in the database (install or upgrade in progress), or might exist but not be writable (GLOBAL READ LOCK in place in the current session, system in READ ONLY or SUPER READ ONLY state).

Example below:

int rc = srv->begin(ta);
if (rc != 0) {
  // failed
}

Check tables

Once the open and lock operation succeeds, tables with the proper name are guaranteed to exist in the database.

This says nothing about the actual table structure.

Note that:

component code is compiled in a component binary, deployed in a library, delivered with a given life cycle (software install),
component data is stored in a MySQL table, stored ultimately somewhere on disk, which follows a different life cycle (database install, upgrade, backup, restore).

The next step to perform table dml is to make sure that the table structure implemented in the component code actually matches the table structure found on disk.

The way to achieve this is to declare, in the code, the expected table structure, and compare it to the table found after open.

This check must be performed for every table involved.

Note that it is not required to check every column in a table: only checking the columns that the code actually uses is sufficient.

Example below:

TA_table table_customer;
TA_table table_order;
TA_table table_order_line;
 
// Find the opened tables
table_customer = srv->get_table(ta, ticket_customer);
table_order = srv->get_table(ta, ticket_order);
table_order_line = srv->get_table(ta, ticket_order_line);
 
// Verify the table shop.customer structure
static const int COL_ID = 0;
static const int COL_NAME = 1;
static const int COL_ADDRESS = 2;
static const TA_table_field_def columns_customer[] = {
  {COL_ID, "ID", 2, TA_TYPE_INTEGER, 0},
  {COL_NAME, "NAME", 4, TA_TYPE_VARCHAR, 64},
  {COL_ADDRESS, "ADDRESS", 7, TA_TYPE_VARCHAR, 255}};
 
rc = srv->check_table_fields(ta, table_customer, columns_customer, 3);
if (rc != 0) {
  // Failed: the table on disk is not what the code expects
}
 
// Verify the table shop.order structure
static const TA_table_field_def columns_order[] = { ... };
rc = srv->check_table_fields(ta, table_order, columns_order, ...);
 
// Verify the table shop.order_line structure
static const TA_table_field_def columns_order_line[] = { ... };
rc = srv->check_table_fields(ta, table_order_line, columns_order_line, ...);

Table scan

Once a table structure has been checked, the code can now access specific columns with safety.

Columns used when reading a row must be acquired based on the column ordinal position in the table, which is known by the calling code.

The table scan itself consists of a loop, processing one row at a time.

Example below:

my_h_string name_value;
my_h_string address_value;
 
string_factory_srv->create(&name_value);;
string_factory_srv->create(&address_value);;
 
scan_srv->init(ta, table_customer);
 
while (scan_srv->next(ta, table_customer) == 0) {
  // NAME column in table shop.customer is as index 1
  srv_varchar->get(ta, table_customer, COL_NAME, name_value);
 
  // ADDRESS column in table shop.customer is at index 2
  srv_varchar->get(ta, table_customer, COL_ADDRESS, address_value);
 
  // Use name_value and address_value
}
 
scan_srv->end(ta, table_customer);
 
string_factory_srv->destroy(name_value);;
string_factory_srv->destroy(address_value);;

Index open

To use an index, the caller must provide:

the index name, per the table DDL,
the list of columns part of the index definition, per the table DDL.

This is required to ensure that what:

the index used in the code
the index defined in the actual table

are actually the same.

Example below:

// For a table such as:
//   CREATE TABLE person (NAME VARCHAR(50), SURNAME VARCHAR(50), ...);
//   ALTER TABLE person ADD INDEX NAME_AND_SURNAME(NAME, SURNAME);
// Index metadata:
// - the key name is NAME_AND_SURNAME
// - the columns in the key are:
//   - "NAME" (in ascending order)
//   - "SURNAME" (in ascending order)
// - the number of columns in the key is 2
 
const char *index_name = "NAME_AND_SURNAME";
size_t int index_name_length = 16;
const TA_index_field_def index_cols[] = {
  {"NAME", 4, true}
  {"SURNAME", 7, true}
};
const size_t index_numcol = 2;
 
// Open the index.
 
TA_key index_key = nullptr;
if (index_srv->init(ta,
                    table_person,
                    index_name, index_name_length,
                    index_cols, index_numcol,
                    &index_key)) {
  // Opening index failed.
}
 
// Use the index.
...
 
// Close the index.
 
if (index_key != nullptr) {
  index_srv->end(access, table_person, index_key);
}

Index scan

An index scan is similar to a full table scan, except that rows will be processed in index order.

Example below:

rc = index_srv->first(ta, table_person, index_key);
 
while (rc == 0) {
  // Do something with the current row.
 
  // Find the next row.
  rc = index_srv->next(ta, table_person, index_key);
}

Index fetch

An index fetch can be executed to find particular rows.

The sequence consist of:

populating the search key
looking up the index for that key
optionally, when the key is not unique, loop for more records matching the search key.

Example involving a simple fetch below:

my_h_string name_value = ...;
my_h_string surname_value = ...;
CHARSET_INFO_h utf8 = charset_srv->get_utf8mb4();
 
// name_value = "Doe"
string_convert_srv->convert_from_buffer(name_value, "Doe", 3, utf8);
// surname_value = "John"
string_convert_srv->convert_from_buffer(surname_value, "John", 4, utf8);
 
// Write NAME = "Doe" in the search key (at index 0)
fa_varchar_srv->set(ta, table_person, COL_NAME, name_value));
 
// Write SURNAME = "John" in the search key (at index 1)
fa_varchar_srv->set(ta, table_person, COL_SURNAME, surname_value));
 
// Fetch the index with a 2 key parts search key (NAME + SURNAME)
rc = index_srv->read_map(ta, table_person, 2, index_key);
 
if (rc == 0) {
  // Found John Doe.
}

Example involving a partial key fetch below:

my_h_string name_value = ...;
my_h_string surname_value = ...;
CHARSET_INFO_h utf8 = charset_srv->get_utf8mb4();
 
// name_value = "Smith"
string_convert_srv->convert_from_buffer(name_value, "Smith", 5, utf8);
 
// Write NAME = "Smith" in the search key
fa_varchar_srv->set(ta, table_person, COL_NAME, &name_value));
 
// Fetch the index with a 1 key parts search key (NAME only)
rc = index_srv->read_map(ta, table_person, 1, index_key);
 
while (rc == 0) {
  // Found someone named Smith, read the surname.
  fa_varchar_srv->get(ta, table_person, COL_SURNAME, surname_value));
 
  // Find next record
  rc = index_srv->next_same(ta, table_person, index_key);
}

Write data

To insert a record, the table must be added to the table access session in TA_WRITE mode.

An insert consist of:

writing to each column in the current record
calling the insert service.

Example below:

my_h_string name_value = ...;
my_h_string address_value = ...;
 
srv_varchar->set(ta, table_customer, COL_NAME, name_value);
srv_varchar->set(ta, table_customer, COL_ADDRESS, address_value);
 
rc = update_srv->insert(ta, table_customer);
if (rc == 0) {
  // Row inserted.
}

Update data

To update a record, the table must be added to the table access session in TA_WRITE mode.

The record to update must be located in a scan:

either in a init() / next() table scan loop
or in an init() / first() / next() index scan loop
or in an init() / read_map() / next_same() index fetch

Then:

write columns to change in the current record
invoke the update service

Example below:

// Table cursor positioned on the row to update
 
// Write to columns
my_h_string address_value = ...;
srv_varchar->set(ta, table_customer, COL_ADDRESS, address_value);
 
// Update the current row
rc = update_srv->update(ta, table_customer);
if (rc == 0) {
  // Row Updated.
}

Delete data

Similar with an update, the table must be opened in TA_WRITE mode, and the table cursor positioned on the row to delete.

Example below:

// Table cursor positioned on the row to delete
 
// Delete the current row
rc = update_srv->delete_row(ta, table_customer);
if (rc == 0) {
  // Row Deleted.
}