package Common::RSDB::DirectIO;

use strict;
use warnings;

# With very large result sets, we have instances where we'd prefer (or perhaps even need) to pull results from
# the server incrementally rather than fetching and storing everything client side, which is the default when
# using DBD::mysql (see mysql_store_result). mysql_use_result is a nifty way around this, but there are a few
# problems, or at least some things to consider. First is that until the entire result set has been processed,
# the table being queried is locked for updates. Even so, that's a relatively fair trade-off. Next and really
# the biggest issue is that no other database operations can be performed until the entire result set is
# processed (or discarded). This literally means you can't go off and read/write records to any table until it's
# finished. In a way, that's a good thing because you don't want to be doing spendy operations when you should
# be reading records as fast as you can and doing something modest with them. Fortunately, those ancilliary
# DB operations will just fail--the MySQL docs *almost* make it sound like the next "read" of the database will
# just get the records still remaining. How bad would that be? All of this is fairly well documented and accurate
# in MySQL docs on mysql_store_result.
#
# The final final issue is that these "streaming" reads can just break right in the middle. Near as I can tell,
# processing stored result sets is largely an atomic operation, but mysql_use_result sets can fail during
# fetchrows with a lost connection. Doesn't seem so bad until you realize that it's fairly easy to lose a
# connection. The server side will only wait until net_write_timeout seconds have elapsed for the client to
# pick up the currently pending packet of data. The default for this is 60 seconds, which in a way is an eternity
# as the client should chunk through that really fast. And it usually does. Except maybe when an app server
# is getting hammered. Near as I can tell, this has happened maybe 20 times in the last 2 years. Not bad
# considering the thousands of processes that have run in that time. Still. So we have to consider that. Even
# worse though is that these things fail during fetchrow and we don't have any error handling for that. And
# there isn't really any way to do anything to recover mid result set anyway--far different than connects
# and executes, those can be retried. Unfortunately, default behavior for DBD::mysql is to just log the error
# and carry on. That, we can't have, so we'll need to instruct DBD::mysql to murder the process. Sure, in simple
# cases, we could just try the whole query over again, but we're usually doing something else and all that would
# need to be (undone and) done again as well. And it's intermittent, so yeah, let's just die, which appears to be
# what we should generally do in other situations as well, but beyond the scope of what we're trying to do here.
#
# The final final final bad thing we've done in the past is turned on mysql_use_result processing and then never
# turned it off for the remaining life of the process. It wasn't intentional, but that's what happened. It mostly
# worked fine. Small result sets really behave almost identically and big ones probably could have used it anyway.
# But that should be by design, not accident. Oh, and because of the use all the results or new requests will fail
# thing, it usually hit this, retried the new request and because that's a new connection, processing reverts to
# the default mysql_store_result mode.
#
# So that leaves us with this dumb little class when we want to do mysql_use_result processing. Have Common::RSDB
# instantiate one of these little guys, it'll save the current state of things, set up processing the best way we
# know how, then you can process your giant dataset and when this goes out of scope, it'll put everything back.
#
# Like so:
#
#     my $directIO = $dbo->DirectIO();
#     my $sth = $dbo->DoCmd('select * from really_big_table');
#     while (my $row = $sth->fetchrow_arrayref())
#     {
#         do something fast with $row
#     }
#     then just let $directIO leave scope or do it sooner with $directIO = undef if you need to
#
# In theory, these could be stacked, but due to the fact that you can't do other database stuff until you finish
# with the first one, not so much. That said, if the query finished and then you loaded another class that did
# its own large result processing, it would work. I guess that's why I didn't make it a singleton with a death
# wish.

sub new {
    my $class = shift;
    my $dbo   = shift;
    my $self  = {};
    bless $self, $class;

    # we'll need this when we unwind everything
    $self->{dbo} = $dbo;

    # Save current settings
    $self->{PrintError}       = $dbo->DBH()->{PrintError};
    $self->{RaiseError}       = $dbo->DBH()->{RaiseError};
    $self->{mysql_use_result} = $dbo->DBH()->{mysql_use_result};

    # Only way we can get current setting
    my $result = $dbo->DoCmd('SHOW SESSION VARIABLES LIKE "net_write_timeout"')->fetchrow_arrayref();
    $self->{net_write_timeout} = $result->[1] || 60;    # Zero/undef would be bad whether it was really short or forever.
                                                        # 60 is the MySQL default, so let's use that if we get nutty.

    # There's a nifty thing in DBD::mysql where you can set net_write_timeout when you connect, see 'mysql_write_timeout'.
    # That is, it'd be nifty if it worked, but it doesn't, at least as of version 4.032. I'd even upgraded from 4.007, which
    # didn't support the feature yet. Boo, and I'm guessing it's not going to work anytime soon. It's possible that it's a client
    # setting rather than server, but I don't care about that and documentation is sparse at best.
    #
    # So, we'll do the old fashioned way and just issue the appropriate command to the server. We're setting this to 5 minutes
    # to improve our chances that the result set can be fully processed. Rather block things just a minute longer than to chuck
    # the whole process.
    $dbo->DBH()->do('SET SESSION net_write_timeout = 300');

    # RaiseError prints errors so let's not print them twice
    $dbo->DBH()->{PrintError} = 0;

    # We might start doing this other places, but we have to do it here (lest we end up with partial result sets, which we have. Gulp)
    $dbo->DBH()->{RaiseError} = 1;

    # Do it!
    $dbo->DBH()->{mysql_use_result} = 1;

    return $self;
}

sub DESTROY {
    my $self = shift;

    my $dbo = $self->{dbo};

    $dbo->DBH()->{mysql_use_result} = $self->{mysql_use_result};
    $dbo->DBH()->{RaiseError}       = $self->{RaiseError};
    $dbo->DBH()->{PrintError}       = $self->{PrintError};

    # We don't want to do operations on a database that's no longer there. This should only happen when the destructor is
    # being invoked during die unwinding, but doesn't seem like superfluous failure messages will help anybody
    my $stat = $dbo->DBH()->{mysql_stat};    # After an error, we seem to need to access this to get mysql_errno set correctly
    if ( !$dbo->DBH()->{mysql_errno} ) {
        $dbo->DBH()->do( 'SET SESSION net_write_timeout = ' . $self->{net_write_timeout} );
    }

    # Make like Elsa and Let It Go
    $self->{dbo} = undef;
}

1;