GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages
DBIx::Custom(3) User Contributed Perl Documentation DBIx::Custom(3)

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

  use DBIx::Custom;
  
  # Connect
  my $dbi = DBIx::Custom->connect(
    "dbi:mysql:database=dbname",
    'ken',
    '!LFKD%$&',
    {mysql_enable_utf8 => 1}
  );
  
  # Create 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'});
  
  # Select, more complex
  #   select book.title as book.title,
  #     book.author as book.author,
  #     comnapy.name as company.name
  #   form book
  #     left outer join company on book.company_id = company.id
  #   where book.author = ?
  #   order by id limit 0, 5
  my $result = $dbi->model('book')->select(
    [
      {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 0, 5'
  );
  
  # Get all rows or only one row
  my $rows = $result->all;
  my $row = $result->one;
  
  # Execute SQL with named place holder
  my $result = $dbi->execute(
    "select id from book where author = :author and title like :title",
    {author => 'ken', title => '%Perl%'}
  );

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

DBIx::Custom - Perl O/R Mapper <http://dbix-custom.hateblo.jp>

DBIx::Custom Documents <https://github.com/yuki-kimoto/DBIx-Custom/wiki>

DBIx::Custom API reference <http://search.cpan.org/~kimoto/DBIx-Custom/>

  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" method.

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.

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

Data source name, used when "connect" method is executed.

  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,
  }

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

Excluded table regex. "each_column", "each_table", "type_rule"

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

Filters, registered by "register_filter" method.

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

Get last succeeded SQL executed by "execute" method.

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

Code reference which return current time, default to the following code reference.

  sub {
    my ($sec, $min, $hour, $mday, $mon, $year) = localtime;
    $mon++;
    $year += 1900;
    return sprintf("%04d-%02d-%02d %02d:%02d:%02d");
  }

This return the time like "2011-10-14 05:05:27".

This is used by "insert" method's "ctime" option and "mtime" option, and "update" method's "mtime" option.

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

Models, included by "include_model" method.

Symbol to specify own columns in select method column option, default to '__MY__'.

  $dbi->table('book')->select({__MY__ => '*'});

  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".

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

Password, used when "connect" method is executed.

  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('[]');

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

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

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

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

  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 ".".

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

User name, used when "connect" method is executed.

  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.

  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.

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

  print $dbi->available_datatype;

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

  print $dbi->available_typename;

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

  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});

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

Create column clause. The following 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"

  # DBI compatible arguments
  my $dbi = DBIx::Custom->connect(
    "dbi:mysql:database=dbname",
    'ken',
    '!LFKD%$&',
    {mysql_enable_utf8 => 1}
  );
  
  # pass DBIx::Custom attributes
  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.

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

Create DBIx::Custom::Model object and initialize model. Model columns attribute is automatically set. You can use this model by using "model" method.

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

You can use model name which different from table name

  $dbi->create_model(name => 'book1', table => 'book');
  $dbi->model('book1')->select(...);

  my $dbh = $dbi->dbh;

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

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

Execute delete statement.

The following options are available.

OPTIONS

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

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.

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

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

  $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" information, 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 { ... });

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

Iterate all table information from 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" information, 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 { ... });

  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 argument 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 an exception, 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 options 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 => [image => DBI::SQL_BLOB]
  bind_type => [[qw/image audio/] => DBI::SQL_BLOB]
    

This is used to bind parameter by "bind_param" of statement 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 "register_filter". This filter is executed before data is saved into database. and before type rule filter is executed.

reuse
  reuse => $hash_ref
    

Reuse query object if the hash reference variable is set.

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

This will improved performance when you want to execute same query repeatedly because generally creating query object is slow.

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 => {worker => 'user'} # {ALIAS => TABLE}
    

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

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.

prepare_attr EXPERIMENTAL
  prepare_attr => {mysql_use_result => 1}
    

Statemend handle attributes, this is DBI's "prepare" method second argument.

query EXPERIMENTAL
  query => 1
    

If you want to get SQL information only except execution, You can get DBIx::Custom::Query object by this option.

  my $query = $dbi->execute(
    "insert into book (id, name) values (:id, :name)",
    {id => 1, name => 'Perl'},
    query => 1
  );
    

DBIx::Custom::Query have the following information

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

You can get bind values and the types by the following way.

  # Build bind values and types
  $query->build;
  
  # Get bind values
  my $bind_values = $query->bind_values;
  
  # Get bind types
  my $bind_value_types = $query->bind_value_types;
    

You can prepare sql and execute SQL by DBI directry.

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

If you know parameters have no duplicate column name, have no filter, you get bind values in the following fastest way.

my $bind_values = [map { $param->{$_} } @columns]

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

get column information except for one which match "exclude_table" pattern.

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

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

get table information except for one which match "exclude" pattern.

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

You can set this value to "user_table_info".

  $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()"}

You can pass multiple parameters, this is very fast.

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

In multiple insert, you can't use "id" option. and only first parameter is used to create sql.

options

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

bulk_insert
  bulk_insert => 1
    

bulk insert is executed if database support bulk insert and multiple parameters is passed to "insert". The SQL like the following one is executed.

  insert into book (id, title) values (?, ?), (?, ?);
    
ctime
  ctime => 'created_time'
    

Created time column name. time when row is created is set to the column. default time format is "YYYY-mm-dd HH:MM:SS", which can be changed by "now" attribute.

prefix
  prefix => 'or replace'
    

prefix before table name section

  insert or replace into book
    
table
  table => 'book'
    

Table name.

mtime
This option is same as "update" method "mtime" option.
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 );
    

  $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');

You can include full-qualified table name like "main.book"

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

  my $main_book = $self->model('main.book');

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

  my $like_value = $dbi->like_value

Code reference which return a value for the like value.

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

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

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

  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.

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

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

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

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

  book.author as author,
  book.title as title

  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.

  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 .

  my $order = $dbi->order;

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

  my $quooted = $dbi->q("title");

Quote string by value of "quote".

  $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');
    }
  );

Register filters, used by "filter" option of many methods.

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

Execute select statement.

You can pass odd number arguments. first argument is "column".

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

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 own column by "__MY__".

  column => [
    {__MY__ => [qw/author title/]},
  ]
    

This is expanded to the following one by using "mycolomn" method.

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

"__MY__" can be changed by "mytable_symbol" attribute.

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

Parameter shown before where clause.

For example, if you want to contain named placeholder 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
  prefix => 'SQL_CALC_FOUND_ROWS'
    

Prefix of column clause

  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 clause or where clause contain table name like "company.name", join clauses 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
  # (1) Hash reference
  where => {author => 'Ken', 'title' => ['Perl', 'Ruby']}
  # -> where author = 'Ken' and title in ('Perl', 'Ruby')
  
  # (2) DBIx::Custom::Where object
  where => $dbi->where(
    clause => ['and', ':author{=}', ':title{like}'],
    param  => {author => 'Ken', title => '%Perl%'}
  )
  # -> where author = 'Ken' and title like '%Perl%'
  
  # (3) Array reference[Array refenrece, Hash reference]
  where => [
    ['and', ':author{=}', ':title{like}'],
    {author => 'Ken', title => '%Perl%'}
  ]
  # -> where author = 'Ken' and title like '%Perl%'
  
  # (4) Array reference[String, Hash reference]
  where => [
    ':author{=} and :title{like}',
    {author => 'Ken', title => '%Perl%'}
  ]
  #  -> where author = 'Ken' and title like '%Perl%'
  
  # (5) String
  where => 'title is null'
  #  -> where title is null
    

Where clause. See also DBIx::Custom::Where to know how to create where clause.

  $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 character, 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 { ... },
    ],
  );

  $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.

prefix
  prefix => 'or replace'
    

prefix before table name section

  update or replace book
    
table
  table => 'book'
    

Table name.

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;
    
mtime
  mtime => 'modified_time'
    

Modified time column name. time row is updated is set to the column. default time format is "YYYY-mm-dd HH:MM:SS", which can be changed by "now" attribute.

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

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

option
  option => {
    select => {
      append => '...'
    },
    insert => {
      prefix => '...'
    },
    update => {
      filter => {}
    }
  }
    

If you want to pass option to each method, you can use "option" option.

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

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

  $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".

  $dbi->show_tables;

Show tables.

  $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".

  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";

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

Create a new DBIx::Custom::Where object. See DBIx::Custom::Where to know how to create where clause.

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

Create DBIx::Custom::Result object.

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

DEBUG output encoding. Default to UTF-8.

  $ENV{DBIX_CUSTOM_SUPPRESS_DEPRECATION} = '0.25';

Suppress deprecation warnings before specified version.

DBIx::Custom

  # Methods
  DBIx::Custom AUTOLOAD feature # will be removed at 2022/5/1
  DBIx::Custom::helper method # will be removed at 2022/5/1
  DBIx::Custom::update_or_insert method is DEPRECATED! # will be removed at 2022/5/1
  DBIx::Custom::count method # will be removed at 2022/5/1
  DBIx::Custom::select,update,delete method's primary_key option is DEPRECATED! # will be removed at 2022/5/1
  DBIx::Custom::select,update,delete method's id option is DEPRECATED! # will be removed at 2022/5/1
  DBIx::Custom::setup method is DEPRECATED! # will be removed at 2022/5/1

DBIx::Custom::Result

  # Options
  kv method's multi option (from 0.28) # will be removed at 2018/3/1

DBIx::Custom::Model

  DBIx::Custom::Model AUTOLOAD feature # will be removed at 2022/5/1
  DBIx::Custom::Model::helper method is DEPRECATED! # will be removed at 2022/5/1
  DBIx::Custom::Model::update_or_insert method is DEPRECATED! # will be removed at 2022/5/1
  DBIx::Custom::Model::count method # will be removed at 2022/5/1
  DBIx::Custom::Model::primary_key attribute is DEPRECATED! # will be removed at 2022/5/1

If a feature is DEPRECATED, you can know it by DEPRECATED warnings. DEPRECATED feature is removed after "five years", but if at least one person use the feature and tell me that thing I extend one year each time he tell me it.

DEPRECATION warnings can be suppressed by "DBIX_CUSTOM_SUPPRESS_DEPRECATION" environment variable.

EXPERIMENTAL features will be changed or deleted without warnings.

Please tell me bugs if you find bug.

"<kimoto.yuki at gmail.com>"

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

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

Copyright 2009-2017 Yuki Kimoto, all rights reserved.

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

Hey! The above document had some coding errors, which are explained below:
Around line 2381:
You forgot a '=back' before '=head2'
Around line 2855:
You forgot a '=back' before '=head2'

You forgot a '=back' before '=head2'

2017-03-30 perl v5.32.1

Search for    or go to Top of page |  Section 3 |  Main Index

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with ManDoc.