43af5d8 13 years ago
1 contributor
1345 lines | 33.23kb
  1. =encoding utf8
  2.  
  3. =head1 NAME
  4.  
  5. DBIx::Custom::Guide - DBIx::Custom Guide
  6.  
  7. =head1 GUIDE
  8.  
  9. (This guide will be completed nearly future and contains EXPERIMENTAL features
  10. The features marked EXPERIMENTAL in POD of L<DBIx::Custom> is
  11. EXPERIMENTAL ones)
  12.  
  13. L<DBIx::Custom> is the class to make easy to execute SQL.
  14. This is L<DBI> wrapper class like L<DBIx::Class> or L<DBIx::Simple>.
  15. You can do thing more easy than L<DBIx::Class>, more flexible
  16. than L<DBIx::Simple>.
  17.  
  18. L<DBIx::Custom> is not O/R mapper, O/R mapper is usefule, but
  19. you must learn many things. Created SQL is sometimes inefficient,
  20. and in many cases you create raw SQL because
  21. O/R mapper can't make complex SQL
  22.  
  23. L<DBIx::Custom> is opposit of O/R mapper.
  24. The main purpose is that we respect SQL
  25. and make easy difficult works if you use only L<DBI>.
  26. If you already learn SQL, it is easy to use L<DBIx::Custom>.
  27.  
  28. I explain L<DBIx::Custom> a little in this section.
  29. In L<DBIx::Custom>, you embbed tag in SQL.
  30.  
  31. select * from book where {= title} and {=author};
  32.  
  33. The part arround {} is tag.
  34. This SQL is converted to the one which contains place holder.
  35.  
  36. select * from book where title = ? and author = ?;
  37.  
  38. Maybe you ask me that this conversion is meaningful.
  39. On the top of this, usuful features is implemented.
  40. See the following descriptions.
  41.  
  42. =over 4
  43.  
  44. =item 1. Specify place holder binding value as hash refernce
  45.  
  46. If you use L<DBI>, you must specify place holder binding value
  47. as array.
  48.  
  49. $sth->execute(@bind);
  50.  
  51. If you use L<DBIx::Custom>, you specify it as hash reference.
  52. my $param = {title => 'Perl', author => 'Ken'};
  53. $dbi->execute($sql, $param);
  54.  
  55. =item 2. Filtering
  56.  
  57. L<DBIx::Custom> provides filtering system.
  58. For example, You think that about date value you want to
  59. manipulate it as date object like L<Time::Piece> in Perl,
  60. and want to convert it to database DATE format.
  61. and want to do reverse.
  62.  
  63. You can use filtering system.
  64.  
  65. At first, register filter.
  66.  
  67. $dbi->register_filter(
  68. tp_to_date => sub {
  69. ...
  70. },
  71. date_to_tp => sub {
  72. ...
  73. }
  74. );
  75.  
  76. next, apply this filter to each column.
  77.  
  78. $dbi->apply_filter('book',
  79. 'issue_date' => {out => 'tp_to_date', in => 'date_to_tp'}
  80. );
  81.  
  82. C<out> is perl-to-database way. C<in> is perl-from-database way.
  83.  
  84. This filter is automatically enabled in many method.
  85.  
  86. $dbi->insert(table => 'book', param => {issue_date => $tp});
  87.  
  88.  
  89. =item 3. Selective search condition
  90.  
  91. It is difficult to create selective where clause in L<DBI>.
  92. For example, If C<title> and C<author> is specified, we create
  93. the following SQL.
  94.  
  95. select * from book where title = ? and author = ?;
  96.  
  97. If only C<title> is specified, the following one
  98.  
  99. select * from book where title = ?;
  100.  
  101. If only C<author> is specified, the following one,
  102.  
  103. select * from book where author = ?;
  104.  
  105. This is hard work. Generally we use modules like L<SQL::Abstract>.
  106. L<DBIx::Custom> prepare the way to make it easy.
  107.  
  108. # Where object
  109. my $where = $dbi->where;
  110. # Search condition
  111. $where->clause(
  112. ['and', '{= title}', {'= author'}]
  113. );
  114. # Setting to automatically select needed column
  115. $where->param({title => 'Perl'});
  116.  
  117. # Embbed where clause to SQL
  118. my $sql = "select * from book $where";
  119.  
  120. You can create where clause which has selected search condition.
  121. You can write nesting of where clause and C<or> condition
  122.  
  123. =item 4. Methods for insert, update, delete, select
  124.  
  125. L<DBIx::Custom> provides methods for insert, update, delete, select
  126. There are C<insert()>, C<update()>, C<delete()>,C<select()>.
  127.  
  128. my $param = {title => 'Perl', author => 'Ken'};
  129. $dbi->insert(table => 'book', param => $param);
  130.  
  131. =item 5. Register method for table.
  132.  
  133. You can register method for table.
  134.  
  135. $dbi->table('book')->method(
  136. list => sub {
  137. ...
  138. },
  139. something => sub {
  140. ...
  141. }
  142. );
  143.  
  144. use the mehtod.
  145.  
  146. $dbi->table('book')->list;
  147.  
  148. Many O/R mapper must create class for table,
  149. but L<DBIx::Custom> make it easy.
  150.  
  151. =back
  152.  
  153. L<DBIx::Custom> is very useful.
  154. See the following if you are interested in it.
  155.  
  156. =head2 1. Connect to database
  157.  
  158. Load L<DBIx::Custom>.
  159.  
  160. use DBIx::Custom;
  161.  
  162. use C<connect()> to connect to database.
  163. Return value is L<DBIx::Custom> object.
  164.  
  165. my $dbi = DBIx::Custom->connect(
  166. data_source => "dbi:mysql:database=bookstore",
  167. user => 'ken',
  168. password => '!LFKD%$&',
  169. dbi_options => {mysql_enable_utf8 => 1}
  170. );
  171.  
  172. C<data_source> must be one corresponding to the database system.
  173. The following ones are data source example.
  174.  
  175. B<MySQL>
  176.  
  177. "dbi:mysql:database=$database"
  178. "dbi:mysql:database=$database;host=$hostname;port=$port"
  179.  
  180. B<SQLite>
  181.  
  182. "dbi:SQLite:dbname=$database"
  183. "dbi:SQLite:dbname=:memory:"
  184.  
  185. B<PostgreSQL>
  186.  
  187. "dbi:Pg:dbname=$dbname"
  188.  
  189. B<Oracle>
  190.  
  191. "dbi:Oracle:$dbname"
  192. "dbi:Oracle:host=$host;sid=$sid"
  193.  
  194. B<ODBC(Microsoft Access)>
  195.  
  196. "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=hoge.mdb"
  197.  
  198. B<ODBC(SQL Server)>
  199.  
  200. "dbi:ODBC:driver={SQL Server};Server=(local);database=test;Trusted_Connection=yes;AutoTranslate=No;"
  201.  
  202. If authentication is needed, you can specify C<user> and C<password>
  203.  
  204. L<DBIx::Custom> is wrapper class of L<DBI>.
  205. You can use all methods of L<DBI> from L<DBIx::Custom> object.
  206.  
  207. $dbi->do(...);
  208. $dbi->begin_work;
  209.  
  210. use C<dhb()> to get database handle of L<DBI>
  211.  
  212. my $dbh = $dbi->dbh;
  213.  
  214. By default, the following ones is set to database handle attributes.
  215.  
  216. RaiseError -> 1
  217. PrintError -> 0
  218. AutoCommit -> 1
  219.  
  220. If fatal error occuer, program terminate.
  221. If SQL is executed, commit is executed automatically.
  222.  
  223. =head2 2. Methods for insert, update, delete, or insert
  224.  
  225. There are following methods.
  226.  
  227. =head3 C<insert()>
  228.  
  229. use C<insert()> to insert row into database
  230.  
  231. $dbi->insert(table => 'book',
  232. param => {title => 'Perl', author => 'Ken'});
  233.  
  234. C<table> is table name, C<param> is insert data.
  235.  
  236. Following SQL is executed.
  237.  
  238. insert into (title, author) values (?, ?);
  239.  
  240. =head3 C<update()>
  241.  
  242. use C<update()> to update row in database.
  243.  
  244. $dbi->update(table => 'book',
  245. param => {title => 'Perl', author => 'Ken'},
  246. where => {id => 5});
  247.  
  248. C<table> is table name, C<param> is update data, C<where> is condition.
  249.  
  250. Following SQL is executed.
  251.  
  252. update book set title = ?, author = ?;
  253.  
  254. You can't execute C<update()> without C<where> for safety.
  255. use C<update_all()> if you want to update all rows.
  256.  
  257. $dbi->update_all(table => 'book',
  258. param => {title => 'Perl', author => 'Ken'});
  259.  
  260. =head3 C<delete()>
  261.  
  262. use C<delete()> to delete rows from database.
  263.  
  264. $dbi->delete(table => 'book',
  265. where => {author => 'Ken'});
  266.  
  267. C<table> is table name, C<where> is condition.
  268.  
  269. Following SQL is executed.
  270.  
  271. delete from book where id = ?;
  272.  
  273. You can't execute C<delete()> without C<where> for safety.
  274. use C<delete_all()> if you want to delete all rows.
  275.  
  276. $dbi->delete_all(table => 'book');
  277.  
  278. =head3 C<select()>
  279.  
  280. use C<select()> to select rows from database
  281.  
  282. my $result = $dbi->select(table => 'book');
  283.  
  284. Following SQL is executed.
  285.  
  286. select * from book;
  287.  
  288. Return value is L<DBIx::Custom::Result> object.
  289. use C<fetch()> to fetch row.
  290.  
  291. while (my $row = $result->fetch) {
  292. my $title = $row->[0];
  293. my $author = $row->[1];
  294. }
  295.  
  296. See L<3. Fetch row/"3. Fetch row"> about L<DBIx::Custom::Result>.
  297.  
  298. Continue more examples.
  299.  
  300. my $result = $dbi->select(
  301. table => 'book',
  302. column => ['author', 'title'],
  303. where => {author => 'Ken'}
  304. );
  305.  
  306. C<column> is column names, C<where> is condition.
  307.  
  308. Following SQL is executed.
  309.  
  310. select author, title from book where author = ?;
  311.  
  312. Next example.
  313.  
  314. my $result = $dbi->select(
  315. table => 'book',
  316. column => ['company.name as company__name']
  317. where => {'book.name' => 'Perl'},
  318. join => ['left outer join company on book.company_id = company.id]
  319. );
  320.  
  321. You can join table by C<join>.
  322.  
  323. Following SQL is executed.
  324.  
  325. select company.name as company__name
  326. from book
  327. left outer join company on book.company_id = company.id
  328. where book.name = ?;
  329.  
  330. company_if of book and id of company is left outer joined.
  331.  
  332. Note that only when C<where> or C<column> contain table name,
  333. C<join> is joined.
  334. if you specify the following option, C<join> is not joined
  335. because C<join> is not needed.
  336.  
  337. my $result = $dbi->select(
  338. table => 'book',
  339. where => {'name' => 'Perl'},
  340. join => ['left outer join company on book.company_id = company.id]
  341. );
  342.  
  343. Following SQL is executeed.
  344.  
  345. select * from book where book.name = ?;
  346.  
  347. You can specify column names easily using C<mycolumn()> and C<column()>.
  348.  
  349. my $result = $dbi->select(
  350. table => 'book',
  351. column => [
  352. $dbi->mycolumn('book' => ['name']),
  353. $dbi->column('company' => ['id', 'name'])
  354. ],
  355. join => ['left outer join company on book.company_id = company.id]
  356. );
  357.  
  358. The following SQL is executed.
  359.  
  360. select book.name as name,
  361. company.id as comapny__id,
  362. company.name as company__name
  363. from book
  364. left outer join company on book.company_id = company.id
  365.  
  366. Next example.
  367.  
  368. my $result = $dbi->select(
  369. table => 'book',
  370. where => {author => 'Ken'},
  371. append => 'for update',
  372. );
  373.  
  374. C<append> is string appending to end of SQL.
  375.  
  376. Following SQL is executed.
  377.  
  378. select * book where author = ? for update;
  379.  
  380. C<appned> is also used at C<insert()>, C<update()>, C<update_all()>
  381. C<delete()>, C<delete_all()>, and C<select()>.
  382.  
  383. =head3 C<execute()>
  384.  
  385. use C<execute()> to execute SQL
  386.  
  387. $dbi->execute("select * from book;");
  388.  
  389. Process tag and execute SQL.
  390.  
  391. $dbi->execute(
  392. "select * from book {= title} and {= author};"
  393. param => {title => 'Perl', author => 'Ken'}
  394. );
  395.  
  396. Following SQL is executed.
  397.  
  398. select * from book title = ? and author = ?;
  399.  
  400. Values of title and author is embbdeded into placeholder.
  401.  
  402. See L<5. Tag/"5. Tag"> about tag.
  403.  
  404. You don't have to wirte last semicolon in C<execute()>.
  405.  
  406. $dbi->execute('select * from book');
  407.  
  408. =head3 insert by using primary key : C<insert_at()>
  409.  
  410. To insert row by using primary key, use C<insert_at()>
  411.  
  412. $dbi->insert_at(
  413. table => 'book', primary_key => ['id'],
  414. where => ['123'], param => {name => 'Ken'}
  415. );
  416.  
  417. In this example, row which id column is 123 is inserted.
  418. NOTE that you must pass array reference as C<where>.
  419. If C<param> contains primary key, the key and value is delete from C<param>.
  420.  
  421. =head3 Update by using primary key : C<update_at()>
  422.  
  423. To update row by using primary key, use C<update_at()>
  424.  
  425. $dbi->update_at(
  426. table => 'book', primary_key => ['id'],
  427. where => ['123'], param => {name => 'Ken'}
  428. );
  429.  
  430. In this example, row which id column is 123 is updated.
  431. NOTE that you must pass array reference as C<where>.
  432. If C<param> contains primary key, the key and value is delete from C<param>.
  433.  
  434. =head3 Delete by using primary key : C<delete_at()>
  435.  
  436. To delete row by using primary key, use C<delete_at()>
  437.  
  438. $dbi->delete_at(table => 'book', primary_key => ['id'], where => ['123']);
  439.  
  440. In this example, row which id column is 123 is deleted.
  441. NOTE that you must pass array reference as C<where>.
  442.  
  443. You can also write arguments like this.
  444.  
  445. $dbi->delete_at(table => 'book', primary_key => ['id'], param => {id => '123'});
  446.  
  447. =head3 Select by using primary key : C<select_at()>
  448.  
  449. To select row by using primary key, use C<select_at()>.
  450.  
  451. $dbi->select_at(table => 'book', primary_key => ['id'], where => ['123']);
  452.  
  453. In this example, row which id colunm is 123 is selected.
  454. NOTE that you must pass array reference as C<where>.
  455.  
  456. You can also write arguments like this.
  457.  
  458. $dbi->select_at(table => 'book', primary_key => ['id'], param => {id => '123'});
  459.  
  460. =head2 3. Fetch row
  461.  
  462. Return value of C<select()> is L<DBIx::Custom::Result> object.
  463. There are many methods to fetch row.
  464.  
  465. =head3 Fetch a row (array) : C<fetch()>
  466.  
  467. use C<fetch()> to fetch a row and assign it into array reference.
  468.  
  469. my $row = $result->fetch;
  470.  
  471. You can get all rows.
  472.  
  473. while (my $row = $result->fetch) {
  474. my $title = $row->[0];
  475. my $author = $row->[1];
  476. }
  477.  
  478. =head3 Fetch only first row (array) : C<fetch_first()>
  479.  
  480. use C<fetch_first()> to fetch only first row.
  481.  
  482. my $row = $result->fetch_first;
  483.  
  484. You can't fetch rest rows
  485. because statement handle C<finish()> is executed.
  486.  
  487. =head3 Fetch rows (array) : C<fetch_multi()>
  488.  
  489. use C<fetch_multi()> to fetch rows and assign it into
  490. array reference which has array references as element.
  491.  
  492. while (my $rows = $result->fetch_multi(2)) {
  493. my $title0 = $rows->[0][0];
  494. my $author0 = $rows->[0][1];
  495. my $title1 = $rows->[1][0];
  496. my $author1 = $rows->[1][1];
  497. }
  498.  
  499. Specify row count as argument.
  500.  
  501. You can get the following data.
  502.  
  503. [
  504. ['Perl', 'Ken'],
  505. ['Ruby', 'Mark']
  506. ]
  507.  
  508. =head3 Fetch all rows (array) : C<fetch_all>
  509.  
  510. use C<fetch_all()> to fetch all rows and assign it into
  511. array reference which has array reference as element.
  512.  
  513. my $rows = $result->fetch_all;
  514.  
  515. You can get the following data.
  516.  
  517. [
  518. ['Perl', 'Ken'],
  519. ['Ruby', 'Mark']
  520. ]
  521.  
  522. =head3 Fetch a row (hash) : C<fetch_hash()>
  523.  
  524. use C<fetch_hash()> to fetch a row and assign it into hash reference.
  525.  
  526. while (my $row = $result->fetch_hash) {
  527. my $title = $row->{title};
  528. my $author = $row->{author};
  529. }
  530.  
  531. =head3 Fetch only first row (hash) : C<fetch_hash_first()>
  532.  
  533. use C<fetch_hash_first()> to fetch only first row
  534. and assign it into hash reference.
  535.  
  536. my $row = $result->fetch_hash_first;
  537.  
  538. You can't fetch rest rows
  539. because statement handle C<finish()> is executed.
  540.  
  541. =head3 Fetch rows (hash) : C<fetch_hash_multi()>
  542.  
  543. use C<fetch_hash_multi()> to fetch rows and
  544. assign it into array reference which has hash references as element.
  545.  
  546. while (my $rows = $result->fetch_hash_multi(5)) {
  547. my $title0 = $rows->[0]{title};
  548. my $author0 = $rows->[0]{author};
  549. my $title1 = $rows->[1]{title};
  550. my $author1 = $rows->[1]{author};
  551. }
  552.  
  553. Specify row count as argument.
  554.  
  555. You can get the following data.
  556.  
  557. [
  558. {title => 'Perl', author => 'Ken'},
  559. {title => 'Ruby', author => 'Mark'}
  560. ]
  561.  
  562. =head3 Fetch all rows (hash) : C<fetch_hash_all()>
  563.  
  564. use C<fetch_hash_all()> to fetch all rows and
  565. assign it into array reference which has hash
  566. references as element.
  567.  
  568. my $rows = $result->fetch_hash_all;
  569.  
  570. You can get the following data.
  571.  
  572. [
  573. {title => 'Perl', author => 'Ken'},
  574. {title => 'Ruby', author => 'Mark'}
  575. ]
  576.  
  577. =head3 Statement handle : C<sth()>
  578.  
  579. use <sth()> to get statement handle.
  580.  
  581. my $sth = $result->sth;
  582.  
  583. =head2 4. Filtering
  584.  
  585. L<DBIx::Custom> provide value filtering.
  586. For example, You maybe want to convert L<Time::Piece> object to
  587. database date format when register data into database.
  588. and convert database date fromat to L<Time::Piece> object
  589. when get data from database.
  590.  
  591. =head3 Register filter : C<register_filter()>
  592.  
  593. use C<register_filter()> to register filter.
  594.  
  595. $dbi->register_filter(
  596. # Time::Piece object to DATE format
  597. tp_to_date => sub {
  598. my $date = shift;
  599.  
  600. return '0000-00-00' unless $tp;
  601. return $tp->strftime('%Y-%m-%d');
  602. },
  603. # DATE to Time::Piece object
  604. date_to_tp => sub {
  605. my $date = shift;
  606.  
  607. return if $date eq '0000-00-00';
  608. return Time::Piece->strptime($date, '%Y-%m-%d');
  609. },
  610. );
  611.  
  612. Registered filter is used by C<apply_filter()> or etc.
  613.  
  614. =head3 Apply filter : C<apply_filter()>
  615.  
  616. use C<apply_filter()> to apply registered filter.
  617.  
  618. $dbi->apply_filter('book',
  619. issue_date => {out => 'tp_to_date', in => 'date_to_tp'},
  620. first_issue_date => {out => 'tp_to_date', in => 'date_to_tp'}
  621. );
  622.  
  623. First argument is table name. Arguments after first argument are pairs of column
  624. name and fitering rule. C<out> of filtering rule is filter which is used when data
  625. is send to database. C<in> of filtering rule is filter which is used when data
  626. is got from database.
  627.  
  628. You can specify code reference as filter.
  629.  
  630. issue_date => {out => sub { ... }, in => sub { ... }}
  631.  
  632. Applied filter become effective at insert()>, C<update()>, C<update_all()>,
  633. C<delete()>, C<delete_all()>, C<select()>.
  634.  
  635. my $tp = Time::Piece->strptime('2010/10/14', '%Y/%m/%d');
  636. my $result = $dbi->select(table => 'book', where => {issue_date => $tp});
  637.  
  638. When data is send to database, L<Time::Piece> object is converted
  639. to database date format "2010-10-14"
  640.  
  641. When data is fetched, database date format is
  642. converted to L<Time::Piece> object.
  643.  
  644. my $row = $resutl->fetch_hash_first;
  645. my $tp = $row->{issue_date};
  646.  
  647. You can also use column name which contains table name.
  648.  
  649. $dbi->select(
  650. table => 'book',
  651. where => {'book.issue_date' => $tp}
  652. );
  653.  
  654. In fetching, Filter is effective if you use "TABLE__COLUMN" as column name.
  655.  
  656. my $result = $dbi->execute(
  657. "select issue_date as book__issue_date from book");
  658.  
  659. You can apply C<end> filter execute after C<in> filter.
  660.  
  661. $dbi->apply_filter('book',
  662. issue_date => {out => 'tp_to_date', in => 'date_to_tp',
  663. end => 'tp_to_displaydate'},
  664. );
  665.  
  666. =head3 Individual filter C<filter>
  667.  
  668. You can apply individual filter .
  669. This filter overwrite the filter by C<apply_filter()>
  670.  
  671. use C<filter> option to apply individual filter
  672. when data is send to database.
  673. This option is used at C<insert()>, C<update()>,
  674. C<update_all()>, C<delete()>, C<delete_all()>, C<select()>,
  675. C<execute()>.
  676.  
  677. C<insert()> example:
  678.  
  679. $dbi->insert(
  680. table => 'book',
  681. param => {issue_date => $tp, first_issue_date => $tp},
  682. filter => {issue_date => 'tp_to_date', first_issue_date => 'tp_to_date'}
  683. );
  684.  
  685. C<execute()> example:
  686.  
  687. my $sql = <<"EOS";
  688. select YEAR(issue_date) as issue_year
  689. from book
  690. where YEAR(issue_date) = {? issue_year}
  691. EOS
  692. my $result = $dbi->execute(
  693. $sql,
  694. param => {issue_year => '2010'},
  695. filter => {issue_year => 'tp_to_year'}
  696. );
  697.  
  698. You can also apply indivisual filter when you fetch row.
  699. use C<DBIx::Custom::Result>'s C<filter()>.
  700.  
  701. $result->filter(issue_year => 'year_to_tp');
  702.  
  703. You can remove filter by C<remove_filter()>
  704.  
  705. $result->remove_filter;
  706.  
  707. =head3 End filtering : C<end_filter()>
  708.  
  709. You can add filter at end.
  710. It is useful to create last output.
  711. use C<end_filter()> to add end filter.
  712.  
  713. $result->end_filter(issue_date => sub {
  714. my $tp = shift;
  715. return '' unless $tp;
  716. return $tp->strftime('%Y/%m/%d %h:%m:%s (%a)');
  717. });
  718.  
  719. In this example, L<Time::Piece> object is converted to readable format.
  720.  
  721. You can remove end_filter by C<end_filter>
  722.  
  723. $result->remove_end_filter;
  724.  
  725. =head3 Automate applying filter : C<each_column()>
  726.  
  727. It is useful to apply filter automatically at date type columns.
  728. You can use C<each_column()> to process all column infos.
  729.  
  730. $dbi->each_column(
  731. sub {
  732. my ($self, $table, $column, $info) = @_;
  733. my $type = $info->{TYPE_NAME};
  734. my $filter = $type eq 'DATE' ? {out => 'tp_to_date', in => 'date_to_tp'}
  735. : $type eq 'DATETIME' ? {out => 'tp_to_datetime', in => 'datetime_to_tp'}
  736. : undef;
  737. $self->apply_filter($table, $column, $filter)
  738. if $filter;
  739. }
  740. );
  741.  
  742. C<each_column()> receive callback.
  743. callback arguments are L<DBIx::Custom> object, table name, column name, column information.
  744. Filter is applied automatically by column type.
  745.  
  746. =head2 5. Tag
  747.  
  748. =head3 Basic of Tag
  749.  
  750. You can embedd tag into SQL.
  751.  
  752. select * from book where {= title} and {like author};
  753.  
  754. {= title} and {like author} are tag. Tag has the folloring format.
  755.  
  756. {TAG_NAME ARG1 ARG2 ...}
  757.  
  758. Tag start C<{> and end C<}>.
  759. Don't insert space between C<{}> and tag name.
  760.  
  761. C<{> and C<}> are reserved word.
  762. If you want to use these, escape it by '\';
  763.  
  764. select from book \\{ ... \\}
  765.  
  766. \ is perl's escape character, you need two \.
  767.  
  768. Tag is expanded before executing SQL.
  769.  
  770. select * from book where title = ? and author like ?;
  771.  
  772. use C<execute()> to execute SQL which contains tag
  773.  
  774. my $sql = "select * from book where {= author} and {like title};"
  775. $dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'});
  776.  
  777. You can specify values embedded into place holder as hash reference using
  778. C<param> option.
  779.  
  780. You can specify C<filter()> at C<execute()>.
  781.  
  782. $dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'}
  783. filter => {title => 'to_something');
  784.  
  785. Note that at C<execute()> the filter applied by C<apply_filter()>
  786. don't has effective to columns.
  787. You have to use C<table> option
  788.  
  789. $dbi->execute($sql, table => ['author', 'book']);
  790.  
  791. =head3 Tag list
  792.  
  793. The following tag is available.
  794.  
  795. =head4 C<?>
  796.  
  797. {? NAME} -> ?
  798.  
  799. =head4 C<=>
  800.  
  801. {= NAME} -> NAME = ?
  802.  
  803. =head4 C<E<lt>E<gt>>
  804.  
  805. {<> NAME} -> NAME <> ?
  806.  
  807. =head4 C<E<lt>>
  808.  
  809. {< NAME} -> NAME < ?
  810.  
  811. =head4 C<E<gt>>
  812.  
  813. {> NAME} -> NAME > ?
  814.  
  815. =head4 C<E<gt>=>
  816.  
  817. {>= NAME} -> NAME >= ?
  818.  
  819. =head4 C<E<lt>=>
  820.  
  821. {<= NAME} -> NAME <= ?
  822.  
  823. =head4 C<like>
  824.  
  825. {like NAME} -> NAME like ?
  826.  
  827. =head4 C<in>
  828.  
  829. {in NAME COUNT} -> NAME in [?, ?, ..]
  830.  
  831. =head4 C<insert_param>
  832.  
  833. {insert_param NAME1 NAME2} -> (NAME1, NAME2) values (?, ?)
  834.  
  835. =head4 C<update_param>
  836.  
  837. {update_param NAME1 NAME2} -> set NAME1 = ?, NAME2 = ?
  838.  
  839. =head3 Manipulate same name's columns
  840.  
  841. It is ok if there are same name's columns.
  842. Let's think two date comparison.
  843.  
  844. my $sql = "select * from table where {> date} and {< date};";
  845.  
  846. In this case, You specify paramter values as array reference.
  847.  
  848. my $dbi->execute($sql, param => {date => ['2010-10-01', '2012-02-10']});
  849.  
  850. =head3 Register Tag : C<register_tag()>
  851.  
  852. You can register custom tag.
  853. use C<register_tag()> to register tag.
  854.  
  855. $dbi->register_tag(
  856. '=' => sub {
  857. my $column = shift;
  858. return ["$column = ?", [$column]];
  859. }
  860. );
  861.  
  862. This is implementation of C<=> tag.
  863. Tag format is the following one.
  864.  
  865. {TAG_NAME ARG1 ARG2 ...}
  866.  
  867. In case C<=> tag. Format is
  868.  
  869. {= title}
  870.  
  871. So subroutine receive one argument "title".
  872. You have to return array reference in the following format.
  873.  
  874. [
  875. String after expanding,
  876. [COLUMN1(This is used for place holder), COLUMN2 , ...]
  877. ]
  878.  
  879. First element is expanded stirng. In this example,
  880.  
  881. 'title = ?'
  882.  
  883. Secount element is array reference which is used to embedd value to
  884. place holder. In this example,
  885.  
  886. ['title']
  887.  
  888. If there are more than one placeholders,
  889. This elements is multipul.
  890.  
  891. You return the following array reference.
  892.  
  893. ['title = ?', ['title']]
  894.  
  895. See source of L<DBIx::Custom::Tag> to see many implementation.
  896.  
  897. =head2 6. Dinamically create where clause
  898.  
  899. =head3 Dinamically create where clause : where()
  900.  
  901. You want to search multiple conditions in many times.
  902. Let's think the following three cases.
  903.  
  904. Case1: Search only C<title>
  905.  
  906. where {= title}
  907.  
  908. Case2: Search only C<author>
  909.  
  910. where {= author}
  911.  
  912. Case3: Search C<title> and C<author>
  913.  
  914. where {= title} and {=author}
  915.  
  916. L<DBIx::Custom> support dinamic where clause creating.
  917. At first, create L<DBIx::Custom::Where> object by C<where()>.
  918.  
  919. my $where = $dbi->where;
  920.  
  921. Set clause by C<clause()>
  922.  
  923. $where->clause(
  924. ['and', '{= title'}, '{= author}']
  925. );
  926.  
  927. C<clause> is the following format.
  928.  
  929. ['or' or 'and', TAG1, TAG2, TAG3]
  930.  
  931. First argument is 'or' or 'and'.
  932. Later than first argument are tag names.
  933.  
  934. You can write more complex format.
  935.  
  936. ['and',
  937. '{= title}',
  938. ['or', '{= author}', '{like date}']
  939. ]
  940.  
  941. This mean "{=title} and ( {=author} or {like date} )".
  942.  
  943. After setting C<clause>, set C<param>.
  944. $where->param({title => 'Perl'});
  945.  
  946. In this example, parameter contains only title.
  947.  
  948. If you execute C<string_to()>, you can get where clause
  949. which contain only parameter name.
  950.  
  951. my $where_clause = $where->to_string;
  952.  
  953. Parameter name is only title, the following where clause is created.
  954.  
  955. where {= title}
  956.  
  957. You can also create where clause by stringification.
  958.  
  959. my $where_clause = "$where";
  960.  
  961. This is useful to embbed it into SQL.
  962.  
  963. =head3 In case where clause contains same name columns
  964.  
  965. Even if same name tags exists, you can create where clause.
  966. Let's think that there are starting date and ending date.
  967.  
  968. my $param = {start_date => '2010-11-15', end_date => '2011-11-21'};
  969.  
  970. In this case, you set parameter value as array reference.
  971.  
  972. my $p = {date => ['2010-11-15', '2011-11-21']};
  973.  
  974. You can embbed these values into same name tags.
  975.  
  976. $where->clause(
  977. ['and', '{> date}', '{< date}']
  978. );
  979. $where->param($p);
  980.  
  981. If starting date isn't exists, create the following parameter.
  982.  
  983. my $p = {date => [$dbi->not_exists, '2011-11-21']};
  984.  
  985. You can get DBIx::Custom::NotExists object by C<not_exists()>
  986. This mean correnspondinf value isn't exists.
  987.  
  988. If ending date isn't exists, create the following parameter.
  989.  
  990. my $p = {date => ['2010-11-15']};
  991.  
  992. If both date isn't exists, create the following parameter.
  993.  
  994. my $p = {date => []};
  995.  
  996. This logic is a little difficut. See the following ones.
  997.  
  998. my @date;
  999. push @date, exists $param->{start_date} ? $param->{start_date}
  1000. : $dbi->not_exists;
  1001. push @date, $param->{end_date} if exists $param->{end_date};
  1002. my $p = {date => \@date};
  1003.  
  1004. =head3 With C<select()>
  1005.  
  1006. You can pass L<DBIx::Custom::Where> object to C<where> of C<select()>.
  1007. my $where = $dbi->where;
  1008. $where->clause(['and', '{= title}', '{= author}']);
  1009. $where->param({title => 'Perl'});
  1010. my $result = $dbi->select(table => 'book', where => $where);
  1011.  
  1012. You can also pass it to C<where> of C<update()>�AC<delete()>
  1013.  
  1014. =head3 With C<execute()>
  1015.  
  1016. L<DBIx::Custom::Where> object is embedded into SQL.
  1017.  
  1018. my $where = $dbi->where;
  1019. $where->clause(['and', '{= title}', '{= author}']);
  1020. $where->param({title => 'Perl'});
  1021.  
  1022. my $sql = <<"EOS";
  1023. select * from {table book};
  1024. $where
  1025. EOS
  1026.  
  1027. $dbi->execute($sql, param => $param);
  1028.  
  1029. =head2 7. Model
  1030.  
  1031. =head3 Model
  1032.  
  1033. you can define model extending L<DBIx::Custom::Model>
  1034. to improve source code view.
  1035.  
  1036. At first, you create basic model class extending <DBIx::Custom::Model>.
  1037.  
  1038. package MyModel;
  1039. use base 'DBIx::Custom::Model';
  1040.  
  1041. Next, you create each model classes.
  1042.  
  1043. MyModel::book
  1044.  
  1045. package MyModel::book;
  1046. use base 'MyModel';
  1047. sub insert { ... }
  1048. sub list { ... }
  1049.  
  1050. MyModel::company
  1051.  
  1052. package MyModel::company;
  1053. use base 'MyModel';
  1054. sub insert { ... }
  1055. sub list { ... }
  1056.  
  1057. The follwoing modules location is needed.
  1058.  
  1059. MyModel.pm
  1060. MyModel / book.pm
  1061. / company.pm
  1062.  
  1063. You can include these models by C<include_model()>
  1064.  
  1065. $dbi->include_model('MyModel');
  1066.  
  1067. First argument is name space of model.
  1068.  
  1069. You can use model like this.
  1070.  
  1071. my $result = $dbi->model('book')->list;
  1072.  
  1073. In mode, You can use such as methods,
  1074. C<insert()>, C<update()>, C<update_all()>,
  1075. C<delete()>, C<delete_all()>, C<select()>
  1076. without C<table> option.
  1077.  
  1078. $dbi->model('book')->insert(param => $param);
  1079.  
  1080. Model is L<DBIx::Custom::Model>.
  1081.  
  1082. If you need table name�Ayou can get it by C<table()>.
  1083.  
  1084. my $table = $model->table;
  1085.  
  1086. You can get L<DBIx::Custom>.
  1087.  
  1088. my $dbi = $model->dbi;
  1089.  
  1090. You can also call all methods of L<DBIx::Custom> and L<DBI>.
  1091.  
  1092. # DBIx::Custom method
  1093. $model->execute($sql);
  1094. # DBI method
  1095. $model->begin_work;
  1096. $model->commit;
  1097.  
  1098. If you want to get all models, you can get them by keys of C<models()>.
  1099.  
  1100. my @models = keys %{$self->models};
  1101.  
  1102. You can set primary key to model.
  1103.  
  1104. $model->primary_key(['id', 'number_id']);
  1105.  
  1106. Primary key is used by C<insert_at>, C<update_at()>, C<delete_at()>,
  1107. C<select_at()>.
  1108.  
  1109. by C<filter> you can define filters applied by C<apply_filter()>
  1110.  
  1111. $model->filter({
  1112. title => {out => ..., in => ..., end => ...},
  1113. author => {out => ..., in => ..., end => ...}
  1114. });
  1115.  
  1116. This filters is applied when C<include_model()> is called.
  1117.  
  1118. You can set column names
  1119.  
  1120. $model->columns(['id', 'number_id']);
  1121.  
  1122. Column names is automarically set by C<setup_model()>.
  1123. This method is needed to be call after C<include_model()>.
  1124.  
  1125. $dbi->setup_model;
  1126.  
  1127. You can set C<join>
  1128.  
  1129. $model->join(['left outer join company on book.company_id = company.id']);
  1130.  
  1131. This C<join> is used by C<select()>, C<select_at()>
  1132.  
  1133. =head2 Class name, Model name, Table name
  1134.  
  1135. Class name, model name, and table name is a little different.
  1136. Generally Class name is model name, and table name is model name.
  1137.  
  1138. CLASS MODEL TABLE
  1139. book (CLASS) -> book (MODEL) -> book
  1140.  
  1141. You can change model name.
  1142.  
  1143. package MyModel::book;
  1144. __PACAKGE__->attr(name => 'book_model');
  1145.  
  1146. CLASS MODEL TABLE
  1147. book book_model (MODEL) -> book_model
  1148.  
  1149. Model name is the name used by L<model()> of L<DBIx::Custom>.
  1150.  
  1151. $dbi->model('book_model');
  1152.  
  1153. You can change table name.
  1154.  
  1155. package MyModel::book;
  1156. __PACAKGE__->attr(table => 'book_table');
  1157.  
  1158. CLASS MODEL TABLE
  1159. book (CLASS) -> book book_table
  1160.  
  1161. Table name is the table really accessed.
  1162.  
  1163. $dbi->model('book')->insert(...); # access to "book_table"
  1164.  
  1165. =head2 Create column clause automatically : mycolumn(), column()
  1166.  
  1167. To create column clause automatically, use C<mycolumn()>.
  1168. Valude of C<table> and C<columns> is used.
  1169.  
  1170. my $column_clause = $model->mycolumn;
  1171.  
  1172. If C<table> is 'book'�AC<column> is ['id', 'name'],
  1173. the following clause is created.
  1174.  
  1175. book.id as id, book.name as name
  1176.  
  1177. These column name is for removing column name ambiguities.
  1178.  
  1179. You can create column clause from columns of other table.
  1180.  
  1181. my $column_clause = $model->column('company');
  1182.  
  1183. If C<table> is 'company'�AC<column> is ['id', 'name'],
  1184. the following clause is created.
  1185.  
  1186. company.id as company__id, company.name as company__name
  1187.  
  1188. =head2 Create column clause automatically : column_clause()
  1189.  
  1190. To create column clause automatically, use C<column_clause()>.
  1191. Valude of C<table> and C<columns> is used.
  1192.  
  1193. my $column_clause = $model->column_clause;
  1194.  
  1195. If C<table> is 'book'�AC<column> is ['id', 'name'],
  1196. the following clause is created.
  1197.  
  1198. book.id as id, book.name as name
  1199.  
  1200. These column name is for removing column name ambiguities.
  1201.  
  1202. If you remove some columns, use C<remove> option.
  1203.  
  1204. my $column_clause = $model->column_clause(remove => ['id']);
  1205.  
  1206. If you add some column, use C<add> option.
  1207.  
  1208. my $column_clause = $model->column_clause(add => ['company.id as company__id']);
  1209.  
  1210. =head2 Model Examples
  1211.  
  1212. Model examples
  1213.  
  1214. package MyDBI;
  1215. use base 'DBIx::Custom';
  1216. sub connect {
  1217. my $self = shift->SUPER::connect(@_);
  1218. $self->include_model(
  1219. MyModel => [
  1220. 'book',
  1221. 'company'
  1222. ]
  1223. );
  1224. }
  1225. package MyModel::book;
  1226. use base 'DBIx::Custom::Model';
  1227. __PACKAGE__->attr('primary_key' => sub { ['id'] };
  1228. sub insert { ... }
  1229. sub list { ... }
  1230. package MyModel::company;
  1231. use base 'DBIx::Custom::Model';
  1232.  
  1233. __PACKAGE__->attr('primary_key' => sub { ['id'] };
  1234. sub insert { ... }
  1235. sub list { ... }
  1236.  
  1237. =head2 8. Improve performance
  1238.  
  1239. =head3 Create query
  1240.  
  1241. If you can't get performance, create query by C<query> option.
  1242. For example, many insert is needed.
  1243.  
  1244. my $params = [
  1245. {title => 'Perl', author => 'Ken'},
  1246. {title => 'Good day', author => 'Tom'}
  1247. ]
  1248. my $query = $dbi->insert(table => 'book', param => $params->[0], query => 1);
  1249.  
  1250. Return value is L<DBIx::Custom::Query> object.
  1251. This query is executed by C<execute()>.
  1252.  
  1253. foreach my $param (@$params) {
  1254. $dbi->execute($query, $param);
  1255. }
  1256.  
  1257. Performance is improved because statement handle is reused
  1258. C<query> option is used in C<insert()>, C<update()>, C<update_all()>,
  1259. C<delete()>, C<delete_all()>.
  1260.  
  1261. Note that parameters count is same as method for creating query and C<execute()>.
  1262.  
  1263. You can create query from any SQL by C<create_query()>.
  1264.  
  1265. my $query = $dbi->create_query(
  1266. "insert into book {insert_param title author};";
  1267. );
  1268.  
  1269. =head2 9. Other features
  1270.  
  1271. =head3 Add method
  1272.  
  1273. You can add method to L<DBIx::Custom> object.
  1274. use C<method()>.
  1275.  
  1276. $dbi->method(
  1277. update_or_insert => sub {
  1278. my $self = shift;
  1279. # something
  1280. },
  1281. find_or_create => sub {
  1282. my $self = shift;
  1283. # something
  1284. }
  1285. );
  1286.  
  1287. You can call these methods from L<DBIx::Custom> object.
  1288.  
  1289. $dbi->update_or_insert;
  1290. $dbi->find_or_create;
  1291.  
  1292. =head3 Change result class
  1293.  
  1294. You can change result class. By default it is L<DBIx::Custom::Result>.
  1295.  
  1296. package MyResult;
  1297. use base 'DBIx::Custom::Result';
  1298. sub some_method { ... }
  1299.  
  1300. 1;
  1301. package main;
  1302. use MyResult;
  1303. my $dbi = DBIx::Custom->connect(...);
  1304. $dbi->result_class('MyResult');
  1305.  
  1306. =head1 EXAMPLES
  1307.  
  1308. You can see exsamples in the following wiki.
  1309.  
  1310. L<DBIx::Custom Wiki|https://github.com/yuki-kimoto/DBIx-Custom/wiki> - Many useful examples
  1311.  
  1312. =cut