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'

Leave a comment

About Yuki Kimoto

user-pic I'm Yuki Kimoto, Japanese Perl programmer.I create Perl modules and applications. GitPrep, SPVM, DBIx::Custom, Validator::Custom, Test::Mojo, Mojolicious::Plugin::AutoRoute, etc.