NAME

       DBIx::Introspector::Advent - Original Announcement of ::ResultSet::DateMethods1

VERSION

       version 0.001005

Content

TEXT

       A common (but rarely acknowledged) problem when writing portable SQL is accurately
       detecting what kind of database you are connected to, and sometimes how you have
       connected.  The typical solution is to assume that your database driver has a one-to-one
       mapping to your database.  This works for many cases, for example many people only use
       "DBD::mysql" to connect to mysql, "DBD::Pg" to connect to Postgres, and "DBD::SQLite" to
       connect to SQLite.

       The problem comes when you use a more generic driver.  For example "DBD::ODBC" can connect
       to any database that supports ODBC (which includes mysql, Postgres, Oracle, and probably
       most importantly SQL Server.)  Often users assume that ODBC means SQL Server but that's
       clearly not correct.

       "DBIx::Introspector" solves this problem (as well as one other.)  It has a basic but
       mostly complete set of detection methods.  If, after it is released, there are problems
       discovered in the detection methods, the user can easily swap in new detection methods.
       The other feature that "DBIx::Introspector" gives the user is a way to query database
       handles (or DSN's) for various pieces of information.

   How can I use it?
       For starters, you need to define a new "DBIx::Introspector".  Let's pretend we are writing
       some program that needs to concatenate stuff in the database, and should support some
       major databases.  This code would probably be sufficient:

        my $d = DBIx::Introspector->new(drivers => '2013-12.01');

        # standard dialects
        $d->decorate_driver_unconnected(Pg     => concat_sql => '? || ?');
        $d->decorate_driver_unconnected(SQLite => concat_sql => '? || ?');

        # non-standard
        $d->decorate_driver_unconnected(MSSQL  => concat_sql => '? + ?');
        $d->decorate_driver_unconnected(mysql  => concat_sql => 'CONCAT( ?, ? )');

       First, note that we specify a string ("2013-12.01") for drivers.  In order to maintain
       backwards compatibility "DBIx::Introspector" forces the user to request a driver set.
       Currently just one set exists, which attempts to match what DBIx::Class does internally at
       the time of release.

       Next, the "decorate_driver_unconnected" call; "unconnected" is because these facts could
       be determined whether we were connected to the database or not.  An example of a connected
       fact might be the following:

        $d->decorate_driver_connected(
          MSSQL => full_version => sub {
             my ($ret) = $_[1]->selectcol_arrayref('SELECT @@VERSION');
             return $ret
          },
        );

       The above code uses a connected "dbh" to ask SQL Server what the versions are of the
       database, OS, patchlevel, etc.

       Note that because this is basically a bespoke prototypical object system you can easily
       add and replace drivers:

        $d->replace_driver({
          name => 'MSSQL',
          connected_determination_strategy => sub {
             my %to = (
                11 => '2012',
                10 => '2008',
                9 => '2005',
             );
             my ($ver) =
                $_[1]->selectcol_arrayref(q(SELECT SERVERPROPERTY('ProductVersion')));
             my ($major) = $ver =~ m/^(\d+)\./;
             my $to = $to{$ver} || '-OlderThanDirt', # or newer, but it's a demo
             return "MSSQL$to"
          },
        });

        $d->add_driver($_) for qw({
          name => 'MSSQL2000',
          unconnected_options => { pagination_method => 'top' },
        },{
          name => 'MSSQL2005',
          unconnected_options => { pagination_method => 'rno' },
        },{
          name => 'MSSQL2008',
          unconnected_options => { pagination_method => 'rno' },
        },{
          name => 'MSSQL2012',
          unconnected_options => { pagination_method => 'sql2012' },
        },{
          name => 'MSSQL-OlderThanDirt',
          # documentation doesn't get this old, so who knows!
        });

       This code replaces the MSSQL driver with one that has another layer of detection based on
       version, and then adds drivers for each (sensible) version.  The "unconnected_options"
       define a known pagination methods for reasonably recent versions of SQL Server.

   What's next?
       For "DBIx::Introspector", there are probably more drivers that could be defined.
       Additionally a standard set of facts would be very handy.  Caching the detection might be
       worthwhile, but I'd rather wait until someone notices a speed issue before doing that.

       On top of that, a number of doors are opened by "DBIx::Introspector".  For example, the
       long dormant DBIx::Exceptions <https://github.com/frioux/DBIx-Exceptions> has been
       blocking on exactly this problem.  Furthermore a number of already existing modules could
       be improved by the use of "DBIx::Introspector", most notably DBIx::Connector, which
       doesn't work for anything using ODBC, ADO, and other non-one-to-one drives.

AUTHOR

       Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>

COPYRIGHT AND LICENSE

       This software is copyright (c) 2015 by Arthur Axel "fREW" Schmidt.

       This is free software; you can redistribute it and/or modify it under the same terms as
       the Perl 5 programming language system itself.