Newer Older
1179 lines | 28.41kb
update pod
Yuki Kimoto authored on 2011-01-28
1
=encoding utf8
2

            
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
3
=head1 NAME
4

            
update pod
Yuki Kimoto authored on 2011-01-28
5
DBIx::Custom::Guide - DBIx::Custom Guide
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
6

            
pod fix
Yuki Kimoto authored on 2011-01-21
7
=head1 GUIDE
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
8

            
update pod
Yuki Kimoto authored on 2011-01-28
9
B<This guide is now writing.>
update pod
Yuki Kimoto authored on 2011-01-26
10

            
update pod
Yuki Kimoto authored on 2011-01-28
11
L<DBIx::Custom> is the class to make easy to execute SQL.
12
This is L<DBI> wrapper class like L<DBIx::Class> or L<DBIx::Simple>.
13
You can do thing more easy than L<DBIx::Class>, more flexible
14
than L<DBIx::Simple>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
15

            
update pod
Yuki Kimoto authored on 2011-02-11
16
L<DBIx::Custom> is not O/R mapper, O/R mapper is usefule, but
update pod
Yuki Kimoto authored on 2011-01-28
17
you must learn many things. Created SQL is sometimes inefficient,
18
and in many cases you create raw SQL because
19
O/R mapper can't make complex SQL
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
20

            
update pod
Yuki Kimoto authored on 2011-01-28
21
L<DBIx::Custom> is opposit of O/R mapper.
22
The main purpose is that we respect SQL
23
and make easy difficult works if you use only L<DBI>.
24
If you already learn SQL, it is easy to use L<DBIx::Custom>.
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
25

            
update pod
Yuki Kimoto authored on 2011-01-28
26
I explain L<DBIx::Custom> a little in this section.
27
In L<DBIx::Custom>, you embbed tag in SQL.
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
28

            
update pod
Yuki Kimoto authored on 2011-01-28
29
    select * from book where {= title} and {=author};
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
30

            
update pod
Yuki Kimoto authored on 2011-01-28
31
The part arround {} is tag.
32
This SQL is converted to the one which contains place holder.
33

            
34
    select * from book where title = ? and author = ?;
35

            
36
Maybe you ask me that this conversion is meaningful.
37
On the top of this, usuful features is implemented.
38
See the following descriptions.
39

            
40
=over 4
41

            
42
=item 1. Specify place holder binding value as hash refernce
43

            
44
If you use L<DBI>, you must specify place holder binding value
45
as array.
46

            
47
    $sth->execute(@bind);
48

            
49
If you use L<DBIx::Custom>, you specify it as hash reference.
50
    
51
    my $param = {title => 'Perl', author => 'Ken'};
52
    $dbi->execute($sql, $param);
53

            
54
=item 2. Filtering
55

            
56
L<DBIx::Custom> provides filtering system.
57
For example, You think that about date value you want to 
58
manipulate it as date object like L<Time::Piece> in Perl,
59
and want to convert it to database DATE format.
60
and want to do reverse.
61

            
62
You can use filtering system.
63

            
64
At first, register filter.
65

            
66
    $dbi->register_filter(
67
        tp_to_date => sub {
68
            ...
69
        },
70
        date_to_tp => sub {
71
            ...
72
        }
73
    );
74

            
75
next, apply this filter to each column.
76

            
77
    $dbi->apply_filter('book',
78
        'issue_date' => {out => 'tp_to_date', in => 'date_to_tp'}
79
    );
80

            
81
C<out> is perl-to-database way. C<in> is perl-from-database way.
82

            
83
This filter is automatically enabled in many method.
84

            
85
    $dbi->insert(table => 'book', param => {issue_date => $tp});
86

            
87

            
88
=item 3. Selective search condition
89

            
90
It is difficult to create selective where clause in L<DBI>.
91
For example, If C<title> and C<author> is specified, we create 
92
the following SQL.
93

            
94
    select * from book where title = ? and author = ?;
95

            
96
If only C<title> is specified, the following one
97

            
98
    select * from book where title = ?;
99

            
100
If only C<author> is specified, the following one,
101

            
102
    select * from book where author = ?;
103

            
104
This is hard work. Generally we use modules like L<SQL::Abstract>.
105
L<DBIx::Custom> prepare the way to make it easy.
106

            
107
    # Where object
108
    my $where = $dbi->where;
109
    
110
    # Search condition
111
    $where->clause(
112
        ['and', '{= title}', {'= author'}]
113
    );
114
    
115
    # Setting to automatically select needed column
116
    $where->param({title => 'Perl'});
117

            
118
    # Embbed where clause to SQL
119
    my $sql = "select * from book $where";
120

            
121
You can create where clause which has selected search condition.
122
You can write nesting of where clause and C<or> condition
123

            
update pod
Yuki Kimoto authored on 2011-01-30
124
=item 4. Methods for insert, update, delete, select
update pod
Yuki Kimoto authored on 2011-01-28
125

            
update pod
Yuki Kimoto authored on 2011-01-30
126
L<DBIx::Custom> provides methods for insert, update, delete, select
127
There are C<insert()>, C<update()>, C<delete()>,C<select()>.
update pod
Yuki Kimoto authored on 2011-01-28
128

            
129
    my $param = {title => 'Perl', author => 'Ken'};
130
    $dbi->insert(table => 'book', param => $param);
131

            
update pod
Yuki Kimoto authored on 2011-01-30
132
=item 5. Register method for table.
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
133

            
update pod
Yuki Kimoto authored on 2011-01-30
134
You can register method for table.
update pod
Yuki Kimoto authored on 2011-01-28
135

            
update pod
Yuki Kimoto authored on 2011-01-30
136
    $dbi->table('book')->method(
update pod
Yuki Kimoto authored on 2011-01-28
137
        list => sub {
138
            ...
139
        },
update pod
Yuki Kimoto authored on 2011-01-30
140
        something => sub {
141
            ...
update pod
Yuki Kimoto authored on 2011-01-28
142
        }
143
    );
144

            
update pod
Yuki Kimoto authored on 2011-01-30
145
use the mehtod.
update pod
Yuki Kimoto authored on 2011-01-28
146

            
147
    $dbi->table('book')->list;
148

            
update pod
Yuki Kimoto authored on 2011-01-30
149
Many O/R mapper must create class for table,
150
but L<DBIx::Custom> make it easy.
update pod
Yuki Kimoto authored on 2011-01-28
151

            
152
=back
153

            
update pod
Yuki Kimoto authored on 2011-01-30
154
L<DBIx::Custom> is very useful.
155
See the following if you are interested in it.
update pod
Yuki Kimoto authored on 2011-01-28
156

            
update pod
Yuki Kimoto authored on 2011-01-30
157
=head2 1. Connect to database
update pod
Yuki Kimoto authored on 2011-01-28
158

            
update pod
Yuki Kimoto authored on 2011-01-30
159
Load L<DBIx::Custom>.
update pod
Yuki Kimoto authored on 2011-01-28
160

            
161
    use DBIx::Custom;
162

            
update pod
Yuki Kimoto authored on 2011-01-30
163
use C<connect()> to connect to database.
164
Return value is L<DBIx::Custom> object.
update pod
Yuki Kimoto authored on 2011-01-28
165

            
166
    my $dbi = DBIx::Custom->connect(
update pod
Yuki Kimoto authored on 2011-01-30
167
        data_source => "dbi:mysql:database=bookstore",
update pod
Yuki Kimoto authored on 2011-01-28
168
        user => 'ken',
169
        password => '!LFKD%$&',
170
        dbi_options => {mysql_enable_utf8 => 1}
171
    );
172

            
update pod
Yuki Kimoto authored on 2011-01-30
173
C<data_source> must be one corresponding to the database system.
174
The following ones are data source example.
update pod
Yuki Kimoto authored on 2011-01-28
175

            
176
B<MySQL>
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
177

            
178
    "dbi:mysql:database=$database"
179
    "dbi:mysql:database=$database;host=$hostname;port=$port"
180

            
update pod
Yuki Kimoto authored on 2011-01-28
181
B<SQLite>
182

            
183
    "dbi:SQLite:dbname=$database"
184
    "dbi:SQLite:dbname=:memory:"
185

            
186
B<PostgreSQL>
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
187

            
188
    "dbi:Pg:dbname=$dbname"
189

            
update pod
Yuki Kimoto authored on 2011-01-28
190
B<Oracle>
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
191

            
192
    "dbi:Oracle:$dbname"
193
    "dbi:Oracle:host=$host;sid=$sid"
194

            
update pod
Yuki Kimoto authored on 2011-01-28
195
B<ODBC(Microsoft Access)>
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
196

            
197
    "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=hoge.mdb"
198

            
update pod
Yuki Kimoto authored on 2011-01-28
199
B<ODBC(SQL Server)>
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
200

            
201
   "dbi:ODBC:driver={SQL Server};Server=(local);database=test;Trusted_Connection=yes;AutoTranslate=No;"
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
202

            
update pod
Yuki Kimoto authored on 2011-01-30
203
If authentication is needed, you can specify C<user> and C<password>
204

            
205
L<DBIx::Custom> is wrapper class of L<DBI>.
206
You can use all methods of L<DBI> from L<DBIx::Custom> object.
207

            
208
    $dbi->do(...);
209
    $dbi->begin_work;
update pod
Yuki Kimoto authored on 2011-01-28
210

            
update pod
Yuki Kimoto authored on 2011-01-30
211
use C<dhb()> to get database handle of L<DBI>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
212

            
update pod
Yuki Kimoto authored on 2011-01-28
213
    my $dbh = $dbi->dbh;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
214

            
update pod
Yuki Kimoto authored on 2011-01-30
215
By default, the following ones is set to database handle attributes.
216

            
217
    RaiseError  ->  1
218
    PrintError  ->  0
219
    AutoCommit  ->  1
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
220

            
update pod
Yuki Kimoto authored on 2011-01-30
221
If fatal error occuer, program terminate.
222
If SQL is executed, commit is executed automatically.
update pod
Yuki Kimoto authored on 2011-01-28
223

            
update pod
Yuki Kimoto authored on 2011-01-31
224
=head2 2. Methods for insert, update, delete, or insert
update pod
Yuki Kimoto authored on 2011-01-28
225

            
update pod
Yuki Kimoto authored on 2011-01-31
226
There are following methods.
update pod
Yuki Kimoto authored on 2011-01-28
227

            
update pod
Yuki Kimoto authored on 2011-01-31
228
=head3 C<insert()>
update pod
Yuki Kimoto authored on 2011-01-28
229

            
update pod
Yuki Kimoto authored on 2011-01-31
230
use C<insert()> to insert row into database
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
231

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
232
    $dbi->insert(table  => 'book',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
233
                 param  => {title => 'Perl', author => 'Ken'});
234

            
update pod
Yuki Kimoto authored on 2011-01-31
235
C<table> is table name, C<param> is insert data.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
236

            
update pod
Yuki Kimoto authored on 2011-01-31
237
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
238

            
update pod
Yuki Kimoto authored on 2011-01-28
239
    insert into (title, author) values (?, ?);
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
240

            
update pod
Yuki Kimoto authored on 2011-01-31
241
=head3 C<update()>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
242

            
update pod
Yuki Kimoto authored on 2011-01-31
243
use C<update()> to update row in database.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
244

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
245
    $dbi->update(table  => 'book', 
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
246
                 param  => {title => 'Perl', author => 'Ken'}, 
247
                 where  => {id => 5});
248

            
update pod
Yuki Kimoto authored on 2011-01-31
249
C<table> is table name, C<param> is update data, C<where> is condition.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
250

            
update pod
Yuki Kimoto authored on 2011-01-31
251
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
252

            
update pod
Yuki Kimoto authored on 2011-01-28
253
    update book set title = ?, author = ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
254

            
update pod
Yuki Kimoto authored on 2011-01-31
255
You can't execute C<update()> without C<where> for safety.
256
use C<update_all()> if you want to update all rows.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
257

            
update pod
Yuki Kimoto authored on 2011-01-28
258
    $dbi->update_all(table  => 'book', 
259
                     param  => {title => 'Perl', author => 'Ken'});
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
260

            
update pod
Yuki Kimoto authored on 2011-01-31
261
=head3 C<delete()>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
262

            
update pod
Yuki Kimoto authored on 2011-01-31
263
use C<delete()> to delete rows from database.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
264

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
265
    $dbi->delete(table  => 'book',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
266
                 where  => {author => 'Ken'});
267

            
update pod
Yuki Kimoto authored on 2011-01-31
268
C<table> is table name, C<where> is condition.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
269

            
update pod
Yuki Kimoto authored on 2011-01-31
270
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
271

            
update pod
Yuki Kimoto authored on 2011-01-28
272
    delete from book where id = ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
273

            
update pod
Yuki Kimoto authored on 2011-01-31
274
You can't execute C<delete()> without C<where> for safety.
275
use C<delete_all()> if you want to delete all rows.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
276

            
update pod
Yuki Kimoto authored on 2011-01-28
277
    $dbi->delete_all(table  => 'book');
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
278

            
update pod
Yuki Kimoto authored on 2011-01-31
279
=head3 C<select()>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
280

            
update pod
Yuki Kimoto authored on 2011-01-31
281
use C<select()> to select rows from database
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
282

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
283
    my $result = $dbi->select(table => 'book');
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
284

            
update pod
Yuki Kimoto authored on 2011-01-31
285
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
286

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
287
    select * from book;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
288

            
update pod
Yuki Kimoto authored on 2011-01-31
289
Return value is L<DBIx::Custom::Result> object.
290
use C<fetch()> to fetch row.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
291

            
292
    while (my $row = $result->fetch) {
293
        my $title  = $row->[0];
294
        my $author = $row->[1];
295
    }
296

            
update pod
Yuki Kimoto authored on 2011-01-31
297
See L<3. Fetch row/"3. Fetch row"> about L<DBIx::Custom::Result>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
298

            
update pod
Yuki Kimoto authored on 2011-01-31
299
Continue more examples.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
300

            
301
    my $result = $dbi->select(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
302
        table  => 'book',
update pod
Yuki Kimoto authored on 2011-01-28
303
        column => ['author',  'title'],
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
304
        where  => {author => 'Ken'}
305
    );
306

            
update pod
Yuki Kimoto authored on 2011-01-31
307
C<column> is column names, C<where> is condition.
308

            
309
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
310

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
311
    select author, title from book where author = ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
312

            
update pod
Yuki Kimoto authored on 2011-01-31
313
Next example.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
314

            
315
    my $result = $dbi->select(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
316
        table    => ['book', 'rental'],
update pod
Yuki Kimoto authored on 2011-01-31
317
        where    => {'book.name' => 'Perl'},
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
318
        relation => {'book.id' => 'rental.book_id'}
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
319
    );
320

            
update pod
Yuki Kimoto authored on 2011-01-31
321
C<relation> is relation of tables. This is inner join.
322

            
323
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
324

            
update pod
Yuki Kimoto authored on 2011-01-28
325
    select * from book, rental where book.name = ? and book.id = rental.book_id;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
326

            
update pod
Yuki Kimoto authored on 2011-01-31
327
Next example.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
328

            
329
    my $result = $dbi->select(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
330
        table  => 'book',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
331
        where  => {author => 'Ken'},
update pod
Yuki Kimoto authored on 2011-01-28
332
        append => 'for update',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
333
    );
334

            
update pod
Yuki Kimoto authored on 2011-01-31
335
C<append> is string appending to end of SQL.
336

            
337
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
338

            
update pod
Yuki Kimoto authored on 2011-01-28
339
    select * book where author = ? for update;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
340

            
update pod
Yuki Kimoto authored on 2011-01-31
341
C<appned> is also used at C<insert()>, C<update()>, C<update_all()>
342
C<delete()>, C<delete_all()>, and C<select()>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
343

            
update pod
Yuki Kimoto authored on 2011-02-11
344
Instead of C<column> and C<table>,
345
you can use C<selection>.
346
This is used to specify column names and table names at once
347

            
348
    my $selection = <<"EOS";
349
    title, author, company_name
350
    from book inner join company on book.company_id = company.id
351
    EOS
352

            
353
    $dbi->select(selection => $selection);
354

            
355
Note that you can't use where clause in C<selection>.
356
use clause like "inner join".
357

            
update pod
Yuki Kimoto authored on 2011-02-11
358
=head3 C<execute()>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
359

            
update pod
Yuki Kimoto authored on 2011-01-31
360
use C<execute()> to execute SQL
update pod
Yuki Kimoto authored on 2011-01-28
361

            
362
    $dbi->execute("select * from book;");
363

            
update pod
Yuki Kimoto authored on 2011-01-31
364
Process tag and execute SQL.
update pod
Yuki Kimoto authored on 2011-01-28
365

            
366
    $dbi->execute(
367
        "select * from book {= title} and {= author};"
368
        param => {title => 'Perl', author => 'Ken'}
369
    );
370

            
update pod
Yuki Kimoto authored on 2011-01-31
371
Following SQL is executed.
update pod
Yuki Kimoto authored on 2011-01-28
372

            
373
    select * from book title = ? and author = ?;
374

            
update pod
Yuki Kimoto authored on 2011-01-31
375
Values of title and author is embbdeded into placeholder.
update pod
Yuki Kimoto authored on 2011-01-28
376

            
update pod
Yuki Kimoto authored on 2011-01-31
377
See L<5. Tag/"5. Tag"> about tag.
update pod
Yuki Kimoto authored on 2011-01-28
378

            
update pod
Yuki Kimoto authored on 2011-01-31
379
You don't have to wirte last semicolon in C<execute()>. 
update pod
Yuki Kimoto authored on 2011-01-28
380

            
381
    $dbi->execute('select * from book');
382

            
update pod
Yuki Kimoto authored on 2011-02-04
383
=head2 3. Fetch row
update pod
Yuki Kimoto authored on 2011-01-28
384

            
update pod
Yuki Kimoto authored on 2011-02-04
385
Return value of C<select()> is L<DBIx::Custom::Result> object.
386
There are many methods to fetch row.
update pod
Yuki Kimoto authored on 2011-01-28
387

            
update pod
Yuki Kimoto authored on 2011-02-11
388
=head3 Fetch a row (array) : C<fetch()>
update pod
Yuki Kimoto authored on 2011-01-28
389

            
update pod
Yuki Kimoto authored on 2011-02-04
390
use C<fetch()> to fetch a row and assign it into array reference.
391

            
392
    my $row = $result->fetch;
393

            
394
You can get all rows.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
395

            
396
    while (my $row = $result->fetch) {
update pod
Yuki Kimoto authored on 2011-01-28
397
        my $title  = $row->[0];
398
        my $author = $row->[1];
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
399
    }
400

            
update pod
Yuki Kimoto authored on 2011-02-11
401
=head3 Fetch only first row (array) : C<fetch_first()>
update pod
Yuki Kimoto authored on 2011-01-28
402

            
update pod
Yuki Kimoto authored on 2011-02-04
403
use C<fetch_first()> to fetch only first row.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
404

            
405
    my $row = $result->fetch_first;
406

            
update pod
Yuki Kimoto authored on 2011-02-04
407
You can't fetch rest rows
408
because statement handle C<finish()> is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
409

            
update pod
Yuki Kimoto authored on 2011-02-11
410
=head3 Fetch rows (array) : C<fetch_multi()>
update pod
Yuki Kimoto authored on 2011-01-28
411

            
update pod
Yuki Kimoto authored on 2011-02-04
412
use C<fetch_multi()> to fetch rows and assign it into
413
array reference which has array references as element.
update pod
Yuki Kimoto authored on 2011-01-28
414

            
415
    while (my $rows = $result->fetch_multi(2)) {
416
        my $title0   = $rows->[0][0];
417
        my $author0  = $rows->[0][1];
418
        
419
        my $title1   = $rows->[1][0];
420
        my $author1  = $rows->[1][1];
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
421
    }
update pod
Yuki Kimoto authored on 2011-01-28
422

            
update pod
Yuki Kimoto authored on 2011-02-04
423
Specify row count as argument.
update pod
Yuki Kimoto authored on 2011-01-28
424

            
update pod
Yuki Kimoto authored on 2011-02-04
425
You can get the following data.
update pod
Yuki Kimoto authored on 2011-01-28
426

            
427
    [
428
        ['Perl', 'Ken'],
429
        ['Ruby', 'Mark']
430
    ]
431

            
update pod
Yuki Kimoto authored on 2011-02-11
432
=head3 Fetch all rows (array) : C<fetch_all>
update pod
Yuki Kimoto authored on 2011-01-28
433

            
update pod
Yuki Kimoto authored on 2011-02-04
434
use C<fetch_all()> to fetch all rows and assign it into
435
array reference which has array reference as element.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
436

            
437
    my $rows = $result->fetch_all;
438

            
update pod
Yuki Kimoto authored on 2011-02-04
439
You can get the following data.
update pod
Yuki Kimoto authored on 2011-01-28
440

            
441
    [
442
        ['Perl', 'Ken'],
443
        ['Ruby', 'Mark']
444
    ]
445

            
update pod
Yuki Kimoto authored on 2011-02-11
446
=head3 Fetch a row (hash) : C<fetch_hash()>
update pod
Yuki Kimoto authored on 2011-01-28
447

            
update pod
Yuki Kimoto authored on 2011-02-04
448
use C<fetch_hash()> to fetch a row and assign it into hash reference.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
449

            
450
    while (my $row = $result->fetch_hash) {
451
        my $title  = $row->{title};
452
        my $author = $row->{author};
453
    }
454

            
update pod
Yuki Kimoto authored on 2011-02-11
455
=head3 Fetch only first row (hash) : C<fetch_hash_first()>
update pod
Yuki Kimoto authored on 2011-01-28
456

            
update pod
Yuki Kimoto authored on 2011-02-04
457
use C<fetch_hash_first()> to fetch only first row
458
and assign it into hash reference.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
459

            
460
    my $row = $result->fetch_hash_first;
update pod
Yuki Kimoto authored on 2011-01-28
461

            
update pod
Yuki Kimoto authored on 2011-02-04
462
You can't fetch rest rows
463
because statement handle C<finish()> is executed.
update pod
Yuki Kimoto authored on 2011-01-28
464

            
update pod
Yuki Kimoto authored on 2011-02-11
465
=head3 Fetch rows (hash) : C<fetch_hash_multi()>
update pod
Yuki Kimoto authored on 2011-01-28
466

            
update pod
Yuki Kimoto authored on 2011-02-04
467
use C<fetch_hash_multi()> to fetch rows and
468
assign it into array reference which has hash references as element.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
469

            
470
    while (my $rows = $result->fetch_hash_multi(5)) {
update pod
Yuki Kimoto authored on 2011-01-28
471
        my $title0   = $rows->[0]{title};
472
        my $author0  = $rows->[0]{author};
473
        my $title1  = $rows->[1]{title};
474
        my $author1 = $rows->[1]{author};
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
475
    }
update pod
Yuki Kimoto authored on 2011-01-28
476

            
update pod
Yuki Kimoto authored on 2011-02-04
477
Specify row count as argument.
update pod
Yuki Kimoto authored on 2011-01-28
478

            
update pod
Yuki Kimoto authored on 2011-02-04
479
You can get the following data.
update pod
Yuki Kimoto authored on 2011-01-28
480

            
481
    [
482
        {title => 'Perl', author => 'Ken'},
483
        {title => 'Ruby', author => 'Mark'}
484
    ]
485

            
update pod
Yuki Kimoto authored on 2011-02-11
486
=head3 Fetch all rows (hash) : C<fetch_hash_all()>
update pod
Yuki Kimoto authored on 2011-01-28
487

            
update pod
Yuki Kimoto authored on 2011-02-04
488
use C<fetch_hash_all()> to fetch all rows and
489
assign it into array reference which has hash 
490
references as element.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
491

            
492
    my $rows = $result->fetch_hash_all;
493

            
update pod
Yuki Kimoto authored on 2011-02-04
494
You can get the following data.
update pod
Yuki Kimoto authored on 2011-01-28
495

            
496
    [
497
        {title => 'Perl', author => 'Ken'},
498
        {title => 'Ruby', author => 'Mark'}
499
    ]
500

            
update pod
Yuki Kimoto authored on 2011-02-11
501
=head3 Statement handle : C<sth()>
update pod
Yuki Kimoto authored on 2011-01-28
502

            
update pod
Yuki Kimoto authored on 2011-02-04
503
use <sth()> to get statement handle.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
504

            
505
    my $sth = $result->sth;
506

            
update pod
Yuki Kimoto authored on 2011-02-07
507
=head2 4. Filtering
update pod
Yuki Kimoto authored on 2011-01-28
508

            
update pod
Yuki Kimoto authored on 2011-02-07
509
L<DBIx::Custom> provide value filtering. 
510
For example, You maybe want to convert L<Time::Piece> object to
511
database date format when register data into database.
512
and convert database date fromat to L<Time::Piece> object
513
when get data from database.
update pod
Yuki Kimoto authored on 2011-01-28
514

            
update pod
Yuki Kimoto authored on 2011-02-11
515
=head3 Register filter : C<register_filter()>
update pod
Yuki Kimoto authored on 2011-01-28
516

            
update pod
Yuki Kimoto authored on 2011-02-11
517
use C<register_filter()> to register filter.
update pod
Yuki Kimoto authored on 2011-01-28
518

            
519
    $dbi->register_filter(
520
        # Time::Piece object to DATE format
521
        tp_to_date => sub {
522
            my $date = shift;
523

            
524
            return '0000-00-00' unless $tp;
525
            return $tp->strftime('%Y-%m-%d');
526
        },
527
        
528
        # DATE to Time::Piece object
529
        date_to_tp => sub {
530
            my $date = shift;
531

            
532
            return if $date eq '0000-00-00';
533
            return Time::Piece->strptime($date, '%Y-%m-%d');
534
        },
535
    );
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
536

            
update pod
Yuki Kimoto authored on 2011-02-07
537
Registered filter is used by C<apply_filter()> or etc.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
538

            
update pod
Yuki Kimoto authored on 2011-02-11
539
=head3 Apply filter : C<apply_filter()>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
540

            
update pod
Yuki Kimoto authored on 2011-02-07
541
use C<apply_filter()> to apply registered filter.
update pod
Yuki Kimoto authored on 2011-01-28
542

            
543
    $dbi->apply_filter('book',
544
        issue_date => {out => 'tp_to_date', in => 'date_to_tp'},
545
        first_issue_date => {out => 'tp_to_date', in => 'date_to_tp'}
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
546
    );
547

            
update pod
Yuki Kimoto authored on 2011-02-07
548
First argument is table name. Arguments after first argument are pairs of column
549
name and fitering rule. C<out> of filtering rule is filter which is used when data
550
is send to database. C<in> of filtering rule is filter which is used when data
551
is got from database. 
552

            
553
You can specify code reference as filter.
update pod
Yuki Kimoto authored on 2011-01-28
554

            
555
    issue_date => {out => sub { ... }, in => sub { ... }}
556

            
update pod
Yuki Kimoto authored on 2011-02-07
557
Applied filter become effective at insert()>, C<update()>, C<update_all()>,
558
C<delete()>, C<delete_all()>, C<select()>.
update pod
Yuki Kimoto authored on 2011-01-28
559

            
560
    my $tp = Time::Piece->strptime('2010/10/14', '%Y/%m/%d');
update pod
Yuki Kimoto authored on 2011-02-07
561
    my $result = $dbi->select(table => 'book', where => {issue_date => $tp});
update pod
Yuki Kimoto authored on 2011-01-28
562

            
update pod
Yuki Kimoto authored on 2011-02-07
563
When data is send to database, L<Time::Piece> object is converted
564
to database date format "2010-10-14"
update pod
Yuki Kimoto authored on 2011-01-28
565

            
update pod
Yuki Kimoto authored on 2011-02-07
566
When data is fetched, database date format is
567
converted to L<Time::Piece> object.
update pod
Yuki Kimoto authored on 2011-01-28
568

            
569
    my $row = $resutl->fetch_hash_first;
570
    my $tp = $row->{issue_date};
571

            
update pod
Yuki Kimoto authored on 2011-02-07
572
You can also use column name which contains table name.
update pod
Yuki Kimoto authored on 2011-01-28
573

            
574
    $dbi->select(
575
        table => 'book',
update pod
Yuki Kimoto authored on 2011-02-07
576
        where => {'book.issue_date' => $tp}
update pod
Yuki Kimoto authored on 2011-01-28
577
    );
578

            
update pod
Yuki Kimoto authored on 2011-02-11
579
In fetching, Filter is effective if you use "TABLE__COLUMN" as column name.
580

            
581
    my $result = $dbi->execute(
582
       "select issue_date as book__issue_date from book");
583

            
584
You can apply C<end> filter execute after C<in> filter.
585

            
586
    $dbi->apply_filter('book',
587
        issue_date => {out => 'tp_to_date', in => 'date_to_tp',
588
                       end => 'tp_to_displaydate'},
589
    );
590

            
update pod
Yuki Kimoto authored on 2011-02-07
591
=head3 Individual filter C<filter>
update pod
Yuki Kimoto authored on 2011-01-28
592

            
update pod
Yuki Kimoto authored on 2011-02-07
593
You can apply individual filter .
594
This filter overwrite the filter by C<apply_filter()>
update pod
Yuki Kimoto authored on 2011-01-28
595

            
update pod
Yuki Kimoto authored on 2011-02-07
596
use C<filter> option to apply individual filter
597
when data is send to database.
598
This option is used at C<insert()>, C<update()>,
599
C<update_all()>, C<delete()>, C<delete_all()>, C<select()>,
600
C<execute()>.
update pod
Yuki Kimoto authored on 2011-01-28
601

            
update pod
Yuki Kimoto authored on 2011-02-07
602
C<insert()> example:
update pod
Yuki Kimoto authored on 2011-01-28
603

            
604
    $dbi->insert(
605
        table => 'book',
606
        param => {issue_date => $tp, first_issue_date => $tp},
607
        filter => {issue_date => 'tp_to_date', first_issue_date => 'tp_to_date'}
608
    );
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
609

            
update pod
Yuki Kimoto authored on 2011-02-07
610
C<execute()> example:
update pod
Yuki Kimoto authored on 2011-01-28
611

            
612
my $sql = <<"EOS";
613
select YEAR(issue_date) as issue_year
614
from book
615
where YEAR(issue_date) = {? issue_year}
616
EOS
617
   
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
618
    my $result = $dbi->execute(
update pod
Yuki Kimoto authored on 2011-01-28
619
        $sql,
620
        param => {issue_year => '2010'},
621
        filter => {issue_year => 'tp_to_year'}
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
622
    );
623

            
update pod
Yuki Kimoto authored on 2011-02-07
624
You can also apply indivisual filter when you fetch row.
625
use C<DBIx::Custom::Result>'s C<filter()>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
626

            
update pod
Yuki Kimoto authored on 2011-01-28
627
    $result->filter(issue_year => 'year_to_tp');
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
628

            
update pod
Yuki Kimoto authored on 2011-02-07
629
=head3 End filtering : C<end_filter()>
update pod
Yuki Kimoto authored on 2011-01-28
630

            
update pod
Yuki Kimoto authored on 2011-02-07
631
You can add filter at end.
632
It is useful to create last output.
633
use C<end_filter()> to add end filter.
update pod
Yuki Kimoto authored on 2011-01-28
634

            
635
    $result->end_filter(issue_date => sub {
636
        my $tp = shift;
637
        
638
        return '' unless $tp;
639
        return $tp->strftime('%Y/%m/%d %h:%m:%s (%a)');
640
    });
641

            
update pod
Yuki Kimoto authored on 2011-02-07
642
In this example, L<Time::Piece> object is converted to readable format.
update pod
Yuki Kimoto authored on 2011-01-28
643

            
update pod
Yuki Kimoto authored on 2011-02-11
644
=head3 Automate applying filter : C<each_column()>
update pod
Yuki Kimoto authored on 2011-01-28
645

            
update pod
Yuki Kimoto authored on 2011-02-07
646
It is useful to apply filter automatically at date type columns.
647
You can use C<each_column()> to process all column infos.
update pod
Yuki Kimoto authored on 2011-01-28
648

            
649
    $dbi->each_column(
650
        sub {
651
            my ($self, $table, $column, $info) = @_;
652
            
653
            my $type = $info->{TYPE_NAME};
654
            
655
            my $filter = $type eq 'DATE'     ? {out => 'tp_to_date', in => 'date_to_tp'}
656
                       : $type eq 'DATETIME' ? {out => 'tp_to_datetime', in => 'datetime_to_tp'}
657
                                             : undef;
658
            
659
            $self->apply_filter($table, $column, $filter)
660
              if $filter;
661
        }
662
    );
663

            
update pod
Yuki Kimoto authored on 2011-02-11
664
C<each_column()> receive callback.
update pod
Yuki Kimoto authored on 2011-02-07
665
callback arguments are L<DBIx::Custom> object, table name, column name, column information.
666
Filter is applied automatically by column type.
update pod
Yuki Kimoto authored on 2011-01-28
667

            
update pod
Yuki Kimoto authored on 2011-02-07
668
=head2 5. Tag
update pod
Yuki Kimoto authored on 2011-01-28
669

            
update pod
Yuki Kimoto authored on 2011-02-07
670
=head3 Basic of Tag
update pod
Yuki Kimoto authored on 2011-01-28
671

            
update pod
Yuki Kimoto authored on 2011-02-07
672
You can embedd tag into SQL.
update pod
Yuki Kimoto authored on 2011-01-28
673

            
674
    select * from book where {= title} and {like author};
675

            
update pod
Yuki Kimoto authored on 2011-02-07
676
{= title} and {like author} are tag. Tag has the folloring format.
update pod
Yuki Kimoto authored on 2011-01-28
677

            
update pod
Yuki Kimoto authored on 2011-02-07
678
    {TAG_NAME ARG1 ARG2 ...}
update pod
Yuki Kimoto authored on 2011-01-28
679

            
update pod
Yuki Kimoto authored on 2011-02-07
680
Tag start C<{> and end C<}>. 
update pod
Yuki Kimoto authored on 2011-02-11
681
Don't insert space between C<{}> and tag name.
update pod
Yuki Kimoto authored on 2011-02-07
682

            
683
C<{> and C<}> are reserved word.
684
If you want to use these, escape it by '\';
update pod
Yuki Kimoto authored on 2011-01-28
685

            
686
    select from book \\{ ... \\}
687

            
update pod
Yuki Kimoto authored on 2011-02-07
688
\ is perl's escape character, you need two \.
689

            
690
Tag is expanded before executing SQL.
update pod
Yuki Kimoto authored on 2011-01-28
691

            
692
    select * from book where title = ? and author like ?;
693

            
update pod
Yuki Kimoto authored on 2011-02-07
694
use C<execute()> to execute SQL which contains tag
update pod
Yuki Kimoto authored on 2011-01-28
695

            
696
    my $sql = "select * from book where {= author} and {like title};"
697
    $dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'});
698

            
update pod
Yuki Kimoto authored on 2011-02-07
699
You can specify values embedded into place holder as hash reference using
700
C<param> option.
update pod
Yuki Kimoto authored on 2011-01-28
701

            
update pod
Yuki Kimoto authored on 2011-02-07
702
You can specify C<filter()> at C<execute()>.
update pod
Yuki Kimoto authored on 2011-01-28
703

            
704
    $dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'}
705
                  filter => {title => 'to_something');
706

            
update pod
Yuki Kimoto authored on 2011-02-07
707
Note that at C<execute()> the filter applied by C<apply_filter()>
708
don't has effective to columns.
update pod
Yuki Kimoto authored on 2011-02-11
709
You have to use C<table> tag in SQL
update pod
Yuki Kimoto authored on 2011-01-28
710

            
update pod
Yuki Kimoto authored on 2011-02-11
711
    my $sql = "select * from {table book} where {= author} and {like title};"
update pod
Yuki Kimoto authored on 2011-01-28
712

            
update pod
Yuki Kimoto authored on 2011-02-07
713
=head3 Tag list
update pod
Yuki Kimoto authored on 2011-01-28
714

            
update pod
Yuki Kimoto authored on 2011-02-07
715
The following tag is available.
update pod
Yuki Kimoto authored on 2011-01-28
716

            
update pod
Yuki Kimoto authored on 2011-02-11
717
=head4 C<table>
718

            
719
    {table NAME} -> NAME
720

            
721
This is used to specify table name in SQL.
722
If you specify table name, Filtering by 
723
C<apply_filter()> is effective.
724

            
update pod
Yuki Kimoto authored on 2011-01-31
725
=head4 C<?>
update pod
Yuki Kimoto authored on 2011-01-28
726

            
727
    {? NAME}    ->   ?
728

            
update pod
Yuki Kimoto authored on 2011-01-31
729
=head4 C<=>
update pod
Yuki Kimoto authored on 2011-01-28
730

            
731
    {= NAME}    ->   NAME = ?
732

            
update pod
Yuki Kimoto authored on 2011-01-31
733
=head4 C<E<lt>E<gt>>
update pod
Yuki Kimoto authored on 2011-01-28
734

            
735
    {<> NAME}   ->   NAME <> ?
736

            
update pod
Yuki Kimoto authored on 2011-01-31
737
=head4 C<E<lt>>
update pod
Yuki Kimoto authored on 2011-01-28
738

            
739
    {< NAME}    ->   NAME < ?
740

            
update pod
Yuki Kimoto authored on 2011-01-31
741
=head4 C<E<gt>>
update pod
Yuki Kimoto authored on 2011-01-28
742

            
743
    {> NAME}    ->   NAME > ?
744

            
update pod
Yuki Kimoto authored on 2011-01-31
745
=head4 C<E<gt>=>
update pod
Yuki Kimoto authored on 2011-01-28
746

            
747
    {>= NAME}   ->   NAME >= ?
748

            
update pod
Yuki Kimoto authored on 2011-01-31
749
=head4 C<E<lt>=>
update pod
Yuki Kimoto authored on 2011-01-28
750

            
751
    {<= NAME}   ->   NAME <= ?
752

            
update pod
Yuki Kimoto authored on 2011-01-31
753
=head4 C<like>
update pod
Yuki Kimoto authored on 2011-01-28
754

            
755
    {like NAME}   ->   NAME like ?
756

            
update pod
Yuki Kimoto authored on 2011-01-31
757
=head4 C<in>
update pod
Yuki Kimoto authored on 2011-01-28
758

            
759
    {in NAME COUNT}   ->   NAME in [?, ?, ..]
760

            
update pod
Yuki Kimoto authored on 2011-01-31
761
=head4 C<insert_param>
update pod
Yuki Kimoto authored on 2011-01-28
762

            
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
763
    {insert_param NAME1 NAME2}   ->   (NAME1, NAME2) values (?, ?)
update pod
Yuki Kimoto authored on 2011-01-28
764

            
update pod
Yuki Kimoto authored on 2011-01-31
765
=head4 C<update_param>
update pod
Yuki Kimoto authored on 2011-01-28
766

            
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
767
    {update_param NAME1 NAME2}   ->   set NAME1 = ?, NAME2 = ?
768

            
update pod
Yuki Kimoto authored on 2011-02-07
769
=head3 Manipulate same name's columns
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
770

            
update pod
Yuki Kimoto authored on 2011-02-07
771
It is ok if there are same name's columns.
772
Let's think two date comparison.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
773

            
update pod
Yuki Kimoto authored on 2011-01-28
774
    my $sql = "select * from table where {> date} and {< date};";
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
775

            
update pod
Yuki Kimoto authored on 2011-02-07
776
In this case, You specify paramter values as array reference.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
777

            
update pod
Yuki Kimoto authored on 2011-01-28
778
    my $dbi->execute($sql, param => {date => ['2010-10-01', '2012-02-10']});
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
779

            
update pod
Yuki Kimoto authored on 2011-02-11
780
=head3 Register Tag : C<register_tag()>
update pod
Yuki Kimoto authored on 2011-01-28
781

            
update pod
Yuki Kimoto authored on 2011-02-11
782
You can register custom tag.
update pod
Yuki Kimoto authored on 2011-02-07
783
use C<register_tag()> to register tag.
update pod
Yuki Kimoto authored on 2011-01-28
784

            
785
    $dbi->register_tag(
786
        '=' => sub {
787
            my $column = shift;
788
            
789
            return ["$column = ?", [$column]];
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
790
        }
791
    );
792

            
update pod
Yuki Kimoto authored on 2011-02-11
793
This is implementation of C<=> tag.
794
Tag format is the following one.
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
795

            
update pod
Yuki Kimoto authored on 2011-02-11
796
    {TAG_NAME ARG1 ARG2 ...}
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
797

            
update pod
Yuki Kimoto authored on 2011-02-11
798
In case C<=> tag. Format is
cleanup
Yuki Kimoto authored on 2011-01-12
799

            
update pod
Yuki Kimoto authored on 2011-01-28
800
    {= title}
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
801

            
update pod
Yuki Kimoto authored on 2011-02-11
802
So subroutine receive one argument "title".
803
You have to return array reference in the following format.
update pod
Yuki Kimoto authored on 2011-01-28
804

            
805
    [
update pod
Yuki Kimoto authored on 2011-02-11
806
        String after expanding,
807
        [COLUMN1(This is used for place holder), COLUMN2 , ...]
update pod
Yuki Kimoto authored on 2011-01-28
808
    ]
809

            
update pod
Yuki Kimoto authored on 2011-02-11
810
First element is expanded stirng. In this example,
update pod
Yuki Kimoto authored on 2011-01-28
811

            
812
    'title = ?'
813

            
update pod
Yuki Kimoto authored on 2011-02-11
814
Secount element is array reference which is used to embedd value to
815
place holder. In this example,
update pod
Yuki Kimoto authored on 2011-01-28
816

            
817
    ['title']
818

            
update pod
Yuki Kimoto authored on 2011-02-11
819
If there are more than one placeholders,
820
This elements is multipul.
update pod
Yuki Kimoto authored on 2011-01-28
821

            
update pod
Yuki Kimoto authored on 2011-02-11
822
You return the following array reference.
update pod
Yuki Kimoto authored on 2011-01-28
823

            
824
    ['title = ?', ['title']]
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
825

            
update pod
Yuki Kimoto authored on 2011-02-11
826
See source of L<DBIx::Custom::Tag> to see many implementation.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
827

            
update pod
Yuki Kimoto authored on 2011-02-11
828
=head2 6. Dinamically create where clause
update pod
Yuki Kimoto authored on 2011-01-28
829

            
update pod
Yuki Kimoto authored on 2011-02-11
830
=head3 Dinamically create where clause : where()
update pod
Yuki Kimoto authored on 2011-01-28
831

            
update pod
Yuki Kimoto authored on 2011-02-11
832
You want to search multiple conditions in many times.
833
Let's think the following three cases.
update pod
Yuki Kimoto authored on 2011-01-28
834

            
update pod
Yuki Kimoto authored on 2011-02-11
835
Case1: Search only C<title>
update pod
Yuki Kimoto authored on 2011-01-28
836

            
837
    where {= title}
838

            
update pod
Yuki Kimoto authored on 2011-02-11
839
Case2: Search only C<author>
update pod
Yuki Kimoto authored on 2011-01-28
840

            
841
    where {= author}
842

            
update pod
Yuki Kimoto authored on 2011-02-11
843
Case3: Search C<title> and C<author>
update pod
Yuki Kimoto authored on 2011-01-28
844

            
845
    where {= title} and {=author}
846

            
update pod
Yuki Kimoto authored on 2011-02-11
847
L<DBIx::Custom> support dinamic where clause creating.
848
At first, create L<DBIx::Custom::Where> object by C<where()>.
update pod
Yuki Kimoto authored on 2011-01-28
849

            
850
    my $where = $dbi->where;
851

            
update pod
Yuki Kimoto authored on 2011-02-11
852
Set clause by C<clause()>
update pod
Yuki Kimoto authored on 2011-01-28
853

            
854
    $where->clause(
855
        ['and', '{= title'}, '{= author}']
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
856
    );
857

            
update pod
Yuki Kimoto authored on 2011-02-11
858
C<clause> is the following format.
update pod
Yuki Kimoto authored on 2011-01-28
859

            
update pod
Yuki Kimoto authored on 2011-02-11
860
    ['or' or 'and', TAG1, TAG2, TAG3]
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
861

            
update pod
Yuki Kimoto authored on 2011-02-11
862
First argument is 'or' or 'and'.
863
Later than first argument are tag names.
update pod
Yuki Kimoto authored on 2011-01-28
864

            
update pod
Yuki Kimoto authored on 2011-02-11
865
You can write more complex format.
update pod
Yuki Kimoto authored on 2011-01-28
866

            
867
    ['and', 
868
      '{= title}', 
869
      ['or', '{= author}', '{like date}']
870
    ]
871

            
update pod
Yuki Kimoto authored on 2011-02-11
872
This mean "{=title} and ( {=author} or {like date} )".
873

            
874
After setting C<clause>, set C<param>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
875
    
update pod
Yuki Kimoto authored on 2011-02-11
876
    $where->param({title => 'Perl'});
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
877

            
update pod
Yuki Kimoto authored on 2011-02-11
878
In this example, parameter contains only title.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
879

            
update pod
Yuki Kimoto authored on 2011-02-11
880
If you execute C<string_to()>, you can get where clause
881
which contain only parameter name.
update pod
Yuki Kimoto authored on 2011-01-28
882

            
883
    my $where_clause = $where->to_string;
884

            
update pod
Yuki Kimoto authored on 2011-02-11
885
Parameter name is only title, the following where clause is created.
update pod
Yuki Kimoto authored on 2011-01-28
886

            
887
    where {= title}
888

            
update pod
Yuki Kimoto authored on 2011-02-11
889
You can also create where clause by stringification.
update pod
Yuki Kimoto authored on 2011-01-28
890

            
891
    my $where_clause = "$where";
892

            
update pod
Yuki Kimoto authored on 2011-02-11
893
This is useful to embbed it into SQL. 
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
894

            
update pod
Yuki Kimoto authored on 2011-02-11
895
=head3 In case where clause contains same name columns
check arguments of connect m...
Yuki Kimoto authored on 2010-12-20
896

            
update pod
Yuki Kimoto authored on 2011-02-11
897
Even if same name tags exists, you can create where clause.
898
Let's think that there are starting date and ending date.
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
899

            
update pod
Yuki Kimoto authored on 2011-01-28
900
    my $param = {start_date => '2010-11-15', end_date => '2011-11-21'};
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
901

            
update pod
Yuki Kimoto authored on 2011-02-11
902
In this case, you set parameter value as array reference.
update pod
Yuki Kimoto authored on 2011-01-28
903

            
904
    my $p = {date => ['2010-11-15', '2011-11-21']};
905

            
update pod
Yuki Kimoto authored on 2011-02-11
906
You can embbed these values into same name tags.
update pod
Yuki Kimoto authored on 2011-01-28
907

            
908
    $where->clause(
909
        ['and', '{> date}', '{< date}']
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
910
    );
update pod
Yuki Kimoto authored on 2011-01-28
911
    $where->param($p);
912

            
update pod
Yuki Kimoto authored on 2011-02-11
913
If starting date isn't exists, create the following parameter.
update pod
Yuki Kimoto authored on 2011-01-28
914

            
915
    my $p = {date => [$dbi->not_exists, '2011-11-21']};
916

            
update pod
Yuki Kimoto authored on 2011-02-11
917
You can get DBIx::Custom::NotExists object by C<not_exists()>
918
This mean correnspondinf value isn't exists.
update pod
Yuki Kimoto authored on 2011-01-28
919

            
update pod
Yuki Kimoto authored on 2011-02-11
920
If ending date isn't exists, create the following parameter.
update pod
Yuki Kimoto authored on 2011-01-28
921

            
922
    my $p = {date => ['2010-11-15']};
923

            
update pod
Yuki Kimoto authored on 2011-02-11
924
If both date isn't exists, create the following parameter.
update pod
Yuki Kimoto authored on 2011-01-28
925

            
926
    my $p = {date => []};
927

            
update pod
Yuki Kimoto authored on 2011-02-11
928
This logic is a little difficut. See the following ones.
update pod
Yuki Kimoto authored on 2011-01-28
929

            
930
    my @date;
931
    push @date, exists $param->{start_date} ? $param->{start_date}
932
                                            : $dbi->not_exists;
933
    push @date, $param->{end_date} if exists $param->{end_date};
934
    my $p = {date => \@date};
935

            
update pod
Yuki Kimoto authored on 2011-02-11
936
=head3 With C<select()>
update pod
Yuki Kimoto authored on 2011-01-28
937

            
update pod
Yuki Kimoto authored on 2011-02-11
938
You can pass L<DBIx::Custom::Where> object to C<where> of C<select()>.
update pod
Yuki Kimoto authored on 2011-01-28
939
    
940
    my $where = $dbi->where;
update pod
Yuki Kimoto authored on 2011-02-11
941
    $where->clause(['and', '{= title}', '{= author}']);
942
    $where->param({title => 'Perl'});
update pod
Yuki Kimoto authored on 2011-01-28
943
    my $result = $dbi->select(table => 'book', where => $where);
944

            
update pod
Yuki Kimoto authored on 2011-02-11
945
You can also pass it to C<where> of C<update()>�AC<delete()>
update pod
Yuki Kimoto authored on 2011-01-28
946

            
update pod
Yuki Kimoto authored on 2011-02-11
947
=head3 With C<execute()>
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
948

            
update pod
Yuki Kimoto authored on 2011-02-11
949
L<DBIx::Custom::Where> object is embedded into SQL.
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
950

            
update pod
Yuki Kimoto authored on 2011-01-28
951
    my $where = $dbi->where;
update pod
Yuki Kimoto authored on 2011-02-11
952
    $where->clause(['and', '{= title}', '{= author}']);
953
    $where->param({title => 'Perl'});
update pod
Yuki Kimoto authored on 2011-01-28
954

            
update pod
Yuki Kimoto authored on 2011-02-11
955
    my $sql = <<"EOS";
956
    select * from {table book};
update pod
Yuki Kimoto authored on 2011-01-28
957
    $where
958
    EOS
959

            
960
    $dbi->execute($sql, param => $param);
961

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
962
=head2 7. Model
update pod
Yuki Kimoto authored on 2011-01-28
963

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
964
=head3 Model
update pod
Yuki Kimoto authored on 2011-01-28
965

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
966
you can define model extending L<DBIx::Custom::Model>
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
967
to improve source code view.
update pod
Yuki Kimoto authored on 2011-01-28
968

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
969
    package MyModel::book;
970
    use base 'DBIx::Custom::Model';
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
971
    
972
    sub insert { ... }
973
    sub list { ... }
update pod
Yuki Kimoto authored on 2011-01-28
974

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
975
You can include and instantiate this class
update pod
Yuki Kimoto authored on 2011-01-28
976

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
977
    $dbi->include_model(
978
        MyModel => [
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
979
            'book',
980
        ]
981
    );
update pod
Yuki Kimoto authored on 2011-01-28
982

            
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
983
First argument is name space.
984
Second argument is array reference of class base names.
update pod
Yuki Kimoto authored on 2011-01-28
985

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
986
If you don't specify second argument, All models under name space is
987
included.
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
988

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
989
    $dbi->include_model('MyModel');
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
990

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
991
Note that in this case name spece module is needed.
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
992

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
993
    # MyModel.pm
994
    package MyModel;
995
    
996
    use base 'DBIx::Custom::Model';
997

            
998
The follwoing modules location is needed.
999

            
1000
    MyModel.pm
1001
    MyModel / book.pm
1002
            / company.pm
1003
    
1004
You can use model like this.
1005

            
1006
    my $result = $dbi->model('book')->list;
1007

            
1008
In mode, You can use such as methods,
1009
C<insert()>, C<update()>, C<update_all()>,
1010
C<delete()>, C<delete_all()>, C<select()>
1011
without C<table> option.
1012

            
1013
    $dbi->model('book')->insert(param => $param);
update pod
Yuki Kimoto authored on 2011-01-28
1014

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1015
Model is L<DBIx::Custom::Model>.
update pod
Yuki Kimoto authored on 2011-01-28
1016

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1017
If you need table name�Ayou can get it by C<table()>.
update pod
Yuki Kimoto authored on 2011-01-28
1018

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1019
    my $table = $model->table;
update pod
Yuki Kimoto authored on 2011-01-28
1020

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1021
You can get L<DBIx::Custom>.
update pod
Yuki Kimoto authored on 2011-01-28
1022

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1023
    my $dbi = $model->dbi;
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1024

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1025
You can also call all methods of L<DBIx::Custom> and L<DBI>. 
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
1026

            
update pod
Yuki Kimoto authored on 2011-02-11
1027
    # DBIx::Custom method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1028
    $model->execute($sql);
update pod
Yuki Kimoto authored on 2011-02-11
1029
    
1030
    # DBI method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1031
    $model->begin_work;
1032
    $model->commit;
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
1033

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1034
=head2 Model Examples
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1035

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1036
Model examples
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1037

            
update pod
Yuki Kimoto authored on 2011-01-28
1038
    package MyDBI;
1039
    
1040
    use base 'DBIx::Custom';
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1041
    
update pod
Yuki Kimoto authored on 2011-01-28
1042
    sub connect {
1043
        my $self = shift->SUPER::connect(@_);
1044
        
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1045
        $self->include_model(
1046
            MyModel => [
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1047
                'book',
1048
                'company'
1049
            ]
update pod
Yuki Kimoto authored on 2011-01-28
1050
        );
1051
    }
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1052
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1053
    package MyModel::book;
1054
    use base 'DBIx::Custom::Model';
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1055
    
1056
    sub insert { ... }
1057
    sub list { ... }
1058
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1059
    package MyModel::company;
1060
    use base 'DBIx::Custom::Model';
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1061
    
1062
    sub insert { ... }
1063
    sub list { ... }
update pod
Yuki Kimoto authored on 2011-01-28
1064

            
update pod
Yuki Kimoto authored on 2011-02-11
1065
=head2 8. Improve performance
update pod
Yuki Kimoto authored on 2011-01-28
1066

            
update pod
Yuki Kimoto authored on 2011-02-11
1067
=head3 Create query
update pod
Yuki Kimoto authored on 2011-01-28
1068

            
update pod
Yuki Kimoto authored on 2011-02-11
1069
If you can't get performance, create query by C<query> option.
1070
For example, many insert is needed.
update pod
Yuki Kimoto authored on 2011-01-28
1071

            
update pod
Yuki Kimoto authored on 2011-02-11
1072
    my $params = [
1073
        {title => 'Perl', author => 'Ken'},
1074
        {title => 'Good day', author => 'Tom'}
1075
    ]
1076
    my $query = $dbi->insert(table => 'book', param => $params->[0], query => 1);
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1077

            
update pod
Yuki Kimoto authored on 2011-02-11
1078
Return value is L<DBIx::Custom::Query> object.
1079
This query is executed by C<execute()>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1080

            
update pod
Yuki Kimoto authored on 2011-02-11
1081
    foreach my $param (@$params) {
1082
        $dbi->execute($query, $param);
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1083
    }
1084

            
update pod
Yuki Kimoto authored on 2011-02-11
1085
Performance is improved because statement handle is reused
1086
C<query> option is used in C<insert()>, C<update()>, C<update_all()>,
1087
C<delete()>, C<delete_all()>.
1088

            
1089
Note that parameters count is same as method for creating query and C<execute()>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1090

            
update pod
Yuki Kimoto authored on 2011-02-11
1091
You can create query from any SQL by C<create_query()>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1092

            
update pod
Yuki Kimoto authored on 2011-02-11
1093
    my $query = $dbi->create_query(
1094
        "insert into book {insert_param title author};";
1095
    );
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1096

            
update pod
Yuki Kimoto authored on 2011-02-11
1097
=head2 9. Other features
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1098

            
update pod
Yuki Kimoto authored on 2011-02-11
1099
=head3 Add method
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1100

            
update pod
Yuki Kimoto authored on 2011-02-11
1101
You can add method to L<DBIx::Custom> object.
update pod
Yuki Kimoto authored on 2011-02-11
1102
use C<method()>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1103

            
update pod
Yuki Kimoto authored on 2011-01-28
1104
    $dbi->method(
1105
        update_or_insert => sub {
1106
            my $self = shift;
1107
            # something
1108
        },
1109
        find_or_create   => sub {
1110
            my $self = shift;
1111
            # something
1112
        }
1113
    );
1114

            
update pod
Yuki Kimoto authored on 2011-02-11
1115
You can call these methods from L<DBIx::Custom> object.
update pod
Yuki Kimoto authored on 2011-01-28
1116

            
1117
    $dbi->update_or_insert;
1118
    $dbi->find_or_create;
1119

            
update pod
Yuki Kimoto authored on 2011-02-11
1120
=head3 Change result class
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1121

            
update pod
Yuki Kimoto authored on 2011-02-11
1122
You can change result class. By default it is L<DBIx::Custom::Result>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1123

            
update pod
Yuki Kimoto authored on 2011-01-28
1124
    package MyResult;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1125
    use base 'DBIx::Custom::Result';
1126
    
1127
    sub some_method { ... }
1128

            
1129
    1;
1130
    
1131
    package main;
1132
    
update pod
Yuki Kimoto authored on 2011-01-28
1133
    use MyResult;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1134
    
1135
    my $dbi = DBIx::Custom->connect(...);
update pod
Yuki Kimoto authored on 2011-01-28
1136
    $dbi->result_class('MyResult');
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1137

            
update pod
Yuki Kimoto authored on 2011-02-11
1138
=head3 Caching
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1139

            
update pod
Yuki Kimoto authored on 2011-02-11
1140
SQL after parsing tag is cached for performance.
1141
You can set C<cache()>. By default, chaching is true.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1142

            
update pod
Yuki Kimoto authored on 2011-01-28
1143
    $dbi->cache(1);
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1144

            
update pod
Yuki Kimoto authored on 2011-02-11
1145
The way to cache is changed by C<cache_method()>.
1146
Default method is the following one.
1147
Cache is saved to memory.
update pod
Yuki Kimoto authored on 2011-01-28
1148

            
1149
    $dbi->cache_method(sub {
1150
        sub {
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1151
            my $self = shift;
update pod
Yuki Kimoto authored on 2011-01-28
1152
            
1153
            $self->{_cached} ||= {};
1154
            
1155
            if (@_ > 1) {
update pod
Yuki Kimoto authored on 2011-02-11
1156
                # Save cache
update pod
Yuki Kimoto authored on 2011-01-28
1157
                $self->{_cached}{$_[0]} = $_[1] 
1158
            }
1159
            else {
update pod
Yuki Kimoto authored on 2011-02-11
1160
                # Get cache
update pod
Yuki Kimoto authored on 2011-01-28
1161
                return $self->{_cached}{$_[0]}
1162
            }
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1163
        }
update pod
Yuki Kimoto authored on 2011-01-28
1164
    });
1165
    
update pod
Yuki Kimoto authored on 2011-02-11
1166
First argument is L<DBIx::Custom> object.
1167
Second argument is SQL before parsing.
1168
Third argument is SQL information after parsing. This is hash reference.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1169

            
update pod
Yuki Kimoto authored on 2011-02-11
1170
If third argument exists, you save cache,
1171
and if third argument isn't exists, you get chace.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1172

            
pod fix
Yuki Kimoto authored on 2011-01-21
1173
=head1 EXAMPLES
1174

            
update pod
Yuki Kimoto authored on 2011-02-11
1175
You can see exsamples in the following wiki.
1176

            
pod fix
Yuki Kimoto authored on 2011-01-21
1177
L<DBIx::Custom Wiki|https://github.com/yuki-kimoto/DBIx-Custom/wiki> - Many useful examples
add examples
Yuki Kimoto authored on 2011-01-07
1178

            
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1179
=cut