Newer Older
605 lines | 14.657kb
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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            
290
    my $where = $dbi->where;
291

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

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

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

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

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

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

            
307
    ['and', 
updated pod
Yuki Kimoto authored on 2011-06-07
308
      'title = :title', 
309
      ['or', 'author = :author', 'date like :date']
update pod
Yuki Kimoto authored on 2011-01-28
310
    ]
311

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

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

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

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

            
323
    my $where_clause = $where->to_string;
324

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

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

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

            
331
    my $where_clause = "$where";
332

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

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

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

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

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

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

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

            
348
    $where->clause(
updated pod
Yuki Kimoto authored on 2011-06-07
349
        ['and', 'date > :date', 'date < :date']
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
350
    );
update pod
Yuki Kimoto authored on 2011-01-28
351
    $where->param($p);
352

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

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

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

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

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

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

            
366
    my $p = {date => []};
367

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

            
370
    my @date;
371
    push @date, exists $param->{start_date} ? $param->{start_date}
372
                                            : $dbi->not_exists;
373
    push @date, $param->{end_date} if exists $param->{end_date};
374
    my $p = {date => \@date};
375

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

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

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-02-11
395
    my $sql = <<"EOS";
updated pod
Yuki Kimoto authored on 2011-06-07
396
    select * from book;
update pod
Yuki Kimoto authored on 2011-01-28
397
    $where
398
    EOS
399

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

            
402
=head2 Filtering
403

            
404
=head3 Register filter : C<register_filter>
405

            
406
If you want to register filter, use C<register_filter>.
407

            
408
    $dbi->register_filter(
409
        # Time::Piece object to DATE format
410
        tp_to_date => sub {
411
            my $date = shift;
412
            return $tp->strftime('%Y-%m-%d');
413
        },
414
        
415
        # DATE to Time::Piece object
416
        date_to_tp => sub {
417
            my $date = shift;
418
            return Time::Piece->strptime($date, '%Y-%m-%d');
419
        },
420
    );
421

            
422
=head3 Filter before sending data into database : C<filter> option
423

            
424
If you filter sending data, use C<filter> option.
425

            
426
    $dbi->execute(
427
        'insert into book (date) values (:date)',
428
        {date => $tp},
429
        filter => {date => 'tp_to_date'}
430
    );
431

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

            
434
    $dbi->insert(
435
        {date => $tp},
436
        table => 'book',
437
        filter => {date => 'tp_to_date'}
438
    );
439

            
440
=head3 Filter after fetching data from database.
441

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

            
444
    my $result = $dbi->select(column => 'date', table => 'book');
445
    $result->filter(date => 'date_to_tp');
446
    my $row = $result->one;
update pod
Yuki Kimoto authored on 2011-01-28
447

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

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

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

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

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

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

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
466
    package MyModel::book;
updatedd pod
Yuki Kimoto authored on 2011-06-12
467
    use MyModel -base;
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
468
    
469
    sub insert { ... }
470
    sub list { ... }
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
471

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

            
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
474
    package MyModel::company;
updatedd pod
Yuki Kimoto authored on 2011-06-12
475
    use MyModel -base;
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
476
    
477
    sub insert { ... }
478
    sub list { ... }
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
479

            
480
The follwoing modules location is needed.
481

            
482
    MyModel.pm
483
    MyModel / book.pm
484
            / company.pm
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-24
485

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

            
488
    $dbi->include_model('MyModel');
489

            
490
First argument is name space of model.
491

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

            
494
    my $result = $dbi->model('book')->list;
495

            
496
In mode, You can use such as methods,
updatedd pod
Yuki Kimoto authored on 2011-06-12
497
C<insert>, C<update>, C<update_all>,
498
C<delete>, C<delete_all>, C<select>
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
499
without C<table> option.
500

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

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

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

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

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

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

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

            
update pod
Yuki Kimoto authored on 2011-02-11
515
    # DBIx::Custom method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
516
    $model->execute($sql);
update pod
Yuki Kimoto authored on 2011-02-11
517
    
518
    # DBI method
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
519
    $model->begin_work;
520
    $model->commit;
added experimental DBIx::Cus...
Yuki Kimoto authored on 2011-01-17
521

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
522
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
523

            
524
    my @models = keys %{$self->models};
525

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

            
528
   $model->primary_key(['id', 'number_id']);
529

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

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

            
535
    $model->columns(['id', 'number_id']);
536

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

            
540
    $dbi->setup_model;
541

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

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

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

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

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

            
cleanup
Yuki Kimoto authored on 2011-06-13
553
    my $mycolumns = $model->mycolumn;
cleanup
Yuki Kimoto authored on 2011-03-21
554

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

            
558
    book.id as id, book.name as name
559

            
560
These column name is for removing column name ambiguities.
561

            
562
You can create column clause from columns of other table.
563

            
cleanup
Yuki Kimoto authored on 2011-06-13
564
    my $columns = $model->column('company');
cleanup
Yuki Kimoto authored on 2011-03-21
565

            
cleanup
Yuki Kimoto authored on 2011-06-13
566
If C<table> is "company", C<column> return ['id', 'name'],
cleanup
Yuki Kimoto authored on 2011-03-21
567
the following clause is created.
568

            
cleanup
Yuki Kimoto authored on 2011-06-13
569
    company.id as "company.id", company.name as "company.name"
add experimental DBIx::Custo...
Yuki Kimoto authored on 2011-02-22
570

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

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

            
update pod
Yuki Kimoto authored on 2011-01-28
575
    package MyDBI;
updatedd pod
Yuki Kimoto authored on 2011-06-12
576
    use DBIx::Custom -base;
added DBIx::Custom::Guides
yuki-kimoto authored on 2010-10-17
577
    
update pod
Yuki Kimoto authored on 2011-01-28
578
    sub connect {
579
        my $self = shift->SUPER::connect(@_);
580
        
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
581
        $self->include_model(
582
            MyModel => [
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
583
                'book',
584
                'company'
585
            ]
update pod
Yuki Kimoto authored on 2011-01-28
586
        );
587
    }
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
588
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
589
    package MyModel::book;
updatedd pod
Yuki Kimoto authored on 2011-06-12
590
    use DBIx::Custom::Model -base;
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
591
    
updatedd pod
Yuki Kimoto authored on 2011-06-12
592
    has primary_key => sub { ['id'] };
add experimental update_at()...
Yuki Kimoto authored on 2011-02-21
593
    
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
594
    sub insert { ... }
595
    sub list { ... }
596
    
add feture. all model class ...
Yuki Kimoto authored on 2011-02-18
597
    package MyModel::company;
updatedd pod
Yuki Kimoto authored on 2011-06-12
598
    use DBIx::Custom::Model -base;
add experimental update_at()...
Yuki Kimoto authored on 2011-02-21
599

            
updatedd pod
Yuki Kimoto authored on 2011-06-12
600
    has primary_key => sub { ['id'] };
removed experimental base_ta...
Yuki Kimoto authored on 2011-02-15
601
    
602
    sub insert { ... }
603
    sub list { ... }
update pod
Yuki Kimoto authored on 2011-01-28
604

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