DBIx::...Advent(3pm)

✨ DBIx::...Advent ✨ 💅

ok bestie so you found the Perl User Contributed Documentation page for DBIx::Introspector::Advent — it's literally the Original Announcement of ::ResultSet::DateMethods1, no cap 🎀. Sit down, grab a strawberry, we're doing lore today. 🍓

---

🌸 VERSION 🌸

version 0.001005

---

🦋 Content 🦋

✨ TEXT ✨

okay so here's the tea, and it's a problem basically everyone has but nobody ever admits out loud: when you're writing SQL that has to run everywhere, you need to actually know what kind of database you're connected to — and sometimes how you connected matters too. 😭

The usual move is to just assume your database driver maps one-to-one onto your database. And honestly? That works a lot of the time! Like, tons of people only ever use DBD::mysql to connect to mysql, DBD::Pg to connect to Postgres, and DBD::SQLite to connect to SQLite. Cute, simple, works. 💖

But then the problem hits when you reach for a more generic driver. For example, DBD::ODBC can connect to literally any database that supports ODBC — which includes mysql, Postgres, Oracle, and probably most importantly SQL Server. And people constantly assume ODBC means SQL Server, but bestie, that is clearly not correct. 💀

DBIx::Introspector solves this problem (and one other thing too, we'll get there). It ships with a basic but mostly complete set of detection methods. And if problems get discovered in those detection methods after it's released, you can easily swap in new detection methods yourself. The other feature DBIx::Introspector hands you is a way to query database handles (or DSN's) for various pieces of information. Obsessed. 👑

🧚 How can I use it? 🧚

So for starters, you gotta define a new DBIx::Introspector. Let's pretend we're 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 thing to notice: we're passing a string ("2013-12.01") for drivers. The way that works is, in order to maintain backwards compatibility, DBIx::Introspector forces you to request a driver set explicitly. Currently just one set exists, and it tries to match what DBIx::Class does internally at the time of release. iykyk. ✨

Next up: that decorate_driver_unconnected call. The unconnected part is there because these facts could be figured out whether we were connected to the database or not. An example of a connected fact might be this:

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

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

And note — 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. slay. 💅

🌈 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. (very "we'll optimize when it's a problem" of them, respect. 🙈)

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.

---

ok and that's the whole page bestie, you ate it 💖

perl v5.34.0 · 2022-06-13 · DBIx::...Advent(3pm) — mwah, go write some portable SQL, you've got this 🦋✨🌸