# PODNAME: DBIx::Introspector::Advent

# ABSTRACT: Original Announcement of ::ResultSet::DateMethods1



=encoding UTF-8

=head1 NAME

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

=head1 VERSION

version 0.001005

=head1 Content

=head1 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 C<DBD::mysql>
to connect to mysql, C<DBD::Pg> to connect to Postgres, and C<DBD::SQLite> to
connect to SQLite.

The problem comes when you use a more generic driver.  For example
C<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.

C<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
C<DBIx::Introspector> gives the user is a way to query database handles
(or DSN's) for various pieces of information.

=head2 How can I use it?

For starters, you need to define a new C<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 (C<2013-12.01>) for drivers.
In order to maintain backwards compatibility C<DBIx::Introspector>
forces the user to request a driver set.  Currently just one set exists,
which attempts to match what L<DBIx::Class> does internally at the time
of release.

Next, the C<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:

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

The above code uses a connected C<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:

   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
C<unconnected_options> define a known pagination methods for reasonably recent
versions of SQL Server.

=head2 What's next?

For C<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 C<DBIx::Introspector>.  For
example, the long dormant
has been blocking on exactly this problem.  Furthermore a
number of already existing modules could be improved by the use of
C<DBIx::Introspector>, most notably L<DBIx::Connector>, which doesn't
work for anything using ODBC, ADO, and other non-one-to-one drives.

=head1 AUTHOR

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


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.