Commit | Line | Data |
9c82c181 |
1 | =head1 NAME |
2 | |
3b44ccc6 |
3 | DBIx::Class::Manual::Intro - Introduction to DBIx::Class |
9c82c181 |
4 | |
40dbc108 |
5 | =head1 INTRODUCTION |
076652e8 |
6 | |
40dbc108 |
7 | So, you are bored with SQL, and want a native Perl interface for your |
8 | database? Or you've been doing this for a while with L<Class::DBI>, |
35d4fe78 |
9 | and think there's a better way? You've come to the right place. |
10 | Let's look at how you can set and use your first native L<DBIx::Class> |
11 | tree. |
076652e8 |
12 | |
35d4fe78 |
13 | First we'll see how you can set up your classes yourself. If you want |
40dbc108 |
14 | them to be auto-discovered, just skip to the next section, which shows |
15 | you how to use L<DBIx::Class::Loader>. |
076652e8 |
16 | |
17 | =head2 Setting it up manually |
18 | |
35d4fe78 |
19 | First, you'll need a base class. It should inherit from |
20 | L<DBIx::Class> like this: |
076652e8 |
21 | |
35d4fe78 |
22 | package MyApp::DB; |
23 | use base qw/DBIx::Class/; |
076652e8 |
24 | |
35d4fe78 |
25 | You will also want to load some of the L<DBIx::Class> components. |
26 | L<DBIx::Class::Core> provides a good starter set. In addition you'll |
27 | have to use either L<DBIx::Class::Schema> or L<DBIx::Class::DB>. |
28 | We'll use C<DB> in this introduction, since it involves less magic. |
29 | C<Schema> is mostly useful if you want to use multiple database |
40dbc108 |
30 | connections. |
076652e8 |
31 | |
35d4fe78 |
32 | __PACKAGE__->load_components(qw/Core DB/); |
076652e8 |
33 | |
35d4fe78 |
34 | If you want serial/auto-incrementing primary keys, you should use the |
77254782 |
35 | L<DBIx::Class::PK::Auto> component. |
36 | __PACKAGE__->load_components(qw/PK::Auto Core DB/); |
076652e8 |
37 | |
35d4fe78 |
38 | C<PK::Auto> classes exist for many databases; see |
39 | L<DBIx::Class::PK::Auto> for more information. |
076652e8 |
40 | |
35d4fe78 |
41 | Once you've loaded the components, it's time to set up your |
42 | connection: |
076652e8 |
43 | |
35d4fe78 |
44 | __PACKAGE__->connection('dbi:SQLite:/home/me/myapp/my.db'); |
45 | |
46 | This method is similar to the normal L<DBI> C<connect> method, and can |
47 | take username, password, and L<DBI> attribute hash as well as the DSN. |
076652e8 |
48 | |
49 | With that out of the way, we can define our first table class: |
50 | |
35d4fe78 |
51 | package MyApp::DB::Album; |
52 | use base qw/MyApp::DB/; |
076652e8 |
53 | |
54 | Then we specify which table it uses, |
55 | |
35d4fe78 |
56 | __PACKAGE__->table('album'); |
076652e8 |
57 | |
58 | and specify which columns it has. |
59 | |
35d4fe78 |
60 | __PACKAGE__->add_columns(qw/albumid artist title label year/); |
076652e8 |
61 | |
40dbc108 |
62 | This will automatically create accessors for each of the columns, so |
63 | that you can read/update the values in rows you've retrieved. |
076652e8 |
64 | |
65 | Also, you need to tell it which column is the primary key: |
66 | |
35d4fe78 |
67 | __PACKAGE__->set_primary_key('albumid'); |
076652e8 |
68 | |
35d4fe78 |
69 | If you have a primary key composed of multiple columns, just pass a |
70 | list instead. |
076652e8 |
71 | |
35d4fe78 |
72 | That's pretty much all you need for a basic setup. If you have more |
40dbc108 |
73 | advanced needs like using more than one database connection for the |
74 | same class, see L<DBIx::Class::Schema>. |
076652e8 |
75 | |
40dbc108 |
76 | =head2 Using L<DBIx::Class::Loader> |
076652e8 |
77 | |
40dbc108 |
78 | This is an additional class, and not part of the L<DBIx::Class> |
79 | distribution. Like L<Class::DBI::Loader>, it inspects your database, |
35d4fe78 |
80 | and automatically creates classes for all the tables in your database. |
81 | Here's a simple setup: |
076652e8 |
82 | |
35d4fe78 |
83 | package MyApp::DB; |
84 | use DBIx::Class::Loader; |
40dbc108 |
85 | |
35d4fe78 |
86 | my $loader = DBIx::Class::Loader->new( |
87 | dsn => 'dbi:SQLite:/home/me/myapp/my.db', |
88 | namespace => 'MyApp::DB' |
89 | ); |
076652e8 |
90 | |
35d4fe78 |
91 | 1; |
076652e8 |
92 | |
35d4fe78 |
93 | This should be equivalent to the manual setup in the section above. |
94 | L<DBIx::Class::Loader> takes lots of other options. For more |
40dbc108 |
95 | information, consult its documentation. |
076652e8 |
96 | |
40dbc108 |
97 | =head2 Basic usage |
076652e8 |
98 | |
35d4fe78 |
99 | Once you've defined the basic classes, either manually or using |
100 | L<DBIx::Class::Loader>, you can start interacting with your database. |
101 | The simplest way to get a record is by primary key: |
076652e8 |
102 | |
35d4fe78 |
103 | my $album = MyApp::DB::Album->find(14); |
076652e8 |
104 | |
35d4fe78 |
105 | This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause, |
106 | and return an instance of C<MyApp::DB::Album> that represents this |
107 | row. Once you have that row, you can access and update columns: |
076652e8 |
108 | |
35d4fe78 |
109 | $album->title('Physical Graffiti'); |
110 | my $title = $album->title; # $title holds 'Physical Graffiti' |
076652e8 |
111 | |
40dbc108 |
112 | If you prefer, you can use the C<set_column> and C<get_column> |
113 | accessors instead: |
076652e8 |
114 | |
35d4fe78 |
115 | $album->set_column('title', 'Presence'); |
116 | $title = $album->get_column('title'); |
076652e8 |
117 | |
40dbc108 |
118 | Just like with L<Class::DBI>, you do an C<update> to commit your |
119 | changes to the database: |
120 | |
35d4fe78 |
121 | $album->update; |
076652e8 |
122 | |
35d4fe78 |
123 | If needed, you can throw away your local changes like this: |
076652e8 |
124 | |
35d4fe78 |
125 | $album->discard_changes if $album->is_changed; |
076652e8 |
126 | |
40dbc108 |
127 | As you can see, C<is_changed> allows you to check if there are local |
128 | changes to your object. |
076652e8 |
129 | |
40dbc108 |
130 | =head2 Adding and removing rows |
076652e8 |
131 | |
40dbc108 |
132 | To create a new record in the database, you can use the C<create> |
35d4fe78 |
133 | method. It returns an instance of C<MyApp::DB::Album> that can be |
134 | used to access the data in the new record: |
076652e8 |
135 | |
35d4fe78 |
136 | my $new_album = MyApp::DB::Album->create({ |
137 | title => 'Wish You Were Here', |
138 | artist => 'Pink Floyd' |
139 | }); |
dfeba824 |
140 | |
141 | Now you can add data to the new record: |
142 | |
35d4fe78 |
143 | $new_album->label('Capitol'); |
144 | $new_album->year('1975'); |
145 | $new_album->update; |
076652e8 |
146 | |
35d4fe78 |
147 | Likewise, you can remove it from the database like this: |
076652e8 |
148 | |
35d4fe78 |
149 | $new_album->delete; |
076652e8 |
150 | |
35d4fe78 |
151 | You can also remove records without or retrieving first. This |
152 | operation takes the same kind of arguments as a search. |
076652e8 |
153 | |
35d4fe78 |
154 | # Delete all of Falco's albums |
155 | MyApp::DB::Album->delete({ artist => 'Falco' }); |
076652e8 |
156 | |
40dbc108 |
157 | =head2 Finding your objects |
076652e8 |
158 | |
40dbc108 |
159 | L<DBIx::Class> provides a few different ways to retrieve data from |
35d4fe78 |
160 | your database. Here's one example: |
161 | |
162 | # Find all of Santana's albums |
163 | my $rs = MyApp::DB::Album->search({ artist => 'Santana' }); |
164 | |
165 | In scalar context, as above, C<search> returns a |
166 | L<DBIx::Class::ResultSet> object. It can be used to peek at the first |
167 | album returned by the database: |
168 | |
169 | my $album = $rs->first; |
170 | print $album->title; |
076652e8 |
171 | |
35d4fe78 |
172 | Or, you can loop over the albums and update each one: |
076652e8 |
173 | |
35d4fe78 |
174 | while (my $album = $rs->next) { |
175 | print $album->artist . ' - ' . $album->title; |
176 | $album->year(2001); |
177 | $album->update; |
178 | } |
a3c5e7e3 |
179 | |
35d4fe78 |
180 | For more information on what you can do with a |
181 | L<DBIx::Class::ResultSet>, see L<DBIx::Class::ResultSet/METHODS>. |
a3c5e7e3 |
182 | |
35d4fe78 |
183 | In list context, the C<search> method returns all of the matching |
a3c5e7e3 |
184 | rows: |
185 | |
35d4fe78 |
186 | # Fetch immediately all of Carlos Santana's albums |
187 | my @albums = MyApp::DB::Album->search({ artist => 'Carlos Santana' }); |
188 | foreach my $album (@albums) { |
189 | print $album->artist . ' - ' . $album->title; |
190 | } |
076652e8 |
191 | |
40dbc108 |
192 | We also provide a handy shortcut for doing a C<LIKE> search: |
076652e8 |
193 | |
35d4fe78 |
194 | # Find albums whose artist starts with 'Jimi' |
195 | my $rs = MyApp::DB::Album->search_like({ artist => 'Jimi%' }); |
076652e8 |
196 | |
40dbc108 |
197 | Or you can provide your own handmade C<WHERE> clause, like: |
35d4fe78 |
198 | |
199 | # Find Peter Frampton albums from the year 1986 |
200 | my $where = 'artist = ? AND year = ?'; |
201 | my @bind = ( 'Peter Frampton', 1986 ); |
202 | my $rs = MyApp::DB::Album->search_literal( $where, @bind ); |
40dbc108 |
203 | |
204 | The preferred way to generate complex queries is to provide a |
205 | L<SQL::Abstract> construct to C<search>: |
206 | |
35d4fe78 |
207 | my $rs = MyApp::DB::Album->search({ |
208 | artist => { '!=', 'Janis Joplin' }, |
209 | year => { '<' => 1980 }, |
210 | albumid => [ 1, 14, 15, 65, 43 ] |
211 | }); |
212 | |
213 | This results in something like the following C<WHERE> clause: |
40dbc108 |
214 | |
35d4fe78 |
215 | WHERE artist != 'Janis Joplin' |
216 | AND year < 1980 |
217 | AND albumid IN (1, 14, 15, 65, 43) |
218 | |
219 | For more examples of complex queries, see |
40dbc108 |
220 | L<DBIx::Class::Manual::Cookbook>. |
221 | |
222 | The search can also be modified by passing another hash with |
223 | attributes: |
224 | |
35d4fe78 |
225 | my @albums = MyApp::DB::Album->search( |
226 | { artist => 'Bob Marley' }, |
227 | { rows => 2, order_by => 'year DESC' } |
228 | ); |
229 | |
230 | C<@albums> then holds the two most recent Bob Marley albums. |
40dbc108 |
231 | |
232 | For a complete overview of the available attributes, see |
233 | L<DBIx::Class::ResultSet/ATTRIBUTES>. |
076652e8 |
234 | |
40dbc108 |
235 | =head1 SEE ALSO |
076652e8 |
236 | |
40dbc108 |
237 | =over 4 |
076652e8 |
238 | |
40dbc108 |
239 | =item * L<DBIx::Class::Manual::Cookbook> |
076652e8 |
240 | |
40dbc108 |
241 | =item * L<DBIx::Class::Manual::FAQ> |
076652e8 |
242 | |
40dbc108 |
243 | =back |
076652e8 |
244 | |
245 | =cut |