Newer Older
1388 lines | 34.307kb
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

            
added select() all_column op...
Yuki Kimoto authored on 2011-03-12
9
(This guide will be completed nearly future and contains EXPERIMENTAL features
10
The features marked EXPERIMENTAL in POD of L<DBIx::Custom> is
11
EXPERIMENTAL ones)
update pod
Yuki Kimoto authored on 2011-01-26
12

            
update pod
Yuki Kimoto authored on 2011-01-28
13
L<DBIx::Custom> is the class to make easy to execute SQL.
14
This is L<DBI> wrapper class like L<DBIx::Class> or L<DBIx::Simple>.
15
You can do thing more easy than L<DBIx::Class>, more flexible
16
than L<DBIx::Simple>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
17

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

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

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

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

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

            
36
    select * from book where title = ? and author = ?;
37

            
38
Maybe you ask me that this conversion is meaningful.
39
On the top of this, usuful features is implemented.
40
See the following descriptions.
41

            
42
=over 4
43

            
44
=item 1. Specify place holder binding value as hash refernce
45

            
46
If you use L<DBI>, you must specify place holder binding value
47
as array.
48

            
49
    $sth->execute(@bind);
50

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

            
56
=item 2. Filtering
57

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

            
64
You can use filtering system.
65

            
66
At first, register filter.
67

            
68
    $dbi->register_filter(
69
        tp_to_date => sub {
70
            ...
71
        },
72
        date_to_tp => sub {
73
            ...
74
        }
75
    );
76

            
77
next, apply this filter to each column.
78

            
79
    $dbi->apply_filter('book',
80
        'issue_date' => {out => 'tp_to_date', in => 'date_to_tp'}
81
    );
82

            
83
C<out> is perl-to-database way. C<in> is perl-from-database way.
84

            
85
This filter is automatically enabled in many method.
86

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

            
89

            
90
=item 3. Selective search condition
91

            
92
It is difficult to create selective where clause in L<DBI>.
93
For example, If C<title> and C<author> is specified, we create 
94
the following SQL.
95

            
96
    select * from book where title = ? and author = ?;
97

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

            
100
    select * from book where title = ?;
101

            
102
If only C<author> is specified, the following one,
103

            
104
    select * from book where author = ?;
105

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

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

            
120
    # Embbed where clause to SQL
121
    my $sql = "select * from book $where";
122

            
123
You can create where clause which has selected search condition.
124
You can write nesting of where clause and C<or> condition
125

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

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

            
131
    my $param = {title => 'Perl', author => 'Ken'};
132
    $dbi->insert(table => 'book', param => $param);
133

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

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

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

            
update pod
Yuki Kimoto authored on 2011-01-30
147
use the mehtod.
update pod
Yuki Kimoto authored on 2011-01-28
148

            
149
    $dbi->table('book')->list;
150

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

            
154
=back
155

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

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

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

            
163
    use DBIx::Custom;
164

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

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

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

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

            
180
    "dbi:mysql:database=$database"
181
    "dbi:mysql:database=$database;host=$hostname;port=$port"
182

            
update pod
Yuki Kimoto authored on 2011-01-28
183
B<SQLite>
184

            
185
    "dbi:SQLite:dbname=$database"
186
    "dbi:SQLite:dbname=:memory:"
187

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

            
190
    "dbi:Pg:dbname=$dbname"
191

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

            
194
    "dbi:Oracle:$dbname"
195
    "dbi:Oracle:host=$host;sid=$sid"
196

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

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

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

            
203
   "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
204

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

            
207
L<DBIx::Custom> is wrapper class of L<DBI>.
208
You can use all methods of L<DBI> from L<DBIx::Custom> object.
209

            
210
    $dbi->do(...);
211
    $dbi->begin_work;
update pod
Yuki Kimoto authored on 2011-01-28
212

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

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

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

            
219
    RaiseError  ->  1
220
    PrintError  ->  0
221
    AutoCommit  ->  1
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
222

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            
294
    while (my $row = $result->fetch) {
295
        my $title  = $row->[0];
296
        my $author = $row->[1];
297
    }
298

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

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

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

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

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

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

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

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

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

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

            
cleanup
Yuki Kimoto authored on 2011-03-21
328
    select company.name as company__name
329
    from book
cleanup
Yuki Kimoto authored on 2011-03-10
330
      left outer join company on book.company_id = company.id
cleanup
Yuki Kimoto authored on 2011-03-21
331
    where book.name = ?;
cleanup
Yuki Kimoto authored on 2011-03-10
332

            
333
company_if of book and id of company is left outer joined.
334

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

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

            
346
Following SQL is executeed.
347

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

            
350
You can specify column names easily using C<mycolumn()> and C<column()>.
351

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

            
361
The following SQL is executed.
362

            
363
    select book.name as name,
364
      company.id as comapny__id,
365
      company.name as company__name
366
    from book
367
      left outer join company on book.company_id = company.id
368

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

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

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

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

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

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

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

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

            
390
    $dbi->execute("select * from book;");
391

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

            
394
    $dbi->execute(
395
        "select * from book {= title} and {= author};"
396
        param => {title => 'Perl', author => 'Ken'}
397
    );
398

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

            
401
    select * from book title = ? and author = ?;
402

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

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

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

            
409
    $dbi->execute('select * from book');
410

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

            
413
To insert row by using primary key, use C<insert_at()>
414

            
415
    $dbi->insert_at(
416
        table => 'book', primary_key => ['id'],
417
        where => ['123'], param => {name => 'Ken'}
418
    );
419

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

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

            
426
To update row by using primary key, use C<update_at()>
427

            
428
    $dbi->update_at(
429
        table => 'book', primary_key => ['id'],
430
        where => ['123'], param => {name => 'Ken'}
431
    );
432

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

            
437
=head3 Delete by using primary key : C<delete_at()>
438

            
439
To delete row by using primary key, use C<delete_at()>
440

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

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

            
446
You can also write arguments like this.
447

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

            
450
=head3 Select by using primary key : C<select_at()>
451

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

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

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

            
459
You can also write arguments like this.
460

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

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

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

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

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

            
472
    my $row = $result->fetch;
473

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

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

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

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

            
485
    my $row = $result->fetch_first;
486

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

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

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

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

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

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

            
507
    [
508
        ['Perl', 'Ken'],
509
        ['Ruby', 'Mark']
510
    ]
511

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

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

            
517
    my $rows = $result->fetch_all;
518

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

            
521
    [
522
        ['Perl', 'Ken'],
523
        ['Ruby', 'Mark']
524
    ]
525

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

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

            
530
    while (my $row = $result->fetch_hash) {
531
        my $title  = $row->{title};
532
        my $author = $row->{author};
533
    }
534

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

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

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

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

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

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

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

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

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

            
561
    [
562
        {title => 'Perl', author => 'Ken'},
563
        {title => 'Ruby', author => 'Mark'}
564
    ]
565

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

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

            
572
    my $rows = $result->fetch_hash_all;
573

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

            
576
    [
577
        {title => 'Perl', author => 'Ken'},
578
        {title => 'Ruby', author => 'Mark'}
579
    ]
580

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

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

            
585
    my $sth = $result->sth;
586

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

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

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

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

            
599
    $dbi->register_filter(
600
        # Time::Piece object to DATE format
601
        tp_to_date => sub {
602
            my $date = shift;
603

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

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

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

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

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

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

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

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

            
635
    issue_date => {out => sub { ... }, in => sub { ... }}
636

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

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

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

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

            
649
    my $row = $resutl->fetch_hash_first;
650
    my $tp = $row->{issue_date};
651

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

            
654
    $dbi->select(
655
        table => 'book',
update pod
Yuki Kimoto authored on 2011-02-07
656
        where => {'book.issue_date' => $tp}
update pod
Yuki Kimoto authored on 2011-01-28
657
    );
658

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

            
661
    my $result = $dbi->execute(
662
       "select issue_date as book__issue_date from book");
663

            
664
You can apply C<end> filter execute after C<in> filter.
665

            
666
    $dbi->apply_filter('book',
667
        issue_date => {out => 'tp_to_date', in => 'date_to_tp',
668
                       end => 'tp_to_displaydate'},
669
    );
670

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

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

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

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

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

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

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

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

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

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

            
711
    $result->remove_filter;
712

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

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

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

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

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

            
730
    $result->remove_end_filter;
731

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

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

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

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

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

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

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

            
762
    select * from book where {= title} and {like author};
763

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

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

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

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

            
774
    select from book \\{ ... \\}
775

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

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

            
780
    select * from book where title = ? and author like ?;
781

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

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

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

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

            
792
    $dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'}
793
                  filter => {title => 'to_something');
794

            
update pod
Yuki Kimoto authored on 2011-02-07
795
Note that at C<execute()> the filter applied by C<apply_filter()>
796
don't has effective to columns.
update pod
Yuki Kimoto authored on 2011-02-11
797
You have to use C<table> tag in SQL
update pod
Yuki Kimoto authored on 2011-01-28
798

            
update pod
Yuki Kimoto authored on 2011-02-11
799
    my $sql = "select * from {table book} where {= author} and {like title};"
update pod
Yuki Kimoto authored on 2011-01-28
800

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

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

            
update pod
Yuki Kimoto authored on 2011-02-11
805
=head4 C<table>
806

            
807
    {table NAME} -> NAME
808

            
809
This is used to specify table name in SQL.
810
If you specify table name, Filtering by 
811
C<apply_filter()> is effective.
812

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

            
815
    {? NAME}    ->   ?
816

            
update pod
Yuki Kimoto authored on 2011-01-31
817
=head4 C<=>
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<lt>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<E<gt>>
update pod
Yuki Kimoto authored on 2011-01-28
830

            
831
    {> NAME}    ->   NAME > ?
832

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

            
835
    {>= NAME}   ->   NAME >= ?
836

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

            
839
    {<= NAME}   ->   NAME <= ?
840

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

            
843
    {like NAME}   ->   NAME like ?
844

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

            
847
    {in NAME COUNT}   ->   NAME in [?, ?, ..]
848

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

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

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

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

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

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

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

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

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

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

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

            
873
    $dbi->register_tag(
874
        '=' => sub {
875
            my $column = shift;
876
            
877
            return ["$column = ?", [$column]];
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
878
        }
879
    );
880

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

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

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

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

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

            
893
    [
update pod
Yuki Kimoto authored on 2011-02-11
894
        String after expanding,
895
        [COLUMN1(This is used for place holder), COLUMN2 , ...]
update pod
Yuki Kimoto authored on 2011-01-28
896
    ]
897

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

            
900
    'title = ?'
901

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

            
905
    ['title']
906

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

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

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

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

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

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

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

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

            
925
    where {= title}
926

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

            
929
    where {= author}
930

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

            
933
    where {= title} and {=author}
934

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

            
938
    my $where = $dbi->where;
939

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

            
942
    $where->clause(
943
        ['and', '{= title'}, '{= author}']
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
944
    );
945

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

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

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

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

            
955
    ['and', 
956
      '{= title}', 
957
      ['or', '{= author}', '{like date}']
958
    ]
959

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

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

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

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

            
971
    my $where_clause = $where->to_string;
972

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

            
975
    where {= title}
976

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

            
979
    my $where_clause = "$where";
980

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

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

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

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

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

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

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

            
996
    $where->clause(
997
        ['and', '{> date}', '{< date}']
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
998
    );
update pod
Yuki Kimoto authored on 2011-01-28
999
    $where->param($p);
1000

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

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

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

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

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

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

            
1014
    my $p = {date => []};
1015

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

            
1018
    my @date;
1019
    push @date, exists $param->{start_date} ? $param->{start_date}
1020
                                            : $dbi->not_exists;
1021
    push @date, $param->{end_date} if exists $param->{end_date};
1022
    my $p = {date => \@date};
1023

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

            
update pod
Yuki Kimoto authored on 2011-02-11
1026
You can pass L<DBIx::Custom::Where> object to C<where> of C<select()>.
update pod
Yuki Kimoto authored on 2011-01-28
1027
    
1028
    my $where = $dbi->where;
update pod
Yuki Kimoto authored on 2011-02-11
1029
    $where->clause(['and', '{= title}', '{= author}']);
1030
    $where->param({title => 'Perl'});
update pod
Yuki Kimoto authored on 2011-01-28
1031
    my $result = $dbi->select(table => 'book', where => $where);
1032

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-02-11
1043
    my $sql = <<"EOS";
1044
    select * from {table book};
update pod
Yuki Kimoto authored on 2011-01-28
1045
    $where
1046
    EOS
1047

            
1048
    $dbi->execute($sql, param => $param);
1049

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

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

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

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1059
    package MyModel;
1060
    
1061
    use base 'DBIx::Custom::Model';
update pod
Yuki Kimoto authored on 2011-01-28
1062

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

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1067
    package MyModel::book;
1068
    
1069
    use base 'MyModel';
1070
    
1071
    sub insert { ... }
1072
    sub list { ... }
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1073

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1076
    package MyModel::company;
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1077
    
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1078
    use base 'MyModel';
1079
    
1080
    sub insert { ... }
1081
    sub list { ... }
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1082

            
1083
The follwoing modules location is needed.
1084

            
1085
    MyModel.pm
1086
    MyModel / book.pm
1087
            / company.pm
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
1088

            
1089
You can include these models by C<include_model()>
1090

            
1091
    $dbi->include_model('MyModel');
1092

            
1093
First argument is name space of model.
1094

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

            
1097
    my $result = $dbi->model('book')->list;
1098

            
1099
In mode, You can use such as methods,
1100
C<insert()>, C<update()>, C<update_all()>,
1101
C<delete()>, C<delete_all()>, C<select()>
1102
without C<table> option.
1103

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

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

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

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-02-11
1118
    # DBIx::Custom method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1119
    $model->execute($sql);
update pod
Yuki Kimoto authored on 2011-02-11
1120
    
1121
    # DBI method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1122
    $model->begin_work;
1123
    $model->commit;
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
1124

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

            
1127
    my @models = keys %{$self->models};
1128

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

            
1131
   $model->primary_key(['id', 'number_id']);
1132

            
update pod
Yuki Kimoto authored on 2011-02-28
1133
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
1134
C<select_at()>.
1135

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

            
1138
    $model->filter({
1139
        title  => {out => ..., in => ..., end => ...},
1140
        author => {out => ..., in => ..., end => ...}
1141
    });
1142

            
1143
This filters is applied when C<include_model()> is called.
1144

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

            
1147
    $model->columns(['id', 'number_id']);
1148

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

            
1152
    $dbi->setup_model;
1153

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

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

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

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

            
1162
Class name, model name, and table name is a little different.
1163
Generally Class name is model name, and table name is model name.
1164

            
1165
    CLASS        MODEL              TABLE
1166
    book         (CLASS) -> book    (MODEL) -> book
1167

            
1168
You can change model name.
1169

            
1170
    package MyModel::book;
1171
    
1172
    __PACAKGE__->attr(name => 'book_model');
1173

            
1174
    CLASS        MODEL         TABLE
1175
    book         book_model    (MODEL) -> book_model
1176

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

            
1179
    $dbi->model('book_model');
1180

            
1181
You can change table name.
1182

            
1183
    package MyModel::book;
1184
    
1185
    __PACAKGE__->attr(table => 'book_table');
1186

            
1187
    CLASS        MODEL              TABLE
1188
    book         (CLASS) -> book    book_table
1189

            
1190
Table name is the table really accessed.
1191

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

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

            
1196
To create column clause automatically, use C<mycolumn()>.
1197
Valude of C<table> and C<columns> is used.
1198

            
1199
    my $column_clause = $model->mycolumn;
1200

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

            
1204
    book.id as id, book.name as name
1205

            
1206
These column name is for removing column name ambiguities.
1207

            
1208
You can create column clause from columns of other table.
1209

            
1210
    my $column_clause = $model->column('company');
1211

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

            
1215
    company.id as company__id, company.name as company__name
1216

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

            
1219
To create column clause automatically, use C<column_clause()>.
1220
Valude of C<table> and C<columns> is used.
1221

            
1222
    my $column_clause = $model->column_clause;
1223

            
1224
If C<table> is 'book'�AC<column> is ['id', 'name'],
1225
the following clause is created.
1226

            
1227
    book.id as id, book.name as name
1228

            
1229
These column name is for removing column name ambiguities.
1230

            
1231
If you remove some columns, use C<remove> option.
1232

            
1233
    my $column_clause = $model->column_clause(remove => ['id']);
1234

            
1235
If you add some column, use C<add> option.
1236

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

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

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

            
update pod
Yuki Kimoto authored on 2011-01-28
1243
    package MyDBI;
1244
    
1245
    use base 'DBIx::Custom';
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1246
    
update pod
Yuki Kimoto authored on 2011-01-28
1247
    sub connect {
1248
        my $self = shift->SUPER::connect(@_);
1249
        
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1250
        $self->include_model(
1251
            MyModel => [
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1252
                'book',
1253
                'company'
1254
            ]
update pod
Yuki Kimoto authored on 2011-01-28
1255
        );
1256
    }
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1257
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1258
    package MyModel::book;
1259
    use base 'DBIx::Custom::Model';
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1260
    
add experimental update_at()...
Yuki Kimoto authored on 2011-02-21
1261
    __PACKAGE__->attr('primary_key' => sub { ['id'] };
1262
    
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1263
    sub insert { ... }
1264
    sub list { ... }
1265
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
1266
    package MyModel::company;
1267
    use base 'DBIx::Custom::Model';
add experimental update_at()...
Yuki Kimoto authored on 2011-02-21
1268

            
1269
    __PACKAGE__->attr('primary_key' => sub { ['id'] };
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
1270
    
1271
    sub insert { ... }
1272
    sub list { ... }
update pod
Yuki Kimoto authored on 2011-01-28
1273

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

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

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

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

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

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

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

            
1298
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
1299

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

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-01-28
1313
    $dbi->method(
1314
        update_or_insert => sub {
1315
            my $self = shift;
1316
            # something
1317
        },
1318
        find_or_create   => sub {
1319
            my $self = shift;
1320
            # something
1321
        }
1322
    );
1323

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

            
1326
    $dbi->update_or_insert;
1327
    $dbi->find_or_create;
1328

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

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

            
update pod
Yuki Kimoto authored on 2011-01-28
1333
    package MyResult;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1334
    use base 'DBIx::Custom::Result';
1335
    
1336
    sub some_method { ... }
1337

            
1338
    1;
1339
    
1340
    package main;
1341
    
update pod
Yuki Kimoto authored on 2011-01-28
1342
    use MyResult;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1343
    
1344
    my $dbi = DBIx::Custom->connect(...);
update pod
Yuki Kimoto authored on 2011-01-28
1345
    $dbi->result_class('MyResult');
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1346

            
update pod
Yuki Kimoto authored on 2011-02-11
1347
=head3 Caching
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1348

            
update pod
Yuki Kimoto authored on 2011-02-11
1349
SQL after parsing tag is cached for performance.
1350
You can set C<cache()>. By default, chaching is true.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1351

            
update pod
Yuki Kimoto authored on 2011-01-28
1352
    $dbi->cache(1);
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1353

            
update pod
Yuki Kimoto authored on 2011-02-11
1354
The way to cache is changed by C<cache_method()>.
1355
Default method is the following one.
1356
Cache is saved to memory.
update pod
Yuki Kimoto authored on 2011-01-28
1357

            
1358
    $dbi->cache_method(sub {
1359
        sub {
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1360
            my $self = shift;
update pod
Yuki Kimoto authored on 2011-01-28
1361
            
1362
            $self->{_cached} ||= {};
1363
            
1364
            if (@_ > 1) {
update pod
Yuki Kimoto authored on 2011-02-11
1365
                # Save cache
update pod
Yuki Kimoto authored on 2011-01-28
1366
                $self->{_cached}{$_[0]} = $_[1] 
1367
            }
1368
            else {
update pod
Yuki Kimoto authored on 2011-02-11
1369
                # Get cache
update pod
Yuki Kimoto authored on 2011-01-28
1370
                return $self->{_cached}{$_[0]}
1371
            }
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1372
        }
update pod
Yuki Kimoto authored on 2011-01-28
1373
    });
1374
    
update pod
Yuki Kimoto authored on 2011-02-11
1375
First argument is L<DBIx::Custom> object.
1376
Second argument is SQL before parsing.
1377
Third argument is SQL information after parsing. This is hash reference.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1378

            
update pod
Yuki Kimoto authored on 2011-02-11
1379
If third argument exists, you save cache,
1380
and if third argument isn't exists, you get chace.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
1381

            
pod fix
Yuki Kimoto authored on 2011-01-21
1382
=head1 EXAMPLES
1383

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

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

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