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


Manual Reference Pages  -  DBIX::CUSTOM (3)

.ds Aq ’

NAME

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

CONTENTS

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
  #   select title, author from book where author = ?
  my $result = $dbi->select(
    [title, author],
    table  => book,
    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->select(
    [
      {book => [qw/title author/]},
      {company => [name]}
    ],
    table  => book,
    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.
  my $result = $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.
o Execute insert, update, delete, or select statement easily
o Create where clause flexibly
o Named place holder support
o Model support
o Connection manager support
o Choice your favorite relational database management system, MySQL, SQLite, PostgreSQL, Oracle, Microsoft SQL Server, Microsoft Access, DB2 or anything,
o Filtering by data type or column name
o Create order by clause flexibly

WEB SITE

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

DOCUMENTS

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

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

ATTRIBUTES

    async_conf EXPERIMENTAL



  my $async_conf = $dbi->async_conf;
  $dbi = $dbi->async_conf($conf);



Setting when async option is used.



  # MySQL
  $dbi->async_conf({
    prepare_attr => {async => 1},
    fh => sub { shift->dbh->mysql_fd }
    my $dbi = shift;
  });



prepare_attr is DBI’s prepare method second argument, fh is callback that return file handle to watch.

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

    default_schema EXPERIMETNAL



  my $default_schema = $self->default_schema;
  $dbi = $self->default_schema(public);



schema name. if database has multiple schema, type_rule->{into} filter don’t work well.

If you set default_schema, type_rule->{into} filter work well.

    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 succeeded SQL executed by execute method.

    (EXPERIMENTAL) last_sth



  my $last_sth = $dbi->last_sth;



Get last executed statement handle.

    now



  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 created_at option and updated_at option, and update method’s updated_at option.

    models



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



Models, included by include_model method.

    mytable_symbol

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



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



    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 ’a-zA-Z_’. Note that you don’t have to specify like ’[a-zA-Z_]’.

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



    async EXPERIMENTAL (Currently, Only work in MySQL)



  async => sub {
    my ($dbi, $result) = @_;
    ...
  };



Database async access. AnyEvent is required.

This is mysql async access example.



  use AnyEvent;

  my $cond = AnyEvent->condvar;

  my $timer = AnyEvent->timer(
    interval => 1,
    cb => sub { 1 }
  );

  my $count = 0;

  $dbi->execute(SELECT SLEEP(1), 3, undef,
    prepare_attr => {async => 1}, statement => select,
    async => sub {
      my ($dbi, $result) = @_;
      my $row = $result->fetch_one;
      is($row->[1], 3, before);
      $cond->send if ++$count == 2;
    }
  );

  $dbi->select(key1, table => table1, prepare_attr => {async => 1},
    async => sub {
      my ($dbi, $result) = @_;
      my $row = $result->fetch_one;
      is($row->[0], 1, after1);
      $dbi->select(key1, table => table1, prepare_attr => {async => 1},
        async => sub {
          my ($dbi, $result) = @_;
          my $row = $result->fetch_one;
          is($row->[0], 1, after2);
          $cond->send if ++$count == 2;
        }
      )
    }
  );

  $cond->recv;



    column



  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"



    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 options are available.

<B>OPTIONSB>

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 following 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 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 { ... });



    each_table



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



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



<B>OPTIONSB>

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.

prepare_attr EXPERIMENTAL


  prepare_attr => {async => 1}



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

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

query


  query => 1



execute method return hash reference which contain SQL and column information



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



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.

primary_key


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



Priamry key. This is used for id option.

select EXPERIMETAL


  select => 1



If you set select to 1, this statement become select statement and return value is always DBIx::Custom::Result object.

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.

    get_column_info



  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 => {...}}
  ]



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

    helper



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



Register helper. These helper is called directly from DBIx::Custom object.



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



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.

<B>optionsB>

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.

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

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



    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.

<B>MyModel.pmB>



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



Model modules, extending name space module.

<B>MyModel/book.pmB>



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



<B>MyModel/company.pmB>



  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.

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

    q



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



Quote string by value of quote.

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



Register filters, used by filter option of many methods.

    select



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



<B>OPTIONSB>

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.

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

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



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



<B>OPTIONSB>

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

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.

    update_all



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



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

    update_or_insert



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



Update or insert.

update_or_insert method execute select method first to find row. If the row is exists, update is executed. If not, insert is executed.

OPTIONS

update_or_insert method use all common option in select, update, delete, and has the following new ones.
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.

    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. See DBIx::Custom::Where to know how to create where clause.

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.

    DBIX_CUSTOM_DISABLE_MODEL_EXECUTE

If you set DBIX_CUSTOM_DISABLE_MODEL_EXECUTE to 1, DBIx::Custom::Model execute method call DBIx::Custom execute.

    DBIX_CUSTOM_SUPPRESS_DEPRECATION



  $ENV{DBIX_CUSTOM_SUPPRESS_DEPRECATION} = 0.25;



Suppress deprecation warnings before specified version.

    DBIX_CUSTOM_TAG_PARSE

If you set DBIX_CUSTOM_TAG_PARSE to 0, tag parsing is off.

DEPRECATED FUNCTIONALITY

DBIx::Custom



  # Attribute methods
  tag_parse # will be removed 2017/1/1
  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
  update_timestamp # will be removed at 2017/1/1
  insert_timestamp # will be removed at 2017/1/1
  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
  insert method created_at option # will be removed 2017/3/1
  update method updated_at option # will be removed 2017/3/1
  select column option [COLUMN => ALIAS] syntax # will be removed 2017/1/1
  execute method id option # will be removed 2017/1/1
  update timestamp option # will be removed 2017/1/1
  insert timestamp option # will be removed 2017/1/1
  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 methods sqlfilter option # will be removed at 2017/1/1
 
  # Others
  execute($query, ...) # execute method receiving query object.
                       # this is removed at 2017/1/1
  execute("select * from {= title}"); # execute methods
                                      # tag parsing functionality
                                      # will be removed at 2017/1/1
  Query caching # will be removed at 2017/1/1



DBIx::Custom::Model



  # Attribute methods
  execute # will be removed at 2017/1/1
  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

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



  # 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

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



  # 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
  column (from 0.25) # will be removed at 2017/2/1
  fetch_first # will be removed at 2017/2/1
  fetch_hash_first # will be removed 2017/2/1
  filter_on # will be removed at 2017/1/1
  filter_off # will be removed at 2017/1/1
  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
 
  # Options
  kv methods multi option (from 0.28) # will be removed at 2018/3/1



DBIx::Custom::Tag



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



DBIx::Custom::Order



  # Other
  prepend method array reference receiving
    $order->prepend([book, desc]); # will be removed 2017/1/1



BACKWARDS COMPATIBILITY POLICY

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 without warnings.

BUGS

Please tell me bugs if you find bug.

<kimoto.yuki at gmail.com>

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

AUTHOR

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

COPYRIGHT & LICENSE

Copyright 2009-2013 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.

POD ERRORS

Hey! <B>The above document had some coding errors, which are explained below:B>
Around line 3176: You forgot a ’=back’ before ’=head2’
Around line 3727: You forgot a ’=back’ before ’=head2’

You forgot a ’=back’ before ’=head2’

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


perl v5.20.3 DBIX::CUSTOM (3) 2015-01-13

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