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