Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
DBIx::Introspector::AdUser(Contributed Perl DocumDBIx::Introspector::Advent(3)

       DBIx::Introspector::Advent - Original Announcement of

       version 0.001005

       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

       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

	  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:

	  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
       <> 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.

       Arthur Axel "fREW" Schmidt <>

       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.

perl v5.32.1			  2015-01-29	 DBIx::Introspector::Advent(3)


Want to link to this manual page? Use this URL:

home | help