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