X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FIntro.pod;h=5c7f7e5809c9d76dd87d012f510472681b614198;hb=5f550837848772810f7ebe6067e1e50150560d39;hp=53f20ef056322fdd3210ec9ccedc24e99757c0db;hpb=772547820db1ef7538e36902bd728c6afc11bde8;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/Intro.pod b/lib/DBIx/Class/Manual/Intro.pod index 53f20ef..5c7f7e5 100644 --- a/lib/DBIx/Class/Manual/Intro.pod +++ b/lib/DBIx/Class/Manual/Intro.pod @@ -4,136 +4,309 @@ DBIx::Class::Manual::Intro - Introduction to DBIx::Class =head1 INTRODUCTION -So, you are bored with SQL, and want a native Perl interface for your -database? Or you've been doing this for a while with L, -and think there's a better way? You've come to the right place. -Let's look at how you can set and use your first native L -tree. +You're bored with SQL, and want a native Perl interface for your database? Or +you've been doing this for a while with L, and think there's a +better way? You've come to the right place. -First we'll see how you can set up your classes yourself. If you want -them to be auto-discovered, just skip to the next section, which shows -you how to use L. +=head1 THE DBIx::Class WAY + +Here are a few simple tips that will help you get your bearings with +DBIx::Class. + +=head2 Tables become Result classes + +DBIx::Class needs to know what your Table structure looks like. You +do that by defining Result classes. Result classes are defined by +calling methods proxied to L. Each Result +class defines one Table, which defines the Columns it has, along with +any Relationships it has to other tables. (And oh, so much more +besides) The important thing to understand: + + A Result class == Table + +(most of the time, but just bear with my simplification) + +=head2 It's all about the ResultSet + +So, we've got some ResultSources defined. Now, we want to actually use those +definitions to help us translate the queries we need into handy perl objects! + +Let's say we defined a ResultSource for an "album" table with three columns: +"albumid", "artist", and "title". Any time we want to query this table, we'll +be creating a L from its ResultSource. For example, the +results of: + + SELECT albumid, artist, title FROM album; + +Would be retrieved by creating a ResultSet object from the album table's +ResultSource, likely by using the "search" method. + +DBIx::Class doesn't limit you to creating only simple ResultSets -- if you +wanted to do something like: + + SELECT title FROM album GROUP BY title; + +You could easily achieve it. + +The important thing to understand: + + Any time you would reach for a SQL query in DBI, you are + creating a DBIx::Class::ResultSet. + +=head2 Search is like "prepare" + +DBIx::Class tends to wait until it absolutely must fetch information from the +database. If you are returning a ResultSet, the query won't execute until you +use a method that wants to access the data. (Such as "next", or "first") + +The important thing to understand: + + Setting up a ResultSet does not execute the query; retrieving + the data does. + +=head2 Search results are returned as Rows + +Rows of the search from the database are blessed into +L objects. + +=head1 SETTING UP DBIx::Class + +Let's look at how you can set and use your first native L tree. + +First we'll see how you can set up your classes yourself. If you want them to +be auto-discovered, just skip to the L, which shows you how to use +L. =head2 Setting it up manually -First, you'll need a base class. It should inherit from -L like this: +First, you should create your base schema class, which inherits from +L: - package MyApp::DB; - use base qw/DBIx::Class/; + package My::Schema; + use base qw/DBIx::Class::Schema/; -You will also want to load some of the L components. -L provides a good starter set. In addition you'll -have to use either L or L. -We'll use C in this introduction, since it involves less magic. -C is mostly useful if you want to use multiple database -connections. +In this class you load your result_source ("table", "model") classes, which we +will define later, using the load_namespaces() method: - __PACKAGE__->load_components(qw/Core DB/); + # load My::Schema::Result::* and their resultset classes + __PACKAGE__->load_namespaces(); -If you want serial/auto-incrementing primary keys, you should use the -L component. - __PACKAGE__->load_components(qw/PK::Auto Core DB/); +By default this loads all the Result (Row) classes in the +My::Schema::Result:: namespace, and also any resultset classes in the +My::Schema::ResultSet:: namespace (if missing, the resultsets are +defaulted to be DBIx::Class::ResultSet objects). You can change the +result and resultset namespaces by using options to the +L call. -C classes exist for many databases; see -L for more information. +It is also possible to do the same things manually by calling +C for the Row classes and defining in those classes any +required resultset classes. -Once you've loaded the components, it's time to set up your -connection: +Next, create each of the classes you want to load as specified above: - __PACKAGE__->connection('dbi:SQLite:/home/me/myapp/my.db'); + package My::Schema::Result::Album; + use base qw/DBIx::Class::Core/; -This method is similar to the normal L C method, and can -take username, password, and L attribute hash as well as the DSN. +Load any additional components you may need with the load_components() method, +and provide component configuration if required. For example, if you want +automatic row ordering: -With that out of the way, we can define our first table class: + __PACKAGE__->load_components(qw/ Ordered /); + __PACKAGE__->position_column('rank'); - package MyApp::DB::Album; - use base qw/MyApp::DB/; +Ordered will refer to a field called 'position' unless otherwise directed. Here you are defining +the ordering field to be named 'rank'. (NOTE: Insert errors may occur if you use the Ordered +component, but have not defined a position column or have a 'position' field in your row.) -Then we specify which table it uses, +Set the table for your class: __PACKAGE__->table('album'); -and specify which columns it has. +Add columns to your class: + + __PACKAGE__->add_columns(qw/ albumid artist title rank /); + +Each column can also be set up with its own accessor, data_type and other pieces +of information that it may be useful to have -- just pass C a hash: + + __PACKAGE__->add_columns(albumid => + { accessor => 'album', + data_type => 'integer', + size => 16, + is_nullable => 0, + is_auto_increment => 1, + }, + artist => + { data_type => 'integer', + size => 16, + is_nullable => 0, + }, + title => + { data_type => 'varchar', + size => 256, + is_nullable => 0, + }, + rank => + { data_type => 'integer', + size => 16, + is_nullable => 0, + default_value => 0, + } + ); + +DBIx::Class doesn't directly use most of this data yet, but various related +modules such as L make use of it. +Also it allows you to create your database tables from your Schema, +instead of the other way around. +See L for details. + +See L for more details of the possible column +attributes. + +Accessors are created for each column automatically, so My::Schema::Result::Album will +have albumid() (or album(), when using the accessor), artist() and title() +methods. + +Define a primary key for your class: - __PACKAGE__->add_columns(qw/albumid artist title label year/); + __PACKAGE__->set_primary_key('albumid'); -This will automatically create accessors for each of the columns, so -that you can read/update the values in rows you've retrieved. +If you have a multi-column primary key, just pass a list instead: -Also, you need to tell it which column is the primary key: + __PACKAGE__->set_primary_key( qw/ albumid artistid / ); - __PACKAGE__->set_primary_key('albumid'); +Define this class' relationships with other classes using either C +to describe a column which contains an ID of another Table, or C to +make a predefined accessor for fetching objects that contain this Table's +foreign key: -If you have a primary key composed of multiple columns, just pass a -list instead. + # in My::Schema::Result::Artist + __PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist'); -That's pretty much all you need for a basic setup. If you have more -advanced needs like using more than one database connection for the -same class, see L. +See L for more information about the various types of +available relationships and how you can design your own. -=head2 Using L +=head2 Using DBIx::Class::Schema::Loader -This is an additional class, and not part of the L -distribution. Like L, it inspects your database, -and automatically creates classes for all the tables in your database. -Here's a simple setup: +This module (L) is an external module, and not part +of the L distribution. It inspects your database, and automatically +creates classes for all the tables in your schema. - package MyApp::DB; - use DBIx::Class::Loader; +The simplest way to use it is via the L script from the +L distribution. For example: - my $loader = DBIx::Class::Loader->new( - dsn => 'dbi:SQLite:/home/me/myapp/my.db', - namespace => 'MyApp::DB' + $ dbicdump -o dump_directory=./lib \ + -o components='["InflateColumn::DateTime"]' \ + MyApp::Schema dbi:mysql:mydb user pass + +If you have a mixed-case database, use the C option, e.g.: + + $ dbicdump -o dump_directory=./lib -o preserve_case=1 \ + -o components='["InflateColumn::DateTime"]' \ + MyApp::Schema dbi:mysql:mydb user pass + +If you are using L, then you can use the helper that comes with +L: + + $ script/myapp_create.pl model MyDB DBIC::Schema MyDB::Schema \ + create=static moniker_map='{ foo => "FOO" }' dbi:SQLite:./myapp.db \ + on_connect_do='PRAGMA foreign_keys=ON' quote_char='"' + +See L for more information on this +helper. + +See the L and L +documentation for more information on the many loader options. + +=head2 Connecting + +To connect to your Schema, you need to provide the connection details or a +database handle. + +=head3 Via connection details + +The arguments are the same as for L: + + my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db'); + +You can create as many different schema instances as you need. So if you have a +second database you want to access: + + my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs ); + +Note that L does not cache connections for you. If you use +multiple connections, you need to do this manually. + +To execute some SQL statements on every connect you can add them as an option in +a special fifth argument to connect: + + my $another_schema = My::Schema->connect( + $dsn, + $user, + $password, + $attrs, + { on_connect_do => \@on_connect_sql_statments } ); - 1; +See L for more information about +this and other special C-time options. + +=head3 Via a database handle -This should be equivalent to the manual setup in the section above. -L takes lots of other options. For more -information, consult its documentation. +The supplied coderef is expected to return a single connected database handle +(e.g. a L C<$dbh>) + + my $schema = My::Schema->connect ( + sub { Some::DBH::Factory->connect }, + \%extra_attrs, + ); =head2 Basic usage Once you've defined the basic classes, either manually or using -L, you can start interacting with your database. +L, you can start interacting with your database. + +To access your database using your $schema object, you can fetch a +L representing each of your tables by +calling the C method. + The simplest way to get a record is by primary key: - my $album = MyApp::DB::Album->find(14); + my $album = $schema->resultset('Album')->find(14); -This will run a C with C in the C clause, and +return an instance of C that represents this row. Once you +have that row, you can access and update columns: $album->title('Physical Graffiti'); my $title = $album->title; # $title holds 'Physical Graffiti' -If you prefer, you can use the C and C -accessors instead: +If you prefer, you can use the C and C accessors +instead: $album->set_column('title', 'Presence'); $title = $album->get_column('title'); -Just like with L, you do an C to commit your -changes to the database: +Just like with L, you call C to save your changes to the +database (by executing the actual C statement): $album->update; -If needed, you can throw away your local changes like this: +If needed, you can throw away your local changes: $album->discard_changes if $album->is_changed; -As you can see, C allows you to check if there are local -changes to your object. +As you can see, C allows you to check if there are local changes to +your object. =head2 Adding and removing rows -To create a new record in the database, you can use the C -method. It returns an instance of C that can be -used to access the data in the new record: +To create a new record in the database, you can use the C method. It +returns an instance of C that can be used to access the data +in the new record: - my $new_album = MyApp::DB::Album->create({ + my $new_album = $schema->resultset('Album')->create({ title => 'Wish You Were Here', artist => 'Pink Floyd' }); @@ -144,32 +317,31 @@ Now you can add data to the new record: $new_album->year('1975'); $new_album->update; -Likewise, you can remove it from the database like this: +Likewise, you can remove it from the database: $new_album->delete; -You can also remove records without or retrieving first. This -operation takes the same kind of arguments as a search. +You can also remove records without retrieving them first, by calling delete +directly on a ResultSet object. # Delete all of Falco's albums - MyApp::DB::Album->delete({ artist => 'Falco' }); + $schema->resultset('Album')->search({ artist => 'Falco' })->delete; =head2 Finding your objects -L provides a few different ways to retrieve data from -your database. Here's one example: +L provides a few different ways to retrieve data from your +database. Here's one example: # Find all of Santana's albums - my $rs = MyApp::DB::Album->search({ artist => 'Santana' }); + my $rs = $schema->resultset('Album')->search({ artist => 'Santana' }); -In scalar context, as above, C returns a -L object. It can be used to peek at the first -album returned by the database: +In scalar context, as above, C returns a L +object. It can be used to peek at the first album returned by the database: my $album = $rs->first; print $album->title; -Or, you can loop over the albums and update each one: +You can loop over the albums and update each one: while (my $album = $rs->next) { print $album->artist . ' - ' . $album->title; @@ -177,14 +349,16 @@ Or, you can loop over the albums and update each one: $album->update; } -For more information on what you can do with a -L, see L. +Or, you can update them all at once: + + $rs->update({ year => 2001 }); -In list context, the C method returns all of the matching -rows: +In list context, the C method returns all of the matching rows: # Fetch immediately all of Carlos Santana's albums - my @albums = MyApp::DB::Album->search({ artist => 'Carlos Santana' }); + my @albums = $schema->resultset('Album')->search( + { artist => 'Carlos Santana' } + ); foreach my $album (@albums) { print $album->artist . ' - ' . $album->title; } @@ -192,22 +366,22 @@ rows: We also provide a handy shortcut for doing a C search: # Find albums whose artist starts with 'Jimi' - my $rs = MyApp::DB::Album->search_like({ artist => 'Jimi%' }); + my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' }); -Or you can provide your own handmade C clause, like: +Or you can provide your own C clause: # Find Peter Frampton albums from the year 1986 my $where = 'artist = ? AND year = ?'; my @bind = ( 'Peter Frampton', 1986 ); - my $rs = MyApp::DB::Album->search_literal( $where, @bind ); + my $rs = $schema->resultset('Album')->search_literal( $where, @bind ); -The preferred way to generate complex queries is to provide a -L construct to C: +The preferred way to generate complex queries is to provide a L +construct to C: - my $rs = MyApp::DB::Album->search({ + my $rs = $schema->resultset('Album')->search({ artist => { '!=', 'Janis Joplin' }, year => { '<' => 1980 }, - albumid => [ 1, 14, 15, 65, 43 ] + albumid => { '-in' => [ 1, 14, 15, 65, 43 ] } }); This results in something like the following C clause: @@ -216,30 +390,85 @@ This results in something like the following C clause: AND year < 1980 AND albumid IN (1, 14, 15, 65, 43) -For more examples of complex queries, see -L. +For more examples of complex queries, see L. The search can also be modified by passing another hash with attributes: - my @albums = MyApp::DB::Album->search( + my @albums = My::Schema->resultset('Album')->search( { artist => 'Bob Marley' }, { rows => 2, order_by => 'year DESC' } ); C<@albums> then holds the two most recent Bob Marley albums. +For more information on what you can do with a L, see +L. + For a complete overview of the available attributes, see L. +=head1 NOTES + +=head2 The Significance and Importance of Primary Keys + +The concept of a L in +DBIx::Class warrants special discussion. The formal definition (which somewhat +resembles that of a classic RDBMS) is I. However this is where the +similarity ends. Any time you call a CRUD operation on a row (e.g. +L, +L, +L, +etc.) DBIx::Class will use the values of of the +L columns to populate +the C clause necessary to accomplish the operation. This is why it is +important to declare a L +on all your result sources B. +In a pinch one can always declare each row identifiable by all its columns: + + __PACKAGE__->set_primary_keys (__PACKAGE__->columns); + +Note that DBIx::Class is smart enough to store a copy of the PK values before +any row-object changes take place, so even if you change the values of PK +columns the C clause will remain correct. + +If you elect not to declare a C, DBIx::Class will behave correctly +by throwing exceptions on any row operation that relies on unique identifiable +rows. If you inherited datasets with multiple identical rows in them, you can +still operate with such sets provided you only utilize +L CRUD methods: +L, +L, +L + +For example, the following would not work (assuming C does not have +a declared PK): + + my $row = $schema->resultset('People') + ->search({ last_name => 'Dantes' }) + ->next; + $row->update({ children => 2 }); # <-- exception thrown because $row isn't + # necessarily unique + +So instead the following should be done: + + $schema->resultset('People') + ->search({ last_name => 'Dantes' }) + ->update({ children => 2 }); # <-- update's ALL Dantes to have children of 2 + +=head2 Problems on RHEL5/CentOS5 + +There used to be an issue with the system perl on Red Hat Enterprise +Linux 5, some versions of Fedora and derived systems. Further +information on this can be found in L + =head1 SEE ALSO =over 4 =item * L -=item * L - =back =cut