1 contributor
- =encoding utf8
-
- =head1 NAME
-
- DBIx::Custom::Guide - DBIx::Custom Guide
-
- =head1 GUIDE
-
- (This guide will be completed nearly future and contains EXPERIMENTAL features
- The features marked EXPERIMENTAL in POD of L<DBIx::Custom> is
- EXPERIMENTAL ones)
-
- L<DBIx::Custom> is the class to make easy to execute SQL.
- This is L<DBI> wrapper class like L<DBIx::Class> or L<DBIx::Simple>.
- You can do thing more easy than L<DBIx::Class>, more flexible
- than L<DBIx::Simple>.
-
- L<DBIx::Custom> is not O/R mapper, O/R mapper is usefule, but
- you must learn many things. Created SQL is sometimes inefficient,
- and in many cases you create raw SQL because
- O/R mapper can't make complex SQL
-
- L<DBIx::Custom> is opposit of O/R mapper.
- The main purpose is that we respect SQL
- and make easy difficult works if you use only L<DBI>.
- If you already learn SQL, it is easy to use L<DBIx::Custom>.
-
- I explain L<DBIx::Custom> a little in this section.
- In L<DBIx::Custom>, you embbed tag in SQL.
-
- select * from book where {= title} and {=author};
-
- The part arround {} is tag.
- This SQL is converted to the one which contains place holder.
-
- select * from book where title = ? and author = ?;
-
- Maybe you ask me that this conversion is meaningful.
- On the top of this, usuful features is implemented.
- See the following descriptions.
-
- =over 4
-
- =item 1. Specify place holder binding value as hash refernce
-
- If you use L<DBI>, you must specify place holder binding value
- as array.
-
- $sth->execute(@bind);
-
- If you use L<DBIx::Custom>, you specify it as hash reference.
-
- my $param = {title => 'Perl', author => 'Ken'};
- $dbi->execute($sql, $param);
-
- =item 2. Filtering
-
- L<DBIx::Custom> provides filtering system.
- For example, You think that about date value you want to
- manipulate it as date object like L<Time::Piece> in Perl,
- and want to convert it to database DATE format.
- and want to do reverse.
-
- You can use filtering system.
-
- At first, register filter.
-
- $dbi->register_filter(
- tp_to_date => sub {
- ...
- },
- date_to_tp => sub {
- ...
- }
- );
-
- next, apply this filter to each column.
-
- $dbi->apply_filter('book',
- 'issue_date' => {out => 'tp_to_date', in => 'date_to_tp'}
- );
-
- C<out> is perl-to-database way. C<in> is perl-from-database way.
-
- This filter is automatically enabled in many method.
-
- $dbi->insert(table => 'book', param => {issue_date => $tp});
-
-
- =item 3. Selective search condition
-
- It is difficult to create selective where clause in L<DBI>.
- For example, If C<title> and C<author> is specified, we create
- the following SQL.
-
- select * from book where title = ? and author = ?;
-
- If only C<title> is specified, the following one
-
- select * from book where title = ?;
-
- If only C<author> is specified, the following one,
-
- select * from book where author = ?;
-
- This is hard work. Generally we use modules like L<SQL::Abstract>.
- L<DBIx::Custom> prepare the way to make it easy.
-
- # Where object
- my $where = $dbi->where;
-
- # Search condition
- $where->clause(
- ['and', '{= title}', {'= author'}]
- );
-
- # Setting to automatically select needed column
- $where->param({title => 'Perl'});
-
- # Embbed where clause to SQL
- my $sql = "select * from book $where";
-
- You can create where clause which has selected search condition.
- You can write nesting of where clause and C<or> condition
-
- =item 4. Methods for insert, update, delete, select
-
- L<DBIx::Custom> provides methods for insert, update, delete, select
- There are C<insert()>, C<update()>, C<delete()>,C<select()>.
-
- my $param = {title => 'Perl', author => 'Ken'};
- $dbi->insert(table => 'book', param => $param);
-
- =item 5. Register method for table.
-
- You can register method for table.
-
- $dbi->table('book')->method(
- list => sub {
- ...
- },
- something => sub {
- ...
- }
- );
-
- use the mehtod.
-
- $dbi->table('book')->list;
-
- Many O/R mapper must create class for table,
- but L<DBIx::Custom> make it easy.
-
- =back
-
- L<DBIx::Custom> is very useful.
- See the following if you are interested in it.
-
- =head2 1. Connect to database
-
- Load L<DBIx::Custom>.
-
- use DBIx::Custom;
-
- use C<connect()> to connect to database.
- Return value is L<DBIx::Custom> object.
-
- my $dbi = DBIx::Custom->connect(
- data_source => "dbi:mysql:database=bookstore",
- user => 'ken',
- password => '!LFKD%$&',
- dbi_options => {mysql_enable_utf8 => 1}
- );
-
- C<data_source> must be one corresponding to the database system.
- The following ones are data source example.
-
- B<MySQL>
-
- "dbi:mysql:database=$database"
- "dbi:mysql:database=$database;host=$hostname;port=$port"
-
- B<SQLite>
-
- "dbi:SQLite:dbname=$database"
- "dbi:SQLite:dbname=:memory:"
-
- B<PostgreSQL>
-
- "dbi:Pg:dbname=$dbname"
-
- B<Oracle>
-
- "dbi:Oracle:$dbname"
- "dbi:Oracle:host=$host;sid=$sid"
-
- B<ODBC(Microsoft Access)>
-
- "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=hoge.mdb"
-
- B<ODBC(SQL Server)>
-
- "dbi:ODBC:driver={SQL Server};Server=(local);database=test;Trusted_Connection=yes;AutoTranslate=No;"
-
- If authentication is needed, you can specify C<user> and C<password>
-
- L<DBIx::Custom> is wrapper class of L<DBI>.
- You can use all methods of L<DBI> from L<DBIx::Custom> object.
-
- $dbi->do(...);
- $dbi->begin_work;
-
- use C<dhb()> to get database handle of L<DBI>
-
- my $dbh = $dbi->dbh;
-
- By default, the following ones is set to database handle attributes.
-
- RaiseError -> 1
- PrintError -> 0
- AutoCommit -> 1
-
- If fatal error occuer, program terminate.
- If SQL is executed, commit is executed automatically.
-
- =head2 2. Methods for insert, update, delete, or insert
-
- There are following methods.
-
- =head3 C<insert()>
-
- use C<insert()> to insert row into database
-
- $dbi->insert(table => 'book',
- param => {title => 'Perl', author => 'Ken'});
-
- C<table> is table name, C<param> is insert data.
-
- Following SQL is executed.
-
- insert into (title, author) values (?, ?);
-
- =head3 C<update()>
-
- use C<update()> to update row in database.
-
- $dbi->update(table => 'book',
- param => {title => 'Perl', author => 'Ken'},
- where => {id => 5});
-
- C<table> is table name, C<param> is update data, C<where> is condition.
-
- Following SQL is executed.
-
- update book set title = ?, author = ?;
-
- You can't execute C<update()> without C<where> for safety.
- use C<update_all()> if you want to update all rows.
-
- $dbi->update_all(table => 'book',
- param => {title => 'Perl', author => 'Ken'});
-
- =head3 C<delete()>
-
- use C<delete()> to delete rows from database.
-
- $dbi->delete(table => 'book',
- where => {author => 'Ken'});
-
- C<table> is table name, C<where> is condition.
-
- Following SQL is executed.
-
- delete from book where id = ?;
-
- You can't execute C<delete()> without C<where> for safety.
- use C<delete_all()> if you want to delete all rows.
-
- $dbi->delete_all(table => 'book');
-
- =head3 C<select()>
-
- use C<select()> to select rows from database
-
- my $result = $dbi->select(table => 'book');
-
- Following SQL is executed.
-
- select * from book;
-
- Return value is L<DBIx::Custom::Result> object.
- use C<fetch()> to fetch row.
-
- while (my $row = $result->fetch) {
- my $title = $row->[0];
- my $author = $row->[1];
- }
-
- See L<3. Fetch row/"3. Fetch row"> about L<DBIx::Custom::Result>.
-
- Continue more examples.
-
- my $result = $dbi->select(
- table => 'book',
- column => ['author', 'title'],
- where => {author => 'Ken'}
- );
-
- C<column> is column names, C<where> is condition.
-
- Following SQL is executed.
-
- select author, title from book where author = ?;
-
- Next example.
-
- my $result = $dbi->select(
- table => 'book',
- column => ['company.name as company__name']
- where => {'book.name' => 'Perl'},
- join => ['left outer join company on book.company_id = company.id]
- );
-
- You can join table by C<join>.
-
- Following SQL is executed.
-
- select company.name as company__name
- from book
- left outer join company on book.company_id = company.id
- where book.name = ?;
-
- company_if of book and id of company is left outer joined.
-
- Note that only when C<where> or C<column> contain table name,
- C<join> is joined.
- if you specify the following option, C<join> is not joined
- because C<join> is not needed.
-
- my $result = $dbi->select(
- table => 'book',
- where => {'name' => 'Perl'},
- join => ['left outer join company on book.company_id = company.id]
- );
-
- Following SQL is executeed.
-
- select * from book where book.name = ?;
-
- You can specify column names easily using C<mycolumn()> and C<column()>.
-
- my $result = $dbi->select(
- table => 'book',
- column => [
- $dbi->mycolumn('book' => ['name']),
- $dbi->column('company' => ['id', 'name'])
- ],
- join => ['left outer join company on book.company_id = company.id]
- );
-
- The following SQL is executed.
-
- select book.name as name,
- company.id as comapny__id,
- company.name as company__name
- from book
- left outer join company on book.company_id = company.id
-
- Next example.
-
- my $result = $dbi->select(
- table => 'book',
- where => {author => 'Ken'},
- append => 'for update',
- );
-
- C<append> is string appending to end of SQL.
-
- Following SQL is executed.
-
- select * book where author = ? for update;
-
- C<appned> is also used at C<insert()>, C<update()>, C<update_all()>
- C<delete()>, C<delete_all()>, and C<select()>.
-
- =head3 C<execute()>
-
- use C<execute()> to execute SQL
-
- $dbi->execute("select * from book;");
-
- Process tag and execute SQL.
-
- $dbi->execute(
- "select * from book {= title} and {= author};"
- param => {title => 'Perl', author => 'Ken'}
- );
-
- Following SQL is executed.
-
- select * from book title = ? and author = ?;
-
- Values of title and author is embbdeded into placeholder.
-
- See L<5. Tag/"5. Tag"> about tag.
-
- You don't have to wirte last semicolon in C<execute()>.
-
- $dbi->execute('select * from book');
-
- =head3 insert by using primary key : C<insert_at()>
-
- To insert row by using primary key, use C<insert_at()>
-
- $dbi->insert_at(
- table => 'book', primary_key => ['id'],
- where => ['123'], param => {name => 'Ken'}
- );
-
- In this example, row which id column is 123 is inserted.
- NOTE that you must pass array reference as C<where>.
- If C<param> contains primary key, the key and value is delete from C<param>.
-
- =head3 Update by using primary key : C<update_at()>
-
- To update row by using primary key, use C<update_at()>
-
- $dbi->update_at(
- table => 'book', primary_key => ['id'],
- where => ['123'], param => {name => 'Ken'}
- );
-
- In this example, row which id column is 123 is updated.
- NOTE that you must pass array reference as C<where>.
- If C<param> contains primary key, the key and value is delete from C<param>.
-
- =head3 Delete by using primary key : C<delete_at()>
-
- To delete row by using primary key, use C<delete_at()>
-
- $dbi->delete_at(table => 'book', primary_key => ['id'], where => ['123']);
-
- In this example, row which id column is 123 is deleted.
- NOTE that you must pass array reference as C<where>.
-
- You can also write arguments like this.
-
- $dbi->delete_at(table => 'book', primary_key => ['id'], param => {id => '123'});
-
- =head3 Select by using primary key : C<select_at()>
-
- To select row by using primary key, use C<select_at()>.
-
- $dbi->select_at(table => 'book', primary_key => ['id'], where => ['123']);
-
- In this example, row which id colunm is 123 is selected.
- NOTE that you must pass array reference as C<where>.
-
- You can also write arguments like this.
-
- $dbi->select_at(table => 'book', primary_key => ['id'], param => {id => '123'});
-
- =head2 3. Fetch row
-
- Return value of C<select()> is L<DBIx::Custom::Result> object.
- There are many methods to fetch row.
-
- =head3 Fetch a row (array) : C<fetch()>
-
- use C<fetch()> to fetch a row and assign it into array reference.
-
- my $row = $result->fetch;
-
- You can get all rows.
-
- while (my $row = $result->fetch) {
- my $title = $row->[0];
- my $author = $row->[1];
- }
-
- =head3 Fetch only first row (array) : C<fetch_first()>
-
- use C<fetch_first()> to fetch only first row.
-
- my $row = $result->fetch_first;
-
- You can't fetch rest rows
- because statement handle C<finish()> is executed.
-
- =head3 Fetch rows (array) : C<fetch_multi()>
-
- use C<fetch_multi()> to fetch rows and assign it into
- array reference which has array references as element.
-
- while (my $rows = $result->fetch_multi(2)) {
- my $title0 = $rows->[0][0];
- my $author0 = $rows->[0][1];
-
- my $title1 = $rows->[1][0];
- my $author1 = $rows->[1][1];
- }
-
- Specify row count as argument.
-
- You can get the following data.
-
- [
- ['Perl', 'Ken'],
- ['Ruby', 'Mark']
- ]
-
- =head3 Fetch all rows (array) : C<fetch_all>
-
- use C<fetch_all()> to fetch all rows and assign it into
- array reference which has array reference as element.
-
- my $rows = $result->fetch_all;
-
- You can get the following data.
-
- [
- ['Perl', 'Ken'],
- ['Ruby', 'Mark']
- ]
-
- =head3 Fetch a row (hash) : C<fetch_hash()>
-
- use C<fetch_hash()> to fetch a row and assign it into hash reference.
-
- while (my $row = $result->fetch_hash) {
- my $title = $row->{title};
- my $author = $row->{author};
- }
-
- =head3 Fetch only first row (hash) : C<fetch_hash_first()>
-
- use C<fetch_hash_first()> to fetch only first row
- and assign it into hash reference.
-
- my $row = $result->fetch_hash_first;
-
- You can't fetch rest rows
- because statement handle C<finish()> is executed.
-
- =head3 Fetch rows (hash) : C<fetch_hash_multi()>
-
- use C<fetch_hash_multi()> to fetch rows and
- assign it into array reference which has hash references as element.
-
- while (my $rows = $result->fetch_hash_multi(5)) {
- my $title0 = $rows->[0]{title};
- my $author0 = $rows->[0]{author};
- my $title1 = $rows->[1]{title};
- my $author1 = $rows->[1]{author};
- }
-
- Specify row count as argument.
-
- You can get the following data.
-
- [
- {title => 'Perl', author => 'Ken'},
- {title => 'Ruby', author => 'Mark'}
- ]
-
- =head3 Fetch all rows (hash) : C<fetch_hash_all()>
-
- use C<fetch_hash_all()> to fetch all rows and
- assign it into array reference which has hash
- references as element.
-
- my $rows = $result->fetch_hash_all;
-
- You can get the following data.
-
- [
- {title => 'Perl', author => 'Ken'},
- {title => 'Ruby', author => 'Mark'}
- ]
-
- =head3 Statement handle : C<sth()>
-
- use <sth()> to get statement handle.
-
- my $sth = $result->sth;
-
- =head2 4. Filtering
-
- L<DBIx::Custom> provide value filtering.
- For example, You maybe want to convert L<Time::Piece> object to
- database date format when register data into database.
- and convert database date fromat to L<Time::Piece> object
- when get data from database.
-
- =head3 Register filter : C<register_filter()>
-
- use C<register_filter()> to register filter.
-
- $dbi->register_filter(
- # Time::Piece object to DATE format
- tp_to_date => sub {
- my $date = shift;
-
- return '0000-00-00' unless $tp;
- return $tp->strftime('%Y-%m-%d');
- },
-
- # DATE to Time::Piece object
- date_to_tp => sub {
- my $date = shift;
-
- return if $date eq '0000-00-00';
- return Time::Piece->strptime($date, '%Y-%m-%d');
- },
- );
-
- Registered filter is used by C<apply_filter()> or etc.
-
- =head3 Apply filter : C<apply_filter()>
-
- use C<apply_filter()> to apply registered filter.
-
- $dbi->apply_filter('book',
- issue_date => {out => 'tp_to_date', in => 'date_to_tp'},
- first_issue_date => {out => 'tp_to_date', in => 'date_to_tp'}
- );
-
- First argument is table name. Arguments after first argument are pairs of column
- name and fitering rule. C<out> of filtering rule is filter which is used when data
- is send to database. C<in> of filtering rule is filter which is used when data
- is got from database.
-
- You can specify code reference as filter.
-
- issue_date => {out => sub { ... }, in => sub { ... }}
-
- Applied filter become effective at insert()>, C<update()>, C<update_all()>,
- C<delete()>, C<delete_all()>, C<select()>.
-
- my $tp = Time::Piece->strptime('2010/10/14', '%Y/%m/%d');
- my $result = $dbi->select(table => 'book', where => {issue_date => $tp});
-
- When data is send to database, L<Time::Piece> object is converted
- to database date format "2010-10-14"
-
- When data is fetched, database date format is
- converted to L<Time::Piece> object.
-
- my $row = $resutl->fetch_hash_first;
- my $tp = $row->{issue_date};
-
- You can also use column name which contains table name.
-
- $dbi->select(
- table => 'book',
- where => {'book.issue_date' => $tp}
- );
-
- In fetching, Filter is effective if you use "TABLE__COLUMN" as column name.
-
- my $result = $dbi->execute(
- "select issue_date as book__issue_date from book");
-
- You can apply C<end> filter execute after C<in> filter.
-
- $dbi->apply_filter('book',
- issue_date => {out => 'tp_to_date', in => 'date_to_tp',
- end => 'tp_to_displaydate'},
- );
-
- =head3 Individual filter C<filter>
-
- You can apply individual filter .
- This filter overwrite the filter by C<apply_filter()>
-
- use C<filter> option to apply individual filter
- when data is send to database.
- This option is used at C<insert()>, C<update()>,
- C<update_all()>, C<delete()>, C<delete_all()>, C<select()>,
- C<execute()>.
-
- C<insert()> example:
-
- $dbi->insert(
- table => 'book',
- param => {issue_date => $tp, first_issue_date => $tp},
- filter => {issue_date => 'tp_to_date', first_issue_date => 'tp_to_date'}
- );
-
- C<execute()> example:
-
- my $sql = <<"EOS";
- select YEAR(issue_date) as issue_year
- from book
- where YEAR(issue_date) = {? issue_year}
- EOS
-
- my $result = $dbi->execute(
- $sql,
- param => {issue_year => '2010'},
- filter => {issue_year => 'tp_to_year'}
- );
-
- You can also apply indivisual filter when you fetch row.
- use C<DBIx::Custom::Result>'s C<filter()>.
-
- $result->filter(issue_year => 'year_to_tp');
-
- You can remove filter by C<remove_filter()>
-
- $result->remove_filter;
-
- =head3 End filtering : C<end_filter()>
-
- You can add filter at end.
- It is useful to create last output.
- use C<end_filter()> to add end filter.
-
- $result->end_filter(issue_date => sub {
- my $tp = shift;
-
- return '' unless $tp;
- return $tp->strftime('%Y/%m/%d %h:%m:%s (%a)');
- });
-
- In this example, L<Time::Piece> object is converted to readable format.
-
- You can remove end_filter by C<end_filter>
-
- $result->remove_end_filter;
-
- =head3 Automate applying filter : C<each_column()>
-
- It is useful to apply filter automatically at date type columns.
- You can use C<each_column()> to process all column infos.
-
- $dbi->each_column(
- sub {
- my ($self, $table, $column, $info) = @_;
-
- my $type = $info->{TYPE_NAME};
-
- my $filter = $type eq 'DATE' ? {out => 'tp_to_date', in => 'date_to_tp'}
- : $type eq 'DATETIME' ? {out => 'tp_to_datetime', in => 'datetime_to_tp'}
- : undef;
-
- $self->apply_filter($table, $column, $filter)
- if $filter;
- }
- );
-
- C<each_column()> receive callback.
- callback arguments are L<DBIx::Custom> object, table name, column name, column information.
- Filter is applied automatically by column type.
-
- =head2 5. Tag
-
- =head3 Basic of Tag
-
- You can embedd tag into SQL.
-
- select * from book where {= title} and {like author};
-
- {= title} and {like author} are tag. Tag has the folloring format.
-
- {TAG_NAME ARG1 ARG2 ...}
-
- Tag start C<{> and end C<}>.
- Don't insert space between C<{}> and tag name.
-
- C<{> and C<}> are reserved word.
- If you want to use these, escape it by '\';
-
- select from book \\{ ... \\}
-
- \ is perl's escape character, you need two \.
-
- Tag is expanded before executing SQL.
-
- select * from book where title = ? and author like ?;
-
- use C<execute()> to execute SQL which contains tag
-
- my $sql = "select * from book where {= author} and {like title};"
- $dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'});
-
- You can specify values embedded into place holder as hash reference using
- C<param> option.
-
- You can specify C<filter()> at C<execute()>.
-
- $dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'}
- filter => {title => 'to_something');
-
- Note that at C<execute()> the filter applied by C<apply_filter()>
- don't has effective to columns.
- You have to use C<table> option
-
- $dbi->execute($sql, table => ['author', 'book']);
-
- =head3 Tag list
-
- The following tag is available.
-
- =head4 C<?>
-
- {? NAME} -> ?
-
- =head4 C<=>
-
- {= NAME} -> NAME = ?
-
- =head4 C<E<lt>E<gt>>
-
- {<> NAME} -> NAME <> ?
-
- =head4 C<E<lt>>
-
- {< NAME} -> NAME < ?
-
- =head4 C<E<gt>>
-
- {> NAME} -> NAME > ?
-
- =head4 C<E<gt>=>
-
- {>= NAME} -> NAME >= ?
-
- =head4 C<E<lt>=>
-
- {<= NAME} -> NAME <= ?
-
- =head4 C<like>
-
- {like NAME} -> NAME like ?
-
- =head4 C<in>
-
- {in NAME COUNT} -> NAME in [?, ?, ..]
-
- =head4 C<insert_param>
-
- {insert_param NAME1 NAME2} -> (NAME1, NAME2) values (?, ?)
-
- =head4 C<update_param>
-
- {update_param NAME1 NAME2} -> set NAME1 = ?, NAME2 = ?
-
- =head3 Manipulate same name's columns
-
- It is ok if there are same name's columns.
- Let's think two date comparison.
-
- my $sql = "select * from table where {> date} and {< date};";
-
- In this case, You specify paramter values as array reference.
-
- my $dbi->execute($sql, param => {date => ['2010-10-01', '2012-02-10']});
-
- =head3 Register Tag : C<register_tag()>
-
- You can register custom tag.
- use C<register_tag()> to register tag.
-
- $dbi->register_tag(
- '=' => sub {
- my $column = shift;
-
- return ["$column = ?", [$column]];
- }
- );
-
- This is implementation of C<=> tag.
- Tag format is the following one.
-
- {TAG_NAME ARG1 ARG2 ...}
-
- In case C<=> tag. Format is
-
- {= title}
-
- So subroutine receive one argument "title".
- You have to return array reference in the following format.
-
- [
- String after expanding,
- [COLUMN1(This is used for place holder), COLUMN2 , ...]
- ]
-
- First element is expanded stirng. In this example,
-
- 'title = ?'
-
- Secount element is array reference which is used to embedd value to
- place holder. In this example,
-
- ['title']
-
- If there are more than one placeholders,
- This elements is multipul.
-
- You return the following array reference.
-
- ['title = ?', ['title']]
-
- See source of L<DBIx::Custom::Tag> to see many implementation.
-
- =head2 6. Dinamically create where clause
-
- =head3 Dinamically create where clause : where()
-
- You want to search multiple conditions in many times.
- Let's think the following three cases.
-
- Case1: Search only C<title>
-
- where {= title}
-
- Case2: Search only C<author>
-
- where {= author}
-
- Case3: Search C<title> and C<author>
-
- where {= title} and {=author}
-
- L<DBIx::Custom> support dinamic where clause creating.
- At first, create L<DBIx::Custom::Where> object by C<where()>.
-
- my $where = $dbi->where;
-
- Set clause by C<clause()>
-
- $where->clause(
- ['and', '{= title'}, '{= author}']
- );
-
- C<clause> is the following format.
-
- ['or' or 'and', TAG1, TAG2, TAG3]
-
- First argument is 'or' or 'and'.
- Later than first argument are tag names.
-
- You can write more complex format.
-
- ['and',
- '{= title}',
- ['or', '{= author}', '{like date}']
- ]
-
- This mean "{=title} and ( {=author} or {like date} )".
-
- After setting C<clause>, set C<param>.
-
- $where->param({title => 'Perl'});
-
- In this example, parameter contains only title.
-
- If you execute C<string_to()>, you can get where clause
- which contain only parameter name.
-
- my $where_clause = $where->to_string;
-
- Parameter name is only title, the following where clause is created.
-
- where {= title}
-
- You can also create where clause by stringification.
-
- my $where_clause = "$where";
-
- This is useful to embbed it into SQL.
-
- =head3 In case where clause contains same name columns
-
- Even if same name tags exists, you can create where clause.
- Let's think that there are starting date and ending date.
-
- my $param = {start_date => '2010-11-15', end_date => '2011-11-21'};
-
- In this case, you set parameter value as array reference.
-
- my $p = {date => ['2010-11-15', '2011-11-21']};
-
- You can embbed these values into same name tags.
-
- $where->clause(
- ['and', '{> date}', '{< date}']
- );
- $where->param($p);
-
- If starting date isn't exists, create the following parameter.
-
- my $p = {date => [$dbi->not_exists, '2011-11-21']};
-
- You can get DBIx::Custom::NotExists object by C<not_exists()>
- This mean correnspondinf value isn't exists.
-
- If ending date isn't exists, create the following parameter.
-
- my $p = {date => ['2010-11-15']};
-
- If both date isn't exists, create the following parameter.
-
- my $p = {date => []};
-
- This logic is a little difficut. See the following ones.
-
- my @date;
- push @date, exists $param->{start_date} ? $param->{start_date}
- : $dbi->not_exists;
- push @date, $param->{end_date} if exists $param->{end_date};
- my $p = {date => \@date};
-
- =head3 With C<select()>
-
- You can pass L<DBIx::Custom::Where> object to C<where> of C<select()>.
-
- my $where = $dbi->where;
- $where->clause(['and', '{= title}', '{= author}']);
- $where->param({title => 'Perl'});
- my $result = $dbi->select(table => 'book', where => $where);
-
- You can also pass it to C<where> of C<update()>�AC<delete()>
-
- =head3 With C<execute()>
-
- L<DBIx::Custom::Where> object is embedded into SQL.
-
- my $where = $dbi->where;
- $where->clause(['and', '{= title}', '{= author}']);
- $where->param({title => 'Perl'});
-
- my $sql = <<"EOS";
- select * from {table book};
- $where
- EOS
-
- $dbi->execute($sql, param => $param);
-
- =head2 7. Model
-
- =head3 Model
-
- you can define model extending L<DBIx::Custom::Model>
- to improve source code view.
-
- At first, you create basic model class extending <DBIx::Custom::Model>.
-
- package MyModel;
-
- use base 'DBIx::Custom::Model';
-
- Next, you create each model classes.
-
- MyModel::book
-
- package MyModel::book;
-
- use base 'MyModel';
-
- sub insert { ... }
- sub list { ... }
-
- MyModel::company
-
- package MyModel::company;
-
- use base 'MyModel';
-
- sub insert { ... }
- sub list { ... }
-
- The follwoing modules location is needed.
-
- MyModel.pm
- MyModel / book.pm
- / company.pm
-
- You can include these models by C<include_model()>
-
- $dbi->include_model('MyModel');
-
- First argument is name space of model.
-
- You can use model like this.
-
- my $result = $dbi->model('book')->list;
-
- In mode, You can use such as methods,
- C<insert()>, C<update()>, C<update_all()>,
- C<delete()>, C<delete_all()>, C<select()>
- without C<table> option.
-
- $dbi->model('book')->insert(param => $param);
-
- Model is L<DBIx::Custom::Model>.
-
- If you need table name�Ayou can get it by C<table()>.
-
- my $table = $model->table;
-
- You can get L<DBIx::Custom>.
-
- my $dbi = $model->dbi;
-
- You can also call all methods of L<DBIx::Custom> and L<DBI>.
-
- # DBIx::Custom method
- $model->execute($sql);
-
- # DBI method
- $model->begin_work;
- $model->commit;
-
- If you want to get all models, you can get them by keys of C<models()>.
-
- my @models = keys %{$self->models};
-
- You can set primary key to model.
-
- $model->primary_key(['id', 'number_id']);
-
- Primary key is used by C<insert_at>, C<update_at()>, C<delete_at()>,
- C<select_at()>.
-
- by C<filter> you can define filters applied by C<apply_filter()>
-
- $model->filter({
- title => {out => ..., in => ..., end => ...},
- author => {out => ..., in => ..., end => ...}
- });
-
- This filters is applied when C<include_model()> is called.
-
- You can set column names
-
- $model->columns(['id', 'number_id']);
-
- Column names is automarically set by C<setup_model()>.
- This method is needed to be call after C<include_model()>.
-
- $dbi->setup_model;
-
- You can set C<join>
-
- $model->join(['left outer join company on book.company_id = company.id']);
-
- This C<join> is used by C<select()>, C<select_at()>
-
- =head2 Class name, Model name, Table name
-
- Class name, model name, and table name is a little different.
- Generally Class name is model name, and table name is model name.
-
- CLASS MODEL TABLE
- book (CLASS) -> book (MODEL) -> book
-
- You can change model name.
-
- package MyModel::book;
-
- __PACAKGE__->attr(name => 'book_model');
-
- CLASS MODEL TABLE
- book book_model (MODEL) -> book_model
-
- Model name is the name used by L<model()> of L<DBIx::Custom>.
-
- $dbi->model('book_model');
-
- You can change table name.
-
- package MyModel::book;
-
- __PACAKGE__->attr(table => 'book_table');
-
- CLASS MODEL TABLE
- book (CLASS) -> book book_table
-
- Table name is the table really accessed.
-
- $dbi->model('book')->insert(...); # access to "book_table"
-
- =head2 Create column clause automatically : mycolumn(), column()
-
- To create column clause automatically, use C<mycolumn()>.
- Valude of C<table> and C<columns> is used.
-
- my $column_clause = $model->mycolumn;
-
- If C<table> is 'book'�AC<column> is ['id', 'name'],
- the following clause is created.
-
- book.id as id, book.name as name
-
- These column name is for removing column name ambiguities.
-
- You can create column clause from columns of other table.
-
- my $column_clause = $model->column('company');
-
- If C<table> is 'company'�AC<column> is ['id', 'name'],
- the following clause is created.
-
- company.id as company__id, company.name as company__name
-
- =head2 Create column clause automatically : column_clause()
-
- To create column clause automatically, use C<column_clause()>.
- Valude of C<table> and C<columns> is used.
-
- my $column_clause = $model->column_clause;
-
- If C<table> is 'book'�AC<column> is ['id', 'name'],
- the following clause is created.
-
- book.id as id, book.name as name
-
- These column name is for removing column name ambiguities.
-
- If you remove some columns, use C<remove> option.
-
- my $column_clause = $model->column_clause(remove => ['id']);
-
- If you add some column, use C<add> option.
-
- my $column_clause = $model->column_clause(add => ['company.id as company__id']);
-
- =head2 Model Examples
-
- Model examples
-
- package MyDBI;
-
- use base 'DBIx::Custom';
-
- sub connect {
- my $self = shift->SUPER::connect(@_);
-
- $self->include_model(
- MyModel => [
- 'book',
- 'company'
- ]
- );
- }
-
- package MyModel::book;
- use base 'DBIx::Custom::Model';
-
- __PACKAGE__->attr('primary_key' => sub { ['id'] };
-
- sub insert { ... }
- sub list { ... }
-
- package MyModel::company;
- use base 'DBIx::Custom::Model';
-
- __PACKAGE__->attr('primary_key' => sub { ['id'] };
-
- sub insert { ... }
- sub list { ... }
-
- =head2 8. Improve performance
-
- =head3 Create query
-
- If you can't get performance, create query by C<query> option.
- For example, many insert is needed.
-
- my $params = [
- {title => 'Perl', author => 'Ken'},
- {title => 'Good day', author => 'Tom'}
- ]
- my $query = $dbi->insert(table => 'book', param => $params->[0], query => 1);
-
- Return value is L<DBIx::Custom::Query> object.
- This query is executed by C<execute()>.
-
- foreach my $param (@$params) {
- $dbi->execute($query, $param);
- }
-
- Performance is improved because statement handle is reused
- C<query> option is used in C<insert()>, C<update()>, C<update_all()>,
- C<delete()>, C<delete_all()>.
-
- Note that parameters count is same as method for creating query and C<execute()>.
-
- You can create query from any SQL by C<create_query()>.
-
- my $query = $dbi->create_query(
- "insert into book {insert_param title author};";
- );
-
- =head2 9. Other features
-
- =head3 Add method
-
- You can add method to L<DBIx::Custom> object.
- use C<method()>.
-
- $dbi->method(
- update_or_insert => sub {
- my $self = shift;
- # something
- },
- find_or_create => sub {
- my $self = shift;
- # something
- }
- );
-
- You can call these methods from L<DBIx::Custom> object.
-
- $dbi->update_or_insert;
- $dbi->find_or_create;
-
- =head3 Change result class
-
- You can change result class. By default it is L<DBIx::Custom::Result>.
-
- package MyResult;
- use base 'DBIx::Custom::Result';
-
- sub some_method { ... }
-
- 1;
-
- package main;
-
- use MyResult;
-
- my $dbi = DBIx::Custom->connect(...);
- $dbi->result_class('MyResult');
-
- =head1 EXAMPLES
-
- You can see exsamples in the following wiki.
-
- L<DBIx::Custom Wiki|https://github.com/yuki-kimoto/DBIx-Custom/wiki> - Many useful examples
-
- =cut