Utility

DBIx::Custom
here

Get the last executed SQL --last_sql

Use the last_sql method to get the last executed SQL.

my $last_sql = $dbi->last_sql;

This is useful when debugging.

Check the executed SQL

To see what SQL was actually executed, set the environment variable DBIX_CUSTOM_DEBUG to a true value (for example, 1). The SQL executed and the bound value are output to the standard error output.

$ENV {DBIX_CUSTOM_DEBUG} = 1

Register helper method --helper

Use the helper method to register a helper method.

$dbi->helper(
  find_or_create => sub {
    my $self = shift;
    ...
  }
);

The registered helper method can be called directly from the DBIx::Custom object.

$dbi->find_or_create;

Merge parameters --merge_param

Use the merge_param method to merge the two parameters.

my $param = $dbi->merge_param({key1 => 1}, {key1 => 1, key2 => 2});

Get the new parameters as follows: If they contain the same key, the values are merged into the array reference.

{key1 => [1, 1], key2 => 2}

Create DBIx::Custom object --new

Use the new method to create a DBIx::Custom object.

my $dbi = DBIx::Custom->new(
  dsn => "dbi: mysql: database = dbname",
  user =>'ken',
  password =>'! LFKD%$&',
  option => {mysql_enable_utf8 => 1}
);

The difference from the connect method is that the new method does not make a connection. If you want to connect the object after it is created, call the connect method.

$dbi->connect;

Get database handle --dbh

Use the dbh method to get the database handle.

my $dbh = $dbi->dbh;

If the connection manager is set in the connector, the database handle will be obtained via the connection manager.

Check the existence of the table

You may want to check if the table already exists in the database. In such cases, it's easy to use the each_column method to find out if there is a table to specify. The each_column method is a method to get all columns, but since it also receives table information as an argument, it can also be used in this way.

my $table_exists;
$dbi->each_column(sub {
  my ($dbi, $table, $column) = @_;
  $table_exists = 1 if $table eq'book';
});

Use Mojolicious::Lite, DBIx::Custom and DBD::mysql to access DB non-blocking like node.js

DBIx::Custom is still in the experimental stage, but with the help of AnyEvent, it is now possible to query the database without blocking.

I'm throwing SQL that waits for 5 seconds, but even if there are multiple requests, I can process the requests without blocking. With this, it seems that Perl can easily make non-blocking queries to the database that you want to do with nodel.js.

use Mojolicious::Lite;
use EV;
use DBIx::Custom;

my $dbi = DBIx::Custom->connect(
  dsn =>'dbi: mysql: database = usertest',
  user =>'root'
);

$dbi->async_conf({{
  prepare_attr => {async => 1},
  fh => sub {shift->dbh->mysql_fd}
});

get'/' => sub {
  my $self = shift;
  
  $self->render_later;
  $dbi->select('SLEEP (5), 3', async => sub {
    my ($dbi, $result) = @_;
    my $row = $result->fetch_one;
    $self->render(text => $row->[1]);
  });
};;

app->start;

The mechanism is like this. First of all, Mojolicious has a non-blocking HTTP server, and as an internal implementation, it goes around the IO loop inside. When Mojolicious has an EV, it can switch from its own Mojo::IOLoop to an IO loop using EV. In DBIx::Custom, the event is registered using AnyEvent. Since I am using EV, I will register more and more events in EV. Mojolicious also registers his events on EV more and more. It spins round and round and processes in order from the one that has finished waiting for IO.

However, this is still a super-experimental process, so I'd be happy if you could try it for any problems.

Stop the analysis function of the deprecated tag to improve the performance

Until the deprecated features are removed, DBIx::Custom performance is not optimized by default due to backwards compatibility policy. Previously we implemented a feature called tags, but for performance reasons this feature has been replaced by named placeholders.

You can improve the performance of DBIx::Custom a little by stopping the tag parsing function.

$ENV {DBIX_CUSTOM_TAG_PARSE} = 0;

In the future, the removal of many other deprecated features will result in a slight overall performance improvement.

Mapping

parameters --mapper

I think that you often convert the parameters received from the URL into the parameters for the database. In such a case, use the mapper method to create a DBIx::Custom::Mapper object and use it.

#Generation of mapper
my $mapper = $dbi->mapper(param => $param);

Suppose the parameters are as follows:

{
  t =>'Perl',
  p => 1000,
  a =>'Ken',
  b =>'XXX'
}

You can use the map method to map the parameters.

#Mapping
my $new_param = $mapper->map(
  t => [title =>'%<value>'], # map key to value
  p => [price =>'<value>'], # map only key
  a => [a =>'%<value>%'] # Map only values
);

The format is as follows.

Original key name => [new key name => new value]

The original value can be expressed in the format & lt; value & gt; . You can also use it to create new values such as %Perl .

It maps to the following parameters:

{
  title =>'%Perl',
  price => 1000,
  a =>'%Ken%'
}

Keys not specified in the map method will not be mapped. The key b is not mapped in this sample.

If you want to change the value a little more flexibly, you can use the subroutine reference. Since the original value can be obtained with the first argument, the new value is returned.

Original key name => [new key name => sub {return'%'. $_ [0].'%'}]

Parameter mapping conditions

The default mapping conditions are as follows:

The value is defined and there is a length

So the following parameters are mapped.

title =>'Perl',

However, anything that does not have an empty string, undefined value, or the key itself will not be mapped. This is because the mapping is intended for web applications.

title =>'',
title => undef

Use the condition attribute to change this condition. You can set the reference of any subroutine or the strings "length", "defined" and "exists" as special values.

$mapper->condition('length'); # Same as default
$mapper->condition('defined'); #value is definedRu
$mapper->condition('exists'); # key exists
$mapper->condition(sub {defined $_ [0]}); # Reference to any subroutine

It is also possible to set each key individually as follows with the map method.

$mapper->map(
   Old key => [new key => new value, {condtion =>'defined'}],
);

DBIx::Custom