X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FFAQ.pod;h=98692c5a9aa9ff6885442d7eeedb18211a585202;hb=2552bd763b0bf0b646cf906c12e95aafb1ba0b7e;hp=11fcf98e87e0261638c83ace2ee8bde9b02e1cad;hpb=61d1e2c2616e7c9cbdcf7fa026341ce78a2e8b40;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/FAQ.pod b/lib/DBIx/Class/Manual/FAQ.pod index 11fcf98..98692c5 100644 --- a/lib/DBIx/Class/Manual/FAQ.pod +++ b/lib/DBIx/Class/Manual/FAQ.pod @@ -1,70 +1,570 @@ -=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 L, intended to provide greater -functionality and simplicity. +=head1 FAQs -It is inspired by the L framework, and meant to support -compability with it, while restructuring the internals and making it -possible to support some new features like self-joins, C, -Cs and more. +How Do I: -=head2 What databases does it support? +=head2 Getting started -At least MSSQL, MySQL, Oracle, PostgreSQL and SQLite. +=over 4 + +=item .. create a database to use? + +First, choose a database. For testing/experimenting, we reccommend +L, which is a self-contained small database (i.e. all you +need to do is to install L from CPAN, and it's usable). + +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. + +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. + +=item .. use DBIx::Class with L? + +Install L from CPAN. See its +documentation, or below, for further details. + +=item .. set up my DBIx::Class classes automatically from my database? + +Install L from CPAN, and read its documentation. + +=item .. set up my DBIx::Class classes manually? + +Look at the L and come back here if you get lost. + +=item .. create my database tables from my DBIx::Class schema? + +Create your classes manually, as above. Write a script that calls +L. See there for details, or the +L. + +=item .. connect to my database? + +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 rights 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 relatiopnship across an intermediate table? (many-to-many) + +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 ".. connect to my +database". Find the L +that you want to search in, 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 don't. You'll need to supply the same functions/expressions to +C, as you did to C attribute, such as: + + ->search({}, { select => [ \'now() AS currenttime'] }) + +Then you can use the alias in your C attribute. + +=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. You'll need to supply the same functions/expressions to +C, as you did to C attribute, such as: + + ->search({}, { select => [ \'now() AS currenttime'] }) + +Then you can use the alias in your C attribute. + +=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: + + ->search({}, { where => \'YEAR(date_of_birth)=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 }); + +=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 -=head2 What's the current status of this module? +=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: -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. + __PACKAGE__->add_columns(my_column => { accessor => '_hidden_my_column' }); -=head2 Where can I go for support? +Then, in the same class, implement a subroutine called "my_column" that +fetches the real value and does the formatting you want. - Mailing list: http://lists.rawmode.org/mailman/listinfo/dbix-class/ +See the Cookbook for more details. - SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/ +=item .. fetch a single (or topmost) row? - Wiki: http://dbix-class.shadowcatsystems.co.uk/ +See L. - IRC: irc.perl.org#dbix-class +A less readable way is to ask a regular search to return 1 row, using +L: -=head2 Inflating your date/time columns + ->search->(undef, { order_by => "id DESC" })->slice(0) -You can use L to automatically create -L objects from your date/time columns. For more information, -see L. +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. -=head1 POSTGRESQL-SPECIFIC PROBLEMS +=item .. refresh a row from storage? -=head2 Can't get last insert ID; inserts with serial primary keys fail +Use L. -Older L and L versions do not handle C -correctly, causing code that uses auto-incrementing primary key -columns to fail with a message such as: + $row->discard_changes - Can't get last insert id at /.../DBIx/Class/Row.pm line 95 +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. -In particular the RHEL 4 and FC3 Linux distributions both ship with -combinations of L and L modules that do not work -correctly. +=item .. fetch my data a "page" at a time? -L version 1.50 and L 1.43 are known to work. +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 -=head1 SEE ALSO +=head2 Inserting and updating data =over 4 -=item L +=item .. insert a row with an auto incrementing primary key? + +In versions of L less than 0.07, you need to ensure your +table class loads the L +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. + +=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 +C supplied with C. + + ->add_columns({ id => { sequence => 'mysequence', auto_nextval => 1 } }); + +=item .. insert many rows of data efficiently? + +=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 +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: + + ->update({ somecolumn => \'othercolumn' }) + +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. + + ->somecolumn() + +you still get back the scalar reference to the string, B the new +value in the database. To get that you must refresh the row from storage +using C. Or chain your function calls like this: + + ->update->discard_changes + +to update the database and refresh the object in one step. + +=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 -=cut +=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 classes. + +One method is to use the built in mk_group_accessors (via L) + + package MyTable; + + use parent 'DBIx::Class'; + + __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 MyTable; + + use Moose; # import Moose + use Moose::Util::TypeConstraint; # import Moose accessor type constraints + + extends 'DBIx::Class'; # 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. + +Starting with version 0.07, you can use L +to work around this issue. + +=item See the SQL statements my code is producing? + +Turn on debugging! 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 + +=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 chance 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