NAME

DBIx::Custom - DBI extension to execute insert, update, delete, and select easily

SYNOPSIS

    use DBIx::Custom;
    
    # Connect
    my $dbi = DBIx::Custom->connect(
        dsn => "dbi:mysql:database=dbname",
        user => 'ken',
        password => '!LFKD%$&',
        option => {mysql_enable_utf8 => 1}
    );

    # Insert 
    $dbi->insert({title => 'Perl', author => 'Ken'}, table  => 'book');
    
    # Update 
    $dbi->update({title => 'Perl', author => 'Ken'}, table  => 'book',
      where  => {id => 5});
    
    # Delete
    $dbi->delete(table  => 'book', where => {author => 'Ken'});

    # Select
    my $result = $dbi->select(table  => 'book',
      column => ['title', 'author'], where  => {author => 'Ken'});

    # Select, more complex
    my $result = $dbi->select(
        table  => 'book',
        column => [
            {book => [qw/title author/]},
            {company => ['name']}
        ],
        where  => {'book.author' => 'Ken'},
        join => ['left outer join company on book.company_id = company.id'],
        append => 'order by id limit 5'
    );
    
    # Fetch
    while (my $row = $result->fetch) {
        
    }
    
    # Fetch as hash
    while (my $row = $result->fetch_hash) {
        
    }
    
    # Execute SQL with parameter.
    $dbi->execute(
        "select id from book where author = :author and title like :title",
        {author => 'ken', title => '%Perl%'}
    );

DESCRIPTION

DBIx::Custom is DBI wrapper module to execute SQL easily. This module have the following features.

Execute insert, update, delete, or select statement easily
Create where clause flexibly
Named place holder support
Model support
Connection manager support
Choice your favorite relational database management system, MySQL, SQLite, PostgreSQL, Oracle, Microsoft SQL Server, Microsoft Access, DB2 or anything,
Filtering by data type or column name
Create order by clause flexibly

DOCUMENTATION

DBIx::Custom::Guide - How to use DBIx::Custom

DBIx::Custom Wiki - Theare are various examples.

Module documentations - DBIx::Custom::Result, DBIx::Custom::Query, DBIx::Custom::Where, DBIx::Custom::Model, DBIx::Custom::Order

ATTRIBUTES

`connector`

    my $connector = $dbi->connector;
    $dbi = $dbi->connector($connector);

Connection manager object. if connector is set, you can get dbh through connection manager. Conection manager object must have dbh mehtod.

This is DBIx::Connector example. Please pass default_option to DBIx::Connector new method.

    my $connector = DBIx::Connector->new(
        "dbi:mysql:database=$database",
        $user,
        $password,
        DBIx::Custom->new->default_option
    );
    
    my $dbi = DBIx::Custom->connect(connector => $connector);

If connector is set to 1 when connect method is called, DBIx::Connector is automatically set to connector

    my $dbi = DBIx::Custom->connect(
      dsn => $dsn, user => $user, password => $password, connector => 1);
    
    my $connector = $dbi->connector; # DBIx::Connector

Note that DBIx::Connector must be installed.

`dsn`

    my $dsn = $dbi->dsn;
    $dbi = $dbi->dsn("DBI:mysql:database=dbname");

Data source name, used when connect method is executed.

`default_option`

    my $default_option = $dbi->default_option;
    $dbi = $dbi->default_option($default_option);

DBI default option, used when connect method is executed, default to the following values.

    {
        RaiseError => 1,
        PrintError => 0,
        AutoCommit => 1,
    }

`exclude_table`

    my $exclude_table = $dbi->exclude_table;
    $dbi = $dbi->exclude_table(qr/pg_/);

Excluded table regex. each_column, each_table, type_rule, and setup_model methods ignore matching tables.

`filters`

    my $filters = $dbi->filters;
    $dbi = $dbi->filters(\%filters);

Filters, registered by register_filter method.

`last_sql`

    my $last_sql = $dbi->last_sql;
    $dbi = $dbi->last_sql($last_sql);

Get last successed SQL executed by execute method.

`models`

    my $models = $dbi->models;
    $dbi = $dbi->models(\%models);

Models, included by include_model method.

`option`

    my $option = $dbi->option;
    $dbi = $dbi->option($option);

DBI option, used when connect method is executed. Each value in option override the value of default_option.

`password`

    my $password = $dbi->password;
    $dbi = $dbi->password('lkj&le`@s');

Password, used when connect method is executed.

`query_builder`

    my $builder = $dbi->query_builder;

Creat query builder. This is DBIx::Custom::QueryBuilder.

`quote`

     my quote = $dbi->quote;
     $dbi = $dbi->quote('"');

Reserved word quote. Default to double quote '"' except for mysql. In mysql, default to back quote '`'

You can set quote pair.

    $dbi->quote('[]');

`result_class`

    my $result_class = $dbi->result_class;
    $dbi = $dbi->result_class('DBIx::Custom::Result');

Result class, default to DBIx::Custom::Result.

`safety_character`

    my $safety_character = $dbi->safety_character;
    $dbi = $dbi->safety_character($character);

Regex of safety character for table and column name, default to '\w'. Note that you don't have to specify like '[\w]'.

`separator`

    my $separator = $dbi->separator;
    $dbi = $dbi->separator('-');

Separator which join table name and column name. This have effect to column and mycolumn method, and select method's column option.

Default to ..

`tag_parse`

    my $tag_parse = $dbi->tag_parse(0);
    $dbi = $dbi->tag_parse;

Enable DEPRECATED tag parsing functionality, default to 1. If you want to disable tag parsing functionality, set to 0.

`user`

    my $user = $dbi->user;
    $dbi = $dbi->user('Ken');

User name, used when connect method is executed.

`user_column_info`

    my $user_column_info = $dbi->user_column_info;
    $dbi = $dbi->user_column_info($user_column_info);

You can set the date like the following one.

    [
        {table => 'book', column => 'title', info => {...}},
        {table => 'author', column => 'name', info => {...}}
    ]

Usually, you set return value of get_column_info.

    my $user_column_info
      = $dbi->get_column_info(exclude_table => qr/^system/);
    $dbi->user_column_info($user_column_info);

If user_column_info is set, each_column use user_column_info to find column info. this is very fast.

`user_table_info`

    my $user_table_info = $dbi->user_table_info;
    $dbi = $dbi->user_table_info($user_table_info);

You can set the following data.

    [
        {table => 'book', info => {...}},
        {table => 'author', info => {...}}
    ]

Usually, you can set return value of get_table_info.

    my $user_table_info = $dbi->get_table_info(exclude => qr/^system/);
    $dbi->user_table_info($user_table_info);

If user_table_info is set, each_table use user_table_info to find table info.

METHODS

DBIx::Custom inherits all methods from Object::Simple and use all methods of DBI and implements the following new ones.

`available_datatype`

    print $dbi->available_datatype;

Get available data types. You can use these data types in type rule's from1 and from2 section.

`available_typename`

    print $dbi->available_typename;

Get available type names. You can use these type names in type_rule's into1 and into2 section.

`assign_clause`

    my $assign_clause = $dbi->assign_clause({title => 'a', age => 2});

Create assign clause

    title = :title, author = :author

This is used to create update clause.

    "update book set " . $dbi->assign_clause({title => 'a', age => 2});

`column`

    my $column = $dbi->column(book => ['author', 'title']);

Create column clause. The follwoing column clause is created.

    book.author as "book.author",
    book.title as "book.title"

You can change separator by separator attribute.

    # Separator is hyphen
    $dbi->separator('-');
    
    book.author as "book-author",
    book.title as "book-title"

`connect`

    my $dbi = DBIx::Custom->connect(
        dsn => "dbi:mysql:database=dbname",
        user => 'ken',
        password => '!LFKD%$&',
        option => {mysql_enable_utf8 => 1}
    );

Connect to the database and create a new DBIx::Custom object.

DBIx::Custom is a wrapper of DBI. AutoCommit and RaiseError options are true, and PrintError option is false by default.

`count`

    my $count = $dbi->count(table => 'book');

Get rows count.

Options is same as select method's ones.

`create_model`

    my $model = $dbi->create_model(
        table => 'book',
        primary_key => 'id',
        join => [
            'inner join company on book.comparny_id = company.id'
        ],
    );

Create DBIx::Custom::Model object and initialize model. the module is also used from model method.

   $dbi->model('book')->select(...);

`dbh`

    my $dbh = $dbi->dbh;

Get DBI database handle. if connector is set, you can get database handle through connector object.

`delete`

    $dbi->delete(table => 'book', where => {title => 'Perl'});

Execute delete statement.

The following opitons are available.

OPTIONS

delete method use all of execute method's options, and use the following new ones.

id

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

ID corresponding to primary_key. You can delete rows by id and primary_key.

    $dbi->delete(
        primary_key => ['id1', 'id2'],
        id => [4, 5],
        table => 'book',
    );

The above is same as the followin one.

    $dbi->delete(where => {id1 => 4, id2 => 5}, table => 'book');

prefix

    prefix => 'some'

prefix before table name section.

    delete some from book

table

    table => 'book'

Table name.

where

Same as select method's where option.

`delete_all`

    $dbi->delete_all(table => $table);

Execute delete statement for all rows. Options is same as delete.

`each_column`

    $dbi->each_column(
        sub {
            my ($dbi, $table, $column, $column_info) = @_;
            
            my $type = $column_info->{TYPE_NAME};
            
            if ($type eq 'DATE') {
                # ...
            }
        }
    );

Iterate all column informations in database. Argument is callback which is executed when one column is found. Callback receive four arguments. DBIx::Custom object, table name, column name, and column information.

If user_column_info is set, each_column method use user_column_info infromation, you can improve the performance of each_column in the following way.

    my $column_infos = $dbi->get_column_info(exclude_table => qr/^system_/);
    $dbi->user_column_info($column_info);
    $dbi->each_column(sub { ... });

`each_table`

    $dbi->each_table(
        sub {
            my ($dbi, $table, $table_info) = @_;
            
            my $table_name = $table_info->{TABLE_NAME};
        }
    );

Iterate all table informationsfrom in database. Argument is callback which is executed when one table is found. Callback receive three arguments, DBIx::Custom object, table name, table information.

If user_table_info is set, each_table method use user_table_info infromation, you can improve the performance of each_table in the following way.

    my $table_infos = $dbi->get_table_info(exclude => qr/^system_/);
    $dbi->user_table_info($table_info);
    $dbi->each_table(sub { ... });

`execute`

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

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

Execute SQL. SQL can contain column parameter such as :author and :title. You can append table name to column name such as :book.title and :book.author. Second argunet is data, embedded into column parameter. Return value is DBIx::Custom::Result object when select statement is executed, or the count of affected rows when insert, update, delete statement is executed.

Named placeholder such as :title is replaced by placeholder ?.

    # Original
    select * from book where title = :title and author like :author
    
    # Replaced
    select * from where title = ? and author like ?;

You can specify operator with named placeholder by name{operator} syntax.

    # Original
    select * from book where :title{=} and :author{like}
    
    # Replaced
    select * from where title = ? and author like ?;

Note that colons in time format such as 12:13:15 is exeption, it is not parsed as named placeholder. If you want to use colon generally, you must escape it by \\

    select * from where title = "aa\\:bb";

OPTIONS

The following opitons are available.

after_build_sql

You can filter sql after the sql is build.

    after_build_sql => $code_ref

The following one is one example.

    $dbi->select(
        table => 'book',
        column => 'distinct(name)',
        after_build_sql => sub {
            "select count(*) from ($_[0]) as t1"
        }
    );

The following SQL is executed.

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

append

    append => 'order by name'

Append some statement after SQL.

bind_type

Specify database bind data type.

    bind_type => [image => DBI::SQL_BLOB]
    bind_type => [[qw/image audio/] => DBI::SQL_BLOB]

This is used to bind parameter by bind_param of statment handle.

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

filter

    filter => {
        title  => sub { uc $_[0] }
        author => sub { uc $_[0] }
    }

    # Filter name
    filter => {
        title  => 'upper_case',
        author => 'upper_case'
    }
        
    # At once
    filter => [
        [qw/title author/]  => sub { uc $_[0] }
    ]

Filter. You can set subroutine or filter name registered by by register_filter. This filter is executed before data is saved into database. and before type rule filter is executed.

id

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

ID corresponding to primary_key. You can delete rows by id and primary_key.

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

The above is same as the followin one.

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

query

    query => 1

execute method return DBIx::Custom::Query object, not executing SQL. You can check SQL or get statment handle.

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

If you want to execute SQL fast, you can do the following way.

    my $query;
    for my $row (@$rows) {
      $query ||= $dbi->insert($row, table => 'table1', query => 1);
      $dbi->execute($query, $row);
    }

Statement handle is reused and SQL parsing is finished, so you can get more performance than normal way.

If you want to execute SQL as possible as fast and don't need filtering. You can do the following way.

    my $query;
    my $sth;
    for my $row (@$rows) {
      $query ||= $dbi->insert($row, table => 'book', query => 1);
      $sth ||= $query->sth;
      $sth->execute(map { $row->{$_} } sort keys %$row);
    }

Note that $row must be simple hash reference, such as {title => 'Perl', author => 'Ken'}. and don't forget to sort $row values by $row key asc order.

primary_key

    primary_key => 'id'
    primary_key => ['id1', 'id2']

Priamry key. This is used when id option find primary key.

table

    table => 'author'

If you want to omit table name in column name and enable into1 and into2 type filter, You must set table option.

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

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

table_alias

    table_alias => {user => 'worker'}

Table alias. Key is real table name, value is alias table name. If you set table_alias, you can enable into1 and into2 type rule on alias table name.

reuse_sth EXPERIMENTAL

    reuse_sth => $has_ref

Reuse statament handle if the hash reference variable is set.

    my $sth = {};
    $dbi->execute($sql, $param, sth => $sth);

This will improved performance when same sql is executed repeatedly because generally creating statement handle is slow.

type_rule_off

    type_rule_off => 1

Turn into1 and into2 type rule off.

type_rule1_off

    type_rule1_off => 1

Turn into1 type rule off.

type_rule2_off

    type_rule2_off => 1

Turn into2 type rule off.

`get_column_info`

    my $column_infos = $dbi->get_column_info(exclude_table => qr/^system_/);

get column infomation except for one which match exclude_table pattern.

    [
        {table => 'book', column => 'title', info => {...}},
        {table => 'author', column => 'name' info => {...}}
    ]

`get_table_info`

    my $table_infos = $dbi->get_table_info(exclude => qr/^system_/);

get table infomation except for one which match exclude pattern.

    [
        {table => 'book', info => {...}},
        {table => 'author', info => {...}}
    ]

You can set this value to user_table_info.

`helper`

    $dbi->helper(
        update_or_insert => sub {
            my $self = shift;
            
            # Process
        },
        find_or_create   => sub {
            my $self = shift;
            
            # Process
        }
    );

    $dbi->update_or_insert;
    $dbi->find_or_create;

`insert`

    $dbi->insert({title => 'Perl', author => 'Ken'}, table  => 'book');

Execute insert statement. First argument is row data. Return value is affected row count.

If you want to set constant value to row data, use scalar reference as parameter value.

    {date => \"NOW()"}

options

insert method use all of execute method's options, and use the following new ones.

id

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

ID corresponding to primary_key. You can insert a row by id and primary_key.

    $dbi->insert(
        {title => 'Perl', author => 'Ken'}
        primary_key => ['id1', 'id2'],
        id => [4, 5],
        table => 'book'
    );

The above is same as the followin one.

    $dbi->insert(
        {id1 => 4, id2 => 5, title => 'Perl', author => 'Ken'},
        table => 'book'
    );

prefix

    prefix => 'or replace'

prefix before table name section

    insert or replace into book

table

    table => 'book'

Table name.

timestamp

    timestamp => 1

If this value is set to 1, automatically created timestamp column is set based on timestamp attribute's insert value.

wrap

    wrap => {price => sub { "max($_[0])" }}

placeholder wrapped string.

If the following statement

    $dbi->insert({price => 100}, table => 'book',
      {price => sub { "$_[0] + 5" }});

is executed, the following SQL is executed.

    insert into book price values ( ? + 5 );

`include_model`

    $dbi->include_model('MyModel');

Include models from specified namespace, the following layout is needed to include models.

    lib / MyModel.pm
        / MyModel / book.pm
                  / company.pm

Name space module, extending DBIx::Custom::Model.

MyModel.pm

    package MyModel;
    use DBIx::Custom::Model -base;
    
    1;

Model modules, extending name space module.

MyModel/book.pm

    package MyModel::book;
    use MyModel -base;
    
    1;

MyModel/company.pm

    package MyModel::company;
    use MyModel -base;
    
    1;

MyModel::book and MyModel::company is included by include_model.

You can get model object by model.

    my $book_model = $dbi->model('book');
    my $company_model = $dbi->model('company');

See DBIx::Custom::Model to know model features.

`insert_timestamp`

    $dbi->insert_timestamp(
      [qw/created_at updated_at/]
        => sub { Time::Piece->localtime->strftime("%Y-%m-%d %H:%M:%S") }
    );

Timestamp value when insert method is executed with timestamp option.

If insert_timestamp is set and insert method is executed with timestamp option, column created_at and update_at is automatically set to the value like "2010-10-11 10:12:54".

    $dbi->insert($param, table => 'book', timestamp => 1);

`like_value`

    my $like_value = $dbi->like_value

Code reference which return a value for the like value.

    sub { "%$_[0]%" }

`mapper`

    my $mapper = $dbi->mapper(param => $param);

Create a new DBIx::Custom::Mapper object.

`merge_param`

    my $param = $dbi->merge_param({key1 => 1}, {key1 => 1, key2 => 2});

Merge parameters. The following new parameter is created.

    {key1 => [1, 1], key2 => 2}

If same keys contains, the value is converted to array reference.

`model`

    my $model = $dbi->model('book');

Get a DBIx::Custom::Model object create by create_model or include_model

`mycolumn`

    my $column = $dbi->mycolumn(book => ['author', 'title']);

Create column clause for myself. The follwoing column clause is created.

    book.author as author,
    book.title as title

`new`

    my $dbi = DBIx::Custom->new(
        dsn => "dbi:mysql:database=dbname",
        user => 'ken',
        password => '!LFKD%$&',
        option => {mysql_enable_utf8 => 1}
    );

Create a new DBIx::Custom object.

`not_exists`

    my $not_exists = $dbi->not_exists;

DBIx::Custom::NotExists object, indicating the column is not exists. This is used in param of DBIx::Custom::Where .

`order`

    my $order = $dbi->order;

Create a new DBIx::Custom::Order object.

`register_filter`

    $dbi->register_filter(
        # Time::Piece object to database DATE format
        tp_to_date => sub {
            my $tp = shift;
            return $tp->strftime('%Y-%m-%d');
        },
        # database DATE format to Time::Piece object
        date_to_tp => sub {
           my $date = shift;
           return Time::Piece->strptime($date, '%Y-%m-%d');
        }
    );

`select`

    my $result = $dbi->select(
        table  => 'book',
        column => ['author', 'title'],
        where  => {author => 'Ken'},
    );

Execute select statement.

OPTIONS

select method use all of execute method's options, and use the following new ones.

column

    column => 'author'
    column => ['author', 'title']

Column clause.

if column is not specified, '*' is set.

    column => '*'

You can specify hash of array reference.

    column => [
        {book => [qw/author title/]},
        {person => [qw/name age/]}
    ]

This is expanded to the following one by using colomn method.

    book.author as "book.author",
    book.title as "book.title",
    person.name as "person.name",
    person.age as "person.age"

You can specify array of array reference, first argument is column name, second argument is alias.

    column => [
        ['date(book.register_datetime)' => 'book.register_date']
    ];

Alias is quoted properly and joined.

    date(book.register_datetime) as "book.register_date"

filter

Same as execute method's filter option.

id

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

ID corresponding to primary_key. You can select rows by id and primary_key.

    $dbi->select(
        primary_key => ['id1', 'id2'],
        id => [4, 5],
        table => 'book'
    );

The above is same as the followin one.

    $dbi->select(
        where => {id1 => 4, id2 => 5},
        table => 'book'
    );

param

    param => {'table2.key3' => 5}

Parameter shown before where clause.

For example, if you want to contain tag in join clause, you can pass parameter by param option.

    join  => ['inner join (select * from table2 where table2.key3 = :table2.key3)' . 
              ' as table2 on table1.key1 = table2.key1']

    prefix => 'SQL_CALC_FOUND_ROWS'

Prefix of column cluase

    select SQL_CALC_FOUND_ROWS title, author from book;

join

    join => [
        'left outer join company on book.company_id = company_id',
        'left outer join location on company.location_id = location.id'
    ]

Join clause. If column cluase or where clause contain table name like "company.name", join clausees needed when SQL is created is used automatically.

    $dbi->select(
        table => 'book',
        column => ['company.location_id as location_id'],
        where => {'company.name' => 'Orange'},
        join => [
            'left outer join company on book.company_id = company.id',
            'left outer join location on company.location_id = location.id'
        ]
    );

In above select, column and where clause contain "company" table, the following SQL is created

    select company.location_id as location_id
    from book
      left outer join company on book.company_id = company.id
    where company.name = ?;

You can specify two table by yourself. This is useful when join parser can't parse the join clause correctly.

    $dbi->select(
        table => 'book',
        column => ['company.location_id as location_id'],
        where => {'company.name' => 'Orange'},
        join => [
            {
                clause => 'left outer join location on company.location_id = location.id',
                table => ['company', 'location']
            }
        ]
    );

table

    table => 'book'

Table name.

where

    # Hash refrence
    where => {author => 'Ken', 'title' => 'Perl'}
    
    # DBIx::Custom::Where object
    where => $dbi->where(
        clause => ['and', 'author = :author', 'title like :title'],
        param  => {author => 'Ken', title => '%Perl%'}
    );
    
    # Array reference 1 (array reference, hash referenc). same as above
    where => [
        ['and', 'author = :author', 'title like :title'],
        {author => 'Ken', title => '%Perl%'}
    ];    
    
    # Array reference 2 (String, hash reference)
    where => [
        'title like :title',
        {title => '%Perl%'}
    ]
    
    # String
    where => 'title is null'

Where clause. See DBIx::Custom::Where.

`setup_model`

    $dbi->setup_model;

Setup all model objects. columns of model object is automatically set, parsing database information.

`type_rule`

    $dbi->type_rule(
        into1 => {
            date => sub { ... },
            datetime => sub { ... }
        },
        into2 => {
            date => sub { ... },
            datetime => sub { ... }
        },
        from1 => {
            # DATE
            9 => sub { ... },
            # DATETIME or TIMESTAMP
            11 => sub { ... },
        }
        from2 => {
            # DATE
            9 => sub { ... },
            # DATETIME or TIMESTAMP
            11 => sub { ... },
        }
    );

Filtering rule when data is send into and get from database. This has a little complex problem.

In into1 and into2 you can specify type name as same as type name defined by create table, such as DATETIME or DATE.

Note that type name and data type don't contain upper case. If these contain upper case charactor, you convert it to lower case.

into2 is executed after into1.

Type rule of into1 and into2 is enabled on the following column name.

1. column name

    issue_date
    issue_datetime

This need table option in each method.

2. table name and column name, separator is dot

    book.issue_date
    book.issue_datetime

You get all type name used in database by available_typename.

    print $dbi->available_typename;

In from1 and from2 you specify data type, not type name. from2 is executed after from1. You get all data type by available_datatype.

    print $dbi->available_datatype;

You can also specify multiple types at once.

    $dbi->type_rule(
        into1 => [
            [qw/DATE DATETIME/] => sub { ... },
        ],
    );

`update`

    $dbi->update({title => 'Perl'}, table  => 'book', where  => {id => 4});

Execute update statement. First argument is update row data.

If you want to set constant value to row data, use scalar reference as parameter value.

    {date => \"NOW()"}

OPTIONS

update method use all of execute method's options, and use the following new ones.

id

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

ID corresponding to primary_key. You can update rows by id and primary_key.

    $dbi->update(
        {title => 'Perl', author => 'Ken'}
        primary_key => ['id1', 'id2'],
        id => [4, 5],
        table => 'book'
    );

The above is same as the followin one.

    $dbi->update(
        {title => 'Perl', author => 'Ken'}
        where => {id1 => 4, id2 => 5},
        table => 'book'
    );

prefix

    prefix => 'or replace'

prefix before table name section

    update or replace book

table

    table => 'book'

Table name.

timestamp

    timestamp => 1

If this value is set to 1, automatically updated timestamp column is set based on timestamp attribute's update value.

where

Same as select method's where option.

wrap

    wrap => {price => sub { "max($_[0])" }}

placeholder wrapped string.

If the following statement

    $dbi->update({price => 100}, table => 'book',
      {price => sub { "$_[0] + 5" }});

is executed, the following SQL is executed.

    update book set price =  ? + 5;

`update_all`

    $dbi->update_all({title => 'Perl'}, table => 'book', );

Execute update statement for all rows. Options is same as update method.

`update_or_insert EXPERIMENTAL`

    # Where
    $dbi->update_or_insert(
        {id => 1, title => 'Perl'},
        table => 'book',
        where => {id => 1},
        select_option => {append => 'for update'}
    );
    
    # ID
    $dbi->update_or_insert(
        {title => 'Perl'},
        table => 'book',
        id => 1,
        primary_key => 'id',
        select_option => {append => 'for update'}
    );

Update or insert.

In both examples, the following SQL is executed.

    # In case insert
    insert into book (id, title) values (?, ?)
    
    # In case update
    update book set (id = ?, title = ?) where book.id = ?

The following opitons are available adding to update option.

select_option

    select_option => {append => 'for update'}

select method option, select method is used to check the row is already exists.

`update_timestamp`

    $dbi->update_timestamp(
      updated_at
        => sub { Time::Piece->localtime->strftime("%Y-%m-%d %H:%M:%S") }
    );

Timestamp value when update method is executed with timestamp option.

If insert_timestamp is set and insert method is executed with timestamp option, column update_at is automatically set to the value like "2010-10-11 10:12:54".

>|perl| $dbi->update($param, table => 'book', timestamp => 1); ||<

`show_datatype`

    $dbi->show_datatype($table);

Show data type of the columns of specified table.

    book
    title: 5
    issue_date: 91

This data type is used in type_rule's from1 and from2.

`show_tables`

    $dbi->show_tables;

Show tables.

`show_typename`

    $dbi->show_typename($table);

Show type name of the columns of specified table.

    book
    title: varchar
    issue_date: date

This type name is used in type_rule's into1 and into2.

`values_clause`

    my $values_clause = $dbi->values_clause({title => 'a', age => 2});

Create values clause.

    (title, author) values (title = :title, age = :age);

You can use this in insert statement.

    my $insert_sql = "insert into book $values_clause";

`where`

    my $where = $dbi->where(
        clause => ['and', 'title = :title', 'author = :author'],
        param => {title => 'Perl', author => 'Ken'}
    );

Create a new DBIx::Custom::Where object.

ENVIRONMENTAL VARIABLES

`DBIX_CUSTOM_DEBUG`

If environment variable DBIX_CUSTOM_DEBUG is set to true, executed SQL and bind values are printed to STDERR.

`DBIX_CUSTOM_DEBUG_ENCODING`

DEBUG output encoding. Default to UTF-8.

DEPRECATED FUNCTIONALITY

DBIx::Custom

    # Attribute methods
    default_dbi_option # will be removed 2017/1/1
    dbi_option # will be removed 2017/1/1
    data_source # will be removed at 2017/1/1
    dbi_options # will be removed at 2017/1/1
    filter_check # will be removed at 2017/1/1
    reserved_word_quote # will be removed at 2017/1/1
    cache_method # will be removed at 2017/1/1
    
    # Methods
    method # will be removed at 2017/1/1
    assign_param # will be removed at 2017/1/1
    update_param # will be removed at 2017/1/1
    insert_param # will be removed at 2017/1/1
    create_query # will be removed at 2017/1/1
    apply_filter # will be removed at 2017/1/1
    select_at # will be removed at 2017/1/1
    delete_at # will be removed at 2017/1/1
    update_at # will be removed at 2017/1/1
    insert_at # will be removed at 2017/1/1
    register_tag # will be removed at 2017/1/1
    default_bind_filter # will be removed at 2017/1/1
    default_fetch_filter # will be removed at 2017/1/1
    insert_param_tag # will be removed at 2017/1/1
    register_tag # will be removed at 2017/1/1
    register_tag_processor # will be removed at 2017/1/1
    update_param_tag # will be removed at 2017/1/1
    
    # Options
    select method where_param option # will be removed 2017/1/1
    delete method where_param option # will be removed 2017/1/1
    update method where_param option # will be removed 2017/1/1
    insert method param option # will be removed at 2017/1/1
    insert method id option # will be removed at 2017/1/1
    select method relation option # will be removed at 2017/1/1
    select method column option [COLUMN, as => ALIAS] format
      # will be removed at 2017/1/1
    execute method's sqlfilter option # will be removed at 2017/1/1
    
    # Others
    execute("select * from {= title}"); # execute method's
                                        # tag parsing functionality
                                        # will be removed at 2017/1/1
    Query caching # will be removed at 2017/1/1

DBIx::Custom::Model

    # Attribute methods
    method # will be removed at 2017/1/1
    filter # will be removed at 2017/1/1
    name # will be removed at 2017/1/1
    type # will be removed at 2017/1/1

DBIx::Custom::Query

    # Attribute methods
    default_filter # will be removed at 2017/1/1
    table # will be removed at 2017/1/1
    filters # will be removed at 2017/1/1
    
    # Methods
    filter # will be removed at 2017/1/1

DBIx::Custom::QueryBuilder

    # Attribute methods
    tags # will be removed at 2017/1/1
    tag_processors # will be removed at 2017/1/1
    
    # Methods
    register_tag # will be removed at 2017/1/1
    register_tag_processor # will be removed at 2017/1/1
    
    # Others
    build_query("select * from {= title}"); # tag parsing functionality
                                            # will be removed at 2017/1/1

DBIx::Custom::Result

    # Attribute methods
    filter_check # will be removed at 2017/1/1
    
    # Methods
    end_filter # will be removed at 2017/1/1
    remove_end_filter # will be removed at 2017/1/1
    remove_filter # will be removed at 2017/1/1
    default_filter # will be removed at 2017/1/1

DBIx::Custom::Tag

    This module is DEPRECATED! # will be removed at 2017/1/1

BACKWARDS COMPATIBILITY POLICY

If a functionality is DEPRECATED, you can know it by DEPRECATED warnings except for attribute method. You can check all DEPRECATED functionalities by document. DEPRECATED functionality is removed after five years, but if at least one person use the functionality and tell me that thing I extend one year each time he tell me it.

EXPERIMENTAL functionality will be changed without warnings.

BUGS

Please tell me bugs if found.

<kimoto.yuki at gmail.com>

http://github.com/yuki-kimoto/DBIx-Custom

AUTHOR

Yuki Kimoto, <kimoto.yuki at gmail.com>

COPYRIGHT & LICENSE

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

3 POD Errors

The following errors were encountered while parsing the POD:

Around line 2742:: You forgot a '=back' before '=head2'
Around line 2974:: Unknown directive: =itme
Around line 3273:: You forgot a '=back' before '=head2'

To install DBIx::Custom, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::Custom

CPAN shell

perl -MCPAN -e shell
install DBIx::Custom

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

DOCUMENTATION

ATTRIBUTES

connector

dsn

default_option

exclude_table

filters

last_sql

models

option

password

query_builder

quote

result_class

safety_character

separator

tag_parse

user

user_column_info

user_table_info

METHODS

available_datatype

available_typename

assign_clause

column

connect

count

create_model

dbh

delete

delete_all

each_column

each_table

execute

get_column_info

get_table_info

helper

insert

include_model

insert_timestamp

like_value

mapper

merge_param

model

mycolumn

new

not_exists

order

register_filter

select

setup_model

type_rule

update

update_all

update_or_insert EXPERIMENTAL

update_timestamp

show_datatype

show_tables

show_typename

values_clause

where

ENVIRONMENTAL VARIABLES

DBIX_CUSTOM_DEBUG

DBIX_CUSTOM_DEBUG_ENCODING

DEPRECATED FUNCTIONALITY

BACKWARDS COMPATIBILITY POLICY

BUGS

AUTHOR

COPYRIGHT & LICENSE

Module Install Instructions

`connector`

`dsn`

`default_option`

`exclude_table`

`filters`

`last_sql`

`models`

`option`

`password`

`query_builder`

`quote`

`result_class`

`safety_character`

`separator`

`tag_parse`

`user`

`user_column_info`

`user_table_info`

`available_datatype`

`available_typename`

`assign_clause`

`column`

`connect`

`count`

`create_model`

`dbh`

`delete`

`delete_all`

`each_column`

`each_table`

`execute`

`get_column_info`

`get_table_info`

`helper`

`insert`

`include_model`

`insert_timestamp`

`like_value`

`mapper`

`merge_param`

`model`

`mycolumn`

`new`

`not_exists`

`order`

`register_filter`

`select`

`setup_model`

`type_rule`

`update`

`update_all`

`update_or_insert EXPERIMENTAL`

`update_timestamp`

`show_datatype`

`show_tables`

`show_typename`

`values_clause`

`where`

`DBIX_CUSTOM_DEBUG`

`DBIX_CUSTOM_DEBUG_ENCODING`