Newer Older
1307 lines | 31.884kb
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(
DBIx::Custom::Model select()...
Yuki Kimoto authored on 2011-02-22
316
        table    => 'book',
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

            
add experimental update_at()...
Yuki Kimoto authored on 2011-02-21
383
=head3 Update by using primary key : C<update_at()>
384

            
385
To update row by using primary key, use C<update_at()>
386

            
387
    $dbi->update_at(
388
        table => 'book', primary_key => ['id'],
389
        where => ['123'], param => {name => 'Ken'}
390
    );
391

            
392
In this example, row which id column is 123 is updated.
393
NOTE that you must pass array reference as C<where>.
394
If C<param> contains primary key, the key and value is delete from C<param>.
395

            
396
=head3 Delete by using primary key : C<delete_at()>
397

            
398
To delete row by using primary key, use C<delete_at()>
399

            
400
    $dbi->delete_at(table => 'book', primary_key => ['id'], where => ['123']);
401

            
402
In this example, row which id column is 123 is deleted.
403
NOTE that you must pass array reference as C<where>.
404

            
405
You can also write arguments like this.
406

            
407
    $dbi->delete_at(table => 'book', primary_key => ['id'], param => {id => '123'});
408

            
409
=head3 Select by using primary key : C<select_at()>
410

            
411
To select row by using primary key, use C<select_at()>.
412

            
413
    $dbi->select_at(table => 'book', primary_key => ['id'], where => ['123']);
414

            
415
In this example, row which id colunm is 123 is selected.
416
NOTE that you must pass array reference as C<where>.
417

            
418
You can also write arguments like this.
419

            
420
    $dbi->select_at(table => 'book', primary_key => ['id'], param => {id => '123'});
421

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

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

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

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

            
431
    my $row = $result->fetch;
432

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

            
435
    while (my $row = $result->fetch) {
update pod
Yuki Kimoto authored on 2011-01-28
436
        my $title  = $row->[0];
437
        my $author = $row->[1];
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
438
    }
439

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

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

            
444
    my $row = $result->fetch_first;
445

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

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

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

            
454
    while (my $rows = $result->fetch_multi(2)) {
455
        my $title0   = $rows->[0][0];
456
        my $author0  = $rows->[0][1];
457
        
458
        my $title1   = $rows->[1][0];
459
        my $author1  = $rows->[1][1];
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
460
    }
update pod
Yuki Kimoto authored on 2011-01-28
461

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

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

            
466
    [
467
        ['Perl', 'Ken'],
468
        ['Ruby', 'Mark']
469
    ]
470

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

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

            
476
    my $rows = $result->fetch_all;
477

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

            
480
    [
481
        ['Perl', 'Ken'],
482
        ['Ruby', 'Mark']
483
    ]
484

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

            
update pod
Yuki Kimoto authored on 2011-02-04
487
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
488

            
489
    while (my $row = $result->fetch_hash) {
490
        my $title  = $row->{title};
491
        my $author = $row->{author};
492
    }
493

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

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

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

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

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

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

            
509
    while (my $rows = $result->fetch_hash_multi(5)) {
update pod
Yuki Kimoto authored on 2011-01-28
510
        my $title0   = $rows->[0]{title};
511
        my $author0  = $rows->[0]{author};
512
        my $title1  = $rows->[1]{title};
513
        my $author1 = $rows->[1]{author};
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
514
    }
update pod
Yuki Kimoto authored on 2011-01-28
515

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

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

            
520
    [
521
        {title => 'Perl', author => 'Ken'},
522
        {title => 'Ruby', author => 'Mark'}
523
    ]
524

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

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

            
531
    my $rows = $result->fetch_hash_all;
532

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

            
535
    [
536
        {title => 'Perl', author => 'Ken'},
537
        {title => 'Ruby', author => 'Mark'}
538
    ]
539

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

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

            
544
    my $sth = $result->sth;
545

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

            
update pod
Yuki Kimoto authored on 2011-02-07
548
L<DBIx::Custom> provide value filtering. 
549
For example, You maybe want to convert L<Time::Piece> object to
550
database date format when register data into database.
551
and convert database date fromat to L<Time::Piece> object
552
when get data from database.
update pod
Yuki Kimoto authored on 2011-01-28
553

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

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

            
558
    $dbi->register_filter(
559
        # Time::Piece object to DATE format
560
        tp_to_date => sub {
561
            my $date = shift;
562

            
563
            return '0000-00-00' unless $tp;
564
            return $tp->strftime('%Y-%m-%d');
565
        },
566
        
567
        # DATE to Time::Piece object
568
        date_to_tp => sub {
569
            my $date = shift;
570

            
571
            return if $date eq '0000-00-00';
572
            return Time::Piece->strptime($date, '%Y-%m-%d');
573
        },
574
    );
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
575

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

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

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

            
582
    $dbi->apply_filter('book',
583
        issue_date => {out => 'tp_to_date', in => 'date_to_tp'},
584
        first_issue_date => {out => 'tp_to_date', in => 'date_to_tp'}
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
585
    );
586

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

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

            
594
    issue_date => {out => sub { ... }, in => sub { ... }}
595

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

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

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

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

            
608
    my $row = $resutl->fetch_hash_first;
609
    my $tp = $row->{issue_date};
610

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

            
613
    $dbi->select(
614
        table => 'book',
update pod
Yuki Kimoto authored on 2011-02-07
615
        where => {'book.issue_date' => $tp}
update pod
Yuki Kimoto authored on 2011-01-28
616
    );
617

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

            
620
    my $result = $dbi->execute(
621
       "select issue_date as book__issue_date from book");
622

            
623
You can apply C<end> filter execute after C<in> filter.
624

            
625
    $dbi->apply_filter('book',
626
        issue_date => {out => 'tp_to_date', in => 'date_to_tp',
627
                       end => 'tp_to_displaydate'},
628
    );
629

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

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

            
update pod
Yuki Kimoto authored on 2011-02-07
635
use C<filter> option to apply individual filter
636
when data is send to database.
637
This option is used at C<insert()>, C<update()>,
638
C<update_all()>, C<delete()>, C<delete_all()>, C<select()>,
639
C<execute()>.
update pod
Yuki Kimoto authored on 2011-01-28
640

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

            
643
    $dbi->insert(
644
        table => 'book',
645
        param => {issue_date => $tp, first_issue_date => $tp},
646
        filter => {issue_date => 'tp_to_date', first_issue_date => 'tp_to_date'}
647
    );
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
648

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

            
651
my $sql = <<"EOS";
652
select YEAR(issue_date) as issue_year
653
from book
654
where YEAR(issue_date) = {? issue_year}
655
EOS
656
   
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
657
    my $result = $dbi->execute(
update pod
Yuki Kimoto authored on 2011-01-28
658
        $sql,
659
        param => {issue_year => '2010'},
660
        filter => {issue_year => 'tp_to_year'}
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
661
    );
662

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

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

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

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

            
674
    $result->end_filter(issue_date => sub {
675
        my $tp = shift;
676
        
677
        return '' unless $tp;
678
        return $tp->strftime('%Y/%m/%d %h:%m:%s (%a)');
679
    });
680

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

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

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

            
688
    $dbi->each_column(
689
        sub {
690
            my ($self, $table, $column, $info) = @_;
691
            
692
            my $type = $info->{TYPE_NAME};
693
            
694
            my $filter = $type eq 'DATE'     ? {out => 'tp_to_date', in => 'date_to_tp'}
695
                       : $type eq 'DATETIME' ? {out => 'tp_to_datetime', in => 'datetime_to_tp'}
696
                                             : undef;
697
            
698
            $self->apply_filter($table, $column, $filter)
699
              if $filter;
700
        }
701
    );
702

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

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

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

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

            
713
    select * from book where {= title} and {like author};
714

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

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

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

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

            
725
    select from book \\{ ... \\}
726

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

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

            
731
    select * from book where title = ? and author like ?;
732

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

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

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

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

            
743
    $dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'}
744
                  filter => {title => 'to_something');
745

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

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

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

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

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

            
758
    {table NAME} -> NAME
759

            
760
This is used to specify table name in SQL.
761
If you specify table name, Filtering by 
762
C<apply_filter()> is effective.
763

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

            
766
    {? NAME}    ->   ?
767

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

            
770
    {= NAME}    ->   NAME = ?
771

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

            
774
    {<> NAME}   ->   NAME <> ?
775

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

            
778
    {< NAME}    ->   NAME < ?
779

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

            
782
    {> NAME}    ->   NAME > ?
783

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

            
786
    {>= NAME}   ->   NAME >= ?
787

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

            
790
    {<= NAME}   ->   NAME <= ?
791

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

            
794
    {like NAME}   ->   NAME like ?
795

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

            
798
    {in NAME COUNT}   ->   NAME in [?, ?, ..]
799

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

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

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

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

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

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

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

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

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

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

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

            
824
    $dbi->register_tag(
825
        '=' => sub {
826
            my $column = shift;
827
            
828
            return ["$column = ?", [$column]];
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
829
        }
830
    );
831

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

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

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

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

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

            
844
    [
update pod
Yuki Kimoto authored on 2011-02-11
845
        String after expanding,
846
        [COLUMN1(This is used for place holder), COLUMN2 , ...]
update pod
Yuki Kimoto authored on 2011-01-28
847
    ]
848

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

            
851
    'title = ?'
852

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

            
856
    ['title']
857

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

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

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

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

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

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

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

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

            
876
    where {= title}
877

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

            
880
    where {= author}
881

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

            
884
    where {= title} and {=author}
885

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

            
889
    my $where = $dbi->where;
890

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

            
893
    $where->clause(
894
        ['and', '{= title'}, '{= author}']
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
895
    );
896

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

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

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

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

            
906
    ['and', 
907
      '{= title}', 
908
      ['or', '{= author}', '{like date}']
909
    ]
910

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

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

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

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

            
922
    my $where_clause = $where->to_string;
923

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

            
926
    where {= title}
927

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

            
930
    my $where_clause = "$where";
931

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

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

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

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

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

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

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

            
947
    $where->clause(
948
        ['and', '{> date}', '{< date}']
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
949
    );
update pod
Yuki Kimoto authored on 2011-01-28
950
    $where->param($p);
951

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

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

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

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

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

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

            
965
    my $p = {date => []};
966

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

            
969
    my @date;
970
    push @date, exists $param->{start_date} ? $param->{start_date}
971
                                            : $dbi->not_exists;
972
    push @date, $param->{end_date} if exists $param->{end_date};
973
    my $p = {date => \@date};
974

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

            
update pod
Yuki Kimoto authored on 2011-02-11
977
You can pass L<DBIx::Custom::Where> object to C<where> of C<select()>.
update pod
Yuki Kimoto authored on 2011-01-28
978
    
979
    my $where = $dbi->where;
update pod
Yuki Kimoto authored on 2011-02-11
980
    $where->clause(['and', '{= title}', '{= author}']);
981
    $where->param({title => 'Perl'});
update pod
Yuki Kimoto authored on 2011-01-28
982
    my $result = $dbi->select(table => 'book', where => $where);
983

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-02-11
994
    my $sql = <<"EOS";
995
    select * from {table book};
update pod
Yuki Kimoto authored on 2011-01-28
996
    $where
997
    EOS
998

            
999
    $dbi->execute($sql, param => $param);
1000

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

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

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1008
At first, you create basic model class extending <DBIx::Custom::Model>.
update pod
Yuki Kimoto authored on 2011-01-28
1009

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1010
    package MyModel;
1011
    
1012
    use base 'DBIx::Custom::Model';
update pod
Yuki Kimoto authored on 2011-01-28
1013

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1014
Next, you create each model classes.
update pod
Yuki Kimoto authored on 2011-01-28
1015

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1016
MyModel::book
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1017

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1018
    package MyModel::book;
1019
    
1020
    use base 'MyModel';
1021
    
1022
    sub insert { ... }
1023
    sub list { ... }
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1024

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1025
MyModel::company
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1026

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1027
    package MyModel::company;
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1028
    
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1029
    use base 'MyModel';
1030
    
1031
    sub insert { ... }
1032
    sub list { ... }
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1033

            
1034
The follwoing modules location is needed.
1035

            
1036
    MyModel.pm
1037
    MyModel / book.pm
1038
            / company.pm
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1039

            
1040
You can include these models by C<include_model()>
1041

            
1042
    $dbi->include_model('MyModel');
1043

            
1044
First argument is name space of model.
1045

            
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1046
You can use model like this.
1047

            
1048
    my $result = $dbi->model('book')->list;
1049

            
1050
In mode, You can use such as methods,
1051
C<insert()>, C<update()>, C<update_all()>,
1052
C<delete()>, C<delete_all()>, C<select()>
1053
without C<table> option.
1054

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

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

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

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-02-11
1069
    # DBIx::Custom method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1070
    $model->execute($sql);
update pod
Yuki Kimoto authored on 2011-02-11
1071
    
1072
    # DBI method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1073
    $model->begin_work;
1074
    $model->commit;
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
1075

            
add models() attribute
Yuki Kimoto authored on 2011-02-21
1076
If you want to get all models, you can get them by keys of C<models()>.
1077

            
1078
    my @models = keys %{$self->models};
1079

            
add DBIx::Custom::Model fore...
Yuki Kimoto authored on 2011-02-21
1080
You can set primary key to model.
1081

            
1082
   $model->primary_key(['id', 'number_id']);
1083

            
1084
Primary key is used by C<update_at()>, C<delete_at()>,
1085
C<select_at()>.
1086

            
add DBIx::Custom::Model colu...
Yuki Kimoto authored on 2011-02-21
1087
You can set column names
1088

            
1089
    $model->columns(['id', 'number_id']);
1090

            
add experimental setup_model...
Yuki Kimoto authored on 2011-02-21
1091
Column names is automarically set by C<setup_model()>.
1092
This method is needed to be call after C<include_model()>.
1093

            
1094
    $dbi->setup_model;
1095

            
cleanup
Yuki Kimoto authored on 2011-02-22
1096
You can set C<relation>
1097

            
1098
    $model->relation({'book.company_id' => 'company.id'});
1099

            
1100
This relation is used by C<select()>, C<select_at()>
1101

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1102
=head2 Class name, Model name, Table name
1103

            
1104
Class name, model name, and table name is a little different.
1105
Generally Class name is model name, and table name is model name.
1106

            
1107
    CLASS        MODEL              TABLE
1108
    book         (CLASS) -> book    (MODEL) -> book
1109

            
1110
You can change model name.
1111

            
1112
    package MyModel::book;
1113
    
1114
    __PACAKGE__->attr(name => 'book_model');
1115

            
1116
    CLASS        MODEL         TABLE
1117
    book         book_model    (MODEL) -> book_model
1118

            
1119
Model name is the name used by L<model()> of L<DBIx::Custom>.
1120

            
1121
    $dbi->model('book_model');
1122

            
1123
You can change table name.
1124

            
1125
    package MyModel::book;
1126
    
1127
    __PACAKGE__->attr(table => 'book_table');
1128

            
1129
    CLASS        MODEL              TABLE
1130
    book         (CLASS) -> book    book_table
1131

            
1132
Table name is the table really accessed.
1133

            
1134
    $dbi->model('book')->insert(...); # access to "book_table"
1135

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-22
1136
=head2 Create column clause automatically : column_clause()
1137

            
1138
To create column clause automatically, use C<column_clause()>.
1139
Valude of C<table> and C<columns> is used.
1140

            
1141
    my $column_clause = $model->column_clause;
1142

            
1143
If C<table> is 'book'�AC<column> is ['id', 'name'],
1144
the following clause is created.
1145

            
1146
    book.id as id, book.name as name
1147

            
1148
These column name is for removing column name ambiguities.
1149

            
1150
If you remove some columns, use C<remove> option.
1151

            
1152
    my $column_clause = $model->column_clause(remove => ['id']);
1153

            
1154
If you add some column, use C<add> option.
1155

            
1156
    my $column_clause = $model->column_clause(add => ['company.id as company__id']);
1157

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

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

            
update pod
Yuki Kimoto authored on 2011-01-28
1162
    package MyDBI;
1163
    
1164
    use base 'DBIx::Custom';
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1165
    
update pod
Yuki Kimoto authored on 2011-01-28
1166
    sub connect {
1167
        my $self = shift->SUPER::connect(@_);
1168
        
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1169
        $self->include_model(
1170
            MyModel => [
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1171
                'book',
1172
                'company'
1173
            ]
update pod
Yuki Kimoto authored on 2011-01-28
1174
        );
1175
    }
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1176
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1177
    package MyModel::book;
1178
    use base 'DBIx::Custom::Model';
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1179
    
add experimental update_at()...
Yuki Kimoto authored on 2011-02-21
1180
    __PACKAGE__->attr('primary_key' => sub { ['id'] };
1181
    
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1182
    sub insert { ... }
1183
    sub list { ... }
1184
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1185
    package MyModel::company;
1186
    use base 'DBIx::Custom::Model';
add experimental update_at()...
Yuki Kimoto authored on 2011-02-21
1187

            
1188
    __PACKAGE__->attr('primary_key' => sub { ['id'] };
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1189
    
1190
    sub insert { ... }
1191
    sub list { ... }
update pod
Yuki Kimoto authored on 2011-01-28
1192

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

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

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

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

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

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

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

            
1217
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
1218

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

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-01-28
1232
    $dbi->method(
1233
        update_or_insert => sub {
1234
            my $self = shift;
1235
            # something
1236
        },
1237
        find_or_create   => sub {
1238
            my $self = shift;
1239
            # something
1240
        }
1241
    );
1242

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

            
1245
    $dbi->update_or_insert;
1246
    $dbi->find_or_create;
1247

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

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

            
update pod
Yuki Kimoto authored on 2011-01-28
1252
    package MyResult;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1253
    use base 'DBIx::Custom::Result';
1254
    
1255
    sub some_method { ... }
1256

            
1257
    1;
1258
    
1259
    package main;
1260
    
update pod
Yuki Kimoto authored on 2011-01-28
1261
    use MyResult;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1262
    
1263
    my $dbi = DBIx::Custom->connect(...);
update pod
Yuki Kimoto authored on 2011-01-28
1264
    $dbi->result_class('MyResult');
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1265

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

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

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

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

            
1277
    $dbi->cache_method(sub {
1278
        sub {
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1279
            my $self = shift;
update pod
Yuki Kimoto authored on 2011-01-28
1280
            
1281
            $self->{_cached} ||= {};
1282
            
1283
            if (@_ > 1) {
update pod
Yuki Kimoto authored on 2011-02-11
1284
                # Save cache
update pod
Yuki Kimoto authored on 2011-01-28
1285
                $self->{_cached}{$_[0]} = $_[1] 
1286
            }
1287
            else {
update pod
Yuki Kimoto authored on 2011-02-11
1288
                # Get cache
update pod
Yuki Kimoto authored on 2011-01-28
1289
                return $self->{_cached}{$_[0]}
1290
            }
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1291
        }
update pod
Yuki Kimoto authored on 2011-01-28
1292
    });
1293
    
update pod
Yuki Kimoto authored on 2011-02-11
1294
First argument is L<DBIx::Custom> object.
1295
Second argument is SQL before parsing.
1296
Third argument is SQL information after parsing. This is hash reference.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1297

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

            
pod fix
Yuki Kimoto authored on 2011-01-21
1301
=head1 EXAMPLES
1302

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

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

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