List:MySQL++« Previous MessageNext Message »
From:Rick Gutleber Date:October 15 2008 7:47pm
Subject:Re: Using MySQL++ with lots and lots of data
View as plain text  
Warren Young wrote:
> Rick Gutleber wrote:
>> However, that leads me to another question.  I was all ready to start 
>> knocking this thing out, but I realized each of the insert( ), 
>> replace( ), etc calls in class Query puts a single query into the 
>> stream which gets executed when you call execute( ).  But now we are 
>> creating a situation where one call to insert( ) could result in 
>> multiple queries.  We are doing so, among other things, to avoid 
>> exceeding the maximum packet size.  If I call execute( ) with, which 
>> in turn calls mysql_real_query( ) which requires multi-statement 
>> execution to be turned on to process multiple statements at once.  I 
>> would guess that maximum packet size would apply to the compound 
>> statement not each individual one.
>
> First, there's no reason for multi-statements.  An insert with 
> multiple values() blocks is not multi-statements.  It's just a single 
> statement inserting multiple rows.  See Query::insert(Iter, Iter) for 
> the syntax.  Note the lack of semicolons in the SQL.
I understand that.  However if the query string exceeds the policy's 
limit, then the bulk INSERT would have to done in chunks.  That's what I 
mean by multiple statements.

>
> Second, this flavor of insert() will insert immediately, without 
> requiring an execute() call.  In effect, it calls execute() 
> internally, as many times as necessary to insert the entire 
> container.  Perhaps it should be called insertfrom() to avoid confusion?
I think that's a good idea.  I didn't consider that because nothing else 
does execute( ) internally, but that is the most obvious thing to do.  
It should be called something a little different because it acts 
different than every other flavor of insert( ).  I'll use that name 
unless I can think of something better.
> It occurs to me that you should probably wrap the whole thing in a 
> transaction.  If you're halfway through the container and an INSERT 
> fails, you shouldn't leave the DB with half a container's worth of 
> data inserted.  The BEGIN statement should be wrapped in a 
> NoExceptions block so it doesn't break insert[from]() if the DB engine 
> isn't capable of transactions.  If the BEGIN succeeds, set a flag that 
> tells you to call ROLLBACK or COMMIT -- no sense trying to call them 
> if the BEGIN failed.  If an INSERT fails, roll back the transaction 
> and throw a BadQuery, just as execute() would for a single-row 
> insert.  Remember to save the error you get from the INSERT before 
> calling ROLLBACK, as that will reset the last-error code.
Makes sense to me.  Thanks for the detail.
>> Query::insert( Iter & it, Policy policy ) {
>
> I think you should pass the policy object as a pointer, so you can 
> pass 0 to get default behavior, and so you can pass a pointer to a 
> static instance where that is sensible.  Some policy objects will 
> probably carry state that gets changed by the insertion process, and 
> so shouldn't be static.  Others will never change, so there's no point 
> creating identical copies again and again.
That's reasonable.  I'm not a big fan of pointers when references will 
do, and would probably have provided a stock do-nothing Policy for those 
purposes, but I'm happy to go with the pointer.  Consistency in 
libraries is very important to me, so I will place emphasis on doing 
things the way they are done elsewhere in MySQL++.

Thanks for all your feedback and help, I think I am now equipped to do 
some coding!  Woot!

Rick

Thread
Using MySQL++ with lots and lots of dataRick Gutleber13 Oct
  • Re: Using MySQL++ with lots and lots of dataAndrew Sayers14 Oct
  • Re: Using MySQL++ with lots and lots of dataWarren Young14 Oct
    • Re: Using MySQL++ with lots and lots of dataRick Gutleber14 Oct
      • Re: Using MySQL++ with lots and lots of dataWarren Young15 Oct
        • Re: Using MySQL++ with lots and lots of dataRick Gutleber15 Oct
          • Re: Using MySQL++ with lots and lots of dataWarren Young15 Oct
            • Re: Using MySQL++ with lots and lots of dataRick Gutleber15 Oct
              • Re: Using MySQL++ with lots and lots of dataWarren Young15 Oct