Good code style of DBIx::Custom at 2017

DBIx::Custom is good DBI wrapper to insert, update, delete, select easily and clean.

I introduce good code style of DBIx::Custom at 2017.

Connect


  my $dbi = DBIx::Custom->connect("dbi:mysql:database=dbname", 'ken', '! LFKD% $&');

Create model

It is good to create model.


  $dbi->create_model('book'); 

Execute SQL

Because model is used, insert, update, delete, select is very clean.


  # Insert
  $dbi->model('book')->insert({title => 'Perl', author => 'Ken'});
  
  # Update
  $dbi->model('book')->update(
    {title => 'Perl', author => 'Ken'},
    where => {id => 5}
  );
  
  # Delete
  $dbi->model('book')->delete(where => {author => 'Ken'});
  
  # Select
  my $result = $dbi->model('book')->select(
    ['title', 'author'],
    where => {author => 'Ken'}
  );
  
  my $rows = $result->all;

model + join

You can set join clause to model. You can get all column easily.


  $dbi->create_model(
    'book',
    join => ['left join author on book.author = author.row_id']
  ); 

  # Select
  my $result = $dbi->model('book')->select(
    [
      {book => '*'},
      {author => '*'}
    ],
    where => {'author.name' => 'kimoto'}
  );

In SQL, join clause is very bored. If you use DBIx::Custom, you can get all columns very easily. This
is the following SQL.


select
  book.row_id as "book.row_id", book.id as "book.id", book.author as "book.author",
  author.row_id as "author.row_id", author.name as "author.name"
from
  book
  left join author on book.author = author.row_id
where author.name = 'kimoto'

If you want to get only self column name in book table, you can write the following way using "__MY__".


  $dbi->create_model(
    'book',
    join => ['left join author on book.author = author.row_id']
  ); 

  # Select
  my $result = $dbi->model('book')->select(
    [
      {__MY__ => '*'},
      {author => '*'}
    ],
    where => {'author.name' => 'kimoto'}
  );

The following SQL is created.


select
  book.row_id as "row_id", book.id as "id", book.author as "author",
  author.row_id as "author.row_id", author.name as "author.name"
from
  book
  left join author on book.author = author.row_id
where author.name = 'kimoto'

2 Comments

Hi Yuki

I am new to perl and fan of your module.

I am asking you here what is the best way to fetch "last_insert_id" using DBIx:Custom.

Here is my code and the result looks like last_insert_id is not working.

#!/usr/bin/perl

use strict;
use DBI;
use DBIx::Custom;

my $dbh = DBI->connect(
"dbi:mysql:dbname=databasename", "username","pwd"
) or die $DBI::errstr;

$dbh->do("INSERT INTO table (value) VALUES ('{}')");

my $id = $dbh->{ q{mysql_insertid}};
print "The last Id of the inserted row is $id\n";

my $dbi = DBIx::Custom->connect(
"dbi:mysql:database=" . "databasename" . ":" . "127.0.0.1",'username','pwd'
);

$dbi->create_model('table');
$dbi->model('table')->insert(value=>'{}');
$id = $dbi->dbh->last_insert_id("", "", "table", "");
print "The last Id of the inserted row is $id\n";

Leave a comment

About Yuki Kimoto

user-pic I'm Perl Programmer. I LOVE Perl. I want to contribute Perl community and Perl users.