view release on metacpan or  search on metacpan

lib/DBIx/Struct.pod  view on Meta::CPAN

=back

=head3 C<filter_timestamp>

Removes microseconds from timestamp columns. Returns object's $self value.
 
 2010-05-19 23:30:01.737126 -> 2010-05-19 23:30:01

=head3 C<fetch>

Fetches the whole row data from table. Useful when primary key is known after
insert but other columns are set by database. Returns object's $self value.

=head3 B<Accessors>

All column names from table except special keywords are accessible via accessors:

 my $prim = one_row("prim", 1);
 print "payload: " .  $prim->payload . "\n";
 # new data
 $prim->payload("pay never");
 $prim->update;
 
Here $prim->payload("pay never") sets new column value. To store it explicitly 
call $prim->update. By default update is called on row object destruction. 
To include a random snippet of SQL verbatim, you specify it as a scalar reference.

 $client->bonus(\"bonus + 10");
 
Or with parameters:

 $client->bonus([\"bonus + ?", 10]);
 
This literal SQL expressions can be used for insert values also.

Special form C<_$column> allows to set internal column value without marking
it for actual update in table.

=head4 B<JSON> output support

Every row-object has C<TO_JSON()> function that can be automatically used
by C<JSON> C<encode> function. This C<TO_JSON()> has some flexibility what
to output. 

  my $user = DBC::Users->findOneByUsername("cruks");
  my $json = JSON->new->convert_blessed;
  # please pay attention on "id", its value is of number type
  print $json->encode($user); # {"username":"cruks","id":1}
  # only one field is required
  print $json->encode($user->TO_JSON("username")); # {"username":"cruks"}
  # one field and more data in output
  print $json->encode($user->TO_JSON("username", 
       {url => "http://site.com/login"})
    ); # {"username":"cruks","url":"http://site.com/login"}
  # just add more data in output
  print $json->encode($user->TO_JSON({url => "http://site.com/login"})); 
  # {"url":"http://site.com/login","username":"cruks","id":1}

=head4 B<JSON> type support

Fields with datatype 'json' or 'jsonb' can be transparently used as perl-structures.

 my $row = one_row(table => $id);
 # table is like
 #   id       | integer | not null default nextval('table_id_seq'::regclass)
 #   settings | jsonb   | 
 
 # row is like 
 #   id |                            settings                             
 #  ----+-----------------------------------------------------------------
 #    1 | {"limits": {"low": 15, "max": 100}}
 
 $row->settings->{current}{runner} = 100;
 
 # now row is like this
 #   id |                            settings                             
 #  ----+-----------------------------------------------------------------
 #    1 | {"limits": {"low": 15, "max": 100}, "current": {"runner": 100}}
 

=head2 Syntax sugar

=head3 find...By...

This is inspired by Spring-Data for Java. This automatically generated function
compiled on first call and selects row(s) or column value(s). 
It should be called on table class:

 my $authors_name = DBC::Article->findOneFirstNameById($id);
 my $authors_names_array_ref = DBC::Article->findFirst10FirstNameByOrderByLastNameAsc;

Format of this find-family is following:

 find-func = find<what>By<search>
 what = <plural><column><distinct>
 plural = All|One|First[$limit] | ''
 * One or First or First1 - selects only one row
 column = one column name | ''
 * returns direct value(s)
 distinct = Distinct | ''
 * select only distinct values
 search = <where><order>
 order = OrderBy<column-to-order-by>(Asc|Desc) | ''
 where = <column>|<column>And<column>|<column>Or<column>
 column = column name | column(LessThanEqual|LessThan|GreaterThanEqual|
                        GreaterThan|IsNull|IsNotNull|IsNot|NotNull|
                        NotEquals|NotIn|NotLike|IsEqualTo|IsTrue|
                        IsFalse|Equals|True|False|Like|Is|Not|In)

Column names are converted from CamelCase to underscore lowercase style: 
FirstName -> first_name. More examples:

 DBC::User->findAll;
 DBC::User->findFirst10ByOrderByLastName;
 DBC::User->findByAddressZipCode($zip);
 DBC::User->findByLoginDateGreaterThan($date);
 DBC::User->findByLoginDateGreaterThanAndReferralIsNull($date);

=head3 Referenced tables

Suppose you have two tables employer and employee: