Support for bulk operations

[ja11sop]

Previously a lot of our code made use of SOCI and we enjoyed being able to use bulk operations. With new work we're looking at using quince to cut down on the boiler-plate schema management and mapping code. I know you'd mentioned on the boost mailing list that bulk operations are on your to-do list so I thought I'd create an issue here so myself and others interested in this can track your progress.

[mshepanski]

Hi, and thanks for your interest in quince.

Yes, I'd like to do something for bulk operations. Unfortunately I am going to be interrupted some time soon (moving house), so I'd like to get started in the direction that will be most useful to you in the short term. So could you please tell me some more specifics, i.e. which dbms, and, if possible, examples of the sql statements that you use with bulk operations. (Of course you can change the column names etc. to protect the details of your application.)

Cheers, --- Michael

[ja11sop]

Hi, thanks for the speedy response! First up - this is not urgent for us, I primarily wanted to put it on the radar. We have not code that needs this right now, it was just a consideration previously with older code using SOCI. I can tell you that we do use postgresql. As things progress (or we dig out our old code with these kinds of operations) I'll be sure to share it with you.

As a side note I added quince as an available dependency to cuppa on github here. Only postgresql backend for now but I'll add sqlite also.

[mshepanski]

I've been reading about SOCI's bulk operations, and I have a couple of questions:

1. When it comes to bulk data retrieval, such as getting a SELECT statement's many rows of output into a std::vector, I think quince already has this covered. (See the example involving all_of_them, and note that, with quince_postgresql, the underlying size of retrieved batches can be configured using fetch_size().) So is there anything else that you would like to see quince doing, in the area of bulk retrieval?
2. When it comes to bulk data insertion, I'm a bit confused as to how this works with SOCI+Postgresql. When I look at the Postgresql documentation, it seems that bulk insertion of n rows requires an INSERT statement with n separately parenthesized lists following the word VALUES. I had assumed that quince would need to generate that syntax when it eventually provides bulk insertions. However the example in the SOCI documentation is:

    statement st = (sql.prepare <<
               "insert into numbers(value) values(:val)",
               use(valsIn));

and I'm wondering how this works. Does SOCI parse the SQL and edit it in this case?

[ja11sop]

Sorry for the delay in getting back to you. For bulk retrieval you are correct. I still need to go back and investigate how we used SOCI for the insertion case.