Newer Older
679 lines | 16.697kb
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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
7
=head1 FEATURES
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
8

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
9
L<DBIx::Custom> is the wrapper class of L<DBI> to execute SQL easily.
10
This module have the following features.
update pod
Yuki Kimoto authored on 2011-01-28
11

            
12
=over 4
13

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
14
=item * Execute INSERT, UPDATE, DELETE, SELECT statement easily
update pod
Yuki Kimoto authored on 2011-01-28
15

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
16
=item * You can specify bind values by hash reference
update pod
Yuki Kimoto authored on 2011-01-28
17

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
18
=item * Filtering by data type. and you can set filter to any column
update pod
Yuki Kimoto authored on 2011-01-28
19

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
20
=item * Creating where clause flexibly
update pod
Yuki Kimoto authored on 2011-01-28
21

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
22
=imte * Support model
update pod
Yuki Kimoto authored on 2011-01-28
23

            
24
=back
25

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
26
=head1 GUIDE
update pod
Yuki Kimoto authored on 2011-01-28
27

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
28
=head2 Connect To Database
update pod
Yuki Kimoto authored on 2011-01-28
29

            
30
    use DBIx::Custom;
31
    my $dbi = DBIx::Custom->connect(
- update_param_tag is DEPREC...
Yuki Kimoto authored on 2011-06-07
32
        dsn => "dbi:mysql:database=bookshop",
update pod
Yuki Kimoto authored on 2011-01-28
33
        user => 'ken',
34
        password => '!LFKD%$&',
updatedd pod
Yuki Kimoto authored on 2011-06-12
35
        dbi_option => {mysql_enable_utf8 => 1}
update pod
Yuki Kimoto authored on 2011-01-28
36
    );
37

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
38
You can connect to database by C<connect> method.
39
C<dsn> is data source name, C<user> is user name, C<password> is password.
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
40

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
41
C<dbi_option> is L<DBI> option.
42
By default, the following option is set.
43
Fatal error throw exeption and commit mode is auto commit.
deprecated DBIx::Custom::MyS...
root authored on 2010-11-26
44

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
45
    {
46
        RaiseError  =>  1
47
        PrintError  =>  0
48
        AutoCommit  =>  1
49
    }
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
50

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
51
=head2 Execute Query
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
52

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
53
=head3 Insert Statement : C<insert>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
54

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
55
If you want to execute insert statement, use C<insert> method.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
56

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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
59
First argument is insert row data, C<table>  is table name.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
60

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
61
=head3 Update Statement : C<update>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
62

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
63
If you want to execute update stateimuse, use C<update> method.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
64

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
65
    $dbi->update(
66
        {title => 'Perl', author => 'Ken'},
67
        table  => 'book', 
68
        where  => {id => 5}
69
    );
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
70

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
71
First argument is update row data, C<table> is table name, C<where> is condition.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
72

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
73
Note that you can't execute C<update> method without C<where>.
74
If you want to update all rows, use update_all.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
75

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
76
    $dbi->update_all({title => 'Perl', author => 'Ken'}, table  => 'book');
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
77

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
78
=head3 Delete Statement : C<delete>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
79

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
80
If you want to execute delete statement, use C<delete> method.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
81

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
82
    $dbi->delete(table  => 'book', where  => {author => 'Ken'});
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
83

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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
86
Note that you can't execute C<delete> method without C<where>.
87
If you want to delete all rows, use C<delete_all> method.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
88

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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
91
=head3 Select Statement : C<select>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
92

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
93
If you want to execute select statement, use C<select> method.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
94

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

            
update pod
Yuki Kimoto authored on 2011-01-31
97
Return value is L<DBIx::Custom::Result> object.
updatedd pod
Yuki Kimoto authored on 2011-06-12
98
You can fetch rows by C<fetch> method.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
99

            
100
    while (my $row = $result->fetch) {
101
        my $title  = $row->[0];
102
        my $author = $row->[1];
103
    }
104

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
105
See also L<Fetch row/"Fetch row"> about L<DBIx::Custom::Result>.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
106

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
107
You can specify column names by C<column> option
108
and condition by C<where> option.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
109

            
110
    my $result = $dbi->select(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
111
        table  => 'book',
update pod
Yuki Kimoto authored on 2011-01-28
112
        column => ['author',  'title'],
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
113
        where  => {author => 'Ken'}
114
    );
115

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
116
You can specify join clause by C<join> option.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
117

            
118
    my $result = $dbi->select(
cleanup
Yuki Kimoto authored on 2011-03-10
119
        table  => 'book',
updatedd pod
Yuki Kimoto authored on 2011-06-12
120
        column => ['company.name as company_name']
cleanup
Yuki Kimoto authored on 2011-03-10
121
        where  => {'book.name' => 'Perl'},
122
        join   => ['left outer join company on book.company_id = company.id]
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
123
    );
124

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
125
Note that join clause is joined only when C<where> or C<column> option contains table name,
126
such as book.name.
cleanup
Yuki Kimoto authored on 2011-03-21
127

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
128
You can append statement to the end of whole statement by C<append> option.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
129

            
130
    my $result = $dbi->select(
remove DBIx::Custom::Model
Yuki Kimoto authored on 2011-01-12
131
        table  => 'book',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
132
        where  => {author => 'Ken'},
update pod
Yuki Kimoto authored on 2011-01-28
133
        append => 'for update',
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
134
    );
135

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
136
=head3 C<execute>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
137

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
138
If you want to execute SQL, use C<execute> method.
update pod
Yuki Kimoto authored on 2011-01-28
139

            
140
    $dbi->execute("select * from book;");
141

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
142
You can specify parameters.
update pod
Yuki Kimoto authored on 2011-01-28
143

            
144
    $dbi->execute(
updated pod
Yuki Kimoto authored on 2011-06-07
145
        "select * from book title = :title and author = :author;"
updatedd pod
Yuki Kimoto authored on 2011-06-12
146
        {title => 'Perl', author => 'Ken'}
update pod
Yuki Kimoto authored on 2011-01-28
147
    );
148

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
149
:title and :author is parameters, which is replaced to placeholers.
update pod
Yuki Kimoto authored on 2011-01-28
150

            
151
    select * from book title = ? and author = ?;
152

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
153
See also L<Parameter/"Parameter"> about parameter.
update pod
Yuki Kimoto authored on 2011-01-28
154

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
155
=head3 C<dbh>
update pod
Yuki Kimoto authored on 2011-01-28
156

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
157
    my $dbh = $dbi->dbh;
update pod
Yuki Kimoto authored on 2011-01-28
158

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
159
Get get database handle object of L<DBI>.
update pod
Yuki Kimoto authored on 2011-01-28
160

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
161
=head3 C<DBI> methods
update pod
Yuki Kimoto authored on 2011-01-28
162

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
163
    $dbi->do(...);
164
    $dbi->begin_work;
update pod
Yuki Kimoto authored on 2011-01-28
165

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
166
You can call all methods of L<DBI> from L<DBIx::Custom> object.
update pod
Yuki Kimoto authored on 2011-01-28
167

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
168
=head2 Fetch Rows
169

            
170
C<select> method return value is L<DBIx::Custom::Result> object.
171
You can fetch a row or rows by various methods.
172

            
173
=head3 Fetch a row (array) : C<fetch>
update pod
Yuki Kimoto authored on 2011-02-04
174

            
175
    my $row = $result->fetch;
176

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
177
C<fetch> method fetch a row and put it into array reference.
178
You can continue to fetch 
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
179

            
180
    while (my $row = $result->fetch) {
update pod
Yuki Kimoto authored on 2011-01-28
181
        my $title  = $row->[0];
182
        my $author = $row->[1];
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
183
    }
184

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
185
=head3 Fetch only first row (array) : C<fetch_first>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
186

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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
189
C<fetch_first> fetch a only first row and finish statment handle,
190
and put it into array refrence.
update pod
Yuki Kimoto authored on 2011-01-28
191

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

            
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
194
    my $rows = $result->fetch_all;
195

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
196
C<fetch_all> fetch all rows and put them into array of array reference.
update pod
Yuki Kimoto authored on 2011-01-28
197

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
198
=head3 Fetch a row (hash) : C<fetch_hash>
update pod
Yuki Kimoto authored on 2011-01-28
199

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
200
    my $row = $result->fetch_hash;
update pod
Yuki Kimoto authored on 2011-01-28
201

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
202
C<fetch_hash> fetch a row and put it into hash reference.
203
You can fetch a row while row exists.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
204

            
205
    while (my $row = $result->fetch_hash) {
206
        my $title  = $row->{title};
207
        my $author = $row->{author};
208
    }
209

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
210
=head3 Fetch only a first row (hash) : C<fetch_hash_first>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
211

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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
214
C<fetch_hash_first> fetch only a first row and finish statement handle,
215
and put them into hash refrence.
update pod
Yuki Kimoto authored on 2011-01-28
216

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
217
C<one> is C<fetch_hash_first> synonym to save word typing.
update pod
Yuki Kimoto authored on 2011-01-28
218

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
219
    my $row = $result->one;
update pod
Yuki Kimoto authored on 2011-01-28
220

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
221
=head3 Fetch all rows (hash) : C<fetch_hash_all>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
222

            
223
    my $rows = $result->fetch_hash_all;
224

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
225
C<fetch_hash_all> fetch all rows and put them into array of hash reference.
update pod
Yuki Kimoto authored on 2011-01-28
226

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
227
=head3 Statement Handle : C<sth>
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
228

            
229
    my $sth = $result->sth;
230

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
231
If you want to get statment handle, use <sth> method.
update pod
Yuki Kimoto authored on 2011-01-28
232

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
233
=head2 Parameter
update pod
Yuki Kimoto authored on 2011-01-28
234

            
updated pod
Yuki Kimoto authored on 2011-06-07
235
=head3 Basic of Parameter
update pod
Yuki Kimoto authored on 2011-01-28
236

            
updated pod
Yuki Kimoto authored on 2011-06-07
237
You can embedd parameter into SQL.
update pod
Yuki Kimoto authored on 2011-01-28
238

            
updated pod
Yuki Kimoto authored on 2011-06-07
239
    select * from book where title = :title and author like :author;
update pod
Yuki Kimoto authored on 2011-01-28
240

            
updated pod
Yuki Kimoto authored on 2011-06-07
241
:title and :author is parameter
update pod
Yuki Kimoto authored on 2011-01-28
242

            
updated pod
Yuki Kimoto authored on 2011-06-07
243
Parameter is converted to place holder.
update pod
Yuki Kimoto authored on 2011-01-28
244

            
245
    select * from book where title = ? and author like ?;
246

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
247
use C<execute> to execute SQL.
update pod
Yuki Kimoto authored on 2011-01-28
248

            
updated pod
Yuki Kimoto authored on 2011-06-07
249
    my $sql = "select * from book where title = :title and author like :author;"
updatedd pod
Yuki Kimoto authored on 2011-06-12
250
    $dbi->execute($sql, {title => 'Perl', author => '%Ken%'});
update pod
Yuki Kimoto authored on 2011-01-28
251

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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
255
You can specify C<filter> at C<execute>.
update pod
Yuki Kimoto authored on 2011-01-28
256

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
257
    $dbi->execute($sql, {title => 'Perl', author => '%Ken%'}
update pod
Yuki Kimoto authored on 2011-01-28
258
                  filter => {title => 'to_something');
259

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
260
Note that at C<execute> the filter applied by C<apply_filter>
update pod
Yuki Kimoto authored on 2011-02-07
261
don't has effective to columns.
improved table search in col...
Yuki Kimoto authored on 2011-04-12
262
You have to use C<table> option
update pod
Yuki Kimoto authored on 2011-01-28
263

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

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

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

            
updated pod
Yuki Kimoto authored on 2011-06-07
271
    my $sql = "select * from table where date > :date and date < :date;";
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
272

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
273
In this case, You specify parameter values as array reference.
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
274

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
275
    my $dbi->execute($sql, {date => ['2010-10-01', '2012-02-10']});
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
276

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
277
=head2 Create where clause
update pod
Yuki Kimoto authored on 2011-01-28
278

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
279
=head3 Dinamically create where clause : where
update pod
Yuki Kimoto authored on 2011-01-28
280

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

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

            
updated pod
Yuki Kimoto authored on 2011-06-07
286
    where title = :title
update pod
Yuki Kimoto authored on 2011-01-28
287

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

            
updated pod
Yuki Kimoto authored on 2011-06-07
290
    where author = :author
update pod
Yuki Kimoto authored on 2011-01-28
291

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

            
updated pod
Yuki Kimoto authored on 2011-06-07
294
    where title = :title and author = :author
update pod
Yuki Kimoto authored on 2011-01-28
295

            
update pod
Yuki Kimoto authored on 2011-02-11
296
L<DBIx::Custom> support dinamic where clause creating.
updatedd pod
Yuki Kimoto authored on 2011-06-12
297
At first, create L<DBIx::Custom::Where> object by C<where>.
update pod
Yuki Kimoto authored on 2011-01-28
298

            
299
    my $where = $dbi->where;
300

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
301
Set clause by C<clause>
update pod
Yuki Kimoto authored on 2011-01-28
302

            
303
    $where->clause(
updated pod
Yuki Kimoto authored on 2011-06-07
304
        ['and', 'title = :title, 'author = :author']
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
305
    );
306

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

            
updated pod
Yuki Kimoto authored on 2011-06-07
309
    ['or' or 'and', PART1, PART1, PART1]
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
310

            
update pod
Yuki Kimoto authored on 2011-02-11
311
First argument is 'or' or 'and'.
updated pod
Yuki Kimoto authored on 2011-06-07
312
Later than first argument are part which contains parameter.
update pod
Yuki Kimoto authored on 2011-01-28
313

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

            
316
    ['and', 
updated pod
Yuki Kimoto authored on 2011-06-07
317
      'title = :title', 
318
      ['or', 'author = :author', 'date like :date']
update pod
Yuki Kimoto authored on 2011-01-28
319
    ]
320

            
updated pod
Yuki Kimoto authored on 2011-06-07
321
This mean "title = :title and ( author = :author or date like :date )".
update pod
Yuki Kimoto authored on 2011-02-11
322

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

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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
329
If you execute C<string_to>, you can get where clause
update pod
Yuki Kimoto authored on 2011-02-11
330
which contain only parameter name.
update pod
Yuki Kimoto authored on 2011-01-28
331

            
332
    my $where_clause = $where->to_string;
333

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

            
updated pod
Yuki Kimoto authored on 2011-06-07
336
    where title = :title
update pod
Yuki Kimoto authored on 2011-01-28
337

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

            
340
    my $where_clause = "$where";
341

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

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

            
updated pod
Yuki Kimoto authored on 2011-06-07
346
Even if same name parameters exists, you can create where clause.
update pod
Yuki Kimoto authored on 2011-02-11
347
Let's think that there are starting date and ending date.
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
348

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

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

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

            
updated pod
Yuki Kimoto authored on 2011-06-07
355
You can embbed these values into same name parameters.
update pod
Yuki Kimoto authored on 2011-01-28
356

            
357
    $where->clause(
updated pod
Yuki Kimoto authored on 2011-06-07
358
        ['and', 'date > :date', 'date < :date']
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
359
    );
update pod
Yuki Kimoto authored on 2011-01-28
360
    $where->param($p);
361

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

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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
366
You can get DBIx::Custom::NotExists object by C<not_exists>
update pod
Yuki Kimoto authored on 2011-02-11
367
This mean correnspondinf value isn't exists.
update pod
Yuki Kimoto authored on 2011-01-28
368

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

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

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

            
375
    my $p = {date => []};
376

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

            
379
    my @date;
380
    push @date, exists $param->{start_date} ? $param->{start_date}
381
                                            : $dbi->not_exists;
382
    push @date, $param->{end_date} if exists $param->{end_date};
383
    my $p = {date => \@date};
384

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
385
=head3 With C<select>
update pod
Yuki Kimoto authored on 2011-01-28
386

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
387
You can pass L<DBIx::Custom::Where> object to C<where> of C<select>.
update pod
Yuki Kimoto authored on 2011-01-28
388
    
389
    my $where = $dbi->where;
updated pod
Yuki Kimoto authored on 2011-06-07
390
    $where->clause(['and', 'title = :title', 'author = :author']);
update pod
Yuki Kimoto authored on 2011-02-11
391
    $where->param({title => 'Perl'});
update pod
Yuki Kimoto authored on 2011-01-28
392
    my $result = $dbi->select(table => 'book', where => $where);
393

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
394
You can also pass it to C<where> of C<update>AC<delete>
update pod
Yuki Kimoto authored on 2011-01-28
395

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
396
=head3 With C<execute>
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
397

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

            
update pod
Yuki Kimoto authored on 2011-01-28
400
    my $where = $dbi->where;
updated pod
Yuki Kimoto authored on 2011-06-07
401
    $where->clause(['and', 'title = :title', 'author = :author']);
update pod
Yuki Kimoto authored on 2011-02-11
402
    $where->param({title => 'Perl'});
update pod
Yuki Kimoto authored on 2011-01-28
403

            
update pod
Yuki Kimoto authored on 2011-02-11
404
    my $sql = <<"EOS";
updated pod
Yuki Kimoto authored on 2011-06-07
405
    select * from book;
update pod
Yuki Kimoto authored on 2011-01-28
406
    $where
407
    EOS
408

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
409
    $dbi->execute($sql, $param, table => 'book');
410

            
411
=head2 Filtering
412

            
413
=head3 Register filter : C<register_filter>
414

            
415
If you want to register filter, use C<register_filter>.
416

            
417
    $dbi->register_filter(
418
        # Time::Piece object to DATE format
419
        tp_to_date => sub {
420
            my $date = shift;
421
            return $tp->strftime('%Y-%m-%d');
422
        },
423
        
424
        # DATE to Time::Piece object
425
        date_to_tp => sub {
426
            my $date = shift;
427
            return Time::Piece->strptime($date, '%Y-%m-%d');
428
        },
429
    );
430

            
431
=head3 Filter before sending data into database : C<filter> option
432

            
433
If you filter sending data, use C<filter> option.
434

            
435
    $dbi->execute(
436
        'insert into book (date) values (:date)',
437
        {date => $tp},
438
        filter => {date => 'tp_to_date'}
439
    );
440

            
441
You can use C<filter> option in C<insert>, C<update>, C<delete>, C<select> method.
442

            
443
    $dbi->insert(
444
        {date => $tp},
445
        table => 'book',
446
        filter => {date => 'tp_to_date'}
447
    );
448

            
449
=head3 Filter after fetching data from database.
450

            
451
If you filter fetch data, use L<DBIx::Custom::Result>'s C<filter> method.
452

            
453
    my $result = $dbi->select(column => 'date', table => 'book');
454
    $result->filter(date => 'date_to_tp');
455
    my $row = $result->one;
update pod
Yuki Kimoto authored on 2011-01-28
456

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

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

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
464
At first, you create basic model class extending <DBIx::Custom::Model>.
updatedd pod
Yuki Kimoto authored on 2011-06-12
465
Each L<DBIx::Custom> class inherit L<Object::Simple>.
466
so you can inherit the following way.
update pod
Yuki Kimoto authored on 2011-01-28
467

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
468
    package MyModel;
updatedd pod
Yuki Kimoto authored on 2011-06-12
469
    use DBIx::Custom::Model -base;
update pod
Yuki Kimoto authored on 2011-01-28
470

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

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
475
    package MyModel::book;
updatedd pod
Yuki Kimoto authored on 2011-06-12
476
    use MyModel -base;
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
477
    
478
    sub insert { ... }
479
    sub list { ... }
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
480

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
483
    package MyModel::company;
updatedd pod
Yuki Kimoto authored on 2011-06-12
484
    use MyModel -base;
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
485
    
486
    sub insert { ... }
487
    sub list { ... }
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
488

            
489
The follwoing modules location is needed.
490

            
491
    MyModel.pm
492
    MyModel / book.pm
493
            / company.pm
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
494

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
495
You can include these models by C<include_model>
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
496

            
497
    $dbi->include_model('MyModel');
498

            
499
First argument is name space of model.
500

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

            
503
    my $result = $dbi->model('book')->list;
504

            
505
In mode, You can use such as methods,
updatedd pod
Yuki Kimoto authored on 2011-06-12
506
C<insert>, C<update>, C<update_all>,
507
C<delete>, C<delete_all>, C<select>
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
508
without C<table> option.
509

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
510
    $dbi->model('book')->insert($param);
update pod
Yuki Kimoto authored on 2011-01-28
511

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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
514
If you need table nameAyou can get it by C<table>.
update pod
Yuki Kimoto authored on 2011-01-28
515

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-02-11
524
    # DBIx::Custom method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
525
    $model->execute($sql);
update pod
Yuki Kimoto authored on 2011-02-11
526
    
527
    # DBI method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
528
    $model->begin_work;
529
    $model->commit;
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
530

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
531
If you want to get all models, you can get them by keys of C<models>.
add models() attribute
Yuki Kimoto authored on 2011-02-21
532

            
533
    my @models = keys %{$self->models};
534

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

            
537
   $model->primary_key(['id', 'number_id']);
538

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
539
Primary key is used by C<insert>, C<update>, C<delete>,
540
and C<select> methods.
add DBIx::Custom::Model fore...
Yuki Kimoto authored on 2011-02-21
541

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
542
by C<filter> you can define filters applied by C<apply_filter>
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
543

            
544
    $model->filter({
545
        title  => {out => ..., in => ..., end => ...},
546
        author => {out => ..., in => ..., end => ...}
547
    });
548

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
549
This filters is applied when C<include_model> is called.
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
550

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

            
553
    $model->columns(['id', 'number_id']);
554

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
555
Column names is automarically set by C<setup_model>.
556
This method is needed to be call after C<include_model>.
add experimental setup_model...
Yuki Kimoto authored on 2011-02-21
557

            
558
    $dbi->setup_model;
559

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

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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
564
C<join> is used by C<select> method.
cleanup
Yuki Kimoto authored on 2011-02-22
565

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

            
568
Class name, model name, and table name is a little different.
569
Generally Class name is model name, and table name is model name.
570

            
571
    CLASS        MODEL              TABLE
572
    book         (CLASS) -> book    (MODEL) -> book
573

            
574
You can change model name.
575

            
576
    package MyModel::book;
577
    
578
    __PACAKGE__->attr(name => 'book_model');
579

            
580
    CLASS        MODEL         TABLE
581
    book         book_model    (MODEL) -> book_model
582

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
583
Model name is the name used by L<model> of L<DBIx::Custom>.
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
584

            
585
    $dbi->model('book_model');
586

            
587
You can change table name.
588

            
589
    package MyModel::book;
590
    
591
    __PACAKGE__->attr(table => 'book_table');
592

            
593
    CLASS        MODEL              TABLE
594
    book         (CLASS) -> book    book_table
595

            
596
Table name is the table really accessed.
597

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

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
600
=head2 Create column clause automatically : mycolumn, column
cleanup
Yuki Kimoto authored on 2011-03-21
601

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
602
To create column clause automatically, use C<mycolumn>.
cleanup
Yuki Kimoto authored on 2011-03-21
603
Valude of C<table> and C<columns> is used.
604

            
605
    my $column_clause = $model->mycolumn;
606

            
data_source is DEPRECATED! I...
Yuki Kimoto authored on 2011-06-06
607
If C<table> is 'book'AC<column> is ['id', 'name'],
cleanup
Yuki Kimoto authored on 2011-03-21
608
the following clause is created.
609

            
610
    book.id as id, book.name as name
611

            
612
These column name is for removing column name ambiguities.
613

            
614
You can create column clause from columns of other table.
615

            
616
    my $column_clause = $model->column('company');
617

            
data_source is DEPRECATED! I...
Yuki Kimoto authored on 2011-06-06
618
If C<table> is 'company'AC<column> is ['id', 'name'],
cleanup
Yuki Kimoto authored on 2011-03-21
619
the following clause is created.
620

            
621
    company.id as company__id, company.name as company__name
622

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
623
=head2 Create column clause automatically : column_clause
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-22
624

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
625
To create column clause automatically, use C<column_clause>.
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-22
626
Valude of C<table> and C<columns> is used.
627

            
628
    my $column_clause = $model->column_clause;
629

            
data_source is DEPRECATED! I...
Yuki Kimoto authored on 2011-06-06
630
If C<table> is 'book'AC<column> is ['id', 'name'],
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-22
631
the following clause is created.
632

            
633
    book.id as id, book.name as name
634

            
635
These column name is for removing column name ambiguities.
636

            
637
If you remove some columns, use C<remove> option.
638

            
639
    my $column_clause = $model->column_clause(remove => ['id']);
640

            
641
If you add some column, use C<add> option.
642

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

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

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

            
update pod
Yuki Kimoto authored on 2011-01-28
649
    package MyDBI;
updatedd pod
Yuki Kimoto authored on 2011-06-12
650
    use DBIx::Custom -base;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
651
    
update pod
Yuki Kimoto authored on 2011-01-28
652
    sub connect {
653
        my $self = shift->SUPER::connect(@_);
654
        
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
655
        $self->include_model(
656
            MyModel => [
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
657
                'book',
658
                'company'
659
            ]
update pod
Yuki Kimoto authored on 2011-01-28
660
        );
661
    }
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
662
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
663
    package MyModel::book;
updatedd pod
Yuki Kimoto authored on 2011-06-12
664
    use DBIx::Custom::Model -base;
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
665
    
updatedd pod
Yuki Kimoto authored on 2011-06-12
666
    has primary_key => sub { ['id'] };
add experimental update_at()...
Yuki Kimoto authored on 2011-02-21
667
    
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
668
    sub insert { ... }
669
    sub list { ... }
670
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
671
    package MyModel::company;
updatedd pod
Yuki Kimoto authored on 2011-06-12
672
    use DBIx::Custom::Model -base;
add experimental update_at()...
Yuki Kimoto authored on 2011-02-21
673

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
674
    has primary_key => sub { ['id'] };
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
675
    
676
    sub insert { ... }
677
    sub list { ... }
update pod
Yuki Kimoto authored on 2011-01-28
678

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