Newer Older
575 lines | 14.902kb
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1
=head1 NAME
2

            
3
DBIx::Custom::Guides - DBIx::Custom Guides
4

            
5
=head1 GUIDES
6

            
7
=head2 1. Connect to the database
8

            
9
C<connect()> method create a new L<DBIx::Custom>
10
object and connect to the database.
11

            
12
    use DBIx::Custom;
13
    my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=dbname",
14
                                    user => 'ken', password => '!LFKD%$&');
15

            
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
16
B<Data source exmaples:>
17

            
18
MySQL
19

            
20
    "dbi:mysql:database=$database"
21
    "dbi:mysql:database=$database;host=$hostname;port=$port"
22

            
23
SQLite
24

            
25
    "dbi:SQLite:dbname=$database"
26
    "dbi:SQLite:dbname=:memory:"
27

            
28
PostgreSQL
29

            
30
    "dbi:Pg:dbname=$dbname"
31

            
32
Oracle
33

            
34
    "dbi:Oracle:$dbname"
35
    "dbi:Oracle:host=$host;sid=$sid"
36

            
37
ODBC(Microsoft Access)
38

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

            
41
ODBC(SQL Server)
42

            
43
   "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
44

            
45
=head2 2. Suger methods
46

            
47
L<DBIx::Custom> has suger methods, such as C<insert()>, C<update()>,
48
C<delete()> or C<select()>. If you want to do small works,
49
You don't have to create SQL statements.
50

            
51
=head3 insert()
52

            
53
Execute insert statement.
54

            
55
    $dbi->insert(table  => 'books',
56
                 param  => {title => 'Perl', author => 'Ken'});
57

            
58
The following SQL is executed.
59

            
60
    insert into (title, author) values (?, ?);
61

            
62
The values of C<title> and C<author> is embedded into the placeholders.
63

            
64
C<append> and C<filter> argument can be specified.
65
See also "METHODS" section.
66

            
67
=head3 update()
68

            
69
Execute update statement.
70

            
71
    $dbi->update(table  => 'books', 
72
                 param  => {title => 'Perl', author => 'Ken'}, 
73
                 where  => {id => 5});
74

            
75
The following SQL is executed.
76

            
77
    update books set title = ?, author = ?;
78

            
79
The values of C<title> and C<author> is embedded into the placeholders.
80

            
81
C<append> and C<filter> argument can be specified.
82
See also "METHOD" section.
83

            
84
If you want to update all rows, use C<update_all()> method.
85

            
86
=head3 delete()
87

            
88
Execute delete statement.
89

            
90
    $dbi->delete(table  => 'books',
91
                 where  => {author => 'Ken'});
92

            
93
The following SQL is executed.
94

            
95
    delete from books where id = ?;
96

            
97
The value of C<id> is embedded into the placehodler.
98

            
99
C<append> and C<filter> argument can be specified.
100
see also "METHODS" section.
101

            
102
If you want to delete all rows, use C<delete_all()> method.
103

            
104
=head3 select()
105

            
106
Execute select statement, only C<table> argument specified :
107

            
108
    my $result = $dbi->select(table => 'books');
109

            
110
The following SQL is executed.
111

            
112
    select * from books;
113

            
114
the result of C<select()> method is L<DBIx::Custom::Result> object.
115
You can fetch a row by C<fetch()> method.
116

            
117
    while (my $row = $result->fetch) {
118
        my $title  = $row->[0];
119
        my $author = $row->[1];
120
    }
121

            
122
L<DBIx::Custom::Result> has various methods to fetch row.
123
See "3. Fetch row".
124

            
125
C<column> and C<where> arguments specified.
126

            
127
    my $result = $dbi->select(
128
        table  => 'books',
129
        column => [qw/author title/],
130
        where  => {author => 'Ken'}
131
    );
132

            
133
The following SQL is executed.
134

            
135
    select author, title from books where author = ?;
136

            
137
the value of C<author> is embdded into the placeholder.
138

            
139
If you want to join tables, specify C<relation> argument. 
140

            
141
    my $result = $dbi->select(
142
        table    => ['books', 'rental'],
143
        column   => ['books.name as book_name']
144
        relation => {'books.id' => 'rental.book_id'}
145
    );
146

            
147
The following SQL is executed.
148

            
149
    select books.name as book_name from books, rental
150
    where books.id = rental.book_id;
151

            
152
If you want to add some string to the end of SQL statement,
153
use C<append> argument.
154

            
155
    my $result = $dbi->select(
156
        table  => 'books',
157
        where  => {author => 'Ken'},
158
        append => 'order by price limit 5',
159
    );
160

            
161
The following SQL is executed.
162

            
163
    select * books where author = ? order by price limit 5;
164

            
165
C<filter> argument can be specified.
166
see also "METHODS" section.
167

            
168
=head2 3. Fetch row
169

            
170
C<select()> method return L<DBIx::Custom::Result> object.
171
You can fetch row by various methods.
172
Note that in this section, array means array reference,
173
and hash meanse hash reference.
174

            
175
Fetch row into array.
176
    
177
    while (my $row = $result->fetch) {
178
        my $author = $row->[0];
179
        my $title  = $row->[1];
180
        
181
    }
182

            
183
Fetch only a first row into array.
184

            
185
    my $row = $result->fetch_first;
186

            
187
Fetch multiple rows into array of array.
188

            
189
    while (my $rows = $result->fetch_multi(5)) {
190
        my $first_author  = $rows->[0][0];
191
        my $first_title   = $rows->[0][1];
192
        my $second_author = $rows->[1][0];
193
        my $second_value  = $rows->[1][1];
194
    
195
    }
196
    
197
Fetch all rows into array of array.
198

            
199
    my $rows = $result->fetch_all;
200

            
201
Fetch row into hash.
202

            
203
    # Fetch a row into hash
204
    while (my $row = $result->fetch_hash) {
205
        my $title  = $row->{title};
206
        my $author = $row->{author};
207
        
208
    }
209

            
210
Fetch only a first row into hash
211

            
212
    my $row = $result->fetch_hash_first;
213
    
214
Fetch multiple rows into array of hash
215

            
216
    while (my $rows = $result->fetch_hash_multi(5)) {
217
        my $first_title   = $rows->[0]{title};
218
        my $first_author  = $rows->[0]{author};
219
        my $second_title  = $rows->[1]{title};
220
        my $second_author = $rows->[1]{author};
221
    
222
    }
223
    
224
Fetch all rows into array of hash
225

            
226
    my $rows = $result->fetch_hash_all;
227

            
228
If you want to access statement handle of L<DBI>, use C<sth> attribute.
229

            
230
    my $sth = $result->sth;
231

            
232
=head2 4. Hash parameter binding
233

            
234
L<DBIx::Custom> provides hash parameter binding.
235

            
236
At frist, I show normal parameter binding.
237

            
238
    use DBI;
239
    my $dbh = DBI->connect(...);
240
    my $sth = $dbh->prepare(
241
        "select * from books where author = ? and title like ?;"
242
    );
243
    $sth->execute('Ken', '%Perl%');
244

            
245
This is very good way because database system can enable SQL caching,
246
and parameter is quoted automatically. this is secure.
247

            
248
L<DBIx::Custom> hash parameter binding system improve
249
normal parameter binding to use hash parameter.
250

            
251
    my $result = $dbi->execute(
252
        "select * from books where {= author} and {like title};"
253
        param => {author => 'Ken', title => '%Perl%'}
254
    );
255

            
256
This is same as the normal way, execpt that the parameter is hash.
257
{= author} and {like title} is called C<tag>.
258
tag is expand to placeholder string internally.
259

            
260
    select * from books where {= author} and {like title}
261
      -> select * from books where author = ? and title like ?;
262

            
263
The following tags is available.
264

            
265
    [TAG]                       [REPLACED]
266
    {? NAME}               ->   ?
267
    {= NAME}               ->   NAME = ?
268
    {<> NAME}              ->   NAME <> ?
269
    
270
    {< NAME}               ->   NAME < ?
271
    {> NAME}               ->   NAME > ?
272
    {>= NAME}              ->   NAME >= ?
273
    {<= NAME}              ->   NAME <= ?
274
    
275
    {like NAME}            ->   NAME like ?
276
    {in NAME COUNT}        ->   NAME in [?, ?, ..]
277
    
278
    {insert_param NAME1 NAME2}   ->   (NAME1, NAME2) values (?, ?)
279
    {update_param NAME1 NAME2}   ->   set NAME1 = ?, NAME2 = ?
280

            
281
See also L<DBIx::Custom::QueryBuilder>.
282

            
283
C<{> and C<}> is reserved. If you use these charactors,
284
you must escape them using '\'. Note that '\' is
285
already perl escaped charactor, so you must write '\\'. 
286

            
287
    'select * from books \\{ something statement \\}'
288

            
289
=head2 5. Filtering
290

            
291
Usually, Perl string is kept as internal string.
292
If you want to save the string to database, You must encode the string.
293
Filtering system help you to convert a data to another data
294
when you save to the data and get the data form database.
295

            
296
If you want to register filter, use C<register_filter()> method.
297

            
298
    $dbi->register_filter(
299
        to_upper_case => sub {
300
            my $value = shift;
301
            return uc $value;
302
        }
303
    );
304

            
305
C<encode_utf8> and C<decode_utf8> filter is registerd by default.
306

            
307
You can specify these filters to C<filter> argument of C<execute()> method.
308

            
309
    my $result = $dbi->execute(
310
        "select * from books where {= author} and {like title};"
311
        param  => {author => 'Ken', title => '%Perl%'},
312
        filter => {author => 'to_upper_case, title => 'encode_utf8'}
313
    );
314

            
315
C<filter> argument can be specified to suger methods, such as
316
C<insert()>, C<update()>, C<update_all()>,
317
C<delete()>, C<delete_all()>, C<select()>.
318

            
319
    # insert(), having filter argument
320
    $dbi->insert(table  => 'books',
321
                 param  => {title => 'Perl', author => 'Ken'},
322
                 filter => {title => 'encode_utf8'});
323
    
324
    # select(), having filter argument
325
    my $result = $dbi->select(
326
        table  => 'books',
327
        column => [qw/author title/],
328
        where  => {author => 'Ken'},
329
        append => 'order by id limit 1',
330
        filter => {title => 'encode_utf8'}
331
    );
332

            
333
Filter works each parmeter, but you prepare default filter for all parameters.
334

            
335
    $dbi->default_bind_filter('encode_utf8');
336

            
337
C<filter()> argument overwrites this default filter.
338
    
339
    $dbi->default_bind_filter('encode_utf8');
340
    $dbi->insert(
341
        table  => 'books',
342
        param  => {title => 'Perl', author => 'Ken', price => 1000},
343
        filter => {author => 'to_upper_case', price => undef}
344
    );
345

            
346
This is same as the following example.
347

            
348
    $dbi->insert(
349
        table  => 'books',
350
        param  => {title => 'Perl', author => 'Ken', price => 1000},
351
        filter => {title => 'encode_uft8' author => 'to_upper_case'}
352
    );
353

            
354
You can also specify filter when the row is fetched. This is reverse of bind filter.
355

            
356
    my $result = $dbi->select(table => 'books');
357
    $result->filter({title => 'decode_utf8', author => 'to_upper_case'});
358

            
359
Filter works each column value, but you prepare a default filter
360
for all clumn value.
361

            
362
    $dbi->default_fetch_filter('decode_utf8');
363

            
364
C<filter()> method of L<DBIx::Custom::Result>
365
overwrites this default filter.
366

            
367
    $dbi->default_fetch_filter('decode_utf8');
368
    my $result = $dbi->select(
369
        table => 'books',
370
        columns => ['title', 'author', 'price']
371
    );
372
    $result->filter({author => 'to_upper_case', price => undef});
373

            
374
This is same as the following one.
375

            
376
    my $result = $dbi->select(
377
        table => 'books',
378
        columns => ['title', 'author', 'price']
379
    );
380
    $result->filter({title => 'decode_utf8', author => 'to_upper_case'});
381

            
382
Note that in fetch filter, column names must be lower case
383
even if the column name conatains upper case charactors.
384
This is requirment not to depend database systems.
385

            
check arguments of connect m...
Yuki Kimoto authored on 2010-12-20
386
B<Filter examples>
387

            
388
MySQL
389

            
390
    # Time::Piece object to DATETIME format
391
    tp_to_datetime => sub {
392
        return shift->strftime('%Y-%m-%d %H:%M:%S');
393
    }
394
    
395
    # Time::Piece object to DATE format
396
    tp_to_date => sub {
397
        return shift->strftime('%Y-%m-%d');
398
    },
399
    
400
    # DATETIME to Time::Piece object
401
    datetime_to_tp => sub {
402
        return Time::Piece->strptime(shift, '%Y-%m-%d %H:%M:%S');
403
    }
404
    
405
    # DATE to Time::Piece object
406
    date_to_tp => sub {
407
        return Time::Piece->strptime(shift, '%Y-%m-%d');
408
    }
409

            
410
SQLite
411
    
412
    # Time::Piece object to DATETIME format
413
    tp_to_datetime => sub {
414
        return shift->strftime('%Y-%m-%d %H:%M:%S');
415
    }
416
    
417
    # Time::Piece object to DATE format
418
    tp_to_date => sub {
419
        return shift->strftime('%Y-%m-%d');
420
    },
421
    
422
    # DATETIME to Time::Piece object
423
    datetime_to_tp => sub {
424
        return Time::Piece->strptime(shift, $FORMATS->{db_datetime});
425
    }
426
    
427
    # DATE to Time::Piece object
428
    date_to_tp => sub {
429
        return Time::Piece->strptime(shift, $FORMATS->{db_date});
430
    }
431
    
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
432
=head2 6. Get high performance
433

            
434
=head3 Disable filter checking
435

            
436
Filter checking is executed by default.
437
This is done to check right filter name is specified,
438
but sometimes damage performance.
439

            
440
If you disable this filter checking,
441
Set C<filter_check> attribute to 0.
442

            
443
    $dbi->filter_check(0);
444

            
445
=head3 Use execute() method instead suger methods
446

            
447
If you execute insert statement by C<insert()> method,
448
you sometimes can't get required performance.
449

            
450
C<insert()> method is a little slow because SQL statement and statement handle
451
is created every time.
452

            
453
In that case, you can prepare a query by C<create_query()> method.
454
    
455
    my $query = $dbi->create_query(
456
        "insert into books {insert_param title author};"
457
    );
458

            
459
Return value of C<create_query()> is L<DBIx::Custom::Query> object.
460
This keep the information of SQL and column names.
461

            
462
    {
463
        sql     => 'insert into books (title, author) values (?, ?);',
464
        columns => ['title', 'author']
465
    }
466

            
467
Execute query repeatedly.
468
    
469
    my $inputs = [
470
        {title => 'Perl',      author => 'Ken'},
471
        {title => 'Good days', author => 'Mike'}
472
    ];
473
    
474
    foreach my $input (@$inputs) {
475
        $dbi->execute($query, $input);
476
    }
477

            
478
This is faster than C<insert()> method.
479

            
480
=head3 caching
481

            
482
C<execute()> method caches the parsed result of the source of SQL.
483
Default to 1
484

            
485
    $dbi->cache(1);
486

            
487
Caching is on memory, but you can change this by C<cache_method()>.
488
First argument is L<DBIx::Custom> object.
489
Second argument is a source of SQL,
490
such as "select * from books where {= title} and {= author};";
491
Third argument is parsed result, such as
492
{sql => "select * from books where title = ? and author = ?",
493
 columns => ['title', 'author']}, this is hash reference.
494
If arguments is more than two, this method is called to set cache.
495
If not, this method is called to get cache.
496

            
497
    $dbi->cache_method(sub {
498
        sub {
499
            my $self = shift;
500
            
501
            $self->{_cached} ||= {};
502
            
503
            # Set cache
504
            if (@_ > 1) {
505
                $self->{_cached}{$_[0]} = $_[1] 
506
            }
507
            
508
            # Get cache
509
            else {
510
                return $self->{_cached}{$_[0]}
511
            }
512
        }
513
    });
514

            
515
=head2 7. More features
516

            
517
=head3 Get DBI object
518

            
519
You can get L<DBI> object and call any method of L<DBI>.
520

            
521
    $dbi->dbh->begin_work;
522
    $dbi->dbh->commit;
523
    $dbi->dbh->rollback;
524

            
525
=head3 Change Result class
526

            
527
You can change Result class if you need.
528

            
529
    package Your::Result;
530
    use base 'DBIx::Custom::Result';
531
    
532
    sub some_method { ... }
533

            
534
    1;
535
    
536
    package main;
537
    
538
    use Your::Result;
539
    
540
    my $dbi = DBIx::Custom->connect(...);
541
    $dbi->result_class('Your::Result');
542

            
543
=head3 Custamize SQL builder object
544

            
545
You can custamize SQL builder object
546

            
547
    my $dbi = DBIx::Custom->connect(...);
548
    $dbi->query_builder->register_tag_processor(
549
        name => sub {
550
           ...
551
        }
552
    );
553

            
554
=head3 Resister helper method
555

            
556
You can resiter helper method.
557

            
558
    $dbi->helper(
559
        update_or_insert => sub {
560
            my $self = shift;
561
            # do something
562
        },
563
        find_or_create   => sub {
564
            my $self = shift;
565
            # do something
566
        }
567
    );
568

            
569
Register helper methods.
570
These method can be called from L<DBIx::Custom> object directory.
571

            
572
    $dbi->update_or_insert;
573
    $dbi->find_or_create;
574

            
575
=cut