X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FIntro.pod;h=d6c218d043efa38c7551a2782884da0954a61014;hb=d71502b;hp=43e60cf8ef5012b9609502bfc70f611bf5f7b771;hpb=3f073ddff92999c67b2e68af9d5ece580c66bffd;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/Intro.pod b/lib/DBIx/Class/Manual/Intro.pod index 43e60cf..d6c218d 100644 --- a/lib/DBIx/Class/Manual/Intro.pod +++ b/lib/DBIx/Class/Manual/Intro.pod @@ -4,75 +4,79 @@ 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. +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. =head1 THE DBIx::Class WAY -Here are a few simple tips that will help you get your bearings -with DBIx::Class. +Here are a few simple tips that will help you get your bearings with +DBIx::Class. -=head2 Tables become ResultSources +=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 -DBIx::Class needs to know what your Table structure looks like. You do that -by defining Ls. Each table get's a ResultSource, -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 ResultSource == 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! +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 it's -ResultSource. For example, the results of: +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. + SELECT albumid, artist, title FROM album; -DBIx::Class doesn't limit you to creating only simple ResultSets -- -if you wanted to do something like: +Would be retrieved by creating a ResultSet object from the album table's +ResultSource, likely by using the "search" method. - SELECT title FROM album GROUP BY title; - -You could easily achieve it. +DBIx::Class doesn't limit you to creating only simple ResultSets -- if you +wanted to do something like: -The important thing to understand: + SELECT title FROM album GROUP BY title; - Any time you would reach for a SQL query in DBI, you are - creating a DBIx::Class::ResultSet. +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") +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. + 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. +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 next section, which shows -you how to use L. +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 @@ -82,41 +86,38 @@ L: package My::Schema; use base qw/DBIx::Class::Schema/; -In this class you load your result_source ("table", "model") classes, which -we will define later, using the load_classes() method. You can specify which -classes to load manually: +In this class you load your result_source ("table", "model") classes, which we +will define later, using the load_namespaces() method: - # load My::Schema::Album and My::Schema::Artist - __PACKAGE__->load_classes(qw/ Album Artist /); + # load My::Schema::Result::* and their resultset classes + __PACKAGE__->load_namespaces(); -Or load classes by namespace: +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. - # load My::Schema::Album, My::Schema::Artist and My::OtherSchema::LinerNotes - __PACKAGE__->load_classes( - { - 'My::Schema' => [qw/ Album Artist /], - 'My::OtherSchema' => [qw/ LinerNotes /] - } - ); - -Or let your schema class load all classes in its namespace automatically: - - # load My::Schema::* - __PACKAGE__->load_classes(); +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. Next, create each of the classes you want to load as specified above: - package My::Schema::Album; - use base qw/DBIx::Class/; + package My::Schema::Result::Album; + use base qw/DBIx::Class::Core/; -Load any components required by each class with the load_components() method. -This should consist of "Core" plus any additional components you want to use. -For example, if you want serial/auto-incrementing primary keys: +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: - __PACKAGE__->load_components(qw/ PK::Auto Core /); + __PACKAGE__->load_components(qw/ Ordered /); + __PACKAGE__->position_column('rank'); -C is supported for many databases; see -L for more information. +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.) Set the table for your class: @@ -124,11 +125,10 @@ Set the table for your class: Add columns to your class: - __PACKAGE__->add_columns(qw/ albumid artist title /); + __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 such as: +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', @@ -136,33 +136,35 @@ a hash such as: size => 16, is_nullable => 0, is_auto_increment => 1, - default_value => '', }, artist => { data_type => 'integer', size => 16, is_nullable => 0, - is_auto_increment => 0, - default_value => '', }, - title => + title => { data_type => 'varchar', size => 256, is_nullable => 0, - is_auto_increment => 0, - default_value => '', + }, + rank => + { data_type => 'integer', + size => 16, + is_nullable => 0, + default_value => 0, } ); -Most of this data isn't yet used directly by DBIx::Class, 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. +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::Album will +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. @@ -174,53 +176,70 @@ If you have a multi-column primary key, just pass a list instead: __PACKAGE__->set_primary_key( qw/ albumid artistid / ); -Define relationships that the class has with any other classes by 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 tables foreign key in one of their columns: +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: - __PACKAGE__->has_many('albums', 'My::Schema::Artist', 'album_id'); + # in My::Schema::Result::Artist + __PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist'); -More information about the various types of relationships available, and -how you can design your own, can be found in 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 external module, 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 My::Schema; - use base qw/DBIx::Class::Schema::Loader/; +The simplest way to use it is via the L script from the +L distribution. For example: + + $ 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 - __PACKAGE__->loader_options( relationships => 1 ); +If you are using L, then you can use the helper that comes with +L: - 1; + $ 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='"' -The actual autoloading process will occur when you create a connected -instance of your schema below. +See L for more information on this +helper. -L takes lots of other options. For more -information, consult its documentation. +See the L and L +documentation for more information on the many loader options. =head2 Connecting -To connect to your Schema, you also need to provide the connection details. -The arguments are the same as you would use for L: +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: +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. +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, like so: +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, @@ -230,53 +249,64 @@ in a special fifth argument to connect, like so: { on_connect_do => \@on_connect_sql_statments } ); -For more information about this and other special C-time options, -see L. +See L for more information about +this and other special C-time options. + +=head3 Via a database handle + +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. -To access your database using your $schema object, you can fetch a L -representing each of your tables by calling the ->resultset method. +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 = $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 call 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 = $schema->resultset('Album')->create({ + my $new_album = $schema->resultset('Album')->create({ title => 'Wish You Were Here', artist => 'Pink Floyd' }); @@ -287,27 +317,26 @@ 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 retrieving them first, by calling -delete directly on a ResultSet object. +You can also remove records without retrieving them first, by calling delete +directly on a ResultSet object. # Delete all of Falco's albums $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 = $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; @@ -324,11 +353,7 @@ Or, you can update them all at once: $rs->update({ year => 2001 }); -For more information on what you can do with a -L, see L. - -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 = $schema->resultset('Album')->search( @@ -343,20 +368,20 @@ We also provide a handy shortcut for doing a C search: # Find albums whose artist starts with 'Jimi' my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' }); -Or you can provide your own 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 = $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 = $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: @@ -365,22 +390,79 @@ 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 = My::Schema->resultset('Album')->search( { artist => 'Bob Marley' }, - { rows => 2, order_by => 'year DESC' } + { rows => 2, order_by => { -desc => 'year' } } ); 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_key(__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 $result = $schema->resultset('People') + ->search({ last_name => 'Dantes' }) + ->next; + $result->update({ children => 2 }); # <-- exception thrown because $result 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