X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FFAQ.pod;h=8a706e1c78387e2581f5eb8c1a0e15e364489dd0;hb=9361b05d319e60314bf2caff1e96ff3c388a50bb;hp=67cdaef7886a36a08abe89dd1611c7077a27ffca;hpb=159a8515db1d0f3c0cc89af5ae811a67985beef5;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/FAQ.pod b/lib/DBIx/Class/Manual/FAQ.pod index 67cdaef..8a706e1 100644 --- a/lib/DBIx/Class/Manual/FAQ.pod +++ b/lib/DBIx/Class/Manual/FAQ.pod @@ -1,60 +1,668 @@ -=head1 NAME +=head1 NAME -DBIx::Class::Manual::FAQ - Frequently Asked Questions +DBIx::Class::Manual::FAQ - Frequently Asked Questions (in theory) -=head1 General +=head1 DESCRIPTION -=head2 What is the point of this module? Is it a fork of Class::DBI? +This document is intended as an anti-map of the documentation. If you +know what you want to do, but not how to do it in L, then +look here. It does B contain much code or examples, it just gives +explanations and pointers to the correct pieces of documentation to +read. -This is an alternative to Class::DBI, intended to provide greater -functionality and simplicity. +=head1 FAQs -It is inspired by the Class::DBI framework, and meant to support -compability with it, while restructuring the internals and making it -possible to support some new features like self-joins, distinct, group -bys and more. +How Do I: -=head2 Who's the intended audience for this module? +=head2 Getting started -=head2 What databases does it support? +=over 4 -At least MSSQL, MySQL, Oracle, PostgreSQL and SQLite. +=item .. create a database to use? -=head2 What's the current status of this module? +First, choose a database. For testing/experimenting, we recommend +L, which is a self-contained small database (i.e. all you +need to do is to install L from CPAN, and it works). -This project is still at an early stage, so the maintainers don't make -any absolute promise that full backwards-compatibility will be -supported; however, if we can without compromising the improvements -we're trying to make, we will, and any non-compatible changes will -merit a full justification on the mailing list and a CPAN developer -release for people to test against. +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, +go to L. -=head2 What's planned in the future? +Now, decide whether you want to have the database itself be the +definitive source of information about the data layout, or your +DBIx::Class schema. If it's the former, look up the documentation for +your database, eg. L, on how +to create tables, and start creating them. For a nice universal +interface to your database, you can try L. If you decided +on the latter choice, read the FAQ on setting up your classes +manually, and the one on creating tables from your schema. -=head2 Where can I go for support? +=item .. use DBIx::Class with L? - Mailing list: http://lists.rawmode.org/mailman/listinfo/dbix-class/ +Install L from CPAN. See its +documentation, or below, for further details. - SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/ +=item .. set up my DBIx::Class classes automatically from my database? - Wiki: http://dbix-class.shadowcatsystems.co.uk/ +Install L from CPAN, and read its documentation. - IRC: irc.perl.org#dbix-class +=item .. set up my DBIx::Class classes manually? -=head1 PostgresQL Specific Problems +Look at the L and come back here if you get lost. -=head2 Can't get last insert id - inserts with serial primary keys fail +=item .. create my database tables from my DBIx::Class schema? -Older DBI and DBD::Pg versions fail to correctly handle -C correctly, causing code that uses auto incrementing -primary key columns to fail with a message such as:- +Create your classes manually, as above. Write a script that calls +L. See there for details, or the +L. - Can't get last insert id at /.../DBIx/Class/Row.pm line 95 +=item .. store/retrieve Unicode data in my database? -In particular the RHEL 4 and FC3 Linux distributions both ship with -combinations of DBI and DBD::Pg modules that do not work correctly. +Make sure you database supports Unicode and set the connect +attributes appropriately - see +L -DBI version 1.50 and DBD::Pg 1.43 are known to work. +=item .. connect to my database? -=cut +Once you have created all the appropriate table/source classes, and an +overall L class, you can start using +them in an application. To do this, you need to create a central +Schema object, which is used to access all the data in the various +tables. See L for details. The actual +connection does not happen until you actually request data, so don't +be alarmed if the error from incorrect connection details happens a +lot later. +=item .. use DBIx::Class across multiple databases? + +If your database server allows you to run querys across multiple +databases at once, then so can DBIx::Class. All you need to do is make +sure you write the database name as part of the +L call. Eg: + + __PACKAGE__->table('mydb.mytablename'); + +And load all the Result classes for both / all databases using one +L call. + +=item .. use DBIx::Class across PostgreSQL/DB2/Oracle schemas? + +Add the name of the schema to the L +as part of the name, and make sure you give the one user you are going +to connect with has permissions to read/write all the schemas/tables as +necessary. + +=back + +=head2 Relationships + +=over 4 + +=item .. tell DBIx::Class about relationships between my tables? + +There are a variety of relationship types that come pre-defined for +you to use. These are all listed in L. If +you need a non-standard type, or more information, look in +L. + +=item .. define a one-to-many relationship? + +This is called a C relationship on the one side, and a +C relationship on the many side. Currently these need to +be set up individually on each side. See L +for details. + +=item .. define a relationship where this table contains another table's primary key? (foreign key) + +Create a C relationship for the field containing the +foreign key. See L. + +=item .. define a foreign key relationship where the key field may contain NULL? + +Just create a C relationship, as above. If the column is +NULL then the inflation to the foreign object will not happen. This +has a side effect of not always fetching all the relevant data, if you +use a nullable foreign-key relationship in a JOIN, then you probably +want to set the C to C. + +=item .. define a relationship where the key consists of more than one column? + +Instead of supplying a single column name, all relationship types also +allow you to supply a hashref containing the condition across which +the tables are to be joined. The condition may contain as many fields +as you like. See L. + +=item .. define a relationship bridge across an intermediate table? (many-to-many) + +The term 'relationship' is used loosely with many_to_many as it is not considered a +relationship in the fullest sense. For more info, read the documentation on L. + +=item .. stop DBIx::Class from attempting to cascade deletes on my has_many and might_have relationships? + +By default, DBIx::Class cascades deletes and updates across +C and C relationships. You can disable this +behaviour on a per-relationship basis by supplying +C<< cascade_delete => 0 >> in the relationship attributes. + +The cascaded operations are performed after the requested delete or +update, so if your database has a constraint on the relationship, it +will have deleted/updated the related records or raised an exception +before DBIx::Class gets to perform the cascaded operation. + +See L. + +=item .. use a relationship? + +Use its name. An accessor is created using the name. See examples in +L. + +=back + +=head2 Searching + +=over 4 + +=item .. search for data? + +Create a C<$schema> object, as mentioned above in L. Find the +L that you want to +search in, by calling C<< $schema->resultset('MySource') >> and call +C on it. See L. + +=item .. search using database functions? + +Supplying something like: + + ->search({'mydatefield' => 'now()'}) + +to search, will probably not do what you expect. It will quote the +text "now()", instead of trying to call the function. To provide +literal, unquoted text you need to pass in a scalar reference, like +so: + + ->search({'mydatefield' => \'now()'}) + +=item .. sort the results of my search? + +Supply a list of columns you want to sort by to the C +attribute. See L. + +=item .. sort my results based on fields I've aliased using C? + +You didn't alias anything, since L +B with the produced SQL. See +L for details. + +=item .. group the results of my search? + +Supply a list of columns you want to group on, to the C +attribute, see L. + +=item .. group my results based on fields I've aliased using C? + +You don't. See the explanation on ordering by an alias above. + +=item .. filter the results of my search? + +The first argument to C is a hashref of accessor names and +values to filter them by, for example: + + ->search({'created_time' => { '>=', '2006-06-01 00:00:00' } }) + +Note that to use a function here you need to make it a scalar +reference: + + ->search({'created_time' => { '>=', \'yesterday()' } }) + +=item .. search in several tables simultaneously? + +To search in two related tables, you first need to set up appropriate +relationships between their respective classes. When searching you +then supply the name of the relationship to the C attribute in +your search, for example when searching in the Books table for all the +books by the author "Fred Bloggs": + + ->search({'authors.name' => 'Fred Bloggs'}, { join => 'authors' }) + +The type of join created in your SQL depends on the type of +relationship between the two tables, see L +for the join used by each relationship. + +=item .. create joins with conditions other than column equality? + +Currently, L can only create join conditions using +equality, so you're probably better off creating a C in your +database, and using that as your source. A C is a stored SQL +query, which can be accessed similarly to a table, see your database +documentation for details. + +=item .. search with an SQL function on the left hand side? + +To use an SQL function on the left hand side of a comparison you currently need +to resort to literal SQL: + + ->search( \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ] ); + +Note: the C 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 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? + +Behind the scenes, DBIx::Class uses L to help construct +its SQL searches. So if you fail to find help in the +L, try looking in the SQL::Abstract +documentation. + +=item .. make searches in Oracle (10gR2 and newer) case-insensitive? + +To make Oracle behave like most RDBMS use on_connect_do to issue +alter session statements on database connection establishment: + + ->on_connect_do("ALTER SESSION SET NLS_COMP = 'LINGUISTIC'"); + ->on_connect_do("ALTER SESSION SET NLS_SORT = '_CI'"); + e.g. + ->on_connect_do("ALTER SESSION SET NLS_SORT = 'BINARY_CI'"); + ->on_connect_do("ALTER SESSION SET NLS_SORT = 'GERMAN_CI'"); + + +=back + +=head2 Fetching data + +=over 4 + +=item .. fetch as much data as possible in as few select calls as possible? + +See the prefetch examples in the L. + +=item .. fetch a whole column of data instead of a row? + +Call C on a L. This returns a +L. See its documentation and the +L for details. + +=item .. fetch a formatted column? + +In your table schema class, create a "private" column accessor with: + + __PACKAGE__->add_columns(my_column => { accessor => '_hidden_my_column' }); + +Then, in the same class, implement a subroutine called "my_column" that +fetches the real value and does the formatting you want. + +See the L for more details. + +=item .. fetch a single (or topmost) row? + +Use the L and +L attributes to order your data and +pick off a single row. + +See also L. + +A less readable way is to ask a regular search to return 1 row, using +L: + + ->search->(undef, { order_by => "id DESC" })->slice(0) + +which (if supported by the database) will use LIMIT/OFFSET to hint to the +database that we really only need one row. This can result in a significant +speed improvement. The method using L mentioned +in the cookbook can do the same if you pass a C attribute to the search. + +=item .. refresh a row from storage? + +Use L. + + $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 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? + +Pass the C and C attributes to your search, eg: + + ->search({}, { rows => 10, page => 1}); + +=item .. get a count of all rows even when paging? + +Call C on the paged resultset, it will return a L +object. Calling C on the pager will return the correct +total. + +C on the resultset will only return the total number in the page. + +=back + +=head2 Inserting and updating data + +=over 4 + +=item .. insert a row with an auto incrementing primary key? + +This happens automatically. After +L a row object, the primary +key value created by your database can be fetched by calling C (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 the next +key value, you can tell it the name of the sequence in the +C supplied with C. + + ->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } }); + +=item .. insert many rows of data efficiently? + +The C method in L provides +efficient bulk inserts. + +L provides an alternative way to do this. + +=item .. update a collection of rows at the same time? + +Create a resultset using a C, to filter the rows of data you +would like to update, then call C 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 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' } }) + +This method will not retrieve the new value and put it in your Row +object. To fetch the new value, use the C method on +the Row. + + # will return the scalar reference: + $row->somecolumn() + + # issue a select using the PK to re-fetch the row data: + $row->discard_changes(); + + # Now returns the correct new value: + $row->somecolumn() + +To update and refresh at once, chain your calls: + + $row->update({ 'somecolumn' => { -ident => 'othercolumn' } })->discard_changes; + +=item .. store JSON/YAML in a column and have it deflate/inflate automatically? + +You can use L to accomplish YAML/JSON storage transparently. + +If you want to use JSON, then in your table schema class, do the following: + + use JSON; + + __PACKAGE__->add_columns(qw/ ... my_column ../) + __PACKAGE__->inflate_column('my_column', { + inflate => sub { jsonToObj(shift) }, + deflate => sub { objToJson(shift) }, + }); + +For YAML, in your table schema class, do the following: + + use YAML; + + __PACKAGE__->add_columns(qw/ ... my_column ../) + __PACKAGE__->inflate_column('my_column', { + inflate => sub { YAML::Load(shift) }, + deflate => sub { YAML::Dump(shift) }, + }); + +This technique is an easy way to store supplemental unstructured data in a table. Be +careful not to overuse this capability, however. If you find yourself depending more +and more on some data within the inflated column, then it may be time to factor that +data out. + +=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 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 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 + +=item How do I store my own (non-db) data in my DBIx::Class objects? + +You can add your own data accessors to your Result classes. + +One method is to use the built in mk_group_accessors (via L) + + package App::Schema::Result::MyTable; + + use parent 'DBIx::Class::Core'; + + __PACKAGE__->table('foo'); #etc + __PACKAGE__->mk_group_accessors('simple' => qw/non_column_data/); # must use simple group + +An another method is to use L with your L package. + + package App::Schema::Result::MyTable; + + use Moose; # import Moose + use Moose::Util::TypeConstraint; # import Moose accessor type constraints + + extends 'DBIx::Class::Core'; # Moose changes the way we define our parent (base) package + + has 'non_column_data' => ( is => 'rw', isa => 'Str' ); # define a simple attribute + + __PACKAGE__->table('foo'); # etc + +With either of these methods the resulting use of the accesssor would be + + my $row; + + # assume that somewhere in here $row will get assigned to a MyTable row + + $row->non_column_data('some string'); # would set the non_column_data accessor + + # some other stuff happens here + + $row->update(); # would not inline the non_column_data accessor into the update + + +=item How do I use DBIx::Class objects in my TT templates? + +Like normal objects, mostly. However you need to watch out for TT +calling methods in list context. When calling relationship accessors +you will not get resultsets, but a list of all the related objects. + +Use the L method, or the +relationship accessor methods ending with "_rs" to work around this +issue. + +See also L. + +=item See the SQL statements my code is producing? + +Set the shell environment variable C to a true value. + +For more info see L for details of how +to turn on debugging in the environment, pass your own filehandle to +save debug to, or create your own callback. + +=item Why didn't my search run any SQL? + +L runs the actual SQL statement as late as possible, thus +if you create a resultset using C in scalar context, no query +is executed. You can create further resultset refinements by calling +search again or relationship accessors. The SQL query is only run when +you ask the resultset for an actual row object. + +=item How do I deal with tables that lack a primary key? + +If your table lacks a primary key, DBIx::Class can't work out which row +it should operate on, for example to delete or update. However, a +UNIQUE constraint on one or more columns allows DBIx::Class to uniquely +identify the row, so you can tell L these +columns act as a primary key, even if they don't from the database's +point of view: + + $resultset->set_primary_key(@column); + +=item How do I make my program start faster? + +Look at the tips in L + +=item How do I reduce the overhead of database queries? + +You can reduce the overhead of object creation within L +using the tips in L +and L + +=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 or L. +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 that does most of the work for you, or do +it the "dirty way". + +L 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 + +=over 4 + +=item Is there a way to make an object auto-stringify itself as a +particular column or group of columns (a-la cdbi Stringfy column +group, or stringify_self method) ? + +See L + +=back + +=head2 Troubleshooting + +=over 4 + +=item Help, I can't connect to postgresql! + +If you get an error such as: + + DBI connect('dbname=dbic','user',...) failed: could not connect to server: + No such file or directory Is the server running locally and accepting + connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? + +Likely you have/had two copies of postgresql installed simultaneously, the +second one will use a default port of 5433, while L is compiled with a +default port of 5432. + +You can change the port setting in C. + +=item I've lost or forgotten my mysql password + +Stop mysqld and restart it with the --skip-grant-tables option. + +Issue the following statements in the mysql client. + + UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root'; + FLUSH PRIVILEGES; + +Restart mysql. + +Taken from: + +L. + +=back