First, choose a database. For testing/experimenting, we reccommend
L<DBD::SQLite>, which is a self-contained small database (i.e. all you
-need to do is to install L<DBD::SQLite> from CPAN, and it's usable).
+need to do is to install L<DBD::SQLite> from CPAN, and it works).
Next, spend some time defining which data you need to store, and how
it relates to the other data you have. For some help on normalisation,
L<DBIx::Class::Schema/deploy>. See there for details, or the
L<DBIx::Class::Manual::Cookbook>.
+=item .. store/retrieve Unicode data in my database?
+
+Make sure you database supports Unicode and set the connect
+attributes appropriately - see
+L<DBIx::Class::Manual::Cookbook/Using Unicode>
+
=item .. connect to my database?
Once you have created all the appropriate table/source classes, and an
Add the name of the schema to the L<DBIx::Class::ResultSource/table>
as part of the name, and make sure you give the one user you are going
-to connect with rights to read/write all the schemas/tables as
+to connect with has permissions to read/write all the schemas/tables as
necessary.
=back
the tables are to be joined. The condition may contain as many fields
as you like. See L<DBIx::Class::Relationship::Base>.
-=item .. define a relatiopnship across an intermediate table? (many-to-many)
+=item .. define a relationship across an intermediate table? (many-to-many)
Read the documentation on L<DBIx::Class::Relationship/many_to_many>.
=item .. search for data?
-Create a C<$schema> object, as mentioned above in ".. connect to my
-database". Find the L<ResultSet|DBIx::Class::Manual::Glossary/ResultSet>
-that you want to search in, and call C<search> on it. See
-L<DBIx::Class::ResultSet/search>.
+Create a C<$schema> object, as mentioned above in L</.. connect to my
+database?>. Find the
+L<ResultSet|DBIx::Class::Manual::Glossary/ResultSet> that you want to
+search in, by calling C<< $schema->resultset('MySource') >> and call
+C<search> on it. See L<DBIx::Class::ResultSet/search>.
=item .. search using database functions?
=item .. sort my results based on fields I've aliased using C<as>?
-You don't. You'll need to supply the same functions/expressions to
-C<order_by>, as you did to C<select>.
-
-To get "fieldname AS alias" in your SQL, you'll need to supply a
-literal chunk of SQL in your C<select> attribute, such as:
-
- ->search({}, { select => [ \'now() AS currenttime'] })
-
-Then you can use the alias in your C<order_by> attribute.
+You didn't alias anything, since L<as|DBIx::Class::ResultSet/as>
+B<has nothing to do> with the produced SQL. See
+L<DBIx::Class::ResultSet/select> for details.
=item .. group the results of my search?
=item .. group my results based on fields I've aliased using C<as>?
-You don't. You'll need to supply the same functions/expressions to
-C<group_by>, as you did to C<select>.
-
-To get "fieldname AS alias" in your SQL, you'll need to supply a
-literal chunk of SQL in your C<select> attribute, such as:
-
- ->search({}, { select => [ \'now() AS currenttime'] })
-
-Then you can use the alias in your C<group_by> attribute.
+You don't. See the explanation on ordering by an alias above.
=item .. filter the results of my search?
=item .. search with an SQL function on the left hand side?
-To use an SQL function on the left hand side of a comparison:
+To use an SQL function on the left hand side of a comparison you currently need
+to resort to literal SQL:
- ->search({}, { where => \'YEAR(date_of_birth)=1979' });
+ ->search( \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ] );
-=begin hidden
-
-(When the bind arg ordering bug is fixed, the previous example can be
-replaced with the following.)
-
- ->search({}, { where => \'YEAR(date_of_birth)=?', bind => [ 1979 ] });
-
-=end hidden
-
-Or, if you have quoting off:
-
- ->search({ 'YEAR(date_of_birth)' => 1979 });
+Note: the C<plain_value> string in the C<< [ plain_value => 1979 ] >> part
+should be either the same as the name of the column (do this if the type of the
+return value of the function is the same as the type of the column) or in the
+case of a function it's currently treated as a dummy string (it is a good idea
+to use C<plain_value> or something similar to convey intent). The value is
+currently only significant when handling special column types (BLOBs, arrays,
+etc.), but this may change in the future.
=item .. find more help on constructing searches?
Then, in the same class, implement a subroutine called "my_column" that
fetches the real value and does the formatting you want.
-See the Cookbook for more details.
+See the L<Cookbook|DBIx::Class::Manual::Cookbook> for more details.
=item .. fetch a single (or topmost) row?
-See L<DBIx::Class::Manual::Cookbook/Retrieve_one_and_only_one_row_from_a_resultset>.
+Use the L<DBIx::Class::ResultSet/rows> and
+L<DBIx::Class::ResultSet/order_by> attributes to order your data and
+pick off a single row.
+
+See also L<DBIx::Class::Manual::Cookbook/Retrieve_one_and_only_one_row_from_a_resultset>.
A less readable way is to ask a regular search to return 1 row, using
L<DBIx::Class::ResultSet/slice>:
=item .. refresh a row from storage?
-Use L<DBIx::Class::PK/discard_changes>.
+Use L<DBIx::Class::Row/discard_changes>.
$row->discard_changes
Discarding changes and refreshing from storage are two sides fo the same coin. When you
want to discard your local changes, just re-fetch the row from storage. When you want
to get a new, fresh copy of the row, just re-fetch the row from storage.
-L<DBIx::Class::PK/discard_changes> does just that by re-fetching the row from storage
+L<DBIx::Class::Row/discard_changes> does just that by re-fetching the row from storage
using the row's primary key.
=item .. fetch my data a "page" at a time?
=item .. insert a row with an auto incrementing primary key?
-In versions of L<DBIx::Class> less than 0.07, you need to ensure your
-table class loads the L<PK::Auto|DBIx::Class::PK::Auto>
-component. This will attempt to fetch the value of your primary key
-from the database after the insert has happened, and store it in the
-created object. In versions 0.07 and above, this component is
-automatically loaded.
+This happens automatically. After
+L<creating|DBIx::Class::ResultSet/create> a row object, the primary
+key value created by your database can be fetched by calling C<id> (or
+the access of your primary key column) on the object.
=item .. insert a row with a primary key that uses a sequence?
You need to create a trigger in your database that updates your
-primary key field from the sequence. To help PK::Auto find your
-inserted key, you can tell it the name of the sequence in the
+primary key field from the sequence. To help PK::Auto find the next
+key value, you can tell it the name of the sequence in the
C<column_info> supplied with C<add_columns>.
->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } });
=item .. insert many rows of data efficiently?
+The C<populate> method in L<DBIx::Class::ResultSet> provides
+efficient bulk inserts.
+
+L<DBIx::Class::Fixtures> provides an alternative way to do this.
+
=item .. update a collection of rows at the same time?
-Create a resultset using a search, to filter the rows of data you
-would like to update, then call update on the resultset to change all
+Create a resultset using a C<search>, to filter the rows of data you
+would like to update, then call C<update> on the resultset to change all
the rows at once.
=item .. use database functions when updating rows?
=item .. update a column using data from another column?
-To stop the column name from being quoted, you'll need to supply a
-scalar reference:
+To stop the column name from being quoted, you'll need to tell DBIC
+that the right hand side is an SQL identifier (it will be quoted
+properly if you have quoting enabled):
+
+ ->update({ somecolumn => { -ident => 'othercolumn' } })
- ->update({ somecolumn => \'othercolumn' })
+This method will not retrieve the new value and put it in your Row
+object. To fetch the new value, use the C<discard_changes> method on
+the Row.
-But note that when using a scalar reference the column in the database
-will be updated but when you read the value from the object with e.g.
+ # will return the scalar reference:
+ $row->somecolumn()
- ->somecolumn()
+ # issue a select using the PK to re-fetch the row data:
+ $row->discard_changes();
-you still get back the scalar reference to the string, B<not> the new
-value in the database. To get that you must refresh the row from storage
-using C<discard_changes()>. Or chain your function calls like this:
+ # Now returns the correct new value:
+ $row->somecolumn()
- ->update->discard_changes
+To update and refresh at once, chain your calls:
-to update the database and refresh the object in one step.
+ $row->update({ 'somecolumn' => { -ident => 'othercolumn' } })->discard_changes;
=item .. store JSON/YAML in a column and have it deflate/inflate automatically?
=back
+=head2 Custom methods in Result classes
+
+You can add custom methods that do arbitrary things, even to unrelated tables.
+For example, to provide a C<< $book->foo() >> method which searches the
+cd table, you'd could add this to Book.pm:
+
+ sub foo {
+ my ($self, $col_data) = @_;
+ return $self->result_source->schema->resultset('cd')->search($col_data);
+ }
+
+And invoke that on any Book Result object like so:
+
+ my $rs = $book->foo({ title => 'Down to Earth' });
+
+When two tables ARE related, L<DBIx::Class::Relationship::Base> provides many
+methods to find or create data in related tables for you. But if you want to
+write your own methods, you can.
+
+For example, to provide a C<< $book->foo() >> method to manually implement
+what create_related() from L<DBIx::Class::Relationship::Base> does, you could
+add this to Book.pm:
+
+ sub foo {
+ my ($self, $relname, $col_data) = @_;
+ return $self->related_resultset($relname)->create($col_data);
+ }
+
+Invoked like this:
+
+ my $author = $book->foo('author', { name => 'Fred' });
+
=head2 Misc
=over 4
calling methods in list context. When calling relationship accessors
you will not get resultsets, but a list of all the related objects.
-Starting with version 0.07, you can use L<DBIx::Class::ResultSet/search_rs>
-to work around this issue.
+Use the L<DBIx::Class::ResultSet/search_rs> method, or the
+relationship accessor methods ending with "_rs" to work around this
+issue.
+
+See also L<DBIx::Class::Relationship/has_many>.
=item See the SQL statements my code is producing?
-Turn on debugging! See L<DBIx::Class::Storage> for details of how
+Set the shell environment variable C<DBIC_TRACE> to a true value.
+
+For more info see L<DBIx::Class::Storage> for details of how
to turn on debugging in the environment, pass your own filehandle to
save debug to, or create your own callback.
using the tips in L<DBIx::Class::Manual::Cookbook/"Skip row object creation for faster results">
and L<DBIx::Class::Manual::Cookbook/"Get raw data for blindingly fast results">
+=item How do I override a run time method (e.g. a relationship accessor)?
+
+If you need access to the original accessor, then you must "wrap around" the original method.
+You can do that either with L<Moose::Manual::MethodModifiers> or L<Class::Method::Modifiers>.
+The code example works for both modules:
+
+ package Your::Schema::Group;
+ use Class::Method::Modifiers;
+
+ # ... declare columns ...
+
+ __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
+ __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
+
+ # if the server group is a "super group", then return all servers
+ # otherwise return only servers that belongs to the given group
+ around 'servers' => sub {
+ my $orig = shift;
+ my $self = shift;
+
+ return $self->$orig(@_) unless $self->is_super_group;
+ return $self->result_source->schema->resultset('Server')->all;
+ };
+
+If you just want to override the original method, and don't care about the data
+from the original accessor, then you have two options. Either use
+L<Method::Signatures::Simple> that does most of the work for you, or do
+it the "dirty way".
+
+L<Method::Signatures::Simple> way:
+
+ package Your::Schema::Group;
+ use Method::Signatures::Simple;
+
+ # ... declare columns ...
+
+ __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
+ __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
+
+ # The method keyword automatically injects the annoying my $self = shift; for you.
+ method servers {
+ return $self->result_source->schema->resultset('Server')->search({ ... });
+ }
+
+The dirty way:
+
+ package Your::Schema::Group;
+ use Sub::Name;
+
+ # ... declare columns ...
+
+ __PACKAGE__->has_many('group_servers', 'Your::Schema::GroupServer', 'group_id');
+ __PACKAGE__->many_to_many('servers', 'group_servers', 'server');
+
+ *servers = subname servers => sub {
+ my $self = shift;
+ return $self->result_source->schema->resultset('Server')->search({ ... });
+ };
+
=back
=head2 Notes for CDBI users
second one will use a default port of 5433, while L<DBD::Pg> is compiled with a
default port of 5432.
-You can chance the port setting in C<postgresql.conf>.
+You can change the port setting in C<postgresql.conf>.
=item I've lost or forgotten my mysql password
projects / dbsrgits/DBIx-Class.git / blobdiff