Newer Older
500 lines | 12.635kb
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

            
update pod
Yuki Kimoto authored on 2011-01-26
7
B<This guide is a little old and not complete. Please wait for a while.>
8

            
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
9
=head2 1. Connect to the database
10

            
11
    use DBIx::Custom;
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
12
    my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=$database",
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
13
                                    user => 'ken', password => '!LFKD%$&');
14

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

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

            
20
SQLite
21

            
22
    "dbi:SQLite:dbname=$database"
23
    "dbi:SQLite:dbname=:memory:"
24

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
25
MySQL
26

            
27
    "dbi:mysql:database=$database"
28
    "dbi:mysql:database=$database;host=$hostname;port=$port"
29

            
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
30
PostgreSQL
31

            
32
    "dbi:Pg:dbname=$dbname"
33

            
34
Oracle
35

            
36
    "dbi:Oracle:$dbname"
37
    "dbi:Oracle:host=$host;sid=$sid"
38

            
39
ODBC(Microsoft Access)
40

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

            
43
ODBC(SQL Server)
44

            
45
   "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
46

            
47
=head2 2. Suger methods
48

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

            
53
=head3 insert()
54

            
55
Execute insert statement.
56

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

            
60
The following SQL is executed.
61

            
62
    insert into (title, author) values (?, ?);
63

            
64
The values of C<title> and C<author> is embedded into the placeholders.
65

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

            
69
=head3 update()
70

            
71
Execute update statement.
72

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

            
77
The following SQL is executed.
78

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
79
    update book set title = ?, author = ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
80

            
81
The values of C<title> and C<author> is embedded into the placeholders.
82

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

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

            
88
=head3 delete()
89

            
90
Execute delete statement.
91

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

            
95
The following SQL is executed.
96

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
97
    delete from book where id = ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
98

            
99
The value of C<id> is embedded into the placehodler.
100

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

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

            
106
=head3 select()
107

            
108
Execute select statement, only C<table> argument specified :
109

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

            
112
The following SQL is executed.
113

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

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

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

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

            
127
C<column> and C<where> arguments specified.
128

            
129
    my $result = $dbi->select(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
130
        table  => 'book',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
131
        column => [qw/author title/],
132
        where  => {author => 'Ken'}
133
    );
134

            
135
The following SQL is executed.
136

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

            
139
the value of C<author> is embdded into the placeholder.
140

            
141
If you want to join tables, specify C<relation> argument. 
142

            
143
    my $result = $dbi->select(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
144
        table    => ['book', 'rental'],
145
        column   => ['book.name as book_name']
146
        relation => {'book.id' => 'rental.book_id'}
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
147
    );
148

            
149
The following SQL is executed.
150

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
151
    select book.name as book_name from book, rental
152
    where book.id = rental.book_id;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
153

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

            
157
    my $result = $dbi->select(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
158
        table  => 'book',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
159
        where  => {author => 'Ken'},
160
        append => 'order by price limit 5',
161
    );
162

            
163
The following SQL is executed.
164

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

            
167
C<filter> argument can be specified.
168
see also "METHODS" section.
169

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
170
=head2 3. Result manipulation
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
171

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

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

            
185
Fetch only a first row into array.
186

            
187
    my $row = $result->fetch_first;
188

            
189
Fetch multiple rows into array of array.
190

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

            
201
    my $rows = $result->fetch_all;
202

            
203
Fetch row into hash.
204

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

            
212
Fetch only a first row into hash
213

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

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

            
228
    my $rows = $result->fetch_hash_all;
229

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

            
232
    my $sth = $result->sth;
233

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
234
=head2 4. Parameter binding
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
235

            
236
L<DBIx::Custom> provides hash parameter binding.
237

            
238
At frist, I show normal parameter binding.
239

            
240
    use DBI;
241
    my $dbh = DBI->connect(...);
242
    my $sth = $dbh->prepare(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
243
        "select * from book where author = ? and title like ?;"
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
244
    );
245
    $sth->execute('Ken', '%Perl%');
246

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

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

            
253
    my $result = $dbi->execute(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
254
        "select * from book where {= author} and {like title};"
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
255
        param => {author => 'Ken', title => '%Perl%'}
256
    );
257

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

            
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
262
    select * from book where {= author} and {like title}
263
      -> select * from book where author = ? and title like ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
264

            
265
The following tags is available.
266

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

            
283
See also L<DBIx::Custom::QueryBuilder>.
284

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

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

            
291
=head2 5. Filtering
292

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

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

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

            
308
You can apply this filter to use C<apply_filter()> method.
309

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

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

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

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

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

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

            
341

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

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

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

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

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

            
362
You can create table object which have methods.
363

            
364
    $dbi->table('book');
365

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

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

            
375
You can define method for table.
376

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

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

            
398
Defined method is called from table class.
399

            
400
    $dbi->table('book')->insert_multi(param => $param);
401

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

            
404
=head3 Use execute() method instead suger methods
405

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

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

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

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

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

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

            
437
This is faster than C<insert()> method.
438

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

            
441
=head3 Get DBI object
442

            
443
You can get L<DBI> object and call any method of L<DBI>.
444

            
445
    $dbi->dbh->begin_work;
446
    $dbi->dbh->commit;
447
    $dbi->dbh->rollback;
448

            
449
=head3 Change Result class
450

            
451
You can change Result class if you need.
452

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

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

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

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

            
removed experimental expand
Yuki Kimoto authored on 2011-01-26
475
=head3 Resister method method
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
476

            
removed experimental expand
Yuki Kimoto authored on 2011-01-26
477
You can resiter method method.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
478

            
removed experimental expand
Yuki Kimoto authored on 2011-01-26
479
    $dbi->method(
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
480
        update_or_insert => sub {
481
            my $self = shift;
482
            # do something
483
        },
484
        find_or_create   => sub {
485
            my $self = shift;
486
            # do something
487
        }
488
    );
489

            
removed experimental expand
Yuki Kimoto authored on 2011-01-26
490
Register method methods.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
491
These method can be called from L<DBIx::Custom> object directory.
492

            
493
    $dbi->update_or_insert;
494
    $dbi->find_or_create;
495

            
pod fix
Yuki Kimoto authored on 2011-01-21
496
=head1 EXAMPLES
497

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

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