INSERTing into a resultset
- 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_()
returns a resultset with a constraint for the joined-to rows.
Calling classnameinsert() on that constraint inserts
a new row that meets that constraint.