Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
DBIx::Custom(3)	      User Contributed Perl Documentation      DBIx::Custom(3)

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

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

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

   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"

   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.

   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 "ctime" option	and "mtime" option,
       and "update" method's "mtime" 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.

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

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

   create_model
	 $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(...);

   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.

       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.

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

       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]

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

   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.

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

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

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

       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.

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

       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.

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

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

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

       Create DBIx::Custom::Result 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.

   DBIX_CUSTOM_SUPPRESS_DEPRECATION
	 $ENV{DBIX_CUSTOM_SUPPRESS_DEPRECATION}	= '0.25';

       Suppress	deprecation warnings before specified version.

DEPRECATED FUNCTIONALITY
       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

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

POD ERRORS
       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'

perl v5.32.1			  2017-03-30		       DBIx::Custom(3)

NAME | SYNOPSIS | DESCRIPTION | WEB SITE | DOCUMENTS | ATTRIBUTES | METHODS | ENVIRONMENTAL VARIABLES | DEPRECATED FUNCTIONALITY | BACKWARDS COMPATIBILITY POLICY | BUGS | AUTHOR | COPYRIGHT & LICENSE | POD ERRORS

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=DBIx::Custom&sektion=3&manpath=FreeBSD+13.0-RELEASE+and+Ports>

home | help