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'
I'm Perl Programmer. I LOVE Perl. I want to contribute Perl community and Perl users.
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";
$dbi->select('last_insert_id()', table => 'foo')->value;