NAME

Mojar::Mysql::Connector - MySQL connector (dbh producer) with added convenience

SYNOPSIS

In an application making only one type of connection.

  use Mojar::Mysql::Connector (
    cnfdir => '/var/local/auth/myapp',
    cnf => 'rw_localhost',
    schema => 'Users'
  );
  ...
  my $dbh = Mojar::Mysql::Connector->connect;

In an application making multiple types of connection.

  use Mojar::Mysql::Connector (
    cnfdir => '/var/local/auth/myapp'
  );

  my $read_connector = Mojar::Mysql::Connector->new(
    cnf => 'ro_remotehost',
    schema => 'Orders'
  );
  my $write_connector = Mojar::Mysql::Connector->new(
    cnf => 'rw_localhost',
    schema => 'Reports'
  );
  ...
  my $read_dbh = $read_connector->connect(mysql_auto_reconnect => 1);
  my $write_dbh = $write_connector->connect;

Employing a helper.

  use Mojar::Mysql::Connector (
    cnfdir => '/var/local/auth/myapp',
    cnf => 'rw_localhost',
    schema => 'Users',
    -dbh => 1
  );
  sub do_da_db_doodah {
    my $self = shift;
    my $dbh = $self->dbh;
    ...
  }

From the commandline.

  perl -MMojar::Mysql::Connector=cnf,ro_localhost,schema,Users,-dbh,1
    -E'say join qq{\n}, @{main->dbh->real_tables}'

DESCRIPTION

MySQL-specific extension (subclass) to DBI in order to improve convenience, security, and error handling. Supports easy use of credential (cnf) files, akin to

  mysql --defaults-file=credentials.cnf

It aims to reduce boilerplate, verbosity, mistakes, and parameter overload, but above all it tries to make it quick and easy to Do The Right Thing.

As the name implies, the class provides connector objects -- containers for storing and updating your connection parameters. When you call connect, the connector returns a handle created using its retained parameters plus any call-time parameters passed. You don't however have to use connectors; for simple usage it can be easier to use connect directly from the class.

You can use a DSN tuple if you want to, but it's more readable and less error-prone to specify your parameters either as a hash or by setting individual attributes. Each call to connect will then construct the DSN for you.

You can optionally import a helper method, called dbh (or whatever name you choose) so that you can focus even less on the connector/connection and more on your code. The helper will cache your database handle and create a new one automatically if the old one is destroyed or goes stale.

The fourth layer of convenience is provided by the added database handle methods. Changing session variables (session_var) is easy, listing only genuine tables (real_tables) is easy, and there's more.

CLASS METHODS

`new`

  Mojar::Mysql::Connector->new(label => 'cache', cnf => 'myuser_localhost');

Constructor for a connector, based on class defaults. Takes a (possibly empty) list of parameters. Returns a connector (Mojar::Mysql::Connector object) the defaults of which are those of the class overlaid with those passed to the constructor.

`connect`

 $dbh1 = Mojar::Mysql::Connector->connect(
   'DBI:mysql:test;host=localhost', 'admin', 's3cr3t', {}
 );
 $dbh2 = Mojar::Mysql::Connector->connect(
   schema => 'test',
   host => 'localhost',
   user => 'admin',
   password => 's3cr3t'
 );
 $dbh3 = Mojar::Mysql::Connector->connect;

Constructor for a connection (db handle). If the first element passed has prefix DBI: then it is a DSN string (the traditional route) and so is passed straight to DBI::connect ("DBI Class Methods" in DBI). Otherwise a DSN is first constructed. (The DSN tuple does not persist and is constructed fresh on each call to connect.)

In the examples above, $dbh1 and $dbh2 are not equivalent because the second connector would also incorporate module defaults and use-time parameters, in addition to the passed parameters. So, for instance, mysql_enable_utf8 might be included in the second connector.

`dsn`

  @dbi_args = Mojar::Mysql::Connector->dsn(
    cnf => 'myuser_localhost', schema => 'test'
  );

A convenience method used internally by connect. Takes a (possibly empty) parameter hash. Returns a four-element array to pass to DBI::connect, constructed from the default values of the constructing class overlaid with any additional parameters passed. The main reason for using this method is when you want to use DBI (or another DSN-consumer) directly but want to avoid the inconvenience of assembling sensible parameters yourself.

  use DBI;
  use Mojar::Mysql::Connector (
    cnfdir => '/srv/myapp/cfg',
    cnf => 'myuser_localhost'
  );
  my $dbh = DBI->connect(
    Mojar::Mysql::Connector->dsn(schema => 'foo', AutoCommit => 0)
  );

`dsn_to_dump`

  warn(Mojar::Mysql::Connector->dsn_to_dump(@dsn));

A convenience method used internally to chop up the four-element array (particularly the fourth element, the hash ref) into something more readable, for error reporting and debugging. Tries to occlude any password within.

`Defaults`

  say Mojar::Util::dumper(Mojar::Mysql::Connector->Defaults);

Provides access to the class defaults in order to help debugging.

OBJECT METHODS

`new`

  $connector->new(label => 'transaction', AutoCommit => 0);

Constructor for a connector based on an existing connector. Takes a (possibly empty) parameter hash. Returns a connector (Mojar::Mysql::Connector object) the defaults of which are those of the given connector overlaid with any arguments passed in.

`connect`

  $dbh = $connector->connect(
    'DBI:mysql:test;host=localhost', 'admin', 's3cr3t', {});
  $dbh = $connector->connect(AutoCommit => 0);
  $dbh = $connector->connect;

Constructor for a connection (db handle). If the first element passed has prefix DBI: then it is a DSN string (the traditional route) and so is passed straight to DBI::connect ("DBI Class Methods" in DBI) without consideration of the connector's existing parameters. Otherwise a DSN is first constructed. (The DSN tuple does not persist and is constructed fresh on each call to connect.)

Attributes

All connector parameters are implemented as attributes with exactly the same spelling. So for example you can

  $connector->RaiseError(undef);  # disable RaiseError
  $connector->mysql_enable_utf8(0);  # disable mysql_enable_utf8

The attributes, with their coded defaults, are

  RaiseError => 1
  PrintError => 0
  PrintWarn => 0
  AutoCommit => 1
  TraceLevel => 0
  mysql_auto_reconnect => 0
  mysql_enable_utf8 => 1

  label
  cnfdir => '.'
  cnf
  cnfgroup

  driver => 'mysql'
  host
  port
  schema
  user
  password

In addition, any DBD::mysql attributes (beginning "mysql_") are passed through to the driver.

  $dbh = $connector->connect(mysql_skip_secure_auth => 1);

DATABASE HANDLE METHODS

`selectall_arrayref_hashrefs`

  $_->{Command} ne 'Sleep' and say $_->{User}
    for $dbh->selectall_arrayref_hashrefs(q{SHOW FULL PROCESSLIST});

  printf '%s can select: %s', $_->{User}, $_->{Select_priv}
    for $dbh->selectall_arrayref_hashrefs(q{SELECT * FROM mysql.user});

Returns an arrayref of hashrefs, each hashref being a record of the resultset. The keys of the hashref are the column/field names of the record. This is simply minimal sugar on the selectall_arrayref method provided by DBI; it saves the little boilerplate of "Slice => {}". If you want to pass bound values then you need undef as the second argument.

  printf '%s can select: %s', $_->{User}, $_->{Select_priv}
    for $dbh->selectall_arrayref_hashrefs(
      q{SELECT * FROM mysql.user WHERE User != ?}, undef, 'root'
    );

`mysqld_version`

  if ($dbh->mysqld_version =~ /^5.0/) {...}

Returns the version of the db server connected to; the version part of

  mysqld --version

`thread_id`

  $tmp_table_name = q{ConcurrencySafe_}. $dbh->thread_id;

Utility method to get the connection's thread identifier (unique on that db server at that point in time).

`current_schema`

  $schema_name = $dbh->current_schema;

The same string as given by

  SELECT DATABASE();

`session_var`

  my ($old) = $dbh->session_var(sql_mode => 'ANSI_QUOTES');
  ...
  $dbh->session_var(sql_mode => $old);

Getter/setter for session variables. To get a value, simply pass the variable's name.

  $value = $dbh->session_var('date_format');

In list context returns the old value and the new value; in scalar context returns the handle to facilitate chaining.

  $dbh->session_var(var1 => ...)
      ->session_var(var2 => ...);

`disable_quotes`

  my @ddl = $dbh->disable_quotes->selectrow_array(q{SHOW CREATE ...});

Disable optional quotes around identifiers. Currently only affects output of SHOW CREATE TABLE. If you have unsafe identifiers (eg spaces or keywords) then those will still be quoted. Lasts the lifetime of the connection.

`enable_quotes`

The inverse of disable_quotes.

`disable_fk_checks`

  $dbh->disable_fk_checks->do(q{DROP TABLE ...});

Disable foreign key checks. Lasts the lifetime of the connection.

`enable_fk_checks`

The inverse of disable_fk_checks.

`schemata`

  for my $schema (@{$dbh->schemata}) {...}

Returns an arrayref of schema names, similar to

  SHOW DATABASES

but does not get fooled by lost+found.

`tables_and_views`

  foreach my $table ($dbh->tables_and_views) {...}

Returns a hashref of table and view names, similar to

  SHOW TABLES

`real_tables`

  for my $table (@{$dbh->real_tables}) {...}

Returns a arrayref of real table names, similar to

  SHOW TABLES

but excluding views.

`views`

  for my $view (@{$dbh->views}) {...}

Returns a arrayref of view names, similar to

  SHOW TABLES

but excluding real tables.

CHARACTER ENCODINGS

To read/store characters encoded as non-ASCII, non-UTF8, you must disable handling of UTF-8.

  $connector = Mojar::Mysql::Connector->new(mysql_enable_utf8 => 0);

This is essential, for example, when fetching high-latin (eg non-ASCII 8859-1) characters.

DEBUGGING

You can enable DBI trace logging at use-time:

  use Mojar::Mysql::Connector (TraceLevel => '3|CON');

and since you have access to all of DBI, you can set tracing using the method

  $dbh->trace('3|CON');
  ...
  $dbh->trace(0);

or by using the attribute

  {
    local $dbh->{TraceLevel} = '3|CON';
    ...
  }

To set tracing for all handles, use the class method instead. See "TRACING" in DBI.

SUPPORT

Homepage

http://niczero.github.com/mojar-mysql

Wiki

http://github.com/niczero/mojar/wiki

RATIONALE

This class was first used in production in 2002. Before then, connecting to databases was ugly and annoying. Setting RaiseError upon every connect was clumsy and irritating. In development teams it was tricky checking that all code was using sensible parameters and awkward ensuring use of risky parameters (eg disable_fk_checks) was kept local. As use of this class spread, it had to be useful in persistent high performance applications as well as many small scripts and the occasional commandline. More recently I discovered the Joy of Mojolicious and employed Mojo::Base to remove unwanted complexity and eliminate a long-standing bug. The ensuing fun motivated an extensive rewrite, fixing broken documentation, improved the tests (thank you travis), and we have, finally, its public release. As noted below there are now quite a few smart alternatives out there but I'm still surprised how little support there is for keeping passwords out of your codebase and helping you manage multiple connections.

COPYRIGHT AND LICENCE

This program is free software, you can redistribute it and/or modify it under the terms of the Artistic License version 2.0.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

CLASS METHODS

new

connect

dsn

dsn_to_dump

Defaults

OBJECT METHODS

new

connect

Attributes

DATABASE HANDLE METHODS

selectall_arrayref_hashrefs

mysqld_version

thread_id

current_schema

session_var

disable_quotes

enable_quotes

disable_fk_checks

enable_fk_checks

schemata

tables_and_views

real_tables

views

CHARACTER ENCODINGS

DEBUGGING

SUPPORT

Homepage

Wiki

RATIONALE

COPYRIGHT AND LICENCE

SEE ALSO

Module Install Instructions

`new`

`connect`

`dsn`

`dsn_to_dump`

`Defaults`

`new`

`connect`

`selectall_arrayref_hashrefs`

`mysqld_version`

`thread_id`

`current_schema`

`session_var`

`disable_quotes`

`enable_quotes`

`disable_fk_checks`

`enable_fk_checks`

`schemata`

`tables_and_views`

`real_tables`

`views`