Newer Older
1341 lines | 33.069kb
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
L<DBIx::Custom> is the class to make easy to execute SQL.
10
This is L<DBI> wrapper class like L<DBIx::Class> or L<DBIx::Simple>.
11
You can do thing more easy than L<DBIx::Class>, more flexible
12
than L<DBIx::Simple>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
13

            
update pod
Yuki Kimoto authored on 2011-02-11
14
L<DBIx::Custom> is not O/R mapper, O/R mapper is usefule, but
update pod
Yuki Kimoto authored on 2011-01-28
15
you must learn many things. Created SQL is sometimes inefficient,
16
and in many cases you create raw SQL because
17
O/R mapper can't make complex SQL
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
18

            
update pod
Yuki Kimoto authored on 2011-01-28
19
L<DBIx::Custom> is opposit of O/R mapper.
20
The main purpose is that we respect SQL
21
and make easy difficult works if you use only L<DBI>.
22
If you already learn SQL, it is easy to use L<DBIx::Custom>.
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
23

            
update pod
Yuki Kimoto authored on 2011-01-28
24
I explain L<DBIx::Custom> a little in this section.
25
In L<DBIx::Custom>, you embbed tag in SQL.
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
26

            
update pod
Yuki Kimoto authored on 2011-01-28
27
    select * from book where {= title} and {=author};
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
28

            
update pod
Yuki Kimoto authored on 2011-01-28
29
The part arround {} is tag.
30
This SQL is converted to the one which contains place holder.
31

            
32
    select * from book where title = ? and author = ?;
33

            
34
Maybe you ask me that this conversion is meaningful.
35
On the top of this, usuful features is implemented.
36
See the following descriptions.
37

            
38
=over 4
39

            
40
=item 1. Specify place holder binding value as hash refernce
41

            
42
If you use L<DBI>, you must specify place holder binding value
43
as array.
44

            
45
    $sth->execute(@bind);
46

            
47
If you use L<DBIx::Custom>, you specify it as hash reference.
48
    
49
    my $param = {title => 'Perl', author => 'Ken'};
50
    $dbi->execute($sql, $param);
51

            
52
=item 2. Filtering
53

            
54
L<DBIx::Custom> provides filtering system.
55
For example, You think that about date value you want to 
56
manipulate it as date object like L<Time::Piece> in Perl,
57
and want to convert it to database DATE format.
58
and want to do reverse.
59

            
60
You can use filtering system.
61

            
62
At first, register filter.
63

            
64
    $dbi->register_filter(
65
        tp_to_date => sub {
66
            ...
67
        },
68
        date_to_tp => sub {
69
            ...
70
        }
71
    );
72

            
73
next, apply this filter to each column.
74

            
75
    $dbi->apply_filter('book',
76
        'issue_date' => {out => 'tp_to_date', in => 'date_to_tp'}
77
    );
78

            
79
C<out> is perl-to-database way. C<in> is perl-from-database way.
80

            
81
This filter is automatically enabled in many method.
82

            
83
    $dbi->insert(table => 'book', param => {issue_date => $tp});
84

            
85

            
86
=item 3. Selective search condition
87

            
88
It is difficult to create selective where clause in L<DBI>.
89
For example, If C<title> and C<author> is specified, we create 
90
the following SQL.
91

            
92
    select * from book where title = ? and author = ?;
93

            
94
If only C<title> is specified, the following one
95

            
96
    select * from book where title = ?;
97

            
98
If only C<author> is specified, the following one,
99

            
100
    select * from book where author = ?;
101

            
102
This is hard work. Generally we use modules like L<SQL::Abstract>.
103
L<DBIx::Custom> prepare the way to make it easy.
104

            
105
    # Where object
106
    my $where = $dbi->where;
107
    
108
    # Search condition
109
    $where->clause(
110
        ['and', '{= title}', {'= author'}]
111
    );
112
    
113
    # Setting to automatically select needed column
114
    $where->param({title => 'Perl'});
115

            
116
    # Embbed where clause to SQL
117
    my $sql = "select * from book $where";
118

            
119
You can create where clause which has selected search condition.
120
You can write nesting of where clause and C<or> condition
121

            
update pod
Yuki Kimoto authored on 2011-01-30
122
=item 4. Methods for insert, update, delete, select
update pod
Yuki Kimoto authored on 2011-01-28
123

            
update pod
Yuki Kimoto authored on 2011-01-30
124
L<DBIx::Custom> provides methods for insert, update, delete, select
125
There are C<insert()>, C<update()>, C<delete()>,C<select()>.
update pod
Yuki Kimoto authored on 2011-01-28
126

            
127
    my $param = {title => 'Perl', author => 'Ken'};
128
    $dbi->insert(table => 'book', param => $param);
129

            
update pod
Yuki Kimoto authored on 2011-01-30
130
=item 5. Register method for table.
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
131

            
update pod
Yuki Kimoto authored on 2011-01-30
132
You can register method for table.
update pod
Yuki Kimoto authored on 2011-01-28
133

            
update pod
Yuki Kimoto authored on 2011-01-30
134
    $dbi->table('book')->method(
update pod
Yuki Kimoto authored on 2011-01-28
135
        list => sub {
136
            ...
137
        },
update pod
Yuki Kimoto authored on 2011-01-30
138
        something => sub {
139
            ...
update pod
Yuki Kimoto authored on 2011-01-28
140
        }
141
    );
142

            
update pod
Yuki Kimoto authored on 2011-01-30
143
use the mehtod.
update pod
Yuki Kimoto authored on 2011-01-28
144

            
145
    $dbi->table('book')->list;
146

            
update pod
Yuki Kimoto authored on 2011-01-30
147
Many O/R mapper must create class for table,
148
but L<DBIx::Custom> make it easy.
update pod
Yuki Kimoto authored on 2011-01-28
149

            
150
=back
151

            
update pod
Yuki Kimoto authored on 2011-01-30
152
L<DBIx::Custom> is very useful.
153
See the following if you are interested in it.
update pod
Yuki Kimoto authored on 2011-01-28
154

            
update pod
Yuki Kimoto authored on 2011-01-30
155
=head2 1. Connect to database
update pod
Yuki Kimoto authored on 2011-01-28
156

            
update pod
Yuki Kimoto authored on 2011-01-30
157
Load L<DBIx::Custom>.
update pod
Yuki Kimoto authored on 2011-01-28
158

            
159
    use DBIx::Custom;
160

            
update pod
Yuki Kimoto authored on 2011-01-30
161
use C<connect()> to connect to database.
162
Return value is L<DBIx::Custom> object.
update pod
Yuki Kimoto authored on 2011-01-28
163

            
164
    my $dbi = DBIx::Custom->connect(
update pod
Yuki Kimoto authored on 2011-01-30
165
        data_source => "dbi:mysql:database=bookstore",
update pod
Yuki Kimoto authored on 2011-01-28
166
        user => 'ken',
167
        password => '!LFKD%$&',
168
        dbi_options => {mysql_enable_utf8 => 1}
169
    );
170

            
update pod
Yuki Kimoto authored on 2011-01-30
171
C<data_source> must be one corresponding to the database system.
172
The following ones are data source example.
update pod
Yuki Kimoto authored on 2011-01-28
173

            
174
B<MySQL>
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
175

            
176
    "dbi:mysql:database=$database"
177
    "dbi:mysql:database=$database;host=$hostname;port=$port"
178

            
update pod
Yuki Kimoto authored on 2011-01-28
179
B<SQLite>
180

            
181
    "dbi:SQLite:dbname=$database"
182
    "dbi:SQLite:dbname=:memory:"
183

            
184
B<PostgreSQL>
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
185

            
186
    "dbi:Pg:dbname=$dbname"
187

            
update pod
Yuki Kimoto authored on 2011-01-28
188
B<Oracle>
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
189

            
190
    "dbi:Oracle:$dbname"
191
    "dbi:Oracle:host=$host;sid=$sid"
192

            
update pod
Yuki Kimoto authored on 2011-01-28
193
B<ODBC(Microsoft Access)>
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
194

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

            
update pod
Yuki Kimoto authored on 2011-01-28
197
B<ODBC(SQL Server)>
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
198

            
199
   "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
200

            
update pod
Yuki Kimoto authored on 2011-01-30
201
If authentication is needed, you can specify C<user> and C<password>
202

            
203
L<DBIx::Custom> is wrapper class of L<DBI>.
204
You can use all methods of L<DBI> from L<DBIx::Custom> object.
205

            
206
    $dbi->do(...);
207
    $dbi->begin_work;
update pod
Yuki Kimoto authored on 2011-01-28
208

            
update pod
Yuki Kimoto authored on 2011-01-30
209
use C<dhb()> to get database handle of L<DBI>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
210

            
update pod
Yuki Kimoto authored on 2011-01-28
211
    my $dbh = $dbi->dbh;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
212

            
update pod
Yuki Kimoto authored on 2011-01-30
213
By default, the following ones is set to database handle attributes.
214

            
215
    RaiseError  ->  1
216
    PrintError  ->  0
217
    AutoCommit  ->  1
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
218

            
update pod
Yuki Kimoto authored on 2011-01-30
219
If fatal error occuer, program terminate.
220
If SQL is executed, commit is executed automatically.
update pod
Yuki Kimoto authored on 2011-01-28
221

            
update pod
Yuki Kimoto authored on 2011-01-31
222
=head2 2. Methods for insert, update, delete, or insert
update pod
Yuki Kimoto authored on 2011-01-28
223

            
update pod
Yuki Kimoto authored on 2011-01-31
224
There are following methods.
update pod
Yuki Kimoto authored on 2011-01-28
225

            
update pod
Yuki Kimoto authored on 2011-01-31
226
=head3 C<insert()>
update pod
Yuki Kimoto authored on 2011-01-28
227

            
update pod
Yuki Kimoto authored on 2011-01-31
228
use C<insert()> to insert row into database
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
229

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

            
update pod
Yuki Kimoto authored on 2011-01-31
233
C<table> is table name, C<param> is insert data.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
234

            
update pod
Yuki Kimoto authored on 2011-01-31
235
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
236

            
update pod
Yuki Kimoto authored on 2011-01-28
237
    insert into (title, author) values (?, ?);
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
238

            
update pod
Yuki Kimoto authored on 2011-01-31
239
=head3 C<update()>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
240

            
update pod
Yuki Kimoto authored on 2011-01-31
241
use C<update()> to update row in database.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
242

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

            
update pod
Yuki Kimoto authored on 2011-01-31
247
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
248

            
update pod
Yuki Kimoto authored on 2011-01-31
249
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
250

            
update pod
Yuki Kimoto authored on 2011-01-28
251
    update book set title = ?, author = ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
252

            
update pod
Yuki Kimoto authored on 2011-01-31
253
You can't execute C<update()> without C<where> for safety.
254
use C<update_all()> if you want to update all rows.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
255

            
update pod
Yuki Kimoto authored on 2011-01-28
256
    $dbi->update_all(table  => 'book', 
257
                     param  => {title => 'Perl', author => 'Ken'});
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
258

            
update pod
Yuki Kimoto authored on 2011-01-31
259
=head3 C<delete()>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
260

            
update pod
Yuki Kimoto authored on 2011-01-31
261
use C<delete()> to delete rows from database.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
262

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

            
update pod
Yuki Kimoto authored on 2011-01-31
266
C<table> is table name, C<where> is condition.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
267

            
update pod
Yuki Kimoto authored on 2011-01-31
268
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
269

            
update pod
Yuki Kimoto authored on 2011-01-28
270
    delete from book where id = ?;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
271

            
update pod
Yuki Kimoto authored on 2011-01-31
272
You can't execute C<delete()> without C<where> for safety.
273
use C<delete_all()> if you want to delete all rows.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
274

            
update pod
Yuki Kimoto authored on 2011-01-28
275
    $dbi->delete_all(table  => 'book');
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
276

            
update pod
Yuki Kimoto authored on 2011-01-31
277
=head3 C<select()>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
278

            
update pod
Yuki Kimoto authored on 2011-01-31
279
use C<select()> to select rows from database
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
280

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

            
update pod
Yuki Kimoto authored on 2011-01-31
283
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
284

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

            
update pod
Yuki Kimoto authored on 2011-01-31
287
Return value is L<DBIx::Custom::Result> object.
288
use C<fetch()> to fetch row.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
289

            
290
    while (my $row = $result->fetch) {
291
        my $title  = $row->[0];
292
        my $author = $row->[1];
293
    }
294

            
update pod
Yuki Kimoto authored on 2011-01-31
295
See L<3. Fetch row/"3. Fetch row"> about L<DBIx::Custom::Result>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
296

            
update pod
Yuki Kimoto authored on 2011-01-31
297
Continue more examples.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
298

            
299
    my $result = $dbi->select(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
300
        table  => 'book',
update pod
Yuki Kimoto authored on 2011-01-28
301
        column => ['author',  'title'],
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
302
        where  => {author => 'Ken'}
303
    );
304

            
update pod
Yuki Kimoto authored on 2011-01-31
305
C<column> is column names, C<where> is condition.
306

            
307
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
308

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

            
update pod
Yuki Kimoto authored on 2011-01-31
311
Next example.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
312

            
313
    my $result = $dbi->select(
cleanup
Yuki Kimoto authored on 2011-03-10
314
        table  => 'book',
315
        column => ['company.name as company__name']
316
        where  => {'book.name' => 'Perl'},
317
        join   => ['left outer join company on book.company_id = company.id]
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
318
    );
319

            
cleanup
Yuki Kimoto authored on 2011-03-10
320
You can join table by C<join>.
update pod
Yuki Kimoto authored on 2011-01-31
321

            
322
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
323

            
cleanup
Yuki Kimoto authored on 2011-03-21
324
    select company.name as company__name
325
    from book
cleanup
Yuki Kimoto authored on 2011-03-10
326
      left outer join company on book.company_id = company.id
cleanup
Yuki Kimoto authored on 2011-03-21
327
    where book.name = ?;
cleanup
Yuki Kimoto authored on 2011-03-10
328

            
329
company_if of book and id of company is left outer joined.
330

            
331
Note that only when C<where> or C<column> contain table name,
332
C<join> is joined.
333
if you specify the following option, C<join> is not joined
334
because C<join> is not needed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
335

            
cleanup
Yuki Kimoto authored on 2011-03-10
336
    my $result = $dbi->select(
337
        table  => 'book',
338
        where  => {'name' => 'Perl'},
339
        join   => ['left outer join company on book.company_id = company.id]
340
    );
341

            
342
Following SQL is executeed.
343

            
344
    select * from book where book.name = ?;
cleanup
Yuki Kimoto authored on 2011-03-21
345

            
346
You can specify column names easily using C<mycolumn()> and C<column()>.
347

            
348
    my $result = $dbi->select(
349
        table  => 'book',
350
        column => [
351
            $dbi->mycolumn('book' => ['name']),
352
            $dbi->column('company' => ['id', 'name'])
353
        ],
354
        join   => ['left outer join company on book.company_id = company.id]
355
    );
356

            
357
The following SQL is executed.
358

            
359
    select book.name as name,
360
      company.id as comapny__id,
361
      company.name as company__name
362
    from book
363
      left outer join company on book.company_id = company.id
364

            
update pod
Yuki Kimoto authored on 2011-01-31
365
Next example.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
366

            
367
    my $result = $dbi->select(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
368
        table  => 'book',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
369
        where  => {author => 'Ken'},
update pod
Yuki Kimoto authored on 2011-01-28
370
        append => 'for update',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
371
    );
372

            
update pod
Yuki Kimoto authored on 2011-01-31
373
C<append> is string appending to end of SQL.
374

            
375
Following SQL is executed.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
376

            
update pod
Yuki Kimoto authored on 2011-01-28
377
    select * book where author = ? for update;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
378

            
update pod
Yuki Kimoto authored on 2011-01-31
379
C<appned> is also used at C<insert()>, C<update()>, C<update_all()>
380
C<delete()>, C<delete_all()>, and C<select()>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
381

            
update pod
Yuki Kimoto authored on 2011-02-11
382
=head3 C<execute()>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
383

            
update pod
Yuki Kimoto authored on 2011-01-31
384
use C<execute()> to execute SQL
update pod
Yuki Kimoto authored on 2011-01-28
385

            
386
    $dbi->execute("select * from book;");
387

            
update pod
Yuki Kimoto authored on 2011-01-31
388
Process tag and execute SQL.
update pod
Yuki Kimoto authored on 2011-01-28
389

            
390
    $dbi->execute(
391
        "select * from book {= title} and {= author};"
392
        param => {title => 'Perl', author => 'Ken'}
393
    );
394

            
update pod
Yuki Kimoto authored on 2011-01-31
395
Following SQL is executed.
update pod
Yuki Kimoto authored on 2011-01-28
396

            
397
    select * from book title = ? and author = ?;
398

            
update pod
Yuki Kimoto authored on 2011-01-31
399
Values of title and author is embbdeded into placeholder.
update pod
Yuki Kimoto authored on 2011-01-28
400

            
update pod
Yuki Kimoto authored on 2011-01-31
401
See L<5. Tag/"5. Tag"> about tag.
update pod
Yuki Kimoto authored on 2011-01-28
402

            
update pod
Yuki Kimoto authored on 2011-01-31
403
You don't have to wirte last semicolon in C<execute()>. 
update pod
Yuki Kimoto authored on 2011-01-28
404

            
405
    $dbi->execute('select * from book');
406

            
update pod
Yuki Kimoto authored on 2011-02-28
407
=head3 insert by using primary key : C<insert_at()>
408

            
409
To insert row by using primary key, use C<insert_at()>
410

            
411
    $dbi->insert_at(
412
        table => 'book', primary_key => ['id'],
413
        where => ['123'], param => {name => 'Ken'}
414
    );
415

            
416
In this example, row which id column is 123 is inserted.
417
NOTE that you must pass array reference as C<where>.
418
If C<param> contains primary key, the key and value is delete from C<param>.
419

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

            
422
To update row by using primary key, use C<update_at()>
423

            
424
    $dbi->update_at(
425
        table => 'book', primary_key => ['id'],
426
        where => ['123'], param => {name => 'Ken'}
427
    );
428

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

            
433
=head3 Delete by using primary key : C<delete_at()>
434

            
435
To delete row by using primary key, use C<delete_at()>
436

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

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

            
442
You can also write arguments like this.
443

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

            
446
=head3 Select by using primary key : C<select_at()>
447

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

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

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

            
455
You can also write arguments like this.
456

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

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

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

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

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

            
468
    my $row = $result->fetch;
469

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

            
472
    while (my $row = $result->fetch) {
update pod
Yuki Kimoto authored on 2011-01-28
473
        my $title  = $row->[0];
474
        my $author = $row->[1];
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
475
    }
476

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

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

            
481
    my $row = $result->fetch_first;
482

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

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

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

            
491
    while (my $rows = $result->fetch_multi(2)) {
492
        my $title0   = $rows->[0][0];
493
        my $author0  = $rows->[0][1];
494
        
495
        my $title1   = $rows->[1][0];
496
        my $author1  = $rows->[1][1];
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
497
    }
update pod
Yuki Kimoto authored on 2011-01-28
498

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

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

            
503
    [
504
        ['Perl', 'Ken'],
505
        ['Ruby', 'Mark']
506
    ]
507

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

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

            
513
    my $rows = $result->fetch_all;
514

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

            
517
    [
518
        ['Perl', 'Ken'],
519
        ['Ruby', 'Mark']
520
    ]
521

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

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

            
526
    while (my $row = $result->fetch_hash) {
527
        my $title  = $row->{title};
528
        my $author = $row->{author};
529
    }
530

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

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

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

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

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

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

            
546
    while (my $rows = $result->fetch_hash_multi(5)) {
update pod
Yuki Kimoto authored on 2011-01-28
547
        my $title0   = $rows->[0]{title};
548
        my $author0  = $rows->[0]{author};
549
        my $title1  = $rows->[1]{title};
550
        my $author1 = $rows->[1]{author};
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
551
    }
update pod
Yuki Kimoto authored on 2011-01-28
552

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

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

            
557
    [
558
        {title => 'Perl', author => 'Ken'},
559
        {title => 'Ruby', author => 'Mark'}
560
    ]
561

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

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

            
568
    my $rows = $result->fetch_hash_all;
569

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

            
572
    [
573
        {title => 'Perl', author => 'Ken'},
574
        {title => 'Ruby', author => 'Mark'}
575
    ]
576

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

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

            
581
    my $sth = $result->sth;
582

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

            
update pod
Yuki Kimoto authored on 2011-02-07
585
L<DBIx::Custom> provide value filtering. 
586
For example, You maybe want to convert L<Time::Piece> object to
587
database date format when register data into database.
588
and convert database date fromat to L<Time::Piece> object
589
when get data from database.
update pod
Yuki Kimoto authored on 2011-01-28
590

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

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

            
595
    $dbi->register_filter(
596
        # Time::Piece object to DATE format
597
        tp_to_date => sub {
598
            my $date = shift;
599

            
600
            return '0000-00-00' unless $tp;
601
            return $tp->strftime('%Y-%m-%d');
602
        },
603
        
604
        # DATE to Time::Piece object
605
        date_to_tp => sub {
606
            my $date = shift;
607

            
608
            return if $date eq '0000-00-00';
609
            return Time::Piece->strptime($date, '%Y-%m-%d');
610
        },
611
    );
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
612

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

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

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

            
619
    $dbi->apply_filter('book',
620
        issue_date => {out => 'tp_to_date', in => 'date_to_tp'},
621
        first_issue_date => {out => 'tp_to_date', in => 'date_to_tp'}
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
622
    );
623

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

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

            
631
    issue_date => {out => sub { ... }, in => sub { ... }}
632

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

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

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

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

            
645
    my $row = $resutl->fetch_hash_first;
646
    my $tp = $row->{issue_date};
647

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

            
650
    $dbi->select(
651
        table => 'book',
update pod
Yuki Kimoto authored on 2011-02-07
652
        where => {'book.issue_date' => $tp}
update pod
Yuki Kimoto authored on 2011-01-28
653
    );
654

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

            
657
    my $result = $dbi->execute(
658
       "select issue_date as book__issue_date from book");
659

            
660
You can apply C<end> filter execute after C<in> filter.
661

            
662
    $dbi->apply_filter('book',
663
        issue_date => {out => 'tp_to_date', in => 'date_to_tp',
664
                       end => 'tp_to_displaydate'},
665
    );
666

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

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

            
update pod
Yuki Kimoto authored on 2011-02-07
672
use C<filter> option to apply individual filter
673
when data is send to database.
674
This option is used at C<insert()>, C<update()>,
675
C<update_all()>, C<delete()>, C<delete_all()>, C<select()>,
676
C<execute()>.
update pod
Yuki Kimoto authored on 2011-01-28
677

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

            
680
    $dbi->insert(
681
        table => 'book',
682
        param => {issue_date => $tp, first_issue_date => $tp},
683
        filter => {issue_date => 'tp_to_date', first_issue_date => 'tp_to_date'}
684
    );
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
685

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

            
688
my $sql = <<"EOS";
689
select YEAR(issue_date) as issue_year
690
from book
691
where YEAR(issue_date) = {? issue_year}
692
EOS
693
   
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
694
    my $result = $dbi->execute(
update pod
Yuki Kimoto authored on 2011-01-28
695
        $sql,
696
        param => {issue_year => '2010'},
697
        filter => {issue_year => 'tp_to_year'}
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
698
    );
699

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

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

            
selection can contain where ...
Yuki Kimoto authored on 2011-03-06
705
You can remove filter by C<remove_filter()>
706

            
707
    $result->remove_filter;
708

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

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

            
715
    $result->end_filter(issue_date => sub {
716
        my $tp = shift;
717
        
718
        return '' unless $tp;
719
        return $tp->strftime('%Y/%m/%d %h:%m:%s (%a)');
720
    });
721

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

            
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-02-28
724
You can remove end_filter by C<end_filter>
725

            
726
    $result->remove_end_filter;
727

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

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

            
733
    $dbi->each_column(
734
        sub {
735
            my ($self, $table, $column, $info) = @_;
736
            
737
            my $type = $info->{TYPE_NAME};
738
            
739
            my $filter = $type eq 'DATE'     ? {out => 'tp_to_date', in => 'date_to_tp'}
740
                       : $type eq 'DATETIME' ? {out => 'tp_to_datetime', in => 'datetime_to_tp'}
741
                                             : undef;
742
            
743
            $self->apply_filter($table, $column, $filter)
744
              if $filter;
745
        }
746
    );
747

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

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

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

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

            
758
    select * from book where {= title} and {like author};
759

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

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

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

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

            
770
    select from book \\{ ... \\}
771

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

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

            
776
    select * from book where title = ? and author like ?;
777

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

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

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

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

            
788
    $dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'}
789
                  filter => {title => 'to_something');
790

            
update pod
Yuki Kimoto authored on 2011-02-07
791
Note that at C<execute()> the filter applied by C<apply_filter()>
792
don't has effective to columns.
improved table search in col...
Yuki Kimoto authored on 2011-04-12
793
You have to use C<table> option
update pod
Yuki Kimoto authored on 2011-01-28
794

            
improved table search in col...
Yuki Kimoto authored on 2011-04-12
795
    $dbi->execute($sql, table => ['author', 'book']);
update pod
Yuki Kimoto authored on 2011-01-28
796

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

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

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

            
803
    {? NAME}    ->   ?
804

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

            
807
    {= NAME}    ->   NAME = ?
808

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

            
811
    {<> NAME}   ->   NAME <> ?
812

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

            
815
    {< NAME}    ->   NAME < ?
816

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

            
819
    {> NAME}    ->   NAME > ?
820

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

            
823
    {>= NAME}   ->   NAME >= ?
824

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

            
827
    {<= NAME}   ->   NAME <= ?
828

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

            
831
    {like NAME}   ->   NAME like ?
832

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

            
835
    {in NAME COUNT}   ->   NAME in [?, ?, ..]
836

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

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

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

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

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

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

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

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

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

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

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

            
861
    $dbi->register_tag(
862
        '=' => sub {
863
            my $column = shift;
864
            
865
            return ["$column = ?", [$column]];
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
866
        }
867
    );
868

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

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

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

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

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

            
881
    [
update pod
Yuki Kimoto authored on 2011-02-11
882
        String after expanding,
883
        [COLUMN1(This is used for place holder), COLUMN2 , ...]
update pod
Yuki Kimoto authored on 2011-01-28
884
    ]
885

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

            
888
    'title = ?'
889

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

            
893
    ['title']
894

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

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

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

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

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

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

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

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

            
913
    where {= title}
914

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

            
917
    where {= author}
918

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

            
921
    where {= title} and {=author}
922

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

            
926
    my $where = $dbi->where;
927

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

            
930
    $where->clause(
931
        ['and', '{= title'}, '{= author}']
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
932
    );
933

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

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

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

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

            
943
    ['and', 
944
      '{= title}', 
945
      ['or', '{= author}', '{like date}']
946
    ]
947

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

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

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

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

            
959
    my $where_clause = $where->to_string;
960

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

            
963
    where {= title}
964

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

            
967
    my $where_clause = "$where";
968

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

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

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

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

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

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

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

            
984
    $where->clause(
985
        ['and', '{> date}', '{< date}']
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
986
    );
update pod
Yuki Kimoto authored on 2011-01-28
987
    $where->param($p);
988

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

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

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

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

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

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

            
1002
    my $p = {date => []};
1003

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

            
1006
    my @date;
1007
    push @date, exists $param->{start_date} ? $param->{start_date}
1008
                                            : $dbi->not_exists;
1009
    push @date, $param->{end_date} if exists $param->{end_date};
1010
    my $p = {date => \@date};
1011

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

            
update pod
Yuki Kimoto authored on 2011-02-11
1014
You can pass L<DBIx::Custom::Where> object to C<where> of C<select()>.
update pod
Yuki Kimoto authored on 2011-01-28
1015
    
1016
    my $where = $dbi->where;
update pod
Yuki Kimoto authored on 2011-02-11
1017
    $where->clause(['and', '{= title}', '{= author}']);
1018
    $where->param({title => 'Perl'});
update pod
Yuki Kimoto authored on 2011-01-28
1019
    my $result = $dbi->select(table => 'book', where => $where);
1020

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-02-11
1031
    my $sql = <<"EOS";
1032
    select * from {table book};
update pod
Yuki Kimoto authored on 2011-01-28
1033
    $where
1034
    EOS
1035

            
1036
    $dbi->execute($sql, param => $param);
1037

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

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

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

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1047
    package MyModel;
1048
    
1049
    use base 'DBIx::Custom::Model';
update pod
Yuki Kimoto authored on 2011-01-28
1050

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

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1055
    package MyModel::book;
1056
    
1057
    use base 'MyModel';
1058
    
1059
    sub insert { ... }
1060
    sub list { ... }
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1061

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1064
    package MyModel::company;
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1065
    
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1066
    use base 'MyModel';
1067
    
1068
    sub insert { ... }
1069
    sub list { ... }
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1070

            
1071
The follwoing modules location is needed.
1072

            
1073
    MyModel.pm
1074
    MyModel / book.pm
1075
            / company.pm
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1076

            
1077
You can include these models by C<include_model()>
1078

            
1079
    $dbi->include_model('MyModel');
1080

            
1081
First argument is name space of model.
1082

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

            
1085
    my $result = $dbi->model('book')->list;
1086

            
1087
In mode, You can use such as methods,
1088
C<insert()>, C<update()>, C<update_all()>,
1089
C<delete()>, C<delete_all()>, C<select()>
1090
without C<table> option.
1091

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

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

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

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-02-11
1106
    # DBIx::Custom method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1107
    $model->execute($sql);
update pod
Yuki Kimoto authored on 2011-02-11
1108
    
1109
    # DBI method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1110
    $model->begin_work;
1111
    $model->commit;
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
1112

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

            
1115
    my @models = keys %{$self->models};
1116

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

            
1119
   $model->primary_key(['id', 'number_id']);
1120

            
update pod
Yuki Kimoto authored on 2011-02-28
1121
Primary key is used by C<insert_at>, C<update_at()>, C<delete_at()>,
add DBIx::Custom::Model fore...
Yuki Kimoto authored on 2011-02-21
1122
C<select_at()>.
1123

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1124
by C<filter> you can define filters applied by C<apply_filter()>
1125

            
1126
    $model->filter({
1127
        title  => {out => ..., in => ..., end => ...},
1128
        author => {out => ..., in => ..., end => ...}
1129
    });
1130

            
1131
This filters is applied when C<include_model()> is called.
1132

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

            
1135
    $model->columns(['id', 'number_id']);
1136

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

            
1140
    $dbi->setup_model;
1141

            
cleanup
Yuki Kimoto authored on 2011-03-10
1142
You can set C<join>
cleanup
Yuki Kimoto authored on 2011-02-22
1143

            
cleanup
Yuki Kimoto authored on 2011-03-10
1144
    $model->join(['left outer join company on book.company_id = company.id']);
cleanup
Yuki Kimoto authored on 2011-02-22
1145

            
cleanup
Yuki Kimoto authored on 2011-03-10
1146
This C<join> is used by C<select()>, C<select_at()>
cleanup
Yuki Kimoto authored on 2011-02-22
1147

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

            
1150
Class name, model name, and table name is a little different.
1151
Generally Class name is model name, and table name is model name.
1152

            
1153
    CLASS        MODEL              TABLE
1154
    book         (CLASS) -> book    (MODEL) -> book
1155

            
1156
You can change model name.
1157

            
1158
    package MyModel::book;
1159
    
1160
    __PACAKGE__->attr(name => 'book_model');
1161

            
1162
    CLASS        MODEL         TABLE
1163
    book         book_model    (MODEL) -> book_model
1164

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

            
1167
    $dbi->model('book_model');
1168

            
1169
You can change table name.
1170

            
1171
    package MyModel::book;
1172
    
1173
    __PACAKGE__->attr(table => 'book_table');
1174

            
1175
    CLASS        MODEL              TABLE
1176
    book         (CLASS) -> book    book_table
1177

            
1178
Table name is the table really accessed.
1179

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

            
cleanup
Yuki Kimoto authored on 2011-03-21
1182
=head2 Create column clause automatically : mycolumn(), column()
1183

            
1184
To create column clause automatically, use C<mycolumn()>.
1185
Valude of C<table> and C<columns> is used.
1186

            
1187
    my $column_clause = $model->mycolumn;
1188

            
1189
If C<table> is 'book'�AC<column> is ['id', 'name'],
1190
the following clause is created.
1191

            
1192
    book.id as id, book.name as name
1193

            
1194
These column name is for removing column name ambiguities.
1195

            
1196
You can create column clause from columns of other table.
1197

            
1198
    my $column_clause = $model->column('company');
1199

            
1200
If C<table> is 'company'�AC<column> is ['id', 'name'],
1201
the following clause is created.
1202

            
1203
    company.id as company__id, company.name as company__name
1204

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

            
1207
To create column clause automatically, use C<column_clause()>.
1208
Valude of C<table> and C<columns> is used.
1209

            
1210
    my $column_clause = $model->column_clause;
1211

            
1212
If C<table> is 'book'�AC<column> is ['id', 'name'],
1213
the following clause is created.
1214

            
1215
    book.id as id, book.name as name
1216

            
1217
These column name is for removing column name ambiguities.
1218

            
1219
If you remove some columns, use C<remove> option.
1220

            
1221
    my $column_clause = $model->column_clause(remove => ['id']);
1222

            
1223
If you add some column, use C<add> option.
1224

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

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

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

            
update pod
Yuki Kimoto authored on 2011-01-28
1231
    package MyDBI;
1232
    
1233
    use base 'DBIx::Custom';
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1234
    
update pod
Yuki Kimoto authored on 2011-01-28
1235
    sub connect {
1236
        my $self = shift->SUPER::connect(@_);
1237
        
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1238
        $self->include_model(
1239
            MyModel => [
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1240
                'book',
1241
                'company'
1242
            ]
update pod
Yuki Kimoto authored on 2011-01-28
1243
        );
1244
    }
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1245
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1246
    package MyModel::book;
1247
    use base 'DBIx::Custom::Model';
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1248
    
add experimental update_at()...
Yuki Kimoto authored on 2011-02-21
1249
    __PACKAGE__->attr('primary_key' => sub { ['id'] };
1250
    
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1251
    sub insert { ... }
1252
    sub list { ... }
1253
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1254
    package MyModel::company;
1255
    use base 'DBIx::Custom::Model';
add experimental update_at()...
Yuki Kimoto authored on 2011-02-21
1256

            
1257
    __PACKAGE__->attr('primary_key' => sub { ['id'] };
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1258
    
1259
    sub insert { ... }
1260
    sub list { ... }
update pod
Yuki Kimoto authored on 2011-01-28
1261

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

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

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

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

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

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

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

            
1286
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
1287

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

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-01-28
1301
    $dbi->method(
1302
        update_or_insert => sub {
1303
            my $self = shift;
1304
            # something
1305
        },
1306
        find_or_create   => sub {
1307
            my $self = shift;
1308
            # something
1309
        }
1310
    );
1311

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

            
1314
    $dbi->update_or_insert;
1315
    $dbi->find_or_create;
1316

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

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

            
update pod
Yuki Kimoto authored on 2011-01-28
1321
    package MyResult;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1322
    use base 'DBIx::Custom::Result';
1323
    
1324
    sub some_method { ... }
1325

            
1326
    1;
1327
    
1328
    package main;
1329
    
update pod
Yuki Kimoto authored on 2011-01-28
1330
    use MyResult;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1331
    
1332
    my $dbi = DBIx::Custom->connect(...);
update pod
Yuki Kimoto authored on 2011-01-28
1333
    $dbi->result_class('MyResult');
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1334

            
pod fix
Yuki Kimoto authored on 2011-01-21
1335
=head1 EXAMPLES
1336

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

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

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