1DBIx::Introspector::AdvUesnetr(3C)ontributed Perl DocumeDnBtIaxt:i:oInntrospector::Advent(3)
2
6 DBIx::Introspector::Advent - Original Announcement of
7 ::ResultSet::DateMethods1
8
10 version 0.001005
11
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 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 "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 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 my $d = DBIx::Introspector->new(drivers => '2013-12.01');
42 # standard dialects
44 $d->decorate_driver_unconnected(Pg => concat_sql => '? || ?');
45 $d->decorate_driver_unconnected(SQLite => concat_sql => '? || ?');
46 # non-standard
48 $d->decorate_driver_unconnected(MSSQL => concat_sql => '? + ?');
49 $d->decorate_driver_unconnected(mysql => concat_sql => 'CONCAT( ?, ? )');
50 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 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 $d->decorate_driver_connected(
63 MSSQL => full_version => sub {
64 my ($ret) = $_[1]->selectcol_arrayref('SELECT @@VERSION');
65 return $ret
66 },
67 );
68 The above code uses a connected "dbh" to ask SQL Server what the
70 versions are of the database, OS, patchlevel, etc.
71 Note that because this is basically a bespoke prototypical object
73 system you can easily add and replace drivers:
74 $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 $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 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 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 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
128 Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>
129
131 This software is copyright (c) 2015 by Arthur Axel "fREW" Schmidt.
132 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.
135perl v5.36.0 2023-01-20 DBIx::Introspector::Advent(3)