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

            
pod fix
Yuki Kimoto authored on 2011-01-21
3
DBIx::Custom::Guide - DBIx::Custom Guides
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
4

            
pod fix
Yuki Kimoto authored on 2011-01-21
5
=head1 GUIDE
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
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

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
358
=head2 6.Create table object
359

            
360
You can create table object which have methods.
361

            
362
    $dbi->table('book');
363

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

            
369
    $dbi->table('book')->insert(
370
        param => {author => 'Taro', name => 'Perl'}
371
    );
372

            
373
You can define method for table.
374

            
375
    $dbi->table('book',
376
        insert_multi => sub {
377
            my $self = shift;
378
            my $table = $self->name;
379
            my $dbi = $self->dbi;
380
            
381
            # Do something
382
        },
383
        cross_summary => sub {
384
            my $self = shift;
385
            my $table = $self->name;
386
            my $dbi = $self->dbi;
387
            
388
            # Do something
389
        }
390
    );
391

            
392
Each method receive L<DBIx::Custom::Table> object as first argument.
393
This class have C<name()> to get table name and C<dbi()>
394
to get L<DBIx::Custom> object.
395

            
396
Defined method is called from table class.
397

            
398
    $dbi->table('book')->insert_multi(param => $param);
399

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

            
402
=head3 Use execute() method instead suger methods
403

            
404
If you execute insert statement by C<insert()> method,
405
you sometimes can't get required performance.
406

            
407
C<insert()> method is a little slow because SQL statement and statement handle
408
is created every time.
409

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

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

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

            
424
Execute query repeatedly.
425
    
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
426
    my $params = [
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
427
        {title => 'Perl',      author => 'Ken'},
428
        {title => 'Good days', author => 'Mike'}
429
    ];
430
    
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
431
    foreach my $param (@$params) {
432
        $dbi->execute($query, $param);
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
433
    }
434

            
435
This is faster than C<insert()> method.
436

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

            
439
=head3 Get DBI object
440

            
441
You can get L<DBI> object and call any method of L<DBI>.
442

            
443
    $dbi->dbh->begin_work;
444
    $dbi->dbh->commit;
445
    $dbi->dbh->rollback;
446

            
447
=head3 Change Result class
448

            
449
You can change Result class if you need.
450

            
451
    package Your::Result;
452
    use base 'DBIx::Custom::Result';
453
    
454
    sub some_method { ... }
455

            
456
    1;
457
    
458
    package main;
459
    
460
    use Your::Result;
461
    
462
    my $dbi = DBIx::Custom->connect(...);
463
    $dbi->result_class('Your::Result');
464

            
removed experimental txn_sco...
Yuki Kimoto authored on 2011-01-24
465
=head3 Register tag
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
466

            
removed experimental txn_sco...
Yuki Kimoto authored on 2011-01-24
467
    $dbi->register_tag(
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
468
        name => sub {
469
           ...
470
        }
471
    );
472

            
473
=head3 Resister helper method
474

            
475
You can resiter helper method.
476

            
477
    $dbi->helper(
478
        update_or_insert => sub {
479
            my $self = shift;
480
            # do something
481
        },
482
        find_or_create   => sub {
483
            my $self = shift;
484
            # do something
485
        }
486
    );
487

            
488
Register helper methods.
489
These method can be called from L<DBIx::Custom> object directory.
490

            
491
    $dbi->update_or_insert;
492
    $dbi->find_or_create;
493

            
pod fix
Yuki Kimoto authored on 2011-01-21
494
=head1 EXAMPLES
495

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

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