1DBIx::Introspector::AdvUesnetr(3C)ontributed Perl DocumeDnBtIaxt:i:oInntrospector::Advent(3)
2
3
4

NAME

6       DBIx::Introspector::Advent - Original Announcement of
7       ::ResultSet::DateMethods1
8

VERSION

10       version 0.001005
11

Content

TEXT

14       A common (but rarely acknowledged) problem when writing portable SQL is
15       accurately detecting what kind of database you are connected to, and
16       sometimes how you have connected.  The typical solution is to assume
17       that your database driver has a one-to-one mapping to your database.
18       This works for many cases, for example many people only use
19       "DBD::mysql" to connect to mysql, "DBD::Pg" to connect to Postgres, and
20       "DBD::SQLite" to connect to SQLite.
21
22       The problem comes when you use a more generic driver.  For example
23       "DBD::ODBC" can connect to any database that supports ODBC (which
24       includes mysql, Postgres, Oracle, and probably most importantly SQL
25       Server.)  Often users assume that ODBC means SQL Server but that's
26       clearly not correct.
27
28       "DBIx::Introspector" solves this problem (as well as one other.)  It
29       has a basic but mostly complete set of detection methods.  If, after it
30       is released, there are problems discovered in the detection methods,
31       the user can easily swap in new detection methods.  The other feature
32       that "DBIx::Introspector" gives the user is a way to query database
33       handles (or DSN's) for various pieces of information.
34
35   How can I use it?
36       For starters, you need to define a new "DBIx::Introspector".  Let's
37       pretend we are writing some program that needs to concatenate stuff in
38       the database, and should support some major databases.  This code would
39       probably be sufficient:
40
41        my $d = DBIx::Introspector->new(drivers => '2013-12.01');
42
43        # standard dialects
44        $d->decorate_driver_unconnected(Pg     => concat_sql => '? || ?');
45        $d->decorate_driver_unconnected(SQLite => concat_sql => '? || ?');
46
47        # non-standard
48        $d->decorate_driver_unconnected(MSSQL  => concat_sql => '? + ?');
49        $d->decorate_driver_unconnected(mysql  => concat_sql => 'CONCAT( ?, ? )');
50
51       First, note that we specify a string ("2013-12.01") for drivers.  In
52       order to maintain backwards compatibility "DBIx::Introspector" forces
53       the user to request a driver set.  Currently just one set exists, which
54       attempts to match what DBIx::Class does internally at the time of
55       release.
56
57       Next, the "decorate_driver_unconnected" call; "unconnected" is because
58       these facts could be determined whether we were connected to the
59       database or not.  An example of a connected fact might be the
60       following:
61
62        $d->decorate_driver_connected(
63          MSSQL => full_version => sub {
64             my ($ret) = $_[1]->selectcol_arrayref('SELECT @@VERSION');
65             return $ret
66          },
67        );
68
69       The above code uses a connected "dbh" to ask SQL Server what the
70       versions are of the database, OS, patchlevel, etc.
71
72       Note that because this is basically a bespoke prototypical object
73       system you can easily add and replace drivers:
74
75        $d->replace_driver({
76          name => 'MSSQL',
77          connected_determination_strategy => sub {
78             my %to = (
79                11 => '2012',
80                10 => '2008',
81                9 => '2005',
82             );
83             my ($ver) =
84                $_[1]->selectcol_arrayref(q(SELECT SERVERPROPERTY('ProductVersion')));
85             my ($major) = $ver =~ m/^(\d+)\./;
86             my $to = $to{$ver} || '-OlderThanDirt', # or newer, but it's a demo
87             return "MSSQL$to"
88          },
89        });
90
91        $d->add_driver($_) for qw({
92          name => 'MSSQL2000',
93          unconnected_options => { pagination_method => 'top' },
94        },{
95          name => 'MSSQL2005',
96          unconnected_options => { pagination_method => 'rno' },
97        },{
98          name => 'MSSQL2008',
99          unconnected_options => { pagination_method => 'rno' },
100        },{
101          name => 'MSSQL2012',
102          unconnected_options => { pagination_method => 'sql2012' },
103        },{
104          name => 'MSSQL-OlderThanDirt',
105          # documentation doesn't get this old, so who knows!
106        });
107
108       This code replaces the MSSQL driver with one that has another layer of
109       detection based on version, and then adds drivers for each (sensible)
110       version.  The "unconnected_options" define a known pagination methods
111       for reasonably recent versions of SQL Server.
112
113   What's next?
114       For "DBIx::Introspector", there are probably more drivers that could be
115       defined.  Additionally a standard set of facts would be very handy.
116       Caching the detection might be worthwhile, but I'd rather wait until
117       someone notices a speed issue before doing that.
118
119       On top of that, a number of doors are opened by "DBIx::Introspector".
120       For example, the long dormant DBIx::Exceptions
121       <https://github.com/frioux/DBIx-Exceptions> has been blocking on
122       exactly this problem.  Furthermore a number of already existing modules
123       could be improved by the use of "DBIx::Introspector", most notably
124       DBIx::Connector, which doesn't work for anything using ODBC, ADO, and
125       other non-one-to-one drives.
126

AUTHOR

128       Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>
129
131       This software is copyright (c) 2015 by Arthur Axel "fREW" Schmidt.
132
133       This is free software; you can redistribute it and/or modify it under
134       the same terms as the Perl 5 programming language system itself.
135
136
137
138perl v5.36.0                      2023-01-20     DBIx::Introspector::Advent(3)
Impressum