DBIx::Custom Documents >

you can edit SQL after SQL is build.

after_build_sql => $code_ref

Exmaple:

$dbi->select(
  table => 'book',
  column => 'distinct(name)',
  after_build_sql => sub {
    my $sql = shift;
    
    my $new_sql = "select count(*) from ($sql) as t1";
    
    return $new_sql;
  }
);

The following SQL is executed.

select count(*) from (select distinct(name) from book) as t1;

Add some statements.

append => 'order by name'

Example:

# select * from book order by name
$dbi->select(
  table => 'book',
  append => 'order by name'
);

If inserted data is binary, you must specify data type.

# DBI
$sth->bind_param($pos, $value, DBI::SQL_BLOB);

If you want to specify data type, use bind_type.

# Bind type option
bind_type => {image => DBI::SQL_BLOB}
bind_type => [image => DBI::SQL_BLOB]

bind_type value is hash reference or array reference.

If same value is set to multiple columns, you can use array reference as column names.

bind_type => [['image', 'audio'] => DBI::SQL_BLOB]

Filter value. Filtering is executed before the value is embdded into place holder.

# Filter option
filter => {title  => 'upper_case'}
filter => {title  => sub { uc $_[0] }}

Filter format is {COLUMN_NAME = FILTER_NAME}> or {FILTER_NAME = FILTER_CODE_REF}>

Filter name must be registered by register_filter method.

Same filter apply to multiple columns.

filter => [['title', 'author']  => 'upper_case']

Set parameter value without column name. primary_key must be set.

id => 4
id => [4, 5]

For example, primary_key is set to [id1, id2], id = [4, 5]> is same as {id1 = 4, id2 => 5}>.

$dbi->execute(
  "select * from book where id1 = :id1 and id2 = :id2",
  {},
  primary_key => ['id1', 'id2'],
  id => [4, 5],
);

Above execute method is same as the following one.

$dbi->execute( "select * from book where id1 = :id1 and id2 = :id2", {id1 => 4, id2 => 5} );

You think id option have what meaning. This is useful for model and update_or_insert method.

# Model
my $book_model
  = $dbi->create_model(table => 'book', parimay_key => 'book_id');

# Update
$book_model->update({title => 'Perl'}, id => 3);

# Update or insert
$book_model->update_or_insert({title => 'Perl'}, id => 3);

Get DBIx::Custom::Query object without executing SQL.

query => 1

DBIx::Custom::Query object is usuful when you want to see SQL and column names.

my $sql = $query->sql;
my $columns = $query->columns;

Don't pass DBIx::Custom::Query object to execute method. This is buggy implementation and removed in nealy future. Use reuse option to improve performance.

Set primary key for id option.

primary_key => 'book_id'
primary_key => ['book_id', 'number']

Note that this is not real primary key in RDBMS. You must set meaningful column names.

create table book (
  id int primary_key auto_increment,
  book_id int not null unique
  title varchar(100)
);

For example, real primary key is id, but primary_key option must be set to book_id.

Improve performance twice or thrid times when same SQL is executed repeatedly. Hash reference is needed to save queries.

reuse => $hash_ref

Example:

my $queries = {};
for my $param (@params) {
  $dbi->execute($sql, $row, reuse => $queries);
}

You can use reuse option in insert, update, delete, or select method.

my $queries = {};
for my $row (@rows) {
  $dbi->insert($row, table => 'book', reuse => $queries);
}

Set table name.

table => 'author'

Usually, in execute method, table options is no need because SQL is directory executed.

but table option have some effects.

If named place holder don't have full-qualified name, it is full-qualified by table name, and parameter name is also full-qualified.

$dbi->execute("select * from book where title = :title and author = :author",
  {title => 'Perl', author => 'Ken'}, table => 'book');

Above execute example is same as the following one.

$dbi->execute(
  "select * from book where title = :book.title and author = :book.author",
  {'book.title' => 'Perl', 'book.author' => 'Ken'});

table option is only needed when type_rule's into1 and intyo2 filtering is enabled. If named place holder and parameter name don't has full-qualified name, iltering don't work because execute method don't know the column name belong to what table.

Set table alias name.

table_alias => {worker => 'user'} # {ALIAS => TABLE}

If table alias name is set, type_rule method's into1 and into2 filtering enable in alias name.

For this example, Filtering on user table is applied to worker alias.

Disable type_rule method's into1 and into2 filtering.

type_rule_off => 1

Note that this option have no effect on from1 and from2 filtering.

Disable type_rule method's into1 filtering.

type_rule1_off => 1

Note that this option have no effect on from1 and from2 filtering.

Disable type_rule method's into2 filtering.

type_rule2_off => 1

Note that this option have no effect on from1 and from2 filtering.