[odb-users] composite ID containing an auto-incrementable field
Sten Kultakangas
ratkaisut at gmail.com
Tue Jul 28 02:47:21 EDT 2020
Hi Boris
I was able to produce code which inserts the record to the database,
however, i could not retrieve the inserted ID. I suspect
mssql::object_statements<object_type>::id_image() cannot be used with more
than one bound value. Can you confirm this? If that's the case, then the
following assignment cannot work:
obj.key = id (sts.id_image ());
So we would need to generate a totally different alternative to
access::object_traits_impl< ::GenericSwitch::Extension, id_mssql >::id()
Obviously we only need the inserted auto id, not the tenant_id, but i
assumed we can reuse the existing code which can be used to generate
access::object_traits_impl< ::GenericSwitch::Extension, id_mssql >::id()
Here's the code
const char access::object_traits_impl< ::GenericSwitch::Extension,
id_mssql >::persist_statement[] =
"INSERT INTO [GenericSwitch].[dbo].[Extension] "
"([tenant_id], "
"[extension], "
"[name], "
"[type_id], "
"[timezone], "
"[coverage_map_id], "
"[office_hours_map_id], "
"[office_holidays_map_id], "
"[rec_days]) "
"OUTPUT INSERTED.[id],INSERTED.[tenant_id] "
"VALUES "
"(?, ?, ?, ?, ?, ?, ?, ?, ?)";
void access::object_traits_impl< ::GenericSwitch::Extension, id_mssql >::
persist (database& db, object_type& obj)
{
ODB_POTENTIALLY_UNUSED (db);
using namespace mssql;
mssql::connection& conn (
mssql::transaction::current ().connection ());
statements_type& sts (
conn.statement_cache ().find_object<object_type> ());
callback (db,
obj,
callback_event::pre_persist);
image_type& im (sts.image ());
binding& imb (sts.insert_image_binding ());
init (im, obj, statement_insert);
if (im.version != sts.insert_image_version () ||
imb.version == 0)
{
bind (imb.bind, im, statement_insert);
sts.insert_image_version (im.version);
imb.version++;
}
{
id_image_type& i (sts.id_image ());
binding& b (sts.id_image_binding ());
if (i.version != sts.id_image_version () || b.version == 0)
{
bind (b.bind, i);
sts.id_image_version (i.version);
b.version++;
}
}
insert_statement& st (sts.persist_statement ());
if (!st.execute ())
throw object_already_persistent ();
obj.key = id (sts.id_image ()); // <--- problem here
callback (db,
obj,
callback_event::post_persist);
}
access::object_traits_impl< ::GenericSwitch::Extension, id_mssql
>::id_type
access::object_traits_impl< ::GenericSwitch::Extension, id_mssql >::
id (const id_image_type& i)
{
mssql::database* db (0);
ODB_POTENTIALLY_UNUSED (db);
id_type id;
{
composite_value_traits< ::GenericSwitch::ObjectId, id_mssql >::init (
id,
i.id_value,
db);
}
return id;
}
access::object_traits_impl< ::GenericSwitch::Extension, id_mssql
>::id_type
access::object_traits_impl< ::GenericSwitch::Extension, id_mssql >::
id (const image_type& i)
{
mssql::database* db (0);
ODB_POTENTIALLY_UNUSED (db);
id_type id;
{
composite_value_traits< ::GenericSwitch::ObjectId, id_mssql >::init (
id,
i.key_value,
db);
}
return id;
}
void access::object_traits_impl< ::GenericSwitch::Extension, id_mssql >::
bind (mssql::bind* b,
image_type& i,
mssql::statement_kind sk)
{
ODB_POTENTIALLY_UNUSED (sk);
using namespace mssql;
std::size_t n (0);
// key
//
if (sk == statement_insert) {
b[n].type = mssql::bind::bigint;
b[n].buffer = &i.key_value.tenant_id_value;
b[n].size_ind = &i.key_value.tenant_id_size_ind;
n++;
}
else if (sk != statement_update)
{
composite_value_traits< ::GenericSwitch::ObjectId, id_mssql >::bind (
b + n, i.key_value, sk);
n += 2UL;
}
// extension
//
b[n].type = mssql::bind::string;
b[n].buffer = &i.extension_value;
b[n].size_ind = &i.extension_size_ind;
b[n].capacity = static_cast<SQLLEN> (sizeof (i.extension_value));
n++;
// name
//
b[n].type = mssql::bind::nstring;
b[n].buffer = &i.name_value;
b[n].size_ind = &i.name_size_ind;
b[n].capacity = static_cast<SQLLEN> (sizeof (i.name_value));
n++;
// type_id
//
b[n].type = mssql::bind::bigint;
b[n].buffer = &i.type_id_value;
b[n].size_ind = &i.type_id_size_ind;
n++;
// timezone
//
b[n].type = mssql::bind::nstring;
b[n].buffer = &i.timezone_value;
b[n].size_ind = &i.timezone_size_ind;
b[n].capacity = static_cast<SQLLEN> (sizeof (i.timezone_value));
n++;
// coverage_map_id
//
b[n].type = mssql::bind::bigint;
b[n].buffer = &i.coverage_map_id_value;
b[n].size_ind = &i.coverage_map_id_size_ind;
n++;
// office_hours_map_id
//
b[n].type = mssql::bind::bigint;
b[n].buffer = &i.office_hours_map_id_value;
b[n].size_ind = &i.office_hours_map_id_size_ind;
n++;
// office_holidays_map_id
//
b[n].type = mssql::bind::bigint;
b[n].buffer = &i.office_holidays_map_id_value;
b[n].size_ind = &i.office_holidays_map_id_size_ind;
n++;
// rec_days
//
b[n].type = mssql::bind::int_;
b[n].buffer = &i.rec_days_value;
b[n].size_ind = &i.rec_days_size_ind;
n++;
}
void access::object_traits_impl< ::GenericSwitch::Extension, id_mssql >::
bind (mssql::bind* b, id_image_type& i)
{
std::size_t n (0);
mssql::statement_kind sk (mssql::statement_select);
composite_value_traits< ::GenericSwitch::ObjectId, id_mssql >::bind (
b + n, i.id_value, sk);
}
void access::object_traits_impl< ::GenericSwitch::Extension, id_mssql >::
init (image_type& i,
const object_type& o,
mssql::statement_kind sk)
{
ODB_POTENTIALLY_UNUSED (i);
ODB_POTENTIALLY_UNUSED (o);
ODB_POTENTIALLY_UNUSED (sk);
using namespace mssql;
if (i.change_callback_.callback != 0)
(i.change_callback_.callback) (i.change_callback_.context);
// key
//
if (sk == statement_insert)
{
// tenant_id
//
{
long long int const& v =
o.key.tenant_id;
bool is_null (false);
mssql::value_traits<
long long int,
mssql::id_bigint >::set_image (
i.key_value.tenant_id_value, is_null, v);
i.key_value.tenant_id_size_ind = is_null ? SQL_NULL_DATA : 0;
}
}
// extension
//
{
::std::string const& v =
o.extension;
bool is_null (false);
std::size_t size (0);
mssql::value_traits<
::std::string,
mssql::id_string >::set_image (
i.extension_value,
sizeof (i.extension_value) - 1,
size,
is_null,
v);
i.extension_size_ind =
is_null ? SQL_NULL_DATA : static_cast<SQLLEN> (size);
}
// name
//
{
::std::string const& v =
o.name;
bool is_null (false);
std::size_t size (0);
mssql::value_traits<
::std::string,
mssql::id_nstring >::set_image (
i.name_value,
sizeof (i.name_value) / 2 - 1,
size,
is_null,
v);
i.name_size_ind =
is_null ? SQL_NULL_DATA : static_cast<SQLLEN> (size * 2);
}
// type_id
//
{
::GenericSwitch::ExtensionType const& v =
o.type_id;
bool is_null (false);
mssql::value_traits<
::GenericSwitch::ExtensionType,
mssql::id_bigint >::set_image (
i.type_id_value, is_null, v);
i.type_id_size_ind = is_null ? SQL_NULL_DATA : 0;
}
// timezone
//
{
::std::string const& v =
o.timezone;
bool is_null (false);
std::size_t size (0);
mssql::value_traits<
::std::string,
mssql::id_nstring >::set_image (
i.timezone_value,
sizeof (i.timezone_value) / 2 - 1,
size,
is_null,
v);
i.timezone_size_ind =
is_null ? SQL_NULL_DATA : static_cast<SQLLEN> (size * 2);
}
// coverage_map_id
//
{
::odb::nullable< long long int > const& v =
o.coverage_map_id;
bool is_null (true);
mssql::value_traits<
::odb::nullable< long long int >,
mssql::id_bigint >::set_image (
i.coverage_map_id_value, is_null, v);
i.coverage_map_id_size_ind = is_null ? SQL_NULL_DATA : 0;
}
// office_hours_map_id
//
{
::odb::nullable< long long int > const& v =
o.office_hours_map_id;
bool is_null (true);
mssql::value_traits<
::odb::nullable< long long int >,
mssql::id_bigint >::set_image (
i.office_hours_map_id_value, is_null, v);
i.office_hours_map_id_size_ind = is_null ? SQL_NULL_DATA : 0;
}
// office_holidays_map_id
//
{
::odb::nullable< long long int > const& v =
o.office_holidays_map_id;
bool is_null (true);
mssql::value_traits<
::odb::nullable< long long int >,
mssql::id_bigint >::set_image (
i.office_holidays_map_id_value, is_null, v);
i.office_holidays_map_id_size_ind = is_null ? SQL_NULL_DATA : 0;
}
// rec_days
//
{
::odb::nullable< int > const& v =
o.rec_days;
bool is_null (true);
mssql::value_traits<
::odb::nullable< int >,
mssql::id_int >::set_image (
i.rec_days_value, is_null, v);
i.rec_days_size_ind = is_null ? SQL_NULL_DATA : 0;
}
}
void access::object_traits_impl< ::GenericSwitch::Extension, id_mssql >::
init (object_type& o,
const image_type& i,
database* db)
{
ODB_POTENTIALLY_UNUSED (o);
ODB_POTENTIALLY_UNUSED (i);
ODB_POTENTIALLY_UNUSED (db);
// key
//
{
::GenericSwitch::ObjectId& v =
o.key;
composite_value_traits< ::GenericSwitch::ObjectId, id_mssql >::init (
v,
i.key_value,
db);
}
// extension
//
{
::std::string& v =
o.extension;
mssql::value_traits<
::std::string,
mssql::id_string >::set_value (
v,
i.extension_value,
static_cast<std::size_t> (i.extension_size_ind),
i.extension_size_ind == SQL_NULL_DATA);
}
// name
//
{
::std::string& v =
o.name;
mssql::value_traits<
::std::string,
mssql::id_nstring >::set_value (
v,
i.name_value,
static_cast<std::size_t> (i.name_size_ind / 2),
i.name_size_ind == SQL_NULL_DATA);
}
// type_id
//
{
::GenericSwitch::ExtensionType& v =
o.type_id;
mssql::value_traits<
::GenericSwitch::ExtensionType,
mssql::id_bigint >::set_value (
v,
i.type_id_value,
i.type_id_size_ind == SQL_NULL_DATA);
}
// timezone
//
{
::std::string& v =
o.timezone;
mssql::value_traits<
::std::string,
mssql::id_nstring >::set_value (
v,
i.timezone_value,
static_cast<std::size_t> (i.timezone_size_ind / 2),
i.timezone_size_ind == SQL_NULL_DATA);
}
// coverage_map_id
//
{
::odb::nullable< long long int >& v =
o.coverage_map_id;
mssql::value_traits<
::odb::nullable< long long int >,
mssql::id_bigint >::set_value (
v,
i.coverage_map_id_value,
i.coverage_map_id_size_ind == SQL_NULL_DATA);
}
// office_hours_map_id
//
{
::odb::nullable< long long int >& v =
o.office_hours_map_id;
mssql::value_traits<
::odb::nullable< long long int >,
mssql::id_bigint >::set_value (
v,
i.office_hours_map_id_value,
i.office_hours_map_id_size_ind == SQL_NULL_DATA);
}
// office_holidays_map_id
//
{
::odb::nullable< long long int >& v =
o.office_holidays_map_id;
mssql::value_traits<
::odb::nullable< long long int >,
mssql::id_bigint >::set_value (
v,
i.office_holidays_map_id_value,
i.office_holidays_map_id_size_ind == SQL_NULL_DATA);
}
// rec_days
//
{
::odb::nullable< int >& v =
o.rec_days;
mssql::value_traits<
::odb::nullable< int >,
mssql::id_int >::set_value (
v,
i.rec_days_value,
i.rec_days_size_ind == SQL_NULL_DATA);
}
}
void access::object_traits_impl< ::GenericSwitch::Extension, id_mssql >::
init (id_image_type& i, const id_type& id)
{
mssql::statement_kind sk (mssql::statement_select);
{
composite_value_traits< ::GenericSwitch::ObjectId, id_mssql >::init (
i.id_value,
id,
sk);
}
}
On Fri, Jul 24, 2020 at 2:17 PM Boris Kolpackov <boris at codesynthesis.com>
wrote:
> Sten Kultakangas <ratkaisut at gmail.com> writes:
>
> > Boris, I am going to make the necessary modifications to
> > odb/relational/source.cxx and odb/relational/mssql/source.cxx but i
> wonder
> > if you have some work-in-progress branch having the relevant
> modifications
> > that I can easily backport to the stable 2.5.0 branch ?
>
> No, I haven't looked into this.
>
>
> > If you haven't yet worked on this issue, then do you have instructions
> for
> > contributors on how to make a patch that can be incorporated into the
> > official version control branch ?
>
> You can either email the patch directly or create a pull request on your
> favorite Git hosting platform (GitHub, etc).
>
> Note that for this to be merged to master we would need the fix for all
> the databases as well as a test.
>
>
> > P.S. Actually tenant_id does not belong to the primary key, but i see no
> > other way to force odb to generate UPDATE statement having condition
> WHERE
> > tenant_id=X and id=X which is necessary for data integrity as our API
> does
> > not currently perform sanity-checks on the user-supplied ID but instead
> it
> > determines the tenant the API user belongs to.
>
> One somewhat admittedly hackish way to achieve this could be to "overlay"
> two classes over the same database table. One class would have the simple
> auto id to be used for persist and the other would have the composite id
> without auto to be used for update. You might also need to tweak the
> generated database schema depending on the database system used.
>
More information about the odb-users
mailing list