Newer Older
580 lines | 14.499kb
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
    use DBIx::Custom;
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
10
    my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=$database",
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
11
                                    user => 'ken', password => '!LFKD%$&');
12

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
13
use C<connect()> to connect to the database.
14
You can sepecfiy C<data_soruce>, C<user>, and C<password>.
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
15

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
16
The following ones are data source exmaple in variouse dabase system.
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
17

            
18
SQLite
19

            
20
    "dbi:SQLite:dbname=$database"
21
    "dbi:SQLite:dbname=:memory:"
22

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
23
MySQL
24

            
25
    "dbi:mysql:database=$database"
26
    "dbi:mysql:database=$database;host=$hostname;port=$port"
27

            
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
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

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
55
    $dbi->insert(table  => 'book',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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

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

            
75
The following SQL is executed.
76

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
77
    update book set title = ?, author = ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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

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

            
93
The following SQL is executed.
94

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
95
    delete from book where id = ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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

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

            
110
The following SQL is executed.
111

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
112
    select * from book;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
128
        table  => 'book',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
129
        column => [qw/author title/],
130
        where  => {author => 'Ken'}
131
    );
132

            
133
The following SQL is executed.
134

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
135
    select author, title from book where author = ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
142
        table    => ['book', 'rental'],
143
        column   => ['book.name as book_name']
144
        relation => {'book.id' => 'rental.book_id'}
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
145
    );
146

            
147
The following SQL is executed.
148

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
149
    select book.name as book_name from book, rental
150
    where book.id = rental.book_id;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
156
        table  => 'book',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
157
        where  => {author => 'Ken'},
158
        append => 'order by price limit 5',
159
    );
160

            
161
The following SQL is executed.
162

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
163
    select * book where author = ? order by price limit 5;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
164

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

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
168
=head2 3. Result manipulation
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
232
=head2 4. Parameter binding
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
241
        "select * from book where author = ? and title like ?;"
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
252
        "select * from book where {= author} and {like title};"
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
260
    select * from book where {= author} and {like title}
261
      -> select * from book where author = ? and title like ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
287
    'select * from book \\{ something statement \\}'
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
288

            
289
=head2 5. Filtering
290

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
291
If you want to filter the value, you can do this. For example,
292
L<Time::Piece> object to database date format, or reverse.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
293

            
294
    $dbi->register_filter(
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
295
        tp_to_date => sub {
296
            return shift->strftime('%Y-%m-%d');
297
        },
298
        date_to_tp => sub {
299
            return Time::Piece->strptime(shift, '%Y-%m-%d');
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
300
        }
301
    );
302

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
303
In this example, L<Time::Piece> object is converted to 'yyyy-mm-dd' format
304
, and reverse.
305

            
306
You can apply this filter to use C<apply_filter()> method.
307

            
308
    $dbi->apply_filter('book',
309
        puplication_date => {out => 'tp_to_date', in => 'date_to_tp'},
310
        someting_date => {out => 'tp_to_date', in => 'date_to_tp'}
311
    );
cleanup
Yuki Kimoto authored on 2011-01-12
312

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
313
In this case, C<book>'s C<publication_date> is automatically converted.
314
C<out> means Perl to Database, C<in> means Database to Perl.
315

            
316
These applied filters have effect C<insert>, C<update>, C<update_all>,
317
C<delete>, C<delete_all>, C<select>
318

            
319
    my $tp = Time::Piece::localtime;
320
    $dbi->insert(
321
        table => 'book',
322
        param => {name => 'Perl', publication_date => $tp}
323
    );
324
    
325
    my $result = $dbi->select(table => 'book');
326
    my $tp = $result->{publication_date};
327
    
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
328

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
329
Note that this has'nt C<execute> method by default.
330
If you want to have effect C<execute()> method, use C<table>
331
option.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
332

            
333
    my $result = $dbi->execute(
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
334
        "select * from book where {= id};",
335
         param => {id => 5},
336
         table => ['book']
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
337
    );
338

            
339

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
340
You can also specify registered filters to C<filter> option of
341
C<insert()>, C<update()>, C<update_all()>, C<delete()>, C<delete_all()>,
342
C<select()> C<execute()>. This is overwirte applied filter.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
343
    
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
344
    $dbi->insert(
345
        table => 'book',
346
        param => {name => 'Perl', publication_date => $tp},
347
        filter => {publication_date => 'tp_to_date'}
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
348
    );
349

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
350
You can also specify C<DBIx::Custom::Result> object.
351
This is overwrite applied filter.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
352

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
353
    my $result = $dbi->select(table => 'book');
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
354
    $result->filter(publication_date => 'date_to_tp');
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
355

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

            
358
MySQL
359

            
360
    # Time::Piece object to DATETIME format
361
    tp_to_datetime => sub {
362
        return shift->strftime('%Y-%m-%d %H:%M:%S');
363
    }
364
    
365
    # Time::Piece object to DATE format
366
    tp_to_date => sub {
367
        return shift->strftime('%Y-%m-%d');
368
    },
369
    
370
    # DATETIME to Time::Piece object
371
    datetime_to_tp => sub {
372
        return Time::Piece->strptime(shift, '%Y-%m-%d %H:%M:%S');
373
    }
374
    
375
    # DATE to Time::Piece object
376
    date_to_tp => sub {
377
        return Time::Piece->strptime(shift, '%Y-%m-%d');
378
    }
379

            
380
SQLite
381
    
382
    # Time::Piece object to DATETIME format
383
    tp_to_datetime => sub {
384
        return shift->strftime('%Y-%m-%d %H:%M:%S');
385
    }
386
    
387
    # Time::Piece object to DATE format
388
    tp_to_date => sub {
389
        return shift->strftime('%Y-%m-%d');
390
    },
391
    
392
    # DATETIME to Time::Piece object
393
    datetime_to_tp => sub {
394
        return Time::Piece->strptime(shift, $FORMATS->{db_datetime});
395
    }
396
    
397
    # DATE to Time::Piece object
398
    date_to_tp => sub {
399
        return Time::Piece->strptime(shift, $FORMATS->{db_date});
400
    }
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
401

            
402
=head2 6.Create table object
403

            
404
You can create table object which have methods.
405

            
406
    $dbi->table('book');
407

            
408
This class have C<insert()>, C<update()>, C<update_all()>,
fix tests
Yuki Kimoto authored on 2011-01-18
409
C<delete()>, C<delete_all()>, C<select()>.
410
These is same as L<DBIx::Custom>'s methods except that 
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
411
you don't have to specify table.
412

            
413
    $dbi->table('book')->insert(
414
        param => {author => 'Taro', name => 'Perl'}
415
    );
416

            
417
You can define method for table.
418

            
419
    $dbi->table('book',
420
        insert_multi => sub {
421
            my $self = shift;
422
            my $table = $self->name;
423
            my $dbi = $self->dbi;
424
            
425
            # Do something
426
        },
427
        cross_summary => sub {
428
            my $self = shift;
429
            my $table = $self->name;
430
            my $dbi = $self->dbi;
431
            
432
            # Do something
433
        }
434
    );
435

            
436
Each method receive L<DBIx::Custom::Table> object as first argument.
437
This class have C<name()> to get table name and C<dbi()>
438
to get L<DBIx::Custom> object.
439

            
440
Defined method is called from table class.
441

            
442
    $dbi->table('book')->insert_multi(param => $param);
443

            
444
=head2 7. Get high performance
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
445

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

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

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

            
454
In that case, you can prepare a query by C<create_query()> method.
455
    
456
    my $query = $dbi->create_query(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
457
        "insert into book {insert_param title author};"
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
458
    );
459

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

            
463
    {
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
464
        sql     => 'insert into book (title, author) values (?, ?);',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
465
        columns => ['title', 'author']
466
    }
467

            
468
Execute query repeatedly.
469
    
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
470
    my $params = [
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
471
        {title => 'Perl',      author => 'Ken'},
472
        {title => 'Good days', author => 'Mike'}
473
    ];
474
    
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
475
    foreach my $param (@$params) {
476
        $dbi->execute($query, $param);
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
477
    }
478

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

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
481
=head2 8. More features
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
482

            
483
=head3 Get DBI object
484

            
485
You can get L<DBI> object and call any method of L<DBI>.
486

            
487
    $dbi->dbh->begin_work;
488
    $dbi->dbh->commit;
489
    $dbi->dbh->rollback;
490

            
491
=head3 Change Result class
492

            
493
You can change Result class if you need.
494

            
495
    package Your::Result;
496
    use base 'DBIx::Custom::Result';
497
    
498
    sub some_method { ... }
499

            
500
    1;
501
    
502
    package main;
503
    
504
    use Your::Result;
505
    
506
    my $dbi = DBIx::Custom->connect(...);
507
    $dbi->result_class('Your::Result');
508

            
add examples
Yuki Kimoto authored on 2011-01-07
509
=head3 Custamize query builder object
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
510

            
add examples
Yuki Kimoto authored on 2011-01-07
511
You can custamize query builder object
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
512

            
513
    my $dbi = DBIx::Custom->connect(...);
514
    $dbi->query_builder->register_tag_processor(
515
        name => sub {
516
           ...
517
        }
518
    );
519

            
520
=head3 Resister helper method
521

            
522
You can resiter helper method.
523

            
524
    $dbi->helper(
525
        update_or_insert => sub {
526
            my $self = shift;
527
            # do something
528
        },
529
        find_or_create   => sub {
530
            my $self = shift;
531
            # do something
532
        }
533
    );
534

            
535
Register helper methods.
536
These method can be called from L<DBIx::Custom> object directory.
537

            
538
    $dbi->update_or_insert;
539
    $dbi->find_or_create;
540

            
add examples
Yuki Kimoto authored on 2011-01-07
541
=head2 EXAMPLES
542

            
543
=head3 Limit clause
544

            
545
    my $rows = $dbi->select(
546
        table => 'table1',
547
        where => {key1 => 1},
548
        append => "order by key2 {limit 1 0}" # {limit COUNT OFFSET}
549
    )->fetch_hash_all;
550

            
551
SQLite
552

            
553
    $dbi->query_builder->register_tag_processor(
554
        limit => sub {
555
            my ($count, $offset) = @_;
556
            
557
            my $s = '';
558
            $s .= "limit $count";
559
            $s .= " offset $offset" if defined $offset;
560
            
561
            return [$s, []];
562
        }
563
    );
564

            
565
MySQL
566

            
567
    $dbi->query_builder->register_tag_processor(
568
        limit => sub {
569
            my ($count, $offset) = @_;
570
            
571
            my $s = '';
572
            $offset = 0 unless defined $offset;
573
            $s .= "limit $offset";
574
            $s .= ", $count";
575
            
576
            return [$s, []];
577
        }
578
    );
579

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