Chapter 12. INSERTing into a resultset

Index

INSERTing into a nested resultset
INSERTing into tables with serial/autoincrement columns
Schema:
<schema>
  <table name="accounts">
    <column name="account_id" datatype="int64_t" primarykey='1' />
    <column name="code" />

    <join class="ledger_entries">
      <column>account_id</column>
    </join>
  </table>

  <table name="ledger_entries">
    <column name="ledger_entry_id" datatype="int64_t" primarykey='1' />
    <column name="account_id" datatype="int64_t"/>
    <column name="ledger_date" datatype="x::ymd"/>
    <column name="amount" datatype="double"/>

    <join class="accounts" type="inner join" only="1">
      <column>account_id</column>
    </join>
  </table>
</schema>
Code:
auto accounts_rs=accounts::create(conn);

accounts_rs::base::row account_1=
    accounts_rs->insert("account_id", 1,
                         "code", "Code1");

insert() takes a variable list of column name and value tuples, similar to update(), and executes an SQL INSERT statement. Any datatype that execute() implements can be used for each individual value. nullptr specifies the value as SQL NULL. This example executes SQL that's equivalent to: INSERT INTO accounts(account_id, code) VALUES(1, "Code1"). insert(), like uppdate() also takes std::map for a parameter, which gets interpreted the same way.

Column names given to insert() must include all of the underlying table's primary keys (except for serial keys). insert() returns the newly-inserted row, with its newly-inserted values. This is done by immediately SELECTing the row, by its primary key, after inserting it. As such, the new row's values accurately reflect any default column values, as defined in the database schema.

insert() throws an exception if the resultset has a group_by(), having(), or an order_by().

The resultset may contain name=value search() constraints. They get turned into column name and value tuples, as if they were given directly in the insert(). This automatically does the right thing with nested resultsets.

INSERTing into a nested resultset

// ...
auto ledger_entries=account_1->join_ledger_entries();

auto new_row=ledger_entries->insert("ledger_entry_id", 10,
                                    "ledger_date", x::ymd(),
                                    "amount", 99);

Continuing the previous example, account_1 is a new row in the accounts table, with account_id of 1. This example creates a nested resultset from the row. The ledger_entries resultset has a account_id=1 search() constraint. As a result, the subsequent insert() inserts new row that has the given column values, and with the account_id column set to 1.

The underlying concept is that a join_classname() returns a resultset with a constraint for the joined-to rows. Calling insert() on that constraint inserts a new row that meets that constraint.