Bulk operations with user-defined types (4.0.0)

[irodushka]

Hi

I'm looking at bulk operations and see that 4.0.0 documentation says:

Bulk operations are supported also for std::vectors of the user-provided types that have appropriate conversion routines defines.

Does it mean that it's possible to insert to a vector of structs now, like std::vector<Person>? Or this option is still unavailable in SOCI?

The issue is that I get a compilation error(s) while trying to do such a bulk insert -

In file included from /usr/local/include/soci/column-info.h:12,
                 from /usr/local/include/soci/soci.h:16,
                 from test6.cpp:1:
/usr/local/include/soci/type-conversion.h: In instantiation of ‘soci::details::use_type_ptr soci::details::do_use(T&, const string&, soci::details::user_type_tag) [with T = std::vector<Person>; soci::details::use_type_ptr = soci::details::type_ptr<soci::details::use_type_base>; std::string = std::basic_string<char>]’:
/usr/local/include/soci/bind-values.h:116:22:   required from ‘void soci::details::use_type_vector::exchange_(const soci::details::use_container<T, void>&, ...) [with T = std::vector<Person>]’
/usr/local/include/soci/bind-values.h:47:9:   required from ‘void soci::details::use_type_vector::exchange(const soci::details::use_container<T, Indicator>&) [with T = std::vector<Person>; Indicator = void]’
/usr/local/include/soci/ref-counted-prepare-info.h:42:7:   required from ‘void soci::details::ref_counted_prepare_info::exchange(const soci::details::use_container<T, Indicator>&) [with T = std::vector<Person>; Indicator = void]’
/usr/local/include/soci/prepare-temp-type.h:50:9:   required from ‘soci::details::prepare_temp_type& soci::details::prepare_temp_type::operator,(const soci::details::use_container<T, Indicator>&) [with T = std::vector<Person>; Indicator = void]’
test6.cpp:68:153:   required from here
/usr/local/include/soci/type-conversion.h:444:12: error: no matching function for call to ‘soci::details::type_ptr<soci::details::use_type_base>::type_ptr(soci::details::conversion_use_type<std::vector<Person> >*)’
     return use_type_ptr(new conversion_use_type<T>(t, name));
            ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

See my example code & full g++ output in attach:

err.log test6.zip

Regards Yuri.

[mloskot]

AFAIR this should be supported, but user-defined types need conversion routines. You will find examples in https://github.com/SOCI/soci/blob/master/tests/common-tests.h

BTW, looks like that specific case for vector<Person> is not covered in the tests. Contributions welcome.

[irodushka]

If you loоk into my sample code you see that I have a conversion implemented. Moreover - if I write use(pv[0]) instead of use(pv) it compiles ok. Maybe I need to create another conversion for vector itself?.. Or I should implement set/get methods for Person class?.. You know, it's hard to guess and a test sample would be very useful here:)

[mloskot]

Sorry, I currently have no time to dig the problem myself. That's why I suggested playing with simple test, trying bulk ops with the PhonebookEntry classes in tests. Simply, try DIY

[irodushka]

Tried to write additional tests. It does not compile any way. If use(bulk_vector) changed to use(bulk_vector[0]) - compiles ok. What am I doing wrong?:)

(Modded common_tests.h in attach)

TEST_CASE_METHOD(common_tests, "Bulk insert with ORM", "[core][orm]")
{
    soci::session sql(backEndFactory_, connectString_);

    sql.uppercase_column_names(true);
    auto_table_creator tableCreator(tc_.table_creator_3(sql));
    
    std::vector<PhonebookEntry> bulk_vector;
    for( int i = 0; i < 100; i++ ) {
        PhonebookEntry temp;
        std::ostringstream oss;
        oss << "bulk" << i;
        temp.name = temp.phone = oss.str();

        bulk_vector.push_back( temp );
    }

    statement insertStatement = (sql.prepare << "insert into soci_test values (:NAME, :PHONE)", use(bulk_vector));
    insertStatement.execute(true);

    int count = 0;

    sql << "select count(*) from soci_test where NAME like ('bulk%')", into(count);

    CHECK(count == 100);
}

SECTION("Bulk insert from vector of boost::fusion::vector")
{
    typedef boost::fusion::vector<double, int, std::string> bfv_t;
    typedef std::vector<bfv_t> v_t;

    v_t bulk_vector;
    for( int i = 0; i < 100; i++ ) {
        bulk_vector.push_back( bfv_t( double(i)/2, i, "Joe Hacker" ) );
    }

    sql << "insert into soci_test(num_float, num_int, name) values(:d, :i, :s)", use(bulk_vector);

    sql << "delete from soci_test";
}

common-tests.zip

[xnum]

I encountered same problem. It does have a bug, but the root cause is this library doesn't support in my opinion.

The bug I found is at type-conversion.h. Missing std:: at the third parameter. Compiler can't figure out ::size_t and instantiate correctly.

After fixed that, this header(values-exchange.h) refused the bulk operation with ORM at compile time. I have no idea why and simply comment it. Finally it throws exception in backend.

In my case, MySQL backend doesn't implement define_by_pos_bulk which postgresql did. The only way is implementing this function and the compile time protection.

Even though you resolves these problems, you still have to resize this vector to specify max size which is not convenient to me, too big is a waste or you have to fetch multiple times if it is too small. I think fetch and push_back is a better way. e.g. https://github.com/SOCI/soci/blob/master/tests/common-tests.h#L767

[jimmyhollywood]

@xnum I use the postgres backend and also can't perform bulk operations. I have implemented conversion routines for my classes but the problem is still there. Did you check that on postgres?

[vadz]

Could you please retry with the latest master? There were several changes possibly affecting this, so it might work now.

If it still doesn't, a patch/PR adding a failing unit test would be very welcome. TIA!

[e1y4s]

@vadz This doesn't seem to work in the latest master either.

MRE :

class TestClass
{

public:

    int value;

    TestClass() : value(0) {}
    TestClass(int value) : value(value) {}
};

namespace soci
{
    template<>
    struct type_conversion<TestClass>
    {
        typedef values base_type;

        static void from_base(const base_type & bt, indicator /*ind*/, TestClass & tc)
	{
            tc.value = bt.get<int>("value");
        }

        static void to_base(const TestClass & tc, base_type & bt, indicator & ind)
	{
            bt.set("value", tc.value);
            ind = i_ok;
        }
    };
}

int main()
{
    // [...]

    std::vector<TestClass> tests;

    for (int i = 0; i < 10; ++i)
    {
        tests.emplace_back(i);
    }

    // This line does not compile
    soci::statement statement = (session.prepare << "INSERT INTO TestClass (value) VALUES (:value)", soci::use(tests));

    return 0;
}

[AndyStieber]

@vadz @Ely4s

This issue is still causing problems, I've created the failing unit test here: https://github.com/SOCI/soci/pull/1065

[albertcp]

Same problem here... Did someone find a way to insert the std::vector<Person> in the database?

[vadz]

You can insert a vector of simple types just fine, of course, and also a vector of a type convertible to some simple type. Inserting a vector using ORM-like API, with the object mapping to multiple columns doesn't work AFAICS and I'm not sure how to make it work.

[albertcp]

Well.. in my case Im trying to insert and select an array of custom object from the database. For the select operation, i'm using an rowset, without any problem. But for the insert operation.. is not the same. I have a type_conversion operation for the class Person and a type_conversion operation for the std::vector<Person>. When I insert 1 Person it works, but when I try to insert an std::vector<Person> it does compile. It is not possible to link a std::vector<CustomObject> to a type_conversion operation?

[vadz]

Right now I don't think it's possible, but it probably could be implemented.