DBIx :: Custom 0.39 release - Adding SQL generation feature, and simple generation of model

I released DBIx::Custom 0.39. This time it is a big update. Please do test enough when updating.

DBIx::Custom 0.39

New features

The following features are added in DBIx::Custom 0.39.

SQL generation feature

SQL generation feature was added. You can get only SQL and bind values using the query option. This is corresponding to the feature of SQL::Abstract.


  my $query = $dbi->model('book')->insert({id => 1, name => 'Perl'}, query => 1);
  
  my $sql = $query->sql;
  my $bind_values   = $query->bind_values;

You can use it in combination with other DBI modules. For example, to send an asynchronous query, you can use AnyEvent::DBI and Mojo::Pg.


  my $pg = Mojo::Pg->new('postgresql:// postgres @ / test');
  $pg->db->query($sql => @$bind_values   => sub {
    my($db, $err, $results) = @_;
    ...
  });
  Mojo::IOLoop->start unless Mojo::IOLoop->is_running;

This is the main feature of this release, which makes it possible to combine DBIx::Custom with your favorite asynchronous DBI module.

This query option is currently implemented as an experimental feature.

Reuse SQL

Also, the above SQL can be reused only by changing parameters.

# First time
my $param = {id => 1, name => 'Perl'};
my $query = $dbi->model('book')->insert($param, query => 1);

my $sql = $query->sql;
my $sth = $dbi->dbh->prepare($sql);
$sth->execute($query->bind_values);

# Second time
$query->param({id => 2, name => 3});
$query->build;
$sth->execute($query->bind_values);

By using this, you can resolve performance problem.

create_result method has also been added to create a DBIx::Custom::Result object from the statement handle.

my $result = $dbi->create_result($sth);

DBIx::Connector is enabled by default

DBIx::Connector is enabled by default.

when you use DBIx::Custom on a pre-fork server such as Mojolicious, you do not have to set connector option.

Supports the same arguments as DBI in the connect method

The connect method of DBIx::Custom supported the same argument format as DBI. You don't need to learn special form.

my $dbi = DBIx::Custom->connect($dsn, $user, $password, $dbh_attrs);

Easy to generate model

The model can now be created with just one argument.

$dbi->create_model('book');
$dbi->model('book')->insert(...);

Unnecessary to call setup_model

The setup_model method call is no longer needed to set columns. It is set automatically when create_model or include_model is called.

Examples

From now, the following code is recommended. It is clean to write insert, update, delete, select after creating the model.

  use DBIx::Custom;
  
  # Connection
  my $dbi = DBIx::Custom->connect("dbi:mysql:database=dbname", 'ken', '! LFKD% $&');
  
  # Create a model
  $dbi->create_model('book');
  
  # 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'});

Delete experimental feature

Experimental features, the async option, async_conf attribute, default_schema attribute of the experiment method have been deleted.

If you execute asynchronous SQL in MySQL, please use "query" option to create SQL, then execute asynchronous query on your own asynchrnous DBI.

DBIx::Custom will not provide asynchronous sql any more. It only provide "synchronous query" and "SQL generation feature".

Deprecated features

There are some deprecated features. It will be removed after 5 years, please prepare.

Autoload feature

The autoload feature is now deprecated.

helper method

helper method is deprecated. Move the registered methods to the framework side.

update_or_insert method

The update_or_insert method is deprecated. Replace with select, update, insert method.

id option, primary_key option

"id" and "primary_key" options are deprecated. Please rewrite it using the where option.

count method

The count method is deprecated. Please rewrite it using "select('count(*'), ...)->value".

Release notes

All changes are below.

0.39 2017-03-28
  ---------------------------------------------------
  NOTE: This release add some DEPRECATED warnings.
  Maybe after these DEPRECATED period, DBIx::Custom will be more stable.
  ---------------------------------------------------
  - DBIx::Custom::helper method is DEPRECATED!
  - DBIx::Custom AUTOLOAD feature is DEPRECATED!
  - DBIx::Custom::update_or_insert method is DEPRECATED!
  - DBIx::Custom::count method is DEPRECATED!
  - DBIx::Custom::insert, select,update,delete method's id option is DEPRECATED!
  - DBIx::Custom::insert, select,update,delete method's primary_key option is DEPRECATED!
  - DBIx::Custom::Model AUTOLOAD feature is DEPRECATED!
  - DBIx::Custom::Model::helper method is DEPRECATED!
  - DBIx::Custom::Model::update_or_insert method is DEPRECATED!
  - DBIx::Custom::Model::count method is DEPRECATED!
  - DBIx::Custom::Model::primary_key attribute is DEPRECATED!
  - DBIx::Custom::setup method is DEPRECATED!
    this method has no meaning because
    columns is automatically set when create_model or include_model is called.
  ------------------------
  Performance down
  ------------------------
  - DBIx::Custom::insert method which have array argument(For example, [{foo => 1, bar => 2}, {foo => 3, bar => 4}])
    become slow. (Performance down 1)
  - DBIx::Custom::execute method's reuse option become no more meaning. (Performance down 2)
  
  You can resolve above two problem by using query option
  ------------------------
  remove EXPERIMENTAL
  ------------------------
  - remove EXPERIMENTAL DBIx::Custom::execute method's async option
  - remove EXPERIMENTAL DBIx::Custom::async_conf attribute
    From this release, DBIx::Custom support only syncronous query,
    but from this release, you can create sql and bind values by excute method's query option
    you can pass sql and bind values to any asyncronous support database module,
    (for example, AnyEvent::DBI or Mojo::Pg)
  - remove EXPERIMENTAL DBIx::Custom::default_schema attribute
  -----------------------
  New features
  -----------------------
  - add EXPERIMENTAL DBIx::Custom::execute query option again. This method return DBIx::Custom::Query object.
    If you use this option, you can resolve "Performance down 1" and "Performance down 2".
  - add EXPERIMENTAL DBIx::Custom::create_result method to create result class from statement handle
  - DBIx::Connector become enable by default
  - Model columns is automatically set when DBIx::Custome::create_model and DBIx::Custome::include_model called
  - DBIx::Custom::connect method support DBI compatible arguments
    DBIx::Custom->connect($dsn, $user, $password, $dbh_attrs);
  - DBIx::Custom::create_model can receive model name only
    $dbi->create_model('book');
  - Model name is indepenet from table name.
    You can write the following
    $dbi->create_model(name => 'book1', table => 'book');

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.